[Tarantool-patches] [PATCH] sql: extend result set with span
Nikita Pettik
korablev at tarantool.org
Sun Dec 29 01:56:55 MSK 2019
Each column of result set features its name span (in full metadata
mode). For instance:
SELECT x + 1 AS add FROM ...;
In this case real name (span) of resulting set column is "x + 1"
meanwhile "add" is its alias. This patch extends metadata with
member which corresponds to column's original expression.
It is worth mentioning that in most cases span coincides with name, so
to avoid overhead and sending the same string twice, we follow the rule
that if span is encoded as MP_NIL then its value is the same as name.
Also note that span is always presented in full metadata mode.
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;
- span (is always set in full metadata mode; it is an original
expression forming result set column. For instance:
SELECT a + 1 AS x; -- x is a name, meanwhile a + 1 is a span);
is encoded with IPROTO_FIELD_SPAN (0x5) key in IPROTO_METADATA map;
in msgpack is encoded as string and held with MP_STR type OR
as NIL with MP_NIL type. The latter case indicates that span
coincides with name. This simple optimization allows to avoid
sending the same string twice.
This extended metadata is send only when PRAGMA full_metadata is
enabled. Otherwise, only basic (name and type) metadata is processed.
---
Branch: https://github.com/tarantool/tarantool/tree/np/gh-4407-extend-sql-metadata-v2
Issue: https://github.com/tarantool/tarantool/issues/4407
src/box/execute.c | 33 ++++++++++++--
src/box/iproto_constants.h | 1 +
src/box/lua/execute.c | 10 +++++
src/box/lua/net_box.c | 34 ++++++++++----
src/box/sql/select.c | 10 +++++
src/box/sql/sqlInt.h | 6 +++
src/box/sql/vdbe.h | 3 ++
src/box/sql/vdbeInt.h | 6 +++
src/box/sql/vdbeapi.c | 15 +++++++
src/box/sql/vdbeaux.c | 19 ++++++++
test/sql/full_metadata.result | 99 ++++++++++++++++++++++++++++++++++++++---
test/sql/full_metadata.test.lua | 11 +++++
12 files changed, 230 insertions(+), 17 deletions(-)
diff --git a/src/box/execute.c b/src/box/execute.c
index c70935d01..3034cee86 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -270,7 +270,7 @@ error:
static inline size_t
metadata_map_sizeof(const char *name, const char *type, const char *coll,
- int nullable, bool is_autoincrement)
+ const char *span, int nullable, bool is_autoincrement)
{
uint32_t members_count = 2;
size_t map_size = 0;
@@ -289,6 +289,12 @@ metadata_map_sizeof(const char *name, const char *type, const char *coll,
map_size += mp_sizeof_uint(IPROTO_FIELD_IS_AUTOINCREMENT);
map_size += mp_sizeof_bool(true);
}
+ if (sql_metadata_is_full()) {
+ members_count++;
+ map_size += mp_sizeof_uint(IPROTO_FIELD_SPAN);
+ map_size += span != NULL ? mp_sizeof_str(strlen(span)) :
+ mp_sizeof_nil();
+ }
map_size += mp_sizeof_uint(IPROTO_FIELD_NAME);
map_size += mp_sizeof_uint(IPROTO_FIELD_TYPE);
map_size += mp_sizeof_str(strlen(name));
@@ -299,10 +305,13 @@ metadata_map_sizeof(const char *name, const char *type, const char *coll,
static inline void
metadata_map_encode(char *buf, const char *name, const char *type,
- const char *coll, int nullable, bool is_autoincrement)
+ const char *coll, const char *span, int nullable,
+ bool is_autoincrement)
{
uint32_t map_sz = 2 + (coll != NULL) + (nullable != -1) +
is_autoincrement;
+ if (sql_metadata_is_full())
+ map_sz++;
buf = mp_encode_map(buf, map_sz);
buf = mp_encode_uint(buf, IPROTO_FIELD_NAME);
buf = mp_encode_str(buf, name, strlen(name));
@@ -320,6 +329,21 @@ metadata_map_encode(char *buf, const char *name, const char *type,
buf = mp_encode_uint(buf, IPROTO_FIELD_IS_AUTOINCREMENT);
buf = mp_encode_bool(buf, true);
}
+ if (sql_metadata_is_full()) {
+ /*
+ * Span is an original expression that forms
+ * result set column. In most cases it is the
+ * same as column name. So to avoid sending
+ * the same string twice simply encode it as
+ * a nil and account this behaviour on client
+ * side (see decode_metadata_optional()).
+ */
+ buf = mp_encode_uint(buf, IPROTO_FIELD_SPAN);
+ if (span != NULL)
+ buf = mp_encode_str(buf, span, strlen(span));
+ else
+ buf = mp_encode_nil(buf);
+ }
}
/**
@@ -348,6 +372,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 *span = sql_column_span(stmt, i);
int nullable = sql_column_nullable(stmt, i);
bool is_autoincrement = sql_column_is_autoincrement(stmt, i);
/*
@@ -357,14 +382,14 @@ 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, span, nullable,
is_autoincrement);
char *pos = (char *) obuf_alloc(out, size);
if (pos == NULL) {
diag_set(OutOfMemory, size, "obuf_alloc", "pos");
return -1;
}
- metadata_map_encode(pos, name, type, coll, nullable,
+ metadata_map_encode(pos, name, type, coll, span, nullable,
is_autoincrement);
}
return 0;
diff --git a/src/box/iproto_constants.h b/src/box/iproto_constants.h
index 30d1af4cb..3808c6f28 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_SPAN = 5,
};
enum iproto_ballot_key {
diff --git a/src/box/lua/execute.c b/src/box/lua/execute.c
index e8e3e2a9f..5ed7b9bd3 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -23,10 +23,13 @@ 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 *span = sql_column_span(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;
+ if (sql_metadata_is_full())
+ table_sz++;
lua_createtable(L, 0, table_sz);
/*
* Can not fail, since all column names are
@@ -51,6 +54,13 @@ lua_sql_get_metadata(struct sql_stmt *stmt, struct lua_State *L,
lua_pushboolean(L, true);
lua_setfield(L, -2, "is_autoincrement");
}
+ if (sql_metadata_is_full()) {
+ if (span != NULL)
+ lua_pushstring(L, span);
+ else
+ lua_pushstring(L, name);
+ lua_setfield(L, -2, "span");
+ }
lua_rawseti(L, -2, i + 1);
}
}
diff --git a/src/box/lua/net_box.c b/src/box/lua/net_box.c
index c22848874..6db506b57 100644
--- a/src/box/lua/net_box.c
+++ b/src/box/lua/net_box.c
@@ -641,7 +641,7 @@ netbox_decode_select(struct lua_State *L)
/** 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)
+ uint32_t map_size, const char *name, uint32_t name_len)
{
/* 2 is default metadata map size (field name + field size). */
while (map_size-- > 2) {
@@ -655,6 +655,24 @@ 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_SPAN) {
+ /*
+ * There's an agreement: if span is not
+ * presented in metadata (encoded as NIL),
+ * then it is the same as name. It allows
+ * avoid sending the same string twice.
+ */
+ const char *span = NULL;
+ if (mp_typeof(**data) == MP_STR) {
+ span = mp_decode_str(data, &len);
+ } else {
+ assert(mp_typeof(**data) == MP_NIL);
+ mp_decode_nil(data);
+ span = name;
+ len = name_len;
+ }
+ lua_pushlstring(L, span, len);
+ lua_setfield(L, -2, "span");
} else {
assert(key == IPROTO_FIELD_IS_AUTOINCREMENT);
bool is_autoincrement = mp_decode_bool(data);
@@ -676,21 +694,21 @@ 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;
lua_createtable(L, 0, map_size);
- uint32_t len;
- const char *str = mp_decode_str(data, &len);
- lua_pushlstring(L, str, len);
+ uint32_t name_len, type_len;
+ const char *str = mp_decode_str(data, &name_len);
+ lua_pushlstring(L, str, name_len);
lua_setfield(L, -2, "name");
key = mp_decode_uint(data);
assert(key == IPROTO_FIELD_TYPE);
- const char *type = mp_decode_str(data, &len);
- lua_pushlstring(L, type, len);
+ const char *type = mp_decode_str(data, &type_len);
+ lua_pushlstring(L, type, type_len);
lua_setfield(L, -2, "type");
- decode_metadata_optional(L, data, map_size);
+ decode_metadata_optional(L, data, map_size, str, name_len);
lua_rawseti(L, -2, i + 1);
}
}
diff --git a/src/box/sql/select.c b/src/box/sql/select.c
index a19494ed9..b4182b88a 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1849,6 +1849,10 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
if (space->sequence != NULL &&
space->sequence_fieldno == (uint32_t) iCol)
vdbe_metadata_set_col_autoincrement(v, i);
+ if (pEList->a[i].zName != NULL) {
+ vdbe_metadata_set_col_span(v, i,
+ pEList->a[i].zSpan);
+ }
}
} else {
const char *z = NULL;
@@ -1859,6 +1863,12 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
else
z = tt_sprintf("column%d", i + 1);
vdbe_metadata_set_col_name(v, i, z);
+ if (is_full_meta) {
+ if (pEList->a[i].zName != NULL) {
+ vdbe_metadata_set_col_span(v, i,
+ pEList->a[i].zSpan);
+ }
+ }
}
}
if (var_count == 0)
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index e248bc673..250f6a2cd 100644
--- a/src/box/sql/sqlInt.h
+++ b/src/box/sql/sqlInt.h
@@ -536,6 +536,9 @@ sql_finalize(sql_stmt * pStmt);
int
sql_reset(struct sql_stmt *stmt);
+bool
+sql_metadata_is_full();
+
int
sql_exec(sql *, /* An open database */
const char *sql, /* SQL to be evaluated */
@@ -585,6 +588,9 @@ sql_column_nullable(sql_stmt *stmt, int n);
bool
sql_column_is_autoincrement(sql_stmt *stmt, int n);
+const char *
+sql_column_span(sql_stmt *stmt, int n);
+
int
sql_initialize(void);
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index 1e585d89d..79fe6f95d 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_span(struct Vdbe *p, int idx, const char *span);
+
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 fcdcd9b70..1022ec040 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -357,6 +357,12 @@ struct sql_column_metadata {
int8_t nullable;
/** True if column features autoincrement property. */
bool is_actoincrement;
+ /**
+ * Span is an original expression forming result set
+ * column. In most cases it is the same as name; it is
+ * different only in case of presence of AS clause.
+ */
+ char *span;
};
/*
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index 5b423c9df..559251140 100644
--- a/src/box/sql/vdbeapi.c
+++ b/src/box/sql/vdbeapi.c
@@ -36,6 +36,7 @@
*/
#include "sqlInt.h"
#include "vdbeInt.h"
+#include "box/session.h"
/*
* Invoke the profile callback. This routine is only called if we already
@@ -115,6 +116,12 @@ sql_clear_bindings(sql_stmt * pStmt)
return rc;
}
+bool
+sql_metadata_is_full()
+{
+ return current_session()->sql_flags & SQL_FullMetadata;
+}
+
/**************************** sql_value_ ******************************
* The following routines extract information from a Mem or sql_value
* structure.
@@ -769,6 +776,14 @@ sql_column_is_autoincrement(sql_stmt *stmt, int n)
return p->metadata[n].is_actoincrement;
}
+const char *
+sql_column_span(sql_stmt *stmt, int n)
+{
+ struct Vdbe *p = (struct Vdbe *) stmt;
+ assert(n < sql_column_count(stmt) && n >= 0);
+ return p->metadata[n].span;
+}
+
/******************************* 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 5b4bc0182..d4db6aca2 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].span);
}
free(v->metadata);
}
@@ -1921,6 +1922,24 @@ vdbe_metadata_set_col_autoincrement(struct Vdbe *p, int idx)
p->metadata[idx].is_actoincrement = true;
}
+int
+vdbe_metadata_set_col_span(struct Vdbe *p, int idx, const char *span)
+{
+ assert(idx < p->nResColumn);
+ if (p->metadata[idx].span != NULL)
+ free((void *)p->metadata[idx].span);
+ if (span == NULL) {
+ p->metadata[idx].span = NULL;
+ return 0;
+ }
+ p->metadata[idx].span = strdup(span);
+ if (p->metadata[idx].span == NULL) {
+ diag_set(OutOfMemory, strlen(span) + 1, "strdup", "span");
+ 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 463108001..ca69f4145 100644
--- a/test/sql/full_metadata.result
+++ b/test/sql/full_metadata.result
@@ -56,6 +56,7 @@ execute("SELECT 'aSd' COLLATE \"unicode_ci\";")
| ---
| - metadata:
| - type: string
+ | span: '''aSd'' COLLATE "unicode_ci"'
| name: '''aSd'' COLLATE "unicode_ci"'
| collation: unicode_ci
| rows:
@@ -64,7 +65,8 @@ execute("SELECT 'aSd' COLLATE \"unicode_ci\";")
execute("SELECT c FROM t;")
| ---
| - metadata:
- | - type: string
+ | - span: C
+ | type: string
| is_nullable: true
| name: C
| collation: unicode_ci
@@ -75,6 +77,7 @@ execute("SELECT c COLLATE \"unicode\" FROM t;")
| ---
| - metadata:
| - type: string
+ | span: c COLLATE "unicode"
| name: c COLLATE "unicode"
| collation: unicode
| rows:
@@ -86,14 +89,17 @@ execute("SELECT c COLLATE \"unicode\" FROM t;")
execute("SELECT id, a, c FROM t;")
| ---
| - metadata:
- | - type: integer
+ | - span: ID
+ | type: integer
| is_autoincrement: true
| name: ID
| is_nullable: false
| - type: integer
+ | span: A
| name: A
| is_nullable: false
- | - type: string
+ | - span: C
+ | type: string
| is_nullable: true
| name: C
| collation: unicode_ci
@@ -103,14 +109,17 @@ execute("SELECT id, a, c FROM t;")
execute("SELECT * FROM t;")
| ---
| - metadata:
- | - type: integer
+ | - span: ID
+ | type: integer
| is_autoincrement: true
| name: ID
| is_nullable: false
| - type: integer
+ | span: A
| name: A
| is_nullable: false
- | - type: string
+ | - span: C
+ | type: string
| is_nullable: true
| name: C
| collation: unicode_ci
@@ -118,6 +127,83 @@ execute("SELECT * FROM t;")
| - [1, 1, 'aSd']
| ...
+-- Span is always set in extended metadata. Span is an original
+-- expression forming result set column.
+--
+execute("SELECT 1 AS x;")
+ | ---
+ | - metadata:
+ | - type: integer
+ | span: '1'
+ | name: X
+ | rows:
+ | - [1]
+ | ...
+execute("SELECT *, id + 1 AS x, a AS y, c || 'abc' FROM t;")
+ | ---
+ | - metadata:
+ | - span: ID
+ | type: integer
+ | is_autoincrement: true
+ | name: ID
+ | is_nullable: false
+ | - type: integer
+ | span: A
+ | name: A
+ | is_nullable: false
+ | - span: C
+ | type: string
+ | is_nullable: true
+ | name: C
+ | collation: unicode_ci
+ | - type: integer
+ | span: id + 1
+ | name: X
+ | - type: integer
+ | span: a
+ | name: Y
+ | is_nullable: false
+ | - type: string
+ | span: c || 'abc'
+ | name: c || 'abc'
+ | rows:
+ | - [1, 1, 'aSd', 2, 1, 'aSdabc']
+ | ...
+
+box.execute("CREATE VIEW v AS SELECT id + 1 AS x, a AS y, c || 'abc' FROM t;")
+ | ---
+ | - row_count: 1
+ | ...
+execute("SELECT * FROM v;")
+ | ---
+ | - metadata:
+ | - type: integer
+ | span: X
+ | name: X
+ | - type: integer
+ | span: Y
+ | name: Y
+ | is_nullable: false
+ | - type: string
+ | span: c || 'abc'
+ | name: c || 'abc'
+ | rows:
+ | - [2, 1, 'aSdabc']
+ | ...
+execute("SELECT x, y FROM v;")
+ | ---
+ | - metadata:
+ | - type: integer
+ | span: x
+ | name: X
+ | - type: integer
+ | span: y
+ | name: Y
+ | is_nullable: false
+ | rows:
+ | - [2, 1]
+ | ...
+
execute("PRAGMA full_metadata = false;")
| ---
| - row_count: 0
@@ -139,6 +225,9 @@ test_run:cmd("setopt delimiter ''");
| - true
| ...
+box.space.V:drop()
+ | ---
+ | ...
box.space.T:drop()
| ---
| ...
diff --git a/test/sql/full_metadata.test.lua b/test/sql/full_metadata.test.lua
index e3b30f7e7..576c49ad3 100644
--- a/test/sql/full_metadata.test.lua
+++ b/test/sql/full_metadata.test.lua
@@ -35,6 +35,16 @@ execute("SELECT c COLLATE \"unicode\" FROM t;")
execute("SELECT id, a, c FROM t;")
execute("SELECT * FROM t;")
+-- Span is always set in extended metadata. Span is an original
+-- expression forming result set column.
+--
+execute("SELECT 1 AS x;")
+execute("SELECT *, id + 1 AS x, a AS y, c || 'abc' FROM t;")
+
+box.execute("CREATE VIEW v AS SELECT id + 1 AS x, a AS y, c || 'abc' FROM t;")
+execute("SELECT * FROM v;")
+execute("SELECT x, y FROM v;")
+
execute("PRAGMA full_metadata = false;")
test_run:cmd("setopt delimiter ';'")
@@ -45,4 +55,5 @@ if remote then
end;
test_run:cmd("setopt delimiter ''");
+box.space.V:drop()
box.space.T:drop()
--
2.15.1
More information about the Tarantool-patches
mailing list