/ Check-in [5021dfe1]
Login

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

Overview
Comment:Merge the latest changes from trunk.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | scrub-backup
Files: files | file ages | folders
SHA1: 5021dfe1f3f723a5938d547a0308f1d63103702d
User & Date: drh 2016-05-17 17:11:21
Context
2016-05-17
21:17
Enhance the scrub utility program so that it does a FULL checkpoint prior to starting the backup, to ensure that the database file content matches what needs to be backed up without having to look at the WAL file. Closed-Leaf check-in: ab1c5ce5 user: drh tags: scrub-backup
17:11
Merge the latest changes from trunk. check-in: 5021dfe1 user: drh tags: scrub-backup
2016-05-16
14:35
Do not run snapshot_fault.test as part of the inmemory_journal permutation. check-in: 995c084b user: dan tags: trunk
2016-05-05
23:59
Additional error reporting. Open the source database read/write so that it can delete the WAL file when done. check-in: d2efd3c1 user: drh tags: scrub-backup
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/fts5/fts5.h.

   139    139   ** xQueryPhrase(pFts5, iPhrase, pUserData, xCallback):
   140    140   **   This API function is used to query the FTS table for phrase iPhrase
   141    141   **   of the current query. Specifically, a query equivalent to:
   142    142   **
   143    143   **       ... FROM ftstable WHERE ftstable MATCH $p ORDER BY rowid
   144    144   **
   145    145   **   with $p set to a phrase equivalent to the phrase iPhrase of the
   146         -**   current query is executed. For each row visited, the callback function
   147         -**   passed as the fourth argument is invoked. The context and API objects 
   148         -**   passed to the callback function may be used to access the properties of
   149         -**   each matched row. Invoking Api.xUserData() returns a copy of the pointer
   150         -**   passed as the third argument to pUserData.
          146  +**   current query is executed. Any column filter that applies to
          147  +**   phrase iPhrase of the current query is included in $p. For each 
          148  +**   row visited, the callback function passed as the fourth argument 
          149  +**   is invoked. The context and API objects passed to the callback 
          150  +**   function may be used to access the properties of each matched row.
          151  +**   Invoking Api.xUserData() returns a copy of the pointer passed as 
          152  +**   the third argument to pUserData.
   151    153   **
   152    154   **   If the callback function returns any value other than SQLITE_OK, the
   153    155   **   query is abandoned and the xQueryPhrase function returns immediately.
   154    156   **   If the returned value is SQLITE_DONE, xQueryPhrase returns SQLITE_OK.
   155    157   **   Otherwise, the error code is propagated upwards.
   156    158   **
   157    159   **   If the query runs to completion without incident, SQLITE_OK is returned.

