SQLite

Changes On Branch tempfiles-lazy-open
Login

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

Changes In Branch tempfiles-lazy-open Excluding Merge-Ins

This is equivalent to a diff from 68142dc5 to e2edd34e

2016-04-13
21:00
Fix compiler warnings by removing surplus local variables in the schema pretty-printer logic of the command-line shell. (check-in: 55a62483 user: drh tags: trunk)
19:20
Merge enhancements from trunk via tempfiles-lazy-open. (check-in: ae16310c user: drh tags: tempfiles-25)
16:02
Merge enhancements from trunk. (Closed-Leaf check-in: e2edd34e user: drh tags: tempfiles-lazy-open)
15:52
Fixes for harmless compiler warnings. (check-in: 68142dc5 user: drh tags: trunk)
15:33
Add the --indent option to the .schema and .fullschema commands in the shell, to enable simple but effective pretty-printing. (check-in: 83cfe82c user: drh tags: trunk)
2016-04-12
20:26
A couple more compiler warning fixes. (Closed-Leaf check-in: 929fa4c3 user: mistachkin tags: warnings)
19:09
Once a temporary database file has been opened, flush all dirty pages to disk when comitting a transaction. (check-in: bbac71aa user: dan tags: tempfiles-lazy-open)

Changes to src/pager.c.

954
955
956
957
958
959
960
961
962
963
964
965
966
967
968

    case PAGER_ERROR:
      /* There must be at least one outstanding reference to the pager if
      ** in ERROR state. Otherwise the pager should have already dropped
      ** back to OPEN state.
      */
      assert( pPager->errCode!=SQLITE_OK );
      assert( sqlite3PcacheRefCount(pPager->pPCache)>0 );
      break;
  }

  return 1;
}
#endif /* ifndef NDEBUG */








|







954
955
956
957
958
959
960
961
962
963
964
965
966
967
968

    case PAGER_ERROR:
      /* There must be at least one outstanding reference to the pager if
      ** in ERROR state. Otherwise the pager should have already dropped
      ** back to OPEN state.
      */
      assert( pPager->errCode!=SQLITE_OK );
      assert( sqlite3PcacheRefCount(pPager->pPCache)>0 || pPager->tempFile );
      break;
  }

  return 1;
}
#endif /* ifndef NDEBUG */

1166
1167
1168
1169
1170
1171
1172


1173
1174
1175
1176
1177
1178
1179
    if( 0==(dc&(SQLITE_IOCAP_ATOMIC|(szPage>>8)) || nSector>szPage) ){
      return 0;
    }
  }

  return JOURNAL_HDR_SZ(pPager) + JOURNAL_PG_SZ(pPager);
}


#endif

/*
** If SQLITE_CHECK_PAGES is defined then we do some sanity checking
** on the cache using a hash function.  This is used for testing
** and debugging only.
*/







>
>







1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
    if( 0==(dc&(SQLITE_IOCAP_ATOMIC|(szPage>>8)) || nSector>szPage) ){
      return 0;
    }
  }

  return JOURNAL_HDR_SZ(pPager) + JOURNAL_PG_SZ(pPager);
}
#else
# define jrnlBufferSize(x) 0
#endif

/*
** If SQLITE_CHECK_PAGES is defined then we do some sanity checking
** on the cache using a hash function.  This is used for testing
** and debugging only.
*/
1813
1814
1815
1816
1817
1818
1819
1820




1821
1822
1823
1824
1825
1826
1827
1828
1829
1830
1831
    pPager->eState = PAGER_OPEN;
  }

  /* If Pager.errCode is set, the contents of the pager cache cannot be
  ** trusted. Now that there are no outstanding references to the pager,
  ** it can safely move back to PAGER_OPEN state. This happens in both
  ** normal and exclusive-locking mode.
  */




  if( pPager->errCode ){
    assert( !MEMDB );
    pager_reset(pPager);
    pPager->changeCountDone = pPager->tempFile;
    pPager->eState = PAGER_OPEN;
    pPager->errCode = SQLITE_OK;
    if( USEFETCH(pPager) ) sqlite3OsUnfetch(pPager->fd, 0, 0);
  }

  pPager->journalOff = 0;
  pPager->journalHdr = 0;







|
>
>
>
>
|
<

|







1815
1816
1817
1818
1819
1820
1821
1822
1823
1824
1825
1826
1827

1828
1829
1830
1831
1832
1833
1834
1835
1836
    pPager->eState = PAGER_OPEN;
  }

  /* If Pager.errCode is set, the contents of the pager cache cannot be
  ** trusted. Now that there are no outstanding references to the pager,
  ** it can safely move back to PAGER_OPEN state. This happens in both
  ** normal and exclusive-locking mode.
  **
  ** Exception: There is no way out of the error state for temp files.
  ** This is because it is not possible to call pager_reset() on a temp
  ** file pager (as this may discard the only copy of some data).  */
  assert( pPager->errCode==SQLITE_OK || !MEMDB );
  if( pPager->tempFile==0 && pPager->errCode ){

    pager_reset(pPager);
    pPager->changeCountDone = 0;
    pPager->eState = PAGER_OPEN;
    pPager->errCode = SQLITE_OK;
    if( USEFETCH(pPager) ) sqlite3OsUnfetch(pPager->fd, 0, 0);
  }

  pPager->journalOff = 0;
  pPager->journalHdr = 0;
2002
2003
2004
2005
2006
2007
2008

2009



2010
2011
2012
2013
2014
2015
2016
    }
  }
