Changeset: 70dcc55c6cdd for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=70dcc55c6cdd
Modified Files:
dump.sql
dump_output.sql
Branch: monetdbe-proxy
Log Message:
dump partition tables.
diffs (141 lines):
diff --git a/dump.sql b/dump.sql
--- a/dump.sql
+++ b/dump.sql
@@ -2,6 +2,8 @@ START TRANSACTION;
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
+CREATE FUNCTION FQTN(s STRING, t STRING) RETURNS STRING BEGIN RETURN DQ(s) ||
'.' || DQ(t); END;
+CREATE FUNCTION ALTER_TABLE(s STRING, t STRING) RETURNS STRING BEGIN RETURN
'ALTER TABLE ' || FQTN(s, t) || ' '; END;
CREATE FUNCTION comment_on(ob STRING, id STRING, r STRING) RETURNS STRING
BEGIN RETURN ifthenelse(r IS NOT NULL, 'COMMENT ON ' || ob || ' ' || id || '
IS ' || SQ(r) || ';', ''); END;
@@ -221,6 +223,89 @@ RETURN
FROM describe_foreign_keys() GROUP BY fk_s, fk_t, pk_s, pk_t, fk,
on_delete, on_update;
END;
+CREATE FUNCTION describe_partition_tables()
+RETURNS TABLE(
+ m_sname STRING,
+ m_tname STRING,
+ p_sname STRING,
+ p_tname STRING,
+ p_type STRING,
+ pvalues STRING,
+ minimum STRING,
+ maximum STRING,
+ with_nulls BOOLEAN) BEGIN
+RETURN
+ SELECT
+ m_sname,
+ m_tname,
+ p_sname,
+ p_tname,
+ CASE
+ WHEN p_raw_type IS NULL THEN 'READ ONLY'
+ WHEN (p_raw_type = 'VALUES' AND pvalues IS NULL) OR (p_raw_type =
'RANGE' AND minimum IS NULL AND maximum IS NULL AND with_nulls) THEN 'FOR NULLS'
+ ELSE p_raw_type
+ END AS p_type,
+ pvalues,
+ minimum,
+ maximum,
+ with_nulls
+ FROM
+ (WITH
+ tp("type", table_id) AS
+ (SELECT CASE WHEN (table_partitions."type" & 2) = 2 THEN
'VALUES' ELSE 'RANGE' END, table_partitions.table_id FROM table_partitions),
+ subq(m_tid, p_mid, "type", m_sname, m_tname, p_sname, p_tname)
AS
+ (SELECT m_t.id, p_m.id, m_t."type", m_s.name, m_t.name,
p_s.name, p_m.name
+ FROM schemas m_s, sys._tables m_t, dependencies d, schemas p_s,
sys._tables p_m
+ WHERE m_t."type" IN (3, 6)
+ AND m_t.schema_id = m_s.id
+ AND m_s.name <> 'tmp'
+ AND m_t.system = FALSE
+ AND m_t.id = d.depend_id
+ AND d.id = p_m.id
+ AND p_m.schema_id = p_s.id
+ ORDER BY m_t.id, p_m.id)
+ SELECT
+ subq.m_sname,
+ subq.m_tname,
+ subq.p_sname,
+ subq.p_tname,
+ tp."type" AS p_raw_type,
+ CASE WHEN tp."type" = 'VALUES'
+ THEN (SELECT GROUP_CONCAT(vp.value, ',')FROM
value_partitions vp WHERE vp.table_id = subq.p_mid)
+ ELSE NULL
+ END AS pvalues,
+ CASE WHEN tp."type" = 'RANGE'
+ THEN (SELECT minimum FROM range_partitions rp WHERE
rp.table_id = subq.p_mid)
+ ELSE NULL
+ END AS minimum,
+ CASE WHEN tp."type" = 'RANGE'
+ THEN (SELECT maximum FROM range_partitions rp WHERE
rp.table_id = subq.p_mid)
+ ELSE NULL
+ END AS maximum,
+ CASE WHEN tp."type" = 'VALUES'
+ THEN EXISTS(SELECT vp.value FROM value_partitions vp
WHERE vp.table_id = subq.p_mid AND vp.value IS NULL)
+ ELSE (SELECT rp.with_nulls FROM range_partitions rp
WHERE rp.table_id = subq.p_mid)
+ END AS with_nulls
+ FROM
+ subq LEFT OUTER JOIN tp
+ ON subq.m_tid = tp.table_id) AS tmp_pi;
+END;
+
+CREATE FUNCTION dump_partition_tables() RETURNS TABLE(stmt STRING) BEGIN
+RETURN
+ SELECT
+ ALTER_TABLE(m_sname, m_tname) || ' ADD ' || FQTN(p_sname,
p_tname) ||
+ CASE
+ WHEN p_type = 'VALUES' THEN ' AS PARTITION IN (' ||
pvalues || ')'
+ WHEN p_type = 'RANGE' THEN ' AS PARTITION FROM ' ||
ifthenelse(minimum IS NOT NULL, SQ(minimum), 'RANGE MINVALUE') || ' TO ' ||
ifthenelse(maximum IS NOT NULL, SQ(maximum), 'RANGE MAXVALUE')
+ WHEN p_type = 'FOR NULLS' THEN ' AS PARTITION FOR NULL
VALUES'
+ ELSE '' --'READ ONLY'
+ END ||
+ CASE WHEN p_type in ('VALUES', 'RANGE') AND with_nulls THEN '
WITH NULL VALUES' ELSE '' END ||
+ ';'
+ FROM describe_partition_tables();
+END;
+
CREATE TEMPORARY TABLE dump_statements(o INT AUTO_INCREMENT, s STRING, PRIMARY
KEY (o));
CREATE PROCEDURE dump_database(describe BOOLEAN)
@@ -304,6 +389,7 @@ BEGIN
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) SELECT * FROM dump_partition_tables();
INSERT INTO dump_statements(s) --dump_create_comments_on_indices
SELECT comment_on('INDEX', DQ(i.name), rem.remark)
diff --git a/dump_output.sql b/dump_output.sql
--- a/dump_output.sql
+++ b/dump_output.sql
@@ -34,6 +34,7 @@ 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"."unknown_scorers" ("name" CHARACTER LARGE OBJECT,
"first_score" INTEGER, "second_score" INTEGER);
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'
;
@@ -52,4 +53,19 @@ CREATE ORDERED INDEX "ind3" ON "sys"."un
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 "sys"."scorers" ADD TABLE "sys"."lower_scorers" AS PARTITION IN
(0, 1, 2, 3, 4);
+ALTER TABLE "sys"."scorers" ADD TABLE "sys"."higher_scorers" AS PARTITION IN
(5, 6, 7, 8, 9);
+ALTER TABLE "sys"."scorers" ADD TABLE "sys"."unknown_scorers" AS PARTITION FOR
NULL VALUES;
+
+CREATE TABLE first_decade (stamp TIMESTAMP, val INT);
+CREATE TABLE second_decade (stamp TIMESTAMP, val INT);
+CREATE TABLE third_decade (stamp TIMESTAMP, val INT);
+ALTER TABLE splitted ADD TABLE first_decade AS PARTITION FROM RANGE MINVALUE
TO TIMESTAMP '2010-01-01 00:00:00' WITH NULL VALUES;
+ALTER TABLE splitted ADD TABLE second_decade AS PARTITION FROM TIMESTAMP
'2010-01-01 00:00:00' TO TIMESTAMP '2020-01-01 00:00:00';
+create merge table m1 (i int);
+create table p1 (i int);
+alter table m1 add table p1;
+
+
+
COMMIT;
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