Changes to ext/fts5/fts5_expr.c.

  1650   1650     if( rc==SQLITE_OK ){
  1651   1651       pNew->pRoot = (Fts5ExprNode*)sqlite3Fts5MallocZero(&rc, 
  1652   1652           sizeof(Fts5ExprNode));
  1653   1653     }
  1654   1654     if( rc==SQLITE_OK ){
  1655   1655       pNew->pRoot->pNear = (Fts5ExprNearset*)sqlite3Fts5MallocZero(&rc, 
  1656   1656           sizeof(Fts5ExprNearset) + sizeof(Fts5ExprPhrase*));
         1657  +  }
         1658  +  if( rc==SQLITE_OK ){
         1659  +    Fts5Colset *pColsetOrig = pOrig->pNode->pNear->pColset;
         1660  +    if( pColsetOrig ){
         1661  +      int nByte = sizeof(Fts5Colset) + pColsetOrig->nCol * sizeof(int);
         1662  +      Fts5Colset *pColset = (Fts5Colset*)sqlite3Fts5MallocZero(&rc, nByte);
         1663  +      if( pColset ){ 
         1664  +        memcpy(pColset, pColsetOrig, nByte);
         1665  +      }
         1666  +      pNew->pRoot->pNear->pColset = pColset;
         1667  +    }
  1657   1668     }
  1658   1669   
  1659   1670     for(i=0; rc==SQLITE_OK && i<pOrig->nTerm; i++){
  1660   1671       int tflags = 0;
  1661   1672       Fts5ExprTerm *p;
  1662   1673       for(p=&pOrig->aTerm[i]; p && rc==SQLITE_OK; p=p->pSynonym){
  1663   1674         const char *zTerm = p->zTerm;

Changes to ext/fts5/test/fts5aux.test.

   241    241     execsql { DELETE FROM x1 }
   242    242     foreach row $lRow { execsql { INSERT INTO x1 VALUES($row) } }
   243    243     breakpoint
   244    244     do_execsql_test 8.$tn {
   245    245       SELECT highlight(x1, 0, '[', ']') FROM x1 WHERE x1 MATCH 'a OR (b AND d)';
   246    246     } $res
   247    247   }
          248  +
          249  +#-------------------------------------------------------------------------
          250  +# Test the built-in bm25() demo.
          251  +#
          252  +reset_db
          253  +do_execsql_test 9.1 {
          254  +  CREATE VIRTUAL TABLE t1 USING fts5(a, b);
          255  +  INSERT INTO t1 VALUES('a',   NULL);           -- 1
          256  +  INSERT INTO t1 VALUES('a',   NULL);           -- 2
          257  +  INSERT INTO t1 VALUES('a',   NULL);           -- 3
          258  +  INSERT INTO t1 VALUES('a',   NULL);           -- 4
          259  +  INSERT INTO t1 VALUES('a',   NULL);           -- 5
          260  +  INSERT INTO t1 VALUES('a',   NULL);           -- 6
          261  +  INSERT INTO t1 VALUES('a',   NULL);           -- 7
          262  +  INSERT INTO t1 VALUES('a',   NULL);           -- 8
          263  +  INSERT INTO t1 VALUES(NULL,  'a a b');        -- 9
          264  +  INSERT INTO t1 VALUES(NULL,  'b b a');        -- 10
          265  +}
          266  +
          267  +do_execsql_test 9.2 {
          268  +  SELECT rowid FROM t1('a AND b') ORDER BY rank;
          269  +} {
          270  +  10 9
          271  +}
          272  +
          273  +do_execsql_test 9.3 {
          274  +  SELECT rowid FROM t1('b:a AND b:b') ORDER BY rank;
          275  +} {
          276  +  9 10
          277  +}
          278  +
          279  +
   248    280   
   249    281   finish_test
   250    282   

Changes to main.mk.

   384    384     $(TOP)/ext/fts3/fts3.c \
   385    385     $(TOP)/ext/fts3/fts3_aux.c \
   386    386     $(TOP)/ext/fts3/fts3_expr.c \
   387    387     $(TOP)/ext/fts3/fts3_tokenizer.c \
   388    388     $(TOP)/ext/fts3/fts3_write.c \
   389    389     $(TOP)/ext/async/sqlite3async.c \
   390    390     $(TOP)/ext/session/sqlite3session.c \
   391         -  $(TOP)/ext/session/test_session.c \
   392         -  $(FTS5_SRC)
          391  +  $(TOP)/ext/session/test_session.c 
   393    392   
   394    393   # Header files used by all library source files.
   395    394   #
   396    395   HDR = \
   397    396      $(TOP)/src/btree.h \
   398    397      $(TOP)/src/btreeInt.h \
   399    398      $(TOP)/src/hash.h \

Changes to src/btree.c.

  1672   1672     assert( sqlite3_mutex_held(pPage->pBt->mutex) );
  1673   1673     pPage->leaf = (u8)(flagByte>>3);  assert( PTF_LEAF == 1<<3 );
  1674   1674     flagByte &= ~PTF_LEAF;
  1675   1675     pPage->childPtrSize = 4-4*pPage->leaf;
  1676   1676     pPage->xCellSize = cellSizePtr;
  1677   1677     pBt = pPage->pBt;
  1678   1678     if( flagByte==(PTF_LEAFDATA | PTF_INTKEY) ){
  1679         -    /* EVIDENCE-OF: R-03640-13415 A value of 5 means the page is an interior
  1680         -    ** table b-tree page. */
         1679  +    /* EVIDENCE-OF: R-07291-35328 A value of 5 (0x05) means the page is an
         1680  +    ** interior table b-tree page. */
  1681   1681       assert( (PTF_LEAFDATA|PTF_INTKEY)==5 );
  1682         -    /* EVIDENCE-OF: R-20501-61796 A value of 13 means the page is a leaf
  1683         -    ** table b-tree page. */
         1682  +    /* EVIDENCE-OF: R-26900-09176 A value of 13 (0x0d) means the page is a
         1683  +    ** leaf table b-tree page. */
  1684   1684       assert( (PTF_LEAFDATA|PTF_INTKEY|PTF_LEAF)==13 );
  1685   1685       pPage->intKey = 1;
  1686   1686       if( pPage->leaf ){
  1687   1687         pPage->intKeyLeaf = 1;
  1688   1688         pPage->xParseCell = btreeParseCellPtr;
  1689   1689       }else{
  1690   1690         pPage->intKeyLeaf = 0;
  1691   1691         pPage->xCellSize = cellSizePtrNoPayload;
  1692   1692         pPage->xParseCell = btreeParseCellPtrNoPayload;
  1693   1693       }
  1694   1694       pPage->maxLocal = pBt->maxLeaf;
  1695   1695       pPage->minLocal = pBt->minLeaf;
  1696   1696     }else if( flagByte==PTF_ZERODATA ){
  1697         -    /* EVIDENCE-OF: R-27225-53936 A value of 2 means the page is an interior
  1698         -    ** index b-tree page. */
         1697  +    /* EVIDENCE-OF: R-43316-37308 A value of 2 (0x02) means the page is an
         1698  +    ** interior index b-tree page. */
  1699   1699       assert( (PTF_ZERODATA)==2 );
  1700         -    /* EVIDENCE-OF: R-16571-11615 A value of 10 means the page is a leaf
  1701         -    ** index b-tree page. */
         1700  +    /* EVIDENCE-OF: R-59615-42828 A value of 10 (0x0a) means the page is a
         1701  +    ** leaf index b-tree page. */
  1702   1702       assert( (PTF_ZERODATA|PTF_LEAF)==10 );
  1703   1703       pPage->intKey = 0;
  1704   1704       pPage->intKeyLeaf = 0;
  1705   1705       pPage->xParseCell = btreeParseCellPtrIndex;
  1706   1706       pPage->maxLocal = pBt->maxLocal;
  1707   1707       pPage->minLocal = pBt->minLocal;
  1708   1708     }else{

Changes to src/build.c.

   588    588   **
   589    589   ** The db parameter is optional.  It is needed if the Table object 
   590    590   ** contains lookaside memory.  (Table objects in the schema do not use
   591    591   ** lookaside memory, but some ephemeral Table objects do.)  Or the
   592    592   ** db parameter can be used with db->pnBytesFreed to measure the memory
   593    593   ** used by the Table object.
   594    594   */
   595         -void sqlite3DeleteTable(sqlite3 *db, Table *pTable){
          595  +static void SQLITE_NOINLINE deleteTable(sqlite3 *db, Table *pTable){
   596    596     Index *pIndex, *pNext;
   597    597     TESTONLY( int nLookaside; ) /* Used to verify lookaside not used for schema */
   598    598   
   599         -  assert( !pTable || pTable->nRef>0 );
   600         -
   601         -  /* Do not delete the table until the reference count reaches zero. */
   602         -  if( !pTable ) return;
   603         -  if( ((!db || db->pnBytesFreed==0) && (--pTable->nRef)>0) ) return;
   604         -
   605    599     /* Record the number of outstanding lookaside allocations in schema Tables
   606    600     ** prior to doing any free() operations.  Since schema Tables do not use
   607    601     ** lookaside, this number should not change. */
   608    602     TESTONLY( nLookaside = (db && (pTable->tabFlags & TF_Ephemeral)==0) ?
   609    603                            db->lookaside.nOut : 0 );
   610    604   
   611    605     /* Delete all indices associated with this table. */
................................................................................
   637    631     sqlite3VtabClear(db, pTable);
   638    632   #endif
   639    633     sqlite3DbFree(db, pTable);
   640    634   
   641    635     /* Verify that no lookaside memory was used by schema tables */
   642    636     assert( nLookaside==0 || nLookaside==db->lookaside.nOut );
   643    637   }
          638  +void sqlite3DeleteTable(sqlite3 *db, Table *pTable){
          639  +  /* Do not delete the table until the reference count reaches zero. */
          640  +  if( !pTable ) return;
          641  +  if( ((!db || db->pnBytesFreed==0) && (--pTable->nRef)>0) ) return;
          642  +  deleteTable(db, pTable);
          643  +}
          644  +
   644    645   
   645    646   /*
   646    647   ** Unlink the given table from the hash tables and the delete the
   647    648   ** table structure with all its indices and foreign keys.
   648    649   */
   649    650   void sqlite3UnlinkAndDeleteTable(sqlite3 *db, int iDb, const char *zTabName){
   650    651     Table *p;
................................................................................
  2217   2218         pSelTab->nCol = 0;
  2218   2219         pSelTab->aCol = 0;
  2219   2220         assert( sqlite3SchemaMutexHeld(db, 0, pTable->pSchema) );
  2220   2221       }else{
  2221   2222         pTable->nCol = 0;
  2222   2223         nErr++;
  2223   2224       }
  2224         -    if( pSelTab ) sqlite3DeleteTable(db, pSelTab);
         2225  +    sqlite3DeleteTable(db, pSelTab);
  2225   2226       sqlite3SelectDelete(db, pSel);
  2226   2227       db->lookaside.bDisable--;
  2227   2228     } else {
  2228   2229       nErr++;
  2229   2230     }
  2230   2231     pTable->pSchema->schemaFlags |= DB_UnresetViews;
  2231   2232   #endif /* SQLITE_OMIT_VIEW */

Changes to src/delete.c.

   369    369       for(pIdx=pTab->pIndex; pIdx; pIdx=pIdx->pNext){
   370    370         assert( pIdx->pSchema==pTab->pSchema );
   371    371         sqlite3VdbeAddOp2(v, OP_Clear, pIdx->tnum, iDb);
   372    372       }
   373    373     }else
   374    374   #endif /* SQLITE_OMIT_TRUNCATE_OPTIMIZATION */
   375    375     {
   376         -    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK;
          376  +    u16 wcf = WHERE_ONEPASS_DESIRED|WHERE_DUPLICATES_OK|WHERE_SEEK_TABLE;
   377    377       if( sNC.ncFlags & NC_VarSelect ) bComplex = 1;
   378    378       wcf |= (bComplex ? 0 : WHERE_ONEPASS_MULTIROW);
   379    379       if( HasRowid(pTab) ){
   380    380         /* For a rowid table, initialize the RowSet to an empty set */
   381    381         pPk = 0;
   382    382         nPk = 1;
   383    383         iRowSet = ++pParse->nMem;

Changes to src/pager.c.

  1872   1872     }
  1873   1873     return rc;
  1874   1874   }
  1875   1875   
  1876   1876   static int pager_truncate(Pager *pPager, Pgno nPage);
  1877   1877   
  1878   1878   /*
  1879         -** The write transaction open on the pager passed as the only argument is
  1880         -** being committed. This function returns true if all dirty pages should
  1881         -** be flushed to disk, or false otherwise. Pages should be flushed to disk
  1882         -** unless one of the following is true:
         1879  +** The write transaction open on pPager is being committed (bCommit==1)
         1880  +** or rolled back (bCommit==0).
  1883   1881   **
  1884         -**   * The db is an in-memory database.
         1882  +** Return TRUE if and only if all dirty pages should be flushed to disk.
  1885   1883   **
  1886         -**   * The db is a temporary database and the db file has not been opened.
         1884  +** Rules:
  1887   1885   **
  1888         -**   * The db is a temporary database and the cache contains less than
  1889         -**     C/4 dirty pages, where C is the configured cache-size.
         1886  +**   *  For non-TEMP databases, always sync to disk.  This is necessary
         1887  +**      for transactions to be durable.
         1888  +**
         1889  +**   *  Sync TEMP database only on a COMMIT (not a ROLLBACK) when the backing
         1890  +**      file has been created already (via a spill on pagerStress()) and
         1891  +**      when the number of dirty pages in memory exceeds 25% of the total
         1892  +**      cache size.
  1890   1893   */
  1891         -static int pagerFlushOnCommit(Pager *pPager){
         1894  +static int pagerFlushOnCommit(Pager *pPager, int bCommit){
  1892   1895     if( pPager->tempFile==0 ) return 1;
         1896  +  if( !bCommit ) return 0;
  1893   1897     if( !isOpen(pPager->fd) ) return 0;
  1894   1898     return (sqlite3PCachePercentDirty(pPager->pPCache)>=25);
  1895   1899   }
  1896   1900   
  1897   1901   /*
  1898   1902   ** This routine ends a transaction. A transaction is usually ended by 
  1899   1903   ** either a COMMIT or a ROLLBACK operation. This routine may be called 
................................................................................
  1993   1997             rc = sqlite3OsSync(pPager->jfd, pPager->syncFlags);
  1994   1998           }
  1995   1999         }
  1996   2000         pPager->journalOff = 0;
  1997   2001       }else if( pPager->journalMode==PAGER_JOURNALMODE_PERSIST
  1998   2002         || (pPager->exclusiveMode && pPager->journalMode!=PAGER_JOURNALMODE_WAL)
  1999   2003       ){
  2000         -      rc = zeroJournalHdr(pPager, hasMaster);
         2004  +      rc = zeroJournalHdr(pPager, hasMaster||pPager->tempFile);
  2001   2005         pPager->journalOff = 0;
  2002   2006       }else{
  2003   2007         /* This branch may be executed with Pager.journalMode==MEMORY if
  2004   2008         ** a hot-journal was just rolled back. In this case the journal
  2005   2009         ** file should be closed and deleted. If this connection writes to
  2006   2010         ** the database file, it will do so using an in-memory journal.
  2007   2011         */
................................................................................
  2028   2032       }
  2029   2033     }
  2030   2034   #endif
  2031   2035   
  2032   2036     sqlite3BitvecDestroy(pPager->pInJournal);
  2033   2037     pPager->pInJournal = 0;
  2034   2038     pPager->nRec = 0;
  2035         -  if( MEMDB || pagerFlushOnCommit(pPager) ){
  2036         -    sqlite3PcacheCleanAll(pPager->pPCache);
  2037         -  }else{
  2038         -    sqlite3PcacheClearWritable(pPager->pPCache);
         2039  +  if( rc==SQLITE_OK ){
         2040  +    if( pagerFlushOnCommit(pPager, bCommit) ){
         2041  +      sqlite3PcacheCleanAll(pPager->pPCache);
         2042  +    }else{
         2043  +      sqlite3PcacheClearWritable(pPager->pPCache);
         2044  +    }
         2045  +    sqlite3PcacheTruncate(pPager->pPCache, pPager->dbSize);
  2039   2046     }
  2040         -  sqlite3PcacheTruncate(pPager->pPCache, pPager->dbSize);
  2041   2047   
  2042   2048     if( pagerUseWal(pPager) ){
  2043   2049       /* Drop the WAL write-lock, if any. Also, if the connection was in 
  2044   2050       ** locking_mode=exclusive mode but is no longer, drop the EXCLUSIVE 
  2045   2051       ** lock held on the database file.
  2046   2052       */
  2047   2053       rc2 = sqlite3WalEndWriteTransaction(pPager->pWal);
................................................................................
  2367   2373       assert( isSavepnt );
  2368   2374       assert( (pPager->doNotSpill & SPILLFLAG_ROLLBACK)==0 );
  2369   2375       pPager->doNotSpill |= SPILLFLAG_ROLLBACK;
  2370   2376       rc = sqlite3PagerGet(pPager, pgno, &pPg, 1);
  2371   2377       assert( (pPager->doNotSpill & SPILLFLAG_ROLLBACK)!=0 );
  2372   2378       pPager->doNotSpill &= ~SPILLFLAG_ROLLBACK;
  2373   2379       if( rc!=SQLITE_OK ) return rc;
  2374         -    pPg->flags &= ~PGHDR_NEED_READ;
  2375   2380       sqlite3PcacheMakeDirty(pPg);
  2376   2381     }
  2377   2382     if( pPg ){
  2378   2383       /* No page should ever be explicitly rolled back that is in use, except
  2379   2384       ** for page 1 which is held in use in order to keep the lock on the
  2380   2385       ** database active. However such a page may be rolled back as a result
  2381   2386       ** of an internal error resulting in an automatic call to
  2382   2387       ** sqlite3PagerRollback().
  2383   2388       */
  2384   2389       void *pData;
  2385   2390       pData = pPg->pData;
  2386   2391       memcpy(pData, (u8*)aData, pPager->pageSize);
  2387   2392       pPager->xReiniter(pPg);
  2388         -    if( isMainJrnl && (!isSavepnt || *pOffset<=pPager->journalHdr) ){
  2389         -      /* If the contents of this page were just restored from the main 
  2390         -      ** journal file, then its content must be as they were when the 
  2391         -      ** transaction was first opened. In this case we can mark the page
  2392         -      ** as clean, since there will be no need to write it out to the
  2393         -      ** database.
  2394         -      **
  2395         -      ** There is one exception to this rule. If the page is being rolled
  2396         -      ** back as part of a savepoint (or statement) rollback from an 
  2397         -      ** unsynced portion of the main journal file, then it is not safe
  2398         -      ** to mark the page as clean. This is because marking the page as
  2399         -      ** clean will clear the PGHDR_NEED_SYNC flag. Since the page is
  2400         -      ** already in the journal file (recorded in Pager.pInJournal) and
  2401         -      ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
  2402         -      ** again within this transaction, it will be marked as dirty but
  2403         -      ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
  2404         -      ** be written out into the database file before its journal file
  2405         -      ** segment is synced. If a crash occurs during or following this,
  2406         -      ** database corruption may ensue.
  2407         -      **
  2408         -      ** Update: Another exception is for temp files that are not 
  2409         -      ** in-memory databases. In this case the page may have been dirty
  2410         -      ** at the start of the transaction.
  2411         -      */
  2412         -      assert( !pagerUseWal(pPager) );
  2413         -      if( pPager->tempFile==0 ) sqlite3PcacheMakeClean(pPg);
  2414         -    }
         2393  +    /* It used to be that sqlite3PcacheMakeClean(pPg) was called here.  But
         2394  +    ** that call was dangerous and had no detectable benefit since the cache
         2395  +    ** is normally cleaned by sqlite3PcacheCleanAll() after rollback and so
         2396  +    ** has been removed. */
  2415   2397       pager_set_pagehash(pPg);
  2416   2398   
  2417   2399       /* If this was page 1, then restore the value of Pager.dbFileVers.
  2418   2400       ** Do this before any decoding. */
  2419   2401       if( pgno==1 ){
  2420   2402         memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers));
  2421   2403       }
