/ Check-in [714e5947]
Login
SQLite training in Houston TX on 2019-11-05 (details)
Part of the 2019 Tcl Conference

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

Overview
Comment:Add the "PRAGMA wal_autocheckpoint" command. Rename "PRAGMA checkpoint" to "PRAGMA wal_checkpoint".
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | wal
Files: files | file ages | folders
SHA1: 714e5947264571386f966aa8fcdd5607b5832238
User & Date: dan 2010-05-03 11:05:09
Context
2010-05-03
12:14
Have sqlite3_wal_checkpoint() populate the database handle error message and error code (as returned by sqlite3_errmsg() and sqlite3_errcode()). check-in: ff234cf5 user: dan tags: wal
11:05
Add the "PRAGMA wal_autocheckpoint" command. Rename "PRAGMA checkpoint" to "PRAGMA wal_checkpoint". check-in: 714e5947 user: dan tags: wal
08:19
Merge two wal leaves. check-in: 23c0e6c3 user: dan tags: wal
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/main.c.

  1187   1187   }
  1188   1188   
  1189   1189   #ifndef SQLITE_OMIT_WAL
  1190   1190   /*
  1191   1191   ** The sqlite3_wal_hook() callback registered by sqlite3_wal_autocheckpoint().
  1192   1192   ** Return non-zero, indicating to the caller that a checkpoint should be run,
  1193   1193   ** if the number of frames in the log file is greater than 
  1194         -** sqlite3.nDefaultCheckpoint (the value configured by wal_autocheckpoint()).
         1194  +** sqlite3.nAutoCheckpoint (the value configured by wal_autocheckpoint()).
  1195   1195   */ 
  1196   1196   static int defaultWalHook(void *p, sqlite3 *db, const char *z, int nFrame){
  1197   1197     UNUSED_PARAMETER(p);
  1198   1198     UNUSED_PARAMETER(z);
  1199         -  return ( nFrame>=db->nDefaultCheckpoint );
         1199  +  return ( nFrame>=db->nAutoCheckpoint );
  1200   1200   }
  1201   1201   
  1202   1202   /*
  1203   1203   ** Configure an sqlite3_wal_hook() callback to automatically checkpoint
  1204   1204   ** a database after committing a transaction if there are nFrame or
  1205   1205   ** more frames in the log file. Passing zero or a negative value as the
  1206   1206   ** nFrame parameter disables automatic checkpoints entirely.
................................................................................
  1209   1209   ** registered using sqlite3_wal_hook(). Likewise, registering a callback
  1210   1210   ** using sqlite3_wal_hook() disables the automatic checkpoint mechanism
  1211   1211   ** configured by this function.
  1212   1212   */
  1213   1213   int sqlite3_wal_autocheckpoint(sqlite3 *db, int nFrame){
  1214   1214     sqlite3_mutex_enter(db->mutex);
  1215   1215     if( nFrame>0 ){
  1216         -    db->nDefaultCheckpoint = nFrame;
  1217   1216       sqlite3_wal_hook(db, defaultWalHook, 0);
         1217  +    db->nAutoCheckpoint = nFrame;
  1218   1218     }else{
  1219   1219       sqlite3_wal_hook(db, 0, 0);
  1220   1220     }
  1221   1221     sqlite3_mutex_leave(db->mutex);
  1222   1222     return SQLITE_OK;
  1223   1223   }
  1224   1224   
................................................................................
  1232   1232     void *pArg                      /* First argument passed to xCallback() */
  1233   1233   ){
  1234   1234     void *pRet;
  1235   1235     sqlite3_mutex_enter(db->mutex);
  1236   1236     pRet = db->pWalArg;
  1237   1237     db->xWalCallback = xCallback;
  1238   1238     db->pWalArg = pArg;
         1239  +  db->nAutoCheckpoint = 0;
  1239   1240     sqlite3_mutex_leave(db->mutex);
  1240   1241     return pRet;
  1241   1242   }
  1242   1243   
  1243   1244   /*
  1244   1245   ** Checkpoint database zDb. If zDb is NULL, the main database is checkpointed.
  1245   1246   */
