* [tarantool-patches] [PATCH] sql: constraints definition among columns in CREATE TABLE() @ 2018-11-18 14:31 Roman Khabibov 2018-11-19 12:41 ` [tarantool-patches] " Vladislav Shpilevoy 2018-11-26 10:33 ` Kirill Yukhin 0 siblings, 2 replies; 7+ messages in thread From: Roman Khabibov @ 2018-11-18 14:31 UTC (permalink / raw) To: tarantool-patches; +Cc: v.shpilevoy Allow constraints to appear along with columns definitions. Disallow typing a constraint name without specifying the constraint and after. Closes #3504 Branch: https://github.com/tarantool/tarantool/tree/romanhabibov/gh-3504-constraints-create-table Issue: https://github.com/tarantool/tarantool/issues/3504 --- src/box/sql/parse.y | 20 ++--- test/sql-tap/check.test.lua | 50 +++---------- test/sql-tap/table.test.lua | 141 +++++++++++++++++++++++++++++++++++- 3 files changed, 161 insertions(+), 50 deletions(-) diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index abaa73736..5b9553017 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -176,15 +176,17 @@ createkw(A) ::= CREATE(A). {disableLookaside(pParse);} ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} -create_table_args ::= LP columnlist conslist_opt RP(E). { +create_table_args ::= LP columnlist RP(E). { sqlite3EndTable(pParse,&E,0); } create_table_args ::= AS select(S). { sqlite3EndTable(pParse,0,S); sql_select_delete(pParse->db, S); } +columnlist ::= columnlist COMMA tconsdef. columnlist ::= columnlist COMMA columnname carglist. columnlist ::= columnname carglist. +columnlist ::= tconsdef. columnname(A) ::= nm(A) typedef(Y). {sqlite3AddColumn(pParse,&A,&Y);} // An IDENTIFIER can be a generic identifier, or one of several @@ -232,9 +234,11 @@ nm(A) ::= id(A). { // "carglist" is a list of additional constraints that come after the // column name and column type in a CREATE TABLE statement. // -carglist ::= carglist ccons. +carglist ::= carglist cconsdef. carglist ::= . -ccons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsdef ::= cconsname ccons. +cconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsname ::= . {pParse->constraintName.n = 0;} ccons ::= DEFAULT term(X). {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT LP expr(X) RP. {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT PLUS term(X). {sqlite3AddDefaultValue(pParse,&X);} @@ -303,13 +307,9 @@ init_deferred_pred_opt(A) ::= . {A = 0;} init_deferred_pred_opt(A) ::= INITIALLY DEFERRED. {A = 1;} init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE. {A = 0;} -conslist_opt ::= . -conslist_opt ::= COMMA conslist. -conslist ::= conslist tconscomma tcons. -conslist ::= tcons. -tconscomma ::= COMMA. {pParse->constraintName.n = 0;} -tconscomma ::= . -tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsdef ::= tconsname tcons. +tconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsname ::= . {pParse->constraintName.n = 0;} tcons ::= PRIMARY KEY LP sortlist(X) autoinc(I) RP. {sqlite3AddPrimaryKey(pParse,X,I,0);} tcons ::= UNIQUE LP sortlist(X) RP. diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua index 039e2291e..ebdbc5b13 100755 --- a/test/sql-tap/check.test.lua +++ b/test/sql-tap/check.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(60) +test:plan(58) --!./tcltestrunner.lua -- 2005 November 2 @@ -270,59 +270,31 @@ test:do_catchsql_test( -- </check-2.6> }) --- Undocumented behavior: The CONSTRAINT name clause can follow a constraint. --- Such a clause is ignored. But the parser must accept it for backwards --- compatibility. --- -test:do_execsql_test( +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. + +test:do_catchsql_test( "check-2.10", [[ CREATE TABLE t2b( - x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, - y TEXT PRIMARY KEY constraint two, - z INTEGER, - UNIQUE(x,z) constraint three + x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one ); ]], { -- <check-2.10> - + 1, "near \")\": syntax error" -- </check-2.10> }) test:do_catchsql_test( "check-2.11", - [[ - INSERT INTO t2b VALUES('xyzzy','hi',5); - ]], { - -- <check-2.11> - 1, "CHECK constraint failed: T2B" - -- </check-2.11> - }) - -test:do_execsql_test( - "check-2.12", [[ CREATE TABLE t2c( - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key - CHECK( typeof(coalesce(x,0))=='integer' ) - CONSTRAINT x_two CONSTRAINT x_three, - y INTEGER, z INTEGER, - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) + CONSTRAINT two ); ]], { - -- <check-2.12> - - -- </check-2.12> - }) - -test:do_catchsql_test( - "check-2.13", - [[ - INSERT INTO t2c VALUES('xyzzy',7,8); - ]], { - -- <check-2.13> - 1, "CHECK constraint failed: X_TWO" - -- </check-2.13> + -- <check-2.10> + 1, "near \")\": syntax error" + -- </check-2.10> }) test:do_execsql_test( diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua index 8367ec016..65b45de0a 100755 --- a/test/sql-tap/table.test.lua +++ b/test/sql-tap/table.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(57) +test:plan(68) --!./tcltestrunner.lua -- 2001 September 15 @@ -1180,4 +1180,143 @@ test:do_test( -- </table-15.1> }) + +-- gh-3504 Check the possibility appear constraints along with columns +-- definitions. + +test:do_execsql_test( + "table-21.1", + [[ + CREATE TABLE t21( + a integer, + primary key (a), + b integer, + check (b > 0), + c integer + check (c > 0) + ); + ]], { + -- <table-21.1> + + -- </table-21.1> + }) + +test:do_catchsql_test( + "table-21.2", + [[ + INSERT INTO t21 VALUES(1, 1, 1); + INSERT INTO t21 VALUES(1, 2, 2); + ]], { + -- <table-21.2> + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" + -- </table-21.2> + }) + +test:do_catchsql_test( + "table-21.3", + [[ + INSERT INTO t21 VALUES(1, -1, 1); + ]], { + -- <table-21.3> + 1, "CHECK constraint failed: T21" + -- </table-21.3> + }) + +test:do_catchsql_test( + "table-21.4", + [[ + INSERT INTO t21 VALUES(1, 1, -1); + ]], { + -- <table-21.4> + 1, "CHECK constraint failed: T21" + -- </table-21.4> + }) + +test:do_execsql_test( + "check-21.cleanup", + [[ + DROP TABLE IF EXISTS t21; + ]], { + -- <check-21.cleanup> + + -- </check-21.cleanup> + }) + +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint +-- only before. + +test:do_catchsql_test( + "table-22.1", + [[ + CREATE TABLE t22( + a integer, + primary key (a) constraint one + ); + ]], { + -- <table-22.1> + 1,"keyword \"constraint\" is reserved" + -- </table-22.1> + }) + +test:do_execsql_test( + "table-22.2", + [[ + CREATE TABLE t22( + a integer primary key, + b integer, + constraint one unique (b), + c integer + ); + ]], { + -- <table-22.2> + + -- </table-22.2> + }) + +test:do_catchsql_test( + "table-22.3", + [[ + INSERT INTO t22 VALUES(1, 1, 1); + INSERT INTO t22 VALUES(2, 1, 1); + ]], { + -- <table-22.3> + 1,"Duplicate key exists in unique index 'unique_ONE_2' in space 'T22'" + -- </table-22.3> + }) + +test:do_execsql_test( + "table-22.4", + [[ + CREATE TABLE t24( + a integer primary key, + b integer constraint two unique, + c integer + ); + ]], { + -- <table-22.4> + + -- </table-22.4> + }) + +test:do_catchsql_test( + "table-22.5", + [[ + INSERT INTO t24 VALUES(1, 1, 1); + INSERT INTO t24 VALUES(2, 1, 1); + ]], { + -- <table-21.5> + 1, "Duplicate key exists in unique index 'unique_TWO_2' in space 'T24'" + -- </table-22.5> + }) + +test:do_execsql_test( + "check-22.cleanup", + [[ + DROP TABLE IF EXISTS t22; + DROP TABLE IF EXISTS t24; + ]], { + -- <check-22.cleanup> + + -- </check-22.cleanup> + }) test:finish_test() -- 2.19.1 ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-18 14:31 [tarantool-patches] [PATCH] sql: constraints definition among columns in CREATE TABLE() Roman Khabibov @ 2018-11-19 12:41 ` Vladislav Shpilevoy 2018-11-21 5:04 ` roman.habibov1 2018-11-26 10:33 ` Kirill Yukhin 1 sibling, 1 reply; 7+ messages in thread From: Vladislav Shpilevoy @ 2018-11-19 12:41 UTC (permalink / raw) To: tarantool-patches, Roman Khabibov Hi! Thanks for the patch! Please, fix one minor comment below. On 18/11/2018 17:31, Roman Khabibov wrote: > Allow constraints to appear along with columns definitions. Disallow typing > a constraint name without specifying the constraint and after. > > Closes #3504 > > Branch: https://github.com/tarantool/tarantool/tree/romanhabibov/gh-3504-constraints-create-table > Issue: https://github.com/tarantool/tarantool/issues/3504 > diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua > index 8367ec016..65b45de0a 100755 > --- a/test/sql-tap/table.test.lua > +++ b/test/sql-tap/table.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test = require("sqltester") > -test:plan(57) > +test:plan(68) > > --!./tcltestrunner.lua > -- 2001 September 15 > @@ -1180,4 +1180,143 @@ test:do_test( Please, add a test that I can not write multiple 'CONSTRAINT <name>' in a row. > > -- </table-15.1> > }) ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-19 12:41 ` [tarantool-patches] " Vladislav Shpilevoy @ 2018-11-21 5:04 ` roman.habibov1 2018-11-21 11:01 ` Vladislav Shpilevoy 2018-11-21 19:29 ` n.pettik 0 siblings, 2 replies; 7+ messages in thread From: roman.habibov1 @ 2018-11-21 5:04 UTC (permalink / raw) To: Vladislav Shpilevoy, tarantool-patches Hi! Thanks for review. > Hi! Thanks for the patch! Please, fix one minor comment > below. > Please, add a test that I can not write multiple 'CONSTRAINT <name>' > in a row. > >> -- </table-15.1> >> }) Done. commit 5bcc130bcabaa1f8efe8b12af35be5f2a4a05e6e Author: Roman Khabibov <roman.habibov1@yandex.ru> Date: Sun Nov 18 15:54:53 2018 +0300 sql: constraints def among columns in CREATE TABLE() Allow constraints to appear along with columns definitions. Disallow typing a constraint name without specifying the constraint and after. Closes #3504 diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index abaa73736..5b9553017 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -176,15 +176,17 @@ createkw(A) ::= CREATE(A). {disableLookaside(pParse);} ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} -create_table_args ::= LP columnlist conslist_opt RP(E). { +create_table_args ::= LP columnlist RP(E). { sqlite3EndTable(pParse,&E,0); } create_table_args ::= AS select(S). { sqlite3EndTable(pParse,0,S); sql_select_delete(pParse->db, S); } +columnlist ::= columnlist COMMA tconsdef. columnlist ::= columnlist COMMA columnname carglist. columnlist ::= columnname carglist. +columnlist ::= tconsdef. columnname(A) ::= nm(A) typedef(Y). {sqlite3AddColumn(pParse,&A,&Y);} // An IDENTIFIER can be a generic identifier, or one of several @@ -232,9 +234,11 @@ nm(A) ::= id(A). { // "carglist" is a list of additional constraints that come after the // column name and column type in a CREATE TABLE statement. // -carglist ::= carglist ccons. +carglist ::= carglist cconsdef. carglist ::= . -ccons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsdef ::= cconsname ccons. +cconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsname ::= . {pParse->constraintName.n = 0;} ccons ::= DEFAULT term(X). {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT LP expr(X) RP. {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT PLUS term(X). {sqlite3AddDefaultValue(pParse,&X);} @@ -303,13 +307,9 @@ init_deferred_pred_opt(A) ::= . {A = 0;} init_deferred_pred_opt(A) ::= INITIALLY DEFERRED. {A = 1;} init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE. {A = 0;} -conslist_opt ::= . -conslist_opt ::= COMMA conslist. -conslist ::= conslist tconscomma tcons. -conslist ::= tcons. -tconscomma ::= COMMA. {pParse->constraintName.n = 0;} -tconscomma ::= . -tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsdef ::= tconsname tcons. +tconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsname ::= . {pParse->constraintName.n = 0;} tcons ::= PRIMARY KEY LP sortlist(X) autoinc(I) RP. {sqlite3AddPrimaryKey(pParse,X,I,0);} tcons ::= UNIQUE LP sortlist(X) RP. diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua index 039e2291e..ebdbc5b13 100755 --- a/test/sql-tap/check.test.lua +++ b/test/sql-tap/check.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(60) +test:plan(58) --!./tcltestrunner.lua -- 2005 November 2 @@ -270,59 +270,31 @@ test:do_catchsql_test( -- </check-2.6> }) --- Undocumented behavior: The CONSTRAINT name clause can follow a constraint. --- Such a clause is ignored. But the parser must accept it for backwards --- compatibility. --- -test:do_execsql_test( +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. + +test:do_catchsql_test( "check-2.10", [[ CREATE TABLE t2b( - x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, - y TEXT PRIMARY KEY constraint two, - z INTEGER, - UNIQUE(x,z) constraint three + x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one ); ]], { -- <check-2.10> - + 1, "near \")\": syntax error" -- </check-2.10> }) test:do_catchsql_test( "check-2.11", - [[ - INSERT INTO t2b VALUES('xyzzy','hi',5); - ]], { - -- <check-2.11> - 1, "CHECK constraint failed: T2B" - -- </check-2.11> - }) - -test:do_execsql_test( - "check-2.12", [[ CREATE TABLE t2c( - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key - CHECK( typeof(coalesce(x,0))=='integer' ) - CONSTRAINT x_two CONSTRAINT x_three, - y INTEGER, z INTEGER, - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) + CONSTRAINT two ); ]], { - -- <check-2.12> - - -- </check-2.12> - }) - -test:do_catchsql_test( - "check-2.13", - [[ - INSERT INTO t2c VALUES('xyzzy',7,8); - ]], { - -- <check-2.13> - 1, "CHECK constraint failed: X_TWO" - -- </check-2.13> + -- <check-2.10> + 1, "near \")\": syntax error" + -- </check-2.10> }) test:do_execsql_test( diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua index 8367ec016..0d70187ba 100755 --- a/test/sql-tap/table.test.lua +++ b/test/sql-tap/table.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(57) +test:plan(70) --!./tcltestrunner.lua -- 2001 September 15 @@ -1180,4 +1180,171 @@ test:do_test( -- </table-15.1> }) + +-- gh-3504 Check the possibility appear constraints along with columns +-- definitions. + +test:do_execsql_test( + "table-21.1", + [[ + CREATE TABLE t21( + a integer, + primary key (a), + b integer, + check (b > 0), + c integer + check (c > 0) + ); + ]], { + -- <table-21.1> + + -- </table-21.1> + }) + +test:do_catchsql_test( + "table-21.2", + [[ + INSERT INTO t21 VALUES(1, 1, 1); + INSERT INTO t21 VALUES(1, 2, 2); + ]], { + -- <table-21.2> + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" + -- </table-21.2> + }) + +test:do_catchsql_test( + "table-21.3", + [[ + INSERT INTO t21 VALUES(1, -1, 1); + ]], { + -- <table-21.3> + 1, "CHECK constraint failed: T21" + -- </table-21.3> + }) + +test:do_catchsql_test( + "table-21.4", + [[ + INSERT INTO t21 VALUES(1, 1, -1); + ]], { + -- <table-21.4> + 1, "CHECK constraint failed: T21" + -- </table-21.4> + }) + +test:do_execsql_test( + "check-21.cleanup", + [[ + DROP TABLE IF EXISTS t21; + ]], { + -- <check-21.cleanup> + + -- </check-21.cleanup> + }) + +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint +-- only before and once or missing. + +test:do_catchsql_test( + "table-22.1", + [[ + CREATE TABLE t22( + a integer, + primary key (a) constraint one + ); + ]], { + -- <table-22.1> + 1,"keyword \"constraint\" is reserved" + -- </table-22.1> + }) + +test:do_execsql_test( + "table-22.2", + [[ + CREATE TABLE t22( + a integer primary key, + b integer, + constraint one unique (b), + c integer + ); + ]], { + -- <table-22.2> + + -- </table-22.2> + }) + +test:do_catchsql_test( + "table-22.3", + [[ + INSERT INTO t22 VALUES(1, 1, 1); + INSERT INTO t22 VALUES(2, 1, 1); + ]], { + -- <table-22.3> + 1,"Duplicate key exists in unique index 'unique_ONE_2' in space 'T22'" + -- </table-22.3> + }) + +test:do_execsql_test( + "table-22.4", + [[ + CREATE TABLE t24( + a integer primary key, + b integer constraint two unique, + c integer + ); + ]], { + -- <table-22.4> + + -- </table-22.4> + }) + +test:do_catchsql_test( + "table-22.5", + [[ + INSERT INTO t24 VALUES(1, 1, 1); + INSERT INTO t24 VALUES(2, 1, 1); + ]], { + -- <table-21.5> + 1, "Duplicate key exists in unique index 'unique_TWO_2' in space 'T24'" + -- </table-22.5> + }) + +test:do_catchsql_test( + "table-22.6", + [[ + CREATE TABLE t26( + a integer primary key, + b integer constraint one constraint one unique, + c integer + ); + ]], { + -- <table-22.6> + 1,"keyword \"constraint\" is reserved" + -- </table-22.6> + }) + +test:do_catchsql_test( + "table-22.7", + [[ + CREATE TABLE t27( + a integer primary key, + b integer constraint one constraint two unique, + c integer + ); + ]], { + -- <table-22.7> + 1,"keyword \"constraint\" is reserved" + -- </table-22.7> + }) + +test:do_execsql_test( + "check-22.cleanup", + [[ + DROP TABLE IF EXISTS t22; + DROP TABLE IF EXISTS t24; + ]], { + -- <check-22.cleanup> + + -- </check-22.cleanup> + }) test:finish_test() ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-21 5:04 ` roman.habibov1 @ 2018-11-21 11:01 ` Vladislav Shpilevoy 2018-11-21 19:29 ` n.pettik 1 sibling, 0 replies; 7+ messages in thread From: Vladislav Shpilevoy @ 2018-11-21 11:01 UTC (permalink / raw) To: roman.habibov1, tarantool-patches, Nikita Pettik Thanks for the fix! LGTM. Nikita, please, review. On 21/11/2018 08:04, roman.habibov1@yandex.ru wrote: > Hi! Thanks for review. > >> Hi! Thanks for the patch! Please, fix one minor comment >> below. >> Please, add a test that I can not write multiple 'CONSTRAINT <name>' >> in a row. >> >>> -- </table-15.1> >>> }) > Done. > > commit 5bcc130bcabaa1f8efe8b12af35be5f2a4a05e6e > Author: Roman Khabibov <roman.habibov1@yandex.ru> > Date: Sun Nov 18 15:54:53 2018 +0300 > > sql: constraints def among columns in CREATE TABLE() > > Allow constraints to appear along with columns definitions. Disallow typing > a constraint name without specifying the constraint and after. > > Closes #3504 > > diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y > index abaa73736..5b9553017 100644 > --- a/src/box/sql/parse.y > +++ b/src/box/sql/parse.y > @@ -176,15 +176,17 @@ createkw(A) ::= CREATE(A). {disableLookaside(pParse);} > ifnotexists(A) ::= . {A = 0;} > ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} > > -create_table_args ::= LP columnlist conslist_opt RP(E). { > +create_table_args ::= LP columnlist RP(E). { > sqlite3EndTable(pParse,&E,0); > } > create_table_args ::= AS select(S). { > sqlite3EndTable(pParse,0,S); > sql_select_delete(pParse->db, S); > } > +columnlist ::= columnlist COMMA tconsdef. > columnlist ::= columnlist COMMA columnname carglist. > columnlist ::= columnname carglist. > +columnlist ::= tconsdef. > columnname(A) ::= nm(A) typedef(Y). {sqlite3AddColumn(pParse,&A,&Y);} > > // An IDENTIFIER can be a generic identifier, or one of several > @@ -232,9 +234,11 @@ nm(A) ::= id(A). { > // "carglist" is a list of additional constraints that come after the > // column name and column type in a CREATE TABLE statement. > // > -carglist ::= carglist ccons. > +carglist ::= carglist cconsdef. > carglist ::= . > -ccons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} > +cconsdef ::= cconsname ccons. > +cconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} > +cconsname ::= . {pParse->constraintName.n = 0;} > ccons ::= DEFAULT term(X). {sqlite3AddDefaultValue(pParse,&X);} > ccons ::= DEFAULT LP expr(X) RP. {sqlite3AddDefaultValue(pParse,&X);} > ccons ::= DEFAULT PLUS term(X). {sqlite3AddDefaultValue(pParse,&X);} > @@ -303,13 +307,9 @@ init_deferred_pred_opt(A) ::= . {A = 0;} > init_deferred_pred_opt(A) ::= INITIALLY DEFERRED. {A = 1;} > init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE. {A = 0;} > > -conslist_opt ::= . > -conslist_opt ::= COMMA conslist. > -conslist ::= conslist tconscomma tcons. > -conslist ::= tcons. > -tconscomma ::= COMMA. {pParse->constraintName.n = 0;} > -tconscomma ::= . > -tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} > +tconsdef ::= tconsname tcons. > +tconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} > +tconsname ::= . {pParse->constraintName.n = 0;} > tcons ::= PRIMARY KEY LP sortlist(X) autoinc(I) RP. > {sqlite3AddPrimaryKey(pParse,X,I,0);} > tcons ::= UNIQUE LP sortlist(X) RP. > diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua > index 039e2291e..ebdbc5b13 100755 > --- a/test/sql-tap/check.test.lua > +++ b/test/sql-tap/check.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test = require("sqltester") > -test:plan(60) > +test:plan(58) > > --!./tcltestrunner.lua > -- 2005 November 2 > @@ -270,59 +270,31 @@ test:do_catchsql_test( > -- </check-2.6> > }) > > --- Undocumented behavior: The CONSTRAINT name clause can follow a constraint. > --- Such a clause is ignored. But the parser must accept it for backwards > --- compatibility. > --- > -test:do_execsql_test( > +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. > + > +test:do_catchsql_test( > "check-2.10", > [[ > CREATE TABLE t2b( > - x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, > - y TEXT PRIMARY KEY constraint two, > - z INTEGER, > - UNIQUE(x,z) constraint three > + x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one > ); > ]], { > -- <check-2.10> > - > + 1, "near \")\": syntax error" > -- </check-2.10> > }) > > test:do_catchsql_test( > "check-2.11", > - [[ > - INSERT INTO t2b VALUES('xyzzy','hi',5); > - ]], { > - -- <check-2.11> > - 1, "CHECK constraint failed: T2B" > - -- </check-2.11> > - }) > - > -test:do_execsql_test( > - "check-2.12", > [[ > CREATE TABLE t2c( > - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key > - CHECK( typeof(coalesce(x,0))=='integer' ) > - CONSTRAINT x_two CONSTRAINT x_three, > - y INTEGER, z INTEGER, > - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two > + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) > + CONSTRAINT two > ); > ]], { > - -- <check-2.12> > - > - -- </check-2.12> > - }) > - > -test:do_catchsql_test( > - "check-2.13", > - [[ > - INSERT INTO t2c VALUES('xyzzy',7,8); > - ]], { > - -- <check-2.13> > - 1, "CHECK constraint failed: X_TWO" > - -- </check-2.13> > + -- <check-2.10> > + 1, "near \")\": syntax error" > + -- </check-2.10> > }) > > test:do_execsql_test( > diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua > index 8367ec016..0d70187ba 100755 > --- a/test/sql-tap/table.test.lua > +++ b/test/sql-tap/table.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test = require("sqltester") > -test:plan(57) > +test:plan(70) > > --!./tcltestrunner.lua > -- 2001 September 15 > @@ -1180,4 +1180,171 @@ test:do_test( > > -- </table-15.1> > }) > + > +-- gh-3504 Check the possibility appear constraints along with columns > +-- definitions. > + > +test:do_execsql_test( > + "table-21.1", > + [[ > + CREATE TABLE t21( > + a integer, > + primary key (a), > + b integer, > + check (b > 0), > + c integer > + check (c > 0) > + ); > + ]], { > + -- <table-21.1> > + > + -- </table-21.1> > + }) > + > +test:do_catchsql_test( > + "table-21.2", > + [[ > + INSERT INTO t21 VALUES(1, 1, 1); > + INSERT INTO t21 VALUES(1, 2, 2); > + ]], { > + -- <table-21.2> > + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" > + -- </table-21.2> > + }) > + > +test:do_catchsql_test( > + "table-21.3", > + [[ > + INSERT INTO t21 VALUES(1, -1, 1); > + ]], { > + -- <table-21.3> > + 1, "CHECK constraint failed: T21" > + -- </table-21.3> > + }) > + > +test:do_catchsql_test( > + "table-21.4", > + [[ > + INSERT INTO t21 VALUES(1, 1, -1); > + ]], { > + -- <table-21.4> > + 1, "CHECK constraint failed: T21" > + -- </table-21.4> > + }) > + > +test:do_execsql_test( > + "check-21.cleanup", > + [[ > + DROP TABLE IF EXISTS t21; > + ]], { > + -- <check-21.cleanup> > + > + -- </check-21.cleanup> > + }) > + > +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint > +-- only before and once or missing. > + > +test:do_catchsql_test( > + "table-22.1", > + [[ > + CREATE TABLE t22( > + a integer, > + primary key (a) constraint one > + ); > + ]], { > + -- <table-22.1> > + 1,"keyword \"constraint\" is reserved" > + -- </table-22.1> > + }) > + > +test:do_execsql_test( > + "table-22.2", > + [[ > + CREATE TABLE t22( > + a integer primary key, > + b integer, > + constraint one unique (b), > + c integer > + ); > + ]], { > + -- <table-22.2> > + > + -- </table-22.2> > + }) > + > +test:do_catchsql_test( > + "table-22.3", > + [[ > + INSERT INTO t22 VALUES(1, 1, 1); > + INSERT INTO t22 VALUES(2, 1, 1); > + ]], { > + -- <table-22.3> > + 1,"Duplicate key exists in unique index 'unique_ONE_2' in space 'T22'" > + -- </table-22.3> > + }) > + > +test:do_execsql_test( > + "table-22.4", > + [[ > + CREATE TABLE t24( > + a integer primary key, > + b integer constraint two unique, > + c integer > + ); > + ]], { > + -- <table-22.4> > + > + -- </table-22.4> > + }) > + > +test:do_catchsql_test( > + "table-22.5", > + [[ > + INSERT INTO t24 VALUES(1, 1, 1); > + INSERT INTO t24 VALUES(2, 1, 1); > + ]], { > + -- <table-21.5> > + 1, "Duplicate key exists in unique index 'unique_TWO_2' in space 'T24'" > + -- </table-22.5> > + }) > + > +test:do_catchsql_test( > + "table-22.6", > + [[ > + CREATE TABLE t26( > + a integer primary key, > + b integer constraint one constraint one unique, > + c integer > + ); > + ]], { > + -- <table-22.6> > + 1,"keyword \"constraint\" is reserved" > + -- </table-22.6> > + }) > + > +test:do_catchsql_test( > + "table-22.7", > + [[ > + CREATE TABLE t27( > + a integer primary key, > + b integer constraint one constraint two unique, > + c integer > + ); > + ]], { > + -- <table-22.7> > + 1,"keyword \"constraint\" is reserved" > + -- </table-22.7> > + }) > + > +test:do_execsql_test( > + "check-22.cleanup", > + [[ > + DROP TABLE IF EXISTS t22; > + DROP TABLE IF EXISTS t24; > + ]], { > + -- <check-22.cleanup> > + > + -- </check-22.cleanup> > + }) > test:finish_test() > ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-21 5:04 ` roman.habibov1 2018-11-21 11:01 ` Vladislav Shpilevoy @ 2018-11-21 19:29 ` n.pettik 2018-11-22 0:23 ` roman.habibov1 1 sibling, 1 reply; 7+ messages in thread From: n.pettik @ 2018-11-21 19:29 UTC (permalink / raw) To: tarantool-patches; +Cc: Vladislav Shpilevoy, roman.habibov1 > commit 5bcc130bcabaa1f8efe8b12af35be5f2a4a05e6e > Author: Roman Khabibov <roman.habibov1@yandex.ru> > Date: Sun Nov 18 15:54:53 2018 +0300 > > sql: constraints def among columns in CREATE TABLE() Commit message lacks a verb. I would call it sort of: sql: allow appearing constraint definition among columns > Allow constraints to appear along with columns definitions. Disallow typing > a constraint name without specifying the constraint and after. Please, re-phrase last sentence, it is really hard to understand what does it mean: after what? You can fix several nitpickings below, but even with them patch is OK. > diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua > index 039e2291e..ebdbc5b13 100755 > --- a/test/sql-tap/check.test.lua > +++ b/test/sql-tap/check.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test = require("sqltester") > -test:plan(60) > +test:plan(58) > > --!./tcltestrunner.lua > -- 2005 November 2 > @@ -270,59 +270,31 @@ test:do_catchsql_test( > -- </check-2.6> > }) > > -test:do_execsql_test( > - "check-2.12", > [[ > CREATE TABLE t2c( > - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key > - CHECK( typeof(coalesce(x,0))=='integer' ) > - CONSTRAINT x_two CONSTRAINT x_three, > - y INTEGER, z INTEGER, > - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two > + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) > + CONSTRAINT two This test is copy of previous one. To diversify cases you can check another type of constraint. For example: CREATE TABLE t (id INT PRIMARY KEY CONSTRAINT PK); Also, both your examples fail even without your patch since table lacks primary key (yes, error message is different, but lets make only one fail in this test). > test:do_execsql_test( > diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua > index 8367ec016..0d70187ba 100755 > --- a/test/sql-tap/table.test.lua > +++ b/test/sql-tap/table.test.lua > @@ -1,6 +1,6 @@ > #!/usr/bin/env tarantool > test = require("sqltester") > -test:plan(57) > +test:plan(70) > > --!./tcltestrunner.lua > -- 2001 September 15 > @@ -1180,4 +1180,171 @@ test:do_test( > > -- </table-15.1> > }) > + > +-- gh-3504 Check the possibility appear constraints along with columns > +-- definitions. Re-phrase like: “Constraints definition can appear among columns ones." > + > +test:do_execsql_test( > + "table-21.1", > + [[ > + CREATE TABLE t21( > + a integer, > + primary key (a), > + b integer, > + check (b > 0), > + c integer > + check (c > 0) > + ); It would be better to use uppercase for SQL keywords. > + ]], { > + -- <table-21.1> > + > + -- </table-21.1> > + }) > + > +test:do_catchsql_test( > + "table-21.2", > + [[ > + INSERT INTO t21 VALUES(1, 1, 1); > + INSERT INTO t21 VALUES(1, 2, 2); > + ]], { > + -- <table-21.2> > + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" > + -- </table-21.2> > + }) > + > +test:do_catchsql_test( > + "table-21.3", > + [[ > + INSERT INTO t21 VALUES(1, -1, 1); > + ]], { > + -- <table-21.3> > + 1, "CHECK constraint failed: T21" > + -- </table-21.3> > + }) > + > +test:do_catchsql_test( > + "table-21.4", > + [[ > + INSERT INTO t21 VALUES(1, 1, -1); > + ]], { > + -- <table-21.4> > + 1, "CHECK constraint failed: T21" > + -- </table-21.4> > + }) > + > +test:do_execsql_test( > + "check-21.cleanup", > + [[ > + DROP TABLE IF EXISTS t21; > + ]], { > + -- <check-21.cleanup> > + > + -- </check-21.cleanup> > + }) > + > +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint > +-- only before and once or missing. Can’t parse this sentence as well. Re-phrase it pls. > + > +test:do_catchsql_test( > + "table-22.1", > + [[ > + CREATE TABLE t22( > + a integer, > + primary key (a) constraint one > + ); > + ]], { > + -- <table-22.1> > + 1,"keyword \"constraint\" is reserved" > + -- </table-22.1> > + }) > + > +test:do_execsql_test( > + "table-22.2", > + [[ > + CREATE TABLE t22( > + a integer primary key, > + b integer, > + constraint one unique (b), > + c integer > + ); > + ]], { Also, I would add test where several constraint definitions come one after another, like this: CREATE TABLE t (id INT, PRIMARY KEY (id), CONSTRAINT pk1 CHECK(id != 0), CONSTRAINT pk2 CHECK(id > 10)); ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-21 19:29 ` n.pettik @ 2018-11-22 0:23 ` roman.habibov1 0 siblings, 0 replies; 7+ messages in thread From: roman.habibov1 @ 2018-11-22 0:23 UTC (permalink / raw) To: n.pettik, tarantool-patches; +Cc: Vladislav Shpilevoy Hi! Thanks for review. > Commit message lacks a verb. I would call it sort of: > > sql: allow appearing constraint definition among columns Done. >> Allow constraints to appear along with columns definitions. Disallow typing >> a constraint name without specifying the constraint and after. > > Please, re-phrase last sentence, it is really hard to understand what does it mean: > after what? Done. > You can fix several nitpickings below, but even with them patch is OK. > >> diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua >> index 039e2291e..ebdbc5b13 100755 >> --- a/test/sql-tap/check.test.lua >> +++ b/test/sql-tap/check.test.lua >> @@ -1,6 +1,6 @@ >> #!/usr/bin/env tarantool >> test = require("sqltester") >> -test:plan(60) >> +test:plan(58) >> >> --!./tcltestrunner.lua >> -- 2005 November 2 >> @@ -270,59 +270,31 @@ test:do_catchsql_test( >> -- </check-2.6> >> }) >> >> -test:do_execsql_test( >> - "check-2.12", >> [[ >> CREATE TABLE t2c( >> - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key >> - CHECK( typeof(coalesce(x,0))=='integer' ) >> - CONSTRAINT x_two CONSTRAINT x_three, >> - y INTEGER, z INTEGER, >> - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two >> + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) >> + CONSTRAINT two > > This test is copy of previous one. To diversify cases you can check > another type of constraint. For example: > > CREATE TABLE t (id INT PRIMARY KEY CONSTRAINT PK); > > Also, both your examples fail even without your patch since table > lacks primary key (yes, error message is different, but lets make > only one fail in this test). Added primary keys. I thought this file about CHECK CONSTRAINT. >> test:do_execsql_test( >> diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua >> index 8367ec016..0d70187ba 100755 >> --- a/test/sql-tap/table.test.lua >> +++ b/test/sql-tap/table.test.lua >> @@ -1,6 +1,6 @@ >> #!/usr/bin/env tarantool >> test = require("sqltester") >> -test:plan(57) >> +test:plan(70) >> >> --!./tcltestrunner.lua >> -- 2001 September 15 >> @@ -1180,4 +1180,171 @@ test:do_test( >> >> -- </table-15.1> >> }) >> + >> +-- gh-3504 Check the possibility appear constraints along with columns >> +-- definitions. > > Re-phrase like: “Constraints definition can appear among columns ones." Done. >> + >> +test:do_execsql_test( >> + "table-21.1", >> + [[ >> + CREATE TABLE t21( >> + a integer, >> + primary key (a), >> + b integer, >> + check (b > 0), >> + c integer >> + check (c > 0) >> + ); > > It would be better to use uppercase for SQL keywords. Done. >> + ]], { >> + -- <table-21.1> >> + >> + -- </table-21.1> >> + }) >> + >> +test:do_catchsql_test( >> + "table-21.2", >> + [[ >> + INSERT INTO t21 VALUES(1, 1, 1); >> + INSERT INTO t21 VALUES(1, 2, 2); >> + ]], { >> + -- <table-21.2> >> + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" >> + -- </table-21.2> >> + }) >> + >> +test:do_catchsql_test( >> + "table-21.3", >> + [[ >> + INSERT INTO t21 VALUES(1, -1, 1); >> + ]], { >> + -- <table-21.3> >> + 1, "CHECK constraint failed: T21" >> + -- </table-21.3> >> + }) >> + >> +test:do_catchsql_test( >> + "table-21.4", >> + [[ >> + INSERT INTO t21 VALUES(1, 1, -1); >> + ]], { >> + -- <table-21.4> >> + 1, "CHECK constraint failed: T21" >> + -- </table-21.4> >> + }) >> + >> +test:do_execsql_test( >> + "check-21.cleanup", >> + [[ >> + DROP TABLE IF EXISTS t21; >> + ]], { >> + -- <check-21.cleanup> >> + >> + -- </check-21.cleanup> >> + }) >> + >> +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint >> +-- only before and once or missing. > > Can’t parse this sentence as well. Re-phrase it pls. + +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. +-- The name may be typed once before the constraint or not. + >> + >> +test:do_catchsql_test( >> + "table-22.1", >> + [[ >> + CREATE TABLE t22( >> + a integer, >> + primary key (a) constraint one >> + ); >> + ]], { >> + -- <table-22.1> >> + 1,"keyword \"constraint\" is reserved" >> + -- </table-22.1> >> + }) >> + >> +test:do_execsql_test( >> + "table-22.2", >> + [[ >> + CREATE TABLE t22( >> + a integer primary key, >> + b integer, >> + constraint one unique (b), >> + c integer >> + ); >> + ]], { > > Also, I would add test where several constraint definitions > come one after another, like this: > > CREATE TABLE t (id INT, PRIMARY KEY (id), CONSTRAINT pk1 CHECK(id != 0), CONSTRAINT pk2 CHECK(id > 10)); +test:do_execsql_test( + "table-22.8", + [[ + CREATE TABLE T28( + id INT, + PRIMARY KEY (id), + CONSTRAINT check1 CHECK(id != 0), + CONSTRAINT check2 CHECK(id > 10) + ); + ]], { + -- <table-22.8> + + -- </table-22.8> + }) + +test:do_catchsql_test( + "table-22.9", + [[ + INSERT INTO T28 VALUES(11); + INSERT INTO T28 VALUES(11); + ]], { + -- <table-22.9> + 1,"Duplicate key exists in unique index 'pk_unnamed_T28_1' in space 'T28'" + -- </table-22.9> + }) + +test:do_catchsql_test( + "table-22.10", + [[ + INSERT INTO T28 VALUES(0); + ]], { + -- <table-22.10> + 1, "CHECK constraint failed: CHECK1" + -- </table-22.10> + }) + +test:do_catchsql_test( + "table-22.11", + [[ + INSERT INTO T28 VALUES(9); + ]], { + -- <table-22.11> + 1, "CHECK constraint failed: CHECK2" + -- </table-22.11> + }) + commit 0cd2b50906b21d37c017b5e8468e13afb7a7c65b Author: Roman Khabibov <roman.habibov1@yandex.ru> Date: Sun Nov 18 15:54:53 2018 +0300 sql: allow appearing constraint definition among columns Allow constraints to appear along with columns definitions. Disallow typing a constraint name without specifying the constraint and after it. Closes #3504 diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y index abaa73736..5b9553017 100644 --- a/src/box/sql/parse.y +++ b/src/box/sql/parse.y @@ -176,15 +176,17 @@ createkw(A) ::= CREATE(A). {disableLookaside(pParse);} ifnotexists(A) ::= . {A = 0;} ifnotexists(A) ::= IF NOT EXISTS. {A = 1;} -create_table_args ::= LP columnlist conslist_opt RP(E). { +create_table_args ::= LP columnlist RP(E). { sqlite3EndTable(pParse,&E,0); } create_table_args ::= AS select(S). { sqlite3EndTable(pParse,0,S); sql_select_delete(pParse->db, S); } +columnlist ::= columnlist COMMA tconsdef. columnlist ::= columnlist COMMA columnname carglist. columnlist ::= columnname carglist. +columnlist ::= tconsdef. columnname(A) ::= nm(A) typedef(Y). {sqlite3AddColumn(pParse,&A,&Y);} // An IDENTIFIER can be a generic identifier, or one of several @@ -232,9 +234,11 @@ nm(A) ::= id(A). { // "carglist" is a list of additional constraints that come after the // column name and column type in a CREATE TABLE statement. // -carglist ::= carglist ccons. +carglist ::= carglist cconsdef. carglist ::= . -ccons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsdef ::= cconsname ccons. +cconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +cconsname ::= . {pParse->constraintName.n = 0;} ccons ::= DEFAULT term(X). {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT LP expr(X) RP. {sqlite3AddDefaultValue(pParse,&X);} ccons ::= DEFAULT PLUS term(X). {sqlite3AddDefaultValue(pParse,&X);} @@ -303,13 +307,9 @@ init_deferred_pred_opt(A) ::= . {A = 0;} init_deferred_pred_opt(A) ::= INITIALLY DEFERRED. {A = 1;} init_deferred_pred_opt(A) ::= INITIALLY IMMEDIATE. {A = 0;} -conslist_opt ::= . -conslist_opt ::= COMMA conslist. -conslist ::= conslist tconscomma tcons. -conslist ::= tcons. -tconscomma ::= COMMA. {pParse->constraintName.n = 0;} -tconscomma ::= . -tcons ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsdef ::= tconsname tcons. +tconsname ::= CONSTRAINT nm(X). {pParse->constraintName = X;} +tconsname ::= . {pParse->constraintName.n = 0;} tcons ::= PRIMARY KEY LP sortlist(X) autoinc(I) RP. {sqlite3AddPrimaryKey(pParse,X,I,0);} tcons ::= UNIQUE LP sortlist(X) RP. diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua index 039e2291e..c24ae2ff1 100755 --- a/test/sql-tap/check.test.lua +++ b/test/sql-tap/check.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(60) +test:plan(58) --!./tcltestrunner.lua -- 2005 November 2 @@ -270,59 +270,33 @@ test:do_catchsql_test( -- </check-2.6> }) --- Undocumented behavior: The CONSTRAINT name clause can follow a constraint. --- Such a clause is ignored. But the parser must accept it for backwards --- compatibility. --- -test:do_execsql_test( +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. + +test:do_catchsql_test( "check-2.10", [[ CREATE TABLE t2b( x INTEGER CHECK( typeof(coalesce(x,0))=='integer' ) CONSTRAINT one, - y TEXT PRIMARY KEY constraint two, - z INTEGER, - UNIQUE(x,z) constraint three + PRIMARY KEY (x) ); ]], { -- <check-2.10> - + 1,"near \",\": syntax error" -- </check-2.10> }) test:do_catchsql_test( "check-2.11", - [[ - INSERT INTO t2b VALUES('xyzzy','hi',5); - ]], { - -- <check-2.11> - 1, "CHECK constraint failed: T2B" - -- </check-2.11> - }) - -test:do_execsql_test( - "check-2.12", [[ CREATE TABLE t2c( - x INTEGER CONSTRAINT x_one CONSTRAINT x_two primary key - CHECK( typeof(coalesce(x,0))=='integer' ) - CONSTRAINT x_two CONSTRAINT x_three, - y INTEGER, z INTEGER, - CONSTRAINT u_one UNIQUE(x,y,z) CONSTRAINT u_two + x INTEGER CONSTRAINT one CHECK( typeof(coalesce(x,0))=='integer' ) + CONSTRAINT two, + PRIMARY KEY (x) ); ]], { - -- <check-2.12> - - -- </check-2.12> - }) - -test:do_catchsql_test( - "check-2.13", - [[ - INSERT INTO t2c VALUES('xyzzy',7,8); - ]], { - -- <check-2.13> - 1, "CHECK constraint failed: X_TWO" - -- </check-2.13> + -- <check-2.10> + 1,"near \",\": syntax error" + -- </check-2.10> }) test:do_execsql_test( diff --git a/test/sql-tap/table.test.lua b/test/sql-tap/table.test.lua index 8367ec016..7fd9bac9f 100755 --- a/test/sql-tap/table.test.lua +++ b/test/sql-tap/table.test.lua @@ -1,6 +1,6 @@ #!/usr/bin/env tarantool test = require("sqltester") -test:plan(57) +test:plan(74) --!./tcltestrunner.lua -- 2001 September 15 @@ -1180,4 +1180,217 @@ test:do_test( -- </table-15.1> }) + +-- gh-3504 Constraints definition can appear among columns ones. + +test:do_execsql_test( + "table-21.1", + [[ + CREATE TABLE t21( + A INTEGER, + PRIMARY KEY (A), + B INTEGER, + CHECK (B > 0), + C INTEGER + CHECK (C > 0) + ); + ]], { + -- <table-21.1> + + -- </table-21.1> + }) + +test:do_catchsql_test( + "table-21.2", + [[ + INSERT INTO T21 VALUES(1, 1, 1); + INSERT INTO T21 VALUES(1, 2, 2); + ]], { + -- <table-21.2> + 1, "Duplicate key exists in unique index 'pk_unnamed_T21_1' in space 'T21'" + -- </table-21.2> + }) + +test:do_catchsql_test( + "table-21.3", + [[ + INSERT INTO T21 VALUES(1, -1, 1); + ]], { + -- <table-21.3> + 1, "CHECK constraint failed: T21" + -- </table-21.3> + }) + +test:do_catchsql_test( + "table-21.4", + [[ + INSERT INTO T21 VALUES(1, 1, -1); + ]], { + -- <table-21.4> + 1, "CHECK constraint failed: T21" + -- </table-21.4> + }) + +test:do_execsql_test( + "check-21.cleanup", + [[ + DROP TABLE IF EXISTS T21; + ]], { + -- <check-21.cleanup> + + -- </check-21.cleanup> + }) + +-- gh-3504: Check the CONSTRAINT name clause can't follow a constraint. +-- The name may be typed once before the constraint or not. + +test:do_catchsql_test( + "table-22.1", + [[ + CREATE TABLE T22( + A INTEGER, + PRIMARY KEY (A) CONSTRAINT ONE + ); + ]], { + -- <table-22.1> + 1,"keyword \"CONSTRAINT\" is reserved" + -- </table-22.1> + }) + +test:do_execsql_test( + "table-22.2", + [[ + CREATE TABLE T22( + A INTEGER PRIMARY KEY, + B INTEGER, + CONSTRAINT ONE UNIQUE (B), + C INTEGER + ); + ]], { + -- <table-22.2> + + -- </table-22.2> + }) + +test:do_catchsql_test( + "table-22.3", + [[ + INSERT INTO T22 VALUES(1, 1, 1); + INSERT INTO T22 VALUES(2, 1, 1); + ]], { + -- <table-22.3> + 1,"Duplicate key exists in unique index 'unique_ONE_2' in space 'T22'" + -- </table-22.3> + }) + +test:do_execsql_test( + "table-22.4", + [[ + CREATE TABLE T24( + A INTEGER PRIMARY KEY, + B INTEGER CONSTRAINT TWO UNIQUE, + C INTEGER + ); + ]], { + -- <table-22.4> + + -- </table-22.4> + }) + +test:do_catchsql_test( + "table-22.5", + [[ + INSERT INTO T24 VALUES(1, 1, 1); + INSERT INTO T24 VALUES(2, 1, 1); + ]], { + -- <table-22.5> + 1, "Duplicate key exists in unique index 'unique_TWO_2' in space 'T24'" + -- </table-22.5> + }) + +test:do_catchsql_test( + "table-22.6", + [[ + CREATE TABLE T26( + A INTEGER PRIMARY KEY, + B INTEGER CONSTRAINT ONE CONSTRAINT ONE UNIQUE, + C INTEGER + ); + ]], { + -- <table-22.6> + 1,"keyword \"CONSTRAINT\" is reserved" + -- </table-22.6> + }) + +test:do_catchsql_test( + "table-22.7", + [[ + CREATE TABLE T27( + A INTEGER PRIMARY KEY, + B INTEGER CONSTRAINT ONE CONSTRAINT TWO UNIQUE, + C INTEGER + ); + ]], { + -- <table-22.7> + 1,"keyword \"CONSTRAINT\" is reserved" + -- </table-22.7> + }) + +test:do_execsql_test( + "table-22.8", + [[ + CREATE TABLE T28( + id INT, + PRIMARY KEY (id), + CONSTRAINT check1 CHECK(id != 0), + CONSTRAINT check2 CHECK(id > 10) + ); + ]], { + -- <table-22.8> + + -- </table-22.8> + }) + +test:do_catchsql_test( + "table-22.9", + [[ + INSERT INTO T28 VALUES(11); + INSERT INTO T28 VALUES(11); + ]], { + -- <table-22.9> + 1,"Duplicate key exists in unique index 'pk_unnamed_T28_1' in space 'T28'" + -- </table-22.9> + }) + +test:do_catchsql_test( + "table-22.10", + [[ + INSERT INTO T28 VALUES(0); + ]], { + -- <table-22.10> + 1, "CHECK constraint failed: CHECK1" + -- </table-22.10> + }) + +test:do_catchsql_test( + "table-22.11", + [[ + INSERT INTO T28 VALUES(9); + ]], { + -- <table-22.11> + 1, "CHECK constraint failed: CHECK2" + -- </table-22.11> + }) + +test:do_execsql_test( + "check-22.cleanup", + [[ + DROP TABLE IF EXISTS t22; + DROP TABLE IF EXISTS t24; + DROP TABLE IF EXISTS t28; + ]], { + -- <check-22.cleanup> + + -- </check-22.cleanup> + }) test:finish_test() ^ permalink raw reply [flat|nested] 7+ messages in thread
* [tarantool-patches] Re: [PATCH] sql: constraints definition among columns in CREATE TABLE() 2018-11-18 14:31 [tarantool-patches] [PATCH] sql: constraints definition among columns in CREATE TABLE() Roman Khabibov 2018-11-19 12:41 ` [tarantool-patches] " Vladislav Shpilevoy @ 2018-11-26 10:33 ` Kirill Yukhin 1 sibling, 0 replies; 7+ messages in thread From: Kirill Yukhin @ 2018-11-26 10:33 UTC (permalink / raw) To: tarantool-patches; +Cc: v.shpilevoy Hello, On 18 Nov 17:31, Roman Khabibov wrote: > Allow constraints to appear along with columns definitions. Disallow typing > a constraint name without specifying the constraint and after. > > Closes #3504 > > Branch: https://github.com/tarantool/tarantool/tree/romanhabibov/gh-3504-constraints-create-table > Issue: https://github.com/tarantool/tarantool/issues/3504 I've checked your patch into 2.1 branch. -- Regards, Kirill Yukhin ^ permalink raw reply [flat|nested] 7+ messages in thread
end of thread, other threads:[~2018-11-26 10:33 UTC | newest] Thread overview: 7+ messages (download: mbox.gz / follow: Atom feed) -- links below jump to the message on this page -- 2018-11-18 14:31 [tarantool-patches] [PATCH] sql: constraints definition among columns in CREATE TABLE() Roman Khabibov 2018-11-19 12:41 ` [tarantool-patches] " Vladislav Shpilevoy 2018-11-21 5:04 ` roman.habibov1 2018-11-21 11:01 ` Vladislav Shpilevoy 2018-11-21 19:29 ` n.pettik 2018-11-22 0:23 ` roman.habibov1 2018-11-26 10:33 ` Kirill Yukhin
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox