Changeset: ed995a75b565 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/ed995a75b565 Modified Files: sql/ChangeLog sql/backends/monet5/sql_upgrades.c sql/scripts/21_dependency_views.sql sql/test/emptydb/Tests/check.stable.out sql/test/emptydb/Tests/check.stable.out.32bit sql/test/emptydb/Tests/check.stable.out.int128 Branch: default Log Message:
Extend system views sys.dependency_tables_on_functions and dependency_views_on_functions with missing column: function_id diffs (241 lines): diff --git a/sql/ChangeLog b/sql/ChangeLog --- a/sql/ChangeLog +++ b/sql/ChangeLog @@ -1,6 +1,10 @@ # ChangeLog file for sql # This file is updated with Maddlog +* Thu Nov 3 2022 Martin van Dinther <[email protected]> +- System views sys.dependency_tables_on_functions and + dependency_views_on_functions have been extended with column: function_id. + * Fri Sep 30 2022 Joeri van Ruth <[email protected]> - When loading data using COPY BINARY INTO, apply default values instead of just inserting NULLs. 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 @@ -5407,8 +5407,8 @@ sql_update_default(Client c, mvc *sql, s " INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;');\n" " RETURN sys.dump_statements;\n" "END;\n" - "update sys.functions set system = true where system <> true and name in ('sq', 'get_remote_table_expressions', 'prepare_esc') and schema_id = 2000 and type = %d;\n" - "update sys._tables set system = true where system <> true and name in ('describe_tables', 'dump_create_users', 'dump_partition_tables', 'dump_comments', 'dump_tables') and schema_id = 2000;\n" + "update sys.functions set system = true where system <> true and name in ('sq', 'get_remote_table_expressions', 'prepare_esc') and schema_id = 2000 and type = %d;\n" + "update sys._tables set system = true where system <> true and name in ('describe_tables', 'dump_create_users', 'dump_partition_tables', 'dump_comments', 'dump_tables') and schema_id = 2000;\n" "update sys.functions set system = true where system <> true and name = 'dump_table_data' and schema_id = 2000 and type = %d;\n" "update sys.functions set system = true where system <> true and name = 'dump_database' and schema_id = 2000 and type = %d;\n" "GRANT SELECT ON sys.describe_tables TO PUBLIC;\n", @@ -5421,6 +5421,50 @@ sql_update_default(Client c, mvc *sql, s output = NULL; } + /* Add new column 'function_id' to views + * sys.dependency_tables_on_functions and dependency_views_on_functions */ + pos = snprintf(buf, bufsize, + "SELECT id FROM sys._columns where name = 'function_id' " + "and table_id in (select id FROM sys._tables where system " + "and name in ('dependency_tables_on_functions','dependency_views_on_functions') " + "and schema_id = 2000);\n"); + if ((err = SQLstatementIntern(c, buf, "update", true, false, &output)) == NULL) { + if ((b = BBPquickdesc(output->cols[0].b)) && BATcount(b) != 2) { + sql_table *t; + if ((t = mvc_bind_table(sql, s, "dependency_tables_on_functions")) != NULL) + t->system = 0; + if ((t = mvc_bind_table(sql, s, "dependency_views_on_functions")) != NULL) + t->system = 0; + pos = 0; + pos += snprintf(buf + pos, bufsize - pos, + "drop view if exists sys.dependency_tables_on_functions;\n" + "drop view if exists sys.dependency_views_on_functions;\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.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.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" + "GRANT SELECT ON sys.dependency_tables_on_functions TO PUBLIC;\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.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.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" + "GRANT SELECT ON sys.dependency_views_on_functions TO PUBLIC;\n" + "update sys._tables set system = true where system <> true and name in " + "('dependency_tables_on_functions','dependency_views_on_functions') and schema_id = 2000;\n"); + assert(pos < bufsize); + printf("Running database upgrade commands:\n%s\n", buf); + err = SQLstatementIntern(c, buf, "update", true, false, NULL); + } + res_table_destroy(output); + output = NULL; + } + GDKfree(buf); return err; /* usually MAL_SUCCEED */ } 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 @@ -167,7 +167,7 @@ GRANT SELECT ON sys.dependency_schemas_o -- SELECT * FROM sys.dependencies_vw WHERE depend_type = 7 ORDER BY obj_type, id; -- Table t has a dependency on function f. CREATE VIEW sys.dependency_tables_on_functions AS -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 +SELECT t.schema_id AS table_schema_id, t.id AS table_id, t.name AS table_name, f.id AS function_id, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type FROM sys.functions AS f, sys.tables AS t, sys.dependencies AS dep WHERE t.id = dep.id AND f.id = dep.depend_id AND dep.depend_type = 7 AND f.type <> 2 AND t.type NOT IN (1, 11) @@ -177,7 +177,7 @@ GRANT SELECT ON sys.dependency_tables_on -- View v has a dependency on function f. CREATE VIEW sys.dependency_views_on_functions AS -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 +SELECT v.schema_id AS view_schema_id, v.id AS view_id, v.name AS view_name, f.id AS function_id, f.name AS function_name, f.type AS function_type, dep.depend_type AS depend_type FROM sys.functions AS f, sys.tables AS v, sys.dependencies AS dep WHERE v.id = dep.id AND f.id = dep.depend_id AND dep.depend_type = 7 AND f.type <> 2 AND v.type IN (1, 11) diff --git a/sql/test/emptydb/Tests/check.stable.out b/sql/test/emptydb/Tests/check.stable.out --- a/sql/test/emptydb/Tests/check.stable.out +++ b/sql/test/emptydb/Tests/check.stable.out @@ -451,13 +451,13 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dependency_owners_on_schemas", "create view sys.dependency_owners_on_schemas as select a.name as owner_name, s.id as schema_id, s.name as schema_name, cast(1 as smallint) as depend_type from sys.schemas as s, sys.auths as a where s.owner = a.id order by a.name, s.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.db_user_info as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_foreignkeys", "create view sys.dependency_tables_on_foreignkeys as 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 from sys.tables as t, sys.keys as k, sys.keys as fk where fk.rkey = k.id and k.table_id = t.id order by t.schema_id, t.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as 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 from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_indexes", "create view sys.dependency_tables_on_indexes as 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 from sys.tables as t, sys.idxs as i where i.table_id = t.id and (i.table_id, i.name) not in (select k.table_id, k.name from sys.keys k) order by t.schema_id, t.name, i.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_procedures", "create view sys.dependency_tables_on_procedures as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as t, sys.dependencies as dep where t.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and t.type not in (1, 11) order by t.name, t.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_triggers", "create view sys.dependency_tables_on_triggers as (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 from sys.tables as t, sys.triggers as tri where tri.table_id = t.id) union (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 from sys.tables as t, sys.triggers as tri, sys.dependencies as dep where dep.id = t.id and dep.depend_id = tri.id and dep.depend_type = 8) order by table_schema_id, table_name, trigger_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_views", "create view sys.dependency_tables_on_views as 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 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 t.type not in (1, 11) and v.type in (1, 11) order by t.schema_id, t.name, v.schema_id, v.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_types", NULL, "TABLE", true, "COMMIT", "READONLY", NULL ] -[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as 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 from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_procedures", "create view sys.dependency_views_on_procedures as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as v, sys.dependencies as dep where v.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and v.type in (1, 11) order by v.name, v.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_views", "create view sys.dependency_views_on_views as 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 from sys.tables as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id = dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in (1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "describe_column_defaults", "create view sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col, c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id = t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -770,9 +770,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_tables_on_functions", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] @@ -805,9 +806,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_views_on_functions", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit b/sql/test/emptydb/Tests/check.stable.out.32bit --- a/sql/test/emptydb/Tests/check.stable.out.32bit +++ b/sql/test/emptydb/Tests/check.stable.out.32bit @@ -451,13 +451,13 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dependency_owners_on_schemas", "create view sys.dependency_owners_on_schemas as select a.name as owner_name, s.id as schema_id, s.name as schema_name, cast(1 as smallint) as depend_type from sys.schemas as s, sys.auths as a where s.owner = a.id order by a.name, s.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.db_user_info as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_foreignkeys", "create view sys.dependency_tables_on_foreignkeys as 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 from sys.tables as t, sys.keys as k, sys.keys as fk where fk.rkey = k.id and k.table_id = t.id order by t.schema_id, t.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as 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 from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_indexes", "create view sys.dependency_tables_on_indexes as 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 from sys.tables as t, sys.idxs as i where i.table_id = t.id and (i.table_id, i.name) not in (select k.table_id, k.name from sys.keys k) order by t.schema_id, t.name, i.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_procedures", "create view sys.dependency_tables_on_procedures as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as t, sys.dependencies as dep where t.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and t.type not in (1, 11) order by t.name, t.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_triggers", "create view sys.dependency_tables_on_triggers as (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 from sys.tables as t, sys.triggers as tri where tri.table_id = t.id) union (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 from sys.tables as t, sys.triggers as tri, sys.dependencies as dep where dep.id = t.id and dep.depend_id = tri.id and dep.depend_type = 8) order by table_schema_id, table_name, trigger_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_views", "create view sys.dependency_tables_on_views as 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 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 t.type not in (1, 11) and v.type in (1, 11) order by t.schema_id, t.name, v.schema_id, v.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_types", NULL, "TABLE", true, "COMMIT", "READONLY", NULL ] -[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as 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 from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_procedures", "create view sys.dependency_views_on_procedures as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as v, sys.dependencies as dep where v.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and v.type in (1, 11) order by v.name, v.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_views", "create view sys.dependency_views_on_views as 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 from sys.tables as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id = dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in (1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "describe_column_defaults", "create view sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col, c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id = t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -770,9 +770,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_tables_on_functions", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] @@ -805,9 +806,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_views_on_functions", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] diff --git a/sql/test/emptydb/Tests/check.stable.out.int128 b/sql/test/emptydb/Tests/check.stable.out.int128 --- a/sql/test/emptydb/Tests/check.stable.out.int128 +++ b/sql/test/emptydb/Tests/check.stable.out.int128 @@ -451,13 +451,13 @@ select 'null in value_partitions.value', [ "sys._tables", "sys", "dependency_owners_on_schemas", "create view sys.dependency_owners_on_schemas as select a.name as owner_name, s.id as schema_id, s.name as schema_name, cast(1 as smallint) as depend_type from sys.schemas as s, sys.auths as a where s.owner = a.id order by a.name, s.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_schemas_on_users", "create view sys.dependency_schemas_on_users as select s.id as schema_id, s.name as schema_name, u.name as user_name, cast(6 as smallint) as depend_type from sys.db_user_info as u, sys.schemas as s where u.default_schema = s.id order by s.name, u.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_foreignkeys", "create view sys.dependency_tables_on_foreignkeys as 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 from sys.tables as t, sys.keys as k, sys.keys as fk where fk.rkey = k.id and k.table_id = t.id order by t.schema_id, t.name, fk.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] -[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as 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 from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_tables_on_functions", "create view sys.dependency_tables_on_functions as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as t, sys.dependencies as dep where t.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and t.type not in (1, 11) order by t.name, t.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_indexes", "create view sys.dependency_tables_on_indexes as 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 from sys.tables as t, sys.idxs as i where i.table_id = t.id and (i.table_id, i.name) not in (select k.table_id, k.name from sys.keys k) order by t.schema_id, t.name, i.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_procedures", "create view sys.dependency_tables_on_procedures as select t.schema_id as table_schema_id, t.id as table_id, t.name as table_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as t, sys.dependencies as dep where t.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and t.type not in (1, 11) order by t.name, t.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_triggers", "create view sys.dependency_tables_on_triggers as (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 from sys.tables as t, sys.triggers as tri where tri.table_id = t.id) union (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 from sys.tables as t, sys.triggers as tri, sys.dependencies as dep where dep.id = t.id and dep.depend_id = tri.id and dep.depend_type = 8) order by table_schema_id, table_name, trigger_name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_tables_on_views", "create view sys.dependency_tables_on_views as 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 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 t.type not in (1, 11) and v.type in (1, 11) order by t.schema_id, t.name, v.schema_id, v.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_types", NULL, "TABLE", true, "COMMIT", "READONLY", NULL ] -[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as 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 from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] +[ "sys._tables", "sys", "dependency_views_on_functions", "create view sys.dependency_views_on_functions as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, f.id as function_id, f.name as function_name, f.type as function_type, dep.depend_type as depend_type from sys.functions as f, sys.tables as v, sys.dependencies as dep where v.id = dep.id and f.id = dep.depend_id and dep.depend_type = 7 and f.type <> 2 and v.type in (1, 11) order by v.name, v.schema_id, f.name, f.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_procedures", "create view sys.dependency_views_on_procedures as select v.schema_id as view_schema_id, v.id as view_id, v.name as view_name, p.id as procedure_id, p.name as procedure_name, p.type as procedure_type, dep.depend_type as depend_type from sys.functions as p, sys.tables as v, sys.dependencies as dep where v.id = dep.id and p.id = dep.depend_id and dep.depend_type = 13 and p.type = 2 and v.type in (1, 11) order by v.name, v.schema_id, p.name, p.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "dependency_views_on_views", "create view sys.dependency_views_on_views as 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 from sys.tables as v1, sys.tables as v2, sys.dependencies as dep where v1.id = dep.id and v2.id = dep.depend_id and dep.depend_type = 5 and v1.type in (1, 11) and v2.type in (1, 11) order by v1.schema_id, v1.name, v2.schema_id, v2.name;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] [ "sys._tables", "sys", "describe_column_defaults", "create view sys.describe_column_defaults as select s.name sch, t.name tbl, c.name col, c.\"default\" def from sys.schemas s, sys.tables t, sys.columns c where s.id = t.schema_id and t.id = c.table_id and s.name <> 'tmp' and not t.system and c.\"default\" is not null;", "VIEW", true, "COMMIT", "WRITABLE", NULL ] @@ -770,9 +770,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_tables_on_functions", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_functions", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_tables_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_tables_on_indexes", "table_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] @@ -805,9 +806,10 @@ select 'null in value_partitions.value', [ "sys._columns", "dependency_views_on_functions", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_functions", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 3, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 4, NULL, NULL ] -[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_id", "int", 32, 0, NULL, true, 3, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_name", "varchar", 256, 0, NULL, true, 4, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "function_type", "int", 32, 0, NULL, true, 5, NULL, NULL ] +[ "sys._columns", "dependency_views_on_functions", "depend_type", "smallint", 16, 0, NULL, true, 6, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_schema_id", "int", 32, 0, NULL, true, 0, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_id", "int", 32, 0, NULL, true, 1, NULL, NULL ] [ "sys._columns", "dependency_views_on_procedures", "view_name", "varchar", 1024, 0, NULL, true, 2, NULL, NULL ] _______________________________________________ checkin-list mailing list -- [email protected] To unsubscribe send an email to [email protected]
