text: # 2010 April 13
  #
  # 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 "blocking-checkpoint"
  # operations.
  #
  
  set testdir [file dirname $argv0]
  source $testdir/tester.tcl
  source $testdir/lock_common.tcl
  source $testdir/wal_common.tcl
  ifcapable !wal {finish_test ; return }
  do_not_use_codec
  
  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" }
        }
  
        set vals {restart full truncate}
        if {[lsearch -exact $vals $a(-mode)]<0} { 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
      } [expr {[nonzero_reserved_bytes]?"/# # 0/":"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
      } [expr {[nonzero_reserved_bytes]?"/# # #/":"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
      } [expr {[nonzero_reserved_bytes]?"/# # #/":"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 aux.auto_vacuum = 0;
        PRAGMA main.auto_vacuum = 0;
        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 3 1 3}
      do_test 2.1.$tn.3 { code1 { do_wal_checkpoint db } } {0 3 3}
      do_test 2.1.$tn.4 { file_page_counts } {2 3 2 3}
    }
  
    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 3 1 4}
      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 3 3}
      do_test 2.2.$tn.5 { file_page_counts } {2 3 2 4}
    }
  
    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 3 1 3}
      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 4 1 4}
      do_test 2.3.$tn.7 { code1 { do_wal_checkpoint db -mode full } } {1 4 3}
  
      # The checkpoint above only writes page 1 of the db file. The other
      # page (page 2) is locked by the read-transaction opened by the
      # [sql2] commmand above. So normally, the db is 1 page in size here.
      # However, in mmap() mode, the db is pre-allocated to 2 pages at the
      # start of the checkpoint, even though page 2 cannot be written.
      set nDb 2
      if {[permutation]!="mmap"} {set nDb 1}
      ifcapable !mmap {set nDb 1}
      do_test 2.3.$tn.8 { file_page_counts } [list $nDb 4 2 4]
    }
  
    # 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 3 3}   -
      2   TYPO      -   {0 3 3}   -
  
      3   FULL      -   {0 4 4}   2
      4   FULL      1   {1 3 3}   1
      5   FULL      2   {1 4 3}   2
      6   FULL      3   {0 4 4}   2
  
      7   RESTART   -   {0 4 4}   3
      8   RESTART   1   {1 3 3}   1
      9   RESTART   2   {1 4 3}   2
      10  RESTART   3   {1 4 4}   3
  
      11  TRUNCATE  -   {0 0 0}   3
      12  TRUNCATE  1   {1 3 3}   1
      13  TRUNCATE  2   {1 4 3}   2
      14  TRUNCATE  3   {1 4 4}   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 auto_vacuum = 0;
          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}
    }
  
    # Test SQLITE_CHECKPOINT_TRUNCATE.
    #
    do_multiclient_test tn {
  
      code1 $do_wal_checkpoint
      code2 $do_wal_checkpoint
      code3 $do_wal_checkpoint
  
      do_test 4.$tn.1 {
        sql1 {
          PRAGMA page_size = 1024;
          PRAGMA auto_vacuum = 0;
          PRAGMA journal_mode = WAL;
          PRAGMA synchronous = normal;
          CREATE TABLE t1(x, y);
          CREATE INDEX i1 ON t1(x, y);
          INSERT INTO t1 VALUES(1, 2);
          INSERT INTO t1 VALUES(3, 4);
        }
        file size test.db-wal
      } [wal_file_size 8 1024]
  
      do_test 4.$tn.2 { do_wal_checkpoint db -mode truncate } {0 0 0}
      do_test 4.$tn.3 { file size test.db-wal } 0
  
      do_test 4.$tn.4 {
        sql2 { SELECT * FROM t1 }
      } {1 2 3 4}
  
      do_test 4.$tn.5 {
        sql2 { INSERT INTO t1 VALUES('a', 'b') }
        file size test.db-wal
      } [wal_file_size 2 1024]
  
    }
    
    # Test that FULL, RESTART and TRUNCATE callbacks block on other clients
    # and truncate the wal file as required even if the entire wal file has
    # already been checkpointed when they are invoked.
    #
    do_multiclient_test tn {
  
      code1 $do_wal_checkpoint
      code2 $do_wal_checkpoint
      code3 $do_wal_checkpoint
  
      do_test 5.$tn.1 {
        sql1 {
          PRAGMA page_size = 1024;
          PRAGMA auto_vacuum = 0;
          PRAGMA journal_mode = WAL;
          PRAGMA synchronous = normal;
          CREATE TABLE t1(x, y);
          CREATE INDEX i1 ON t1(x, y);
          INSERT INTO t1 VALUES(1, 2);
          INSERT INTO t1 VALUES(3, 4);
          INSERT INTO t1 VALUES(5, 6);
        }
        file size test.db-wal
      } [wal_file_size 10 1024]
  
      do_test 5.$tn.2 { 
        sql2 { BEGIN; SELECT * FROM t1 }
      } {1 2 3 4 5 6}
  
      do_test 5.$tn.3 { do_wal_checkpoint db -mode passive } {0 10 10}
  
      do_test 5.$tn.4 { 
        sql3 { BEGIN; INSERT INTO t1 VALUES(7, 8); }
      } {}
  
      do_test 5.$tn.5 { do_wal_checkpoint db -mode passive  } {0 10 10}
      do_test 5.$tn.6 { do_wal_checkpoint db -mode full     } {1 10 10}
  
      do_test 5.$tn.7 { sql3 { ROLLBACK } } {}
  
      do_test 5.$tn.8 { do_wal_checkpoint db -mode full     } {0 10 10}
      do_test 5.$tn.9 { do_wal_checkpoint db -mode truncate } {1 10 10}
  
      do_test 5.$tn.10 { 
        file size test.db-wal
      } [wal_file_size 10 1024]
  
      proc xBusyHandler {n} { sql2 { COMMIT } ; return 0 }
      db busy xBusyHandler
  
      do_test 5.$tn.11 { do_wal_checkpoint db -mode truncate } {0 0 0}
      do_test 5.$tn.12 { file size test.db-wal } 0
  
      do_test 5.$tn.13 {
        sql1 {
          INSERT INTO t1 VALUES(7, 8);
          INSERT INTO t1 VALUES(9, 10);
          SELECT * FROM t1;
        }
      } {1 2 3 4 5 6 7 8 9 10}
  
      do_test 5.$tn.14 { 
        sql2 { BEGIN; SELECT * FROM t1 }
      } {1 2 3 4 5 6 7 8 9 10}
  
      proc xBusyHandler {n} { return 1 }
      do_test 5.$tn.15 { do_wal_checkpoint db -mode truncate } {1 4 4}
      do_test 5.$tn.16 { file size test.db-wal } [wal_file_size 4 1024]
  
      do_test 5.$tn.17 { do_wal_checkpoint db -mode restart } {1 4 4}
  
      proc xBusyHandler {n} { sql2 { COMMIT } ; return 0 }
      db busy xBusyHandler
      do_test 5.$tn.18 { do_wal_checkpoint db -mode restart } {0 4 4}
      do_test 5.$tn.19 { file size test.db-wal } [wal_file_size 4 1024]
  
      do_test 5.$tn.20 { do_wal_checkpoint db -mode truncate } {0 0 0}
      do_test 5.$tn.21 { file size test.db-wal } 0
    }
  
  }
  
  
  finish_test
  