Changeset: 15c9517f19db for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=15c9517f19db
Modified Files:
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.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
Branch: Mar2018
Log Message:
Upgrade approvals.
diffs (truncated from 465 to 300 lines):
diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -35,9 +35,47 @@ delete from systemfunctions where functi
set schema "sys";
commit;
-# 15:50:22 >
-# 15:50:22 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-17773" "--port=32731"
-# 15:50:22 >
+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 "sys";
+commit;
+
+
+# 12:37:06 >
+# 12:37:06 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-5246" "--port=36929"
+# 12:37:06 >
#select 1;
% .L2 # table_name
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -35,9 +35,47 @@ delete from systemfunctions where functi
set schema "sys";
commit;
-# 15:55:37 >
-# 15:55:37 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-12365" "--port=35877"
-# 15:55:37 >
+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 "sys";
+commit;
+
+
+# 12:45:40 >
+# 12:45:40 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-30170" "--port=38749"
+# 12:45:40 >
#select 1;
% .L2 # table_name
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
@@ -35,9 +35,47 @@ delete from systemfunctions where functi
set schema "sys";
commit;
-# 16:02:52 >
-# 16:02:52 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-5391" "--port=32916"
-# 16:02:52 >
+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 "sys";
+commit;
+
+
+# 12:54:59 >
+# 12:54:59 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-3953" "--port=34683"
+# 12:54:59 >
#select 1;
% .L2 # table_name
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5363,6 +5363,43 @@ delete from systemfunctions where functi
set schema "sys";
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 "sys";
+commit;
+
# 16:53:35 >
# 16:53:35 > "mclient" "-lsql" "-ftest" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-30908" "--port=39660"
diff --git
a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
@@ -5363,6 +5363,43 @@ delete from systemfunctions where functi
set schema "sys";
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 "sys";
+commit;
+
# 15:43:25 >
# 15:43:25 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-5725" "--port=37647"
diff --git a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -35,9 +35,47 @@ delete from systemfunctions where functi
set schema "sys";
commit;
-# 15:50:23 >
-# 15:50:23 > "mclient" "-lsql" "-ftest" "-tnone" "-Eutf-8" "-i" "-e"
"--host=/var/tmp/mtest-17773" "--port=32731"
-# 15:50:23 >
+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');
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
