SQLite

  int *pnCkpt                     /* OUT: Total number of frames checkpointed */
){
#ifdef SQLITE_OMIT_WAL
  return SQLITE_OK;
#else
  int rc;                         /* Return code */
  int iDb = SQLITE_MAX_ATTACHED;  /* sqlite3.aDb[] index of db to checkpoint */

  /* Initialize the output variables to -1 in case an error occurs. */
  if( pnLog ) *pnLog = -1;
  if( pnCkpt ) *pnCkpt = -1;

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

*/
int sqlite3Checkpoint(sqlite3 *db, int iDb, int eMode, int *pnLog, int *pnCkpt){
  int rc = SQLITE_OK;             /* Return code */
  int i;                          /* Used to iterate through attached dbs */
  int bBusy = 0;                  /* True if SQLITE_BUSY has been encountered */

  assert( sqlite3_mutex_held(db->mutex) );
  assert( !pnLog || *pnLog==-1 );
  assert( !pnCkpt || *pnCkpt==-1 );

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

  if( objc==3 ){
    zDb = Tcl_GetString(objv[2]);
  }
  rc = sqlite3_wal_checkpoint(db, zDb);
  Tcl_SetResult(interp, (char *)t1ErrorName(rc), TCL_STATIC);
  return TCL_OK;
}

/*
** tclcmd:  sqlite3_wal_checkpoint_v2 db MODE ?NAME?
**
** This command calls the wal_checkpoint_v2() function with the specified
** mode argument (passive, full or restart). If present, the database name
** NAME is passed as the second argument to wal_checkpoint_v2(). If it the
** NAME argument is not present, a NULL pointer is passed instead.
**
** If wal_checkpoint_v2() returns any value other than SQLITE_BUSY or
** SQLITE_OK, then this command returns TCL_ERROR. The Tcl result is set
** to the error message obtained from sqlite3_errmsg().
**
** Otherwise, this command returns a list of three integers. The first integer
** is 1 if SQLITE_BUSY was returned, or 0 otherwise. The following two integers
** are the values returned via the output paramaters by wal_checkpoint_v2() -
** the number of frames in the log and the number of frames in the log
** that have been checkpointed.
*/
static int test_wal_checkpoint_v2(
  ClientData clientData, /* Unused */
  Tcl_Interp *interp,    /* The TCL interpreter that invoked this command */
  int objc,              /* Number of arguments */
  Tcl_Obj *CONST objv[]  /* Command arguments */
){
  char *zDb = 0;
  sqlite3 *db;
  int rc;

  int eMode;
  int nLog = -555;
  int nCkpt = -555;
  Tcl_Obj *pRet;

  const char * aMode[] = { "passive", "full", "restart", 0 };
  assert( SQLITE_CHECKPOINT_PASSIVE==0 );
  assert( SQLITE_CHECKPOINT_FULL==1 );
  assert( SQLITE_CHECKPOINT_RESTART==2 );

  if( objc!=3 && objc!=4 ){
    Tcl_WrongNumArgs(interp, 1, objv, "DB MODE ?NAME?");
    return TCL_ERROR;
  }

  if( objc==4 ){
    zDb = Tcl_GetString(objv[3]);
  }
  if( getDbPointer(interp, Tcl_GetString(objv[1]), &db)
   || Tcl_GetIndexFromObj(interp, objv[2], aMode, "mode", 0, &eMode) 
  ){
    return TCL_ERROR;
  }

  rc = sqlite3_wal_checkpoint_v2(db, zDb, eMode, &nLog, &nCkpt);
  if( rc!=SQLITE_OK && rc!=SQLITE_BUSY ){
    Tcl_SetResult(interp, (char *)sqlite3_errmsg(db), TCL_VOLATILE);
    return TCL_ERROR;
  }

  pRet = Tcl_NewObj();
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(rc==SQLITE_BUSY?1:0));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nLog));
  Tcl_ListObjAppendElement(interp, pRet, Tcl_NewIntObj(nCkpt));
  Tcl_SetObjResult(interp, pRet);

  return TCL_OK;
}

/*
** tclcmd:  test_sqlite3_log ?SCRIPT?
*/
static struct LogCallback {
  Tcl_Interp *pInterp;
  Tcl_Obj *pObj;

     { "sqlite3_blob_close",  test_blob_close, 0  },
#endif
     { "pcache_stats",       test_pcache_stats, 0  },
#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
     { "sqlite3_unlock_notify", test_unlock_notify, 0  },
#endif
     { "sqlite3_wal_checkpoint",   test_wal_checkpoint, 0  },
     { "sqlite3_wal_checkpoint_v2",test_wal_checkpoint_v2, 0  },
     { "test_sqlite3_log",         test_sqlite3_log, 0  },
     { "print_explain_query_plan", test_print_eqp, 0  },
  };
  static int bitmask_size = sizeof(Bitmask)*8;
  int i;
  extern int sqlite3_sync_count, sqlite3_fullsync_count;
  extern int sqlite3_opentemp_count;

