Index: src/main.c ================================================================== --- src/main.c +++ src/main.c @@ -1189,16 +1189,16 @@ #ifndef SQLITE_OMIT_WAL /* ** The sqlite3_wal_hook() callback registered by sqlite3_wal_autocheckpoint(). ** Return non-zero, indicating to the caller that a checkpoint should be run, ** if the number of frames in the log file is greater than -** sqlite3.nDefaultCheckpoint (the value configured by wal_autocheckpoint()). +** sqlite3.nAutoCheckpoint (the value configured by wal_autocheckpoint()). */ static int defaultWalHook(void *p, sqlite3 *db, const char *z, int nFrame){ UNUSED_PARAMETER(p); UNUSED_PARAMETER(z); - return ( nFrame>=db->nDefaultCheckpoint ); + return ( nFrame>=db->nAutoCheckpoint ); } /* ** Configure an sqlite3_wal_hook() callback to automatically checkpoint ** a database after committing a transaction if there are nFrame or @@ -1211,12 +1211,12 @@ ** configured by this function. */ int sqlite3_wal_autocheckpoint(sqlite3 *db, int nFrame){ sqlite3_mutex_enter(db->mutex); if( nFrame>0 ){ - db->nDefaultCheckpoint = nFrame; sqlite3_wal_hook(db, defaultWalHook, 0); + db->nAutoCheckpoint = nFrame; }else{ sqlite3_wal_hook(db, 0, 0); } sqlite3_mutex_leave(db->mutex); return SQLITE_OK; @@ -1234,10 +1234,11 @@ void *pRet; sqlite3_mutex_enter(db->mutex); pRet = db->pWalArg; db->xWalCallback = xCallback; db->pWalArg = pArg; + db->nAutoCheckpoint = 0; sqlite3_mutex_leave(db->mutex); return pRet; } /* @@ -1868,11 +1869,11 @@ /* Enable the lookaside-malloc subsystem */ setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside, sqlite3GlobalConfig.nLookaside); - sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_CACHE_SIZE); + sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_WAL_AUTOCHECKPOINT); opendb_out: if( db ){ assert( db->mutex!=0 || isThreadsafe==0 || sqlite3GlobalConfig.bFullMutex==0 ); sqlite3_mutex_leave(db->mutex); Index: src/pragma.c ================================================================== --- src/pragma.c +++ src/pragma.c @@ -1400,17 +1400,34 @@ }else #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */ #ifndef SQLITE_OMIT_WAL /* - ** PRAGMA [database.]checkpoint + ** PRAGMA [database.]wal_checkpoint ** ** Checkpoint the database. */ - if( sqlite3StrICmp(zLeft, "checkpoint")==0 ){ + if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){ + if( sqlite3ReadSchema(pParse) ) goto pragma_out; sqlite3VdbeAddOp3(v, OP_Checkpoint, iDb, 0, 0); }else + + /* + ** PRAGMA wal_autocheckpoint + ** PRAGMA wal_autocheckpoint = N + ** + ** Configure a database connection to automatically checkpoint a database + ** after accumulating N frames in the log. Or query for the current value + ** of N. + */ + if( sqlite3StrICmp(zLeft, "wal_autocheckpoint")==0 ){ + if( zRight ){ + int nAuto = atoi(zRight); + sqlite3_wal_autocheckpoint(db, nAuto); + } + returnSingleInt(pParse, "wal_autocheckpoint", db->nAutoCheckpoint); + }else #endif #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST) /* ** Report the current state of file logs for all databases Index: src/sqliteInt.h ================================================================== --- src/sqliteInt.h +++ src/sqliteInt.h @@ -822,11 +822,11 @@ void *pRollbackArg; /* Argument to xRollbackCallback() */ void (*xRollbackCallback)(void*); /* Invoked at every commit. */ void *pUpdateArg; void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64); #ifndef SQLITE_OMIT_WAL - int nDefaultCheckpoint; /* Value configured by wal_autocheckpoint() */ + int nAutoCheckpoint; /* Value configured by wal_autocheckpoint() */ int (*xWalCallback)(void *, sqlite3 *, const char *, int); void *pWalArg; #endif void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*); void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*); Index: src/sqliteLimit.h ================================================================== --- src/sqliteLimit.h +++ src/sqliteLimit.h @@ -106,10 +106,18 @@ #endif #ifndef SQLITE_DEFAULT_TEMP_CACHE_SIZE # define SQLITE_DEFAULT_TEMP_CACHE_SIZE 500 #endif +/* +** The default number of frames to accumulate in the log file before +** checkpointing the database in WAL mode. +*/ +#ifndef SQLITE_DEFAULT_WAL_AUTOCHECKPOINT +# define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000 +#endif + /* ** The maximum number of attached databases. This must be between 0 ** and 30. The upper bound on 30 is because a 32-bit integer bitmap ** is used internally to track attached databases. */ Index: src/vdbeapi.c ================================================================== --- src/vdbeapi.c +++ src/vdbeapi.c @@ -319,11 +319,11 @@ if( pBt ){ int nEntry = sqlite3PagerWalCallback(sqlite3BtreePager(pBt)); if( db->xWalCallback && nEntry>0 && rc==SQLITE_OK && db->xWalCallback(db->pWalArg, db, db->aDb[i].zName, nEntry) ){ - rc = sqlite3PagerCheckpoint(sqlite3BtreePager(pBt)); + rc = sqlite3Checkpoint(db, i); } } } #endif return rc; Index: test/savepoint.test ================================================================== --- test/savepoint.test +++ test/savepoint.test @@ -790,11 +790,11 @@ } } {} integrity_check savepoint-11.7 do_test savepoint-11.8 { execsql { ROLLBACK } - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } file size test.db } {8192} do_test savepoint-11.9 { execsql { Index: test/wal.test ================================================================== --- test/wal.test +++ test/wal.test @@ -347,11 +347,11 @@ INSERT INTO t1 VALUES(1, 2); } list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 3 1024]] do_test wal-7.2 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list 2048 [log_file_size 3 1024]] # Execute some transactions in auto-vacuum mode to test database file # truncation. @@ -378,18 +378,18 @@ INSERT INTO t1 SELECT blob(900) FROM t1; /* 4 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 8 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 16 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 32 */ INSERT INTO t1 SELECT blob(900) FROM t1; /* 64 */ - PRAGMA checkpoint; + PRAGMA wal_checkpoint; } file size test.db } [expr 68*1024] do_test wal-8.3 { execsql { DELETE FROM t1 WHERE rowid<54; - PRAGMA checkpoint; + PRAGMA wal_checkpoint; } file size test.db } [expr 14*1024] # Run some "warm-body" tests to ensure that log-summary files with more @@ -424,11 +424,11 @@ execsql {PRAGMA integrity_check } db3 } {ok} db3 close do_test wal-9.4 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } db2 close sqlite3_wal db2 test.db execsql {PRAGMA integrity_check } db2 } {ok} @@ -547,11 +547,11 @@ # 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 checkpoint } + catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} 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} @@ -566,11 +566,11 @@ if {$x<5} { return 0 } return 1 } db busy busyhandler do_test wal-10.$tn.14 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } } {} # Similar to the test above. Except this time, a new read transaction is # started (db3) while the checkpointer is waiting for an old one (db2) to # finish. The checkpointer can finish, but any subsequent write operations @@ -580,21 +580,21 @@ db busy {} do_test wal-10.$tn.15 { sql2 { BEGIN; SELECT * FROM t1; } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.16 { - catchsql { PRAGMA checkpoint } + catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} proc busyhandler x { if {$x==3} { sql3 { BEGIN; SELECT * FROM t1 } } if {$x==4} { sql2 COMMIT } if {$x<5} { return 0 } return 1 } db busy busyhandler do_test wal-10.$tn.17 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } } {} do_test wal-10.$tn.18 { sql3 { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12} do_test wal-10.$tn.19 { @@ -614,17 +614,17 @@ # Set [db3] up as a "region D" reader again. Then upgrade it to a writer # and back down to a reader. Then, check that a checkpoint is not possible # (as [db3] still has a snapshot locked). # do_test wal-10.$tn.23 { - execsql { PRAGMA checkpoint } + 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 checkpoint } + execsql { PRAGMA wal_checkpoint } } {} do_test wal-10.$tn.26 { catchsql { INSERT INTO t1 VALUES(15, 16) } } {1 {database is locked}} do_test wal-10.$tn.27 { @@ -639,15 +639,15 @@ execsql { SELECT * FROM t1 } } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16} db busy {} do_test wal-10.$tn.29 { execsql { INSERT INTO t1 VALUES(17, 18) } - catchsql { PRAGMA checkpoint } + catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} do_test wal-10.$tn.30 { code3 { sqlite3_finalize $::STMT } - execsql { PRAGMA checkpoint } + 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. @@ -683,19 +683,19 @@ } } {a b c d} proc busyhandler x { return 1 } db busy busyhandler do_test wal-10.$tn.36 { - catchsql { PRAGMA checkpoint } + catchsql { PRAGMA wal_checkpoint } } {1 {database is locked}} 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 checkpoint } + execsql { PRAGMA wal_checkpoint } } {} catch { db close } catch { code2 { db2 close } } catch { code3 { db3 close } } @@ -716,11 +716,11 @@ CREATE TABLE t1(x PRIMARY KEY); } list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044] } {1 3} do_test wal-11.2 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 3 [log_file_size 3 1024]] do_test wal-11.3 { execsql { INSERT INTO t1 VALUES( blob(900) ) } list [expr [file size test.db]/1024] [file size test.db-wal] @@ -751,11 +751,11 @@ SELECT count(*) FROM t1; PRAGMA integrity_check; } } {16 ok} do_test wal-11.8 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } list [expr [file size test.db]/1024] [file size test.db-wal] } [list 37 [log_file_size 41 1024]] do_test wal-11.9 { db close list [expr [file size test.db]/1024] [log_deleted test.db-wal] @@ -827,15 +827,15 @@ execsql { SELECT * FROM t2 } db2 } {B 1} db2 close do_test wal-12.5 { execsql { - PRAGMA checkpoint; + PRAGMA wal_checkpoint; UPDATE t2 SET y = 2 WHERE x = 'B'; - PRAGMA checkpoint; + PRAGMA wal_checkpoint; UPDATE t1 SET y = 1 WHERE x = 'A'; - PRAGMA checkpoint; + PRAGMA wal_checkpoint; UPDATE t1 SET y = 0 WHERE x = 'A'; SELECT * FROM t2; } } {B 2} do_test wal-12.6 { @@ -953,19 +953,19 @@ INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); INSERT INTO t1 SELECT randomblob(10), randomblob(100); } - # After executing the "PRAGMA checkpoint", connection [db] was being + # After executing the "PRAGMA wal_checkpoint", connection [db] was being # left with an inconsistent cache. Running the CREATE INDEX statement # in this state led to database corruption. catchsql { - PRAGMA checkpoint; + PRAGMA wal_checkpoint; CREATE INDEX i1 on t1(b); } db2 eval { PRAGMA integrity_check } } {ok} catch { db close } catch { db2 close } finish_test Index: test/walbak.test ================================================================== --- test/walbak.test +++ test/walbak.test @@ -69,11 +69,11 @@ } {wal} do_test walbak-1.5 { list [file size test.db] [file size test.db-wal] } [list 1024 [log_file_size 6 1024]] do_test walbak-1.6 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [log_file_size 6 1024]] do_test walbak-1.7 { execsql { CREATE TABLE t2(a, b); @@ -85,11 +85,11 @@ do_test walbak-1.8 { execsql { VACUUM } list [file size test.db] [file size test.db-wal] } [list [expr 3*1024] [log_file_size 8 1024]] do_test walbak-1.9 { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } list [file size test.db] [file size test.db-wal] } [list [expr 2*1024] [log_file_size 8 1024]] #------------------------------------------------------------------------- # Backups when the source db is modified mid-backup. Index: test/walcrash.test ================================================================== --- test/walcrash.test +++ test/walcrash.test @@ -190,11 +190,11 @@ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ - PRAGMA checkpoint; + PRAGMA wal_checkpoint; INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); } } {1 {child process exited abnormally}} @@ -231,11 +231,11 @@ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 20 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 24 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 28 */ INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4; /* 32 */ - PRAGMA checkpoint; + PRAGMA wal_checkpoint; INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); INSERT INTO t1 VALUES(randomblob(900)); } } {1 {child process exited abnormally}} @@ -258,13 +258,13 @@ PRAGMA journal_mode = wal; BEGIN; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); COMMIT; - PRAGMA checkpoint; + PRAGMA wal_checkpoint; CREATE INDEX i1 ON t1(a); - PRAGMA checkpoint; + PRAGMA wal_checkpoint; } } {1 {child process exited abnormally}} do_test walcrash-7.$i.2 { sqlite3 db test.db Index: test/walhook.test ================================================================== --- test/walhook.test +++ test/walhook.test @@ -9,38 +9,42 @@ # #*********************************************************************** # This file implements regression tests for SQLite library. The # focus of this file is testing the operation of the library in # "PRAGMA journal_mode=WAL" mode. +# +# More specifically, this file contains regression tests for the +# sqlite3_wal_hook() mechanism, including the sqlite3_wal_autocheckpoint() +# and "PRAGMA wal_autocheckpoint" convenience interfaces. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !wal {finish_test ; return } -proc sqlite3_wal {args} { - eval sqlite3 $args - [lindex $args 0] eval { - PRAGMA journal_mode = wal; - PRAGMA synchronous = normal; - PRAGMA page_size = 1024; - } +proc log_file_size {nFrame pgsz} { + expr {12 + ($pgsz+16)*$nFrame} } -sqlite3_wal db test.db -db wal_hook wal_hook set ::wal_hook [list] proc wal_hook {zDb nEntry} { lappend ::wal_hook $zDb $nEntry return 0 } +db wal_hook wal_hook do_test walhook-1.1 { - execsql { CREATE TABLE t1(i PRIMARY KEY, j) } + execsql { + PRAGMA page_size = 1024; + PRAGMA journal_mode = wal; + PRAGMA synchronous = normal; + CREATE TABLE t1(i PRIMARY KEY, j); + } set ::wal_hook } {main 3} + do_test walhook-1.2 { set ::wal_hook [list] execsql { INSERT INTO t1 VALUES(1, 'one') } set ::wal_hook } {main 5} @@ -47,28 +51,61 @@ do_test walhook-1.3 { proc wal_hook {args} { return 1 } execsql { INSERT INTO t1 VALUES(2, 'two') } file size test.db } [expr 3*1024] - do_test walhook-1.4 { proc wal_hook {zDb nEntry} { - execsql { PRAGMA checkpoint } + execsql { PRAGMA wal_checkpoint } return 0 } execsql { CREATE TABLE t2(a, b) } file size test.db } [expr 4*1024] do_test walhook-1.5 { - sqlite3_wal db2 test.db - proc wal_hook {zDb nEntry} { - execsql { PRAGMA checkpoint } db2 + sqlite3 db2 test.db + proc wal_hook {zDb nEntry} { + execsql { PRAGMA wal_checkpoint } db2 return 0 } execsql { CREATE TABLE t3(a PRIMARY KEY, b) } file size test.db } [expr 6*1024] + +db2 close +db close +sqlite3 db test.db +do_test walhook-2.1 { + execsql { PRAGMA synchronous = NORMAL } + execsql { PRAGMA wal_autocheckpoint } +} {1000} +do_test walhook-2.2 { + execsql { PRAGMA wal_autocheckpoint = 10} +} {10} +do_test walhook-2.3 { + execsql { PRAGMA wal_autocheckpoint } +} {10} + +# +# The database connection is configured with "PRAGMA wal_autocheckpoint = 10". +# Check that transactions are written to the log file until it contains at +# least 10 frames, then the database is checkpointed. Subsequent transactions +# are written into the start of the log file. +# +foreach {tn sql dbpages logpages} { + 4 "CREATE TABLE t4(x PRIMARY KEY, y)" 6 3 + 5 "INSERT INTO t4 VALUES(1, 'one')" 6 5 + 6 "INSERT INTO t4 VALUES(2, 'two')" 6 7 + 7 "INSERT INTO t4 VALUES(3, 'three')" 6 9 + 8 "INSERT INTO t4 VALUES(4, 'four')" 8 11 + 9 "INSERT INTO t4 VALUES(5, 'five')" 8 11 +} { + do_test walhook-2.$tn { + execsql $sql + list [file size test.db] [file size test.db-wal] + } [list [expr $dbpages*1024] [log_file_size $logpages 1024]] +} catch { db2 close } catch { db close } finish_test Index: test/walslow.test ================================================================== --- test/walslow.test +++ test/walslow.test @@ -46,11 +46,11 @@ execsql { INSERT INTO t1 VALUES(randomblob($w), randomblob($x)) } execsql { PRAGMA integrity_check } } {ok} do_test walslow-1.seed=$seed.$iTest.2 { - execsql "PRAGMA checkpoint;" + execsql "PRAGMA wal_checkpoint;" execsql { PRAGMA integrity_check } } {ok} do_test walslow-1.seed=$seed.$iTest.3 { file delete -force testX.db testX.db-wal Index: test/walthread.test ================================================================== --- test/walthread.test +++ test/walthread.test @@ -239,11 +239,11 @@ # Each of the N threads runs N read transactions followed by a single write # transaction in a loop as fast as possible. # # There is also a single checkpointer thread. It runs the following loop: # -# 1) Execute "PRAGMA checkpoint" +# 1) Execute "PRAGMA wal_checkpoint" # 2) Sleep for 500 ms. # do_thread_test2 walthread-1 -seconds $seconds(walthread-1) -init { execsql { PRAGMA journal_mode = WAL; @@ -293,11 +293,11 @@ set nRun } -thread ckpt 1 { set nRun 0 while {[tt_continue]} { - db eval "PRAGMA checkpoint" + db eval "PRAGMA wal_checkpoint" usleep 500 incr nRun } set nRun }