Changeset: 3bf904cef234 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3bf904cef234
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Implemented all comment types.
diffs (137 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -9,8 +9,6 @@ CREATE FUNCTION DQ (s STRING) RETURNS ST
CREATE FUNCTION FQTN(s STRING, t STRING) RETURNS STRING BEGIN RETURN DQ(s) ||
'.' || DQ(t); END;
CREATE FUNCTION ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN RETURN
'ALTER TABLE ' || FQTN(s, t) || ' '; END;
-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
@@ -442,6 +440,43 @@ CREATE FUNCTION dump_triggers() RETURNS
SELECT schema_guard(sch, tab, def) FROM describe_triggers();
END;
+CREATE FUNCTION describe_comments() RETURNS TABLE(id INT, tpe STRING, fqn
STRING, rem STRING) BEGIN
+ RETURN
+ SELECT o.id, o.tpe, o.nme, c.remark FROM (
+
+ SELECT id, 'SCHEMA', DQ(name) FROM schemas
+
+ UNION ALL
+
+ SELECT t.id, CASE WHEN ts.table_type_name = 'VIEW' THEN
'VIEW' ELSE 'TABLE' END, FQTN(s.name, t.name)
+ FROM schemas s JOIN tables t ON s.id = t.schema_id JOIN
table_types ts ON t.type = ts.table_type_id
+ WHERE NOT s.name <> 'tmp'
+
+ UNION ALL
+
+ SELECT c.id, 'COLUMN', FQTN(s.name, t.name) || '.' ||
DQ(c.name) FROM sys.columns c, sys.tables t, sys.schemas s WHERE c.table_id =
t.id AND t.schema_id = s.id
+
+ UNION ALL
+
+ SELECT idx.id, 'INDEX', FQTN(s.name, idx.name) FROM
sys.idxs idx, sys._tables t, sys.schemas s WHERE idx.table_id = t.id AND
t.schema_id = s.id
+
+ UNION ALL
+
+ SELECT seq.id, 'SEQUENCE', FQTN(s.name, seq.name) FROM
sys.sequences seq, schemas s WHERE seq.schema_id = s.id
+
+ UNION ALL
+
+ SELECT f.id, ft.function_type_keyword, FQTN(s.name,
f.name) FROM functions f, function_types ft, schemas s WHERE f.type =
ft.function_type_id AND f.schema_id = s.id
+
+ ) AS o(id, tpe, nme)
+ JOIN comments c ON c.id = o.id;
+END;
+
+CREATE FUNCTION dump_comments() RETURNS TABLE(stmt STRING) BEGIN
+RETURN
+ SELECT 'COMMENT ON ' || c.tpe || ' ' || c.fqn || ' IS ' || SQ(c.rem) ||
';' FROM describe_comments() c;
+END;
+
--The dump statement should normally have an auto-incremented column
representing the creation order.
--But in cases of db objects that can be interdependent, i.e. functions and
table-likes, we need access to the underlying sequence of the AUTO_INCREMENT
property.
--Because we need to explicitly overwrite the creation order column "o" in
those cases. After inserting the dump statements for functions and table-likes,
@@ -484,11 +519,6 @@ BEGIN
FROM schemas s, auths a
WHERE s.authorization = a.id AND s.system = FALSE;
- INSERT INTO dump_statements(s) --dump_create_comments_on_schemas
- SELECT comment_on('SCHEMA', DQ(s.name), rem.remark)
- FROM schemas s JOIN comments rem ON s.id = rem.id
- WHERE NOT s.system;
-
INSERT INTO dump_statements(s) --dump_add_schemas_to_users
SELECT
'ALTER USER ' || DQ(ui.name) || ' SET SCHEMA ' || DQ(s.name) || ';'
@@ -506,13 +536,6 @@ BEGIN
INSERT INTO dump_statements(s) SELECT * FROM dump_sequences();
- INSERT INTO dump_statements(s) --dump_create_comments_on_sequences
- SELECT comment_on('SEQUENCE', DQ(sch.name) || '.' || DQ(seq.name),
rem.remark)
- FROM
- sys.schemas sch,
- sys.sequences seq JOIN sys.comments rem ON seq.id =
rem.id
- WHERE sch.id = seq.schema_id;
-
--START OF COMPLICATED DEPENDENCY STUFF:
--functions and table-likes can be interdependent. They should be
inserted in the order of their catalogue id.
DECLARE offs INT;
@@ -533,15 +556,8 @@ BEGIN
INSERT INTO dump_statements(s) SELECT * FROM dump_partition_tables();
INSERT INTO dump_statements(s) SELECT * from dump_triggers();
- INSERT INTO dump_statements(s) --dump_create_comments_on_indices
- SELECT comment_on('INDEX', DQ(i.name), rem.remark)
- FROM sys.idxs i JOIN sys.comments rem ON i.id = rem.id;
+ INSERT INTO dump_statements(s) SELECT * FROM dump_comments();
- INSERT INTO dump_statements(s) --dump_create_comments_on_columns
- 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 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
@@ -5,7 +5,6 @@ CREATE USER "voc" WITH ENCRYPTED PASSWOR
CREATE USER "voc2" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
NAME 'VOC Explorer' SCHEMA sys;
CREATE SCHEMA "sbar" AUTHORIZATION monetdb;
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;
@@ -16,7 +15,6 @@ CREATE SEQUENCE "sys"."seq6" AS BIGINT
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));
CREATE TABLE "sys"."bob" ("ts" TIMESTAMP(3) );
@@ -47,6 +45,7 @@ CREATE TABLE "sys"."first_decade" ("stam
CREATE TABLE "sys"."second_decade" ("stamp" TIMESTAMP , "val" INTEGER);
CREATE TABLE "sys"."third_decade" ("stamp" TIMESTAMP , "val" INTEGER);
CREATE TABLE "sys"."p1" ("i" INTEGER);
+create or replace window "sys"."stddev" (val bigint) returns double external
name "sql"."stdevp";
ALTER TABLE "sys"."yoyo" ALTER COLUMN "tsz" SET DEFAULT 'BLABOLO';
ALTER TABLE "sys"."bolo" ADD CONSTRAINT "cpk" PRIMARY KEY ("s", "v");
ALTER TABLE "sys"."rolo" ADD CONSTRAINT "rolo_v_pkey" PRIMARY KEY ("v");
@@ -65,6 +64,9 @@ ALTER TABLE "sys"."splitted" ADD TABLE
ALTER TABLE "sys"."splitted" ADD TABLE "sys"."second_decade" AS PARTITION
FROM '2010-01-01 00:00:00.000000' TO '2020-01-01 00:00:00.000000' ;
ALTER TABLE "sys"."m1" ADD TABLE "sys"."p1";
create trigger extra_insert after insert on "sfoo"."tfoo1" referencing new row
as new_row for each statement insert into tfoo2(i) values (new_row.i);
-COMMENT ON INDEX "ind3" IS 'This is a comment on an index.' ;
+COMMENT ON SCHEMA "sbar" IS 'This is a comment on a schema' ;
COMMENT ON COLUMN "sfoo"."tfoo1"."i" IS 'This is a comment on a column.' ;
+COMMENT ON INDEX "sys"."ind3" IS 'This is a comment on an index.' ;
+COMMENT ON SEQUENCE "sys"."seq1" IS 'This is a comment on a sequence.' ;
+COMMENT ON WINDOW "sys"."stddev" IS 'This is a comment on a window function.'
;
COMMIT;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
