Changeset: ab21dbcf2e77 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=ab21dbcf2e77
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Fix ALTER TABLE statements and code style clean ups.
diffs (170 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -73,12 +73,13 @@ CREATE FUNCTION dump_CONSTRAINT_type_nam
END;
END;
-CREATE FUNCTION describe_constraints() RETURNS TABLE("table" STRING, nr INT,
col STRING, con STRING, type STRING) BEGIN
+CREATE FUNCTION describe_constraints() RETURNS TABLE(s STRING, "table" STRING,
nr INT, col STRING, con STRING, type STRING) BEGIN
RETURN
- SELECT t.name, kc.nr, kc.name, k.name,
dump_CONSTRAINT_type_name(k.type)
- FROM sys._tables t, sys.objects kc, sys.keys k
+ SELECT s.name, t.name, kc.nr, kc.name, k.name,
dump_CONSTRAINT_type_name(k.type)
+ FROM sys.schemas s, sys._tables t, sys.objects kc, sys.keys k
WHERE kc.id = k.id
AND k.table_id = t.id
+ AND s.id = t.schema_id
AND t.system = FALSE
AND k.type in (0, 1)
AND t.type IN (0, 6);
@@ -86,11 +87,11 @@ END;
CREATE FUNCTION dump_table_constraint_type() RETURNS TABLE(stm STRING) BEGIN
RETURN
- SELECT
- 'ALTER TABLE ' || DQ("table") ||
+ SELECT
+ 'ALTER TABLE ' || DQ(s) || '.' || DQ("table") ||
' ADD CONSTRAINT ' || DQ(con) || ' '||
type || ' (' || GROUP_CONCAT(DQ(col), ', ') || ');'
- FROM describe_constraints() GROUP BY "table", con, type;
+ FROM describe_constraints() GROUP BY s, "table", con, type;
END;
CREATE FUNCTION describe_indices() RETURNS TABLE (i STRING, o INT, s STRING, t
STRING, c STRING, it STRING) BEGIN
@@ -132,7 +133,7 @@ END;
CREATE FUNCTION dump_column_definition(tid INT) RETURNS STRING BEGIN
RETURN
- SELECT
+ SELECT
' (' ||
GROUP_CONCAT(
DQ(c.name) || ' ' ||
@@ -140,7 +141,7 @@ CREATE FUNCTION dump_column_definition(t
ifthenelse(c."null" = 'false', ' NOT NULL', '')
||
ifthenelse(c."default" IS NOT NULL, ' DEFAULT '
|| c."default", '')
, ', ') || ')'
- FROM sys._columns c
+ FROM sys._columns c
WHERE c.table_id = tid;
END;
@@ -173,8 +174,8 @@ CREATE FUNCTION describe_foreign_keys()
pk_s STRING, pk_t STRING, pk_c STRING,
on_update STRING, on_delete STRING) BEGIN
- RETURN
- WITH action_type (id, act) AS (VALUES
+ RETURN
+ WITH action_type (id, act) AS (VALUES
(0, 'NO ACTION'),
(1, 'CASCADE'),
(2, 'RESTRICT'),
@@ -195,7 +196,7 @@ CREATE FUNCTION describe_foreign_keys()
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
@@ -211,11 +212,12 @@ END;
CREATE FUNCTION dump_foreign_keys() RETURNS TABLE(stmt STRING) BEGIN
RETURN
- SELECT
+ 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
+ '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;
@@ -230,7 +232,7 @@ BEGIN
INSERT INTO dump_statements(s) --dump_create_roles
SELECT 'CREATE ROLE ' || DQ(name) || ';' FROM auths
- WHERE name NOT IN (SELECT name FROM db_user_info)
+ WHERE name NOT IN (SELECT name FROM db_user_info)
AND grantor <> 0;
INSERT INTO dump_statements(s) --dump_create_users
@@ -282,8 +284,8 @@ BEGIN
WHERE sch.id = seq.schema_id;
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) ||
+ SELECT
+ 'CREATE ' || ts.table_type_name || ' ' || DQ(s.name) ||
'.' || DQ(t.name) || dump_column_definition(t.id) ||
CASE
WHEN ts.table_type_name = 'REMOTE TABLE' THEN
dump_remote_table_expressions(s.name,
t.name) || ';'
@@ -313,7 +315,6 @@ BEGIN
--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
@@ -1,7 +1,9 @@
START TRANSACTION;
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;
+CREATE USER "voc" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
+NAME 'VOC Explorer' SCHEMA sys;
+CREATE USER "voc2" WITH ENCRYPTED PASSWORD
'ea45cf4e124b215a28631ec7ff0bf06e82fc26b2be7a066c9594855690fb5d42438be58d6523132384a1738cb4e5139caa1f970ebdfb422d65834d9a4ef61c0e'
+ NAME 'VOC Explorer' SCHEMA sys;
CREATE SCHEMA "sbar" AUTHORIZATION monetdb;
CREATE SCHEMA "sfoo" AUTHORIZATION monetdb;
COMMENT ON SCHEMA "sbar" IS 'This is a comment on sbar' ;
@@ -15,6 +17,8 @@ CREATE SEQUENCE "sys"."seq_8264" AS INTE
CREATE SEQUENCE "sys"."seq_8325" AS INTEGER;
CREATE SEQUENCE "sys"."seq_8231" AS INTEGER;
CREATE SEQUENCE "sys"."seq_8441" AS INTEGER;
+CREATE SEQUENCE "sys"."seq_8546" AS INTEGER;
+CREATE SEQUENCE "sys"."seq_8259" AS INTEGER;
COMMENT ON SEQUENCE "sys"."seq_8219" IS 'This is a comment on a sequence.' ;
CREATE TABLE "sys"."test" ("s" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."bla" ("s" CHARACTER LARGE OBJECT(10));
@@ -30,22 +34,22 @@ 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 "sfoo"."foo" ("fi" INTEGER, "fs" CHARACTER LARGE OBJECT);
-CREATE TABLE "sbar"."bar" ("bi" INTEGER, "bs" CHARACTER LARGE OBJECT);
+CREATE TABLE "sfoo"."foo" ("fi" INTEGER NOT NULL, "fs" CHARACTER LARGE OBJECT
NOT NULL);
+CREATE TABLE "sbar"."bar" ("bi" INTEGER NOT NULL, "bs" CHARACTER LARGE OBJECT
NOT NULL);
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 "sys"."bolo" ADD CONSTRAINT "cpk" PRIMARY KEY ("s", "v");
+ALTER TABLE "sys"."rolo" ADD CONSTRAINT "rolo_v_pkey" PRIMARY KEY ("v");
+ALTER TABLE "sys"."ungolo" ADD CONSTRAINT "ungolo_x_y_unique" UNIQUE ("x",
"y");
+ALTER TABLE "sys"."ungolo" ADD CONSTRAINT "ungolo_z_unique" UNIQUE ("z");
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);
CREATE IMPRINTS INDEX "ind2" ON "sys"."ungolo"(y,z);
CREATE ORDERED INDEX "ind3" ON "sys"."ungolo"(x,z);
+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;
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
