Changeset: 76407e0ba055 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=76407e0ba055
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Implement schema guards for functions and views.
diffs (95 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -11,6 +11,13 @@ CREATE FUNCTION ALTER_TABLE(s STRING, t
CREATE FUNCTION comment_on(ob STRING, id STRING, r STRING) RETURNS STRING
BEGIN RETURN ifthenelse(r IS NOT NULL, 'COMMENT ON ' || ob || ' ' || id || '
IS ' || SQ(r) || ';', ''); END;
+--We need pcre to implement a header guard which means adding the schema of an
object explicitely to its identifier.
+CREATE FUNCTION replace_first(ori STRING, pat STRING, rep STRING, flg STRING)
RETURNS STRING EXTERNAL NAME "pcre"."replace_first";
+CREATE FUNCTION schema_guard(sch STRING, nme STRING, stmt STRING) RETURNS
STRING BEGIN
+RETURN
+ SELECT replace_first(stmt, '(\\s*"?' || sch || '"?\\s*\\.|)\\s*"?' ||
nme || '"?\\s*', ' ' || FQTN(sch, nme) || ' ', 'imsx');
+END;
+
CREATE FUNCTION dump_type(type STRING, digits INT, scale INT) RETURNS STRING
BEGIN
RETURN
CASE
@@ -360,7 +367,7 @@ RETURN
END;
CREATE FUNCTION dump_functions() RETURNS TABLE (o INT, stmt STRING) BEGIN
- RETURN SELECT f.o, 'SET SCHEMA ' || DQ(f.sch) || ';' || f.def || 'SET
SCHEMA "sys";' FROM describe_functions() f;
+ RETURN SELECT f.o, schema_guard(f.sch, f.fun, f.def) FROM
describe_functions() f;
END;
CREATE FUNCTION describe_tables() RETURNS TABLE(o INT, sch STRING, tab STRING,
typ STRING, col STRING, opt STRING) BEGIN
@@ -377,7 +384,7 @@ RETURN
WHEN ts.table_type_name = 'MERGE TABLE' THEN
dump_merge_table_partition_expressions(t.id)
WHEN ts.table_type_name = 'VIEW' THEN
- t.query
+ schema_guard(s.name, t.name, t.query)
ELSE
''
END
@@ -499,10 +506,8 @@ BEGIN
SELECT comment_on('COLUMN', DQ(s.name) || '.' || DQ(t.name) || '.' ||
DQ(c.name), rem.remark)
FROM sys.columns c JOIN sys.comments rem ON c.id = rem.id,
sys.tables t, sys.schemas s WHERE c.table_id = t.id AND t.schema_id = s.id AND
NOT t.system;
- --TODO VIEW
- --TODO SCHEMA GUARD
--TODO Triggers
- --TODO COMMENTS ON TABLE
+ --TODO COMMENTS ON TABLE and add schema to commented objects identifier
--TODO TABLE level grants
--TODO COLUMN level grants
--TODO User Defined Types? sys.types
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -7,15 +7,15 @@ CREATE SCHEMA "sbar" AUTHORIZATION monet
CREATE SCHEMA "sfoo" AUTHORIZATION monetdb;
COMMENT ON SCHEMA "sbar" IS 'This is a comment on sbar' ;
ALTER USER "voc" SET SCHEMA "sfoo";
-CREATE SEQUENCE "sys"."seq1"AS BIGINT START WITH 5 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
-CREATE SEQUENCE "sys"."seq2"AS BIGINT START WITH 4 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
-CREATE SEQUENCE "sys"."seq3"AS BIGINT START WITH 4 MINVALUE 4 MAXVALUE 10
CACHE 2 CYCLE;
-CREATE SEQUENCE "sys"."seq4"AS BIGINT START WITH 1 MAXVALUE 10 CACHE 2 CYCLE;
-CREATE SEQUENCE "sys"."seq5"AS BIGINT START WITH 1 MAXVALUE 10 CACHE 2;
-CREATE SEQUENCE "sys"."seq6"AS BIGINT START WITH 1 CACHE 2;
-CREATE SEQUENCE "sys"."seq7"AS BIGINT START WITH 1;
-CREATE SEQUENCE "sys"."seq8"AS BIGINT START WITH -10 INCREMENT BY -1 MINVALUE
-10 MAXVALUE -1;
-CREATE SEQUENCE "sys"."seq9"AS BIGINT START WITH 10 MINVALUE 10 MAXVALUE 10;
+CREATE SEQUENCE "sys"."seq1" AS BIGINT START WITH 5 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq2" AS BIGINT START WITH 4 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq3" AS BIGINT START WITH 4 MINVALUE 4 MAXVALUE 10
CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq4" AS BIGINT START WITH 1 MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq5" AS BIGINT START WITH 1 MAXVALUE 10 CACHE 2;
+CREATE SEQUENCE "sys"."seq6" AS BIGINT START WITH 1 CACHE 2;
+CREATE SEQUENCE "sys"."seq7" AS BIGINT START WITH 1;
+CREATE SEQUENCE "sys"."seq8" AS BIGINT START WITH -10 INCREMENT BY -1
MINVALUE -10 MAXVALUE -1;
+CREATE SEQUENCE "sys"."seq9" AS BIGINT START WITH 10 MINVALUE 10 MAXVALUE 10;
COMMENT ON SEQUENCE "sys"."seq1" IS 'This is a comment on a sequence.' ;
CREATE TABLE "sys"."test" ("s" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."bla" ("s" CHARACTER LARGE OBJECT(10));
@@ -29,8 +29,8 @@ CREATE TABLE "sfoo"."tfoo" ("i" INTEGER)
CREATE TABLE "sfoo"."test" ("s" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."pfoo1" ("i" INTEGER);
CREATE TABLE "sys"."pfoo2" ("i" INTEGER);
-SET SCHEMA "sfoo";create function
-func1(x int, y int) returns int begin return x + y; end;SET SCHEMA "sys";
+create function "sfoo"."func1" (x int, y int) returns int begin return x + y;
end;
+create view "sfoo"."baz" (i) as select func1(t.x, t.y) from (values (10, 1),
(20, 2)) as t(x,y);
CREATE TABLE "sys"."lower_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
CREATE TABLE "sys"."higher_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
CREATE TABLE "sys"."unknown_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
@@ -54,7 +54,7 @@ ALTER TABLE "sbar"."bar" ADD CONSTRAINT
CREATE INDEX "ind1" ON "sys"."ungolo"(x,y);
CREATE IMPRINTS INDEX "ind2" ON "sys"."ungolo"(y,z);
CREATE ORDERED INDEX "ind3" ON "sys"."ungolo"(x,z);
-ALTER TABLE "sfoo"."foo" ADD CONSTRAINT "fk_foo_to_bar" FOREIGN KEY("fi","fs")
REFERENCES "sbar"."bar"("bi","bs") ON DELETE CASCADE ON UPDATE SET NULL;
+ALTER TABLE "sfoo"."foo" ADD CONSTRAINT "fk_foo_to_bar" FOREIGN KEY("fi","fs")
REFERENCES "sbar"."bar"("bi","bs") ON DELETE SET NULL ON UPDATE CASCADE;
ALTER TABLE "sys"."scorers" ADD TABLE "sys"."unknown_scorers" AS PARTITION
FOR NULL VALUES;
ALTER TABLE "sys"."scorers" ADD TABLE "sys"."lower_scorers" AS PARTITION IN
(0,1,2,3,4);
ALTER TABLE "sys"."scorers" ADD TABLE "sys"."higher_scorers" AS PARTITION IN
(5,6,7,8,9);
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
