Changeset: 8bfd7eb27841 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=8bfd7eb27841
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Implement (most of) dump_sequences logic.
diffs (138 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -310,6 +310,50 @@ RETURN
FROM describe_partition_tables();
END;
+CREATE FUNCTION describe_sequences()
+ RETURNS TABLE(
+ sch STRING,
+ seq STRING,
+ s BIGINT,
+ rs BIGINT,
+ mi BIGINT,
+ ma BIGINT,
+ inc BIGINT,
+ cache BIGINT,
+ cycle BOOLEAN)
+BEGIN
+ RETURN SELECT
+ s.name as sch,
+ seq.name as seq,
+ seq."start",
+ get_value_for(s.name, seq.name) AS "restart",
+ seq."minvalue",
+ seq."maxvalue",
+ seq."increment",
+ seq."cacheinc",
+ seq."cycle"
+ FROM sys.sequences seq, sys.schemas s
+ WHERE s.id = seq.schema_id
+ ORDER BY s.name, seq.name;
+END;
+
+CREATE FUNCTION dump_sequences() RETURNS TABLE(stmt STRING) BEGIN
+RETURN
+ SELECT
+ 'CREATE SEQUENCE ' || FQTN(sch, seq) || 'AS BIGINT ' ||
+ CASE WHEN "s" <> 0 THEN ' START WITH ' || "s" ELSE '' END ||
+ CASE WHEN "inc" <> 1 THEN ' INCREMENT BY ' || "inc" ELSE '' END
||
+ CASE WHEN "mi" <> 0 THEN ' MINVALUE ' || "mi" ELSE '' END ||
+ CASE WHEN "ma" <> 0 THEN ' MAXVALUE ' || "ma" ELSE '' END ||
+ CASE WHEN "cache" <> 1 THEN ' CACHE ' || "cache" ELSE '' END ||
+ CASE WHEN "cycle" THEN ' CYCLE' ELSE '' END || ';'
+ FROM describe_sequences();
+END;
+
+--We cannot directly use dump_sequences() later because the temporary table
"dump_statements" creates a SEQUENCE due to AUTO_INCREMENT.
+--So we first dump the current sequences into a temp table "_dump_sequences"
which will use to create the database_dump.
+
+CREATE TEMPORARY TABLE _dump_sequences AS SELECT * FROM dump_sequences();
CREATE TEMPORARY TABLE dump_statements(o INT AUTO_INCREMENT, s STRING, PRIMARY
KEY (o));
CREATE PROCEDURE dump_database(describe BOOLEAN)
@@ -360,10 +404,7 @@ BEGIN
FROM sys.auths a1, sys.auths a2, sys.user_role ur
WHERE a1.id = ur.login_id AND a2.id = ur.role_id;
- INSERT INTO dump_statements(s) --dump_create_sequences
- SELECT 'CREATE SEQUENCE ' || DQ(sch.name) || '.' ||
DQ(seq.name) || ' AS INTEGER;'
- FROM sys.schemas sch, sys.sequences seq
- WHERE sch.id = seq.schema_id;
+ 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)
@@ -403,7 +444,6 @@ 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 details on SEQUENCES
--TODO STREAM TABLE?
--TODO functions
--TODO VIEW
@@ -413,6 +453,7 @@ BEGIN
--TODO COLUMN level grants
--TODO User Defined Types? sys.types
--TODO Triggers
+ --TODO ALTER SEQUENCE using RESTART WITH after importing table_data.
INSERT INTO dump_statements(s) VALUES ('COMMIT;');
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -1,26 +1,21 @@
START TRANSACTION;
CREATE ROLE "king";
-CREATE USER "voc" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
-NAME 'VOC Explorer' SCHEMA sys;
-CREATE USER "voc2" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
- NAME 'VOC Explorer' SCHEMA sys;
+CREATE USER "voc" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
NAME 'VOC Explorer' SCHEMA sys;
+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"."seq_18814" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8219" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_9119" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8917" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_10005" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8264" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8325" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8231" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8441" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8546" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8259" AS INTEGER;
-CREATE SEQUENCE "sys"."seq_8308" AS INTEGER;
-COMMENT ON SEQUENCE "sys"."seq_8219" IS 'This is a comment on a sequence.' ;
+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));
CREATE TABLE "sys"."bob" ("ts" TIMESTAMP(3) );
@@ -56,10 +51,10 @@ 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 SET NULL ON UPDATE CASCADE;
+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 "sys"."scorers" ADD TABLE "sys"."unknown_scorers" AS PARTITION
FOR NULL VALUES;
-ALTER TABLE "sys"."scorers" ADD TABLE "sys"."lower_scorers" AS PARTITION IN
(4,3,2,1,0);
-ALTER TABLE "sys"."scorers" ADD TABLE "sys"."higher_scorers" AS PARTITION IN
(9,8,7,6,5);
+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);
ALTER TABLE "sys"."splitted" ADD TABLE "sys"."first_decade" AS PARTITION FROM
RANGE MINVALUE TO '2010-01-01 00:00:00.000000' WITH NULL VALUES;
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";
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
