/ Check-in [bac7342c]
Login

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

Overview
Comment:Merge in the blocking-checkpoint enhancement, including the new sqlite3_wal_checkpoint_v2() interface and the PRAGMA wal_checkpoint(full) statement.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: bac7342c368a7c4f5f2878e08d9581dcbf57dd58
User & Date: drh 2011-02-02 16:34:08
Context
2011-02-02
17:30
Optimize handling of equality and range constraints on the "term" column of an fts4aux table. check-in: 386701de user: dan tags: trunk
16:34
Merge in the blocking-checkpoint enhancement, including the new sqlite3_wal_checkpoint_v2() interface and the PRAGMA wal_checkpoint(full) statement. check-in: bac7342c user: drh tags: trunk
04:40
Merge fts4aux branch. check-in: c6d9f7d8 user: dan tags: trunk
2010-11-19
18:51
Merge the checkpoint_fullfsync pragma and the superlock demonstration into the checkpoint-v2 experimental branch. Closed-Leaf check-in: ebf74015 user: drh tags: blocking-checkpoint
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to main.mk.

532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
test:	testfixture$(EXE) sqlite3$(EXE)
	./testfixture$(EXE) $(TOP)/test/veryquick.test

# The next two rules are used to support the "threadtest" target. Building
# threadtest runs a few thread-safety tests that are implemented in C. This
# target is invoked by the releasetest.tcl script.
# 
threadtest3$(EXE): sqlite3.c $(TOP)/test/threadtest3.c
	$(TCCX) -O2 sqlite3.c $(TOP)/test/threadtest3.c \
		-o threadtest3$(EXE) $(THREADLIB)

threadtest: threadtest3$(EXE)
	./threadtest3$(EXE)

sqlite3_analyzer$(EXE):	$(TOP)/src/tclsqlite.c sqlite3.c $(TESTSRC) \
			$(TOP)/tool/spaceanal.tcl







|
|







532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
test:	testfixture$(EXE) sqlite3$(EXE)
	./testfixture$(EXE) $(TOP)/test/veryquick.test

# The next two rules are used to support the "threadtest" target. Building
# threadtest runs a few thread-safety tests that are implemented in C. This
# target is invoked by the releasetest.tcl script.
# 
threadtest3$(EXE): sqlite3.o $(TOP)/test/threadtest3.c $(TOP)/test/tt3_checkpoint.c
	$(TCCX) -O2 sqlite3.o $(TOP)/test/threadtest3.c \
		-o threadtest3$(EXE) $(THREADLIB)

threadtest: threadtest3$(EXE)
	./threadtest3$(EXE)

sqlite3_analyzer$(EXE):	$(TOP)/src/tclsqlite.c sqlite3.c $(TESTSRC) \
			$(TOP)/tool/spaceanal.tcl

Changes to src/btree.c.

7928
7929
7930
7931
7932
7933
7934


7935
7936
7937
7938
7939
7940
7941
7942
7943
7944
7945
7946
7947
7948
7949
7950
7951

#ifndef SQLITE_OMIT_WAL
/*
** Run a checkpoint on the Btree passed as the first argument.
**
** Return SQLITE_LOCKED if this or any other connection has an open 
** transaction on the shared-cache the argument Btree is connected to.


*/
int sqlite3BtreeCheckpoint(Btree *p){
  int rc = SQLITE_OK;
  if( p ){
    BtShared *pBt = p->pBt;
    sqlite3BtreeEnter(p);
    if( pBt->inTransaction!=TRANS_NONE ){
      rc = SQLITE_LOCKED;
    }else{
      rc = sqlite3PagerCheckpoint(pBt->pPager);
    }
    sqlite3BtreeLeave(p);
  }
  return rc;
}
#endif








>
>

|







|







7928
7929
7930
7931
7932
7933
7934
7935
7936
7937
7938
7939
7940
7941
7942
7943
7944
7945
7946
7947
7948
7949
7950
7951
7952
7953

#ifndef SQLITE_OMIT_WAL
/*
** Run a checkpoint on the Btree passed as the first argument.
**
** Return SQLITE_LOCKED if this or any other connection has an open 
** transaction on the shared-cache the argument Btree is connected to.
**
** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART.
*/
int sqlite3BtreeCheckpoint(Btree *p, int eMode, int *pnLog, int *pnCkpt){
  int rc = SQLITE_OK;
  if( p ){
    BtShared *pBt = p->pBt;
    sqlite3BtreeEnter(p);
    if( pBt->inTransaction!=TRANS_NONE ){
      rc = SQLITE_LOCKED;
    }else{
      rc = sqlite3PagerCheckpoint(pBt->pPager, eMode, pnLog, pnCkpt);
    }
    sqlite3BtreeLeave(p);
  }
  return rc;
}
#endif

Changes to src/btree.h.

203
204
205
206
207
208
209
210
211
212
213
214
215
216
217

#ifdef SQLITE_TEST
int sqlite3BtreeCursorInfo(BtCursor*, int*, int);
void sqlite3BtreeCursorList(Btree*);
#endif

#ifndef SQLITE_OMIT_WAL
  int sqlite3BtreeCheckpoint(Btree*);
#endif

/*
** If we are not using shared cache, then there is no need to
** use mutexes to access the BtShared structures.  So make the
** Enter and Leave procedures no-ops.
*/







|







203
204
205
206
207
208
209
210
211
212
213
214
215
216
217

#ifdef SQLITE_TEST
int sqlite3BtreeCursorInfo(BtCursor*, int*, int);
void sqlite3BtreeCursorList(Btree*);
#endif

#ifndef SQLITE_OMIT_WAL
  int sqlite3BtreeCheckpoint(Btree*, int, int *, int *);
#endif

/*
** If we are not using shared cache, then there is no need to
** use mutexes to access the BtShared structures.  So make the
** Enter and Leave procedures no-ops.
*/

Changes to src/main.c.

1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349



1350
1351
1352
1353
1354







1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372










1373
1374
1375
1376
1377
1378
1379
....
1383
1384
1385
1386
1387
1388
1389


1390
1391
1392
1393

1394
1395
1396
1397
1398
1399






1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
  sqlite3_mutex_leave(db->mutex);
  return pRet;
#else
  return 0;
#endif
}


/*
** Checkpoint database zDb. If zDb is NULL, or if the buffer zDb points
** to contains a zero-length string, all attached databases are 
** checkpointed.
*/
int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb){



#ifdef SQLITE_OMIT_WAL
  return SQLITE_OK;
#else
  int rc;                         /* Return code */
  int iDb = SQLITE_MAX_ATTACHED;  /* sqlite3.aDb[] index of db to checkpoint */








  sqlite3_mutex_enter(db->mutex);
  if( zDb && zDb[0] ){
    iDb = sqlite3FindDbName(db, zDb);
  }
  if( iDb<0 ){
    rc = SQLITE_ERROR;
    sqlite3Error(db, SQLITE_ERROR, "unknown database: %s", zDb);
  }else{
    rc = sqlite3Checkpoint(db, iDb);
    sqlite3Error(db, rc, 0);
  }
  rc = sqlite3ApiExit(db, rc);
  sqlite3_mutex_leave(db->mutex);
  return rc;
#endif
}











#ifndef SQLITE_OMIT_WAL
/*
** Run a checkpoint on database iDb. This is a no-op if database iDb is
** not currently open in WAL mode.
**
** If a transaction is open on the database being checkpointed, this 
** function returns SQLITE_LOCKED and a checkpoint is not attempted. If 
................................................................................
** The mutex on database handle db should be held by the caller. The mutex
** associated with the specific b-tree being checkpointed is taken by
** this function while the checkpoint is running.
**
** If iDb is passed SQLITE_MAX_ATTACHED, then all attached databases are
** checkpointed. If an error is encountered it is returned immediately -
** no attempt is made to checkpoint any remaining databases.


*/
int sqlite3Checkpoint(sqlite3 *db, int iDb){
  int rc = SQLITE_OK;             /* Return code */
  int i;                          /* Used to iterate through attached dbs */


  assert( sqlite3_mutex_held(db->mutex) );

  for(i=0; i<db->nDb && rc==SQLITE_OK; i++){
    if( i==iDb || iDb==SQLITE_MAX_ATTACHED ){
      rc = sqlite3BtreeCheckpoint(db->aDb[i].pBt);






    }
  }

  return rc;
}
#endif /* SQLITE_OMIT_WAL */

/*
** This function returns true if main-memory should be used instead of
** a temporary file for transient pager files and statement journals.
** The value returned depends on the value of db->temp_store (runtime







|
|
|
|
|
|
|
>
>
>





>
>
>
>
>
>
>









|








>
>
>
>
>
>
>
>
>
>







 







>
>

|


>





|
>
>
>
>
>
>



|







1336
1337
1338
1339
1340
1341
1342
1343
1344
1345
1346
1347
1348
1349
1350
1351
1352
1353
1354
1355
1356
1357
1358
1359
1360
1361
1362
1363
1364
1365
1366
1367
1368
1369
1370
1371
1372
1373
1374
1375
1376
1377
1378
1379
1380
1381
1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
....
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
1436
1437
1438
1439
  sqlite3_mutex_leave(db->mutex);
  return pRet;
#else
  return 0;
#endif
}

/*
** Checkpoint database zDb.
*/
int sqlite3_wal_checkpoint_v2(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of attached database (or NULL) */
  int eMode,                      /* SQLITE_CHECKPOINT_* value */
  int *pnLog,                     /* OUT: Size of WAL log in frames */
  int *pnCkpt                     /* OUT: Total number of frames checkpointed */
){
#ifdef SQLITE_OMIT_WAL
  return SQLITE_OK;
#else
  int rc;                         /* Return code */
  int iDb = SQLITE_MAX_ATTACHED;  /* sqlite3.aDb[] index of db to checkpoint */

  if( eMode!=SQLITE_CHECKPOINT_PASSIVE
   && eMode!=SQLITE_CHECKPOINT_FULL
   && eMode!=SQLITE_CHECKPOINT_RESTART
  ){
    return SQLITE_MISUSE;
  }

  sqlite3_mutex_enter(db->mutex);
  if( zDb && zDb[0] ){
    iDb = sqlite3FindDbName(db, zDb);
  }
  if( iDb<0 ){
    rc = SQLITE_ERROR;
    sqlite3Error(db, SQLITE_ERROR, "unknown database: %s", zDb);
  }else{
    rc = sqlite3Checkpoint(db, iDb, eMode, pnLog, pnCkpt);
    sqlite3Error(db, rc, 0);
  }
  rc = sqlite3ApiExit(db, rc);
  sqlite3_mutex_leave(db->mutex);
  return rc;
#endif
}


/*
** Checkpoint database zDb. If zDb is NULL, or if the buffer zDb points
** to contains a zero-length string, all attached databases are 
** checkpointed.
*/
int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb){
  return sqlite3_wal_checkpoint_v2(db, zDb, SQLITE_CHECKPOINT_PASSIVE, 0, 0);
}

#ifndef SQLITE_OMIT_WAL
/*
** Run a checkpoint on database iDb. This is a no-op if database iDb is
** not currently open in WAL mode.
**
** If a transaction is open on the database being checkpointed, this 
** function returns SQLITE_LOCKED and a checkpoint is not attempted. If 
................................................................................
** The mutex on database handle db should be held by the caller. The mutex
** associated with the specific b-tree being checkpointed is taken by
** this function while the checkpoint is running.
**
** If iDb is passed SQLITE_MAX_ATTACHED, then all attached databases are
** checkpointed. If an error is encountered it is returned immediately -
** no attempt is made to checkpoint any remaining databases.
**
** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART.
*/
int sqlite3Checkpoint(sqlite3 *db, int iDb, int eMode, int *pnLog, int *pnCkpt){
  int rc = SQLITE_OK;             /* Return code */
  int i;                          /* Used to iterate through attached dbs */
  int bBusy = 0;                  /* True if SQLITE_BUSY has been encountered */

  assert( sqlite3_mutex_held(db->mutex) );

  for(i=0; i<db->nDb && rc==SQLITE_OK; i++){
    if( i==iDb || iDb==SQLITE_MAX_ATTACHED ){
      rc = sqlite3BtreeCheckpoint(db->aDb[i].pBt, eMode, pnLog, pnCkpt);
      pnLog = 0;
      pnCkpt = 0;
      if( rc==SQLITE_BUSY ){
        bBusy = 1;
        rc = SQLITE_OK;
      }
    }
  }

  return (rc==SQLITE_OK && bBusy) ? SQLITE_BUSY : rc;
}
#endif /* SQLITE_OMIT_WAL */

