Changeset: 2af550df201d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2af550df201d
Modified Files:
clients/mapiclient/dump.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Jan2022
Log Message:
Extend view describe_sequences to the right instead of the middle; fixed
upgrade.
Also, keep msqldump compatible with older versions.
Note, try to avoid changing upgrade code for older releases.
diffs (truncated from 4139 to 300 lines):
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);
@@ -2389,16 +2398,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 +2862,31 @@ dump_database(Mapi mid, stream *toConsol
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 *restart = 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 *schema = mapi_fetch_field(hdl, 0);
/* sch */
+ const char *name = mapi_fetch_field(hdl, 1);
/* seq */
+ const char *restart = mapi_fetch_field(hdl, 3);
/* rs */
+ const char *minvalue;
+ const char *maxvalue;
+ const char *increment = mapi_fetch_field(hdl, 6);
/* inc */
+ const char *cycle = mapi_fetch_field(hdl, 8);
/* cycle */
+
+ if (mapi_get_field_count(hdl) > 9) {
+ /* new version (Jan2022) of
sys.describe_sequences */
+ minvalue = mapi_fetch_field(hdl, 11);
/* rmi */
+ maxvalue = mapi_fetch_field(hdl, 12);
/* rma */
+ } else {
+ /* old version (pre Jan2022) of
sys.describe_sequences */
+ minvalue = mapi_fetch_field(hdl, 4);
/* minvalue */
+ maxvalue = mapi_fetch_field(hdl, 5);
/* maxvalue */
+ if (strcmp(minvalue, "0") == 0)
+ minvalue = NULL;
+ if (strcmp(maxvalue, "0") == 0)
+ maxvalue = NULL;
+ }
if (sname != NULL && strcmp(schema, sname) != 0)
continue;
- // sleep(7);
-
mnstr_printf(toConsole,
"ALTER SEQUENCE ");
dquoted_print(toConsole, schema, ".");
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -2774,27 +2774,7 @@ sql_update_jul2021(Client c, mvc *sql, c
" s.name as sch,\n"
" seq.name as seq,\n"
" seq.\"start\" s,\n"
- " sys.get_value_for(s.name,
seq.name) AS rs,\n"
- " CASE WHEN seq.\"minvalue\" =
-9223372036854775807 AND seq.\"increment\" > 0 AND seq.\"start\" = 1 THEN TRUE
ELSE FALSE END nomin,\n"
- " CASE WHEN seq.\"maxvalue\" =
9223372036854775807 AND seq.\"increment\" < 0 AND seq.\"start\" = -1 THEN TRUE
ELSE FALSE END nomax,\n"
- " CASE\n"
- " WHEN seq.\"minvalue\"
= 0 AND seq.\"increment\" > 0 THEN NULL\n"
- " WHEN seq.\"minvalue\" <>
-9223372036854775807 THEN seq.\"minvalue\"\n"
- " ELSE\n"
- " CASE\n"
- " WHEN
seq.\"increment\" < 0 THEN NULL\n"
- " ELSE CASE WHEN
seq.\"start\" = 1 THEN NULL ELSE seq.\"minvalue\" END\n"
- " END\n"
- " END rmi,\n"
- " CASE \n"
- " WHEN seq.\"maxvalue\" = 0
AND seq.\"increment\" < 0 THEN NULL\n"
- " WHEN seq.\"maxvalue\" <>
9223372036854775807 THEN seq.\"maxvalue\"\n"
- " ELSE\n"
- " CASE\n"
- " WHEN
seq.\"increment\" > 0 THEN NULL\n"
- " ELSE CASE WHEN
seq.\"start\" = -1 THEN NULL ELSE seq.\"maxvalue\" END\n"
- " END\n"
- " END rma,\n"
+ " get_value_for(s.name,
seq.name) AS rs,\n"
" seq.\"minvalue\" mi,\n"
" seq.\"maxvalue\" ma,\n"
" seq.\"increment\" inc,\n"
@@ -3450,53 +3430,55 @@ sql_update_jan2022(Client c, mvc *sql, c
/* 52_describe.sql; but we need to drop most everything from
* 76_dump.sql first */
- t = mvc_bind_table(sql, s, "dump_privileges");
+ t = mvc_bind_table(sql, s, "describe_comments");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_constraints");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_functions");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_partition_tables");
t->system = 0;
- t = mvc_bind_table(sql, s, "dump_user_defined_types");
+ t = mvc_bind_table(sql, s, "describe_privileges");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_sequences");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_add_schemas_to_users");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_column_defaults");
t->system = 0;
t = mvc_bind_table(sql, s, "dump_comments");
t->system = 0;
- t = mvc_bind_table(sql, s, "dump_triggers");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_tables");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_functions");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_start_sequences");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_sequences");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_partition_tables");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_foreign_keys");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_column_defaults");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_indices");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_table_constraint_type");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_grant_user_privileges");
- t->system = 0;
- t = mvc_bind_table(sql, s, "dump_add_schemas_to_users");
+ t = mvc_bind_table(sql, s, "dump_create_roles");
t->system = 0;
t = mvc_bind_table(sql, s, "dump_create_schemas");
t->system = 0;
t = mvc_bind_table(sql, s, "dump_create_users");
t->system = 0;
- t = mvc_bind_table(sql, s, "dump_create_roles");
+ t = mvc_bind_table(sql, s, "dump_foreign_keys");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_functions");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_grant_user_privileges");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_constraints");
+ t = mvc_bind_table(sql, s, "dump_indices");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_tables");
+ t = mvc_bind_table(sql, s, "dump_partition_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_privileges");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_comments");
+ t = mvc_bind_table(sql, s, "dump_sequences");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_privileges");
+ t = mvc_bind_table(sql, s, "dump_start_sequences");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_table_constraint_type");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_partition_tables");
+ t = mvc_bind_table(sql, s, "dump_tables");
t->system = 0;
- t = mvc_bind_table(sql, s, "describe_functions");
+ t = mvc_bind_table(sql, s, "dump_triggers");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_user_defined_types");
t->system = 0;
pos += snprintf(buf + pos, bufsize - pos,
/* drop dependant stuff from
76_dump.sql */
@@ -3773,6 +3755,11 @@ sql_update_jan2022(Client c, mvc *sql, c
" seq.name seq,\n"
" seq.\"start\" s,\n"
"
peak_next_value_for(s.name, seq.name) rs,\n"
+ " seq.\"minvalue\" mi,\n"
+ " seq.\"maxvalue\" ma,\n"
+ " seq.\"increment\"
inc,\n"
+ " seq.\"cacheinc\"
cache,\n"
+ " seq.\"cycle\" cycle,\n"
" CASE WHEN
seq.\"minvalue\" = -9223372036854775807 AND seq.\"increment\" > 0 AND
seq.\"start\" = 1 THEN TRUE ELSE FALSE END nomin,\n"
" CASE WHEN
seq.\"maxvalue\" = 9223372036854775807 AND seq.\"increment\" < 0 AND
seq.\"start\" = -1 THEN TRUE ELSE FALSE END nomax,\n"
" CASE\n"
@@ -3792,12 +3779,7 @@ sql_update_jan2022(Client c, mvc *sql, c
"
WHEN seq.\"increment\" > 0 THEN NULL\n"
"
ELSE CASE WHEN seq.\"start\" = -1 THEN NULL ELSE seq.\"maxvalue\" END\n"
" END\n"
- " END rma,\n"
- " seq.\"minvalue\" mi,\n"
- " seq.\"maxvalue\" ma,\n"
- " seq.\"increment\"
inc,\n"
- " seq.\"cacheinc\"
cache,\n"
- " seq.\"cycle\" cycle\n"
+ " END rma\n"
" FROM sys.sequences seq,
sys.schemas s\n"
" WHERE s.id = seq.schema_id\n"
" AND s.name <> 'tmp'\n"
@@ -3818,7 +3800,7 @@ sql_update_jan2022(Client c, mvc *sql, c
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true
where system <> true and name in ('sq', 'fqn',
'get_merge_table_partition_expressions', 'get_remote_table_expressions',
'schema_guard') and schema_id = 2000 and type = %d;\n", F_FUNC);
pos += snprintf(buf + pos, bufsize - pos,
- "update sys._tables set system = true where
name in ('describe_constraints', 'describe_tables', 'describe_comments',
'describe_privileges', 'describe_partition_tables', 'describe_functions') AND
schema_id = 2000;\n");
+ "update sys._tables set system = true where
name in ('describe_constraints', 'describe_tables', 'describe_comments',
'describe_privileges', 'describe_partition_tables', 'describe_sequences',
'describe_functions') AND schema_id = 2000;\n");
/* 76_dump.sql (most everything already dropped) */
pos += snprintf(buf + pos, bufsize - pos,
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