*/
static int walCheckpoint(
  Wal *pWal,                      /* Wal connection */
  int eMode,                      /* One of PASSIVE, FULL or RESTART */
  int (*xBusyCall)(void*),        /* Function to call when busy */
  void *pBusyArg,                 /* Context argument for xBusyHandler */
  int sync_flags,                 /* Flags for OsSync() (or 0) */
  u8 *zBuf                        /* Temporary buffer to use */

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

  szPage = walPagesize(pWal);
  testcase( szPage<=32768 );
  testcase( szPage>=65536 );
  pInfo = walCkptInfo(pWal);

  if( pInfo->nBackfill>=pWal->hdr.mxFrame ) return SQLITE_OK;

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

        rc = sqlite3OsTruncate(pWal->pDbFd, szDb);
        if( rc==SQLITE_OK && sync_flags ){
          rc = sqlite3OsSync(pWal->pDbFd, sync_flags);
        }
      }
      if( rc==SQLITE_OK ){
        pInfo->nBackfill = mxSafeFrame;

      }
    }

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

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

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

    /* If no error occurred, set the output variables. */
    if( rc==SQLITE_OK || rc==SQLITE_BUSY ){
      if( pnLog ) *pnLog = (int)pWal->hdr.mxFrame;

      if( pnCkpt ) *pnCkpt = (int)(walCkptInfo(pWal)->nBackfill);
    }
  }

  if( isChanged ){
    /* If a new wal-index header was loaded before the checkpoint was 
    ** performed, then the pager-cache associated with pWal is now
    ** out of date. So zero the cached wal-index header to ensure that

do_catchsql_test 6.2 { SELECT * FROM t1 } {1 {no such table: t1}}
do_catchsql_test 6.3 { SELECT * FROM t2 } {0 {a b}}

db_swap test.db2 test.db
do_catchsql_test 6.4 { SELECT * FROM t1 } {0 {1 2 3 4}}
do_catchsql_test 6.5 { SELECT * FROM t2 } {1 {no such table: t2}}

do_execsql_test  6.6 { PRAGMA wal_checkpoint } {0 0 0}

db_swap test.db2 test.db
do_catchsql_test 6.7 { SELECT * FROM t1 } {1 {no such table: t1}}
do_catchsql_test 6.8 { SELECT * FROM t2 } {0 {a b}}

db_swap test.db2 test.db
do_catchsql_test 6.9 { SELECT * FROM t1 } {0 {1 2 3 4}}

set testprefix wal5

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


# A checkpoint may be requested either using the C API or by executing
# an SQL PRAGMA command. To test both methods, all tests in this file are 
# run twice - once using each method to request checkpoints.
#
foreach {testprefix do_wal_checkpoint} {

  wal5-pragma {
    proc do_wal_checkpoint { dbhandle args } {
      array set a $args
      foreach key [array names a] {
        if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" }
      }

      set sql "PRAGMA "
      if {[info exists a(-db)]} { append sql "$a(-db)." }
      append sql "wal_checkpoint"
      if {[info exists a(-mode)]} { append sql " = $a(-mode)" }

      uplevel [list $dbhandle eval $sql]
    }
  }

  wal5-capi {
    proc do_wal_checkpoint { dbhandle args } {
      set a(-mode) passive
      array set a $args
      foreach key [array names a] {
        if {[lsearch {-mode -db} $key]<0} { error "unknown switch: $key" }
      }

      if {$a(-mode)!="restart" && $a(-mode)!="full"} { set a(-mode) passive }

      set cmd [list sqlite3_wal_checkpoint_v2 $dbhandle $a(-mode)]
      if {[info exists a(-db)]} { lappend sql $a(-db) }

      uplevel $cmd
    }
  }
} {

  eval $do_wal_checkpoint

  do_multiclient_test tn {

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

    proc reopen_all {} {
      code1 {db close}
      code2 {db2 close}
      code3 {db3 close}

      code1 {sqlite3 db test.db}
      code2 {sqlite3 db2 test.db}
      code3 {sqlite3 db3 test.db}

      sql1  { PRAGMA synchronous = NORMAL }
      code1 { db busy busyhandler }
    }

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

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

    # Attempt a checkpoint.
    do_test 1.$tn.3 {
      code1 { do_wal_checkpoint db }
      list [db_page_count] [wal_page_count]
    } {5 9}

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

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

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

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

    set ::busy_handler_script { 
      if {$n==5} { sql2 COMMIT } 
      if {$n==6} { set ::db_file_size [db_page_count] }
      if {$n==7} { sql3 COMMIT }
    }
    do_test 1.$tn.11 {
      code1 { do_wal_checkpoint db -mode restart }
      list [db_page_count] [wal_page_count] $::nBusyHandler
    } {10 5 8}
    do_test 1.$tn.12 { set ::db_file_size } 10
  }


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

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

  # Test that executing "PRAGMA wal_checkpoint" checkpoints all attached
  # databases, not just the main db.  In capi mode, check that this is
  # true if a NULL pointer is passed to wal_checkpoint_v2() in place of a 
  # database name.
  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.1.$tn.1 {
      sql1 {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } {1 5 1 5}
    do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } {0 5 5}
    do_test 2.1.$tn.4 { file_page_counts } {2 5 2 5}
  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.2.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
        INSERT INTO t2 VALUES(3, 4);
      }
    } {}
    do_test 2.2.$tn.2 { file_page_counts } {1 5 1 7}
    do_test 2.2.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.2.$tn.4 { code1 { do_wal_checkpoint db -mode restart } } {1 5 5}
    do_test 2.2.$tn.5 { file_page_counts } {2 5 2 7}
  }

  do_multiclient_test tn {
    setup_and_attach_aux
    do_test 2.3.$tn.1 {
      execsql {
        CREATE TABLE t1(a, b);
        INSERT INTO t1 VALUES(1, 2);
        CREATE TABLE aux.t2(a, b);
        INSERT INTO t2 VALUES(1, 2);
      }
    } {}
    do_test 2.3.$tn.2 { file_page_counts } {1 5 1 5}
    do_test 2.3.$tn.3 { sql2 { BEGIN; SELECT * FROM t1 } } {1 2}
    do_test 2.3.$tn.4 { sql1 { INSERT INTO t1 VALUES(3, 4) } } {}
    do_test 2.3.$tn.5 { sql1 { INSERT INTO t2 VALUES(3, 4) } } {}
    do_test 2.3.$tn.6 { file_page_counts } {1 7 1 7}
    do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } {1 7 5}
    do_test 2.3.$tn.8 { file_page_counts } {1 7 2 7}
  }

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

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

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

  } {
    do_multiclient_test tn {
      setup_and_attach_aux

      proc busyhandler {x} {
        set ::max_busyhandler $x
        if {$::busy_on!="-" && $x==$::busy_on} { return 1 }
        switch -- $x {
          1 { sql2 "COMMIT ; BEGIN ; SELECT * FROM t1" }
          2 { sql3 "COMMIT" }
          3 { sql2 "COMMIT" }
        }
        return 0
      }
      set ::max_busyhandler -

      do_test 2.4.$tn1.$tn.1 {
        sql1 {
          CREATE TABLE t1(a, b);
          INSERT INTO t1 VALUES(1, 2);
        }
        sql2 { BEGIN; INSERT INTO t1 VALUES(3, 4) }
        sql3 { BEGIN; SELECT * FROM t1 }
      } {1 2}

      do_test 2.4.$tn1.$tn.2 {
        code1 { db busy busyhandler }
        code1 { do_wal_checkpoint db -mode [string tolower $checkpoint] }
      } $ckpt_expected
      do_test 2.4.$tn1.$tn.3 { set ::max_busyhandler } $expected
    }
  }


  do_multiclient_test tn {

    code1 $do_wal_checkpoint
    code2 $do_wal_checkpoint
    code3 $do_wal_checkpoint
    
    do_test 3.$tn.1 {
      sql1 {
        PRAGMA journal_mode = WAL;
        PRAGMA synchronous = normal;
        CREATE TABLE t1(x, y);
      }

      sql2 { PRAGMA journal_mode }
      sql3 { PRAGMA journal_mode }
    } {wal}

    do_test 3.$tn.2 { code2 { do_wal_checkpoint db2 } } {0 2 2}

    do_test 3.$tn.3 { code2 { do_wal_checkpoint db2 } } {0 2 2}

    do_test 3.$tn.4 { code3 { do_wal_checkpoint db3 } } {0 2 2}

    code1 {db  close}
    code2 {db2 close}
    code3 {db3 close}

    code1 {sqlite3 db  test.db}
    code2 {sqlite3 db2 test.db}
    code3 {sqlite3 db3 test.db}

    do_test 3.$tn.5 { sql3 { PRAGMA journal_mode } } {wal}

    do_test 3.$tn.6 { code3 { do_wal_checkpoint db3 } } {0 0 0}
  }
}


finish_test