From 529dc0249275880970d6b22abafde6a68eafa560 Mon Sep 17 00:00:00 2001 From: drh Date: Tue, 28 Nov 2017 02:47:42 +0000 Subject: [PATCH] Add the "PRAGMA table_ipk(TABLE)" command for evaluation purposes. FossilOrigin-Name: 2494132a2b1221a4aec9817272c088b3c6bbf9340856927457ff6de62d469676 --- manifest | 21 ++++++---- manifest.uuid | 2 +- src/pragma.c | 19 +++++++++ src/pragma.h | 98 +++++++++++++++++++++++--------------------- test/intpkey.test | 85 +++++++++++++++++++++++++++++++++++++- tool/mkpragmatab.tcl | 5 +++ 6 files changed, 172 insertions(+), 58 deletions(-) diff --git a/manifest b/manifest index 9d698eeef1..b230be5fae 100644 --- a/manifest +++ b/manifest @@ -1,5 +1,5 @@ -C Update\stests\sto\sdeal\swith\sSQLITE_FAST_SECURE_DELETE. -D 2017-11-28T00:52:14.148 +C Add\sthe\s"PRAGMA\stable_ipk(TABLE)"\scommand\sfor\sevaluation\spurposes. +D 2017-11-28T02:47:42.046 F Makefile.in 6a879cbf01e37f9eac131414955f71774b566502d9a57ded1b8585b507503cb8 F Makefile.linux-gcc 7bc79876b875010e8c8f9502eb935ca92aa3c434 F Makefile.msc e5d7606238f55816da99f719969598df5b091aa2e9a6935c9412fcae8f53fc44 @@ -464,8 +464,8 @@ F src/parse.y d79001da275bfe344b409006b85e81e486a0f6afc3762fdf0944f000f4aa0111 F src/pcache.c 7ae91a4557a43d77d449accbfdc68846e6516f8e2eda46e8bbe4536fb669b201 F src/pcache.h 072f94d29281cffd99e46c1539849f248c4b56ae7684c1f36626797fee375170 F src/pcache1.c 716975564c15eb6679e97f734cec1bfd6c16ac3d4010f05f1f8e509fc7d19880 -F src/pragma.c d04725ac25387d9638919e197fb009f378e13af7bf899516979e54b3164e3602 -F src/pragma.h bb83728944b42f6d409c77f5838a8edbdb0fe83046c5496ffc9602b40340a324 +F src/pragma.c 021613c97a331d42c45ecc1510c8ebd222972f9679254078a7e2b5c4513c4566 +F src/pragma.h 4ce81be73b88094c23fc635643f8712178f986f776830ff8115627c810714b75 F src/prepare.c 7cf451f903ad92a14e22de415a13e7a7d30f1bd23b3d21eeb0dc7264723244c5 F src/printf.c 9506b4b96e59c0467047155f09015750cb2878aeda3d39e5610c1192ddc3c41c F src/random.c 80f5d666f23feb3e6665a6ce04c7197212a88384 @@ -975,7 +975,7 @@ F test/instrfault.test 0f870b218ea17cd477bb19ed330eecdb460dd53a F test/intarray.test 46d95b457916638c5d8b1af21fb174804b3acf8b F test/interrupt.test 16ea879ec728cb76414c148c5f24afd5d1f91054 F test/interrupt2.test e4408ca770a6feafbadb0801e54a0dcd1a8d108d -F test/intpkey.test ac71107a49a06492b69b82aafaf225400598d3c8 +F test/intpkey.test 21290ef810c049467d9f4f3b3361178ced254ff37df62af314a0ede878009b25 F test/io.test f95bca1783b01ea7761671560d023360d2dfa4cc F test/ioerr.test 2a24bd6ed5a8b062e64bfe1f6cf94fb25e92210d F test/ioerr2.test 2593563599e2cc6b6b4fcf5878b177bdd5d8df26 @@ -1613,7 +1613,7 @@ F tool/mkmsvcmin.tcl 8baf26690b80d861d0ac341b29880eec6ade39e4f11fe690271ded9cb90 F tool/mkopcodec.tcl d1b6362bd3aa80d5520d4d6f3765badf01f6c43c F tool/mkopcodeh.tcl 4ee2a30ccbd900dc4d5cdb61bdab87cd2166cd2affcc78c9cc0b8d22a65b2eee F tool/mkopts.tcl 66ac10d240cc6e86abd37dc908d50382f84ff46e -F tool/mkpragmatab.tcl 2144bc8550a6471a029db262a132d2df4b9e0db61b90398bf64f5b7b3f8d92cd +F tool/mkpragmatab.tcl f49ae6e8cb961d36f449109badcb7020c2fcdeb6d03c7f1acda89633e10250ca F tool/mkshellc.tcl 574307265b49d813301fba91ccd74e6a26d33f65f74b6891c320a0ffbee07895 F tool/mksourceid.c d458f9004c837bee87a6382228ac20d3eae3c49ea3b0a5aace936f8b60748d3b F tool/mkspeedsql.tcl a1a334d288f7adfe6e996f2e712becf076745c97 @@ -1678,7 +1678,10 @@ F vsixtest/vsixtest.tcl 6a9a6ab600c25a91a7acc6293828957a386a8a93 F vsixtest/vsixtest.vcxproj.data 2ed517e100c66dc455b492e1a33350c1b20fbcdc F vsixtest/vsixtest.vcxproj.filters 37e51ffedcdb064aad6ff33b6148725226cd608e F vsixtest/vsixtest_TemporaryKey.pfx e5b1b036facdb453873e7084e1cae9102ccc67a0 -P 465350e55ddaf30cfba7874653301de7238a9dc2bc5e1f800fc95de9360679f6 -R 5c3698b136ad7029a7e22749e9f1a061 +P e6b89304695be371978e65dddd710c8bd563c66b9c94d23165142b6c235c82e1 +R 89fbad1a4c6de971034ac5f140aa2b84 +T *branch * pragma-table-ipk +T *sym-pragma-table-ipk * +T -sym-trunk * U drh -Z 455c99a49ae132932748abfe00c111c4 +Z 5a5f1ee8645506e803bde7a836253a0d diff --git a/manifest.uuid b/manifest.uuid index 289fbf5515..cd35e45dfc 100644 --- a/manifest.uuid +++ b/manifest.uuid @@ -1 +1 @@ -e6b89304695be371978e65dddd710c8bd563c66b9c94d23165142b6c235c82e1 \ No newline at end of file +2494132a2b1221a4aec9817272c088b3c6bbf9340856927457ff6de62d469676 \ No newline at end of file diff --git a/src/pragma.c b/src/pragma.c index 918b1d8131..e521228dac 100644 --- a/src/pragma.c +++ b/src/pragma.c @@ -1117,6 +1117,25 @@ void sqlite3Pragma( } break; + /* + ** PRAGMA table_ipk() + ** + ** If
has an INTEGER PRIMARY KEY column that is an alias for + ** the ROWID, then return the name of that column. If
does not + ** have a ROWID alias, or if it does not have a ROWID, or if
is + ** a view or virtual table or if it does not exist, then return no rows. + */ + case PragTyp_TABLE_IPK: { + if( zRight ){ + Table *pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb); + sqlite3CodeVerifySchema(pParse, iDb); + if( pTab && HasRowid(pTab) && pTab->iPKey>=0 ){ + sqlite3VdbeMultiLoad(v, 1, "s", pTab->aCol[pTab->iPKey].zName); + } + } + } + break; + #ifdef SQLITE_DEBUG case PragTyp_STATS: { Index *pIdx; diff --git a/src/pragma.h b/src/pragma.h index c9ece2dc87..4758f790d4 100644 --- a/src/pragma.h +++ b/src/pragma.h @@ -40,18 +40,19 @@ #define PragTyp_SOFT_HEAP_LIMIT 32 #define PragTyp_SYNCHRONOUS 33 #define PragTyp_TABLE_INFO 34 -#define PragTyp_TEMP_STORE 35 -#define PragTyp_TEMP_STORE_DIRECTORY 36 -#define PragTyp_THREADS 37 -#define PragTyp_WAL_AUTOCHECKPOINT 38 -#define PragTyp_WAL_CHECKPOINT 39 -#define PragTyp_ACTIVATE_EXTENSIONS 40 -#define PragTyp_HEXKEY 41 -#define PragTyp_KEY 42 -#define PragTyp_REKEY 43 -#define PragTyp_LOCK_STATUS 44 -#define PragTyp_PARSER_TRACE 45 -#define PragTyp_STATS 46 +#define PragTyp_TABLE_IPK 35 +#define PragTyp_TEMP_STORE 36 +#define PragTyp_TEMP_STORE_DIRECTORY 37 +#define PragTyp_THREADS 38 +#define PragTyp_WAL_AUTOCHECKPOINT 39 +#define PragTyp_WAL_CHECKPOINT 40 +#define PragTyp_ACTIVATE_EXTENSIONS 41 +#define PragTyp_HEXKEY 42 +#define PragTyp_KEY 43 +#define PragTyp_REKEY 44 +#define PragTyp_LOCK_STATUS 45 +#define PragTyp_PARSER_TRACE 46 +#define PragTyp_STATS 47 /* Property flags associated with various pragma. */ #define PragFlg_NeedSchema 0x01 /* Force schema load before running */ @@ -75,31 +76,31 @@ static const char *const pragCName[] = { /* 4 */ "notnull", /* 5 */ "dflt_value", /* 6 */ "pk", - /* 7 */ "tbl", /* Used by: stats */ - /* 8 */ "idx", - /* 9 */ "wdth", - /* 10 */ "hght", - /* 11 */ "flgs", - /* 12 */ "seqno", /* Used by: index_info */ - /* 13 */ "cid", - /* 14 */ "name", - /* 15 */ "seqno", /* Used by: index_xinfo */ - /* 16 */ "cid", - /* 17 */ "name", - /* 18 */ "desc", - /* 19 */ "coll", - /* 20 */ "key", - /* 21 */ "seq", /* Used by: index_list */ - /* 22 */ "name", - /* 23 */ "unique", - /* 24 */ "origin", - /* 25 */ "partial", - /* 26 */ "seq", /* Used by: database_list */ - /* 27 */ "name", - /* 28 */ "file", - /* 29 */ "name", /* Used by: function_list */ - /* 30 */ "builtin", - /* 31 */ "name", /* Used by: module_list pragma_list */ + /* 7 */ "name", /* Used by: table_ipk module_list pragma_list */ + /* 8 */ "tbl", /* Used by: stats */ + /* 9 */ "idx", + /* 10 */ "wdth", + /* 11 */ "hght", + /* 12 */ "flgs", + /* 13 */ "seqno", /* Used by: index_info */ + /* 14 */ "cid", + /* 15 */ "name", + /* 16 */ "seqno", /* Used by: index_xinfo */ + /* 17 */ "cid", + /* 18 */ "name", + /* 19 */ "desc", + /* 20 */ "coll", + /* 21 */ "key", + /* 22 */ "seq", /* Used by: index_list */ + /* 23 */ "name", + /* 24 */ "unique", + /* 25 */ "origin", + /* 26 */ "partial", + /* 27 */ "seq", /* Used by: database_list */ + /* 28 */ "name", + /* 29 */ "file", + /* 30 */ "name", /* Used by: function_list */ + /* 31 */ "builtin", /* 32 */ "seq", /* Used by: collation_list */ /* 33 */ "name", /* 34 */ "id", /* Used by: foreign_key_list */ @@ -237,7 +238,7 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "database_list", /* ePragTyp: */ PragTyp_DATABASE_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0, - /* ColNames: */ 26, 3, + /* ColNames: */ 27, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED) @@ -317,7 +318,7 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "function_list", /* ePragTyp: */ PragTyp_FUNCTION_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 29, 2, + /* ColNames: */ 30, 2, /* iArg: */ 0 }, #endif #endif @@ -353,17 +354,17 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "index_info", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 12, 3, + /* ColNames: */ 13, 3, /* iArg: */ 0 }, {/* zName: */ "index_list", /* ePragTyp: */ PragTyp_INDEX_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 21, 5, + /* ColNames: */ 22, 5, /* iArg: */ 0 }, {/* zName: */ "index_xinfo", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, - /* ColNames: */ 15, 6, + /* ColNames: */ 16, 6, /* iArg: */ 1 }, #endif #if !defined(SQLITE_OMIT_INTEGRITY_CHECK) @@ -436,7 +437,7 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "module_list", /* ePragTyp: */ PragTyp_MODULE_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 31, 1, + /* ColNames: */ 7, 1, /* iArg: */ 0 }, #endif #endif @@ -469,7 +470,7 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "pragma_list", /* ePragTyp: */ PragTyp_PRAGMA_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 31, 1, + /* ColNames: */ 7, 1, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) @@ -556,7 +557,7 @@ static const PragmaName aPragmaName[] = { {/* zName: */ "stats", /* ePragTyp: */ PragTyp_STATS, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, - /* ColNames: */ 7, 5, + /* ColNames: */ 8, 5, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) @@ -572,6 +573,11 @@ static const PragmaName aPragmaName[] = { /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 1, 6, /* iArg: */ 0 }, + {/* zName: */ "table_ipk", + /* ePragTyp: */ PragTyp_TABLE_IPK, + /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1, + /* ColNames: */ 7, 1, + /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "temp_store", @@ -646,4 +652,4 @@ static const PragmaName aPragmaName[] = { /* iArg: */ SQLITE_WriteSchema }, #endif }; -/* Number of pragmas: 60 on by default, 77 total. */ +/* Number of pragmas: 61 on by default, 78 total. */ diff --git a/test/intpkey.test b/test/intpkey.test index a56cab4c69..60203f04fa 100644 --- a/test/intpkey.test +++ b/test/intpkey.test @@ -1,4 +1,4 @@ -# 2001 September 15 +# 2001-09-15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: @@ -13,7 +13,6 @@ # This file implements tests for the special processing associated # with INTEGER PRIMARY KEY columns. # -# $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl @@ -642,4 +641,86 @@ do_execsql_test intpkey-17.2 { SELECT * FROM t17 ORDER BY +x; } {123 elephant 248 ostrich} +# 2017-11-28: The table_ipk pragma. +# +do_execsql_test intpkey-18.0 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY, abc, pqr); + PRAGMA table_ipk(t1); +} {xyz} +do_execsql_test intpkey-18.1 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY ASC, abc, pqr); + PRAGMA table_ipk(t1); +} {xyz} +do_execsql_test intpkey-18.2 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY DESC, abc, pqr); + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.3 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz)); + PRAGMA table_ipk(t1); +} {xyz} +do_execsql_test intpkey-18.4 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz ASC)); + PRAGMA table_ipk(t1); +} {xyz} +do_execsql_test intpkey-18.5 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz DESC)); + PRAGMA table_ipk(t1); +} {xyz} +do_execsql_test intpkey-18.10 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY, abc, pqr) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.11 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY ASC, abc, pqr) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.12 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER PRIMARY KEY DESC, abc, pqr) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.13 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz)) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.14 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz ASC)) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.15 { + DROP TABLE IF EXISTS t1; + CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz DESC)) WITHOUT ROWID; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.20 { + DROP TABLE IF EXISTS t1; + PRAGMA table_ipk(t1); +} {} +do_execsql_test intpkey-18.21 { + CREATE TABLE t1(a,b,xyz INTEGER PRIMARY KEY NOT NULL); + SELECT * FROM pragma_table_ipk('t1'); +} {xyz} +do_execsql_test intpkey-18.22 { + PRAGMA table_ipk(pragma_table_ipk); +} {} +do_execsql_test intpkey-18.23 { + PRAGMA table_info(pragma_table_ipk); +} {0 name {} 0 {} 0} +do_execsql_test intpkey-18.24 { + CREATE VIEW v1 AS SELECT * FROM t1 WHERE rowid>5; + PRAGMA table_ipk(v1); +} {} + + finish_test diff --git a/tool/mkpragmatab.tcl b/tool/mkpragmatab.tcl index 1bfbeb7c58..0f601cab73 100644 --- a/tool/mkpragmatab.tcl +++ b/tool/mkpragmatab.tcl @@ -223,6 +223,11 @@ set pragma_def { COLS: cid name type notnull dflt_value pk IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) + NAME: table_ipk + FLAG: NeedSchema Result1 + COLS: name + IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) + NAME: stats FLAG: NeedSchema Result0 SchemaReq COLS: tbl idx wdth hght flgs -- 2.39.5