#endif

  sqlite3BitvecDestroy(pPager->pInJournal);
  pPager->pInJournal = 0;
  pPager->nRec = 0;

  sqlite3PcacheCleanAll(pPager->pPCache);



  sqlite3PcacheTruncate(pPager->pPCache, pPager->dbSize);

  if( pagerUseWal(pPager) ){
    /* Drop the WAL write-lock, if any. Also, if the connection was in 
    ** locking_mode=exclusive mode but is no longer, drop the EXCLUSIVE 
    ** lock held on the database file.
    */







>
|
>
>
>







2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
    }
  }
#endif

  sqlite3BitvecDestroy(pPager->pInJournal);
  pPager->pInJournal = 0;
  pPager->nRec = 0;
  if( isOpen(pPager->fd) || MEMDB ){
    sqlite3PcacheCleanAll(pPager->pPCache);
  }else{
    sqlite3PcacheClearWritable(pPager->pPCache);
  }
  sqlite3PcacheTruncate(pPager->pPCache, pPager->dbSize);

  if( pagerUseWal(pPager) ){
    /* Drop the WAL write-lock, if any. Also, if the connection was in 
    ** locking_mode=exclusive mode but is no longer, drop the EXCLUSIVE 
    ** lock held on the database file.
    */
2370
2371
2372
2373
2374
2375
2376




2377
2378
2379
2380
2381
2382
2383
2384
2385
2386
      ** already in the journal file (recorded in Pager.pInJournal) and
      ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
      ** again within this transaction, it will be marked as dirty but
      ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
      ** be written out into the database file before its journal file
      ** segment is synced. If a crash occurs during or following this,
      ** database corruption may ensue.




      */
      assert( !pagerUseWal(pPager) );
      sqlite3PcacheMakeClean(pPg);
    }
    pager_set_pagehash(pPg);

    /* If this was page 1, then restore the value of Pager.dbFileVers.
    ** Do this before any decoding. */
    if( pgno==1 ){
      memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers));







>
>
>
>


|







