[tarantool-patches] [PATCH v2 5/5] sql: introduce ALTER TABLE ADD CONSTRAINT UNIQUE/PRIMARY KEY
Nikita Pettik
korablev at tarantool.org
Wed Jan 23 20:56:18 MSK 2019
Table (aka space) can be created without indexes at least from Lua-land
(note that according ANSI SQL table may lack PK). Since there were no
facilities to create primary key constraint on already existing table,
lets extend ADD CONSTRAINT statement with UNIQUE and PRIMARY KEY
clauses. In this case, UNIQUE works exactly in the same way as CREATE
UNIQUE INDEX ... statement does. In Tarantool primary index is an index
with id == 0, so during execution of ADD CONSTRAINT PRIMARY KEY we check
that there is no any entries in _index space and create that one.
Otherwise, error is raised.
Part of #3097
Follow-up #3914
---
src/box/sql/build.c | 29 ++++++++++++++++++++--
src/box/sql/parse.y | 20 ++++++++++++++++
test/sql-tap/alter.test.lua | 57 +++++++++++++++++++++++++++++++++++++++++++-
test/sql-tap/index1.test.lua | 11 ++++++++-
4 files changed, 113 insertions(+), 4 deletions(-)
diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 660d5acfc..6eb9718be 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -2103,6 +2103,19 @@ generate_index_id(struct Parse *parse, uint32_t space_id, int cursor)
return iid_reg;
}
+static void
+pk_check_existence(struct Parse *parse, uint32_t space_id, int _index_cursor)
+{
+ struct Vdbe *v = sqlite3GetVdbe(parse);
+ int tmp_reg = ++parse->nMem;
+ sqlite3VdbeAddOp2(v, OP_Integer, space_id, tmp_reg);
+ int found_addr = sqlite3VdbeAddOp4Int(v, OP_NotFound, _index_cursor, 0,
+ tmp_reg, 1);
+ sqlite3VdbeAddOp4(v, OP_Halt, SQLITE_ERROR, ON_CONFLICT_ACTION_FAIL, 0,
+ "multiple primary keys are not allowed", P4_STATIC);
+ sqlite3VdbeJumpHere(v, found_addr);
+}
+
/**
* Add new index to table's indexes list.
* We follow convention that PK comes first in list.
@@ -2550,8 +2563,20 @@ sql_create_index(struct Parse *parse) {
(void *)space_by_id(BOX_INDEX_ID),
P4_SPACEPTR);
sqlite3VdbeChangeP5(vdbe, OPFLAG_SEEKEQ);
-
- int index_id = generate_index_id(parse, def->id, cursor);
+ int index_id;
+ /*
+ * In case we are creating PRIMARY KEY constraint
+ * (via ALTER TABLE) we must ensure that table
+ * doesn't feature any indexes. Otherwise,
+ * we can immediately halt execution of VDBE.
+ */
+ if (idx_type == SQL_INDEX_TYPE_CONSTRAINT_PK) {
+ pk_check_existence(parse, def->id, cursor);
+ index_id = parse->nMem++;
+ sqlite3VdbeAddOp2(vdbe, OP_Integer, 0, index_id);
+ } else {
+ index_id = generate_index_id(parse, def->id, cursor);
+ }
sqlite3VdbeAddOp1(vdbe, OP_Close, cursor);
vdbe_emit_create_index(parse, def, index->def,
def->id, index_id);
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 7044921c7..56485528d 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1593,6 +1593,7 @@ add_constraint_start ::= ADD CONSTRAINT nm(Z). {
}
constraint_def ::= foreign_key_def.
+constraint_def ::= unique_def.
foreign_key_def ::= FOREIGN KEY LP eidlist(FA) RP REFERENCES nm(T)
eidlist_opt(TA) refargs(R) defer_subclause_opt(D). {
@@ -1606,6 +1607,25 @@ foreign_key_def ::= FOREIGN KEY LP eidlist(FA) RP REFERENCES nm(T)
sql_create_foreign_key(pParse);
}
+unique_def ::= unique_spec(U) LP sortlist(X) RP. {
+ struct create_constraint_def *constr_def = pParse->alter_entity_def;
+ struct create_index_def *idx_def =
+ create_index_def_new(pParse, constr_def, X, U, SORT_ORDER_ASC);
+ if (idx_def == NULL)
+ break;
+ pParse->alter_entity_def = (void *) idx_def;
+ sql_create_index(pParse);
+ }
+
+%type unique_spec {int}
+unique_spec(U) ::= UNIQUE. { U = SQL_INDEX_TYPE_CONSTRAINT_UNIQUE; }
+unique_spec(U) ::= PRIMARY KEY. { U = SQL_INDEX_TYPE_CONSTRAINT_PK; }
+
+/**
+ * Currently, to drop unique constraint it is required
+ * to use drop index (since fk and unique constraints don't
+ * share name space).
+ */
drop_constraint_def ::= DROP CONSTRAINT nm(Z). {
struct alter_entity_def *alter_def = pParse->alter_entity_def;
struct drop_entity_def *drop_def =
diff --git a/test/sql-tap/alter.test.lua b/test/sql-tap/alter.test.lua
index 1aad555c0..318b0f68d 100755
--- a/test/sql-tap/alter.test.lua
+++ b/test/sql-tap/alter.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(43)
+test:plan(50)
test:do_execsql_test(
"alter-1.1",
@@ -517,6 +517,61 @@ test:do_catchsql_test(
-- </alter-7.11>
})
+test:do_test(
+ "alter-8.1.0",
+ function()
+ format = {}
+ format[1] = { name = 'id', type = 'scalar'}
+ format[2] = { name = 'f2', type = 'scalar'}
+ s = box.schema.create_space('T', {format = format})
+ end,
+ {})
+
+test:do_catchsql_test(
+ "alter-8.1.1",
+ [[
+ ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY("id");
+ ]], {
+ 0
+ })
+
+test:do_test(
+ "alter-8.1.2",
+ function()
+ return box.space.T.index[0].id
+ end, 0)
+
+test:do_catchsql_test(
+ "alter-8.2",
+ [[
+ ALTER TABLE t ADD CONSTRAINT pk1 PRIMARY KEY("f2");
+ ]], {
+ 1, "multiple primary keys are not allowed"
+ })
+
+test:do_catchsql_test(
+ "alter-8.3.1",
+ [[
+ ALTER TABLE t ADD CONSTRAINT i1 UNIQUE("f2");
+ ]], {
+ 0
+ })
+
+test:do_test(
+ "alter-8.3.2",
+ function()
+ i = box.space.T.index[1]
+ return i.id
+ end, 1)
+
+test:do_catchsql_test(
+ "alter-8.4",
+ [[
+ DROP INDEX i1 ON t;
+ DROP INDEX pk ON t;
+ ]], {
+ 0
+})
-- Commented due to #2953
--
diff --git a/test/sql-tap/index1.test.lua b/test/sql-tap/index1.test.lua
index 121381747..3edf863a9 100755
--- a/test/sql-tap/index1.test.lua
+++ b/test/sql-tap/index1.test.lua
@@ -1,6 +1,6 @@
#!/usr/bin/env tarantool
test = require("sqltester")
-test:plan(72)
+test:plan(73)
--!./tcltestrunner.lua
-- 2001 September 15
@@ -1034,4 +1034,13 @@ test:do_catchsql_test(
1, "can not add a secondary key before primary"
})
+test:do_catchsql_test(
+ "alter-8.2",
+ [[
+ ALTER TABLE t ADD CONSTRAINT pk PRIMARY KEY("id");
+ CREATE UNIQUE INDEX i ON t("id");
+ ]], {
+ 0
+})
+
test:finish_test()
--
2.15.1
More information about the Tarantool-patches
mailing list