[sql] Import reference version of SQLite 3.17..

third_party/sqlite/sqlite-src-3170000/test/walbak.test

+# 2010 April 22
+#
+# 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.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+source $testdir/wal_common.tcl
+source $testdir/malloc_common.tcl
+
+do_not_use_codec
+
+ifcapable !wal {finish_test ; return }
+
+
+# Test organization:
+# 
+#   walback-1.*: Simple tests.
+#
+#   walback-2.*: Test backups when the source db is modified mid-backup.
+#
+#   walback-3.*: Backup of WAL sources into rollback destinations, and 
+#                vice-versa.
+#
+
+# Make sure a simple backup from a WAL database works.
+#
+do_test walbak-1.0 {
+  execsql { 
+    PRAGMA synchronous = NORMAL;
+    PRAGMA page_size = 1024;
+    PRAGMA auto_vacuum = 0;
+    PRAGMA journal_mode = wal;
+    BEGIN;
+      CREATE TABLE t1(a PRIMARY KEY, b);
+      INSERT INTO t1 VALUES('I', 'one');
+    COMMIT;
+  }
+} {wal}
+do_test walbak-1.1 {
+  forcedelete bak.db bak.db-journal bak.db-wal
+  db backup bak.db
+  file size bak.db
+} [expr 3*1024]
+do_test walbak-1.2 {
+  sqlite3 db2 bak.db
+  execsql { 
+    SELECT * FROM t1;
+    PRAGMA main.journal_mode;
+  } db2
+} {I one wal}
+do_test walbak-1.3 {
+  execsql { PRAGMA integrity_check } db2
+} {ok}
+db2 close
+
+# Try a VACUUM on a WAL database.
+#
+do_test walbak-1.4 {
+  execsql { 
+    VACUUM;
+    PRAGMA main.journal_mode;
+  }
+} {wal}
+do_test walbak-1.5 {
+  list [file size test.db] [file size test.db-wal]
+} [list 1024 [wal_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] [wal_file_size 6 1024]]
+do_test walbak-1.6.1 {
+  hexio_read test.db 18 2
+} {0202}
+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] [wal_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] [wal_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] [wal_file_size 8 1024]]
+
+#-------------------------------------------------------------------------
+# Backups when the source db is modified mid-backup.
+#
+proc sig {{db db}} {
+  $db eval { 
+    PRAGMA integrity_check;
+    SELECT md5sum(a, b) FROM t1; 
+  }
+}
+db close
+delete_file test.db
+sqlite3 db test.db
+do_test walbak-2.1 {
+  execsql { PRAGMA journal_mode = WAL }
+  execsql {
+    CREATE TABLE t1(a PRIMARY KEY, b);
+    BEGIN;
+      INSERT INTO t1 VALUES(randomblob(500), randomblob(500));
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  2 */
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  4 */
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /*  8 */
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 16 */
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 32 */
+      INSERT INTO t1 SELECT randomblob(500), randomblob(500) FROM t1; /* 64 */
+    COMMIT;
+  }
+} {}
+do_test walbak-2.2 {
+  forcedelete abc.db
+  db backup abc.db
+  sqlite3 db2 abc.db
+  string compare [sig db] [sig db2]
+} {0}
+
+do_test walbak-2.3 {
+  sqlite3_backup B db2 main db main
+  B step 50
+  execsql { UPDATE t1 SET b = randomblob(500) }
+  list [B step 1000] [B finish]
+} {SQLITE_DONE SQLITE_OK}
+do_test walbak-2.4 {
+  string compare [sig db] [sig db2]
+} {0}
+
+do_test walbak-2.5 {
+  db close
+  sqlite3 db test.db
+  execsql { PRAGMA cache_size = 10 }
+  sqlite3_backup B db2 main db main
+  B step 50
+  execsql {
+    BEGIN;
+      UPDATE t1 SET b = randomblob(500);
+  }
+  expr [file size test.db-wal] > 10*1024
+} {1}
+do_test walbak-2.6 {
+  B step 1000
+} {SQLITE_BUSY}
+do_test walbak-2.7 {
+  execsql COMMIT
+  list [B step 1000] [B finish]
+} {SQLITE_DONE SQLITE_OK}
+do_test walbak-2.8 {
+  string compare [sig db] [sig db2]
+} {0}
+
+do_test walbak-2.9 {
+  db close
+  sqlite3 db test.db
+  execsql { PRAGMA cache_size = 10 }
+  sqlite3_backup B db2 main db main
+  B step 50
+  execsql {
+    BEGIN;
+      UPDATE t1 SET b = randomblob(500);
+  }
+  expr [file size test.db-wal] > 10*1024
+} {1}
+do_test walbak-2.10 {
+  B step 1000
+} {SQLITE_BUSY}
+do_test walbak-2.11 {
+  execsql ROLLBACK
+set sigB [sig db]
+  list [B step 1000] [B finish]
+} {SQLITE_DONE SQLITE_OK}
+do_test walbak-2.12 {
+  string compare [sig db] [sig db2]
+} {0}
+db2 close
+db close
+
+#-------------------------------------------------------------------------
+# Run some backup operations to copy back and forth between WAL and:
+#
+#   walbak-3.1.*: an in-memory database
+#
+#   walbak-3.2.*: a temporary database
+#
+#   walbak-3.3.*: a database in rollback mode.
+#
+#   walbak-3.4.*: a database in rollback mode that (initially) uses a 
+#                 different page-size.
+#
+# Check that this does not confuse any connected clients.
+#
+foreach {tn setup} {
+  1 {
+    sqlite3 db  test.db
+    sqlite3 db2 :memory:
+    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
+    db2 eval { PRAGMA page_size = 1024 }
+  }
+
+  2 {
+    sqlite3 db  test.db
+    sqlite3 db2 ""
+    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
+    db2 eval { PRAGMA page_size = 1024 }
+  }
+
+  3 {
+    sqlite3 db  test.db
+    sqlite3 db2 test.db2
+    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
+    db2 eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = PERSIST }
+  }
+
+  4 {
+    sqlite3 db  test.db
+    sqlite3 db2 test.db2
+    db  eval { PRAGMA page_size = 1024 ; PRAGMA journal_mode = WAL }
+    db2 eval { 
+      PRAGMA page_size = 2048;
+      PRAGMA journal_mode = PERSIST;
+      CREATE TABLE xx(x);
+    }
+  }
+
+} {
+  if {$tn==4 && [sqlite3 -has-codec]} continue
+  foreach f [glob -nocomplain test.db*] { forcedelete $f }
+
+  eval $setup
+
+  do_test walbak-3.$tn.1 {
+    execsql {
+      CREATE TABLE t1(a, b);
+      INSERT INTO t1 VALUES(1, 2);
+      INSERT INTO t1 VALUES(3, 4);
+      SELECT * FROM t1;
+    }
+  } {1 2 3 4}
+
+  do_test walbak-3.$tn.2 {
+    sqlite3_backup B db2 main db main
+    B step 10000
+    B finish
+    execsql { SELECT * FROM t1 } db2
+  } {1 2 3 4}
+
+  do_test walbak-3.$tn.3 {
+    execsql {
+      INSERT INTO t1 VALUES(5, 6);
+      INSERT INTO t1 VALUES(7, 8);
+      SELECT * FROM t1;
+    } db2
+  } {1 2 3 4 5 6 7 8}
+
+  do_test walbak-3.$tn.4 {
+    sqlite3_backup B db main db2 main
+    B step 10000
+    B finish
+    execsql { SELECT * FROM t1 }
+  } {1 2 3 4 5 6 7 8}
+
+  # Check that [db] is still in WAL mode.
+  do_test walbak-3.$tn.5 {
+    execsql { PRAGMA journal_mode }
+  } {wal}
+  do_test walbak-3.$tn.6 {
+    execsql { PRAGMA wal_checkpoint }
+    hexio_read test.db 18 2
+  } {0202}
+
+  # If it was not an in-memory database, check that [db2] is still in
+  # rollback mode.
+  if {[file exists test.db2]} {
+    do_test walbak-3.$tn.7 {
+      execsql { PRAGMA journal_mode } db2
+    } {wal}
+    do_test walbak-3.$tn.8 {
+      execsql { PRAGMA wal_checkpoint }
+      hexio_read test.db 18 2
+    } {0202}
+  }
+
+  db  close
+  db2 close
+}
+
+#-------------------------------------------------------------------------
+# Test that the following holds when a backup operation is run:
+#
+#   Source  |  Destination inital  |  Destination final
+#   ---------------------------------------------------
+#   Rollback   Rollback               Rollback
+#   Rollback   WAL                    WAL
+#   WAL        Rollback               WAL
+#   WAL        WAL                    WAL
+#
+foreach {tn src dest dest_final} {
+  1   delete    delete    delete
+  2   delete    wal       wal
+  3   wal       delete    wal
+  4   wal       wal       wal
+} {
+  catch { db close } 
+  catch { db2 close } 
+  forcedelete test.db test.db2
+
+  do_test walbak-4.$tn.1 {
+    sqlite3 db test.db
+    db eval "PRAGMA journal_mode = $src"
+    db eval {
+      CREATE TABLE t1(a, b);
+      INSERT INTO t1 VALUES('I', 'II');
+      INSERT INTO t1 VALUES('III', 'IV');
+    }
+
+    sqlite3 db2 test.db2
+    db2 eval "PRAGMA journal_mode = $dest"
+    db2 eval {
+      CREATE TABLE t2(x, y);
+      INSERT INTO t2 VALUES('1', '2');
+      INSERT INTO t2 VALUES('3', '4');
+    }
+  } {}
+
+  do_test walbak-4.$tn.2 { execsql { PRAGMA journal_mode } db  } $src
+  do_test walbak-4.$tn.3 { execsql { PRAGMA journal_mode } db2 } $dest
+
+  do_test walbak-4.$tn.4 { db backup test.db2 } {}
+  do_test walbak-4.$tn.5 {
+    execsql { SELECT * FROM t1 } db2
+  } {I II III IV}
+  do_test walbak-4.$tn.5 { execsql { PRAGMA journal_mode } db2 } $dest_final
+
+
+  db2 close
+  do_test walbak-4.$tn.6 { file exists test.db2-wal } 0
+  sqlite3 db2 test.db2
+  do_test walbak-4.$tn.7 { execsql { PRAGMA journal_mode } db2 } $dest_final
+}
+
+
+finish_test