Changeset: 1d188cc7d53b for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/1d188cc7d53b
Modified Files:
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
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
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
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-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
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
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
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
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Mar2025
Log Message:
Approve upgrade tests after release.
diffs (truncated from 9892 to 300 lines):
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
@@ -1,512 +1,6 @@
Running database upgrade commands to update system tables.
Running database upgrade commands:
-update sys._columns set type_digits = 7 where type = 'tinyint' and type_digits
<> 7;
-update sys._columns set type_digits = 15 where type = 'smallint' and
type_digits <> 15;
-update sys._columns set type_digits = 31 where type = 'int' and type_digits <>
31;
-update sys._columns set type_digits = 63 where type = 'bigint' and type_digits
<> 63;
-update sys._columns set type_digits = 127 where type = 'hugeint' and
type_digits <> 127;
-update sys._columns set type = 'varchar' where type in ('clob', 'char') and
table_id in (select id from sys._tables where system and name <>
'netcdf_files');
-update sys.args set type_digits = 7 where type = 'tinyint' and type_digits <>
7;
-update sys.args set type_digits = 15 where type = 'smallint' and type_digits
<> 15;
-update sys.args set type_digits = 31 where type = 'int' and type_digits <> 31;
-update sys.args set type_digits = 63 where type = 'bigint' and type_digits <>
63;
-update sys.args set type_digits = 127 where type = 'hugeint' and type_digits
<> 127;
-update sys.args set type = 'varchar' where type in ('clob', 'char');
-drop aggregate median(decimal(18,3));
-drop aggregate median_avg(decimal(18,3));
-drop aggregate quantile(decimal(18,3), double);
-drop aggregate quantile_avg(decimal(18,3), double);
-create aggregate median(val DECIMAL(2)) returns DECIMAL(2)
- external name "aggr"."median";
-GRANT EXECUTE ON AGGREGATE median(DECIMAL(2)) TO PUBLIC;
-create aggregate median(val DECIMAL(4)) returns DECIMAL(4)
- external name "aggr"."median";
-GRANT EXECUTE ON AGGREGATE median(DECIMAL(4)) TO PUBLIC;
-create aggregate median(val DECIMAL(9)) returns DECIMAL(9)
- external name "aggr"."median";
-GRANT EXECUTE ON AGGREGATE median(DECIMAL(9)) TO PUBLIC;
-create aggregate median(val DECIMAL(18)) returns DECIMAL(18)
- external name "aggr"."median";
-GRANT EXECUTE ON AGGREGATE median(DECIMAL(18)) TO PUBLIC;
-create aggregate median_avg(val DECIMAL(2)) returns DOUBLE
- external name "aggr"."median_avg";
-GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(2)) TO PUBLIC;
-create aggregate median_avg(val DECIMAL(4)) returns DOUBLE
- external name "aggr"."median_avg";
-GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(4)) TO PUBLIC;
-create aggregate median_avg(val DECIMAL(9)) returns DOUBLE
- external name "aggr"."median_avg";
-GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(9)) TO PUBLIC;
-create aggregate median_avg(val DECIMAL(18)) returns DOUBLE
- external name "aggr"."median_avg";
-GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(18)) TO PUBLIC;
-create aggregate quantile(val DECIMAL(2), q DOUBLE) returns DECIMAL(2)
- external name "aggr"."quantile";
-GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(2), DOUBLE) TO PUBLIC;
-create aggregate quantile(val DECIMAL(4), q DOUBLE) returns DECIMAL(4)
- external name "aggr"."quantile";
-GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(4), DOUBLE) TO PUBLIC;
-create aggregate quantile(val DECIMAL(9), q DOUBLE) returns DECIMAL(9)
- external name "aggr"."quantile";
-GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(9), DOUBLE) TO PUBLIC;
-create aggregate quantile(val DECIMAL(18), q DOUBLE) returns DECIMAL(18)
- external name "aggr"."quantile";
-GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(18), DOUBLE) TO PUBLIC;
-create aggregate quantile_avg(val DECIMAL(2), q DOUBLE) returns DOUBLE
- external name "aggr"."quantile_avg";
-GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(2), DOUBLE) TO PUBLIC;
-create aggregate quantile_avg(val DECIMAL(4), q DOUBLE) returns DOUBLE
- external name "aggr"."quantile_avg";
-GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(4), DOUBLE) TO PUBLIC;
-create aggregate quantile_avg(val DECIMAL(9), q DOUBLE) returns DOUBLE
- external name "aggr"."quantile_avg";
-GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(9), DOUBLE) TO PUBLIC;
-create aggregate quantile_avg(val DECIMAL(18), q DOUBLE) returns DOUBLE
- external name "aggr"."quantile_avg";
-GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(18), DOUBLE) TO PUBLIC;
-drop function if exists sys.time_to_str(time with time zone, string) cascade;
-drop function if exists sys.timestamp_to_str(timestamp with time zone, string)
cascade;
-create function time_to_str(d time, format string) returns string
- external name mtime."time_to_str";
-create function time_to_str(d time with time zone, format string) returns
string
- external name mtime."timetz_to_str";
-create function timestamp_to_str(d timestamp with time zone, format string)
returns string
- external name mtime."timestamptz_to_str";
-grant execute on function time_to_str(time, string) to public;
-grant execute on function time_to_str(time with time zone, string) to public;
-grant execute on function timestamp_to_str(timestamp with time zone, string)
to public;
-update sys.functions set system = true where not system and schema_id = 2000
and name in ('time_to_str', 'timestamp_to_str', 'median', 'median_avg',
'quantile', 'quantile_avg');
-drop function if exists sys.dump_database(boolean) cascade;
-drop view sys.dump_comments;
-drop view sys.dump_tables;
-drop view sys.dump_functions;
-drop view sys.dump_function_grants;
-drop function if exists sys.describe_columns(string, string) cascade;
-drop view sys.describe_functions;
-drop view sys.describe_privileges;
-drop view sys.describe_comments;
-drop view sys.fully_qualified_functions;
-drop view sys.describe_tables;
-drop function if exists sys.describe_type(string, integer, integer) cascade;
-CREATE FUNCTION sys.describe_type(ctype string, digits integer, tscale integer)
- RETURNS string
-BEGIN
- RETURN sys.sql_datatype(ctype, digits, tscale, false, false);
-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', 'UNLOGGED 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.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.name) || '()'
- ELSE
- 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)
|| ')'
- 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 AS id, o.tpe AS tpe, o.nme AS fqn, cm.remark AS rem
- FROM (
- SELECT id, 'SCHEMA', sys.DQ(name) FROM sys.schemas WHERE NOT system
- 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 NOT t.system
- 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 NOT t.system AND 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 NOT t.system AND 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 NOT f.system AND 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 cm ON cm.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_functions AS
- WITH func_args_all(func_id, number, max_number, func_arg) AS
- (
- SELECT
- func_id,
- number,
- max(number) OVER (PARTITION BY func_id ORDER BY number DESC),
- group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ') OVER (PARTITION BY func_id ORDER BY number)
- FROM sys.args
- WHERE inout = 1
- ),
- func_args(func_id, func_arg) AS
- (
- SELECT func_id, func_arg
- FROM func_args_all
- WHERE number = max_number
- ),
- func_rets_all(func_id, number, max_number, func_ret, func_ret_type) AS
- (
- SELECT
- func_id,
- number,
- max(number) OVER (PARTITION BY func_id ORDER BY number DESC),
- group_concat(sys.dq(name) || ' ' || sys.describe_type(type, type_digits,
type_scale),', ') OVER (PARTITION BY func_id ORDER BY number),
- group_concat(sys.describe_type(type, type_digits, type_scale),', ') OVER
(PARTITION BY func_id ORDER BY number)
- FROM sys.args
- WHERE inout = 0
- ),
- func_rets(func_id, func_ret, func_ret_type) AS
- (
- SELECT
- func_id,
- func_ret,
- func_ret_type
- FROM func_rets_all
- WHERE number = max_number
- )
- SELECT
- f.id o,
- s.name sch,
- f.name fun,
- CASE WHEN f.language IN (1, 2) THEN f.func ELSE 'CREATE ' ||
ft.function_type_keyword || ' ' || sys.FQN(s.name, f.name) || '(' ||
coalesce(fa.func_arg, '') || ')' || CASE WHEN f.type = 5 THEN ' RETURNS TABLE
(' || coalesce(fr.func_ret, '') || ')' WHEN f.type IN (1,3) THEN ' RETURNS ' ||
fr.func_ret_type ELSE '' END || CASE WHEN fl.language_keyword IS NULL THEN ''
ELSE ' LANGUAGE ' || fl.language_keyword END || ' ' || f.func END def
- FROM sys.functions f
- LEFT OUTER JOIN func_args fa ON fa.func_id = f.id
- LEFT OUTER JOIN func_rets fr ON fr.func_id = f.id
- JOIN sys.schemas s ON f.schema_id = s.id
- JOIN sys.function_types ft ON f.type = ft.function_type_id
- LEFT OUTER JOIN sys.function_languages fl ON f.language = fl.language_id
- WHERE s.name <> 'tmp' AND NOT f.system;
-CREATE FUNCTION sys.describe_columns(schemaName string, tableName string)
- RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls
boolean, cDefault string, number integer, sqltype string, remark string)
-BEGIN
- RETURN SELECT c.name, c."type", c.type_digits, c.type_scale, c."null",
c."default", c.number, sys.describe_type(c."type", c.type_digits,
c.type_scale), com.remark
- FROM sys._tables t, sys.schemas s, sys._columns c
- LEFT OUTER JOIN sys.comments com ON c.id = com.id
- WHERE c.table_id = t.id
- AND t.name = tableName
- AND t.schema_id = s.id
- AND s.name = schemaName
- ORDER BY c.number;
-END;
-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,
- max(a.number) OVER (PARTITION BY a.func_id ORDER BY a.number DESC),
- group_concat(sys.describe_type(a.type, a.type_digits, a.type_scale), ', ')
OVER (PARTITION BY a.func_id ORDER BY a.number)
- FROM sys.args a
- WHERE a.inout = 1),
- func_args(func_id, func_arg) AS
- (SELECT func_id, func_arg FROM func_args_all WHERE number = max_number)
- SELECT
- 'GRANT ' || pc.privilege_code_name || ' ON ' || ft.function_type_keyword || '
'
- || sys.FQN(s.name, f.name) || '(' || coalesce(fa.func_arg, '') || ') TO '
- || ifthenelse(a.name = 'public', 'PUBLIC', sys.dq(a.name))
- || CASE WHEN p.grantable = 1 THEN ' WITH GRANT OPTION' ELSE '' END || ';'
stmt,
- s.name schema_name,
- f.name function_name,
- a.name grantee
- FROM sys.schemas s,
- sys.functions f LEFT OUTER JOIN func_args fa ON f.id = fa.func_id,
- sys.auths a,
- sys.privileges p,
- sys.auths g,
- sys.function_types ft,
- sys.privilege_codes pc
- WHERE s.id = f.schema_id
- AND f.id = p.obj_id
- AND p.auth_id = a.id
- AND p.grantor = g.id
- AND p.privileges = pc.privilege_code_id
- AND f.type = ft.function_type_id
- AND NOT f.system
- ORDER BY s.name, f.name, a.name, g.name, p.grantable;
-CREATE VIEW sys.dump_functions AS
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