/*
** This function returns true if main-memory should be used instead of
** a temporary file for transient pager files and statement journals.
** The value returned depends on the value of db->temp_store (runtime

Changes to src/pager.c.

2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
....
6595
6596
6597
6598
6599
6600
6601
6602




6603
6604
6605
6606
6607
6608
6609




6610
6611
6612
6613
6614
6615
6616
  return rc;
}

/*
** This function is a wrapper around sqlite3WalFrames(). As well as logging
** the contents of the list of pages headed by pList (connected by pDirty),
** this function notifies any active backup processes that the pages have
** changed.
**
** The list of pages passed into this routine is always sorted by page number.
** Hence, if page 1 appears anywhere on the list, it will be the first page.
*/ 
static int pagerWalFrames(
  Pager *pPager,                  /* Pager object */
  PgHdr *pList,                   /* List of frames to log */
................................................................................
*/
sqlite3_backup **sqlite3PagerBackupPtr(Pager *pPager){
  return &pPager->pBackup;
}

#ifndef SQLITE_OMIT_WAL
/*
** This function is called when the user invokes "PRAGMA checkpoint".




*/
int sqlite3PagerCheckpoint(Pager *pPager){
  int rc = SQLITE_OK;
  if( pPager->pWal ){
    u8 *zBuf = (u8 *)pPager->pTmpSpace;
    rc = sqlite3WalCheckpoint(pPager->pWal, pPager->ckptSyncFlags,
                              pPager->pageSize, zBuf);




  }
  return rc;
}

int sqlite3PagerWalCallback(Pager *pPager){
  return sqlite3WalCallback(pPager->pWal);
}







|







 







|
>
>
>
>

|


<
|
<
>
>
>
>







2943
2944
2945
2946
2947
2948
2949
2950
2951
2952
2953
2954
2955
2956
2957
....
6595
6596
6597
6598
6599
6600
6601
6602
6603
6604
6605
6606
6607
6608
6609
6610

6611

6612
6613
6614
6615
6616
6617
6618
6619
6620
6621
6622
  return rc;
}

/*
** This function is a wrapper around sqlite3WalFrames(). As well as logging
** the contents of the list of pages headed by pList (connected by pDirty),
** this function notifies any active backup processes that the pages have
** changed. 
**
** The list of pages passed into this routine is always sorted by page number.
** Hence, if page 1 appears anywhere on the list, it will be the first page.
*/ 
static int pagerWalFrames(
  Pager *pPager,                  /* Pager object */
  PgHdr *pList,                   /* List of frames to log */
................................................................................
*/
sqlite3_backup **sqlite3PagerBackupPtr(Pager *pPager){
  return &pPager->pBackup;
}

#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()
** or wal_blocking_checkpoint() API functions.
**
** Parameter eMode is one of SQLITE_CHECKPOINT_PASSIVE, FULL or RESTART.
*/
int sqlite3PagerCheckpoint(Pager *pPager, int eMode, int *pnLog, int *pnCkpt){
  int rc = SQLITE_OK;
  if( pPager->pWal ){

    rc = sqlite3WalCheckpoint(pPager->pWal, eMode,

        pPager->xBusyHandler, pPager->pBusyHandlerArg,
        pPager->ckptSyncFlags, pPager->pageSize, (u8 *)pPager->pTmpSpace,
        pnLog, pnCkpt
    );
  }
  return rc;
}

int sqlite3PagerWalCallback(Pager *pPager){
  return sqlite3WalCallback(pPager->pWal);
}

Changes to src/pager.h.

134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
int sqlite3PagerSync(Pager *pPager);
int sqlite3PagerCommitPhaseTwo(Pager*);
int sqlite3PagerRollback(Pager*);
int sqlite3PagerOpenSavepoint(Pager *pPager, int n);
int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint);
int sqlite3PagerSharedLock(Pager *pPager);

int sqlite3PagerCheckpoint(Pager *pPager);
int sqlite3PagerWalSupported(Pager *pPager);
int sqlite3PagerWalCallback(Pager *pPager);
int sqlite3PagerOpenWal(Pager *pPager, int *pisOpen);
int sqlite3PagerCloseWal(Pager *pPager);

/* Functions used to query pager state and configuration. */
u8 sqlite3PagerIsreadonly(Pager*);







|







134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
int sqlite3PagerSync(Pager *pPager);
int sqlite3PagerCommitPhaseTwo(Pager*);
int sqlite3PagerRollback(Pager*);
int sqlite3PagerOpenSavepoint(Pager *pPager, int n);
int sqlite3PagerSavepoint(Pager *pPager, int op, int iSavepoint);
int sqlite3PagerSharedLock(Pager *pPager);

int sqlite3PagerCheckpoint(Pager *pPager, int, int*, int*);
int sqlite3PagerWalSupported(Pager *pPager);
int sqlite3PagerWalCallback(Pager *pPager);
int sqlite3PagerOpenWal(Pager *pPager, int *pisOpen);
int sqlite3PagerCloseWal(Pager *pPager);

/* Functions used to query pager state and configuration. */
u8 sqlite3PagerIsreadonly(Pager*);

Changes to src/pragma.c.

1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393









1394
1395








1396
1397
1398
1399
1400
1401
1402
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
    }
  }else
#endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */

#ifndef SQLITE_OMIT_WAL
  /*
  **   PRAGMA [database.]wal_checkpoint
  **
  ** Checkpoint the database.
  */
  if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){









    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
    sqlite3VdbeAddOp3(v, OP_Checkpoint, pId2->z?iDb:SQLITE_MAX_ATTACHED, 0, 0);








  }else

  /*
  **   PRAGMA wal_autocheckpoint
  **   PRAGMA wal_autocheckpoint = N
  **
  ** Configure a database connection to automatically checkpoint a database







|




>
>
>
>
>
>
>
>
>

<
>
>
>
>
>
>
>
>







1382
1383
1384
1385
1386
1387
1388
1389
1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403

1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
      sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
    }
  }else
#endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */

#ifndef SQLITE_OMIT_WAL
  /*
  **   PRAGMA [database.]wal_checkpoint = passive|full|restart
  **
  ** Checkpoint the database.
  */
  if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){
    int iBt = (pId2->z?iDb:SQLITE_MAX_ATTACHED);
    int eMode = SQLITE_CHECKPOINT_PASSIVE;
    if( zRight ){
      if( sqlite3StrICmp(zRight, "full")==0 ){
        eMode = SQLITE_CHECKPOINT_FULL;
      }else if( sqlite3StrICmp(zRight, "restart")==0 ){
        eMode = SQLITE_CHECKPOINT_RESTART;
      }
    }
    if( sqlite3ReadSchema(pParse) ) goto pragma_out;

    sqlite3VdbeSetNumCols(v, 3);
    pParse->nMem = 3;
    sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "busy", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 1, COLNAME_NAME, "log", SQLITE_STATIC);
    sqlite3VdbeSetColName(v, 2, COLNAME_NAME, "checkpointed", SQLITE_STATIC);

    sqlite3VdbeAddOp2(v, OP_Checkpoint, iBt, eMode);
    sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 3);
  }else

  /*
  **   PRAGMA wal_autocheckpoint
  **   PRAGMA wal_autocheckpoint = N
  **
  ** Configure a database connection to automatically checkpoint a database

Changes to src/sqlite.h.in.

2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
....
6245
6246
6247
6248
6249
6250
6251



















































































6252
6253
6254
6255
6256
6257
6258
6259
6260
6261
6262
6263
6264
** compiled using either [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
*/
const char *sqlite3_sql(sqlite3_stmt *pStmt);

/*
** CAPI3REF: Determine If An SQL Statement Writes The Database
**
** ^The sqlite3_stmt_readonly(X) interface returns true (non-zero) if 
** and only if the [prepared statement] X makes no direct changes to
** the content of the database file.
**
** Note that [application-defined SQL functions] or
** [virtual tables] might change the database indirectly as a side effect.  
** ^(For example, if an application defines a function "eval()" that 
** calls [sqlite3_exec()], then the following SQL statement would
................................................................................
**
** ^The [wal_checkpoint pragma] can be used to invoke this interface
** from SQL.  ^The [sqlite3_wal_autocheckpoint()] interface and the
** [wal_autocheckpoint pragma] can be used to cause this interface to be
** run whenever the WAL reaches a certain size threshold.
*/
int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb);




















































































/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif







|







 







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













2662
2663
2664
2665
2666
2667
2668
2669
2670
2671
2672
2673
2674
2675
2676
....
6245
6246
6247
6248
6249
6250
6251
6252
6253
6254
6255
6256
6257
6258
6259
6260
6261
6262
6263
6264
6265
6266
6267
6268
6269
6270
6271
6272
6273
6274
6275
6276
6277
6278
6279
6280
6281
6282
6283
6284
6285
6286
6287
6288
6289
6290
6291
6292
6293
6294
6295
6296
6297
6298
6299
6300
6301
6302
6303
6304
6305
6306
6307
6308
6309
6310
6311
6312
6313
6314
6315
6316
6317
6318
6319
6320
6321
6322
6323
6324
6325
6326
6327
6328
6329
6330
6331
6332
6333
6334
6335
6336
6337
6338
6339
6340
6341
6342
6343
6344
6345
6346
6347
** compiled using either [sqlite3_prepare_v2()] or [sqlite3_prepare16_v2()].
*/
const char *sqlite3_sql(sqlite3_stmt *pStmt);

/*
** CAPI3REF: Determine If An SQL Statement Writes The Database
**
** ^The sqlite3_stmt_readonly(X) interface returns true (non-zero) if
** and only if the [prepared statement] X makes no direct changes to
** the content of the database file.
**
** Note that [application-defined SQL functions] or
** [virtual tables] might change the database indirectly as a side effect.  
** ^(For example, if an application defines a function "eval()" that 
** calls [sqlite3_exec()], then the following SQL statement would
................................................................................
**
** ^The [wal_checkpoint pragma] can be used to invoke this interface
** from SQL.  ^The [sqlite3_wal_autocheckpoint()] interface and the
** [wal_autocheckpoint pragma] can be used to cause this interface to be
** run whenever the WAL reaches a certain size threshold.
*/
int sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb);

