Changeset: 364ec372f783 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=364ec372f783
Modified Files:
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: Mar2018
Log Message:
Also added upgrade output to the
sql/test/testdb-upgrade*/Tests/upgrade.stable.out* files
Note: I couldn't test it locally but I think this will become the upgrade
output. Have to wait on the testweb to see if it needs some small finetuning.
diffs (truncated from 374 to 300 lines):
diff --git a/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -33,6 +33,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
+
# 15:51:04 >
# 15:51:04 > "/usr/bin/python" "upgrade.SQL.py" "upgrade"
# 15:51:04 >
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
@@ -30,6 +30,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
+
# 15:56:11 >
# 15:56:11 > "/usr/bin/python" "upgrade.SQL.py" "upgrade"
# 15:56:11 >
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
@@ -30,6 +30,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
+
# 16:03:29 >
# 16:03:29 > "/usr/bin/python" "upgrade.SQL.py" "upgrade"
# 16:03:29 >
diff --git a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5358,6 +5358,42 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
# 12:48:11 >
# 12:48:11 > "/usr/bin/python2" "upgrade.SQL.py" "upgrade"
diff --git a/sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -35,6 +35,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
+
# 15:51:04 >
# 15:51:04 > "/usr/bin/python" "upgrade.SQL.py" "upgrade"
# 15:51:04 >
diff --git a/sql/test/testdb-upgrade/Tests/upgrade.stable.out
b/sql/test/testdb-upgrade/Tests/upgrade.stable.out
--- a/sql/test/testdb-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/testdb-upgrade/Tests/upgrade.stable.out
@@ -30,6 +30,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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 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 in (select id from sys.schemas where name = 'sys');
+set schema "testschema";
+commit;
+
# 15:56:11 >
# 15:56:11 > "/usr/bin/python" "upgrade.SQL.py" "upgrade"
# 15:56:11 >
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
@@ -30,6 +30,43 @@ delete from systemfunctions where functi
set schema "testschema";
commit;
+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
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
