From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng1.m.smailru.net (smtpng1.m.smailru.net [94.100.181.251]) (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 9BD644696CE for ; Thu, 21 Nov 2019 00:28:37 +0300 (MSK) From: Nikita Pettik Date: Thu, 21 Nov 2019 00:28:15 +0300 Message-Id: <6df993c3a6068f2d2ed7d7b289644e9a1dd2cfce.1574277369.git.korablev@tarantool.org> In-Reply-To: References: In-Reply-To: References: Subject: [Tarantool-patches] [PATCH v2 16/16] netbox: introduce prepared statements List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org This patch introduces support of prepared statements in IProto protocol. To achieve this new IProto command is added - IPROTO_PREPARE. It is sent with IPROTO_SQL_TEXT key and it means to prepare SQL statement (for details see previous commit). Also to reply on PREPARE request a few response keys are added: IPROTO_BIND_METADATA (contains parameters metadata) and IPROTO_BIND_COUNT (count of parameters to be bound). Closes #2592 @TarantoolBot document Title: Prepared statements in SQL Now it is possible to 'prepare' (i.e. compile into byte-code and save to the cache) statement and execute it several times. Mechanism is similar to ones in other DBs. Prepared statement is identified by string containing original SQL request. Prepared statement cache is global and follows LRU eviction policy: when there's no place for another one statement, the statement that is least recently used (prepared) is removed. Cache size is adjusted by box.cfg{sql_cache_size} variable (can be set dynamically; in case size is reduced some statements may be erased from cache). Note that any DDL operation leads to expiration of all prepared statements: they are recompiled on demand. To erase whole cache one can set its size to 0. Prepared statements are available in local mode (i.e. via box.prepare() function) and are supported in IProto protocol. Typical workflow with prepared statements is following: s = box.prepare("SELECT * FROM t WHERE id = ?;") s:execute({1}) or box.execute(s.sql_str, {1}) s:execute({2}) or box.execute(s.sql_str, {2}) In terms of remote connection: cn = netbox:connect(addr) s = cn:prepare("SELECT * FROM t WHERE id = ?;") cn:execute(s.sql_str, {1}) --- src/box/execute.c | 88 +++++++++++++++++++++++++++++++++++++ src/box/iproto.cc | 25 ++++++++--- src/box/iproto_constants.c | 6 ++- src/box/iproto_constants.h | 4 ++ src/box/lua/net_box.c | 79 +++++++++++++++++++++++++++++++++ src/box/lua/net_box.lua | 13 ++++++ src/box/xrow.c | 2 +- src/box/xrow.h | 2 +- test/box/misc.result | 1 + test/sql/engine.cfg | 1 + test/sql/prepared.result | 106 ++++++++++++++++++++++++++------------------- test/sql/prepared.test.lua | 61 +++++++++++++++++++------- 12 files changed, 320 insertions(+), 68 deletions(-) diff --git a/src/box/execute.c b/src/box/execute.c index 2b5a9ba90..13e5a6ba1 100644 --- a/src/box/execute.c +++ b/src/box/execute.c @@ -326,6 +326,67 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count) return 0; } +static inline int +sql_get_params_metadata(struct sql_stmt *stmt, struct obuf *out) +{ + int bind_count = sql_bind_parameter_count(stmt); + int size = mp_sizeof_uint(IPROTO_BIND_METADATA) + + mp_sizeof_array(bind_count); + char *pos = (char *) obuf_alloc(out, size); + if (pos == NULL) { + diag_set(OutOfMemory, size, "obuf_alloc", "pos"); + return -1; + } + pos = mp_encode_uint(pos, IPROTO_BIND_METADATA); + pos = mp_encode_array(pos, bind_count); + for (int i = 0; i < bind_count; ++i) { + size_t size = mp_sizeof_map(2) + + mp_sizeof_uint(IPROTO_FIELD_NAME) + + mp_sizeof_uint(IPROTO_FIELD_TYPE); + const char *name = sql_bind_parameter_name(stmt, i); + if (name == NULL) + name = "?"; + const char *type = "ANY"; + size += mp_sizeof_str(strlen(name)); + size += mp_sizeof_str(strlen(type)); + char *pos = (char *) obuf_alloc(out, size); + if (pos == NULL) { + diag_set(OutOfMemory, size, "obuf_alloc", "pos"); + return -1; + } + pos = mp_encode_map(pos, 2); + pos = mp_encode_uint(pos, IPROTO_FIELD_NAME); + pos = mp_encode_str(pos, name, strlen(name)); + pos = mp_encode_uint(pos, IPROTO_FIELD_TYPE); + pos = mp_encode_str(pos, type, strlen(type)); + } + return 0; +} + +static int +sql_get_prepare_common_keys(struct sql_stmt *stmt, struct obuf *out, int keys, + const char *sql_str) +{ + int size = mp_sizeof_map(keys) + + mp_sizeof_uint(IPROTO_SQL_TEXT) + + mp_sizeof_str(strlen(sql_str)) + + mp_sizeof_uint(IPROTO_BIND_COUNT) + + mp_sizeof_uint(sql_bind_parameter_count(stmt)); + char *pos = (char *) obuf_alloc(out, size); + if (pos == NULL) { + diag_set(OutOfMemory, size, "obuf_alloc", "pos"); + return -1; + } + pos = mp_encode_map(pos, keys); + pos = mp_encode_uint(pos, IPROTO_SQL_TEXT); + pos = mp_encode_str(pos, sql_str, strlen(sql_str)); + pos = mp_encode_uint(pos, IPROTO_BIND_COUNT); + pos = mp_encode_uint(pos, sql_bind_parameter_count(stmt)); + if (sql_get_params_metadata(stmt, out) != 0) + return -1; + return 0; +} + static int port_sql_dump_msgpack(struct port *port, struct obuf *out) { @@ -407,6 +468,33 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out) } break; } + case DQL_PREPARE: { + /* Format is following: + * query_id, + * param_count, + * params {name, type}, + * metadata {name, type} + */ + int keys = 4; + if (sql_get_prepare_common_keys(stmt, out, keys, + sql_stmt_query_str(stmt)) != 0) + return -1; + if (sql_get_metadata(stmt, out, sql_column_count(stmt)) != 0) + return -1; + break; + } + case DML_PREPARE: { + /* Format is following: + * query_id, + * param_count, + * params {name, type}, + */ + int keys = 3; + if (sql_get_prepare_common_keys(stmt, out, keys, + sql_stmt_query_str(stmt)) != 0) + return -1; + break; + } default: unreachable(); } return 0; diff --git a/src/box/iproto.cc b/src/box/iproto.cc index 34c8f469a..a52c76961 100644 --- a/src/box/iproto.cc +++ b/src/box/iproto.cc @@ -178,7 +178,7 @@ struct iproto_msg struct call_request call; /** Authentication request. */ struct auth_request auth; - /* SQL request, if this is the EXECUTE request. */ + /* SQL request, if this is the EXECUTE/PREPARE request. */ struct sql_request sql; /** In case of iproto parse error, saved diagnostics. */ struct diag diag; @@ -1155,6 +1155,7 @@ static const struct cmsg_hop *dml_route[IPROTO_TYPE_STAT_MAX] = { call_route, /* IPROTO_CALL */ sql_route, /* IPROTO_EXECUTE */ NULL, /* IPROTO_NOP */ + sql_route, /* IPROTO_PREPARE */ }; static const struct cmsg_hop join_route[] = { @@ -1210,6 +1211,7 @@ iproto_msg_decode(struct iproto_msg *msg, const char **pos, const char *reqend, cmsg_init(&msg->base, call_route); break; case IPROTO_EXECUTE: + case IPROTO_PREPARE: if (xrow_decode_sql(&msg->header, &msg->sql) != 0) goto error; cmsg_init(&msg->base, sql_route); @@ -1653,7 +1655,8 @@ tx_process_sql(struct cmsg *m) if (tx_check_schema(msg->header.schema_version)) goto error; - assert(msg->header.type == IPROTO_EXECUTE); + assert(msg->header.type == IPROTO_EXECUTE || + msg->header.type == IPROTO_PREPARE); tx_inject_delay(); if (msg->sql.bind != NULL) { bind_count = sql_bind_list_decode(msg->sql.bind, &bind); @@ -1662,9 +1665,20 @@ tx_process_sql(struct cmsg *m) } sql = msg->sql.sql_text; sql = mp_decode_str(&sql, &len); - if (sql_prepare_and_execute(sql, len, bind, bind_count, &port, - &fiber()->gc) != 0) - goto error; + if (msg->header.type == IPROTO_EXECUTE) { + sql = msg->sql.sql_text; + sql = mp_decode_str(&sql, &len); + if (sql_prepare_and_execute(sql, len, bind, bind_count, + &port, &fiber()->gc) != 0) + goto error; + } else { + /* IPROTO_PREPARE */ + sql = msg->sql.sql_text; + sql = mp_decode_str(&sql, &len); + if (sql_prepare(sql, len, &port) != 0) + goto error; + } + /* * Take an obuf only after execute(). Else the buffer can * become out of date during yield. @@ -1676,6 +1690,7 @@ tx_process_sql(struct cmsg *m) port_destroy(&port); goto error; } + /* Nothing to dump in case of UNPREPARE request. */ if (port_dump_msgpack(&port, out) != 0) { port_destroy(&port); obuf_rollback_to_svp(out, &header_svp); diff --git a/src/box/iproto_constants.c b/src/box/iproto_constants.c index 09ded1ecb..6c4dc4f8c 100644 --- a/src/box/iproto_constants.c +++ b/src/box/iproto_constants.c @@ -107,6 +107,7 @@ const char *iproto_type_strs[] = "CALL", "EXECUTE", NULL, /* NOP */ + "PREPARE", }; #define bit(c) (1ULL<sql_text = value; } - if (request->sql_text == NULL) { + if (request->sql_text == NULL ) { xrow_on_decode_err(row->body[0].iov_base, end, ER_MISSING_REQUEST_FIELD, iproto_key_name(IPROTO_SQL_TEXT)); return -1; diff --git a/src/box/xrow.h b/src/box/xrow.h index 60def2d3c..bcb96f079 100644 --- a/src/box/xrow.h +++ b/src/box/xrow.h @@ -526,7 +526,7 @@ int iproto_reply_error(struct obuf *out, const struct error *e, uint64_t sync, uint32_t schema_version); -/** EXECUTE request. */ +/** EXECUTE/PREPARE request. */ struct sql_request { /** SQL statement text. */ const char *sql_text; diff --git a/test/box/misc.result b/test/box/misc.result index ce66e1bfe..8bb93b16c 100644 --- a/test/box/misc.result +++ b/test/box/misc.result @@ -230,6 +230,7 @@ t; - EVAL - CALL - ERROR + - PREPARE - REPLACE - UPSERT - AUTH diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg index a1b4b0fc5..e38bec24e 100644 --- a/test/sql/engine.cfg +++ b/test/sql/engine.cfg @@ -10,6 +10,7 @@ "local": {"remote": "false"} }, "prepared.test.lua": { + "remote": {"remote": "true"}, "local": {"remote": "false"} }, "*": { diff --git a/test/sql/prepared.result b/test/sql/prepared.result index 3321f41f3..4a92f4d97 100644 --- a/test/sql/prepared.result +++ b/test/sql/prepared.result @@ -12,25 +12,41 @@ fiber = require('fiber') -- Wrappers to make remote and local execution interface return -- same result pattern. -- +is_remote = test_run:get_cfg('remote') == 'true' + | --- + | ... +execute = nil + | --- + | ... +prepare = nil + | --- + | ... + test_run:cmd("setopt delimiter ';'") | --- | - true | ... -execute = function(...) - local res, err = box.execute(...) - if err ~= nil then - error(err) +if is_remote then + box.schema.user.grant('guest','read, write, execute', 'universe') + box.schema.user.grant('guest', 'create', 'space') + cn = remote.connect(box.cfg.listen) + execute = function(...) return cn:execute(...) end + prepare = function(...) return cn:prepare(...) end +else + execute = function(...) + local res, err = box.execute(...) + if err ~= nil then + error(err) + end + return res end - return res -end; - | --- - | ... -prepare = function(...) - local res, err = box.prepare(...) - if err ~= nil then - error(err) + prepare = function(...) + local res, err = box.prepare(...) + if err ~= nil then + error(err) + end + return res end - return res end; | --- | ... @@ -119,28 +135,22 @@ execute(s.sql_str, {1, 3}) | type: string | rows: [] | ... -s:execute({1, 2}) + +test_run:cmd("setopt delimiter ';'") | --- - | - metadata: - | - name: ID - | type: integer - | - name: A - | type: number - | - name: B - | type: string - | rows: - | - [1, 2, '3'] + | - true | ... -s:execute({1, 3}) +if not is_remote then + res = s:execute({1, 2}) + assert(res ~= nil) + res = s:execute({1, 3}) + assert(res ~= nil) +end; | --- - | - metadata: - | - name: ID - | type: integer - | - name: A - | type: number - | - name: B - | type: string - | rows: [] + | ... +test_run:cmd("setopt delimiter ''"); + | --- + | - true | ... -- Test preparation of different types of queries. @@ -287,6 +297,7 @@ space:replace{7, 8.5, '9'} s = prepare("SELECT a FROM test WHERE b = '3';") | --- | ... + execute(s.sql_str) | --- | - metadata: @@ -303,21 +314,21 @@ execute(s.sql_str) | rows: | - [2] | ... -s:execute() +test_run:cmd("setopt delimiter ';'") | --- - | - metadata: - | - name: A - | type: number - | rows: - | - [2] + | - true | ... -s:execute() +if not is_remote then + res = s:execute() + assert(res ~= nil) + res = s:execute() + assert(res ~= nil) +end; | --- - | - metadata: - | - name: A - | type: number - | rows: - | - [2] + | ... +test_run:cmd("setopt delimiter ''"); + | --- + | - true | ... s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';") @@ -561,6 +572,13 @@ f2:join(); | - true | ... +if is_remote then + cn:close() + box.schema.user.revoke('guest', 'read, write, execute', 'universe') + box.schema.user.revoke('guest', 'create', 'space') +end; + | --- + | ... test_run:cmd("setopt delimiter ''"); | --- | - true diff --git a/test/sql/prepared.test.lua b/test/sql/prepared.test.lua index 24e49832f..de667d6d5 100644 --- a/test/sql/prepared.test.lua +++ b/test/sql/prepared.test.lua @@ -5,20 +5,32 @@ fiber = require('fiber') -- Wrappers to make remote and local execution interface return -- same result pattern. -- +is_remote = test_run:get_cfg('remote') == 'true' +execute = nil +prepare = nil + test_run:cmd("setopt delimiter ';'") -execute = function(...) - local res, err = box.execute(...) - if err ~= nil then - error(err) +if is_remote then + box.schema.user.grant('guest','read, write, execute', 'universe') + box.schema.user.grant('guest', 'create', 'space') + cn = remote.connect(box.cfg.listen) + execute = function(...) return cn:execute(...) end + prepare = function(...) return cn:prepare(...) end +else + execute = function(...) + local res, err = box.execute(...) + if err ~= nil then + error(err) + end + return res end - return res -end; -prepare = function(...) - local res, err = box.prepare(...) - if err ~= nil then - error(err) + prepare = function(...) + local res, err = box.prepare(...) + if err ~= nil then + error(err) + end + return res end - return res end; test_run:cmd("setopt delimiter ''"); @@ -39,8 +51,15 @@ 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_run:cmd("setopt delimiter ';'") +if not is_remote then + res = s:execute({1, 2}) + assert(res ~= nil) + res = s:execute({1, 3}) + assert(res ~= nil) +end; +test_run:cmd("setopt delimiter ''"); -- Test preparation of different types of queries. -- Let's start from DDL. It doesn't make much sense since @@ -93,10 +112,17 @@ 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() +test_run:cmd("setopt delimiter ';'") +if not is_remote then + res = s:execute() + assert(res ~= nil) + res = s:execute() + assert(res ~= nil) +end; +test_run:cmd("setopt delimiter ''"); s = prepare("SELECT count(*), count(a - 3), max(b), abs(id) FROM test WHERE b = '3';") execute(s.sql_str) @@ -190,6 +216,11 @@ f2:set_joinable(true) f1:join(); f2:join(); +if is_remote then + cn:close() + box.schema.user.revoke('guest', 'read, write, execute', 'universe') + box.schema.user.revoke('guest', 'create', 'space') +end; test_run:cmd("setopt delimiter ''"); box.space.TEST:drop() -- 2.15.1