Changeset: 49790620d563 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=49790620d563
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/storage/bat/bat_logger.c
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
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
sql/test/emptydb-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.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.int128
Branch: default
Log Message:
More upgrade improvements.
diffs (truncated from 6706 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
@@ -1022,7 +1022,6 @@ sql_update_nov2019(Client c, mvc *sql, c
"create aggregate median_avg(val DOUBLE) returns
DOUBLE\n"
" external name \"aggr\".\"median_avg\";\n"
"GRANT EXECUTE ON AGGREGATE median_avg(DOUBLE) TO
PUBLIC;\n"
- "\n"
"create aggregate quantile_avg(val TINYINT, q DOUBLE)
returns DOUBLE\n"
" external name \"aggr\".\"quantile_avg\";\n"
"GRANT EXECUTE ON AGGREGATE quantile_avg(TINYINT,
DOUBLE) TO PUBLIC;\n"
@@ -2261,7 +2260,6 @@ sql_update_oct2020(Client c, mvc *sql, c
"DROP AGGREGATE corr(INTERVAL
MONTH,INTERVAL MONTH);\n"
"DROP WINDOW corr(INTERVAL
SECOND,INTERVAL SECOND);\n"
"DROP WINDOW corr(INTERVAL
MONTH,INTERVAL MONTH);\n"
- "\n"
"create aggregate median(val INTERVAL
DAY) returns INTERVAL DAY\n"
" external name \"aggr\".\"median\";\n"
"GRANT EXECUTE ON AGGREGATE
median(INTERVAL DAY) TO PUBLIC;\n"
@@ -2548,19 +2546,16 @@ sql_update_default(Client c, mvc *sql, c
" END\n"
" END;\n"
"END;\n"
- "\n"
"CREATE FUNCTION sys.SQ (s STRING)
RETURNS STRING BEGIN RETURN ' ''' || sys.replace(s,'''','''''') || ''' ';
END;\n"
"CREATE FUNCTION sys.DQ (s STRING)
RETURNS STRING BEGIN RETURN '\"' || sys.replace(s,'\"','\"\"') || '\"'; END;
--TODO: Figure out why this breaks with the space\n"
"CREATE FUNCTION sys.FQN(s STRING, t
STRING) RETURNS STRING BEGIN RETURN sys.DQ(s) || '.' || sys.DQ(t); END;\n"
"CREATE FUNCTION sys.ALTER_TABLE(s
STRING, t STRING) RETURNS STRING BEGIN RETURN 'ALTER TABLE ' || sys.FQN(s, t)
|| ' '; END;\n"
- "\n"
"--We need pcre to implement a header
guard which means adding the schema of an object explicitely to its
identifier.\n"
"CREATE FUNCTION sys.replace_first(ori
STRING, pat STRING, rep STRING, flg STRING) RETURNS STRING EXTERNAL NAME
\"pcre\".\"replace_first\";\n"
"CREATE FUNCTION sys.schema_guard(sch
STRING, nme STRING, stmt STRING) RETURNS STRING BEGIN\n"
"RETURN\n"
" SELECT sys.replace_first(stmt,
'(\\\\s*\"?' || sch || '\"?\\\\s*\\\\.|)\\\\s*\"?' || nme || '\"?\\\\s*', ' '
|| sys.FQN(sch, nme) || ' ', 'imsx');\n"
"END;\n"
- "\n"
"CREATE VIEW sys.describe_constraints
AS\n"
" SELECT\n"
" s.name sch,\n"
@@ -2575,7 +2570,6 @@ sql_update_default(Client c, mvc *sql, c
" AND t.system = FALSE\n"
" AND k.type in (0, 1)\n"
" AND t.type IN (0, 6);\n"
- "\n"
"CREATE VIEW sys.describe_indices AS\n"
" WITH it (id, idx) AS (VALUES (0,
'INDEX'), (4, 'IMPRINTS INDEX'), (5, 'ORDERED INDEX')) --UNIQUE INDEX wraps to
INDEX.\n"
" SELECT\n"
@@ -2600,7 +2594,6 @@ sql_update_default(Client c, mvc *sql, c
" AND k.type IS NULL\n"
" AND i.type = it.id\n"
" ORDER BY i.name, kc.nr;\n"
- "\n"
"CREATE VIEW
sys.describe_column_defaults AS\n"
" SELECT\n"
" s.name sch,\n"
@@ -2614,7 +2607,6 @@ sql_update_default(Client c, mvc *sql, c
" s.name <> 'tmp' AND\n"
" NOT t.system AND\n"
" c.\"default\" IS NOT NULL;\n"
- "\n"
"CREATE VIEW sys.describe_foreign_keys
AS\n"
" WITH action_type (id, act) AS
(VALUES\n"
" (0, 'NO ACTION'),\n"
@@ -2654,7 +2646,6 @@ sql_update_default(Client c, mvc *sql, c
" AND (fkk.\"action\" & 255)
= od.id\n"
" AND ((fkk.\"action\" >> 8) &
255) = ou.id\n"
" ORDER BY fkk.name, fkkc.nr;\n"
- "\n"
"--TODO: CRASHES when this function
gets inlined into describe_tables\n"
"CREATE FUNCTION
sys.get_merge_table_partition_expressions(tid INT) RETURNS STRING\n"
"BEGIN\n"
@@ -2677,12 +2668,10 @@ sql_update_default(Client c, mvc *sql, c
" END\n"
" FROM (VALUES (tid)) t(id) LEFT
JOIN sys.table_partitions tp ON t.id = tp.table_id;\n"
"END;\n"
- "\n"
"--TODO: gives mergejoin errors when
inlined\n"
"CREATE FUNCTION
sys.get_remote_table_expressions(s STRING, t STRING) RETURNS STRING BEGIN\n"
" RETURN SELECT ' ON ' ||
sys.SQ(uri) || ' WITH USER ' || sys.SQ(username) || ' ENCRYPTED PASSWORD ' ||
sys.SQ(\"hash\") FROM sys.remote_table_credentials(s ||'.' || t);\n"
"END;\n"
- "\n"
"CREATE VIEW sys.describe_tables AS\n"
" SELECT\n"
" t.id o,\n"
@@ -2714,7 +2703,6 @@ sql_update_default(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"
- "\n"
"CREATE VIEW sys.describe_triggers AS\n"
" SELECT\n"
" s.name sch,\n"
@@ -2723,7 +2711,6 @@ sql_update_default(Client c, mvc *sql, c
" tr.statement def\n"
" FROM sys.schemas s, sys.tables
t, sys.triggers tr\n"
" WHERE s.id = t.schema_id AND
t.id = tr.table_id AND NOT t.system;\n"
- "\n"
"CREATE VIEW sys.describe_comments AS\n"
" SELECT\n"
" o.id id,\n"
@@ -2732,32 +2719,20 @@ sql_update_default(Client c, mvc *sql, c
" c.remark rem\n"
" FROM (\n"
" SELECT id, 'SCHEMA',
sys.DQ(name) FROM sys.schemas\n"
- "\n"
" UNION ALL\n"
- "\n"
" SELECT t.id, CASE WHEN
ts.table_type_name = 'VIEW' THEN 'VIEW' ELSE 'TABLE' END, 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 s.name <>
'tmp'\n"
- "\n"
" UNION ALL\n"
- "\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"
- "\n"
" UNION ALL\n"
- "\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"
- "\n"
" UNION ALL\n"
- "\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"
- "\n"
" UNION ALL\n"
- "\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"
- "\n"
" ) AS o(id, tpe, nme)\n"
" JOIN sys.comments c ON
c.id = o.id;\n"
- "\n"
"CREATE VIEW
sys.fully_qualified_functions AS\n"
" WITH fqn(id, tpe, sig, num) AS\n"
" (\n"
@@ -2780,7 +2755,6 @@ 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"
- "\n"
"CREATE VIEW sys.describe_privileges
AS\n"
" SELECT\n"
" CASE\n"
@@ -2819,7 +2793,6 @@ sql_update_default(Client c, mvc *sql, c
" p.privileges =
pc.privilege_code_id AND\n"
" p.auth_id = a.id AND\n"
" p.grantor = g.id;\n"
- "\n"
"CREATE FUNCTION
sys.describe_table(schemaName string, tableName string)\n"
" RETURNS TABLE(name string, query
string, type string, id integer, remark string)\n"
"BEGIN\n"
@@ -2831,7 +2804,6 @@ sql_update_default(Client c, mvc *sql, c
" AND t.name = tableName\n"
" AND t.type =
tt.table_type_id;\n"
"END;\n"
- "\n"
"CREATE VIEW
sys.describe_user_defined_types AS\n"
" SELECT\n"
" s.name sch,\n"
@@ -2844,7 +2816,6 @@ sql_update_default(Client c, mvc *sql, c
" (s.name = 'sys' AND
t.sqlname not in ('geometrya', 'mbr', 'url', 'inet', 'json', 'uuid', 'xml'))
OR\n"
" (s.name <> 'sys')\n"
" );\n"
- "\n"
"CREATE VIEW
sys.describe_partition_tables AS\n"
" SELECT \n"
" m_sch,\n"
@@ -2900,7 +2871,6 @@ sql_update_default(Client c, mvc *sql, c
" FROM \n"
" subq LEFT OUTER JOIN tp\n"
" ON subq.m_tid = tp.table_id)
AS tmp_pi;\n"
- "\n"
"CREATE VIEW sys.describe_sequences
AS\n"
" SELECT\n"
" s.name as sch,\n"
@@ -2916,7 +2886,6 @@ sql_update_default(Client c, mvc *sql, c
" WHERE s.id = seq.schema_id\n"
" AND s.name <> 'tmp'\n"
" ORDER BY s.name, seq.name;\n"
- "\n"
"CREATE VIEW sys.describe_functions
AS\n"
" SELECT\n"
" f.id o,\n"
@@ -2924,7 +2893,6 @@ sql_update_default(Client c, mvc *sql, c
" f.name fun,\n"
" f.func def\n"
" FROM sys.functions f JOIN
sys.schemas s ON f.schema_id = s.id WHERE s.name <> 'tmp' AND NOT f.system;\n"
- "\n"
"CREATE FUNCTION
sys.describe_columns(schemaName string, tableName string)\n"
" RETURNS TABLE(name string, type
string, digits integer, scale integer, Nulls boolean, cDefault string, number
integer, sqltype string, remark string)\n"
"BEGIN\n"
@@ -2937,7 +2905,6 @@ sql_update_default(Client c, mvc *sql, c
" AND s.name = schemaName\n"
" ORDER BY c.number;\n"
"END;\n"
- "\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"
@@ -2950,6 +2917,51 @@ sql_update_default(Client c, mvc *sql, c
" WHERE f.name=functionName AND
s.name = schemaName;\n"
"END;\n");
+ /* 75_storagemodel.sql not changed but dependencies
changed
+ * since sys.objects has a new column */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop procedure
sys.storagemodelinit();\n"
+ "create procedure
sys.storagemodelinit()\n"
+ "begin\n"
+ " delete from
sys.storagemodelinput;\n"
+ " insert into
sys.storagemodelinput\n"
+ " select \"schema\", \"table\",
\"column\", \"type\", typewidth, \"count\",\n"
+ " -- assume all variable size
types contain distinct values\n"
+ " case when (\"unique\" or
\"type\" IN ('varchar', 'char', 'clob', 'json', 'url', 'blob', 'geometry',
'geometrya'))\n"
+ " then \"count\" else 0
end,\n"
+ " case when \"count\" > 0 and
heapsize >= 8192 and \"type\" in ('varchar', 'char', 'clob', 'json', 'url')\n"
+ " -- string heaps have a
header of 8192\n"
+ " then cast((heapsize -
8192) / \"count\" as bigint)\n"
+ " when \"count\" > 0 and
heapsize >= 32 and \"type\" in ('blob', 'geometry', 'geometrya')\n"
+ " -- binary data heaps have
a header of 32\n"
+ " then cast((heapsize - 32)
/ \"count\" as bigint)\n"
+ " else typewidth end,\n"
+ " FALSE, case sorted when true
then true else false end, \"unique\", TRUE\n"
+ " from sys.\"storage\"; -- view
sys.\"storage\" excludes system tables (as those are not useful to be modeled
for storagesize by application users)\n"
+ " update sys.storagemodelinput\n"
+ " set reference = TRUE\n"
+ " where (\"schema\", \"table\",
\"column\") in (\n"
+ " SELECT fkschema.\"name\",
fktable.\"name\", fkkeycol.\"name\"\n"
+ " FROM sys.\"keys\" AS
fkkey,\n"
+ " sys.\"objects\" AS
fkkeycol,\n"
+ " sys.\"tables\" AS
fktable,\n"
+ " sys.\"schemas\" AS
fkschema\n"
+ " WHERE fktable.\"id\" =
fkkey.\"table_id\"\n"
+ " AND fkkey.\"id\" =
fkkeycol.\"id\"\n"
+ " AND fkschema.\"id\" =
fktable.\"schema_id\"\n"
+ " AND fkkey.\"rkey\" > -1 );\n"
+ " update sys.storagemodelinput\n"
+ " set isacolumn = FALSE\n"
+ " where (\"schema\", \"table\",
\"column\") NOT in (\n"
+ " SELECT sch.\"name\",
tbl.\"name\", col.\"name\"\n"
+ " FROM sys.\"schemas\" AS
sch,\n"
+ " sys.\"tables\" AS tbl,\n"
+ " sys.\"columns\" AS col\n"
+ " WHERE sch.\"id\" =
tbl.\"schema_id\"\n"
+ " AND tbl.\"id\" =
col.\"table_id\");\n"
+ "end;\n"
+ "update sys.functions set system = true
where name = 'storagemodelinit' and schema_id = 2000;\n");
+
/* 76_dump.sql */
pos += snprintf(buf + pos, bufsize - pos,
"CREATE VIEW sys.dump_create_roles AS\n"
@@ -2957,7 +2969,6 @@ sql_update_default(Client c, mvc *sql, c
" 'CREATE ROLE ' || sys.dq(name)
|| ';' stmt FROM sys.auths\n"
" WHERE name NOT IN (SELECT name
FROM sys.db_user_info)\n"
" AND grantor <> 0;\n"
- "\n"
"CREATE VIEW sys.dump_create_users AS\n"
" SELECT\n"
" 'CREATE USER ' ||
sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' ||\n"
@@ -2967,13 +2978,11 @@ sql_update_default(Client c, mvc *sql, c
" WHERE ui.default_schema = s.id\n"
" AND ui.name <> 'monetdb'\n"
" AND ui.name <> '.snapshot';\n"
- "\n"
"CREATE VIEW sys.dump_create_schemas
AS\n"
" SELECT\n"
" 'CREATE SCHEMA ' ||
sys.dq(s.name) || ifthenelse(a.name <> 'sysadmin', ' AUTHORIZATION ' || a.name,
' ') || ';' stmt\n"
" FROM sys.schemas s, sys.auths a\n"
" WHERE s.authorization = a.id AND
s.system = FALSE;\n"
- "\n"
"CREATE VIEW
sys.dump_add_schemas_to_users AS\n"
" SELECT\n"
" 'ALTER USER ' ||
sys.dq(ui.name) || ' SET SCHEMA ' || sys.dq(s.name) || ';' stmt\n"
@@ -2982,31 +2991,26 @@ sql_update_default(Client c, mvc *sql, c
" AND ui.name <> 'monetdb'\n"
" AND ui.name <> '.snapshot'\n"
" AND s.name <> 'sys';\n"
- "\n"
"CREATE VIEW
sys.dump_grant_user_privileges AS\n"
" SELECT\n"
" 'GRANT ' || sys.dq(a2.name) ||
' ' || ifthenelse(a1.name = 'public', 'PUBLIC', sys.dq(a1.name)) || ';' stmt\n"
" FROM sys.auths a1, sys.auths a2,
sys.user_role ur\n"
" WHERE a1.id = ur.login_id AND
a2.id = ur.role_id;\n"
- "\n"
"CREATE VIEW
sys.dump_table_constraint_type AS\n"
" SELECT\n"
" 'ALTER TABLE ' || sys.DQ(sch)
|| '.' || sys.DQ(tbl) ||\n"
" ' ADD CONSTRAINT ' ||
sys.DQ(con) || ' '||\n"
" tpe || ' (' ||
GROUP_CONCAT(sys.DQ(col), ', ') || ');' stmt\n"
" FROM sys.describe_constraints
GROUP BY sch, tbl, con, tpe;\n"
- "\n"
"CREATE VIEW sys.dump_indices AS\n"
" SELECT\n"
" 'CREATE ' || tpe || ' ' ||\n"
" sys.DQ(ind) || ' ON ' ||
sys.DQ(sch) || '.' || sys.DQ(tbl) ||\n"
" '(' || GROUP_CONCAT(col) ||
');' stmt\n"
" FROM sys.describe_indices GROUP BY
ind, tpe, sch, tbl;\n"
- "\n"
"CREATE VIEW sys.dump_column_defaults
AS\n"
" SELECT 'ALTER TABLE ' ||
sys.FQN(sch, tbl) || ' ALTER COLUMN ' || sys.DQ(col) || ' SET DEFAULT ' || def
|| ';' stmt\n"
" FROM
sys.describe_column_defaults;\n"
- "\n"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
