Changeset: 692e1d90e121 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/692e1d90e121
Modified Files:
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
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/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
Branch: default
Log Message:
Approve upgrade.
diffs (294 lines):
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
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
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
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
diff --git
a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
@@ -4235,6 +4235,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
diff --git a/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
b/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
--- a/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
+++ b/sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
@@ -21,6 +21,38 @@ update sys.functions set system = true w
set schema "sys";
create view sys.malfunctions as select * from sys.malfunctions();
update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+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, 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 WHERE id > 2000
+ 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;
drop function sys.dump_database(boolean);
drop procedure sys.dump_table_data();
drop procedure sys._dump_table_data(string, string);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
