SQLite

}

#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.
**
** The callback registered by this function replaces any existing callback
** 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);

      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 ){

  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 */

#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

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

    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;

    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
  # checkpointing the database. Add a busy-handler to [db]. If [db2] completes
  # its transaction from within the busy-handler, [db] is able to complete
  # the checkpoint operation.
  #
  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) }
  } {}
  do_test wal-10.$tn.28 {
    code3 {
      set ::STMT [sqlite3_prepare db3 "SELECT * FROM t1" -1 TAIL]
      sqlite3_step $::STMT
    }
    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

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

      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}

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

      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

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

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