Changeset: 0552ef2b9a60 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=0552ef2b9a60
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
Small fixes and fix output.
diffs (120 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -65,7 +65,7 @@ CREATE FUNCTION dump_type(type STRING, d
END;
END;
-CREATE FUNCTION dump_contraint_type_name(id INT) RETURNS STRING BEGIN
+CREATE FUNCTION dump_CONSTRAINT_type_name(id INT) RETURNS STRING BEGIN
RETURN
CASE
WHEN id = 0 THEN 'PRIMARY KEY'
@@ -75,7 +75,7 @@ END;
CREATE FUNCTION describe_constraints() RETURNS TABLE("table" STRING, nr INT,
col STRING, con STRING, type STRING) BEGIN
RETURN
- SELECT t.name, kc.nr, kc.name, k.name,
dump_contraint_type_name(k.type)
+ 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
WHERE kc.id = k.id
AND k.table_id = t.id
@@ -88,7 +88,7 @@ CREATE FUNCTION dump_table_constraint_ty
RETURN
SELECT
'ALTER TABLE ' || DQ("table") ||
- ' ADD CONTRAINT ' || DQ(con) || ' '||
+ ' ADD CONSTRAINT ' || DQ(con) || ' '||
type || ' (' || GROUP_CONCAT(DQ(col), ', ') || ');'
FROM describe_constraints() GROUP BY "table", con, type;
END;
@@ -115,7 +115,7 @@ END;
CREATE FUNCTION dump_merge_table_partition_expressions(tid INT) RETURNS STRING
BEGIN
RETURN SELECT
- 'PARTITION BY ' ||
+ ' PARTITION BY ' ||
CASE
WHEN bit_and(tp.type, 2) = 2
THEN 'VALUES '
@@ -123,8 +123,8 @@ BEGIN
END ||
CASE
WHEN bit_and(tp.type, 4) = 4 --column expression
- THEN 'ON ' || (SELECT DQ(c.name) FROM
sys.columns c WHERE c.id = tp.column_id)
- ELSE 'USING ' || SQ(tp.expression) --generic
expression
+ THEN 'ON ' || '(' || (SELECT DQ(c.name) || ')'
FROM sys.columns c WHERE c.id = tp.column_id)
+ ELSE 'USING ' || '(' || tp.expression || ')'
--generic expression
END
FROM sys.table_partitions tp
WHERE tp.table_id = tid;
@@ -193,23 +193,18 @@ BEGIN
WHERE sch.id = seq.schema_id;
INSERT INTO dump_statements(s) --dump_create_tables
- SELECT CASE
- WHEN t.type = 5 THEN
- 'CREATE ' || ts.table_type_name || ' ' ||
DQ(s.name) || '.' || DQ(t.name) || dump_column_definition(t.id) ||
dump_remote_table_expressions(s.name, t.name) || ';'
- ELSE
- '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) || ';'
+ WHEN ts.table_type_name = 'MERGE TABLE' THEN
+
dump_merge_table_partition_expressions(t.id) || ';'
+ ELSE
+ ';'
END
FROM sys.schemas s, table_types ts, sys._tables t
- WHERE t.type IN (0, 5, 6)
- AND t.system = FALSE
- AND s.id = t.schema_id
- AND ts.table_type_id = t.type
- AND s.name <> 'tmp';
-
- INSERT INTO dump_statements(s) --dump_create_merge_tables
- SELECT 'CREATE ' || ts.table_type_name || ' ' || DQ(s.name) ||
'.' || DQ(t.name) || dump_column_definition(t.id) ||
dump_merge_table_partition_expressions(t.id) || ';'
- FROM sys.schemas s, table_types ts, sys._tables t
- WHERE t.type = 3
+ WHERE ts.table_type_name IN ('TABLE', 'MERGE TABLE', 'REMOTE
TABLE', 'REPLICA TABLE')
AND t.system = FALSE
AND s.id = t.schema_id
AND ts.table_type_id = t.type
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -10,6 +10,9 @@ CREATE SEQUENCE "sys"."seq_18814" AS INT
CREATE SEQUENCE "sys"."seq_8219" AS INTEGER;
CREATE SEQUENCE "sys"."seq_9119" AS INTEGER;
CREATE SEQUENCE "sys"."seq_8917" AS INTEGER;
+CREATE SEQUENCE "sys"."seq_10005" AS INTEGER;
+CREATE SEQUENCE "sys"."seq_8264" AS INTEGER;
+CREATE SEQUENCE "sys"."seq_8325" 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));
@@ -23,14 +26,16 @@ CREATE TABLE "sfoo"."tfoo" ("i" INTEGER)
CREATE TABLE "sfoo"."test" ("s" CHARACTER LARGE OBJECT);
CREATE TABLE "sys"."pfoo1" ("i" INTEGER);
CREATE TABLE "sys"."pfoo2" ("i" INTEGER);
-CREATE REMOTE TABLE "sys"."rfoo" ("i" INTEGER) ON
'mapi:monetdb://remote.host.url:50000/dbname' WITH USER 'bob' ENCRYPTED
PASSWORD
'4a3ed8147e37876adc8f76328e5abcc1b470e6acfc18efea0135f983604953a58e183c1a6086e91ba3e821d926f5fdeb37761c7ca0328a963f5e92870675b728'
;
+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 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 "ungolo" ADD CONSTRAINT "ungolo_x_y_unique" UNIQUE ("x", "y");
ALTER TABLE "ungolo" ADD CONSTRAINT "ungolo_z_unique" UNIQUE ("z");
-CREATE MERGE TABLE scorers (name clob, first_score int, second_score int)
PARTITION BY VALUES USING (greatest(first_score, second_score) % 10);
-CREATE TABLE lower_scorers (name clob, first_score int, second_score int);
-CREATE TABLE higher_scorers (name clob, first_score int, second_score int);
-ALTER TABLE scorers ADD TABLE lower_scorers AS PARTITION IN (0, 1, 2, 3, 4);
-ALTER TABLE scorers ADD TABLE higher_scorers AS PARTITION IN (5, 6, 7, 8, 9);
COMMIT;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