................................................................................
  1866   1867                             SQLITE_DEFAULT_LOCKING_MODE);
  1867   1868   #endif
  1868   1869   
  1869   1870     /* Enable the lookaside-malloc subsystem */
  1870   1871     setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside,
  1871   1872                           sqlite3GlobalConfig.nLookaside);
  1872   1873   
  1873         -  sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_CACHE_SIZE);
         1874  +  sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_WAL_AUTOCHECKPOINT);
  1874   1875   
  1875   1876   opendb_out:
  1876   1877     if( db ){
  1877   1878       assert( db->mutex!=0 || isThreadsafe==0 || sqlite3GlobalConfig.bFullMutex==0 );
  1878   1879       sqlite3_mutex_leave(db->mutex);
  1879   1880     }
  1880   1881     rc = sqlite3_errcode(db);

Changes to src/pragma.c.

  1398   1398         sqlite3VdbeAddOp2(v, OP_ResultRow, 1, 1);
  1399   1399       }
  1400   1400     }else
  1401   1401   #endif /* SQLITE_OMIT_COMPILEOPTION_DIAGS */
  1402   1402   
  1403   1403   #ifndef SQLITE_OMIT_WAL
  1404   1404     /*
  1405         -  **   PRAGMA [database.]checkpoint
         1405  +  **   PRAGMA [database.]wal_checkpoint
  1406   1406     **
  1407   1407     ** Checkpoint the database.
  1408   1408     */
  1409         -  if( sqlite3StrICmp(zLeft, "checkpoint")==0 ){
         1409  +  if( sqlite3StrICmp(zLeft, "wal_checkpoint")==0 ){
         1410  +    if( sqlite3ReadSchema(pParse) ) goto pragma_out;
  1410   1411       sqlite3VdbeAddOp3(v, OP_Checkpoint, iDb, 0, 0);
  1411   1412     }else
         1413  +
         1414  +  /*
         1415  +  **   PRAGMA wal_autocheckpoint
         1416  +  **   PRAGMA wal_autocheckpoint = N
         1417  +  **
         1418  +  ** Configure a database connection to automatically checkpoint a database
         1419  +  ** after accumulating N frames in the log. Or query for the current value
         1420  +  ** of N.
         1421  +  */
         1422  +  if( sqlite3StrICmp(zLeft, "wal_autocheckpoint")==0 ){
         1423  +    if( zRight ){
         1424  +      int nAuto = atoi(zRight);
         1425  +      sqlite3_wal_autocheckpoint(db, nAuto);
         1426  +    }
         1427  +    returnSingleInt(pParse, "wal_autocheckpoint", db->nAutoCheckpoint);
         1428  +  }else
  1412   1429   #endif
  1413   1430   
  1414   1431   #if defined(SQLITE_DEBUG) || defined(SQLITE_TEST)
  1415   1432     /*
  1416   1433     ** Report the current state of file logs for all databases
  1417   1434     */
  1418   1435     if( sqlite3StrICmp(zLeft, "lock_status")==0 ){

Changes to src/sqliteInt.h.

   820    820     void *pCommitArg;                 /* Argument to xCommitCallback() */   
   821    821     int (*xCommitCallback)(void*);    /* Invoked at every commit. */
   822    822     void *pRollbackArg;               /* Argument to xRollbackCallback() */   
   823    823     void (*xRollbackCallback)(void*); /* Invoked at every commit. */
   824    824     void *pUpdateArg;
   825    825     void (*xUpdateCallback)(void*,int, const char*,const char*,sqlite_int64);
   826    826   #ifndef SQLITE_OMIT_WAL
   827         -  int nDefaultCheckpoint;       /* Value configured by wal_autocheckpoint() */
          827  +  int nAutoCheckpoint;          /* Value configured by wal_autocheckpoint() */
   828    828     int (*xWalCallback)(void *, sqlite3 *, const char *, int);
   829    829     void *pWalArg;
   830    830   #endif
   831    831     void(*xCollNeeded)(void*,sqlite3*,int eTextRep,const char*);
   832    832     void(*xCollNeeded16)(void*,sqlite3*,int eTextRep,const void*);
   833    833     void *pCollNeededArg;
   834    834     sqlite3_value *pErr;          /* Most recent error message */

Changes to src/sqliteLimit.h.

   104    104   #ifndef SQLITE_DEFAULT_CACHE_SIZE
   105    105   # define SQLITE_DEFAULT_CACHE_SIZE  2000
   106    106   #endif
   107    107   #ifndef SQLITE_DEFAULT_TEMP_CACHE_SIZE
   108    108   # define SQLITE_DEFAULT_TEMP_CACHE_SIZE  500
   109    109   #endif
   110    110   
          111  +/*
          112  +** The default number of frames to accumulate in the log file before
          113  +** checkpointing the database in WAL mode.
          114  +*/
          115  +#ifndef SQLITE_DEFAULT_WAL_AUTOCHECKPOINT
          116  +# define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT  1000
          117  +#endif
          118  +
   111    119   /*
   112    120   ** The maximum number of attached databases.  This must be between 0
   113    121   ** and 30.  The upper bound on 30 is because a 32-bit integer bitmap
   114    122   ** is used internally to track attached databases.
   115    123   */
   116    124   #ifndef SQLITE_MAX_ATTACHED
   117    125   # define SQLITE_MAX_ATTACHED 10

Changes to src/vdbeapi.c.

   317    317     for(i=0; i<db->nDb; i++){
   318    318       Btree *pBt = db->aDb[i].pBt;
   319    319       if( pBt ){
   320    320         int nEntry = sqlite3PagerWalCallback(sqlite3BtreePager(pBt));
   321    321         if( db->xWalCallback && nEntry>0 && rc==SQLITE_OK
   322    322          && db->xWalCallback(db->pWalArg, db, db->aDb[i].zName, nEntry)
   323    323         ){
   324         -        rc = sqlite3PagerCheckpoint(sqlite3BtreePager(pBt));
          324  +        rc = sqlite3Checkpoint(db, i);
   325    325         }
   326    326       }
   327    327     }
   328    328   #endif
   329    329     return rc;
   330    330   }
   331    331   

Changes to test/savepoint.test.

   788    788       CREATE TABLE t3(a, b, UNIQUE(a, b));
   789    789       ROLLBACK TO one;
   790    790     }
   791    791   } {}
   792    792   integrity_check savepoint-11.7
   793    793   do_test savepoint-11.8 {
   794    794     execsql { ROLLBACK }
   795         -  execsql { PRAGMA checkpoint }
          795  +  execsql { PRAGMA wal_checkpoint }
   796    796     file size test.db
   797    797   } {8192}
   798    798   
   799    799   do_test savepoint-11.9 {
   800    800     execsql {
   801    801       DROP TABLE IF EXISTS t1;
   802    802       DROP TABLE IF EXISTS t2;

Changes to test/wal.test.

   345    345       PRAGMA page_size = 1024;
   346    346       CREATE TABLE t1(a, b);
   347    347       INSERT INTO t1 VALUES(1, 2);
   348    348     }
   349    349     list [file size test.db] [file size test.db-wal]
   350    350   } [list 1024 [log_file_size 3 1024]]
   351    351   do_test wal-7.2 {
   352         -  execsql { PRAGMA checkpoint }
          352  +  execsql { PRAGMA wal_checkpoint }
   353    353     list [file size test.db] [file size test.db-wal]
   354    354   } [list 2048 [log_file_size 3 1024]]
   355    355   
   356    356   # Execute some transactions in auto-vacuum mode to test database file
   357    357   # truncation.
   358    358   #
   359    359   do_test wal-8.1 {
................................................................................
   376    376       INSERT INTO t1 VALUES(blob(900));
   377    377       INSERT INTO t1 VALUES(blob(900));
   378    378       INSERT INTO t1 SELECT blob(900) FROM t1;       /*  4 */
   379    379       INSERT INTO t1 SELECT blob(900) FROM t1;       /*  8 */
   380    380       INSERT INTO t1 SELECT blob(900) FROM t1;       /* 16 */
   381    381       INSERT INTO t1 SELECT blob(900) FROM t1;       /* 32 */
   382    382       INSERT INTO t1 SELECT blob(900) FROM t1;       /* 64 */
   383         -    PRAGMA checkpoint;
          383  +    PRAGMA wal_checkpoint;
   384    384     }
   385    385     file size test.db
   386    386   } [expr 68*1024]
   387    387   do_test wal-8.3 {
   388    388     execsql { 
   389    389       DELETE FROM t1 WHERE rowid<54;
   390         -    PRAGMA checkpoint;
          390  +    PRAGMA wal_checkpoint;
   391    391     }
   392    392     file size test.db
   393    393   } [expr 14*1024]
   394    394   
   395    395   # Run some "warm-body" tests to ensure that log-summary files with more
   396    396   # than 256 entries (log summaries that contain index blocks) work Ok.
   397    397   #
