[Tarantool-patches] [PATCH v2 15/16] box: introduce prepared statements
Nikita Pettik
korablev at tarantool.org
Thu Nov 21 00:28:14 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 string, 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 method
:execute(). It corresponds to box.execute(stmt.sql_str), i.e. automatically
substitutes 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 are re-prepared automatically on demand.
It is worth noting that box.execute() always attempts at finding
statement to be executed in prepared statement cache. Thus, once statement
is prepared, both box.execute() and :execute() methods will execute
already compiled statement.
SQL cache memory limit is regulated by box{sql_cache_size} which can be
set dynamically. Setting it to 0 completely erases cache.
Part of #2592
---
src/box/execute.c | 90 +++++++
src/box/execute.h | 8 +-
src/box/lua/execute.c | 141 ++++++++++-
src/box/lua/execute.h | 2 +-
src/box/lua/init.c | 2 +-
src/box/sql/prepare.c | 9 -
test/box/misc.result | 1 +
test/sql/engine.cfg | 3 +
test/sql/prepared.result | 574 +++++++++++++++++++++++++++++++++++++++++++++
test/sql/prepared.test.lua | 196 ++++++++++++++++
10 files changed, 1009 insertions(+), 17 deletions(-)
create mode 100644 test/sql/prepared.result
create mode 100644 test/sql/prepared.test.lua
diff --git a/src/box/execute.c b/src/box/execute.c
index d2a999099..2b5a9ba90 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,59 @@ 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();
+}
+
+static int
+sql_reprepare(struct stmt_cache_node **node)
+{
+ struct sql_stmt *stmt = (*node)->stmt;
+ const char *sql_str = sql_stmt_query_str(stmt);
+ struct sql_stmt *fresh_stmt;
+ if (sql_compile(sql_str, strlen(sql_str), NULL,
+ &fresh_stmt, NULL) != 0)
+ return -1;
+ sql_prepared_stmt_cache_delete(*node);
+ if (sql_prepared_stmt_cache_insert(fresh_stmt) != 0) {
+ sql_finalize(fresh_stmt);
+ return -1;
+ }
+ sql_str = sql_stmt_query_str(fresh_stmt);
+ *node = sql_prepared_stmt_cache_find(sql_str);
+ return 0;
+}
+
+int
+sql_prepare(const char *sql, int len, struct port *port)
+{
+ struct stmt_cache_node *stmt_node = sql_prepared_stmt_cache_find(sql);
+ struct sql_stmt *stmt;
+ if (stmt_node == NULL) {
+ if (sql_compile(sql, len, NULL, &stmt, NULL) != 0)
+ return -1;
+ if (sql_prepared_stmt_cache_insert(stmt) != 0) {
+ sql_finalize(stmt);
+ return -1;
+ }
+ } else {
+ if (! sql_stmt_check_schema_version(stmt_node->stmt)) {
+ if (sql_reprepare(&stmt_node) != 0)
+ return -1;
+ } else {
+ sql_cache_stmt_refresh(stmt_node);
+ }
+ stmt = stmt_node->stmt;
+ }
+ enum sql_dump_format dump_format = sql_column_count(stmt) > 0 ?
+ DQL_PREPARE : DML_PREPARE;
+ port_sql_create(port, stmt, dump_format, PREPARE);
+
+ return 0;
+}
+
/**
* Execute prepared SQL statement.
*
@@ -448,11 +502,47 @@ sql_execute(struct sql_stmt *stmt, struct port *port, struct region *region)
return 0;
}
+static int
+sql_execute_prepared(struct sql_stmt *stmt, const struct sql_bind *bind,
+ uint32_t bind_count, struct port *port,
+ struct region *region)
+{
+ 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_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,
struct region *region)
{
+ struct stmt_cache_node *stmt_node = sql_prepared_stmt_cache_find(sql);
+ if (stmt_node != NULL) {
+ if (! sql_stmt_check_schema_version(stmt_node->stmt)) {
+ if (sql_reprepare(&stmt_node) != 0)
+ return -1;
+ }
+ if (! sql_stmt_busy(stmt_node->stmt)) {
+ return sql_execute_prepared(stmt_node->stmt, bind,
+ bind_count, port, region);
+ }
+ }
+ /*
+ * In case statement is evicted from cache or it is executed
+ * right now by another fiber, EXECUTE_PREPARED request results
+ * in casual PREPARE + EXECUTE.
+ */
struct sql_stmt *stmt;
if (sql_compile(sql, len, NULL, &stmt, NULL) != 0)
return -1;
diff --git a/src/box/execute.h b/src/box/execute.h
index 16b424d36..176966716 100644
--- a/src/box/execute.h
+++ b/src/box/execute.h
@@ -134,13 +134,11 @@ sql_stmt_query_str(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 1b2f8d235..8d46399d4 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,68 @@ 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, "sql_str");
+ if (!lua_isstring(L, -1))
+ return luaL_error(L, "Query is expected to be string");
+ lua_remove(L, 1);
+ if (top == 2) {
+ /*
+ * Stack state (before remove operation):
+ * 1 Prepared statement object (Lua table)
+ * 2 Bindings (Lua table)
+ * 3 SQL string (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 SQL string
+ * 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);
+}
+
void
port_sql_dump_lua(struct port *port, struct lua_State *L, bool is_flat)
{
@@ -82,6 +146,55 @@ 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 */
+ lua_pushstring(L, sql_stmt_query_str(port_sql->stmt));
+ lua_setfield(L, -2, "sql_str");
+ /* 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");
+ 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 */
+ lua_pushstring(L, sql_stmt_query_str(port_sql->stmt));
+ lua_setfield(L, -2, "sql_str");
+ /* 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");
+ break;
+ }
default: unreachable();
}
}
@@ -272,12 +385,38 @@ lbox_execute(struct lua_State *L)
return 1;
}
+/**
+ * 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);
+ return 1;
+}
+
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_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 47e40223d..182c154d5 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..ce66e1bfe 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
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..3321f41f3
--- /dev/null
+++ b/test/sql/prepared.result
@@ -0,0 +1,574 @@
+-- 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;
+ | ---
+ | ...
+
+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.sql_str
+ | ---
+ | - SELECT * FROM test WHERE id = ? AND a = ?;
+ | ...
+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.sql_str, {1, 2})
+ | ---
+ | - metadata:
+ | - name: ID
+ | type: integer
+ | - name: A
+ | type: number
+ | - name: B
+ | type: string
+ | rows:
+ | - [1, 2, '3']
+ | ...
+execute(s.sql_str, {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: []
+ | ...
+
+-- 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.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Index 'I1' already exists in space 'TEST'
+ | ...
+
+s = prepare("DROP INDEX i1 ON test;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: No index 'I1' is defined in space 'TEST'
+ | ...
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Space 'V' already exists
+ | ...
+
+s = prepare("DROP VIEW v;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Space 'V' does not exist
+ | ...
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 0
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Space 'TEST1' already exists
+ | ...
+
+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.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Constraint FK1 already exists
+ | ...
+box.space.TEST2:drop()
+ | ---
+ | ...
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Trigger 'TR1' already exists
+ | ...
+
+s = prepare("DROP TRIGGER tr1;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Trigger 'TR1' doesn't exist
+ | ...
+
+s = prepare("DROP TABLE test1;")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str)
+ | ---
+ | - error: Space 'TEST1' does not exist
+ | ...
+
+-- 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.sql_str)
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [2]
+ | ...
+execute(s.sql_str)
+ | ---
+ | - 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]
+ | ...
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+ | ---
+ | ...
+execute(s.sql_str)
+ | ---
+ | - 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.sql_str)
+ | ---
+ | - 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]
+ | ...
+
+-- 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.sql_str)
+ | ---
+ | ...
+res.metadata
+ | ---
+ | - - name: group_concat(TRIM(TRAILING FROM t),x'0a')
+ | type: string
+ | ...
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+ | ---
+ | ...
+execute(s.sql_str, {'6'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [5]
+ | ...
+execute(s.sql_str, {'9'})
+ | ---
+ | - metadata:
+ | - name: A
+ | type: number
+ | rows:
+ | - [8.5]
+ | ...
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+ | ---
+ | ...
+execute(s.sql_str, {5, 6, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str, {6, 10, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+execute(s.sql_str, {9, 11, '7'})
+ | ---
+ | - row_count: 1
+ | ...
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+ | ---
+ | ...
+res = execute(s1.sql_str)
+ | ---
+ | ...
+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.sql_str)
+ | ---
+ | ...
+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.sql_str)
+ | ---
+ | - metadata:
+ | - name: defer_foreign_keys
+ | type: integer
+ | rows:
+ | - [0]
+ | ...
+
+-- Setting cache size to 0 erases all content from it.
+--
+box.cfg{sql_cache_size = 0}
+ | ---
+ | ...
+s = prepare("SELECT a FROM test;")
+ | ---
+ | - error: 'Failed to prepare SQL statement: size of statement exceeds cache memory
+ | limit. Please, increase SQL cache size'
+ | ...
+assert(s ~= nil)
+ | ---
+ | - true
+ | ...
+
+-- Still with small size everything should work.
+--
+box.cfg{sql_cache_size = 1500}
+ | ---
+ | ...
+
+test_run:cmd("setopt delimiter ';'");
+ | ---
+ | - true
+ | ...
+for i = 1, 5 do
+ pcall(prepare, string.format("SELECT * FROM test WHERE id = %d;", i))
+end;
+ | ---
+ | ...
+s = prepare("SELECT a FROM test");
+ | ---
+ | ...
+assert(s ~= nil);
+ | ---
+ | - true
+ | ...
+
+-- 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()
+ 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
+ | ...
+
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
+ | ...
+
+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..24e49832f
--- /dev/null
+++ b/test/sql/prepared.test.lua
@@ -0,0 +1,196 @@
+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;
+
+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.sql_str
+s.metadata
+s.params
+s.param_count
+execute(s.sql_str, {1, 2})
+execute(s.sql_str, {1, 3})
+s:execute({1, 2})
+s:execute({1, 3})
+
+-- 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.sql_str)
+execute(s.sql_str)
+
+s = prepare("DROP INDEX i1 ON test;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+s = prepare("CREATE VIEW v AS SELECT * FROM test;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+s = prepare("DROP VIEW v;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+s = prepare("ALTER TABLE test RENAME TO test1")
+execute(s.sql_str)
+execute(s.sql_str)
+
+box.execute("CREATE TABLE test2 (id INT PRIMARY KEY);")
+s = prepare("ALTER TABLE test2 ADD CONSTRAINT fk1 FOREIGN KEY (id) REFERENCES test2")
+execute(s.sql_str)
+execute(s.sql_str)
+box.space.TEST2:drop()
+
+s = prepare("CREATE TRIGGER tr1 INSERT ON test1 FOR EACH ROW BEGIN DELETE FROM test1; END;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+s = prepare("DROP TRIGGER tr1;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+s = prepare("DROP TABLE test1;")
+execute(s.sql_str)
+execute(s.sql_str)
+
+-- 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.sql_str)
+execute(s.sql_str)
+s:execute()
+s:execute()
+
+s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';")
+execute(s.sql_str)
+execute(s.sql_str)
+
+-- 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.sql_str)
+res.metadata
+
+-- Workflow with bindings is still the same.
+--
+s = prepare("SELECT a FROM test WHERE b = ?;")
+execute(s.sql_str, {'6'})
+execute(s.sql_str, {'9'})
+
+-- DML
+s = prepare("INSERT INTO test VALUES (?, ?, ?);")
+execute(s.sql_str, {5, 6, '7'})
+execute(s.sql_str, {6, 10, '7'})
+execute(s.sql_str, {9, 11, '7'})
+
+-- EXPLAIN and PRAGMA work fine as well.
+--
+s1 = prepare("EXPLAIN SELECT a FROM test WHERE b = '3';")
+res = execute(s1.sql_str)
+res.metadata
+assert(res.rows ~= nil)
+
+s2 = prepare("EXPLAIN QUERY PLAN SELECT a FROM test WHERE b = '3';")
+res = execute(s2.sql_str)
+res.metadata
+assert(res.rows ~= nil)
+
+s3 = prepare("PRAGMA count_changes;")
+execute(s3.sql_str)
+
+-- Setting cache size to 0 erases all content from it.
+--
+box.cfg{sql_cache_size = 0}
+s = prepare("SELECT a FROM test;")
+assert(s ~= nil)
+
+-- Still with small size everything should work.
+--
+box.cfg{sql_cache_size = 1500}
+
+test_run:cmd("setopt delimiter ';'");
+for i = 1, 5 do
+ pcall(prepare, string.format("SELECT * FROM test WHERE id = %d;", i))
+end;
+s = prepare("SELECT a FROM test");
+assert(s ~= nil);
+
+-- 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()
+ 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();
+
+test_run:cmd("setopt delimiter ''");
+
+box.space.TEST:drop()
+box.schema.func.drop('SLEEP')
--
2.15.1
More information about the Tarantool-patches
mailing list