[tarantool-patches] [PATCH v4 4/4] sql: support user-defined functions in SQL
Kirill Shcherbatov
kshcherbatov at tarantool.org
Wed Aug 21 18:28:09 MSK 2019
Closes #2200
Closes #4113
Closes #2233
@TarantoolBot document
Title: The box.internal.sql_function_create is forbidden
Legacy mechanism box.internal.sql_function_create to make some
Lua function available in SQL is forbidden now.
To make some function available in SQL you need to use
box.schema.func.create() mechanism: you need to specify
1) function language and language-specific options(e.g. you
are able to define a persistent Lua function)
2) whether this function is_deterministic or not: deterministic
functions allows to generate more efficient SQL VDBE bytecode
so you better specify it when it is true
3) the function returns type: a Tarantool type string describes
a type of value returned by function
4) param_list - a table of Tarantool's types strings desccribe
function argument types
5) exports - a table of Tarantool's frontends where this function
should be available ('LUA' by default). You need to specify
{'LUA', 'SQL'} to make function available both in SQL requests
and visible in box.func folder
Example:
-- Case1: C function
-- function1.so has int divide() symbol
box.schema.func.create("function1.divide", {language = 'C',
returns = 'number',
param_list = {'number', 'number'},
is_deterministic = true,
exports = {'LUA', 'SQL'}})
box.execute('SELECT "function1.divide"(6, 3)')
- metadata:
- name: '"function1.divide"(6, 3)'
type: number
rows:
- [2]
box.schema.func.drop("function1.divide")
-- Case2: Persistent Lua function
box.schema.func.create("SUMMARIZE", {language = 'LUA',
returns = 'number',
body = 'function (a, b) return a + b end',
param_list = {'number', 'number'},
is_deterministic = true,
exports = {'LUA', 'SQL'}})
box.execute('SELECT summarize(1, 2)')
- metadata:
- name: summarize(1, 2)
type: number
rows:
- [3]
box.schema.func.drop("summarize")
Moreover there is a special predefined Lua function LUA that
allows to evaluate a custom Lua expressions in SQL.
You need to pass a string in form "return ...." to LUA function
that returns more than one value of any type.
Example:
box.execute('SELECT lua(\'return 1 + 1\')')
- metadata:
- name: lua('return 1 + 1')
type: any
rows:
- [2]
box.execute('SELECT lua(\'return box.cfg.memtx_memory\')')
- metadata:
- name: lua('return box.cfg.memtx_memory')
type: any
rows:
- [268435456]
---
src/box/port.h | 17 ++
src/lib/core/port.h | 15 ++
src/box/call.c | 1 +
src/box/execute.c | 1 +
src/box/lua/call.c | 4 +
src/box/port.c | 4 +
src/box/sql/expr.c | 6 +-
src/box/sql/func.c | 278 ++++++++++++++++++++++++++++++++
src/box/sql/vdbe.c | 43 +++++
src/box/lua/schema.lua | 3 +-
test/box/function1.result | 176 ++++++++++++++++++++
test/box/function1.test.lua | 63 ++++++++
test/sql-tap/alias.test.lua | 11 +-
test/sql-tap/check.test.lua | 11 +-
test/sql-tap/func5.test.lua | 27 +++-
test/sql-tap/lua_sql.test.lua | 121 +++++++-------
test/sql-tap/subquery.test.lua | 21 ++-
test/sql-tap/trigger9.test.lua | 8 +-
test/sql/errinj.result | 26 ---
test/sql/errinj.test.lua | 10 --
test/sql/func-recreate.result | 41 ++++-
test/sql/func-recreate.test.lua | 26 ++-
22 files changed, 783 insertions(+), 130 deletions(-)
diff --git a/src/box/port.h b/src/box/port.h
index a7f5d81bd..9d3d02b3c 100644
--- a/src/box/port.h
+++ b/src/box/port.h
@@ -113,6 +113,23 @@ static_assert(sizeof(struct port_lua) <= sizeof(struct port),
void
port_lua_create(struct port *port, struct lua_State *L);
+struct sql_value;
+
+/** Port implementation used with vdbe memory variables. */
+struct port_vdbemem {
+ const struct port_vtab *vtab;
+ struct sql_value *mem;
+ uint32_t mem_count;
+};
+
+static_assert(sizeof(struct port_vdbemem) <= sizeof(struct port),
+ "sizeof(struct port_vdbemem) must be <= sizeof(struct port)");
+
+/** Initialize a port to dump data in sql vdbe memory. */
+void
+port_vdbemem_create(struct port *base, struct sql_value *mem,
+ uint32_t mem_count);
+
void
port_init(void);
diff --git a/src/lib/core/port.h b/src/lib/core/port.h
index 09a026df5..d61342287 100644
--- a/src/lib/core/port.h
+++ b/src/lib/core/port.h
@@ -98,6 +98,15 @@ struct port_vtab {
* is responsible for cleaning up.
**/
const char *(*get_msgpack)(struct port *port, uint32_t *size);
+ /**
+ * Get the content of a port as a sequence of VDBE memory
+ * cells. This API is used to pass VDBE variables to
+ * user-defined Lua functions (see OP_Function in sql/vdbe.c).
+ * The lifecycle of the returned value is the same as for
+ * @get_msgpack method, i.e. it depends on particular
+ * implementation
+ */
+ struct sql_value *(*get_vdbemem)(struct port *port, uint32_t *size);
/** Destroy a port and release associated resources. */
void (*destroy)(struct port *port);
};
@@ -150,6 +159,12 @@ port_get_msgpack(struct port *port, uint32_t *size)
return port->vtab->get_msgpack(port, size);
}
+static inline struct sql_value *
+port_get_vdbemem(struct port *port, uint32_t *size)
+{
+ return port->vtab->get_vdbemem(port, size);
+}
+
#if defined(__cplusplus)
} /* extern "C" */
#endif /* defined __cplusplus */
diff --git a/src/box/call.c b/src/box/call.c
index ac2bf3004..b459a9839 100644
--- a/src/box/call.c
+++ b/src/box/call.c
@@ -73,6 +73,7 @@ static const struct port_vtab port_msgpack_vtab = {
.dump_lua = port_msgpack_dump_lua,
.dump_plain = NULL,
.get_msgpack = port_msgpack_get_msgpack,
+ .get_vdbemem = NULL,
.destroy = NULL,
};
diff --git a/src/box/execute.c b/src/box/execute.c
index 68e94e442..e8b012e5b 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -109,6 +109,7 @@ const struct port_vtab port_sql_vtab = {
/* .dump_lua = */ port_sql_dump_lua,
/* .dump_plain = */ NULL,
/* .get_msgpack = */ NULL,
+ /* .get_vdbemem = */ NULL,
/* .destroy = */ port_sql_destroy,
};
diff --git a/src/box/lua/call.c b/src/box/lua/call.c
index 001578b5a..631003c84 100644
--- a/src/box/lua/call.c
+++ b/src/box/lua/call.c
@@ -510,12 +510,16 @@ port_lua_destroy(struct port *base)
extern const char *
port_lua_dump_plain(struct port *port, uint32_t *size);
+extern struct sql_value *
+port_lua_get_vdbemem(struct port *base, uint32_t *size);
+
static const struct port_vtab port_lua_vtab = {
.dump_msgpack = port_lua_dump,
.dump_msgpack_16 = port_lua_dump_16,
.dump_lua = port_lua_dump_lua,
.dump_plain = port_lua_dump_plain,
.get_msgpack = port_lua_get_msgpack,
+ .get_vdbemem = port_lua_get_vdbemem,
.destroy = port_lua_destroy,
};
diff --git a/src/box/port.c b/src/box/port.c
index 7f552bcfe..6e2fe3a6e 100644
--- a/src/box/port.c
+++ b/src/box/port.c
@@ -140,11 +140,15 @@ port_free(void)
mempool_destroy(&port_tuple_entry_pool);
}
+extern struct sql_value *
+port_tuple_get_vdbemem(struct port *base, uint32_t *size);
+
const struct port_vtab port_tuple_vtab = {
.dump_msgpack = port_tuple_dump_msgpack,
.dump_msgpack_16 = port_tuple_dump_msgpack_16,
.dump_lua = port_tuple_dump_lua,
.dump_plain = NULL,
.get_msgpack = NULL,
+ .get_vdbemem = port_tuple_get_vdbemem,
.destroy = port_tuple_destroy,
};
diff --git a/src/box/sql/expr.c b/src/box/sql/expr.c
index 9055a0770..e59dfa22a 100644
--- a/src/box/sql/expr.c
+++ b/src/box/sql/expr.c
@@ -4135,9 +4135,9 @@ sqlExprCodeTarget(Parse * pParse, Expr * pExpr, int target)
sqlVdbeAddOp4(v, OP_CollSeq, 0, 0, 0,
(char *)coll, P4_COLLSEQ);
}
- assert(func->def->language ==
- FUNC_LANGUAGE_SQL_BUILTIN);
- int op = OP_BuiltinFunction0;
+ int op = func->def->language ==
+ FUNC_LANGUAGE_SQL_BUILTIN ?
+ OP_BuiltinFunction0 : OP_Function;
sqlVdbeAddOp4(v, op, constMask, r1, target,
(char *)func, P4_FUNC);
sqlVdbeChangeP5(v, (u8) nFarg);
diff --git a/src/box/sql/func.c b/src/box/sql/func.c
index 041183b8a..a5868e505 100644
--- a/src/box/sql/func.c
+++ b/src/box/sql/func.c
@@ -47,6 +47,10 @@
#include "box/coll_id_cache.h"
#include "box/schema.h"
#include "box/func.h"
+#include "box/port.h"
+#include "box/tuple.h"
+#include "lua/utils.h"
+#include "mpstream.h"
/*
* Return the collating function associated with a function.
@@ -72,6 +76,280 @@ sqlSkipAccumulatorLoad(sql_context * context)
context->skipFlag = 1;
}
+/**
+ * Allocate a sequence of initialized vdbe memory registers
+ * on region.
+ */
+static struct Mem *
+vdbemem_alloc_on_region(uint32_t count)
+{
+ struct region *region = &fiber()->gc;
+ struct Mem *ret = region_alloc(region, count * sizeof(*ret));
+ if (ret == NULL) {
+ diag_set(OutOfMemory, count * sizeof(*ret),
+ "region_alloc", "ret");
+ return NULL;
+ }
+ memset(ret, 0, count * sizeof(*ret));
+ for (uint32_t i = 0; i < count; i++) {
+ sqlVdbeMemInit(&ret[i], sql_get(), MEM_Null);
+ assert(memIsValid(&ret[i]));
+ }
+ return ret;
+}
+
+static void
+port_vdbemem_dump_lua(struct port *base, struct lua_State *L, bool is_flat)
+{
+ (void) is_flat;
+ struct port_vdbemem *port = (struct port_vdbemem *) base;
+ assert(is_flat == true);
+ for (uint32_t i = 0; i < port->mem_count; i++) {
+ sql_value *param =
+ (sql_value *)((struct Mem *)port->mem + i);
+ switch (sql_value_type(param)) {
+ case MP_INT:
+ luaL_pushint64(L, sql_value_int64(param));
+ break;
+ case MP_UINT:
+ luaL_pushuint64(L, sql_value_uint64(param));
+ break;
+ case MP_DOUBLE:
+ lua_pushnumber(L, sql_value_double(param));
+ break;
+ case MP_STR:
+ lua_pushstring(L, (const char *) sql_value_text(param));
+ break;
+ case MP_BIN:
+ lua_pushlstring(L, sql_value_blob(param),
+ (size_t) sql_value_bytes(param));
+ break;
+ case MP_NIL:
+ lua_pushnil(L);
+ break;
+ case MP_BOOL:
+ lua_pushboolean(L, sql_value_boolean(param));
+ break;
+ default:
+ unreachable();
+ }
+ }
+}
+
+static const char *
+port_vdbemem_get_msgpack(struct port *base, uint32_t *size)
+{
+ struct port_vdbemem *port = (struct port_vdbemem *) base;
+ struct region *region = &fiber()->gc;
+ size_t region_svp = region_used(region);
+ bool is_error = false;
+ struct mpstream stream;
+ mpstream_init(&stream, region, region_reserve_cb, region_alloc_cb,
+ set_encode_error, &is_error);
+ mpstream_encode_array(&stream, port->mem_count);
+ for (uint32_t i = 0; i < port->mem_count && !is_error; i++) {
+ sql_value *param =
+ (sql_value *)((struct Mem *)port->mem + i);
+ switch (sql_value_type(param)) {
+ case MP_INT: {
+ sql_int64 val = sql_value_int64(param);
+ if (val < 0) {
+ mpstream_encode_int(&stream, val);
+ break;
+ }
+ FALLTHROUGH;
+ }
+ case MP_UINT: {
+ sql_int64 val = sql_value_int64(param);
+ mpstream_encode_uint(&stream, val);
+ break;
+ }
+ case MP_DOUBLE: {
+ mpstream_encode_double(&stream,
+ sql_value_double(param));
+ break;
+ }
+ case MP_STR: {
+ const char *str = (const char *) sql_value_text(param);
+ mpstream_encode_str(&stream, str);
+ break;
+ }
+ case MP_BIN: {
+ mpstream_encode_binl(&stream, sql_value_bytes(param));
+ mpstream_memcpy(&stream, sql_value_blob(param),
+ sql_value_bytes(param));
+ break;
+ }
+ case MP_NIL: {
+ mpstream_encode_nil(&stream);
+ break;
+ }
+ case MP_BOOL: {
+ mpstream_encode_bool(&stream, sql_value_boolean(param));
+ break;
+ }
+ default:
+ unreachable();
+ }
+ }
+ mpstream_flush(&stream);
+ *size = region_used(region) - region_svp;
+ if (is_error)
+ goto error;
+ const char *ret = (char *)region_join(region, *size);
+ if (ret == NULL)
+ goto error;
+ return ret;
+error:
+ diag_set(OutOfMemory, *size, "region", "ret");
+ return NULL;
+}
+
+static const struct port_vtab port_vdbemem_vtab;
+
+void
+port_vdbemem_create(struct port *base, struct sql_value *mem,
+ uint32_t mem_count)
+{
+ struct port_vdbemem *port = (struct port_vdbemem *) base;
+ port->vtab = &port_vdbemem_vtab;
+ port->mem = mem;
+ port->mem_count = mem_count;
+}
+
+static struct sql_value *
+port_vdbemem_get_vdbemem(struct port *base, uint32_t *mem_count)
+{
+ struct port_vdbemem *port = (struct port_vdbemem *) base;
+ assert(port->vtab == &port_vdbemem_vtab);
+ *mem_count = port->mem_count;
+ return port->mem;
+}
+
+static const struct port_vtab port_vdbemem_vtab = {
+ .dump_msgpack = NULL,
+ .dump_msgpack_16 = NULL,
+ .dump_lua = port_vdbemem_dump_lua,
+ .dump_plain = NULL,
+ .get_msgpack = port_vdbemem_get_msgpack,
+ .get_vdbemem = port_vdbemem_get_vdbemem,
+ .destroy = NULL,
+};
+
+struct sql_value *
+port_lua_get_vdbemem(struct port *base, uint32_t *size)
+{
+ struct port_lua *port = (struct port_lua *) base;
+ struct lua_State *L = port->L;
+ int argc = lua_gettop(L);
+ if (argc == 0) {
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ "No value was passed from Lua");
+ return NULL;
+ }
+ *size = argc;
+ struct region *region = &fiber()->gc;
+ size_t region_svp = region_used(region);
+ struct Mem *val = vdbemem_alloc_on_region(argc);
+ if (val == NULL)
+ return NULL;
+ for (int i = 0; i < argc; i++) {
+ switch(lua_type(L, -1 - i)) {
+ case LUA_TBOOLEAN:
+ mem_set_bool(&val[i], lua_toboolean(L, -1 - i));
+ break;
+ case LUA_TNUMBER:
+ sqlVdbeMemSetDouble(&val[i], lua_tonumber(L, -1 - i));
+ break;
+ case LUA_TSTRING:
+ if (sqlVdbeMemSetStr(&val[i], lua_tostring(L, -1 - i),
+ -1, 1, SQL_TRANSIENT) != 0)
+ goto error;
+ break;
+ case LUA_TNIL:
+ sqlVdbeMemSetNull(&val[i]);
+ break;
+ default:
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ "Unsupported type passed from Lua");
+ goto error;
+ }
+ }
+ return (struct sql_value *)val;
+error:
+ for (int i = 0; i < argc; i++)
+ sqlVdbeMemRelease(&val[i]);
+ region_truncate(region, region_svp);
+ return NULL;
+}
+
+struct sql_value *
+port_tuple_get_vdbemem(struct port *base, uint32_t *size)
+{
+ struct port_tuple *port = (struct port_tuple *)base;
+ *size = port->size;
+ if (*size == 0) {
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ "No value was passed from C");
+ return NULL;
+ }
+ struct region *region = &fiber()->gc;
+ size_t region_svp = region_used(region);
+ struct Mem *val = vdbemem_alloc_on_region(port->size);
+ if (val == NULL)
+ return NULL;
+ int i = 0;
+ struct port_tuple_entry *pe;
+ for (pe = port->first; pe != NULL; pe = pe->next) {
+ const char *data = tuple_data(pe->tuple);
+ if (mp_typeof(*data) != MP_ARRAY ||
+ mp_decode_array(&data) != 1) {
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ "Unsupported type passed from C");
+ goto error;
+ }
+ uint32_t len;
+ const char *str;
+ switch (mp_typeof(*data)) {
+ case MP_BOOL:
+ mem_set_bool(&val[i], mp_decode_bool(&data));
+ break;
+ case MP_FLOAT:
+ sqlVdbeMemSetDouble(&val[i], mp_decode_float(&data));
+ break;
+ case MP_DOUBLE:
+ sqlVdbeMemSetDouble(&val[i], mp_decode_double(&data));
+ break;
+ case MP_INT:
+ mem_set_i64(val, mp_decode_int(&data));
+ break;
+ case MP_UINT:
+ mem_set_u64(val, mp_decode_uint(&data));
+ break;
+ case MP_STR:
+ str = mp_decode_str(&data, &len);
+ if (sqlVdbeMemSetStr(&val[i], str, len,
+ 1, SQL_TRANSIENT) != 0)
+ goto error;
+ break;
+ case MP_NIL:
+ sqlVdbeMemSetNull(val);
+ break;
+ default:
+ diag_set(ClientError, ER_SQL_EXECUTE,
+ "Unsupported type passed from C");
+ goto error;
+ }
+ i++;
+ }
+ return (struct sql_value *) val;
+error:
+ for (int i = 0; i < port->size; i++)
+ sqlVdbeMemRelease(&val[i]);
+ region_truncate(region, region_svp);
+ return NULL;
+}
+
/*
* Implementation of the non-aggregate min() and max() functions
*/
diff --git a/src/box/sql/vdbe.c b/src/box/sql/vdbe.c
index c5d91ceee..9fb217822 100644
--- a/src/box/sql/vdbe.c
+++ b/src/box/sql/vdbe.c
@@ -46,6 +46,7 @@
#include "box/fk_constraint.h"
#include "box/txn.h"
#include "box/tuple.h"
+#include "box/port.h"
#include "sqlInt.h"
#include "vdbeInt.h"
#include "tarantoolInt.h"
@@ -1783,6 +1784,48 @@ case OP_BuiltinFunction: {
break;
}
+/* Opcode: Function P1 P2 P3 P4 P5
+ * Synopsis: r[P3]=func(r[P2 at P5])
+ *
+ * Invoke a user function (P4 is a pointer to a function object
+ * that defines the function) with P5 arguments taken from
+ * register P2 and successors. The result of the function is
+ * stored in register P3.
+ */
+case OP_Function: {
+ struct func *func = pOp->p4.func;
+ int argc = pOp->p5;
+ struct Mem *argv = &aMem[pOp->p2];
+ struct port args, ret;
+
+ struct region *region = &fiber()->gc;
+ size_t region_svp = region_used(region);
+ port_vdbemem_create(&args, (struct sql_value *)argv, argc);
+ if (func_call(func, &args, &ret) != 0)
+ goto abort_due_to_error;
+
+ pOut = vdbe_prepare_null_out(p, pOp->p3);
+ uint32_t size;
+ struct Mem *mem = (struct Mem *)port_get_vdbemem(&ret, &size);
+ if (mem != NULL && size > 0)
+ *pOut = mem[0];
+ port_destroy(&ret);
+ region_truncate(region, region_svp);
+ if (mem == NULL)
+ goto abort_due_to_error;
+
+ /*
+ * Copy the result of the function invocation into
+ * register P3.
+ */
+ if ((pOut->flags & (MEM_Str | MEM_Blob)) != 0)
+ if (sqlVdbeMemTooBig(pOut)) goto too_big;
+
+ REGISTER_TRACE(p, pOp->p3, pOut);
+ UPDATE_MAX_BLOBSIZE(pOut);
+ break;
+}
+
/* Opcode: BitAnd P1 P2 P3 * *
* Synopsis: r[P3]=r[P1]&r[P2]
*
diff --git a/src/box/lua/schema.lua b/src/box/lua/schema.lua
index 65017d391..98067f795 100644
--- a/src/box/lua/schema.lua
+++ b/src/box/lua/schema.lua
@@ -2143,7 +2143,8 @@ box.schema.func.create = function(name, opts)
language = 'string', body = 'string',
is_deterministic = 'boolean',
is_sandboxed = 'boolean', comment = 'string',
- opts = 'table' })
+ param_list = 'table', returns = 'string',
+ exports = 'table', opts = 'table' })
local _func = box.space[box.schema.FUNC_ID]
local _vfunc = box.space[box.schema.VFUNC_ID]
local func = _vfunc.index.name:get{name}
diff --git a/test/box/function1.result b/test/box/function1.result
index 0ece5448a..a41ca4e3c 100644
--- a/test/box/function1.result
+++ b/test/box/function1.result
@@ -366,6 +366,182 @@ test_run:cmd("setopt delimiter ''");
c:close()
---
...
+--
+-- gh-2233: Invoke Lua functions created outside SQL.
+--
+box.schema.func.create('WAITFOR', {language = 'SQL_BUILTIN', \
+ param_list = {'integer'}, returns = 'integer',exports = {'SQL'}})
+---
+- error: 'Failed to create function ''WAITFOR'': given built-in is not predefined'
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ is_deterministic = true,
+ exports = {'LUA'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.execute('SELECT "function1.divide"()')
+---
+- null
+- function function1.divide() is not available in SQL
+...
+box.func["function1.divide"]:drop()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ is_deterministic = true,
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.execute('SELECT "function1.divide"()')
+---
+- null
+- invalid argument
+...
+box.execute('SELECT "function1.divide"(6)')
+---
+- null
+- 'Wrong number of arguments is passed to function1.divide(): expected 0, got 1'
+...
+box.execute('SELECT "function1.divide"(6, 3)')
+---
+- null
+- 'Wrong number of arguments is passed to function1.divide(): expected 0, got 2'
+...
+box.func["function1.divide"]:drop()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ param_list = {'number', 'number'},
+ is_deterministic = true,
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.execute('SELECT "function1.divide"()')
+---
+- null
+- 'Wrong number of arguments is passed to function1.divide(): expected 2, got 0'
+...
+box.execute('SELECT "function1.divide"(6)')
+---
+- null
+- 'Wrong number of arguments is passed to function1.divide(): expected 2, got 1'
+...
+box.execute('SELECT "function1.divide"(6, 3, 3)')
+---
+- null
+- 'Wrong number of arguments is passed to function1.divide(): expected 2, got 3'
+...
+box.execute('SELECT "function1.divide"(6, 3)')
+---
+- metadata:
+ - name: '"function1.divide"(6, 3)'
+ type: number
+ rows:
+ - [2]
+...
+box.execute('SELECT "function1.divide"(5, 2)')
+---
+- metadata:
+ - name: '"function1.divide"(5, 2)'
+ type: number
+ rows:
+ - [2.5]
+...
+box.func["function1.divide"]:drop()
+---
+...
+function SUMMARIZE(a, b) return a + b end
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number',
+ is_deterministic = true,
+ param_list = {'number', 'number'},
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.execute('SELECT summarize(1, 2)')
+---
+- metadata:
+ - name: summarize(1, 2)
+ type: number
+ rows:
+ - [3]
+...
+box.func.SUMMARIZE:drop()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number',
+ body = 'function (a, b) return a + b end',
+ is_deterministic = true,
+ param_list = {'number', 'number'},
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.execute('SELECT summarize(1, 2)')
+---
+- metadata:
+ - name: summarize(1, 2)
+ type: number
+ rows:
+ - [3]
+...
+box.func.SUMMARIZE:drop()
+---
+...
+--
+-- gh-4113: Valid method to use Lua from SQL
+--
+box.execute('SELECT lua(\'return 1 + 1\')')
+---
+- metadata:
+ - name: lua('return 1 + 1')
+ type: any
+ rows:
+ - [2]
+...
+box.execute('SELECT lua(\'return box.cfg\')')
+---
+- null
+- 'Failed to execute SQL statement: Unsupported type passed from Lua'
+...
+box.execute('SELECT lua(\'return box.cfg()\')')
+---
+- null
+- 'Failed to execute SQL statement: No value was passed from Lua'
+...
+box.execute('SELECT lua(\'return box.cfg.memtx_memory\')')
+---
+- metadata:
+ - name: lua('return box.cfg.memtx_memory')
+ type: any
+ rows:
+ - [107374182]
+...
-- Test registered functions interface.
function divide(a, b) return a / b end
---
diff --git a/test/box/function1.test.lua b/test/box/function1.test.lua
index 665972eda..e576cbb6f 100644
--- a/test/box/function1.test.lua
+++ b/test/box/function1.test.lua
@@ -128,6 +128,69 @@ identifier.run_test(
test_run:cmd("setopt delimiter ''");
c:close()
+--
+-- gh-2233: Invoke Lua functions created outside SQL.
+--
+box.schema.func.create('WAITFOR', {language = 'SQL_BUILTIN', \
+ param_list = {'integer'}, returns = 'integer',exports = {'SQL'}})
+
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ is_deterministic = true,
+ exports = {'LUA'}})
+test_run:cmd("setopt delimiter ''");
+box.execute('SELECT "function1.divide"()')
+box.func["function1.divide"]:drop()
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ is_deterministic = true,
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+box.execute('SELECT "function1.divide"()')
+box.execute('SELECT "function1.divide"(6)')
+box.execute('SELECT "function1.divide"(6, 3)')
+box.func["function1.divide"]:drop()
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create("function1.divide", {language = 'C', returns = 'number',
+ param_list = {'number', 'number'},
+ is_deterministic = true,
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+box.execute('SELECT "function1.divide"()')
+box.execute('SELECT "function1.divide"(6)')
+box.execute('SELECT "function1.divide"(6, 3, 3)')
+box.execute('SELECT "function1.divide"(6, 3)')
+box.execute('SELECT "function1.divide"(5, 2)')
+box.func["function1.divide"]:drop()
+
+function SUMMARIZE(a, b) return a + b end
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number',
+ is_deterministic = true,
+ param_list = {'number', 'number'},
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+box.execute('SELECT summarize(1, 2)')
+box.func.SUMMARIZE:drop()
+
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create("SUMMARIZE", {language = 'LUA', returns = 'number',
+ body = 'function (a, b) return a + b end',
+ is_deterministic = true,
+ param_list = {'number', 'number'},
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+box.execute('SELECT summarize(1, 2)')
+box.func.SUMMARIZE:drop()
+
+--
+-- gh-4113: Valid method to use Lua from SQL
+--
+box.execute('SELECT lua(\'return 1 + 1\')')
+box.execute('SELECT lua(\'return box.cfg\')')
+box.execute('SELECT lua(\'return box.cfg()\')')
+box.execute('SELECT lua(\'return box.cfg.memtx_memory\')')
+
-- Test registered functions interface.
function divide(a, b) return a / b end
box.schema.func.create("divide", {comment = 'Divide two values'})
diff --git a/test/sql-tap/alias.test.lua b/test/sql-tap/alias.test.lua
index f9e6fc9fa..75391b305 100755
--- a/test/sql-tap/alias.test.lua
+++ b/test/sql-tap/alias.test.lua
@@ -25,14 +25,13 @@ test:plan(9)
--
counter = 0
-sequence = function()
- counter = counter + 1
- return counter
-end
-- Function is declared as deterministic deliberately.
-- Otherwise it would be called as much as it occurs in a query.
-box.internal.sql_create_function("sequence", "INT", sequence, 0, true)
+box.schema.func.create('SEQUENCE', {language = 'Lua', is_deterministic = true,
+ returns = 'unsigned',
+ body = 'function() counter = counter + 1 return counter end',
+ exports = {'LUA', 'SQL'}})
test:do_test(
"alias-1.1",
@@ -220,6 +219,6 @@ test:do_test(
-- -- </alias-3.1>
-- })
-
+box.func.SEQUENCE:drop()
test:finish_test()
diff --git a/test/sql-tap/check.test.lua b/test/sql-tap/check.test.lua
index 6e4666cf2..c9b60bbf5 100755
--- a/test/sql-tap/check.test.lua
+++ b/test/sql-tap/check.test.lua
@@ -680,10 +680,11 @@ test:do_execsql_test(
-- cannot be tested).
--
--reset_db()
-local function myfunc(x)
- return x < 10
-end
-box.internal.sql_create_function("myfunc", "INT", myfunc)
+box.schema.func.create('MYFUNC', {language = 'Lua',
+ is_deterministic = true,
+ body = 'function(x) return x < 10 end',
+ returns = 'boolean', param_list = {'number'},
+ exports = {'LUA', 'SQL'}})
test:do_execsql_test(
7.1,
@@ -808,5 +809,7 @@ test:do_catchsql_test(
-- </9.3>
})
+box.func.MYFUNC:drop()
+
test:finish_test()
diff --git a/test/sql-tap/func5.test.lua b/test/sql-tap/func5.test.lua
index 0b255e659..45994a352 100755
--- a/test/sql-tap/func5.test.lua
+++ b/test/sql-tap/func5.test.lua
@@ -71,13 +71,25 @@ test:do_execsql_test(
global_counter = 0
-counter = function(str)
- global_counter = global_counter + 1
- return global_counter
-end
+box.schema.func.create('COUNTER1', {language = 'Lua', is_deterministic = false,
+ param_list = {'any'}, returns = 'integer',
+ exports = {'SQL', 'LUA'},
+ body = [[
+ function(str)
+ global_counter = global_counter + 1
+ return global_counter
+ end
+ ]]})
-box.internal.sql_create_function("counter1", "INT", counter, -1, false)
-box.internal.sql_create_function("counter2", "INT", counter, -1, true)
+box.schema.func.create('COUNTER2', {language = 'Lua', is_deterministic = true,
+ param_list = {'any'}, returns = 'integer',
+ exports = {'SQL', 'LUA'},
+ body = [[
+ function(str)
+ global_counter = global_counter + 1
+ return global_counter
+ end
+ ]]})
test:do_execsql_test(
"func5-2.2",
@@ -257,4 +269,7 @@ test:do_execsql_test(
SELECT LEAST(false, 'STR', 1, 0.5);
]], { false } )
+box.func.COUNTER1:drop()
+box.func.COUNTER2:drop()
+
test:finish_test()
diff --git a/test/sql-tap/lua_sql.test.lua b/test/sql-tap/lua_sql.test.lua
index b0ecccd52..67eff2d1b 100755
--- a/test/sql-tap/lua_sql.test.lua
+++ b/test/sql-tap/lua_sql.test.lua
@@ -1,19 +1,16 @@
#!/usr/bin/env tarantool
test = require("sqltester")
NULL = require('msgpack').NULL
-test:plan(24)
-
-local function func1(a)
- return a
-end
-local function allways_2(a)
- return 2
-end
+test:plan(22)
test:do_test(
"lua_sql-1.0",
function ()
- box.internal.sql_create_function("func1", "INT", allways_2)
+ box.schema.func.create('FUNC1', {language = 'Lua',
+ is_deterministic = true,
+ body = 'function(a) return 2 end',
+ param_list = {'any'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
return test:execsql("select func1(1)")
end,
{2})
@@ -22,34 +19,16 @@ test:do_test(
test:do_test(
"lua_sql-1.1",
function ()
- box.internal.sql_create_function("func1", "INT", func1)
+ box.func.FUNC1:drop()
+ box.schema.func.create('FUNC1', {language = 'Lua',
+ is_deterministic = true,
+ body = 'function(a) return a end',
+ param_list = {'scalar'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
return test:execsql("select func1(1)")
end,
{1})
--- try to loose memory
-test:do_test(
- "lua_sql-1.2",
- function ()
- for i = 1, 1000000, 1 do
- box.internal.sql_create_function("func1", "INT", func1)
- end
- return test:execsql("select func1(1)")
- end,
- {1})
-
--- check sql polymorphism
-test:do_test(
- "lua_sql-1.3",
- function ()
- box.internal.sql_create_function("allways_2", "INT", allways_2, 1) -- specify 1 arg
- box.internal.sql_create_function("allways_2", "INT", func1)
- box.internal.sql_create_function("allways_2", "INT", func1, 2)
- box.internal.sql_create_function("allways_2", "INT", func1, 3)
- return test:execsql("select allways_2(1)")
- end,
- {2})
-
test:do_catchsql_test(
"lua_sql-1.0",
"select func3(1)",
@@ -72,7 +51,7 @@ for _, val in ipairs({
{result})
end
-local from_sql_to_lua = {
+from_sql_to_lua = {
[1] = {1, 1},
[2] = {"1", 1},
[3] = {"1.5", 1.5},
@@ -81,14 +60,19 @@ local from_sql_to_lua = {
[6] = {"x'0500'", "\u{0005}\u{0000}"},
[7] = {"123123123123123", 123123123123123LL},
}
-local json = require("json")
-local function check_from_sql_to_lua(i, arg)
- if from_sql_to_lua[i][2] == arg then
- return 1
- end
- return 0
-end
-box.internal.sql_create_function("check_from_sql_to_lua", "INT", check_from_sql_to_lua)
+
+box.schema.func.create('CHECK_FROM_SQL_TO_LUA', {language = 'Lua',
+ is_deterministic = true,
+ body = [[
+ function(i, arg)
+ if from_sql_to_lua[i][2] == arg then
+ return 1
+ end
+ return 0
+ end
+ ]],
+ param_list = {'integer', 'scalar'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
-- check for different types
for i = 1, #from_sql_to_lua, 1 do
@@ -98,17 +82,23 @@ for i = 1, #from_sql_to_lua, 1 do
{1})
end
-local from_lua_to_sql = {
+from_lua_to_sql = {
[1] = {1, 1},
[2] = {"1.5", 1.5},
[3] = {"'1'", "1"},
[4] = {"true", true},
[5] = {"false", false},
}
-local function check_from_lua_to_sql(i)
- return from_lua_to_sql[i][2]
-end
-box.internal.sql_create_function("check_from_lua_to_sql", "VARBINARY", check_from_lua_to_sql)
+
+box.schema.func.create('CHECK_FROM_LUA_TO_SQL', {language = 'Lua',
+ is_deterministic = true,
+ body = [[
+ function(i)
+ return from_lua_to_sql[i][2]
+ end
+ ]],
+ param_list = {'integer'}, returns = 'scalar',
+ exports = {'LUA', 'SQL'}})
-- check for different types
for i = 1, #from_lua_to_sql, 1 do
@@ -118,14 +108,20 @@ for i = 1, #from_lua_to_sql, 1 do
{true})
end
-local from_lua_to_sql_bad = {
+from_lua_to_sql_bad = {
[1] = NULL,
[2] = 12LL, -- it is possible to support this type
}
-local function check_from_lua_to_sql_bad(i)
- return from_lua_to_sql_bad[i]
-end
-box.internal.sql_create_function("check_from_lua_to_sql_bad", "VARBINARY", check_from_lua_to_sql_bad)
+
+box.schema.func.create('CHECK_FROM_LUA_TO_SQL_BAD', {language = 'Lua',
+ is_deterministic = true,
+ body = [[
+ function(i)
+ return from_lua_to_sql_bad[i]
+ end
+ ]],
+ param_list = {'integer'}, returns = 'scalar',
+ exports = {'LUA', 'SQL'}})
for i = 1, #from_lua_to_sql_bad, 1 do
test:do_catchsql_test(
@@ -134,16 +130,27 @@ for i = 1, #from_lua_to_sql_bad, 1 do
{1, "/Unsupported/"})
end
-local function allways_error()
- error("my_error123")
- return 1
-end
-box.internal.sql_create_function("allways_error", "INT", allways_error)
+box.schema.func.create('ALLWAYS_ERROR', {language = 'Lua',
+ is_deterministic = true,
+ body = [[
+ function()
+ error("my_error123")
+ return 1
+ end
+ ]],
+ param_list = {}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+
test:do_catchsql_test(
"lua_sql-2.6",
"select allways_error()",
{1, "/my_error123/"})
+box.func.FUNC1:drop()
+box.func.CHECK_FROM_SQL_TO_LUA:drop()
+box.func.CHECK_FROM_LUA_TO_SQL:drop()
+box.func.CHECK_FROM_LUA_TO_SQL_BAD:drop()
+box.func.ALLWAYS_ERROR:drop()
test:finish_test()
diff --git a/test/sql-tap/subquery.test.lua b/test/sql-tap/subquery.test.lua
index 4e4b8d453..6bedf5879 100755
--- a/test/sql-tap/subquery.test.lua
+++ b/test/sql-tap/subquery.test.lua
@@ -710,17 +710,20 @@ test:do_execsql_test(
-- for a matching column name did not cause an otherwise static subquery
-- to become a dynamic (correlated) subquery.
--
-local callcnt = 0
+callcnt = 0
test:do_test(
"subquery-5.1",
function()
- local function callcntproc(n)
- callcnt = callcnt + 1
- return n
- end
-
- callcnt = 0
- box.internal.sql_create_function("callcnt", "INT", callcntproc)
+ box.schema.func.create('CALLCNT', {language = 'Lua',
+ is_deterministic = true,
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'},
+ body = [[
+ function(n)
+ callcnt = callcnt + 1
+ return n
+ end
+ ]]})
return test:execsql [[
CREATE TABLE t4(x TEXT,y INT PRIMARY KEY);
INSERT INTO t4 VALUES('one',1);
@@ -791,6 +794,8 @@ test:do_test(
return callcnt
end, 1)
+box.func.CALLCNT:drop()
+
--############ was disable until we get #2652 fixed
-- Ticket #2652. Allow aggregate functions of outer queries inside
-- a non-aggregate subquery.
diff --git a/test/sql-tap/trigger9.test.lua b/test/sql-tap/trigger9.test.lua
index e7e170b3d..e39a31384 100755
--- a/test/sql-tap/trigger9.test.lua
+++ b/test/sql-tap/trigger9.test.lua
@@ -46,7 +46,11 @@ local function has_rowdata(sql)
-- X(41, "X!cmd", [=[["expr","[lsearch [execsql \"explain $sql\"] RowData]>=0"]]=])
end
-box.internal.sql_create_function('randstr', 'TEXT', test.randstr, 1)
+box.schema.func.create('RANDSTR', {language = 'Lua',
+ body = 'function(n) return test.randstr(n) end',
+ param_list = {'integer'}, returns = 'string',
+ exports = {'LUA', 'SQL'}})
+
-- MUST_WORK_TEST
test:do_execsql_test(
@@ -171,6 +175,8 @@ test:do_execsql_test(
-- </trigger9-1.4.3>
})
+box.func.RANDSTR:drop()
+
test:do_execsql_test(
"trigger9-1.5.1",
[[
diff --git a/test/sql/errinj.result b/test/sql/errinj.result
index ea570f0dc..ecc194fb8 100644
--- a/test/sql/errinj.result
+++ b/test/sql/errinj.result
@@ -397,32 +397,6 @@ box.execute("DROP TABLE t3;")
- row_count: 1
...
--
--- gh-3931: Store regular identifiers in case-normal form
---
-errinj = box.error.injection
----
-...
-errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", true)
----
-- ok
-...
-box.execute("CREATE TABLE hello (id INT primary key,x INT,y INT);")
----
-- null
-- Failed to allocate 6 bytes in sqlDbMallocRawNN for res
-...
-dummy_f = function(int) return 1 end
----
-...
-box.internal.sql_create_function("counter1", "INT", dummy_f, -1, false)
----
-- error: Failed to allocate 9 bytes in region_alloc for res
-...
-errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", false)
----
-- ok
-...
---
-- Tests which are aimed at verifying work of commit/rollback
-- triggers on _ck_constraint space.
--
diff --git a/test/sql/errinj.test.lua b/test/sql/errinj.test.lua
index 552860a38..2b4f74a82 100644
--- a/test/sql/errinj.test.lua
+++ b/test/sql/errinj.test.lua
@@ -118,16 +118,6 @@ box.execute("INSERT INTO t3 VALUES(1, 1, 3);")
errinj.set("ERRINJ_WAL_IO", false)
box.execute("DROP TABLE t3;")
---
--- gh-3931: Store regular identifiers in case-normal form
---
-errinj = box.error.injection
-errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", true)
-box.execute("CREATE TABLE hello (id INT primary key,x INT,y INT);")
-dummy_f = function(int) return 1 end
-box.internal.sql_create_function("counter1", "INT", dummy_f, -1, false)
-errinj.set("ERRINJ_SQL_NAME_NORMALIZATION", false)
-
--
-- Tests which are aimed at verifying work of commit/rollback
-- triggers on _ck_constraint space.
diff --git a/test/sql/func-recreate.result b/test/sql/func-recreate.result
index 73fb03cc4..1f6966431 100644
--- a/test/sql/func-recreate.result
+++ b/test/sql/func-recreate.result
@@ -12,7 +12,15 @@ box.execute('pragma sql_default_engine=\''..engine..'\'')
fiber = require('fiber')
---
...
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) fiber.sleep(n) return n end)
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
---
...
ch = fiber.channel(1)
@@ -24,10 +32,19 @@ _ = fiber.create(function () ch:put(box.execute('select WAITFOR(0.2)')) end)
fiber.sleep(0.1)
---
...
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end)
+box.func.WAITFOR:drop()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
---
-- error: 'Failed to create function ''WAITFOR'': unable to create function due to
- active statements'
...
ch:get()
---
@@ -37,6 +54,20 @@ ch:get()
rows:
- [0.2]
...
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end)
+box.func.WAITFOR:drop()
+---
+...
+test_run:cmd("setopt delimiter ';'")
+---
+- true
+...
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+---
+...
+box.func.WAITFOR:drop()
---
...
diff --git a/test/sql/func-recreate.test.lua b/test/sql/func-recreate.test.lua
index 753e9ca4d..5496baf6e 100644
--- a/test/sql/func-recreate.test.lua
+++ b/test/sql/func-recreate.test.lua
@@ -4,14 +4,34 @@ box.execute('pragma sql_default_engine=\''..engine..'\'')
-- Check errors during function create process
fiber = require('fiber')
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) fiber.sleep(n) return n end)
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
ch = fiber.channel(1)
_ = fiber.create(function () ch:put(box.execute('select WAITFOR(0.2)')) end)
fiber.sleep(0.1)
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end)
+box.func.WAITFOR:drop()
+
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
ch:get()
-box.internal.sql_create_function('WAITFOR', 'INT', function (n) require('fiber').sleep(n) return n end)
+box.func.WAITFOR:drop()
+test_run:cmd("setopt delimiter ';'")
+box.schema.func.create('WAITFOR', {language = 'Lua',
+ body = 'function (n) fiber.sleep(n) return n end',
+ param_list = {'integer'}, returns = 'integer',
+ exports = {'LUA', 'SQL'}})
+test_run:cmd("setopt delimiter ''");
+
+box.func.WAITFOR:drop()
--
2.22.1
More information about the Tarantool-patches
mailing list