................................................................................
   422    422     file copy test.db-wal test2.db-wal
   423    423     sqlite3_wal db3 test2.db 
   424    424     execsql {PRAGMA integrity_check } db3
   425    425   } {ok}
   426    426   db3 close
   427    427   
   428    428   do_test wal-9.4 {
   429         -  execsql { PRAGMA checkpoint }
          429  +  execsql { PRAGMA wal_checkpoint }
   430    430     db2 close
   431    431     sqlite3_wal db2 test.db
   432    432     execsql {PRAGMA integrity_check } db2
   433    433   } {ok}
   434    434   
   435    435   foreach handle {db db2 db3} { catch { $handle close } }
   436    436   unset handle
................................................................................
   545    545     # Open a read transaction with [db2]. Check that this prevents [db] from
   546    546     # checkpointing the database. But not from writing to it.
   547    547     #
   548    548     do_test wal-10.$tn.11 {
   549    549       sql2 { BEGIN; SELECT * FROM t1 }
   550    550     } {1 2 3 4 5 6 7 8 9 10}
   551    551     do_test wal-10.$tn.12 {
   552         -    catchsql { PRAGMA checkpoint } 
          552  +    catchsql { PRAGMA wal_checkpoint } 
   553    553     } {1 {database is locked}}
   554    554     do_test wal-10.$tn.13 {
   555    555       execsql { INSERT INTO t1 VALUES(11, 12) }
   556    556       sql2 {SELECT * FROM t1}
   557    557     } {1 2 3 4 5 6 7 8 9 10}
   558    558   
   559    559     # Connection [db2] is holding a lock on a snapshot, preventing [db] from
................................................................................
   564    564     proc busyhandler x {
   565    565       if {$x==4} { sql2 COMMIT }
   566    566       if {$x<5} { return 0 }
   567    567       return 1
   568    568     }
   569    569     db busy busyhandler
   570    570     do_test wal-10.$tn.14 {
   571         -    execsql { PRAGMA checkpoint } 
          571  +    execsql { PRAGMA wal_checkpoint } 
   572    572     } {}
   573    573   
   574    574     # Similar to the test above. Except this time, a new read transaction is
   575    575     # started (db3) while the checkpointer is waiting for an old one (db2) to 
   576    576     # finish. The checkpointer can finish, but any subsequent write operations 
   577    577     # must wait until after db3 has closed the read transaction, as db3 is a
   578    578     # "region D" writer.
   579    579     #
   580    580     db busy {}
   581    581     do_test wal-10.$tn.15 {
   582    582       sql2 { BEGIN; SELECT * FROM t1; }
   583    583     } {1 2 3 4 5 6 7 8 9 10 11 12}
   584    584     do_test wal-10.$tn.16 {
   585         -    catchsql { PRAGMA checkpoint } 
          585  +    catchsql { PRAGMA wal_checkpoint } 
   586    586     } {1 {database is locked}}
   587    587     proc busyhandler x {
   588    588       if {$x==3} { sql3 { BEGIN; SELECT * FROM t1 } }
   589    589       if {$x==4} { sql2 COMMIT }
   590    590       if {$x<5}  { return 0 }
   591    591       return 1
   592    592     }
   593    593     db busy busyhandler
   594    594     do_test wal-10.$tn.17 {
   595         -    execsql { PRAGMA checkpoint } 
          595  +    execsql { PRAGMA wal_checkpoint } 
   596    596     } {}
   597    597     do_test wal-10.$tn.18 {
   598    598       sql3 { SELECT * FROM t1 }
   599    599     } {1 2 3 4 5 6 7 8 9 10 11 12}
   600    600     do_test wal-10.$tn.19 {
   601    601       catchsql { INSERT INTO t1 VALUES(13, 14) }
   602    602     } {1 {database is locked}}
................................................................................
   612    612     } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
   613    613   
   614    614     # Set [db3] up as a "region D" reader again. Then upgrade it to a writer
   615    615     # and back down to a reader. Then, check that a checkpoint is not possible
   616    616     # (as [db3] still has a snapshot locked).
   617    617     #
   618    618     do_test wal-10.$tn.23 {
   619         -    execsql { PRAGMA checkpoint }
          619  +    execsql { PRAGMA wal_checkpoint }
   620    620     } {}
   621    621     do_test wal-10.$tn.24 {
   622    622       sql2 { BEGIN; SELECT * FROM t1; }
   623    623     } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
   624    624     do_test wal-10.$tn.25 {
   625         -    execsql { PRAGMA checkpoint }
          625  +    execsql { PRAGMA wal_checkpoint }
   626    626     } {}
   627    627     do_test wal-10.$tn.26 {
   628    628       catchsql { INSERT INTO t1 VALUES(15, 16) }
   629    629     } {1 {database is locked}}
   630    630     do_test wal-10.$tn.27 {
   631    631       sql3 { INSERT INTO t1 VALUES(15, 16) }
   632    632     } {}
