[tarantool-patches] [PATCH v5 6/6] box: user-friendly interface to manage ck constraints
Kirill Shcherbatov
kshcherbatov at tarantool.org
Thu May 23 13:19:39 MSK 2019
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 | 15 +++++++
src/box/lua/schema.lua | 31 ++++++++++++-
src/box/lua/space.cc | 63 +++++++++++++++++++++++++++
test/sql/checks.result | 94 ++++++++++++++++++++++++++++++++++++++++
test/sql/checks.test.lua | 30 +++++++++++++
5 files changed, 232 insertions(+), 1 deletion(-)
diff --git a/src/box/alter.cc b/src/box/alter.cc
index e4ded1995..f7da02cd0 100644
--- a/src/box/alter.cc
+++ b/src/box/alter.cc
@@ -4256,6 +4256,21 @@ on_replace_ck_constraint_commit(struct trigger *trigger, void *event)
{
struct txn_stmt *stmt = txn_last_stmt((struct txn *) event);
struct ck_constraint *ck = (struct ck_constraint *)trigger->data;
+
+ /*
+ * Configure CK constraint trigger to use it only when
+ * some ck constraint is defined.
+ */
+ struct space *space = space_by_id(ck->space_id);
+ assert(space != NULL);
+ if (rlist_empty(&space->ck_constraint))
+ trigger_clear(&space->ck_constraint_trigger);
+ else if (rlist_empty(&space->ck_constraint_trigger.link))
+ trigger_add(&space->on_replace, &space->ck_constraint_trigger);
+
+ /* Export schema changes to Lua. */
+ trigger_run_xc(&on_alter_space, space);
+
if (stmt->old_tuple != NULL)
ck_constraint_delete(ck);
}
diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua
index f2d76d0af..5504bc666 100644
--- a/src/box/lua/schema.lua
+++ b/src/box/lua/schema.lua
@@ -1219,6 +1219,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)
@@ -1567,7 +1576,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]
@@ -1613,6 +1631,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
@@ -1661,6 +1685,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 3e8f3b2e5..b375716c6 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.
+ * Updata a ck_constraint table in the parent space table object
+ * on the Lua stack.
+ */
+static void
+lbox_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_str");
+
+ lua_setfield(L, -2, ck_constraint->def->name);
+ }
+ lua_pop(L, 1);
+}
+
/**
* Make a single space available in Lua,
* via box.space[] array.
@@ -344,6 +405,8 @@ lbox_fillspace(struct lua_State *L, struct space *space, int i)
lua_pop(L, 1); /* pop the index field */
+ lbox_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 3973d242f..e6b526a25 100644
--- a/test/sql/checks.result
+++ b/test/sql/checks.result
@@ -582,6 +582,100 @@ 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'}})
+---
+...
+_ = 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 ~= nil
+---
+- true
+...
+s1.ck_constraint.greater ~= nil
+---
+- true
+...
+s2.ck_constraint.physics ~= nil
+---
+- true
+...
+s2.ck_constraint.greater ~= nil
+---
+- true
+...
+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 ~= nil
+---
+- true
+...
+s2.ck_constraint.greater == nil
+---
+- true
+...
+s1:insert({2, 1})
+---
+- error: 'Check constraint failed ''greater'': X > 20'
+...
+s2:insert({1, 2})
+---
+- error: 'Check constraint failed ''physics'': X > Y'
+...
+s1:drop()
+---
+...
+s2:drop()
+---
+...
test_run:cmd("clear filter")
---
- true
diff --git a/test/sql/checks.test.lua b/test/sql/checks.test.lua
index c00654f99..f06b9e647 100644
--- a/test/sql/checks.test.lua
+++ b/test/sql/checks.test.lua
@@ -199,4 +199,34 @@ 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'}})
+_ = 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 ~= nil
+s1.ck_constraint.greater ~= nil
+s2.ck_constraint.physics ~= nil
+s2.ck_constraint.greater ~= nil
+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 ~= nil
+s2.ck_constraint.greater == nil
+s1:insert({2, 1})
+s2:insert({1, 2})
+s1:drop()
+s2:drop()
+
test_run:cmd("clear filter")
--
2.21.0
More information about the Tarantool-patches
mailing list