Import SQLite 3.7.6.3.

third_party/sqlite/src/test/walcrash.test

+# 2010 February 8
+#
+# 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 when
+# recovering a database following a simulated system failure in 
+# "PRAGMA journal_mode=WAL" mode.
+#
+
+#
+# These are 'warm-body' tests of database recovery used while developing 
+# the WAL code. They serve to prove that a few really simple cases work:
+#
+# walcrash-1.*: Recover a database.
+# walcrash-2.*: Recover a database where the failed transaction spanned more
+#               than one page.
+# walcrash-3.*: Recover multiple databases where the failed transaction 
+#               was a multi-file transaction.
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+ifcapable !wal {finish_test ; return }
+
+db close
+
+set seed 0
+set REPEATS 100
+
+# walcrash-1.*
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+  do_test walcrash-1.$i.1 {
+    crashsql -delay 4 -file test.db-wal -seed [incr seed] {
+      PRAGMA journal_mode = WAL;
+      CREATE TABLE t1(a, b);
+      INSERT INTO t1 VALUES(1, 1);
+      INSERT INTO t1 VALUES(2, 3);
+      INSERT INTO t1 VALUES(3, 6);
+    }
+  } {1 {child process exited abnormally}}
+  do_test walcrash-1.$i.2 {
+    sqlite3 db test.db
+    execsql { SELECT sum(a)==max(b) FROM t1 }
+  } {1}
+  integrity_check walcrash-1.$i.3
+  db close
+  
+  do_test walcrash-1.$i.4 {
+    crashsql -delay 2 -file test.db-wal -seed [incr seed] {
+      INSERT INTO t1 VALUES(4, (SELECT sum(a) FROM t1) + 4);
+      INSERT INTO t1 VALUES(5, (SELECT sum(a) FROM t1) + 5);
+    }
+  } {1 {child process exited abnormally}}
+  do_test walcrash-1.$i.5 {
+    sqlite3 db test.db
+    execsql { SELECT sum(a)==max(b) FROM t1 }
+  } {1}
+  integrity_check walcrash-1.$i.6
+  do_test walcrash-1.$i.7 {
+    execsql { PRAGMA main.journal_mode }
+  } {wal}
+  db close
+}
+
+# walcrash-2.*
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+  do_test walcrash-2.$i.1 {
+    crashsql -delay 4 -file test.db-wal -seed [incr seed] {
+      PRAGMA journal_mode = WAL;
+      CREATE TABLE t1(a PRIMARY KEY, b);
+      INSERT INTO t1 VALUES(1, 2);
+      INSERT INTO t1 VALUES(3, 4);
+      INSERT INTO t1 VALUES(5, 9);
+    }
+  } {1 {child process exited abnormally}}
+  do_test walcrash-2.$i.2 {
+    sqlite3 db test.db
+    execsql { SELECT sum(a)==max(b) FROM t1 }
+  } {1}
+  integrity_check walcrash-2.$i.3
+  db close
+  
+  do_test walcrash-2.$i.4 {
+    crashsql -delay 2 -file test.db-wal -seed [incr seed] {
+      INSERT INTO t1 VALUES(6, (SELECT sum(a) FROM t1) + 6);
+      INSERT INTO t1 VALUES(7, (SELECT sum(a) FROM t1) + 7);
+    }
+  } {1 {child process exited abnormally}}
+  do_test walcrash-2.$i.5 {
+    sqlite3 db test.db
+    execsql { SELECT sum(a)==max(b) FROM t1 }
+  } {1}
+  integrity_check walcrash-2.$i.6
+  do_test walcrash-2.$i.6 {
+    execsql { PRAGMA main.journal_mode }
+  } {wal}
+  db close
+}
+
+# walcrash-3.*
+#
+# for {set i 1} {$i < $REPEATS} {incr i} {
+#   file delete -force test.db test.db-wal
+#   file delete -force test2.db test2.db-wal
+# 
+#   do_test walcrash-3.$i.1 {
+#     crashsql -delay 2 -file test2.db-wal -seed [incr seed] {
+#       PRAGMA journal_mode = WAL;
+#       ATTACH 'test2.db' AS aux;
+#       CREATE TABLE t1(a PRIMARY KEY, b);
+#       CREATE TABLE aux.t2(a PRIMARY KEY, b);
+#       BEGIN;
+#         INSERT INTO t1 VALUES(1, 2);
+#         INSERT INTO t2 VALUES(1, 2);
+#       COMMIT;
+#     }
+#   } {1 {child process exited abnormally}}
+# 
+#   do_test walcrash-3.$i.2 {
+#     sqlite3_wal db test.db
+#     execsql { 
+#       ATTACH 'test2.db' AS aux;
+#       SELECT * FROM t1 EXCEPT SELECT * FROM t2;
+#     }
+#   } {}
+#   do_test walcrash-3.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
+#   do_test walcrash-3.$i.4 { execsql { PRAGMA aux.integrity_check  } } {ok}
+# 
+#   db close
+# }
+
+# walcrash-4.*
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+  file delete -force test2.db test2.db-wal
+
+  do_test walcrash-4.$i.1 {
+    crashsql -delay 3 -file test.db-wal -seed [incr seed] -blocksize 4096 {
+      PRAGMA journal_mode = WAL;
+      PRAGMA page_size = 1024;
+      CREATE TABLE t1(a PRIMARY KEY, b);
+      INSERT INTO t1 VALUES(1, 2);
+      INSERT INTO t1 VALUES(3, 4);
+    }
+  } {1 {child process exited abnormally}}
+
+  do_test walcrash-4.$i.2 {
+    sqlite3 db test.db
+    execsql { 
+      SELECT * FROM t1 WHERE a = 1;
+    }
+  } {1 2}
+  do_test walcrash-4.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
+  do_test walcrash-4.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
+
+  db close
+}
+
+# walcrash-5.*
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+  file delete -force test2.db test2.db-wal
+
+  do_test walcrash-5.$i.1 {
+    crashsql -delay 11 -file test.db-wal -seed [incr seed] -blocksize 4096 {
+      PRAGMA journal_mode = WAL;
+      PRAGMA page_size = 1024;
+      BEGIN;
+        CREATE TABLE t1(x PRIMARY KEY);
+        INSERT INTO t1 VALUES(randomblob(900));
+        INSERT INTO t1 VALUES(randomblob(900));
+        INSERT INTO t1 SELECT randomblob(900) FROM t1;           /* 4 */
+      COMMIT;
+      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 8 */
+      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 {
+    sqlite3 db test.db
+    execsql { SELECT count(*)==33 OR count(*)==34 FROM t1 WHERE x != 1 }
+  } {1}
+  do_test walcrash-5.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
+  do_test walcrash-5.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
+
+  db close
+}
+
+# walcrash-6.*
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+  file delete -force test2.db test2.db-wal
+
+  do_test walcrash-6.$i.1 {
+    crashsql -delay 12 -file test.db-wal -seed [incr seed] -blocksize 512 {
+      PRAGMA journal_mode = WAL;
+      PRAGMA page_size = 1024;
+      BEGIN;
+        CREATE TABLE t1(x PRIMARY KEY);
+        INSERT INTO t1 VALUES(randomblob(900));
+        INSERT INTO t1 VALUES(randomblob(900));
+        INSERT INTO t1 SELECT randomblob(900) FROM t1;           /* 4 */
+      COMMIT;
+      INSERT INTO t1 SELECT randomblob(900) FROM t1 LIMIT 4;   /* 8 */
+      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 {
+    sqlite3 db test.db
+    execsql { SELECT count(*)==34 OR count(*)==35 FROM t1 WHERE x != 1 }
+  } {1}
+  do_test walcrash-6.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
+  do_test walcrash-6.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
+
+  db close
+}
+
+#-------------------------------------------------------------------------
+# This test case simulates a crash while checkpointing the database. Page
+# 1 is one of the pages overwritten by the checkpoint. This is a special
+# case because it means the content of page 1 may be damaged. SQLite will
+# have to determine:
+#
+#   (a) that the database is a WAL database, and 
+#   (b) the database page-size
+#
+# based on the log file.
+#
+for {set i 1} {$i < $REPEATS} {incr i} {
+  file delete -force test.db test.db-wal
+
+  # Select a page-size for this test.
+  #
+  set pgsz [lindex {512 1024 2048 4096 8192 16384} [expr $i%6]]
+
+  do_test walcrash-7.$i.1 {
+    crashsql -delay 3 -file test.db -seed [incr seed] -blocksize 512 "
+      PRAGMA page_size = $pgsz;
+      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}
+  do_test walcrash-7.$i.3 { execsql { PRAGMA main.integrity_check } } {ok}
+  do_test walcrash-7.$i.4 { execsql { PRAGMA main.journal_mode } } {wal}
+
+  db close
+}
+
+finish_test
+