[tarantool-patches] [PATCH v3 2/4] sql: an ability to disable CK constraints
Kirill Shcherbatov
kshcherbatov at tarantool.org
Mon Sep 16 15:47:10 MSK 2019
Closes #4244
@TarantoolBot document
Title: an ability to disable CK constraints
Now it is possible to disable and enable ck constraints.
This option is not persistent. All ck constraints are enabled
by default when Tarantool is configured. Ck constraints checks
are not performed during standard recovery, but performed during
force_recovery - all conflicting tuples are skipped in case of
ck_constraint conflict.
To change CK constraint "is_enabled" state, call
-- in LUA
ck_obj:enable(new_state in {true, false})
-- in SQL
ALTER TABLE {TABLE_NAME} {EN, DIS}ABLE CONSTRAINT {CK_NAME};
Example:
box.space.T6.ck_constraint.ck_unnamed_T6_1:enable(false)
box.space.T6.ck_constraint.ck_unnamed_T6_1
- space_id: 512
is_enabled: false
name: ck_unnamed_T6_1
expr: a < 10
box.space.T6:insert({11})
-- passed
box.execute("ALTER TABLE t6 ENABLE CONSTRAINT \"ck_unnamed_T6_1\"")
box.space.T6:insert({12})
- error: 'Check constraint failed ''ck_unnamed_T6_1'': a < 10'
---
extra/mkkeywordhash.c | 2 +
src/box/sql/alter.c | 38 ++
src/box/sql/parse.y | 10 +
src/box/sql/parse_def.h | 25 +-
src/box/sql/sqlInt.h | 10 +
test/sql/checks.re | 817 +++++++++++++++++++++++++++++++++++++++
test/sql/checks.result | 49 +++
test/sql/checks.test.lua | 15 +
8 files changed, 965 insertions(+), 1 deletion(-)
create mode 100644 test/sql/checks.re
diff --git a/extra/mkkeywordhash.c b/extra/mkkeywordhash.c
index 2a59d6a61..3c3de4cca 100644
--- a/extra/mkkeywordhash.c
+++ b/extra/mkkeywordhash.c
@@ -89,6 +89,7 @@ static Keyword aKeywordTable[] = {
{ "DEFERRED", "TK_DEFERRED", false },
{ "DEFERRABLE", "TK_DEFERRABLE", false },
{ "DELETE", "TK_DELETE", true },
+ { "DISABLE", "TK_DISABLE", false },
{ "DESC", "TK_DESC", true },
{ "DISTINCT", "TK_DISTINCT", true },
{ "DROP", "TK_DROP", true },
@@ -203,6 +204,7 @@ static Keyword aKeywordTable[] = {
{ "DETERMINISTIC", "TK_STANDARD", true },
{ "DOUBLE", "TK_STANDARD", true },
{ "ELSEIF", "TK_STANDARD", true },
+ { "ENABLE", "TK_ENABLE", false },
{ "FETCH", "TK_STANDARD", true },
{ "FLOAT", "TK_STANDARD", true },
{ "FUNCTION", "TK_STANDARD", true },
diff --git a/src/box/sql/alter.c b/src/box/sql/alter.c
index 765600186..963d77a0a 100644
--- a/src/box/sql/alter.c
+++ b/src/box/sql/alter.c
@@ -36,6 +36,7 @@
#include "sqlInt.h"
#include "box/box.h"
#include "box/schema.h"
+#include "box/ck_constraint.h"
void
sql_alter_table_rename(struct Parse *parse)
@@ -79,6 +80,43 @@ tnt_error:
goto exit_rename_table;
}
+void
+sql_alter_ck_constraint_enable(struct Parse *parse)
+{
+ struct enable_entity_def *enable_def = &parse->enable_entity_def;
+ struct SrcList *src_tab = enable_def->base.entity_name;
+ assert(enable_def->base.entity_type == ENTITY_TYPE_CK);
+ assert(enable_def->base.alter_action == ALTER_ACTION_ENABLE);
+ assert(src_tab->nSrc == 1);
+ struct sql *db = parse->db;
+
+ char *constraint_name = NULL;
+ const char *tbl_name = src_tab->a[0].zName;
+ struct space *space = space_by_name(tbl_name);
+ if (space == NULL) {
+ diag_set(ClientError, ER_NO_SUCH_SPACE, tbl_name);
+ goto tnt_error;
+ }
+
+ constraint_name = sql_name_from_token(db, &enable_def->name);
+ if (constraint_name == NULL)
+ goto tnt_error;
+
+ if (space_ck_constraint_enable(space, constraint_name,
+ enable_def->new_status) != 0) {
+ diag_set(ClientError, ER_NO_SUCH_CONSTRAINT, constraint_name);
+ goto tnt_error;
+ }
+
+exit_alter_ck_constraint:
+ sqlDbFree(db, constraint_name);
+ sqlSrcListDelete(db, src_tab);
+ return;
+tnt_error:
+ parse->is_aborted = true;
+ goto exit_alter_ck_constraint;
+}
+
/* This function is used to implement the ALTER TABLE command.
* The table name in the CREATE TRIGGER statement is replaced with the third
* argument and the result returned. This is analagous to rename_table()
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index 643e025bd..9d134cc12 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -760,6 +760,10 @@ sortlist(A) ::= expr(Y) sortorder(Z). {
sqlExprListSetSortOrder(A,Z);
}
+%type enable {bool}
+enable(A) ::= ENABLE. {A = true;}
+enable(A) ::= DISABLE. {A = false;}
+
%type sortorder {int}
sortorder(A) ::= ASC. {A = SORT_ORDER_ASC;}
@@ -1747,6 +1751,12 @@ cmd ::= ALTER TABLE fullname(X) DROP CONSTRAINT nm(Z). {
sql_drop_foreign_key(pParse);
}
+cmd ::= alter_table_start(A) enable(E) CONSTRAINT nm(Z). {
+ enable_entity_def_init(&pParse->enable_entity_def, ENTITY_TYPE_CK, A,
+ &Z, E);
+ sql_alter_ck_constraint_enable(pParse);
+}
+
//////////////////////// COMMON TABLE EXPRESSIONS ////////////////////////////
%type with {With*}
%type wqlist {With*}
diff --git a/src/box/sql/parse_def.h b/src/box/sql/parse_def.h
index 557e41529..0f07a1dc5 100644
--- a/src/box/sql/parse_def.h
+++ b/src/box/sql/parse_def.h
@@ -169,7 +169,8 @@ enum entity_type {
enum alter_action {
ALTER_ACTION_CREATE = 0,
ALTER_ACTION_DROP,
- ALTER_ACTION_RENAME
+ ALTER_ACTION_RENAME,
+ ALTER_ACTION_ENABLE,
};
struct alter_entity_def {
@@ -181,6 +182,17 @@ struct alter_entity_def {
struct SrcList *entity_name;
};
+struct enable_entity_def {
+ struct alter_entity_def base;
+ /**
+ * Name of index/trigger/constraint to be
+ * enabled/disabled.
+ */
+ struct Token name;
+ /** A new state to be set for entity found. */
+ bool new_status;
+};
+
struct rename_entity_def {
struct alter_entity_def base;
struct Token new_name;
@@ -325,6 +337,17 @@ rename_entity_def_init(struct rename_entity_def *rename_def,
rename_def->new_name = *new_name;
}
+static inline void
+enable_entity_def_init(struct enable_entity_def *enable_def,
+ enum entity_type type, struct SrcList *parent_name,
+ struct Token *name, bool new_status)
+{
+ alter_entity_def_init(&enable_def->base, parent_name, type,
+ ALTER_ACTION_ENABLE);
+ enable_def->name = *name;
+ enable_def->new_status = new_status;
+}
+
static inline void
create_entity_def_init(struct create_entity_def *create_def,
enum entity_type type, struct SrcList *parent_name,
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index e617edd79..24687e08f 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -2192,6 +2192,7 @@ struct Parse {
struct drop_table_def drop_table_def;
struct drop_trigger_def drop_trigger_def;
struct drop_view_def drop_view_def;
+ struct enable_entity_def enable_entity_def;
};
/**
* Table def is not part of union since information
@@ -3961,6 +3962,15 @@ extern int sqlPendingByte;
void
sql_alter_table_rename(struct Parse *parse);
+/**
+ * Generate code to implement the "ALTER TABLE xxx RENAME TO yyy"
+ * command.
+ *
+ * @param parse Current parsing context.
+ */
+void
+sql_alter_ck_constraint_enable(struct Parse *parse);
+
/**
* Return the length (in bytes) of the token that begins at z[0].
* Store the token type in *type before returning.
diff --git a/test/sql/checks.re b/test/sql/checks.re
new file mode 100644
index 000000000..d701bc11c
--- /dev/null
+++ b/test/sql/checks.re
@@ -0,0 +1,817 @@
+env = require('test_run')
+---
+...
+test_run = env.new()
+---
+...
+test_run:cmd("push filter ".."'\\.lua.*:[0-9]+: ' to '.lua...\"]:<line>: '")
+---
+- true
+...
+engine = test_run:get_cfg('engine')
+---
+...
+box.execute('pragma sql_default_engine=\''..engine..'\'')
+---
+- row_count: 0
+...
+--
+-- gh-3272: Move SQL CHECK into server
+--
+-- Until Tarantool version 2.2 check constraints were stored in
+-- space opts.
+-- Make sure that now this legacy option is ignored.
+opts = {checks = {{expr = 'X>5'}}}
+---
+...
+format = {{name = 'X', type = 'unsigned'}}
+---
+...
+t = {513, 1, 'test', 'memtx', 0, opts, format}
+---
+...
+s = box.space._space:insert(t)
+---
+...
+_ = box.space.test:create_index('pk')
+---
+...
+-- Invalid expression test.
+box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', false, 'SQL', 'X><5'})
+---
+- error: 'Failed to create check constraint ''CK_CONSTRAINT_01'': Syntax error near
+ ''<'''
+...
+-- Non-existent space test.
+box.space._ck_constraint:insert({550, 'CK_CONSTRAINT_01', false, 'SQL', 'X<5'})
+---
+- error: Space '550' does not exist
+...
+-- Pass integer instead of expression.
+box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', false, 'SQL', 666})
+---
+- error: 'Tuple field 5 type does not match one required by operation: expected string'
+...
+-- Deferred CK constraints are not supported.
+box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', true, 'SQL', 'X<5'})
+---
+- error: Tarantool does not support deferred ck constraints
+...
+-- The only supported language is SQL.
+box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', false, 'LUA', 'X<5'})
+---
+- error: Unsupported language 'LUA' specified for function 'CK_CONSTRAINT_01'
+...
+-- Check constraints LUA creation test.
+box.space._ck_constraint:insert({513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<5'})
+---
+- [513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<5']
+...
+box.space._ck_constraint:count({})
+---
+- 1
+...
+box.execute("INSERT INTO \"test\" VALUES(5);")
+---
+- null
+- 'Check constraint failed ''CK_CONSTRAINT_01'': X<5'
+...
+box.space.test:insert({5})
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_01'': X<5'
+...
+box.space._ck_constraint:replace({513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<=5'})
+---
+- [513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<=5']
+...
+box.execute("INSERT INTO \"test\" VALUES(5);")
+---
+- row_count: 1
+...
+box.execute("INSERT INTO \"test\" VALUES(6);")
+---
+- null
+- 'Check constraint failed ''CK_CONSTRAINT_01'': X<=5'
+...
+box.space.test:insert({6})
+---
+- error: 'Check constraint failed ''CK_CONSTRAINT_01'': X<=5'
+...
+-- Can't drop table with check constraints.
+box.space.test:delete({5})
+---
+- [5]
+...
+box.space.test.index.pk:drop()
+---
+...
+box.space._space:delete({513})
+---
+- error: 'Can''t drop space ''test'': the space has check constraints'
+...
+box.space._ck_constraint:delete({513, 'CK_CONSTRAINT_01'})
+---
+- [513, 'CK_CONSTRAINT_01', false, 'SQL', 'X<=5']
+...
+box.space._space:delete({513})
+---
+- [513, 1, 'test', 'memtx', 0, {'checks': [{'expr': 'X>5'}]}, [{'name': 'X', 'type': 'unsigned'}]]
+...
+-- Create table with checks in sql.
+box.execute("CREATE TABLE t1(x INTEGER CONSTRAINT ONE CHECK( x<5 ), y NUMBER CONSTRAINT TWO CHECK( y>x ), z INTEGER PRIMARY KEY);")
+---
+- row_count: 1
+...
+box.space._ck_constraint:count()
+---
+- 2
+...
+box.execute("INSERT INTO t1 VALUES (7, 1, 1)")
+---
+- null
+- 'Check constraint failed ''ONE'': x<5'
+...
+box.space.T1:insert({7, 1, 1})
+---
+- error: 'Check constraint failed ''ONE'': x<5'
+...
+box.execute("INSERT INTO t1 VALUES (2, 1, 1)")
+---
+- null
+- 'Check constraint failed ''TWO'': y>x'
+...
+box.space.T1:insert({2, 1, 1})
+---
+- error: 'Check constraint failed ''TWO'': y>x'
+...
+box.execute("INSERT INTO t1 VALUES (2, 4, 1)")
+---
+- row_count: 1
+...
+box.space.T1:update({1}, {{'+', 1, 5}})
+---
+- error: 'Check constraint failed ''ONE'': x<5'
+...
+box.execute("DROP TABLE t1")
+---
+- row_count: 1
+...
+-- Test space creation rollback on spell error in ck constraint.
+box.execute("CREATE TABLE first (id NUMBER PRIMARY KEY CHECK(id < 5), a INT CONSTRAINT ONE CHECK(a >< 5));")
+---
+- null
+- Syntax error near '<'
+...
+box.space.FIRST == nil
+---
+- true
+...
+box.space._ck_constraint:count() == 0
+---
+- true
+...
+-- Ck constraints are disallowed for spaces having no format.
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+_ = s:create_index('pk')
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'physics', false, 'SQL', 'X<Y'})
+---
+- error: Tarantool does not support CK constraint for space without format
+...
+s:format({{name='X', type='integer'}, {name='Y', type='integer'}})
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'physics', false, 'SQL', 'X<Y'})
+---
+...
+box.execute("INSERT INTO \"test\" VALUES(2, 1);")
+---
+- null
+- 'Check constraint failed ''physics'': X<Y'
+...
+s:format({{name='Y', type='integer'}, {name='X', type='integer'}})
+---
+...
+box.execute("INSERT INTO \"test\" VALUES(1, 2);")
+---
+- null
+- 'Check constraint failed ''physics'': X<Y'
+...
+box.execute("INSERT INTO \"test\" VALUES(2, 1);")
+---
+- row_count: 1
+...
+s:truncate()
+---
+...
+box.execute("INSERT INTO \"test\" VALUES(1, 2);")
+---
+- null
+- 'Check constraint failed ''physics'': X<Y'
+...
+s:format({})
+---
+- error: Tarantool does not support CK constraint for space without format
+...
+s:format()
+---
+- [{'name': 'Y', 'type': 'integer'}, {'name': 'X', 'type': 'integer'}]
+...
+s:format({{name='Y1', type='integer'}, {name='X1', type='integer'}})
+---
+- error: 'Failed to create check constraint ''physics'': Can’t resolve field ''X'''
+...
+-- Ck constraint creation is forbidden for non-empty space
+s:insert({2, 1})
+---
+- [2, 1]
+...
+_ = box.space._ck_constraint:insert({s.id, 'conflict', false, 'SQL', 'X>10'})
+---
+- error: 'Failed to create check constraint ''conflict'': referencing space must be
+ empty'
+...
+s:truncate()
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'conflict', false, 'SQL', 'X>10'})
+---
+...
+box.execute("INSERT INTO \"test\" VALUES(1, 2);")
+---
+- null
+- 'Check constraint failed ''conflict'': X>10'
+...
+box.execute("INSERT INTO \"test\" VALUES(11, 11);")
+---
+- null
+- 'Check constraint failed ''physics'': X<Y'
+...
+box.execute("INSERT INTO \"test\" VALUES(12, 11);")
+---
+- row_count: 1
+...
+s:drop()
+---
+...
+box.execute("CREATE TABLE T2(ID INT PRIMARY KEY, CONSTRAINT CK1 CHECK(ID > 0), CONSTRAINT CK1 CHECK(ID < 0))")
+---
+- null
+- Constraint CK1 already exists
+...
+box.space.T2
+---
+- null
+...
+box.space._ck_constraint:select()
+---
+- []
+...
+--
+-- gh-3611: Segfault on table creation with check referencing this table
+--
+box.execute("CREATE TABLE w2 (s1 INT PRIMARY KEY, CHECK ((SELECT COUNT(*) FROM w2) = 0));")
+---
+- null
+- 'Failed to create check constraint ''ck_unnamed_W2_1'': Subqueries are prohibited
+ in a ck constraint definition'
+...
+box.execute("DROP TABLE w2;")
+---
+- null
+- Space 'W2' does not exist
+...
+--
+-- gh-3653: Dissallow bindings for DDL
+--
+box.execute("CREATE TABLE t5(x INT PRIMARY KEY, y INT, CHECK( x*y < ? ));")
+---
+- null
+- 'Failed to create check constraint ''ck_unnamed_T5_1'': bindings are not allowed
+ in DDL'
+...
+-- Test trim CK constraint code correctness.
+box.execute("CREATE TABLE t1(x TEXT PRIMARY KEY CHECK(x LIKE '1 a'))")
+---
+- row_count: 1
+...
+box.space._ck_constraint:select()[1].code
+---
+- x LIKE '1 a'
+...
+box.execute("INSERT INTO t1 VALUES('1 a')")
+---
+- null
+- 'Check constraint failed ''ck_unnamed_T1_1'': x LIKE ''1 a'''
+...
+box.execute("INSERT INTO t1 VALUES('1 a')")
+---
+- null
+- 'Check constraint failed ''ck_unnamed_T1_1'': x LIKE ''1 a'''
+...
+box.execute("INSERT INTO t1 VALUES('1 a')")
+---
+- row_count: 1
+...
+box.execute("DROP TABLE t1")
+---
+- row_count: 1
+...
+--
+-- Test binding reset on new insertion
+--
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+_ = s:create_index('pk')
+---
+...
+s:format({{name='X', type='any'}, {name='Y', type='integer'}, {name='Z', type='integer', is_nullable=true}})
+---
+...
+ck_not_null = box.space._ck_constraint:insert({s.id, 'ZnotNULL', false, 'SQL', 'X = 1 AND Z IS NOT NULL'})
+---
+...
+s:insert({2, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1, box.NULL})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({2, 1, 3})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1})
+---
+- error: 'Check constraint failed ''ZnotNULL'': X = 1 AND Z IS NOT NULL'
+...
+s:insert({1, 1, 3})
+---
+- [1, 1, 3]
+...
+s:drop()
+---
+...
+--
+-- Test ck constraint corner cases
+--
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+_ = s:create_index('pk')
+---
+...
+s:format({{name='X', type='any'}, {name='Y', type='integer'}, {name='Z', type='integer', is_nullable=true}})
+---
+...
+ck_not_null = box.space._ck_constraint:insert({s.id, 'ZnotNULL', false, 'SQL', 'Z IS NOT NULL'})
+---
+...
+s:insert({1, 2, box.NULL})
+---
+- error: 'Check constraint failed ''ZnotNULL'': Z IS NOT NULL'
+...
+s:insert({1, 2})
+---
+- error: 'Check constraint failed ''ZnotNULL'': Z IS NOT NULL'
+...
+_ = box.space._ck_constraint:delete({s.id, 'ZnotNULL'})
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'XlessY', false, 'SQL', 'X < Y and Y < Z'})
+---
+...
+s:insert({'1', 2})
+---
+- error: 'Tuple field 1 type does not match one required by operation: expected unsigned'
+...
+s:insert({})
+---
+- error: Tuple field 1 required by space format is missing
+...
+s:insert({2, 1})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:insert({1, 2})
+---
+- [1, 2]
+...
+s:insert({2, 3, 1})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:insert({2, 3, 4})
+---
+- [2, 3, 4]
+...
+s:update({2}, {{'+', 2, 3}})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+---
+- [2, 6, 7]
+...
+s:replace({2, 1, 3})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+box.snapshot()
+---
+- ok
+...
+s = box.space["test"]
+---
+...
+s:update({2}, {{'+', 2, 3}})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:update({2}, {{'+', 2, 3}, {'+', 3, 3}})
+---
+- [2, 9, 10]
+...
+s:replace({2, 1, 3})
+---
+- error: 'Check constraint failed ''XlessY'': X < Y and Y < Z'
+...
+s:drop()
+---
+...
+--
+-- Test complex CHECK constraints.
+--
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+s:format({{name='X', type='integer'}, {name='Y', type='integer'}, {name='Z', type='integer'}})
+---
+...
+_ = s:create_index('pk', {parts = {3, 'integer'}})
+---
+...
+_ = s:create_index('unique', {parts = {1, 'integer'}})
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'complex1', false, 'SQL', 'x+y==11 OR x*y==12 OR x/y BETWEEN 5 AND 8 OR -x == y+10'})
+---
+...
+s:insert({1, 10, 1})
+---
+- [1, 10, 1]
+...
+s:update({1}, {{'=', 1, 4}, {'=', 2, 3}})
+---
+- [4, 3, 1]
+...
+s:update({1}, {{'=', 1, 12}, {'=', 2, 2}})
+---
+- [12, 2, 1]
+...
+s:update({1}, {{'=', 1, 12}, {'=', 2, -22}})
+---
+- [12, -22, 1]
+...
+s:update({1}, {{'=', 1, 0}, {'=', 2, 1}})
+---
+- error: 'Check constraint failed ''complex1'': x+y==11 OR x*y==12 OR x/y BETWEEN
+ 5 AND 8 OR -x == y+10'
+...
+s:get({1})
+---
+- [12, -22, 1]
+...
+s:update({1}, {{'=', 1, 0}, {'=', 2, 2}})
+---
+- error: 'Check constraint failed ''complex1'': x+y==11 OR x*y==12 OR x/y BETWEEN
+ 5 AND 8 OR -x == y+10'
+...
+s:get({1})
+---
+- [12, -22, 1]
+...
+s:drop()
+---
+...
+s = box.schema.create_space('test', {engine = engine})
+---
+...
+s:format({{name='X', type='integer'}, {name='Z', type='any'}})
+---
+...
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'complex2', false, 'SQL', 'typeof(coalesce(z,0))==\'integer\''})
+---
+...
+s:insert({1, 'string'})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, {map=true}})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, {'a', 'r','r','a','y'}})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, 3.14})
+---
+- error: 'Check constraint failed ''complex2'': typeof(coalesce(z,0))==''integer'''
+...
+s:insert({1, 666})
+---
+- [1, 666]
+...
+s:drop()
+---
+...
+--
+-- Test large tuple.
+--
+s = box.schema.create_space('test')
+---
+...
+_ = s:create_index('pk', {parts = {1, 'integer'}})
+---
+...
+format65 = {}
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+for i = 1,66 do
+ table.insert(format65, {name='X'..i, type='integer', is_nullable = true})
+end
+test_run:cmd("setopt delimiter ''");
+---
+...
+s:format(format65)
+---
+...
+_ = box.space._ck_constraint:insert({s.id, 'X1is666andX65is666', false, 'SQL', 'X1 == 666 and X65 == 666 and X63 IS NOT NULL'})
+---
+...
+s:insert(s:frommap({X1 = 1, X65 = 1}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 1}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 1, X65 = 666}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 666}))
+---
+- error: 'Check constraint failed ''X1is666andX65is666'': X1 == 666 and X65 == 666
+ and X63 IS NOT NULL'
+...
+s:insert(s:frommap({X1 = 666, X65 = 666, X63 = 1}))
+---
+- [666, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, null, null, null, null, null, null, null, null,
+ null, null, null, null, null, null, 1, null, 666]
+...
+s:drop()
+---
+...
+--
+-- Test ck constraints LUA integration.
+--
+s1 = box.schema.create_space('test1')
+---
+...
+_ = s1:create_index('pk')
+---
+...
+s1:format({{name='X', type='any'}, {name='Y', type='integer'}})
+---
+...
+s2 = box.schema.create_space('test2')
+---
+...
+_ = s2:create_index('pk')
+---
+...
+s2:format({{name='X', type='any'}, {name='Y', type='integer'}})
+---
+...
+test_run:cmd("push filter 'space_id: [0-9]+' to 'space_id: <ID>'")
+---
+- true
+...
+_ = s1:create_check_constraint('physics', 'X < Y')
+---
+...
+_ = s1:create_check_constraint('physics', 'X > Y')
+---
+- error: Duplicate key exists in unique index 'primary' in space '_ck_constraint'
+...
+_ = s1:create_check_constraint('greater', 'X > 20')
+---
+...
+_ = s2:create_check_constraint('physics', 'X > Y')
+---
+...
+_ = s2:create_check_constraint('greater', 'X > 20')
+---
+...
+s1.ck_constraint.physics
+---
+- space_id: <ID>
+ is_enabled: true
+ name: physics
+ expr: X < Y
+...
+s1.ck_constraint.greater
+---
+- space_id: <ID>
+ is_enabled: true
+ name: greater
+ expr: X > 20
+...
+s2.ck_constraint.physics
+---
+- space_id: <ID>
+ is_enabled: true
+ name: physics
+ expr: X > Y
+...
+s2.ck_constraint.greater
+---
+- space_id: <ID>
+ is_enabled: true
+ name: greater
+ expr: X > 20
+...
+s1:insert({2, 1})
+---
+- error: 'Check constraint failed ''greater'': X > 20'
+...
+s1:insert({21, 20})
+---
+- error: 'Check constraint failed ''physics'': X < Y'
+...
+s2:insert({1, 2})
+---
+- error: 'Check constraint failed ''greater'': X > 20'
+...
+s2:insert({21, 22})
+---
+- error: 'Check constraint failed ''physics'': X > Y'
+...
+s2.ck_constraint.greater:drop()
+---
+...
+s2.ck_constraint.physics
+---
+- space_id: <ID>
+ is_enabled: true
+ name: physics
+ expr: X > Y
+...
+s2.ck_constraint.greater
+---
+- null
+...
+s1:insert({2, 1})
+---
+- error: 'Check constraint failed ''greater'': X > 20'
+...
+s2:insert({1, 2})
+---
+- error: 'Check constraint failed ''physics'': X > Y'
+...
+s2:insert({2, 1})
+---
+- [2, 1]
+...
+physics_ck = s2.ck_constraint.physics
+---
+...
+s1:drop()
+---
+...
+s2:drop()
+---
+...
+physics_ck
+---
+- space_id: <ID>
+ is_enabled: true
+ name: physics
+ expr: X > Y
+...
+physics_ck:drop()
+---
+...
+--
+-- gh-4244: An ability to disable CK constraints
+-- Make shure that ck constraints are turning on and of with
+-- :enable configurator.
+--
+engine = test_run:get_cfg('engine')
+---
+...
+box.execute('pragma sql_default_engine=\''..engine..'\'')
+---
+- row_count: 0
+...
+box.execute("CREATE TABLE test(a INT CHECK (a < 5) primary key);");
+---
+- row_count: 1
+...
+box.space.TEST:insert({10})
+---
+- error: 'Check constraint failed ''ck_unnamed_TEST_1'': a < 5'
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1:enable(false)
+---
+...
+box.space.TEST:insert({11})
+---
+- [11]
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1:enable(true)
+---
+...
+box.space.TEST:insert({12})
+---
+- error: 'Check constraint failed ''ck_unnamed_TEST_1'': a < 5'
+...
+box.execute("DROP TABLE test;")
+---
+- row_count: 1
+...
+--
+-- Test ENABLE/DISABLE CK constraints from SQL works.
+--
+box.execute("ALTER TABLE falsch DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- null
+- Space 'FALSCH' does not exist
+...
+box.execute("CREATE TABLE test(a INT CHECK (a < 10) primary key);");
+---
+- row_count: 1
+...
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"falsch\"")
+---
+- null
+- Constraint falsch does not exist
+...
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- row_count: 0
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == false
+---
+- true
+...
+box.space.TEST:insert({11})
+---
+- [11]
+...
+box.execute("ALTER TABLE test ENABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- row_count: 0
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == true
+---
+- true
+...
+box.space.TEST:insert({12})
+---
+- error: 'Check constraint failed ''ck_unnamed_TEST_1'': a < 10'
+...
+box.execute("DROP TABLE test;")
+---
+- row_count: 1
+...
+test_run:cmd("clear filter")
+---
+- true
+...
diff --git a/test/sql/checks.result b/test/sql/checks.result
index 0ca49203c..e766f88b8 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -762,6 +762,55 @@ box.space.TEST:insert({12})
---
- error: 'Check constraint failed ''ck_unnamed_TEST_1'': a < 5'
...
+box.execute("DROP TABLE test;")
+---
+- row_count: 1
+...
+--
+-- Test ENABLE/DISABLE CK constraints from SQL works.
+--
+box.execute("ALTER TABLE falsch DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- null
+- Space 'FALSCH' does not exist
+...
+box.execute("CREATE TABLE test(a INT CHECK (a < 10) primary key);");
+---
+- row_count: 1
+...
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"falsch\"")
+---
+- null
+- Constraint falsch does not exist
+...
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- row_count: 0
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == false
+---
+- true
+...
+box.space.TEST:insert({11})
+---
+- [11]
+...
+box.execute("ALTER TABLE test ENABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+---
+- row_count: 0
+...
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == true
+---
+- true
+...
+box.space.TEST:insert({12})
+---
+- error: 'Check constraint failed ''ck_unnamed_TEST_1'': a < 10'
+...
+box.execute("DROP TABLE test;")
+---
+- row_count: 1
+...
test_run:cmd("clear filter")
---
- true
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index db5d0b05f..442c50eaa 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -247,5 +247,20 @@ box.space.TEST.ck_constraint.ck_unnamed_TEST_1:enable(false)
box.space.TEST:insert({11})
box.space.TEST.ck_constraint.ck_unnamed_TEST_1:enable(true)
box.space.TEST:insert({12})
+box.execute("DROP TABLE test;")
+
+--
+-- Test ENABLE/DISABLE CK constraints from SQL works.
+--
+box.execute("ALTER TABLE falsch DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+box.execute("CREATE TABLE test(a INT CHECK (a < 10) primary key);");
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"falsch\"")
+box.execute("ALTER TABLE test DISABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == false
+box.space.TEST:insert({11})
+box.execute("ALTER TABLE test ENABLE CONSTRAINT \"ck_unnamed_TEST_1\"")
+box.space.TEST.ck_constraint.ck_unnamed_TEST_1.is_enabled == true
+box.space.TEST:insert({12})
+box.execute("DROP TABLE test;")
test_run:cmd("clear filter")
--
2.23.0
More information about the Tarantool-patches
mailing list