* [tarantool-patches] Re: [PATCH] sql: change of PRAGMA INDEX_INFO syntax
2018-10-09 16:02 ` [tarantool-patches] " n.pettik
@ 2018-10-12 9:48 ` Alexander Turenko
2018-10-12 13:25 ` n.pettik
0 siblings, 1 reply; 6+ messages in thread
From: Alexander Turenko @ 2018-10-12 9:48 UTC (permalink / raw)
To: n.pettik; +Cc: tarantool-patches
Thanks for the review.
Answers, the diff from the previous version and the new full diff are
below.
WBR, Alexander Turenko.
> > This change removes 'pragma index_xinfo' syntax. 'pragma index_info'
> > now works as 'pragma index_xinfo' and also displays type of columns in
> > index.
> >
> > Cleaned up pragma column names array (pragma.h::pragCName).
> >
> > Fixes #3194
> > —
>
I have updated the commit message to the following:
> sql: update INDEX_INFO pragma
>
> Removed INDEX_XINFO pragma and updated INDEX_INFO with its columns with
> the following corrections: replaced 6th result column (whether an index
> column is key/aux one) with type name of the index column.
>
> Cleaned up pragma column names array (pragma.h::pragCName).
>
> Fixes #3194.
> Could you rebase on the latest 2.0? At current state I can’t build it on MacOS:
> <...>
Thanks for catching it up. Rebased.
> > diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
> > index 4f64ab6f2..0eacef5c0 100644
> > --- a/src/box/sql/pragma.c
> > +++ b/src/box/sql/pragma.c
> > @@ -322,8 +322,7 @@ sql_pragma_table_stats(struct space *space, void *data)
> > }
> >
> > /**
> > - * This function handles PRAGMA INDEX_INFO and PRAGMA INDEX_XINFO
> > - * statements.
> > + * This function handles PRAGMA INDEX_INFO statement.
>
> Nit: it would be great if you put here (explanation of) format of
> displayed output. For example see sql_pragma_table_info().
>
Done. See diff below.
> > for (uint32_t i = 0; i < part_count; i++, part++) {
> > - sqlite3VdbeMultiLoad(v, 1, "iis", i, part->fieldno,
> > - space->def->fields[part->fieldno].name);
> > - if (pragma->iArg > 0) {
> > - const char *c_n;
> > - uint32_t id = part->coll_id;
> > - struct coll *coll = part->coll;
> > - if (coll != NULL)
> > - c_n = coll_by_id(id)->name;
> > - else
> > - c_n = "BINARY";
> > - sqlite3VdbeMultiLoad(v, 4, "isi", part->sort_order,
> > - c_n, i < part_count);
> > - }
> > + const char *c_n;
> > + uint32_t id = part->coll_id;
> > + struct coll *coll = part->coll;
> > + if (coll != NULL)
> > + c_n = coll_by_id(id)->name;
> > + else
> > + c_n = "BINARY";
> > + uint32_t fieldno = part->fieldno;
> > + enum field_type type = space->def->fields[fieldno].type;
> > + sqlite3VdbeMultiLoad(v, 1, "iisisis", i, fieldno,
> > + space->def->fields[fieldno].name,
> > + part->sort_order, c_n, i < part_count,
>
> But i < part_count is always true, isn’t it?
>
Yep, we now have no non-key columns in indexes. Removed. See diff below.
> > /* Definitions of all built-in pragmas */
> > @@ -97,11 +82,13 @@ typedef struct PragmaName {
> > u8 nPragCName; /* Num of col names. 0 means use pragma name */
> > u32 iArg; /* Extra argument */
> > } PragmaName;
> > +/* The order of pragmas in this array is important: it has */
> > +/* to be sorted. For more info see pragma_locate function. */
>
> Nit:
>
> -/* The order of pragmas in this array is important: it has */
> -/* to be sorted. For more info see pragma_locate function. */
> +
> +/**
> + * The order of pragmas in this array is important: it has
> + * to be sorted. For more info see pragma_locate function.
> + */
>
Done.
Diff from the previous version
------------------------------
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index 506782db0..4265ace25 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -322,15 +322,26 @@ sql_pragma_table_stats(struct space *space, void *data)
}
/**
- * This function handles PRAGMA INDEX_INFO statement.
+ * This function handles PRAGMA INDEX_INFO(<table>.<index>).
+ *
+ * Return a single row for each column of the index.
+ * The columns of the returned data set are:
+ *
+ * - seqno: Zero-based column id within the index.
+ * - cid: Zero-based column id within the table.
+ * - name: Table column name.
+ * - desc: Whether sorting by the column is descending (1 or 0).
+ * - coll: Collation name.
+ * - type: Type of a column value.
*
* @param parse Current parsing content.
* @param pragma Definition of index_info pragma.
- * @param table_name Name of table index belongs to.
+ * @param tbl_name Name of table index belongs to.
* @param idx_name Name of index to display info about.
*/
static void
-sql_pragma_index_info(struct Parse *parse, const PragmaName *pragma,
+sql_pragma_index_info(struct Parse *parse,
+ MAYBE_UNUSED const PragmaName *pragma,
const char *tbl_name, const char *idx_name)
{
if (idx_name == NULL || tbl_name == NULL)
@@ -343,12 +354,12 @@ sql_pragma_index_info(struct Parse *parse, const PragmaName *pragma,
if (space->def->opts.sql == NULL)
return;
uint32_t iid = box_index_id_by_name(space_id, idx_name,
- strlen(idx_name));
+ strlen(idx_name));
if (iid == BOX_ID_NIL)
return;
struct index *idx = space_index(space, iid);
assert(idx != NULL);
- parse->nMem = 7;
+ parse->nMem = 6;
struct Vdbe *v = sqlite3GetVdbe(parse);
assert(v != NULL);
uint32_t part_count = idx->def->key_def->part_count;
@@ -364,9 +375,9 @@ sql_pragma_index_info(struct Parse *parse, const PragmaName *pragma,
c_n = "BINARY";
uint32_t fieldno = part->fieldno;
enum field_type type = space->def->fields[fieldno].type;
- sqlite3VdbeMultiLoad(v, 1, "iisisis", i, fieldno,
+ sqlite3VdbeMultiLoad(v, 1, "iisiss", i, fieldno,
space->def->fields[fieldno].name,
- part->sort_order, c_n, i < part_count,
+ part->sort_order, c_n,
field_type_strs[type]);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, parse->nMem);
}
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index 1d7a1f053..e60801608 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -50,28 +50,27 @@ static const char *const pragCName[] = {
/* 12 */ "name",
/* 13 */ "desc",
/* 14 */ "coll",
- /* 15 */ "key",
- /* 16 */ "type",
+ /* 15 */ "type",
/* Used by: index_list */
- /* 17 */ "seq",
- /* 18 */ "name",
- /* 19 */ "unique",
- /* 20 */ "origin",
- /* 21 */ "partial",
+ /* 16 */ "seq",
+ /* 17 */ "name",
+ /* 18 */ "unique",
+ /* 19 */ "origin",
+ /* 20 */ "partial",
/* Used by: collation_list */
- /* 22 */ "seq",
- /* 23 */ "name",
+ /* 21 */ "seq",
+ /* 22 */ "name",
/* Used by: foreign_key_list */
- /* 24 */ "id",
- /* 25 */ "seq",
- /* 26 */ "table",
- /* 27 */ "from",
- /* 28 */ "to",
- /* 29 */ "on_update",
- /* 30 */ "on_delete",
- /* 31 */ "match",
+ /* 23 */ "id",
+ /* 24 */ "seq",
+ /* 25 */ "table",
+ /* 26 */ "from",
+ /* 27 */ "to",
+ /* 28 */ "on_update",
+ /* 29 */ "on_delete",
+ /* 30 */ "match",
/* Used by: busy_timeout */
- /* 32 */ "timeout",
+ /* 31 */ "timeout",
};
/* Definitions of all built-in pragmas */
@@ -83,13 +82,15 @@ typedef struct PragmaName {
u8 nPragCName; /* Num of col names. 0 means use pragma name */
u32 iArg; /* Extra argument */
} PragmaName;
-/* The order of pragmas in this array is important: it has */
-/* to be sorted. For more info see pragma_locate function. */
+/**
+ * The order of pragmas in this array is important: it has
+ * to be sorted. For more info see pragma_locate function.
+ */
static const PragmaName aPragmaName[] = {
{ /* zName: */ "busy_timeout",
/* ePragTyp: */ PragTyp_BUSY_TIMEOUT,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 32, 1,
+ /* ColNames: */ 31, 1,
/* iArg: */ 0},
{ /* zName: */ "case_sensitive_like",
/* ePragTyp: */ PragTyp_CASE_SENSITIVE_LIKE,
@@ -100,7 +101,7 @@ static const PragmaName aPragmaName[] = {
{ /* zName: */ "collation_list",
/* ePragTyp: */ PragTyp_COLLATION_LIST,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 22, 2,
+ /* ColNames: */ 21, 2,
/* iArg: */ 0},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
@@ -121,7 +122,7 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 24, 8,
+ /* ColNames: */ 23, 8,
/* iArg: */ 0},
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "full_column_names",
@@ -135,13 +136,13 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_INDEX_INFO,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 10, 7,
+ /* ColNames: */ 10, 6,
/* iArg: */ 1},
{ /* zName: */ "index_list",
/* ePragTyp: */ PragTyp_INDEX_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 17, 5,
+ /* ColNames: */ 16, 5,
/* iArg: */ 0},
#endif
#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_PARSER_TRACE)
diff --git a/test/sql-tap/index-info.test.lua b/test/sql-tap/index-info.test.lua
index 198a0092d..5a2eb2b36 100755
--- a/test/sql-tap/index-info.test.lua
+++ b/test/sql-tap/index-info.test.lua
@@ -34,7 +34,7 @@ test:do_execsql_test(
"index-info-1.3",
"PRAGMA index_info (t1.a);",
{
- 0, 0, 'A', 0, 'BINARY', 1, 'integer',
+ 0, 0, 'A', 0, 'BINARY', 'integer',
})
-- Case: multiple columns index with integer columns.
@@ -42,9 +42,9 @@ test:do_execsql_test(
"index-info-1.4",
"PRAGMA index_info (t1.abc);",
{
- 0, 0, 'A', 0, 'BINARY', 1, 'integer',
- 1, 1, 'B', 0, 'BINARY', 1, 'scalar',
- 2, 2, 'C', 0, 'BINARY', 1, 'scalar',
+ 0, 0, 'A', 0, 'BINARY', 'integer',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 2, 'C', 0, 'BINARY', 'scalar',
})
-- Case: multiple columns, reverse columns order.
@@ -52,9 +52,9 @@ test:do_execsql_test(
"index-info-1.5",
"PRAGMA index_info (t1.cba);",
{
- 0, 2, 'C', 0, 'BINARY', 1, 'scalar',
- 1, 1, 'B', 0, 'BINARY', 1, 'scalar',
- 2, 0, 'A', 0, 'BINARY', 1, 'integer',
+ 0, 2, 'C', 0, 'BINARY', 'scalar',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 0, 'A', 0, 'BINARY', 'integer',
})
-- Case: index with a string column.
@@ -62,7 +62,7 @@ test:do_execsql_test(
"index-info-1.6",
"PRAGMA index_info (t1.d);",
{
- 0, 3, 'D', 0, 'BINARY', 1, 'scalar',
+ 0, 3, 'D', 0, 'BINARY', 'scalar',
})
test:finish_test()
diff --git a/test/sql-tap/index7.test.lua b/test/sql-tap/index7.test.lua
index 9eb644dd5..fe46d47f2 100755
--- a/test/sql-tap/index7.test.lua
+++ b/test/sql-tap/index7.test.lua
@@ -310,9 +310,9 @@ test:do_catchsql_test(
pragma index_info(t.i1);
]],
{0, {
- 0, 0, 'A', 0, 'BINARY', 1, 'scalar',
- 1, 1, 'B', 0, 'BINARY', 1, 'scalar',
- 2, 2, 'C', 0, 'BINARY', 1, 'scalar',
+ 0, 0, 'A', 0, 'BINARY', 'scalar',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 2, 'C', 0, 'BINARY', 'scalar',
}}
)
Full diff
---------
diff --git a/src/box/sql/parse.y b/src/box/sql/parse.y
index e8027ab80..8ef1c2835 100644
--- a/src/box/sql/parse.y
+++ b/src/box/sql/parse.y
@@ -1283,7 +1283,7 @@ cmd ::= PRAGMA nm(X) EQ minus_num(Y). {
cmd ::= PRAGMA nm(X) LP minus_num(Y) RP. {
sqlite3Pragma(pParse,&X,&Y,0,1);
}
-cmd ::= PRAGMA nm(X) EQ nm(Z) DOT nm(Y). {
+cmd ::= PRAGMA nm(X) LP nm(Z) DOT nm(Y) RP. {
sqlite3Pragma(pParse,&X,&Y,&Z,0);
}
cmd ::= PRAGMA . {
diff --git a/src/box/sql/pragma.c b/src/box/sql/pragma.c
index 2e007f942..4265ace25 100644
--- a/src/box/sql/pragma.c
+++ b/src/box/sql/pragma.c
@@ -322,16 +322,26 @@ sql_pragma_table_stats(struct space *space, void *data)
}
/**
- * This function handles PRAGMA INDEX_INFO and PRAGMA INDEX_XINFO
- * statements.
+ * This function handles PRAGMA INDEX_INFO(<table>.<index>).
+ *
+ * Return a single row for each column of the index.
+ * The columns of the returned data set are:
+ *
+ * - seqno: Zero-based column id within the index.
+ * - cid: Zero-based column id within the table.
+ * - name: Table column name.
+ * - desc: Whether sorting by the column is descending (1 or 0).
+ * - coll: Collation name.
+ * - type: Type of a column value.
*
* @param parse Current parsing content.
* @param pragma Definition of index_info pragma.
- * @param table_name Name of table index belongs to.
+ * @param tbl_name Name of table index belongs to.
* @param idx_name Name of index to display info about.
*/
static void
-sql_pragma_index_info(struct Parse *parse, const PragmaName *pragma,
+sql_pragma_index_info(struct Parse *parse,
+ MAYBE_UNUSED const PragmaName *pragma,
const char *tbl_name, const char *idx_name)
{
if (idx_name == NULL || tbl_name == NULL)
@@ -344,37 +354,31 @@ sql_pragma_index_info(struct Parse *parse, const PragmaName *pragma,
if (space->def->opts.sql == NULL)
return;
uint32_t iid = box_index_id_by_name(space_id, idx_name,
- strlen(idx_name));
+ strlen(idx_name));
if (iid == BOX_ID_NIL)
return;
struct index *idx = space_index(space, iid);
assert(idx != NULL);
- /* PRAGMA index_xinfo (more informative version). */
- if (pragma->iArg > 0) {
- parse->nMem = 6;
- } else {
- /* PRAGMA index_info ... */
- parse->nMem = 3;
- }
+ parse->nMem = 6;
struct Vdbe *v = sqlite3GetVdbe(parse);
assert(v != NULL);
uint32_t part_count = idx->def->key_def->part_count;
assert(parse->nMem <= pragma->nPragCName);
struct key_part *part = idx->def->key_def->parts;
for (uint32_t i = 0; i < part_count; i++, part++) {
- sqlite3VdbeMultiLoad(v, 1, "iis", i, part->fieldno,
- space->def->fields[part->fieldno].name);
- if (pragma->iArg > 0) {
- const char *c_n;
- uint32_t id = part->coll_id;
- struct coll *coll = part->coll;
- if (coll != NULL)
- c_n = coll_by_id(id)->name;
- else
- c_n = "BINARY";
- sqlite3VdbeMultiLoad(v, 4, "isi", part->sort_order,
- c_n, i < part_count);
- }
+ const char *c_n;
+ uint32_t id = part->coll_id;
+ struct coll *coll = part->coll;
+ if (coll != NULL)
+ c_n = coll_by_id(id)->name;
+ else
+ c_n = "BINARY";
+ uint32_t fieldno = part->fieldno;
+ enum field_type type = space->def->fields[fieldno].type;
+ sqlite3VdbeMultiLoad(v, 1, "iisiss", i, fieldno,
+ space->def->fields[fieldno].name,
+ part->sort_order, c_n,
+ field_type_strs[type]);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, parse->nMem);
}
}
diff --git a/src/box/sql/pragma.h b/src/box/sql/pragma.h
index 63fa47138..e60801608 100644
--- a/src/box/sql/pragma.h
+++ b/src/box/sql/pragma.h
@@ -32,61 +32,45 @@
* result column is different from the name of the pragma
*/
static const char *const pragCName[] = {
- /* 0 */ "cid",
- /* Used by: table_info */
+ /* Used by: table_info */
+ /* 0 */ "cid",
/* 1 */ "name",
/* 2 */ "type",
/* 3 */ "notnull",
/* 4 */ "dflt_value",
/* 5 */ "pk",
- /* 6 */ "table",
- /* Used by: stats */
+ /* Used by: stats */
+ /* 6 */ "table",
/* 7 */ "index",
/* 8 */ "width",
/* 9 */ "height",
- /* 10 */ "seqno",
- /* Used by: index_info */
+ /* Used by: index_info */
+ /* 10 */ "seqno",
/* 11 */ "cid",
/* 12 */ "name",
- /* 13 */ "seqno",
- /* Used by: index_xinfo */
- /* 14 */ "cid",
- /* 15 */ "name",
- /* 16 */ "desc",
- /* 17 */ "coll",
- /* 18 */ "key",
- /* 19 */ "seq",
- /* Used by: index_list */
- /* 20 */ "name",
- /* 21 */ "unique",
- /* 22 */ "origin",
- /* 23 */ "partial",
- /* 24 */ "seq",
- /* Used by: database_list */
- /* 25 */ "name",
- /* 26 */ "file",
- /* 27 */ "seq",
- /* Used by: collation_list */
- /* 28 */ "name",
- /* 29 */ "id",
- /* Used by: foreign_key_list */
- /* 30 */ "seq",
- /* 31 */ "table",
- /* 32 */ "from",
- /* 33 */ "to",
- /* 34 */ "on_update",
- /* 35 */ "on_delete",
- /* 36 */ "match",
- /* 37 */ "table",
- /* 38 */ "rowid",
- /* 39 */ "parent",
- /* 40 */ "fkid",
- /* 41 */ "busy",
- /* Used by: wal_checkpoint */
- /* 42 */ "log",
- /* 43 */ "checkpointed",
- /* 44 */ "timeout",
- /* Used by: busy_timeout */
+ /* 13 */ "desc",
+ /* 14 */ "coll",
+ /* 15 */ "type",
+ /* Used by: index_list */
+ /* 16 */ "seq",
+ /* 17 */ "name",
+ /* 18 */ "unique",
+ /* 19 */ "origin",
+ /* 20 */ "partial",
+ /* Used by: collation_list */
+ /* 21 */ "seq",
+ /* 22 */ "name",
+ /* Used by: foreign_key_list */
+ /* 23 */ "id",
+ /* 24 */ "seq",
+ /* 25 */ "table",
+ /* 26 */ "from",
+ /* 27 */ "to",
+ /* 28 */ "on_update",
+ /* 29 */ "on_delete",
+ /* 30 */ "match",
+ /* Used by: busy_timeout */
+ /* 31 */ "timeout",
};
/* Definitions of all built-in pragmas */
@@ -98,11 +82,15 @@ typedef struct PragmaName {
u8 nPragCName; /* Num of col names. 0 means use pragma name */
u32 iArg; /* Extra argument */
} PragmaName;
+/**
+ * The order of pragmas in this array is important: it has
+ * to be sorted. For more info see pragma_locate function.
+ */
static const PragmaName aPragmaName[] = {
{ /* zName: */ "busy_timeout",
/* ePragTyp: */ PragTyp_BUSY_TIMEOUT,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 44, 1,
+ /* ColNames: */ 31, 1,
/* iArg: */ 0},
{ /* zName: */ "case_sensitive_like",
/* ePragTyp: */ PragTyp_CASE_SENSITIVE_LIKE,
@@ -113,7 +101,7 @@ static const PragmaName aPragmaName[] = {
{ /* zName: */ "collation_list",
/* ePragTyp: */ PragTyp_COLLATION_LIST,
/* ePragFlg: */ PragFlg_Result0,
- /* ColNames: */ 27, 2,
+ /* ColNames: */ 21, 2,
/* iArg: */ 0},
#endif
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
@@ -134,7 +122,7 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 29, 8,
+ /* ColNames: */ 23, 8,
/* iArg: */ 0},
#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
{ /* zName: */ "full_column_names",
@@ -148,20 +136,14 @@ static const PragmaName aPragmaName[] = {
/* ePragTyp: */ PragTyp_INDEX_INFO,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 10, 3,
- /* iArg: */ 0},
+ /* ColNames: */ 10, 6,
+ /* iArg: */ 1},
{ /* zName: */ "index_list",
/* ePragTyp: */ PragTyp_INDEX_LIST,
/* ePragFlg: */
PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 19, 5,
+ /* ColNames: */ 16, 5,
/* iArg: */ 0},
- { /* zName: */ "index_xinfo",
- /* ePragTyp: */ PragTyp_INDEX_INFO,
- /* ePragFlg: */
- PragFlg_NeedSchema | PragFlg_Result1 | PragFlg_SchemaOpt,
- /* ColNames: */ 13, 6,
- /* iArg: */ 1},
#endif
#if defined(SQLITE_DEBUG) && !defined(SQLITE_OMIT_PARSER_TRACE)
{ /* zName: */ "parser_trace",
diff --git a/test/sql-tap/index-info.test.lua b/test/sql-tap/index-info.test.lua
index d3bb70e2d..5a2eb2b36 100755
--- a/test/sql-tap/index-info.test.lua
+++ b/test/sql-tap/index-info.test.lua
@@ -1,50 +1,68 @@
#!/usr/bin/env tarantool
-test = require("sqltester")
-test:plan(4)
+
+local test = require("sqltester")
+test:plan(6)
test:execsql([[
- CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE, c INT);
- INSERT INTO t1 VALUES (1, 1, 1), (2, 2, 2);
- INSERT INTO t1 VALUES (3, 3, 3), (4, 4, 4);
- CREATE INDEX t1ix1 ON t1(a);
- CREATE INDEX t1ix2 ON t1(a, b);
- CREATE INDEX t1ix3 ON t1(a, b, c);
+ CREATE TABLE t1(a INT PRIMARY KEY, b INT UNIQUE, c INT, d STRING);
+ INSERT INTO t1 VALUES (1, 1, 1, 'abcd'), (2, 2, 2, 'abcde');
+ INSERT INTO t1 VALUES (3, 3, 3, 'abcdef'), (4, 4, 4, 'abcdefg');
+ CREATE INDEX a ON t1(a);
+ CREATE INDEX abc ON t1(a, b, c);
+ CREATE INDEX cba ON t1(c, b, a);
+ CREATE INDEX d ON t1(d);
]])
+-- Case: old index_xinfo pragma is banned.
+test:do_catchsql_test(
+ "index-info-1.1",
+ "PRAGMA index_xinfo (t1.a);",
+ {
+ 1, "no such pragma: INDEX_XINFO",
+ })
+
+-- Case: old index_info syntax is banned.
+test:do_catchsql_test(
+ "index-info-1.2",
+ "PRAGMA index_info = t1.a;",
+ {
+ 1, "near \".\": syntax error",
+ })
+
+-- Case: single column index with an integer column.
test:do_execsql_test(
- "index-info-1.1",
- "PRAGMA index_info = t1.t1ix1;",
- {
- -- <index-info-1.1>
- 0, 0, 'A'
- -- <index-info-1.1>
- })
+ "index-info-1.3",
+ "PRAGMA index_info (t1.a);",
+ {
+ 0, 0, 'A', 0, 'BINARY', 'integer',
+ })
+-- Case: multiple columns index with integer columns.
test:do_execsql_test(
- "index-info-1.2",
- "PRAGMA index_info = t1.t1ix2;",
- {
- -- <index-info-1.1>
- 0, 0, 'A', 1, 1, 'B',
- -- <index-info-1.1>
- })
+ "index-info-1.4",
+ "PRAGMA index_info (t1.abc);",
+ {
+ 0, 0, 'A', 0, 'BINARY', 'integer',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 2, 'C', 0, 'BINARY', 'scalar',
+ })
+-- Case: multiple columns, reverse columns order.
test:do_execsql_test(
- "index-info-1.3",
- "PRAGMA index_info = t1.t1ix3;",
- {
- -- <index-info-1.1>
- 0, 0, 'A', 1, 1, 'B', 2, 2, 'C'
- -- <index-info-1.1>
- })
+ "index-info-1.5",
+ "PRAGMA index_info (t1.cba);",
+ {
+ 0, 2, 'C', 0, 'BINARY', 'scalar',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 0, 'A', 0, 'BINARY', 'integer',
+ })
+-- Case: index with a string column.
test:do_execsql_test(
- "index-info-1.1",
- "PRAGMA index_xinfo = t1.t1ix1;",
- {
- -- <index-info-1.1>
- 0, 0, 'A', 0, 'BINARY', 1,
- -- <index-info-1.1>
- })
+ "index-info-1.6",
+ "PRAGMA index_info (t1.d);",
+ {
+ 0, 3, 'D', 0, 'BINARY', 'scalar',
+ })
test:finish_test()
diff --git a/test/sql-tap/index7.test.lua b/test/sql-tap/index7.test.lua
index 7d4a54723..fe46d47f2 100755
--- a/test/sql-tap/index7.test.lua
+++ b/test/sql-tap/index7.test.lua
@@ -307,9 +307,13 @@ test:do_catchsql_test(
[[
CREATE TABLE t(a,b,c, PRIMARY KEY(a));
CREATE INDEX i1 ON t(a, a, b, c, c, b, b, b, c, b, c);
- pragma index_info = t.i1;
+ pragma index_info(t.i1);
]],
- {0, {0,0,"A",1,1,"B",2,2,"C"}}
+ {0, {
+ 0, 0, 'A', 0, 'BINARY', 'scalar',
+ 1, 1, 'B', 0, 'BINARY', 'scalar',
+ 2, 2, 'C', 0, 'BINARY', 'scalar',
+ }}
)
-- There was the following bug:
^ permalink raw reply [flat|nested] 6+ messages in thread