Changeset: a8a880db76ae for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=a8a880db76ae
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
support FOREIGN KEY CONSTRAINT.
diffs (112 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -166,6 +166,59 @@ BEGIN
WHERE tp.table_id = tid;
END;
+--SELECT * FROM dump_foreign_keys();
+CREATE FUNCTION describe_foreign_keys() RETURNS TABLE(
+ fk_s STRING, fk_t STRING, fk_c STRING,
+ o INT, fk STRING,
+ pk_s STRING, pk_t STRING, pk_c STRING,
+ on_update STRING, on_delete STRING) BEGIN
+
+ RETURN
+ WITH action_type (id, act) AS (VALUES
+ (0, 'NO ACTION'),
+ (1, 'CASCADE'),
+ (2, 'RESTRICT'),
+ (3, 'SET NULL'),
+ (4, 'SET DEFAULT'))
+ SELECT
+ fs.name AS fsname, fkt.name AS ktname, fkkc.name AS fcname,
+ fkkc.nr AS o, fkk.name AS fkname,
+ ps.name AS psname, pkt.name AS ptname, pkkc.name AS pcname,
+ ou.act as on_update, od.act as on_delete
+ FROM sys._tables fkt,
+ sys.objects fkkc,
+ sys.keys fkk,
+ sys._tables pkt,
+ sys.objects pkkc,
+ sys.keys pkk,
+ sys.schemas ps,
+ sys.schemas fs,
+ action_type ou,
+ action_type od
+
+ WHERE fkt.id = fkk.table_id
+ AND pkt.id = pkk.table_id
+ AND fkk.id = fkkc.id
+ AND pkk.id = pkkc.id
+ AND fkk.rkey = pkk.id
+ AND fkkc.nr = pkkc.nr
+ AND pkt.schema_id = ps.id
+ AND fkt.schema_id = fs.id
+ AND (fkk."action" & 255) = ou.id
+ AND ((fkk."action" >> 8) & 255) = od.id
+ ORDER BY fkk.name, fkkc.nr;
+END;
+
+CREATE FUNCTION dump_foreign_keys() RETURNS TABLE(stmt STRING) BEGIN
+RETURN
+ SELECT
+ 'ALTER TABLE ' || DQ(fk_s) || '.'|| DQ(fk_t) || ' ADD
CONSTRAINT ' || DQ(fk) || ' ' ||
+ 'FOREIGN KEY(' || GROUP_CONCAT(DQ(fk_c), ',') ||') ' ||
+ 'REFERENCES ' || DQ(pk_s) || '.' || DQ(pk_t) || '(' ||
GROUP_CONCAT(DQ(pk_c), ',') || ') ' ||
+ 'ON DELETE ' || on_delete || ' ON UPDATE ' || on_update
+ FROM describe_foreign_keys() GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
+END;
+
CREATE TEMPORARY TABLE dump_statements(o INT AUTO_INCREMENT, s STRING, PRIMARY
KEY (o));
CREATE PROCEDURE dump_database(describe BOOLEAN)
@@ -247,8 +300,8 @@ BEGIN
AND s.name <> 'tmp';
INSERT INTO dump_statements(s) SELECT * FROM
dump_table_constraint_type();
-
INSERT INTO dump_statements(s) SELECT * FROM dump_indices();
+ INSERT INTO dump_statements(s) SELECT * FROM dump_foreign_keys();
INSERT INTO dump_statements(s) --dump_create_comments_on_indices
SELECT comment_on('INDEX', DQ(i.name), rem.remark)
@@ -258,7 +311,9 @@ 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 FOREIGN KEYs
+ --INSERT INTO dump_statements(s) SELECT * FROM dump_foreign_keys();
+
+ --TODO ADD schema's to ALTER statements
--TODO PARTITION TABLES
--TODO details on SEQUENCES
--TODO functions
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -30,14 +30,16 @@ CREATE TABLE "sys"."pfoo1" ("i" INTEGER)
CREATE TABLE "sys"."pfoo2" ("i" INTEGER);
CREATE TABLE "sys"."lower_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
CREATE TABLE "sys"."higher_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
-CREATE TABLE "sys"."foo" ("i" INTEGER, "s" CHARACTER LARGE OBJECT);
-CREATE TABLE "sys"."bar" ("i" INTEGER, "s" CHARACTER LARGE OBJECT);
+CREATE TABLE "sfoo"."foo" ("fi" INTEGER, "fs" CHARACTER LARGE OBJECT);
+CREATE TABLE "sbar"."bar" ("bi" INTEGER, "bs" CHARACTER LARGE OBJECT);
CREATE REMOTE TABLE "sys"."rfoo" ("i" INTEGER) ON
'mapi:monetdb://remote.host.url:50000/dbname' WITH USER 'bob' ENCRYPTED
PASSWORD
'f8e3183d38e6c51889582cb260ab825252f395b4ac8fb0e6b13e9a71f7c10a80d5301e4a949f2783cb0c20205f1d850f87045f4420ad2271c8fd5f0cd8944be3'
;
CREATE MERGE TABLE "sys"."scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER) PARTITION BY VALUES USING
("sys"."mod"("sys"."greatest"("first_score","second_score"),10));
CREATE MERGE TABLE "sys"."splitted" ("stamp" TIMESTAMP , "val" INTEGER)
PARTITION BY RANGE ON ("stamp");
CREATE REPLICA TABLE "sys"."rep" ("i" INTEGER);
ALTER TABLE "bolo" ADD CONSTRAINT "cpk" PRIMARY KEY ("s", "v");
ALTER TABLE "rolo" ADD CONSTRAINT "rolo_v_pkey" PRIMARY KEY ("v");
+ALTER TABLE "sfoo"."foo" ADD CONSTRAINT "foo_pk" PRIMARY KEY ("fi", "fs");
+ALTER TABLE "sbar"."bar" ADD CONSTRAINT "bar_pk" PRIMARY KEY ("bi", "bs");
ALTER TABLE "ungolo" ADD CONSTRAINT "ungolo_x_y_unique" UNIQUE ("x", "y");
ALTER TABLE "ungolo" ADD CONSTRAINT "ungolo_z_unique" UNIQUE ("z");
CREATE INDEX "ind1" ON "sys"."ungolo"(x,y);
@@ -45,4 +47,5 @@ CREATE IMPRINTS INDEX "ind2" ON "sys"."u
CREATE ORDERED INDEX "ind3" ON "sys"."ungolo"(x,z);
COMMENT ON INDEX "ind3" IS 'This is a comment on an index.' ;
COMMENT ON COLUMN "sfoo"."tfoo"."i" IS 'This is a comment on a column.' ;
+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;
COMMIT;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
