SQLite

Changes On Branch index_xinfo
Login
Hide Diff Patch Side-by-Side Diff

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

Changes In Branch index_xinfo Excluding Merge-Ins

This is equivalent to a diff from 71691c4b to 3af19f84

2015-02-06
01:07
Add the index_xinfo pragma which gives information about the fields that reference the table PRIMARY KEY in addition to the index key fields. Add extra columns "desc", "coll", and "key" to the index_info and index_xinfo pragmas. Add the "origin" and "partial" columns to the index_list pragma. (check-in: 2743846c user: drh tags: trunk)
2015-02-04
23:51
Merge all changes from index_xinfo, including the move of the pragma table into the separate pragma.h file. (check-in: 21e95d28 user: drh tags: ota-update)
23:13
Merge all recent trunk changes, including the movement of the pragma table into the separate pragma.h header file. (Closed-Leaf check-in: 3af19f84 user: drh tags: index_xinfo)
20:56
Fix a missing mutex in SQLITE_TESTCTRL_IMPOSTER. (check-in: 71691c4b user: drh tags: trunk)
2015-02-03
19:20
Fix a typo in the --help output for speedtest1. Fix a dependency error in the Makefile.in for speedtest1. (check-in: f30a057a user: drh tags: trunk)
2015-01-31
02:00
Add the "index_xinfo" pragma. Add new columns to the "index_info" and "index_list" pragmas. (check-in: 30f51d7b user: drh tags: index_xinfo)

Changes to src/pragma.c.

︙
277
278
279
280
281
282
283

284
285
286
287
288
289
290

277
278
279
280
281
282
283
284
285
286
287
288
289
290
291








+








  char *aFcntl[4];       /* Argument to SQLITE_FCNTL_PRAGMA */
  int iDb;               /* Database index for <database> */
  int lwr, upr, mid = 0;       /* Binary search bounds */
  int rc;                      /* return value form SQLITE_FCNTL_PRAGMA */
  sqlite3 *db = pParse->db;    /* The database connection */
  Db *pDb;                     /* The specific database being pragmaed */
  Vdbe *v = sqlite3GetVdbe(pParse);  /* Prepared statement */
  const struct sPragmaNames *pPragma;

  if( v==0 ) return;
  sqlite3VdbeRunOnlyOnce(v);
  pParse->nMem = 2;

  /* Interpret the [database.] part of the pragma statement. iDb is the
  ** index of the database this pragma is being applied to in db.aDb[]. */
︙
354
355
356
357
358
359
360

361
362
363

364
365
366
367
368

369
370
371
372
373
374
375

355
356
357
358
359
360
361
362
363
364

365
366
367
368
369

370
371
372
373
374
375
376
377








+


-
+




