Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch auto-analyze Excluding Merge-Ins
This is equivalent to a diff from 45797feefe to 5f7fc79aa0
2017-03-06
| ||
17:33 | Add an initial implementation of the "PRAGMA optimize" command. (check-in: 137aeb2b16 user: drh tags: trunk) | |
11:39 | Merge updates from trunk. (Closed-Leaf check-in: 5f7fc79aa0 user: drh tags: auto-analyze) | |
2017-03-03
| ||
21:51 | If a reprepare is needed after binding to a variable with a number larger than 32, set only the high-order bit of the Vdbe.expmask rather than setting all bits. This could potentially result in fewer false-positive reprepares. (check-in: 45797feefe user: drh tags: trunk) | |
21:36 | Remove an redundant function call from the date/time function implementation. (check-in: 4a04c48a31 user: drh tags: trunk) | |
2017-03-02
| ||
14:17 | Add an optional bitmask of allowed optimizations on the "PRAGMA optimize" command. The 0x01 bit is Debug Mode. (check-in: a35388eef4 user: drh tags: auto-analyze) | |
Changes to src/analyze.c.
︙ | ︙ | |||
1384 1385 1386 1387 1388 1389 1390 | assert( pName2!=0 || pName1==0 ); if( pName1==0 ){ /* Form 1: Analyze everything */ for(i=0; i<db->nDb; i++){ if( i==1 ) continue; /* Do not analyze the TEMP database */ analyzeDatabase(pParse, i); } | | | < < | | < < < < < < < < < < < | | | | | > | 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 | assert( pName2!=0 || pName1==0 ); if( pName1==0 ){ /* Form 1: Analyze everything */ for(i=0; i<db->nDb; i++){ if( i==1 ) continue; /* Do not analyze the TEMP database */ analyzeDatabase(pParse, i); } }else if( pName2->n==0 && (iDb = sqlite3FindDb(db, pName1))>=0 ){ /* Analyze the schema named as the argument */ analyzeDatabase(pParse, iDb); }else{ /* Form 3: Analyze the table or index named as an argument */ iDb = sqlite3TwoPartName(pParse, pName1, pName2, &pTableName); if( iDb>=0 ){ zDb = pName2->n ? db->aDb[iDb].zDbSName : 0; z = sqlite3NameFromToken(db, pTableName); if( z ){ if( (pIdx = sqlite3FindIndex(db, z, zDb))!=0 ){ analyzeTable(pParse, pIdx->pTable, pIdx); }else if( (pTab = sqlite3LocateTable(pParse, 0, z, zDb))!=0 ){ analyzeTable(pParse, pTab, 0); } sqlite3DbFree(db, z); } } } if( db->nSqlExec==0 && (v = sqlite3GetVdbe(pParse))!=0 ){ sqlite3VdbeAddOp0(v, OP_Expire); } } /* ** Used to pass information from the analyzer reader through to the ** callback routine. */ typedef struct analysisInfo analysisInfo; |
︙ | ︙ | |||
1546 1547 1548 1549 1550 1551 1552 | 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); | > | > > > > | 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 | 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 |
︙ | ︙ | |||
1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 | ** 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) ); | > | > > > > | | 1842 1843 1844 1845 1846 1847 1848 1849 1850 1851 1852 1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 1867 1868 1869 | ** 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 */ |
︙ | ︙ | |||
1880 1881 1882 1883 1884 1885 1886 | 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) ); | | | | | 1878 1879 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 | 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/btree.c.
︙ | ︙ | |||
5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 | int sqlite3BtreeEof(BtCursor *pCur){ /* TODO: What if the cursor is in CURSOR_REQUIRESEEK but all table entries ** have been deleted? This API will need to change to return an error code ** as well as the boolean result value. */ return (CURSOR_VALID!=pCur->eState); } /* ** Advance the cursor to the next entry in the database. If ** successful then set *pRes=0. If the cursor ** was already pointing to the last entry in the database before ** this routine was called, then set *pRes=1. ** | > > > > > > > > > > > > > > > > > > > > > > > > | 5363 5364 5365 5366 5367 5368 5369 5370 5371 5372 5373 5374 5375 5376 5377 5378 5379 5380 5381 5382 5383 5384 5385 5386 5387 5388 5389 5390 5391 5392 5393 5394 5395 5396 5397 5398 5399 5400 | int sqlite3BtreeEof(BtCursor *pCur){ /* TODO: What if the cursor is in CURSOR_REQUIRESEEK but all table entries ** have been deleted? This API will need to change to return an error code ** as well as the boolean result value. */ return (CURSOR_VALID!=pCur->eState); } /* ** Return an estimate for the number of rows in the table that pCur is ** pointing to. Return a negative number if no estimate is currently ** available. */ i64 sqlite3BtreeRowCountEst(BtCursor *pCur){ i64 n; u8 i; assert( cursorOwnsBtShared(pCur) ); assert( sqlite3_mutex_held(pCur->pBtree->db->mutex) ); /* Currently this interface is only called by the OP_IfSmaller ** opcode, and it that case the cursor will always be valid and ** will always point to a leaf node. */ if( NEVER(pCur->eState!=CURSOR_VALID) ) return -1; if( NEVER(pCur->apPage[pCur->iPage]->leaf==0) ) return -1; for(n=1, i=0; i<=pCur->iPage; i++){ n *= pCur->apPage[i]->nCell; } return n; } /* ** Advance the cursor to the next entry in the database. If ** successful then set *pRes=0. If the cursor ** was already pointing to the last entry in the database before ** this routine was called, then set *pRes=1. ** |
︙ | ︙ |
Changes to src/btree.h.
︙ | ︙ | |||
292 293 294 295 296 297 298 299 300 301 302 303 304 305 | i64 sqlite3BtreeIntegerKey(BtCursor*); int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*); const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt); u32 sqlite3BtreePayloadSize(BtCursor*); char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*); struct Pager *sqlite3BtreePager(Btree*); #ifndef SQLITE_OMIT_INCRBLOB int sqlite3BtreePayloadChecked(BtCursor*, u32 offset, u32 amt, void*); int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*); void sqlite3BtreeIncrblobCursor(BtCursor *); #endif void sqlite3BtreeClearCursor(BtCursor *); | > | 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 | i64 sqlite3BtreeIntegerKey(BtCursor*); int sqlite3BtreePayload(BtCursor*, u32 offset, u32 amt, void*); const void *sqlite3BtreePayloadFetch(BtCursor*, u32 *pAmt); u32 sqlite3BtreePayloadSize(BtCursor*); char *sqlite3BtreeIntegrityCheck(Btree*, int *aRoot, int nRoot, int, int*); struct Pager *sqlite3BtreePager(Btree*); i64 sqlite3BtreeRowCountEst(BtCursor*); #ifndef SQLITE_OMIT_INCRBLOB int sqlite3BtreePayloadChecked(BtCursor*, u32 offset, u32 amt, void*); int sqlite3BtreePutData(BtCursor*, u32 offset, u32 amt, void*); void sqlite3BtreeIncrblobCursor(BtCursor *); #endif void sqlite3BtreeClearCursor(BtCursor *); |
︙ | ︙ |
Changes to src/build.c.
︙ | ︙ | |||
3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 | */ 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) ); | > > > | 3450 3451 3452 3453 3454 3455 3456 3457 3458 3459 3460 3461 3462 3463 3464 3465 3466 | */ 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.
︙ | ︙ | |||
1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 | k); sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 6); } } } break; case PragTyp_STATS: { Index *pIdx; HashElem *i; | > | | | > | | | > | > | 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 1143 1144 1145 1146 1147 1148 1149 1150 | 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; |
︙ | ︙ | |||
1853 1854 1855 1856 1857 1858 1859 1860 1861 1862 1863 1864 1865 1866 | ** connection on which it is invoked to free up as much memory as it ** can, by calling sqlite3_db_release_memory(). */ case PragTyp_SHRINK_MEMORY: { sqlite3_db_release_memory(db); break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** ** 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 | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1857 1858 1859 1860 1861 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 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 1901 1902 1903 1904 1905 1906 1907 1908 1909 1910 1911 1912 1913 1914 1915 1916 1917 1918 1919 1920 1921 1922 1923 1924 1925 1926 1927 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 | ** connection on which it is invoked to free up as much memory as it ** can, by calling sqlite3_db_release_memory(). */ case PragTyp_SHRINK_MEMORY: { sqlite3_db_release_memory(db); break; } /* ** PRAGMA optimize ** PRAGMA optimize(MASK) ** PRAGMA schema.optimize ** PRAGMA schema.optimize(MASK) ** ** Attempt to optimize the database. All schemas are optimized in the first ** two forms, and only the specified schema is optimized in the latter two. ** ** The details of optimizations performed by this pragma does are expected ** to change and improve over time. Applications should anticipate that ** this pragma will perform new optimizations in future releases. ** ** The optional argument is a bitmask of optimizations to perform: ** ** 0x0001 Debugging mode. Do not actually perform any optimizations ** but instead return one line of text for each optimization ** that would have been done. Off by default. ** ** 0x0002 Run ANALYZE on tables that might benefit. On by default. ** See below for additional information. ** ** 0x0004 (Not yet implemented) Record usage and performance ** information from the current session in the ** database file so that it will be available to "optimize" ** pragmas run by future database connections. ** ** 0x0008 (Not yet implemented) Create indexes that might have ** been helpful to recent queries ** ** The default MASK is 0x000e, which means perform all of the optimizations ** listed above except do not set Debug Mode. New optimizations may be ** added in future releases but they will be turned off by default. The ** default MASK will always be 0x0e. ** ** DETERMINATION OF WHEN TO RUN ANALYZE ** ** In the current implementation, a table is analyzed if only if all of ** the following are true: ** ** (1) MASK bit 0x02 is set. ** ** (2) The query planner used sqlite_stat1-style statistics for one or ** more indexes of the table at some point during the lifetime of ** the current connection. ** ** (3) One or more indexes of the table are currently unanalyzed OR ** the number of rows in the table has increased by 25 times or more ** since the last time ANALYZE was run. ** ** The rules for when tables are analyzed are likely to change in ** future releases. */ case PragTyp_OPTIMIZE: { int iDbLast; /* Loop termination point for the schema loop */ int iTabCur; /* Cursor for a table whose size needs checking */ HashElem *k; /* Loop over tables of a schema */ Schema *pSchema; /* The current schema */ Table *pTab; /* A table in the schema */ Index *pIdx; /* An index of the table */ LogEst szThreshold; /* Size threshold above which reanalysis is needd */ char *zSubSql; /* SQL statement for the OP_SqlExec opcode */ u32 opMask; /* Mask of operations to perform */ if( zRight ){ opMask = (u32)sqlite3Atoi(zRight); if( (opMask & 0x02)==0 ) break; }else{ opMask = 0xe; } iTabCur = pParse->nTab++; for(iDbLast = zDb?iDb:db->nDb-1; iDb<=iDbLast; iDb++){ if( iDb==1 ) continue; sqlite3CodeVerifySchema(pParse, iDb); pSchema = db->aDb[iDb].pSchema; for(k=sqliteHashFirst(&pSchema->tblHash); k; k=sqliteHashNext(k)){ pTab = (Table*)sqliteHashData(k); /* If table pTab has not been used in a way that would benefit from ** having analysis statistics during the current session, then skip it. ** This also has the effect of skipping virtual tables and views */ if( (pTab->tabFlags & TF_StatsUsed)==0 ) continue; /* Reanalyze if the table is 25 times larger than the last analysis */ szThreshold = pTab->nRowLogEst + 46; assert( sqlite3LogEst(25)==46 ); for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){ if( !pIdx->hasStat1 ){ szThreshold = 0; /* Always analyze if any index lacks statistics */ break; } } if( szThreshold ){ sqlite3OpenTable(pParse, iTabCur, iDb, pTab, OP_OpenRead); sqlite3VdbeAddOp3(v, OP_IfSmaller, iTabCur, sqlite3VdbeCurrentAddr(v)+2+(opMask&1), szThreshold); VdbeCoverage(v); } zSubSql = sqlite3MPrintf(db, "ANALYZE \"%w\".\"%w\"", db->aDb[iDb].zDbSName, pTab->zName); if( opMask & 0x01 ){ int r1 = sqlite3GetTempReg(pParse); sqlite3VdbeAddOp4(v, OP_String8, 0, r1, 0, zSubSql, P4_DYNAMIC); sqlite3VdbeAddOp2(v, OP_ResultRow, r1, 1); }else{ sqlite3VdbeAddOp4(v, OP_SqlExec, 0, 0, 0, zSubSql, P4_DYNAMIC); } } } sqlite3VdbeAddOp0(v, OP_Expire); break; } /* ** PRAGMA busy_timeout ** PRAGMA busy_timeout = N ** ** 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 |
︙ | ︙ |
Changes to src/pragma.h.
︙ | ︙ | |||
26 27 28 29 30 31 32 | #define PragTyp_INTEGRITY_CHECK 18 #define PragTyp_JOURNAL_MODE 19 #define PragTyp_JOURNAL_SIZE_LIMIT 20 #define PragTyp_LOCK_PROXY_FILE 21 #define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 | > | | | | < > | 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 57 58 | #define PragTyp_INTEGRITY_CHECK 18 #define PragTyp_JOURNAL_MODE 19 #define PragTyp_JOURNAL_SIZE_LIMIT 20 #define PragTyp_LOCK_PROXY_FILE 21 #define PragTyp_LOCKING_MODE 22 #define PragTyp_PAGE_COUNT 23 #define PragTyp_MMAP_SIZE 24 #define PragTyp_OPTIMIZE 25 #define PragTyp_PAGE_SIZE 26 #define PragTyp_SECURE_DELETE 27 #define PragTyp_SHRINK_MEMORY 28 #define PragTyp_SOFT_HEAP_LIMIT 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 #define PragTyp_STATS 43 /* 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 */ |
︙ | ︙ | |||
67 68 69 70 71 72 73 | /* 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", | | | | | | | | | | | | | | | | | | | | | | > | | | < | | | | | | | | | | > | | | | | | | 68 69 70 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 123 | /* 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 */ "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 */ "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 */ |
︙ | ︙ | |||
153 154 155 156 157 158 159 | /* ColNames: */ 0, 0, /* iArg: */ SQLITE_AutoIndex }, #endif #endif {/* zName: */ "busy_timeout", /* ePragTyp: */ PragTyp_BUSY_TIMEOUT, /* ePragFlg: */ PragFlg_Result0, | | | 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 | /* 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 }, |
︙ | ︙ | |||
190 191 192 193 194 195 196 | /* ColNames: */ 0, 0, /* iArg: */ SQLITE_CkptFullFSync }, #endif #if !defined(SQLITE_OMIT_SCHEMA_PRAGMAS) {/* zName: */ "collation_list", /* ePragTyp: */ PragTyp_COLLATION_LIST, /* ePragFlg: */ PragFlg_Result0, | | | 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 | /* 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, |
︙ | ︙ | |||
225 226 227 228 229 230 231 | /* 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, | | | 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 | /* 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, |
︙ | ︙ | |||
262 263 264 265 266 267 268 | /* 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, | | | | 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 | /* 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, |
︙ | ︙ | |||
332 333 334 335 336 337 338 | /* 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, | | | | | 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 | /* 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, |
︙ | ︙ | |||
389 390 391 392 393 394 395 | /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) {/* zName: */ "lock_status", /* ePragTyp: */ PragTyp_LOCK_STATUS, /* ePragFlg: */ PragFlg_Result0, | | > > > > > > > | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 | /* 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, /* iArg: */ 0 }, {/* zName: */ "max_page_count", /* ePragTyp: */ PragTyp_PAGE_COUNT, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "mmap_size", /* ePragTyp: */ PragTyp_MMAP_SIZE, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, #endif {/* zName: */ "optimize", /* ePragTyp: */ PragTyp_OPTIMIZE, /* ePragFlg: */ PragFlg_Result1, /* ColNames: */ 0, 0, /* iArg: */ 0 }, #if !defined(SQLITE_OMIT_PAGER_PRAGMAS) {/* zName: */ "page_count", /* ePragTyp: */ PragTyp_PAGE_COUNT, /* ePragFlg: */ PragFlg_NeedSchema|PragFlg_Result0|PragFlg_SchemaReq, /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "page_size", /* ePragTyp: */ PragTyp_PAGE_SIZE, |
︙ | ︙ | |||
506 507 508 509 510 511 512 | {/* zName: */ "sql_trace", /* ePragTyp: */ PragTyp_FLAG, /* ePragFlg: */ PragFlg_Result0|PragFlg_NoColumns1, /* ColNames: */ 0, 0, /* iArg: */ SQLITE_SqlTrace }, #endif #endif | | | | 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 | {/* 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, |
︙ | ︙ | |||
589 590 591 592 593 594 595 | /* ePragTyp: */ PragTyp_WAL_AUTOCHECKPOINT, /* ePragFlg: */ 0, /* ColNames: */ 0, 0, /* iArg: */ 0 }, {/* zName: */ "wal_checkpoint", /* ePragTyp: */ PragTyp_WAL_CHECKPOINT, /* ePragFlg: */ PragFlg_NeedSchema, | | | | 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 | /* 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: 60 on by default, 74 total. */ |
Changes to src/sqliteInt.h.
︙ | ︙ | |||
1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 | u8 dfltLockMode; /* Default locking-mode for attached dbs */ signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 skipBtreeMutex; /* True if no shared-cache backends */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ struct sqlite3InitInfo { /* Information used during initialization */ | > | 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 | u8 dfltLockMode; /* Default locking-mode for attached dbs */ signed char nextAutovac; /* Autovac setting after VACUUM if >=0 */ u8 suppressErr; /* Do not issue error messages if true */ u8 vtabOnConflict; /* Value to return for s3_vtab_on_conflict() */ u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */ u8 mTrace; /* zero or more SQLITE_TRACE flags */ u8 skipBtreeMutex; /* True if no shared-cache backends */ u8 nSqlExec; /* Number of pending OP_SqlExec opcodes */ int nextPagesize; /* Pagesize after VACUUM if >0 */ u32 magic; /* Magic number for detect library misuse */ int nChange; /* Value returned by sqlite3_changes() */ int nTotalChange; /* Value returned by sqlite3_total_changes() */ int aLimit[SQLITE_N_LIMIT]; /* Limits */ int nMaxSorterMmap; /* Maximum size of regions mapped by sorter */ struct sqlite3InitInfo { /* Information used during initialization */ |
︙ | ︙ | |||
1879 1880 1881 1882 1883 1884 1885 | ** 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 */ | | | | > | 1880 1881 1882 1883 1884 1885 1886 1887 1888 1889 1890 1891 1892 1893 1894 1895 1896 1897 1898 1899 1900 | ** 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 /* nRowLogEst set from sqlite_stat1 */ #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 */ #define TF_StatsUsed 0x0100 /* Query planner decisions affected by ** Index.aiRowLogEst[] values */ #define TF_HasNotNull 0x0200 /* Contains NOT NULL constraints */ /* ** 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 |
︙ | ︙ | |||
2127 2128 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 | 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 */ | > | 2129 2130 2131 2132 2133 2134 2135 2136 2137 2138 2139 2140 2141 2142 2143 | 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; /* aiRowLogEst values come from sqlite_stat1 */ #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 src/vdbe.c.
︙ | ︙ | |||
4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 | } }else{ assert( pOp->p2==0 ); } break; } /* Opcode: SorterSort P1 P2 * * * ** ** After all records have been inserted into the Sorter object ** identified by P1, invoke this opcode to actually do the sorting. ** Jump to P2 if there are no records to be sorted. ** | > > > > > > > > > > > > > > > > > > > > > > > > > > > | 4843 4844 4845 4846 4847 4848 4849 4850 4851 4852 4853 4854 4855 4856 4857 4858 4859 4860 4861 4862 4863 4864 4865 4866 4867 4868 4869 4870 4871 4872 4873 4874 4875 4876 4877 4878 4879 4880 4881 4882 4883 | } }else{ assert( pOp->p2==0 ); } break; } /* Opcode: IfSmaller P1 P2 P3 * * ** ** Estimate the number of rows in the table P1. Jump to P2 if that ** estimate is less than approximately 2**(0.1*P3). */ case OP_IfSmaller: { /* jump */ VdbeCursor *pC; BtCursor *pCrsr; int res; i64 sz; assert( pOp->p1>=0 && pOp->p1<p->nCursor ); pC = p->apCsr[pOp->p1]; assert( pC!=0 ); pCrsr = pC->uc.pCursor; assert( pCrsr ); rc = sqlite3BtreeFirst(pCrsr, &res); if( rc ) goto abort_due_to_error; if( res==0 ){ sz = sqlite3BtreeRowCountEst(pCrsr); if( ALWAYS(sz>=0) && sqlite3LogEst((u64)sz)<pOp->p3 ) res = 1; } VdbeBranchTaken(res!=0,2); if( res ) goto jump_to_p2; break; } /* Opcode: SorterSort P1 P2 * * * ** ** After all records have been inserted into the Sorter object ** identified by P1, invoke this opcode to actually do the sorting. ** Jump to P2 if there are no records to be sorted. ** |
︙ | ︙ | |||
5486 5487 5488 5489 5490 5491 5492 5493 5494 5495 5496 5497 5498 5499 | flags = BTREE_BLOBKEY; } rc = sqlite3BtreeCreateTable(pDb->pBt, &pgno, flags); if( rc ) goto abort_due_to_error; pOut->u.i = pgno; break; } /* Opcode: ParseSchema P1 * * P4 * ** ** Read and parse all entries from the SQLITE_MASTER table of database P1 ** that match the WHERE clause P4. ** ** This opcode invokes the parser to create a new virtual machine, | > > > > > > > > > > > > | 5513 5514 5515 5516 5517 5518 5519 5520 5521 5522 5523 5524 5525 5526 5527 5528 5529 5530 5531 5532 5533 5534 5535 5536 5537 5538 | flags = BTREE_BLOBKEY; } rc = sqlite3BtreeCreateTable(pDb->pBt, &pgno, flags); if( rc ) goto abort_due_to_error; pOut->u.i = pgno; break; } /* Opcode: SqlExec * * * P4 * ** ** Run the SQL statement or statements specified in the P4 string. */ case OP_SqlExec: { db->nSqlExec++; rc = sqlite3_exec(db, pOp->p4.z, 0, 0, 0); db->nSqlExec--; if( rc ) goto abort_due_to_error; break; } /* Opcode: ParseSchema P1 * * P4 * ** ** Read and parse all entries from the SQLITE_MASTER table of database P1 ** that match the WHERE clause P4. ** ** This opcode invokes the parser to create a new virtual machine, |
︙ | ︙ |
Changes to src/where.c.
︙ | ︙ | |||
2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 | && (eOp & (WO_IS|WO_ISNULL))!=0 ){ testcase( eOp & WO_IS ); testcase( eOp & WO_ISNULL ); continue; } pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->u.btree.nBtm = saved_nBtm; pNew->u.btree.nTop = saved_nTop; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; | > > > > > | 2383 2384 2385 2386 2387 2388 2389 2390 2391 2392 2393 2394 2395 2396 2397 2398 2399 2400 2401 | && (eOp & (WO_IS|WO_ISNULL))!=0 ){ testcase( eOp & WO_IS ); testcase( eOp & WO_ISNULL ); continue; } if( IsUniqueIndex(pProbe) && saved_nEq==pProbe->nKeyCol-1 ){ pBuilder->bldFlags |= SQLITE_BLDF_UNIQUE; }else{ pBuilder->bldFlags |= SQLITE_BLDF_INDEXED; } pNew->wsFlags = saved_wsFlags; pNew->u.btree.nEq = saved_nEq; pNew->u.btree.nBtm = saved_nBtm; pNew->u.btree.nTop = saved_nTop; pNew->nLTerm = saved_nLTerm; if( whereLoopResize(db, pNew, pNew->nLTerm+1) ) break; /* OOM */ pNew->aLTerm[pNew->nLTerm++] = pTerm; |
︙ | ︙ | |||
2930 2931 2932 2933 2934 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 | whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; } } rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); pBuilder->nRecValid = 0; pBuilder->pRec = 0; #endif /* If there was an INDEXED BY clause, then only that one index is | > > > > > > > > | 2935 2936 2937 2938 2939 2940 2941 2942 2943 2944 2945 2946 2947 2948 2949 2950 2951 2952 2953 2954 2955 2956 2957 | whereLoopOutputAdjust(pWC, pNew, rSize); rc = whereLoopInsert(pBuilder, pNew); pNew->nOut = rSize; if( rc ) break; } } pBuilder->bldFlags = 0; rc = whereLoopAddBtreeIndex(pBuilder, pSrc, pProbe, 0); if( pBuilder->bldFlags==SQLITE_BLDF_INDEXED ){ /* If a non-unique index is used, or if a prefix of the key for ** unique index is used (making the index functionally non-unique) ** then the sqlite_stat1 data becomes important for scoring the ** plan */ pTab->tabFlags |= TF_StatsUsed; } #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 sqlite3Stat4ProbeFree(pBuilder->pRec); pBuilder->nRecValid = 0; pBuilder->pRec = 0; #endif /* If there was an INDEXED BY clause, then only that one index is |
︙ | ︙ |
Changes to src/whereInt.h.
︙ | ︙ | |||
391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 | ExprList *pOrderBy; /* ORDER BY clause */ WhereLoop *pNew; /* Template WhereLoop */ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif }; /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed ** into the second half to give some continuity. ** | > > > > > | 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 | ExprList *pOrderBy; /* ORDER BY clause */ WhereLoop *pNew; /* Template WhereLoop */ WhereOrSet *pOrSet; /* Record best loops here, if not NULL */ #ifdef SQLITE_ENABLE_STAT3_OR_STAT4 UnpackedRecord *pRec; /* Probe for stat4 (if required) */ int nRecValid; /* Number of valid fields currently in pRec */ #endif unsigned int bldFlags; /* SQLITE_BLDF_* flags */ }; /* Allowed values for WhereLoopBuider.bldFlags */ #define SQLITE_BLDF_INDEXED 0x0001 /* An index is used */ #define SQLITE_BLDF_UNIQUE 0x0002 /* All keys of a UNIQUE index used */ /* ** The WHERE clause processing routine has two halves. The ** first part does the start of the WHERE loop and the second ** half does the tail of the WHERE loop. An instance of ** this structure is returned by the first half and passed ** into the second half to give some continuity. ** |
︙ | ︙ |
Added test/autoanalyze1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 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 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 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 123 | # 2017-02-17 # # 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 tests for the logic used to estimate when # running ANALYZE would be beneficial. # # Note that this test uses some hard-coded bitmask values from sqliteInt.h. # If any of the following constants changes: # # define TF_HasStat1 0x0010 # define TF_StatsUsed 0x0100 # # then some of the magic numbers in test results below might need to be # adjusted. # set testdir [file dirname $argv0] source $testdir/tester.tcl # There is nothing to test if ANALYZE is disable for this build. # These tests also use "PRAGMA stats" which are only enabled for # debugging builds. # ifcapable {!debug || !analyze} { finish_test return } do_execsql_test autoanalyze1-100 { -- Build up a test table with some indexes CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d); CREATE UNIQUE INDEX t1bc ON t1(b,c); CREATE INDEX t1d ON t1(d); WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100) INSERT INTO t1(a,b,c,d) SELECT x, x, x, x FROM c; -- Verify that the hasStat1 flag is clear on on indexes SELECT idx, flgs FROM pragma_stats WHERE idx IS NOT NULL ORDER BY idx; -- Verify that the TF_HasStat1 flag is clear on the table SELECT tbl, (flgs & 0x10)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {t1bc 0 t1d 0 t1 0} # No use of stat1 recorded so far do_execsql_test autoanalyze1-110 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} # Access using a unique index does not set the TF_StatsUsed flag. # do_execsql_test autoanalyze1-200 { SELECT * FROM t1 WHERE a=55; } {55 55 55 55} do_execsql_test autoanalyze1-201 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} do_execsql_test autoanalyze1-210 { SELECT * FROM t1 WHERE a IN (55,199,299); } {55 55 55 55} do_execsql_test autoanalyze1-211 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} do_execsql_test autoanalyze1-220 { SELECT * FROM t1 WHERE (b,c)=(45,45); } {45 45 45 45} do_execsql_test autoanalyze1-221 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} # Any use of the non-unique t1d index triggers the use of stats. # sqlite3 db test.db do_execsql_test autoanalyze1-300 { SELECT * FROM t1 WHERE d=45; } {45 45 45 45} do_execsql_test autoanalyze1-301 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {1} sqlite3 db test.db do_execsql_test autoanalyze1-310 { SELECT * FROM t1 WHERE d=45 AND a=45; } {45 45 45 45} do_execsql_test autoanalyze1-311 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} ;# The ROWID lookup short-circuits the d=45 constraint sqlite3 db test.db do_execsql_test autoanalyze1-320 { SELECT * FROM t1 WHERE d=45 AND a IN (45,46); } {45 45 45 45} do_execsql_test autoanalyze1-321 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {1} # Any use of prefix of a unique index triggers the use of stats # sqlite3 db test.db do_execsql_test autoanalyze1-400 { SELECT * FROM t1 WHERE b=45; } {45 45 45 45} do_execsql_test autoanalyze1-401 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {1} # The TF_StatsUsed flag is reset when the database is reopened # sqlite3 db test.db do_execsql_test autoanalyze1-500 { SELECT (flgs & 0x0100)!=0 FROM pragma_stats WHERE tbl='t1' AND idx IS NULL; } {0} finish_test |
Changes to tool/mkpragmatab.tcl.
︙ | ︙ | |||
221 222 223 224 225 226 227 | 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 | | | | 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: tbl idx wdth hght flgs 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) |
︙ | ︙ | |||
357 358 359 360 361 362 363 364 365 366 367 368 369 370 | IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit FLAG: Result0 NAME: threads FLAG: Result0 } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h" puts "Overwriting $destfile with new pragma table..." set fd [open $destfile wb] | > > > | 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 | IF: defined(SQLITE_HAS_CODEC) || defined(SQLITE_ENABLE_CEROD) NAME: soft_heap_limit FLAG: Result0 NAME: threads FLAG: Result0 NAME: optimize FLAG: Result1 } # Open the output file # set destfile "[file dir [file dir [file normal $argv0]]]/src/pragma.h" puts "Overwriting $destfile with new pragma table..." set fd [open $destfile wb] |
︙ | ︙ |