Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1112,10 +1112,29 @@ pCol->notNull ? 1 : 0, pCol->pDflt ? pCol->pDflt->u.zToken : 0, k); } } + } + 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: src/pragma.h ================================================================== --- src/pragma.h +++ src/pragma.h @@ -38,22 +38,23 @@ #define PragTyp_SECURE_DELETE 30 #define PragTyp_SHRINK_MEMORY 31 #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 */ #define PragFlg_NoColumns 0x02 /* OP_ResultRow called with zero columns */ #define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present */ @@ -73,35 +74,35 @@ /* 2 */ "name", /* 3 */ "type", /* 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 */ /* 35 */ "seq", /* 36 */ "table", @@ -235,11 +236,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* 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) {/* zName: */ "default_cache_size", /* ePragTyp: */ PragTyp_DEFAULT_CACHE_SIZE, @@ -315,11 +316,11 @@ #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) #if defined(SQLITE_INTROSPECTION_PRAGMAS) {/* zName: */ "function_list", /* ePragTyp: */ PragTyp_FUNCTION_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 29, 2, + /* ColNames: */ 30, 2, /* iArg: */ 0 }, #endif #endif #if defined(SQLITE_HAS_CODEC) {/* zName: */ "hexkey", @@ -351,21 +352,21 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* 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) {/* zName: */ "integrity_check", /* ePragTyp: */ PragTyp_INTEGRITY_CHECK, @@ -434,11 +435,11 @@ #if !defined(SQLITE_OMIT_VIRTUALTABLE) #if defined(SQLITE_INTROSPECTION_PRAGMAS) {/* zName: */ "module_list", /* ePragTyp: */ PragTyp_MODULE_LIST, /* ePragFlg: */ PragFlg_Result0, - /* ColNames: */ 31, 1, + /* ColNames: */ 7, 1, /* iArg: */ 0 }, #endif #endif #endif {/* zName: */ "optimize", @@ -467,11 +468,11 @@ #endif #if defined(SQLITE_INTROSPECTION_PRAGMAS) {/* 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) {/* zName: */ "query_only", /* ePragTyp: */ PragTyp_FLAG, @@ -554,11 +555,11 @@ #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG) {/* 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) {/* zName: */ "synchronous", /* ePragTyp: */ PragTyp_SYNCHRONOUS, @@ -569,10 +570,15 @@ #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "table_info", /* ePragTyp: */ PragTyp_TABLE_INFO, /* 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", /* ePragTyp: */ PragTyp_TEMP_STORE, @@ -644,6 +650,6 @@ /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema }, #endif }; -/* Number of pragmas: 60 on by default, 77 total. */ +/* Number of pragmas: 61 on by default, 78 total. */ Index: test/intpkey.test ================================================================== --- test/intpkey.test +++ test/intpkey.test @@ -1,6 +1,6 @@ -# 2001 September 15 +# 2001-09-15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. @@ -11,11 +11,10 @@ # This file implements regression tests for SQLite library. # # 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 # Create a table with a primary key and a datatype other than @@ -639,7 +638,89 @@ DELETE FROM t17; INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe'); UPDATE t17 SET y='ostrich' WHERE abs(x)=248; 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 Index: tool/mkpragmatab.tcl ================================================================== --- tool/mkpragmatab.tcl +++ tool/mkpragmatab.tcl @@ -221,10 +221,15 @@ NAME: table_info FLAG: NeedSchema Result1 SchemaOpt 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 IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG)