/ Check-in [2494132a]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add the "PRAGMA table_ipk(TABLE)" command for evaluation purposes.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | pragma-table-ipk
Files: files | file ages | folders
SHA3-256: 2494132a2b1221a4aec9817272c088b3c6bbf9340856927457ff6de62d469676
User & Date: drh 2017-11-28 02:47:42
Context
2017-11-28
02:47
Add the "PRAGMA table_ipk(TABLE)" command for evaluation purposes. Leaf check-in: 2494132a user: drh tags: pragma-table-ipk
00:52
Update tests to deal with SQLITE_FAST_SECURE_DELETE. check-in: e6b89304 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/pragma.c.

  1110   1110                  pCol->zName,
  1111   1111                  sqlite3ColumnType(pCol,""),
  1112   1112                  pCol->notNull ? 1 : 0,
  1113   1113                  pCol->pDflt ? pCol->pDflt->u.zToken : 0,
  1114   1114                  k);
  1115   1115         }
  1116   1116       }
         1117  +  }
         1118  +  break;
         1119  +
         1120  +  /*
         1121  +  **   PRAGMA table_ipk(<table>)
         1122  +  **
         1123  +  ** If <table> has an INTEGER PRIMARY KEY column that is an alias for
         1124  +  ** the ROWID, then return the name of that column.  If <table> does not
         1125  +  ** have a ROWID alias, or if it does not have a ROWID, or if <table> is
         1126  +  ** a view or virtual table or if it does not exist, then return no rows.
         1127  +  */
         1128  +  case PragTyp_TABLE_IPK: {
         1129  +    if( zRight ){
         1130  +      Table *pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb);
         1131  +      sqlite3CodeVerifySchema(pParse, iDb);
         1132  +      if( pTab && HasRowid(pTab) && pTab->iPKey>=0 ){
         1133  +        sqlite3VdbeMultiLoad(v, 1, "s", pTab->aCol[pTab->iPKey].zName);
         1134  +      }
         1135  +    }
  1117   1136     }
  1118   1137     break;
  1119   1138   
  1120   1139   #ifdef SQLITE_DEBUG
  1121   1140     case PragTyp_STATS: {
  1122   1141       Index *pIdx;
  1123   1142       HashElem *i;

Changes to src/pragma.h.

    36     36   #define PragTyp_PAGE_SIZE                     28
    37     37   #define PragTyp_PRAGMA_LIST                   29
    38     38   #define PragTyp_SECURE_DELETE                 30
    39     39   #define PragTyp_SHRINK_MEMORY                 31
    40     40   #define PragTyp_SOFT_HEAP_LIMIT               32
    41     41   #define PragTyp_SYNCHRONOUS                   33
    42     42   #define PragTyp_TABLE_INFO                    34
    43         -#define PragTyp_TEMP_STORE                    35
    44         -#define PragTyp_TEMP_STORE_DIRECTORY          36
    45         -#define PragTyp_THREADS                       37
    46         -#define PragTyp_WAL_AUTOCHECKPOINT            38
    47         -#define PragTyp_WAL_CHECKPOINT                39
    48         -#define PragTyp_ACTIVATE_EXTENSIONS           40
    49         -#define PragTyp_HEXKEY                        41
    50         -#define PragTyp_KEY                           42
    51         -#define PragTyp_REKEY                         43
    52         -#define PragTyp_LOCK_STATUS                   44
    53         -#define PragTyp_PARSER_TRACE                  45
    54         -#define PragTyp_STATS                         46
           43  +#define PragTyp_TABLE_IPK                     35
           44  +#define PragTyp_TEMP_STORE                    36
           45  +#define PragTyp_TEMP_STORE_DIRECTORY          37
           46  +#define PragTyp_THREADS                       38
           47  +#define PragTyp_WAL_AUTOCHECKPOINT            39
           48  +#define PragTyp_WAL_CHECKPOINT                40
           49  +#define PragTyp_ACTIVATE_EXTENSIONS           41
           50  +#define PragTyp_HEXKEY                        42
           51  +#define PragTyp_KEY                           43
           52  +#define PragTyp_REKEY                         44
           53  +#define PragTyp_LOCK_STATUS                   45
           54  +#define PragTyp_PARSER_TRACE                  46
           55  +#define PragTyp_STATS                         47
    55     56   
    56     57   /* Property flags associated with various pragma. */
    57     58   #define PragFlg_NeedSchema 0x01 /* Force schema load before running */
    58     59   #define PragFlg_NoColumns  0x02 /* OP_ResultRow called with zero columns */
    59     60   #define PragFlg_NoColumns1 0x04 /* zero columns if RHS argument is present */
    60     61   #define PragFlg_ReadOnly   0x08 /* Read-only HEADER_VALUE */
    61     62   #define PragFlg_Result0    0x10 /* Acts as query when no argument */
................................................................................
    71     72     /*   0 */ "cache_size",  /* Used by: default_cache_size */
    72     73     /*   1 */ "cid",         /* Used by: table_info */
    73     74     /*   2 */ "name",       
    74     75     /*   3 */ "type",       
    75     76     /*   4 */ "notnull",    
    76     77     /*   5 */ "dflt_value", 
    77     78     /*   6 */ "pk",         
    78         -  /*   7 */ "tbl",         /* Used by: stats */
    79         -  /*   8 */ "idx",        
    80         -  /*   9 */ "wdth",       
    81         -  /*  10 */ "hght",       
    82         -  /*  11 */ "flgs",       
    83         -  /*  12 */ "seqno",       /* Used by: index_info */
    84         -  /*  13 */ "cid",        
    85         -  /*  14 */ "name",       
    86         -  /*  15 */ "seqno",       /* Used by: index_xinfo */
    87         -  /*  16 */ "cid",        
    88         -  /*  17 */ "name",       
    89         -  /*  18 */ "desc",       
    90         -  /*  19 */ "coll",       
    91         -  /*  20 */ "key",        
    92         -  /*  21 */ "seq",         /* Used by: index_list */
    93         -  /*  22 */ "name",       
    94         -  /*  23 */ "unique",     
    95         -  /*  24 */ "origin",     
    96         -  /*  25 */ "partial",    
    97         -  /*  26 */ "seq",         /* Used by: database_list */
    98         -  /*  27 */ "name",       
    99         -  /*  28 */ "file",       
   100         -  /*  29 */ "name",        /* Used by: function_list */
   101         -  /*  30 */ "builtin",    
   102         -  /*  31 */ "name",        /* Used by: module_list pragma_list */
           79  +  /*   7 */ "name",        /* Used by: table_ipk module_list pragma_list */
           80  +  /*   8 */ "tbl",         /* Used by: stats */
           81  +  /*   9 */ "idx",        
           82  +  /*  10 */ "wdth",       
           83  +  /*  11 */ "hght",       
           84  +  /*  12 */ "flgs",       
           85  +  /*  13 */ "seqno",       /* Used by: index_info */
           86  +  /*  14 */ "cid",        
           87  +  /*  15 */ "name",       
           88  +  /*  16 */ "seqno",       /* Used by: index_xinfo */
           89  +  /*  17 */ "cid",        
           90  +  /*  18 */ "name",       
           91  +  /*  19 */ "desc",       
           92  +  /*  20 */ "coll",       
           93  +  /*  21 */ "key",        
           94  +  /*  22 */ "seq",         /* Used by: index_list */
           95  +  /*  23 */ "name",       
           96  +  /*  24 */ "unique",     
           97  +  /*  25 */ "origin",     
           98  +  /*  26 */ "partial",    
           99  +  /*  27 */ "seq",         /* Used by: database_list */
          100  +  /*  28 */ "name",       
          101  +  /*  29 */ "file",       
          102  +  /*  30 */ "name",        /* Used by: function_list */
          103  +  /*  31 */ "builtin",    
   103    104     /*  32 */ "seq",         /* Used by: collation_list */
   104    105     /*  33 */ "name",       
   105    106     /*  34 */ "id",          /* Used by: foreign_key_list */
   106    107     /*  35 */ "seq",        
   107    108     /*  36 */ "table",      
   108    109     /*  37 */ "from",       
   109    110     /*  38 */ "to",         
................................................................................
   233    234     /* ColNames:  */ 0, 0,
   234    235     /* iArg:      */ BTREE_DATA_VERSION },
   235    236   #endif
   236    237   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   237    238    {/* zName:     */ "database_list",
   238    239     /* ePragTyp:  */ PragTyp_DATABASE_LIST,
   239    240     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0,
   240         -  /* ColNames:  */ 26, 3,
          241  +  /* ColNames:  */ 27, 3,
   241    242     /* iArg:      */ 0 },
   242    243   #endif
   243    244   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED)
   244    245    {/* zName:     */ "default_cache_size",
   245    246     /* ePragTyp:  */ PragTyp_DEFAULT_CACHE_SIZE,
   246    247     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
   247    248     /* ColNames:  */ 0, 1,
................................................................................
   313    314     /* iArg:      */ SQLITE_FullFSync },
   314    315   #endif
   315    316   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   316    317   #if defined(SQLITE_INTROSPECTION_PRAGMAS)
   317    318    {/* zName:     */ "function_list",
   318    319     /* ePragTyp:  */ PragTyp_FUNCTION_LIST,
   319    320     /* ePragFlg:  */ PragFlg_Result0,
   320         -  /* ColNames:  */ 29, 2,
          321  +  /* ColNames:  */ 30, 2,
   321    322     /* iArg:      */ 0 },
   322    323   #endif
   323    324   #endif
   324    325   #if defined(SQLITE_HAS_CODEC)
   325    326    {/* zName:     */ "hexkey",
   326    327     /* ePragTyp:  */ PragTyp_HEXKEY,
   327    328     /* ePragFlg:  */ 0,
................................................................................
   349    350     /* ColNames:  */ 0, 0,
   350    351     /* iArg:      */ 0 },
   351    352   #endif
   352    353   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   353    354    {/* zName:     */ "index_info",
   354    355     /* ePragTyp:  */ PragTyp_INDEX_INFO,
   355    356     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
   356         -  /* ColNames:  */ 12, 3,
          357  +  /* ColNames:  */ 13, 3,
   357    358     /* iArg:      */ 0 },
   358    359    {/* zName:     */ "index_list",
   359    360     /* ePragTyp:  */ PragTyp_INDEX_LIST,
   360    361     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
   361         -  /* ColNames:  */ 21, 5,
          362  +  /* ColNames:  */ 22, 5,
   362    363     /* iArg:      */ 0 },
   363    364    {/* zName:     */ "index_xinfo",
   364    365     /* ePragTyp:  */ PragTyp_INDEX_INFO,
   365    366     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
   366         -  /* ColNames:  */ 15, 6,
          367  +  /* ColNames:  */ 16, 6,
   367    368     /* iArg:      */ 1 },
   368    369   #endif
   369    370   #if !defined(SQLITE_OMIT_INTEGRITY_CHECK)
   370    371    {/* zName:     */ "integrity_check",
   371    372     /* ePragTyp:  */ PragTyp_INTEGRITY_CHECK,
   372    373     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_Result1,
   373    374     /* ColNames:  */ 0, 0,
................................................................................
   432    433   #endif
   433    434   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   434    435   #if !defined(SQLITE_OMIT_VIRTUALTABLE)
   435    436   #if defined(SQLITE_INTROSPECTION_PRAGMAS)
   436    437    {/* zName:     */ "module_list",
   437    438     /* ePragTyp:  */ PragTyp_MODULE_LIST,
   438    439     /* ePragFlg:  */ PragFlg_Result0,
   439         -  /* ColNames:  */ 31, 1,
          440  +  /* ColNames:  */ 7, 1,
   440    441     /* iArg:      */ 0 },
   441    442   #endif
   442    443   #endif
   443    444   #endif
   444    445    {/* zName:     */ "optimize",
   445    446     /* ePragTyp:  */ PragTyp_OPTIMIZE,
   446    447     /* ePragFlg:  */ PragFlg_Result1|PragFlg_NeedSchema,
................................................................................
   465    466     /* ColNames:  */ 0, 0,
   466    467     /* iArg:      */ 0 },
   467    468   #endif
   468    469   #if defined(SQLITE_INTROSPECTION_PRAGMAS)
   469    470    {/* zName:     */ "pragma_list",
   470    471     /* ePragTyp:  */ PragTyp_PRAGMA_LIST,
   471    472     /* ePragFlg:  */ PragFlg_Result0,
   472         -  /* ColNames:  */ 31, 1,
          473  +  /* ColNames:  */ 7, 1,
   473    474     /* iArg:      */ 0 },
   474    475   #endif
   475    476   #if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
   476    477    {/* zName:     */ "query_only",
   477    478     /* ePragTyp:  */ PragTyp_FLAG,
   478    479     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
   479    480     /* ColNames:  */ 0, 0,
................................................................................
   552    553     /* iArg:      */ SQLITE_SqlTrace },
   553    554   #endif
   554    555   #endif
   555    556   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG)
   556    557    {/* zName:     */ "stats",
   557    558     /* ePragTyp:  */ PragTyp_STATS,
   558    559     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq,
   559         -  /* ColNames:  */ 7, 5,
          560  +  /* ColNames:  */ 8, 5,
   560    561     /* iArg:      */ 0 },
   561    562   #endif
   562    563   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   563    564    {/* zName:     */ "synchronous",
   564    565     /* ePragTyp:  */ PragTyp_SYNCHRONOUS,
   565    566     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
   566    567     /* ColNames:  */ 0, 0,
................................................................................
   567    568     /* iArg:      */ 0 },
   568    569   #endif
   569    570   #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   570    571    {/* zName:     */ "table_info",
   571    572     /* ePragTyp:  */ PragTyp_TABLE_INFO,
   572    573     /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt,
   573    574     /* ColNames:  */ 1, 6,
          575  +  /* iArg:      */ 0 },
          576  + {/* zName:     */ "table_ipk",
          577  +  /* ePragTyp:  */ PragTyp_TABLE_IPK,
          578  +  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result1,
          579  +  /* ColNames:  */ 7, 1,
   574    580     /* iArg:      */ 0 },
   575    581   #endif
   576    582   #if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   577    583    {/* zName:     */ "temp_store",
   578    584     /* ePragTyp:  */ PragTyp_TEMP_STORE,
   579    585     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
   580    586     /* ColNames:  */ 0, 0,
