Changeset: 02fa7ae3a58a for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/02fa7ae3a58a
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/Tests/comment-dump.test
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
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-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.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: Jan2022
Log Message:
Fix dumping of comments on functions: we need the argument types.
diffs (truncated from 1963 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
@@ -3480,6 +3480,8 @@ sql_update_jan2022(Client c, mvc *sql, c
t->system = 0;
t = mvc_bind_table(sql, s, "dump_user_defined_types");
t->system = 0;
+ t = mvc_bind_table(sql, s, "fully_qualified_functions");
+ t->system = 0;
pos += snprintf(buf + pos, bufsize - pos,
/* drop dependant stuff from
76_dump.sql */
"drop function
sys.dump_database(boolean);\n"
@@ -3509,6 +3511,7 @@ sql_update_jan2022(Client c, mvc *sql, c
"drop view sys.describe_functions;\n"
"drop view
sys.describe_partition_tables;\n"
"drop view sys.describe_privileges;\n"
+ "drop view
sys.fully_qualified_functions;\n"
"drop view sys.describe_comments;\n"
"drop view sys.describe_tables;\n"
"drop view sys.describe_sequences;\n"
@@ -3590,6 +3593,28 @@ sql_update_jan2022(Client c, mvc *sql, c
" AND s.id =
t.schema_id\n"
" AND ts.table_type_id =
t.type\n"
" AND s.name <> 'tmp';\n"
+ "CREATE VIEW
sys.fully_qualified_functions AS\n"
+ " WITH fqn(id, tpe, sig, num)
AS\n"
+ " (\n"
+ " SELECT\n"
+ " f.id,\n"
+ "
ft.function_type_keyword,\n"
+ " CASE WHEN
a.type IS NULL THEN\n"
+ "
sys.fqn(s.name, f.name) || '()'\n"
+ " ELSE\n"
+ "
sys.fqn(s.name, f.name) || '(' || group_concat(sys.describe_type(a.type,
a.type_digits, a.type_scale), ',') OVER (PARTITION BY f.id ORDER BY a.number)
|| ')'\n"
+ " END,\n"
+ " a.number\n"
+ " FROM sys.schemas s,
sys.function_types ft, sys.functions f LEFT JOIN sys.args a ON f.id =
a.func_id\n"
+ " WHERE s.id= f.schema_id
AND f.type = ft.function_type_id\n"
+ " )\n"
+ " SELECT\n"
+ " fqn1.id id,\n"
+ " fqn1.tpe tpe,\n"
+ " fqn1.sig nme\n"
+ " FROM\n"
+ " fqn fqn1 JOIN (SELECT
id, max(num) FROM fqn GROUP BY id) fqn2(id, num)\n"
+ " ON fqn1.id = fqn2.id
AND (fqn1.num = fqn2.num OR fqn1.num IS NULL AND fqn2.num is NULL);\n"
"CREATE VIEW sys.describe_comments AS\n"
" SELECT\n"
" o.id id,\n"
@@ -3609,7 +3634,7 @@ sql_update_jan2022(Client c, mvc *sql, c
" 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, sys.FQN(s.name, f.name) FROM sys.functions f,
sys.function_types ft, sys.schemas s WHERE f.type = ft.function_type_id AND
f.schema_id = s.id\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 WHERE 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 c ON c.id = o.id;\n"
"CREATE VIEW sys.describe_privileges
AS\n"
@@ -3800,7 +3825,7 @@ sql_update_jan2022(Client c, mvc *sql, c
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true
where system <> true and name in ('sq', 'fqn',
'get_merge_table_partition_expressions', 'get_remote_table_expressions',
'schema_guard') and schema_id = 2000 and type = %d;\n", F_FUNC);
pos += snprintf(buf + pos, bufsize - pos,
- "update sys._tables set system = true where
name in ('describe_constraints', 'describe_tables', 'describe_comments',
'describe_privileges', 'describe_partition_tables', 'describe_sequences',
'describe_functions') AND schema_id = 2000;\n");
+ "update sys._tables set system = true where
name in ('describe_constraints', 'describe_tables',
'fully_qualified_functions', 'describe_comments', 'describe_privileges',
'describe_partition_tables', 'describe_sequences', 'describe_functions') AND
schema_id = 2000;\n");
/* 76_dump.sql (most everything already dropped) */
pos += snprintf(buf + pos, bufsize - pos,
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
@@ -314,6 +314,29 @@ CREATE VIEW sys.describe_triggers AS
FROM sys.schemas s, sys.tables t, sys.triggers tr
WHERE s.id = t.schema_id AND t.id = tr.table_id AND NOT
t.system;
+CREATE VIEW sys.fully_qualified_functions AS
+ WITH fqn(id, tpe, sig, num) AS
+ (
+ SELECT
+ f.id,
+ ft.function_type_keyword,
+ CASE WHEN a.type IS NULL THEN
+ sys.fqn(s.name, f.name) || '()'
+ ELSE
+ sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
+ END,
+ a.number
+ FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
+ WHERE s.id= f.schema_id AND f.type = ft.function_type_id
+ )
+ SELECT
+ fqn1.id id,
+ fqn1.tpe tpe,
+ fqn1.sig nme
+ FROM
+ fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
+ 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,
@@ -343,34 +366,11 @@ CREATE VIEW sys.describe_comments AS
UNION ALL
- SELECT f.id, ft.function_type_keyword, sys.FQN(s.name,
f.name) FROM sys.functions f, sys.function_types ft, sys.schemas s WHERE f.type
= ft.function_type_id AND f.schema_id = s.id
+ 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;
-CREATE VIEW sys.fully_qualified_functions AS
- WITH fqn(id, tpe, sig, num) AS
- (
- SELECT
- f.id,
- ft.function_type_keyword,
- CASE WHEN a.type IS NULL THEN
- s.name || '.' || f.name || '()'
- ELSE
- s.name || '.' || f.name || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
- END,
- a.number
- FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
- WHERE s.id= f.schema_id AND f.type = ft.function_type_id
- )
- SELECT
- fqn1.id id,
- fqn1.tpe tpe,
- fqn1.sig nme
- FROM
- fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
- ON fqn1.id = fqn2.id AND (fqn1.num = fqn2.num OR fqn1.num IS
NULL AND fqn2.num is NULL);
-
CREATE VIEW sys.describe_privileges AS
SELECT
CASE
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
----
-648 values hashing to a0dd41998f637915834fcc0f7ec4984e
+651 values hashing to fcf545c1dde0da41e4ac6e7dd57e59fb
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
----
-2274 values hashing to 4f9b4426928c74b12499a7975579a842
+2277 values hashing to be0394cfd4bbe2f3fb9af9fb43087e76
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
@@ -68,7 +68,7 @@ statement ok
COMMENT ON PROCEDURE g() IS 'proc'
query T nosort
-select stmt from sys.dump_database(false) order by o
+select stmt from sys.dump_database(false) order by o, stmt
----
START TRANSACTION;
SET SCHEMA "sys";
@@ -84,16 +84,16 @@ create "foo"."f" unction "foo"."f" (i in
create procedure "foo"."g" () be "foo"."g" in delete from tab where false;
end@;
UPDATE sys.sequences seq SET start = 1 WHERE name = 'counter' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'foo');
CREATE INDEX "idx" ON "foo"."tab"(j,i);
-COMMENT ON SCHEMA "foo" IS 'foo foo';
-COMMENT ON TABLE "foo"."tab" IS 'table';
-COMMENT ON VIEW "foo"."vivi" IS 'phew';
COMMENT ON COLUMN "foo"."tab"."i" IS 'ii';
COMMENT ON COLUMN "foo"."tab"."j" IS 'jj';
+COMMENT ON FUNCTION "foo"."f"(INTEGER) IS '0 parms';
+COMMENT ON FUNCTION "foo"."f"(INTEGER,INTEGER) IS '1 parm';
+COMMENT ON FUNCTION "foo"."f"(INTEGER,INTEGER,INTEGER) IS '2 parms';
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 SEQUENCE "foo"."counter" IS 'counting';
-COMMENT ON FUNCTION "foo"."f" IS '2 parms';
-COMMENT ON FUNCTION "foo"."f" IS '1 parm';
-COMMENT ON FUNCTION "foo"."f" IS '0 parms';
-COMMENT ON PROCEDURE "foo"."g" IS 'proc';
+COMMENT ON TABLE "foo"."tab" IS 'table';
+COMMENT ON VIEW "foo"."vivi" IS 'phew';
COMMIT;
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -5200,6 +5200,7 @@ drop view sys.dump_create_roles;
drop view sys.describe_functions;
drop view sys.describe_partition_tables;
drop view sys.describe_privileges;
+drop view sys.fully_qualified_functions;
drop view sys.describe_comments;
drop view sys.describe_tables;
drop view sys.describe_sequences;
@@ -5280,6 +5281,28 @@ CREATE VIEW sys.describe_tables AS
AND s.id = t.schema_id
AND ts.table_type_id = t.type
AND s.name <> 'tmp';
+CREATE VIEW sys.fully_qualified_functions AS
+ WITH fqn(id, tpe, sig, num) AS
+ (
+ SELECT
+ f.id,
+ ft.function_type_keyword,
+ CASE WHEN a.type IS NULL THEN
+ sys.fqn(s.name, f.name) || '()'
+ ELSE
+ sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
+ END,
+ a.number
+ FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
+ WHERE s.id= f.schema_id AND f.type = ft.function_type_id
+ )
+ SELECT
+ fqn1.id id,
+ fqn1.tpe tpe,
+ fqn1.sig nme
+ FROM
+ fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
+ 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,
@@ -5299,7 +5322,7 @@ CREATE VIEW sys.describe_comments AS
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, sys.FQN(s.name,
f.name) FROM sys.functions f, sys.function_types ft, sys.schemas s WHERE f.type
= ft.function_type_id AND f.schema_id = s.id
+ 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;
CREATE VIEW sys.describe_privileges AS
@@ -5488,7 +5511,7 @@ GRANT SELECT ON sys.describe_partition_t
GRANT SELECT ON sys.describe_sequences TO PUBLIC;
GRANT SELECT ON sys.describe_functions TO PUBLIC;
update sys.functions set system = true where system <> true and name in ('sq',
'fqn', 'get_merge_table_partition_expressions', 'get_remote_table_expressions',
'schema_guard') and schema_id = 2000 and type = 1;
-update sys._tables set system = true where name in ('describe_constraints',
'describe_tables', 'describe_comments', 'describe_privileges',
'describe_partition_tables', 'describe_sequences', 'describe_functions') AND
schema_id = 2000;
+update sys._tables set system = true where name in ('describe_constraints',
'describe_tables', 'fully_qualified_functions', 'describe_comments',
'describe_privileges', 'describe_partition_tables', 'describe_sequences',
'describe_functions') AND schema_id = 2000;
CREATE VIEW sys.dump_create_roles AS
SELECT
'CREATE ROLE ' || sys.dq(name) || ';' stmt,
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5271,6 +5271,7 @@ drop view sys.dump_create_roles;
drop view sys.describe_functions;
drop view sys.describe_partition_tables;
drop view sys.describe_privileges;
+drop view sys.fully_qualified_functions;
drop view sys.describe_comments;
drop view sys.describe_tables;
drop view sys.describe_sequences;
@@ -5351,6 +5352,28 @@ CREATE VIEW sys.describe_tables AS
AND s.id = t.schema_id
AND ts.table_type_id = t.type
AND s.name <> 'tmp';
+CREATE VIEW sys.fully_qualified_functions AS
+ WITH fqn(id, tpe, sig, num) AS
+ (
+ SELECT
+ f.id,
+ ft.function_type_keyword,
+ CASE WHEN a.type IS NULL THEN
+ sys.fqn(s.name, f.name) || '()'
+ ELSE
+ sys.fqn(s.name, f.name) || '(' ||
group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ',') OVER
(PARTITION BY f.id ORDER BY a.number) || ')'
+ END,
+ a.number
+ FROM sys.schemas s, sys.function_types ft, sys.functions f LEFT
JOIN sys.args a ON f.id = a.func_id
+ WHERE s.id= f.schema_id AND f.type = ft.function_type_id
+ )
+ SELECT
+ fqn1.id id,
+ fqn1.tpe tpe,
+ fqn1.sig nme
+ FROM
+ fqn fqn1 JOIN (SELECT id, max(num) FROM fqn GROUP BY id)
fqn2(id, num)
+ 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,
@@ -5370,7 +5393,7 @@ CREATE VIEW sys.describe_comments AS
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, sys.FQN(s.name,
f.name) FROM sys.functions f, sys.function_types ft, sys.schemas s WHERE f.type
= ft.function_type_id AND f.schema_id = s.id
+ 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;
CREATE VIEW sys.describe_privileges AS
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