/*
**
** CAPI3REF: Checkpoint a database
**
** Run a checkpoint operation on WAL database zDb attached to database 
** handle db. The specific operation is determined by the value of the 
** eMode parameter:
**
** <dl>
** <dt>SQLITE_CHECKPOINT_PASSIVE<dd>
**   Checkpoint as many frames as possible without waiting for any database 
**   readers or writers to finish. Sync the db file if all frames in the log
**   are checkpointed. This mode is the same as calling 
**   sqlite3_wal_checkpoint(). The busy-handler callback is never invoked.
**
** <dt>SQLITE_CHECKPOINT_FULL<dd>
**   This mode blocks (calls the busy-handler callback) until there is no
**   database writer and all readers are reading from the most recent database
**   snapshot. It then checkpoints all frames in the log file and syncs the
**   database file. This call blocks database writers while it is running,
**   but not database readers.
**
** <dt>SQLITE_CHECKPOINT_RESTART<dd>
**   This mode works the same way as SQLITE_CHECKPOINT_FULL, except after 
**   checkpointing the log file it blocks (calls the busy-handler callback)
**   until all readers are reading from the database file only. This ensures 
**   that the next client to write to the database file restarts the log file 
**   from the beginning. This call blocks database writers while it is running,
**   but not database readers.
** </dl>
**
** If pnLog is not NULL, then *pnLog is set to the total number of frames in
** the log file before returning. If pnCkpt is not NULL, then *pnCkpt is set to
** the total number of checkpointed frames (including any that were already
** checkpointed when this function is called). *pnLog and *pnCkpt may be
** populated even if sqlite3_wal_checkpoint_v2() returns other than SQLITE_OK.
** If no values are available because of an error, they are both set to -1
** before returning to communicate this to the caller.
**
** All calls obtain an exclusive "checkpoint" lock on the database file. If
** any other process is running a checkpoint operation at the same time, the 
** lock cannot be obtained and SQLITE_BUSY is returned. Even if there is a 
** busy-handler configured, it will not be invoked in this case.
**
** The SQLITE_CHECKPOINT_FULL and RESTART modes also obtain the exclusive 
** "writer" lock on the database file. If the writer lock cannot be obtained
** immediately, and a busy-handler is configured, it is invoked and the writer
** lock retried until either the busy-handler returns 0 or the lock is
** successfully obtained. The busy-handler is also invoked while waiting for
** database readers as described above. If the busy-handler returns 0 before
** the writer lock is obtained or while waiting for database readers, the
** checkpoint operation proceeds from that point in the same way as 
** SQLITE_CHECKPOINT_PASSIVE - checkpointing as many frames as possible 
** without blocking any further. SQLITE_BUSY is returned in this case.
**
** If parameter zDb is NULL or points to a zero length string, then the
** specified operation is attempted on all WAL databases. In this case the
** values written to output parameters *pnLog and *pnCkpt are undefined. If 
** an SQLITE_BUSY error is encountered when processing one or more of the 
** attached WAL databases, the operation is still attempted on any remaining 
** attached databases and SQLITE_BUSY is returned to the caller. If any other 
** error occurs while processing an attached database, processing is abandoned 
** and the error code returned to the caller immediately. If no error 
** (SQLITE_BUSY or otherwise) is encountered while processing the attached 
** databases, SQLITE_OK is returned.
**
** If database zDb is the name of an attached database that is not in WAL
** mode, SQLITE_OK is returned and both *pnLog and *pnCkpt set to -1. If
** zDb is not NULL (or a zero length string) and is not the name of any
** attached database, SQLITE_ERROR is returned to the caller.
*/
int sqlite3_wal_checkpoint_v2(
  sqlite3 *db,                    /* Database handle */
  const char *zDb,                /* Name of attached database (or NULL) */
  int eMode,                      /* SQLITE_CHECKPOINT_* value */
  int *pnLog,                     /* OUT: Size of WAL log in frames */
  int *pnCkpt                     /* OUT: Total number of frames checkpointed */
);
#define SQLITE_CHECKPOINT_PASSIVE 0
#define SQLITE_CHECKPOINT_FULL    1
#define SQLITE_CHECKPOINT_RESTART 2


/*
** Undo the hack that converts floating point types to integer for
** builds on processors without floating point support.
*/
#ifdef SQLITE_OMIT_FLOATING_POINT
# undef double
#endif

#ifdef __cplusplus
}  /* End of the 'extern "C"' block */
#endif
#endif

Changes to src/sqliteInt.h.

3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
VTable *sqlite3GetVTable(sqlite3*, Table*);
const char *sqlite3JournalModename(int);
int sqlite3Checkpoint(sqlite3*, int);
int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);

/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 







|







3037
3038
3039
3040
3041
3042
3043
3044
3045
3046
3047
3048
3049
3050
3051
int sqlite3TransferBindings(sqlite3_stmt *, sqlite3_stmt *);
int sqlite3Reprepare(Vdbe*);
void sqlite3ExprListCheckLength(Parse*, ExprList*, const char*);
CollSeq *sqlite3BinaryCompareCollSeq(Parse *, Expr *, Expr *);
int sqlite3TempInMemory(const sqlite3*);
VTable *sqlite3GetVTable(sqlite3*, Table*);
const char *sqlite3JournalModename(int);
int sqlite3Checkpoint(sqlite3*, int, int, int*, int*);
int sqlite3WalDefaultHook(void*,sqlite3*,const char*,int);

/* Declarations for functions in fkey.c. All of these are replaced by
** no-op macros if OMIT_FOREIGN_KEY is defined. In this case no foreign
** key functionality is available. If OMIT_TRIGGER is defined but
** OMIT_FOREIGN_KEY is not, only some of the functions are no-oped. In
** this case foreign keys are parsed, but no other functionality is 

Changes to src/vdbe.c.

5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221

5222
5223







5224












5225
5226
5227
5228
5229
5230
5231
  if( sqlite3VdbeMemTooBig(pMem) ){
    goto too_big;
  }
  break;
}

#ifndef SQLITE_OMIT_WAL
/* Opcode: Checkpoint P1 * * * *
**
** Checkpoint database P1. This is a no-op if P1 is not currently in
** WAL mode.

*/
case OP_Checkpoint: {







  rc = sqlite3Checkpoint(db, pOp->p1);












  break;
};  
#endif

#ifndef SQLITE_OMIT_PRAGMA
/* Opcode: JournalMode P1 P2 P3 * P5
**







|


|
>


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







5211
5212
5213
5214
5215
5216
5217
5218
5219
5220
5221
5222
5223
5224
5225
5226
5227
5228
5229
5230
5231
5232
5233
5234
5235
5236
5237
5238
5239
5240
5241
5242
5243
5244
5245
5246
5247
5248
5249
5250
5251
  if( sqlite3VdbeMemTooBig(pMem) ){
    goto too_big;
  }
  break;
}

#ifndef SQLITE_OMIT_WAL
/* Opcode: Checkpoint P1 P2 P3 * *
**
** Checkpoint database P1. This is a no-op if P1 is not currently in
** WAL mode. Parameter P2 is one of SQLITE_CHECKPOINT_PASSIVE, FULL
** or RESTART.
*/
case OP_Checkpoint: {
  int nLog = -1;                  /* Number of pages in WAL log */
  int nCkpt = -1;                 /* Number of checkpointed pages */
  int bBusy = 0;
  assert( pOp->p2==SQLITE_CHECKPOINT_PASSIVE
       || pOp->p2==SQLITE_CHECKPOINT_FULL
       || pOp->p2==SQLITE_CHECKPOINT_RESTART
  );
  rc = sqlite3Checkpoint(db, pOp->p1, pOp->p2, &nLog, &nCkpt);
  if( rc==SQLITE_BUSY ){
    rc = SQLITE_OK;
    bBusy = 1;
  }

  aMem[1].u.i = bBusy;
  aMem[2].u.i = nLog;
  aMem[3].u.i = nCkpt;
  MemSetTypeFlag(&aMem[1], MEM_Int);
  MemSetTypeFlag(&aMem[2], MEM_Int);
  MemSetTypeFlag(&aMem[3], MEM_Int);

  break;
};  
#endif

#ifndef SQLITE_OMIT_PRAGMA
/* Opcode: JournalMode P1 P2 P3 * P5
**

Changes to src/wal.c.

1553
1554
1555
1556
1557
1558
1559




























1560
1561
1562
1563
1564
1565
1566
....
1587
1588
1589
1590
1591
1592
1593



1594
1595
1596

1597
1598
1599
1600
1601
1602
1603
1604
1605
1606

1607
1608

1609
1610
1611
1612
1613
1614
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626
1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639
1640
1641
1642
1643

1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656
1657
1658
....
1692
1693
1694
1695
1696
1697
1698

1699
1700
1701
1702
1703


1704
1705
1706








1707



1708





1709


1710
1711
1712
1713
1714
1715
1716
....
1735
1736
1737
1738
1739
1740
1741
1742


1743
1744
1745
1746
1747
1748
1749
....
2650
2651
2652
2653
2654
2655
2656



2657
2658
2659



2660
2661
2662


2663
2664
2665

2666
2667

2668
2669
2670
2671
2672
2673
2674
2675
2676
2677
2678
2679




















2680



2681




2682
2683


2684
2685
2686
2687
2688
2689
2690
2691
2692
2693
2694

2695
2696
2697
2698
2699
2700
2701
2702
2703
2704
2705

  if( rc!=SQLITE_OK ){
    walIteratorFree(p);
  }
  *pp = p;
  return rc;
}





























/*
** Copy as much content as we can from the WAL back into the database file
** in response to an sqlite3_wal_checkpoint() request or the equivalent.
**
** The amount of information copies from WAL to database might be limited
** by active readers.  This routine will never overwrite a database page
................................................................................
**
** The caller must be holding sufficient locks to ensure that no other
** checkpoint is running (in any other thread or process) at the same
** time.
*/
static int walCheckpoint(
  Wal *pWal,                      /* Wal connection */



  int sync_flags,                 /* Flags for OsSync() (or 0) */
  int nBuf,                       /* Size of zBuf in bytes */
  u8 *zBuf                        /* Temporary buffer to use */

){
  int rc;                         /* Return code */
  int szPage;                     /* Database page-size */
  WalIterator *pIter = 0;         /* Wal iterator context */
  u32 iDbpage = 0;                /* Next database page to write */
  u32 iFrame = 0;                 /* Wal frame containing data for iDbpage */
  u32 mxSafeFrame;                /* Max frame that can be backfilled */
  u32 mxPage;                     /* Max database page to write */
  int i;                          /* Loop counter */
  volatile WalCkptInfo *pInfo;    /* The checkpoint status information */


  szPage = (pWal->hdr.szPage&0xfe00) + ((pWal->hdr.szPage&0x0001)<<16);

  testcase( szPage<=32768 );
  testcase( szPage>=65536 );
  pInfo = walCkptInfo(pWal);
  if( pInfo->nBackfill>=pWal->hdr.mxFrame ) return SQLITE_OK;

  /* Allocate the iterator */
  rc = walIteratorInit(pWal, &pIter);
  if( rc!=SQLITE_OK ){
    return rc;
  }
  assert( pIter );

  /*** TODO:  Move this test out to the caller.  Make it an assert() here ***/
  if( szPage!=nBuf ){
    rc = SQLITE_CORRUPT_BKPT;
    goto walcheckpoint_out;
  }

  /* Compute in mxSafeFrame the index of the last frame of the WAL that is
  ** safe to write into the database.  Frames beyond mxSafeFrame might
  ** overwrite database pages that are in use by active readers and thus
  ** cannot be backfilled from the WAL.
  */
  mxSafeFrame = pWal->hdr.mxFrame;
  mxPage = pWal->hdr.nPage;
  for(i=1; i<WAL_NREADER; i++){
    u32 y = pInfo->aReadMark[i];
    if( mxSafeFrame>=y ){
      assert( y<=pWal->hdr.mxFrame );
      rc = walLockExclusive(pWal, WAL_READ_LOCK(i), 1);
      if( rc==SQLITE_OK ){
        pInfo->aReadMark[i] = READMARK_NOT_USED;
        walUnlockExclusive(pWal, WAL_READ_LOCK(i), 1);
      }else if( rc==SQLITE_BUSY ){
        mxSafeFrame = y;

      }else{
        goto walcheckpoint_out;
      }
    }
  }

  if( pInfo->nBackfill<mxSafeFrame
   && (rc = walLockExclusive(pWal, WAL_READ_LOCK(0), 1))==SQLITE_OK
  ){
    i64 nSize;                    /* Current size of database file */
    u32 nBackfill = pInfo->nBackfill;

    /* Sync the WAL to disk */
    if( sync_flags ){
      rc = sqlite3OsSync(pWal->pWalFd, sync_flags);
................................................................................
        rc = sqlite3OsTruncate(pWal->pDbFd, szDb);
        if( rc==SQLITE_OK && sync_flags ){
          rc = sqlite3OsSync(pWal->pDbFd, sync_flags);
        }
      }
      if( rc==SQLITE_OK ){
        pInfo->nBackfill = mxSafeFrame;

      }
    }

    /* Release the reader lock held while backfilling */
    walUnlockExclusive(pWal, WAL_READ_LOCK(0), 1);


  }else if( rc==SQLITE_BUSY ){
    /* Reset the return code so as not to report a checkpoint failure
    ** just because active readers prevent any backfill.








    */



    rc = SQLITE_OK;





  }



 walcheckpoint_out:
  walIteratorFree(pIter);
  return rc;
}

