Changeset: 9eed40e78256 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/9eed40e78256
Modified Files:
sql/backends/monet5/sql_upgrades.c
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/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: default
Log Message:
Upgrade code.
diffs (truncated from 12184 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
@@ -3496,17 +3496,9 @@ sql_update_default(Client c, mvc *sql, c
pos = snprintf(buf, bufsize, "set schema \"sys\";\n");
- /* 17_temporal.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function sys.epoch(bigint);\n");
+ /* sys.epoch_ms now returns a decimal(18,3) */
pos += snprintf(buf + pos, bufsize - pos,
- "create function sys.epoch(sec
DECIMAL(18,3)) "
- "returns TIMESTAMP WITH TIME ZONE\n"
- "external name mtime.epoch;\n"
- "grant execute on function sys.epoch
(DECIMAL(18,3)) to public;\n"
- "update sys.functions set system = true
where system <> true and name in ('epoch') and schema_id = 2000 and type =
%d;\n", F_FUNC);
-
- pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
+ "update sys.args set type = 'decimal',
type_digits = 18, type_scale = 3 where func_id in (select id from sys.functions
where name = 'epoch_ms' and schema_id = 2000) and number = 0 and type =
'bigint';\n");
/* 16_tracelog */
t = mvc_bind_table(sql, s, "tracelog");
@@ -3527,6 +3519,660 @@ sql_update_default(Client c, mvc *sql, c
"update sys.functions set system = true where system <>
true and schema_id = 2000"
" and name = 'tracelog' and type = %d;\n", (int)
F_UNION);
+ /* 17_temporal.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop function sys.epoch(bigint);\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "create function sys.epoch(sec
DECIMAL(18,3)) "
+ "returns TIMESTAMP WITH TIME ZONE\n"
+ "external name mtime.epoch;\n"
+ "grant execute on function sys.epoch
(DECIMAL(18,3)) to public;\n"
+ "update sys.functions set system = true
where system <> true and name in ('epoch') and schema_id = 2000 and type =
%d;\n", F_FUNC);
+
+ pos += snprintf(buf + pos, bufsize - pos, "set schema \"%s\";\n",
prev_schema);
+
+ /* 52_describe.sql; but we need to drop most everything from
+ * 76_dump.sql first */
+ t = mvc_bind_table(sql, s, "dump_privileges");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_user_defined_types");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_comments");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_triggers");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_functions");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_start_sequences");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_sequences");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_partition_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_foreign_keys");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_column_defaults");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_indices");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_table_constraint_type");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_grant_user_privileges");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_add_schemas_to_users");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_create_schemas");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_create_users");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_create_roles");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_constraints");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_comments");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_privileges");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_partition_tables");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "describe_functions");
+ t->system = 0;
+ pos += snprintf(buf + pos, bufsize - pos,
+ /* drop dependant stuff from
76_dump.sql */
+ "drop function
sys.dump_database(boolean);\n"
+ "drop procedure
sys.dump_table_data();\n"
+ "drop procedure
sys._dump_table_data(string, string);\n"
+ "drop function sys.prepare_esc(string,
string);\n"
+ "drop function sys.esc(string);\n"
+ "drop view sys.dump_privileges;\n"
+ "drop view
sys.dump_user_defined_types;\n"
+ "drop view sys.dump_comments;\n"
+ "drop view sys.dump_triggers;\n"
+ "drop view sys.dump_tables;\n"
+ "drop view sys.dump_functions;\n"
+ "drop view sys.dump_start_sequences;\n"
+ "drop view sys.dump_sequences;\n"
+ "drop view sys.dump_partition_tables;\n"
+ "drop view sys.dump_foreign_keys;\n"
+ "drop view sys.dump_column_defaults;\n"
+ "drop view sys.dump_indices;\n"
+ "drop view
sys.dump_table_constraint_type;\n"
+ "drop view
sys.dump_grant_user_privileges;\n"
+ "drop view
sys.dump_add_schemas_to_users;\n"
+ "drop view sys.dump_create_schemas;\n"
+ "drop view sys.dump_create_users;\n"
+ "drop view sys.dump_create_roles;\n"
+
+ "drop view sys.describe_functions;\n"
+ "drop view
sys.describe_partition_tables;\n"
+ "drop view sys.describe_privileges;\n"
+ "drop view sys.describe_comments;\n"
+ "drop view sys.describe_tables;\n"
+ "drop function
sys.get_remote_table_expressions(string, string);\n"
+ "drop function
sys.get_merge_table_partition_expressions(int);\n"
+ "drop view sys.describe_constraints;\n"
+ "drop function sys.alter_table(string,
string);\n"
+ "drop function sys.sq(string);\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE FUNCTION sys.SQ (s STRING)
RETURNS STRING BEGIN RETURN '''' || sys.replace(s,'''','''''') || ''''; END;\n"
+ "CREATE FUNCTION sys.ALTER_TABLE(s
STRING, t STRING) RETURNS STRING BEGIN RETURN 'ALTER TABLE ' || sys.FQN(s, t);
END;\n"
+ "CREATE VIEW sys.describe_constraints
AS\n"
+ " SELECT\n"
+ " s.name sch,\n"
+ " t.name tbl,\n"
+ " kc.name col,\n"
+ " k.name con,\n"
+ " CASE k.type WHEN 0 THEN
'PRIMARY KEY' WHEN 1 THEN 'UNIQUE' END tpe\n"
+ " FROM sys.schemas s, sys._tables
t, sys.objects kc, sys.keys k\n"
+ " WHERE kc.id = k.id\n"
+ " AND k.table_id = t.id\n"
+ " AND s.id =
t.schema_id\n"
+ " AND t.system = FALSE\n"
+ " AND k.type in (0, 1);\n"
+ "CREATE FUNCTION
sys.get_merge_table_partition_expressions(tid INT) RETURNS STRING\n"
+ "BEGIN\n"
+ " RETURN\n"
+ " SELECT\n"
+ " CASE WHEN
tp.table_id IS NOT NULL THEN\n"
+ " '
PARTITION BY ' ||\n"
+ "
ifthenelse(bit_and(tp.type, 2) = 2, 'VALUES ', 'RANGE ') ||\n"
+ " CASE\n"
+ "
WHEN bit_and(tp.type, 4) = 4\n"
+ "
THEN 'ON ' || '(' || (SELECT sys.DQ(c.name) || ')' FROM sys.columns c WHERE
c.id = tp.column_id)\n"
+ "
ELSE 'USING ' || '(' || tp.expression || ')'\n"
+ " END\n"
+ " ELSE\n"
+ " ''\n"
+ " END\n"
+ " FROM (VALUES (tid))
t(id) LEFT JOIN sys.table_partitions tp ON t.id = tp.table_id;\n"
+ "END;\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"
+ "CREATE VIEW sys.describe_tables AS\n"
+ " SELECT\n"
+ " t.id o,\n"
+ " s.name sch,\n"
+ " t.name tab,\n"
+ " ts.table_type_name
typ,\n"
+ " (SELECT\n"
+ " ' (' ||\n"
+ " GROUP_CONCAT(\n"
+ "
sys.DQ(c.name) || ' ' ||\n"
+ "
sys.describe_type(c.type, c.type_digits, c.type_scale) ||\n"
+ "
ifthenelse(c.\"null\" = 'false', ' NOT NULL', '')\n"
+ " , ', ') ||
')'\n"
+ " FROM sys._columns c\n"
+ " WHERE c.table_id =
t.id) col,\n"
+ " CASE
ts.table_type_name\n"
+ " WHEN 'REMOTE
TABLE' THEN\n"
+ "
sys.get_remote_table_expressions(s.name, t.name)\n"
+ " WHEN 'MERGE
TABLE' THEN\n"
+ "
sys.get_merge_table_partition_expressions(t.id)\n"
+ " WHEN 'VIEW'
THEN\n"
+ "
sys.schema_guard(s.name, t.name, t.query)\n"
+ " ELSE\n"
+ " ''\n"
+ " END opt\n"
+ " FROM sys.schemas s,
sys.table_types ts, sys.tables t\n"
+ " WHERE ts.table_type_name IN
('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE TABLE', 'REPLICA TABLE')\n"
+ " AND t.system = FALSE\n"
+ " AND s.id =
t.schema_id\n"
+ " AND ts.table_type_id =
t.type\n"
+ " AND s.name <> 'tmp';\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, 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"
+ " ) AS o(id, tpe,
nme)\n"
+ " JOIN
sys.comments c ON c.id = o.id;\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_partition_tables AS\n"
+ " SELECT \n"
+ " m_sch,\n"
+ " m_tbl,\n"
+ " p_sch,\n"
+ " p_tbl,\n"
+ " CASE\n"
+ " WHEN p_raw_type
IS NULL THEN 'READ ONLY'\n"
+ " WHEN
(p_raw_type = 'VALUES' AND pvalues IS NULL) OR (p_raw_type = 'RANGE' AND
minimum IS NULL AND maximum IS NULL AND with_nulls) THEN 'FOR NULLS'\n"
+ " ELSE
p_raw_type\n"
+ " END AS tpe,\n"
+ " pvalues,\n"
+ " minimum,\n"
+ " maximum,\n"
+ " with_nulls\n"
+ " FROM \n"
+ " (WITH\n"
+ " tp(\"type\", table_id)
AS\n"
+ " (SELECT
ifthenelse((table_partitions.\"type\" & 2) = 2, 'VALUES', 'RANGE'),
table_partitions.table_id FROM sys.table_partitions),\n"
+ " subq(m_tid, p_mid,
\"type\", m_sch, m_tbl, p_sch, p_tbl) AS\n"
+ " (SELECT m_t.id, p_m.id,
m_t.\"type\", m_s.name, m_t.name, p_s.name, p_m.name\n"
+ " FROM sys.schemas m_s,
sys._tables m_t, sys.dependencies d, sys.schemas p_s, sys._tables p_m\n"
+ " WHERE m_t.\"type\" IN
(3, 6)\n"
+ " AND
m_t.schema_id = m_s.id\n"
+ " AND m_s.name <>
'tmp'\n"
+ " AND m_t.system
= FALSE\n"
+ " AND m_t.id =
d.depend_id\n"
+ " AND d.id =
p_m.id\n"
+ " AND
p_m.schema_id = p_s.id\n"
+ " ORDER BY m_t.id,
p_m.id)\n"
+ " SELECT\n"
+ " subq.m_sch,\n"
+ " subq.m_tbl,\n"
+ " subq.p_sch,\n"
+ " subq.p_tbl,\n"
+ " tp.\"type\" AS
p_raw_type,\n"
+ " CASE WHEN tp.\"type\" =
'VALUES'\n"
+ " THEN (SELECT
GROUP_CONCAT(vp.value, ',') FROM sys.value_partitions vp WHERE vp.table_id =
subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS pvalues,\n"
+ " CASE WHEN tp.\"type\" =
'RANGE'\n"
+ " THEN (SELECT
minimum FROM sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS minimum,\n"
+ " CASE WHEN tp.\"type\" =
'RANGE'\n"
+ " THEN (SELECT
maximum FROM sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " ELSE NULL\n"
+ " END AS maximum,\n"
+ " CASE WHEN tp.\"type\" =
'VALUES'\n"
+ " THEN
EXISTS(SELECT vp.value FROM sys.value_partitions vp WHERE vp.table_id =
subq.p_mid AND vp.value IS NULL)\n"
+ " ELSE (SELECT
rp.with_nulls FROM sys.range_partitions rp WHERE rp.table_id = subq.p_mid)\n"
+ " END AS with_nulls\n"
+ " FROM \n"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
