[Tarantool-patches] [PATCH 14/15] box: introduce prepared statements
Nikita Pettik
korablev at tarantool.org
Thu Nov 7 04:04:54 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 query 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() call also features two methods
- :unprepare() and :execute(). First one corresponds to
box.unprepare(stmt.query_id), i.e. automatically substitutes id of prepared
statement to be unprepared. After "unpreparation" statement is removed
from cache and all resources related to it are released.
:execute() method does the same - it unfolds into box.execute(stmt.id).
Query ids are local to each session, sequential and start from 0.
After schema changes all prepared statement located in cache are
considered to be expired - cache should be erased and all statements
re-prepared. It can be done with box.session.sql_cache_erase().
It is worth noting that box.execute() now accepts two types of first
parameter: in case it is string then original :execute() method is
called (which treats it as string containing SQL statement to be
compiled and executed); if it is number - it is supposed to be id of
prepared statement to be executed.
SQL cache memory limit is regulated by box{sql_cache_size} which can be
set dynamically.
Part of #2592
---
src/box/errcode.h | 1 +
src/box/execute.c | 72 ++++-
src/box/execute.h | 29 +-
src/box/lua/execute.c | 211 ++++++++++++++-
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 | 654 +++++++++++++++++++++++++++++++++++++++++++++
test/sql/prepared.test.lua | 222 +++++++++++++++
11 files changed, 1184 insertions(+), 24 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..6ecd37d75 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 corresponding id %d 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 d2a999099..3fd1afd8a 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -45,6 +45,7 @@
#include "tuple.h"
#include "sql/vdbe.h"
#include "box/lua/execute.h"
+#include "box/prep_stmt.h"
const char *sql_info_key_strs[] = {
"row_count",
@@ -411,6 +412,35 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out)
return 0;
}
+int
+sql_prepare(const char *sql, int len, struct port *port)
+{
+ struct sql_stmt *stmt;
+ if (sql_compile(sql, len, NULL, &stmt, NULL) != 0)
+ return -1;
+ uint32_t stmt_id;
+ if (sql_prepared_stmt_cache_insert(stmt, &stmt_id) != 0)
+ return -1;
+ enum sql_dump_format dump_format = sql_column_count(stmt) > 0 ?
+ DQL_PREPARE : DML_PREPARE;
+ port_sql_create(port, stmt, dump_format, PREPARE);
+ ((struct port_sql *)port)->query_id = stmt_id;
+
+ return 0;
+}
+
+int
+sql_unprepare(uint32_t query_id)
+{
+ struct sql_stmt *stmt = sql_prepared_stmt_cache_find(query_id);
+ if (stmt == NULL) {
+ diag_set(ClientError, ER_WRONG_QUERY_ID, query_id);
+ return -1;
+ }
+ sql_prepared_stmt_cache_delete(stmt, query_id);
+ return 0;
+}
+
/**
* Execute prepared SQL statement.
*
@@ -426,7 +456,7 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out)
* @retval 0 Success.
* @retval -1 Error.
*/
-static inline int
+static int
sql_execute(struct sql_stmt *stmt, struct port *port, struct region *region)
{
int rc, column_count = sql_column_count(stmt);
@@ -448,6 +478,46 @@ sql_execute(struct sql_stmt *stmt, struct port *port, struct region *region)
return 0;
}
+int
+sql_execute_prepared(uint32_t query_id, const struct sql_bind *bind,
+ uint32_t bind_count, struct port *port,
+ struct region *region)
+{
+ struct sql_stmt *stmt = sql_prepared_stmt_cache_find(query_id);
+ if (stmt == NULL) {
+ diag_set(ClientError, ER_WRONG_QUERY_ID, query_id);
+ return -1;
+ }
+ if (sql_schema_version(stmt) != box_schema_version()) {
+ diag_set(ClientError, ER_SQL_EXECUTE, "statement has expired");
+ return -1;
+ }
+ /*
+ * Each fiber has its own session so even if during statement
+ * execution yield occurs, another fiber can't access this
+ * particular statement.
+ */
+ if (sql_stmt_busy(stmt)) {
+ panic("Prepared statement is malformed: "
+ "its status is RUN before execution");
+ }
+ if (sql_bind(stmt, bind, bind_count) != 0)
+ return -1;
+ enum sql_dump_format dump_format = sql_column_count(stmt) > 0 ?
+ DQL_EXECUTE : DML_EXECUTE;
+ port_sql_create(port, stmt, dump_format, EXECUTE_PREPARED);
+ if (sql_bind(stmt, bind, bind_count) != 0)
+ return -1;
+ if (sql_execute(stmt, port, region) != 0) {
+ port_destroy(port);
+ sql_reset(stmt);
+ return -1;
+ }
+ sql_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 d5b4d8421..ef2648b34 100644
--- a/src/box/execute.h
+++ b/src/box/execute.h
@@ -109,6 +109,11 @@ struct port_sql {
uint8_t dump_format;
/** enum sql_request_type */
uint8_t request;
+ /**
+ * In case of "prepare" request user receives id of query
+ * using which query can be executed later.
+ */
+ uint32_t query_id;
};
extern const struct port_vtab port_sql_vtab;
@@ -128,13 +133,27 @@ sql_stmt_sizeof(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 len, struct port *port);
+
+/**
+ * Remove entry from cache and release any occupied resources.
+ * In case of wrong query id the error is raised.
+ */
+int
+sql_unprepare(uint32_t query_id);
+
+/**
+ * Execute prepared statement via given id. At the end statement
+ * is not destroyed. Otherwise this function is similar to sql_execute().
*/
int
-sql_prepare(const char *sql, int length, struct sql_stmt **stmt,
- const char **sql_tail);
+sql_execute_prepared(uint32_t query_id, const struct sql_bind *bind,
+ uint32_t bind_count, struct port *port,
+ struct region *region);
#if defined(__cplusplus)
} /* extern "C" { */
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index 1b2f8d235..981f4e325 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/prep_stmt.h"
+#include "box/schema.h"
/**
* Serialize a description of the prepared statement.
@@ -38,6 +40,102 @@ 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, "query_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 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 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 two ways: as member of prepared
+ * statement handle ()
+ */
+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(query_id)");
+ }
+ lua_Integer query_id;
+ if (lua_istable(L, 1)) {
+ lua_getfield(L, -1, "query_id");
+ if (! lua_isnumber(L, -1)) {
+ return luaL_error(L, "Query id is expected "\
+ " to be numeric");
+ }
+ query_id = lua_tointeger(L, -1);
+ lua_pop(L, 1);
+ } else {
+ query_id = lua_tonumber(L, 1);
+ }
+ if (query_id < 0)
+ return luaL_error(L, "Query id can't be negative");
+ if (sql_unprepare((uint32_t) query_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,6 +180,61 @@ port_sql_dump_lua(struct port *port, struct lua_State *L, bool is_flat)
}
break;
}
+ case DQL_PREPARE: {
+ /* Format is following:
+ * query_id,
+ * param_count,
+ * params {name, type},
+ * metadata {name, type}
+ * execute(), unprepare()
+ */
+ lua_createtable(L, 0, 6);
+ /* query_id */
+ luaL_pushuint64(L, port_sql->query_id);
+ lua_setfield(L, -2, "query_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:
+ * query_id,
+ * param_count,
+ * params {name, type},
+ * execute(), unprepare()
+ */
+ lua_createtable(L, 0, 5);
+ /* query_id */
+ luaL_pushuint64(L, port_sql->query_id);
+ lua_setfield(L, -2, "query_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();
}
}
@@ -251,10 +404,10 @@ lbox_execute(struct lua_State *L)
struct port port;
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);
+ if ((top != 1 && top != 2) || ! lua_isstring(L, 1)) {
+ return luaL_error(L, "Usage: box.execute(sqlstring[, params]) "
+ "or box.execute(query_id[, params])");
+ }
if (top == 2) {
if (! lua_istable(L, 2))
@@ -263,9 +416,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, "Query 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[, params])");
+
+ 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);
@@ -273,11 +461,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 780085e56..61a50cbdc 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_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 78ffbf1dc..b75f615d5 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
---
@@ -536,6 +538,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..1646af94f
--- /dev/null
+++ b/test/sql/prepared.result
@@ -0,0 +1,654 @@
+-- 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.query_id
+ | ---
+ | - 0
+ | ...
+s.metadata
+ | ---
+ | - - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | ...
+s.params
+ | ---
+ | - - name: '?'
+ | type: ANY
+ | - name: '?'
+ | type: ANY
+ | ...
+s.params_count
+ | ---
+ | - null
+ | ...
+execute(s.query_id, {1, 2})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows:
+ | - [1, 2, '3']
+ | ...
+execute(s.query_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.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP INDEX i1 ON test;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP VIEW v;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 0
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_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.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+box.space.TEST2:drop()
+ | ---
+ | ...
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP TRIGGER tr1;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("DROP TABLE test1;")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id)
+ | ---
+ | - error: 'Failed to execute SQL statement: statement has expired'
+ | ...
+unprepare(s.query_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']
+ | ...
+s = prepare("SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+execute(s.query_id)
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+execute(s.query_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.query_id)
+ | ---
+ | - null
+ | ...
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+ | ---
+ | ...
+execute(s.query_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.query_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.query_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.query_id)
+ | ---
+ | ...
+res.metadata
+ | ---
+ | - - name: group_concat(TRIM(TRAILING FROM t),x'0a')
+ | type: string
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+ | ---
+ | ...
+execute(s.query_id, {'6'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [5]
+ | ...
+execute(s.query_id, {'9'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [8.5]
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+ | ---
+ | ...
+execute(s.query_id, {5, 6, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id, {6, 10, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.query_id, {9, 11, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+unprepare(s.query_id)
+ | ---
+ | - null
+ | ...
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+res = execute(s1.query_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.query_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.query_id)
+ | ---
+ | - metadata:
+ | - name: defer_foreign_keys
+ | type: INTEGER
+ | rows:
+ | - [0]
+ | ...
+
+unprepare(s3.query_id)
+ | ---
+ | - null
+ | ...
+unprepare(s2.query_id)
+ | ---
+ | - null
+ | ...
+unprepare(s1.query_id)
+ | ---
+ | - null
+ | ...
+
+-- Make sure cache memory limit can't be exceeed. We have to
+-- create separate fiber (in local mode) since cache is local
+-- to session. After cache creation its size is fixed and can't
+-- be reconfigured. Also test that ids in each session start from 0.
+--
+test_run:cmd("setopt delimiter ';'")
+ | ---
+ | - true
+ | ...
+box.cfg{sql_cache_size = 3000}
+res = nil;
+ | ---
+ | ...
+_ = fiber.create(function()
+ s = prepare("SELECT * FROM test;")
+ res = s.query_id
+end);
+ | ---
+ | ...
+while res == nil do fiber.sleep(0.00001) end;
+ | ---
+ | ...
+assert(res == 0);
+ | ---
+ | - true
+ | ...
+
+ok = nil
+res = nil
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, "SELECT * FROM test;")
+ end
+ ok, res = pcall(prepare, "SELECT * FROM test;")
+end);
+ | ---
+ | ...
+while ok == nil do fiber.sleep(0.00001) end;
+ | ---
+ | ...
+assert(ok == false);
+ | ---
+ | - true
+ | ...
+res;
+ | ---
+ | - 'Failed to prepare SQL statement: prepared statement cache is full'
+ | ...
+
+-- Make sure cache can be purged with box.session.sql_cache_erase()
+--
+res = nil;
+ | ---
+ | ...
+ok = nil;
+ | ---
+ | ...
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, "SELECT * FROM test;")
+ end
+ box.session.sql_cache_erase()
+ ok, res = pcall(prepare, "SELECT * FROM test;")
+end);
+ | ---
+ | ...
+while ok == nil do fiber.sleep(0.00001) end;
+ | ---
+ | ...
+assert(ok == true);
+ | ---
+ | - true
+ | ...
+assert(res ~= nil);
+ | ---
+ | - true
+ | ...
+
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
+ | ...
+
+box.space.TEST:drop()
+ | ---
+ | ...
diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua
new file mode 100644
index 000000000..680f0bdb3
--- /dev/null
+++ b/test/sql/prepared.test.lua
@@ -0,0 +1,222 @@
+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.query_id
+s.metadata
+s.params
+s.params_count
+execute(s.query_id, {1, 2})
+execute(s.query_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.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("DROP INDEX i1 ON test;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("DROP VIEW v;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_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.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+box.space.TEST2:drop()
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("DROP TRIGGER tr1;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_id)
+
+s = prepare("DROP TABLE test1;")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_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'}
+s = prepare("SELECT a FROM test WHERE b = '3';")
+execute(s.query_id)
+execute(s.query_id)
+s:execute()
+s:execute()
+unprepare(s.query_id)
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+execute(s.query_id)
+execute(s.query_id)
+unprepare(s.query_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.query_id)
+res.metadata
+unprepare(s.query_id)
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+execute(s.query_id, {'6'})
+execute(s.query_id, {'9'})
+unprepare(s.query_id)
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+execute(s.query_id, {5, 6, '7'})
+execute(s.query_id, {6, 10, '7'})
+execute(s.query_id, {9, 11, '7'})
+unprepare(s.query_id)
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+res = execute(s1.query_id)
+res.metadata
+assert(res.rows ~= nil)
+
+s2 = prepare("EXPLAIN QUERY PLAN SELECT a FROM test WHERE b = '3';")
+res = execute(s2.query_id)
+res.metadata
+assert(res.rows ~= nil)
+
+s3 = prepare("PRAGMA count_changes;")
+execute(s3.query_id)
+
+unprepare(s3.query_id)
+unprepare(s2.query_id)
+unprepare(s1.query_id)
+
+-- Make sure cache memory limit can't be exceeed. We have to
+-- create separate fiber (in local mode) since cache is local
+-- to session. After cache creation its size is fixed and can't
+-- be reconfigured. Also test that ids in each session start from 0.
+--
+test_run:cmd("setopt delimiter ';'")
+box.cfg{sql_cache_size = 3000}
+res = nil;
+_ = fiber.create(function()
+ s = prepare("SELECT * FROM test;")
+ res = s.query_id
+end);
+while res == nil do fiber.sleep(0.00001) end;
+assert(res == 0);
+
+ok = nil
+res = nil
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, "SELECT * FROM test;")
+ end
+ ok, res = pcall(prepare, "SELECT * FROM test;")
+end);
+while ok == nil do fiber.sleep(0.00001) end;
+assert(ok == false);
+res;
+
+-- Make sure cache can be purged with box.session.sql_cache_erase()
+--
+res = nil;
+ok = nil;
+_ = fiber.create(function()
+ for i = 1, 5 do
+ pcall(prepare, "SELECT * FROM test;")
+ end
+ box.session.sql_cache_erase()
+ ok, res = pcall(prepare, "SELECT * FROM test;")
+end);
+while ok == nil do fiber.sleep(0.00001) end;
+assert(ok == true);
+assert(res ~= nil);
+
+test_run:cmd("setopt delimiter ''");
+
+box.space.TEST:drop()
--
2.15.1
More information about the Tarantool-patches
mailing list