From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8FA5F2A217 for ; Tue, 16 Apr 2019 09:51:47 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id AGuHcU0A9Wct for ; Tue, 16 Apr 2019 09:51:47 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 31EC329F39 for ; Tue, 16 Apr 2019 09:51:47 -0400 (EDT) From: Kirill Shcherbatov Subject: [tarantool-patches] [PATCH v3 3/3] box: user-friendly interface to manage ck constraints Date: Tue, 16 Apr 2019 16:51:40 +0300 Message-Id: <0511eb70cd35f5a3b8fc527e5e08814f3188eb15.1555420166.git.kshcherbatov@tarantool.org> In-Reply-To: References: MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org, korablev@tarantool.org Cc: Kirill Shcherbatov @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. 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. To create a new CK constraint for a space, use s = box.schema.create_space('person') _ = s:create_index('pk', {parts = {1, 'string'}}) s:format({{name='name', type='string'}, {name='age', type='integer'}, {name='experience', type='integer'}}) s:ck_constraint({'physics', '\"age\" > 14 and \"experience\" < \"age\"'}) s:insert({"James Bond", 36, 36}) --- - error: 'Check constraint failed ''physics'': "age" > 14 and "experience" < "age"' ... s:insert("James Bond", 36, 16) -- success s:insert({"Bobby", 6, 0}) --- - error: 'Check constraint failed ''physics'': "age" > 14 and "experience" < "age"' ... To list all ck constraints assosiated with space, s:ck_constraint() To replace ck constraint, use following syntax: s:ck_constraint({'physics', '\"experience\" < \"age\"'}, {'physics'}) s:insert({"Bobby", 6, 0}) -- success To drop ck constraint, use: s:ck_constraint(nil, {'physics', '\"experience\" < \"age\"'}) --- src/box/lua/schema.lua | 19 ++++++++++ test/sql/checks.result | 78 ++++++++++++++++++++++++++++++++++++++++ test/sql/checks.test.lua | 29 +++++++++++++++ 3 files changed, 126 insertions(+) diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua index e01f500e6..69aebc01c 100644 --- a/src/box/lua/schema.lua +++ b/src/box/lua/schema.lua @@ -1533,6 +1533,25 @@ space_mt.auto_increment = function(space, tuple) table.insert(tuple, 1, max + 1) return space:insert(tuple) end +-- Manage space ck constraints +space_mt.ck_constraint = function(space, new, old) + check_space_arg(space, 'ck_constraint') + if new == nil and old == nil then + return box.space._ck_constraint.index['space_id']:select({space.id}) + end + if new == nil then + box.space._ck_constraint:delete({old[1], space.id}) + else + local msg = "Usage: space:ck_constraint({name, expr_str} | nil, " .. + "[{name, expr_str} | nil])" + if old ~= nil and old[1] ~= new[1] then + box.error(box.error.PROC_LUA, + "Error: old and new CK constraint names must " .. + "coincide. " .. msg) + end + return box.space._ck_constraint:replace({new[1], space.id, new[2]}) + end +end space_mt.pairs = function(space, key, opts) check_space_arg(space, 'pairs') diff --git a/test/sql/checks.result b/test/sql/checks.result index 0dd5d820f..301140988 100644 --- a/test/sql/checks.result +++ b/test/sql/checks.result @@ -395,3 +395,81 @@ s:replace({2, 1, 3}) s:drop() --- ... +-- Test ck constraints user-friendly creation interface +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'}}) +--- +... +s1:ck_constraint({'physics', 'X < Y'}) +--- +- ['physics', 520, 'X < Y'] +... +s1:ck_constraint({'greater', 'X > 10'}) +--- +- ['greater', 520, 'X > 10'] +... +s2:ck_constraint({'physics', 'X > Y'}) +--- +- ['physics', 521, 'X > Y'] +... +s1:ck_constraint() +--- +- - ['greater', 520, 'X > 10'] + - ['physics', 520, 'X < Y'] +... +s2:ck_constraint() +--- +- - ['physics', 521, 'X > Y'] +... +s1:ck_constraint({'greater', 'X > 20'}, {'greater'}) +--- +- ['greater', 520, 'X > 20'] +... +s2:ck_constraint({'greater', 'X > 20'}, {'greater'}) +--- +- ['greater', 521, 'X > 20'] +... +s1:ck_constraint() +--- +- - ['greater', 520, 'X > 20'] + - ['physics', 520, 'X < Y'] +... +s2:ck_constraint() +--- +- - ['greater', 521, 'X > 20'] + - ['physics', 521, 'X > Y'] +... +s1:ck_constraint(nil, {'greater'}) +--- +... +s2:ck_constraint(nil, {'greater'}) +--- +... +s1:ck_constraint() +--- +- - ['physics', 520, 'X < Y'] +... +s2:ck_constraint() +--- +- - ['physics', 521, 'X > Y'] +... +s1:drop() +--- +... +s2:drop() +--- +... diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua index 2652f3b7d..c2c5275e5 100644 --- a/test/sql/checks.test.lua +++ b/test/sql/checks.test.lua @@ -134,3 +134,32 @@ s:update({2}, {{'+', 2, 3}}) s:update({2}, {{'+', 2, 3}, {'+', 3, 3}}) s:replace({2, 1, 3}) s:drop() + +-- Test ck constraints user-friendly creation interface +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'}}) +s1:ck_constraint({'physics', 'X < Y'}) +s1:ck_constraint({'greater', 'X > 10'}) +s2:ck_constraint({'physics', 'X > Y'}) +s1:ck_constraint() +s2:ck_constraint() +s1:ck_constraint({'greater', 'X > 20'}, {'greater'}) +s2:ck_constraint({'greater', 'X > 20'}, {'greater'}) +s1:ck_constraint() +s2:ck_constraint() +s1:insert({2, 1}) +s1:insert({21, 20}) +s2:insert({1, 2}) +s2:insert({21, 22}) +s1:ck_constraint(nil, {'greater'}) +s2:ck_constraint(nil, {'greater'}) +s1:ck_constraint() +s2:ck_constraint() +s1:insert({2, 1}) +s2:insert({1, 2}) +s1:drop() +s2:drop() -- 2.21.0