-
+








    if( rc<0 ){
      upr = mid - 1;
    }else{
      lwr = mid + 1;
    }
  }
  if( lwr>upr ) goto pragma_out;
  pPragma = &aPragmaNames[mid];

  /* Make sure the database schema is loaded if the pragma requires that */
  if( (aPragmaNames[mid].mPragFlag & PragFlag_NeedSchema)!=0 ){
  if( (pPragma->mPragFlag & PragFlag_NeedSchema)!=0 ){
    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
  }

  /* Jump to the appropriate pragma handler */
  switch( aPragmaNames[mid].ePragTyp ){
  switch( pPragma->ePragTyp ){
  
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS) && !defined(SQLITE_OMIT_DEPRECATED)
  /*
  **  PRAGMA [database.]default_cache_size
  **  PRAGMA [database.]default_cache_size=N
  **
  ** The first form reports the current persistent setting for the
︙
940
941
942
943
944
945
946
947

948
949
950

951
952
953
954
955
956
957

942
943
944
945
946
947
948

949

950

951
952
953
954
955
956
957
958








-
+
-

-
+








    break;
  }
#endif /* SQLITE_OMIT_PAGER_PRAGMAS */

#ifndef SQLITE_OMIT_FLAG_PRAGMAS
  case PragTyp_FLAG: {
    if( zRight==0 ){
      returnSingleInt(pParse, aPragmaNames[mid].zName,
      returnSingleInt(pParse, pPragma->zName, (db->flags & pPragma->iArg)!=0 );
                     (db->flags & aPragmaNames[mid].iArg)!=0 );
    }else{
      int mask = aPragmaNames[mid].iArg;    /* Mask of bits to set or clear. */
      int mask = pPragma->iArg;    /* Mask of bits to set or clear. */
      if( db->autoCommit==0 ){
        /* Foreign key support may not be enabled or disabled while not
        ** in auto-commit mode.  */
        mask &= ~(SQLITE_ForeignKeys);
      }
#if SQLITE_USER_AUTHENTICATION
      if( db->auth.authLevel==UAUTH_User ){
︙
1072
1073
1074
1075
1076
1077
1078

1079
1080
1081


1082
1083
1084
1085



1086

1087
1088
1089
1090
1091
1092









1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106


1107
1108
1109
1110


1111

1112
1113
1114


1115

1116
1117
1118
1119
1120
1121
1122

1073
1074
1075
1076
1077
1078
1079
1080
1081


1082
1083
1084
1085
1086
1087
1088
1089
1090

1091
1092
1093
1094



1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
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








+

-
-
+
+




+
+
+
-
+



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












-
-
+
+




+
+

+



+
+
-
+









  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);
    if( pIdx ){
      int i;
      int mx = pPragma->iArg ? pIdx->nColumn : pIdx->nKeyCol;
      pTab = pIdx->pTable;
      sqlite3VdbeSetNumCols(v, 3);
      pParse->nMem = 3;
      sqlite3VdbeSetNumCols(v, 6);
      pParse->nMem = 6;
      sqlite3CodeVerifySchema(pParse, iDb);
      sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seqno", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "cid", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "name", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "desc", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 4, COLNAME_NAME, "coll", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 5, COLNAME_NAME, "key", SQLITE_STATIC);
      for(i=0; i<pIdx->nKeyCol; i++){
      for(i=0; i<mx; i++){
        i16 cnum = pIdx->aiColumn[i];
        sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
        sqlite3VdbeAddOp2(v, OP_Integer, cnum, 2);
        assert( pTab->nCol>cnum );
        sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pTab->aCol[cnum].zName, 0);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
        if( cnum<0 ){
          sqlite3VdbeAddOp2(v, OP_Null, 0, 3);
        }else{
          sqlite3VdbeAddOp4(v, OP_String8, 0, 3, 0, pTab->aCol[cnum].zName, 0);
        }
        sqlite3VdbeAddOp2(v, OP_Integer, pIdx->aSortOrder[i], 4);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 5, 0, pIdx->azColl[i], 0);
        sqlite3VdbeAddOp2(v, OP_Integer, i<pIdx->nKeyCol, 6);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
      }
    }
  }
  break;

  case PragTyp_INDEX_LIST: if( zRight ){
    Index *pIdx;
    Table *pTab;
    int i;
    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);
      sqlite3VdbeSetNumCols(v, 3);
      pParse->nMem = 3;
      sqlite3VdbeSetNumCols(v, 5);
      pParse->nMem = 5;
      sqlite3CodeVerifySchema(pParse, iDb);
      sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "seq", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "name", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "unique", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "origin", SQLITE_STATIC);
      sqlite3VdbeSetColName(v, 4, COLNAME_NAME, "partial", SQLITE_STATIC);
      for(pIdx=pTab->pIndex, i=0; pIdx; pIdx=pIdx->pNext, i++){
        const char *azOrigin[] = { "c", "u", "pk" };
        sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
        sqlite3VdbeAddOp2(v, OP_Integer, IsUniqueIndex(pIdx), 3);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 4, 0, azOrigin[pIdx->idxType], 0);
        sqlite3VdbeAddOp2(v, OP_Integer, pIdx->pPartIdxWhere!=0, 5);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5);
      }
    }
  }
  break;

  case PragTyp_DATABASE_LIST: {
    int i;
︙
1678
1679
1680
1681
1682
1683
1684
1685

1686
1687

1688
1689
1690
1691
1692
1693
1694

1694
1695
1696
1697
1698
1699
1700

1701
1702

1703
1704
1705
1706
1707
1708
1709
1710








-
+

-
+








  ** the schema-version is potentially dangerous and may lead to program
  ** crashes or database corruption. Use with caution!
  **
  ** The user-version is not used internally by SQLite. It may be used by
  ** applications for any purpose.
  */
  case PragTyp_HEADER_VALUE: {
    int iCookie = aPragmaNames[mid].iArg;  /* Which cookie to read or write */
    int iCookie = pPragma->iArg;  /* Which cookie to read or write */
    sqlite3VdbeUsesBtree(v, iDb);
    if( zRight && (aPragmaNames[mid].mPragFlag & PragFlag_ReadOnly)==0 ){
    if( zRight && (pPragma->mPragFlag & PragFlag_ReadOnly)==0 ){
      /* Write the specified cookie value */
      static const VdbeOpList setCookie[] = {
        { OP_Transaction,    0,  1,  0},    /* 0 */
        { OP_Integer,        0,  1,  0},    /* 1 */
        { OP_SetCookie,      0,  0,  1},    /* 2 */
      };
      int addr = sqlite3VdbeAddOpList(v, ArraySize(setCookie), setCookie, 0);
︙
1800
1801
1802
1803
1804
1805
1806
1807

1808
1809
1810
1811
1812
1813
1814

1816
1817
1818
1819
1820
1821
1822

1823
1824
1825
1826
1827
1828
1829
1830








-
+








  **
  ** Call sqlite3_busy_timeout(db, N).  Return the current timeout value
  ** if one is set.  If no busy handler or a different busy handler is set
  ** then 0 is returned.  Setting the busy_timeout to 0 or negative
  ** disables the timeout.
  */
  /*case PragTyp_BUSY_TIMEOUT*/ default: {
    assert( aPragmaNames[mid].ePragTyp==PragTyp_BUSY_TIMEOUT );
    assert( pPragma->ePragTyp==PragTyp_BUSY_TIMEOUT );
    if( zRight ){
      sqlite3_busy_timeout(db, sqlite3Atoi(zRight));
    }
    returnSingleInt(pParse, "timeout",  db->busyTimeout);
    break;
  }
︙

Changes to src/pragma.h.

︙
232
233
234
235
236
237
238




239
240
241
242
243
244
245

232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249








+
+
+
+








    /* ePragTyp:  */ PragTyp_INDEX_INFO,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
  { /* zName:     */ "index_list",
    /* ePragTyp:  */ PragTyp_INDEX_LIST,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
  { /* zName:     */ "index_xinfo",
    /* ePragTyp:  */ PragTyp_INDEX_INFO,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 1 },
#endif
#if !defined(SQLITE_OMIT_INTEGRITY_CHECK)
  { /* zName:     */ "integrity_check",
    /* ePragTyp:  */ PragTyp_INTEGRITY_CHECK,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif
︙
448
449
450
451
452
453
454
455


452
453
454
455
456
457
458

459








-
+

#if !defined(SQLITE_OMIT_FLAG_PRAGMAS)
  { /* zName:     */ "writable_schema",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
#endif
};
/* Number of pragmas: 58 on by default, 71 total. */
/* Number of pragmas: 59 on by default, 72 total. */

Changes to test/pragma.test.

︙
46
47
48
49
50
51
52
























53
54
55
56
57
58
59

46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83








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








#              directive - if it is present.
#

ifcapable !pragma {
  finish_test
  return
}

# Capture the output of a pragma in a TEMP table.
#
proc capture_pragma {db tabname sql} {
  $db eval "DROP TABLE IF EXISTS temp.$tabname"
  set once 1
  $db eval $sql x {
    if {$once} {
      set once 0
      set ins "INSERT INTO $tabname VALUES"
      set crtab "CREATE TEMP TABLE $tabname "
      set sep "("
      foreach col $x(*) {
        append ins ${sep}\$x($col)
        append crtab ${sep}\"$col\"
        set sep ,
      }
      append ins )
      append crtab )
      $db eval $crtab
    }
    $db eval $ins
  }
}

# Delete the preexisting database to avoid the special setup
# that the "all.test" script does.
#
db close
delete_file test.db test.db-journal
delete_file test3.db test3.db-journal
︙
616
617
618
619
620
621
622
623

624
625

626
627
628
629
630
631
632
633


634
635

636
637
638
639
640
641
642

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








-
+


+








+
+


+








  } {}
  do_test pragma-6.3.4 {
    execsql {
      pragma foreign_key_list(t5);
    }
  } {}
  do_test pragma-6.4 {
    execsql {
    capture_pragma db out {
      pragma index_list(t3);
    }
    db eval {SELECT seq, "name", "unique" FROM out ORDER BY seq}
  } {0 sqlite_autoindex_t3_1 1}
}
ifcapable {!foreignkey} {
  execsql {CREATE TABLE t3(a,b UNIQUE)}
}
do_test pragma-6.5.1 {
  execsql {
    CREATE INDEX t3i1 ON t3(a,b);
  }
  capture_pragma db out {
    pragma index_info(t3i1);
  }
  db eval {SELECT seqno, cid, name FROM out ORDER BY seqno}
} {0 0 a 1 1 b}
do_test pragma-6.5.2 {
  execsql {
    pragma index_info(t3i1_bogus);
  }
} {}
︙
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

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


727
728
729
730
731
732
733
734
735








-

+
+
+















-
-
+
-
-
+
+








    CREATE TABLE test_table(
      one INT NOT NULL DEFAULT -1, 
      two text,
      three VARCHAR(45, 65) DEFAULT 'abcde',
      four REAL DEFAULT X'abcdef',
      five DEFAULT CURRENT_TIME
    );
    PRAGMA table_info(test_table);
  }
  capture_pragma db out {PRAGMA table_info(test_table)}
  db eval {SELECT cid, "name", type, "notnull", dflt_value, pk FROM out
            ORDER BY cid}
} [concat \
  {0 one INT 1 -1 0} \
  {1 two text 0 {} 0} \
  {2 three {VARCHAR(45, 65)} 0 'abcde' 0} \
  {3 four REAL 0 X'abcdef' 0} \
  {4 five {} 0 CURRENT_TIME 0} \
]
} ;# ifcapable schema_pragmas
# Miscellaneous tests
#
ifcapable schema_pragmas {
do_test pragma-7.1.1 {
  # Make sure a pragma knows to read the schema if it needs to
  db close
  sqlite3 db test.db
  execsql {
    pragma index_list(t3);
  capture_pragma db out "PRAGMA index_list(t3)"
  }
} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
  db eval {SELECT name, "origin" FROM out ORDER BY name DESC}
} {t3i1 c sqlite_autoindex_t3_1 u}
do_test pragma-7.1.2 {
  execsql {
    pragma index_list(t3_bogus);
  }
} {}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {
︙
1701
1702
1703
1704
1705
1706
1707
1708

1709
1710
1711
1712
1713
1714








1715
1716
1717
1718
1719
1720



1721
1722
1723
1724
1725
1726
1727

1730
1731
1732
1733
1734
1735
1736

1737
1738
1739
1740
1741


1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753


1754
1755
1756
1757
1758
1759
1760
1761
1762
1763








-
+




-
-
+
+
+
+
+
+
+
+




-
-
+
+
+








    CREATE TABLE t1(a INTEGER PRIMARY KEY,b,c,d);
    CREATE INDEX i1 ON t1(b,c);
    CREATE INDEX i2 ON t1(c,d);
    CREATE TABLE t2(x INTEGER REFERENCES t1);
  }
  db2 eval {SELECT name FROM sqlite_master}
} {t1 i1 i2 t2}
do_test 23.2 {
do_test 23.2a {
  db eval {
    DROP INDEX i2;
    CREATE INDEX i2 ON t1(c,d,b);
  }
  db2 eval {PRAGMA index_info(i2)}
} {0 2 c 1 3 d 2 1 b}
  capture_pragma db2 out {PRAGMA index_info(i2)}
  db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 |}
do_test 23.2b {
breakpoint;
  capture_pragma db2 out {PRAGMA index_xinfo(i2)}
  db2 eval {SELECT cid, name, "desc", coll, "key", '|' FROM out ORDER BY seqno}
} {2 c 0 BINARY 1 | 3 d 0 BINARY 1 | 1 b 0 BINARY 1 | -1 {} 0 BINARY 0 |}
do_test 23.3 {
  db eval {
    CREATE INDEX i3 ON t1(d,b,c);
  }
  db2 eval {PRAGMA index_list(t1)}
} {0 i3 0 1 i2 0 2 i1 0}
  capture_pragma db2 out {PRAGMA index_list(t1)}
  db2 eval {SELECT name, "unique", origin FROM out ORDER BY seq}
} {i3 0 c i2 0 c i1 0 c}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }
︙

Changes to tool/mkpragmatab.tcl.

︙
201
202
203
204
205
206
207








208
209
210
211
212
213
214

201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222








+
+
+
+
+
+
+
+








  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: stats
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: index_info
  TYPE: INDEX_INFO
  ARG:  0
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: index_xinfo
  TYPE: INDEX_INFO
  ARG:  1
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

  NAME: index_list
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
︙