/*
................................................................................
    ** The EXCLUSIVE lock is not released before returning.
    */
    rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE);
    if( rc==SQLITE_OK ){
      if( pWal->exclusiveMode==WAL_NORMAL_MODE ){
        pWal->exclusiveMode = WAL_EXCLUSIVE_MODE;
      }
      rc = sqlite3WalCheckpoint(pWal, sync_flags, nBuf, zBuf);


      if( rc==SQLITE_OK ){
        isDelete = 1;
      }
    }

    walIndexClose(pWal, isDelete);
    sqlite3OsClose(pWal->pWalFd);
................................................................................

/* 
** This routine is called to implement sqlite3_wal_checkpoint() and
** related interfaces.
**
** Obtain a CHECKPOINT lock and then backfill as much information as
** we can from WAL into the database.



*/
int sqlite3WalCheckpoint(
  Wal *pWal,                      /* Wal connection */



  int sync_flags,                 /* Flags to sync db file with (or 0) */
  int nBuf,                       /* Size of temporary buffer */
  u8 *zBuf                        /* Temporary buffer to use */


){
  int rc;                         /* Return code */
  int isChanged = 0;              /* True if a new wal-index header is loaded */


  assert( pWal->ckptLock==0 );


  WALTRACE(("WAL%p: checkpoint begins\n", pWal));
  rc = walLockExclusive(pWal, WAL_CKPT_LOCK, 1);
  if( rc ){
    /* Usually this is SQLITE_BUSY meaning that another thread or process
    ** is already running a checkpoint, or maybe a recovery.  But it might
    ** also be SQLITE_IOERR. */
    return rc;
  }
  pWal->ckptLock = 1;

  /* Copy data from the log to the database file. */




















  rc = walIndexReadHdr(pWal, &isChanged);



  if( rc==SQLITE_OK ){




    rc = walCheckpoint(pWal, sync_flags, nBuf, zBuf);
  }


  if( isChanged ){
    /* If a new wal-index header was loaded before the checkpoint was 
    ** performed, then the pager-cache associated with pWal is now
    ** out of date. So zero the cached wal-index header to ensure that
    ** next time the pager opens a snapshot on this database it knows that
    ** the cache needs to be reset.
    */
    memset(&pWal->hdr, 0, sizeof(WalIndexHdr));
  }

  /* Release the locks. */

  walUnlockExclusive(pWal, WAL_CKPT_LOCK, 1);
  pWal->ckptLock = 0;
  WALTRACE(("WAL%p: checkpoint %s\n", pWal, rc ? "failed" : "ok"));
  return rc;
}

/* Return the value to pass to a sqlite3_wal_hook callback, the
** number of frames in the WAL at the point of the last commit since
** sqlite3WalCallback() was called.  If no commits have occurred since
** the last call, then return 0.
*/







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







 







>
>
>

|
<
>










>

<
>












|
|
|
|
<










|

|





>







|







 







>





>
>
|

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







 







|
>
>







 







>
>
>



>
>
>


|
>
>



>


>











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











>



|







1553
1554
1555
1556
1557
1558
1559
1560
1561
1562
1563
1564
1565
1566
1567
1568
1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
1585
1586
1587
1588
1589
1590
1591
1592
1593
1594
....
1615
1616
1617
1618
1619
1620
1621
1622
1623
1624
1625
1626

1627
1628
1629
1630
1631
1632
1633
1634
1635
1636
1637
1638
1639

1640
1641
1642
1643
1644
1645
1646
1647
1648
1649
1650
1651
1652
1653
1654
1655
1656

1657
1658
1659
1660
1661
1662
1663
1664
1665
1666
1667
1668
1669
1670
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680
1681
1682
1683
1684
1685
1686
1687
1688
1689
1690
....
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740

1741
1742
1743
1744
1745
1746
1747
1748
1749
1750
1751
1752
1753
1754
1755
1756
1757
1758
1759
1760
1761
1762
1763
1764
1765
1766
1767
1768
....
1787
1788
1789
1790
1791
1792
1793
1794
1795
1796
1797
1798
1799
1800
1801
1802
1803
....
2704
2705
2706
2707
2708
2709
2710
2711
2712
2713
2714
2715
2716
2717
2718
2719
2720
2721
2722
2723
2724
2725
2726
2727
2728
2729
2730
2731
2732
2733
2734
2735
2736
2737
2738
2739
2740
2741
2742
2743
2744
2745
2746
2747
2748
2749
2750
2751
2752
2753
2754
2755
2756
2757
2758
2759
2760
2761
2762
2763
2764
2765
2766
2767
2768
2769
2770
2771
2772
2773
2774
2775
2776
2777
2778
2779
2780
2781
2782
2783
2784
2785
2786
2787
2788
2789
2790
2791
2792
2793
2794
2795
2796
2797
2798
2799

  if( rc!=SQLITE_OK ){
    walIteratorFree(p);
  }
  *pp = p;
  return rc;
}

/*
** Attempt to obtain the exclusive WAL lock defined by parameters lockIdx and
** n. If the attempt fails and parameter xBusy is not NULL, then it is a
** busy-handler function. Invoke it and retry the lock until either the
** lock is successfully obtained or the busy-handler returns 0.
*/
static int walBusyLock(
  Wal *pWal,                      /* WAL connection */
  int (*xBusy)(void*),            /* Function to call when busy */
  void *pBusyArg,                 /* Context argument for xBusyHandler */
  int lockIdx,                    /* Offset of first byte to lock */
  int n                           /* Number of bytes to lock */
){
  int rc;
  do {
    rc = walLockExclusive(pWal, lockIdx, n);
  }while( xBusy && rc==SQLITE_BUSY && xBusy(pBusyArg) );
  return rc;
}

/*
** The cache of the wal-index header must be valid to call this function.
** Return the page-size in bytes used by the database.
*/
static int walPagesize(Wal *pWal){
  return (pWal->hdr.szPage&0xfe00) + ((pWal->hdr.szPage&0x0001)<<16);
}

/*
** Copy as much content as we can from the WAL back into the database file
** in response to an sqlite3_wal_checkpoint() request or the equivalent.
**
** The amount of information copies from WAL to database might be limited
** by active readers.  This routine will never overwrite a database page
................................................................................
**
** The caller must be holding sufficient locks to ensure that no other
** checkpoint is running (in any other thread or process) at the same
** time.
*/
static int walCheckpoint(
  Wal *pWal,                      /* Wal connection */
  int eMode,                      /* One of PASSIVE, FULL or RESTART */
  int (*xBusyCall)(void*),        /* Function to call when busy */
  void *pBusyArg,                 /* Context argument for xBusyHandler */
  int sync_flags,                 /* Flags for OsSync() (or 0) */
  u8 *zBuf,                       /* Temporary buffer to use */

  int *pnCkpt                     /* Total frames checkpointed */
){
  int rc;                         /* Return code */
  int szPage;                     /* Database page-size */
  WalIterator *pIter = 0;         /* Wal iterator context */
  u32 iDbpage = 0;                /* Next database page to write */
  u32 iFrame = 0;                 /* Wal frame containing data for iDbpage */
  u32 mxSafeFrame;                /* Max frame that can be backfilled */
  u32 mxPage;                     /* Max database page to write */
  int i;                          /* Loop counter */
  volatile WalCkptInfo *pInfo;    /* The checkpoint status information */
  int (*xBusy)(void*) = 0;        /* Function to call when waiting for locks */


  szPage = walPagesize(pWal);
  testcase( szPage<=32768 );
  testcase( szPage>=65536 );
  pInfo = walCkptInfo(pWal);
  if( pInfo->nBackfill>=pWal->hdr.mxFrame ) return SQLITE_OK;

  /* Allocate the iterator */
  rc = walIteratorInit(pWal, &pIter);
  if( rc!=SQLITE_OK ){
    return rc;
  }
  assert( pIter );

  pInfo = walCkptInfo(pWal);
  mxPage = pWal->hdr.nPage;
  if( pnCkpt ) *pnCkpt = pInfo->nBackfill;
  if( eMode!=SQLITE_CHECKPOINT_PASSIVE ) xBusy = xBusyCall;


  /* Compute in mxSafeFrame the index of the last frame of the WAL that is
  ** safe to write into the database.  Frames beyond mxSafeFrame might
  ** overwrite database pages that are in use by active readers and thus
  ** cannot be backfilled from the WAL.
  */
  mxSafeFrame = pWal->hdr.mxFrame;
  mxPage = pWal->hdr.nPage;
  for(i=1; i<WAL_NREADER; i++){
    u32 y = pInfo->aReadMark[i];
    if( mxSafeFrame>y ){
      assert( y<=pWal->hdr.mxFrame );
      rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(i), 1);
      if( rc==SQLITE_OK ){
        pInfo->aReadMark[i] = READMARK_NOT_USED;
        walUnlockExclusive(pWal, WAL_READ_LOCK(i), 1);
      }else if( rc==SQLITE_BUSY ){
        mxSafeFrame = y;
        xBusy = 0;
      }else{
        goto walcheckpoint_out;
      }
    }
  }

  if( pInfo->nBackfill<mxSafeFrame
   && (rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(0), 1))==SQLITE_OK
  ){
    i64 nSize;                    /* Current size of database file */
    u32 nBackfill = pInfo->nBackfill;

    /* Sync the WAL to disk */
    if( sync_flags ){
      rc = sqlite3OsSync(pWal->pWalFd, sync_flags);
................................................................................
        rc = sqlite3OsTruncate(pWal->pDbFd, szDb);
        if( rc==SQLITE_OK && sync_flags ){
          rc = sqlite3OsSync(pWal->pDbFd, sync_flags);
        }
      }
      if( rc==SQLITE_OK ){
        pInfo->nBackfill = mxSafeFrame;
        if( pnCkpt ) *pnCkpt = mxSafeFrame;
      }
    }

    /* Release the reader lock held while backfilling */
    walUnlockExclusive(pWal, WAL_READ_LOCK(0), 1);
  }

  if( rc==SQLITE_BUSY ){
    /* Reset the return code so as not to report a checkpoint failure

    ** just because there are active readers.  */
    rc = SQLITE_OK;
  }

  /* If this is an SQLITE_CHECKPOINT_RESTART operation, and the entire wal
  ** file has been copied into the database file, then block until all
  ** readers have finished using the wal file. This ensures that the next
  ** process to write to the database restarts the wal file.
  */
  if( rc==SQLITE_OK && eMode!=SQLITE_CHECKPOINT_PASSIVE ){
    assert( pWal->writeLock );
    if( pInfo->nBackfill<pWal->hdr.mxFrame ){
      rc = SQLITE_BUSY;
    }else if( eMode==SQLITE_CHECKPOINT_RESTART ){
      assert( mxSafeFrame==pWal->hdr.mxFrame );
      rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_READ_LOCK(1), WAL_NREADER-1);
      if( rc==SQLITE_OK ){
        walUnlockExclusive(pWal, WAL_READ_LOCK(1), WAL_NREADER-1);
      }
    }
  }

 walcheckpoint_out:
  walIteratorFree(pIter);
  return rc;
}