................................................................................
   642    648    {/* zName:     */ "writable_schema",
   643    649     /* ePragTyp:  */ PragTyp_FLAG,
   644    650     /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
   645    651     /* ColNames:  */ 0, 0,
   646    652     /* iArg:      */ SQLITE_WriteSchema },
   647    653   #endif
   648    654   };
   649         -/* Number of pragmas: 60 on by default, 77 total. */
          655  +/* Number of pragmas: 61 on by default, 78 total. */

Changes to test/intpkey.test.

     1         -# 2001 September 15
            1  +# 2001-09-15
     2      2   #
     3      3   # The author disclaims copyright to this source code.  In place of
     4      4   # a legal notice, here is a blessing:
     5      5   #
     6      6   #    May you do good and not evil.
     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
................................................................................
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.
    12     12   #
    13     13   # This file implements tests for the special processing associated
    14     14   # with INTEGER PRIMARY KEY columns.
    15     15   #
    16         -# $Id: intpkey.test,v 1.24 2007/11/29 17:43:28 danielk1977 Exp $
    17     16   
    18     17   set testdir [file dirname $argv0]
    19     18   source $testdir/tester.tcl
    20     19   
    21     20   # Create a table with a primary key and a datatype other than
    22     21   # integer
    23     22   #
................................................................................
   637    636   } {248 giraffe}
   638    637   do_execsql_test intpkey-17.2 {
   639    638     DELETE FROM t17;
   640    639     INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
   641    640     UPDATE t17 SET y='ostrich' WHERE abs(x)=248;
   642    641     SELECT * FROM t17 ORDER BY +x;
   643    642   } {123 elephant 248 ostrich}
          643  +
          644  +# 2017-11-28:  The table_ipk pragma.
          645  +#
          646  +do_execsql_test intpkey-18.0 {
          647  +  DROP TABLE IF EXISTS t1;
          648  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY, abc, pqr);
          649  +  PRAGMA table_ipk(t1);
          650  +} {xyz}
          651  +do_execsql_test intpkey-18.1 {
          652  +  DROP TABLE IF EXISTS t1;
          653  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY ASC, abc, pqr);
          654  +  PRAGMA table_ipk(t1);
          655  +} {xyz}
          656  +do_execsql_test intpkey-18.2 {
          657  +  DROP TABLE IF EXISTS t1;
          658  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY DESC, abc, pqr);
          659  +  PRAGMA table_ipk(t1);
          660  +} {}
          661  +do_execsql_test intpkey-18.3 {
          662  +  DROP TABLE IF EXISTS t1;
          663  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz));
          664  +  PRAGMA table_ipk(t1);
          665  +} {xyz}
          666  +do_execsql_test intpkey-18.4 {
          667  +  DROP TABLE IF EXISTS t1;
          668  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz ASC));
          669  +  PRAGMA table_ipk(t1);
          670  +} {xyz}
          671  +do_execsql_test intpkey-18.5 {
          672  +  DROP TABLE IF EXISTS t1;
          673  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz DESC));
          674  +  PRAGMA table_ipk(t1);
          675  +} {xyz}
          676  +do_execsql_test intpkey-18.10 {
          677  +  DROP TABLE IF EXISTS t1;
          678  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY, abc, pqr) WITHOUT ROWID;
          679  +  PRAGMA table_ipk(t1);
          680  +} {}
          681  +do_execsql_test intpkey-18.11 {
          682  +  DROP TABLE IF EXISTS t1;
          683  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY ASC, abc, pqr) WITHOUT ROWID;
          684  +  PRAGMA table_ipk(t1);
          685  +} {}
          686  +do_execsql_test intpkey-18.12 {
          687  +  DROP TABLE IF EXISTS t1;
          688  +  CREATE TABLE t1(xyz INTEGER PRIMARY KEY DESC, abc, pqr) WITHOUT ROWID;
          689  +  PRAGMA table_ipk(t1);
          690  +} {}
          691  +do_execsql_test intpkey-18.13 {
          692  +  DROP TABLE IF EXISTS t1;
          693  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz)) WITHOUT ROWID;
          694  +  PRAGMA table_ipk(t1);
          695  +} {}
          696  +do_execsql_test intpkey-18.14 {
          697  +  DROP TABLE IF EXISTS t1;
          698  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz ASC)) WITHOUT ROWID;
          699  +  PRAGMA table_ipk(t1);
          700  +} {}
          701  +do_execsql_test intpkey-18.15 {
          702  +  DROP TABLE IF EXISTS t1;
          703  +  CREATE TABLE t1(xyz INTEGER, abc, pqr, PRIMARY KEY(xyz DESC)) WITHOUT ROWID;
          704  +  PRAGMA table_ipk(t1);
          705  +} {}
          706  +do_execsql_test intpkey-18.20 {
          707  +  DROP TABLE IF EXISTS t1;
          708  +  PRAGMA table_ipk(t1);
          709  +} {}
          710  +do_execsql_test intpkey-18.21 {
          711  +  CREATE TABLE t1(a,b,xyz INTEGER PRIMARY KEY NOT NULL);
          712  +  SELECT * FROM pragma_table_ipk('t1');
          713  +} {xyz}
          714  +do_execsql_test intpkey-18.22 {
          715  +  PRAGMA table_ipk(pragma_table_ipk);
          716  +} {}
          717  +do_execsql_test intpkey-18.23 {
          718  +  PRAGMA table_info(pragma_table_ipk);
          719  +} {0 name {} 0 {} 0}
          720  +do_execsql_test intpkey-18.24 {
          721  +  CREATE VIEW v1 AS SELECT * FROM t1 WHERE rowid>5;
          722  +  PRAGMA table_ipk(v1);
          723  +} {}
          724  +
   644    725   
   645    726   finish_test

Changes to tool/mkpragmatab.tcl.

   219    219     IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS)
   220    220   
   221    221     NAME: table_info
   222    222     FLAG: NeedSchema Result1 SchemaOpt
   223    223     COLS: cid name type notnull dflt_value pk
   224    224     IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
   225    225   
          226  +  NAME: table_ipk
          227  +  FLAG: NeedSchema Result1
          228  +  COLS: name
          229  +  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
          230  +
   226    231     NAME: stats
   227    232     FLAG: NeedSchema Result0 SchemaReq
   228    233     COLS: tbl idx wdth hght flgs
   229    234     IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG)
   230    235   
   231    236     NAME: index_info
   232    237     TYPE: INDEX_INFO