................................................................................
  6018   6000   void sqlite3PagerDontWrite(PgHdr *pPg){
  6019   6001     Pager *pPager = pPg->pPager;
  6020   6002     if( !pPager->tempFile && (pPg->flags&PGHDR_DIRTY) && pPager->nSavepoint==0 ){
  6021   6003       PAGERTRACE(("DONT_WRITE page %d of %d\n", pPg->pgno, PAGERID(pPager)));
  6022   6004       IOTRACE(("CLEAN %p %d\n", pPager, pPg->pgno))
  6023   6005       pPg->flags |= PGHDR_DONT_WRITE;
  6024   6006       pPg->flags &= ~PGHDR_WRITEABLE;
         6007  +    testcase( pPg->flags & PGHDR_NEED_SYNC );
  6025   6008       pager_set_pagehash(pPg);
  6026   6009     }
  6027   6010   }
  6028   6011   
  6029   6012   /*
  6030   6013   ** This routine is called to increment the value of the database file 
  6031   6014   ** change-counter, stored as a 4-byte big-endian integer starting at 
................................................................................
  6213   6196     );
  6214   6197     assert( assert_pager_state(pPager) );
  6215   6198   
  6216   6199     /* If a prior error occurred, report that error again. */
  6217   6200     if( NEVER(pPager->errCode) ) return pPager->errCode;
  6218   6201   
  6219   6202     /* Provide the ability to easily simulate an I/O error during testing */
  6220         -  if( (rc = sqlite3FaultSim(400))!=SQLITE_OK ) return rc;
         6203  +  if( sqlite3FaultSim(400) ) return SQLITE_IOERR;
  6221   6204   
  6222   6205     PAGERTRACE(("DATABASE SYNC: File=%s zMaster=%s nSize=%d\n", 
  6223   6206         pPager->zFilename, zMaster, pPager->dbSize));
  6224   6207   
  6225   6208     /* If no database changes have been made, return early. */
  6226   6209     if( pPager->eState<PAGER_WRITER_CACHEMOD ) return SQLITE_OK;
  6227   6210   
  6228   6211     assert( MEMDB==0 || pPager->tempFile );
  6229   6212     assert( isOpen(pPager->fd) || pPager->tempFile );
  6230         -  if( 0==pagerFlushOnCommit(pPager) ){
         6213  +  if( 0==pagerFlushOnCommit(pPager, 1) ){
  6231   6214       /* If this is an in-memory db, or no pages have been written to, or this
  6232   6215       ** function has already been called, it is mostly a no-op.  However, any
  6233   6216       ** backup in progress needs to be restarted.  */
  6234   6217       sqlite3BackupRestart(pPager->pBackup);
  6235   6218     }else{
  6236   6219       if( pagerUseWal(pPager) ){
  6237   6220         PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
................................................................................
  6848   6831          || pPager->eState==PAGER_WRITER_DBMOD
  6849   6832     );
  6850   6833     assert( assert_pager_state(pPager) );
  6851   6834   
  6852   6835     /* In order to be able to rollback, an in-memory database must journal
  6853   6836     ** the page we are moving from.
  6854   6837     */
         6838  +  assert( pPager->tempFile || !MEMDB );
  6855   6839     if( pPager->tempFile ){
  6856   6840       rc = sqlite3PagerWrite(pPg);
  6857   6841       if( rc ) return rc;
  6858   6842     }
  6859   6843   
  6860   6844     /* If the page being moved is dirty and has not been saved by the latest
  6861   6845     ** savepoint, then save the current contents of the page into the 
................................................................................
  6922   6906     sqlite3PcacheMove(pPg, pgno);
  6923   6907     sqlite3PcacheMakeDirty(pPg);
  6924   6908   
  6925   6909     /* For an in-memory database, make sure the original page continues
  6926   6910     ** to exist, in case the transaction needs to roll back.  Use pPgOld
  6927   6911     ** as the original page since it has already been allocated.
  6928   6912     */
  6929         -  if( pPager->tempFile ){
  6930         -    assert( pPgOld );
         6913  +  if( pPager->tempFile && pPgOld ){
  6931   6914       sqlite3PcacheMove(pPgOld, origPgno);
  6932   6915       sqlite3PagerUnrefNotNull(pPgOld);
  6933   6916     }
  6934   6917   
  6935   6918     if( needSyncPgno ){
  6936   6919       /* If needSyncPgno is non-zero, then the journal file needs to be 
  6937   6920       ** sync()ed before any data is written to database file page needSyncPgno.

Changes to src/pcache.c.

    10     10   **
    11     11   *************************************************************************
    12     12   ** This file implements that page cache.
    13     13   */
    14     14   #include "sqliteInt.h"
    15     15   
    16     16   /*
    17         -** A complete page cache is an instance of this structure.
           17  +** A complete page cache is an instance of this structure.  Every
           18  +** entry in the cache holds a single page of the database file.  The
           19  +** btree layer only operates on the cached copy of the database pages.
           20  +**
           21  +** A page cache entry is "clean" if it exactly matches what is currently
           22  +** on disk.  A page is "dirty" if it has been modified and needs to be
           23  +** persisted to disk.
           24  +**
           25  +** pDirty, pDirtyTail, pSynced:
           26  +**   All dirty pages are linked into the doubly linked list using
           27  +**   PgHdr.pDirtyNext and pDirtyPrev. The list is maintained in LRU order
           28  +**   such that p was added to the list more recently than p->pDirtyNext.
           29  +**   PCache.pDirty points to the first (newest) element in the list and
           30  +**   pDirtyTail to the last (oldest).
           31  +**
           32  +**   The PCache.pSynced variable is used to optimize searching for a dirty
           33  +**   page to eject from the cache mid-transaction. It is better to eject
           34  +**   a page that does not require a journal sync than one that does. 
           35  +**   Therefore, pSynced is maintained to that it *almost* always points
           36  +**   to either the oldest page in the pDirty/pDirtyTail list that has a
           37  +**   clear PGHDR_NEED_SYNC flag or to a page that is older than this one
           38  +**   (so that the right page to eject can be found by following pDirtyPrev
           39  +**   pointers).
    18     40   */
    19     41   struct PCache {
    20     42     PgHdr *pDirty, *pDirtyTail;         /* List of dirty pages in LRU order */
    21     43     PgHdr *pSynced;                     /* Last synced page in dirty page list */
    22     44     int nRefSum;                        /* Sum of ref counts over all pages */
    23     45     int szCache;                        /* Configured cache size */
    24     46     int szSpill;                        /* Size before spilling occurs */
................................................................................
    26     48     int szExtra;                        /* Size of extra space for each page */
    27     49     u8 bPurgeable;                      /* True if pages are on backing store */
    28     50     u8 eCreate;                         /* eCreate value for for xFetch() */
    29     51     int (*xStress)(void*,PgHdr*);       /* Call to try make a page clean */
    30     52     void *pStress;                      /* Argument to xStress */
    31     53     sqlite3_pcache *pCache;             /* Pluggable cache module */
    32     54   };
           55  +
           56  +/********************************** Test and Debug Logic **********************/
           57  +/*
           58  +** Debug tracing macros.  Enable by by changing the "0" to "1" and
           59  +** recompiling.
           60  +**
           61  +** When sqlite3PcacheTrace is 1, single line trace messages are issued.
           62  +** When sqlite3PcacheTrace is 2, a dump of the pcache showing all cache entries
           63  +** is displayed for many operations, resulting in a lot of output.
           64  +*/
           65  +#if defined(SQLITE_DEBUG) && 0
           66  +  int sqlite3PcacheTrace = 2;       /* 0: off  1: simple  2: cache dumps */
           67  +  int sqlite3PcacheMxDump = 9999;   /* Max cache entries for pcacheDump() */
           68  +# define pcacheTrace(X) if(sqlite3PcacheTrace){sqlite3DebugPrintf X;}
           69  +  void pcacheDump(PCache *pCache){
           70  +    int N;
           71  +    int i, j;
           72  +    sqlite3_pcache_page *pLower;
           73  +    PgHdr *pPg;
           74  +    unsigned char *a;
           75  +  
           76  +    if( sqlite3PcacheTrace<2 ) return;
           77  +    if( pCache->pCache==0 ) return;
           78  +    N = sqlite3PcachePagecount(pCache);
           79  +    if( N>sqlite3PcacheMxDump ) N = sqlite3PcacheMxDump;
           80  +    for(i=1; i<=N; i++){
           81  +       pLower = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, i, 0);
           82  +       if( pLower==0 ) continue;
           83  +       pPg = (PgHdr*)pLower->pExtra;
           84  +       printf("%3d: nRef %2d flgs %02x data ", i, pPg->nRef, pPg->flags);
           85  +       a = (unsigned char *)pLower->pBuf;
           86  +       for(j=0; j<12; j++) printf("%02x", a[j]);
           87  +       printf("\n");
           88  +       if( pPg->pPage==0 ){
           89  +         sqlite3GlobalConfig.pcache2.xUnpin(pCache->pCache, pLower, 0);
           90  +       }
           91  +    }
           92  +  }
           93  +  #else
           94  +# define pcacheTrace(X)
           95  +# define pcacheDump(X)
           96  +#endif
           97  +
           98  +/*
           99  +** Check invariants on a PgHdr entry.  Return true if everything is OK.
          100  +** Return false if any invariant is violated.
          101  +**
          102  +** This routine is for use inside of assert() statements only.  For
          103  +** example:
          104  +**
          105  +**          assert( sqlite3PcachePageSanity(pPg) );
          106  +*/
          107  +#if SQLITE_DEBUG
          108  +int sqlite3PcachePageSanity(PgHdr *pPg){
          109  +  PCache *pCache;
          110  +  assert( pPg!=0 );
          111  +  assert( pPg->pgno>0 );    /* Page number is 1 or more */
          112  +  pCache = pPg->pCache;
          113  +  assert( pCache!=0 );      /* Every page has an associated PCache */
          114  +  if( pPg->flags & PGHDR_CLEAN ){
          115  +    assert( (pPg->flags & PGHDR_DIRTY)==0 );/* Cannot be both CLEAN and DIRTY */
          116  +    assert( pCache->pDirty!=pPg );          /* CLEAN pages not on dirty list */
          117  +    assert( pCache->pDirtyTail!=pPg );
          118  +  }
          119  +  /* WRITEABLE pages must also be DIRTY */
          120  +  if( pPg->flags & PGHDR_WRITEABLE ){
          121  +    assert( pPg->flags & PGHDR_DIRTY );     /* WRITEABLE implies DIRTY */
          122  +  }
          123  +  /* NEED_SYNC can be set independently of WRITEABLE.  This can happen,
          124  +  ** for example, when using the sqlite3PagerDontWrite() optimization:
          125  +  **    (1)  Page X is journalled, and gets WRITEABLE and NEED_SEEK.
          126  +  **    (2)  Page X moved to freelist, WRITEABLE is cleared
          127  +  **    (3)  Page X reused, WRITEABLE is set again
          128  +  ** If NEED_SYNC had been cleared in step 2, then it would not be reset
          129  +  ** in step 3, and page might be written into the database without first
          130  +  ** syncing the rollback journal, which might cause corruption on a power
          131  +  ** loss.
          132  +  **
          133  +  ** Another example is when the database page size is smaller than the
          134  +  ** disk sector size.  When any page of a sector is journalled, all pages
          135  +  ** in that sector are marked NEED_SYNC even if they are still CLEAN, just
          136  +  ** in case they are later modified, since all pages in the same sector
          137  +  ** must be journalled and synced before any of those pages can be safely
          138  +  ** written.
          139  +  */
          140  +  return 1;
          141  +}
          142  +#endif /* SQLITE_DEBUG */
          143  +
    33    144   
    34    145   /********************************** Linked List Management ********************/
    35    146   
    36    147   /* Allowed values for second argument to pcacheManageDirtyList() */
    37    148   #define PCACHE_DIRTYLIST_REMOVE   1    /* Remove pPage from dirty list */
    38    149   #define PCACHE_DIRTYLIST_ADD      2    /* Add pPage to the dirty list */
    39    150   #define PCACHE_DIRTYLIST_FRONT    3    /* Move pPage to the front of the list */
................................................................................
    43    154   ** argument determines what operation to do.  The 0x01 bit means first
    44    155   ** remove pPage from the dirty list.  The 0x02 means add pPage back to
    45    156   ** the dirty list.  Doing both moves pPage to the front of the dirty list.
    46    157   */
    47    158   static void pcacheManageDirtyList(PgHdr *pPage, u8 addRemove){
    48    159     PCache *p = pPage->pCache;
    49    160   
          161  +  pcacheTrace(("%p.DIRTYLIST.%s %d\n", p,
          162  +                addRemove==1 ? "REMOVE" : addRemove==2 ? "ADD" : "FRONT",
          163  +                pPage->pgno));
    50    164     if( addRemove & PCACHE_DIRTYLIST_REMOVE ){
    51    165       assert( pPage->pDirtyNext || pPage==p->pDirtyTail );
    52    166       assert( pPage->pDirtyPrev || pPage==p->pDirty );
    53    167     
    54    168       /* Update the PCache1.pSynced variable if necessary. */
    55    169       if( p->pSynced==pPage ){
    56         -      PgHdr *pSynced = pPage->pDirtyPrev;
    57         -      while( pSynced && (pSynced->flags&PGHDR_NEED_SYNC) ){
    58         -        pSynced = pSynced->pDirtyPrev;
    59         -      }
    60         -      p->pSynced = pSynced;
          170  +      p->pSynced = pPage->pDirtyPrev;
    61    171       }
    62    172     
    63    173       if( pPage->pDirtyNext ){
    64    174         pPage->pDirtyNext->pDirtyPrev = pPage->pDirtyPrev;
    65    175       }else{
    66    176         assert( pPage==p->pDirtyTail );
    67    177         p->pDirtyTail = pPage->pDirtyPrev;
    68    178       }
    69    179       if( pPage->pDirtyPrev ){
    70    180         pPage->pDirtyPrev->pDirtyNext = pPage->pDirtyNext;
    71    181       }else{
          182  +      /* If there are now no dirty pages in the cache, set eCreate to 2. 
          183  +      ** This is an optimization that allows sqlite3PcacheFetch() to skip
          184  +      ** searching for a dirty page to eject from the cache when it might
          185  +      ** otherwise have to.  */
    72    186         assert( pPage==p->pDirty );
    73    187         p->pDirty = pPage->pDirtyNext;
    74         -      if( p->pDirty==0 && p->bPurgeable ){
    75         -        assert( p->eCreate==1 );
          188  +      assert( p->bPurgeable || p->eCreate==2 );
          189  +      if( p->pDirty==0 ){         /*OPTIMIZATION-IF-TRUE*/
          190  +        assert( p->bPurgeable==0 || p->eCreate==1 );
    76    191           p->eCreate = 2;
    77    192         }
    78    193       }
    79    194       pPage->pDirtyNext = 0;
    80    195       pPage->pDirtyPrev = 0;
    81    196     }
    82    197     if( addRemove & PCACHE_DIRTYLIST_ADD ){
................................................................................
    90    205         p->pDirtyTail = pPage;
    91    206         if( p->bPurgeable ){
    92    207           assert( p->eCreate==2 );
    93    208           p->eCreate = 1;
    94    209         }
    95    210       }
    96    211       p->pDirty = pPage;
    97         -    if( !p->pSynced && 0==(pPage->flags&PGHDR_NEED_SYNC) ){
          212  +
          213  +    /* If pSynced is NULL and this page has a clear NEED_SYNC flag, set
          214  +    ** pSynced to point to it. Checking the NEED_SYNC flag is an 
          215  +    ** optimization, as if pSynced points to a page with the NEED_SYNC
          216  +    ** flag set sqlite3PcacheFetchStress() searches through all newer 
          217  +    ** entries of the dirty-list for a page with NEED_SYNC clear anyway.  */
          218  +    if( !p->pSynced 
          219  +     && 0==(pPage->flags&PGHDR_NEED_SYNC)   /*OPTIMIZATION-IF-FALSE*/
          220  +    ){
    98    221         p->pSynced = pPage;
    99    222       }
   100    223     }
          224  +  pcacheDump(p);
   101    225   }
   102    226   
   103    227   /*
   104    228   ** Wrapper around the pluggable caches xUnpin method. If the cache is
   105    229   ** being used for an in-memory database, this function is a no-op.
   106    230   */
   107    231   static void pcacheUnpin(PgHdr *p){
   108    232     if( p->pCache->bPurgeable ){
          233  +    pcacheTrace(("%p.UNPIN %d\n", p->pCache, p->pgno));
   109    234       sqlite3GlobalConfig.pcache2.xUnpin(p->pCache->pCache, p->pPage, 0);
          235  +    pcacheDump(p->pCache);
   110    236     }
   111    237   }
   112    238   
   113    239   /*
   114    240   ** Compute the number of pages of cache requested.   p->szCache is the
   115    241   ** cache size requested by the "PRAGMA cache_size" statement.
   116    242   */
................................................................................
   172    298     p->szExtra = szExtra;
   173    299     p->bPurgeable = bPurgeable;
   174    300     p->eCreate = 2;
   175    301     p->xStress = xStress;
   176    302     p->pStress = pStress;
   177    303     p->szCache = 100;
   178    304     p->szSpill = 1;
          305  +  pcacheTrace(("%p.OPEN szPage %d bPurgeable %d\n",p,szPage,bPurgeable));
   179    306     return sqlite3PcacheSetPageSize(p, szPage);
   180    307   }
   181    308   
   182    309   /*
   183    310   ** Change the page size for PCache object. The caller must ensure that there
   184    311   ** are no outstanding page references when this function is called.
   185    312   */
................................................................................
   194    321       if( pNew==0 ) return SQLITE_NOMEM_BKPT;
   195    322       sqlite3GlobalConfig.pcache2.xCachesize(pNew, numberOfCachePages(pCache));
   196    323       if( pCache->pCache ){
   197    324         sqlite3GlobalConfig.pcache2.xDestroy(pCache->pCache);
   198    325       }
   199    326       pCache->pCache = pNew;
   200    327       pCache->szPage = szPage;
          328  +    pcacheTrace(("%p.PAGESIZE %d\n",pCache,szPage));
   201    329     }
   202    330     return SQLITE_OK;
   203    331   }
   204    332   
   205    333   /*
   206    334   ** Try to obtain a page from the cache.
   207    335   **
................................................................................
   228    356   */
   229    357   sqlite3_pcache_page *sqlite3PcacheFetch(
   230    358     PCache *pCache,       /* Obtain the page from this cache */
   231    359     Pgno pgno,            /* Page number to obtain */
   232    360     int createFlag        /* If true, create page if it does not exist already */
   233    361   ){
   234    362     int eCreate;
          363  +  sqlite3_pcache_page *pRes;
   235    364   
   236    365     assert( pCache!=0 );
   237    366     assert( pCache->pCache!=0 );
   238    367     assert( createFlag==3 || createFlag==0 );
   239    368     assert( pgno>0 );
          369  +  assert( pCache->eCreate==((pCache->bPurgeable && pCache->pDirty) ? 1 : 2) );
   240    370   
   241    371     /* eCreate defines what to do if the page does not exist.
   242    372     **    0     Do not allocate a new page.  (createFlag==0)
   243    373     **    1     Allocate a new page if doing so is inexpensive.
   244    374     **          (createFlag==1 AND bPurgeable AND pDirty)
   245    375     **    2     Allocate a new page even it doing so is difficult.
   246    376     **          (createFlag==1 AND !(bPurgeable AND pDirty)
   247    377     */
   248    378     eCreate = createFlag & pCache->eCreate;
   249    379     assert( eCreate==0 || eCreate==1 || eCreate==2 );
   250    380     assert( createFlag==0 || pCache->eCreate==eCreate );
   251    381     assert( createFlag==0 || eCreate==1+(!pCache->bPurgeable||!pCache->pDirty) );
   252         -  return sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, eCreate);
          382  +  pRes = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, eCreate);
          383  +  pcacheTrace(("%p.FETCH %d%s (result: %p)\n",pCache,pgno,
          384  +               createFlag?" create":"",pRes));
          385  +  return pRes;
   253    386   }
   254    387   
   255    388   /*
   256    389   ** If the sqlite3PcacheFetch() routine is unable to allocate a new
   257    390   ** page because no clean pages are available for reuse and the cache
   258    391   ** size limit has been reached, then this routine can be invoked to 
   259    392   ** try harder to allocate a page.  This routine might invoke the stress
................................................................................
   272    405     if( pCache->eCreate==2 ) return 0;
   273    406   
   274    407     if( sqlite3PcachePagecount(pCache)>pCache->szSpill ){
   275    408       /* Find a dirty page to write-out and recycle. First try to find a 
   276    409       ** page that does not require a journal-sync (one with PGHDR_NEED_SYNC
   277    410       ** cleared), but if that is not possible settle for any other 
   278    411       ** unreferenced dirty page.
   279         -    */
          412  +    **
          413  +    ** If the LRU page in the dirty list that has a clear PGHDR_NEED_SYNC
          414  +    ** flag is currently referenced, then the following may leave pSynced
          415  +    ** set incorrectly (pointing to other than the LRU page with NEED_SYNC
          416  +    ** cleared). This is Ok, as pSynced is just an optimization.  */
   280    417       for(pPg=pCache->pSynced; 
   281    418           pPg && (pPg->nRef || (pPg->flags&PGHDR_NEED_SYNC)); 
   282    419           pPg=pPg->pDirtyPrev
   283    420       );
   284    421       pCache->pSynced = pPg;
   285    422       if( !pPg ){
   286    423         for(pPg=pCache->pDirtyTail; pPg && pPg->nRef; pPg=pPg->pDirtyPrev);
................................................................................
   290    427   #ifdef SQLITE_LOG_CACHE_SPILL
   291    428         sqlite3_log(SQLITE_FULL, 
   292    429                     "spill page %d making room for %d - cache used: %d/%d",
   293    430                     pPg->pgno, pgno,
   294    431                     sqlite3GlobalConfig.pcache.xPagecount(pCache->pCache),
   295    432                   numberOfCachePages(pCache));
   296    433   #endif
          434  +      pcacheTrace(("%p.SPILL %d\n",pCache,pPg->pgno));
   297    435         rc = pCache->xStress(pCache->pStress, pPg);
          436  +      pcacheDump(pCache);
   298    437         if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){
   299    438           return rc;
   300    439         }
   301    440       }
   302    441     }
   303    442     *ppPage = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, 2);
   304    443     return *ppPage==0 ? SQLITE_NOMEM_BKPT : SQLITE_OK;
