[prev in list] [next in list] [prev in thread] [next in thread]
List: monetdb-checkins
Subject: MonetDB: monetdbe-proxy - Dump previously known start value of s...
From: Aris Koning <commits+aris.koning=monetdbsolutions.com () monetdb ! org>
Date: 2020-12-24 21:18:37
Message-ID: hg.3dd41860827e.1608844717.6315528441665844383 () monetdb-vm0 ! spin-off ! cwi ! nl
[Download RAW message or body]
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
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic