Import SQLite 3.7.6.3.

third_party/sqlite/src/test/wal5.test

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