SQLite

Changes On Branch pragma-table-ipk
Login

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

Changes In Branch pragma-table-ipk Excluding Merge-Ins

This is equivalent to a diff from e6b89304 to 2494132a

2017-11-28
07:52
Add experimental feature to detect threading bugs in apps that use SQLITE_CONFIG_MULTITHREADED. Enabled at compile time using SQLITE_ENABLE_MULTITHREADED_CHECKS. (check-in: 40b598c8 user: dan tags: trunk)
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)
2017-11-27
17:56
Fix a faulty NEVER assert() that could fail for SQLITE_ENABLE_STAT4 builds that use foreign keys. (check-in: 465350e5 user: dan tags: trunk)

Changes to src/pragma.c.

1110
1111
1112
1113
1114
1115
1116



















1117
1118
1119
1120
1121
1122
1123
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142







+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+







               pCol->zName,
               sqlite3ColumnType(pCol,""),
               pCol->notNull ? 1 : 0,
               pCol->pDflt ? pCol->pDflt->u.zToken : 0,
               k);
      }
    }
  }
  break;

  /*
  **   PRAGMA table_ipk(<table>)
  **
  ** If <table> has an INTEGER PRIMARY KEY column that is an alias for
  ** the ROWID, then return the name of that column.  If <table> does not
  ** have a ROWID alias, or if it does not have a ROWID, or if <table> 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;
    HashElem *i;

Changes to src/pragma.h.

36
37
38
39
40
41
42

43
44
45
46
47
48
49
50
51
52
53
54












55
56
57
58
59
60
61
36
37
38
39
40
41
42
43












44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62







+
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+







#define PragTyp_PAGE_SIZE                     28
#define PragTyp_PRAGMA_LIST                   29
#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_TABLE_IPK                     35
#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_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 */
#define PragFlg_ReadOnly   0x08 /* Read-only HEADER_VALUE */
#define PragFlg_Result0    0x10 /* Acts as query when no argument */
71
72
73
74
75
76
77

78

79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101























102
103
104
105
106
107
108
109
72
73
74
75
76
77
78
79

80























81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

104
105
106
107
108
109
110







+
-
+
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
-







  /*   0 */ "cache_size",  /* Used by: default_cache_size */
  /*   1 */ "cid",         /* Used by: table_info */
  /*   2 */ "name",       
  /*   3 */ "type",       
  /*   4 */ "notnull",    
  /*   5 */ "dflt_value", 
  /*   6 */ "pk",         
  /*   7 */ "name",        /* Used by: table_ipk module_list pragma_list */
  /*   7 */ "tbl",         /* Used by: stats */
  /*   8 */ "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",    
  /*   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",    
  /*  31 */ "name",        /* Used by: module_list pragma_list */
  /*  32 */ "seq",         /* Used by: collation_list */
  /*  33 */ "name",       
  /*  34 */ "id",          /* Used by: foreign_key_list */
  /*  35 */ "seq",        
  /*  36 */ "table",      
  /*  37 */ "from",       
  /*  38 */ "to",         
233
234
235
236
237
238
239
240

241
242
243
244
245
246
247
234
235
236
237
238
239
240

241
242
243
244
245
246
247
248







-
+







  /* ColNames:  */ 0, 0,
  /* iArg:      */ BTREE_DATA_VERSION },
#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,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 1,
313
314
315
316
317
318
319
320

321
322
323
324
325
326
327
314
315
316
317
318
319
320

321
322
323
324
325
326
327
328







-
+







  /* iArg:      */ SQLITE_FullFSync },
#endif
#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",
  /* ePragTyp:  */ PragTyp_HEXKEY,
  /* ePragFlg:  */ 0,
349
350
351
352
353
354
355
356

357
358
359
360
361

362
363
364
365
366

367
368
369
370
371
372
373
350
351
352
353
354
355
356

357
358
359
360
361

362
363
364
365
366

367
368
369
370
371
372
373
374







-
+




-
+




-
+







  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#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,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_Result1,
  /* ColNames:  */ 0, 0,
432
433
434
435
436
437
438
439

440
441
442
443
444
445
446
433
434
435
436
437
438
439

440
441
442
443
444
445
446
447







-
+







#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
#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",
  /* ePragTyp:  */ PragTyp_OPTIMIZE,
  /* ePragFlg:  */ PragFlg_Result1|PragFlg_NeedSchema,
465
466
467
468
469
470
471
472

473
474
475
476
477
478
479
466
467
468
469
470
471
472

473
474
475
476
477
478
479
480







-
+







  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#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,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
552
553
554
555
556
557
558
559

560
561
562
563
564
565
566
567
568
569
570
571
572
573





574
575
576
577
578
579
580
553
554
555
556
557
558
559

560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586







-
+














+
+
+
+
+







  /* iArg:      */ SQLITE_SqlTrace },
#endif
#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,
  /* ePragFlg:  */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
  /* iArg:      */ 0 },
#endif
#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,
  /* ePragFlg:  */ PragFlg_Result0|PragFlg_NoColumns1,
  /* ColNames:  */ 0, 0,
642
643
644
645
646
647
648
649

648
649
650
651
652
653
654

655







-
+
 {/* zName:     */ "writable_schema",
  /* ePragTyp:  */ PragTyp_FLAG,
  /* 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. */

Changes to test/intpkey.test.

1

2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

16
17
18
19
20
21
22
-
+














-







# 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.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# 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
# integer
#
637
638
639
640
641
642
643
644


















































































645
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726








+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+

} {248 giraffe}
do_execsql_test intpkey-17.2 {
  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

Changes to tool/mkpragmatab.tcl.

219
220
221
222
223
224
225





226
227
228
229
230
231
232
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237







+
+
+
+
+







  IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS)

  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)

  NAME: index_info
  TYPE: INDEX_INFO