................................................................................
   637    637       }
   638    638       sql3 COMMIT
   639    639       execsql { SELECT * FROM t1 }
   640    640     } {1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16}
   641    641     db busy {}
   642    642     do_test wal-10.$tn.29 {
   643    643       execsql { INSERT INTO t1 VALUES(17, 18) }
   644         -    catchsql { PRAGMA checkpoint }
          644  +    catchsql { PRAGMA wal_checkpoint }
   645    645     } {1 {database is locked}}
   646    646     do_test wal-10.$tn.30 {
   647    647       code3 { sqlite3_finalize $::STMT }
   648         -    execsql { PRAGMA checkpoint }
          648  +    execsql { PRAGMA wal_checkpoint }
   649    649     } {}
   650    650   
   651    651     # At one point, if a reader failed to upgrade to a writer because it
   652    652     # was reading an old snapshot, the write-locks were not being released.
   653    653     # Test that this bug has been fixed.
   654    654     #
   655    655     do_test wal-10.$tn.31 {
................................................................................
   681    681         BEGIN;
   682    682           SELECT * FROM t1;
   683    683       }
   684    684     } {a b c d}
   685    685     proc busyhandler x { return 1 }
   686    686     db busy busyhandler
   687    687     do_test wal-10.$tn.36 {
   688         -    catchsql { PRAGMA checkpoint }
          688  +    catchsql { PRAGMA wal_checkpoint }
   689    689     } {1 {database is locked}}
   690    690     do_test wal-10.$tn.36 {
   691    691       sql3 { INSERT INTO t1 VALUES('e', 'f') }
   692    692       sql2 { SELECT * FROM t1 }
   693    693     } {a b c d}
   694    694     do_test wal-10.$tn.37 {
   695    695       sql2 COMMIT
   696         -    execsql { PRAGMA checkpoint }
          696  +    execsql { PRAGMA wal_checkpoint }
   697    697     } {}
   698    698   
   699    699     catch { db close }
   700    700     catch { code2 { db2 close } }
   701    701     catch { code3 { db3 close } }
   702    702     catch { close $::code2_chan }
   703    703     catch { close $::code3_chan }
................................................................................
   714    714       PRAGMA cache_size = 10;
   715    715       PRAGMA page_size = 1024;
   716    716       CREATE TABLE t1(x PRIMARY KEY);
   717    717     }
   718    718     list [expr [file size test.db]/1024] [expr [file size test.db-wal]/1044]
   719    719   } {1 3}
   720    720   do_test wal-11.2 {
   721         -  execsql { PRAGMA checkpoint }
          721  +  execsql { PRAGMA wal_checkpoint }
   722    722     list [expr [file size test.db]/1024] [file size test.db-wal]
   723    723   } [list 3 [log_file_size 3 1024]]
   724    724   do_test wal-11.3 {
   725    725     execsql { INSERT INTO t1 VALUES( blob(900) ) }
   726    726     list [expr [file size test.db]/1024] [file size test.db-wal]
   727    727   } [list 3 [log_file_size 4 1024]]
   728    728   
................................................................................
   749    749   do_test wal-11.7 {
   750    750     execsql { 
   751    751       SELECT count(*) FROM t1;
   752    752       PRAGMA integrity_check;
   753    753     }
   754    754   } {16 ok}
   755    755   do_test wal-11.8 {
   756         -  execsql { PRAGMA checkpoint }
          756  +  execsql { PRAGMA wal_checkpoint }
   757    757     list [expr [file size test.db]/1024] [file size test.db-wal]
   758    758   } [list 37 [log_file_size 41 1024]]
   759    759   do_test wal-11.9 {
   760    760     db close
   761    761     list [expr [file size test.db]/1024] [log_deleted test.db-wal]
   762    762   } {37 1}
   763    763   sqlite3_wal db test.db
