/ 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 Unified Diffs Show Whitespace Changes Patch

Changes to src/main.c.

1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
....
1209
1210
1211
1212
1213
1214
1215
1216
1217

1218
1219
1220
1221
1222
1223
1224
....
1232
1233
1234
1235
1236
1237
1238

1239
1240
1241
1242
1243
1244
1245
....
1866
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
}

#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()).
*/ 
static int defaultWalHook(void *p, sqlite3 *db, const char *z, int nFrame){
  UNUSED_PARAMETER(p);
  UNUSED_PARAMETER(z);
  return ( nFrame>=db->nDefaultCheckpoint );
}

/*
** Configure an sqlite3_wal_hook() callback to automatically checkpoint
** a database after committing a transaction if there are nFrame or
** more frames in the log file. Passing zero or a negative value as the
** nFrame parameter disables automatic checkpoints entirely.
................................................................................
** registered using sqlite3_wal_hook(). Likewise, registering a callback
** using sqlite3_wal_hook() disables the automatic checkpoint mechanism
** 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);

  }else{
    sqlite3_wal_hook(db, 0, 0);
  }
  sqlite3_mutex_leave(db->mutex);
  return SQLITE_OK;
}

................................................................................
  void *pArg                      /* First argument passed to xCallback() */
){
  void *pRet;
  sqlite3_mutex_enter(db->mutex);
  pRet = db->pWalArg;
  db->xWalCallback = xCallback;
  db->pWalArg = pArg;

  sqlite3_mutex_leave(db->mutex);
  return pRet;
}

/*
** Checkpoint database zDb. If zDb is NULL, the main database is checkpointed.
*/
................................................................................
                          SQLITE_DEFAULT_LOCKING_MODE);
#endif

  /* Enable the lookaside-malloc subsystem */
  setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside,
                        sqlite3GlobalConfig.nLookaside);

  sqlite3_wal_autocheckpoint(db, SQLITE_DEFAULT_CACHE_SIZE);

opendb_out:
  if( db ){
    assert( db->mutex!=0 || isThreadsafe==0 || sqlite3GlobalConfig.bFullMutex==0 );
    sqlite3_mutex_leave(db->mutex);
  }
  rc = sqlite3_errcode(db);







|




|







 







<

>







 







>







 







|







1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
....
1209
1210
1211
1212
1213
1214
1215

1216
1217
1218
1219
1220
1221
1222
1223
1224
....
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
....
1867
1868
1869
1870
1871
1872
1873
1874
1875
1876
1877
1878
1879
1880
1881
}

#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.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->nAutoCheckpoint );
}

/*
** Configure an sqlite3_wal_hook() callback to automatically checkpoint
** a database after committing a transaction if there are nFrame or
** more frames in the log file. Passing zero or a negative value as the
** nFrame parameter disables automatic checkpoints entirely.
................................................................................
** registered using sqlite3_wal_hook(). Likewise, registering a callback
** using sqlite3_wal_hook() disables the automatic checkpoint mechanism
** configured by this function.
*/
int sqlite3_wal_autocheckpoint(sqlite3 *db, int nFrame){
  sqlite3_mutex_enter(db->mutex);
  if( nFrame>0 ){

    sqlite3_wal_hook(db, defaultWalHook, 0);
    db->nAutoCheckpoint = nFrame;
  }else{
    sqlite3_wal_hook(db, 0, 0);
  }
  sqlite3_mutex_leave(db->mutex);
  return SQLITE_OK;
}

................................................................................
  void *pArg                      /* First argument passed to xCallback() */
){
  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;
}

/*
** Checkpoint database zDb. If zDb is NULL, the main database is checkpointed.
*/
................................................................................
                          SQLITE_DEFAULT_LOCKING_MODE);
#endif

  /* Enable the lookaside-malloc subsystem */
  setupLookaside(db, 0, sqlite3GlobalConfig.szLookaside,
                        sqlite3GlobalConfig.nLookaside);

  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);
  }
  rc = sqlite3_errcode(db);

Changes to src/pragma.c.

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.]checkpoint
  **
  ** Checkpoint the database.
  */
  if( sqlite3StrICmp(zLeft, "checkpoint")==0 ){

    sqlite3VdbeAddOp3(v, OP_Checkpoint, iDb, 0, 0);
  }else
















#endif

#if defined(SQLITE_DEBUG) || defined(SQLITE_TEST)
  /*
  ** Report the current state of file logs for all databases
  */
  if( sqlite3StrICmp(zLeft, "lock_status")==0 ){







|



|
>


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







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
1424
1425
1426
1427
1428
1429
1430
1431
1432
1433
1434
1435
      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, 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
  */
  if( sqlite3StrICmp(zLeft, "lock_status")==0 ){

Changes to src/sqliteInt.h.

820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
  void *pCommitArg;                 /* Argument to xCommitCallback() */   
  int (*xCommitCallback)(void*);    /* Invoked at every commit. */
  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 (*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*);
  void *pCollNeededArg;
  sqlite3_value *pErr;          /* Most recent error message */







|







820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
  void *pCommitArg;                 /* Argument to xCommitCallback() */   
  int (*xCommitCallback)(void*);    /* Invoked at every commit. */
  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 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*);
  void *pCollNeededArg;
  sqlite3_value *pErr;          /* Most recent error message */

Changes to src/sqliteLimit.h.

104
105
106
107
108
109
110








111
112
113
114
115
116
117
#ifndef SQLITE_DEFAULT_CACHE_SIZE
# define SQLITE_DEFAULT_CACHE_SIZE  2000
#endif
#ifndef SQLITE_DEFAULT_TEMP_CACHE_SIZE
# define SQLITE_DEFAULT_TEMP_CACHE_SIZE  500
#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.
*/
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 10







>
>
>
>
>
>
>
>







104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
#ifndef SQLITE_DEFAULT_CACHE_SIZE
# define SQLITE_DEFAULT_CACHE_SIZE  2000
#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.
*/
#ifndef SQLITE_MAX_ATTACHED
# define SQLITE_MAX_ATTACHED 10

Changes to src/vdbeapi.c.

317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
  for(i=0; i<db->nDb; i++){
    Btree *pBt = db->aDb[i].pBt;
    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));
      }
    }
  }
#endif
  return rc;
}








|







317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
  for(i=0; i<db->nDb; i++){
    Btree *pBt = db->aDb[i].pBt;
    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 = sqlite3Checkpoint(db, i);
      }
    }
  }
#endif
  return rc;
}

Changes to test/savepoint.test.

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

do_test savepoint-11.9 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;







|







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

do_test savepoint-11.9 {
  execsql {
    DROP TABLE IF EXISTS t1;
    DROP TABLE IF EXISTS t2;

Changes to test/wal.test.

345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
...
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
...
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
...
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
...
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
...
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
...
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
...
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
...
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
    PRAGMA page_size = 1024;
    CREATE TABLE t1(a, b);
    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 }
  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.
#
do_test wal-8.1 {
................................................................................
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 VALUES(blob(900));
    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;
  }
  file size test.db
} [expr 68*1024]
do_test wal-8.3 {
  execsql { 
    DELETE FROM t1 WHERE rowid<54;
    PRAGMA checkpoint;
  }
  file size test.db
} [expr 14*1024]

# Run some "warm-body" tests to ensure that log-summary files with more
# than 256 entries (log summaries that contain index blocks) work Ok.
#
................................................................................
  file copy test.db-wal test2.db-wal
  sqlite3_wal db3 test2.db 
  execsql {PRAGMA integrity_check } db3
} {ok}
db3 close

do_test wal-9.4 {
  execsql { PRAGMA checkpoint }
  db2 close
  sqlite3_wal db2 test.db
  execsql {PRAGMA integrity_check } db2
} {ok}

