[Tarantool-patches] [PATCH v3 18/20] box: introduce prepared statements
Nikita Pettik
korablev at tarantool.org
Fri Dec 20 15:47:23 MSK 2019
This patch introduces local prepared statements. Support of prepared
statements in IProto protocol and netbox is added in the next patch.
Prepared statement is an opaque instance of SQL Virtual Machine. It can
be executed several times without necessity of query recompilation. To
achieve this one can use box.prepare(...) function. It takes string of
SQL query to be prepared; returns extended set of meta-information
including statement's ID, parameter's types and names, types and names
of columns of the resulting set, count of parameters to be bound. Lua
object representing result of :prepare() invocation also features two
methods - :execute() and :unprepare(). They correspond to
box.execute(stmt.stmt_id) and box.unprepare(stmt.stmt_id), i.e.
automatically substitute string of prepared statement to be executed.
Statements are held in prepared statement cache - for details see
previous commit. After schema changes all prepared statement located in
cache are considered to be expired - they must be re-prepared by
separate :prepare() call (or be invalidated with :unrepare()).
Two sessions can share one prepared statements. But in current
implementation if statement is executed by one session, another one is
not able to use it and will compile it from scratch and than execute.
SQL cache memory limit is regulated by box{sql_cache_size} which can be
set dynamically. However, it can be set to the value which is less than
the size of current free space in cache (since otherwise some statements
can disappear from cache).
Part of #2592
---
src/box/errcode.h | 1 +
src/box/execute.c | 114 ++++++++
src/box/execute.h | 16 +-
src/box/lua/execute.c | 213 +++++++++++++-
src/box/lua/execute.h | 2 +-
src/box/lua/init.c | 2 +-
src/box/sql/prepare.c | 9 -
test/box/misc.result | 3 +
test/sql/engine.cfg | 3 +
test/sql/prepared.result | 687 +++++++++++++++++++++++++++++++++++++++++++++
test/sql/prepared.test.lua | 240 ++++++++++++++++
11 files changed, 1267 insertions(+), 23 deletions(-)
create mode 100644 test/sql/prepared.result
create mode 100644 test/sql/prepared.test.lua
diff --git a/src/box/errcode.h b/src/box/errcode.h
index ee44f61b3..9e12f3a31 100644
--- a/src/box/errcode.h
+++ b/src/box/errcode.h
@@ -259,6 +259,7 @@ struct errcode_record {
/*204 */_(ER_SQL_FUNC_WRONG_RET_COUNT, "SQL expects exactly one argument returned from %s, got %d")\
/*205 */_(ER_FUNC_INVALID_RETURN_TYPE, "Function '%s' returned value of invalid type: expected %s got %s") \
/*206 */_(ER_SQL_PREPARE, "Failed to prepare SQL statement: %s") \
+ /*207 */_(ER_WRONG_QUERY_ID, "Prepared statement with id %u does not exist") \
/*
* !IMPORTANT! Please follow instructions at start of the file
diff --git a/src/box/execute.c b/src/box/execute.c
index 3bc4988b7..09224c23a 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -30,6 +30,7 @@
*/
#include "execute.h"
+#include "assoc.h"
#include "bind.h"
#include "iproto_constants.h"
#include "sql/sqlInt.h"
@@ -45,6 +46,8 @@
#include "tuple.h"
#include "sql/vdbe.h"
#include "box/lua/execute.h"
+#include "box/sql_stmt_cache.h"
+#include "session.h"
const char *sql_info_key_strs[] = {
"row_count",
@@ -413,6 +416,81 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out)
return 0;
}
+static bool
+sql_stmt_check_schema_version(struct sql_stmt *stmt)
+{
+ return sql_stmt_schema_version(stmt) == box_schema_version();
+}
+
+/**
+ * Re-compile statement and refresh global prepared statement
+ * cache with the newest value.
+ */
+static int
+sql_reprepare(struct sql_stmt **stmt)
+{
+ const char *sql_str = sql_stmt_query_str(*stmt);
+ struct sql_stmt *new_stmt;
+ if (sql_stmt_compile(sql_str, strlen(sql_str), NULL,
+ &new_stmt, NULL) != 0)
+ return -1;
+ if (sql_stmt_cache_update(*stmt, new_stmt) != 0)
+ return -1;
+ *stmt = new_stmt;
+ return 0;
+}
+
+/**
+ * Compile statement and save it to the global holder;
+ * update session hash with prepared statement ID (if
+ * it's not already there).
+ */
+int
+sql_prepare(const char *sql, int len, struct port *port)
+{
+ uint32_t stmt_id = sql_stmt_calculate_id(sql, len);
+ struct sql_stmt *stmt = sql_stmt_cache_find(stmt_id);
+ if (stmt == NULL) {
+ if (sql_stmt_compile(sql, len, NULL, &stmt, NULL) != 0)
+ return -1;
+ if (sql_stmt_cache_insert(stmt) != 0) {
+ sql_stmt_finalize(stmt);
+ return -1;
+ }
+ } else {
+ if (! sql_stmt_check_schema_version(stmt)) {
+ if (sql_reprepare(&stmt) != 0)
+ return -1;
+ }
+ }
+ assert(stmt != NULL);
+ /* Add id to the list of available statements in session. */
+ if (!session_check_stmt_id(current_session(), stmt_id))
+ session_add_stmt_id(current_session(), stmt_id);
+ enum sql_serialization_format format = sql_column_count(stmt) > 0 ?
+ DQL_PREPARE : DML_PREPARE;
+ port_sql_create(port, stmt, format, false);
+
+ return 0;
+}
+
+/**
+ * Deallocate prepared statement from current session:
+ * remove its ID from session-local hash and unref entry
+ * in global holder.
+ */
+int
+sql_unprepare(uint32_t stmt_id)
+{
+ if (!session_check_stmt_id(current_session(), stmt_id)) {
+ diag_set(ClientError, ER_WRONG_QUERY_ID, stmt_id);
+ return -1;
+ }
+ session_remove_stmt_id(current_session(), stmt_id);
+ sql_stmt_unref(stmt_id);
+ return 0;
+}
+
/**
* Execute prepared SQL statement.
*
@@ -450,6 +528,42 @@ sql_execute(struct sql_stmt *stmt, struct port *port, struct region *region)
return 0;
}
+int
+sql_execute_prepared(uint32_t stmt_id, const struct sql_bind *bind,
+ uint32_t bind_count, struct port *port,
+ struct region *region)
+{
+
+ if (!session_check_stmt_id(current_session(), stmt_id)) {
+ diag_set(ClientError, ER_WRONG_QUERY_ID, stmt_id);
+ return -1;
+ }
+ struct sql_stmt *stmt = sql_stmt_cache_find(stmt_id);
+ assert(stmt != NULL);
+ if (! sql_stmt_check_schema_version(stmt)) {
+ diag_set(ClientError, ER_SQL_EXECUTE, "statement has expired");
+ return -1;
+ }
+ if (sql_stmt_busy(stmt)) {
+ const char *sql_str = sql_stmt_query_str(stmt);
+ return sql_prepare_and_execute(sql_str, strlen(sql_str), bind,
+ bind_count, port, region);
+ }
+ if (sql_bind(stmt, bind, bind_count) != 0)
+ return -1;
+ enum sql_serialization_format format = sql_column_count(stmt) > 0 ?
+ DQL_EXECUTE : DML_EXECUTE;
+ port_sql_create(port, stmt, format, false);
+ if (sql_execute(stmt, port, region) != 0) {
+ port_destroy(port);
+ sql_stmt_reset(stmt);
+ return -1;
+ }
+ sql_stmt_reset(stmt);
+
+ return 0;
+}
+
int
sql_prepare_and_execute(const char *sql, int len, const struct sql_bind *bind,
uint32_t bind_count, struct port *port,
diff --git a/src/box/execute.h b/src/box/execute.h
index 61c8e0281..5e2327f4a 100644
--- a/src/box/execute.h
+++ b/src/box/execute.h
@@ -62,6 +62,14 @@ extern const char *sql_info_key_strs[];
struct region;
struct sql_bind;
+int
+sql_unprepare(uint32_t stmt_id);
+
+int
+sql_execute_prepared(uint32_t query_id, const struct sql_bind *bind,
+ uint32_t bind_count, struct port *port,
+ struct region *region);
+
/**
* Prepare and execute an SQL statement.
* @param sql SQL statement.
@@ -135,13 +143,11 @@ sql_stmt_busy(const struct sql_stmt *stmt);
* Prepare (compile into VDBE byte-code) statement.
*
* @param sql UTF-8 encoded SQL statement.
- * @param length Length of @param sql in bytes.
- * @param[out] stmt A pointer to the prepared statement.
- * @param[out] sql_tail End of parsed string.
+ * @param len Length of @param sql in bytes.
+ * @param port Port to store request response.
*/
int
-sql_prepare(const char *sql, int length, struct sql_stmt **stmt,
- const char **sql_tail);
+sql_prepare(const char *sql, int len, struct port *port);
#if defined(__cplusplus)
} /* extern "C" { */
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index b164ffcaf..6cb1f5db9 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -5,6 +5,8 @@
#include "box/port.h"
#include "box/execute.h"
#include "box/bind.h"
+#include "box/sql_stmt_cache.h"
+#include "box/schema.h"
/**
* Serialize a description of the prepared statement.
@@ -38,6 +40,101 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
}
}
+static inline void
+lua_sql_get_params_metadata(struct sql_stmt *stmt, struct lua_State *L)
+{
+ int bind_count = sql_bind_parameter_count(stmt);
+ lua_createtable(L, bind_count, 0);
+ for (int i = 0; i < bind_count; ++i) {
+ lua_createtable(L, 0, 2);
+ const char *name = sql_bind_parameter_name(stmt, i);
+ if (name == NULL)
+ name = "?";
+ const char *type = "ANY";
+ lua_pushstring(L, name);
+ lua_setfield(L, -2, "name");
+ lua_pushstring(L, type);
+ lua_setfield(L, -2, "type");
+ lua_rawseti(L, -2, i + 1);
+ }
+}
+
+/** Forward declaration to avoid code movement. */
+static int
+lbox_execute(struct lua_State *L);
+
+/**
+ * Prepare SQL statement: compile it and save to the cache.
+ * In fact it is wrapper around box.execute() which unfolds
+ * it to box.execute(stmt.query_id).
+ */
+static int
+lbox_execute_prepared(struct lua_State *L)
+{
+ int top = lua_gettop(L);
+
+ if ((top != 1 && top != 2) || ! lua_istable(L, 1))
+ return luaL_error(L, "Usage: statement:execute([, params])");
+ lua_getfield(L, 1, "stmt_id");
+ if (!lua_isnumber(L, -1))
+ return luaL_error(L, "Query id is expected to be numeric");
+ lua_remove(L, 1);
+ if (top == 2) {
+ /*
+ * Stack state (before remove operation):
+ * 1 Prepared statement object (Lua table)
+ * 2 Bindings (Lua table)
+ * 3 Statement ID(fetched from PS table) - top of stack
+ *
+ * We should make it suitable to pass arguments to
+ * lbox_execute(), i.e. after manipulations stack
+ * should look like:
+ * 1 Statement ID
+ * 2 Bindings - top of stack
+ * Since there's no swap operation, we firstly remove
+ * PS object, then copy table of values to be bound to
+ * the top of stack (push), and finally remove original
+ * bindings from stack.
+ */
+ lua_pushvalue(L, 1);
+ lua_remove(L, 1);
+ }
+ return lbox_execute(L);
+}
+
+/**
+ * Unprepare statement: remove it from prepared statements cache.
+ * This function can be called in two ways: as member of prepared
+ * statement handle (stmt:unprepare()) or as box.unprepare(stmt_id).
+ */
+static int
+lbox_unprepare(struct lua_State *L)
+{
+ int top = lua_gettop(L);
+
+ if (top != 1 || (! lua_istable(L, 1) && ! lua_isnumber(L, 1))) {
+ return luaL_error(L, "Usage: statement:unprepare() or "\
+ "box.unprepare(stmt_id)");
+ }
+ lua_Integer stmt_id;
+ if (lua_istable(L, 1)) {
+ lua_getfield(L, -1, "stmt_id");
+ if (! lua_isnumber(L, -1)) {
+ return luaL_error(L, "Statement id is expected "\
+ "to be numeric");
+ }
+ stmt_id = lua_tointeger(L, -1);
+ lua_pop(L, 1);
+ } else {
+ stmt_id = lua_tonumber(L, 1);
+ }
+ if (stmt_id < 0)
+ return luaL_error(L, "Statement id can't be negative");
+ if (sql_unprepare((uint32_t) stmt_id) != 0)
+ return luaT_push_nil_and_error(L);
+ return 0;
+}
+
void
port_sql_dump_lua(struct port *port, struct lua_State *L, bool is_flat)
{
@@ -82,7 +179,66 @@ port_sql_dump_lua(struct port *port, struct lua_State *L, bool is_flat)
}
break;
}
- default: {
+ case DQL_PREPARE: {
+ /* Format is following:
+ * stmt_id,
+ * param_count,
+ * params {name, type},
+ * metadata {name, type}
+ * execute(), unprepare()
+ */
+ lua_createtable(L, 0, 6);
+ /* query_id */
+ const char *sql_str = sql_stmt_query_str(port_sql->stmt);
+ luaL_pushuint64(L, sql_stmt_calculate_id(sql_str,
+ strlen(sql_str)));
+ lua_setfield(L, -2, "stmt_id");
+ /* param_count */
+ luaL_pushuint64(L, sql_bind_parameter_count(stmt));
+ lua_setfield(L, -2, "param_count");
+ /* params map */
+ lua_sql_get_params_metadata(stmt, L);
+ lua_setfield(L, -2, "params");
+ /* metadata */
+ lua_sql_get_metadata(stmt, L, sql_column_count(stmt));
+ lua_setfield(L, -2, "metadata");
+ /* execute function */
+ lua_pushcfunction(L, lbox_execute_prepared);
+ lua_setfield(L, -2, "execute");
+ /* unprepare function */
+ lua_pushcfunction(L, lbox_unprepare);
+ lua_setfield(L, -2, "unprepare");
+ break;
+ }
+ case DML_PREPARE : {
+ assert(((struct port_tuple *) port)->size == 0);
+ /* Format is following:
+ * stmt_id,
+ * param_count,
+ * params {name, type},
+ * execute(), unprepare()
+ */
+ lua_createtable(L, 0, 5);
+ /* query_id */
+ const char *sql_str = sql_stmt_query_str(port_sql->stmt);
+ luaL_pushuint64(L, sql_stmt_calculate_id(sql_str,
+ strlen(sql_str)));
+ lua_setfield(L, -2, "stmt_id");
+ /* param_count */
+ luaL_pushuint64(L, sql_bind_parameter_count(stmt));
+ lua_setfield(L, -2, "param_count");
+ /* params map */
+ lua_sql_get_params_metadata(stmt, L);
+ lua_setfield(L, -2, "params");
+ /* execute function */
+ lua_pushcfunction(L, lbox_execute_prepared);
+ lua_setfield(L, -2, "execute");
+ /* unprepare function */
+ lua_pushcfunction(L, lbox_unprepare);
+ lua_setfield(L, -2, "unprepare");
+ break;
+ }
+ default:{
unreachable();
}
}
@@ -253,9 +409,8 @@ lbox_execute(struct lua_State *L)
int top = lua_gettop(L);
if ((top != 1 && top != 2) || ! lua_isstring(L, 1))
- return luaL_error(L, "Usage: box.execute(sqlstring[, params])");
-
- const char *sql = lua_tolstring(L, 1, &length);
+ return luaL_error(L, "Usage: box.execute(sqlstring[, params]) "
+ "or box.execute(stmt_id[, params])");
if (top == 2) {
if (! lua_istable(L, 2))
@@ -264,9 +419,44 @@ lbox_execute(struct lua_State *L)
if (bind_count < 0)
return luaT_push_nil_and_error(L);
}
+ /*
+ * lua_isstring() returns true for numeric values as well,
+ * so test explicit type instead.
+ */
+ if (lua_type(L, 1) == LUA_TSTRING) {
+ const char *sql = lua_tolstring(L, 1, &length);
+ if (sql_prepare_and_execute(sql, length, bind, bind_count, &port,
+ &fiber()->gc) != 0)
+ return luaT_push_nil_and_error(L);
+ } else {
+ assert(lua_type(L, 1) == LUA_TNUMBER);
+ lua_Integer query_id = lua_tointeger(L, 1);
+ if (query_id < 0)
+ return luaL_error(L, "Statement id can't be negative");
+ if (sql_execute_prepared(query_id, bind, bind_count, &port,
+ &fiber()->gc) != 0)
+ return luaT_push_nil_and_error(L);
+ }
+ port_dump_lua(&port, L, false);
+ port_destroy(&port);
+ return 1;
+}
- if (sql_prepare_and_execute(sql, length, bind, bind_count, &port,
- &fiber()->gc) != 0)
+/**
+ * Prepare SQL statement: compile it and save to the cache.
+ */
+static int
+lbox_prepare(struct lua_State *L)
+{
+ size_t length;
+ struct port port;
+ int top = lua_gettop(L);
+
+ if ((top != 1 && top != 2) || ! lua_isstring(L, 1))
+ return luaL_error(L, "Usage: box.prepare(sqlstring)");
+
+ const char *sql = lua_tolstring(L, 1, &length);
+ if (sql_prepare(sql, length, &port) != 0)
return luaT_push_nil_and_error(L);
port_dump_lua(&port, L, false);
port_destroy(&port);
@@ -274,11 +464,20 @@ lbox_execute(struct lua_State *L)
}
void
-box_lua_execute_init(struct lua_State *L)
+box_lua_sql_init(struct lua_State *L)
{
lua_getfield(L, LUA_GLOBALSINDEX, "box");
lua_pushstring(L, "execute");
lua_pushcfunction(L, lbox_execute);
lua_settable(L, -3);
+
+ lua_pushstring(L, "prepare");
+ lua_pushcfunction(L, lbox_prepare);
+ lua_settable(L, -3);
+
+ lua_pushstring(L, "unprepare");
+ lua_pushcfunction(L, lbox_unprepare);
+ lua_settable(L, -3);
+
lua_pop(L, 1);
}
diff --git a/src/box/lua/execute.h b/src/box/lua/execute.h
index 23e193fa4..bafd67615 100644
--- a/src/box/lua/execute.h
+++ b/src/box/lua/execute.h
@@ -66,6 +66,6 @@ lua_sql_bind_list_decode(struct lua_State *L, struct sql_bind **out_bind,
int idx);
void
-box_lua_execute_init(struct lua_State *L);
+box_lua_sql_init(struct lua_State *L);
#endif /* INCLUDES_TARANTOOL_LUA_EXECUTE_H */
diff --git a/src/box/lua/init.c b/src/box/lua/init.c
index 7ffed409d..7be520e09 100644
--- a/src/box/lua/init.c
+++ b/src/box/lua/init.c
@@ -314,7 +314,7 @@ box_lua_init(struct lua_State *L)
box_lua_ctl_init(L);
box_lua_session_init(L);
box_lua_xlog_init(L);
- box_lua_execute_init(L);
+ box_lua_sql_init(L);
luaopen_net_box(L);
lua_pop(L, 1);
tarantool_lua_console_init(L);
diff --git a/src/box/sql/prepare.c b/src/box/sql/prepare.c
index 73d6866a4..5b1c6c581 100644
--- a/src/box/sql/prepare.c
+++ b/src/box/sql/prepare.c
@@ -193,15 +193,6 @@ sqlReprepare(Vdbe * p)
return 0;
}
-int
-sql_prepare(const char *sql, int length, struct sql_stmt **stmt,
- const char **sql_tail)
-{
- int rc = sql_stmt_compile(sql, length, 0, stmt, sql_tail);
- assert(rc == 0 || stmt == NULL || *stmt == NULL);
- return rc;
-}
-
void
sql_parser_create(struct Parse *parser, struct sql *db, uint32_t sql_flags)
{
diff --git a/test/box/misc.result b/test/box/misc.result
index 7e5d28b70..90923f28e 100644
--- a/test/box/misc.result
+++ b/test/box/misc.result
@@ -73,6 +73,7 @@ t
- on_commit
- on_rollback
- once
+ - prepare
- priv
- rollback
- rollback_to_savepoint
@@ -86,6 +87,7 @@ t
- space
- stat
- tuple
+ - unprepare
...
t = nil
---
@@ -555,6 +557,7 @@ t;
204: box.error.SQL_FUNC_WRONG_RET_COUNT
205: box.error.FUNC_INVALID_RETURN_TYPE
206: box.error.SQL_PREPARE
+ 207: box.error.WRONG_QUERY_ID
...
test_run:cmd("setopt delimiter ''");
---
diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg
index 284c42082..a1b4b0fc5 100644
--- a/test/sql/engine.cfg
+++ b/test/sql/engine.cfg
@@ -9,6 +9,9 @@
"remote": {"remote": "true"},
"local": {"remote": "false"}
},
+ "prepared.test.lua": {
+ "local": {"remote": "false"}
+ },
"*": {
"memtx": {"engine": "memtx"},
"vinyl": {"engine": "vinyl"}
diff --git a/test/sql/prepared.result b/test/sql/prepared.result
new file mode 100644
index 000000000..bd37cfdd7
--- /dev/null
+++ b/test/sql/prepared.result
@@ -0,0 +1,687 @@
+-- test-run result file version 2
+remote = require('net.box')
+ | ---
+ | ...
+test_run = require('test_run').new()
+ | ---
+ | ...
+fiber = require('fiber')
+ | ---
+ | ...
+
+-- Wrappers to make remote and local execution interface return
+-- same result pattern.
+--
+test_run:cmd("setopt delimiter ';'")
+ | ---
+ | - true
+ | ...
+execute = function(...)
+ local res, err = box.execute(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+ | ---
+ | ...
+prepare = function(...)
+ local res, err = box.prepare(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+ | ---
+ | ...
+unprepare = function(...)
+ local res, err = box.unprepare(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+ | ---
+ | ...
+
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
+ | ...
+
+-- Test local interface and basic capabilities of prepared statements.
+--
+execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)')
+ | ---
+ | - row_count: 1
+ | ...
+space = box.space.TEST
+ | ---
+ | ...
+space:replace{1, 2, '3'}
+ | ---
+ | - [1, 2, '3']
+ | ...
+space:replace{4, 5, '6'}
+ | ---
+ | - [4, 5, '6']
+ | ...
+space:replace{7, 8.5, '9'}
+ | ---
+ | - [7, 8.5, '9']
+ | ...
+s, e = prepare("SELECT * FROM test WHERE id = ? AND a = ?;")
+ | ---
+ | ...
+assert(e == nil)
+ | ---
+ | - true
+ | ...
+assert(s ~= nil)
+ | ---
+ | - true
+ | ...
+s.stmt_id
+ | ---
+ | - 3603193623
+ | ...
+s.metadata
+ | ---
+ | - - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | ...
+s.params
+ | ---
+ | - - name: '?'
+ | type: ANY
+ | - name: '?'
+ | type: ANY
+ | ...
+s.param_count
+ | ---
+ | - 2
+ | ...
+execute(s.stmt_id, {1, 2})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows:
+ | - [1, 2, '3']
+ | ...
+execute(s.stmt_id, {1, 3})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows: []
+ | ...
+s:execute({1, 2})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows:
+ | - [1, 2, '3']
+ | ...
+s:execute({1, 3})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows: []
+ | ...
+s:unprepare()
+ | ---
+ | ...
+
+-- Test preparation of different types of queries.
+-- Let's start from DDL. It doesn't make much sense since
+-- any prepared DDL statement can be executed once, but
+-- anyway make sure that no crashes occur.
+--
+s = prepare("CREATE INDEX i1 ON test(a)")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP INDEX i1 ON test;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP VIEW v;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 0
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+box.execute("CREATE TABLE test2 (id INT PRIMARY KEY);")
+ | ---
+ | - row_count: 1
+ | ...
+s = prepare("ALTER TABLE test2 ADD CONSTRAINT fk1 FOREIGN KEY (id) REFERENCES test2")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+box.space.TEST2:drop()
+ | ---
+ | ...
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP TRIGGER tr1;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP TABLE test1;")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- DQL
+--
+execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)')
+ | ---
+ | - row_count: 1
+ | ...
+space = box.space.TEST
+ | ---
+ | ...
+space:replace{1, 2, '3'}
+ | ---
+ | - [1, 2, '3']
+ | ...
+space:replace{4, 5, '6'}
+ | ---
+ | - [4, 5, '6']
+ | ...
+space:replace{7, 8.5, '9'}
+ | ---
+ | - [7, 8.5, '9']
+ | ...
+_ = prepare("SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+s = prepare("SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+s:execute()
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+s:execute()
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+ | ---
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - metadata:
+ | - name: count(*)
+ | type: integer
+ | - name: count(a - 3)
+ | type: integer
+ | - name: max(b)
+ | type: scalar
+ | - name: abs(id)
+ | type: number
+ | rows:
+ | - [1, 1, '3', 1]
+ | ...
+execute(s.stmt_id)
+ | ---
+ | - metadata:
+ | - name: count(*)
+ | type: integer
+ | - name: count(a - 3)
+ | type: integer
+ | - name: max(b)
+ | type: scalar
+ | - name: abs(id)
+ | type: number
+ | rows:
+ | - [1, 1, '3', 1]
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- Let's try something a bit more complicated. For instance recursive
+-- query displaying Mandelbrot set.
+--
+s = prepare([[WITH RECURSIVE \
+ xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2), \
+ yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0), \
+ m(iter, cx, cy, x, y) AS ( \
+ SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis \
+ UNION ALL \
+ SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \
+ WHERE (x*x + y*y) < 4.0 AND iter<28), \
+ m2(iter, cx, cy) AS ( \
+ SELECT max(iter), cx, cy FROM m GROUP BY cx, cy), \
+ a(t) AS ( \
+ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \
+ FROM m2 GROUP BY cy) \
+ SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]])
+ | ---
+ | ...
+
+res = execute(s.stmt_id)
+ | ---
+ | ...
+res.metadata
+ | ---
+ | - - name: group_concat(TRIM(TRAILING FROM t),x'0a')
+ | type: string
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+ | ---
+ | ...
+execute(s.stmt_id, {'6'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [5]
+ | ...
+execute(s.stmt_id, {'9'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [8.5]
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+ | ---
+ | ...
+execute(s.stmt_id, {5, 6, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id, {6, 10, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.stmt_id, {9, 11, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+unprepare(s.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+res = execute(s1.stmt_id)
+ | ---
+ | ...
+res.metadata
+ | ---
+ | - - name: addr
+ | type: integer
+ | - name: opcode
+ | type: text
+ | - name: p1
+ | type: integer
+ | - name: p2
+ | type: integer
+ | - name: p3
+ | type: integer
+ | - name: p4
+ | type: text
+ | - name: p5
+ | type: text
+ | - name: comment
+ | type: text
+ | ...
+assert(res.rows ~= nil)
+ | ---
+ | - true
+ | ...
+
+s2 = prepare("EXPLAIN QUERY PLAN SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+res = execute(s2.stmt_id)
+ | ---
+ | ...
+res.metadata
+ | ---
+ | - - name: selectid
+ | type: integer
+ | - name: order
+ | type: integer
+ | - name: from
+ | type: integer
+ | - name: detail
+ | type: text
+ | ...
+assert(res.rows ~= nil)
+ | ---
+ | - true
+ | ...
+
+s3 = prepare("PRAGMA count_changes;")
+ | ---
+ | ...
+execute(s3.stmt_id)
+ | ---
+ | - metadata:
+ | - name: defer_foreign_keys
+ | type: integer
+ | rows:
+ | - [0]
+ | ...
+
+unprepare(s2.stmt_id)
+ | ---
+ | - null
+ | ...
+unprepare(s3.stmt_id)
+ | ---
+ | - null
+ | ...
+unprepare(s1.stmt_id)
+ | ---
+ | - null
+ | ...
+
+-- Setting cache size to 0 is possible only in case if
+-- there's no any prepared statements right now .
+--
+box.cfg{sql_cache_size = 0}
+ | ---
+ | ...
+prepare("SELECT a FROM test;")
+ | ---
+ | - error: 'Failed to prepare SQL statement: Memory limit for SQL prepared statements
+ | has been reached. Please, deallocate active statements or increase SQL cache size.'
+ | ...
+box.cfg{sql_cache_size = 0}
+ | ---
+ | ...
+
+-- Still with small size everything should work.
+--
+box.cfg{sql_cache_size = 1500}
+ | ---
+ | ...
+
+test_run:cmd("setopt delimiter ';'");
+ | ---
+ | - true
+ | ...
+ok = nil
+res = nil
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, string.format("SELECT * FROM test WHERE a = %d;", i))
+ end
+ ok, res = pcall(prepare, "SELECT * FROM test WHERE b = '3';")
+end);
+ | ---
+ | ...
+while ok == nil do fiber.sleep(0.00001) end;
+ | ---
+ | ...
+assert(ok == false);
+ | ---
+ | - true
+ | ...
+res;
+ | ---
+ | - 'Failed to prepare SQL statement: Memory limit for SQL prepared statements has been
+ | reached. Please, deallocate active statements or increase SQL cache size.'
+ | ...
+
+-- Check that after fiber is dead, its session gets rid of
+-- all prepared statements.
+--
+box.cfg{sql_cache_size = 0};
+ | ---
+ | ...
+box.cfg{sql_cache_size = 3000};
+ | ---
+ | ...
+
+-- Make sure that if prepared statement is busy (is executed
+-- right now), prepared statement is not used, i.e. statement
+-- is compiled from scratch, executed and finilized.
+--
+box.schema.func.create('SLEEP', {language = 'Lua',
+ body = 'function () fiber.sleep(0.1) return 1 end',
+ exports = {'LUA', 'SQL'}});
+ | ---
+ | ...
+
+s = prepare("SELECT id, SLEEP() FROM test;");
+ | ---
+ | ...
+assert(s ~= nil);
+ | ---
+ | - true
+ | ...
+
+function implicit_yield()
+ prepare("SELECT id, SLEEP() FROM test;")
+ execute("SELECT id, SLEEP() FROM test;")
+end;
+ | ---
+ | ...
+
+f1 = fiber.new(implicit_yield)
+f2 = fiber.new(implicit_yield)
+f1:set_joinable(true)
+f2:set_joinable(true)
+
+f1:join();
+ | ---
+ | ...
+f2:join();
+ | ---
+ | - true
+ | ...
+
+unprepare(s.stmt_id);
+ | ---
+ | - null
+ | ...
+
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
+ | ...
+
+box.cfg{sql_cache_size = 5 * 1024 * 1024}
+ | ---
+ | ...
+box.space.TEST:drop()
+ | ---
+ | ...
+box.schema.func.drop('SLEEP')
+ | ---
+ | ...
diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
new file mode 100644
index 000000000..49d2fb3ae
--- /dev/null
+++ b/test/sql/prepared.test.lua
@@ -0,0 +1,240 @@
+remote = require('net.box')
+test_run = require('test_run').new()
+fiber = require('fiber')
+
+-- Wrappers to make remote and local execution interface return
+-- same result pattern.
+--
+test_run:cmd("setopt delimiter ';'")
+execute = function(...)
+ local res, err = box.execute(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+prepare = function(...)
+ local res, err = box.prepare(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+unprepare = function(...)
+ local res, err = box.unprepare(...)
+ if err ~= nil then
+ error(err)
+ end
+ return res
+end;
+
+test_run:cmd("setopt delimiter ''");
+
+-- Test local interface and basic capabilities of prepared statements.
+--
+execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)')
+space = box.space.TEST
+space:replace{1, 2, '3'}
+space:replace{4, 5, '6'}
+space:replace{7, 8.5, '9'}
+s, e = prepare("SELECT * FROM test WHERE id = ? AND a = ?;")
+assert(e == nil)
+assert(s ~= nil)
+s.stmt_id
+s.metadata
+s.params
+s.param_count
+execute(s.stmt_id, {1, 2})
+execute(s.stmt_id, {1, 3})
+s:execute({1, 2})
+s:execute({1, 3})
+s:unprepare()
+
+-- Test preparation of different types of queries.
+-- Let's start from DDL. It doesn't make much sense since
+-- any prepared DDL statement can be executed once, but
+-- anyway make sure that no crashes occur.
+--
+s = prepare("CREATE INDEX i1 ON test(a)")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("DROP INDEX i1 ON test;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("DROP VIEW v;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+box.execute("CREATE TABLE test2 (id INT PRIMARY KEY);")
+s = prepare("ALTER TABLE test2 ADD CONSTRAINT fk1 FOREIGN KEY (id) REFERENCES test2")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+box.space.TEST2:drop()
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("DROP TRIGGER tr1;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+s = prepare("DROP TABLE test1;")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+-- DQL
+--
+execute('CREATE TABLE test (id INT PRIMARY KEY, a NUMBER, b TEXT)')
+space = box.space.TEST
+space:replace{1, 2, '3'}
+space:replace{4, 5, '6'}
+space:replace{7, 8.5, '9'}
+_ = prepare("SELECT a FROM test WHERE b = '3';")
+s = prepare("SELECT a FROM test WHERE b = '3';")
+execute(s.stmt_id)
+execute(s.stmt_id)
+s:execute()
+s:execute()
+unprepare(s.stmt_id)
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+execute(s.stmt_id)
+execute(s.stmt_id)
+unprepare(s.stmt_id)
+
+-- Let's try something a bit more complicated. For instance recursive
+-- query displaying Mandelbrot set.
+--
+s = prepare([[WITH RECURSIVE \
+ xaxis(x) AS (VALUES(-2.0) UNION ALL SELECT x+0.05 FROM xaxis WHERE x<1.2), \
+ yaxis(y) AS (VALUES(-1.0) UNION ALL SELECT y+0.1 FROM yaxis WHERE y<1.0), \
+ m(iter, cx, cy, x, y) AS ( \
+ SELECT 0, x, y, 0.0, 0.0 FROM xaxis, yaxis \
+ UNION ALL \
+ SELECT iter+1, cx, cy, x*x-y*y + cx, 2.0*x*y + cy FROM m \
+ WHERE (x*x + y*y) < 4.0 AND iter<28), \
+ m2(iter, cx, cy) AS ( \
+ SELECT max(iter), cx, cy FROM m GROUP BY cx, cy), \
+ a(t) AS ( \
+ SELECT group_concat( substr(' .+*#', 1+LEAST(iter/7,4), 1), '') \
+ FROM m2 GROUP BY cy) \
+ SELECT group_concat(TRIM(TRAILING FROM t),x'0a') FROM a;]])
+
+res = execute(s.stmt_id)
+res.metadata
+unprepare(s.stmt_id)
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+execute(s.stmt_id, {'6'})
+execute(s.stmt_id, {'9'})
+unprepare(s.stmt_id)
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+execute(s.stmt_id, {5, 6, '7'})
+execute(s.stmt_id, {6, 10, '7'})
+execute(s.stmt_id, {9, 11, '7'})
+unprepare(s.stmt_id)
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+res = execute(s1.stmt_id)
+res.metadata
+assert(res.rows ~= nil)
+
+s2 = prepare("EXPLAIN QUERY PLAN SELECT a FROM test WHERE b = '3';")
+res = execute(s2.stmt_id)
+res.metadata
+assert(res.rows ~= nil)
+
+s3 = prepare("PRAGMA count_changes;")
+execute(s3.stmt_id)
+
+unprepare(s2.stmt_id)
+unprepare(s3.stmt_id)
+unprepare(s1.stmt_id)
+
+-- Setting cache size to 0 is possible only in case if
+-- there's no any prepared statements right now .
+--
+box.cfg{sql_cache_size = 0}
+prepare("SELECT a FROM test;")
+box.cfg{sql_cache_size = 0}
+
+-- Still with small size everything should work.
+--
+box.cfg{sql_cache_size = 1500}
+
+test_run:cmd("setopt delimiter ';'");
+ok = nil
+res = nil
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, string.format("SELECT * FROM test WHERE a = %d;", i))
+ end
+ ok, res = pcall(prepare, "SELECT * FROM test WHERE b = '3';")
+end);
+while ok == nil do fiber.sleep(0.00001) end;
+assert(ok == false);
+res;
+
+-- Check that after fiber is dead, its session gets rid of
+-- all prepared statements.
+--
+box.cfg{sql_cache_size = 0};
+box.cfg{sql_cache_size = 3000};
+
+-- Make sure that if prepared statement is busy (is executed
+-- right now), prepared statement is not used, i.e. statement
+-- is compiled from scratch, executed and finilized.
+--
+box.schema.func.create('SLEEP', {language = 'Lua',
+ body = 'function () fiber.sleep(0.1) return 1 end',
+ exports = {'LUA', 'SQL'}});
+
+s = prepare("SELECT id, SLEEP() FROM test;");
+assert(s ~= nil);
+
+function implicit_yield()
+ prepare("SELECT id, SLEEP() FROM test;")
+ execute("SELECT id, SLEEP() FROM test;")
+end;
+
+f1 = fiber.new(implicit_yield)
+f2 = fiber.new(implicit_yield)
+f1:set_joinable(true)
+f2:set_joinable(true)
+
+f1:join();
+f2:join();
+
+unprepare(s.stmt_id);
+
+test_run:cmd("setopt delimiter ''");
+
+box.cfg{sql_cache_size = 5 * 1024 * 1024}
+box.space.TEST:drop()
+box.schema.func.drop('SLEEP')
--
2.15.1
More information about the Tarantool-patches
mailing list