[Tarantool-patches] [PATCH 3/6] sql: extend result set with collation
Nikita Pettik
korablev at tarantool.org
Wed Nov 27 15:15:43 MSK 2019
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.
Part of #4407
---
src/box/execute.c | 31 +++++++--
src/box/iproto_constants.h | 1 +
src/box/lua/execute.c | 7 +-
src/box/lua/net_box.c | 20 +++++-
src/box/sql/select.c | 18 ++++++
src/box/sql/sqlInt.h | 3 +
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/collation.result | 155 +++++++++++++++++++++++++++------------------
11 files changed, 195 insertions(+), 70 deletions(-)
diff --git a/src/box/execute.c b/src/box/execute.c
index e8b012e5b..20bfd0957 100644
--- a/src/box/execute.c
+++ b/src/box/execute.c
@@ -267,6 +267,23 @@ error:
region_truncate(region, svp);
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.
@@ -291,9 +308,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 +318,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 76ecdd541..8a530bfc1 100644
--- a/src/box/lua/execute.c
+++ b/src/box/lua/execute.c
@@ -20,7 +20,8 @@ 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);
+ lua_createtable(L, 0, coll != NULL ? 3 : 2);
const char *name = sql_column_name(stmt, i);
const char *type = sql_column_datatype(stmt, i);
/*
@@ -34,6 +35,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..afbd1e1be 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,7 +667,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);
+ assert(map_size == 2 || map_size == 3);
(void) map_size;
uint32_t key = mp_decode_uint(data);
assert(key == IPROTO_FIELD_NAME);
@@ -665,6 +682,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/select.c b/src/box/sql/select.c
index d6b8a158f..66e8c1274 100644
--- a/src/box/sql/select.c
+++ b/src/box/sql/select.c
@@ -1794,6 +1794,22 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
var_pos[var_count++] = i;
vdbe_set_metadata_col_type(v, i,
field_type_strs[sql_expr_type(p)]);
+ if (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.
+ */
+ (void) sql_expr_coll(pParse, p, &unused, &id, &coll);
+ if (id != COLL_NONE) {
+ struct coll_id *coll_id = coll_by_id(id);
+ vdbe_set_metadata_col_collation(v, i,
+ coll_id->name,
+ coll_id->name_len);
+ }
+ }
if (pEList->a[i].zName) {
char *zName = pEList->a[i].zName;
vdbe_set_metadata_col_name(v, i, zName);
@@ -1811,6 +1827,7 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
if (!shortNames && !fullNames) {
vdbe_set_metadata_col_name(v, i,
pEList->a[i].zSpan);
+
} else if (fullNames) {
const char *zName = tt_sprintf("%s.%s",
space_def->name,
@@ -1819,6 +1836,7 @@ generate_column_metadata(struct Parse *pParse, struct SrcList *pTabList,
} else {
vdbe_set_metadata_col_name(v, i, zCol);
}
+
} else {
const char *z = pEList->a[i].zSpan;
if (z == NULL)
diff --git a/src/box/sql/sqlInt.h b/src/box/sql/sqlInt.h
index 2594b73e0..4c2e3ed73 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);
diff --git a/src/box/sql/vdbe.h b/src/box/sql/vdbe.h
index 4142fb6ba..5f042d7af 100644
--- a/src/box/sql/vdbe.h
+++ b/src/box/sql/vdbe.h
@@ -253,6 +253,10 @@ vdbe_set_metadata_col_name(struct Vdbe *v, int col_idx, const char *name);
int
vdbe_set_metadata_col_type(struct Vdbe *v, int col_idx, const char *type);
+int
+vdbe_set_metadata_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 9ab3753cb..fba86d664 100644
--- a/src/box/sql/vdbeInt.h
+++ b/src/box/sql/vdbeInt.h
@@ -349,6 +349,7 @@ struct ScanStatus {
struct sql_column_metadata {
const char *name;
const char *type;
+ const char *collation;
};
/*
diff --git a/src/box/sql/vdbeapi.c b/src/box/sql/vdbeapi.c
index d746a42f2..6895d0ad5 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 db11fbf33..1707df7f0 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((void *)v->metadata[i].name);
free((void *)v->metadata[i].type);
+ free((void *)v->metadata[i].collation);
}
free(v->metadata);
}
@@ -1888,6 +1889,21 @@ vdbe_set_metadata_col_type(struct Vdbe *p, int idx, const char *type)
return 0;
}
+int
+vdbe_set_metadata_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, "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/collation.result b/test/sql/collation.result
index 11962ef47..137a38bb4 100644
--- a/test/sql/collation.result
+++ b/test/sql/collation.result
@@ -85,10 +85,12 @@ box.execute([[SELECT descriptor, upper(letter COLLATE "TURKISH") AS upper,lower(
- metadata:
- name: DESCRIPTOR
type: string
- - name: UPPER
- type: string
- - name: LOWER
- type: string
+ - type: string
+ name: UPPER
+ collation: TURKISH
+ - type: string
+ name: LOWER
+ collation: TURKISH
rows:
- ['Latin Capital Letter I U+0049', 'I', 'ı']
- ['Latin Capital Letter I With Dot Above U+0130', 'İ', 'i']
@@ -125,8 +127,9 @@ box.execute([[SELECT descriptor, upper(letter COLLATE "GERMAN"), letter FROM tu
- metadata:
- name: DESCRIPTOR
type: string
- - name: upper(letter COLLATE "GERMAN")
- type: string
+ - type: string
+ name: upper(letter COLLATE "GERMAN")
+ collation: GERMAN
- name: LETTER
type: string
rows:
@@ -237,10 +240,12 @@ box.execute("SELECT * FROM t WHERE a = b;")
type: integer
- name: A
type: string
- - name: B
- type: string
- - name: C
- type: string
+ - type: string
+ name: B
+ collation: binary
+ - type: string
+ name: C
+ collation: unicode_ci
rows: []
...
box.execute("SELECT * FROM t WHERE a COLLATE \"binary\" = b;")
@@ -250,10 +255,12 @@ box.execute("SELECT * FROM t WHERE a COLLATE \"binary\" = b;")
type: integer
- name: A
type: string
- - name: B
- type: string
- - name: C
- type: string
+ - type: string
+ name: B
+ collation: binary
+ - type: string
+ name: C
+ collation: unicode_ci
rows: []
...
box.execute("SELECT * FROM t WHERE b = c;")
@@ -268,10 +275,12 @@ box.execute("SELECT * FROM t WHERE b COLLATE \"binary\" = c;")
type: integer
- name: A
type: string
- - name: B
- type: string
- - name: C
- type: string
+ - type: string
+ name: B
+ collation: binary
+ - type: string
+ name: C
+ collation: unicode_ci
rows: []
...
box.execute("SELECT * FROM t WHERE a = c;")
@@ -281,10 +290,12 @@ box.execute("SELECT * FROM t WHERE a = c;")
type: integer
- name: A
type: string
- - name: B
- type: string
- - name: C
- type: string
+ - type: string
+ name: B
+ collation: binary
+ - type: string
+ name: C
+ collation: unicode_ci
rows: []
...
box.execute("SELECT * FROM t WHERE a COLLATE \"binary\" = c COLLATE \"unicode\";")
@@ -313,8 +324,9 @@ box.execute("SELECT c FROM t UNION SELECT b FROM t;")
box.execute("SELECT b FROM t UNION SELECT a FROM t;")
---
- metadata:
- - name: B
- type: string
+ - type: string
+ name: B
+ collation: binary
rows: []
...
box.execute("SELECT a FROM t UNION SELECT c FROM t;")
@@ -327,15 +339,17 @@ box.execute("SELECT a FROM t UNION SELECT c FROM t;")
box.execute("SELECT c COLLATE \"binary\" FROM t UNION SELECT a FROM t;")
---
- metadata:
- - name: c COLLATE "binary"
- type: string
+ - type: string
+ name: c COLLATE "binary"
+ collation: binary
rows: []
...
box.execute("SELECT b COLLATE \"unicode\" FROM t UNION SELECT a FROM t;")
---
- metadata:
- - name: b COLLATE "unicode"
- type: string
+ - type: string
+ name: b COLLATE "unicode"
+ collation: unicode
rows: []
...
box.execute("DROP TABLE t;")
@@ -392,8 +406,9 @@ box.execute("UPDATE t0 SET s1 = 'A';")
box.execute("SELECT s1 FROM t0;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode_ci
rows:
- ['A']
...
@@ -402,8 +417,9 @@ box.execute("SELECT * FROM t1;")
- metadata:
- name: S1
type: integer
- - name: S0
- type: string
+ - type: string
+ name: S0
+ collation: unicode_ci
rows:
- [1, 'a']
...
@@ -586,8 +602,9 @@ box.execute("INSERT INTO t4b VALUES('gHz', 'xxx', 4);")
box.execute("SELECT a FROM t4b ORDER BY a COLLATE \"unicode_ci\" || ''")
---
- metadata:
- - name: A
- type: string
+ - type: string
+ name: A
+ collation: unicode_ci
rows:
- ['abc']
- ['BCD']
@@ -597,8 +614,9 @@ box.execute("SELECT a FROM t4b ORDER BY a COLLATE \"unicode_ci\" || ''")
box.execute("SELECT a FROM t4b ORDER BY a || b")
---
- metadata:
- - name: A
- type: string
+ - type: string
+ name: A
+ collation: unicode_ci
rows:
- ['abc']
- ['BCD']
@@ -662,8 +680,9 @@ box.execute("INSERT INTO t3b VALUES ('A');")
box.execute("SELECT * FROM t3b;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: binary
rows:
- ['A']
- ['a']
@@ -697,8 +716,9 @@ box.execute("INSERT INTO t3b VALUES ('a');")
box.execute("SELECT * FROM t3b;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: binary
rows:
- ['A']
- ['a']
@@ -727,8 +747,9 @@ box.execute("INSERT INTO t3c VALUES ('A');")
box.execute("SELECT * FROM t3c;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode
rows:
- ['a']
- ['A']
@@ -762,8 +783,9 @@ box.execute("INSERT INTO t3c VALUES ('a');")
box.execute("SELECT * FROM t3c;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode
rows:
- ['a']
- ['A']
@@ -792,8 +814,9 @@ box.execute("INSERT INTO t3d VALUES ('A');")
box.execute("SELECT * FROM t3d;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: binary
rows:
- ['A']
- ['a']
@@ -827,8 +850,9 @@ box.execute("INSERT INTO t3d VALUES ('a');")
box.execute("SELECT * FROM t3d;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: binary
rows:
- ['A']
- ['a']
@@ -858,8 +882,9 @@ box.execute("INSERT INTO t3e VALUES ('A');")
box.execute("SELECT * FROM t3e;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode_ci
rows:
- ['a']
...
@@ -887,8 +912,9 @@ box.execute("INSERT INTO t3f VALUES ('A');")
box.execute("SELECT * FROM t3f;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode
rows:
- ['a']
- ['A']
@@ -922,8 +948,9 @@ box.execute("INSERT INTO t3f VALUES ('a');")
box.execute("SELECT * FROM t3f;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode
rows:
- ['a']
- ['A']
@@ -953,8 +980,9 @@ box.execute("INSERT INTO t3g VALUES ('A');")
box.execute("SELECT * FROM t3g;")
---
- metadata:
- - name: S1
- type: string
+ - type: string
+ name: S1
+ collation: unicode_ci
rows:
- ['a']
...
@@ -1118,8 +1146,9 @@ box.execute("INSERT INTO jj VALUES (1,'A'), (2,'a')")
box.execute("SELECT DISTINCT trim(s2) FROM jj;")
---
- metadata:
- - name: trim(s2)
- type: string
+ - type: string
+ name: trim(s2)
+ collation: unicode_ci
rows:
- ['A']
...
@@ -1130,8 +1159,9 @@ box.execute("INSERT INTO jj VALUES (3, 'aS'), (4, 'AS');")
box.execute("SELECT DISTINCT replace(s2, 'S', 's') FROM jj;")
---
- metadata:
- - name: replace(s2, 'S', 's')
- type: string
+ - type: string
+ name: replace(s2, 'S', 's')
+ collation: unicode_ci
rows:
- ['A']
- ['as']
@@ -1139,8 +1169,9 @@ box.execute("SELECT DISTINCT replace(s2, 'S', 's') FROM jj;")
box.execute("SELECT DISTINCT substr(s2, 1, 1) FROM jj;")
---
- metadata:
- - name: substr(s2, 1, 1)
- type: string
+ - type: string
+ name: substr(s2, 1, 1)
+ collation: unicode_ci
rows:
- ['A']
...
--
2.15.1
More information about the Tarantool-patches
mailing list