Changeset: 5d16b384dec7 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/5d16b384dec7
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/upgrade.stable.out.int128
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/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Jun2023
Log Message:
Fix upgrade output.
diffs (truncated from 611 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
@@ -367,7 +367,7 @@ update sys.functions set system = true w
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
Running database upgrade commands:
-CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
-GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+CREATE FUNCTION timestamp_to_str(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;
UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
diff --git a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out
@@ -367,7 +367,7 @@ update sys.functions set system = true w
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
Running database upgrade commands:
-CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
-GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+CREATE FUNCTION timestamp_to_str(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;
UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -436,7 +436,7 @@ update sys.functions set system = true w
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
Running database upgrade commands:
-CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
-GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+CREATE FUNCTION timestamp_to_str(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;
UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -367,7 +367,7 @@ update sys.functions set system = true w
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
Running database upgrade commands:
-CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
-GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+CREATE FUNCTION timestamp_to_str(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;
UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out
@@ -367,7 +367,7 @@ update sys.functions set system = true w
delete from sys.triggers where name = 'system_update_tables' and table_id =
2067;
Running database upgrade commands:
-CREATE FUNCTION "timestamp_to_str"(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
-GRANT EXECUTE ON FUNCTION "timestamp_to_str"(TIMESTAMP, STRING) TO PUBLIC;
+CREATE FUNCTION timestamp_to_str(d TIMESTAMP, format STRING) RETURNS STRING
EXTERNAL NAME mtime."timestamp_to_str";
+GRANT EXECUTE ON FUNCTION timestamp_to_str(TIMESTAMP, STRING) TO PUBLIC;
UPDATE sys.functions SET system = true WHERE system <> true AND name =
'timestamp_to_str' AND schema_id = 2000 and type = 1;
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -70,330 +70,6 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
Running database upgrade commands:
-alter table sys.db_user_info add column max_memory bigint;
-alter table sys.db_user_info add column max_workers int;
-alter table sys.db_user_info add column optimizer varchar(1024);
-alter table sys.db_user_info add column default_role int;
-alter table sys.db_user_info add column password varchar(256);
-update sys.db_user_info u set max_memory = 0, max_workers = 0, optimizer =
'default_pipe', default_role = (select id from sys.auths a where a.name =
u.name);
--- and copying passwords
-
-Running database upgrade commands:
-drop view sys.dependency_schemas_on_users cascade;
-drop view sys.roles cascade;
-drop view sys.users cascade;
-drop function sys.db_users() cascade;
-CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths a WHERE
a.name NOT IN (SELECT u.name FROM sys.db_user_info u);
-GRANT SELECT ON sys.roles TO PUBLIC;
-CREATE VIEW sys.users AS SELECT name, fullname, default_schema, schema_path,
max_memory, max_workers, optimizer, default_role FROM sys.db_user_info;
-GRANT SELECT ON sys.users TO PUBLIC;
-CREATE FUNCTION sys.db_users() RETURNS TABLE(name varchar(2048)) RETURN SELECT
name FROM sys.db_user_info;
-CREATE VIEW sys.dependency_schemas_on_users AS
-SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6
AS smallint) AS depend_type
- FROM sys.db_user_info AS u, sys.schemas AS s
- WHERE u.default_schema = s.id
- ORDER BY s.name, u.name;
-GRANT SELECT ON sys.dependency_schemas_on_users TO PUBLIC;
-update sys._tables set system = true where name in ('users', 'roles',
'dependency_schemas_on_users') AND schema_id = 2000;
-update sys.functions set system = true where system <> true and name in
('db_users') and schema_id = 2000 and type = 5;
-
-Running database upgrade commands:
-drop function sys.dump_database(boolean) cascade;
-drop procedure sys.dump_table_data() cascade;
-drop procedure sys.dump_table_data(string, string) cascade;
-drop view sys.dump_partition_tables cascade;
-drop view sys.describe_partition_tables cascade;
-drop view sys.dump_sequences cascade;
-drop view sys.dump_start_sequences cascade;
-drop view sys.dump_tables cascade;
-drop view sys.describe_tables cascade;
-drop view sys.dump_create_users cascade;
-drop view sys.dump_functions cascade;
-drop view sys.dump_triggers cascade;
-drop function sys.schema_guard cascade;
-drop function sys.replace_first(string, string, string, string) cascade;
-CREATE FUNCTION sys.schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
-RETURN
- SELECT 'SET SCHEMA ' || sys.dq(sch) || '; ' || stmt;
-END;
-CREATE VIEW sys.dump_functions AS
- SELECT f.o o, sys.schema_guard(f.sch, f.fun, f.def) stmt,
- f.sch schema_name,
- f.fun function_name
- FROM sys.describe_functions f;
-CREATE VIEW sys.dump_triggers AS
- SELECT sys.schema_guard(sch, tab, def) stmt,
- sch schema_name,
- tab table_name,
- tri trigger_name
- FROM sys.describe_triggers;
-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
- FROM sys.schemas m_s, sys._tables m_t, sys.dependencies d, sys.schemas p_s,
sys._tables p_m
- WHERE m_t."type" IN (3, 6)
- AND m_t.schema_id = m_s.id
- AND m_s.name <> 'tmp'
- AND m_t.system = FALSE
- AND m_t.id = d.depend_id
- AND d.id = p_m.id
- AND p_m.schema_id = p_s.id
- ORDER BY m_t.id, p_m.id),
- vals(id,vals) as
- (SELECT vp.table_id, GROUP_CONCAT(vp.value, ',') FROM sys.value_partitions vp
GROUP BY vp.table_id)
- SELECT
- subq.m_sch,
- subq.m_tbl,
- subq.p_sch,
- subq.p_tbl,
- tp."type" AS p_raw_type,
- CASE WHEN tp."type" = 'VALUES'
- THEN (SELECT vals.vals FROM vals WHERE vals.id = subq.p_mid)
- ELSE NULL
- END AS pvalues,
- CASE WHEN tp."type" = 'RANGE'
- THEN (SELECT minimum FROM sys.range_partitions rp WHERE rp.table_id =
subq.p_mid)
- ELSE NULL
- END AS minimum,
- CASE WHEN tp."type" = 'RANGE'
- THEN (SELECT maximum FROM sys.range_partitions rp WHERE rp.table_id =
subq.p_mid)
- ELSE NULL
- END AS maximum,
- CASE WHEN tp."type" = 'VALUES'
- THEN EXISTS(SELECT vp.value FROM sys.value_partitions vp WHERE vp.table_id =
subq.p_mid AND vp.value IS NULL)
- ELSE (SELECT rp.with_nulls FROM sys.range_partitions rp WHERE rp.table_id =
subq.p_mid)
- END AS with_nulls
- FROM
- subq LEFT OUTER JOIN tp
- ON subq.m_tid = tp.table_id) AS tmp_pi;
-GRANT SELECT ON sys.describe_partition_tables TO PUBLIC;
-CREATE VIEW sys.dump_partition_tables AS
-SELECT
- 'ALTER TABLE ' || sys.FQN(m_sch, m_tbl) || ' ADD TABLE ' || sys.FQN(p_sch,
p_tbl) ||
- CASE
- WHEN tpe = 'VALUES' THEN ' AS PARTITION IN (' || pvalues || ')'
- WHEN tpe = 'RANGE' THEN ' AS PARTITION FROM ' || ifthenelse(minimum IS NOT
NULL, sys.SQ(minimum), 'RANGE MINVALUE') || ' TO ' || ifthenelse(maximum IS NOT
NULL, sys.SQ(maximum), 'RANGE MAXVALUE')
- WHEN tpe = 'FOR NULLS' THEN ' AS PARTITION FOR NULL VALUES'
- ELSE '' --'READ ONLY'
- END ||
- CASE WHEN tpe in ('VALUES', 'RANGE') AND with_nulls THEN ' WITH NULL VALUES'
ELSE '' END ||
- ';' stmt,
- m_sch merge_schema_name,
- m_tbl merge_table_name,
- p_sch partition_schema_name,
- p_tbl partition_table_name
- FROM sys.describe_partition_tables;
-CREATE VIEW sys.dump_sequences AS
- SELECT
- 'CREATE SEQUENCE ' || sys.FQN(sch, seq) || ' AS BIGINT;' stmt,
- sch schema_name,
- seq seqname
- FROM sys.describe_sequences;
-CREATE VIEW sys.dump_start_sequences AS
- SELECT 'ALTER SEQUENCE ' || sys.FQN(sch, seq) ||
- CASE WHEN s = 0 THEN '' ELSE ' RESTART WITH ' || rs END ||
- CASE WHEN inc = 1 THEN '' ELSE ' INCREMENT BY ' || inc END ||
- CASE WHEN nomin THEN ' NO MINVALUE' WHEN rmi IS NULL THEN '' ELSE ' MINVALUE
' || rmi END ||
- CASE WHEN nomax THEN ' NO MAXVALUE' WHEN rma IS NULL THEN '' ELSE ' MAXVALUE
' || rma END ||
- CASE WHEN "cache" = 1 THEN '' ELSE ' CACHE ' || "cache" END ||
- CASE WHEN "cycle" THEN '' ELSE ' NO' END || ' CYCLE;' stmt,
- sch schema_name,
- seq sequence_name
- FROM sys.describe_sequences;
-CREATE PROCEDURE sys.dump_table_data(sch STRING, tbl STRING)
-BEGIN
- DECLARE tid INT;
- SET tid = (SELECT MIN(t.id) FROM sys.tables t, sys.schemas s WHERE t.name =
tbl AND t.schema_id = s.id AND s.name = sch);
- IF tid IS NOT NULL THEN
- DECLARE k INT;
- DECLARE m INT;
- SET k = (SELECT MIN(c.id) FROM sys.columns c WHERE c.table_id = tid);
- SET m = (SELECT MAX(c.id) FROM sys.columns c WHERE c.table_id = tid);
- IF k IS NOT NULL AND m IS NOT NULL THEN
- DECLARE cname STRING;
- DECLARE ctype STRING;
- DECLARE _cnt INT;
- SET cname = (SELECT c.name FROM sys.columns c WHERE c.id = k);
- SET ctype = (SELECT c.type FROM sys.columns c WHERE c.id = k);
- SET _cnt = (SELECT count FROM sys.storage(sch, tbl, cname));
- IF _cnt > 0 THEN
- DECLARE COPY_INTO_STMT STRING;
- DECLARE SELECT_DATA_STMT STRING;
- SET COPY_INTO_STMT = 'COPY ' || _cnt || ' RECORDS INTO ' || sys.FQN(sch, tbl)
|| '(' || sys.DQ(cname);
- SET SELECT_DATA_STMT = 'SELECT (SELECT COUNT(*) FROM sys.dump_statements) +
RANK() OVER(), ' || sys.prepare_esc(cname, ctype);
- WHILE (k < m) DO
- SET k = (SELECT MIN(c.id) FROM sys.columns c WHERE c.table_id = tid AND c.id
> k);
- SET cname = (SELECT c.name FROM sys.columns c WHERE c.id = k);
- SET ctype = (SELECT c.type FROM sys.columns c WHERE c.id = k);
- SET COPY_INTO_STMT = (COPY_INTO_STMT || ', ' || sys.DQ(cname));
- SET SELECT_DATA_STMT = (SELECT_DATA_STMT || '|| ''|'' || ' ||
sys.prepare_esc(cname, ctype));
- END WHILE;
- SET COPY_INTO_STMT = (COPY_INTO_STMT || ') FROM STDIN USING DELIMITERS
''|'',E''\\n'',''"'';');
- SET SELECT_DATA_STMT = (SELECT_DATA_STMT || ' FROM ' || sys.FQN(sch, tbl));
- INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, COPY_INTO_STMT);
- CALL sys.EVAL('INSERT INTO sys.dump_statements ' || SELECT_DATA_STMT || ';');
- END IF;
- END IF;
- END IF;
-END;
-CREATE PROCEDURE sys.dump_table_data()
-BEGIN
- DECLARE i INT;
- SET i = (SELECT MIN(t.id) FROM sys.tables t, sys.table_types ts WHERE t.type
= ts.table_type_id AND ts.table_type_name = 'TABLE' AND NOT t.system);
- IF i IS NOT NULL THEN
- DECLARE M INT;
- SET M = (SELECT MAX(t.id) FROM sys.tables t, sys.table_types ts WHERE t.type
= ts.table_type_id AND ts.table_type_name = 'TABLE' AND NOT t.system);
- DECLARE sch STRING;
- DECLARE tbl STRING;
- WHILE i IS NOT NULL AND i <= M DO
- SET sch = (SELECT s.name FROM sys.tables t, sys.schemas s WHERE s.id =
t.schema_id AND t.id = i);
- SET tbl = (SELECT t.name FROM sys.tables t, sys.schemas s WHERE s.id =
t.schema_id AND t.id = i);
- CALL sys.dump_table_data(sch, tbl);
- SET i = (SELECT MIN(t.id) FROM sys.tables t, sys.table_types ts WHERE t.type
= ts.table_type_id AND ts.table_type_name = 'TABLE' AND NOT t.system AND t.id >
i);
- END WHILE;
- END IF;
-END;
-CREATE VIEW sys.dump_create_users AS
- SELECT
- 'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' ||
- sys.sq(sys.password_hash(ui.name)) ||
- ' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys' || ifthenelse(ui.schema_path
= '"sys"', '', ' SCHEMA PATH ' || sys.sq(ui.schema_path)) || ';' stmt,
- ui.name user_name
- FROM sys.db_user_info ui, sys.schemas s
- WHERE ui.default_schema = s.id
- AND ui.name <> 'monetdb'
- AND ui.name <> '.snapshot';
-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
_______________________________________________
checkin-list mailing list -- checkin-list@monetdb.org
To unsubscribe send an email to checkin-list-le...@monetdb.org
