From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from smtpng2.m.smailru.net (smtpng2.m.smailru.net [94.100.179.3]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by dev.tarantool.org (Postfix) with ESMTPS id 32A804696C4 for ; Wed, 11 Dec 2019 16:45:06 +0300 (MSK) From: Nikita Pettik Date: Wed, 11 Dec 2019 16:44:58 +0300 Message-Id: <0164c69c939fb61b7e6255c4cb695562d05fdef5.1576071711.git.korablev@tarantool.org> In-Reply-To: References: In-Reply-To: References: Subject: [Tarantool-patches] [PATCH v2 6/6] sql: extend result set with alias List-Id: Tarantool development patches List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , To: tarantool-patches@dev.tarantool.org Cc: v.shpilevoy@tarantool.org 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