Changeset: 947934479dc1 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/947934479dc1 Added Files: sql/scripts/10_sys_schema_extension.sql Removed Files: sql/scripts/51_sys_schema_extension.sql Modified Files: sql/ChangeLog sql/backends/monet5/CMakeLists.txt sql/backends/monet5/sql_upgrades.c sql/scripts/21_dependency_views.sql sql/scripts/CMakeLists.txt sql/test/Dependencies/Tests/dependency_owner_schema_3.test 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 sql/test/emptydb/Tests/check.stable.out.int128 sql/test/sys-schema/Tests/check_ForeignKey_referential_integrity.test 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:
Add some more information to sys.ids view. Whether tables/views are system tables/views, and the "owner" of function arguments. diffs (truncated from 1054 to 300 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -1,6 +1,12 @@ # ChangeLog file for sql # This file is updated with Maddlog +* Tue Sep 7 2021 Sjoerd Mullender <[email protected]> +- The view sys.ids has been changed to give more information about the + objects in the system. In particular, there is an extra column + added at the end that indicates whether the object is a system + object. + * Mon Aug 23 2021 Sjoerd Mullender <[email protected]> - The example modules opt_sql_append and udf are no longer loaded by default and no longer part of the binary release. If installed, diff --git a/sql/backends/monet5/CMakeLists.txt b/sql/backends/monet5/CMakeLists.txt --- a/sql/backends/monet5/CMakeLists.txt +++ b/sql/backends/monet5/CMakeLists.txt @@ -14,6 +14,7 @@ set(sql_public_headers add_library(sql SHARED) set(include_sql_files + 10_sys_schema_extension 12_url 13_date 14_inet @@ -32,7 +33,6 @@ set(include_sql_files 42_md5sum 45_uuid 46_profiler - 51_sys_schema_extension 52_describe 58_hot_snapshot 60_wlcr 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 @@ -3531,6 +3531,47 @@ sql_update_default(Client c, mvc *sql, c "update sys._tables set system = true where system <> true and schema_id = 2000" " and name = 'malfunctions';\n"); + /* 21_dependency_views.sql */ + t = mvc_bind_table(sql, s, "ids"); + t->system = 0; /* make it non-system else the drop view will fail */ + t = mvc_bind_table(sql, s, "dependencies_vw"); + t->system = 0; /* make it non-system else the drop view will fail */ + pos += snprintf(buf + pos, bufsize - pos, + "drop view sys.dependencies_vw;\n" /* depends on sys.ids */ + "drop view sys.ids;\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 WHERE id > 2000\n" + " ORDER BY id;\n" + "GRANT SELECT ON sys.ids TO PUBLIC;\n"); + pos += snprintf(buf + pos, bufsize - pos, + "CREATE VIEW sys.dependencies_vw AS\n" + "SELECT d.id, i1.obj_type, i1.name,\n" + " d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as used_by_name,\n" + " d.depend_type, dt.dependency_type_name\n" + " FROM sys.dependencies d\n" + " JOIN sys.ids i1 ON d.id = i1.id\n" + " JOIN sys.ids i2 ON d.depend_id = i2.id\n" + " JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id\n" + " ORDER BY id, depend_id;\n" + "GRANT SELECT ON sys.dependencies_vw TO PUBLIC;\n"); + pos += snprintf(buf + pos, bufsize - pos, + "UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw') AND schema_id = 2000;\n"); + /* 52_describe.sql; but we need to drop most everything from * 76_dump.sql first */ t = mvc_bind_table(sql, s, "dump_privileges"); diff --git a/sql/scripts/51_sys_schema_extension.sql b/sql/scripts/10_sys_schema_extension.sql rename from sql/scripts/51_sys_schema_extension.sql rename to sql/scripts/10_sys_schema_extension.sql 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 @@ -9,24 +9,23 @@ -- Utility view to combine and list all potential referenced object ids, their name, schema_id (if applicable), -- table_id (if applicable), table_name (if applicable), obj_type and system table name (which can be used to query the details of the object) -CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type, sys_table) AS -SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type, 'sys.auths' AS sys_table FROM sys.auths UNION ALL -SELECT id, name, cast(null as int) as schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'schema', 'sys.schemas' FROM sys.schemas UNION ALL -SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'sys._tables' FROM sys._tables UNION ALL -SELECT id, name, schema_id, id as table_id, name as table_name, case when type = 1 then 'view' else 'table' end, 'tmp._tables' FROM tmp._tables UNION ALL -SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'sys._columns' FROM sys._columns c JOIN sys._tables t ON c.table_id = t.id UNION ALL -SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column', 'tmp._columns' FROM tmp._columns c JOIN tmp._tables t ON c.table_id = t.id UNION ALL -SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'sys.keys' FROM sys.keys k JOIN sys._tables t ON k.table_id = t.id UNION ALL -SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key', 'tmp.keys' FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id UNION ALL -SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'sys.idxs' FROM sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL -SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index', 'tmp.idxs' FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id UNION ALL -SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'sys.triggers' FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id UNION ALL -SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger', 'tmp.triggers' FROM tmp.triggers g JOIN tmp._tables t ON g.table_id = t.id UNION ALL -SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when type = 2 then 'procedure' else 'function' end, 'sys.functions' FROM sys.functions UNION ALL -SELECT a.id, a.name, f.schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, case when f.type = 2 then 'procedure arg' else 'function arg' end, 'sys.args' FROM sys.args a JOIN sys.functions f ON a.func_id = f.id UNION ALL -SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'sequence', 'sys.sequences' FROM sys.sequences UNION ALL -SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table', 'sys.objects' FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id JOIN sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL -SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as varchar(124)) as table_name, 'type', 'sys.types' FROM sys.types WHERE id > 2000 /* exclude system types to prevent duplicates with auths.id */ +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 /* exclude system types to prevent duplicates with auths.id */ ORDER BY id; /* do not include: SELECT id, 'object', name FROM sys.objects; as it has duplicates with keys, columns, etc */ /* do not include: SELECT id, 'object', name FROM tmp.objects; as it has duplicates with keys, columns, etc */ diff --git a/sql/scripts/CMakeLists.txt b/sql/scripts/CMakeLists.txt --- a/sql/scripts/CMakeLists.txt +++ b/sql/scripts/CMakeLists.txt @@ -8,6 +8,7 @@ #[[ install(FILES + 10_sys_schema_extension.sql 12_url.sql 13_date.sql 14_inet.sql @@ -26,7 +27,6 @@ install(FILES 42_md5sum.sql 45_uuid.sql 46_profiler.sql - 51_sys_schema_extension.sql 52_describe.sql 58_hot_snapshot.sql 60_wlcr.sql diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test --- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test +++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test @@ -48,7 +48,7 @@ DEP_SCHEMA query TTT rowsort SELECT t.name, v.name, 'DEP_VIEW' from sys.tables as t, sys.tables as v, sys.dependencies as dep where t.id = dep.id AND v.id = dep.depend_id AND dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by t.name, v.name ---- -627 values hashing to ba31ce2ed151ea27b635fb7be420a269 +636 values hashing to 90e2ee5bb64e32ae6e9d2ac0ab8dae7b query TTT rowsort SELECT t.name, i.name, 'DEP_INDEX' from sys.tables as t, sys.idxs as i where i.table_id = t.id and i.name not in (select name from sys.keys) and t.type in (0, 10, 20, 30) order by t.name, i.name @@ -126,7 +126,7 @@ DEP_FUNC query TTT rowsort SELECT c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables as v, sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name ---- -2181 values hashing to 41189eb0b528b8ed3c6ab64910056631 +2217 values hashing to 419c161985866d250f6e16deb56ab344 query TTT rowsort SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc, sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id = c.table_id AND k.rkey = -1 order by c.name, k.name 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 @@ -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 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 @@ -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.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 @@ -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); _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
