Changeset: 7ff275c4ad0a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/7ff275c4ad0a
Modified Files:
sql/backends/monet5/sql_upgrades.c
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/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: default
Log Message:
A little cleanup of the upgrade code.
Test whether we need to update the system tables by comparing a few
select functions in the internal administration and in the sys.functions
table. Also, no need to keep setting the schema to "sys".
diffs (truncated from 1760 to 300 lines):
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
@@ -26,7 +26,7 @@
* functions, args) when internal types and/or functions have changed
* (i.e. the ones in sql_types.c) */
static str
-sql_fix_system_tables(Client c, mvc *sql, const char *prev_schema)
+sql_fix_system_tables(Client c, mvc *sql)
{
size_t bufsize = 1000000, pos = 0;
char *buf = GDKmalloc(bufsize), *err = NULL;
@@ -37,7 +37,6 @@ sql_fix_system_tables(Client c, mvc *sql
if (buf == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
s = mvc_bind_schema(sql, "sys");
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
pos += snprintf(buf + pos, bufsize - pos,
"delete from sys.dependencies where id < 2000;\n");
@@ -137,8 +136,6 @@ sql_fix_system_tables(Client c, mvc *sql
}
}
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
-
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
@@ -146,23 +143,73 @@ sql_fix_system_tables(Client c, mvc *sql
return err; /* usually MAL_SUCCEED */
}
+static str
+check_sys_tables(Client c, mvc *m, sql_schema *s)
+{
+ struct {
+ const char *name;
+ const char *func;
+ const char *type;
+ sql_ftype ftype;
+ } tests[] = {
+ /* tests a few internal functions: the last one created, the
+ * first one created, and one of the first ones created after
+ * the geom module */
+ { "sys_update_tables", "update_tables", NULL, F_PROC, },
+ { "length", "nitems", "blob", F_FUNC, },
+ { "not_unique", "not_unique", "oid", F_AGGR, },
+ {0},
+ };
+
+ /* if any of the tested function's internal ID does not match the ID
+ * in the sys.functions table, we recreate the internal part of the
+ * system tables */
+ for (int i = 0; tests[i].name; i++) {
+ bool needsystabfix = true;
+ sql_subtype tp, *tpp;
+ if (tests[i].type) {
+ sql_find_subtype(&tp, tests[i].type, 0, 0);
+ tpp = &tp;
+ } else {
+ tpp = NULL;
+ }
+ sql_subfunc *f = sql_bind_func(m, s->base.name, tests[i].name,
tpp, NULL, tests[i].ftype);
+ if (f == NULL)
+ throw(SQL, __func__, "cannot find procedure
sys.%s(%s)", tests[i].name, tests[i].type ? tests[i].type : "");
+ sqlid id = f->func->base.id;
+ char buf[128];
+ snprintf(buf, sizeof(buf), "select id from sys.functions where
name = '%s' and func = '%s' and schema_id = 2000;\n", tests[i].name,
tests[i].func);
+ res_table *output = NULL;
+ char *err = SQLstatementIntern(c, buf, "update", true, false,
&output);
+ if (err)
+ return err;
+ BAT *b;
+ if ((b = BATdescriptor(output->cols[0].b)) != NULL) {
+ if (BATcount(b) > 0) {
+ BATiter bi = bat_iterator(b);
+ needsystabfix = * (int *) BUNtloc(bi, 0) != id;
+ bat_iterator_end(&bi);
+ }
+ BBPunfix(b->batCacheid);
+ }
+ res_table_destroy(output);
+ if (needsystabfix)
+ return sql_fix_system_tables(c, m);
+ }
+ return NULL;
+}
+
#ifdef HAVE_HGE
static str
-sql_update_hugeint(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+sql_update_hugeint(Client c, mvc *sql)
{
size_t bufsize = 8192, pos = 0;
char *buf, *err;
- if (!*systabfixed &&
- (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
- return err;
- *systabfixed = true;
-
+ (void) sql;
if ((buf = GDKmalloc(bufsize)) == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
-
/* 90_generator_hge.sql */
pos += snprintf(buf + pos, bufsize - pos,
"create function sys.generate_series(first hugeint,
\"limit\" hugeint)\n"
@@ -242,7 +289,6 @@ sql_update_hugeint(Client c, mvc *sql, c
"update sys.functions set system = true where system <>
true and name = 'filter' and schema_id = (select id from sys.schemas where name
= 'json') and type = %d;\n",
(int) F_UNION, (int) F_AGGR, (int) F_ANALYTIC, (int)
F_FUNC);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
@@ -253,7 +299,7 @@ sql_update_hugeint(Client c, mvc *sql, c
#endif
static str
-sql_drop_functions_dependencies_Xs_on_Ys(Client c, const char *prev_schema)
+sql_drop_functions_dependencies_Xs_on_Ys(Client c)
{
size_t bufsize = 1600, pos = 0;
char *err = NULL, *buf = GDKmalloc(bufsize);
@@ -263,7 +309,6 @@ sql_drop_functions_dependencies_Xs_on_Ys
/* remove functions which were created in
sql/scripts/21_dependency_functions.sql */
pos += snprintf(buf + pos, bufsize - pos,
- "set schema \"sys\";\n"
"DROP FUNCTION dependencies_schemas_on_users();\n"
"DROP FUNCTION dependencies_owners_on_schemas();\n"
"DROP FUNCTION dependencies_tables_on_views();\n"
@@ -282,7 +327,6 @@ sql_drop_functions_dependencies_Xs_on_Ys
"DROP FUNCTION dependencies_functions_on_triggers();\n"
"DROP FUNCTION dependencies_keys_on_foreignKeys();\n");
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
@@ -292,7 +336,7 @@ sql_drop_functions_dependencies_Xs_on_Ys
}
static str
-sql_update_storagemodel(Client c, mvc *sql, const char *prev_schema, bool
oct2020_upgrade)
+sql_update_storagemodel(Client c, mvc *sql, bool oct2020_upgrade)
{
size_t bufsize = 20000, pos = 0;
char *buf, *err;
@@ -315,7 +359,6 @@ sql_update_storagemodel(Client c, mvc *s
/* new 75_storagemodel.sql */
pos += snprintf(buf + pos, bufsize - pos,
- "set schema sys;\n"
/* drop objects in reverse order of original creation of old
75_storagemodel.sql */
"drop view if exists sys.tablestoragemodel;\n"
"drop view if exists sys.storagemodel cascade;\n"
@@ -628,7 +671,6 @@ sql_update_storagemodel(Client c, mvc *s
"update sys.functions set system = true where system <> true
and schema_id = (select id from sys.schemas where name = 'sys')"
" and name in ('columnsize', 'heapsize', 'hashsize',
'imprintsize') and type = %d;\n", (int) F_FUNC);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
@@ -794,40 +836,16 @@ bailout:
}
static str
-sql_update_nov2019(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+sql_update_nov2019(Client c, mvc *sql)
{
size_t bufsize = 16384, pos = 0;
char *err = NULL, *buf = GDKmalloc(bufsize);
- res_table *output;
- BAT *b;
-
+
+ (void) sql;
if (buf == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
pos += snprintf(buf + pos, bufsize - pos,
- "select id from sys.args where func_id in (select id
from sys.functions where schema_id = (select id from sys.schemas where name =
'sys') and name = 'second' and func = 'sql_seconds') and number = 0 and
type_scale = 3;\n");
- err = SQLstatementIntern(c, buf, "update", 1, 0, &output);
- if (err) {
- GDKfree(buf);
- return err;
- }
- b = BATdescriptor(output->cols[0].b);
- if (b) {
- if (BATcount(b) > 0 && !*systabfixed) {
- err = sql_fix_system_tables(c, sql, prev_schema);
- *systabfixed = true;
- }
- BBPunfix(b->batCacheid);
- }
- res_table_destroy(output);
- if (err) {
- GDKfree(buf);
- return err;
- }
-
- pos = 0;
- pos += snprintf(buf + pos, bufsize - pos,
- "set schema \"sys\";\n"
"create function sys.deltas (\"schema\" string)"
" returns table (\"id\" int, \"cleared\" boolean,
\"immutable\" bigint, \"inserted\" bigint, \"updates\" bigint, \"deletes\"
bigint, \"level\" int)"
" external name \"sql\".\"deltas\";\n"
@@ -1031,7 +1049,6 @@ sql_update_nov2019(Client c, mvc *sql, c
"update sys.args set type = 'ptr' where"
" func_id = (select id from sys.functions where name =
'copyfrom' and func = 'copy_from' and mod = 'sql' and type = %d) and name =
'arg_1';\n", (int) F_UNION);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
@@ -1042,21 +1059,15 @@ sql_update_nov2019(Client c, mvc *sql, c
#ifdef HAVE_HGE
static str
-sql_update_nov2019_sp1_hugeint(Client c, mvc *sql, const char *prev_schema,
bool *systabfixed)
+sql_update_nov2019_sp1_hugeint(Client c, mvc *sql)
{
size_t bufsize = 1024, pos = 0;
char *buf, *err;
- if (!*systabfixed &&
- (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
- return err;
- *systabfixed = true;
-
+ (void) sql;
if ((buf = GDKmalloc(bufsize)) == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"sys\";\n");
-
/* 39_analytics_hge.sql */
pos += snprintf(buf + pos, bufsize - pos,
"create aggregate median_avg(val HUGEINT) returns
DOUBLE\n"
@@ -1069,7 +1080,6 @@ sql_update_nov2019_sp1_hugeint(Client c,
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true where system <>
true and name in ('median_avg', 'quantile_avg') and schema_id = (select id from
sys.schemas where name = 'sys') and type = %d;\n", (int) F_AGGR);
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
@@ -1080,24 +1090,16 @@ sql_update_nov2019_sp1_hugeint(Client c,
#endif
static str
-sql_update_jun2020(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+sql_update_jun2020(Client c, mvc *sql)
{
sql_table *t;
size_t bufsize = 32768, pos = 0;
char *err = NULL, *buf = NULL;
sql_schema *sys = mvc_bind_schema(sql, "sys");
- if (!*systabfixed &&
- (err = sql_fix_system_tables(c, sql, prev_schema)) != NULL)
- return err;
- *systabfixed = true;
-
if ((buf = GDKmalloc(bufsize)) == NULL)
throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
- pos += snprintf(buf + pos, bufsize - pos,
- "set schema \"sys\";\n");
-
/* convert old PYTHON2 and PYTHON2_MAP to PYTHON and PYTHON_MAP
* see also function load_func() in store.c */
pos += snprintf(buf + pos, bufsize - pos,
@@ -1653,16 +1655,14 @@ sql_update_jun2020(Client c, mvc *sql, c
(int) F_PROC, (int) F_UNION);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
if (err == MAL_SUCCEED) {
- pos = snprintf(buf, bufsize, "set schema \"sys\";\n"
+ pos = snprintf(buf, bufsize,
"ALTER TABLE sys.keywords SET READ ONLY;\n"
"ALTER TABLE sys.function_languages SET READ
ONLY;\n");
- pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
@@ -1672,7 +1672,7 @@ sql_update_jun2020(Client c, mvc *sql, c
}
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
