Changeset: e5bcdcbaf5c2 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=e5bcdcbaf5c2
Modified Files:
dump_output.sql
sql/scripts/76_dump.sql
Branch: monetdbe-proxy
Log Message:
Dump table data.
diffs (229 lines):
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -52,6 +52,7 @@ CREATE TABLE "sys"."foo" ("i" INTEGER, "
create function "sys"."f1" () returns int begin return 10; end;
create procedure "sys"."f1" (i int) begin declare x int; end;
create procedure "sys"."f1" () begin declare x int; end;
+CREATE TABLE "sys"."tbl_with_data" ("c1" INTEGER, "c2" BIGINT, "c3" BINARY
LARGE OBJECT, "c4" BOOLEAN, "c5" CHARACTER LARGE OBJECT, "c6" DATE, "c7"
INTERVAL DAY, "c8" DECIMAL(18,3), "c9" DECIMAL(5), "c10" DECIMAL(5,2), "c11"
DOUBLE, "c12" FLOAT(5), "c13" FLOAT(5,4), "c14" GEOMETRY(POINT), "c18" INTERVAL
YEAR, "c19" INTERVAL YEAR TO MONTH, "c20" INTERVAL MONTH, "c21" REAL, "c22"
INTERVAL DAY, "c23" INTERVAL DAY TO HOUR, "c24" INTERVAL HOUR, "c25" INTERVAL
HOUR TO MINUTE, "c26" TIME, "c27" TIMESTAMP, "c28" TIMESTAMP(2), "c29"
TIMESTAMP WITH TIME ZONE, "c30" JSON, "c31" INET, "c32" URL, "c33" UUID);
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");
@@ -82,4 +83,8 @@ INSERT INTO sys.privileges VALUES ((SELE
INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions() fqn WHERE fqn.nme = 'sys.f1(INTEGER)' AND fqn.tpe
= 'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),false);
INSERT INTO sys.privileges VALUES ((SELECT fqn.id FROM
fully_qualified_functions() fqn WHERE fqn.nme = 'sys.f1()' AND fqn.tpe =
'PROCEDURE' ),(SELECT id FROM auths a WHERE a.name = 'voc' ),(SELECT
pc.privilege_code_id FROM privilege_codes pc WHERE pc.privilege_code_name =
'EXECUTE' ),(SELECT id FROM auths g WHERE g.name = 'monetdb' ),true);
INSERT INTO sys.privileges VALUES (0,(SELECT id FROM auths a WHERE a.name =
'voc' ),(SELECT pc.privilege_code_id FROM privilege_codes pc WHERE
pc.privilege_code_name = 'UPDATE' ),(SELECT id FROM auths g WHERE g.name =
'monetdb' ),false);
+COPY 3 RECORDS INTO "sys"."tbl_with_data"("c1", "c2", "c3", "c4", "c5", "c6",
"c7", "c8", "c9", "c10", "c11", "c12", "c13", "c14", "c18", "c19", "c20",
"c21", "c22", "c23", "c24", "c25", "c26", "c27", "c28", "c29", "c30", "c31",
"c32", "c33") FROM STDIN USING DELIMITERS '|','\n','"';
+1234|5678|90|true|"Hello\n \\|\"
World"|2020-12-20|10.000|1023.345|12345|123.45|1123.455|1122133.5|121233.45|"POINT
(5.1
34.5)"|2000|4000|8000|65333.414|8000.000|4000.000|2000.000|1000.000|14:18:18|2015-05-22
14:18:17.780331|2015-05-22 00:00:00.00|2015-05-22
13:18:17.780331+01:00|"{\"price\":9}"|10.1.0.0/16|"https://m...@www.monetdb.org:458/Doc/Abc.html?lang=nl&sort=asc#example";|65950c76-a2f6-4543-660a-b849cf5f2453
+null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null
+null|null|null|null|"null"|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null|null
COMMIT;
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
@@ -141,32 +141,102 @@ END;
--Because we need to explicitly overwrite the creation order column "o" in
those cases. After inserting the dump statements for sys.functions and
table-likes,
--we can restart the auto-increment sequence with a sensible value for
following dump statements.
-CREATE SEQUENCE sys._auto_increment;
-CREATE TABLE sys.dump_statements(o INT DEFAULT NEXT VALUE FOR
sys._auto_increment, s STRING, PRIMARY KEY (o));
+CREATE TABLE sys.dump_statements(o INT, s STRING, PRIMARY KEY (o));
+
+CREATE FUNCTION current_size_dump_statements() RETURNS INT BEGIN RETURN SELECT
COUNT(*) FROM dump_statements; END;
+
+CREATE PROCEDURE _dump_table_data(sch STRING, tbl STRING) BEGIN
+
+ DECLARE k INT;
+ SET k = (SELECT MIN(c.id) FROM columns c, tables t WHERE c.table_id = t.id
AND t.name = tbl);
+ IF k IS NOT NULL THEN
+
+ DECLARE cname STRING;
+ DECLARE ctype STRING;
+ SET cname = (SELECT c.name FROM sys.columns c WHERE c.id = k);
+ SET ctype = (SELECT c.type FROM sys.columns c WHERE c.id = k);
+
+ DECLARE COPY_INTO_STMT STRING;
+ DECLARE _cnt INT;
+ SET _cnt = (SELECT MIN(s.count) FROM sys.storage() s WHERE
s.schema = sch AND s.table = tbl);
+
+ IF _cnt > 0 THEN
+ SET COPY_INTO_STMT = 'COPY ' || _cnt || ' RECORDS INTO
' || FQN(sch, tbl) || '(' || DQ(cname);
+
+ DECLARE SELECT_DATA_STMT STRING;
+ SET SELECT_DATA_STMT = 'SELECT
current_size_dump_statements() + RANK() OVER(), ' || prepare_esc(cname, ctype);
+
+ DECLARE M INT;
+ SET M = (SELECT MAX(c.id) FROM columns c, tables t
WHERE c.table_id = t.id AND t.name = tbl);
+
+ IF (k < M) THEN
+ SET k = (SELECT MIN(c.id) FROM columns c,
tables t WHERE c.table_id = t.id AND t.name = tbl AND c.id > k);
+ END IF;
+
+ WHILE (k < M) DO
+ SET cname = (SELECT c.name FROM sys.columns c
WHERE c.id = k);
+ SET ctype = (SELECT c.type FROM sys.columns c
WHERE c.id = k);
+ SET COPY_INTO_STMT = (COPY_INTO_STMT || ', ' ||
DQ(cname));
+ SET SELECT_DATA_STMT = SELECT_DATA_STMT || '||
''|'' || ' || prepare_esc(cname, ctype);
+ SET k = (SELECT MIN(c.id ) FROM columns c,
tables t WHERE c.table_id = t.id AND t.name = tbl AND c.id > k);
+ END WHILE;
---Because ALTER SEQUENCE statements are not allowed in procedures,
---we have to do a really nasty hack to restart the _auto_increment sequence.
+ SET cname = (SELECT c.name FROM sys.columns c WHERE
c.id = k);
+ SET ctype = (SELECT c.type FROM sys.columns c WHERE
c.id = k);
+ SET COPY_INTO_STMT = (COPY_INTO_STMT || ', ' ||
DQ(cname));
+ SET SELECT_DATA_STMT = SELECT_DATA_STMT || '|| ''|'' ||
' || prepare_esc(cname, ctype);
+
+ SET COPY_INTO_STMT = (COPY_INTO_STMT || ') FROM STDIN
USING DELIMITERS ''|'',''\\n'',''"'';');
+ SET SELECT_DATA_STMT = SELECT_DATA_STMT || ' FROM ' ||
FQN(sch, tbl);
+
+ insert into dump_statements VALUES
(current_size_dump_statements() + 1, COPY_INTO_STMT);
+
+ CALL sys.EVAL('INSERT INTO dump_statements ' ||
SELECT_DATA_STMT || ';');
+ END IF;
+ END IF;
+END;
+
+CREATE PROCEDURE dump_table_data() BEGIN
+
+ DECLARE i INT;
+ SET i = (SELECT MIN(t.id) FROM sys.tables t, sys.table_types ts WHERE
t.type = ts.table_type_id AND ts.table_type_name = 'TABLE' AND NOT t.system);
-CREATE FUNCTION sys.restart_sequence(sch STRING, seq STRING, val BIGINT)
RETURNS BIGINT EXTERNAL NAME sql."restart";
+ IF i IS NOT NULL THEN
+ DECLARE M INT;
+ SET M = (SELECT MAX(t.id) FROM sys.tables t, sys.table_types ts
WHERE t.type = ts.table_type_id AND ts.table_type_name = 'TABLE' AND NOT
t.system);
+
+ DECLARE sch STRING;
+ DECLARE tbl STRING;
+
+ WHILE i < M DO
+ set sch = (SELECT s.name FROM sys.tables t, sys.schemas
s WHERE s.id = t.schema_id AND t.id = i);
+ set tbl = (SELECT t.name FROM sys.tables t, sys.schemas
s WHERE s.id = t.schema_id AND t.id = i);
+ CALL _dump_table_data(sch, tbl);
+ SET i = (SELECT MIN(t.id) FROM sys.tables t,
sys.table_types ts WHERE t.type = ts.table_type_id AND ts.table_type_name =
'TABLE' AND NOT t.system AND t.id > i);
+ END WHILE;
+
+ set sch = (SELECT s.name FROM sys.tables t, sys.schemas s WHERE
s.id = t.schema_id AND t.id = i);
+ set tbl = (SELECT t.name FROM sys.tables t, sys.schemas s WHERE
s.id = t.schema_id AND t.id = i);
+ CALL _dump_table_data(sch, tbl);
+ END IF;
+END;
CREATE FUNCTION sys.dump_database(describe BOOLEAN) RETURNS TABLE(o int, stmt
STRING)
BEGIN
SET SCHEMA sys;
TRUNCATE dump_statements;
- DECLARE dummy_result BIGINT; --HACK: otherwise I cannot call
restart_sequence.
- SET dummy_result = sys.restart_sequence('sys', '_auto_increment', 0);
- INSERT INTO dump_statements(s) VALUES ('START TRANSACTION;');
- INSERT INTO dump_statements(s) VALUES ('SET SCHEMA "sys";');
+ INSERT INTO dump_statements VALUES (0, 'START TRANSACTION;');
+ INSERT INTO dump_statements VALUES (current_size_dump_statements() + 1,
'SET SCHEMA "sys";');
- INSERT INTO dump_statements(s) --dump_create_roles
- SELECT 'CREATE ROLE ' || sys.dq(name) || ';' FROM sys.auths
+ INSERT INTO dump_statements --dump_create_roles
+ SELECT current_size_dump_statements() + RANK() OVER(), 'CREATE
ROLE ' || sys.dq(name) || ';' FROM sys.auths
WHERE name NOT IN (SELECT name FROM sys.db_user_info)
AND grantor <> 0;
- INSERT INTO dump_statements(s) --dump_create_users
- SELECT
+ INSERT INTO dump_statements --dump_create_users
+ SELECT current_size_dump_statements() + RANK() OVER(),
'CREATE USER ' || sys.dq(ui.name) || ' WITH ENCRYPTED PASSWORD ' ||
sys.sq(sys.password_hash(ui.name)) ||
' NAME ' || sys.sq(ui.fullname) || ' SCHEMA sys;'
@@ -175,16 +245,16 @@ BEGIN
AND ui.name <> 'monetdb'
AND ui.name <> '.snapshot';
- INSERT INTO dump_statements(s) --dump_create_schemas
- SELECT
+ INSERT INTO dump_statements --dump_create_schemas
+ SELECT current_size_dump_statements() + RANK() OVER(),
'CREATE SCHEMA ' || sys.dq(s.name) || ifthenelse(a.name <>
'sysadmin', ' AUTHORIZATION ' || a.name, ' ') || ';'
FROM sys.schemas s, sys.auths a
WHERE s.authorization = a.id AND s.system = FALSE;
- INSERT INTO dump_statements(s) SELECT stmt FROM
sys.dump_user_defined_types();
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_user_defined_types();
- INSERT INTO dump_statements(s) --dump_add_schemas_to_users
- SELECT
+ INSERT INTO dump_statements --dump_add_schemas_to_users
+ SELECT current_size_dump_statements() + RANK() OVER(),
'ALTER USER ' || sys.dq(ui.name) || ' SET SCHEMA ' ||
sys.dq(s.name) || ';'
FROM sys.db_user_info ui, sys.schemas s
WHERE ui.default_schema = s.id
@@ -192,45 +262,42 @@ BEGIN
AND ui.name <> '.snapshot'
AND s.name <> 'sys';
- INSERT INTO dump_statements(s) --dump_grant_user_priviledges
- SELECT
+ INSERT INTO dump_statements --dump_grant_user_priviledges
+ SELECT current_size_dump_statements() + RANK() OVER(),
'GRANT ' || sys.dq(a2.name) || ' ' || ifthenelse(a1.name =
'public', 'PUBLIC', sys.dq(a1.name)) || ';'
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) SELECT stmt FROM sys.dump_sequences();
-
- --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;
- SET offs = (SELECT max(o) FROM dump_statements) - (SELECT min(ids.id)
FROM (select id from sys.tables union select id from sys.functions) ids(id));
-
- INSERT INTO dump_statements SELECT f.o + offs, f.stmt FROM
sys.dump_functions() f;
- INSERT INTO dump_statements SELECT t.o + offs, t.stmt FROM
sys.dump_tables() t;
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_sequences();
- SET offs = (SELECT max(o) + 1 FROM dump_statements);
- SET dummy_result = sys.restart_sequence('sys', '_auto_increment', offs);
- --END OF COMPLICATED DEPENDENCY STUFF.
+ --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
+ FROM (
+ SELECT * FROM sys.dump_functions() f
+ UNION
+ SELECT * FROM sys.dump_tables() t
+ ) AS stmts(o, s);
- INSERT INTO dump_statements(s) SELECT stmt FROM
sys.dump_column_defaults();
- INSERT INTO dump_statements(s) SELECT stmt FROM
sys.dump_table_constraint_type();
- INSERT INTO dump_statements(s) SELECT stmt FROM sys.dump_indices();
- INSERT INTO dump_statements(s) SELECT stmt FROM sys.dump_foreign_keys();
- INSERT INTO dump_statements(s) SELECT stmt FROM
sys.dump_partition_tables();
- INSERT INTO dump_statements(s) SELECT stmt from sys.dump_triggers();
- INSERT INTO dump_statements(s) SELECT stmt FROM sys.dump_comments();
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_column_defaults();
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_table_constraint_type();
+ 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_comments();
--We are dumping ALL privileges so we need to erase existing privileges
on the receiving side;
- INSERT INTO dump_statements(s) VALUES ('TRUNCATE sys.privileges;');
- INSERT INTO dump_statements(s) SELECT stmt FROM sys.dump_privileges();
+ INSERT INTO dump_statements VALUES (current_size_dump_statements() + 1,
'TRUNCATE sys.privileges;');
+ INSERT INTO dump_statements SELECT current_size_dump_statements() +
RANK() OVER(), stmt FROM sys.dump_privileges();
- --TODO dumping table data
+ CALL dump_table_data();
+
--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(s) VALUES ('COMMIT;');
+ 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