/*
................................................................................
    ** The EXCLUSIVE lock is not released before returning.
    */
    rc = sqlite3OsLock(pWal->pDbFd, SQLITE_LOCK_EXCLUSIVE);
    if( rc==SQLITE_OK ){
      if( pWal->exclusiveMode==WAL_NORMAL_MODE ){
        pWal->exclusiveMode = WAL_EXCLUSIVE_MODE;
      }
      rc = sqlite3WalCheckpoint(
          pWal, SQLITE_CHECKPOINT_PASSIVE, 0, 0, sync_flags, nBuf, zBuf, 0, 0
      );
      if( rc==SQLITE_OK ){
        isDelete = 1;
      }
    }

    walIndexClose(pWal, isDelete);
    sqlite3OsClose(pWal->pWalFd);
................................................................................

/* 
** This routine is called to implement sqlite3_wal_checkpoint() and
** related interfaces.
**
** Obtain a CHECKPOINT lock and then backfill as much information as
** we can from WAL into the database.
**
** If parameter xBusy is not NULL, it is a pointer to a busy-handler
** callback. In this case this function runs a blocking checkpoint.
*/
int sqlite3WalCheckpoint(
  Wal *pWal,                      /* Wal connection */
  int eMode,                      /* PASSIVE, FULL or RESTART */
  int (*xBusy)(void*),            /* Function to call when busy */
  void *pBusyArg,                 /* Context argument for xBusyHandler */
  int sync_flags,                 /* Flags to sync db file with (or 0) */
  int nBuf,                       /* Size of temporary buffer */
  u8 *zBuf,                       /* Temporary buffer to use */
  int *pnLog,                     /* OUT: Number of frames in WAL */
  int *pnCkpt                     /* OUT: Number of backfilled frames in WAL */
){
  int rc;                         /* Return code */
  int isChanged = 0;              /* True if a new wal-index header is loaded */
  int eMode2 = eMode;             /* Mode to pass to walCheckpoint() */

  assert( pWal->ckptLock==0 );
  assert( pWal->writeLock==0 );

  WALTRACE(("WAL%p: checkpoint begins\n", pWal));
  rc = walLockExclusive(pWal, WAL_CKPT_LOCK, 1);
  if( rc ){
    /* Usually this is SQLITE_BUSY meaning that another thread or process
    ** is already running a checkpoint, or maybe a recovery.  But it might
    ** also be SQLITE_IOERR. */
    return rc;
  }
  pWal->ckptLock = 1;

  /* If this is a blocking-checkpoint, then obtain the write-lock as well
  ** to prevent any writers from running while the checkpoint is underway.
  ** This has to be done before the call to walIndexReadHdr() below.
  **
  ** If the writer lock cannot be obtained, then a passive checkpoint is
  ** run instead. Since the checkpointer is not holding the writer lock,
  ** there is no point in blocking waiting for any readers. Assuming no 
  ** other error occurs, this function will return SQLITE_BUSY to the caller.
  */
  if( eMode!=SQLITE_CHECKPOINT_PASSIVE ){
    rc = walBusyLock(pWal, xBusy, pBusyArg, WAL_WRITE_LOCK, 1);
    if( rc==SQLITE_OK ){
      pWal->writeLock = 1;
    }else if( rc==SQLITE_BUSY ){
      eMode2 = SQLITE_CHECKPOINT_PASSIVE;
      rc = SQLITE_OK;
    }
  }

  /* Read the wal-index header. */
  if( rc==SQLITE_OK ){
    rc = walIndexReadHdr(pWal, &isChanged);
  }

  /* Copy data from the log to the database file. */
  if( rc==SQLITE_OK && pWal->hdr.mxFrame ){
    if( walPagesize(pWal)!=nBuf ){
      rc = SQLITE_CORRUPT_BKPT;
    }else{
      if( pnLog ) *pnLog = (int)pWal->hdr.mxFrame;
      rc = walCheckpoint(pWal, eMode2, xBusy, pBusyArg, sync_flags,zBuf,pnCkpt);
    }
  }

  if( isChanged ){
    /* If a new wal-index header was loaded before the checkpoint was 
    ** performed, then the pager-cache associated with pWal is now
    ** out of date. So zero the cached wal-index header to ensure that
    ** next time the pager opens a snapshot on this database it knows that
    ** the cache needs to be reset.
    */
    memset(&pWal->hdr, 0, sizeof(WalIndexHdr));
  }

  /* Release the locks. */
  sqlite3WalEndWriteTransaction(pWal);
  walUnlockExclusive(pWal, WAL_CKPT_LOCK, 1);
  pWal->ckptLock = 0;
  WALTRACE(("WAL%p: checkpoint %s\n", pWal, rc ? "failed" : "ok"));
  return (rc==SQLITE_OK && eMode!=eMode2 ? SQLITE_BUSY : rc);
}

/* Return the value to pass to a sqlite3_wal_hook callback, the
** number of frames in the WAL at the point of the last commit since
** sqlite3WalCallback() was called.  If no commits have occurred since
** the last call, then return 0.
*/

Changes to src/wal.h.

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
..
82
83
84
85
86
87
88



89
90
91


92
93
94
95
96
97
98

#ifndef _WAL_H_
#define _WAL_H_

#include "sqliteInt.h"

#ifdef SQLITE_OMIT_WAL
# define sqlite3WalOpen(x,y,z)                 0
# define sqlite3WalClose(w,x,y,z)              0
# define sqlite3WalBeginReadTransaction(y,z)   0
# define sqlite3WalEndReadTransaction(z)
# define sqlite3WalRead(v,w,x,y,z)             0
# define sqlite3WalDbsize(y)                   0
# define sqlite3WalBeginWriteTransaction(y)    0
# define sqlite3WalEndWriteTransaction(x)      0
# define sqlite3WalUndo(x,y,z)                 0
# define sqlite3WalSavepoint(y,z)
# define sqlite3WalSavepointUndo(y,z)          0
# define sqlite3WalFrames(u,v,w,x,y,z)         0
# define sqlite3WalCheckpoint(u,v,w,x)         0
# define sqlite3WalCallback(z)                 0
# define sqlite3WalExclusiveMode(y,z)          0
# define sqlite3WalHeapMemory(z)               0
#else

#define WAL_SAVEPOINT_NDATA 4

/* Connection to a write-ahead log (WAL) file. 
** There is one object of this type for each pager. 
*/
................................................................................

/* Write a frame or frames to the log. */
int sqlite3WalFrames(Wal *pWal, int, PgHdr *, Pgno, int, int);

/* Copy pages from the log to the database file */ 
int sqlite3WalCheckpoint(
  Wal *pWal,                      /* Write-ahead log connection */



  int sync_flags,                 /* Flags to sync db file with (or 0) */
  int nBuf,                       /* Size of buffer nBuf */
  u8 *zBuf                        /* Temporary buffer to use */


);

/* Return the value to pass to a sqlite3_wal_hook callback, the
** number of frames in the WAL at the point of the last commit since
** sqlite3WalCallback() was called.  If no commits have occurred since
** the last call, then return 0.
*/







|
|
|

|
|
|
|
|

|
|
|
|
|
|







 







>
>
>


|
>
>







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
..
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103

#ifndef _WAL_H_
#define _WAL_H_

#include "sqliteInt.h"

#ifdef SQLITE_OMIT_WAL
# define sqlite3WalOpen(x,y,z)                   0
# define sqlite3WalClose(w,x,y,z)                0
# define sqlite3WalBeginReadTransaction(y,z)     0
# define sqlite3WalEndReadTransaction(z)
# define sqlite3WalRead(v,w,x,y,z)               0
# define sqlite3WalDbsize(y)                     0
# define sqlite3WalBeginWriteTransaction(y)      0
# define sqlite3WalEndWriteTransaction(x)        0
# define sqlite3WalUndo(x,y,z)                   0
# define sqlite3WalSavepoint(y,z)
# define sqlite3WalSavepointUndo(y,z)            0
# define sqlite3WalFrames(u,v,w,x,y,z)           0
# define sqlite3WalCheckpoint(r,s,t,u,v,w,x,y,z) 0
# define sqlite3WalCallback(z)                   0
# define sqlite3WalExclusiveMode(y,z)            0
# define sqlite3WalHeapMemory(z)                 0
#else

#define WAL_SAVEPOINT_NDATA 4

/* Connection to a write-ahead log (WAL) file. 
** There is one object of this type for each pager. 
*/
................................................................................

/* Write a frame or frames to the log. */
int sqlite3WalFrames(Wal *pWal, int, PgHdr *, Pgno, int, int);

/* Copy pages from the log to the database file */ 
int sqlite3WalCheckpoint(
  Wal *pWal,                      /* Write-ahead log connection */
  int eMode,                      /* One of PASSIVE, FULL and RESTART */
  int (*xBusy)(void*),            /* Function to call when busy */
  void *pBusyArg,                 /* Context argument for xBusyHandler */
  int sync_flags,                 /* Flags to sync db file with (or 0) */
  int nBuf,                       /* Size of buffer nBuf */
  u8 *zBuf,                       /* Temporary buffer to use */
  int *pnLog,                     /* OUT: Number of frames in WAL */
  int *pnCkpt                     /* OUT: Number of backfilled frames in WAL */
);

/* Return the value to pass to a sqlite3_wal_hook callback, the
** number of frames in the WAL at the point of the last commit since
** sqlite3WalCallback() was called.  If no commits have occurred since
** the last call, then return 0.
*/

Changes to test/attach.test.

831
832
833
834
835
836
837

838
  }
} {noname inmem}
do_test attach-10.2 {
  lrange [execsql {
    PRAGMA database_list;
  }] 9 end
} {4 noname {} 5 inmem {}}

finish_test







>

831
832
833
834
835
836
837
838
839
  }
} {noname inmem}
do_test attach-10.2 {
  lrange [execsql {
    PRAGMA database_list;
  }] 9 end
} {4 noname {} 5 inmem {}}

finish_test

Changes to test/pager1.test.

