[tarantool-patches] [PATCH] sql: introduce ADD CONSTRAINT CHECK statement

Nikita Pettik korablev at tarantool.org
Mon Jul 8 21:57:15 MSK 2019


This patch extends parser's grammar to allow to create CHECK constraints
on already existent tables via SQL facilities. Syntax is following:

ALTER TABLE <table> ADD CONSTRAINT <name> CHECK (<expr>);

Closes #3097
---
Branch: https://github.com/tarantool/tarantool/tree/np/sql-add-create-ck-syntax
Issue: https://github.com/tarantool/tarantool/issues/3097

 src/box/sql/build.c          | 26 +++++++++++++++++++++---
 src/box/sql/parse.y          |  7 ++++++-
 src/box/sql/parse_def.h      |  6 +++---
 test/sql-tap/alter2.test.lua | 48 +++++++++++++++++++++++++++++++++++++++++++-
 4 files changed, 79 insertions(+), 8 deletions(-)

diff --git a/src/box/sql/build.c b/src/box/sql/build.c
index 292168f88..396de63fd 100644
--- a/src/box/sql/build.c
+++ b/src/box/sql/build.c
@@ -668,6 +668,11 @@ trim_space_snprintf(char *wptr, const char *str, uint32_t str_len)
 	*wptr = '\0';
 }
 
