Changeset: da0b715fe516 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/da0b715fe516
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/52_describe.sql
sql/test/Dependencies/Tests/dependency_DBobjects.test
sql/test/Tests/comment-dump.test
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.32bit
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.32bit
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-hge/Tests/upgrade.stable.out.ppc64.int128
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.32bit
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.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.32bit
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.32bit
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.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.32bit
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: Sep2022
Log Message:
Instead of changing the query, add a SET SCHEMA in the dump.
diffs (truncated from 1431 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
@@ -4773,6 +4773,10 @@ sql_update_sep2022(Client c, mvc *sql)
t->system = 0;
t = mvc_bind_table(sql, s, "dump_create_users");
t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_functions");
+ t->system = 0;
+ t = mvc_bind_table(sql, s, "dump_triggers");
+ t->system = 0;
pos = 0;
pos += snprintf(buf + pos, bufsize - pos,
@@ -4786,9 +4790,28 @@ sql_update_sep2022(Client c, mvc *sql)
"drop view sys.dump_start_sequences;\n"
"drop view sys.dump_tables;\n"
"drop view sys.describe_tables;\n"
- "drop view sys.dump_create_users;\n");
+ "drop view sys.dump_create_users;\n"
+ "drop view sys.dump_functions;\n"
+ "drop view sys.dump_triggers;\n"
+ "drop function sys.schema_guard;\n"
+ "drop function sys.replace_first(string, string,
string, string);\n");
pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE FUNCTION sys.schema_guard(sch STRING, nme
STRING, stmt STRING) RETURNS STRING BEGIN\n"
+ "RETURN\n"
+ " SELECT 'SET SCHEMA ' || sys.dq(sch) || '; ' ||
stmt;\n"
+ "END;\n"
+ "CREATE VIEW sys.dump_functions AS\n"
+ " SELECT f.o o, sys.schema_guard(f.sch, f.fun, f.def)
stmt,\n"
+ " f.sch schema_name,\n"
+ " f.fun function_name\n"
+ " FROM sys.describe_functions f;\n"
+ "CREATE VIEW sys.dump_triggers AS\n"
+ " SELECT sys.schema_guard(sch, tab, def) stmt,\n"
+ " sch schema_name,\n"
+ " tab table_name,\n"
+ " tri trigger_name\n"
+ " FROM sys.describe_triggers;\n"
"CREATE VIEW sys.describe_partition_tables AS\n"
" SELECT\n"
" m_sch,\n"
@@ -5031,11 +5054,13 @@ sql_update_sep2022(Client c, mvc *sql)
" RETURN sys.dump_statements;\n"
"END;\n");
pos += snprintf(buf + pos, bufsize - pos,
- "update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users')
AND schema_id = 2000;\n");
+ "update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users',
'dump_functions', 'dump_triggers') AND schema_id = 2000;\n");
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true where system <>
true and name in ('dump_table_data') and schema_id = 2000 and type = %d;\n",
F_PROC);
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true where system <>
true and name in ('dump_database') and schema_id = 2000 and type = %d;\n",
F_UNION);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where system <>
true and name in ('schema_guard') and schema_id = 2000 and type = %d;\n",
F_FUNC);
/* 12_url.sql */
pos += snprintf(buf + pos, bufsize - pos,
diff --git a/sql/scripts/52_describe.sql b/sql/scripts/52_describe.sql
--- a/sql/scripts/52_describe.sql
+++ b/sql/scripts/52_describe.sql
@@ -149,16 +149,12 @@ CREATE FUNCTION sys.SQ (s STRING) RETURN
CREATE FUNCTION sys.DQ (s STRING) RETURNS STRING BEGIN RETURN '"' ||
sys.replace(s,'"','""') || '"'; END; --TODO: Figure out why this breaks with
the space
CREATE FUNCTION sys.FQN(s STRING, t STRING) RETURNS STRING BEGIN RETURN '"' ||
sys.replace(s,'"','""') || '"."' || sys.replace(t,'"','""') || '"'; END;
---We need pcre to implement a header guard which means adding the schema
---of an object explicitly to its identifier.
---Note that we don't do a case insensitive match: the SQL layer has
---converted all unquoted tokens to lower case, so it there is an upper
---case value, it must have been quoted, and it should still be quoted in
---the saved query, so that would be the only match we find.
-CREATE FUNCTION sys.replace_first(ori STRING, pat STRING, rep STRING, flg
STRING) RETURNS STRING EXTERNAL NAME "pcre"."replace_first";
+-- Some creation queries are stored in the catalog. If these queries
+-- were originally executed in some schema and don't themselves contain
+-- that schema, we need to first switch to that schema.
CREATE FUNCTION sys.schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
RETURN
- SELECT sys.replace_first(stmt, r'(((?<!")\b' || sch || r'\b(?!"))|"' ||
sch || r'"\s*\.\s*)?(((?<!")\b' || nme || r'\b(?!"))|"' || nme || '")',
sys.FQN(sch, nme), '');
+ SELECT 'SET SCHEMA ' || sys.dq(sch) || '; ' || stmt;
END;
CREATE VIEW sys.describe_constraints AS
diff --git a/sql/test/Dependencies/Tests/dependency_DBobjects.test
b/sql/test/Dependencies/Tests/dependency_DBobjects.test
--- a/sql/test/Dependencies/Tests/dependency_DBobjects.test
+++ b/sql/test/Dependencies/Tests/dependency_DBobjects.test
@@ -412,15 +412,15 @@ DEP_FUNC
dq
prepare_esc
DEP_FUNC
+dq
+schema_guard
+DEP_FUNC
dump_table_data
dump_database
DEP_FUNC
f1
f2
DEP_FUNC
-fqn
-schema_guard
-DEP_FUNC
getproj4
st_transform
DEP_FUNC
@@ -433,9 +433,6 @@ DEP_FUNC
remote_table_credentials
get_remote_table_expressions
DEP_FUNC
-replace_first
-schema_guard
-DEP_FUNC
sq
get_remote_table_expressions
DEP_FUNC
diff --git a/sql/test/Tests/comment-dump.test b/sql/test/Tests/comment-dump.test
--- a/sql/test/Tests/comment-dump.test
+++ b/sql/test/Tests/comment-dump.test
@@ -78,14 +78,14 @@ SET SCHEMA "sys";
CREATE SCHEMA "foo" AUTHORIZATION "monetdb";
CREATE SEQUENCE "foo"."counter" AS BIGINT;
CREATE TABLE "foo"."tab" ("i" INTEGER, "j" DECIMAL(4,2));
-create view "foo"."sel" as select * from foo."tab";
-create view "foo"."SEL" as select * from sel;
-create function "foo"."f"() returns int begin return 42; end;
-create function "foo"."f"(i int) returns int begin return 43; end;
-create function "foo"."f"(i int, j int) returns int begin return 44; end;
-create function "foo"."f"(i int, j int, k int) returns int begin return 45;
end;
-create function "foo"."f"(i int, j int, k int, l int) returns int begin return
45; end;
-create procedure "foo"."g"() begin delete from tab where false; end;
+SET SCHEMA "foo"; create view sel as select * from foo."tab";
+SET SCHEMA "foo"; create view "SEL" as select * from sel;
+SET SCHEMA "foo"; create function f() returns int begin return 42; end;
+SET SCHEMA "foo"; create function f(i int) returns int begin return 43; end;
+SET SCHEMA "foo"; create function f(i int, j int) returns int begin return 44;
end;
+SET SCHEMA "foo"; create function f(i int, j int, k int) returns int begin
return 45; end;
+SET SCHEMA "foo"; create function f(i int, j int, k int, l int) returns int
begin return 45; end;
+SET SCHEMA "foo"; create procedure g() begin delete from tab where false; end;
ALTER SEQUENCE "foo"."counter" RESTART WITH 1 NO CYCLE;
CREATE INDEX "idx" ON "foo"."tab"(j,i);
COMMENT ON COLUMN "foo"."tab"."i" IS 'ii';
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
@@ -4125,6 +4125,25 @@ drop view sys.dump_start_sequences;
drop view sys.dump_tables;
drop view sys.describe_tables;
drop view sys.dump_create_users;
+drop view sys.dump_functions;
+drop view sys.dump_triggers;
+drop function sys.schema_guard;
+drop function sys.replace_first(string, string, string, string);
+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,
@@ -4364,9 +4383,10 @@ BEGIN
INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
RETURN sys.dump_statements;
END;
-update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users')
AND schema_id = 2000;
+update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users',
'dump_functions', 'dump_triggers') AND schema_id = 2000;
update sys.functions set system = true where system <> true and name in
('dump_table_data') and schema_id = 2000 and type = 2;
update sys.functions set system = true where system <> true and name in
('dump_database') and schema_id = 2000 and type = 5;
+update sys.functions set system = true where system <> true and name in
('schema_guard') and schema_id = 2000 and type = 1;
CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
EXTERNAL NAME url."extractURLHost";
GRANT EXECUTE ON FUNCTION url_extract_host(string, bool) TO PUBLIC;
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128
@@ -4125,6 +4125,25 @@ drop view sys.dump_start_sequences;
drop view sys.dump_tables;
drop view sys.describe_tables;
drop view sys.dump_create_users;
+drop view sys.dump_functions;
+drop view sys.dump_triggers;
+drop function sys.schema_guard;
+drop function sys.replace_first(string, string, string, string);
+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,
@@ -4364,9 +4383,10 @@ BEGIN
INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
RETURN sys.dump_statements;
END;
-update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users')
AND schema_id = 2000;
+update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users',
'dump_functions', 'dump_triggers') AND schema_id = 2000;
update sys.functions set system = true where system <> true and name in
('dump_table_data') and schema_id = 2000 and type = 2;
update sys.functions set system = true where system <> true and name in
('dump_database') and schema_id = 2000 and type = 5;
+update sys.functions set system = true where system <> true and name in
('schema_guard') and schema_id = 2000 and type = 1;
CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
EXTERNAL NAME url."extractURLHost";
GRANT EXECUTE ON FUNCTION url_extract_host(string, bool) TO PUBLIC;
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
@@ -3778,6 +3778,25 @@ drop view sys.dump_start_sequences;
drop view sys.dump_tables;
drop view sys.describe_tables;
drop view sys.dump_create_users;
+drop view sys.dump_functions;
+drop view sys.dump_triggers;
+drop function sys.schema_guard;
+drop function sys.replace_first(string, string, string, string);
+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,
@@ -4017,9 +4036,10 @@ BEGIN
INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
RETURN sys.dump_statements;
END;
-update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users')
AND schema_id = 2000;
+update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users',
'dump_functions', 'dump_triggers') AND schema_id = 2000;
update sys.functions set system = true where system <> true and name in
('dump_table_data') and schema_id = 2000 and type = 2;
update sys.functions set system = true where system <> true and name in
('dump_database') and schema_id = 2000 and type = 5;
+update sys.functions set system = true where system <> true and name in
('schema_guard') and schema_id = 2000 and type = 1;
CREATE function sys.url_extract_host(url string, no_www bool) RETURNS STRING
EXTERNAL NAME url."extractURLHost";
GRANT EXECUTE ON FUNCTION url_extract_host(string, bool) TO PUBLIC;
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
@@ -4194,6 +4194,25 @@ drop view sys.dump_start_sequences;
drop view sys.dump_tables;
drop view sys.describe_tables;
drop view sys.dump_create_users;
+drop view sys.dump_functions;
+drop view sys.dump_triggers;
+drop function sys.schema_guard;
+drop function sys.replace_first(string, string, string, string);
+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,
@@ -4433,9 +4452,10 @@ BEGIN
INSERT INTO sys.dump_statements VALUES ((SELECT COUNT(*) FROM
sys.dump_statements) + 1, 'COMMIT;');
RETURN sys.dump_statements;
END;
-update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users')
AND schema_id = 2000;
+update sys._tables set system = true where name in
('describe_partition_tables', 'dump_partition_tables', 'dump_sequences',
'dump_start_sequences', 'describe_tables', 'dump_tables', 'dump_create_users',
'dump_functions', 'dump_triggers') AND schema_id = 2000;
update sys.functions set system = true where system <> true and name in
('dump_table_data') and schema_id = 2000 and type = 2;
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
