Changeset: f877e52442e4 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/f877e52442e4
Branch: Sep2022
Log Message:
merged
diffs (truncated from 1803 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/Dependencies/Tests/dependency_functions.test
b/sql/test/Dependencies/Tests/dependency_functions.test
--- a/sql/test/Dependencies/Tests/dependency_functions.test
+++ b/sql/test/Dependencies/Tests/dependency_functions.test
@@ -52,6 +52,9 @@ DEP_FUNC
dq
prepare_esc
DEP_FUNC
+dq
+schema_guard
+DEP_FUNC
dump_table_data
dump_database
DEP_FUNC
@@ -64,9 +67,6 @@ DEP_FUNC
f1
f3
DEP_FUNC
-fqn
-schema_guard
-DEP_FUNC
getproj4
st_transform
DEP_FUNC
@@ -79,9 +79,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
@@ -116,12 +113,12 @@ DEP_FUNC
dq
prepare_esc
DEP_FUNC
+dq
+schema_guard
+DEP_FUNC
dump_table_data
dump_database
DEP_FUNC
-fqn
-schema_guard
-DEP_FUNC
getproj4
st_transform
DEP_FUNC
@@ -134,9 +131,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/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -289,12 +289,12 @@ DEP_FUNC
dq
prepare_esc
DEP_FUNC
+dq
+schema_guard
+DEP_FUNC
dump_table_data
dump_database
DEP_FUNC
-fqn
-schema_guard
-DEP_FUNC
getproj4
st_transform
DEP_FUNC
@@ -307,9 +307,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/Tests/select_window_pushdown.test
b/sql/test/Tests/select_window_pushdown.test
--- a/sql/test/Tests/select_window_pushdown.test
+++ b/sql/test/Tests/select_window_pushdown.test
@@ -5,7 +5,6 @@ CREATE TABLE Test (k int, v int);
statement ok
INSERT INTO Test SELECT value % 10 as k, value as v FROM generate_series(1,
100);
-
# simple eq filter on the partition key, must be pushed down,
# while the flag filter cannot be safely pushed down
query T nosort
@@ -25,14 +24,13 @@ project (
| | | project (
| | | | project (
| | | | | select (
-| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
-| | | | | ) [ ("test"."k") = (int(32) "10") ]
-| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
-| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
-| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
-| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
-) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
-
+| | | | | | table("sys"."test") [ "test"."k" NOT NULL, "test"."v" NOT NULL
UNIQUE ]
+| | | | | ) [ ("test"."k" NOT NULL) = (int(32) "10") ]
+| | | | ) [ "test"."k" NOT NULL as "t1"."k", "test"."v" NOT NULL UNIQUE as
"t1"."v", "sys"."mod"("test"."v" NOT NULL UNIQUE, int(32) "2") NOT NULL as
"%1"."%1", "sys"."="("%1"."%1" NOT NULL, int(32) "0") NOT NULL as "t1"."flag" ]
+| | | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL ]
[ "t1"."k" ASC NOT NULL, "t1"."v" NULLS LAST NOT NULL UNIQUE ]
+| | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL,
"sys"."rank"("sys"."star"(), "sys"."diff"("t1"."k" NOT NULL),
"sys"."diff"("t1"."v" NOT NULL UNIQUE)) as "t2"."rank" ]
+| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
+) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
# simple range filter on the partition key
query T nosort
@@ -52,14 +50,13 @@ project (
| | | project (
| | | | project (
| | | | | select (
-| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
-| | | | | ) [ (int(32) "10") <= ("test"."k") <= (int(32) "50") ]
-| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%1"."%1", "sys"."="("%1"."%1", int(32)
"0") as "t1"."flag" ]
-| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
-| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
-| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
-) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
-
+| | | | | | table("sys"."test") [ "test"."k" NOT NULL, "test"."v" NOT NULL
UNIQUE ]
+| | | | | ) [ (int(32) "10") <= ("test"."k" NOT NULL) <= (int(32) "50") ]
+| | | | ) [ "test"."k" NOT NULL as "t1"."k", "test"."v" NOT NULL UNIQUE as
"t1"."v", "sys"."mod"("test"."v" NOT NULL UNIQUE, int(32) "2") NOT NULL as
"%1"."%1", "sys"."="("%1"."%1" NOT NULL, int(32) "0") NOT NULL as "t1"."flag" ]
+| | | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL ]
[ "t1"."k" ASC NOT NULL, "t1"."v" NULLS LAST NOT NULL UNIQUE ]
+| | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL,
"sys"."rank"("sys"."star"(), "sys"."diff"("t1"."k" NOT NULL),
"sys"."diff"("t1"."v" NOT NULL UNIQUE)) as "t2"."rank" ]
+| ) [ ("t1"."flag" NOT NULL) = (boolean(1) "false"), ("t2"."rank") = (int(32)
"1") ]
+) [ "t1"."k" NOT NULL as "t2"."k", "t1"."v" NOT NULL UNIQUE as "t2"."v",
"t1"."flag" NOT NULL as "t2"."flag", "t2"."rank" NOT NULL ]
# simple not in filter on the partition key
query T nosort
@@ -79,14 +76,13 @@ project (
| | | project (
| | | | project (
| | | | | select (
-| | | | | | table("sys"."test") [ "test"."k", "test"."v" ]
-| | | | | ) [ ("test"."k") notin (int(32) "10", int(32) "20", int(32) "30") ]
-| | | | ) [ "test"."k" as "t1"."k", "test"."v" as "t1"."v",
"sys"."mod"("test"."v", int(32) "2") as "%2"."%2", "sys"."="("%2"."%2", int(32)
"0") as "t1"."flag" ]
-| | | ) [ "t1"."k", "t1"."v", "t1"."flag" ] [ "t1"."k" ASC, "t1"."v" NULLS
LAST ]
-| | ) [ "t1"."k", "t1"."v", "t1"."flag", "sys"."rank"("sys"."star"(),
"sys"."diff"("t1"."k"), "sys"."diff"("t1"."v")) as "t2"."rank" ]
-| ) [ ("t1"."flag") = (boolean(1) "false"), ("t2"."rank") = (int(32) "1") ]
-) [ "t1"."k" as "t2"."k", "t1"."v" as "t2"."v", "t1"."flag" as "t2"."flag",
"t2"."rank" ]
-
+| | | | | | table("sys"."test") [ "test"."k" NOT NULL, "test"."v" NOT NULL
UNIQUE ]
+| | | | | ) [ ("test"."k" NOT NULL) notin (int(32) "10", int(32) "20", int(32)
"30") ]
+| | | | ) [ "test"."k" NOT NULL as "t1"."k", "test"."v" NOT NULL UNIQUE as
"t1"."v", "sys"."mod"("test"."v" NOT NULL UNIQUE, int(32) "2") NOT NULL as
"%2"."%2", "sys"."="("%2"."%2" NOT NULL, int(32) "0") NOT NULL as "t1"."flag" ]
+| | | ) [ "t1"."k" NOT NULL, "t1"."v" NOT NULL UNIQUE, "t1"."flag" NOT NULL ]
[ "t1"."k" ASC NOT NULL, "t1"."v" NULLS LAST NOT NULL UNIQUE ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
