From: Kirill Shcherbatov <kshcherbatov@tarantool.org> To: tarantool-patches@freelists.org, Vladislav Shpilevoy <v.shpilevoy@tarantool.org> Subject: [tarantool-patches] Re: [PATCH v5 6/6] box: user-friendly interface to manage ck constraints Date: Fri, 31 May 2019 16:45:26 +0300 [thread overview] Message-ID: <895e9944-bd3a-cbc0-0c52-ae2e9f1d3837@tarantool.org> (raw) In-Reply-To: <a9c26aff-d274-8a03-1e54-124f1a4407b8@tarantool.org> > 1. I think, 'expr' is better name. What else except 'str' you > can expose? - nothing. It means, that '_str' suffix is > redundant, IMO. Ok. Done. ================================== Closes #3691 @TarantoolBot document Title: check constraint for Lua space The check constraint is a type of integrity constraint which specifies a requirement that must be met by tuple before it is inserted into space. The constraint result must be predictable. Expression in check constraint must be <boolean value expression> I.e. return boolean result. Now it is possible to create ck constraints only for empty space having format. Constraint expression is a string that defines relations between top-level tuple fields. Take into account that all names are converted to an uppercase before resolve(like SQL does), use \" sign for names of fields that were created not with SQL. The check constraints are fired on insertion to the Lua space together with Lua space triggers. The execution order of ck constraints checks and space triggers follows their creation sequence. Note: this patch changes the CK constraints execution order for SQL. Previously check of CK constraints integrity was fired before tuple is formed; meanwhile now they are implemented as NoSQL before replace triggers, which are fired right before tuple insertion. In turn, type casts are performed earlier than msgpack serialization. You should be careful with functions that use field types in your check constrains (like typeof()). Consider following situation: ``` box.execute("CREATE TABLE t2(id INT primary key, x INTEGER CHECK (x > 1));") box.execute("INSERT INTO t2 VALUES(3, 1.1)") ``` the last operation would fail because 1.1 is silently cast to integer 1 which is not greater than 1. To create a new CK constraint for a space, use space:create_check_constraint method. All space constraints are shown in space.ck_constraint table. To drop ck constraint, use :drop method. Example: ``` s1 = box.schema.create_space('test1') pk = s1:create_index('pk') ck = s1:create_check_constraint('physics', 'X < Y') s1:insert({2, 1}) -- fail ck:drop() ``` --- src/box/alter.cc | 2 + src/box/lua/schema.lua | 31 +++++++++- src/box/lua/space.cc | 63 ++++++++++++++++++++ test/sql/checks.result | 124 +++++++++++++++++++++++++++++++++++++++ test/sql/checks.test.lua | 35 +++++++++++ 5 files changed, 254 insertions(+), 1 deletion(-) diff --git a/src/box/alter.cc b/src/box/alter.cc index 7a6975427..3e8282255 100644 --- a/src/box/alter.cc +++ b/src/box/alter.cc @@ -4304,6 +4304,8 @@ on_replace_ck_constraint_commit(struct trigger *trigger, void *event) assert(stmt->new_tuple != NULL); ck_constraint_delete(ck); } + /* Export schema changes to Lua. */ + trigger_run_xc(&on_alter_space, space); } /** A trigger invoked on replace in the _ck_constraint space. */ diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua index 91fae8378..3f080eced 100644 --- a/src/box/lua/schema.lua +++ b/src/box/lua/schema.lua @@ -1309,6 +1309,15 @@ local function check_primary_index(space) end box.internal.check_primary_index = check_primary_index -- for net.box +-- Helper function to check ck_constraint:method() usage +local function check_ck_constraint_arg(ck_constraint, method) + if type(ck_constraint) ~= 'table' or ck_constraint.name == nil then + local fmt = 'Use ck_constraint:%s(...) instead of ck_constraint.%s(...)' + error(string.format(fmt, method, method)) + end +end +box.internal.check_ck_constraint_arg = check_ck_constraint_arg + box.internal.schema_version = builtin.box_schema_version local function check_iterator_type(opts, key_is_nil) @@ -1657,7 +1666,16 @@ space_mt.auto_increment = function(space, tuple) table.insert(tuple, 1, max + 1) return space:insert(tuple) end - +-- Manage space ck constraints +space_mt.create_check_constraint = function(space, name, code) + check_space_arg(space, 'create_constraint') + if name == nil or code == nil then + box.error(box.error.PROC_LUA, + "Usage: space:create_constraint(name, code)") + end + box.space._ck_constraint:insert({space.id, name, false, 'SQL', code}) + return space.ck_constraint[name] +end space_mt.pairs = function(space, key, opts) check_space_arg(space, 'pairs') local pk = space.index[0] @@ -1703,6 +1721,12 @@ end space_mt.frommap = box.internal.space.frommap space_mt.__index = space_mt +local ck_constraint_mt = {} +ck_constraint_mt.drop = function(ck_constraint) + check_ck_constraint_arg(ck_constraint, 'drop') + box.space._ck_constraint:delete({ck_constraint.space_id, ck_constraint.name}) +end + box.schema.index_mt = base_index_mt box.schema.memtx_index_mt = memtx_index_mt box.schema.vinyl_index_mt = vinyl_index_mt @@ -1751,6 +1775,11 @@ function box.schema.space.bless(space) setmetatable(index, wrap_schema_object_mt(index_mt_name)) end end + for j, ck_constraint in pairs(space.ck_constraint) do + if type(j) == 'string' then + setmetatable(ck_constraint, {__index = ck_constraint_mt}) + end + end end end diff --git a/src/box/lua/space.cc b/src/box/lua/space.cc index 509306eae..b2c5937bc 100644 --- a/src/box/lua/space.cc +++ b/src/box/lua/space.cc @@ -28,6 +28,7 @@ * THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF * SUCH DAMAGE. */ +#include "box/ck_constraint.h" #include "box/lua/space.h" #include "box/lua/tuple.h" #include "box/lua/key_def.h" @@ -147,6 +148,66 @@ lbox_space_before_replace(struct lua_State *L) lbox_push_txn_stmt, lbox_pop_txn_stmt); } +/** + * Make ck_constraints available in Lua, via ck_constraint[] + * array for space table by given index i. + * Updata a ck_constraint table in the parent space table object + * on the Lua stack. + */ +static void +lbox_push_ck_constraint(struct lua_State *L, struct space *space, int i) +{ + lua_getfield(L, i, "ck_constraint"); + if (lua_isnil(L, -1)) { + lua_pop(L, 1); + lua_pushstring(L, "ck_constraint"); + lua_newtable(L); + lua_settable(L, i); + lua_getfield(L, i, "ck_constraint"); + } else { + lua_pushnil(L); + while (lua_next(L, -2) != 0) { + size_t name_len; + const char *name = lua_tolstring(L, -2, &name_len); + /* + * Remove ck_constraint only if it was + * deleted. + */ + if (space_ck_constraint_by_name(space, name, + (uint32_t)name_len) == NULL) { + lua_pushlstring(L, name, name_len); + lua_pushnil(L); + lua_settable(L, -5); + } + lua_pop(L, 1); + } + } + struct ck_constraint *ck_constraint = NULL; + rlist_foreach_entry(ck_constraint, &space->ck_constraint, link) { + lua_getfield(L, i, ck_constraint->def->name); + if (lua_isnil(L, -1)) { + lua_pop(L, 1); + lua_pushstring(L, ck_constraint->def->name); + lua_newtable(L); + lua_settable(L, -3); + lua_getfield(L, -1, ck_constraint->def->name); + assert(!lua_isnil(L, -1)); + } + + lua_pushstring(L, ck_constraint->def->name); + lua_setfield(L, -2, "name"); + + lua_pushnumber(L, space->def->id); + lua_setfield(L, -2, "space_id"); + + lua_pushstring(L, ck_constraint->def->expr_str); + lua_setfield(L, -2, "expr"); + + lua_setfield(L, -2, ck_constraint->def->name); + } + lua_pop(L, 1); +} + /** * Make a single space available in Lua, * via box.space[] array. @@ -352,6 +413,8 @@ lbox_fillspace(struct lua_State *L, struct space *space, int i) lua_pop(L, 1); /* pop the index field */ + lbox_push_ck_constraint(L, space, i); + lua_getfield(L, LUA_GLOBALSINDEX, "box"); lua_pushstring(L, "schema"); lua_gettable(L, -2); diff --git a/test/sql/checks.result b/test/sql/checks.result index 6272cea7a..ce28637c6 100644 --- a/test/sql/checks.result +++ b/test/sql/checks.result @@ -585,6 +585,130 @@ s:insert(s:frommap({X1 = 666, X65 = 666, X63 = 1})) 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> + name: physics + expr: X < Y +... +s1.ck_constraint.greater +--- +- space_id: <ID> + name: greater + expr: X > 20 +... +s2.ck_constraint.physics +--- +- space_id: <ID> + name: physics + expr: X > Y +... +s2.ck_constraint.greater +--- +- space_id: <ID> + 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> + 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 +--- +... +s1:drop() +--- +... +s2:drop() +--- +... +physics_ck +--- +- [] +... +physics_ck:drop() +--- +- error: '[string "return physics_ck:drop() "]:1: attempt to call method ''drop'' + (a nil value)' +... test_run:cmd("clear filter") --- - true diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua index dadedb935..c8efcc7cb 100644 --- a/test/sql/checks.test.lua +++ b/test/sql/checks.test.lua @@ -199,4 +199,39 @@ s:insert(s:frommap({X1 = 666, X65 = 666})) s:insert(s:frommap({X1 = 666, X65 = 666, X63 = 1})) 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>'") +_ = s1:create_check_constraint('physics', 'X < Y') +_ = s1:create_check_constraint('physics', 'X > Y') +_ = 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 +s1.ck_constraint.greater +s2.ck_constraint.physics +s2.ck_constraint.greater +s1:insert({2, 1}) +s1:insert({21, 20}) +s2:insert({1, 2}) +s2:insert({21, 22}) +s2.ck_constraint.greater:drop() +s2.ck_constraint.physics +s2.ck_constraint.greater +s1:insert({2, 1}) +s2:insert({1, 2}) +s2:insert({2, 1}) +physics_ck = s2.ck_constraint +s1:drop() +s2:drop() +physics_ck +physics_ck:drop() + test_run:cmd("clear filter") -- 2.21.0
next prev parent reply other threads:[~2019-05-31 13:45 UTC|newest] Thread overview: 31+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-05-23 10:19 [tarantool-patches] [PATCH v5 0/6] box: run checks on insertions in LUA spaces Kirill Shcherbatov 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 1/6] sql: introduce a new method to bind a pointer Kirill Shcherbatov 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 2/6] sql: refactor OP_Column vdbe instruction Kirill Shcherbatov 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 3/6] sql: introduce tuple_fetcher class Kirill Shcherbatov 2019-05-26 12:05 ` [tarantool-patches] " Vladislav Shpilevoy 2019-05-31 13:45 ` Kirill Shcherbatov 2019-05-31 19:45 ` Konstantin Osipov 2019-05-31 19:50 ` Kirill Shcherbatov 2019-05-31 22:36 ` Vladislav Shpilevoy 2019-06-01 5:45 ` Konstantin Osipov 2019-06-02 18:50 ` Kirill Shcherbatov 2019-06-03 21:15 ` Vladislav Shpilevoy 2019-06-05 6:47 ` Konstantin Osipov 2019-06-05 6:48 ` Konstantin Osipov 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 4/6] schema: add new system space for CHECK constraints Kirill Shcherbatov 2019-05-26 12:06 ` [tarantool-patches] " Vladislav Shpilevoy 2019-05-26 13:31 ` n.pettik 2019-05-26 13:32 ` Vladislav Shpilevoy 2019-05-31 13:45 ` Kirill Shcherbatov 2019-06-03 21:15 ` Vladislav Shpilevoy 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 5/6] box: run check constraint tests on space alter Kirill Shcherbatov 2019-05-26 12:07 ` [tarantool-patches] " Vladislav Shpilevoy 2019-05-31 13:45 ` Kirill Shcherbatov 2019-06-03 21:15 ` Vladislav Shpilevoy 2019-05-23 10:19 ` [tarantool-patches] [PATCH v5 6/6] box: user-friendly interface to manage ck constraints Kirill Shcherbatov 2019-05-26 12:07 ` [tarantool-patches] " Vladislav Shpilevoy 2019-05-31 13:45 ` Kirill Shcherbatov [this message] 2019-06-03 21:15 ` [tarantool-patches] Re: [PATCH v5 0/6] box: run checks on insertions in LUA spaces Vladislav Shpilevoy 2019-06-04 7:21 ` Kirill Shcherbatov 2019-06-04 18:59 ` Vladislav Shpilevoy 2019-06-06 11:58 ` 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=895e9944-bd3a-cbc0-0c52-ae2e9f1d3837@tarantool.org \ --to=kshcherbatov@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='[tarantool-patches] Re: [PATCH v5 6/6] box: user-friendly interface to manage ck constraints' \ /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