................................................................................
   825    825     file copy -force test.db-wal test2.db-wal
   826    826     sqlite3_wal db2 test2.db
   827    827     execsql { SELECT * FROM t2 } db2
   828    828   } {B 1}
   829    829   db2 close
   830    830   do_test wal-12.5 {
   831    831     execsql {
   832         -    PRAGMA checkpoint;
          832  +    PRAGMA wal_checkpoint;
   833    833       UPDATE t2 SET y = 2 WHERE x = 'B'; 
   834         -    PRAGMA checkpoint;
          834  +    PRAGMA wal_checkpoint;
   835    835       UPDATE t1 SET y = 1 WHERE x = 'A';
   836         -    PRAGMA checkpoint;
          836  +    PRAGMA wal_checkpoint;
   837    837       UPDATE t1 SET y = 0 WHERE x = 'A';
   838    838       SELECT * FROM t2;
   839    839     }
   840    840   } {B 2}
   841    841   do_test wal-12.6 {
   842    842     file copy -force test.db test2.db
   843    843     file copy -force test.db-wal test2.db-wal
................................................................................
   951    951     db2 eval { 
   952    952       INSERT INTO t1 SELECT randomblob(10), randomblob(100);
   953    953       INSERT INTO t1 SELECT randomblob(10), randomblob(100);
   954    954       INSERT INTO t1 SELECT randomblob(10), randomblob(100);
   955    955       INSERT INTO t1 SELECT randomblob(10), randomblob(100);
   956    956     }
   957    957   
   958         -  # After executing the "PRAGMA checkpoint", connection [db] was being
          958  +  # After executing the "PRAGMA wal_checkpoint", connection [db] was being
   959    959     # left with an inconsistent cache. Running the CREATE INDEX statement
   960    960     # in this state led to database corruption.
   961    961     catchsql { 
   962         -    PRAGMA checkpoint;
          962  +    PRAGMA wal_checkpoint;
   963    963       CREATE INDEX i1 on t1(b);
   964    964     }
   965    965      
   966    966     db2 eval { PRAGMA integrity_check }
   967    967   } {ok}
   968    968   
   969    969   catch { db close }
   970    970   catch { db2 close }
   971    971   finish_test

