SQLite

Check-in [f0cf8c85dc]
Login

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

Overview
Comment:Restore the index_list pragma back to its former operation. Create a new PRAGMA stats used to access the table and index widths and heights.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: f0cf8c85dcbcc7778aed2816792c368d777f79cb
User & Date: drh 2013-10-12 20:22:00.025
Context
2013-10-14
10:46
Add a new application_id for GeoPackage version 1.0. (check-in: 98ddfe4571 user: drh tags: trunk)
2013-10-12
23:39
Merge updates from trunk. (Closed-Leaf check-in: a88b5be01e user: mistachkin tags: toTypeFuncs)
20:22
Restore the index_list pragma back to its former operation. Create a new PRAGMA stats used to access the table and index widths and heights. (check-in: f0cf8c85dc user: drh tags: trunk)
15:12
Fix handling of "DROP TABLE" commands when "PRAGMA defer_foreign_keys=1" is set. (check-in: 27001356ed user: dan tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to src/pragma.c.
︙︙
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

#define PragTyp_LOCKING_MODE                  21
#define PragTyp_PAGE_COUNT                    22
#define PragTyp_MMAP_SIZE                     23
#define PragTyp_PAGE_SIZE                     24
#define PragTyp_SECURE_DELETE                 25
#define PragTyp_SHRINK_MEMORY                 26
#define PragTyp_SOFT_HEAP_LIMIT               27

#define PragTyp_SYNCHRONOUS                   28
#define PragTyp_TABLE_INFO                    29
#define PragTyp_TEMP_STORE                    30
#define PragTyp_TEMP_STORE_DIRECTORY          31
#define PragTyp_WAL_AUTOCHECKPOINT            32
#define PragTyp_WAL_CHECKPOINT                33
#define PragTyp_ACTIVATE_EXTENSIONS           34
#define PragTyp_HEXKEY                        35
#define PragTyp_KEY                           36
#define PragTyp_REKEY                         37
#define PragTyp_LOCK_STATUS                   38
#define PragTyp_PARSER_TRACE                  39
#define PragFlag_NeedSchema           0x01
static const struct sPragmaNames {
  const char *const zName;  /* Name of pragma */
  u8 ePragTyp;              /* PragTyp_XXX value */
  u8 mPragFlag;             /* Zero or more PragFlag_XXX values */
  u32 iArg;                 /* Extra argument */
} aPragmaNames[] = {








>
|
|
|
|
|
|
|
|
|
|
|
|








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

#define PragTyp_LOCKING_MODE                  21
#define PragTyp_PAGE_COUNT                    22
#define PragTyp_MMAP_SIZE                     23
#define PragTyp_PAGE_SIZE                     24
#define PragTyp_SECURE_DELETE                 25
#define PragTyp_SHRINK_MEMORY                 26
#define PragTyp_SOFT_HEAP_LIMIT               27
#define PragTyp_STATS                         28
#define PragTyp_SYNCHRONOUS                   29
#define PragTyp_TABLE_INFO                    30
#define PragTyp_TEMP_STORE                    31
#define PragTyp_TEMP_STORE_DIRECTORY          32
#define PragTyp_WAL_AUTOCHECKPOINT            33
#define PragTyp_WAL_CHECKPOINT                34
#define PragTyp_ACTIVATE_EXTENSIONS           35
#define PragTyp_HEXKEY                        36
#define PragTyp_KEY                           37
#define PragTyp_REKEY                         38
#define PragTyp_LOCK_STATUS                   39
#define PragTyp_PARSER_TRACE                  40
#define PragFlag_NeedSchema           0x01
static const struct sPragmaNames {
  const char *const zName;  /* Name of pragma */
  u8 ePragTyp;              /* PragTyp_XXX value */
  u8 mPragFlag;             /* Zero or more PragFlag_XXX values */
  u32 iArg;                 /* Extra argument */
} aPragmaNames[] = {
︙︙
354
355
356
357
358
359
360






361
362
363
364
365
366
367

    /* ePragFlag: */ 0,
    /* iArg:      */ 0 },
#if defined(SQLITE_DEBUG)
  { /* zName:     */ "sql_trace",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_SqlTrace },






#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
  { /* zName:     */ "synchronous",
    /* ePragTyp:  */ PragTyp_SYNCHRONOUS,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif








>
>
>
>
>
>








355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374

    /* ePragFlag: */ 0,
    /* iArg:      */ 0 },
#if defined(SQLITE_DEBUG)
  { /* zName:     */ "sql_trace",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_SqlTrace },
#endif
#if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)
  { /* zName:     */ "stats",
    /* ePragTyp:  */ PragTyp_STATS,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif
#if !defined(SQLITE_OMIT_PAGER_PRAGMAS)
  { /* zName:     */ "synchronous",
    /* ePragTyp:  */ PragTyp_SYNCHRONOUS,
    /* ePragFlag: */ PragFlag_NeedSchema,
    /* iArg:      */ 0 },
#endif
︙︙
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430

    /* iArg:      */ 0 },
#endif
  { /* zName:     */ "writable_schema",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
};
/* Number of pragmas: 55 on by default, 67 total. */
/* End of the automatically generated pragma table.
***************************************************************************/

/*
** Interpret the given string as a safety level.  Return 0 for OFF,
** 1 for ON or NORMAL and 2 for FULL.  Return 1 for an empty or 
** unrecognized string argument.  The FULL option is disallowed








|








423
424
425
426
427
428
429
430
431
432
433
434
435
436
437

    /* iArg:      */ 0 },
#endif
  { /* zName:     */ "writable_schema",
    /* ePragTyp:  */ PragTyp_FLAG,
    /* ePragFlag: */ 0,
    /* iArg:      */ SQLITE_WriteSchema|SQLITE_RecoveryMode },
};
/* Number of pragmas: 56 on by default, 68 total. */
/* End of the automatically generated pragma table.
***************************************************************************/

/*
** Interpret the given string as a safety level.  Return 0 for OFF,
** 1 for ON or NORMAL and 2 for FULL.  Return 1 for an empty or 
** unrecognized string argument.  The FULL option is disallowed
︙︙
1420
1421
1422
1423
1424
1425
1426






























1427
1428
1429
1430
1431
1432
1433

        }
        sqlite3VdbeAddOp2(v, OP_Integer, k, 6);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
      }
    }
  }
  break;































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








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








1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
1440
1441
1442
1443
1444
1445
1446
1447
1448
1449
1450
1451
1452
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470

        }
        sqlite3VdbeAddOp2(v, OP_Integer, k, 6);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
      }
    }
  }
  break;

  case PragTyp_STATS: {
    Index *pIdx;
    HashElem *i;
    v = sqlite3GetVdbe(pParse);
    sqlite3VdbeSetNumCols(v, 4);
    pParse->nMem = 4;
    sqlite3CodeVerifySchema(pParse, iDb);
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "table", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "index", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "width", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 3, COLNAME_NAME, "height", SQLITE_STATIC);
    for(i=sqliteHashFirst(&pDb->pSchema->tblHash); i; i=sqliteHashNext(i)){
      Table *pTab = sqliteHashData(i);
      sqlite3VdbeAddOp4(v, OP_String8, 0, 1, 0, pTab->zName, 0);
      sqlite3VdbeAddOp2(v, OP_Null, 0, 2);
      sqlite3VdbeAddOp2(v, OP_Integer,
                           (int)sqlite3LogEstToInt(pTab->szTabRow), 3);
      sqlite3VdbeAddOp2(v, OP_Integer, (int)pTab->nRowEst, 4);
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
      for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
        sqlite3VdbeAddOp2(v, OP_Integer,
                             (int)sqlite3LogEstToInt(pIdx->szIdxRow), 3);
        sqlite3VdbeAddOp2(v, OP_Integer, (int)pIdx->aiRowEst[0], 4);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
      }
    }
  }
  break;

  case PragTyp_INDEX_INFO: if( zRight ){
    Index *pIdx;
    Table *pTab;
    pIdx = sqlite3FindIndex(db, zRight, zDb);
    if( pIdx ){
      int i;
︙︙
1453
1454
1455
1456
1457
1458
1459
1460
1461
1462
1463
1464
1465
1466
1467
1468
1469
1470
1471
1472
1473
1474
1475
1476
1477
1478
1479
1480
1481
1482
1483
1484
1485
1486

  case PragTyp_INDEX_LIST: if( zRight ){
    Index *pIdx;
    Table *pTab;
    int i;
    pTab = sqlite3FindTable(db, zRight, zDb);
    if( pTab ){
      v = sqlite3GetVdbe(pParse);
      sqlite3VdbeSetNumCols(v, 4);
      pParse->nMem = 4;
      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, "avgrowsize", SQLITE_STATIC);
      sqlite3VdbeAddOp2(v, OP_Integer, 0, 1);
      sqlite3VdbeAddOp2(v, OP_Null, 0, 2);
      sqlite3VdbeAddOp2(v, OP_Integer, 1, 3);
      sqlite3VdbeAddOp2(v, OP_Integer,
                           (int)sqlite3LogEstToInt(pTab->szTabRow), 4);
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
      for(pIdx=pTab->pIndex, i=1; pIdx; pIdx=pIdx->pNext, i++){
        sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
        sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);
        sqlite3VdbeAddOp2(v, OP_Integer,
                             (int)sqlite3LogEstToInt(pIdx->szIdxRow), 4);
        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
      }
    }
  }
  break;

  case PragTyp_DATABASE_LIST: {
    int i;








|
|




<
<
<
<
<
<
<
|



<
<
|








1490
1491
1492
1493
1494
1495
1496
1497
1498
1499
1500
1501
1502







1503
1504
1505
1506


1507
1508
1509
1510
1511
1512
1513
1514

  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;
      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);







      for(pIdx=pTab->pIndex, i=0; pIdx; pIdx=pIdx->pNext, i++){
        sqlite3VdbeAddOp2(v, OP_Integer, i, 1);
        sqlite3VdbeAddOp4(v, OP_String8, 0, 2, 0, pIdx->zName, 0);
        sqlite3VdbeAddOp2(v, OP_Integer, pIdx->onError!=OE_None, 3);


        sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
      }
    }
  }
  break;

  case PragTyp_DATABASE_LIST: {
    int i;
︙︙
Changes to test/pragma.test.
︙︙
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

      pragma foreign_key_list(t5);
    }
  } {}
  do_test pragma-6.4 {
    execsql {
      pragma index_list(t3);
    }
  } {/0 {} 1 \d+ 1 sqlite_autoindex_t3_1 1 \d+/}
}
ifcapable {!foreignkey} {
  execsql {CREATE TABLE t3(a,b UNIQUE)}
}
do_test pragma-6.5.1 {
  execsql {
    CREATE INDEX t3i1 ON t3(a,b);








|








570
571
572
573
574
575
576
577
578
579
580
581
582
583
584

      pragma foreign_key_list(t5);
    }
  } {}
  do_test pragma-6.4 {
    execsql {
      pragma index_list(t3);
    }
  } {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);
︙︙
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657

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);
  }
} {/0 {} 1 \d+ 1 t3i1 0 \d+ 2 sqlite_autoindex_t3_1 1 \d+/}
do_test pragma-7.1.2 {
  execsql {
    pragma index_list(t3_bogus);
  }
} {}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {








|








643
644
645
646
647
648
649
650
651
652
653
654
655
656
657

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);
  }
} {0 t3i1 0 1 sqlite_autoindex_t3_1 1}
do_test pragma-7.1.2 {
  execsql {
    pragma index_list(t3_bogus);
  }
} {}
} ;# ifcapable schema_pragmas
ifcapable {utf16} {
︙︙
1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671

  db2 eval {PRAGMA index_info(i2)}
} {0 2 c 1 3 d 2 1 b}
do_test 23.3 {
  db eval {
    CREATE INDEX i3 ON t1(d,b,c);
  }
  db2 eval {PRAGMA index_list(t1)}
} {/0 {} 1 \d+ 1 i3 0 \d+ 2 i2 0 \d+ 3 i1 0 \d+/}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }








|








1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671

  db2 eval {PRAGMA index_info(i2)}
} {0 2 c 1 3 d 2 1 b}
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}
do_test 23.4 {
  db eval {
    ALTER TABLE t1 ADD COLUMN e;
  }
  db2 eval {
    PRAGMA table_info(t1);
  }
︙︙
Changes to tool/mkpragmatab.tcl.
︙︙
165
166
167
168
169
170
171




172
173
174
175
176
177
178

  IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS) && SQLITE_ENABLE_LOCKING_STYLE

  NAME: synchronous
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS)

  NAME: table_info




  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_SCHEMA_PRAGMAS)

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









>
>
>
>








165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182

  IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS) && SQLITE_ENABLE_LOCKING_STYLE

  NAME: synchronous
  FLAG: NeedSchema
  IF:   !defined(SQLITE_OMIT_PAGER_PRAGMAS)

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

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

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