From: Nikita Pettik <korablev@tarantool.org> To: tarantool-patches@freelists.org Cc: v.shpilevoy@tarantool.org, kostja@tarantool.org, alexander.turenko@tarantool.org, Nikita Pettik <korablev@tarantool.org> Subject: [tarantool-patches] [PATCH 8/8] sql: introduce dry-run execution Date: Tue, 27 Aug 2019 16:34:29 +0300 [thread overview] Message-ID: <9ebc8e59010c5099ef1f39a5563aa64f5c9746a1.1566907520.git.korablev@tarantool.org> (raw) In-Reply-To: <cover.1566907519.git.korablev@tarantool.org> In-Reply-To: <cover.1566907519.git.korablev@tarantool.org> 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
next prev parent reply other threads:[~2019-08-27 13:34 UTC|newest] Thread overview: 26+ messages / expand[flat|nested] mbox.gz Atom feed top 2019-08-27 13:34 [tarantool-patches] [PATCH 0/8] rfc: introduce dry-run execution of SQL queries Nikita Pettik 2019-08-27 13:34 ` [tarantool-patches] [PATCH 1/8] port: increase padding of struct port Nikita Pettik 2019-08-28 9:33 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` [tarantool-patches] [PATCH 2/8] port: move struct port_sql to box/port.h Nikita Pettik 2019-08-28 9:33 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` [tarantool-patches] [PATCH 3/8] sql: remove sql_prepare_v2() Nikita Pettik 2019-08-28 9:33 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` [tarantool-patches] [PATCH 4/8] sql: refactor sql_prepare() and sqlPrepare() Nikita Pettik 2019-08-28 9:35 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` [tarantool-patches] [PATCH 5/8] sql: move sql_prepare() declaration to box/execute.h Nikita Pettik 2019-08-28 9:35 ` [tarantool-patches] " Konstantin Osipov 2019-08-27 13:34 ` [tarantool-patches] [PATCH 6/8] refactoring: use sql_prepare() and sql_execute() in tx_process_sql() Nikita Pettik 2019-08-28 9:37 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` [tarantool-patches] [PATCH 7/8] netbox: allow passing options to :execute() Nikita Pettik 2019-08-28 9:38 ` [tarantool-patches] " Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-27 13:34 ` Nikita Pettik [this message] 2019-08-28 9:39 ` [tarantool-patches] Re: [PATCH 8/8] sql: introduce dry-run execution Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy 2019-08-28 9:31 ` [tarantool-patches] Re: [PATCH 0/8] rfc: introduce dry-run execution of SQL queries Konstantin Osipov 2019-08-29 20:46 ` Vladislav Shpilevoy
Reply instructions: You may reply publicly to this message via plain-text email using any one of the following methods: * Save the following mbox file, import it into your mail client, and reply-to-all from there: mbox Avoid top-posting and favor interleaved quoting: https://en.wikipedia.org/wiki/Posting_style#Interleaved_style * Reply using the --to, --cc, and --in-reply-to switches of git-send-email(1): git send-email \ --in-reply-to=9ebc8e59010c5099ef1f39a5563aa64f5c9746a1.1566907520.git.korablev@tarantool.org \ --to=korablev@tarantool.org \ --cc=alexander.turenko@tarantool.org \ --cc=kostja@tarantool.org \ --cc=tarantool-patches@freelists.org \ --cc=v.shpilevoy@tarantool.org \ --subject='Re: [tarantool-patches] [PATCH 8/8] sql: introduce dry-run execution' \ /path/to/YOUR_REPLY https://kernel.org/pub/software/scm/git/docs/git-send-email.html * If your mail client supports setting the In-Reply-To header via mailto: links, try the mailto: link
This is a public inbox, see mirroring instructions for how to clone and mirror all data and code used for this inbox