foreach handle {db db2 db3} { catch { $handle close } }
unset handle
................................................................................
  # Open a read transaction with [db2]. Check that this prevents [db] from
  # 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 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}

  # Connection [db2] is holding a lock on a snapshot, preventing [db] from
................................................................................
  proc busyhandler x {
    if {$x==4} { sql2 COMMIT }
    if {$x<5} { return 0 }
    return 1
  }
  db busy busyhandler
  do_test wal-10.$tn.14 {
    execsql { PRAGMA 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 
  # must wait until after db3 has closed the read transaction, as db3 is a
  # "region D" writer.
  #
  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 } 
  } {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 } 
  } {}
  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 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {1 {database is locked}}
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # 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 }
  } {}
  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 }
  } {}
  do_test wal-10.$tn.26 {
    catchsql { INSERT INTO t1 VALUES(15, 16) }
  } {1 {database is locked}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(15, 16) }
  } {}
................................................................................
    }
    sql3 COMMIT
    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 }
  } {1 {database is locked}}
  do_test wal-10.$tn.30 {
    code3 { sqlite3_finalize $::STMT }
    execsql { PRAGMA 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 {
................................................................................
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  proc busyhandler x { return 1 }
  db busy busyhandler
  do_test wal-10.$tn.36 {
    catchsql { PRAGMA 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 }
  } {}

  catch { db close }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }
  catch { close $::code2_chan }
  catch { close $::code3_chan }
................................................................................
    PRAGMA cache_size = 10;
    PRAGMA page_size = 1024;
    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 }
  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]
} [list 3 [log_file_size 4 1024]]

................................................................................
do_test wal-11.7 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.8 {
  execsql { PRAGMA 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]
} {37 1}
sqlite3_wal db test.db
................................................................................
  file copy -force test.db-wal test2.db-wal
  sqlite3_wal db2 test2.db
  execsql { SELECT * FROM t2 } db2
} {B 1}
db2 close
do_test wal-12.5 {
  execsql {
    PRAGMA checkpoint;
    UPDATE t2 SET y = 2 WHERE x = 'B'; 
    PRAGMA checkpoint;
    UPDATE t1 SET y = 1 WHERE x = 'A';
    PRAGMA 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
................................................................................
  db2 eval { 
    INSERT INTO t1 SELECT randomblob(10), randomblob(100);
    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
  # left with an inconsistent cache. Running the CREATE INDEX statement
  # in this state led to database corruption.
  catchsql { 
    PRAGMA checkpoint;
    CREATE INDEX i1 on t1(b);
  }
   
  db2 eval { PRAGMA integrity_check }
} {ok}

catch { db close }
catch { db2 close }
finish_test







|







 







|






|







 







|







 







|







 







|













|









|







 







|





|







 







|



|







 







|







|







 







|







 







|







 







|

|

|







 







|



|









345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
...
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
...
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
...
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
...
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
...
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
...
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
...
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
...
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
...
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
...
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
...
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
    PRAGMA page_size = 1024;
    CREATE TABLE t1(a, b);
    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 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.
#
do_test wal-8.1 {
................................................................................
    INSERT INTO t1 VALUES(blob(900));
    INSERT INTO t1 VALUES(blob(900));
    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 wal_checkpoint;
  }
  file size test.db
} [expr 68*1024]
do_test wal-8.3 {
  execsql { 
    DELETE FROM t1 WHERE rowid<54;
    PRAGMA wal_checkpoint;
  }
  file size test.db
} [expr 14*1024]

# Run some "warm-body" tests to ensure that log-summary files with more
# than 256 entries (log summaries that contain index blocks) work Ok.
#
................................................................................
  file copy test.db-wal test2.db-wal
  sqlite3_wal db3 test2.db 
  execsql {PRAGMA integrity_check } db3
} {ok}
db3 close

do_test wal-9.4 {
  execsql { PRAGMA wal_checkpoint }
  db2 close
  sqlite3_wal db2 test.db
  execsql {PRAGMA integrity_check } db2
} {ok}

