SQLite

Check-in [62274ff683]
Login

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

Overview
Comment:Enhance the "PRAGMA index_info()" and "PRAGMA index_xinfo()" statements so that they allow a WITHOUT ROWID table as their argument, and in that case show the structure of the underlying index used to implement the WITHOUT ROWID table.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | wor-pk-dups
Files: files | file ages | folders
SHA3-256: 62274ff683ba077d8b0fa34ad653ce235a8c5cbfdb7ab8558469af7333d03698
User & Date: drh 2019-07-17 11:01:11.853
Context
2019-07-17
12:42
New test cases for PRAGMA index_xinfo on a WITHOUT ROWID table. And new testcases using index_xinfo to verify that WITHOUT ROWID tables are constructed correctly. (Closed-Leaf check-in: 340378c1e6 user: drh tags: wor-pk-dups)
11:01
Enhance the "PRAGMA index_info()" and "PRAGMA index_xinfo()" statements so that they allow a WITHOUT ROWID table as their argument, and in that case show the structure of the underlying index used to implement the WITHOUT ROWID table. (check-in: 62274ff683 user: drh tags: wor-pk-dups)
09:18
Fix problems with duplicate fields in the PRIMARY KEYs of WITHOUT ROWID tables. (check-in: bd9a47a3a2 user: dan tags: wor-pk-dups)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/pragma.c.
︙︙
1153
1154
1155
1156
1157
1158
1159









1160
1161
1162
1163
1164
1165
1166

  break;
#endif

  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);









    if( pIdx ){
      int iIdxDb = sqlite3SchemaToIndex(db, pIdx->pSchema);
      int i;
      int mx;
      if( pPragma->iArg ){
        /* PRAGMA index_xinfo (newer version with more rows and columns) */
        mx = pIdx->nColumn;








>
>
>
>
>
>
>
>
>








1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175

  break;
#endif

  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);
    if( pIdx==0 ){
      /* If there is no index named zRight, check to see if there is a
      ** WITHOUT ROWID table named zRight, and if there is, show the
      ** structure of the PRIMARY KEY index for that table. */
      pTab = sqlite3LocateTable(pParse, LOCATE_NOERR, zRight, zDb);
      if( pTab && !HasRowid(pTab) ){
        pIdx = sqlite3PrimaryKeyIndex(pTab);
      }
    }
    if( pIdx ){
      int iIdxDb = sqlite3SchemaToIndex(db, pIdx->pSchema);
      int i;
      int mx;
      if( pPragma->iArg ){
        /* PRAGMA index_xinfo (newer version with more rows and columns) */
        mx = pIdx->nColumn;
︙︙
Changes to test/without_rowid7.test.
︙︙
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35












36




37
38




do_catchsql_test 1.1 {
  INSERT INTO t1 VALUES(1, 'one'), (1, 'ONE');
} {1 {UNIQUE constraint failed: t1.a, t1.b}}


do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, PRIMARY KEY(a, a COLLATE nocase, a)) WITHOUT ROWID;
}

do_execsql_test 2.1 {
  INSERT INTO t2 VALUES(1, 'one');
  SELECT b FROM t2;
} {one}


















finish_test











|







>
>
>
>
>
>
>
>
>
>
>
>

>
>
>
>
|

>
>

21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56


do_catchsql_test 1.1 {
  INSERT INTO t1 VALUES(1, 'one'), (1, 'ONE');
} {1 {UNIQUE constraint failed: t1.a, t1.b}}


do_execsql_test 2.0 {
  CREATE TABLE t2(a, b, PRIMARY KEY(a COLLATE nocase, a)) WITHOUT ROWID;
}

do_execsql_test 2.1 {
  INSERT INTO t2 VALUES(1, 'one');
  SELECT b FROM t2;
} {one}

do_execsql_test 2.2a {
  PRAGMA index_info(t2);
} {0 0 a 1 0 a}
do_execsql_test 2.2b {
  SELECT *, '|' FROM pragma_index_info('t2');
} {0 0 a | 1 0 a |}
do_execsql_test 2.3a {
  PRAGMA index_xinfo(t2);
} {0 0 a 0 nocase 1 1 0 a 0 BINARY 1 2 1 b 0 BINARY 0}
do_execsql_test 2.3b {
  SELECT *, '|' FROM pragma_index_xinfo('t2');
} {0 0 a 0 nocase 1 | 1 0 a 0 BINARY 1 | 2 1 b 0 BINARY 0 |}

do_execsql_test 2.4 {
  CREATE TABLE t3(a, b, PRIMARY KEY(a COLLATE nocase, a));
  PRAGMA index_info(t3);
} {}



finish_test