Changeset: f3aa538ab35d for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/f3aa538ab35d Modified Files: sql/common/sql_types.c sql/include/sql_catalog.h sql/storage/store.c Branch: mangled Log Message:
Merge with default. diffs (truncated from 8693 to 300 lines): diff --git a/clients/Tests/MAL-signatures.stable.out b/clients/Tests/MAL-signatures.stable.out --- a/clients/Tests/MAL-signatures.stable.out +++ b/clients/Tests/MAL-signatures.stable.out @@ -6386,6 +6386,8 @@ [ "batsql", "ntile", "pattern batsql.ntile(X_0:bat[:any], X_1:any_1, X_2:any_2, X_3:any_3):bat[:any_1] ", "SQLntile;", "return the groups divided as equally as possible" ] [ "batsql", "ntile", "pattern batsql.ntile(X_0:bat[:any], X_1:bat[:any_1], X_2:any_2, X_3:any_3):bat[:any_1] ", "SQLntile;", "return the groups divided as equally as possible" ] [ "batsql", "password", "pattern batsql.password(X_0:bat[:str]):bat[:str] ", "db_password_wrap;", "Return password hash of user" ] +[ "batsql", "peak_next_value", "pattern batsql.peak_next_value(X_0:bat[:str], X_1:bat[:str]):bat[:lng] ", "mvc_peak_next_value_bulk;", "Peaks at the next value of the sequence" ] +[ "batsql", "peak_next_value", "pattern batsql.peak_next_value(X_0:bat[:str], X_1:bat[:str], X_2:bat[:oid], X_3:bat[:oid]):bat[:lng] ", "mvc_peak_next_value_bulk;", "Peaks at the next value of the sequence" ] [ "batsql", "percent_rank", "pattern batsql.percent_rank(X_0:bat[:any_1], X_1:any_2, X_2:any_3):bat[:dbl] ", "SQLpercent_rank;", "return the percentage into the total number of groups for each row" ] [ "batsql", "prod", "pattern batsql.prod(X_0:bat[:bte], X_1:any, X_2:any, X_3:int, X_4:any, X_5:any):bat[:lng] ", "SQLprod;", "return the product of groups" ] [ "batsql", "prod", "pattern batsql.prod(X_0:bat[:dbl], X_1:any, X_2:any, X_3:int, X_4:any, X_5:any):bat[:dbl] ", "SQLprod;", "return the product of groups" ] @@ -9291,7 +9293,7 @@ [ "sql", "bind_idxbat", "pattern sql.bind_idxbat(X_0:int, X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int) (X_7:bat[:oid], X_8:bat[:any_1]) ", "mvc_bind_idxbat_wrap;", "Bind the 'schema.table.index' BAT with access kind:\n0 - base table\n1 - inserts\n2 - updates" ] [ "sql", "bind_idxbat", "pattern sql.bind_idxbat(X_0:int, X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int):bat[:any_1] ", "mvc_bind_idxbat_wrap;", "Bind the 'schema.table.index' BAT with access kind:\n0 - base table\n1 - inserts\n2 - updates" ] [ "sql", "claim", "unsafe pattern sql.claim(X_0:int, X_1:str, X_2:str, X_3:lng) (X_4:oid, X_5:bat[:oid]) ", "mvc_claim_wrap;", "Claims slots for appending rows." ] -[ "sql", "clear_table", "unsafe pattern sql.clear_table(X_0:str, X_1:str):lng ", "mvc_clear_table_wrap;", "Clear the table sname.tname." ] +[ "sql", "clear_table", "unsafe pattern sql.clear_table(X_0:str, X_1:str, X_2:int):lng ", "mvc_clear_table_wrap;", "Clear the table sname.tname." ] [ "sql", "copy_from", "unsafe pattern sql.copy_from(X_0:ptr, X_1:str, X_2:str, X_3:str, X_4:str, X_5:str, X_6:lng, X_7:lng, X_8:int, X_9:str, X_10:int, X_11:int):bat[:any]... ", "mvc_import_table_wrap;", "Import a table from bstream s with the \ngiven tuple and seperators (sep/rsep)" ] [ "sql", "copy_rejects", "pattern sql.copy_rejects() (X_0:bat[:lng], X_1:bat[:int], X_2:bat[:str], X_3:bat[:str]) ", "COPYrejects;", "" ] [ "sql", "copy_rejects_clear", "unsafe pattern sql.copy_rejects_clear():void ", "COPYrejects_clear;", "" ] @@ -9678,7 +9680,7 @@ [ "wlc", "alter_user", "pattern wlc.alter_user(X_0:str, X_1:str, X_2:int, X_3:str, X_4:str):void ", "WLCgeneric;", "Catalog operation alter_user" ] [ "wlc", "append", "pattern wlc.append(X_0:str, X_1:str, X_2:str, X_3:any):int ", "WLCappend;", "Keep the insertions in the workload-capture-replay list" ] [ "wlc", "catalog", "pattern wlc.catalog(X_0:str):void ", "WLCcatalog;", "Keep the catalog changing queries for replay. " ] -[ "wlc", "clear_table", "pattern wlc.clear_table(X_0:str, X_1:str):int ", "WLCclear_table;", "Keep the deletions in the workload-capture-replay list" ] +[ "wlc", "clear_table", "pattern wlc.clear_table(X_0:str, X_1:str, X_2:int):int ", "WLCclear_table;", "Keep the deletions in the workload-capture-replay list" ] [ "wlc", "comment_on", "pattern wlc.comment_on(X_0:int, X_1:str):void ", "WLCgeneric;", "Catalog operation comment_on" ] [ "wlc", "commit", "pattern wlc.commit():void ", "WLCcommitCmd;", "Commit the workload-capture-replay record" ] [ "wlc", "commit", "pattern wlc.commit():void ", "WLCcommitCmd;", "Mark the end of the work unit" ] @@ -9748,7 +9750,7 @@ [ "wlr", "alter_user", "pattern wlr.alter_user(X_0:str, X_1:str, X_2:int, X_3:str, X_4:str):void ", "WLRgeneric;", "Catalog operation alter_user" ] [ "wlr", "append", "pattern wlr.append(X_0:str, X_1:str, X_2:str, X_3:oid, X_4:bat[:oid], X_5:any...):int ", "WLRappend;", "Apply the insertions in the workload-capture-replay list" ] [ "wlr", "catalog", "pattern wlr.catalog(X_0:str):void ", "WLRcatalog;", "A catalog changing query" ] -[ "wlr", "clear_table", "pattern wlr.clear_table(X_0:str, X_1:str):int ", "WLRclear_table;", "Destroy the tuples in the table" ] +[ "wlr", "clear_table", "pattern wlr.clear_table(X_0:str, X_1:str, X_2:int):int ", "WLRclear_table;", "Destroy the tuples in the table" ] [ "wlr", "comment_on", "pattern wlr.comment_on(X_0:int, X_1:str):void ", "WLRgeneric;", "Catalog operation comment_on" ] [ "wlr", "commit", "pattern wlr.commit():void ", "WLRcommit;", "Mark the end of the work unit" ] [ "wlr", "create_function", "pattern wlr.create_function(X_0:str, X_1:str, X_2:int):void ", "WLRgeneric;", "Catalog operation create_function" ] diff --git a/clients/Tests/MAL-signatures.stable.out.int128 b/clients/Tests/MAL-signatures.stable.out.int128 --- a/clients/Tests/MAL-signatures.stable.out.int128 +++ b/clients/Tests/MAL-signatures.stable.out.int128 @@ -8927,6 +8927,8 @@ [ "batsql", "ntile", "pattern batsql.ntile(X_0:bat[:any], X_1:any_1, X_2:any_2, X_3:any_3):bat[:any_1] ", "SQLntile;", "return the groups divided as equally as possible" ] [ "batsql", "ntile", "pattern batsql.ntile(X_0:bat[:any], X_1:bat[:any_1], X_2:any_2, X_3:any_3):bat[:any_1] ", "SQLntile;", "return the groups divided as equally as possible" ] [ "batsql", "password", "pattern batsql.password(X_0:bat[:str]):bat[:str] ", "db_password_wrap;", "Return password hash of user" ] +[ "batsql", "peak_next_value", "pattern batsql.peak_next_value(X_0:bat[:str], X_1:bat[:str]):bat[:lng] ", "mvc_peak_next_value_bulk;", "Peaks at the next value of the sequence" ] +[ "batsql", "peak_next_value", "pattern batsql.peak_next_value(X_0:bat[:str], X_1:bat[:str], X_2:bat[:oid], X_3:bat[:oid]):bat[:lng] ", "mvc_peak_next_value_bulk;", "Peaks at the next value of the sequence" ] [ "batsql", "percent_rank", "pattern batsql.percent_rank(X_0:bat[:any_1], X_1:any_2, X_2:any_3):bat[:dbl] ", "SQLpercent_rank;", "return the percentage into the total number of groups for each row" ] [ "batsql", "prod", "pattern batsql.prod(X_0:bat[:bte], X_1:any, X_2:any, X_3:int, X_4:any, X_5:any):bat[:hge] ", "SQLprod;", "return the product of groups" ] [ "batsql", "prod", "pattern batsql.prod(X_0:bat[:bte], X_1:any, X_2:any, X_3:int, X_4:any, X_5:any):bat[:lng] ", "SQLprod;", "return the product of groups" ] @@ -12593,7 +12595,7 @@ [ "sql", "bind_idxbat", "pattern sql.bind_idxbat(X_0:int, X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int) (X_7:bat[:oid], X_8:bat[:any_1]) ", "mvc_bind_idxbat_wrap;", "Bind the 'schema.table.index' BAT with access kind:\n0 - base table\n1 - inserts\n2 - updates" ] [ "sql", "bind_idxbat", "pattern sql.bind_idxbat(X_0:int, X_1:str, X_2:str, X_3:str, X_4:int, X_5:int, X_6:int):bat[:any_1] ", "mvc_bind_idxbat_wrap;", "Bind the 'schema.table.index' BAT with access kind:\n0 - base table\n1 - inserts\n2 - updates" ] [ "sql", "claim", "unsafe pattern sql.claim(X_0:int, X_1:str, X_2:str, X_3:lng) (X_4:oid, X_5:bat[:oid]) ", "mvc_claim_wrap;", "Claims slots for appending rows." ] -[ "sql", "clear_table", "unsafe pattern sql.clear_table(X_0:str, X_1:str):lng ", "mvc_clear_table_wrap;", "Clear the table sname.tname." ] +[ "sql", "clear_table", "unsafe pattern sql.clear_table(X_0:str, X_1:str, X_2:int):lng ", "mvc_clear_table_wrap;", "Clear the table sname.tname." ] [ "sql", "copy_from", "unsafe pattern sql.copy_from(X_0:ptr, X_1:str, X_2:str, X_3:str, X_4:str, X_5:str, X_6:lng, X_7:lng, X_8:int, X_9:str, X_10:int, X_11:int):bat[:any]... ", "mvc_import_table_wrap;", "Import a table from bstream s with the \ngiven tuple and seperators (sep/rsep)" ] [ "sql", "copy_rejects", "pattern sql.copy_rejects() (X_0:bat[:lng], X_1:bat[:int], X_2:bat[:str], X_3:bat[:str]) ", "COPYrejects;", "" ] [ "sql", "copy_rejects_clear", "unsafe pattern sql.copy_rejects_clear():void ", "COPYrejects_clear;", "" ] @@ -12999,7 +13001,7 @@ [ "wlc", "alter_user", "pattern wlc.alter_user(X_0:str, X_1:str, X_2:int, X_3:str, X_4:str):void ", "WLCgeneric;", "Catalog operation alter_user" ] [ "wlc", "append", "pattern wlc.append(X_0:str, X_1:str, X_2:str, X_3:any):int ", "WLCappend;", "Keep the insertions in the workload-capture-replay list" ] [ "wlc", "catalog", "pattern wlc.catalog(X_0:str):void ", "WLCcatalog;", "Keep the catalog changing queries for replay. " ] -[ "wlc", "clear_table", "pattern wlc.clear_table(X_0:str, X_1:str):int ", "WLCclear_table;", "Keep the deletions in the workload-capture-replay list" ] +[ "wlc", "clear_table", "pattern wlc.clear_table(X_0:str, X_1:str, X_2:int):int ", "WLCclear_table;", "Keep the deletions in the workload-capture-replay list" ] [ "wlc", "comment_on", "pattern wlc.comment_on(X_0:int, X_1:str):void ", "WLCgeneric;", "Catalog operation comment_on" ] [ "wlc", "commit", "pattern wlc.commit():void ", "WLCcommitCmd;", "Commit the workload-capture-replay record" ] [ "wlc", "commit", "pattern wlc.commit():void ", "WLCcommitCmd;", "Mark the end of the work unit" ] @@ -13069,7 +13071,7 @@ [ "wlr", "alter_user", "pattern wlr.alter_user(X_0:str, X_1:str, X_2:int, X_3:str, X_4:str):void ", "WLRgeneric;", "Catalog operation alter_user" ] [ "wlr", "append", "pattern wlr.append(X_0:str, X_1:str, X_2:str, X_3:oid, X_4:bat[:oid], X_5:any...):int ", "WLRappend;", "Apply the insertions in the workload-capture-replay list" ] [ "wlr", "catalog", "pattern wlr.catalog(X_0:str):void ", "WLRcatalog;", "A catalog changing query" ] -[ "wlr", "clear_table", "pattern wlr.clear_table(X_0:str, X_1:str):int ", "WLRclear_table;", "Destroy the tuples in the table" ] +[ "wlr", "clear_table", "pattern wlr.clear_table(X_0:str, X_1:str, X_2:int):int ", "WLRclear_table;", "Destroy the tuples in the table" ] [ "wlr", "comment_on", "pattern wlr.comment_on(X_0:int, X_1:str):void ", "WLRgeneric;", "Catalog operation comment_on" ] [ "wlr", "commit", "pattern wlr.commit():void ", "WLRcommit;", "Mark the end of the work unit" ] [ "wlr", "create_function", "pattern wlr.create_function(X_0:str, X_1:str, X_2:int):void ", "WLRgeneric;", "Catalog operation create_function" ] diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c --- a/clients/mapiclient/dump.c +++ b/clients/mapiclient/dump.c @@ -1506,46 +1506,55 @@ describe_sequence(Mapi mid, const char * goto bailout; snprintf(query, maxquerylen, - "SELECT s.name, " /* 0 */ - "seq.name, " /* 1 */ - "peak_next_value_for(s.name, seq.name), " /* 2 */ - "seq.\"minvalue\", " /* 3 */ - "seq.\"maxvalue\", " /* 4 */ - "seq.\"increment\", " /* 5 */ - "seq.\"cycle\", " /* 6 */ - "seq.\"cacheinc\", " /* 7 */ - "rem.\"remark\" " /* 8 */ - "FROM sys.sequences seq LEFT OUTER JOIN sys.comments rem ON seq.id = rem.id, " - "sys.schemas s " - "WHERE s.id = seq.schema_id " - "AND s.name = '%s' " - "AND seq.name = '%s' " - "ORDER BY s.name, seq.name", + "SELECT c.remark, q.* " + "FROM sys.sequences seq LEFT OUTER JOIN sys.comments c ON seq.id = c.id, " + "sys.schemas s, " + "sys.describe_sequences q " + "WHERE s.id = seq.schema_id " + "AND s.name = '%s' " /* schema name */ + "AND seq.name = '%s' " /* sequence name */ + "AND q.sch = '%s' " /* schema name */ + "AND q.seq = '%s' " /* sequence name */ + "ORDER BY q.sch, q.seq", + schema, tname, schema, tname); if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) goto bailout; while (mapi_fetch_row(hdl) != 0) { - const char *schema = mapi_fetch_field(hdl, 0); - const char *name = mapi_fetch_field(hdl, 1); - const char *start = mapi_fetch_field(hdl, 2); - const char *minvalue = mapi_fetch_field(hdl, 3); - const char *maxvalue = mapi_fetch_field(hdl, 4); - const char *increment = mapi_fetch_field(hdl, 5); - const char *cycle = mapi_fetch_field(hdl, 6); - const char *cacheinc = mapi_fetch_field(hdl, 7); - const char *remark = mapi_fetch_field(hdl, 8); + const char *remark = mapi_fetch_field(hdl, 0); + const char *schema = mapi_fetch_field(hdl, 1); /* sch */ + const char *name = mapi_fetch_field(hdl, 2); /* seq */ + const char *restart = mapi_fetch_field(hdl, 4); /* rs */ + const char *minvalue; + const char *maxvalue; + const char *increment = mapi_fetch_field(hdl, 7); /* inc */ + const char *cacheinc = mapi_fetch_field(hdl, 8); /* cache */ + const char *cycle = mapi_fetch_field(hdl, 9); /* cycle */ + if (mapi_get_field_count(hdl) > 10) { + /* new version (Jan2022) of sys.describe_sequences */ + minvalue = mapi_fetch_field(hdl, 12); /* rmi */ + maxvalue = mapi_fetch_field(hdl, 13); /* rma */ + } else { + /* old version (pre Jan2022) of sys.describe_sequences */ + minvalue = mapi_fetch_field(hdl, 5); /* minvalue */ + maxvalue = mapi_fetch_field(hdl, 6); /* maxvalue */ + if (strcmp(minvalue, "0") == 0) + minvalue = NULL; + if (strcmp(maxvalue, "0") == 0) + maxvalue = NULL; + } mnstr_printf(toConsole, "CREATE SEQUENCE "); dquoted_print(toConsole, schema, "."); dquoted_print(toConsole, name, NULL); - mnstr_printf(toConsole, " START WITH %s", start); + mnstr_printf(toConsole, " START WITH %s", restart); if (strcmp(increment, "1") != 0) mnstr_printf(toConsole, " INCREMENT BY %s", increment); - if (strcmp(minvalue, "0") != 0) + if (minvalue) mnstr_printf(toConsole, " MINVALUE %s", minvalue); - if (strcmp(maxvalue, "0") != 0) + if (maxvalue) mnstr_printf(toConsole, " MAXVALUE %s", maxvalue); if (strcmp(cacheinc, "1") != 0) mnstr_printf(toConsole, " CACHE %s", cacheinc); @@ -1683,9 +1692,9 @@ dump_table_data(Mapi mid, const char *sc goto bailout; if (mapi_rows_affected(hdl) != 1) { if (mapi_rows_affected(hdl) == 0) - fprintf(stderr, "table '%s.%s' does not exist\n", schema, tname); + fprintf(stderr, "table %s.%s does not exist\n", schema, tname); else - fprintf(stderr, "table '%s.%s' is not unique\n", schema, tname); + fprintf(stderr, "table %s.%s is not unique\n", schema, tname); goto bailout; } while ((mapi_fetch_row(hdl)) != 0) { @@ -1853,6 +1862,93 @@ bailout: return 1; } +static int +dump_table_alters(Mapi mid, const char *schema, const char *tname, stream *toConsole) +{ + char *sname = NULL; + char *query = NULL; + size_t maxquerylen; + MapiHdl hdl = NULL; + char *s = NULL; + char *t = NULL; + int rc = 1; + + if (schema == NULL) { + if ((sname = strchr(tname, '.')) != NULL) { + size_t len = sname - tname + 1; + + sname = malloc(len); + if (sname == NULL) + goto bailout; + strcpy_len(sname, tname, len); + tname += len; + } else if ((sname = get_schema(mid)) == NULL) { + goto bailout; + } + schema = sname; + } + + maxquerylen = 5120 + 2*strlen(tname) + 2*strlen(schema); + query = malloc(maxquerylen); + s = sescape(schema); + t = sescape(tname); + if (query == NULL || s == NULL || t == NULL) + goto bailout; + + snprintf(query, maxquerylen, + "SELECT t.access FROM sys._tables t, sys.schemas s " + "WHERE s.name = '%s' AND t.schema_id = s.id AND t.name = '%s'", + s, t); + if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) + goto bailout; + if (mapi_rows_affected(hdl) != 1) { + if (mapi_rows_affected(hdl) == 0) + fprintf(stderr, "table %s.%s does not exist\n", schema, tname); + else + fprintf(stderr, "table %s.%s is not unique\n", schema, tname); + goto bailout; + } + while ((mapi_fetch_row(hdl)) != 0) { + const char *access = mapi_fetch_field(hdl, 0); + if (access && (*access == '1' || *access == '2')) { + mnstr_printf(toConsole, "ALTER TABLE "); + dquoted_print(toConsole, schema, "."); + dquoted_print(toConsole, tname, " "); + mnstr_printf(toConsole, "SET %s ONLY;\n", *access == '1' ? "READ" : "INSERT"); + } + } + mapi_close_handle(hdl); + snprintf(query, maxquerylen, + "SELECT name, storage FROM sys._columns " + "WHERE storage IS NOT NULL " + "AND table_id = (SELECT id FROM sys._tables WHERE name = '%s' " + "AND schema_id = (SELECT id FROM sys.schemas WHERE name = '%s'))", + t, s); + if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid)) + goto bailout; + while ((mapi_fetch_row(hdl)) != 0) { + const char *cname = mapi_fetch_field(hdl, 0); + const char *storage = mapi_fetch_field(hdl, 1); + char *stg = sescape(storage); + if (stg == NULL) + goto bailout; + mnstr_printf(toConsole, "ALTER TABLE "); + dquoted_print(toConsole, schema, "."); + dquoted_print(toConsole, tname, " "); + mnstr_printf(toConsole, "ALTER COLUMN "); + dquoted_print(toConsole, cname, " "); + mnstr_printf(toConsole, "SET STORAGE '%s';\n", stg); + free(stg); + } + rc = 0; /* success */ + bailout: + free(s); + free(t); + mapi_close_handle(hdl); /* may be NULL */ + free(sname); /* may be NULL */ + return rc; +} + int dump_table(Mapi mid, const char *schema, const char *tname, stream *toConsole, bool describe, bool foreign, bool useInserts, bool databaseDump, @@ -1863,6 +1959,8 @@ dump_table(Mapi mid, const char *schema, rc = describe_table(mid, schema, tname, toConsole, foreign, databaseDump); if (rc == 0 && !describe) rc = dump_table_data(mid, schema, tname, toConsole, useInserts, noescape); + if (rc == 0) + rc = dump_table_alters(mid, schema, tname, toConsole); return rc; } @@ -2389,16 +2487,7 @@ dump_database(Mapi mid, stream *toConsol "WHERE sch.id = seq.schema_id " "ORDER BY sch.name, seq.name"; const char *sequences2 = - "SELECT " - "sch, " - "seq, " - "rs, " - "rmi, " - "rma, " - "inc, " - "cycle " - "FROM sys.describe_sequences " - "ORDER BY sch, seq"; + "SELECT * FROM sys.describe_sequences ORDER BY sch, seq"; /* we must dump tables, views, functions/procedures and triggers in order of creation since they can refer to each other */ const char *tables_views_functions_triggers = "with vft (sname, name, id, query, remark, type) AS (" @@ -2862,19 +2951,31 @@ dump_database(Mapi mid, stream *toConsol goto bailout; _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list