Changeset: 3dd41860827e for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=3dd41860827e
Modified Files:
dump_output.sql
sql/scripts/76_dump.sql
Branch: monetdbe-proxy
Log Message:
Dump previously known start value of sequence.
diffs (104 lines):
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -8,15 +8,24 @@ CREATE SCHEMA "sfoo" AUTHORIZATION monet
CREATE TYPE "sfoo"."json" EXTERNAL NAME "json";
CREATE TYPE "sys"."t1" EXTERNAL NAME "json";
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 10 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq2" AS BIGINT START WITH 10 INCREMENT BY 3 MINVALUE 4
MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq3" AS BIGINT START WITH 10 MINVALUE 4 MAXVALUE 10
CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq4" AS BIGINT START WITH 10 MAXVALUE 10 CACHE 2 CYCLE;
+CREATE SEQUENCE "sys"."seq5" AS BIGINT START WITH 10 MAXVALUE 10 CACHE 2;
+CREATE SEQUENCE "sys"."seq6" AS BIGINT START WITH 10 CACHE 2;
+CREATE SEQUENCE "sys"."seq7" AS BIGINT START WITH 10;
+CREATE SEQUENCE "sys"."seq8" AS BIGINT START WITH -5 INCREMENT BY -1 MINVALUE
-10 MAXVALUE -1;
+CREATE SEQUENCE "sys"."seq9" AS BIGINT START WITH 10 MINVALUE 10 MAXVALUE 10;
+UPDATE sys.sequences seq SET start = 5 WHERE name = 'seq1' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 4 WHERE name = 'seq2' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 4 WHERE name = 'seq3' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 1 WHERE name = 'seq4' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 1 WHERE name = 'seq5' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 1 WHERE name = 'seq6' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 1 WHERE name = 'seq7' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = -1 WHERE name = 'seq8' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
+UPDATE sys.sequences seq SET start = 10 WHERE name = 'seq9' AND schema_id =
(SELECT s.id FROM sys.schemas s WHERE s.name = 'sys' );
CREATE TABLE "sys"."test" ("s" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."bla" ("s" CHARACTER LARGE OBJECT(10));
CREATE TABLE "sys"."bob" ("ts" TIMESTAMP(3));
diff --git a/sql/scripts/76_dump.sql b/sql/scripts/76_dump.sql
--- a/sql/scripts/76_dump.sql
+++ b/sql/scripts/76_dump.sql
@@ -52,7 +52,7 @@ CREATE FUNCTION dump_sequences() RETURNS
RETURN
SELECT
'CREATE SEQUENCE ' || FQN(sch, seq) || ' AS BIGINT ' ||
- CASE WHEN "s" <> 0 THEN ' START WITH ' || "s" ELSE '' END ||
+ CASE WHEN "s" <> 0 THEN 'START WITH ' || "rs" 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 ||
@@ -61,6 +61,15 @@ RETURN
FROM describe_sequences();
END;
+CREATE FUNCTION dump_start_sequences() RETURNS TABLE(stmt STRING) BEGIN
+RETURN
+ SELECT
+ 'UPDATE sys.sequences seq SET start = ' || s ||
+ ' WHERE name = ' || SQ(seq) ||
+ ' AND schema_id = (SELECT s.id FROM sys.schemas s WHERE s.name
= ' || SQ(sch) || ');'
+ FROM describe_sequences();
+END;
+
CREATE FUNCTION dump_functions() RETURNS TABLE (o INT, stmt STRING) BEGIN
RETURN SELECT f.o, schema_guard(f.sch, f.fun, f.def) FROM
describe_functions() f;
END;
@@ -227,7 +236,7 @@ BEGIN
SET SCHEMA sys;
TRUNCATE dump_statements;
- INSERT INTO dump_statements VALUES (0, 'START TRANSACTION;');
+ INSERT INTO dump_statements VALUES (1, 'START TRANSACTION;');
INSERT INTO dump_statements VALUES (current_size_dump_statements() + 1,
'SET SCHEMA "sys";');
INSERT INTO dump_statements --dump_create_roles
@@ -269,6 +278,7 @@ BEGIN
WHERE a1.id = ur.login_id AND a2.id = ur.role_id;
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_sequences();
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_start_sequences();
--functions and table-likes can be interdependent. They should be
inserted in the order of their catalogue id.
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(ORDER BY stmts.o), stmts.s
@@ -283,7 +293,7 @@ BEGIN
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_indices();
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_foreign_keys();
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_partition_tables();
- INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt from sys.dump_triggers();
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_triggers();
INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_comments();
--We are dumping ALL privileges so we need to erase existing privileges
on the receiving side;
@@ -294,12 +304,9 @@ BEGIN
CALL dump_table_data();
END IF;
- --TODO ALTER SEQUENCE using RESTART WITH after importing table_data.
--TODO loaders ,procedures, window and filter sys.functions.
--TODO look into order dependent group_concat
--TODO ADD upgrade code
- INSERT INTO dump_statements VALUES (current_size_dump_statements() + 1,
'COMMIT;');
-
RETURN dump_statements;
END;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
