Changeset: 7eeb12bb2628 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/7eeb12bb2628
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/scripts/76_dump.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/Tests/comment-dump.test
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/sql_dump/Tests/dump.test
Branch: default
Log Message:
Fix problem with view sys.describe_comments. It used to describe all comments,
even when the comment was defined on a system object.
This made it impossible to add comments for system objects in system scripts,
as done for new schema information_schema, see 91_information_schema.sql.
As msqldump program also never dumps comments of system objects, I changed view
sys.describe_comments to exclude comments from system objects.
diffs (truncated from 456 to 300 lines):
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
@@ -5861,8 +5861,9 @@ static str
sql_update_default(Client c, mvc *sql, sql_schema *s)
{
sql_subtype tp;
+ sql_schema *info;
char *err = NULL;
- sql_schema *info;
+ res_table *output = NULL;
sql_find_subtype(&tp, "varchar", 0, 0);
if (sql_bind_func(sql, s->base.name, "similarity", &tp, &tp, F_FUNC,
true)) {
@@ -5883,20 +5884,15 @@ sql_update_default(Client c, mvc *sql, s
" select\n"
" schemas.name as schema,\n"
" tables.name as table,\n"
- " table_types.table_type_name as table_type,\n"
- " privilege_codes.privilege_code_name as privs,\n"
- " privileges.privileges as privs_code\n"
- " from privileges\n"
- " join sys.roles\n"
- " on privileges.auth_id = roles.id\n"
- " join sys.tables\n"
- " on privileges.obj_id = tables.id\n"
- " join sys.table_types\n"
- " on tables.type = table_types.table_type_id\n"
- " join sys.schemas\n"
- " on tables.schema_id = schemas.id\n"
- " join sys.privilege_codes\n"
- " on privileges.privileges =
privilege_codes.privilege_code_id\n"
+ " tt.table_type_name as table_type,\n"
+ " pc.privilege_code_name as privs,\n"
+ " p.privileges as privs_code\n"
+ " from privileges p\n"
+ " join sys.roles on p.auth_id = roles.id\n"
+ " join sys.tables on p.obj_id = tables.id\n"
+ " join sys.table_types tt on tables.type = tt.table_type_id\n"
+ " join sys.schemas on tables.schema_id = schemas.id\n"
+ " join sys.privilege_codes on p.privileges =
pc.privilege_code_id\n"
" where roles.name = current_role;\n"
"GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;\n"
"update sys._tables set system = true where system <> true and
schema_id = 2000 and name = 'describe_accessible_tables';\n"
@@ -5919,6 +5915,91 @@ sql_update_default(Client c, mvc *sql, s
}
}
+ /* 52_describe.sql changes to update sys.describe_comments view */
+ if ((err = SQLstatementIntern(c, "select id from sys.tables where name
= 'describe_comments' and schema_id = 2000 and query like '% not t.system%';",
"update", true, false, &output)) == NULL) {
+ BAT *b;
+ if ((b = BBPquickdesc(output->cols[0].b)) && BATcount(b) == 0) {
+ sql_table *t;
+ /* set views internally to non-system to allow drop
commands to succeed without error */
+ if ((t = mvc_bind_table(sql, s, "describe_comments"))
!= NULL)
+ t->system = 0;
+ if ((t = mvc_bind_table(sql, s, "dump_comments")) !=
NULL)
+ t->system = 0;
+
+ const char *cmds =
+ "DROP FUNCTION IF EXISTS sys.dump_database(BOOLEAN)
CASCADE;\n"
+ "DROP VIEW IF EXISTS sys.dump_comments CASCADE;\n"
+ "DROP VIEW IF EXISTS sys.describe_comments CASCADE;\n"
+ "CREATE VIEW sys.describe_comments AS\n"
+ " SELECT o.id AS id, o.tpe AS tpe, o.nme AS fqn,
cm.remark AS rem\n"
+ " FROM (\n"
+ " SELECT id, 'SCHEMA', sys.DQ(name) FROM
sys.schemas WHERE NOT system\n"
+ " UNION ALL\n"
+ " SELECT t.id,
ifthenelse(ts.table_type_name = 'VIEW', 'VIEW', 'TABLE'), sys.FQN(s.name,
t.name)\n"
+ " FROM sys.schemas s JOIN sys._tables t
ON s.id = t.schema_id JOIN sys.table_types ts ON t.type = ts.table_type_id\n"
+ " WHERE NOT t.system\n"
+ " UNION ALL\n"
+ " SELECT c.id, 'COLUMN', sys.FQN(s.name,
t.name) || '.' || sys.DQ(c.name) FROM sys.columns c, sys._tables t, sys.schemas
s WHERE NOT t.system AND c.table_id = t.id AND t.schema_id = s.id\n"
+ " UNION ALL\n"
+ " SELECT idx.id, 'INDEX', sys.FQN(s.name,
idx.name) FROM sys.idxs idx, sys._tables t, sys.schemas s WHERE NOT t.system
AND idx.table_id = t.id AND t.schema_id = s.id\n"
+ " UNION ALL\n"
+ " SELECT seq.id, 'SEQUENCE',
sys.FQN(s.name, seq.name) FROM sys.sequences seq, sys.schemas s WHERE
seq.schema_id = s.id\n"
+ " UNION ALL\n"
+ " SELECT f.id, ft.function_type_keyword,
qf.nme FROM sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf\n"
+ " WHERE NOT f.system AND f.type =
ft.function_type_id AND f.schema_id = s.id AND qf.id = f.id\n"
+ " ) AS o(id, tpe, nme)\n"
+ " JOIN sys.comments cm ON cm.id = o.id;\n"
+ "GRANT SELECT ON sys.describe_comments TO PUBLIC;\n"
+ "CREATE VIEW sys.dump_comments AS\n"
+ " SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || '
IS ' || sys.SQ(c.rem) || ';' stmt FROM sys.describe_comments c;\n"
+ "CREATE FUNCTION sys.dump_database(describe BOOLEAN)
RETURNS TABLE(o int, stmt STRING)\n"
+ "BEGIN\n"
+ " SET SCHEMA sys;\n"
+ " TRUNCATE sys.dump_statements;\n"
+ " INSERT INTO sys.dump_statements VALUES (1, 'START
TRANSACTION;');\n"
+ " INSERT INTO sys.dump_statements VALUES (2, 'SET
SCHEMA \"sys\";');\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_create_roles;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_create_users;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_create_schemas;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_user_defined_types;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_add_schemas_to_users;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_grant_user_privileges;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_sequences;\n"
+ " --functions and table-likes can be interdependent.
They should be inserted in the order of their catalogue id.\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(ORDER BY stmts.o), stmts.s\n"
+ " FROM (\n"
+ " SELECT f.o,
f.stmt FROM sys.dump_functions f\n"
+ " UNION ALL\n"
+ " SELECT t.o,
t.stmt FROM sys.dump_tables t\n"
+ " ) AS stmts(o, s);\n"
+ " IF NOT DESCRIBE THEN\n"
+ " CALL sys.dump_table_data();\n"
+ " END IF;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_start_sequences;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_column_defaults;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_table_constraint_type;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_indices;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_foreign_keys;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_partition_tables;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_triggers;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_comments;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_table_grants;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_column_grants;\n"
+ " INSERT INTO sys.dump_statements SELECT (SELECT
COUNT(*) FROM sys.dump_statements) + RANK() OVER(), stmt FROM
sys.dump_function_grants;\n"
+ " INSERT INTO sys.dump_statements VALUES ((SELECT
COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;');\n"
+ " RETURN sys.dump_statements;\n"
+ "END;\n"
+ "update sys._tables set system = true where schema_id =
2000 and name in ('describe_comments','dump_comments');\n"
+ "update sys.functions set system = true where system <>
true and schema_id = 2000 and name = 'dump_database' and type = 5;\n";
+
+ printf("Running database upgrade commands:\n%s\n",
cmds);
+ fflush(stdout);
+ err = SQLstatementIntern(c, cmds, "update", true,
false, NULL);
+ }
+ res_table_destroy(output);
+ output = NULL;
+ }
+
/* 91_information_schema.sql */
info = mvc_bind_schema(sql, "information_schema");
if (info == NULL) {
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -340,38 +340,24 @@ CREATE VIEW sys.fully_qualified_function
ON fqn1.id = fqn2.id AND (fqn1.num = fqn2.num OR fqn1.num IS
NULL AND fqn2.num is NULL);
CREATE VIEW sys.describe_comments AS
- SELECT
- o.id id,
- o.tpe tpe,
- o.nme fqn,
- c.remark rem
- FROM (
- SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas
-
- UNION ALL
-
- SELECT t.id, ifthenelse(ts.table_type_name = 'VIEW',
'VIEW', 'TABLE'), sys.FQN(s.name, t.name)
- FROM sys.schemas s JOIN sys.tables t ON s.id =
t.schema_id JOIN sys.table_types ts ON t.type = ts.table_type_id
- WHERE s.name <> 'tmp'
-
- UNION ALL
-
- SELECT c.id, 'COLUMN', sys.FQN(s.name, t.name) || '.'
|| sys.DQ(c.name) FROM sys.columns c, sys.tables t, sys.schemas s WHERE
c.table_id = t.id AND t.schema_id = s.id
-
- UNION ALL
-
- SELECT idx.id, 'INDEX', sys.FQN(s.name, idx.name) FROM
sys.idxs idx, sys._tables t, sys.schemas s WHERE idx.table_id = t.id AND
t.schema_id = s.id
-
- UNION ALL
-
- SELECT seq.id, 'SEQUENCE', sys.FQN(s.name, seq.name)
FROM sys.sequences seq, sys.schemas s WHERE seq.schema_id = s.id
-
- UNION ALL
-
- SELECT f.id, ft.function_type_keyword, qf.nme FROM
sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf WHERE f.type = ft.function_type_id AND
f.schema_id = s.id AND qf.id = f.id
-
- ) AS o(id, tpe, nme)
- JOIN sys.comments c ON c.id = o.id;
+ SELECT o.id AS id, o.tpe AS tpe, o.nme AS fqn, cm.remark AS rem
+ FROM (
+ SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas WHERE NOT
system
+ UNION ALL
+ SELECT t.id, ifthenelse(ts.table_type_name = 'VIEW', 'VIEW',
'TABLE'), sys.FQN(s.name, t.name)
+ FROM sys.schemas s JOIN sys._tables t ON s.id = t.schema_id
JOIN sys.table_types ts ON t.type = ts.table_type_id
+ WHERE NOT t.system
+ UNION ALL
+ SELECT c.id, 'COLUMN', sys.FQN(s.name, t.name) || '.' ||
sys.DQ(c.name) FROM sys.columns c, sys._tables t, sys.schemas s WHERE NOT
t.system AND c.table_id = t.id AND t.schema_id = s.id
+ UNION ALL
+ SELECT idx.id, 'INDEX', sys.FQN(s.name, idx.name) FROM sys.idxs
idx, sys._tables t, sys.schemas s WHERE NOT t.system AND idx.table_id = t.id
AND t.schema_id = s.id
+ UNION ALL
+ SELECT seq.id, 'SEQUENCE', sys.FQN(s.name, seq.name) FROM
sys.sequences seq, sys.schemas s WHERE seq.schema_id = s.id
+ UNION ALL
+ SELECT f.id, ft.function_type_keyword, qf.nme FROM
sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf
+ WHERE NOT f.system AND f.type = ft.function_type_id AND
f.schema_id = s.id AND qf.id = f.id
+ ) AS o(id, tpe, nme)
+ JOIN sys.comments cm ON cm.id = o.id;
CREATE VIEW sys.describe_privileges AS
SELECT
@@ -605,20 +591,15 @@ CREATE VIEW sys.describe_accessible_tabl
SELECT
schemas.name AS schema,
tables.name AS table,
- table_types.table_type_name AS table_type,
- privilege_codes.privilege_code_name AS privs,
- privileges.privileges AS privs_code
- FROM privileges
- JOIN sys.roles
- ON privileges.auth_id = roles.id
- JOIN sys.tables
- ON privileges.obj_id = tables.id
- JOIN sys.table_types
- ON tables.type = table_types.table_type_id
- JOIN sys.schemas
- ON tables.schema_id = schemas.id
- JOIN sys.privilege_codes
- ON privileges.privileges = privilege_codes.privilege_code_id
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
WHERE roles.name = current_role;
GRANT SELECT ON sys.describe_constraints TO PUBLIC;
diff --git a/sql/scripts/76_dump.sql b/sql/scripts/76_dump.sql
--- a/sql/scripts/76_dump.sql
+++ b/sql/scripts/76_dump.sql
@@ -352,7 +352,7 @@ BEGIN
INSERT INTO sys.dump_statements SELECT (SELECT COUNT(*) FROM
sys.dump_statements) + RANK() OVER(), stmt FROM sys.dump_function_grants;
--TODO Improve performance of dump_table_data.
- --TODO loaders ,procedures, window and filter sys.functions.
+ --TODO loaders, procedures, window and filter sys.functions.
--TODO look into order dependent group_concat
INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
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
@@ -666,9 +666,6 @@ tables
describe_column_defaults
DEP_VIEW
tables
-describe_comments
-DEP_VIEW
-tables
describe_privileges
DEP_VIEW
tables
@@ -823,7 +820,7 @@ DEP_FUNC
query TTT rowsort
SELECT distinct 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
----
-1620 values hashing to 29d0134221c10cbcbdf5930e29ab53d1
+1623 values hashing to 6b21bdd80c4e73ef598244a23a31446a
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/Tests/comment-dump.test b/sql/test/Tests/comment-dump.test
--- a/sql/test/Tests/comment-dump.test
+++ b/sql/test/Tests/comment-dump.test
@@ -96,7 +96,6 @@ COMMENT ON FUNCTION "foo"."f"(INTEGER,IN
COMMENT ON INDEX "foo"."idx" IS 'index on j';
COMMENT ON PROCEDURE "foo"."g"() IS 'proc';
COMMENT ON SCHEMA "foo" IS 'foo foo';
-COMMENT ON SCHEMA "information_schema" IS 'ISO/IEC 9075-11 SQL/Schemata';
COMMENT ON SEQUENCE "foo"."counter" IS 'counting';
COMMENT ON TABLE "foo"."tab" IS 'table';
COMMENT ON VIEW "foo"."sel" IS 'phew';
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
@@ -486,9 +486,9 @@ select 'null in fkeys.delete_action', de
[ "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_accessible_tables", "create view
sys.describe_accessible_tables as select schemas.name as schema, tables.name as
table, table_types.table_type_name as table_type,
privilege_codes.privilege_code_name as privs, privileges.privileges as
privs_code from privileges join sys.roles on privileges.auth_id = roles.id join
sys.tables on privileges.obj_id = tables.id join sys.table_types on tables.type
= table_types.table_type_id join sys.schemas on tables.schema_id = schemas.id
join sys.privilege_codes on privileges.privileges =
privilege_codes.privilege_code_id where roles.name = current_role;", "VIEW",
true, "COMMIT", "WRITABLE", NULL ]
+[ "sys._tables", "sys", "describe_accessible_tables", "create view
sys.describe_accessible_tables as select schemas.name as schema, tables.name as
table, tt.table_type_name as table_type, pc.privilege_code_name as privs,
p.privileges as privs_code from privileges p join sys.roles on p.auth_id =
roles.id join sys.tables on p.obj_id = tables.id join sys.table_types tt on
tables.type = tt.table_type_id join sys.schemas on tables.schema_id =
schemas.id join sys.privilege_codes pc on p.privileges = pc.privilege_code_id
where roles.name = current_role;", "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 ]
-[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id id, o.tpe tpe, o.nme fqn, c.remark rem
from (select id, 'SCHEMA', sys.dq(name) from sys.schemas union all select t.id,
ifthenelse(ts.table_type_name = 'VIEW', 'VIEW', 'TABLE'), sys.fqn(s.name,
t.name) from sys.schemas s join sys.tables t on s.id = t.schema_id join
sys.table_types ts on t.type = ts.table_type_id where s.name <> 'tmp' union all
select c.id, 'COLUMN', sys.fqn(s.name, t.name) || '.' || sys.dq(c.name) from
sys.columns c, sys.tables t, sys.schemas s where c.table_id = t.id and
t.schema_id = s.id union all select idx.id, 'INDEX', sys.fqn(s.name, idx.name)
from sys.idxs idx, sys._tables t, sys.schemas s where idx.table_id = t.id and
t.schema_id = s.id union all select seq.id, 'SEQUENCE', sys.fqn(s.name,
seq.name) from sys.sequences seq, sys.schemas s where seq.schema_id = s.id
union all select f.id, ft.function_type_keyword, qf.nme from sys.functions f,
sys.function_types ft, sys.
schemas s, sys.fully_qualified_functions qf where f.type = ft.function_type_id
and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme) join sys.comments c
on c.id = o.id;", "VIEW", true, "COMMIT", "WRITABLE", NULL
]
+[ "sys._tables", "sys", "describe_comments", "create view
sys.describe_comments as select o.id as id, o.tpe as tpe, o.nme as fqn,
cm.remark as rem from (select id, 'SCHEMA', sys.dq(name) from sys.schemas where
not system union all select t.id, ifthenelse(ts.table_type_name = 'VIEW',
'VIEW', 'TABLE'), sys.fqn(s.name, t.name) from sys.schemas s join sys._tables t
on s.id = t.schema_id join sys.table_types ts on t.type = ts.table_type_id
where not t.system union all select c.id, 'COLUMN', sys.fqn(s.name, t.name) ||
'.' || sys.dq(c.name) from sys.columns c, sys._tables t, sys.schemas s where
not t.system and c.table_id = t.id and t.schema_id = s.id union all select
idx.id, 'INDEX', sys.fqn(s.name, idx.name) from sys.idxs idx, sys._tables t,
sys.schemas s where not t.system and idx.table_id = t.id and t.schema_id = s.id
union all select seq.id, 'SEQUENCE', sys.fqn(s.name, seq.name) from
sys.sequences seq, sys.schemas s where seq.schema_id = s.id union all select
f.id, ft.function_type_ke
yword, qf.nme from sys.functions f, sys.function_types ft, sys.schemas s,
sys.fully_qualified_functions qf where not f.system and f.type =
ft.function_type_id and f.schema_id = s.id and qf.id = f.id) as o(id, tpe, nme)
join sys.comments cm on cm.id = o.id;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_constraints", "create view
sys.describe_constraints as select s.name sch, t.name tbl, kc.name col, k.name
con, case k.type when 0 then 'PRIMARY KEY' when 1 then 'UNIQUE' end tpe from
sys.schemas s, sys._tables t, sys.objects kc, sys.keys k where kc.id = k.id and
k.table_id = t.id and s.id = t.schema_id and t.system = false and k.type in (0,
1);", "VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_foreign_keys", "create view
sys.describe_foreign_keys as with action_type (id, act) as (values (0, 'NO
ACTION'), (1, 'CASCADE'), (2, 'RESTRICT'), (3, 'SET NULL'), (4, 'SET DEFAULT'))
select fs.name fk_s, fkt.name fk_t, fkkc.name fk_c, fkkc.nr o, fkk.name fk,
ps.name pk_s, pkt.name pk_t, pkkc.name pk_c, ou.act on_update, od.act on_delete
from sys._tables fkt, sys.objects fkkc, sys.keys fkk, sys._tables pkt,
sys.objects pkkc, sys.keys pkk, sys.schemas ps, sys.schemas fs, action_type ou,
action_type od where fkt.id = fkk.table_id and pkt.id = pkk.table_id and fkk.id
= fkkc.id and pkk.id = pkkc.id and fkk.rkey = pkk.id and fkkc.nr = pkkc.nr and
pkt.schema_id = ps.id and fkt.schema_id = fs.id and (fkk.\"action\" & 255) =
od.id and ((fkk.\"action\" >> 8) & 255) = ou.id order by fkk.name, fkkc.nr;",
"VIEW", true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "describe_functions", "create view
sys.describe_functions as with func_args_all(func_id, number, max_number,
func_arg) as (select func_id, number, max(number) over (partition by func_id
order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number) from sys.args where inout = 1), func_args(func_id,
func_arg) as (select func_id, func_arg from func_args_all where number =
max_number), func_rets_all(func_id, number, max_number, func_ret,
func_ret_type) as (select func_id, number, max(number) over (partition by
func_id order by number desc), group_concat(sys.dq(name) || ' ' ||
sys.describe_type(type, type_digits, type_scale),', ') over (partition by
func_id order by number), group_concat(sys.describe_type(type, type_digits,
type_scale),', ') over (partition by func_id order by number) from sys.args
where inout = 0), func_rets(func_id, func_ret, func_ret_type) as (select
func_id, func_ret, func_ret_type from func_rets_all where number =
max_number) select f.id o, s.name sch, f.name fun, case when f.language in (1,
2) then f.func else 'CREATE ' || ft.function_type_keyword || ' ' ||
sys.fqn(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ')' || case when
f.type = 5 then ' RETURNS TABLE (' || coalesce(fr.func_ret, '') || ')' when
f.type in (1,3) then ' RETURNS ' || fr.func_ret_type else '' end || case when
fl.language_keyword is null then '' else ' LANGUAGE ' || fl.language_keyword
end || ' ' || f.func end def from sys.functions f left outer join func_args fa
on fa.func_id = f.id left outer join func_rets fr on fr.func_id = f.id join
sys.schemas s on f.schema_id = s.id join sys.function_types ft on f.type =
ft.function_type_id left outer join sys.function_languages fl on f.language =
fl.language_id where s.name <> 'tmp' and not f.system;", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
@@ -3169,7 +3169,6 @@ select 'null in fkeys.delete_action', de
[ "table used by view", "sys", "schemas", "sys",
"describe_comments", "VIEW" ]
[ "table used by view", "sys", "sequences", "sys",
"describe_comments", "VIEW" ]
[ "table used by view", "sys", "table_types", "sys",
"describe_comments", "VIEW" ]
-[ "table used by view", "sys", "tables", "sys",
"describe_comments", "VIEW" ]
[ "table used by view", "sys", "_tables", "sys",
"describe_constraints", "VIEW" ]
[ "table used by view", "sys", "keys", "sys", "describe_constraints",
"VIEW" ]
[ "table used by view", "sys", "objects", "sys",
"describe_constraints", "VIEW" ]
@@ -3629,7 +3628,10 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "tables", "schema_id", "sys",
"describe_column_defaults", "VIEW" ]
[ "column used by view", "sys", "tables", "system", "sys",
"describe_column_defaults", "VIEW" ]
[ "column used by view", "sys", "_tables", "id", "sys",
"describe_comments", "VIEW" ]
+[ "column used by view", "sys", "_tables", "name", "sys",
"describe_comments", "VIEW" ]
[ "column used by view", "sys", "_tables", "schema_id", "sys",
"describe_comments", "VIEW" ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