1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
do_test pager1-22.1.1 {
  faultsim_delete_and_reopen
  execsql {
    CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
    INSERT INTO ko DEFAULT VALUES;
  }
  execsql { PRAGMA wal_checkpoint }
} {}
do_test pager1-22.2.1 {
  testvfs tv -default 1
  tv filter xSync
  tv script xSyncCb
  proc xSyncCb {args} {incr ::synccount}
  set ::synccount 0
  sqlite3 db test.db







|







1988
1989
1990
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000
2001
2002
do_test pager1-22.1.1 {
  faultsim_delete_and_reopen
  execsql {
    CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
    INSERT INTO ko DEFAULT VALUES;
  }
  execsql { PRAGMA wal_checkpoint }
} {0 -1 -1}
do_test pager1-22.2.1 {
  testvfs tv -default 1
  tv filter xSync
  tv script xSyncCb
  proc xSyncCb {args} {incr ::synccount}
  set ::synccount 0
  sqlite3 db test.db

Changes to test/threadtest3.c.

1390
1391
1392
1393
1394
1395
1396

1397
1398
1399
1400
1401
1402
1403
....
1404
1405
1406
1407
1408
1409
1410
1411
1412



1413
1414
1415
1416
1417
1418
1419
  launch_thread(&err, &threads, dynamic_triggers_1, 0);

  join_all_threads(&err, &threads);

  print_and_free_err(&err);
}



int main(int argc, char **argv){
  struct ThreadTest {
    void (*xTest)(int);
    const char *zTest;
    int nMs;
  } aTest[] = {
................................................................................
    { walthread1, "walthread1", 20000 },
    { walthread2, "walthread2", 20000 },
    { walthread3, "walthread3", 20000 },
    { walthread4, "walthread4", 20000 },
    { walthread5, "walthread5",  1000 },
    { walthread5, "walthread5",  1000 },
    
    { cgt_pager_1, "cgt_pager_1", 0 },
    { dynamic_triggers, "dynamic_triggers", 20000 },



  };

  int i;
  char *zTest = 0;
  int nTest = 0;
  int bTestfound = 0;
  int bPrefix = 0;







>







 







|

>
>
>







1390
1391
1392
1393
1394
1395
1396
1397
1398
1399
1400
1401
1402
1403
1404
....
1405
1406
1407
1408
1409
1410
1411
1412
1413
1414
1415
1416
1417
1418
1419
1420
1421
1422
1423
  launch_thread(&err, &threads, dynamic_triggers_1, 0);

  join_all_threads(&err, &threads);

  print_and_free_err(&err);
}

#include "tt3_checkpoint.c"

int main(int argc, char **argv){
  struct ThreadTest {
    void (*xTest)(int);
    const char *zTest;
    int nMs;
  } aTest[] = {
................................................................................
    { walthread1, "walthread1", 20000 },
    { walthread2, "walthread2", 20000 },
    { walthread3, "walthread3", 20000 },
    { walthread4, "walthread4", 20000 },
    { walthread5, "walthread5",  1000 },
    { walthread5, "walthread5",  1000 },
    
    { cgt_pager_1,      "cgt_pager_1", 0 },
    { dynamic_triggers, "dynamic_triggers", 20000 },

    { checkpoint_starvation_1, "checkpoint_starvation_1", 10000 },
    { checkpoint_starvation_2, "checkpoint_starvation_2", 10000 },
  };

  int i;
  char *zTest = 0;
  int nTest = 0;
  int bTestfound = 0;
  int bPrefix = 0;

Added test/tt3_checkpoint.c.













































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
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
/*
** 2001 September 15
**
** 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 is part of the test program "threadtest3". Despite being a C
** file it is not compiled separately, but included by threadtest3.c using
** the #include directive normally used with header files.
**
** This file contains the implementation of test cases:
**
**     checkpoint_starvation_1
**     checkpoint_starvation_2
*/

/*
** Both test cases involve 1 writer/checkpointer thread and N reader threads.
** 
** Each reader thread performs a series of read transactions, one after 
** another. Each read transaction lasts for 100 ms.
**
** The writer writes transactions as fast as possible. It uses a callback
** registered with sqlite3_wal_hook() to try to keep the WAL-size limited to 
** around 50 pages.
**
** In test case checkpoint_starvation_1, the auto-checkpoint uses 
** SQLITE_CHECKPOINT_PASSIVE. In checkpoint_starvation_2, it uses RESTART.
** The expectation is that in the first case the WAL file will grow very 
** large, and in the second will be limited to the 50 pages or thereabouts.
** However, the overall transaction throughput will be lower for 
** checkpoint_starvation_2, as every checkpoint will block for up to 200 ms
** waiting for readers to clear.
*/

/* Frame limit used by the WAL hook for these tests. */
#define CHECKPOINT_STARVATION_FRAMELIMIT 50

/* Duration in ms of each read transaction */
#define CHECKPOINT_STARVATION_READMS    100

struct CheckpointStarvationCtx {
  int eMode;
  int nMaxFrame;
};
typedef struct CheckpointStarvationCtx CheckpointStarvationCtx;

static int checkpoint_starvation_walhook(
  void *pCtx, 
  sqlite3 *db, 
  const char *zDb, 
  int nFrame
){
  CheckpointStarvationCtx *p = (CheckpointStarvationCtx *)pCtx;
  if( nFrame>p->nMaxFrame ){
    p->nMaxFrame = nFrame;
  }
  if( nFrame>=CHECKPOINT_STARVATION_FRAMELIMIT ){
    sqlite3_wal_checkpoint_v2(db, zDb, p->eMode, 0, 0);
  }
  return SQLITE_OK;
}

static char *checkpoint_starvation_reader(int iTid, int iArg){
  Error err = {0};
  Sqlite db = {0};

  opendb(&err, &db, "test.db", 0);
  while( !timetostop(&err) ){
    i64 iCount1, iCount2;
    sql_script(&err, &db, "BEGIN");
    iCount1 = execsql_i64(&err, &db, "SELECT count(x) FROM t1");
    usleep(CHECKPOINT_STARVATION_READMS*1000);
    iCount2 = execsql_i64(&err, &db, "SELECT count(x) FROM t1");
    sql_script(&err, &db, "COMMIT");

    if( iCount1!=iCount2 ){
      test_error(&err, "Isolation failure - %lld %lld", iCount1, iCount2);
    }
  }
  closedb(&err, &db);

  print_and_free_err(&err);
  return 0;
}

static void checkpoint_starvation_main(int nMs, CheckpointStarvationCtx *p){
  Error err = {0};
  Sqlite db = {0};
  Threadset threads = {0};
  int nInsert = 0;
  int i;

  opendb(&err, &db, "test.db", 1);
  sql_script(&err, &db, 
      "PRAGMA page_size = 1024;"
      "PRAGMA journal_mode = WAL;"
      "CREATE TABLE t1(x);"
  );

  setstoptime(&err, nMs);

  for(i=0; i<4; i++){
    launch_thread(&err, &threads, checkpoint_starvation_reader, 0);
    usleep(CHECKPOINT_STARVATION_READMS*1000/4);
  }

  sqlite3_wal_hook(db.db, checkpoint_starvation_walhook, (void *)p);
  while( !timetostop(&err) ){
    sql_script(&err, &db, "INSERT INTO t1 VALUES(randomblob(1200))");
    nInsert++;
  }

  printf(" Checkpoint mode  : %s\n",
      p->eMode==SQLITE_CHECKPOINT_PASSIVE ? "PASSIVE" : "RESTART"
  );
  printf(" Peak WAL         : %d frames\n", p->nMaxFrame);
  printf(" Transaction count: %d transactions\n", nInsert);

  join_all_threads(&err, &threads);
  closedb(&err, &db);
  print_and_free_err(&err);
}

static void checkpoint_starvation_1(int nMs){
  Error err = {0};
  CheckpointStarvationCtx ctx = { SQLITE_CHECKPOINT_PASSIVE, 0 };
  checkpoint_starvation_main(nMs, &ctx);
  if( ctx.nMaxFrame<(CHECKPOINT_STARVATION_FRAMELIMIT*10) ){
    test_error(&err, "WAL failed to grow - %d frames", ctx.nMaxFrame);
  }
  print_and_free_err(&err);
}

static void checkpoint_starvation_2(int nMs){
  Error err = {0};
  CheckpointStarvationCtx ctx = { SQLITE_CHECKPOINT_RESTART, 0 };
  checkpoint_starvation_main(nMs, &ctx);
  if( ctx.nMaxFrame>CHECKPOINT_STARVATION_FRAMELIMIT+10 ){
    test_error(&err, "WAL grew too large - %d frames", ctx.nMaxFrame);
  }
  print_and_free_err(&err);
}


Changes to test/wal.test.

283
284
285
286
287
288
289
290
291

292
293
294
295
296
297
298
...
538
539
540
541
542
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
...
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
...
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
...
793
794
795
796
797
798
799
800
801

802
803
804
805
806
807
808
...
841
842
843
844
845
846
847

848
849
850
851
852
853
854
....
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
....
1396
1397
1398
1399
1400
1401
1402
1403
1404
1405
1406
1407
1408
1409
1410
1411
1412
1413
....
1433
1434
1435
1436
1437
1438
1439
1440
1441

1442
1443
1444
1445
1446
1447
1448
    PRAGMA wal_checkpoint;
    BEGIN;
      INSERT INTO t2 VALUES('w', 'x');
      SAVEPOINT save;
        INSERT INTO t2 VALUES('y', 'z');
      ROLLBACK TO save;
    COMMIT;
    SELECT * FROM t2;
  }

} {w x}


reopen_db
do_test wal-5.1 {
  execsql {
    CREATE TEMP TABLE t2(a, b);
................................................................................
  # checkpointing the database. But not from writing to it.
  #
  do_test wal-10.$tn.11 {
    sql2 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10}
  do_test wal-10.$tn.12 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}   ;# Reader no longer block checkpoints
  do_test wal-10.$tn.13 {
    execsql { INSERT INTO t1 VALUES(11, 12) }
    sql2 {SELECT * FROM t1}
  } {1 2 3 4 5 6 7 8 9 10}

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}

  # The following series of test cases used to verify another blocking
  # case in WAL - a case which no longer blocks.
  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {}}
  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } {}
  do_test wal-10.$tn.18 {
    sql3 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {0 {}}
  do_test wal-10.$tn.20 {
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Another series of tests that used to demonstrate blocking behavior
  # but which now work.
  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } {}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {0 {}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(17, 18) }
  } {}
  do_test wal-10.$tn.28 {
................................................................................
      sqlite3_step $::STMT
    }
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}
  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(19, 20) }
    catchsql { PRAGMA wal_checkpoint }
  } {0 {}}
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } {}

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {
    sql2 COMMIT
................................................................................
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  do_test wal-10.$tn.36 {
    catchsql { PRAGMA wal_checkpoint }
  } {0 {}}
  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }
  } {}
}

#-------------------------------------------------------------------------
# This block of tests, wal-11.*, test that nothing goes terribly wrong
# if frames must be written to the log file before a transaction is
# committed (in order to free up memory).
#
................................................................................
  execsql {
    PRAGMA wal_checkpoint;
    UPDATE t2 SET y = 2 WHERE x = 'B'; 
    PRAGMA wal_checkpoint;
    UPDATE t1 SET y = 1 WHERE x = 'A';
    PRAGMA wal_checkpoint;
    UPDATE t1 SET y = 0 WHERE x = 'A';
    SELECT * FROM t2;
  }

} {B 2}
do_test wal-12.6 {
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  sqlite3_wal db2 test2.db
  execsql { SELECT * FROM t2 } db2
} {B 2}
................................................................................
  set fd [open test.db-wal w]
  seek $fd [expr 200*1024*1024]
  puts $fd ""
  close $fd
  sqlite3 db test.db
  execsql { SELECT * FROM t2 }
} {B 2}

do_test wal-13.1.3 {
  db close
  file exists test.db-wal
} {0}

do_test wal-13.2.1 {
  sqlite3 db test.db
................................................................................
# The following block of tests - wal-16.* - test that if a NULL pointer or
# an empty string is passed as the second argument of the wal_checkpoint()
# API, an attempt is made to checkpoint all attached databases.
#
foreach {tn ckpt_cmd ckpt_res ckpt_main ckpt_aux} {
  1 {sqlite3_wal_checkpoint db}              SQLITE_OK     1 1
  2 {sqlite3_wal_checkpoint db ""}           SQLITE_OK     1 1
  3 {db eval "PRAGMA wal_checkpoint"}        {}            1 1

  4 {sqlite3_wal_checkpoint db main}         SQLITE_OK     1 0
  5 {sqlite3_wal_checkpoint db aux}          SQLITE_OK     0 1
  6 {sqlite3_wal_checkpoint db temp}         SQLITE_OK     0 0
  7 {db eval "PRAGMA main.wal_checkpoint"}   {}            1 0
  8 {db eval "PRAGMA aux.wal_checkpoint"}    {}            0 1
  9 {db eval "PRAGMA temp.wal_checkpoint"}   {}            0 0
} {
  do_test wal-16.$tn.1 {
    file delete -force test2.db test2.db-wal test2.db-journal
    file delete -force test.db test.db-wal test.db-journal

    sqlite3 db test.db
    execsql {
................................................................................
      INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 8192 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 16384 */
    } }
  }
} {0}
do_test wal-20.3 {
  close $::buddy
  execsql {
    PRAGMA wal_checkpoint;
    SELECT count(*) FROM t1;
  }
} {16384}
do_test wal-20.4 {
  db close
  sqlite3 db test.db
  execsql { SELECT count(*) FROM t1 }
} {16384}
integrity_check wal-20.5
................................................................................
    PRAGMA cache_size = 10;
    PRAGMA wal_checkpoint;
    BEGIN;
      SAVEPOINT s;
        INSERT INTO t1 SELECT randomblob(900), randomblob(900) FROM t1;
      ROLLBACK TO s;
    COMMIT;
    SELECT * FROM t1;
  }

} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test wal-21.3 {
  execsql { PRAGMA integrity_check }
} {ok}

#-------------------------------------------------------------------------
# Test reading and writing of databases with different page-sizes.







<

>







 







|









|









|


|







 







|





|







 







