Changeset: 3cd4e1a6f6c8 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/3cd4e1a6f6c8
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/common/sql_types.c
sql/scripts/21_dependency_views.sql
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.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.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.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.int128
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.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.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.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.int128
Branch: default
Log Message:
Start id for built-in types at 4 instead of 1 since 1-3 are (also) users.
Also, we can now list all types in sys.ids since there is no duplicate
id anymore.
diffs (truncated from 1067 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
@@ -4377,6 +4377,11 @@ sql_update_default(Client c, mvc *sql, s
if (!sql_bind_func(sql, s->base.name, "vacuum", &tp, &tp, F_PROC, true,
true)) {
sql->session->status = 0; /* if the function was not found
clean the error */
sql->errstr[0] = '\0';
+ sql_table *t;
+ t = mvc_bind_table(sql, s, "ids");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dependencies_vw");
+ t->system = 0;
const char query[] =
"create procedure sys.vacuum(sname string, tname
string)\n"
"external name sql.vacuum;\n"
@@ -4391,10 +4396,42 @@ sql_update_default(Client c, mvc *sql, s
")\n"
"external name sql.unclosed_result_sets;\n"
"grant execute on function sys.unclosed_result_sets()
to public;\n"
- "update sys.functions set system = true where system <>
true and schema_id = 2000 and name in ('vacuum', 'stop_vacuum',
'unclosed_result_sets');\n";
- printf("Running database upgrade commands:\n%s\n",
query);
- fflush(stdout);
- err = SQLstatementIntern(c, query, "update", true,
false, NULL);
+ "update sys.functions set system = true where system <>
true and schema_id = 2000 and name in ('vacuum', 'stop_vacuum',
'unclosed_result_sets');\n"
+ "drop view sys.dependencies_vw cascade;\n"
+ "drop view sys.ids cascade;\n"
+ "CREATE VIEW sys.ids (id, name, schema_id, table_id,
table_name, obj_type, sys_table, system) 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, (name in
('public','sysadmin','monetdb','.snapshot')) AS system 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,
ifthenelse(system, 'system schema', 'schema'), 'sys.schemas', system FROM
sys.schemas UNION ALL\n"
+ "SELECT t.id, name, t.schema_id, t.id as table_id,
t.name as table_name, cast(lower(tt.table_type_name) as varchar(40)),
'sys.tables', t.system FROM sys.tables t left outer join sys.table_types tt on
t.type = tt.table_type_id UNION ALL\n"
+ "SELECT c.id, c.name, t.schema_id, c.table_id, t.name
as table_name, ifthenelse(t.system, 'system column', 'column'), 'sys._columns',
t.system 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', t.system 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, ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system
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', t.system 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, ifthenelse(t.system, 'system index', 'index'), 'sys.idxs',
t.system 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', t.system 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, ifthenelse(t.system, 'system trigger', 'trigger'),
'sys.triggers', t.system 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', t.system FROM tmp.triggers g JOIN
tmp._tables t ON g.table_id = t.id UNION ALL\n"
+ "SELECT f.id, f.name, f.schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, cast(ifthenelse(f.system,
'system ', '') || lower(ft.function_type_keyword) as varchar(40)),
'sys.functions', f.system FROM sys.functions f left outer join
sys.function_types ft on f.type = ft.function_type_id UNION ALL\n"
+ "SELECT a.id, a.name, f.schema_id, a.func_id as
table_id, f.name as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) || ' arg' as varchar(44)), 'sys.args', f.system
FROM sys.args a JOIN sys.functions f ON a.func_id = f.id left outer join
sys.function_types ft on f.type = ft.function_type_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', false FROM sys.sequences UNION ALL\n"
+ "SELECT o.id, o.name, pt.schema_id, pt.id, pt.name,
'partition of merge table', 'sys.objects', false 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',
(sqlname in ('inet','json','url','uuid')) FROM sys.types\n"
+ " ORDER BY id;\n"
+ "GRANT SELECT ON sys.ids TO PUBLIC;\n"
+ "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"
+ "update sys._tables set system = true where system <>
true and schema_id = 2000 and name in ('ids', 'dependencies_vw');\n";
+ printf("Running database upgrade commands:\n%s\n", query);
+ fflush(stdout);
+ err = SQLstatementIntern(c, query, "update", true, false, NULL);
}
return err;
diff --git a/sql/common/sql_types.c b/sql/common/sql_types.c
--- a/sql/common/sql_types.c
+++ b/sql/common/sql_types.c
@@ -746,7 +746,7 @@ sql_dup_subfunc(allocator *sa, sql_func
}
-static sqlid local_id = 1;
+static sqlid local_id;
static sql_type *
sql_create_type(allocator *sa, const char *sqlname, unsigned int digits,
unsigned int scale, unsigned char radix, sql_class eclass, const char *impl)
@@ -1712,7 +1712,7 @@ sqltypeinit( allocator *sa)
void
types_init(allocator *sa)
{
- local_id = 1;
+ local_id = 4; /* 1 to 3 are user id's */
types = sa_list(sa);
localtypes = sa_list(sa);
funcs = sa_list(sa);
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
@@ -29,7 +29,7 @@ SELECT f.id, f.name, f.schema_id, cast(n
SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types WHERE id > 2000 /* exclude system
types to prevent duplicates with auths.id */
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types', (sqlname in
('inet','json','url','uuid')) FROM sys.types
ORDER BY id;
/* do not include: SELECT id, 'object', name FROM sys.objects; as it has
duplicates with keys, columns, etc */
/* do not include: SELECT id, 'object', name FROM tmp.objects; as it has
duplicates with keys, columns, etc */
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -521,4 +521,36 @@ returns table(
external name sql.unclosed_result_sets;
grant execute on function sys.unclosed_result_sets() to public;
update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('vacuum', 'stop_vacuum', 'unclosed_result_sets');
+drop view sys.dependencies_vw cascade;
+drop view sys.ids cascade;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) 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, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system 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, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system 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', t.system 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,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system 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', t.system 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,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system 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', t.system 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,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
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', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types
+ 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 system <> true and schema_id = 2000
and name in ('ids', 'dependencies_vw');
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -504,4 +504,36 @@ returns table(
external name sql.unclosed_result_sets;
grant execute on function sys.unclosed_result_sets() to public;
update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('vacuum', 'stop_vacuum', 'unclosed_result_sets');
+drop view sys.dependencies_vw cascade;
+drop view sys.ids cascade;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) 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, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system 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, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system 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', t.system 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,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system 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', t.system 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,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system 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', t.system 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,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
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', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types
+ 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 system <> true and schema_id = 2000
and name in ('ids', 'dependencies_vw');
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -587,4 +587,36 @@ returns table(
external name sql.unclosed_result_sets;
grant execute on function sys.unclosed_result_sets() to public;
update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('vacuum', 'stop_vacuum', 'unclosed_result_sets');
+drop view sys.dependencies_vw cascade;
+drop view sys.ids cascade;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) 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, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system 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, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system 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', t.system 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,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system 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', t.system 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,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system 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', t.system 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,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
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', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types
+ 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 system <> true and schema_id = 2000
and name in ('ids', 'dependencies_vw');
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -521,4 +521,36 @@ returns table(
external name sql.unclosed_result_sets;
grant execute on function sys.unclosed_result_sets() to public;
update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('vacuum', 'stop_vacuum', 'unclosed_result_sets');
+drop view sys.dependencies_vw cascade;
+drop view sys.ids cascade;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) 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, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system 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, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system 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', t.system 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,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system 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', t.system 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,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system 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', t.system 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,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
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', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types
+ 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 system <> true and schema_id = 2000
and name in ('ids', 'dependencies_vw');
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -504,4 +504,36 @@ returns table(
external name sql.unclosed_result_sets;
grant execute on function sys.unclosed_result_sets() to public;
update sys.functions set system = true where system <> true and schema_id =
2000 and name in ('vacuum', 'stop_vacuum', 'unclosed_result_sets');
+drop view sys.dependencies_vw cascade;
+drop view sys.ids cascade;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) 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, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system 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, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system 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', t.system 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,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system 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', t.system 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,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system 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', t.system 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,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
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', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_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', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false 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', (sqlname in
('inet','json','url','uuid')) FROM sys.types
+ 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 system <> true and schema_id = 2000
and name in ('ids', 'dependencies_vw');
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -587,4 +587,36 @@ returns table(
external name sql.unclosed_result_sets;
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
