From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtp16.mail.ru (smtp16.mail.ru [94.100.176.153]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 8B67746970E for ; Wed, 25 Dec 2019 18:23:55 +0300 (MSK) Date: Wed, 25 Dec 2019 18:23:54 +0300 From: Sergey Ostanevich Message-ID: <20191225152354.GR19594@tarantool.org> References: <35b9568be5d828100fefc6e19926757b19ec689a.1576844632.git.korablev@tarantool.org> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: <35b9568be5d828100fefc6e19926757b19ec689a.1576844632.git.korablev@tarantool.org> Subject: Re: [Tarantool-patches] [PATCH v3 18/20] box: introduce prepared statements List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: Nikita Pettik Cc: tarantool-patches@dev.tarantool.org Hi! Thanks for the patch, LGTM with just 2 nits below. Sergos On 20 Dec 15:47, Nikita Pettik wrote: > 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. It would be nice to mention plans on what should/will be done for resolution of this. Also, my previous question on DDL during the execution of the statement is valid - one session can ruin execution of a prepared statement in another session with a DDL. Should there be some guard for DDL until all executions of prep statements are finished? > > 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) The naming could be better - since you state something as true or false, it should be definitive, like sql_stmt_schema_version_is_valid() > +{ > + 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)) { The unaries should not be space-delimited as per C style $3.1 https://www.tarantool.io/en/doc/2.2/dev_guide/c_style_guide/ > + 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 >