[tarantool-patches] [PATCH 8/8] sql: introduce dry-run execution
Nikita Pettik
korablev at tarantool.org
Tue Aug 27 16:34:29 MSK 2019
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
More information about the Tarantool-patches
mailing list