|



|







 







|







|







 







<

>







 







>







 







|




|
|
|







 







<
|
|
<







 







<

>







283
284
285
286
287
288
289

290
291
292
293
294
295
296
297
298
...
538
539
540
541
542
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
...
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
...
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
...
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
...
793
794
795
796
797
798
799

800
801
802
803
804
805
806
807
808
...
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
....
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
....
1397
1398
1399
1400
1401
1402
1403

1404
1405

1406
1407
1408
1409
1410
1411
1412
....
1432
1433
1434
1435
1436
1437
1438

1439
1440
1441
1442
1443
1444
1445
1446
1447
    PRAGMA wal_checkpoint;
    BEGIN;
      INSERT INTO t2 VALUES('w', 'x');
      SAVEPOINT save;
        INSERT INTO t2 VALUES('y', 'z');
      ROLLBACK TO save;
    COMMIT;

  }
  execsql { SELECT * FROM t2 }
} {w x}


reopen_db
do_test wal-5.1 {
  execsql {
    CREATE TEMP TABLE t2(a, b);
................................................................................
  # checkpointing the database. But not from writing to it.
  #
  do_test wal-10.$tn.11 {
    sql2 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10}
  do_test wal-10.$tn.12 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 13 13}}   ;# Reader no longer block checkpoints
  do_test wal-10.$tn.13 {
    execsql { INSERT INTO t1 VALUES(11, 12) }
    sql2 {SELECT * FROM t1}
  } {1 2 3 4 5 6 7 8 9 10}

  # Writers do not block checkpoints any more either.
  #
  do_test wal-10.$tn.14 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 15 13}}

  # The following series of test cases used to verify another blocking
  # case in WAL - a case which no longer blocks.
  #
  do_test wal-10.$tn.15 {
    sql2 { COMMIT; BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.16 {
    catchsql { PRAGMA wal_checkpoint } 
  } {0 {0 15 15}}
  do_test wal-10.$tn.17 {
    execsql { PRAGMA wal_checkpoint } 
  } {0 15 15}
  do_test wal-10.$tn.18 {
    sql3 { BEGIN; SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12}
  do_test wal-10.$tn.19 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {0 {}}
  do_test wal-10.$tn.20 {
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # Another series of tests that used to demonstrate blocking behavior
  # but which now work.
  #
  do_test wal-10.$tn.23 {
    execsql { PRAGMA wal_checkpoint }
  } {0 17 17}
  do_test wal-10.$tn.24 {
    sql2 { BEGIN; SELECT * FROM t1; }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
  do_test wal-10.$tn.25 {
    execsql { PRAGMA wal_checkpoint }
  } {0 17 17}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {0 {}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(17, 18) }
  } {}
  do_test wal-10.$tn.28 {
................................................................................
      sqlite3_step $::STMT
    }
    execsql { SELECT * FROM t1 }
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18}
  do_test wal-10.$tn.29 {
    execsql { INSERT INTO t1 VALUES(19, 20) }
    catchsql { PRAGMA wal_checkpoint }
  } {0 {0 6 0}}
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA wal_checkpoint }
  } {0 6 0}

  # At one point, if a reader failed to upgrade to a writer because it
  # was reading an old snapshot, the write-locks were not being released.
  # Test that this bug has been fixed.
  #
  do_test wal-10.$tn.31 {
    sql2 COMMIT
................................................................................
    sql2 {
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  do_test wal-10.$tn.36 {
    catchsql { PRAGMA wal_checkpoint }
  } {0 {0 16 16}}
  do_test wal-10.$tn.36 {
    sql3 { INSERT INTO t1 VALUES('e', 'f') }
    sql2 { SELECT * FROM t1 }
  } {a b c d}
  do_test wal-10.$tn.37 {
    sql2 COMMIT
    execsql { PRAGMA wal_checkpoint }
  } {0 18 18}
}

#-------------------------------------------------------------------------
# This block of tests, wal-11.*, test that nothing goes terribly wrong
# if frames must be written to the log file before a transaction is
# committed (in order to free up memory).
#
................................................................................
  execsql {
    PRAGMA wal_checkpoint;
    UPDATE t2 SET y = 2 WHERE x = 'B'; 
    PRAGMA wal_checkpoint;
    UPDATE t1 SET y = 1 WHERE x = 'A';
    PRAGMA wal_checkpoint;
    UPDATE t1 SET y = 0 WHERE x = 'A';

  }
  execsql {  SELECT * FROM t2 }
} {B 2}
do_test wal-12.6 {
  file copy -force test.db test2.db
  file copy -force test.db-wal test2.db-wal
  sqlite3_wal db2 test2.db
  execsql { SELECT * FROM t2 } db2
} {B 2}
................................................................................
  set fd [open test.db-wal w]
  seek $fd [expr 200*1024*1024]
  puts $fd ""
  close $fd
  sqlite3 db test.db
  execsql { SELECT * FROM t2 }
} {B 2}
breakpoint
do_test wal-13.1.3 {
  db close
  file exists test.db-wal
} {0}

do_test wal-13.2.1 {
  sqlite3 db test.db
................................................................................
# The following block of tests - wal-16.* - test that if a NULL pointer or
# an empty string is passed as the second argument of the wal_checkpoint()
# API, an attempt is made to checkpoint all attached databases.
#
foreach {tn ckpt_cmd ckpt_res ckpt_main ckpt_aux} {
  1 {sqlite3_wal_checkpoint db}              SQLITE_OK     1 1
  2 {sqlite3_wal_checkpoint db ""}           SQLITE_OK     1 1
  3 {db eval "PRAGMA wal_checkpoint"}        {0 10 10}     1 1

  4 {sqlite3_wal_checkpoint db main}         SQLITE_OK     1 0
  5 {sqlite3_wal_checkpoint db aux}          SQLITE_OK     0 1
  6 {sqlite3_wal_checkpoint db temp}         SQLITE_OK     0 0
  7 {db eval "PRAGMA main.wal_checkpoint"}   {0 10 10}     1 0
  8 {db eval "PRAGMA aux.wal_checkpoint"}    {0 16 16}     0 1
  9 {db eval "PRAGMA temp.wal_checkpoint"}   {0 -1 -1}     0 0
} {
  do_test wal-16.$tn.1 {
    file delete -force test2.db test2.db-wal test2.db-journal
    file delete -force test.db test.db-wal test.db-journal

    sqlite3 db test.db
    execsql {
................................................................................
      INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 8192 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1;       /* 16384 */
    } }
  }
} {0}
do_test wal-20.3 {
  close $::buddy

  execsql { PRAGMA wal_checkpoint }
  execsql { SELECT count(*) FROM t1 }

} {16384}
do_test wal-20.4 {
  db close
  sqlite3 db test.db
  execsql { SELECT count(*) FROM t1 }
} {16384}
integrity_check wal-20.5
................................................................................
    PRAGMA cache_size = 10;
    PRAGMA wal_checkpoint;
    BEGIN;
      SAVEPOINT s;
        INSERT INTO t1 SELECT randomblob(900), randomblob(900) FROM t1;
      ROLLBACK TO s;
    COMMIT;

  }
  execsql { SELECT * FROM t1 }
} {1 2 3 4 5 6 7 8 9 10 11 12}
do_test wal-21.3 {
  execsql { PRAGMA integrity_check }
} {ok}

#-------------------------------------------------------------------------
# Test reading and writing of databases with different page-sizes.

Changes to test/wal2.test.

344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
...
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
...
805
806
807
808
809
810
811

812
813
814
815
816
817
818
819
820
821
....
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE data(x);
    INSERT INTO data VALUES('need xShmOpen to see this');
    PRAGMA wal_checkpoint;
  }
} {wal}
do_test wal2-4.2 {
  db close
  testvfs tvfs -noshm 1
  sqlite3 db test.db -vfs tvfs
  catchsql { SELECT * FROM data }
} {1 {unable to open database file}}
do_test wal2-4.3 {
................................................................................
    PRAGMA journal_mode = wal;
    PRAGMA locking_mode = exclusive;
    CREATE TABLE t2(a, b);
    PRAGMA wal_checkpoint;
    INSERT INTO t2 VALUES('I', 'II');
    PRAGMA journal_mode;
  }
} {wal exclusive wal}
do_test wal2-6.5.2 {
  execsql {
    PRAGMA locking_mode = normal;
    INSERT INTO t2 VALUES('III', 'IV');
    PRAGMA locking_mode = exclusive;
    SELECT * FROM t2;
  }
} {normal exclusive I II III IV}
do_test wal2-6.5.3 {
  execsql { PRAGMA wal_checkpoint }
} {}
db close

proc lock_control {method filename handle spec} {
  foreach {start n op type} $spec break
  if {$op == "lock"} { return SQLITE_IOERR }
  return SQLITE_OK
}
................................................................................
  execsql {
    PRAGMA auto_vacuum=OFF;
    PRAGMA page_size = 1024;
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(zeroblob(8188*1020));
    CREATE TABLE t2(y);

  }
  execsql {
    PRAGMA wal_checkpoint;
    SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
  }
} {1}
do_test wal2-8.1.3 {
  execsql {
    PRAGMA cache_size = 10;
    CREATE TABLE t3(z);
................................................................................
      set b(1,1) {0 {}}
      do_test wal2-13.$tn.4 {
        catchsql { INSERT INTO t1 DEFAULT VALUES }
      } $b($can_read,$can_write)
    }
    catch { db close }
  }
} 

#-------------------------------------------------------------------------
# Test that "PRAGMA checkpoint_fullsync" appears to be working.
#
foreach {tn sql reslist} {
  1 { }                                 {8 0 3 0 5 0}
  2 { PRAGMA checkpoint_fullfsync = 1 } {8 4 3 2 5 2}







|







 







|










|







 







>


<







 







|







344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
...
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
...
805
806
807
808
809
810
811
812
813
814

815
816
817
818
819
820
821
....
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
  sqlite3 db test.db
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE data(x);
    INSERT INTO data VALUES('need xShmOpen to see this');
    PRAGMA wal_checkpoint;
  }
} {wal 0 5 5}
do_test wal2-4.2 {
  db close
  testvfs tvfs -noshm 1
  sqlite3 db test.db -vfs tvfs
  catchsql { SELECT * FROM data }
} {1 {unable to open database file}}
do_test wal2-4.3 {
................................................................................
    PRAGMA journal_mode = wal;
    PRAGMA locking_mode = exclusive;
    CREATE TABLE t2(a, b);
    PRAGMA wal_checkpoint;
    INSERT INTO t2 VALUES('I', 'II');
    PRAGMA journal_mode;
  }
} {wal exclusive 0 3 3 wal}
do_test wal2-6.5.2 {
  execsql {
    PRAGMA locking_mode = normal;
    INSERT INTO t2 VALUES('III', 'IV');
    PRAGMA locking_mode = exclusive;
    SELECT * FROM t2;
  }
} {normal exclusive I II III IV}
do_test wal2-6.5.3 {
  execsql { PRAGMA wal_checkpoint }
} {0 4 4}
db close

proc lock_control {method filename handle spec} {
  foreach {start n op type} $spec break
  if {$op == "lock"} { return SQLITE_IOERR }
  return SQLITE_OK
}
................................................................................
  execsql {
    PRAGMA auto_vacuum=OFF;
    PRAGMA page_size = 1024;
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(x);
    INSERT INTO t1 VALUES(zeroblob(8188*1020));
    CREATE TABLE t2(y);
    PRAGMA wal_checkpoint;
  }
  execsql {

    SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
  }
} {1}
do_test wal2-8.1.3 {
  execsql {
    PRAGMA cache_size = 10;
    CREATE TABLE t3(z);
................................................................................
      set b(1,1) {0 {}}
      do_test wal2-13.$tn.4 {
        catchsql { INSERT INTO t1 DEFAULT VALUES }
      } $b($can_read,$can_write)
    }
    catch { db close }
  }
}

