Changeset: 3d2a7c11f996 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/3d2a7c11f996
Modified Files:
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit
Branch: default
Log Message:
32 bit approvals.
diffs (truncated from 3094 to 300 lines):
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -2738,7 +2738,7 @@ insert into sys.functions values (737, '
insert into sys.args values (38553, 737, 'res_0', 'int', 32, 0, 0, 0);
insert into sys.args values (38554, 737, 'arg_1', 'date', 0, 0, 1, 1);
insert into sys.functions values (738, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38555, 738, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38555, 738, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38556, 738, 'arg_1', 'date', 0, 0, 1, 1);
insert into sys.functions values (739, 'hour', 'hours', 'mtime', 0, 1, false,
false, false, 2000, true, false);
insert into sys.args values (38557, 739, 'res_0', 'int', 32, 0, 0, 0);
@@ -2750,7 +2750,7 @@ insert into sys.functions values (741, '
insert into sys.args values (38561, 741, 'res_0', 'decimal', 9, 6, 0, 0);
insert into sys.args values (38562, 741, 'arg_1', 'time', 7, 0, 1, 1);
insert into sys.functions values (742, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38563, 742, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38563, 742, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38564, 742, 'arg_1', 'time', 7, 0, 1, 1);
insert into sys.functions values (743, 'hour', 'hours', 'mtime', 0, 1, false,
false, false, 2000, true, false);
insert into sys.args values (38565, 743, 'res_0', 'int', 32, 0, 0, 0);
@@ -2762,7 +2762,7 @@ insert into sys.functions values (745, '
insert into sys.args values (38569, 745, 'res_0', 'decimal', 9, 6, 0, 0);
insert into sys.args values (38570, 745, 'arg_1', 'timetz', 7, 0, 1, 1);
insert into sys.functions values (746, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38571, 746, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38571, 746, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38572, 746, 'arg_1', 'timetz', 7, 0, 1, 1);
insert into sys.functions values (747, 'century', 'century', 'mtime', 0, 1,
false, false, false, 2000, true, false);
insert into sys.args values (38573, 747, 'res_0', 'int', 32, 0, 0, 0);
@@ -2792,7 +2792,7 @@ insert into sys.functions values (755, '
insert into sys.args values (38589, 755, 'res_0', 'decimal', 9, 6, 0, 0);
insert into sys.args values (38590, 755, 'arg_1', 'timestamp', 7, 0, 1, 1);
insert into sys.functions values (756, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38591, 756, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38591, 756, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38592, 756, 'arg_1', 'timestamp', 7, 0, 1, 1);
insert into sys.functions values (757, 'century', 'century', 'mtime', 0, 1,
false, false, false, 2000, true, false);
insert into sys.args values (38593, 757, 'res_0', 'int', 32, 0, 0, 0);
@@ -2822,7 +2822,7 @@ insert into sys.functions values (765, '
insert into sys.args values (38609, 765, 'res_0', 'decimal', 9, 6, 0, 0);
insert into sys.args values (38610, 765, 'arg_1', 'timestamptz', 7, 0, 1, 1);
insert into sys.functions values (766, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38611, 766, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38611, 766, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38612, 766, 'arg_1', 'timestamptz', 7, 0, 1, 1);
insert into sys.functions values (767, 'year', 'year', 'mtime', 0, 1, false,
false, false, 2000, true, false);
insert into sys.args values (38613, 767, 'res_0', 'int', 32, 0, 0, 0);
@@ -2843,7 +2843,7 @@ insert into sys.functions values (772, '
insert into sys.args values (38623, 772, 'res_0', 'int', 32, 0, 0, 0);
insert into sys.args values (38624, 772, 'arg_1', 'day_interval', 4, 0, 1, 1);
insert into sys.functions values (773, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38625, 773, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38625, 773, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38626, 773, 'arg_1', 'day_interval', 4, 0, 1, 1);
insert into sys.functions values (774, 'day', 'day', 'mtime', 0, 1, false,
false, false, 2000, true, false);
insert into sys.args values (38627, 774, 'res_0', 'bigint', 64, 0, 0, 0);
@@ -2858,7 +2858,7 @@ insert into sys.functions values (777, '
insert into sys.args values (38633, 777, 'res_0', 'int', 32, 0, 0, 0);
insert into sys.args values (38634, 777, 'arg_1', 'sec_interval', 13, 0, 1, 1);
insert into sys.functions values (778, 'epoch_ms', 'epoch_ms', 'mtime', 0, 1,
false, false, false, 2000, true, false);
-insert into sys.args values (38635, 778, 'res_0', 'bigint', 64, 0, 0, 0);
+insert into sys.args values (38635, 778, 'res_0', 'decimal', 18, 3, 0, 0);
insert into sys.args values (38636, 778, 'arg_1', 'sec_interval', 13, 0, 1, 1);
insert into sys.functions values (779, 'next_value_for', 'next_value', 'sql',
0, 1, true, false, false, 2000, true, true);
insert into sys.args values (38637, 779, 'res_0', 'bigint', 64, 0, 0, 0);
@@ -4519,3 +4519,646 @@ GRANT SELECT ON sys.dependencies_vw TO P
UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
set schema "sys";
+Running database upgrade commands:
+set schema "sys";
+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';
+drop view sys.tracelog;
+drop function sys.tracelog();
+create function sys.tracelog()
+ returns table (
+ ticks bigint, -- time in microseconds
+ stmt string, -- actual statement executed
+ event string -- profiler event executed
+ )
+ external name sql.dump_trace;
+create view sys.tracelog as select * from sys.tracelog();
+update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'tracelog';
+update sys.functions set system = true where system <> true and schema_id =
2000 and name = 'tracelog' and type = 5;
+drop function sys.epoch(bigint);
+create function sys.epoch(sec DECIMAL(18,3)) returns TIMESTAMP WITH TIME ZONE
+external name mtime.epoch;
+grant execute on function sys.epoch (DECIMAL(18,3)) to public;
+update sys.functions set system = true where system <> true and name in
('epoch') and schema_id = 2000 and type = 1;
+set schema "sys";
+create view sys.malfunctions as select * from sys.malfunctions();
+update sys._tables set system = true where system <> true and schema_id = 2000
and name = 'malfunctions';
+drop view sys.dependencies_vw;
+drop view sys.ids;
+CREATE VIEW sys.ids (id, name, schema_id, table_id, table_name, obj_type,
sys_table, system) AS
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, 'author' AS obj_type,
'sys.auths' AS sys_table, (name in ('public','sysadmin','monetdb','.snapshot'))
AS system FROM sys.auths UNION ALL
+SELECT id, name, cast(null as int) as schema_id, cast(null as int) as
table_id, cast(null as varchar(124)) as table_name, ifthenelse(system, 'system
schema', 'schema'), 'sys.schemas', system FROM sys.schemas UNION ALL
+SELECT t.id, name, t.schema_id, t.id as table_id, t.name as table_name,
cast(lower(tt.table_type_name) as varchar(40)), 'sys.tables', t.system FROM
sys.tables t left outer join sys.table_types tt on t.type = tt.table_type_id
UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name,
ifthenelse(t.system, 'system column', 'column'), 'sys._columns', t.system FROM
sys._columns c JOIN sys._tables t ON c.table_id = t.id UNION ALL
+SELECT c.id, c.name, t.schema_id, c.table_id, t.name as table_name, 'column',
'tmp._columns', t.system FROM tmp._columns c JOIN tmp._tables t ON c.table_id =
t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name,
ifthenelse(t.system, 'system key', 'key'), 'sys.keys', t.system FROM sys.keys k
JOIN sys._tables t ON k.table_id = t.id UNION ALL
+SELECT k.id, k.name, t.schema_id, k.table_id, t.name as table_name, 'key',
'tmp.keys', t.system FROM tmp.keys k JOIN tmp._tables t ON k.table_id = t.id
UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name,
ifthenelse(t.system, 'system index', 'index'), 'sys.idxs', t.system FROM
sys.idxs i JOIN sys._tables t ON i.table_id = t.id UNION ALL
+SELECT i.id, i.name, t.schema_id, i.table_id, t.name as table_name, 'index' ,
'tmp.idxs', t.system FROM tmp.idxs i JOIN tmp._tables t ON i.table_id = t.id
UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name,
ifthenelse(t.system, 'system trigger', 'trigger'), 'sys.triggers', t.system
FROM sys.triggers g JOIN sys._tables t ON g.table_id = t.id UNION ALL
+SELECT g.id, g.name, t.schema_id, g.table_id, t.name as table_name, 'trigger',
'tmp.triggers', t.system FROM tmp.triggers g JOIN tmp._tables t ON g.table_id =
t.id UNION ALL
+SELECT f.id, f.name, f.schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, cast(ifthenelse(f.system, 'system ', '') ||
lower(ft.function_type_keyword) as varchar(40)), 'sys.functions', f.system FROM
sys.functions f left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT a.id, a.name, f.schema_id, a.func_id as table_id, f.name as table_name,
cast(ifthenelse(f.system, 'system ', '') || lower(ft.function_type_keyword) ||
' arg' as varchar(44)), 'sys.args', f.system FROM sys.args a JOIN sys.functions
f ON a.func_id = f.id left outer join sys.function_types ft on f.type =
ft.function_type_id UNION ALL
+SELECT id, name, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'sequence', 'sys.sequences', false FROM
sys.sequences UNION ALL
+SELECT o.id, o.name, pt.schema_id, pt.id, pt.name, 'partition of merge table',
'sys.objects', false FROM sys.objects o JOIN sys._tables pt ON o.sub = pt.id
JOIN sys._tables mt ON o.nr = mt.id WHERE mt.type = 3 UNION ALL
+SELECT id, sqlname, schema_id, cast(null as int) as table_id, cast(null as
varchar(124)) as table_name, 'type', 'sys.types', (sqlname in
('inet','json','url','uuid')) FROM sys.types WHERE id > 2000
+ ORDER BY id;
+GRANT SELECT ON sys.ids TO PUBLIC;
+CREATE VIEW sys.dependencies_vw AS
+SELECT d.id, i1.obj_type, i1.name,
+ d.depend_id as used_by_id, i2.obj_type as used_by_obj_type, i2.name as
used_by_name,
+ d.depend_type, dt.dependency_type_name
+ FROM sys.dependencies d
+ JOIN sys.ids i1 ON d.id = i1.id
+ JOIN sys.ids i2 ON d.depend_id = i2.id
+ JOIN sys.dependency_types dt ON d.depend_type = dt.dependency_type_id
+ ORDER BY id, depend_id;
+GRANT SELECT ON sys.dependencies_vw TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE name in ('ids', 'dependencies_vw')
AND schema_id = 2000;
+drop function sys.dump_database(boolean);
+drop procedure sys.dump_table_data();
+drop procedure sys._dump_table_data(string, string);
+drop function sys.prepare_esc(string, string);
+drop function sys.esc(string);
+drop view sys.dump_privileges;
+drop view sys.dump_user_defined_types;
+drop view sys.dump_comments;
+drop view sys.dump_triggers;
+drop view sys.dump_tables;
+drop view sys.dump_functions;
+drop view sys.dump_start_sequences;
+drop view sys.dump_sequences;
+drop view sys.dump_partition_tables;
+drop view sys.dump_foreign_keys;
+drop view sys.dump_column_defaults;
+drop view sys.dump_indices;
+drop view sys.dump_table_constraint_type;
+drop view sys.dump_grant_user_privileges;
+drop view sys.dump_add_schemas_to_users;
+drop view sys.dump_create_schemas;
+drop view sys.dump_create_users;
+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.describe_comments;
+drop view sys.describe_tables;
+drop function sys.get_remote_table_expressions(string, string);
+drop function sys.get_merge_table_partition_expressions(int);
+drop view sys.describe_constraints;
+drop function sys.alter_table(string, string);
+drop function sys.sq(string);
+CREATE FUNCTION sys.SQ (s STRING) RETURNS STRING BEGIN RETURN '''' ||
sys.replace(s,'''','''''') || ''''; END;
+CREATE FUNCTION sys.ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN
RETURN 'ALTER TABLE ' || sys.FQN(s, t); END;
+CREATE VIEW sys.describe_constraints AS
+ SELECT
+ s.name sch,
+ t.name tbl,
+ kc.name col,
+ k.name con,
+ CASE k.type WHEN 0 THEN 'PRIMARY KEY' WHEN 1 THEN 'UNIQUE' END
tpe
+ FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
+ WHERE kc.id = k.id
+ AND k.table_id = t.id
+ AND s.id = t.schema_id
+ AND t.system = FALSE
+ AND k.type in (0, 1);
+CREATE FUNCTION sys.get_merge_table_partition_expressions(tid INT) RETURNS
STRING
+BEGIN
+ RETURN
+ SELECT
+ CASE WHEN tp.table_id IS NOT NULL THEN
+ ' PARTITION BY ' ||
+ ifthenelse(bit_and(tp.type, 2) = 2, 'VALUES ',
'RANGE ') ||
+ CASE
+ WHEN bit_and(tp.type, 4) = 4
+ THEN 'ON ' || '(' || (SELECT
sys.DQ(c.name) || ')' FROM sys.columns c WHERE c.id = tp.column_id)
+ ELSE 'USING ' || '(' || tp.expression
|| ')'
+ END
+ ELSE
+ ''
+ END
+ FROM (VALUES (tid)) t(id) LEFT JOIN sys.table_partitions tp ON
t.id = tp.table_id;
+END;
+CREATE FUNCTION sys.get_remote_table_expressions(s STRING, t STRING) RETURNS
STRING BEGIN
+ RETURN SELECT ' ON ' || sys.SQ(uri) || ' WITH USER ' ||
sys.SQ(username) || ' ENCRYPTED PASSWORD ' || sys.SQ("hash") FROM
sys.remote_table_credentials(s ||'.' || t);
+END;
+CREATE VIEW sys.describe_tables AS
+ SELECT
+ t.id o,
+ s.name sch,
+ t.name tab,
+ ts.table_type_name typ,
+ (SELECT
+ ' (' ||
+ GROUP_CONCAT(
+ sys.DQ(c.name) || ' ' ||
+ sys.describe_type(c.type, c.type_digits,
c.type_scale) ||
+ ifthenelse(c."null" = 'false', ' NOT NULL', '')
+ , ', ') || ')'
+ FROM sys._columns c
+ WHERE c.table_id = t.id) col,
+ CASE ts.table_type_name
+ WHEN 'REMOTE TABLE' THEN
+ sys.get_remote_table_expressions(s.name, t.name)
+ WHEN 'MERGE TABLE' THEN
+ sys.get_merge_table_partition_expressions(t.id)
+ WHEN 'VIEW' THEN
+ sys.schema_guard(s.name, t.name, t.query)
+ ELSE
+ ''
+ END opt
+ FROM sys.schemas s, sys.table_types ts, sys.tables t
+ WHERE ts.table_type_name IN ('TABLE', 'VIEW', 'MERGE TABLE', 'REMOTE
TABLE', 'REPLICA TABLE')
+ AND t.system = FALSE
+ AND s.id = t.schema_id
+ AND ts.table_type_id = t.type
+ AND s.name <> 'tmp';
+CREATE VIEW sys.describe_comments AS
+ SELECT
+ o.id id,
+ o.tpe tpe,
+ o.nme fqn,
+ 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, 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
+ ) AS o(id, tpe, nme)
+ JOIN sys.comments c ON c.id = o.id;
+CREATE VIEW sys.describe_privileges AS
+ SELECT
+ CASE
+ WHEN o.tpe IS NULL AND pc.privilege_code_name =
'SELECT' THEN --GLOBAL privileges: SELECT maps to COPY FROM
+ 'COPY FROM'
+ WHEN o.tpe IS NULL AND pc.privilege_code_name =
'UPDATE' THEN --GLOBAL privileges: UPDATE maps to COPY INTO
+ 'COPY INTO'
+ ELSE
+ o.nme
+ END o_nme,
+ coalesce(o.tpe, 'GLOBAL') o_tpe,
+ pc.privilege_code_name p_nme,
+ a.name a_nme,
+ g.name g_nme,
+ p.grantable grantable
+ FROM
+ sys.privileges p LEFT JOIN
+ (
+ SELECT t.id, s.name || '.' || t.name , 'TABLE'
+ from sys.schemas s, sys.tables t where s.id =
t.schema_id
+ UNION ALL
+ SELECT c.id, s.name || '.' || t.name || '.' || c.name,
'COLUMN'
+ FROM sys.schemas s, sys.tables t, sys.columns c where
s.id = t.schema_id AND t.id = c.table_id
+ UNION ALL
+ SELECT f.id, f.nme, f.tpe
+ FROM sys.fully_qualified_functions f
+ ) o(id, nme, tpe) ON o.id = p.obj_id,
+ sys.privilege_codes pc,
+ auths a, auths g
+ WHERE
+ p.privileges = pc.privilege_code_id AND
+ p.auth_id = a.id AND
+ p.grantor = g.id;
+CREATE VIEW sys.describe_partition_tables AS
+ SELECT
+ m_sch,
+ m_tbl,
+ p_sch,
+ p_tbl,
+ CASE
+ WHEN p_raw_type IS NULL THEN 'READ ONLY'
+ 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'
+ ELSE p_raw_type
+ END AS tpe,
+ pvalues,
+ minimum,
+ maximum,
+ with_nulls
+ FROM
+ (WITH
+ tp("type", table_id) AS
+ (SELECT ifthenelse((table_partitions."type" & 2) = 2, 'VALUES',
'RANGE'), table_partitions.table_id FROM sys.table_partitions),
+ subq(m_tid, p_mid, "type", m_sch, m_tbl, p_sch, p_tbl) AS
+ (SELECT m_t.id, p_m.id, m_t."type", m_s.name, m_t.name,
p_s.name, p_m.name
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