+static void
+vdbe_emit_ck_constraint_create(struct Parse *parser,
+			       const struct ck_constraint_def *ck_def,
+			       uint32_t reg_space_id);
+
 void
 sql_create_check_contraint(struct Parse *parser)
 {
@@ -680,7 +685,7 @@ sql_create_check_contraint(struct Parse *parser)
 	assert(alter_def->entity_type == ENTITY_TYPE_CK);
 	(void) alter_def;
 	struct space *space = parser->create_table_def.new_space;
-	assert(space != NULL);
+	bool is_alter = space == NULL;
 
 	/* Prepare payload for ck constraint definition. */
 	struct region *region = &parser->region;
@@ -694,6 +699,7 @@ sql_create_check_contraint(struct Parse *parser)
 			return;
 		}
 	} else {
+		assert(! is_alter);
 		uint32_t ck_idx = ++parser->create_table_def.check_count;
 		name = tt_sprintf("CK_CONSTRAINT_%d_%s", ck_idx,
 				  space->def->name);
@@ -734,8 +740,22 @@ sql_create_check_contraint(struct Parse *parser)
 	trim_space_snprintf(ck_def->expr_str, expr_str, expr_str_len);
 	memcpy(ck_def->name, name, name_len);
 	ck_def->name[name_len] = '\0';
-
-	rlist_add_entry(&parser->create_table_def.new_check, ck_parse, link);
+	if (is_alter) {
+		const char *space_name = alter_def->entity_name->a[0].zName;
+		struct space *space = space_by_name(space_name);
+		if (space == NULL) {
+			diag_set(ClientError, ER_NO_SUCH_SPACE, space_name);
+			parser->is_aborted = true;
+			return;
+		}
+		int space_id_reg = ++parser->nMem;
+		sqlVdbeAddOp2(sqlGetVdbe(parser), OP_Integer, space->def->id,
+			      space_id_reg);
+		vdbe_emit_ck_constraint_create(parser, ck_def, space_id_reg);
+	} else {
+		rlist_add_entry(&parser->create_table_def.new_check, ck_parse,
+				link);
+	}
 }
 
 /*
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 010feffd4..2a60ad25b 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -283,7 +283,7 @@ ccons ::= cconsname(N) UNIQUE. {
 ccons ::= check_constraint_def .
 
 check_constraint_def ::= cconsname(N) CHECK LP expr(X) RP. {
-  create_ck_def_init(&pParse->create_ck_def, &N, &X);
+  create_ck_def_init(&pParse->create_ck_def, NULL, &N, &X);
   sql_create_check_contraint(pParse);
 }
 
@@ -1680,6 +1680,11 @@ cmd ::= alter_add_constraint(N) FOREIGN KEY LP eidlist(FA) RP REFERENCES
   sql_create_foreign_key(pParse);
 }
 
+cmd ::= alter_add_constraint(N) CHECK LP expr(X) RP. {
+    create_ck_def_init(&pParse->create_ck_def, N.table_name, &N.name, &X);
+    sql_create_check_contraint(pParse);
+}
+
 cmd ::= alter_add_constraint(N) unique_spec(U) LP sortlist(X) RP. {
   create_index_def_init(&pParse->create_index_def, N.table_name, &N.name, X, U,
                         SORT_ORDER_ASC, false);
diff --git a/src/box/sql/parse_def.h b/src/box/sql/parse_def.h
index 6c1b6fddd..557e41529 100644
--- a/src/box/sql/parse_def.h
+++ b/src/box/sql/parse_def.h
@@ -417,10 +417,10 @@ create_trigger_def_init(struct create_trigger_def *trigger_def,
 }
 
 static inline void
-create_ck_def_init(struct create_ck_def *ck_def, struct Token *name,
-		   struct ExprSpan *expr)
+create_ck_def_init(struct create_ck_def *ck_def, struct SrcList *table_name,
+		   struct Token *name, struct ExprSpan *expr)
 {
-	create_constraint_def_init(&ck_def->base, NULL, name, false,
+	create_constraint_def_init(&ck_def->base, table_name, name, false,
 				   false, ENTITY_TYPE_CK);
 	ck_def->expr = expr;
 }
diff --git a/test/sql-tap/alter2.test.lua b/test/sql-tap/alter2.test.lua
index 8969dfab2..4dc8f8255 100755
--- a/test/sql-tap/alter2.test.lua
+++ b/test/sql-tap/alter2.test.lua
@@ -1,6 +1,6 @@
 #!/usr/bin/env tarantool
 test = require("sqltester")
-test:plan(21)
+test:plan(26)
 
 -- This suite is aimed to test ALTER TABLE ADD CONSTRAINT statement.
 --
@@ -260,4 +260,50 @@ test:do_catchsql_test(
         -- </alter2-5.2>
     })
 
+-- Test ADD CONSTRAINT CHECK functionality. CHECK constraints are
+-- intagrated into Tarantool's core, so basically we whould test
+-- only grammar and validate correctness of raised errors.
+--
+test:do_catchsql_test(
+    "alter2-6.1",
+    [[
+        CREATE TABLE t1 (id INT PRIMARY KEY);
+        ALTER TABLE t1 ADD CONSTRAINT ck CHECK(id > 0);
+        INSERT INTO t1 VALUES (-1);
+    ]], { 1, "Check constraint failed 'CK': id > 0" })
+
+-- Make sure that one can't create constraint with the same name twice.
+--
+test:do_catchsql_test(
+    "alter2-6.2",
+    [[
+        ALTER TABLE t1 ADD CONSTRAINT ck CHECK(id > 0);
+    ]], { 1, "Constraint CK already exists" })
+
+-- Make sure that CHECK constraint can be created only on empty space.
+--
+test:do_catchsql_test(
+    "alter2-6.3",
+    [[
+        INSERT INTO t1 VALUES (1);
+        ALTER TABLE t1 ADD CONSTRAINT ck1 CHECK(id > 0);
+    ]], { 1, "Failed to create check constraint 'CK1': referencing space must be empty" })
+
+-- "Non-existant" space error is raised correctly.
+--
+test:do_catchsql_test(
+    "alter2-6.4",
+    [[
+        ALTER TABLE fake ADD CONSTRAINT ck CHECK(id > 0);
+    ]], { 1, "Space 'FAKE' does not exist" })
+
+-- "Non-existant" column error is raised correctly.
+--
+test:do_catchsql_test(
+    "alter2-6.5",
+    [[
+        CREATE TABLE t2 (id INT PRIMARY KEY);
+        ALTER TABLE t2 ADD CONSTRAINT ck CHECK(fake_col > 0);
+    ]], { 1, "Failed to create check constraint 'CK': Can’t resolve field 'FAKE_COL'" })
+
 test:finish_test()
-- 
2.15.1





More information about the Tarantool-patches mailing list