Changeset: 6734481fe839 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6734481fe839
Added Files:
sql/scripts/21_dependency_views.sql
Modified Files:
sql/ChangeLog
sql/backends/monet5/sql_upgrades.c
sql/scripts/21_dependency_functions.sql
sql/scripts/51_sys_schema_extension.sql
sql/scripts/Makefile.ag
sql/test/BugTracker-2016/Tests/storagemodel.stable.out
sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_in_select.Bug-6254.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out.int128
sql/test/Dependencies/Tests/dependency_loop.stable.out
sql/test/Dependencies/Tests/dependency_loop.stable.out.int128
sql/test/DropCascade/Tests/drop_columns_cascade.stable.out
sql/test/DropCascade/Tests/drop_pkey_cascade.stable.out
sql/test/Tests/keys.stable.out
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.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: sqlextra
Log Message:
Merge with default branch.
diffs (truncated from 7119 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,6 +1,20 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Thu Feb 1 2018 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Added new system view: sys.ids which contains all database objects
+ ids which can be used in sys.dependencies table.
+- Added new system view: sys.dependencies_vw which shows all data of
+ sys.dependencies including names on objects, object types and dependency
types.
+- Added new system views for finding out dependencies between database objects.
+ These new dependency views improve, extend and replace the
+ sys.dependencies_X_on_Y() functions as previously defined in
+ 21_dependency_functions.sql.
+ The sys.dependencies_X_on_Y() functions are now marked as deprecated.
+- Added new system view: sys.roles which contains all defined roles.
+- Added new system view: sys.var_values which shows the values for
+ system variables.
+
* Thu Dec 7 2017 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
- Removed system function sys.environment(). It was a duplicate of system
function sys.env().
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
@@ -1185,7 +1185,6 @@ sql_update_default(Client c, mvc *sql)
"-- ALTER TABLE sys.keywords SET READ ONLY;\n"
/* TODO fix. Last ALTER TABLE is disabled as it produces:
!SQLException:sql.alter_table_set_access:40000!ALTER TABLE: set READ or INSERT
ONLY not possible with outstanding updates (wait until updates are flushed) */
);
-
pos += snprintf(buf + pos, bufsize - pos,
"delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
@@ -1199,6 +1198,335 @@ sql_update_default(Client c, mvc *sql)
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_add_sys_views(Client c)
+{
+ size_t bufsize = 1000, pos = 0;
+ char *buf = GDKmalloc(bufsize), *err = NULL;
+ if (buf== NULL)
+ throw(SQL, "sql_add_sys_views", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE VIEW sys.roles AS SELECT id, name, grantor FROM
sys.auths a WHERE a.name NOT IN (SELECT u.name FROM sys.db_users() u);\n"
+ "GRANT SELECT ON sys.roles TO PUBLIC;\n"
+ "\n"
+ "CREATE VIEW sys.var_values (var_name, value) AS\n"
+ "SELECT 'cache' AS var_name, convert(cache, varchar(10)) AS
value UNION ALL\n"
+ "SELECT 'current_role', current_role UNION ALL\n"
+ "SELECT 'current_schema', current_schema UNION ALL\n"
+ "SELECT 'current_timezone', current_timezone UNION ALL\n"
+ "SELECT 'current_user', current_user UNION ALL\n"
+ "SELECT 'debug', debug UNION ALL\n"
+ "SELECT 'history', history UNION ALL\n"
+ "SELECT 'last_id', last_id UNION ALL\n"
+ "SELECT 'optimizer', optimizer UNION ALL\n"
+ "SELECT 'pi', pi() UNION ALL\n"
+ "SELECT 'rowcnt', rowcnt;\n"
+ "GRANT SELECT ON sys.var_values TO PUBLIC;\n"
+ "\n"
+ "UPDATE sys._tables SET system = true\n"
+ " WHERE name IN ('roles', 'var_values') AND schema_id IN
(SELECT id FROM sys.schemas WHERE name = 'sys');\n"
+ );
+
+ assert(pos < bufsize);
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+
+static str
+sql_add_new_sys_dependencies_views(Client c)
+{
+ size_t bufsize = 40000, pos = 0;
+ char *buf = GDKmalloc(bufsize), *err = NULL;
+ if (buf== NULL)
+ throw(SQL, "sql_add_sys_dependencies_views", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+
+ pos += snprintf(buf + pos, bufsize - pos,
+"CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table) 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 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, 'schema', 'sys.schemas'
FROM sys.schemas UNION ALL\n"
+"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\n"
+"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\n"
+"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\n"
+"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\n"
+"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\n"
+"SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys' FROM tmp.keys k JOIN sys._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, 'index',
'sys.idxs' 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' FROM tmp.idxs i JOIN sys._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,
'trigger', 'sys.triggers' 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' FROM tmp.triggers g JOIN sys._tables t ON g.table_id
= t.id UNION ALL\n"
+"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\n"
+"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\n"
+"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\n"
+"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 */\n"
+" ORDER BY id;\n"
+"\n"
+"GRANT SELECT ON sys.ids TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependencies_vw AS\n"
+"SELECT d.id, i1.obj_type, i1.name,\n"
+" d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,\n"
+" d.depend_type, dt.dependency_type_name\n"
+" FROM sys.dependencies d\n"
+" JOIN sys.ids i1 ON d.id = i1.id\n"
+" JOIN sys.ids i2 ON d.depend_id = i2.id\n"
+" JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id\n"
+" ORDER BY id, depend_id;\n"
+"\n"
+"GRANT SELECT ON sys.dependencies_vw TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_owners_on_schemas AS\n"
+"SELECT a.name AS owner_name, s.id AS schema_id, s.name AS schema_name, CAST(1
AS smallint) AS depend_type\n"
+" FROM sys.schemas AS s, sys.auths AS a\n"
+" WHERE s.owner = a.id\n"
+" ORDER BY a.name, s.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_owners_on_schemas TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_columns_on_keys AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, c.id AS column_id, c.name AS column_name, k.id AS key_id, k.name AS
key_name, CAST(kc.nr +1 AS int) AS key_col_nr, CAST(k.type AS smallint) AS
key_type, CAST(4 AS smallint) AS depend_type\n"
+" FROM sys.columns AS c, sys.objects AS kc, sys.keys AS k, sys.tables AS t\n"
+" WHERE k.table_id = c.table_id AND c.table_id = t.id AND kc.id = k.id AND
kc.name = c.name\n"
+" AND k.type IN (0, 1)\n"
+" ORDER BY t.schema_id, t.name, c.name, k.type, k.name, kc.nr;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_columns_on_keys TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_views AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, v.schema_id AS view_schema_id, v.id AS view_id, v.name AS
view_name, dep.depend_type AS depend_type\n"
+" FROM sys.tables AS t, sys.tables AS v, sys.dependencies AS dep\n"
+" WHERE t.id = dep.id AND v.id = dep.depend_id\n"
+" AND dep.depend_type = 5 AND t.type NOT IN (1, 11) AND v.type IN (1, 11)\n"
+" ORDER BY t.schema_id, t.name, v.schema_id, v.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_views TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_views_on_views AS\n"
+"SELECT v1.schema_id AS view1_schema_id, v1.id AS view1_id, v1.name AS
view1_name, v2.schema_id AS view2_schema_id, v2.id AS view2_id, v2.name AS
view2_name, dep.depend_type AS depend_type\n"
+" FROM sys.tables AS v1, sys.tables AS v2, sys.dependencies AS dep\n"
+" WHERE v1.id = dep.id AND v2.id = dep.depend_id\n"
+" AND dep.depend_type = 5 AND v1.type IN (1, 11) AND v2.type IN (1, 11)\n"
+" ORDER BY v1.schema_id, v1.name, v2.schema_id, v2.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_views_on_views TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_columns_on_views AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, c.id AS column_id, c.name AS column_name, v.schema_id AS
view_schema_id, v.id AS view_id, v.name AS view_name, dep.depend_type AS
depend_type\n"
+" FROM sys.columns AS c, sys.tables AS v, sys.tables AS t, sys.dependencies
AS dep\n"
+" WHERE c.id = dep.id AND v.id = dep.depend_id AND c.table_id = t.id\n"
+" AND dep.depend_type = 5 AND v.type IN (1, 11)\n"
+" ORDER BY t.schema_id, t.name, c.name, v.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_columns_on_views TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_functions_on_views AS\n"
+"SELECT f.schema_id AS function_schema_id, f.id AS function_id, f.name AS
function_name, v.schema_id AS view_schema_id, v.id AS view_id, v.name AS
view_name, dep.depend_type AS depend_type\n"
+" FROM sys.functions AS f, sys.tables AS v, sys.dependencies AS dep\n"
+" WHERE f.id = dep.id AND v.id = dep.depend_id\n"
+" AND dep.depend_type = 5 AND v.type IN (1, 11)\n"
+" ORDER BY f.schema_id, f.name, v.schema_id, v.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_functions_on_views TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_schemas_on_users AS\n"
+"SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6
AS smallint) AS depend_type\n"
+" FROM sys.users AS u, sys.schemas AS s\n"
+" WHERE u.default_schema = s.id\n"
+" ORDER BY s.name, u.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_functions AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, f.name AS function_name, f.type AS function_type, dep.depend_type
AS depend_type\n"
+" FROM sys.functions AS f, sys.tables AS t, sys.dependencies AS dep\n"
+" WHERE t.id = dep.id AND f.id = dep.depend_id\n"
+" AND dep.depend_type = 7 AND f.type <> 2 AND t.type NOT IN (1, 11)\n"
+" ORDER BY t.name, t.schema_id, f.name, f.id;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_functions TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_views_on_functions AS\n"
+"SELECT v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name,
f.name AS function_name, f.type AS function_type, dep.depend_type AS
depend_type\n"
+" FROM sys.functions AS f, sys.tables AS v, sys.dependencies AS dep\n"
+" WHERE v.id = dep.id AND f.id = dep.depend_id\n"
+" AND dep.depend_type = 7 AND f.type <> 2 AND v.type IN (1, 11)\n"
+" ORDER BY v.name, v.schema_id, f.name, f.id;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_views_on_functions TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_columns_on_functions AS\n"
+"SELECT c.table_id, c.id AS column_id, c.name, f.id AS function_id, f.name AS
function_name, f.type AS function_type, dep.depend_type AS depend_type\n"
+" FROM sys.functions AS f, sys.columns AS c, sys.dependencies AS dep\n"
+" WHERE c.id = dep.id AND f.id = dep.depend_id\n"
+" AND dep.depend_type = 7 AND f.type <> 2\n"
+" ORDER BY c.name, c.table_id, f.name, f.id;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_columns_on_functions TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_functions_on_functions AS\n"
+"SELECT f1.schema_id, f1.id AS function_id, f1.name AS function_name, f1.type
AS function_type,\n"
+" f2.schema_id AS used_in_function_schema_id, f2.id AS
used_in_function_id, f2.name AS used_in_function_name, f2.type AS
used_in_function_type, dep.depend_type AS depend_type\n"
+" FROM sys.functions AS f1, sys.functions AS f2, sys.dependencies AS dep\n"
+" WHERE f1.id = dep.id AND f2.id = dep.depend_id\n"
+" AND dep.depend_type = 7 AND f2.type <> 2\n"
+" ORDER BY f1.name, f1.id, f2.name, f2.id;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_functions_on_functions TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_triggers AS\n"
+"(SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, tri.id AS trigger_id, tri.name AS trigger_name, CAST(8 AS smallint)
AS depend_type\n"
+" FROM sys.tables AS t, sys.triggers AS tri\n"
+" WHERE tri.table_id = t.id)\n"
+"UNION\n"
+"(SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, tri.id AS trigger_id, tri.name AS trigger_name, dep.depend_type AS
depend_type\n"
+" FROM sys.tables AS t, sys.triggers AS tri, sys.dependencies AS dep\n"
+" WHERE dep.id = t.id AND dep.depend_id = tri.id\n"
+" AND dep.depend_type = 8)\n"
+" ORDER BY table_schema_id, table_name, trigger_name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_triggers TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_columns_on_triggers AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, tri.id AS trigger_id, tri.name AS trigger_name, c.id AS column_id,
c.name AS column_name, dep.depend_type AS depend_type\n"
+" FROM sys.tables AS t, sys.columns AS c, sys.triggers AS tri,
sys.dependencies AS dep\n"
+" WHERE dep.id = c.id AND dep.depend_id = tri.id AND c.table_id = t.id\n"
+" AND dep.depend_type = 8\n"
+" ORDER BY t.schema_id, t.name, tri.name, c.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_columns_on_triggers TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_functions_on_triggers AS\n"
+"SELECT f.schema_id AS function_schema_id, f.id AS function_id, f.name AS
function_name, f.type AS function_type,\n"
+" tri.id AS trigger_id, tri.name AS trigger_name, tri.table_id AS
trigger_table_id, dep.depend_type AS depend_type\n"
+" FROM sys.functions AS f, sys.triggers AS tri, sys.dependencies AS dep\n"
+" WHERE dep.id = f.id AND dep.depend_id = tri.id\n"
+" AND dep.depend_type = 8\n"
+" ORDER BY f.schema_id, f.name, tri.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_functions_on_triggers TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_indexes AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, i.id AS index_id, i.name AS index_name, i.type AS index_type,
CAST(10 AS smallint) AS depend_type\n"
+" FROM sys.tables AS t, sys.idxs AS i\n"
+" WHERE i.table_id = t.id\n"
+" -- exclude internal system generated and managed indexes for enforcing
declarative PKey and Unique constraints\n"
+" AND (i.table_id, i.name) NOT IN (SELECT k.table_id, k.name FROM sys.keys
k)\n"
+" ORDER BY t.schema_id, t.name, i.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_indexes TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_columns_on_indexes AS\n"
+"SELECT c.id AS column_id, c.name AS column_name, t.id AS table_id, t.name AS
table_name, t.schema_id, i.id AS index_id, i.name AS index_name, i.type AS
index_type, CAST(ic.nr +1 AS INT) AS seq_nr, CAST(10 AS smallint) AS
depend_type\n"
+" FROM sys.tables AS t, sys.columns AS c, sys.objects AS ic, sys.idxs AS i\n"
+" WHERE ic.name = c.name AND ic.id = i.id AND c.table_id = i.table_id AND
c.table_id = t.id\n"
+" -- exclude internal system generated and managed indexes for enforcing
declarative PKey and Unique constraints\n"
+" AND (i.table_id, i.name) NOT IN (SELECT k.table_id, k.name FROM sys.keys
k)\n"
+" ORDER BY c.name, t.name, t.schema_id, i.name, ic.nr;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_columns_on_indexes TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_foreignkeys AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, fk.name AS fk_name, CAST(k.type AS smallint) AS key_type, CAST(11
AS smallint) AS depend_type\n"
+" FROM sys.tables AS t, sys.keys AS k, sys.keys AS fk\n"
+" WHERE fk.rkey = k.id and k.table_id = t.id\n"
+" ORDER BY t.schema_id, t.name, fk.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_foreignkeys TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_keys_on_foreignkeys AS\n"
+"SELECT k.table_id AS key_table_id, k.id AS key_id, k.name AS key_name,
fk.table_id AS fk_table_id, fk.id AS fk_id, fk.name AS fk_name, CAST(k.type AS
smallint) AS key_type, CAST(11 AS smallint) AS depend_type\n"
+" FROM sys.keys AS k, sys.keys AS fk\n"
+" WHERE k.id = fk.rkey\n"
+" ORDER BY k.name, fk.name;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_keys_on_foreignkeys TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_tables_on_procdures AS\n"
+"SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS
table_name, p.name, p.type AS proc_type, dep.depend_type AS depend_type\n"
+" FROM sys.functions AS p, sys.tables AS t, sys.dependencies AS dep\n"
+" WHERE t.id = dep.id AND p.id = dep.depend_id\n"
+" AND dep.depend_type = 13 AND p.type = 2 AND t.type NOT IN (1, 11)\n"
+" ORDER BY t.name, t.schema_id, p.name, p.id;\n"
+"\n"
+"GRANT SELECT ON sys.dependency_tables_on_procdures TO PUBLIC;\n"
+"\n"
+"CREATE VIEW sys.dependency_views_on_procdures AS\n"
+"SELECT v.name AS view_name, p.name AS proc_name, p.type AS proc_type,
dep.depend_type AS depend_type\n"
+" FROM sys.functions AS p, sys.tables AS v, sys.dependencies AS dep\n"
+" WHERE v.id = dep.id AND p.id = dep.depend_id\n"
+" AND dep.depend_type = 13 AND p.type = 2 AND v.type IN (1, 11)\n"
+" ORDER BY v.name, v.schema_id, p.name, p.id;\n"
+"\n"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
