Changeset: 36c8b75d135f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=36c8b75d135f
Modified Files:
sql/server/rel_schema.c
sql/server/sql_partition.c
sql/test/merge-partitions/Tests/mergepart27.sql
sql/test/merge-partitions/Tests/mergepart27.stable.err
sql/test/merge-partitions/Tests/mergepart27.stable.out
Branch: Apr2019
Log Message:
Changes to partitioned merge tables:
- We will allow foreign keys on non-partitioned columns, however the constraint
that foreign keys definition must exactly be the same in all tables in the
hierarchy still remains.
- SQL constraints cannot be added or dropped on a table while is part of a
partitioned merge table.
Also fixed validation of SQL_DROP_CONSTRAINT statements.
diffs (truncated from 412 to 300 lines):
diff --git a/sql/server/rel_schema.c b/sql/server/rel_schema.c
--- a/sql/server/rel_schema.c
+++ b/sql/server/rel_schema.c
@@ -917,7 +917,8 @@ table_element(mvc *sql, symbol *s, sql_s
}
} break;
case SQL_DROP_CONSTRAINT:
- assert(0);
+ res = SQL_OK;
+ break;
default:
res = SQL_ERR;
}
@@ -1457,15 +1458,6 @@ sql_alter_table(mvc *sql, dlist *dl, dli
sql_exp ** updates, *e;
assert(te);
- if (t && te && te->token == SQL_DROP_CONSTRAINT) {
- dlist *l = te->data.lval;
- char *kname = l->h->data.sval;
- int drop_action = l->h->next->data.i_val;
-
- sname = get_schema_name(sql, sname, tname);
- return rel_drop(sql->sa, DDL_DROP_CONSTRAINT, sname,
kname, drop_action, 0);
- }
-
if (t->persistence != SQL_DECLARED_TABLE)
sname = s->base.name;
@@ -1555,6 +1547,15 @@ sql_alter_table(mvc *sql, dlist *dl, dli
if (!nt || (te && table_element(sql, te, s, nt, 1) == SQL_ERR))
return NULL;
+ if (te->token == SQL_DROP_CONSTRAINT) {
+ dlist *l = te->data.lval;
+ char *kname = l->h->data.sval;
+ int drop_action = l->h->next->data.i_val;
+
+ sname = get_schema_name(sql, sname, tname);
+ return rel_drop(sql->sa, DDL_DROP_CONSTRAINT, sname,
kname, drop_action, 0);
+ }
+
if (t->s && !nt->s)
nt->s = t->s;
diff --git a/sql/server/sql_partition.c b/sql/server/sql_partition.c
--- a/sql/server/sql_partition.c
+++ b/sql/server/sql_partition.c
@@ -32,41 +32,50 @@ table_column_colnr(int *colnr)
str
sql_partition_validate_key(mvc *sql, sql_table *nt, sql_key *k, const char* op)
{
- if(isPartitionedByColumnTable(nt)) {
- assert(nt->part.pcol);
- if(list_length(k->columns) != 1) {
- throw(SQL, "sql.partition", SQLSTATE(42000) "%s TABLE:
%s.%s: in a partitioned table the keys must match "
- "the
columns used in the partition definition", op, nt->s->base.name, nt->base.name);
- } else {
- sql_kc *kcol = k->columns->h->data;
- if(kcol->c->colnr != nt->part.pcol->colnr)
- throw(SQL, "sql.partition", SQLSTATE(42000) "%s
TABLE: %s.%s: in a partitioned table the keys must "
-
"match the columns used in the partition definition", op, nt->s->base.name,
nt->base.name);
- }
- } else if(isPartitionedByExpressionTable(nt)) {
- list *kcols, *pcols;
- sql_allocator *p1, *p2;
+ if (k->type != fkey) {
+ const char *keys = (k->type == pkey) ? "primary" : "unique";
+ assert(k->type == pkey || k->type == ukey);
+
+ if (isPartitionedByColumnTable(nt)) {
+ assert(nt->part.pcol);
+ if (list_length(k->columns) != 1) {
+ throw(SQL, "sql.partition", SQLSTATE(42000) "%s
TABLE: %s.%s: in a partitioned table, the %s key's "
+ "columns must match the columns used
in the partition definition", op, nt->s->base.name,
+ nt->base.name, keys);
+ } else {
+ sql_kc *kcol = k->columns->h->data;
+ if (kcol->c->colnr != nt->part.pcol->colnr)
+ throw(SQL, "sql.partition",
SQLSTATE(42000) "%s TABLE: %s.%s: in a partitioned table, the %s key's "
+ "columns must match the
columns used in the partition definition", op, nt->s->base.name,
+ nt->base.name, keys);
+ }
+ } else if (isPartitionedByExpressionTable(nt)) {
+ list *kcols, *pcols;
+ sql_allocator *p1, *p2;
- assert(nt->part.pexp->cols);
- if(list_length(k->columns) != list_length(nt->part.pexp->cols))
- throw(SQL, "sql.partition", SQLSTATE(42000) "%s TABLE:
%s.%s: in a partitioned table the keys must match "
- "the
columns used in the partition definition", op, nt->s->base.name, nt->base.name);
+ assert(nt->part.pexp->cols);
+ if (list_length(k->columns) !=
list_length(nt->part.pexp->cols))
+ throw(SQL, "sql.partition", SQLSTATE(42000) "%s
TABLE: %s.%s: in a partitioned table, the %s key's "
+ "columns must match the columns used
in the partition definition", op, nt->s->base.name,
+ nt->base.name, keys);
- p1 = k->columns->sa; /* save the original sql allocators */
- p2 = nt->part.pexp->cols->sa;
- k->columns->sa = sql->sa;
- nt->part.pexp->cols->sa = sql->sa;
- kcols = list_sort(k->columns, (fkeyvalue)&key_column_colnr,
NULL);
- pcols = list_sort(nt->part.pexp->cols,
(fkeyvalue)&table_column_colnr, NULL);
- k->columns->sa = p1;
- nt->part.pexp->cols->sa = p2;
+ p1 = k->columns->sa; /* save the original sql
allocators */
+ p2 = nt->part.pexp->cols->sa;
+ k->columns->sa = sql->sa;
+ nt->part.pexp->cols->sa = sql->sa;
+ kcols = list_sort(k->columns,
(fkeyvalue)&key_column_colnr, NULL);
+ pcols = list_sort(nt->part.pexp->cols,
(fkeyvalue)&table_column_colnr, NULL);
+ k->columns->sa = p1;
+ nt->part.pexp->cols->sa = p2;
- for (node *nn = kcols->h, *mm = pcols->h; nn && mm; nn =
nn->next, mm = mm->next) {
- sql_kc *kcol = nn->data;
- int *colnr = mm->data;
- if (kcol->c->colnr != *colnr)
- throw(SQL, "sql.partition", SQLSTATE(42000) "%s
TABLE: %s.%s: in a partitioned table the keys must match "
- "the
columns used in the partition definition", op, nt->s->base.name, nt->base.name);
+ for (node *nn = kcols->h, *mm = pcols->h; nn && mm; nn
= nn->next, mm = mm->next) {
+ sql_kc *kcol = nn->data;
+ int *colnr = mm->data;
+ if (kcol->c->colnr != *colnr)
+ throw(SQL, "sql.partition",
SQLSTATE(42000) "%s TABLE: %s.%s: in a partitioned table, the %s key's "
+ "columns must match the
columns used in the partition definition", op, nt->s->base.name,
+ nt->base.name, keys);
+ }
}
}
return NULL;
diff --git a/sql/test/merge-partitions/Tests/mergepart27.sql
b/sql/test/merge-partitions/Tests/mergepart27.sql
--- a/sql/test/merge-partitions/Tests/mergepart27.sql
+++ b/sql/test/merge-partitions/Tests/mergepart27.sql
@@ -31,39 +31,45 @@ DROP TABLE checkdefault;
DROP TABLE subt1;
DROP TABLE subt2;
-CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32)) PARTITION BY
RANGE USING (b || 'ups'); --error
-CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY) PARTITION BY
RANGE USING (a + 1); --error
+CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32)) PARTITION BY
RANGE USING (b || 'ups'); --error, primary not on a partitioned column
+CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY) PARTITION BY
RANGE USING (a + 1); --error, primary not on a partitioned column
-CREATE MERGE TABLE checkkeys (a int, b int, PRIMARY KEY(a, b)) PARTITION BY
RANGE USING (a + 1); --error
+CREATE MERGE TABLE checkkeys (a int, b int, PRIMARY KEY(a, b)) PARTITION BY
RANGE USING (a + 1); --error, primary not on a partitioned column
CREATE MERGE TABLE checkkeys (a int, b int, PRIMARY KEY(a, b)) PARTITION BY
RANGE USING (a + b + 1);
DROP TABLE checkkeys;
-CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32)) PARTITION BY
RANGE ON (b); --error
-CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY) PARTITION BY
RANGE ON (a); --error
+CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32)) PARTITION BY
RANGE ON (b); --error, primary not on a partitioned column
+CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY) PARTITION BY
RANGE ON (a); --error, primary not on a partitioned column
CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32)) PARTITION BY
RANGE ON (a);
CREATE TABLE referenceme (mememe int PRIMARY KEY);
CREATE TABLE otherref (othermeme varchar(32) PRIMARY KEY);
ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
-ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref (othermeme);
--error not compatible key with partition
+ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref (othermeme);
--foreign keys on non-partitioned columns is allowed
ALTER TABLE checkkeys DROP CONSTRAINT checkkeys_a_fkey;
CREATE TABLE subt1 (a int PRIMARY KEY, b varchar(32));
CREATE TABLE subt2 (a int, b varchar(32) PRIMARY KEY);
+ALTER TABLE checkkeys ADD TABLE subt1 AS PARTITION BETWEEN 1 AND 100; --error,
doesn't have the same foreign key
+ALTER TABLE subt1 ADD FOREIGN KEY (b) REFERENCES otherref (othermeme);
ALTER TABLE checkkeys ADD TABLE subt1 AS PARTITION BETWEEN 1 AND 100;
-ALTER TABLE checkkeys ADD TABLE subt2 AS PARTITION BETWEEN 101 AND 200; --error
+ALTER TABLE subt1 DROP CONSTRAINT subt1_b_fkey; --error, cannot drop SQL
constraints while the table is part of a merge table
+ALTER TABLE subt1 ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
--error, cannot add SQL constraints while the table is part of a merge table
+ALTER TABLE checkkeys ADD TABLE subt2 AS PARTITION BETWEEN 101 AND 200;
--error, primary keys don't match
-ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
--error
-ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref (othermeme);
--error
+ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
--error, merge table has child tables
+ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref (othermeme);
--error, merge table has child tables
ALTER TABLE checkkeys DROP TABLE subt1;
+ALTER TABLE subt1 DROP CONSTRAINT subt1_b_fkey;
CREATE TABLE subt3 (a int PRIMARY KEY, b varchar(32), FOREIGN KEY (a)
REFERENCES referenceme(mememe));
CREATE TABLE another (mememe int PRIMARY KEY);
-ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100; --error
checkkeys does not have the foreign key
+ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100; --error
checkkeys does not have the foreign key b
ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
+ALTER TABLE checkkeys DROP CONSTRAINT checkkeys_b_fkey;
ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100;
ALTER TABLE checkkeys DROP TABLE subt3;
ALTER TABLE checkkeys DROP CONSTRAINT checkkeys_a_fkey;
@@ -72,10 +78,22 @@ ALTER TABLE subt3 DROP CONSTRAINT subt3_
ALTER TABLE subt3 ADD FOREIGN KEY (a) REFERENCES another (mememe);
ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100; --error
foreign keys reference different tables
+CREATE MERGE TABLE checkunique (a int unique, b varchar(32)) PARTITION BY
RANGE ON (b); --error, partition by on a not unique column
+CREATE MERGE TABLE checkunique (a int unique, b varchar(32)) PARTITION BY
RANGE ON (a);
+CREATE TABLE subt4 (a int, b varchar(32) unique);
+ALTER TABLE checkunique ADD TABLE subt4 AS PARTITION BETWEEN 1 AND 2; --error,
the partition is not on the unique column
+DROP TABLE subt4;
+
+CREATE TABLE subt4 (a int unique , b varchar(32));
+ALTER TABLE checkunique ADD TABLE subt4 AS PARTITION BETWEEN 1 AND 2;
+ALTER TABLE checkunique DROP TABLE subt4;
+
DROP TABLE checkkeys;
+DROP TABLE checkunique;
DROP TABLE subt1;
DROP TABLE subt2;
DROP TABLE subt3;
+DROP TABLE subt4;
DROP TABLE referenceme;
DROP TABLE otherref;
DROP TABLE another;
diff --git a/sql/test/merge-partitions/Tests/mergepart27.stable.err
b/sql/test/merge-partitions/Tests/mergepart27.stable.err
--- a/sql/test/merge-partitions/Tests/mergepart27.stable.err
+++ b/sql/test/merge-partitions/Tests/mergepart27.stable.err
@@ -40,50 +40,66 @@ MAPI = (monetdb) /var/tmp/mtest-12261/.
QUERY = ALTER TABLE checkdefault ADD TABLE subt1 AS PARTITION BETWEEN 1 AND
100; --error not compatible defaults
ERROR = !ALTER RANGE PARTITION TABLE: to be added table column DEFAULT value
doesn't match RANGE PARTITION TABLE definition
CODE = 3F000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32))
PARTITION BY RANGE USING (b || 'ups'); --error
-ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32))
PARTITION BY RANGE USING (b || 'ups'); --error, primary not on a partitioned
column
+ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table, the primary
key's columns must match the columns used in the partition definition
+CODE = 42000
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY)
PARTITION BY RANGE USING (a + 1); --error, primary not on a partitioned column
+ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table, the primary
key's columns must match the columns used in the partition definition
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY)
PARTITION BY RANGE USING (a + 1); --error
-ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = CREATE MERGE TABLE checkkeys (a int, b int, PRIMARY KEY(a, b))
PARTITION BY RANGE USING (a + 1); --error, primary not on a partitioned column
+ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table, the primary
key's columns must match the columns used in the partition definition
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = CREATE MERGE TABLE checkkeys (a int, b int, PRIMARY KEY(a, b))
PARTITION BY RANGE USING (a + 1); --error
-ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32))
PARTITION BY RANGE ON (b); --error, primary not on a partitioned column
+ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table, the primary
key's columns must match the columns used in the partition definition
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = CREATE MERGE TABLE checkkeys (a int PRIMARY KEY, b varchar(32))
PARTITION BY RANGE ON (b); --error
-ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY)
PARTITION BY RANGE ON (a); --error, primary not on a partitioned column
+ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table, the primary
key's columns must match the columns used in the partition definition
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = CREATE MERGE TABLE checkkeys (a int, b varchar(32) PRIMARY KEY)
PARTITION BY RANGE ON (a); --error
-ERROR = !CREATE TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-8822/.s.monetdb.32314
+QUERY = ALTER TABLE checkkeys ADD TABLE subt1 AS PARTITION BETWEEN 1 AND 100;
--error, doesn't have the same foreign key
+ERROR = !ALTER RANGE PARTITION TABLE: to be added table key doesn't match
RANGE PARTITION TABLE definition
+CODE = 3F000
+MAPI = (monetdb) /var/tmp/mtest-31522/.s.monetdb.37006
+QUERY = ALTER TABLE subt1 DROP CONSTRAINT subt1_b_fkey; --error, cannot drop
SQL constraints while the table is part of a merge table
+ERROR = !ALTER TABLE: cannot drop constraint from a PARTITION of a MERGE or
REPLICA TABLE 'subt1'
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref
(othermeme); --error not compatible key with partition
-ERROR = !ALTER TABLE: sys.checkkeys: in a partitioned table the keys must
match the columns used in the partition definition
+MAPI = (monetdb) /var/tmp/mtest-31522/.s.monetdb.37006
+QUERY = ALTER TABLE subt1 ADD FOREIGN KEY (a) REFERENCES referenceme (mememe);
--error, cannot add SQL constraints while the table is part of a merge table
+ERROR = !ALTER TABLE: cannot add constraint to a PARTITION of a MERGE or
REPLICA TABLE 'subt1'
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = ALTER TABLE checkkeys ADD TABLE subt2 AS PARTITION BETWEEN 101 AND
200; --error
+MAPI = (monetdb) /var/tmp/mtest-31522/.s.monetdb.37006
+QUERY = ALTER TABLE checkkeys ADD TABLE subt2 AS PARTITION BETWEEN 101 AND
200; --error, primary keys don't match
ERROR = !ALTER RANGE PARTITION TABLE: to be added table column NULL check
doesn't match RANGE PARTITION TABLE definition
CODE = 3F000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme
(mememe); --error
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = ALTER TABLE checkkeys ADD FOREIGN KEY (a) REFERENCES referenceme
(mememe); --error, merge table has child tables
ERROR = !ALTER TABLE: cannot add constraint to MERGE TABLE 'checkkeys'
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref
(othermeme); --error
+MAPI = (monetdb) /var/tmp/mtest-31182/.s.monetdb.34419
+QUERY = ALTER TABLE checkkeys ADD FOREIGN KEY (b) REFERENCES otherref
(othermeme); --error, merge table has child tables
ERROR = !ALTER TABLE: cannot add constraint to MERGE TABLE 'checkkeys'
CODE = 42000
-MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
-QUERY = ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100;
--error checkkeys does not have the foreign key
-ERROR = !ALTER RANGE PARTITION TABLE: to be added table key doesn't match
RANGE PARTITION TABLE definition
+MAPI = (monetdb) /var/tmp/mtest-8822/.s.monetdb.32314
+QUERY = ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100;
--error checkkeys does not have the foreign key b
+ERROR = !ALTER RANGE PARTITION TABLE: to be added table key's columns doesn't
match RANGE PARTITION TABLE definition
CODE = 3F000
MAPI = (monetdb) /var/tmp/mtest-12261/.s.monetdb.38633
QUERY = ALTER TABLE checkkeys ADD TABLE subt3 AS PARTITION BETWEEN 1 AND 100;
--error foreign keys reference different tables
ERROR = !ALTER RANGE PARTITION TABLE: to be added table key doesn't match
RANGE PARTITION TABLE definition
CODE = 3F000
+MAPI = (monetdb) /var/tmp/mtest-10136/.s.monetdb.37062
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