2379
2380
2381
2382
2383
2384
2385
2386
2387
2388
2389
2390
2391
2392
2393
2394
2395
2396
2397
2398
2399
      ** already in the journal file (recorded in Pager.pInJournal) and
      ** the PGHDR_NEED_SYNC flag is cleared, if the page is written to
      ** again within this transaction, it will be marked as dirty but
      ** the PGHDR_NEED_SYNC flag will not be set. It could then potentially
      ** be written out into the database file before its journal file
      ** segment is synced. If a crash occurs during or following this,
      ** database corruption may ensue.
      **
      ** Update: Another exception is for temp files that are not 
      ** in-memory databases. In this case the page may have been dirty
      ** at the start of the transaction.
      */
      assert( !pagerUseWal(pPager) );
      if( pPager->tempFile==0 ) sqlite3PcacheMakeClean(pPg);
    }
    pager_set_pagehash(pPg);

    /* If this was page 1, then restore the value of Pager.dbFileVers.
    ** Do this before any decoding. */
    if( pgno==1 ){
      memcpy(&pPager->dbFileVers, &((u8*)pData)[24],sizeof(pPager->dbFileVers));
4254
4255
4256
4257
4258
4259
4260
4261
4262

4263
4264
4265
4266
4267
4268
4269
** be obtained, SQLITE_BUSY is returned.
*/
static int pager_write_pagelist(Pager *pPager, PgHdr *pList){
  int rc = SQLITE_OK;                  /* Return code */

  /* This function is only called for rollback pagers in WRITER_DBMOD state. */
  assert( !pagerUseWal(pPager) );
  assert( pPager->eState==PAGER_WRITER_DBMOD );
  assert( pPager->eLock==EXCLUSIVE_LOCK );


  /* If the file is a temp-file has not yet been opened, open it now. It
  ** is not possible for rc to be other than SQLITE_OK if this branch
  ** is taken, as pager_wait_on_lock() is a no-op for temp-files.
  */
  if( !isOpen(pPager->fd) ){
    assert( pPager->tempFile && rc==SQLITE_OK );







|

>







4267
4268
4269
4270
4271
4272
4273
4274
4275
4276
4277
4278
4279
4280
4281
4282
4283
** be obtained, SQLITE_BUSY is returned.
*/
static int pager_write_pagelist(Pager *pPager, PgHdr *pList){
  int rc = SQLITE_OK;                  /* Return code */

  /* This function is only called for rollback pagers in WRITER_DBMOD state. */
  assert( !pagerUseWal(pPager) );
  assert( pPager->tempFile || pPager->eState==PAGER_WRITER_DBMOD );
  assert( pPager->eLock==EXCLUSIVE_LOCK );
  assert( isOpen(pPager->fd) || pList->pDirty==0 );

  /* If the file is a temp-file has not yet been opened, open it now. It
  ** is not possible for rc to be other than SQLITE_OK if this branch
  ** is taken, as pager_wait_on_lock() is a no-op for temp-files.
  */
  if( !isOpen(pPager->fd) ){
    assert( pPager->tempFile && rc==SQLITE_OK );
5019
5020
5021
5022
5023
5024
5025

5026
5027
5028
5029
5030
5031
5032
5033
5034
  /* This routine is only called from b-tree and only when there are no
  ** outstanding pages. This implies that the pager state should either
  ** be OPEN or READER. READER is only possible if the pager is or was in 
  ** exclusive access mode.
  */
  assert( sqlite3PcacheRefCount(pPager->pPCache)==0 );
  assert( assert_pager_state(pPager) );

  assert( pPager->eState==PAGER_OPEN || pPager->eState==PAGER_READER );
  if( NEVER(MEMDB && pPager->errCode) ){ return pPager->errCode; }

  if( !pagerUseWal(pPager) && pPager->eState==PAGER_OPEN ){
    int bHotJournal = 1;          /* True if there exists a hot journal-file */

    assert( !MEMDB );

    rc = pager_wait_on_lock(pPager, SHARED_LOCK);







>

<







5033
5034
5035
5036
5037
5038
5039
5040
5041

5042
5043
5044
5045
5046
5047
5048
  /* This routine is only called from b-tree and only when there are no
  ** outstanding pages. This implies that the pager state should either
  ** be OPEN or READER. READER is only possible if the pager is or was in 
  ** exclusive access mode.
  */
  assert( sqlite3PcacheRefCount(pPager->pPCache)==0 );
  assert( assert_pager_state(pPager) );
  if( pPager->tempFile && pPager->errCode ) { return pPager->errCode; }
  assert( pPager->eState==PAGER_OPEN || pPager->eState==PAGER_READER );


  if( !pagerUseWal(pPager) && pPager->eState==PAGER_OPEN ){
    int bHotJournal = 1;          /* True if there exists a hot journal-file */

    assert( !MEMDB );

    rc = pager_wait_on_lock(pPager, SHARED_LOCK);
5549
5550
5551
5552
5553
5554
5555
5556
5557


5558
5559


5560
5561
5562

5563
5564
5565
5566
5567
5568
5569
5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
    }
  
    /* Open the journal file if it is not already open. */
    if( !isOpen(pPager->jfd) ){
      if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY ){
        sqlite3MemJournalOpen(pPager->jfd);
      }else{
        const int flags =                   /* VFS flags to open journal file */
          SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE|


          (pPager->tempFile ? 
            (SQLITE_OPEN_DELETEONCLOSE|SQLITE_OPEN_TEMP_JOURNAL):


            (SQLITE_OPEN_MAIN_JOURNAL)
          );


        /* Verify that the database still has the same name as it did when
        ** it was originally opened. */
        rc = databaseIsUnmoved(pPager);
        if( rc==SQLITE_OK ){
#ifdef SQLITE_ENABLE_ATOMIC_WRITE
          rc = sqlite3JournalOpen(
              pVfs, pPager->zJournal, pPager->jfd, flags, jrnlBufferSize(pPager)
          );
#else
          rc = sqlite3OsOpen(pVfs, pPager->zJournal, pPager->jfd, flags, 0);
#endif
        }
      }
      assert( rc!=SQLITE_OK || isOpen(pPager->jfd) );
    }
  
  
    /* Write the first journal header to the journal file and open 







<
|
>
>
|
|
>
>
|
|
|
>




<
|
|

<
<
<







5563
5564
5565
5566
5567
5568
5569

5570
5571
5572
5573
5574
5575
5576
5577
5578
5579
5580
5581
5582
5583
5584

5585
5586
5587



5588
5589
5590
5591
5592
5593
5594
    }
  
    /* Open the journal file if it is not already open. */
    if( !isOpen(pPager->jfd) ){
      if( pPager->journalMode==PAGER_JOURNALMODE_MEMORY ){
        sqlite3MemJournalOpen(pPager->jfd);
      }else{

        int flags = SQLITE_OPEN_READWRITE|SQLITE_OPEN_CREATE;
        int nSpill;

        if( pPager->tempFile ){
          flags |= (SQLITE_OPEN_DELETEONCLOSE|SQLITE_OPEN_TEMP_JOURNAL);
          nSpill = sqlite3Config.nStmtSpill;
        }else{
          flags |= SQLITE_OPEN_MAIN_JOURNAL;
          nSpill = jrnlBufferSize(pPager);
        }
          
        /* Verify that the database still has the same name as it did when
        ** it was originally opened. */
        rc = databaseIsUnmoved(pPager);
        if( rc==SQLITE_OK ){

          rc = sqlite3JournalOpen (
              pVfs, pPager->zJournal, pPager->jfd, flags, nSpill
          );



        }
      }
      assert( rc!=SQLITE_OK || isOpen(pPager->jfd) );
    }
  
  
    /* Write the first journal header to the journal file and open 
5937
5938
5939
5940
5941
5942
5943

5944
5945
5946
5947
5948
5949
5950
  assert( assert_pager_state(pPager) );
  if( pPager->errCode ){
    return pPager->errCode;
  }else if( (pPg->flags & PGHDR_WRITEABLE)!=0 && pPager->dbSize>=pPg->pgno ){
    if( pPager->nSavepoint ) return subjournalPageIfRequired(pPg);
    return SQLITE_OK;
  }else if( pPager->sectorSize > (u32)pPager->pageSize ){

    return pagerWriteLargeSector(pPg);
  }else{
    return pager_write(pPg);
  }
}

/*







>







5951
5952
5953
5954
5955
5956
5957
5958
5959
5960
5961
5962
5963
5964
5965
  assert( assert_pager_state(pPager) );
  if( pPager->errCode ){
    return pPager->errCode;
  }else if( (pPg->flags & PGHDR_WRITEABLE)!=0 && pPager->dbSize>=pPg->pgno ){
    if( pPager->nSavepoint ) return subjournalPageIfRequired(pPg);
    return SQLITE_OK;
  }else if( pPager->sectorSize > (u32)pPager->pageSize ){
    assert( pPager->tempFile==0 );
    return pagerWriteLargeSector(pPg);
  }else{
    return pager_write(pPg);
  }
}

/*
6175
6176
6177
6178
6179
6180
6181
6182


6183
6184
6185
6186
6187
6188
6189
6190
6191
6192
6193

  PAGERTRACE(("DATABASE SYNC: File=%s zMaster=%s nSize=%d\n", 
      pPager->zFilename, zMaster, pPager->dbSize));

  /* If no database changes have been made, return early. */
  if( pPager->eState<PAGER_WRITER_CACHEMOD ) return SQLITE_OK;

  if( MEMDB ){


    /* If this is an in-memory db, or no pages have been written to, or this
    ** function has already been called, it is mostly a no-op.  However, any
    ** backup in progress needs to be restarted.
    */
    sqlite3BackupRestart(pPager->pBackup);
  }else{
    if( pagerUseWal(pPager) ){
      PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
      PgHdr *pPageOne = 0;
      if( pList==0 ){
        /* Must have at least one page for the WAL commit flag.







|
>
>


|
<







6190
6191
6192
6193
6194
6195
6196
6197
6198
6199
6200
6201
6202

6203
6204
6205
6206
6207
6208
6209

  PAGERTRACE(("DATABASE SYNC: File=%s zMaster=%s nSize=%d\n", 
      pPager->zFilename, zMaster, pPager->dbSize));

  /* If no database changes have been made, return early. */
  if( pPager->eState<PAGER_WRITER_CACHEMOD ) return SQLITE_OK;

  assert( MEMDB==0 || pPager->tempFile );
  assert( isOpen(pPager->fd) || pPager->tempFile );
  if( !isOpen(pPager->fd) ){
    /* If this is an in-memory db, or no pages have been written to, or this
    ** function has already been called, it is mostly a no-op.  However, any
    ** backup in progress needs to be restarted.  */

    sqlite3BackupRestart(pPager->pBackup);
  }else{
    if( pagerUseWal(pPager) ){
      PgHdr *pList = sqlite3PcacheDirtyList(pPager->pPCache);
      PgHdr *pPageOne = 0;
      if( pList==0 ){
        /* Must have at least one page for the WAL commit flag.
6518
6519
6520
6521
6522
6523
6524
6525
6526
6527
6528
6529
6530
6531
6532
6533
6534
6535
  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
  if( reset ){
    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
  }
}

/*
** Return true if this is an in-memory pager.
*/
int sqlite3PagerIsMemdb(Pager *pPager){
  return MEMDB;
}

/*
** Check that there are at least nSavepoint savepoints open. If there are
** currently less than nSavepoints open, then open one or more savepoints
** to make up the difference. If the number of savepoints is already
** equal to nSavepoint, then this function is a no-op.







|


|







6534
6535
6536
6537
6538
6539
6540
6541
6542
6543
6544
6545
6546
6547
6548
6549
6550
6551
  *pnVal += pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT];
  if( reset ){
    pPager->aStat[eStat - SQLITE_DBSTATUS_CACHE_HIT] = 0;
  }
}

/*
** Return true if this is an in-memory or temp-file backed pager.
*/
int sqlite3PagerIsMemdb(Pager *pPager){
  return pPager->tempFile;
}

/*
** Check that there are at least nSavepoint savepoints open. If there are
** currently less than nSavepoints open, then open one or more savepoints
** to make up the difference. If the number of savepoints is already
** equal to nSavepoint, then this function is a no-op.
6801
6802
6803
6804
6805
6806
6807
6808
6809
6810
6811
6812
6813
6814
6815
       || pPager->eState==PAGER_WRITER_DBMOD
  );
  assert( assert_pager_state(pPager) );

  /* In order to be able to rollback, an in-memory database must journal
  ** the page we are moving from.
  */
  if( MEMDB ){
    rc = sqlite3PagerWrite(pPg);
    if( rc ) return rc;
  }

  /* If the page being moved is dirty and has not been saved by the latest
  ** savepoint, then save the current contents of the page into the 
  ** sub-journal now. This is required to handle the following scenario:







|







6817
6818
6819
6820
6821
6822
6823
6824
6825
6826
6827
6828
6829
6830
6831
       || pPager->eState==PAGER_WRITER_DBMOD
  );
  assert( assert_pager_state(pPager) );

  /* In order to be able to rollback, an in-memory database must journal
  ** the page we are moving from.
  */
  if( pPager->tempFile ){
    rc = sqlite3PagerWrite(pPg);
    if( rc ) return rc;
  }

  /* If the page being moved is dirty and has not been saved by the latest
  ** savepoint, then save the current contents of the page into the 
  ** sub-journal now. This is required to handle the following scenario:
6858
6859
6860
6861
6862
6863
6864
6865
6866
6867
6868
6869
6870
6871
6872
6873
6874
6875
6876
6877
6878
6879
6880
6881
6882
6883
6884
6885
6886
6887
6888
6889
  ** for the page moved there.
  */
  pPg->flags &= ~PGHDR_NEED_SYNC;
  pPgOld = sqlite3PagerLookup(pPager, pgno);
  assert( !pPgOld || pPgOld->nRef==1 );
  if( pPgOld ){
    pPg->flags |= (pPgOld->flags&PGHDR_NEED_SYNC);
    if( MEMDB ){
      /* Do not discard pages from an in-memory database since we might
      ** need to rollback later.  Just move the page out of the way. */
      sqlite3PcacheMove(pPgOld, pPager->dbSize+1);
    }else{
      sqlite3PcacheDrop(pPgOld);
    }
  }

  origPgno = pPg->pgno;
  sqlite3PcacheMove(pPg, pgno);
  sqlite3PcacheMakeDirty(pPg);

  /* For an in-memory database, make sure the original page continues
  ** to exist, in case the transaction needs to roll back.  Use pPgOld
  ** as the original page since it has already been allocated.
  */
  if( MEMDB ){
    assert( pPgOld );
    sqlite3PcacheMove(pPgOld, origPgno);
    sqlite3PagerUnrefNotNull(pPgOld);
  }

  if( needSyncPgno ){
    /* If needSyncPgno is non-zero, then the journal file needs to be 







|
















|







6874
6875
6876
6877
6878
6879
6880
6881
6882
6883
6884
6885
6886
6887
6888
6889
6890
6891
6892
6893
6894
6895
6896
6897
6898
6899
6900
6901
6902
6903
6904
6905
  ** for the page moved there.
  */
  pPg->flags &= ~PGHDR_NEED_SYNC;
  pPgOld = sqlite3PagerLookup(pPager, pgno);
  assert( !pPgOld || pPgOld->nRef==1 );
  if( pPgOld ){
    pPg->flags |= (pPgOld->flags&PGHDR_NEED_SYNC);
    if( pPager->tempFile ){
      /* Do not discard pages from an in-memory database since we might
      ** need to rollback later.  Just move the page out of the way. */
      sqlite3PcacheMove(pPgOld, pPager->dbSize+1);
    }else{
      sqlite3PcacheDrop(pPgOld);
    }
  }

  origPgno = pPg->pgno;
  sqlite3PcacheMove(pPg, pgno);
  sqlite3PcacheMakeDirty(pPg);

  /* For an in-memory database, make sure the original page continues
  ** to exist, in case the transaction needs to roll back.  Use pPgOld
  ** as the original page since it has already been allocated.
  */
  if( pPager->tempFile ){
    assert( pPgOld );
    sqlite3PcacheMove(pPgOld, origPgno);
    sqlite3PagerUnrefNotNull(pPgOld);
  }

  if( needSyncPgno ){
    /* If needSyncPgno is non-zero, then the journal file needs to be 
7128
7129
7130
7131
7132
7133
7134

7135
7136
7137
7138
7139
7140
7141
7142
}

#ifndef SQLITE_OMIT_VACUUM
/*
** Unless this is an in-memory or temporary database, clear the pager cache.
*/
void sqlite3PagerClearCache(Pager *pPager){

  if( !MEMDB && pPager->tempFile==0 ) pager_reset(pPager);
}
#endif

#ifndef SQLITE_OMIT_WAL
/*
** This function is called when the user invokes "PRAGMA wal_checkpoint",
** "PRAGMA wal_blocking_checkpoint" or calls the sqlite3_wal_checkpoint()







>
|







7144
7145
7146
7147
7148
7149
7150
7151
7152
7153
7154
7155
7156
7157
7158
7159
}

#ifndef SQLITE_OMIT_VACUUM
/*
** Unless this is an in-memory or temporary database, clear the pager cache.
*/
void sqlite3PagerClearCache(Pager *pPager){
  assert( MEMDB==0 || pPager->tempFile );
  if( pPager->tempFile==0 ) pager_reset(pPager);
}
#endif

#ifndef SQLITE_OMIT_WAL
/*
** This function is called when the user invokes "PRAGMA wal_checkpoint",
** "PRAGMA wal_blocking_checkpoint" or calls the sqlite3_wal_checkpoint()

Changes to src/pcache.c.

250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
  assert( createFlag==0 || pCache->eCreate==eCreate );
  assert( createFlag==0 || eCreate==1+(!pCache->bPurgeable||!pCache->pDirty) );
  return sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, eCreate);
}

/*
** If the sqlite3PcacheFetch() routine is unable to allocate a new
** page because new clean pages are available for reuse and the cache
** size limit has been reached, then this routine can be invoked to 
** try harder to allocate a page.  This routine might invoke the stress
** callback to spill dirty pages to the journal.  It will then try to
** allocate the new page and will only fail to allocate a new page on
** an OOM error.
**
** This routine should be invoked only after sqlite3PcacheFetch() fails.







|







250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
  assert( createFlag==0 || pCache->eCreate==eCreate );
  assert( createFlag==0 || eCreate==1+(!pCache->bPurgeable||!pCache->pDirty) );
  return sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache, pgno, eCreate);
}

/*
** If the sqlite3PcacheFetch() routine is unable to allocate a new
** page because no clean pages are available for reuse and the cache
** size limit has been reached, then this routine can be invoked to 
** try harder to allocate a page.  This routine might invoke the stress
** callback to spill dirty pages to the journal.  It will then try to
** allocate the new page and will only fail to allocate a new page on
** an OOM error.
**
** This routine should be invoked only after sqlite3PcacheFetch() fails.
434
435
436
437
438
439
440











441
442
443
444
445
446
447
*/
void sqlite3PcacheCleanAll(PCache *pCache){
  PgHdr *p;
  while( (p = pCache->pDirty)!=0 ){
    sqlite3PcacheMakeClean(p);
  }
}












/*
** Clear the PGHDR_NEED_SYNC flag from all dirty pages.
*/
void sqlite3PcacheClearSyncFlags(PCache *pCache){
  PgHdr *p;
  for(p=pCache->pDirty; p; p=p->pDirtyNext){







>
>
>
>
>
>
>
>
>
>
>







434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
*/
void sqlite3PcacheCleanAll(PCache *pCache){
  PgHdr *p;
  while( (p = pCache->pDirty)!=0 ){
    sqlite3PcacheMakeClean(p);
  }
}

/*
** Clear the PGHDR_NEED_SYNC and PGHDR_WRITEABLE flag from all dirty pages.
*/
void sqlite3PcacheClearWritable(PCache *pCache){
  PgHdr *p;
  for(p=pCache->pDirty; p; p=p->pDirtyNext){
    p->flags &= ~(PGHDR_NEED_SYNC|PGHDR_WRITEABLE);
  }
  pCache->pSynced = pCache->pDirtyTail;
}

/*
** Clear the PGHDR_NEED_SYNC flag from all dirty pages.
*/
void sqlite3PcacheClearSyncFlags(PCache *pCache){
  PgHdr *p;
  for(p=pCache->pDirty; p; p=p->pDirtyNext){
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
    for(p=pCache->pDirty; p; p=pNext){
      pNext = p->pDirtyNext;
      /* This routine never gets call with a positive pgno except right
      ** after sqlite3PcacheCleanAll().  So if there are dirty pages,
      ** it must be that pgno==0.
      */
      assert( p->pgno>0 );
      if( ALWAYS(p->pgno>pgno) ){
        assert( p->flags&PGHDR_DIRTY );
        sqlite3PcacheMakeClean(p);
      }
    }
    if( pgno==0 && pCache->nRefSum ){
      sqlite3_pcache_page *pPage1;
      pPage1 = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache,1,0);







|







491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
    for(p=pCache->pDirty; p; p=pNext){
      pNext = p->pDirtyNext;
      /* This routine never gets call with a positive pgno except right
      ** after sqlite3PcacheCleanAll().  So if there are dirty pages,
      ** it must be that pgno==0.
      */
      assert( p->pgno>0 );
      if( p->pgno>pgno ){
        assert( p->flags&PGHDR_DIRTY );
        sqlite3PcacheMakeClean(p);
      }
    }
    if( pgno==0 && pCache->nRefSum ){
      sqlite3_pcache_page *pPage1;
      pPage1 = sqlite3GlobalConfig.pcache2.xFetch(pCache->pCache,1,0);

Changes to src/test3.c.

543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559

560
561
562
563
564
565
566
567
568
569

570
571
572
573
574
575
576
  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
  return TCL_OK;
}

/*
** Usage:   btree_ismemdb ID
**
** Return true if the B-Tree is in-memory.
*/
static int btree_ismemdb(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  int res;


  if( argc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TestTextToPtr(argv[1]);
  sqlite3_mutex_enter(pBt->db->mutex);
  sqlite3BtreeEnter(pBt);
  res = sqlite3PagerIsMemdb(sqlite3BtreePager(pBt));

  sqlite3BtreeLeave(pBt);
  sqlite3_mutex_leave(pBt->db->mutex);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(res));
  return SQLITE_OK;
}

/*







|









>









|
>







543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
  Tcl_SetResult(interp, zBuf, TCL_VOLATILE);
  return TCL_OK;
}

/*
** Usage:   btree_ismemdb ID
**
** Return true if the B-Tree is currently stored entirely in memory.
*/
static int btree_ismemdb(
  void *NotUsed,
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int argc,              /* Number of arguments */
  const char **argv      /* Text of each argument */
){
  Btree *pBt;
  int res;
  sqlite3_file *pFile;

  if( argc!=2 ){
    Tcl_AppendResult(interp, "wrong # args: should be \"", argv[0],
       " ID\"", 0);
    return TCL_ERROR;
  }
  pBt = sqlite3TestTextToPtr(argv[1]);
  sqlite3_mutex_enter(pBt->db->mutex);
  sqlite3BtreeEnter(pBt);
  pFile = sqlite3PagerFile(sqlite3BtreePager(pBt));
  res = (pFile->pMethods==0);
  sqlite3BtreeLeave(pBt);
  sqlite3_mutex_leave(pBt->db->mutex);
  Tcl_SetObjResult(interp, Tcl_NewBooleanObj(res));
  return SQLITE_OK;
}

/*

Changes to test/cffault.test.

11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#
# This file contains fault-injection test cases for the 
# sqlite3_db_cacheflush API.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix cacheflush
source $testdir/malloc_common.tcl

# Run the supplied SQL on a copy of the database currently stored on 
# disk in file $dbfile.
proc diskquery {dbfile sql} {
  forcecopy $dbfile dq.db
  sqlite3 dq dq.db







|







11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
#
# This file contains fault-injection test cases for the 
# sqlite3_db_cacheflush API.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix cffault
source $testdir/malloc_common.tcl

# Run the supplied SQL on a copy of the database currently stored on 
# disk in file $dbfile.
proc diskquery {dbfile sql} {
  forcecopy $dbfile dq.db
  sqlite3 dq dq.db

Changes to test/lock.test.

419
420
421
422
423
424
425
426
427

428
429
430
431
432
433
434
# At one point the following set of conditions would cause SQLite to 
# retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
# 
#   * The journal-mode is set to something other than 'delete', and
#   * there exists one or more active read-only statements, and
#   * a transaction that modified zero database pages is committed.
# 
set temp_status unlocked
if {$TEMP_STORE>=2} {set temp_status unknown}

do_test lock-7.1 {
  set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
  sqlite3_step $STMT
} {SQLITE_ROW}
do_test lock-7.2 {
  execsql { PRAGMA lock_status }
} [list main shared temp $temp_status]







|
|
>







419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
# At one point the following set of conditions would cause SQLite to 
# retain a RESERVED or EXCLUSIVE lock after the transaction was committed:
# 
#   * The journal-mode is set to something other than 'delete', and
#   * there exists one or more active read-only statements, and
#   * a transaction that modified zero database pages is committed.
# 
#set temp_status unlocked
#if {$TEMP_STORE>=2} {set temp_status unknown}
set temp_status unknown
do_test lock-7.1 {
  set STMT [sqlite3_prepare $DB "SELECT * FROM sqlite_master" -1 TAIL]
  sqlite3_step $STMT
} {SQLITE_ROW}
do_test lock-7.2 {
  execsql { PRAGMA lock_status }
} [list main shared temp $temp_status]

Changes to test/pragma.test.

1079
1080
1081
1082
1083
1084
1085


1086











1087
1088
1089
1090
1091
1092
1093
} {-450}
} ; # ifcapable schema_version

# Check to see if TEMP_STORE is memory or disk.  Return strings
# "memory" or "disk" as appropriate.
#
proc check_temp_store {} {


  db eval {CREATE TEMP TABLE IF NOT EXISTS a(b)}











  db eval {PRAGMA database_list} {
    if {$name=="temp"} {
      set bt [btree_from_db db 1]
      if {[btree_ismemdb $bt]} {
        return "memory"
      }
      return "disk"







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







1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
} {-450}
} ; # ifcapable schema_version

# Check to see if TEMP_STORE is memory or disk.  Return strings
# "memory" or "disk" as appropriate.
#
proc check_temp_store {} {
  db eval {
    PRAGMA temp.cache_size = 1;
    CREATE TEMP TABLE IF NOT EXISTS a(b);
    DELETE FROM a;
    INSERT INTO a VALUES(randomblob(1000));
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
    INSERT INTO a SELECT * FROM a;
  }
  db eval {PRAGMA database_list} {
    if {$name=="temp"} {
      set bt [btree_from_db db 1]
      if {[btree_ismemdb $bt]} {
        return "memory"
      }
      return "disk"

Added test/tempfault.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
# 2016 April 11
#
# 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 contains tests for fault-injection when SQLite is used with
# a temp file database.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/malloc_common.tcl
set testprefix tempfault

sqlite3_memdebug_vfs_oom_test 0

do_faultsim_test 1 -faults oom* -prep {
  sqlite3 db ""
  db eval {
    PRAGMA page_size = 1024;
    CREATE TABLE t1(a, b);
    INSERT INTO t1 VALUES(1, 2);
    INSERT INTO t1 VALUES(3, 4);
  }
} -body {
  execsql { INSERT INTO t1 VALUES(5, 6) }
} -test {
  faultsim_test_result {0 {}}
  set rc [catch { execsql { SELECT * FROM t1 } } msg]
  if {$rc==0 && $msg != "1 2 3 4 5 6" && $msg != "1 2 3 4"} {
    error "data mismatch 1: $msg"
  }
  if {$testrc==0 && $msg != "1 2 3 4 5 6"} {
    error "data mismatch 2: $msg"
  }
  faultsim_integrity_check
}

do_faultsim_test 2 -faults oom* -prep {
  sqlite3 db ""
  db eval {
    PRAGMA page_size = 1024;
    PRAGMA cache_size = 10;
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(b, a);
    WITH x(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<50)
    INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
  }
} -body {
  execsql { UPDATE t1 SET a = randomblob(99) }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check db
}

do_faultsim_test 3 -faults oom* -prep {
  sqlite3 db ""
  db eval {
    PRAGMA page_size = 1024;
    PRAGMA cache_size = 10;
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(b, a);
    WITH x(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<50)
    INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
  }
} -body {
  execsql { 
    BEGIN;
      UPDATE t1 SET a = randomblob(99);
      SAVEPOINT abc;
        UPDATE t1 SET a = randomblob(98) WHERE (rowid%10)==0;
      ROLLBACK TO abc;
        UPDATE t1 SET a = randomblob(97) WHERE (rowid%5)==0;
      ROLLBACK TO abc;
    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}
  faultsim_integrity_check db
}

do_faultsim_test 4 -faults * -prep {
  sqlite3 db ""
  db eval {
    PRAGMA page_size = 1024;
    PRAGMA cache_size = 10;
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(b, a);
    WITH x(i) AS (SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<50)
    INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
  }
} -body {
  execsql { 
    BEGIN;
      UPDATE t1 SET a = randomblob(99);
      SAVEPOINT abc;
        UPDATE t1 SET a = randomblob(98) WHERE (rowid%10)==0;
      ROLLBACK TO abc;
        UPDATE t1 SET a = randomblob(97) WHERE (rowid%5)==0;
      ROLLBACK TO abc;
    COMMIT;
  }
} -test {
  faultsim_test_result {0 {}}
}

sqlite3_memdebug_vfs_oom_test 1
finish_test

Added test/temptable2.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
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
# 2016 March 3
#
# 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.
#
#***********************************************************************

set testdir [file dirname $argv0]
source $testdir/tester.tcl
set testprefix temptable2

do_execsql_test 1.1 {
  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
}

do_execsql_test 1.2 {
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100000 )
  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM X;
} {}

do_execsql_test 1.3 {
  PRAGMA temp.integrity_check;
} {ok}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 2.1 {
  CREATE TEMP TABLE t2(a, b);
  INSERT INTO t2 VALUES(1, 2);
} {}

do_execsql_test 2.2 {
  BEGIN;
    INSERT INTO t2 VALUES(3, 4);
    SELECT * FROM t2;
} {1 2 3 4}

do_execsql_test 2.3 {
  ROLLBACK;
  SELECT * FROM t2;
} {1 2}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 3.1.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);

  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )
  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;

  SELECT count(*) FROM t1;
} {1000}
do_execsql_test 3.1.2 {
  BEGIN;
    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
  ROLLBACK;
}
do_execsql_test 3.1.3 {
  SELECT count(*) FROM t1;
} {1000}
do_execsql_test 3.1.4 { PRAGMA temp.integrity_check } {ok}

do_execsql_test 3.2.1 {
  BEGIN;
    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==0;
    SAVEPOINT abc;
      UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==1;
    ROLLBACK TO abc;
    UPDATE t1 SET b=randomblob(100) WHERE (rowid%10)==2;
  COMMIT;
}
do_execsql_test 3.2.2 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 4.1.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);

  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<10 )
  INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;

  SELECT count(*) FROM t1;
  PRAGMA temp.page_count;
} {10 9}

do_execsql_test 4.1.2 {
  BEGIN;
    UPDATE t1 SET b=randomblob(100);
  ROLLBACK;
}

do_execsql_test 4.1.3 {
  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  SELECT count(*) FROM t2;
  SELECT count(*) FROM t1;
} {500 10}

do_test 4.1.4 {
  set n [db one { PRAGMA temp.page_count }]
  expr ($n >280 && $n < 300) 
} 1

do_execsql_test 4.1.4 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
#
reset_db
do_execsql_test 5.1.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;

  CREATE TEMP TABLE t1(a, b);
  CREATE INDEX i1 ON t1(a, b);
  INSERT INTO t1 VALUES(1, 2);
}

do_test 5.1.2 {
  set n [db one { PRAGMA temp.page_count }]
  expr ($n > 280 && $n < 290)
} {1}

do_execsql_test 5.1.3 {
  BEGIN;
    UPDATE t1 SET a=2;
    UPDATE t2 SET a=randomblob(100);
    SELECT count(*) FROM t1;
  ROLLBACK;
} {1}

do_execsql_test 5.1.4 {
  UPDATE t2 SET a=randomblob(100);

  SELECT * FROM t1;
} {1 2}

do_execsql_test 5.1.5 { PRAGMA temp.integrity_check } {ok}

#-------------------------------------------------------------------------
# Test this:
#
#   1. Page is DIRTY at the start of a transaction.
#   2. Page is written out as part of the transaction.
#   3. Page is then read back in.
#   4. Transaction is rolled back. Is the page now clean or dirty?
#
# This actually does work. Step 4 marks the page as clean. But it also
# writes to the database file itself. So marking it clean is correct - 
# the page does match the contents of the db file.
#
reset_db

do_execsql_test 6.1 {
  PRAGMA main.cache_size = 10;
  PRAGMA temp.cache_size = 10;

  CREATE TEMP TABLE t1(x);
  INSERT INTO t1 VALUES('one');

  CREATE TEMP TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
}

do_execsql_test 6.2 {
  UPDATE t1 SET x='two';             -- step 1
  BEGIN;
    UPDATE t2 SET a=randomblob(100); -- step 2
    SELECT * FROM t1;                -- step 3
  ROLLBACK;                          -- step 4

  SELECT count(*) FROM t2;
  SELECT * FROM t1;
} {two 500 two}

#-------------------------------------------------------------------------
#
reset_db
sqlite3 db ""
do_execsql_test 7.1 {
  PRAGMA auto_vacuum=INCREMENTAL;
  CREATE TABLE t1(x);
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 VALUES(zeroblob(900));
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  INSERT INTO t1 SELECT x FROM t1;
  BEGIN;
  DELETE FROM t1 WHERE rowid%2;
  PRAGMA incremental_vacuum(4);
  ROLLBACK;
  PRAGMA integrity_check;
} {ok}

#-------------------------------------------------------------------------
# Try changing the page size using a backup operation when pages are
# stored in main-memory only.
#
reset_db
do_execsql_test 8.1 {
  CREATE TABLE t2(a, b);
  CREATE INDEX i2 ON t2(a, b);
  WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<20 )
  INSERT INTO t2 SELECT randomblob(100), randomblob(100) FROM x;
  PRAGMA page_count;
} {13}

do_test 8.2 {
  sqlite3 tmp ""
  execsql {
    PRAGMA page_size = 8192;
    CREATE TABLE t1(a, b);
    CREATE INDEX i1 ON t1(a, b);
    WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<100 )
    INSERT INTO t1 SELECT randomblob(100), randomblob(100) FROM x;
    PRAGMA page_count;
  } tmp
} {10}

do_test 8.3 {
  sqlite3_backup B tmp main db main
  B step 5
  B finish
} {SQLITE_READONLY}

do_test 8.4 {
  execsql {
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
    PRAGMA page_size;
  } tmp
} {100 ok 8192}

do_test 8.5 { 
  tmp eval { UPDATE t1 SET a=randomblob(100) }
} {}

do_test 8.6 {
  sqlite3_backup B tmp main db main
  B step 1000
  B finish
} {SQLITE_READONLY}

tmp close

#-------------------------------------------------------------------------
# Try inserts and deletes with a large db in auto-vacuum mode. Check
#
foreach {tn mode} {
  1 delete
  2 wal
} {
  reset_db
  sqlite3 db ""
  do_execsql_test 9.$tn.1.1 {
    PRAGMA cache_size = 15;
    PRAGMA auto_vacuum = 1;
  }
  do_execsql_test 9.$tn.1.2 "PRAGMA journal_mode = $mode" delete

  do_execsql_test 9.$tn.1.3 {
    CREATE TABLE tx(a, b);
    CREATE INDEX i1 ON tx(a);
    CREATE INDEX i2 ON tx(b);
    WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<1000 )
      INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
  }

  for {set i 2} {$i<20} {incr i} {
    do_execsql_test 9.$tn.$i.1 { DELETE FROM tx WHERE (random()%3)==0 }

    do_execsql_test 9.$tn.$i.2 { PRAGMA integrity_check } ok

      do_execsql_test 9.$tn.$i.3 { 
        WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<400 )
          INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
      }

    do_execsql_test 9.$tn.$i.4 { PRAGMA integrity_check } ok

    do_execsql_test 9.$tn.$i.5 { 
      BEGIN;
      DELETE FROM tx WHERE (random()%3)==0;
      WITH x(i) AS ( SELECT 1 UNION ALL SELECT i+1 FROM x WHERE i<500 )
        INSERT INTO tx SELECT randomblob(100), randomblob(100) FROM x;
      COMMIT;
    }

    do_execsql_test 9.$tn.$i.6 { PRAGMA integrity_check } ok
  }
}

finish_test