Changeset: b34b0ebe43bd for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/b34b0ebe43bd
Modified Files:
sql/backends/monet5/sql_upgrades.c
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/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.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/dump.stable.out
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/dump.stable.out
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.int128
sql/test/testdb-upgrade-hge/Tests/dump.stable.out
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/dump.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: mangled
Log Message:
Fix some upgrades.
diffs (truncated from 2097 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
@@ -4717,41 +4717,19 @@ sql_update_default(Client c, mvc *sql, c
pos += snprintf(buf + pos, bufsize - pos,
"DROP FUNCTION sys.dump_database;\n"
"DROP VIEW sys.dump_comments;\n"
- "CREATE OR REPLACE VIEW sys.describe_comments AS\n"
- "SELECT\n"
- "o.id id,\n"
- "o.tpe tpe,\n"
- "o.nme fqn,\n"
- "c.remark rem\n"
- "FROM (\n"
- "SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas\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 s.name <> 'tmp'\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
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 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, sys.FQN(s.name,
f.sqlname) 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"
- ") AS o(id, tpe, nme)\n"
- "JOIN sys.comments c ON c.id = o.id;\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"
+ "DROP VIEW sys.describe_comments;\n"
"DROP VIEW sys.describe_privileges;\n"
- "CREATE OR REPLACE VIEW sys.fully_qualified_functions AS\n"
+ "DROP VIEW sys.fully_qualified_functions;\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"
- "s.name || '.' || f.sqlname ||
'()'\n"
+ "sys.fqn(s.name, f.sqlname) ||
'()'\n"
"ELSE\n"
- "s.name || '.' || f.sqlname ||
'(' || group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale),
',') OVER (PARTITION BY f.id ORDER BY a.number) || ')'\n"
+ "sys.fqn(s.name, f.sqlname) ||
'(' || 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"
@@ -4764,41 +4742,66 @@ sql_update_default(Client c, mvc *sql, c
"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_privileges AS\n"
- "SELECT\n"
- "CASE\n"
- "WHEN o.tpe IS NULL AND
pc.privilege_code_name = 'SELECT' THEN --GLOBAL privileges: SELECT maps to COPY
FROM\n"
- "'COPY FROM'\n"
- "WHEN o.tpe IS NULL AND
pc.privilege_code_name = 'UPDATE' THEN --GLOBAL privileges: UPDATE maps to COPY
INTO\n"
- "'COPY INTO'\n"
- "ELSE\n"
- "o.nme\n"
- "END o_nme,\n"
- "coalesce(o.tpe, 'GLOBAL') o_tpe,\n"
- "pc.privilege_code_name p_nme,\n"
- "a.name a_nme,\n"
- "g.name g_nme,\n"
- "p.grantable grantable\n"
- "FROM\n"
- "sys.privileges p LEFT JOIN\n"
- "(\n"
- "SELECT t.id, s.name || '.' || t.name
, 'TABLE'\n"
- "from sys.schemas s,
sys.tables t where s.id = t.schema_id\n"
- "UNION ALL\n"
- "SELECT c.id, s.name || '.' ||
t.name || '.' || c.name, 'COLUMN'\n"
- "FROM sys.schemas s,
sys.tables t, sys.columns c where s.id = t.schema_id AND t.id = c.table_id\n"
- "UNION ALL\n"
- "SELECT f.id, f.nme, f.tpe\n"
- "FROM
sys.fully_qualified_functions f\n"
- ") o(id, nme, tpe) ON o.id =
p.obj_id,\n"
- "sys.privilege_codes pc,\n"
- "auths a, auths g\n"
- "WHERE\n"
- "p.privileges = pc.privilege_code_id
AND\n"
- "p.auth_id = a.id AND\n"
- "p.grantor = g.id;\n"
+ "CREATE VIEW sys.describe_comments AS\n"
+ "SELECT\n"
+ "o.id id,\n"
+ "o.tpe tpe,\n"
+ "o.nme fqn,\n"
+ "c.remark rem\n"
+ "FROM (\n"
+ "SELECT id, 'SCHEMA', sys.DQ(name) FROM
sys.schemas\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 s.name <> 'tmp'\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
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 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 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.dump_comments AS\n"
+ "SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS
' || sys.SQ(c.rem) || ';' stmt FROM sys.describe_comments c;\n"
+ "CREATE VIEW sys.describe_privileges AS\n"
+ "SELECT\n"
+ "CASE\n"
+ "WHEN o.tpe IS NULL AND
pc.privilege_code_name = 'SELECT' THEN --GLOBAL privileges: SELECT maps to COPY
FROM\n"
+ "'COPY FROM'\n"
+ "WHEN o.tpe IS NULL AND
pc.privilege_code_name = 'UPDATE' THEN --GLOBAL privileges: UPDATE maps to COPY
INTO\n"
+ "'COPY INTO'\n"
+ "ELSE\n"
+ "o.nme\n"
+ "END o_nme,\n"
+ "coalesce(o.tpe, 'GLOBAL') o_tpe,\n"
+ "pc.privilege_code_name p_nme,\n"
+ "a.name a_nme,\n"
+ "g.name g_nme,\n"
+ "p.grantable grantable\n"
+ "FROM\n"
+ "sys.privileges p LEFT JOIN\n"
+ "(\n"
+ "SELECT t.id, s.name || '.' || t.name ,
'TABLE'\n"
+ "from sys.schemas s, sys.tables t
where s.id = t.schema_id\n"
+ "UNION ALL\n"
+ "SELECT c.id, s.name || '.' || t.name
|| '.' || c.name, 'COLUMN'\n"
+ "FROM sys.schemas s, sys.tables t,
sys.columns c where s.id = t.schema_id AND t.id = c.table_id\n"
+ "UNION ALL\n"
+ "SELECT f.id, f.nme, f.tpe\n"
+ "FROM sys.fully_qualified_functions
f\n"
+ ") o(id, nme, tpe) ON o.id = p.obj_id,\n"
+ "sys.privilege_codes pc,\n"
+ "auths a, auths g\n"
+ "WHERE\n"
+ "p.privileges = pc.privilege_code_id AND\n"
+ "p.auth_id = a.id AND\n"
+ "p.grantor = g.id;\n"
"DROP VIEW dump_functions;\n"
- "CREATE OR REPLACE VIEW sys.describe_functions AS\n"
+ "DROP VIEW sys.describe_functions;\n"
+ "CREATE VIEW sys.describe_functions AS\n"
"WITH func_args_all(func_id, number, max_number,
func_arg) AS\n"
"(\n"
"SELECT\n"
@@ -4866,7 +4869,8 @@ sql_update_default(Client c, mvc *sql, c
"WHERE f.sqlname=functionName AND
s.name = schemaName;\n"
"END;\n"
"UPDATE sys.functions SET system = true WHERE system = false
AND sqlname in ('describe_function') and schema_id = 2000;\n"
- "CREATE OR REPLACE VIEW sys.dump_function_grants AS\n"
+ "DROP VIEW sys.dump_function_grants;\n"
+ "CREATE VIEW sys.dump_function_grants AS\n"
"WITH func_args_all(func_id, number, max_number,
func_arg) AS\n"
"(SELECT a.func_id,\n"
"a.number,\n"
@@ -4950,7 +4954,10 @@ sql_update_default(Client c, mvc *sql, c
"'dump_function_grants'\n"
");\n"
"update sys.functions set system = true where system <> true
and sqlname = 'dump_database' and schema_id = 2000;\n"
-
+ "GRANT SELECT ON sys.fully_qualified_functions TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_comments TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_privileges TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_functions TO PUBLIC;\n"
);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
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
@@ -5279,7 +5279,32 @@ Running database upgrade commands:
set schema "sys";
DROP FUNCTION sys.dump_database;
DROP VIEW sys.dump_comments;
-CREATE OR REPLACE VIEW sys.describe_comments AS
+DROP VIEW sys.describe_comments;
+DROP VIEW sys.describe_privileges;
+DROP VIEW sys.fully_qualified_functions;
+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.sqlname) || '()'
+ELSE
+sys.fqn(s.name, f.sqlname) || '(' || 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,
o.tpe tpe,
@@ -5298,34 +5323,11 @@ SELECT idx.id, 'INDEX', sys.FQN(s.name,
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.sqlname) 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.dump_comments AS
SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' || sys.SQ(c.rem) ||
';' stmt FROM sys.describe_comments c;
-DROP VIEW sys.describe_privileges;
-CREATE OR REPLACE 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.sqlname || '()'
-ELSE
-s.name || '.' || f.sqlname || '(' || 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
@@ -5360,7 +5362,8 @@ p.privileges = pc.privilege_code_id AND
p.auth_id = a.id AND
p.grantor = g.id;
DROP VIEW dump_functions;
-CREATE OR REPLACE VIEW sys.describe_functions AS
+DROP VIEW sys.describe_functions;
+CREATE VIEW sys.describe_functions AS
WITH func_args_all(func_id, number, max_number, func_arg) AS
(
SELECT
@@ -5428,7 +5431,8 @@ LEFT OUTER JOIN sys.comments c ON f.id =
WHERE f.sqlname=functionName AND s.name = schemaName;
END;
UPDATE sys.functions SET system = true WHERE system = false AND sqlname in
('describe_function') and schema_id = 2000;
-CREATE OR REPLACE VIEW sys.dump_function_grants AS
+DROP VIEW sys.dump_function_grants;
+CREATE VIEW sys.dump_function_grants AS
WITH func_args_all(func_id, number, max_number, func_arg) AS
(SELECT a.func_id,
a.number,
@@ -5512,4 +5516,8 @@ UPDATE sys._tables SET system = true WHE
'dump_function_grants'
);
update sys.functions set system = true where system <> true and sqlname =
'dump_database' and schema_id = 2000;
+GRANT SELECT ON sys.fully_qualified_functions TO PUBLIC;
+GRANT SELECT ON sys.describe_comments TO PUBLIC;
+GRANT SELECT ON sys.describe_privileges TO PUBLIC;
+GRANT SELECT ON sys.describe_functions TO PUBLIC;
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
@@ -5350,7 +5350,32 @@ Running database upgrade commands:
set schema "sys";
DROP FUNCTION sys.dump_database;
DROP VIEW sys.dump_comments;
-CREATE OR REPLACE VIEW sys.describe_comments AS
+DROP VIEW sys.describe_comments;
+DROP VIEW sys.describe_privileges;
+DROP VIEW sys.fully_qualified_functions;
+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.sqlname) || '()'
+ELSE
+sys.fqn(s.name, f.sqlname) || '(' || 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
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