foreach handle {db db2 db3} { catch { $handle close } }
unset handle
................................................................................
  # Open a read transaction with [db2]. Check that this prevents [db] from
  # 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 } 
  } {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}

  # Connection [db2] is holding a lock on a snapshot, preventing [db] from
................................................................................
  proc busyhandler x {
    if {$x==4} { sql2 COMMIT }
    if {$x<5} { return 0 }
    return 1
  }
  db busy busyhandler
  do_test wal-10.$tn.14 {
    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 
  # must wait until after db3 has closed the read transaction, as db3 is a
  # "region D" writer.
  #
  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 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 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 {
    catchsql { INSERT INTO t1 VALUES(13, 14) }
  } {1 {database is locked}}
................................................................................
  } {1 2 3 4 5 6 7 8 9 10 11 12 13 14}

  # 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 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) }
  } {1 {database is locked}}
  do_test wal-10.$tn.27 {
    sql3 { INSERT INTO t1 VALUES(15, 16) }
  } {}
................................................................................
    }
    sql3 COMMIT
    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 wal_checkpoint }
  } {1 {database is locked}}
  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 {
................................................................................
      BEGIN;
        SELECT * FROM t1;
    }
  } {a b c d}
  proc busyhandler x { return 1 }
  db busy busyhandler
  do_test wal-10.$tn.36 {
    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 wal_checkpoint }
  } {}

  catch { db close }
  catch { code2 { db2 close } }
  catch { code3 { db3 close } }
  catch { close $::code2_chan }
  catch { close $::code3_chan }
................................................................................
    PRAGMA cache_size = 10;
    PRAGMA page_size = 1024;
    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 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]
} [list 3 [log_file_size 4 1024]]

................................................................................
do_test wal-11.7 {
  execsql { 
    SELECT count(*) FROM t1;
    PRAGMA integrity_check;
  }
} {16 ok}
do_test wal-11.8 {
  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]
} {37 1}
sqlite3_wal db test.db
................................................................................
  file copy -force test.db-wal test2.db-wal
  sqlite3_wal db2 test2.db
  execsql { SELECT * FROM t2 } db2
} {B 1}
db2 close
do_test wal-12.5 {
  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
................................................................................
  db2 eval { 
    INSERT INTO t1 SELECT randomblob(10), randomblob(100);
    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 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 wal_checkpoint;
    CREATE INDEX i1 on t1(b);
  }
   
  db2 eval { PRAGMA integrity_check }
} {ok}

catch { db close }
catch { db2 close }
finish_test

Changes to test/walbak.test.

67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
    PRAGMA main.journal_mode;
  }
} {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 }
  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);
    INSERT INTO t2 SELECT * FROM t1;
    DROP TABLE t1;
................................................................................
  list [file size test.db] [file size test.db-wal]
} [list [expr 3*1024] [log_file_size 6 1024]]
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 }
  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.
#
proc sig {{db db}} {







|







 







|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
..
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
    PRAGMA main.journal_mode;
  }
} {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 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);
    INSERT INTO t2 SELECT * FROM t1;
    DROP TABLE t1;
