[tarantool-patches] [PATCH v3 3/3] box: user-friendly interface to manage ck constraints

[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