Examples for changing table scheme from MAL/C
Hello Developers,
I need some help to understand monetdb kernel/sql structure.
Please, provide example code/paper or link for follow situation: I have a table (SQL), and I want to change structure of it (add/drop/rename/order column). I know, I can do it with SQL, but I need to view examples in MAL or C.
I want to write function like this:
int add_column(str schemaName, str tableName, str newColumnName) { // return 1 if column successfully created }
Before posting this message I saw the source code of SQL kernel and found some functions like sys_drop_column, mvc_create_column and others. But, I don't know how to execute these functions from my own user defined C/MAL function.
Hello Developers,
I wrote follow function, which renames table column:
C source:
/* Rename column * rename_column(schema:str, table:str, oldColumnName:str, newColumnName:str) * */ str rename_column(Client cntxt, MalBlkPtr mb, MalStkPtr stk, InstrPtr pci) { /* arguments */ int* result = getArgReference_int(stk, pci, 0); str* schemaName = getArgReference_str(stk, pci, 1); str* tableName = getArgReference_str(stk, pci, 2); str* oldColumnName = getArgReference_str(stk, pci, 3); str* newColumnName = getArgReference_str(stk, pci, 4);
sql_schema* schema; sql_table* table; sql_column* oldColumn; sql_column* newColumn;
mvc *m = NULL;
str msg;
*result = 0;
/* check for SQL context */ if ((msg = getSQLContext(cntxt, mb, &m, NULL)) != NULL) return msg; if ((msg = checkSQLContext(cntxt)) != NULL) return msg;
/* get schema */ schema = mvc_bind_schema(m, *schemaName);
if (schema == NULL) throw(SQL, "rename_column", "Schema not found");
/* get table */ table = mvc_bind_table(m, schema, *tableName);
if (table == NULL) throw(SQL, "rename_column", "Table not found");
if (strcmp(*oldColumnName, *newColumnName) == 0) throw(SQL, "rename_column", "New column name is same as old name");
/* get old column */ oldColumn = mvc_bind_column(m, table, *oldColumnName);
if (oldColumn == NULL) throw(SQL, "rename_column", "Column not found");
/* get old column */ newColumn = mvc_bind_column(m, table, *newColumnName);
if (newColumn) throw(SQL, "rename_column", "Column with new name already exists");
sql_schema* sysSchema = mvc_bind_schema(m, "sys"); sql_table* sct = mvc_bind_table(m, sysSchema, "_columns"); sql_column* sctId = mvc_bind_column(m, sct, "id"); sql_column* sctName = mvc_bind_column(m, sct, "name");
if (m->debug) fprintf(stderr, "rename_column: Renaming column %s(id:%d, nr:%d) to %s\n" , oldColumn->base.name, oldColumn->base.id, oldColumn->colnr , newColumnName);
oid rowId = table_funcs.column_find_row(m->session->tr, sctId, (void* )&oldColumn->base.id, NULL);
/* set the new name */ oldColumn->base.name = GDKstrdup(*newColumnName);
/* update system table */ table_funcs.column_update_value(m->session->tr, sctName, rowId, (void* )oldColumn->base.name);
if (m->debug) fprintf(stderr, "rename_column: Rename finished\n");
*result = 1;
return MAL_SUCCEED; }
I inserted this function into sql.c, because I don't know how to call sql kernel functions from UDF (udf.c).
MAL source:
module udf;
# rename_column pattern rename_column(schemaName:str, tableName:str, oldColumnName:str, newColumnName:str):int address rename_column comment "Rename column of SQL table";
SQL source:
-- rename_column create function rename_column(schemaName string, tableName string, oldColumnName string, newColumnName string) returns int external name udf.rename_column;
I am calling rename function from mclient:
CREATE TABLE test (column1 int, column2 string); SELECT rename_column('sys', 'test', 'column1', 'column3');
Function succeeds, but after restarting server and calling "test" table unexpectedly stops the server. Someone can explain me, how to debug mserver5 on Windows environment "before" it goes to cras.
participants (1)
-
Sherzod Mutalov