Changeset: 05363a87418d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/05363a87418d
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.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: mangled
Log Message:
Fix missing dependencies in upgrade.
diffs (truncated from 334 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
@@ -4650,6 +4650,226 @@ sql_update_jan2022(Client c, mvc *sql, c
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_update_default(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
+{
+ size_t bufsize = 65536, pos = 0;
+ char *buf = NULL, *err = NULL;
+ res_table *output;
+
+ if ((buf = GDKmalloc(bufsize)) == NULL)
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "select c.id from schemas s, tables t, columns c,
functions f, dependencies d \n"
+ "where s.id = t.schema_id and t.id = c.table_id and
d.id = c.id and d.depend_id = f.id and f.name = 'describe_function' and s.name
= 'sys' and t.name = 'functions' and c.name = 'mangled_name';\n");
+ err = SQLstatementIntern(c, buf, "update", 1, 0, &output);
+ if (err) {
+ GDKfree(buf);
+ return err;
+ }
+ BAT* b = BATdescriptor(output->cols[0].b);
+ if (b) {
+ if (BATcount(b)) {
+ assert(BATcount(b) == 1);
+ BBPunfix(b->batCacheid);
+ /* nothing to do */
+ GDKfree(buf);
+ return NULL;
+ }
+ BBPunfix(b->batCacheid);
+ }
+ else
+ throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
+
+ if (!*systabfixed && (err = sql_fix_system_tables(c, sql, prev_schema))
!= NULL)
+ return err;
+ *systabfixed = true;
+
+ pos = snprintf(buf, bufsize, "SET SCHEMA \"sys\";\n");
+
+ sql_schema *s = mvc_bind_schema(sql, "sys");
+ sql_table* t = NULL;
+
+ t = mvc_bind_table(sql, s, "describe_privileges");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_comments");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_comments");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "fully_qualified_functions");
+ t->system = 0;
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "DROP VIEW sys.describe_privileges;\n"
+ "DROP FUNCTION sys.dump_database(BOOLEAN);\n"
+ "DROP VIEW sys.dump_comments;\n"
+ "DROP VIEW sys.describe_comments;\n"
+ "DROP VIEW sys.fully_qualified_functions;\n"
+ "DROP FUNCTION sys.describe_function(STRING,
STRING);\n"
+ "CREATE FUNCTION
sys.describe_function(schemaName string, functionName string)\n"
+ " RETURNS TABLE(id integer, name string,
type string, language string, remark string)\n"
+ "BEGIN\n"
+ " RETURN SELECT f.id, f.name,
ft.function_type_keyword, fl.language_keyword, c.remark\n"
+ " FROM sys.functions f\n"
+ " JOIN sys.schemas s ON
f.schema_id = s.id\n"
+ " JOIN sys.function_types ft ON
f.type = ft.function_type_id\n"
+ " LEFT OUTER JOIN
sys.function_languages fl ON f.language = fl.language_id\n"
+ " LEFT OUTER JOIN sys.comments c
ON f.id = c.id\n"
+ " WHERE f.name=functionName AND
s.name = schemaName;\n"
+ "END;\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_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"
+ "UPDATE sys._tables SET system = true WHERE\n"
+ " system <> true AND\n"
+ " schema_id = 2000 AND\n"
+ " type = (SELECT table_type_id
FROM sys.table_types WHERE table_type_name = 'SYSTEM VIEW') AND\n"
+ " name in (\n"
+ "
'describe_privileges',\n"
+ "
'fully_qualified_functions'\n"
+ " );\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 FUNCTION sys.dump_database(describe
BOOLEAN) RETURNS TABLE(o int, stmt STRING)\n"
+ "BEGIN\n"
+ "\n"
+ " SET SCHEMA sys;\n"
+ " TRUNCATE sys.dump_statements;\n"
+ "\n"
+ " INSERT INTO sys.dump_statements VALUES (1,
'START TRANSACTION;');\n"
+ " INSERT INTO sys.dump_statements VALUES
((SELECT COUNT(*) FROM sys.dump_statements) + 1, '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"
+ "\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\n"
+ " SELECT
t.o, t.stmt FROM sys.dump_tables t\n"
+ " ) AS
stmts(o, s);\n"
+ "\n"
+ " -- dump table data before adding constraints
and fixing sequences\n"
+ " IF NOT DESCRIBE THEN\n"
+ " CALL sys.dump_table_data();\n"
+ " END IF;\n"
+ "\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"
+ "\n"
+ " --TODO Improve performance of
dump_table_data.\n"
+ " --TODO loaders ,procedures, window and
filter sys.functions.\n"
+ " --TODO look into order dependent
group_concat\n"
+ "\n"
+ " INSERT INTO sys.dump_statements VALUES
((SELECT COUNT(*) FROM sys.dump_statements) + 1, 'COMMIT;');\n"
+ "\n"
+ " RETURN sys.dump_statements;\n"
+ "END;\n"
+ "UPDATE sys.functions SET system = TRUE WHERE
system <> true AND schema_id = 2000 AND name = 'describe_function';\n"
+ "GRANT SELECT ON sys.fully_qualified_functions
TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_comments TO
PUBLIC;\n"
+ "GRANT SELECT ON sys.dump_comments TO PUBLIC;\n"
+ "GRANT SELECT ON sys.describe_privileges TO
PUBLIC;\n"
+ "GRANT EXECUTE ON FUNCTION
sys.describe_function(STRING, STRING) TO PUBLIC;\n"
+ );
+
+ assert(pos < bufsize);
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, buf, "update", true, false, NULL);
+
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+
int
SQLupgrades(Client c, mvc *m)
{
@@ -4855,6 +5075,13 @@ SQLupgrades(Client c, mvc *m)
return -1;
}
+ if ((err = sql_update_default(c, m, prev_schema, &systabfixed)) !=
NULL) {
+ TRC_CRITICAL(SQL_PARSER, "%s\n", err);
+ freeException(err);
+ GDKfree(prev_schema);
+ return -1;
+ }
+
GDKfree(prev_schema);
return 0;
}
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
@@ -345,29 +345,18 @@ CREATE VIEW sys.describe_comments AS
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;
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
@@ -4407,6 +4407,7 @@ select 'null in value_partitions.value',
[ "column used by function", "sys", "functions", "func", "sys",
"describe_function", "FUNCTION" ]
[ "column used by function", "sys", "functions", "id", "sys",
"describe_function", "FUNCTION" ]
[ "column used by function", "sys", "functions", "language", "sys",
"describe_function", "FUNCTION" ]
+[ "column used by function", "sys", "functions", "mangled_name", "sys",
"describe_function", "FUNCTION" ]
[ "column used by function", "sys", "functions", "mod", "sys",
"describe_function", "FUNCTION" ]
[ "column used by function", "sys", "functions", "name", "sys",
"describe_function", "FUNCTION" ]
[ "column used by function", "sys", "functions", "schema_id", "sys",
"describe_function", "FUNCTION" ]
@@ -5433,6 +5434,7 @@ select 'null in value_partitions.value',
[ "column used by view", "sys", "functions", "func", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "id", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "language", "sys",
"fully_qualified_functions", "VIEW" ]
+[ "column used by view", "sys", "functions", "mangled_name", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "mod", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "name", "sys",
"fully_qualified_functions", "VIEW" ]
[ "column used by view", "sys", "functions", "schema_id", "sys",
"fully_qualified_functions", "VIEW" ]
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
@@ -4406,6 +4406,7 @@ select 'null in value_partitions.value',
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
