From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 022152CBC7 for ; Fri, 12 Oct 2018 05:48:30 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id z9mU24IGkwH1 for ; Fri, 12 Oct 2018 05:48:29 -0400 (EDT) Received: from smtpng3.m.smailru.net (smtpng3.m.smailru.net [94.100.177.149]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTPS id 1599B2CB73 for ; Fri, 12 Oct 2018 05:48:29 -0400 (EDT) Date: Fri, 12 Oct 2018 12:48:25 +0300 From: Alexander Turenko Subject: [tarantool-patches] Re: [PATCH] sql: change of PRAGMA INDEX_INFO syntax Message-ID: <20181012094825.fdlkgbm24mfu5txp@tkn_work_nb> References: MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: Sender: tarantool-patches-bounce@freelists.org Errors-to: tarantool-patches-bounce@freelists.org Reply-To: tarantool-patches@freelists.org List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: tarantool-patches List-subscribe: List-owner: List-post: List-archive: To: "n.pettik" Cc: tarantool-patches@freelists.org 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(.). + * + * 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(
.). + * + * 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;", - { - -- - 0, 0, 'A' - -- - }) + "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;", - { - -- - 0, 0, 'A', 1, 1, 'B', - -- - }) + "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;", - { - -- - 0, 0, 'A', 1, 1, 'B', 2, 2, 'C' - -- - }) + "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;", - { - -- - 0, 0, 'A', 0, 'BINARY', 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: