Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Enhance the Index and Table objects so that they remember if their stats come from the sqlite_stat1 table. Make the "PRAGMA stats" an SQLITE_DEBUG only pragma. Add the flags column to "PRAGMA stats". These are all preliminary steps toward a "PRAGMA analyze_ifneeded;" feature. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | auto-analyze |
| Files: | files | file ages | folders |
| SHA1: |
85026c8ee143bbd46565660fff8346ef |
| User & Date: | drh 2017-02-17 13:38:15 |
Context
|
2017-02-17
| ||
| 15:26 | Set the TF_StatsUsed flag on tables when the query planner outcome is affected by the sqlite_stat1 data. Also, change the column names of the "PRAGMA stats" command so that they are not keywords. check-in: fb2b8ae8 user: drh tags: auto-analyze | |
| 13:38 | Enhance the Index and Table objects so that they remember if their stats come from the sqlite_stat1 table. Make the "PRAGMA stats" an SQLITE_DEBUG only pragma. Add the flags column to "PRAGMA stats". These are all preliminary steps toward a "PRAGMA analyze_ifneeded;" feature. check-in: 85026c8e user: drh tags: auto-analyze | |
| 02:04 | Fix a test case that was made to fail by the LIKE optimization enhancement in check-in [158290c0ab] but which went unnoticed because test builds were running with ICU enabled and ICU disables the LIKE optimization. check-in: 218b2bbb user: drh tags: trunk | |
Changes
Changes to src/analyze.c.
1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 .... 1831 1832 1833 1834 1835 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 .... 1862 1863 1864 1865 1866 1867 1868 1869 1870 1871 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 |
pIndex->aiRowEst = (tRowcnt*)sqlite3MallocZero(sizeof(tRowcnt) * nCol);
if( pIndex->aiRowEst==0 ) sqlite3OomFault(pInfo->db);
}
aiRowEst = pIndex->aiRowEst;
#endif
pIndex->bUnordered = 0;
decodeIntArray((char*)z, nCol, aiRowEst, pIndex->aiRowLogEst, pIndex);
if( pIndex->pPartIdxWhere==0 ) pTable->nRowLogEst = pIndex->aiRowLogEst[0];
}else{
Index fakeIdx;
fakeIdx.szIdxRow = pTable->szTabRow;
#ifdef SQLITE_ENABLE_COSTMULT
fakeIdx.pTable = pTable;
#endif
decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
pTable->szTabRow = fakeIdx.szIdxRow;
}
return 0;
}
/*
** If the Index.aSample variable is not NULL, delete the aSample[] array
................................................................................
** code may be ignored.
*/
int sqlite3AnalysisLoad(sqlite3 *db, int iDb){
analysisInfo sInfo;
HashElem *i;
char *zSql;
int rc = SQLITE_OK;
assert( iDb>=0 && iDb<db->nDb );
assert( db->aDb[iDb].pBt!=0 );
/* Clear any prior statistics */
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
pIdx->aiRowLogEst[0] = 0;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
sqlite3DeleteIndexSamples(db, pIdx);
pIdx->aSample = 0;
#endif
}
/* Load new statistics out of the sqlite_stat1 table */
................................................................................
rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
sqlite3DbFree(db, zSql);
}
}
/* Set appropriate defaults on all indexes not in the sqlite_stat1 table */
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
if( pIdx->aiRowLogEst[0]==0 ) sqlite3DefaultRowEst(pIdx);
}
/* Load the statistics from the sqlite_stat4 table. */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
if( rc==SQLITE_OK && OptimizationEnabled(db, SQLITE_Stat34) ){
db->lookaside.bDisable++;
rc = loadStat4(db, sInfo.zDatabase);
db->lookaside.bDisable--;
}
for(i=sqliteHashFirst(&db->aDb[iDb].pSchema->idxHash);i;i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
sqlite3_free(pIdx->aiRowEst);
pIdx->aiRowEst = 0;
}
#endif
if( rc==SQLITE_NOMEM ){
|
> > | > > > > | > > > > < > | | | |
1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 .... 1836 1837 1838 1839 1840 1841 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 .... 1872 1873 1874 1875 1876 1877 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 |
pIndex->aiRowEst = (tRowcnt*)sqlite3MallocZero(sizeof(tRowcnt) * nCol);
if( pIndex->aiRowEst==0 ) sqlite3OomFault(pInfo->db);
}
aiRowEst = pIndex->aiRowEst;
#endif
pIndex->bUnordered = 0;
decodeIntArray((char*)z, nCol, aiRowEst, pIndex->aiRowLogEst, pIndex);
pIndex->hasStat1 = 1;
if( pIndex->pPartIdxWhere==0 ){
pTable->nRowLogEst = pIndex->aiRowLogEst[0];
pTable->tabFlags |= TF_HasStat1;
}
}else{
Index fakeIdx;
fakeIdx.szIdxRow = pTable->szTabRow;
#ifdef SQLITE_ENABLE_COSTMULT
fakeIdx.pTable = pTable;
#endif
decodeIntArray((char*)z, 1, 0, &pTable->nRowLogEst, &fakeIdx);
pTable->szTabRow = fakeIdx.szIdxRow;
pTable->tabFlags |= TF_HasStat1;
}
return 0;
}
/*
** If the Index.aSample variable is not NULL, delete the aSample[] array
................................................................................
** code may be ignored.
*/
int sqlite3AnalysisLoad(sqlite3 *db, int iDb){
analysisInfo sInfo;
HashElem *i;
char *zSql;
int rc = SQLITE_OK;
Schema *pSchema = db->aDb[iDb].pSchema;
assert( iDb>=0 && iDb<db->nDb );
assert( db->aDb[iDb].pBt!=0 );
/* Clear any prior statistics */
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
for(i=sqliteHashFirst(&pSchema->tblHash); i; i=sqliteHashNext(i)){
Table *pTab = sqliteHashData(i);
pTab->tabFlags &= ~TF_HasStat1;
}
for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
pIdx->hasStat1 = 0;
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
sqlite3DeleteIndexSamples(db, pIdx);
pIdx->aSample = 0;
#endif
}
/* Load new statistics out of the sqlite_stat1 table */
................................................................................
rc = sqlite3_exec(db, zSql, analysisLoader, &sInfo, 0);
sqlite3DbFree(db, zSql);
}
}
/* Set appropriate defaults on all indexes not in the sqlite_stat1 table */
assert( sqlite3SchemaMutexHeld(db, iDb, 0) );
for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
if( !pIdx->hasStat1 ) sqlite3DefaultRowEst(pIdx);
}
/* Load the statistics from the sqlite_stat4 table. */
#ifdef SQLITE_ENABLE_STAT3_OR_STAT4
if( rc==SQLITE_OK && OptimizationEnabled(db, SQLITE_Stat34) ){
db->lookaside.bDisable++;
rc = loadStat4(db, sInfo.zDatabase);
db->lookaside.bDisable--;
}
for(i=sqliteHashFirst(&pSchema->idxHash); i; i=sqliteHashNext(i)){
Index *pIdx = sqliteHashData(i);
sqlite3_free(pIdx->aiRowEst);
pIdx->aiRowEst = 0;
}
#endif
if( rc==SQLITE_NOMEM ){
|
Changes to src/build.c.
3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 |
*/
void sqlite3DefaultRowEst(Index *pIdx){
/* 10, 9, 8, 7, 6 */
LogEst aVal[] = { 33, 32, 30, 28, 26 };
LogEst *a = pIdx->aiRowLogEst;
int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol);
int i;
/* Set the first entry (number of rows in the index) to the estimated
** number of rows in the table, or half the number of rows in the table
** for a partial index. But do not let the estimate drop below 10. */
a[0] = pIdx->pTable->nRowLogEst;
if( pIdx->pPartIdxWhere!=0 ) a[0] -= 10; assert( 10==sqlite3LogEst(2) );
if( a[0]<33 ) a[0] = 33; assert( 33==sqlite3LogEst(10) );
|
> > > |
3449 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 |
*/
void sqlite3DefaultRowEst(Index *pIdx){
/* 10, 9, 8, 7, 6 */
LogEst aVal[] = { 33, 32, 30, 28, 26 };
LogEst *a = pIdx->aiRowLogEst;
int nCopy = MIN(ArraySize(aVal), pIdx->nKeyCol);
int i;
/* Indexes with default row estimates should not have stat1 data */
assert( !pIdx->hasStat1 );
/* Set the first entry (number of rows in the index) to the estimated
** number of rows in the table, or half the number of rows in the table
** for a partial index. But do not let the estimate drop below 10. */
a[0] = pIdx->pTable->nRowLogEst;
if( pIdx->pPartIdxWhere!=0 ) a[0] -= 10; assert( 10==sqlite3LogEst(2) );
if( a[0]<33 ) a[0] = 33; assert( 33==sqlite3LogEst(10) );
|
Changes to src/pragma.c.
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 |
k);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
}
}
}
break;
case PragTyp_STATS: {
Index *pIdx;
HashElem *i;
pParse->nMem = 4;
sqlite3CodeVerifySchema(pParse, iDb);
for(i=sqliteHashFirst(&pDb->pSchema->tblHash); i; i=sqliteHashNext(i)){
Table *pTab = sqliteHashData(i);
sqlite3VdbeMultiLoad(v, 1, "ssii",
pTab->zName,
0,
pTab->szTabRow,
pTab->nRowLogEst);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 4);
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
sqlite3VdbeMultiLoad(v, 2, "sii",
pIdx->zName,
pIdx->szIdxRow,
pIdx->aiRowLogEst[0]);
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;
|
> | | | > | | | > | > |
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 |
k);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6);
}
}
}
break;
#ifdef SQLITE_DEBUG
case PragTyp_STATS: {
Index *pIdx;
HashElem *i;
pParse->nMem = 5;
sqlite3CodeVerifySchema(pParse, iDb);
for(i=sqliteHashFirst(&pDb->pSchema->tblHash); i; i=sqliteHashNext(i)){
Table *pTab = sqliteHashData(i);
sqlite3VdbeMultiLoad(v, 1, "ssiii",
pTab->zName,
0,
pTab->szTabRow,
pTab->nRowLogEst,
pTab->tabFlags);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5);
for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
sqlite3VdbeMultiLoad(v, 2, "siii",
pIdx->zName,
pIdx->szIdxRow,
pIdx->aiRowLogEst[0],
pIdx->hasStat1);
sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 5);
}
}
}
break;
#endif
case PragTyp_INDEX_INFO: if( zRight ){
Index *pIdx;
Table *pTab;
pIdx = sqlite3FindIndex(db, zRight, zDb);
if( pIdx ){
int i;
|
Changes to src/pragma.h.
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 57 .. 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 110 111 112 113 114 115 116 117 118 119 120 121 ... 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 ... 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 ... 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 ... 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 ... 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 ... 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 ... 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 ... 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 |
#define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 #define PragTyp_PAGE_SIZE 25 #define PragTyp_SECURE_DELETE 26 #define PragTyp_SHRINK_MEMORY 27 #define PragTyp_SOFT_HEAP_LIMIT 28 #define PragTyp_STATS 29 #define PragTyp_SYNCHRONOUS 30 #define PragTyp_TABLE_INFO 31 #define PragTyp_TEMP_STORE 32 #define PragTyp_TEMP_STORE_DIRECTORY 33 #define PragTyp_THREADS 34 #define PragTyp_WAL_AUTOCHECKPOINT 35 #define PragTyp_WAL_CHECKPOINT 36 #define PragTyp_ACTIVATE_EXTENSIONS 37 #define PragTyp_HEXKEY 38 #define PragTyp_KEY 39 #define PragTyp_REKEY 40 #define PragTyp_LOCK_STATUS 41 #define PragTyp_PARSER_TRACE 42 /* 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 */ ................................................................................ /* 4 */ "notnull", /* 5 */ "dflt_value", /* 6 */ "pk", /* 7 */ "table", /* Used by: stats */ /* 8 */ "index", /* 9 */ "width", /* 10 */ "height", /* 11 */ "seqno", /* Used by: index_info */ /* 12 */ "cid", /* 13 */ "name", /* 14 */ "seqno", /* Used by: index_xinfo */ /* 15 */ "cid", /* 16 */ "name", /* 17 */ "desc", /* 18 */ "coll", /* 19 */ "key", /* 20 */ "seq", /* Used by: index_list */ /* 21 */ "name", /* 22 */ "unique", /* 23 */ "origin", /* 24 */ "partial", /* 25 */ "seq", /* Used by: database_list */ /* 26 */ "name", /* 27 */ "file", /* 28 */ "seq", /* Used by: collation_list */ /* 29 */ "name", /* 30 */ "id", /* Used by: foreign_key_list */ /* 31 */ "seq", /* 32 */ "table", /* 33 */ "from", /* 34 */ "to", /* 35 */ "on_update", /* 36 */ "on_delete", /* 37 */ "match", /* 38 */ "table", /* Used by: foreign_key_check */ /* 39 */ "rowid", /* 40 */ "parent", /* 41 */ "fkid", /* 42 */ "busy", /* Used by: wal_checkpoint */ /* 43 */ "log", /* 44 */ "checkpointed", /* 45 */ "timeout", /* Used by: busy_timeout */ /* 46 */ "database", /* Used by: lock_status */ /* 47 */ "status", }; /* Definitions of all built-in pragmas */ typedef struct PragmaName { const char *const zName; /* Name of pragma */ u8 ePragTyp; /* PragTyp_XXX value */ u8 mPragFlg; /* Zero or more PragFlg_XXX values */ ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ SQLITE_AutoIndex }, #endif #endif {/* zName: */ "busy_timeout", /* ePragTyp: */ PragTyp_BUSY_TIMEOUT, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 45, 1, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "cache_size", /* ePragTyp: */ PragTyp_CACHE_SIZE, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ 0 }, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ SQLITE_CkptFullFSync }, #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 28, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_COMPILEOPTION_DIAGS) {/* zName: */ "compile_options", /* ePragTyp: */ PragTyp_COMPILE_OPTIONS, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 0, 0, ................................................................................ /* 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: */ 25, 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, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 38, 4, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) {/* zName: */ "foreign_key_list", /* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 30, 8, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_keys", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, ................................................................................ /* 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: */ 11, 3, /* iArg: */ 0 }, {/* zName: */ "index_list", /* ePragTyp: */ PragTyp_INDEX_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 20, 5, /* iArg: */ 0 }, {/* zName: */ "index_xinfo", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 14, 6, /* iArg: */ 1 }, #endif #if !defined(SQLITE_OMIT_INTEGRITY_CHECK) {/* zName: */ "integrity_check", /* ePragTyp: */ PragTyp_INTEGRITY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 0, 0, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) {/* zName: */ "lock_status", /* ePragTyp: */ PragTyp_LOCK_STATUS, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 46, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "locking_mode", /* ePragTyp: */ PragTyp_LOCKING_MODE, /* ePragFlg: */ PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 0, 0, ................................................................................ {/* zName: */ "sql_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_SqlTrace }, #endif #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "stats", /* ePragTyp: */ PragTyp_STATS, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 7, 4, /* 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, ................................................................................ /* ePragTyp: */ PragTyp_WAL_AUTOCHECKPOINT, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "wal_checkpoint", /* ePragTyp: */ PragTyp_WAL_CHECKPOINT, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 42, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; /* Number of pragmas: 60 on by default, 73 total. */ |
< | | | | | | | | | | | | | > > | < | > | < | | | | > | | | | | | | | | | | < | | | | | | > | | | | | < > | | | | | | | | | | | | | | | | | |
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 57 .. 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 110 111 112 113 114 115 116 117 118 119 120 121 122 ... 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 ... 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 ... 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 ... 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 ... 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 ... 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 ... 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 ... 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 |
#define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 #define PragTyp_PAGE_SIZE 25 #define PragTyp_SECURE_DELETE 26 #define PragTyp_SHRINK_MEMORY 27 #define PragTyp_SOFT_HEAP_LIMIT 28 #define PragTyp_SYNCHRONOUS 29 #define PragTyp_TABLE_INFO 30 #define PragTyp_TEMP_STORE 31 #define PragTyp_TEMP_STORE_DIRECTORY 32 #define PragTyp_THREADS 33 #define PragTyp_WAL_AUTOCHECKPOINT 34 #define PragTyp_WAL_CHECKPOINT 35 #define PragTyp_ACTIVATE_EXTENSIONS 36 #define PragTyp_HEXKEY 37 #define PragTyp_KEY 38 #define PragTyp_REKEY 39 #define PragTyp_LOCK_STATUS 40 #define PragTyp_PARSER_TRACE 41 #define PragTyp_STATS 42 /* 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 */ ................................................................................ /* 4 */ "notnull", /* 5 */ "dflt_value", /* 6 */ "pk", /* 7 */ "table", /* Used by: stats */ /* 8 */ "index", /* 9 */ "width", /* 10 */ "height", /* 11 */ "flags", /* 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 */ "seq", /* Used by: collation_list */ /* 30 */ "name", /* 31 */ "id", /* Used by: foreign_key_list */ /* 32 */ "seq", /* 33 */ "table", /* 34 */ "from", /* 35 */ "to", /* 36 */ "on_update", /* 37 */ "on_delete", /* 38 */ "match", /* 39 */ "table", /* Used by: foreign_key_check */ /* 40 */ "rowid", /* 41 */ "parent", /* 42 */ "fkid", /* 43 */ "busy", /* Used by: wal_checkpoint */ /* 44 */ "log", /* 45 */ "checkpointed", /* 46 */ "timeout", /* Used by: busy_timeout */ /* 47 */ "database", /* Used by: lock_status */ /* 48 */ "status", }; /* Definitions of all built-in pragmas */ typedef struct PragmaName { const char *const zName; /* Name of pragma */ u8 ePragTyp; /* PragTyp_XXX value */ u8 mPragFlg; /* Zero or more PragFlg_XXX values */ ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ SQLITE_AutoIndex }, #endif #endif {/* zName: */ "busy_timeout", /* ePragTyp: */ PragTyp_BUSY_TIMEOUT, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 46, 1, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "cache_size", /* ePragTyp: */ PragTyp_CACHE_SIZE, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ 0 }, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ SQLITE_CkptFullFSync }, #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 29, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_COMPILEOPTION_DIAGS) {/* zName: */ "compile_options", /* ePragTyp: */ PragTyp_COMPILE_OPTIONS, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 0, 0, ................................................................................ /* 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, /* 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, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_key_check", /* ePragTyp: */ PragTyp_FOREIGN_KEY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 39, 4, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FOREIGN_KEY) {/* zName: */ "foreign_key_list", /* ePragTyp: */ PragTyp_FOREIGN_KEY_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 31, 8, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) #if !defined(SQLITE_OMIT_FOREIGN_KEY) && !defined(SQLITE_OMIT_TRIGGER) {/* zName: */ "foreign_keys", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, ................................................................................ /* 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, /* iArg: */ 0 }, {/* zName: */ "index_list", /* ePragTyp: */ PragTyp_INDEX_LIST, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 21, 5, /* iArg: */ 0 }, {/* zName: */ "index_xinfo", /* ePragTyp: */ PragTyp_INDEX_INFO, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result1|PragFlg_SchemaOpt, /* ColNames: */ 15, 6, /* iArg: */ 1 }, #endif #if !defined(SQLITE_OMIT_INTEGRITY_CHECK) {/* zName: */ "integrity_check", /* ePragTyp: */ PragTyp_INTEGRITY_CHECK, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 0, 0, ................................................................................ /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) {/* zName: */ "lock_status", /* ePragTyp: */ PragTyp_LOCK_STATUS, /* ePragFlg: */ PragFlg_Result0, /* ColNames: */ 47, 2, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "locking_mode", /* ePragTyp: */ PragTyp_LOCKING_MODE, /* ePragFlg: */ PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 0, 0, ................................................................................ {/* zName: */ "sql_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* 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, /* 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, ................................................................................ /* ePragTyp: */ PragTyp_WAL_AUTOCHECKPOINT, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "wal_checkpoint", /* ePragTyp: */ PragTyp_WAL_CHECKPOINT, /* ePragFlg: */ PragFlg_NeedSchema, /* ColNames: */ 43, 3, /* iArg: */ 0 }, #endif #if !defined(SQLITE_OMIT_FLAG_PRAGMAS) {/* zName: */ "writable_schema", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_WriteSchema|SQLITE_RecoveryMode }, #endif }; /* Number of pragmas: 59 on by default, 73 total. */ |
Changes to src/sqliteInt.h.
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
1897
....
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
|
** ** TF_OOOHidden applies to tables or view that have hidden columns that are ** followed by non-hidden columns. Example: "CREATE VIRTUAL TABLE x USING ** vtab1(a HIDDEN, b);". Since "b" is a non-hidden column but "a" is hidden, ** the TF_OOOHidden attribute would apply in this case. Such tables require ** special handling during INSERT processing. */ #define TF_Readonly 0x01 /* Read-only system table */ #define TF_Ephemeral 0x02 /* An ephemeral table */ #define TF_HasPrimaryKey 0x04 /* Table has a primary key */ #define TF_Autoincrement 0x08 /* Integer primary key is autoincrement */ /* available for reuse: 0x10 */ #define TF_WithoutRowid 0x20 /* No rowid. PRIMARY KEY is the key */ #define TF_NoVisibleRowid 0x40 /* No user-visible "rowid" column */ #define TF_OOOHidden 0x80 /* Out-of-Order hidden columns */ /* ** Test to see whether or not a table is a virtual table. This is ** done as a macro so that it will be optimized out when virtual ** table support is omitted from the build. */ #ifndef SQLITE_OMIT_VIRTUALTABLE ................................................................................ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned idxType:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ tRowcnt *aiRowEst; /* Non-logarithmic stat1 data for this index */ tRowcnt nRowEst0; /* Non-logarithmic number of rows in the index */ |
|
|
|
|
|
|
|
|
<
>
|
1875
1876
1877
1878
1879
1880
1881
1882
1883
1884
1885
1886
1887
1888
1889
1890
1891
1892
1893
1894
1895
1896
....
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
2137
2138
2139
|
** ** TF_OOOHidden applies to tables or view that have hidden columns that are ** followed by non-hidden columns. Example: "CREATE VIRTUAL TABLE x USING ** vtab1(a HIDDEN, b);". Since "b" is a non-hidden column but "a" is hidden, ** the TF_OOOHidden attribute would apply in this case. Such tables require ** special handling during INSERT processing. */ #define TF_Readonly 0x0001 /* Read-only system table */ #define TF_Ephemeral 0x0002 /* An ephemeral table */ #define TF_HasPrimaryKey 0x0004 /* Table has a primary key */ #define TF_Autoincrement 0x0008 /* Integer primary key is autoincrement */ #define TF_HasStat1 0x0010 /* Has STAT1 data */ #define TF_WithoutRowid 0x0020 /* No rowid. PRIMARY KEY is the key */ #define TF_NoVisibleRowid 0x0040 /* No user-visible "rowid" column */ #define TF_OOOHidden 0x0080 /* Out-of-Order hidden columns */ /* ** Test to see whether or not a table is a virtual table. This is ** done as a macro so that it will be optimized out when virtual ** table support is omitted from the build. */ #ifndef SQLITE_OMIT_VIRTUALTABLE ................................................................................ u8 onError; /* OE_Abort, OE_Ignore, OE_Replace, or OE_None */ unsigned idxType:2; /* 1==UNIQUE, 2==PRIMARY KEY, 0==CREATE INDEX */ unsigned bUnordered:1; /* Use this index for == or IN queries only */ unsigned uniqNotNull:1; /* True if UNIQUE and NOT NULL for all columns */ unsigned isResized:1; /* True if resizeIndexObject() has been called */ unsigned isCovering:1; /* True if this is a covering index */ unsigned noSkipScan:1; /* Do not try to use skip-scan if true */ unsigned hasStat1:1; /* True if stat1 data has been loaded */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 int nSample; /* Number of elements in aSample[] */ int nSampleCol; /* Size of IndexSample.anEq[] and so on */ tRowcnt *aAvgEq; /* Average nEq values for keys not in aSample */ IndexSample *aSample; /* Samples of the left-most key */ tRowcnt *aiRowEst; /* Non-logarithmic stat1 data for this index */ tRowcnt nRowEst0; /* Non-logarithmic number of rows in the index */ |
Changes to tool/mkpragmatab.tcl.
221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 |
NAME: table_info FLAG: NeedSchema Result1 SchemaOpt COLS: cid name type notnull dflt_value pk IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) NAME: stats FLAG: NeedSchema Result0 SchemaReq COLS: table index width height IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) NAME: index_info TYPE: INDEX_INFO ARG: 0 FLAG: NeedSchema Result1 SchemaOpt COLS: seqno cid name IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) |
| | |
221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 |
NAME: table_info FLAG: NeedSchema Result1 SchemaOpt COLS: cid name type notnull dflt_value pk IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) NAME: stats FLAG: NeedSchema Result0 SchemaReq COLS: table index width height flags IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) && defined(SQLITE_DEBUG) NAME: index_info TYPE: INDEX_INFO ARG: 0 FLAG: NeedSchema Result1 SchemaOpt COLS: seqno cid name IF: !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) |