#-------------------------------------------------------------------------
# Test that "PRAGMA checkpoint_fullsync" appears to be working.
#
foreach {tn sql reslist} {
  1 { }                                 {8 0 3 0 5 0}
  2 { PRAGMA checkpoint_fullfsync = 1 } {8 4 3 2 5 2}

Changes to test/wal3.test.

423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
...
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
...
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
do_test wal3-6.1.2 {
  sqlite3 db2 test.db
  sqlite3 db3 test.db
  execsql { BEGIN ; SELECT * FROM t1 } db3
} {o t t f}
do_test wal3-6.1.3 {
  execsql { PRAGMA wal_checkpoint } db2
} {}

# At this point the log file has been fully checkpointed. However, 
# connection [db3] holds a lock that prevents the log from being wrapped.
# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
# as it is obtaining the lock, [db2] appends to the log file.
#
T filter xShmLock
................................................................................
      BEGIN;
      SELECT * FROM t1;
    }]
  }
}
do_test wal3-6.2.2 {
  execsql { PRAGMA wal_checkpoint }
} {}
do_test wal3-6.2.3 {
  set ::R
} {h h l b}
do_test wal3-6.2.4 {
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('b', 'c'); }
  set sz2 [file size test.db-wal]
................................................................................
    PRAGMA journal_mode = WAL;
    CREATE TABLE b(c);
    INSERT INTO b VALUES('Tehran');
    INSERT INTO b VALUES('Qom');
    INSERT INTO b VALUES('Markazi');
    PRAGMA wal_checkpoint;
  }
} {wal}
do_test wal3-8.2 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi}
do_test wal3-8.3 {
  db eval { SELECT * FROM b } {
    db eval { INSERT INTO b VALUES('Qazvin') }
    set r [db2 eval { SELECT * FROM b }]







|







 







|







 







|







423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
...
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
...
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
do_test wal3-6.1.2 {
  sqlite3 db2 test.db
  sqlite3 db3 test.db
  execsql { BEGIN ; SELECT * FROM t1 } db3
} {o t t f}
do_test wal3-6.1.3 {
  execsql { PRAGMA wal_checkpoint } db2
} {0 7 7}

# At this point the log file has been fully checkpointed. However, 
# connection [db3] holds a lock that prevents the log from being wrapped.
# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But
# as it is obtaining the lock, [db2] appends to the log file.
#
T filter xShmLock
................................................................................
      BEGIN;
      SELECT * FROM t1;
    }]
  }
}
do_test wal3-6.2.2 {
  execsql { PRAGMA wal_checkpoint }
} {0 7 7}
do_test wal3-6.2.3 {
  set ::R
} {h h l b}
do_test wal3-6.2.4 {
  set sz1 [file size test.db-wal]
  execsql { INSERT INTO t1 VALUES('b', 'c'); }
  set sz2 [file size test.db-wal]
................................................................................
    PRAGMA journal_mode = WAL;
    CREATE TABLE b(c);
    INSERT INTO b VALUES('Tehran');
    INSERT INTO b VALUES('Qom');
    INSERT INTO b VALUES('Markazi');
    PRAGMA wal_checkpoint;
  }
} {wal 0 9 9}
do_test wal3-8.2 {
  execsql { SELECT * FROM b }
} {Tehran Qom Markazi}
do_test wal3-8.3 {
  db eval { SELECT * FROM b } {
    db eval { INSERT INTO b VALUES('Qazvin') }
    set r [db2 eval { SELECT * FROM b }]

Added test/wal5.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
# 2010 April 13
#
# The author disclaims copyright to this source code.  In place of
# a legal notice, here is a blessing:
#
#    May you do good and not evil.
#    May you find forgiveness for yourself and forgive others.
#    May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.  The
# focus of this file is testing the operation of "blocking-checkpoint"
# operations.
#

set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/lock_common.tcl
source $testdir/wal_common.tcl
ifcapable !wal {finish_test ; return }

set testprefix wal5

proc db_page_count  {{file test.db}} { expr [file size $file] / 1024 }
proc wal_page_count {{file test.db}} { wal_frame_count ${file}-wal 1024 }


do_multiclient_test tn {


  set ::nBusyHandler 0
  set ::busy_handler_script ""
  proc busyhandler {n} {
    incr ::nBusyHandler 
    eval $::busy_handler_script
    return 0
  }

  proc reopen_all {} {
    code1 {db close}
    code2 {db2 close}
    code3 {db3 close}
    code1 {sqlite3 db test.db}
    code2 {sqlite3 db2 test.db}
    code3 {sqlite3 db3 test.db}
    sql1  { PRAGMA synchronous = NORMAL }
    code1 { db busy busyhandler }
  }

  do_test 1.$tn.1 {
    reopen_all
    sql1 {
      PRAGMA page_size = 1024;
      PRAGMA auto_vacuum = 0;
      CREATE TABLE t1(x, y);
      PRAGMA journal_mode = WAL;
      INSERT INTO t1 VALUES(1, zeroblob(1200));
      INSERT INTO t1 VALUES(2, zeroblob(1200));
      INSERT INTO t1 VALUES(3, zeroblob(1200));
    }
    expr [file size test.db] / 1024
  } {2}

  # Have connection 2 grab a read-lock on the current snapshot.
  do_test 1.$tn.2 { sql2 { BEGIN; SELECT x FROM t1 } } {1 2 3}

  # Attempt a checkpoint.
  do_test 1.$tn.3 {
    sql1 { PRAGMA wal_checkpoint }
    list [db_page_count] [wal_page_count]
  } {5 9}

  # Write to the db again. The log cannot wrap because of the lock still
  # held by connection 2. The busy-handler has not yet been invoked.
  do_test 1.$tn.4 {
    sql1 { INSERT INTO t1 VALUES(4, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {5 12 0}

  # Now do a blocking-checkpoint. Set the busy-handler up so that connection
  # 2 releases its lock on the 6th invocation. The checkpointer should then
  # proceed to checkpoint the entire log file. Next write should go to the 
  # start of the log file.
  #
  set ::busy_handler_script { if {$n==5} { sql2 COMMIT } }
  do_test 1.$tn.5 {
    sql1 { PRAGMA wal_checkpoint = RESTART }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {6 12 6}
  do_test 1.$tn.6 {
    set ::nBusyHandler 0
    sql1 { INSERT INTO t1 VALUES(5, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {6 12 0}

  do_test 1.$tn.7 {
    reopen_all
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {7 0 0}

  do_test 1.$tn.8  { sql2 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5}
  do_test 1.$tn.9  {
    sql1 { INSERT INTO t1 VALUES(6, zeroblob(1200)) }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {7 5 0}
  do_test 1.$tn.10 { sql3 { BEGIN ; SELECT x FROM t1 } } {1 2 3 4 5 6}

  set ::busy_handler_script { 
    if {$n==5} { sql2 COMMIT } 
    if {$n==6} { set ::db_file_size [db_page_count] }
    if {$n==7} { sql3 COMMIT }
  }
  do_test 1.$tn.11 {
    sql1 { PRAGMA wal_checkpoint = RESTART }
    list [db_page_count] [wal_page_count] $::nBusyHandler
  } {10 5 8}
  do_test 1.$tn.12 { set ::db_file_size } 10
}


#-------------------------------------------------------------------------
# This block of tests explores checkpoint operations on more than one 
# database file.
#
proc setup_and_attach_aux {} {
  sql1 { ATTACH 'test.db2' AS aux }
  sql2 { ATTACH 'test.db2' AS aux }
  sql3 { ATTACH 'test.db2' AS aux }
  sql1 {
    PRAGMA main.page_size=1024; PRAGMA main.journal_mode=WAL;
    PRAGMA aux.page_size=1024;  PRAGMA aux.journal_mode=WAL;
  }
}

proc file_page_counts {} {
  list [db_page_count  test.db ] \
       [wal_page_count test.db ] \
       [db_page_count  test.db2] \
       [wal_page_count test.db2]
}

# Test that executing "PRAGMA wal_checkpoint" checkpoints all attached
# databases, not just the main db.
#
do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.1.$tn.1 {
    sql1 {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}
  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.1.$tn.3 { sql1 { PRAGMA wal_checkpoint } } {0 5 5}
  do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}
}

do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.2.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
      INSERT INTO t2 VALUES(3, 4);
    }
  } {}
  do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}
  do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.2.$tn.4 { sql1 { PRAGMA wal_checkpoint = RESTART } } {1 5 5}
  do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}
}

do_multiclient_test tn {
  setup_and_attach_aux
  do_test 2.3.$tn.1 {
    execsql {
      CREATE TABLE t1(a, b);
      INSERT INTO t1 VALUES(1, 2);
      CREATE TABLE aux.t2(a, b);
      INSERT INTO t2 VALUES(1, 2);
    }
  } {}
  do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}
  do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
  do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
  do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
  do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}
  do_test 2.3.$tn.7 { sql1 { PRAGMA wal_checkpoint = FULL } } {1 7 5}
  do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}
}

# Check that checkpoints block on the correct locks. And respond correctly
# if they cannot obtain those locks. There are three locks that a checkpoint
# may block on (in the following order):
#
#   1. The writer lock: FULL and RESTART checkpoints block until any writer
#      process releases its lock.
#
#   2. Readers using part of the log file. FULL and RESTART checkpoints block
#      until readers using part (but not all) of the log file have finished.
#
#   3. Readers using any of the log file. After copying data into the
#      database file, RESTART checkpoints block until readers using any part
#      of the log file have finished.
#
# This test case involves running a checkpoint while there exist other 
# processes holding all three types of locks.
#
foreach {tn1 checkpoint busy_on ckpt_expected expected} {
  1   PASSIVE   -   {0 5 5}   -
  2   TYPO      -   {0 5 5}   -

  3   FULL      -   {0 7 7}   2
  4   FULL      1   {1 5 5}   1
  5   FULL      2   {1 7 5}   2
  6   FULL      3   {0 7 7}   2

  7   RESTART   -   {0 7 7}   3
  8   RESTART   1   {1 5 5}   1
  9   RESTART   2   {1 7 5}   2
  10  RESTART   3   {1 7 7}   3

} {
  do_multiclient_test tn {
    setup_and_attach_aux
  
    proc busyhandler {x} {
      set ::max_busyhandler $x
      if {$::busy_on!="-" && $x==$::busy_on} { return 1 }
      switch -- $x {
        1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" }
        2 { sql3 "COMMIT" }
        3 { sql2 "COMMIT" }
      }
      return 0
    }
    set ::max_busyhandler -
  
    do_test 2.4.$tn1.$tn.1 {
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
      }
      sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
      sql3 { BEGIN; SELECT * FROM t1 }
    } {1 2}
  
    do_test 2.4.$tn1.$tn.2 {
      code1 { db busy busyhandler }
      sql1 "PRAGMA wal_checkpoint = $checkpoint"
    } $ckpt_expected
    do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
  }
}


finish_test

Changes to test/wal_common.tcl.

14
15
16
17
18
19
20

21

22
23
24
25
26
27
28
#

proc wal_file_size {nFrame pgsz} {
  expr {32 + ($pgsz+24)*$nFrame}
}

proc wal_frame_count {zFile pgsz} {

  set f [file size $zFile]

  expr {($f - 32) / ($pgsz+24)}
}

proc wal_cksum_intlist {ckv1 ckv2 intlist} {
  upvar $ckv1 c1
  upvar $ckv2 c2
  foreach {v1 v2} $intlist {







>

>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
#

proc wal_file_size {nFrame pgsz} {
  expr {32 + ($pgsz+24)*$nFrame}
}

proc wal_frame_count {zFile pgsz} {
  if {[file exists $zFile]==0} { return 0 }
  set f [file size $zFile]
  if {$f < 32} { return 0 }
  expr {($f - 32) / ($pgsz+24)}
}

proc wal_cksum_intlist {ckv1 ckv2 intlist} {
  upvar $ckv1 c1
  upvar $ckv2 c2
  foreach {v1 v2} $intlist {