................................................................................
  list [file size test.db] [file size test.db-wal]
} [list [expr 3*1024] [log_file_size 6 1024]]
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 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.
#
proc sig {{db db}} {

Changes to test/walcrash.test.

188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
      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;
      INSERT INTO t1 VALUES(randomblob(900));
      INSERT INTO t1 VALUES(randomblob(900));
      INSERT INTO t1 VALUES(randomblob(900));
    }
  } {1 {child process exited abnormally}}

  do_test walcrash-5.$i.2 {
................................................................................
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
      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;
      INSERT INTO t1 VALUES(randomblob(900));
      INSERT INTO t1 VALUES(randomblob(900));
      INSERT INTO t1 VALUES(randomblob(900));
    }
  } {1 {child process exited abnormally}}

  do_test walcrash-6.$i.2 {
................................................................................
  do_test walcrash-7.$i.1 {
    crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 {
      PRAGMA journal_mode = wal;
      BEGIN;
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
      COMMIT;
      PRAGMA checkpoint;
      CREATE INDEX i1 ON t1(a);
      PRAGMA checkpoint;
    }
  } {1 {child process exited abnormally}}

  do_test walcrash-7.$i.2 {
    sqlite3 db test.db
    execsql { SELECT b FROM t1 WHERE a = 1 }
  } {2}







|







 







|







 







|

|







188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
...
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
...
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
      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 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}}

  do_test walcrash-5.$i.2 {
................................................................................
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 12 */
      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 16 */
      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 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}}

  do_test walcrash-6.$i.2 {
................................................................................
  do_test walcrash-7.$i.1 {
    crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 {
      PRAGMA journal_mode = wal;
      BEGIN;
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
      COMMIT;
      PRAGMA wal_checkpoint;
      CREATE INDEX i1 ON t1(a);
      PRAGMA wal_checkpoint;
    }
  } {1 {child process exited abnormally}}

  do_test walcrash-7.$i.2 {
    sqlite3 db test.db
    execsql { SELECT b FROM t1 WHERE a = 1 }
  } {2}

Changes to test/walhook.test.

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
#    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 the library in
# "PRAGMA journal_mode=WAL" mode.
#





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;
  }
}
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
}


do_test walhook-1.1 {




  execsql { 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}
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 }
    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
    return 0
  }
  execsql { CREATE TABLE t3(a PRIMARY KEY, b) }
  file size test.db
} [expr 6*1024]



































catch { db2 close }
catch { db close }
finish_test







>
>
>
>






|
|
<
<
<
<
|
<
<
<






>


>
>
>
>
|
>


>










<


|







|
|
|






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



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
#    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 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 log_file_size {nFrame pgsz} {
  expr {12 + ($pgsz+16)*$nFrame}




}




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 { 
    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}
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 wal_checkpoint }
    return 0
  }
  execsql { CREATE TABLE t2(a, b) }
  file size test.db
} [expr 4*1024]

do_test walhook-1.5 {
  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

Changes to test/walslow.test.

44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
      set w [expr int(rand()*2000)]
      set x [expr int(rand()*2000)]
      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 integrity_check }
    } {ok}

    do_test walslow-1.seed=$seed.$iTest.3 {
      file delete -force testX.db testX.db-wal
      file copy test.db testX.db
      file copy test.db-wal testX.db-wal







|







44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
      set w [expr int(rand()*2000)]
      set x [expr int(rand()*2000)]
      execsql { INSERT INTO t1 VALUES(randomblob($w), randomblob($x)) }
      execsql { PRAGMA integrity_check }
    } {ok}

    do_test walslow-1.seed=$seed.$iTest.2 {
      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
      file copy test.db testX.db
      file copy test.db-wal testX.db-wal

Changes to test/walthread.test.

237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
...
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
#      rows in the table.
#
# 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"
#   2) Sleep for 500 ms.
#
do_thread_test2 walthread-1 -seconds $seconds(walthread-1) -init {
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(x PRIMARY KEY);
    PRAGMA lock_status;
................................................................................
    incr nRun
  }
  set nRun

} -thread ckpt 1 {
  set nRun 0
  while {[tt_continue]} {
    db eval "PRAGMA checkpoint"
    usleep 500
    incr nRun
  }
  set nRun
}

#--------------------------------------------------------------------------







|







 







|







237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
...
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
#      rows in the table.
#
# 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 wal_checkpoint"
#   2) Sleep for 500 ms.
#
do_thread_test2 walthread-1 -seconds $seconds(walthread-1) -init {
  execsql {
    PRAGMA journal_mode = WAL;
    CREATE TABLE t1(x PRIMARY KEY);
    PRAGMA lock_status;
................................................................................
    incr nRun
  }
  set nRun

} -thread ckpt 1 {
  set nRun 0
  while {[tt_continue]} {
    db eval "PRAGMA wal_checkpoint"
    usleep 500
    incr nRun
  }
  set nRun
}

#--------------------------------------------------------------------------