[Tarantool-patches] [PATCH v2 3/6] sql: extend result set with collation

[Nikita Pettik <korablev@tarantool.org>]

If resulting set column is of STRING type and features collation (no
matter explicit or implicit) different from "none", then metadata will
contain its name.

This patch also introduces new pragma: full_metadata. By default it is
not set. If it is turned on, then optional metadata (like collation) is
pushed to Lua stack. Note that via IProto protocol always full metadata
is send, but its decoding depends on session SQL settings.

Part of #4407
---
 src/box/execute.c               |  32 +++++++++---
 src/box/iproto_constants.h      |   1 +
 src/box/lua/execute.c           |   8 ++-
 src/box/lua/net_box.c           |  23 +++++++--
 src/box/sql/pragma.h            |   8 +++
 src/box/sql/select.c            |  19 +++++++
 src/box/sql/sqlInt.h            |   7 +++
 src/box/sql/vdbe.h              |   4 ++
 src/box/sql/vdbeInt.h           |   1 +
 src/box/sql/vdbeapi.c           |   9 ++++
 src/box/sql/vdbeaux.c           |  16 ++++++
 test/sql/engine.cfg             |   4 ++
 test/sql/full_metadata.result   | 106 ++++++++++++++++++++++++++++++++++++++++
 test/sql/full_metadata.test.lua |  43 ++++++++++++++++
 test/sql/sql-debug.result       |   1 +
 15 files changed, 272 insertions(+), 10 deletions(-)
 create mode 100644 test/sql/full_metadata.result
 create mode 100644 test/sql/full_metadata.test.lua

diff --git a/src/box/execute.c b/src/box/execute.c
index e8b012e5b..72300235a 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -268,6 +268,24 @@ error:
 	return -1;
 }
 
+static size_t
+metadata_map_sizeof(const char *name, const char *type, const char *coll)
+{
+	uint32_t members_count = 2;
+	size_t map_size = 0;
+	if (coll != NULL) {
+		members_count++;
+		map_size += mp_sizeof_uint(IPROTO_FIELD_COLL);
+		map_size += mp_sizeof_str(strlen(coll));
+	}
+	map_size += mp_sizeof_uint(IPROTO_FIELD_NAME);
+	map_size += mp_sizeof_uint(IPROTO_FIELD_TYPE);
+	map_size += mp_sizeof_str(strlen(name));
+	map_size += mp_sizeof_str(strlen(type));
+	map_size += mp_sizeof_map(members_count);
+	return map_size;
+}
+
 /**
  * Serialize a description of the prepared statement.
  * @param stmt Prepared statement.
@@ -291,9 +309,7 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 	pos = mp_encode_uint(pos, IPROTO_METADATA);
 	pos = mp_encode_array(pos, column_count);
 	for (int i = 0; i < column_count; ++i) {
-		size_t size = mp_sizeof_map(2) +
-			      mp_sizeof_uint(IPROTO_FIELD_NAME) +
-			      mp_sizeof_uint(IPROTO_FIELD_TYPE);
+		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
 		/*
@@ -303,18 +319,22 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 		 */
 		assert(name != NULL);
 		assert(type != NULL);
-		size += mp_sizeof_str(strlen(name));
-		size += mp_sizeof_str(strlen(type));
+		size = metadata_map_sizeof(name, type, coll);
 		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);
+		uint32_t map_sz = 2 + (coll != NULL);
+		pos = mp_encode_map(pos, map_sz);
 		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));
+		if (coll != NULL) {
+			pos = mp_encode_uint(pos, IPROTO_FIELD_COLL);
+			pos = mp_encode_str(pos, coll, strlen(coll));
+		}
 	}
 	return 0;
 }
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index 5e8a7d483..fa9c029a2 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -131,6 +131,7 @@ enum iproto_key {
 enum iproto_metadata_key {
 	IPROTO_FIELD_NAME = 0,
 	IPROTO_FIELD_TYPE = 1,
+	IPROTO_FIELD_COLL = 2,
 };
 
 enum iproto_ballot_key {
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index ffa3d4d2e..55b8a8fef 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -20,9 +20,11 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 	assert(column_count > 0);
 	lua_createtable(L, column_count, 0);
 	for (int i = 0; i < column_count; ++i) {
-		lua_createtable(L, 0, 2);
+		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
+		size_t table_sz = 2 + (coll != NULL);
+		lua_createtable(L, 0, table_sz);
 		/*
 		 * Can not fail, since all column names are
 		 * preallocated during prepare phase and the
@@ -34,6 +36,10 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 		lua_setfield(L, -2, "name");
 		lua_pushstring(L, type);
 		lua_setfield(L, -2, "type");
+		if (coll != NULL) {
+			lua_pushstring(L, coll);
+			lua_setfield(L, -2, "collation");
+		}
 		lua_rawseti(L, -2, i + 1);
 	}
 }
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index 001af95dc..025143307 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -638,6 +638,23 @@ netbox_decode_select(struct lua_State *L)
 	return 2;
 }
 
+/** Decode optional (i.e. may be present in response) metadata fields. */
+static void
+decode_metadata_optional(struct lua_State *L, const char **data,
+			 uint32_t map_size)
+{
+	/* 2 is default metadata map size (field name + field size). */
+	while (map_size-- > 2) {
+		uint32_t key = mp_decode_uint(data);
+		uint32_t len;
+		if (key == IPROTO_FIELD_COLL) {
+			const char *coll = mp_decode_str(data, &len);
+			lua_pushlstring(L, coll, len);
+			lua_setfield(L, -2, "collation");
+		}
+	}
+}
+
 /**
  * Decode IPROTO_METADATA into array of maps.
  * @param L Lua stack to push result on.
@@ -650,12 +667,11 @@ netbox_decode_metadata(struct lua_State *L, const char **data)
 	lua_createtable(L, count, 0);
 	for (uint32_t i = 0; i < count; ++i) {
 		uint32_t map_size = mp_decode_map(data);
-		assert(map_size == 2);
-		(void) map_size;
+		assert(map_size == 2 || map_size == 3);
 		uint32_t key = mp_decode_uint(data);
 		assert(key == IPROTO_FIELD_NAME);
 		(void) key;
-		lua_createtable(L, 0, 1);
+		lua_createtable(L, 0, map_size);
 		uint32_t len;
 		const char *str = mp_decode_str(data, &len);
 		lua_pushlstring(L, str, len);
@@ -665,6 +681,7 @@ netbox_decode_metadata(struct lua_State *L, const char **data)
 		const char *type = mp_decode_str(data, &len);
 		lua_pushlstring(L, type, len);
 		lua_setfield(L, -2, "type");
+		decode_metadata_optional(L, data, map_size);
 		lua_rawseti(L, -2, i + 1);
 	}
 }
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index 30a1fc41a..aa9377a13 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -144,6 +144,9 @@ static const char *const pragCName[] = {
 	/* Used by: where_trace */
 	/*  90 */ "where_trace",
 	/*  91 */ "integer",
+	/* Used by: full_metadata */
+	/*  92 */ "full_metadata",
+	/*  93 */ "integer",
 };
 
 /* Definitions of all built-in pragmas */
@@ -186,6 +189,11 @@ static const PragmaName aPragmaName[] = {
 	 /* ePragFlg:  */ PragFlg_Result0 | PragFlg_NoColumns1,
 	 /* ColNames:  */ 64, 1,
 	 /* iArg:      */ SQL_FullColNames},
+	{ /* zName:     */ "full_metadata",
+	  /* ePragTyp:  */ PragTyp_FLAG,
+	  /* ePragFlg:  */ PragFlg_Result0 | PragFlg_NoColumns1,
+	  /* ColNames:  */ 92, 1,
+	  /* iArg:      */ SQL_FullMetadata},
 	{ /* zName:     */ "index_info",
 	 /* ePragTyp:  */ PragTyp_INDEX_INFO,
 	 /* ePragFlg:  */
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index e4768121e..506c1a7c7 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1783,6 +1783,7 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 	pParse->colNamesSet = 1;
 	fullNames = (pParse->sql_flags & SQL_FullColNames) != 0;
 	shortNames = (pParse->sql_flags & SQL_ShortColNames) != 0;
+	bool is_full_meta = (pParse->sql_flags & SQL_FullMetadata) != 0;
 	sqlVdbeSetNumCols(v, pEList->nExpr);
 	uint32_t var_count = 0;
 	for (i = 0; i < pEList->nExpr; i++) {
@@ -1794,6 +1795,24 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 			var_pos[var_count++] = i;
 		vdbe_metadata_set_col_type(v, i,
 					   field_type_strs[sql_expr_type(p)]);
+		if (is_full_meta && sql_expr_type(p) == FIELD_TYPE_STRING) {
+			bool unused;
+			uint32_t id;
+			struct coll *coll = NULL;
+			/*
+			 * If sql_expr_coll fails then it fails somewhere
+			 * above the call stack.
+			 */
+			int rc =  sql_expr_coll(pParse, p, &unused, &id, &coll);
+			assert(rc == 0);
+			(void) rc;
+			if (id != COLL_NONE) {
+				struct coll_id *coll_id = coll_by_id(id);
+				vdbe_metadata_set_col_collation(v, i,
+								coll_id->name,
+								coll_id->name_len);
+			}
+		}
 		if (p->op == TK_COLUMN || p->op == TK_AGG_COLUMN) {
 			char *zCol;
 			int iCol = p->iColumn;
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 2594b73e0..e9ad068f8 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -576,6 +576,9 @@ sql_column_name(sql_stmt *, int N);
 const char *
 sql_column_datatype(sql_stmt *, int N);
 
+const char *
+sql_column_coll(sql_stmt *stmt, int n);
+
 int
 sql_initialize(void);
 
@@ -1181,6 +1184,10 @@ struct sql {
 #define SQL_EnableTrigger  0x01000000	/* True to enable triggers */
 #define SQL_DeferFKs       0x02000000	/* Defer all FK constraints */
 #define SQL_VdbeEQP        0x08000000	/* Debug EXPLAIN QUERY PLAN */
+#define SQL_FullMetadata   0x04000000	/* Display optional properties
+					 * (nullability, autoincrement, alias)
+					 * in metadata.
+					 */
 
 /* Bits of the sql.dbOptFlags field. */
 #define SQL_QueryFlattener 0x0001	/* Query flattening */
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index ddced20e1..22ba0b756 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -253,6 +253,10 @@ vdbe_metadata_set_col_name(struct Vdbe *v, int col_idx, const char *name);
 int
 vdbe_metadata_set_col_type(struct Vdbe *v, int col_idx, const char *type);
 
+int
+vdbe_metadata_set_col_collation(struct Vdbe *p, int idx, const char *coll,
+				size_t coll_len);
+
 void sqlVdbeCountChanges(Vdbe *);
 sql *sqlVdbeDb(Vdbe *);
 void sqlVdbeSetSql(Vdbe *, const char *z, int n, int);
diff --git a/src/box/sql/vdbeInt.h b/src/box/sql/vdbeInt.h
index 64250bee2..d3de5770b 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -349,6 +349,7 @@ struct ScanStatus {
 struct sql_column_metadata {
 	char *name;
 	char *type;
+	char *collation;
 };
 
 /*
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 48db0bf43..e57a80334 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -745,6 +745,15 @@ sql_column_datatype(sql_stmt *stmt, int n)
 	return p->metadata[n].type;
 }
 
+const char *
+sql_column_coll(sql_stmt *stmt, int n)
+{
+	struct Vdbe *p = (struct Vdbe *) stmt;
+	assert(n < sql_column_count(stmt) && n >= 0);
+	return p->metadata[n].collation;
+}
+
+
 /******************************* sql_bind_  **************************
  *
  * Routines used to attach values to wildcards in a compiled SQL statement.
diff --git a/src/box/sql/vdbeaux.c b/src/box/sql/vdbeaux.c
index f2cf386bb..02d9fc6ce 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -1834,6 +1834,7 @@ vdbe_metadata_delete(struct Vdbe *v)
 		for (int i = 0; i < v->nResColumn; ++i) {
 			free(v->metadata[i].name);
 			free(v->metadata[i].type);
+			free(v->metadata[i].collation);
 		}
 		free(v->metadata);
 	}
@@ -1888,6 +1889,21 @@ vdbe_metadata_set_col_type(struct Vdbe *p, int idx, const char *type)
 	return 0;
 }
 
+int
+vdbe_metadata_set_col_collation(struct Vdbe *p, int idx, const char *coll,
+				size_t coll_len)
+{
+	assert(idx < p->nResColumn);
+	if (p->metadata[idx].collation != NULL)
+		free((void *)p->metadata[idx].collation);
+	p->metadata[idx].collation = strndup(coll, coll_len);
+	if (p->metadata[idx].collation == NULL) {
+		diag_set(OutOfMemory, coll_len + 1, "strndup", "collation");
+		return -1;
+	}
+	return 0;
+}
+
 /*
  * This routine checks that the sql.nVdbeActive count variable
  * matches the number of vdbe's in the list sql.pVdbe that are
diff --git a/test/sql/engine.cfg b/test/sql/engine.cfg
index 284c42082..efc42a114 100644
--- a/test/sql/engine.cfg
+++ b/test/sql/engine.cfg
@@ -9,6 +9,10 @@
         "remote": {"remote": "true"},
         "local": {"remote": "false"}
     },
+    "full_metadata.test.lua": {
+        "remote": {"remote": "true"},
+        "local": {"remote": "false"}
+    },
     "*": {
         "memtx": {"engine": "memtx"},
         "vinyl": {"engine": "vinyl"}
diff --git a/test/sql/full_metadata.result b/test/sql/full_metadata.result
new file mode 100644
index 000000000..aebb938fe
--- /dev/null
+++ b/test/sql/full_metadata.result
@@ -0,0 +1,106 @@
+-- test-run result file version 2
+netbox = require('net.box')
+ | ---
+ | ...
+test_run = require('test_run').new()
+ | ---
+ | ...
+
+box.execute("CREATE TABLE t (id INT PRIMARY KEY AUTOINCREMENT, a INT NOT NULL, c TEXT COLLATE \"unicode_ci\");")
+ | ---
+ | - row_count: 1
+ | ...
+box.execute("INSERT INTO t VALUES (1, 1, 'aSd');")
+ | ---
+ | - row_count: 1
+ | ...
+
+remote = test_run:get_cfg('remote') == 'true'
+ | ---
+ | ...
+execute = nil
+ | ---
+ | ...
+test_run:cmd("setopt delimiter ';'")
+ | ---
+ | - true
+ | ...
+if remote then
+	box.schema.user.grant('guest','read, write, execute', 'universe')
+	box.schema.user.grant('guest', 'create', 'space')
+	cn = netbox.connect(box.cfg.listen)
+	execute = function(...) return cn:execute(...) end
+else
+	execute = function(...)
+		local res, err = box.execute(...)
+		if err ~= nil then
+			error(err)
+		end
+		return res
+	end
+end;
+ | ---
+ | ...
+test_run:cmd("setopt delimiter ''");
+ | ---
+ | - true
+ | ...
+
+execute("PRAGMA full_metadata = true;")
+ | ---
+ | - row_count: 0
+ | ...
+-- Make sure collation is presented in extended metadata.
+--
+execute("SELECT 'aSd' COLLATE \"unicode_ci\";")
+ | ---
+ | - metadata:
+ |   - type: string
+ |     name: '''aSd'' COLLATE "unicode_ci"'
+ |     collation: unicode_ci
+ |   rows:
+ |   - ['aSd']
+ | ...
+execute("SELECT c FROM t;")
+ | ---
+ | - metadata:
+ |   - type: string
+ |     name: C
+ |     collation: unicode_ci
+ |   rows:
+ |   - ['aSd']
+ | ...
+execute("SELECT c COLLATE \"unicode\" FROM t;")
+ | ---
+ | - metadata:
+ |   - type: string
+ |     name: c COLLATE "unicode"
+ |     collation: unicode
+ |   rows:
+ |   - ['aSd']
+ | ...
+
+execute("PRAGMA full_metadata = false;")
+ | ---
+ | - row_count: 0
+ | ...
+
+test_run:cmd("setopt delimiter ';'")
+ | ---
+ | - true
+ | ...
+if 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
+ | ...
+
+box.space.T:drop()
+ | ---
+ | ...
diff --git a/test/sql/full_metadata.test.lua b/test/sql/full_metadata.test.lua
new file mode 100644
index 000000000..4aa2492a1
--- /dev/null
+++ b/test/sql/full_metadata.test.lua
@@ -0,0 +1,43 @@
+netbox = require('net.box')
+test_run = require('test_run').new()
+
+box.execute("CREATE TABLE t (id INT PRIMARY KEY AUTOINCREMENT, a INT NOT NULL, c TEXT COLLATE \"unicode_ci\");")
+box.execute("INSERT INTO t VALUES (1, 1, 'aSd');")
+
+remote = test_run:get_cfg('remote') == 'true'
+execute = nil
+test_run:cmd("setopt delimiter ';'")
+if remote then
+	box.schema.user.grant('guest','read, write, execute', 'universe')
+	box.schema.user.grant('guest', 'create', 'space')
+	cn = netbox.connect(box.cfg.listen)
+	execute = function(...) return cn:execute(...) end
+else
+	execute = function(...)
+		local res, err = box.execute(...)
+		if err ~= nil then
+			error(err)
+		end
+		return res
+	end
+end;
+test_run:cmd("setopt delimiter ''");
+
+execute("PRAGMA full_metadata = true;")
+-- Make sure collation is presented in extended metadata.
+--
+execute("SELECT 'aSd' COLLATE \"unicode_ci\";")
+execute("SELECT c FROM t;")
+execute("SELECT c COLLATE \"unicode\" FROM t;")
+
+execute("PRAGMA full_metadata = false;")
+
+test_run:cmd("setopt delimiter ';'")
+if 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.T:drop()
diff --git a/test/sql/sql-debug.result b/test/sql/sql-debug.result
index b19075366..a1b97663c 100644
--- a/test/sql/sql-debug.result
+++ b/test/sql/sql-debug.result
@@ -41,6 +41,7 @@ box.execute('PRAGMA')
   - ['count_changes', 0]
   - ['defer_foreign_keys', 0]
   - ['full_column_names', 0]
+  - ['full_metadata', 0]
   - ['parser_trace', 0]
   - ['recursive_triggers', 1]
   - ['reverse_unordered_selects', 0]
-- 
2.15.1