Changeset: 50c69056b970 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/50c69056b970
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/21_dependency_views.sql
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-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: Jul2021
Log Message:
Update sys.ids view to incorporate rows for merge table partitions.
diffs (truncated from 1359 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
@@ -3390,6 +3390,89 @@ bailout:
return err; /* usually MAL_SUCCEED */
}
+/* upgrades after Jul2021_3 build */
+static str
+sql_update_jul2021_5(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+{
+ size_t bufsize = 65536, pos = 0;
+ char *buf = NULL, *err = NULL;
+ res_table *output = NULL;
+ sql_schema *s = mvc_bind_schema(sql, "sys");
+ sql_table *t;
+
+ (void) systabfixed;
+
+ if ((buf = GDKmalloc(bufsize)) == NULL)
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ /* if the string 'partition of merge table' is not in the sys.ids
+ * query, upgrade */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "select query from sys._tables where
name = 'ids' and schema_id = 2000 and query like '%%partition of merge
table%%';\n");
+ assert(pos < bufsize);
+ if ((err = SQLstatementIntern(c, buf, "update", true, false, &output))
== NULL) {
+ BAT *b;
+ if ((b = BATdescriptor(output->cols[0].b))) {
+ if (BATcount(b) == 0) {
+ pos = snprintf(buf, bufsize, "set schema
\"sys\";\n");
+
+ /* 21_dependency_views.sql */
+ t = mvc_bind_table(sql, s, "ids");
+ t->system = 0; /* make it non-system else the
drop view will fail */
+ t = mvc_bind_table(sql, s, "dependencies_vw");
+ t->system = 0; /* make it non-system else the
drop view will fail */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop view
sys.dependencies_vw;\n"
+ "drop view
sys.ids;\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE VIEW
sys.ids (id, name, schema_id, table_id, table_name, obj_type, sys_table) AS\n"
+ "SELECT id,
name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null
as varchar(124)) as table_name, 'author' AS obj_type, 'sys.auths' AS sys_table
FROM sys.auths UNION ALL\n"
+ "SELECT id,
name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null
as varchar(124)) as table_name, 'schema', 'sys.schemas' FROM sys.schemas UNION
ALL\n"
+ "SELECT id,
name, schema_id, id as table_id, name as table_name, case when type = 1 then
'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL\n"
+ "SELECT id,
name, schema_id, id as table_id, name as table_name, case when type = 1 then
'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL\n"
+ "SELECT c.id,
c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'sys._columns'
FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id UNION ALL\n"
+ "SELECT c.id,
c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'tmp._columns'
FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id UNION ALL\n"
+ "SELECT k.id,
k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'sys.keys' FROM
sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL\n"
+ "SELECT k.id,
k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'tmp.keys' FROM
tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL\n"
+ "SELECT i.id,
i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'sys.idxs' FROM
sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL\n"
+ "SELECT i.id,
i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'tmp.idxs' FROM
tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL\n"
+ "SELECT g.id,
g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id
UNION ALL\n"
+ "SELECT g.id,
g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id
UNION ALL\n"
+ "SELECT id,
name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as
table_name, case when type = 2 then 'procedure' else 'function' end,
'sys.functions' FROM sys.functions UNION ALL\n"
+ "SELECT a.id,
a.name, f.schema_id, cast(null as int) as table_id, cast(null as varchar(124))
as table_name, case when f.type = 2 then 'procedure arg' else 'function arg'
end, 'sys.args' FROM sys.args a JOIN sys.functions f ON a.func_id = f.id UNION
ALL\n"
+ "SELECT id,
name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as
table_name, 'sequence', 'sys.sequences' FROM sys.sequences UNION ALL\n"
+ "SELECT o.id,
o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table', 'sys.objects'
FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN sys._tables mt ON
o.nr = mt.id WHERE mt.type = 3 UNION ALL\n"
+ "SELECT id,
sqlname, schema_id, cast(null as int) as table_id, cast(null as varchar(124))
as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000 /* exclude
system types to prevent duplicates with auths.id */\n"
+ " ORDER BY
id;\n"
+ "GRANT SELECT
ON sys.ids TO PUBLIC;\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE VIEW
sys.dependencies_vw AS\n"
+ "SELECT d.id,
i1.obj_type, i1.name,\n"
+ "
d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,\n"
+ "
d.depend_type, dt.dependency_type_name\n"
+ " FROM
sys.dependencies d\n"
+ " JOIN sys.ids
i1 ON d.id = i1.id\n"
+ " JOIN sys.ids
i2 ON d.depend_id = i2.id\n"
+ " JOIN
sys.dependency_types dt ON d.depend_type = dt.dependency_type_id\n"
+ " ORDER BY id,
depend_id;\n"
+ "GRANT SELECT
ON sys.dependencies_vw TO PUBLIC;\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "UPDATE
sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw') AND
schema_id = 2000;\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);
+ }
+ BBPunfix(b->batCacheid);
+ }
+ res_table_destroy(output);
+ }
+
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -3634,6 +3717,13 @@ SQLupgrades(Client c, mvc *m)
return -1;
}
+ if ((err = sql_update_jul2021_5(c, m, prev_schema, &systabfixed)) !=
NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ freeException(err);
+ GDKfree(prev_schema);
+ return -1;
+ }
+
GDKfree(prev_schema);
return 0;
}
diff --git a/sql/scripts/21_dependency_views.sql
b/sql/scripts/21_dependency_views.sql
--- a/sql/scripts/21_dependency_views.sql
+++ b/sql/scripts/21_dependency_views.sql
@@ -25,6 +25,7 @@ SELECT g.id, g.name, t.schema_id, g.tabl
SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when type = 2 then 'procedure' else
'function' end, 'sys.functions' FROM sys.functions UNION ALL
SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON
a.func_id = f.id UNION ALL
SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences
UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN
sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000
/* exclude system types to prevent duplicates with auths.id */
ORDER BY id;
/* do not include: SELECT id, 'object', name FROM sys.objects; as it has
duplicates with keys, columns, etc */
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -5077,3 +5077,40 @@ ALTER TABLE sys.table_types SET READ ONL
ALTER TABLE sys.function_types SET READ ONLY;
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.dependencies_vw;
+drop view sys.ids;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table) AS
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type,
'sys.auths' AS sys_table FROM sys.auths UNION ALL
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas'
FROM sys.schemas UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id
UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id
UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id
UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when type = 2 then 'procedure' else
'function' end, 'sys.functions' FROM sys.functions UNION ALL
+SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON
a.func_id = f.id UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences
UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN
sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000
/* exclude system types to prevent duplicates with auths.id */
+ ORDER BY id;
+GRANT SELECT ON sys.ids TO PUBLIC;
+CREATE VIEW sys.dependencies_vw AS
+SELECT d.id, i1.obj_type, i1.name,
+ d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,
+ d.depend_type, dt.dependency_type_name
+ FROM sys.dependencies d
+ JOIN sys.ids i1 ON d.id = i1.id
+ JOIN sys.ids i2 ON d.depend_id = i2.id
+ JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id
+ ORDER BY id, depend_id;
+GRANT SELECT ON sys.dependencies_vw TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
+set schema "sys";
+
diff --git
a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
@@ -5077,3 +5077,40 @@ ALTER TABLE sys.table_types SET READ ONL
ALTER TABLE sys.function_types SET READ ONLY;
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.dependencies_vw;
+drop view sys.ids;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table) AS
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type,
'sys.auths' AS sys_table FROM sys.auths UNION ALL
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas'
FROM sys.schemas UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id
UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id
UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id
UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when type = 2 then 'procedure' else
'function' end, 'sys.functions' FROM sys.functions UNION ALL
+SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON
a.func_id = f.id UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences
UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN
sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000
/* exclude system types to prevent duplicates with auths.id */
+ ORDER BY id;
+GRANT SELECT ON sys.ids TO PUBLIC;
+CREATE VIEW sys.dependencies_vw AS
+SELECT d.id, i1.obj_type, i1.name,
+ d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,
+ d.depend_type, dt.dependency_type_name
+ FROM sys.dependencies d
+ JOIN sys.ids i1 ON d.id = i1.id
+ JOIN sys.ids i2 ON d.depend_id = i2.id
+ JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id
+ ORDER BY id, depend_id;
+GRANT SELECT ON sys.dependencies_vw TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
+set schema "sys";
+
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -4482,3 +4482,40 @@ ALTER TABLE sys.table_types SET READ ONL
ALTER TABLE sys.function_types SET READ ONLY;
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.dependencies_vw;
+drop view sys.ids;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table) AS
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type,
'sys.auths' AS sys_table FROM sys.auths UNION ALL
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas'
FROM sys.schemas UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id
UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id
UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id
UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when type = 2 then 'procedure' else
'function' end, 'sys.functions' FROM sys.functions UNION ALL
+SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON
a.func_id = f.id UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences
UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN
sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000
/* exclude system types to prevent duplicates with auths.id */
+ ORDER BY id;
+GRANT SELECT ON sys.ids TO PUBLIC;
+CREATE VIEW sys.dependencies_vw AS
+SELECT d.id, i1.obj_type, i1.name,
+ d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,
+ d.depend_type, dt.dependency_type_name
+ FROM sys.dependencies d
+ JOIN sys.ids i1 ON d.id = i1.id
+ JOIN sys.ids i2 ON d.depend_id = i2.id
+ JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id
+ ORDER BY id, depend_id;
+GRANT SELECT ON sys.dependencies_vw TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
+set schema "sys";
+
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -4482,3 +4482,40 @@ ALTER TABLE sys.table_types SET READ ONL
ALTER TABLE sys.function_types SET READ ONLY;
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+drop view sys.dependencies_vw;
+drop view sys.ids;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table) AS
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type,
'sys.auths' AS sys_table FROM sys.auths UNION ALL
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas'
FROM sys.schemas UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL
+SELECT id, name, schema_id, id as table_id, name as table_name, case when type
= 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id
UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index',
'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id
UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id
UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when type = 2 then 'procedure' else
'function' end, 'sys.functions' FROM sys.functions UNION ALL
+SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else
'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON
a.func_id = f.id UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences
UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN
sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000
/* exclude system types to prevent duplicates with auths.id */
+ ORDER BY id;
+GRANT SELECT ON sys.ids TO PUBLIC;
+CREATE VIEW sys.dependencies_vw AS
+SELECT d.id, i1.obj_type, i1.name,
+ d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,
+ d.depend_type, dt.dependency_type_name
+ FROM sys.dependencies d
+ JOIN sys.ids i1 ON d.id = i1.id
+ JOIN sys.ids i2 ON d.depend_id = i2.id
+ JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id
+ ORDER BY id, depend_id;
+GRANT SELECT ON sys.dependencies_vw TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
+set schema "sys";
+
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5151,3 +5151,40 @@ ALTER TABLE sys.table_types SET READ ONL
ALTER TABLE sys.function_types SET READ ONLY;
set schema "sys";
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