Changes to test/walbak.test.

    67     67       PRAGMA main.journal_mode;
    68     68     }
    69     69   } {wal}
    70     70   do_test walbak-1.5 {
    71     71     list [file size test.db] [file size test.db-wal]
    72     72   } [list 1024 [log_file_size 6 1024]]
    73     73   do_test walbak-1.6 {
    74         -  execsql { PRAGMA checkpoint }
           74  +  execsql { PRAGMA wal_checkpoint }
    75     75     list [file size test.db] [file size test.db-wal]
    76     76   } [list [expr 3*1024] [log_file_size 6 1024]]
    77     77   do_test walbak-1.7 {
    78     78     execsql { 
    79     79       CREATE TABLE t2(a, b);
    80     80       INSERT INTO t2 SELECT * FROM t1;
    81     81       DROP TABLE t1;
................................................................................
    83     83     list [file size test.db] [file size test.db-wal]
    84     84   } [list [expr 3*1024] [log_file_size 6 1024]]
    85     85   do_test walbak-1.8 {
    86     86     execsql { VACUUM }
    87     87     list [file size test.db] [file size test.db-wal]
    88     88   } [list [expr 3*1024] [log_file_size 8 1024]]
    89     89   do_test walbak-1.9 {
    90         -  execsql { PRAGMA checkpoint }
           90  +  execsql { PRAGMA wal_checkpoint }
    91     91     list [file size test.db] [file size test.db-wal]
    92     92   } [list [expr 2*1024] [log_file_size 8 1024]]
    93     93   
    94     94   #-------------------------------------------------------------------------
    95     95   # Backups when the source db is modified mid-backup.
    96     96   #
    97     97   proc sig {{db db}} {

Changes to test/walcrash.test.

   188    188         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
   189    189         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
   190    190         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 20 */
   191    191         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 24 */
   192    192         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 28 */
   193    193         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 32 */
   194    194   
   195         -      PRAGMA checkpoint;
          195  +      PRAGMA wal_checkpoint;
   196    196         INSERT INTO t1 VALUES(randomblob(900));
   197    197         INSERT INTO t1 VALUES(randomblob(900));
   198    198         INSERT INTO t1 VALUES(randomblob(900));
   199    199       }
   200    200     } {1 {child process exited abnormally}}
   201    201   
   202    202     do_test walcrash-5.$i.2 {
................................................................................
   229    229         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
   230    230         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
   231    231         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 20 */
   232    232         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 24 */
   233    233         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 28 */
   234    234         INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 32 */
   235    235   
   236         -      PRAGMA checkpoint;
          236  +      PRAGMA wal_checkpoint;
   237    237         INSERT INTO t1 VALUES(randomblob(900));
   238    238         INSERT INTO t1 VALUES(randomblob(900));
   239    239         INSERT INTO t1 VALUES(randomblob(900));
   240    240       }
   241    241     } {1 {child process exited abnormally}}
   242    242   
   243    243     do_test walcrash-6.$i.2 {
................................................................................
   256    256     do_test walcrash-7.$i.1 {
   257    257       crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 {
   258    258         PRAGMA journal_mode = wal;
   259    259         BEGIN;
   260    260           CREATE TABLE t1(a, b);
   261    261           INSERT INTO t1 VALUES(1, 2);
   262    262         COMMIT;
   263         -      PRAGMA checkpoint;
          263  +      PRAGMA wal_checkpoint;
   264    264         CREATE INDEX i1 ON t1(a);
   265         -      PRAGMA checkpoint;
          265  +      PRAGMA wal_checkpoint;
   266    266       }
   267    267     } {1 {child process exited abnormally}}
   268    268   
   269    269     do_test walcrash-7.$i.2 {
   270    270       sqlite3 db test.db
   271    271       execsql { SELECT b FROM t1 WHERE a = 1 }
   272    272     } {2}

Changes to test/walhook.test.

     7      7   #    May you find forgiveness for yourself and forgive others.
     8      8   #    May you share freely, never taking more than you give.
     9      9   #
    10     10   #***********************************************************************
    11     11   # This file implements regression tests for SQLite library.  The
    12     12   # focus of this file is testing the operation of the library in
    13     13   # "PRAGMA journal_mode=WAL" mode.
           14  +# 
           15  +# More specifically, this file contains regression tests for the 
           16  +# sqlite3_wal_hook() mechanism, including the sqlite3_wal_autocheckpoint()
           17  +# and "PRAGMA wal_autocheckpoint" convenience interfaces.
    14     18   #
    15     19   
    16     20   set testdir [file dirname $argv0]
    17     21   source $testdir/tester.tcl
    18     22   
    19     23   ifcapable !wal {finish_test ; return }
    20     24   
    21         -proc sqlite3_wal {args} {
    22         -  eval sqlite3 $args
    23         -  [lindex $args 0] eval { 
    24         -    PRAGMA journal_mode = wal;
    25         -    PRAGMA synchronous = normal;
    26         -    PRAGMA page_size = 1024;
    27         -  }
           25  +proc log_file_size {nFrame pgsz} {
           26  +  expr {12 + ($pgsz+16)*$nFrame}
    28     27   }
    29         -sqlite3_wal db test.db
    30         -db wal_hook wal_hook
    31     28   
    32     29   set ::wal_hook [list]
    33     30   proc wal_hook {zDb nEntry} {
    34     31     lappend ::wal_hook $zDb $nEntry
    35     32     return 0
    36     33   }
           34  +db wal_hook wal_hook
    37     35   
    38     36   do_test walhook-1.1 {
    39         -  execsql { CREATE TABLE t1(i PRIMARY KEY, j) }
           37  +  execsql { 
           38  +    PRAGMA page_size = 1024;
           39  +    PRAGMA journal_mode = wal;
           40  +    PRAGMA synchronous = normal;
           41  +    CREATE TABLE t1(i PRIMARY KEY, j);
           42  +  }
    40     43     set ::wal_hook
    41     44   } {main 3}
           45  +
    42     46   do_test walhook-1.2 {
    43     47     set ::wal_hook [list]
    44     48     execsql { INSERT INTO t1 VALUES(1, 'one') }
    45     49     set ::wal_hook
    46     50   } {main 5}
    47     51   do_test walhook-1.3 {
    48     52     proc wal_hook {args} { return 1 }
    49     53     execsql { INSERT INTO t1 VALUES(2, 'two') }
    50     54     file size test.db
    51     55   } [expr 3*1024]
    52         -
    53     56   do_test walhook-1.4 {
    54     57     proc wal_hook {zDb nEntry} { 
    55         -    execsql { PRAGMA checkpoint }
           58  +    execsql { PRAGMA wal_checkpoint }
    56     59       return 0
    57     60     }
    58     61     execsql { CREATE TABLE t2(a, b) }
    59     62     file size test.db
    60     63   } [expr 4*1024]
    61     64   
    62     65   do_test walhook-1.5 {
    63         -  sqlite3_wal db2 test.db
    64         -  proc wal_hook {zDb nEntry} { 
    65         -    execsql { PRAGMA checkpoint } db2
           66  +  sqlite3 db2 test.db
           67  +  proc wal_hook {zDb nEntry} {
           68  +    execsql { PRAGMA wal_checkpoint } db2
    66     69       return 0
    67     70     }
    68     71     execsql { CREATE TABLE t3(a PRIMARY KEY, b) }
    69     72     file size test.db
    70     73   } [expr 6*1024]
           74  +
           75  +db2 close
           76  +db close
           77  +sqlite3 db test.db
           78  +do_test walhook-2.1 {
           79  +  execsql { PRAGMA synchronous = NORMAL }
           80  +  execsql { PRAGMA wal_autocheckpoint }
           81  +} {1000}
           82  +do_test walhook-2.2 {
           83  +  execsql { PRAGMA wal_autocheckpoint = 10}
           84  +} {10}
           85  +do_test walhook-2.3 {
           86  +  execsql { PRAGMA wal_autocheckpoint }
           87  +} {10}
           88  +
           89  +#
           90  +# The database connection is configured with "PRAGMA wal_autocheckpoint = 10".
           91  +# Check that transactions are written to the log file until it contains at
           92  +# least 10 frames, then the database is checkpointed. Subsequent transactions
           93  +# are written into the start of the log file.
           94  +#
           95  +foreach {tn sql dbpages logpages} {
           96  +  4 "CREATE TABLE t4(x PRIMARY KEY, y)"   6   3
           97  +  5 "INSERT INTO t4 VALUES(1, 'one')"     6   5
           98  +  6 "INSERT INTO t4 VALUES(2, 'two')"     6   7
           99  +  7 "INSERT INTO t4 VALUES(3, 'three')"   6   9
          100  +  8 "INSERT INTO t4 VALUES(4, 'four')"    8  11
          101  +  9 "INSERT INTO t4 VALUES(5, 'five')"    8  11
          102  +} {
          103  +  do_test walhook-2.$tn {
          104  +    execsql $sql
          105  +    list [file size test.db] [file size test.db-wal]
          106  +  } [list [expr $dbpages*1024] [log_file_size $logpages 1024]]
          107  +}
    71    108   
    72    109   catch { db2 close }
    73    110   catch { db close }
    74    111   finish_test

Changes to test/walslow.test.

    44     44         set w [expr int(rand()*2000)]
    45     45         set x [expr int(rand()*2000)]
    46     46         execsql { INSERT INTO t1 VALUES(randomblob($w), randomblob($x)) }
    47     47         execsql { PRAGMA integrity_check }
    48     48       } {ok}
    49     49   
    50     50       do_test walslow-1.seed=$seed.$iTest.2 {
    51         -      execsql "PRAGMA checkpoint;"
           51  +      execsql "PRAGMA wal_checkpoint;"
    52     52         execsql { PRAGMA integrity_check }
    53     53       } {ok}
    54     54   
    55     55       do_test walslow-1.seed=$seed.$iTest.3 {
    56     56         file delete -force testX.db testX.db-wal
    57     57         file copy test.db testX.db
    58     58         file copy test.db-wal testX.db-wal

Changes to test/walthread.test.

   237    237   #      rows in the table.
   238    238   #
   239    239   # Each of the N threads runs N read transactions followed by a single write
   240    240   # transaction in a loop as fast as possible.
   241    241   #
   242    242   # There is also a single checkpointer thread. It runs the following loop:
   243    243   #
   244         -#   1) Execute "PRAGMA checkpoint"
          244  +#   1) Execute "PRAGMA wal_checkpoint"
   245    245   #   2) Sleep for 500 ms.
   246    246   #
   247    247   do_thread_test2 walthread-1 -seconds $seconds(walthread-1) -init {
   248    248     execsql {
   249    249       PRAGMA journal_mode = WAL;
   250    250       CREATE TABLE t1(x PRIMARY KEY);
   251    251       PRAGMA lock_status;
................................................................................
   291    291       incr nRun
   292    292     }
   293    293     set nRun
   294    294   
   295    295   } -thread ckpt 1 {
   296    296     set nRun 0
   297    297     while {[tt_continue]} {
   298         -    db eval "PRAGMA checkpoint"
          298  +    db eval "PRAGMA wal_checkpoint"
   299    299       usleep 500
   300    300       incr nRun
   301    301     }
   302    302     set nRun
   303    303   }
   304    304   
   305    305   #--------------------------------------------------------------------------