From: Nikita Pettik <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, Nikita Pettik <korablev@tarantool.org> Subject: [tarantool-patches] [PATCH] sql: introduce ADD CONSTRAINT CHECK statement Date: Mon, 8 Jul 2019 21:57:15 +0300 [thread overview] Message-ID: <20190708185715.44116-1-korablev@tarantool.org> (raw) 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
next reply other threads:[~2019-07-08 18:57 UTC|newest] Thread overview: 5+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-07-08 18:57 Nikita Pettik [this message] 2019-07-11 21:32 ` [tarantool-patches] " Vladislav Shpilevoy 2019-07-12 9:24 ` n.pettik 2019-07-12 19:00 ` Vladislav Shpilevoy 2019-07-13 14:16 ` Kirill Yukhin
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=20190708185715.44116-1-korablev@tarantool.org \ --to=korablev@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [tarantool-patches] [PATCH] sql: introduce ADD CONSTRAINT CHECK statement' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox