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

Nikita Pettik korablev at tarantool.org
Wed Dec 11 16:44:58 MSK 2019


Each column of result set can feature its name alias. For instance:

SELECT x + 1 AS add FROM ...;

In this case real name of resulting set column is "x + 1" meanwhile
"add" is its alias. This patch extends metadata with optional metadata
member which corresponds to column's alias.

Closes #4407

@TarantoolBot document
Title: extended SQL metadata

Before this patch metadata for SQL DQL contained only two fields:
name and type of each column of result set. Now it may contain
following properties:
 - collation (in case type of resulting set column is string and
              collation is different from default "none");
   is encoded with IPROTO_FIELD_COLL (0x2) key in IPROTO_METADATA map;
   in msgpack is encoded as string and held with MP_STR type;
 - is_nullable (in case column of result set corresponds to space's
                field; for expressions like x+1 for the sake of
                simplicity nullability is omitted);
   is encoded with IPROTO_FIELD_IS_NULLABLE key (0x3) in IPROTO_METADATA;
   in msgpack is encoded as boolean and held with MP_BOOL type;
   note that absence of this field implies that nullability is unknown;
 - is_autoincrement (is set only for autoincrement column in result
                     set);
   is encoded with IPROTO_FIELD_IS_AUNTOINCREMENT (0x4) key in IPROTO_METADATA;
   in msgpack is encoded as boolean and held with MP_BOOL type;
 - alias (if column of result set is specified with AS label);
   is encoded with IPROTO_FIELD_ALIAS (0x5) key in IPROTO_METADATA map;
   in msgpack is encoded as string and held with MP_STR type;
   note that if there's no

This extended metadata is send only when PRAGMA full_metadata is
enabled. Otherwise, only basic (name and type) metadata is processed.
---
 src/box/execute.c               | 18 ++++++++++++++----
 src/box/iproto_constants.h      |  1 +
 src/box/lua/execute.c           |  9 +++++++--
 src/box/lua/net_box.c           |  6 +++++-
 src/box/sql/select.c            | 32 ++++++++++++++++++++++++++++----
 src/box/sql/sqlInt.h            |  3 +++
 src/box/sql/vdbe.h              |  3 +++
 src/box/sql/vdbeInt.h           |  1 +
 src/box/sql/vdbeapi.c           |  8 ++++++++
 src/box/sql/vdbeaux.c           | 15 +++++++++++++++
 test/sql/full_metadata.result   | 38 +++++++++++++++++++++++++++++++++++---
 test/sql/full_metadata.test.lua |  7 +++++++
 12 files changed, 127 insertions(+), 14 deletions(-)

diff --git a/src/box/execute.c b/src/box/execute.c
index c853991a0..0fe98af3d 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -270,7 +270,7 @@ error:
 
 static size_t
 metadata_map_sizeof(const char *name, const char *type, const char *coll,
-		    int nullable, bool is_autoincrement)
+		    const char *alias, int nullable, bool is_autoincrement)
 {
 	uint32_t members_count = 2;
 	size_t map_size = 0;
@@ -279,6 +279,11 @@ metadata_map_sizeof(const char *name, const char *type, const char *coll,
 		map_size += mp_sizeof_uint(IPROTO_FIELD_COLL);
 		map_size += mp_sizeof_str(strlen(coll));
 	}
+	if (alias != NULL) {
+		members_count++;
+		map_size += mp_sizeof_uint(IPROTO_FIELD_ALIAS);
+		map_size += mp_sizeof_str(strlen(alias));
+	}
 	if (nullable != -1) {
 		members_count++;
 		map_size += mp_sizeof_uint(IPROTO_FIELD_IS_NULLABLE);
@@ -323,6 +328,7 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
+		const char *alias = sql_column_alias(stmt, i);
 		int nullable = sql_column_nullable(stmt, i);
 		bool is_autoincrement = sql_column_is_autoincrement(stmt, i);
 		/*
@@ -332,15 +338,15 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 		 */
 		assert(name != NULL);
 		assert(type != NULL);
-		size = metadata_map_sizeof(name, type, coll, nullable,
+		size = metadata_map_sizeof(name, type, coll, alias, nullable,
 					   is_autoincrement);
 		char *pos = (char *) obuf_alloc(out, size);
 		if (pos == NULL) {
 			diag_set(OutOfMemory, size, "obuf_alloc", "pos");
 			return -1;
 		}
-		uint32_t map_sz = 2 + (coll != NULL) + (nullable != -1) +
-				  is_autoincrement;
+		uint32_t map_sz = 2 + (coll != NULL) + (alias != NULL) +
+				  (nullable != -1) + is_autoincrement;
 		pos = mp_encode_map(pos, map_sz);
 		pos = mp_encode_uint(pos, IPROTO_FIELD_NAME);
 		pos = mp_encode_str(pos, name, strlen(name));
@@ -350,6 +356,10 @@ sql_get_metadata(struct sql_stmt *stmt, struct obuf *out, int column_count)
 			pos = mp_encode_uint(pos, IPROTO_FIELD_COLL);
 			pos = mp_encode_str(pos, coll, strlen(coll));
 		}
+		if (alias != NULL) {
+			pos = mp_encode_uint(pos, IPROTO_FIELD_ALIAS);
+			pos = mp_encode_str(pos, alias, strlen(alias));
+		}
 		if (nullable != -1) {
 			pos = mp_encode_uint(pos, IPROTO_FIELD_IS_NULLABLE);
 			pos = mp_encode_bool(pos, nullable);
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index 30d1af4cb..8e50e0cb1 100644
--- a/src/box/iproto_constants.h
+++ b/src/box/iproto_constants.h
@@ -134,6 +134,7 @@ enum iproto_metadata_key {
 	IPROTO_FIELD_COLL = 2,
 	IPROTO_FIELD_IS_NULLABLE = 3,
 	IPROTO_FIELD_IS_AUTOINCREMENT = 4,
+	IPROTO_FIELD_ALIAS = 5,
 };
 
 enum iproto_ballot_key {
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index e8e3e2a9f..9055d3a76 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -23,10 +23,11 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 		const char *coll = sql_column_coll(stmt, i);
 		const char *name = sql_column_name(stmt, i);
 		const char *type = sql_column_datatype(stmt, i);
+		const char *alias = sql_column_alias(stmt, i);
 		int nullable = sql_column_nullable(stmt, i);
 		bool is_autoincrement = sql_column_is_autoincrement(stmt, i);
-		size_t table_sz = 2 + (coll != NULL) + (nullable != -1) +
-				  is_autoincrement;
+		size_t table_sz = 2 + (coll != NULL) + (alias != NULL) +
+				  (nullable != -1) + is_autoincrement ;
 		lua_createtable(L, 0, table_sz);
 		/*
 		 * Can not fail, since all column names are
@@ -51,6 +52,10 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
 			lua_pushboolean(L, true);
 			lua_setfield(L, -2, "is_autoincrement");
 		}
+		if (alias != NULL) {
+			lua_pushstring(L, alias);
+			lua_setfield(L, -2, "alias");
+		}
 		lua_rawseti(L, -2, i + 1);
 	}
 }
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index 88ef4ff78..1bfe08f64 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -655,6 +655,10 @@ decode_metadata_optional(struct lua_State *L, const char **data,
 			bool is_nullable = mp_decode_bool(data);
 			lua_pushboolean(L, is_nullable);
 			lua_setfield(L, -2, "is_nullable");
+		} else if (key == IPROTO_FIELD_ALIAS) {
+			const char *alias = mp_decode_str(data, &len);
+			lua_pushlstring(L, alias, len);
+			lua_setfield(L, -2, "alias");
 		} else {
 			assert(key == IPROTO_FIELD_IS_AUTOINCREMENT);
 			bool autoincrement = mp_decode_bool(data);
@@ -676,7 +680,7 @@ 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 && map_size <= 5);
+		assert(map_size >= 2 && map_size <= 6);
 		uint32_t key = mp_decode_uint(data);
 		assert(key == IPROTO_FIELD_NAME);
 		(void) key;
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index d92da4d8e..c1770e7b4 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1827,7 +1827,19 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 			zCol = space_def->fields[iCol].name;
 			const char *name = NULL;
 			if (pEList->a[i].zName != NULL) {
-				name = pEList->a[i].zName;
+				if (is_full_meta) {
+					const char *alias = NULL;
+					if (pEList->a[i].zSpan != NULL) {
+						alias = pEList->a[i].zName;
+						name = pEList->a[i].zSpan;
+					} else {
+						alias = pEList->a[i].zName;
+						name = pEList->a[i].zName;
+					}
+					vdbe_metadata_set_col_alias(v, i, alias);
+				} else {
+					name = pEList->a[i].zName;
+				}
 			} else {
 				if (!shortNames && !fullNames) {
 					name = pEList->a[i].zSpan;
@@ -1854,9 +1866,21 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
 			}
 		} else {
 			const char *z = NULL;
-			if (pEList->a[i].zName != NULL)
-				z = pEList->a[i].zName;
-			else if (pEList->a[i].zSpan != NULL)
+			if (pEList->a[i].zName != NULL) {
+				if (is_full_meta ) {
+					const char *alias = NULL;
+					if (pEList->a[i].zSpan != NULL) {
+						alias = pEList->a[i].zName;
+						z = pEList->a[i].zSpan;
+					} else {
+						alias = pEList->a[i].zName;
+						z = pEList->a[i].zName;
+					}
+					vdbe_metadata_set_col_alias(v, i, alias);
+				} else {
+					z = pEList->a[i].zName;
+				}
+			} else if (pEList->a[i].zSpan != NULL)
 				z = pEList->a[i].zSpan;
 			else
 				z = tt_sprintf("column%d", i + 1);
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index e248bc673..b38b66b74 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -585,6 +585,9 @@ sql_column_nullable(sql_stmt *stmt, int n);
 bool
 sql_column_is_autoincrement(sql_stmt *stmt, int n);
 
+const char *
+sql_column_alias(sql_stmt *stmt, int n);
+
 int
 sql_initialize(void);
 
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index da9a311b8..f35f11241 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -263,6 +263,9 @@ vdbe_metadata_set_col_nullability(struct Vdbe *p, int idx, int nullable);
 void
 vdbe_metadata_set_col_autoincrement(struct Vdbe *p, int idx);
 
+int
+vdbe_metadata_set_col_alias(struct Vdbe *p, int idx, const char *alias);
+
 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 d79635b14..eaa40a35e 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -350,6 +350,7 @@ struct sql_column_metadata {
 	char *name;
 	char *type;
 	char *collation;
+	char *alias;
 	/**
 	 * -1 is for any member of result set except for pure
 	 * columns: all other expressions are nullable by default.
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 5b423c9df..47cd068ff 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -769,6 +769,14 @@ sql_column_is_autoincrement(sql_stmt *stmt, int n)
 	return p->metadata[n].is_actoincrement;
 }
 
+const char *
+sql_column_alias(sql_stmt *stmt, int n)
+{
+	struct Vdbe *p = (struct Vdbe *) stmt;
+	assert(n < sql_column_count(stmt) && n >= 0);
+	return p->metadata[n].alias;
+}
+
 /******************************* 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 e3672097c..625464902 100644
--- a/src/box/sql/vdbeaux.c
+++ b/src/box/sql/vdbeaux.c
@@ -1835,6 +1835,7 @@ vdbe_metadata_delete(struct Vdbe *v)
 			free(v->metadata[i].name);
 			free(v->metadata[i].type);
 			free(v->metadata[i].collation);
+			free(v->metadata[i].alias);
 		}
 		free(v->metadata);
 	}
@@ -1918,6 +1919,20 @@ vdbe_metadata_set_col_autoincrement(struct Vdbe *p, int idx)
 	p->metadata[idx].is_actoincrement = 1;
 }
 
+int
+vdbe_metadata_set_col_alias(struct Vdbe *p, int idx, const char *alias)
+{
+	assert(idx < p->nResColumn);
+	if (p->metadata[idx].alias != NULL)
+		free((void *)p->metadata[idx].alias);
+	p->metadata[idx].alias = strdup(alias);
+	if (p->metadata[idx].alias == NULL) {
+		diag_set(OutOfMemory, strlen(alias) + 1, "strdup", "alias");
+		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/full_metadata.result b/test/sql/full_metadata.result
index 7c2982682..8f0bced36 100644
--- a/test/sql/full_metadata.result
+++ b/test/sql/full_metadata.result
@@ -103,14 +103,17 @@ execute("SELECT id, a, c FROM t;")
 execute("SELECT * FROM t;")
  | ---
  | - metadata:
- |   - type: integer
+ |   - alias: ID
+ |     type: integer
  |     is_autoincrement: true
  |     name: ID
  |     is_nullable: false
  |   - type: integer
- |     name: A
  |     is_nullable: false
- |   - type: string
+ |     name: A
+ |     alias: A
+ |   - alias: C
+ |     type: string
  |     is_nullable: true
  |     name: C
  |     collation: unicode_ci
@@ -118,6 +121,35 @@ execute("SELECT * FROM t;")
  |   - [1, 1, 'aSd']
  | ...
 
+-- Alias is always set in extended metadata. If column label in
+-- form of AS clause is set, then this alias is presented in
+-- metadata. Otherwise, alias is just the same as name.
+--
+execute("SELECT 1 AS x;")
+ | ---
+ | - metadata:
+ |   - type: integer
+ |     name: '1'
+ |     alias: X
+ |   rows:
+ |   - [1]
+ | ...
+execute("SELECT a AS a_label, c AS c_label FROM t;")
+ | ---
+ | - metadata:
+ |   - type: integer
+ |     is_nullable: false
+ |     name: a
+ |     alias: A_LABEL
+ |   - alias: C_LABEL
+ |     type: string
+ |     is_nullable: true
+ |     name: c
+ |     collation: unicode_ci
+ |   rows:
+ |   - [1, 'aSd']
+ | ...
+
 execute("PRAGMA full_metadata = false;")
  | ---
  | - row_count: 0
diff --git a/test/sql/full_metadata.test.lua b/test/sql/full_metadata.test.lua
index e6bfa1eaf..533fa90c7 100644
--- a/test/sql/full_metadata.test.lua
+++ b/test/sql/full_metadata.test.lua
@@ -35,6 +35,13 @@ execute("SELECT c COLLATE \"unicode\" FROM t;")
 execute("SELECT id, a, c FROM t;")
 execute("SELECT * FROM t;")
 
+-- Alias is always set in extended metadata. If column label in
+-- form of AS clause is set, then this alias is presented in
+-- metadata. Otherwise, alias is just the same as name.
+--
+execute("SELECT 1 AS x;")
+execute("SELECT a AS a_label, c AS c_label FROM t;")
+
 execute("PRAGMA full_metadata = false;")
 
 test_run:cmd("setopt delimiter ';'")
-- 
2.15.1



More information about the Tarantool-patches mailing list