................................................................................
   350    489     pPgHdr = (PgHdr *)pPage->pExtra;
   351    490   
   352    491     if( !pPgHdr->pPage ){
   353    492       return pcacheFetchFinishWithInit(pCache, pgno, pPage);
   354    493     }
   355    494     pCache->nRefSum++;
   356    495     pPgHdr->nRef++;
          496  +  assert( sqlite3PcachePageSanity(pPgHdr) );
   357    497     return pPgHdr;
   358    498   }
   359    499   
   360    500   /*
   361    501   ** Decrement the reference count on a page. If the page is clean and the
   362    502   ** reference count drops to 0, then it is made eligible for recycling.
   363    503   */
   364    504   void SQLITE_NOINLINE sqlite3PcacheRelease(PgHdr *p){
   365    505     assert( p->nRef>0 );
   366    506     p->pCache->nRefSum--;
   367    507     if( (--p->nRef)==0 ){
   368    508       if( p->flags&PGHDR_CLEAN ){
   369    509         pcacheUnpin(p);
   370         -    }else if( p->pDirtyPrev!=0 ){
   371         -      /* Move the page to the head of the dirty list. */
          510  +    }else if( p->pDirtyPrev!=0 ){ /*OPTIMIZATION-IF-FALSE*/
          511  +      /* Move the page to the head of the dirty list. If p->pDirtyPrev==0,
          512  +      ** then page p is already at the head of the dirty list and the
          513  +      ** following call would be a no-op. Hence the OPTIMIZATION-IF-FALSE
          514  +      ** tag above.  */
   372    515         pcacheManageDirtyList(p, PCACHE_DIRTYLIST_FRONT);
   373    516       }
   374    517     }
   375    518   }
   376    519   
   377    520   /*
   378    521   ** Increase the reference count of a supplied page by 1.
   379    522   */
   380    523   void sqlite3PcacheRef(PgHdr *p){
   381    524     assert(p->nRef>0);
          525  +  assert( sqlite3PcachePageSanity(p) );
   382    526     p->nRef++;
   383    527     p->pCache->nRefSum++;
   384    528   }
   385    529   
   386    530   /*
   387    531   ** Drop a page from the cache. There must be exactly one reference to the
   388    532   ** page. This function deletes that reference, so after it returns the
   389    533   ** page pointed to by p is invalid.
   390    534   */
   391    535   void sqlite3PcacheDrop(PgHdr *p){
   392    536     assert( p->nRef==1 );
          537  +  assert( sqlite3PcachePageSanity(p) );
   393    538     if( p->flags&PGHDR_DIRTY ){
   394    539       pcacheManageDirtyList(p, PCACHE_DIRTYLIST_REMOVE);
   395    540     }
   396    541     p->pCache->nRefSum--;
   397    542     sqlite3GlobalConfig.pcache2.xUnpin(p->pCache->pCache, p->pPage, 1);
   398    543   }
   399    544   
   400    545   /*
   401    546   ** Make sure the page is marked as dirty. If it isn't dirty already,
   402    547   ** make it so.
   403    548   */
   404    549   void sqlite3PcacheMakeDirty(PgHdr *p){
   405    550     assert( p->nRef>0 );
   406         -  if( p->flags & (PGHDR_CLEAN|PGHDR_DONT_WRITE) ){
          551  +  assert( sqlite3PcachePageSanity(p) );
          552  +  if( p->flags & (PGHDR_CLEAN|PGHDR_DONT_WRITE) ){    /*OPTIMIZATION-IF-FALSE*/
   407    553       p->flags &= ~PGHDR_DONT_WRITE;
   408    554       if( p->flags & PGHDR_CLEAN ){
   409    555         p->flags ^= (PGHDR_DIRTY|PGHDR_CLEAN);
          556  +      pcacheTrace(("%p.DIRTY %d\n",p->pCache,p->pgno));
   410    557         assert( (p->flags & (PGHDR_DIRTY|PGHDR_CLEAN))==PGHDR_DIRTY );
   411    558         pcacheManageDirtyList(p, PCACHE_DIRTYLIST_ADD);
   412    559       }
          560  +    assert( sqlite3PcachePageSanity(p) );
   413    561     }
   414    562   }
   415    563   
   416    564   /*
   417    565   ** Make sure the page is marked as clean. If it isn't clean already,
   418    566   ** make it so.
   419    567   */
   420    568   void sqlite3PcacheMakeClean(PgHdr *p){
   421         -  if( (p->flags & PGHDR_DIRTY) ){
          569  +  assert( sqlite3PcachePageSanity(p) );
          570  +  if( ALWAYS((p->flags & PGHDR_DIRTY)!=0) ){
   422    571       assert( (p->flags & PGHDR_CLEAN)==0 );
   423    572       pcacheManageDirtyList(p, PCACHE_DIRTYLIST_REMOVE);
   424    573       p->flags &= ~(PGHDR_DIRTY|PGHDR_NEED_SYNC|PGHDR_WRITEABLE);
   425    574       p->flags |= PGHDR_CLEAN;
          575  +    pcacheTrace(("%p.CLEAN %d\n",p->pCache,p->pgno));
          576  +    assert( sqlite3PcachePageSanity(p) );
   426    577       if( p->nRef==0 ){
   427    578         pcacheUnpin(p);
   428    579       }
   429    580     }
   430    581   }
   431    582   
   432    583   /*
   433    584   ** Make every page in the cache clean.
   434    585   */
   435    586   void sqlite3PcacheCleanAll(PCache *pCache){
   436    587     PgHdr *p;
          588  +  pcacheTrace(("%p.CLEAN-ALL\n",pCache));
   437    589     while( (p = pCache->pDirty)!=0 ){
   438    590       sqlite3PcacheMakeClean(p);
   439    591     }
   440    592   }
   441    593   
   442    594   /*
   443    595   ** Clear the PGHDR_NEED_SYNC and PGHDR_WRITEABLE flag from all dirty pages.
   444    596   */
   445    597   void sqlite3PcacheClearWritable(PCache *pCache){
   446    598     PgHdr *p;
          599  +  pcacheTrace(("%p.CLEAR-WRITEABLE\n",pCache));
   447    600     for(p=pCache->pDirty; p; p=p->pDirtyNext){
   448    601       p->flags &= ~(PGHDR_NEED_SYNC|PGHDR_WRITEABLE);
   449    602     }
   450    603     pCache->pSynced = pCache->pDirtyTail;
   451    604   }
   452    605   
   453    606   /*
................................................................................
   464    617   /*
   465    618   ** Change the page number of page p to newPgno. 
   466    619   */
   467    620   void sqlite3PcacheMove(PgHdr *p, Pgno newPgno){
   468    621     PCache *pCache = p->pCache;
   469    622     assert( p->nRef>0 );
   470    623     assert( newPgno>0 );
          624  +  assert( sqlite3PcachePageSanity(p) );
          625  +  pcacheTrace(("%p.MOVE %d -> %d\n",pCache,p->pgno,newPgno));
   471    626     sqlite3GlobalConfig.pcache2.xRekey(pCache->pCache, p->pPage, p->pgno,newPgno);
   472    627     p->pgno = newPgno;
   473    628     if( (p->flags&PGHDR_DIRTY) && (p->flags&PGHDR_NEED_SYNC) ){
   474    629       pcacheManageDirtyList(p, PCACHE_DIRTYLIST_FRONT);
   475    630     }
   476    631   }
   477    632   
................................................................................
   484    639   ** function is 0, then the data area associated with page 1 is zeroed, but
   485    640   ** the page object is not dropped.
   486    641   */
   487    642   void sqlite3PcacheTruncate(PCache *pCache, Pgno pgno){
   488    643     if( pCache->pCache ){
   489    644       PgHdr *p;
   490    645       PgHdr *pNext;
          646  +    pcacheTrace(("%p.TRUNCATE %d\n",pCache,pgno));
   491    647       for(p=pCache->pDirty; p; p=pNext){
   492    648         pNext = p->pDirtyNext;
   493    649         /* This routine never gets call with a positive pgno except right
   494    650         ** after sqlite3PcacheCleanAll().  So if there are dirty pages,
   495    651         ** it must be that pgno==0.
   496    652         */
   497    653         assert( p->pgno>0 );
................................................................................
   514    670   }
   515    671   
   516    672   /*
   517    673   ** Close a cache.
   518    674   */
   519    675   void sqlite3PcacheClose(PCache *pCache){
   520    676     assert( pCache->pCache!=0 );
          677  +  pcacheTrace(("%p.CLOSE\n",pCache));
   521    678     sqlite3GlobalConfig.pcache2.xDestroy(pCache->pCache);
   522    679   }
   523    680   
   524    681   /* 
   525    682   ** Discard the contents of the cache.
   526    683   */
   527    684   void sqlite3PcacheClear(PCache *pCache){

Changes to src/pcache.h.

    22     22   ** Every page in the cache is controlled by an instance of the following
    23     23   ** structure.
    24     24   */
    25     25   struct PgHdr {
    26     26     sqlite3_pcache_page *pPage;    /* Pcache object page handle */
    27     27     void *pData;                   /* Page data */
    28     28     void *pExtra;                  /* Extra content */
    29         -  PgHdr *pDirty;                 /* Transient list of dirty pages */
           29  +  PgHdr *pDirty;                 /* Transient list of dirty sorted by pgno */
    30     30     Pager *pPager;                 /* The pager this page is part of */
    31     31     Pgno pgno;                     /* Page number for this page */
    32     32   #ifdef SQLITE_CHECK_PAGES
    33     33     u32 pageHash;                  /* Hash of page content */
    34     34   #endif
    35     35     u16 flags;                     /* PGHDR flags defined below */
    36     36   
................................................................................
    47     47   
    48     48   /* Bit values for PgHdr.flags */
    49     49   #define PGHDR_CLEAN           0x001  /* Page not on the PCache.pDirty list */
    50     50   #define PGHDR_DIRTY           0x002  /* Page is on the PCache.pDirty list */
    51     51   #define PGHDR_WRITEABLE       0x004  /* Journaled and ready to modify */
    52     52   #define PGHDR_NEED_SYNC       0x008  /* Fsync the rollback journal before
    53     53                                        ** writing this page to the database */
    54         -#define PGHDR_NEED_READ       0x010  /* Content is unread */
    55         -#define PGHDR_DONT_WRITE      0x020  /* Do not write content to disk */
    56         -#define PGHDR_MMAP            0x040  /* This is an mmap page object */
           54  +#define PGHDR_DONT_WRITE      0x010  /* Do not write content to disk */
           55  +#define PGHDR_MMAP            0x020  /* This is an mmap page object */
    57     56   
    58         -#define PGHDR_WAL_APPEND      0x080  /* Appended to wal file */
           57  +#define PGHDR_WAL_APPEND      0x040  /* Appended to wal file */
    59     58   
    60     59   /* Initialize and shutdown the page cache subsystem */
    61     60   int sqlite3PcacheInitialize(void);
    62     61   void sqlite3PcacheShutdown(void);
    63     62   
    64     63   /* Page cache buffer management:
    65     64   ** These routines implement SQLITE_CONFIG_PAGECACHE.
................................................................................
   133    132   #if defined(SQLITE_CHECK_PAGES) || defined(SQLITE_DEBUG)
   134    133   /* Iterate through all dirty pages currently stored in the cache. This
   135    134   ** interface is only available if SQLITE_CHECK_PAGES is defined when the 
   136    135   ** library is built.
   137    136   */
   138    137   void sqlite3PcacheIterateDirty(PCache *pCache, void (*xIter)(PgHdr *));
   139    138   #endif
          139  +
          140  +#if defined(SQLITE_DEBUG)
          141  +/* Check invariants on a PgHdr object */
          142  +int sqlite3PcachePageSanity(PgHdr*);
          143  +#endif
   140    144   
   141    145   /* Set and get the suggested cache-size for the specified pager-cache.
   142    146   **
   143    147   ** If no global maximum is configured, then the system attempts to limit
   144    148   ** the total number of pages cached by purgeable pager-caches to the sum
   145    149   ** of the suggested cache-sizes.
   146    150   */

Changes to src/sqliteInt.h.

   407    407   **
   408    408   ** In other words, ALWAYS and NEVER are added for defensive code.
   409    409   **
   410    410   ** When doing coverage testing ALWAYS and NEVER are hard-coded to
   411    411   ** be true and false so that the unreachable code they specify will
   412    412   ** not be counted as untested code.
   413    413   */
   414         -#if defined(SQLITE_COVERAGE_TEST)
          414  +#if defined(SQLITE_COVERAGE_TEST) || defined(SQLITE_MUTATION_TEST)
   415    415   # define ALWAYS(X)      (1)
   416    416   # define NEVER(X)       (0)
   417    417   #elif !defined(NDEBUG)
   418    418   # define ALWAYS(X)      ((X)?1:(assert(0),0))
   419    419   # define NEVER(X)       ((X)?(assert(0),1):0)
   420    420   #else
   421    421   # define ALWAYS(X)      (X)
................................................................................
  2539   2539   #define WHERE_GROUPBY          0x0100 /* pOrderBy is really a GROUP BY */
  2540   2540   #define WHERE_DISTINCTBY       0x0200 /* pOrderby is really a DISTINCT clause */
  2541   2541   #define WHERE_WANT_DISTINCT    0x0400 /* All output needs to be distinct */
  2542   2542   #define WHERE_SORTBYGROUP      0x0800 /* Support sqlite3WhereIsSorted() */
  2543   2543   #define WHERE_REOPEN_IDX       0x1000 /* Try to use OP_ReopenIdx */
  2544   2544   #define WHERE_ONEPASS_MULTIROW 0x2000 /* ONEPASS is ok with multiple rows */
  2545   2545   #define WHERE_USE_LIMIT        0x4000 /* There is a constant LIMIT clause */
         2546  +#define WHERE_SEEK_TABLE       0x8000 /* Do not defer seeks on main table */
  2546   2547   
  2547   2548   /* Allowed return values from sqlite3WhereIsDistinct()
  2548   2549   */
  2549   2550   #define WHERE_DISTINCT_NOOP      0  /* DISTINCT keyword not used */
  2550   2551   #define WHERE_DISTINCT_UNIQUE    1  /* No duplicates */
  2551   2552   #define WHERE_DISTINCT_ORDERED   2  /* All duplicates are adjacent */
  2552   2553   #define WHERE_DISTINCT_UNORDERED 3  /* Duplicates are scattered */

Changes to src/update.c.

   348    348   #endif
   349    349   
   350    350     /* Begin the database scan
   351    351     */
   352    352     if( HasRowid(pTab) ){
   353    353       sqlite3VdbeAddOp3(v, OP_Null, 0, regRowSet, regOldRowid);
   354    354       pWInfo = sqlite3WhereBegin(
   355         -        pParse, pTabList, pWhere, 0, 0, WHERE_ONEPASS_DESIRED, iIdxCur
          355  +        pParse, pTabList, pWhere, 0, 0,
          356  +            WHERE_ONEPASS_DESIRED | WHERE_SEEK_TABLE, iIdxCur
   356    357       );
   357    358       if( pWInfo==0 ) goto update_cleanup;
   358    359       okOnePass = sqlite3WhereOkOnePass(pWInfo, aiCurOnePass);
   359    360     
   360    361       /* Remember the rowid of every item to be updated.
   361    362       */
   362    363       sqlite3VdbeAddOp2(v, OP_Rowid, iDataCur, regOldRowid);

Changes to src/where.c.

   257    257   
   258    258   /*
   259    259   ** Initialize a WHERE clause scanner object.  Return a pointer to the
   260    260   ** first match.  Return NULL if there are no matches.
   261    261   **
   262    262   ** The scanner will be searching the WHERE clause pWC.  It will look
   263    263   ** for terms of the form "X <op> <expr>" where X is column iColumn of table
   264         -** iCur.  The <op> must be one of the operators described by opMask.
          264  +** iCur.   Or if pIdx!=0 then X is column iColumn of index pIdx.  pIdx
          265  +** must be one of the indexes of table iCur.
          266  +**
          267  +** The <op> must be one of the operators described by opMask.
   265    268   **
   266    269   ** If the search is for X and the WHERE clause contains terms of the
   267    270   ** form X=Y then this routine might also return terms of the form
   268    271   ** "Y <op> <expr>".  The number of levels of transitivity is limited,
   269    272   ** but is enough to handle most commonly occurring SQL statements.
   270    273   **
   271    274   ** If X is not the INTEGER PRIMARY KEY then X must be compatible with
................................................................................
   305    308     pScan->nEquiv = 1;
   306    309     pScan->iEquiv = 1;
   307    310     return whereScanNext(pScan);
   308    311   }
   309    312   
   310    313   /*
   311    314   ** Search for a term in the WHERE clause that is of the form "X <op> <expr>"
   312         -** where X is a reference to the iColumn of table iCur and <op> is one of
   313         -** the WO_xx operator codes specified by the op parameter.
   314         -** Return a pointer to the term.  Return 0 if not found.
          315  +** where X is a reference to the iColumn of table iCur or of index pIdx
          316  +** if pIdx!=0 and <op> is one of the WO_xx operator codes specified by
          317  +** the op parameter.  Return a pointer to the term.  Return 0 if not found.
   315    318   **
   316         -** If pIdx!=0 then search for terms matching the iColumn-th column of pIdx
          319  +** If pIdx!=0 then it must be one of the indexes of table iCur.  
          320  +** Search for terms matching the iColumn-th column of pIdx
   317    321   ** rather than the iColumn-th column of table iCur.
   318    322   **
   319    323   ** The term returned might by Y=<expr> if there is another constraint in
   320    324   ** the WHERE clause that specifies that X=Y.  Any such constraints will be
   321    325   ** identified by the WO_EQUIV bit in the pTerm->eOperator field.  The
   322    326   ** aiCur[]/iaColumn[] arrays hold X and all its equivalents. There are 11
   323    327   ** slots in aiCur[]/aiColumn[] so that means we can look for X plus up to 10

Changes to src/wherecode.c.

  1320   1320   
  1321   1321       /* Seek the table cursor, if required */
  1322   1322       disableTerm(pLevel, pRangeStart);
  1323   1323       disableTerm(pLevel, pRangeEnd);
  1324   1324       if( omitTable ){
  1325   1325         /* pIdx is a covering index.  No need to access the main table. */
  1326   1326       }else if( HasRowid(pIdx->pTable) ){
  1327         -      if( pWInfo->eOnePass!=ONEPASS_OFF ){
         1327  +      if( (pWInfo->wctrlFlags & WHERE_SEEK_TABLE)!=0 ){
  1328   1328           iRowidReg = ++pParse->nMem;
  1329   1329           sqlite3VdbeAddOp2(v, OP_IdxRowid, iIdxCur, iRowidReg);
  1330   1330           sqlite3ExprCacheStore(pParse, iCur, -1, iRowidReg);
  1331   1331           sqlite3VdbeAddOp3(v, OP_NotExists, iCur, 0, iRowidReg);
  1332   1332           VdbeCoverage(v);
  1333   1333         }else{
  1334   1334           codeDeferredSeek(pWInfo, pIdx, iCur, iIdxCur);
................................................................................
  1516   1516       /* Run a separate WHERE clause for each term of the OR clause.  After
  1517   1517       ** eliminating duplicates from other WHERE clauses, the action for each
  1518   1518       ** sub-WHERE clause is to to invoke the main loop body as a subroutine.
  1519   1519       */
  1520   1520       wctrlFlags =  WHERE_OMIT_OPEN_CLOSE
  1521   1521                   | WHERE_FORCE_TABLE
  1522   1522                   | WHERE_ONETABLE_ONLY
  1523         -                | WHERE_NO_AUTOINDEX;
         1523  +                | WHERE_NO_AUTOINDEX
         1524  +                | (pWInfo->wctrlFlags & WHERE_SEEK_TABLE);
  1524   1525       for(ii=0; ii<pOrWc->nTerm; ii++){
  1525   1526         WhereTerm *pOrTerm = &pOrWc->a[ii];
  1526   1527         if( pOrTerm->leftCursor==iCur || (pOrTerm->eOperator & WO_AND)!=0 ){
  1527   1528           WhereInfo *pSubWInfo;           /* Info for single OR-term scan */
  1528   1529           Expr *pOrExpr = pOrTerm->pExpr; /* Current OR clause term */
  1529   1530           int jmp1 = 0;                   /* Address of jump operation */
  1530   1531           if( pAndExpr && !ExprHasProperty(pOrExpr, EP_FromJoin) ){

Changes to test/intpkey.test.

   610    610   do_execsql_test intpkey-16.0 {
   611    611     CREATE TABLE t16a(id "INTEGER" PRIMARY KEY AUTOINCREMENT, b [TEXT], c `INT`);
   612    612   } {}
   613    613   do_execsql_test intpkey-16.1 {
   614    614     PRAGMA table_info=t16a;
   615    615   } {0 id INTEGER 0 {} 1 1 b TEXT 0 {} 0 2 c INT 0 {} 0}
   616    616   
   617         -
          617  +# 2016-05-06 ticket https://www.sqlite.org/src/tktview/16c9801ceba4923939085
          618  +# When the schema contains an index on the IPK and no other index
          619  +# and a WHERE clause on a delete uses an OR where both sides referencing
          620  +# the IPK, then it is possible that the OP_Delete will fail because there
          621  +# deferred seek of the OP_Seek is not resolved prior to reaching the OP_Delete.
          622  +#
          623  +do_execsql_test intpkey-17.0 {
          624  +  CREATE TABLE t17(x INTEGER PRIMARY KEY, y TEXT);
          625  +  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
          626  +  CREATE INDEX t17x ON t17(x);
          627  +  DELETE FROM t17 WHERE x=99 OR x<130;
          628  +  SELECT * FROM t17;
          629  +} {248 giraffe}
          630  +do_execsql_test intpkey-17.1 {
          631  +  DROP INDEX t17x;
          632  +  DELETE FROM t17;
          633  +  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
          634  +  CREATE UNIQUE INDEX t17x ON t17(abs(x));
          635  +  DELETE FROM t17 WHERE abs(x) IS NULL OR abs(x)<130;
          636  +  SELECT * FROM t17;
          637  +} {248 giraffe}
          638  +do_execsql_test intpkey-17.2 {
          639  +  DELETE FROM t17;
          640  +  INSERT INTO t17(x,y) VALUES(123,'elephant'),(248,'giraffe');
          641  +  UPDATE t17 SET y='ostrich' WHERE abs(x)=248;
          642  +  SELECT * FROM t17 ORDER BY +x;
          643  +} {123 elephant 248 ostrich}
   618    644   
   619    645   finish_test

Changes to test/permutations.test.

   742    742     # Exclude stmt.test, which expects sub-journals to use temporary files.
   743    743     stmt.test symlink.test
   744    744   
   745    745     zerodamage.test
   746    746   
   747    747     # WAL mode is different.
   748    748     wal* tkt-2d1a5c67d.test backcompat.test e_wal* rowallock.test
          749  +
          750  +  # This test does not work as the "PRAGMA journal_mode = memory"
          751  +  # statement switches the database out of wal mode at inopportune
          752  +  # times.
          753  +  snapshot_fault.test
   749    754   }]
   750    755   
   751    756   ifcapable mem3 {
   752    757     test_suite "memsys3" -description {
   753    758       Run tests using the allocator in mem3.c.
   754    759     } -files [test_set $::allquicktests -exclude {
   755    760       autovacuum.test           delete3.test              manydb.test

Changes to test/shell1.test.

   910    910         } else {
   911    911           set got <empty>
   912    912         }
   913    913         error "failed with byte $hex mismatch, got $got"
   914    914       }
   915    915     }
   916    916   } {}
          917  +
          918  +# These test cases do not work on MinGW
          919  +if 0 {
   917    920   
   918    921   # The string used here is the word "test" in Chinese.
   919    922   # In UTF-8, it is encoded as: \xE6\xB5\x8B\xE8\xAF\x95
   920    923   set test \u6D4B\u8BD5
   921    924   
   922    925   do_test shell1-6.0 {
   923    926     set fileName $test; append fileName .db
................................................................................
   947    950       error "failed with error: $res"
   948    951     }
   949    952     if {$res ne "CREATE TABLE ${test}(x);"} {
   950    953       error "failed with mismatch: $res"
   951    954     }
   952    955     forcedelete test3.db
   953    956   } {}
          957  +}
   954    958   
   955    959   finish_test

Changes to test/snapshot.test.

   348    348     sqlite3 db2 test.db 
   349    349     db2 eval "PRAGMA application_id"
   350    350     db2 eval "BEGIN"
   351    351     sqlite3_snapshot_open db2 main $::snapshot
   352    352     db2 eval { SELECT * FROM x1 }
   353    353   } {z zz zzz}
   354    354   
   355         -# EVIDENCE-OF: R-55491-50411 A snapshot will fail to open if the
   356         -# database connection D has not previously completed at least one read
   357         -# operation against the database file.
   358         -#
   359    355   do_test 6.5 {
   360    356     db2 close
   361    357     sqlite3 db2 test.db 
   362    358     db2 eval "BEGIN"
   363    359     list [catch {sqlite3_snapshot_open db2 main $::snapshot} msg] $msg
   364    360   } {1 SQLITE_ERROR}
   365    361   

Changes to test/sort5.test.

    74     74   sqlite3 db test.db -vfs tvfs
    75     75   execsql { CREATE TABLE t1(x) }
    76     76   
    77     77   # Each iteration of the following loop attempts to sort 10001 records
    78     78   # each a bit over 100 bytes in size. In total a little more than 1MiB 
    79     79   # of data.
    80     80   #
    81         -breakpoint
    82     81   foreach {tn pgsz cachesz bTemp} {
    83         -  2 1024   1000  1
    84         -
    85     82     1 4096   1000  0
    86     83     2 1024   1000  1
    87     84   
    88     85     3 4096  -1000  1
    89     86     4 1024  -1000  1
    90     87   
    91     88     5 4096  -9000  0
................................................................................
    92     89     6 1024  -9000  0
    93     90   } {
    94     91     do_execsql_test 2.$tn.0 "
    95     92       PRAGMA page_size = $pgsz;
    96     93       VACUUM;
    97     94       PRAGMA cache_size = $cachesz;
    98     95     "
           96  +
           97  +  if {[db one {PRAGMA page_size}]!=$pgsz} {
           98  +    # SEE is not able to change page sizes and that messes up the
           99  +    # results that follow.
          100  +    continue
          101  +  }
    99    102   
   100    103     do_test 2.$tn.1 {
   101    104       set ::iTemp 0
   102    105       catch { array unset F }
   103    106       execsql {
   104    107         WITH x(i, j) AS (
   105    108           SELECT 1, randomblob(100)
................................................................................
   109    112         SELECT * FROM x ORDER BY j;
   110    113       }
   111    114       expr {[array names F]!=""}
   112    115     } $bTemp
   113    116   }
   114    117   
   115    118   finish_test
   116         -

Added test/temptable3.test.

            1  +# 2016-05-10
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +
           12  +set testdir [file dirname $argv0]
           13  +source $testdir/tester.tcl
           14  +set testprefix temptable3
           15  +
           16  +db close
           17  +sqlite3 db {}
           18  +do_execsql_test 1.1 {
           19  +  PRAGMA cache_size = 1;
           20  +  PRAGMA page_size = 1024;
           21  +  PRAGMA auto_vacuum = 2;
           22  +  CREATE TABLE t1(x);
           23  +  INSERT INTO t1 VALUES( randomblob(800) );
           24  +  INSERT INTO t1 VALUES( randomblob(800) );
           25  +  CREATE TABLE t2(x);
           26  +  PRAGMA integrity_check;
           27  +} {ok}
           28  +
           29  +db close
           30  +sqlite3 db {}
           31  +do_execsql_test 1.2 {
           32  +  PRAGMA cache_size = 1;
           33  +  PRAGMA auto_vacuum = 2;
           34  +  CREATE TABLE t1(x);
           35  +  CREATE TABLE t2(x UNIQUE);
           36  +  INSERT INTO t2 VALUES(1), (2), (3);
           37  +  DROP TABLE t1;
           38  +  PRAGMA integrity_check;
           39  +} {ok}
           40  +
           41  +finish_test

Changes to tool/spaceanal.tcl.

   148    148   # queries the in-memory db to produce the space-analysis report.
   149    149   #
   150    150   sqlite3 mem :memory:
   151    151   set tabledef {CREATE TABLE space_used(
   152    152      name clob,        -- Name of a table or index in the database file
   153    153      tblname clob,     -- Name of associated table
   154    154      is_index boolean, -- TRUE if it is an index, false for a table
          155  +   is_without_rowid boolean, -- TRUE if WITHOUT ROWID table  
   155    156      nentry int,       -- Number of entries in the BTree
   156    157      leaf_entries int, -- Number of leaf entries
   157    158      depth int,        -- Depth of the b-tree
   158    159      payload int,      -- Total amount of data stored in this table or index
   159    160      ovfl_payload int, -- Total amount of data stored on overflow pages
   160    161      ovfl_cnt int,     -- Number of entries that use overflow
   161    162      mx_payload int,   -- Maximum payload size
................................................................................
   180    181   set isCompressed 0
   181    182   set compressOverhead 0
   182    183   set depth 0
   183    184   set sql { SELECT name, tbl_name FROM sqlite_master WHERE rootpage>0 }
   184    185   foreach {name tblname} [concat sqlite_master sqlite_master [db eval $sql]] {
   185    186   
   186    187     set is_index [expr {$name!=$tblname}]
   187         -  set idx_btree [expr {$is_index || [is_without_rowid $name]}]
          188  +  set is_without_rowid [is_without_rowid $name]
   188    189     db eval {
   189    190       SELECT 
   190    191         sum(ncell) AS nentry,
   191    192         sum((pagetype=='leaf')*ncell) AS leaf_entries,
   192    193         sum(payload) AS payload,
   193    194         sum((pagetype=='overflow') * payload) AS ovfl_payload,
   194    195         sum(path LIKE '%+000000') AS ovfl_cnt,
................................................................................
   231    232       set prev $pageno
   232    233     }
   233    234     mem eval {
   234    235       INSERT INTO space_used VALUES(
   235    236         $name,
   236    237         $tblname,
   237    238         $is_index,
          239  +      $is_without_rowid,
   238    240         $nentry,
   239    241         $leaf_entries,
   240    242         $depth,
   241    243         $payload,     
   242    244         $ovfl_payload,
   243    245         $ovfl_cnt,   
   244    246         $mx_payload,
................................................................................
   326    328   
   327    329     # Query the in-memory database for the sum of various statistics 
   328    330     # for the subset of tables/indices identified by the WHERE clause in
   329    331     # $where. Note that even if the WHERE clause matches no rows, the
   330    332     # following query returns exactly one row (because it is an aggregate).
   331    333     #
   332    334     # The results of the query are stored directly by SQLite into local 
   333         -  # variables (i.e. $nentry, $nleaf etc.).
          335  +  # variables (i.e. $nentry, $payload etc.).
   334    336     #
   335    337     mem eval "
   336    338       SELECT
   337         -      int(sum(nentry)) AS nentry,
   338         -      int(sum(leaf_entries)) AS nleaf,
          339  +      int(sum(
          340  +        CASE WHEN (is_without_rowid OR is_index) THEN nentry 
          341  +             ELSE leaf_entries 
          342  +        END
          343  +      )) AS nentry,
   339    344         int(sum(payload)) AS payload,
   340    345         int(sum(ovfl_payload)) AS ovfl_payload,
   341    346         max(mx_payload) AS mx_payload,
   342    347         int(sum(ovfl_cnt)) as ovfl_cnt,
   343    348         int(sum(leaf_pages)) AS leaf_pages,
   344    349         int(sum(int_pages)) AS int_pages,
   345    350         int(sum(ovfl_pages)) AS ovfl_pages,
................................................................................
   371    376     # ovfl_cnt_percent: Percentage of btree entries that use overflow pages.
   372    377     #
   373    378     set total_pages [expr {$leaf_pages+$int_pages+$ovfl_pages}]
   374    379     set total_pages_percent [percent $total_pages $file_pgcnt]
   375    380     set storage [expr {$total_pages*$pageSize}]
   376    381     set payload_percent [percent $payload $storage {of storage consumed}]
   377    382     set total_unused [expr {$ovfl_unused+$int_unused+$leaf_unused}]
   378         -  set avg_payload [divide $payload $nleaf]
   379         -  set avg_unused [divide $total_unused $nleaf]
          383  +  set avg_payload [divide $payload $nentry]
          384  +  set avg_unused [divide $total_unused $nentry]
   380    385     if {$int_pages>0} {
   381    386       # TODO: Is this formula correct?
   382    387       set nTab [mem eval "
   383    388         SELECT count(*) FROM (
   384    389             SELECT DISTINCT tblname FROM space_used WHERE $where AND is_index=0
   385    390         )
   386    391       "]
   387    392       set avg_fanout [mem eval "
   388    393         SELECT (sum(leaf_pages+int_pages)-$nTab)/sum(int_pages) FROM space_used
   389    394             WHERE $where
   390    395       "]
   391    396       set avg_fanout [format %.2f $avg_fanout]
   392    397     }
   393         -  set ovfl_cnt_percent [percent $ovfl_cnt $nleaf {of all entries}]
          398  +  set ovfl_cnt_percent [percent $ovfl_cnt $nentry {of all entries}]
   394    399   
   395    400     # Print out the sub-report statistics.
   396    401     #
   397    402     statline {Percentage of total database} $total_pages_percent
   398         -  statline {Number of entries} $nleaf
          403  +  statline {Number of entries} $nentry
   399    404     statline {Bytes of storage consumed} $storage
   400    405     if {$compressed_size!=$storage} {
   401    406       set compressed_size [expr {$compressed_size+$compressOverhead*$total_pages}]
   402    407       set pct [expr {$compressed_size*100.0/$storage}]
   403    408       set pct [format {%5.1f%%} $pct]
   404    409       statline {Bytes used after compression} $compressed_size $pct
   405    410     }