[tarantool-patches] [PATCH 8/8] sql: introduce dry-run execution

[Nikita Pettik <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