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. -- Best regards, Sherzod mailto:shmutalov@gmail.com
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. -- Best regards, Sherzod mailto:shmutalov@gmail.com
participants (1)
-
Sherzod Mutalov