Changeset: 2227f1d52305 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2227f1d52305
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
dump functions while being careful with object dependencies.
diffs (98 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -2,6 +2,7 @@ START TRANSACTION;
--We start with creating static versions of catalogue tables that are going to
be affected by this dump script itself.
CREATE TEMPORARY TABLE _user_sequences AS SELECT * FROM sys.sequences;
+CREATE TEMPORARY TABLE _user_functions AS SELECT * FROM sys.functions f WHERE
NOT f.system;
CREATE FUNCTION SQ (s STRING) RETURNS STRING BEGIN RETURN ' ''' || s || ''' ';
END;
CREATE FUNCTION DQ (s STRING) RETURNS STRING BEGIN RETURN '"' || s || '"';
END; --TODO: Figure out why this breaks with the space
@@ -353,20 +354,35 @@ RETURN
FROM describe_sequences();
END;
+CREATE FUNCTION describe_functions() RETURNS TABLE (o INT, sch STRING, fun
STRING, def STRING) BEGIN
+RETURN
+ SELECT f.id, s.name, f.name, f.func from _user_functions f JOIN schemas
s ON f.schema_id = s.id;
+END;
+
+CREATE FUNCTION dump_functions() RETURNS TABLE (o INT, stmt STRING) BEGIN
+ RETURN SELECT f.o, 'SET SCHEMA ' || DQ(f.sch) || ';' || f.def || 'SET
SCHEMA "sys";' FROM describe_functions() f;
+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 and after inserting the dump statements for functions and
table-likes,
+--Because we need to explicitly overwrite the creation order column "o" in
those cases. After inserting the dump statements for functions and table-likes,
--we can restart the auto-increment sequence with a sensible value for
following dump statements.
CREATE SEQUENCE _auto_increment;
CREATE TEMPORARY TABLE dump_statements(o INT DEFAULT NEXT VALUE FOR
_auto_increment, s STRING, PRIMARY KEY (o));
+--Because ALTER SEQUENCE statements are not allowed in procedures,
+--we have to do a really nasty hack to restart the _auto_increment sequence.
+
+CREATE FUNCTION restart_sequence(sch STRING, seq STRING, val BIGINT) RETURNS
BIGINT EXTERNAL NAME sql."restart";
+
CREATE PROCEDURE dump_database(describe BOOLEAN)
BEGIN
set schema sys;
INSERT INTO dump_statements(s) VALUES ('START TRANSACTION;');
+ INSERT INTO dump_statements(s) VALUES ('SET SCHEMA "sys";');
INSERT INTO dump_statements(s) --dump_create_roles
SELECT 'CREATE ROLE ' || DQ(name) || ';' FROM auths
@@ -418,6 +434,15 @@ BEGIN
sys.sequences seq JOIN sys.comments rem ON seq.id =
rem.id
WHERE sch.id = seq.schema_id;
+ DECLARE current_order INT;
+ SET current_order = (SELECT max(o) FROM dump_statements) - (SELECT
min(ids.id) FROM (select id from tables union select id from functions)
ids(id));
+
+ INSERT INTO dump_statements SELECT f.o + current_order, f.stmt FROM
dump_functions() f;
+
+ SET current_order = (SELECT max(o) + 1 FROM dump_statements);
+ DECLARE dummy_result BIGINT;
+ SET dummy_result = restart_sequence('sys', '_auto_increment',
current_order + 1);
+
INSERT INTO dump_statements(s) --dump_create_tables
SELECT
'CREATE ' || ts.table_type_name || ' ' || DQ(s.name) ||
'.' || DQ(t.name) || dump_column_definition(t.id) ||
@@ -449,15 +474,12 @@ 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 STREAM TABLE?
- --TODO functions
--TODO VIEW
--TODO Triggers
--TODO COMMENTS ON TABLE
--TODO TABLE level grants
--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,4 +1,5 @@
START TRANSACTION;
+SET SCHEMA "sys";
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;
@@ -16,6 +17,8 @@ CREATE SEQUENCE "sys"."seq7"AS BIGINT S
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.' ;
+SET SCHEMA "sfoo";create function
+func1(x int, y int) returns int begin return x + y; end;SET SCHEMA "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) );
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
