From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 345A8292CE for ; Tue, 27 Aug 2019 09:34:40 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id eMunBKz3hhlG for ; Tue, 27 Aug 2019 09:34:40 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id ABCD629274 for ; Tue, 27 Aug 2019 09:34:39 -0400 (EDT) From: Nikita Pettik Subject: [tarantool-patches] [PATCH 8/8] sql: introduce dry-run execution Date: Tue, 27 Aug 2019 16:34:29 +0300 Message-Id: <9ebc8e59010c5099ef1f39a5563aa64f5c9746a1.1566907520.git.korablev@tarantool.org> In-Reply-To: References: In-Reply-To: References: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-Help: List-Unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-Subscribe: List-Owner: List-post: List-Archive: To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, kostja@tarantool.org, alexander.turenko@tarantool.org, Nikita Pettik To get result of dry-run execution locally, one can use box.dry_run("sql_string") method, which returns query's meta-information. Note this method does not support binding values substitution. To get result of dry-run execution using net.box facilities, one can pass map containing dry_run options to :execute() method (leaving array of bindings empty): cn:execute("SELECT 1;", {}, {{dry_run = true}}) Or simply set options considering their order: cn:execute("SELECT 1;", {}, {true}) Note that there's no binding substitution even if array of values to be bound is not empty. Also, dry-run execution for DML and DDL request doesn't make any sense - zero row_count is always returned. Under the hood we add 'meta_only' flag to struct port_sql, which regulates whether response contains only metadata or metadata and tuples forming the result set of query. Closes #3292 --- src/box/execute.c | 20 ++++++++++--- src/box/iproto.cc | 18 ++++++------ src/box/lua/execute.c | 37 ++++++++++++++++++++++-- src/box/lua/net_box.c | 7 +++-- src/box/port.h | 4 ++- test/box/misc.result | 1 + test/sql/iproto.result | 75 ++++++++++++++++++++++++++++++++++++++++++++++++ test/sql/iproto.test.lua | 15 ++++++++++ 8 files changed, 159 insertions(+), 18 deletions(-) diff --git a/src/box/execute.c b/src/box/execute.c index 2e3aeef2c..d8245c72b 100644 --- a/src/box/execute.c +++ b/src/box/execute.c @@ -82,6 +82,14 @@ static_assert(sizeof(struct port_sql) <= sizeof(struct port), * | } | * +-------------------- OR ----------------------+ * | IPROTO_BODY: { | + * | IPROTO_METADATA: [ | + * | {IPROTO_FIELD_NAME: column name1}, | + * | {IPROTO_FIELD_NAME: column name2}, | + * | ... | + * | ], | + * | } | + * +-------------------- OR ----------------------+ + * | IPROTO_BODY: { | * | IPROTO_SQL_INFO: { | * | SQL_INFO_ROW_COUNT: number | * | SQL_INFO_AUTOINCREMENT_IDS: [ | @@ -122,10 +130,11 @@ const struct port_vtab port_sql_vtab = { }; void -port_sql_create(struct port *port, struct sql_stmt *stmt) +port_sql_create(struct port *port, struct sql_stmt *stmt, bool meta_only) { port_tuple_create(port); ((struct port_sql *)port)->stmt = stmt; + ((struct port_sql *)port)->meta_only = meta_only; port->vtab = &port_sql_vtab; } @@ -332,10 +341,11 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out) { assert(port->vtab == &port_sql_vtab); sql *db = sql_get(); - struct sql_stmt *stmt = ((struct port_sql *)port)->stmt; + struct port_sql *port_sql = (struct port_sql *) port; + struct sql_stmt *stmt = port_sql->stmt; int column_count = sql_column_count(stmt); if (column_count > 0) { - int keys = 2; + int keys = port_sql->meta_only ? 1 : 2; int size = mp_sizeof_map(keys); char *pos = (char *) obuf_alloc(out, size); if (pos == NULL) { @@ -345,6 +355,8 @@ port_sql_dump_msgpack(struct port *port, struct obuf *out) pos = mp_encode_map(pos, keys); if (sql_get_metadata(stmt, out, column_count) != 0) return -1; + if (port_sql->meta_only) + return 0; size = mp_sizeof_uint(IPROTO_DATA); pos = (char *) obuf_alloc(out, size); if (pos == NULL) { @@ -491,7 +503,7 @@ sql_prepare_and_execute(const char *sql, int len, const struct sql_bind *bind, if (sql_prepare(sql, len, &stmt, NULL) != 0) return -1; assert(stmt != NULL); - port_sql_create(port, stmt); + port_sql_create(port, stmt, false); if (sql_bind(stmt, bind, bind_count) == 0 && sql_execute(stmt, port, region) == 0) return 0; diff --git a/src/box/iproto.cc b/src/box/iproto.cc index a92e66ace..22019efaa 100644 --- a/src/box/iproto.cc +++ b/src/box/iproto.cc @@ -1671,14 +1671,16 @@ tx_process_sql(struct cmsg *m) if (sql_prepare(sql, len, &stmt, NULL) != 0) goto error; assert(stmt != NULL); - port_sql_create(&port, stmt); - if (sql_bind(stmt, bind, bind_count) != 0) { - port_destroy(&port); - goto error; - } - if (sql_execute(stmt, &port, &fiber()->gc) != 0) { - port_destroy(&port); - goto error; + port_sql_create(&port, stmt, opts.dry_run); + if (!opts.dry_run) { + if (sql_bind(stmt, bind, bind_count) != 0) { + port_destroy(&port); + goto error; + } + if (sql_execute(stmt, &port, &fiber()->gc) != 0) { + port_destroy(&port); + goto error; + } } /* * Take an obuf only after execute(). Else the buffer can diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c index 76ecdd541..fd95e508e 100644 --- a/src/box/lua/execute.c +++ b/src/box/lua/execute.c @@ -45,12 +45,15 @@ port_sql_dump_lua(struct port *port, struct lua_State *L, bool is_flat) assert(is_flat == false); assert(port->vtab == &port_sql_vtab); struct sql *db = sql_get(); - struct sql_stmt *stmt = ((struct port_sql *)port)->stmt; + struct port_sql *port_sql = (struct port_sql *) port; + struct sql_stmt *stmt = port_sql->stmt; int column_count = sql_column_count(stmt); if (column_count > 0) { - lua_createtable(L, 0, 2); + lua_createtable(L, 0, port_sql->meta_only ? 1 : 2); lua_sql_get_metadata(stmt, L, column_count); lua_setfield(L, -2, "metadata"); + if (port_sql->meta_only) + return; port_tuple_vtab.dump_lua(port, L, false); lua_setfield(L, -2, "rows"); } else { @@ -266,6 +269,31 @@ lbox_execute(struct lua_State *L) return 1; } +/** + * In contrast to ordinary "execute" method, this one only + * prepares (compiles) statement but not executes. It allows + * to get query's meta-information. + */ +static int +lbox_dry_run(struct lua_State *L) +{ + size_t length; + struct port port; + int top = lua_gettop(L); + + if ((top != 1) || ! lua_isstring(L, 1)) + return luaL_error(L, "Usage: box.dry_run(sqlstring)"); + + const char *sql = lua_tolstring(L, 1, &length); + struct sql_stmt *stmt; + if (sql_prepare(sql, length, &stmt, NULL) != 0) + return luaT_push_nil_and_error(L); + port_sql_create(&port, stmt, true); + port_dump_lua(&port, L, false); + port_destroy(&port); + return 1; +} + void box_lua_execute_init(struct lua_State *L) { @@ -273,5 +301,10 @@ box_lua_execute_init(struct lua_State *L) lua_pushstring(L, "execute"); lua_pushcfunction(L, lbox_execute); lua_settable(L, -3); + + lua_pushstring(L, "dry_run"); + lua_pushcfunction(L, lbox_dry_run); + lua_settable(L, -3); + lua_pop(L, 1); } diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c index 001af95dc..fe8492a52 100644 --- a/src/box/lua/net_box.c +++ b/src/box/lua/net_box.c @@ -738,12 +738,13 @@ netbox_decode_execute(struct lua_State *L) } if (info_index == 0) { assert(meta_index != 0); - assert(rows_index != 0); lua_createtable(L, 0, 2); lua_pushvalue(L, meta_index - 1); lua_setfield(L, -2, "metadata"); - lua_pushvalue(L, rows_index - 1); - lua_setfield(L, -2, "rows"); + if (rows_index != 0) { + lua_pushvalue(L, rows_index - 1); + lua_setfield(L, -2, "rows"); + } } else { assert(meta_index == 0); assert(rows_index == 0); diff --git a/src/box/port.h b/src/box/port.h index 0d2cc7e84..65b980e61 100644 --- a/src/box/port.h +++ b/src/box/port.h @@ -135,13 +135,15 @@ struct port_sql { struct port_tuple port_tuple; /* Prepared SQL statement. */ struct sql_stmt *stmt; + /** If true then dump only query's meta-info. */ + bool meta_only; }; extern const struct port_vtab port_sql_vtab; /** Create instance of SQL port using given attributes. */ void -port_sql_create(struct port *port, struct sql_stmt *stmt); +port_sql_create(struct port *port, struct sql_stmt *stmt, bool meta_only); #if defined(__cplusplus) } /* extern "C" */ diff --git a/test/box/misc.result b/test/box/misc.result index 4d25a9fe7..6e92d4778 100644 --- a/test/box/misc.result +++ b/test/box/misc.result @@ -62,6 +62,7 @@ t - cfg - commit - ctl + - dry_run - error - execute - feedback diff --git a/test/sql/iproto.result b/test/sql/iproto.result index ae5349546..bd4705157 100644 --- a/test/sql/iproto.result +++ b/test/sql/iproto.result @@ -820,6 +820,81 @@ cn:execute("SELECT GREATEST(1, 2, 3);") rows: - [3] ... +-- gh-3292: introduce dry-run SQL query execution. +-- Dry-run returns only meta-information, i.e. there's no +-- execution of query, only its compilation. +-- +cn:execute("SELECT * FROM t1;", {}, {true}) +--- +- metadata: + - name: ID + type: integer +... +cn:execute("SELECT * FROM t1;", {}, {{dry_run = true}}) +--- +- metadata: + - name: ID + type: integer +... +box.dry_run("SELECT * FROM t1;") +--- +- metadata: + - name: ID + type: integer +... +cn:execute("SELECT 1, 'abc', 0.123, x'00', ?;", {1}, {{dry_run = true}}) +--- +- metadata: + - name: '1' + type: integer + - name: '''abc''' + type: string + - name: '0.123' + type: number + - name: x'00' + type: varbinary + - name: '?' + type: boolean +... +box.dry_run("SELECT 1, 'abc', 0.123, x'00', ?;") +--- +- metadata: + - name: '1' + type: integer + - name: '''abc''' + type: string + - name: '0.123' + type: number + - name: x'00' + type: varbinary + - name: '?' + type: boolean +... +cn:execute("DELETE FROM t1;") +--- +- row_count: 5 +... +cn:execute("INSERT INTO t1 VALUES (6);", {}, {{dry_run = true}}) +--- +- metadata: + - name: rows inserted + type: INTEGER +... +cn:execute("SELECT * FROM t1;") +--- +- metadata: + - name: ID + type: integer + rows: [] +... +cn:execute("CREATE TABLE new_t (id INT PRIMARY KEY);", {}, {{dry_run = true}}) +--- +- row_count: 0 +... +assert(box.space.NEW_T == nil) +--- +- true +... cn:close() --- ... diff --git a/test/sql/iproto.test.lua b/test/sql/iproto.test.lua index 7dcea7c2e..bf81ae335 100644 --- a/test/sql/iproto.test.lua +++ b/test/sql/iproto.test.lua @@ -247,6 +247,21 @@ res.metadata cn:execute("SELECT LEAST(1, 2, 3);") cn:execute("SELECT GREATEST(1, 2, 3);") +-- gh-3292: introduce dry-run SQL query execution. +-- Dry-run returns only meta-information, i.e. there's no +-- execution of query, only its compilation. +-- +cn:execute("SELECT * FROM t1;", {}, {true}) +cn:execute("SELECT * FROM t1;", {}, {{dry_run = true}}) +box.dry_run("SELECT * FROM t1;") +cn:execute("SELECT 1, 'abc', 0.123, x'00', ?;", {1}, {{dry_run = true}}) +box.dry_run("SELECT 1, 'abc', 0.123, x'00', ?;") +cn:execute("DELETE FROM t1;") +cn:execute("INSERT INTO t1 VALUES (6);", {}, {{dry_run = true}}) +cn:execute("SELECT * FROM t1;") +cn:execute("CREATE TABLE new_t (id INT PRIMARY KEY);", {}, {{dry_run = true}}) +assert(box.space.NEW_T == nil) + cn:close() box.execute('DROP TABLE t1') -- 2.15.1