3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
6 # May you do good and not evil.
7 # May you find forgiveness for yourself and forgive others.
8 # May you share freely, never taking more than you give.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the operation of the library in
13 # "PRAGMA journal_mode=WAL" mode.
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 source $testdir/lock_common.tcl
19 source $testdir/malloc_common.tcl
20 source $testdir/wal_common.tcl
24 ifcapable !wal {finish_test ; return }
26 set sqlite_sync_count 0
27 proc cond_incr_sync_count {adj} {
28 global sqlite_sync_count
29 if {$::tcl_platform(platform) == "windows"} {
30 incr sqlite_sync_count $adj
33 incr sqlite_sync_count $adj
38 proc set_tvfs_hdr {file args} {
40 # Set $nHdr to the number of bytes in the wal-index header:
42 set nInt [expr {$nHdr/4}]
44 if {[llength $args]>2} {
45 error {wrong # args: should be "set_tvfs_hdr fileName ?val1? ?val2?"}
48 set blob [tvfs shm $file]
49 if {$::tcl_platform(byteOrder)=="bigEndian"} {set fmt I} {set fmt i}
51 if {[llength $args]} {
52 set ia [lindex $args 0]
54 if {[llength $args]==2} {
55 set ib [lindex $args 1]
57 binary scan $blob a[expr $nHdr*2]a* dummy tail
58 set blob [binary format ${fmt}${nInt}${fmt}${nInt}a* $ia $ib $tail]
62 binary scan $blob ${fmt}${nInt} ints
66 proc incr_tvfs_hdr {file idx incrval} {
67 set ints [set_tvfs_hdr $file]
68 set v [lindex $ints $idx]
71 set_tvfs_hdr $file $ints
75 #-------------------------------------------------------------------------
78 # Set up a small database containing a single table. The database is not
79 # checkpointed during the test - all content resides in the log file.
81 # Two connections are established to the database file - a writer ([db])
82 # and a reader ([db2]). For each of the 8 integer fields in the wal-index
83 # header (6 fields and 2 checksum values), do the following:
85 # 1. Modify the database using the writer.
87 # 2. Attempt to read the database using the reader. Before the reader
88 # has a chance to snapshot the wal-index header, increment one
89 # of the integer fields (so that the reader ends up with a corrupted
92 # 3. Check that the reader recovers the wal-index and reads the correct
96 proc tvfs_cb {method filename args} {
97 set ::filename $filename
105 sqlite3 db test.db -vfs tvfs
106 sqlite3 db2 test.db -vfs tvfs
109 PRAGMA journal_mode = WAL;
113 INSERT INTO t1 VALUES(1);
114 INSERT INTO t1 VALUES(2);
115 INSERT INTO t1 VALUES(3);
116 INSERT INTO t1 VALUES(4);
117 SELECT count(a), sum(a) FROM t1;
121 execsql { SELECT count(a), sum(a) FROM t1 } db2
125 {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive} \
126 {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive} \
129 {4 1 lock shared} {4 1 unlock shared} \
132 {4 1 lock exclusive} {4 1 unlock exclusive} \
135 foreach {tn iInsert res wal_index_hdr_mod wal_locks} "
136 2 5 {5 15} 0 {$RECOVER $READ}
137 3 6 {6 21} 1 {$RECOVER $READ}
138 4 7 {7 28} 2 {$RECOVER $READ}
139 5 8 {8 36} 3 {$RECOVER $READ}
140 6 9 {9 45} 4 {$RECOVER $READ}
141 7 10 {10 55} 5 {$RECOVER $READ}
142 8 11 {11 66} 6 {$RECOVER $READ}
143 9 12 {12 78} 7 {$RECOVER $READ}
144 10 13 {13 91} 8 {$RECOVER $READ}
145 11 14 {14 105} 9 {$RECOVER $READ}
146 12 15 {15 120} -1 {$INITSLOT $READ}
149 do_test wal2-1.$tn.1 {
150 execsql { INSERT INTO t1 VALUES($iInsert) }
152 proc tvfs_cb {method args} {
153 lappend ::locks [lindex $args 2]
157 if {$::wal_index_hdr_mod >= 0} {
158 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
160 execsql { SELECT count(a), sum(a) FROM t1 } db2
163 do_test wal2-1.$tn.2 {
170 forcedelete test.db test.db-wal test.db-journal
172 #-------------------------------------------------------------------------
173 # This test case is very similar to the previous one, except, after
174 # the reader reads the corrupt wal-index header, but before it has
175 # a chance to re-read it under the cover of the RECOVER lock, the
176 # wal-index header is replaced with a valid, but out-of-date, header.
178 # Because the header checksum looks Ok, the reader does not run recovery,
179 # it simply drops back to a READ lock and proceeds. But because the
180 # header is out-of-date, the reader reads the out-of-date snapshot.
182 # After this, the header is corrupted again and the reader is allowed
183 # to run recovery. This time, it sees an up-to-date snapshot of the
186 set WRITER [list 0 1 lock exclusive]
188 {0 1 lock exclusive} {0 1 unlock exclusive} \
189 {4 1 lock exclusive} {4 1 unlock exclusive} \
190 {4 1 lock shared} {4 1 unlock shared} \
197 proc tvfs_cb {method args} {
198 set ::filename [lindex $args 0]
202 sqlite3 db test.db -vfs tvfs
203 sqlite3 db2 test.db -vfs tvfs
206 PRAGMA journal_mode = WAL;
210 INSERT INTO t1 VALUES(1);
211 INSERT INTO t1 VALUES(2);
212 INSERT INTO t1 VALUES(3);
213 INSERT INTO t1 VALUES(4);
214 SELECT count(a), sum(a) FROM t1;
218 execsql { SELECT count(a), sum(a) FROM t1 } db2
221 foreach {tn iInsert res0 res1 wal_index_hdr_mod} {
227 7 10 {9 45} {10 55} 5
228 8 11 {10 55} {11 66} 6
229 9 12 {11 66} {12 78} 7
233 do_test wal2-2.$tn.1 {
234 set oldhdr [set_tvfs_hdr $::filename]
235 execsql { INSERT INTO t1 VALUES($iInsert) }
236 execsql { SELECT count(a), sum(a) FROM t1 }
239 do_test wal2-2.$tn.2 {
241 proc tvfs_cb {method args} {
242 set lock [lindex $args 2]
243 lappend ::locks $lock
244 if {$lock == $::WRITER} {
245 set_tvfs_hdr $::filename $::oldhdr
250 if {$::wal_index_hdr_mod >= 0} {
251 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
253 execsql { SELECT count(a), sum(a) FROM t1 } db2
256 do_test wal2-2.$tn.3 {
260 do_test wal2-2.$tn.4 {
262 proc tvfs_cb {method args} {
263 set lock [lindex $args 2]
264 lappend ::locks $lock
268 if {$::wal_index_hdr_mod >= 0} {
269 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
271 execsql { SELECT count(a), sum(a) FROM t1 } db2
277 forcedelete test.db test.db-wal test.db-journal
281 #-------------------------------------------------------------------------
282 # This test case - wal2-3.* - tests the response of the library to an
283 # SQLITE_BUSY when attempting to obtain a READ or RECOVER lock.
285 # wal2-3.0 - 2: SQLITE_BUSY when obtaining a READ lock
286 # wal2-3.3 - 6: SQLITE_BUSY when obtaining a RECOVER lock
289 proc tvfs_cb {method args} {
290 if {$method == "xShmLock"} {
291 if {[info exists ::locked]} { return SQLITE_BUSY }
297 if {$x>3} { unset -nocomplain ::locked }
303 sqlite3 db test.db -vfs tvfs
307 PRAGMA journal_mode = WAL;
309 INSERT INTO t1 VALUES(1);
310 INSERT INTO t1 VALUES(2);
311 INSERT INTO t1 VALUES(3);
312 INSERT INTO t1 VALUES(4);
319 execsql { SELECT count(a), sum(a) FROM t1 }
326 proc tvfs_cb {method args} {
327 if {$method == "xShmLock"} {
328 if {[info exists ::sabotage]} {
329 unset -nocomplain ::sabotage
330 incr_tvfs_hdr [lindex $args 0] 1 1
332 if {[info exists ::locked] && [lindex $args 2] == "RECOVER"} {
340 list [info exists ::sabotage] [info exists ::locked]
343 execsql { SELECT count(a), sum(a) FROM t1 }
346 list [info exists ::sabotage] [info exists ::locked]
350 forcedelete test.db test.db-wal test.db-journal
354 #-------------------------------------------------------------------------
355 # Test that a database connection using a VFS that does not support the
356 # xShmXXX interfaces cannot open a WAL database.
361 PRAGMA auto_vacuum = 0;
362 PRAGMA journal_mode = WAL;
363 CREATE TABLE data(x);
364 INSERT INTO data VALUES('need xShmOpen to see this');
365 PRAGMA wal_checkpoint;
367 # Three pages in the WAL file at this point: One copy of page 1 and two
368 # of the root page for table "data".
372 testvfs tvfs -noshm 1
373 sqlite3 db test.db -vfs tvfs
374 catchsql { SELECT * FROM data }
375 } {1 {unable to open database file}}
379 sqlite3 db test.db -vfs tvfs
380 catchsql { SELECT * FROM data }
381 } {0 {{need xShmOpen to see this}}}
385 #-------------------------------------------------------------------------
386 # Test that if a database connection is forced to run recovery before it
387 # can perform a checkpoint, it does not transition into RECOVER state.
389 # UPDATE: This has now changed. When running a checkpoint, if recovery is
390 # required the client grabs all exclusive locks (just as it would for a
391 # recovery performed as a pre-cursor to a normal database transaction).
393 set expected_locks [list]
394 lappend expected_locks {1 1 lock exclusive} ;# Lock checkpoint
395 lappend expected_locks {0 1 lock exclusive} ;# Lock writer
396 lappend expected_locks {2 1 lock exclusive} ;# Lock recovery
397 lappend expected_locks {4 4 lock exclusive} ;# Lock all aReadMark[]
398 lappend expected_locks {2 1 unlock exclusive} ;# Unlock recovery
399 lappend expected_locks {4 4 unlock exclusive} ;# Unlock all aReadMark[]
400 lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer
401 lappend expected_locks {3 1 lock exclusive} ;# Lock aReadMark[0]
402 lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0]
403 lappend expected_locks {1 1 unlock exclusive} ;# Unlock checkpoint
405 proc tvfs_cb {method args} {
406 set ::shm_file [lindex $args 0]
407 if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
408 return $::tvfs_cb_return
410 set tvfs_cb_return SQLITE_OK
415 sqlite3 db test.db -vfs tvfs
417 PRAGMA journal_mode = WAL;
419 INSERT INTO x VALUES(1);
422 incr_tvfs_hdr $::shm_file 1 1
424 execsql { PRAGMA wal_checkpoint }
430 #-------------------------------------------------------------------------
431 # This block, test cases wal2-6.*, tests the operation of WAL with
432 # "PRAGMA locking_mode=EXCLUSIVE" set.
434 # wal2-6.1.*: Changing to WAL mode before setting locking_mode=exclusive.
436 # wal2-6.2.*: Changing to WAL mode after setting locking_mode=exclusive.
438 # wal2-6.3.*: Changing back to rollback mode from WAL mode after setting
439 # locking_mode=exclusive.
441 # wal2-6.4.*: Check that xShmLock calls are omitted in exclusive locking
446 # wal2-6.6.*: Check that if the xShmLock() to reaquire a WAL read-lock when
447 # exiting exclusive mode fails (i.e. SQLITE_IOERR), then the
448 # connection silently remains in exclusive mode.
451 forcedelete test.db test.db-wal test.db-journal
454 Pragma Journal_Mode = Wal;
458 execsql { PRAGMA lock_status }
459 } {main unlocked temp closed}
462 SELECT * FROM sqlite_master;
463 Pragma Locking_Mode = Exclusive;
467 CREATE TABLE t1(a, b);
468 INSERT INTO t1 VALUES(1, 2);
472 } {main exclusive temp closed}
475 PRAGMA locking_mode = normal;
478 } {normal main exclusive temp closed}
484 } {1 2 main shared temp closed}
487 INSERT INTO t1 VALUES(3, 4);
490 } {main shared temp closed}
494 forcedelete test.db test.db-wal test.db-journal
497 Pragma Locking_Mode = Exclusive;
498 Pragma Journal_Mode = Wal;
501 } {exclusive wal main exclusive temp closed}
505 CREATE TABLE t1(a, b);
506 INSERT INTO t1 VALUES(1, 2);
510 } {main exclusive temp closed}
514 execsql { SELECT * FROM sqlite_master }
515 execsql { PRAGMA LOCKING_MODE = EXCLUSIVE }
522 } {1 2 main shared temp closed}
525 INSERT INTO t1 VALUES(3, 4);
528 } {main exclusive temp closed}
531 PRAGMA locking_mode = NORMAL;
534 } {normal main exclusive temp closed}
537 BEGIN IMMEDIATE; COMMIT;
540 } {main shared temp closed}
543 PRAGMA locking_mode = EXCLUSIVE;
544 BEGIN IMMEDIATE; COMMIT;
545 PRAGMA locking_mode = NORMAL;
551 } {1 2 3 4 main shared temp closed}
554 INSERT INTO t1 VALUES(5, 6);
558 } {1 2 3 4 5 6 main shared temp closed}
562 forcedelete test.db test.db-wal test.db-journal
565 PRAGMA journal_mode = WAL;
566 PRAGMA locking_mode = exclusive;
569 INSERT INTO t1 VALUES('Chico');
570 INSERT INTO t1 VALUES('Harpo');
573 list [file exists test.db-wal] [file exists test.db-journal]
576 execsql { PRAGMA journal_mode = DELETE }
577 file exists test.db-wal
580 execsql { PRAGMA lock_status }
581 } {main exclusive temp closed}
585 INSERT INTO t1 VALUES('Groucho');
588 if {[atomic_batch_write test.db]==0} {
589 do_test wal2-6.3.4.1 {
590 list [file exists test.db-wal] [file exists test.db-journal]
594 execsql { PRAGMA lock_status }
595 } {main exclusive temp closed}
599 if {[atomic_batch_write test.db]==0} {
600 do_test wal2-6.3.6.1 {
601 list [file exists test.db-wal] [file exists test.db-journal]
605 execsql { PRAGMA lock_status }
606 } {main exclusive temp closed}
610 # This test - wal2-6.4.* - uses a single database connection and the
611 # [testvfs] instrumentation to test that xShmLock() is being called
612 # as expected when a WAL database is used with locking_mode=exclusive.
615 forcedelete test.db test.db-wal test.db-journal
616 proc tvfs_cb {method args} {
617 set ::shm_file [lindex $args 0]
618 if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
623 sqlite3 db test.db -vfs tvfs
628 {0 1 lock exclusive} {1 2 lock exclusive} {4 4 lock exclusive}
629 {1 2 unlock exclusive} {4 4 unlock exclusive} {0 1 unlock exclusive}
632 {3 1 lock shared} {3 1 unlock shared}
634 set READMARK0_WRITE {
636 {0 1 lock exclusive} {3 1 unlock shared}
637 {4 1 lock exclusive} {4 1 unlock exclusive} {4 1 lock shared}
638 {0 1 unlock exclusive} {4 1 unlock shared}
641 {4 1 lock exclusive} {4 1 unlock exclusive}
644 {4 1 lock shared} {4 1 unlock shared}
646 set READMARK1_WRITE {
648 {0 1 lock exclusive} {0 1 unlock exclusive}
652 foreach {tn sql res expected_locks} {
654 PRAGMA auto_vacuum = 0;
655 PRAGMA journal_mode = WAL;
658 INSERT INTO t1 VALUES('Leonard');
659 INSERT INTO t1 VALUES('Arthur');
667 # This test should do the READMARK1_SET locking to populate the
668 # aReadMark[1] slot with the current mxFrame value. Followed by
669 # READMARK1_READ to read the database.
678 # aReadMark[1] is already set to mxFrame. So just READMARK1_READ
679 # this time, not READMARK1_SET.
681 SELECT * FROM t1 ORDER BY x
687 PRAGMA locking_mode = exclusive
691 INSERT INTO t1 VALUES('Julius Henry');
693 } {Leonard Arthur {Julius Henry}} {
698 INSERT INTO t1 VALUES('Karl');
700 } {Leonard Arthur {Julius Henry} Karl} { }
703 PRAGMA locking_mode = normal
707 SELECT * FROM t1 ORDER BY x
708 } {Arthur {Julius Henry} Karl Leonard} $READMARK1_READ
710 10 { DELETE FROM t1 } {} $READMARK1_WRITE
721 foreach el [subst $expected_locks] { lappend L $el }
724 foreach sq [split $sql "\n"] {
725 set sq [string trim $sq]
726 if {[string match {#*} $sq]==0} {append S "$sq\n"}
730 do_test wal2-6.4.$tn.1 { execsql $S } $res
731 do_test wal2-6.4.$tn.2 { set ::locks } $L
740 PRAGMA auto_vacuum = 0;
741 PRAGMA journal_mode = wal;
742 PRAGMA locking_mode = exclusive;
743 CREATE TABLE t2(a, b);
744 PRAGMA wal_checkpoint;
745 INSERT INTO t2 VALUES('I', 'II');
748 } {wal exclusive 0 2 2 wal}
751 PRAGMA locking_mode = normal;
752 INSERT INTO t2 VALUES('III', 'IV');
753 PRAGMA locking_mode = exclusive;
756 } {normal exclusive I II III IV}
758 execsql { PRAGMA wal_checkpoint }
762 proc lock_control {method filename handle spec} {
763 foreach {start n op type} $spec break
764 if {$op == "lock"} { return SQLITE_IOERR }
769 T script lock_control
771 sqlite3 db test.db -vfs T
772 execsql { SELECT * FROM sqlite_master }
773 execsql { PRAGMA locking_mode = exclusive }
774 execsql { INSERT INTO t2 VALUES('V', 'VI') }
777 execsql { PRAGMA locking_mode = normal }
779 execsql { INSERT INTO t2 VALUES('VII', 'VIII') }
782 # At this point the connection should still be in exclusive-mode, even
783 # though it tried to exit exclusive-mode when committing the INSERT
784 # statement above. To exit exclusive mode, SQLite has to take a read-lock
785 # on the WAL file using xShmLock(). Since that call failed, it remains
788 sqlite3 db2 test.db -vfs T
789 catchsql { SELECT * FROM t2 } db2
790 } {1 {database is locked}}
794 execsql { INSERT INTO t2 VALUES('IX', 'X') }
797 # This time, we have successfully exited exclusive mode. So the second
798 # connection can read the database.
799 sqlite3 db2 test.db -vfs T
800 catchsql { SELECT * FROM t2 } db2
801 } {0 {I II III IV V VI VII VIII IX X}}
807 #-------------------------------------------------------------------------
808 # Test a theory about the checksum algorithm. Theory was false and this
809 # test did not provoke a bug.
811 forcedelete test.db test.db-wal test.db-journal
815 PRAGMA page_size = 4096;
816 PRAGMA journal_mode = WAL;
817 CREATE TABLE t1(a, b);
822 forcecopy test.db test2.db
823 forcecopy test.db-wal test2.db-wal
824 # The first 32 bytes of the WAL file contain the WAL header. Offset 48
825 # is the first byte of the checksum for the first frame in the WAL.
826 # The following three lines replaces the contents of that byte with
829 if {$newval == [hexio_read test2.db-wal 48 1]} { set newval 00 }
830 hexio_write test2.db-wal 48 $newval
834 execsql { PRAGMA wal_checkpoint } db2
835 execsql { SELECT * FROM sqlite_master } db2
839 forcedelete test.db test.db-wal test.db-journal
843 PRAGMA auto_vacuum=OFF;
844 PRAGMA page_size = 1024;
845 PRAGMA journal_mode = WAL;
847 INSERT INTO t1 VALUES(zeroblob(8188*1020));
849 PRAGMA wal_checkpoint;
852 SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
857 PRAGMA cache_size = 10;
860 INSERT INTO t3 VALUES(randomblob(900));
861 INSERT INTO t3 SELECT randomblob(900) FROM t3;
862 INSERT INTO t2 VALUES('hello');
863 INSERT INTO t3 SELECT randomblob(900) FROM t3;
864 INSERT INTO t3 SELECT randomblob(900) FROM t3;
865 INSERT INTO t3 SELECT randomblob(900) FROM t3;
866 INSERT INTO t3 SELECT randomblob(900) FROM t3;
867 INSERT INTO t3 SELECT randomblob(900) FROM t3;
868 INSERT INTO t3 SELECT randomblob(900) FROM t3;
872 INSERT INTO t2 VALUES('goodbye');
873 INSERT INTO t3 SELECT randomblob(900) FROM t3;
874 INSERT INTO t3 SELECT randomblob(900) FROM t3;
879 execsql { SELECT * FROM t2 }
884 #-------------------------------------------------------------------------
885 # Test that even if the checksums for both are valid, if the two copies
886 # of the wal-index header in the wal-index do not match, the client
887 # runs (or at least tries to run) database recovery.
890 proc get_name {method args} { set ::filename [lindex $args 0] ; tvfs filter {} }
895 forcedelete test.db test.db-wal test.db-journal
897 sqlite3 db test.db -vfs tvfs
899 PRAGMA journal_mode = WAL;
901 INSERT INTO x VALUES('Barton');
902 INSERT INTO x VALUES('Deakin');
905 # Set $wih(1) to the contents of the wal-index header after
906 # the frames associated with the first two rows in table 'x' have
907 # been inserted. Then insert one more row and set $wih(2)
908 # to the new value of the wal-index header.
910 # If the $wih(1) is written into the wal-index before running
911 # a read operation, the client will see only the first two rows. If
912 # $wih(2) is written into the wal-index, the client will see
913 # three rows. If an invalid header is written into the wal-index, then
914 # the client will run recovery and see three rows.
916 set wih(1) [set_tvfs_hdr $::filename]
917 execsql { INSERT INTO x VALUES('Watson') }
918 set wih(2) [set_tvfs_hdr $::filename]
920 sqlite3 db2 test.db -vfs tvfs
921 execsql { SELECT * FROM x } db2
922 } {Barton Deakin Watson}
924 foreach {tn hdr1 hdr2 res} [list \
925 3 $wih(1) $wih(1) {Barton Deakin} \
926 4 $wih(1) $wih(2) {Barton Deakin Watson} \
927 5 $wih(2) $wih(1) {Barton Deakin Watson} \
928 6 $wih(2) $wih(2) {Barton Deakin Watson} \
929 7 $wih(1) $wih(1) {Barton Deakin} \
930 8 {0 0 0 0 0 0 0 0 0 0 0 0} {0 0 0 0 0 0 0 0 0 0 0 0} {Barton Deakin Watson}
933 set_tvfs_hdr $::filename $hdr1 $hdr2
934 execsql { SELECT * FROM x } db2
941 #-------------------------------------------------------------------------
942 # This block of tests - wal2-10.* - focus on the libraries response to
943 # new versions of the wal or wal-index formats.
945 # wal2-10.1.*: Test that the library refuses to "recover" a new WAL
948 # wal2-10.2.*: Test that the library refuses to read or write a database
949 # if the wal-index version is newer than it understands.
951 # At time of writing, the only versions of the wal and wal-index formats
952 # that exist are versions 3007000 (corresponding to SQLite version 3.7.0,
953 # the first version of SQLite to feature wal mode).
955 do_test wal2-10.1.1 {
956 faultsim_delete_and_reopen
958 PRAGMA journal_mode = WAL;
959 CREATE TABLE t1(a, b);
960 PRAGMA wal_checkpoint;
961 INSERT INTO t1 VALUES(1, 2);
962 INSERT INTO t1 VALUES(3, 4);
964 faultsim_save_and_close
966 do_test wal2-10.1.2 {
967 faultsim_restore_and_reopen
968 execsql { SELECT * FROM t1 }
970 do_test wal2-10.1.3 {
971 faultsim_restore_and_reopen
972 set hdr [wal_set_walhdr test.db-wal]
975 do_test wal2-10.1.4 {
977 wal_set_walhdr test.db-wal $hdr
978 catchsql { SELECT * FROM t1 }
979 } {1 {unable to open database file}}
981 testvfs tvfs -default 1
982 do_test wal2-10.2.1 {
983 faultsim_restore_and_reopen
984 execsql { SELECT * FROM t1 }
986 do_test wal2-10.2.2 {
987 set hdr [set_tvfs_hdr $::filename]
990 do_test wal2-10.2.3 {
992 wal_fix_walindex_cksum hdr
993 set_tvfs_hdr $::filename $hdr
994 catchsql { SELECT * FROM t1 }
995 } {1 {unable to open database file}}
999 #-------------------------------------------------------------------------
1000 # This block of tests - wal2-11.* - tests that it is not possible to put
1001 # the library into an infinite loop by presenting it with a corrupt
1002 # hash table (one that appears to contain a single chain of infinite
1005 # wal2-11.1.*: While reading the hash-table.
1007 # wal2-11.2.*: While writing the hash-table.
1009 testvfs tvfs -default 1
1011 faultsim_delete_and_reopen
1013 PRAGMA journal_mode = WAL;
1014 CREATE TABLE t1(a, b, c);
1015 INSERT INTO t1 VALUES(1, 2, 3);
1016 INSERT INTO t1 VALUES(4, 5, 6);
1017 INSERT INTO t1 VALUES(7, 8, 9);
1020 } {wal 1 2 3 4 5 6 7 8 9}
1022 do_test wal2-11.1.1 {
1024 execsql { SELECT name FROM sqlite_master } db2
1027 if {$::tcl_version>=8.5} {
1028 # Set all zeroed slots in the first hash table to invalid values.
1030 set blob [string range [tvfs shm $::filename] 0 16383]
1031 set I [string range [tvfs shm $::filename] 16384 end]
1035 lappend I [expr $p ? $p : 400]
1037 append blob [binary format t* $I]
1038 tvfs shm $::filename $blob
1040 catchsql { INSERT INTO t1 VALUES(10, 11, 12) }
1041 } {1 {database disk image is malformed}}
1043 # Fill up the hash table on the first page of shared memory with 0x55 bytes.
1045 set blob [string range [tvfs shm $::filename] 0 16383]
1046 append blob [string repeat [binary format c 55] 16384]
1047 tvfs shm $::filename $blob
1049 catchsql { SELECT * FROM t1 } db2
1050 } {1 {database disk image is malformed}}
1057 #-------------------------------------------------------------------------
1058 # If a connection is required to create a WAL or SHM file, it creates
1059 # the new files with the same file-system permissions as the database
1060 # file itself. Test this.
1062 if {$::tcl_platform(platform) == "unix"} {
1063 faultsim_delete_and_reopen
1064 # Changed on 2012-02-13: umask is deliberately ignored for -wal files.
1065 #set umask [exec /bin/sh -c umask]
1072 CREATE TABLE tx(y, z);
1073 PRAGMA journal_mode = WAL;
1076 list [file exists test.db-wal] [file exists test.db-shm]
1079 foreach {tn permissions} {
1085 set effective [format %.5o [expr $permissions & ~$umask]]
1086 do_test wal2-12.2.$tn.1 {
1087 file attributes test.db -permissions $permissions
1088 file attributes test.db -permissions
1090 do_test wal2-12.2.$tn.2 {
1091 list [file exists test.db-wal] [file exists test.db-shm]
1093 do_test wal2-12.2.$tn.3 {
1095 execsql { INSERT INTO tx DEFAULT VALUES }
1096 list [file exists test.db-wal] [file exists test.db-shm]
1098 do_test wal2-12.2.$tn.4 {
1099 list [file attr test.db-wal -perm] [file attr test.db-shm -perm]
1100 } [list $effective $effective]
1101 do_test wal2-12.2.$tn.5 {
1103 list [file exists test.db-wal] [file exists test.db-shm]
1108 #-------------------------------------------------------------------------
1109 # Test the libraries response to discovering that one or more of the
1110 # database, wal or shm files cannot be opened, or can only be opened
1113 if {$::tcl_platform(platform) == "unix"} {
1116 foreach f {test.db test.db-wal test.db-shm} {
1117 if {[file exists $f]} {
1118 lappend L [file attr $f -perm]
1126 faultsim_delete_and_reopen
1128 PRAGMA journal_mode = WAL;
1129 CREATE TABLE t1(a, b);
1130 PRAGMA wal_checkpoint;
1131 INSERT INTO t1 VALUES('3.14', '2.72');
1133 do_test wal2-13.1.1 {
1134 list [file exists test.db-shm] [file exists test.db-wal]
1136 faultsim_save_and_close
1138 foreach {tn db_perm wal_perm shm_perm can_open can_read can_write} {
1139 2 00644 00644 00644 1 1 1
1140 3 00644 00400 00644 1 1 0
1141 4 00644 00644 00400 1 1 0
1142 5 00400 00644 00644 1 1 0
1144 7 00644 00000 00644 1 0 0
1145 8 00644 00644 00000 1 0 0
1146 9 00000 00644 00644 0 0 0
1149 do_test wal2-13.$tn.1 {
1150 file attr test.db -perm $db_perm
1151 file attr test.db-wal -perm $wal_perm
1152 file attr test.db-shm -perm $shm_perm
1154 set L [file attr test.db -perm]
1155 lappend L [file attr test.db-wal -perm]
1156 lappend L [file attr test.db-shm -perm]
1157 } [list $db_perm $wal_perm $shm_perm]
1159 # If $can_open is true, then it should be possible to open a database
1160 # handle. Otherwise, if $can_open is 0, attempting to open the db
1161 # handle throws an "unable to open database file" exception.
1164 set r(0) {1 {unable to open database file}}
1165 do_test wal2-13.$tn.2 {
1166 list [catch {sqlite3 db test.db ; set {} ok} msg] $msg
1171 # If $can_read is true, then the client should be able to read from
1172 # the database file. If $can_read is false, attempting to read should
1173 # throw the "unable to open database file" exception.
1175 set a(0) {1 {unable to open database file}}
1176 set a(1) {0 {3.14 2.72}}
1177 do_test wal2-13.$tn.3 {
1178 catchsql { SELECT * FROM t1 }
1181 # Now try to write to the db file. If the client can read but not
1182 # write, then it should throw the familiar "unable to open db file"
1183 # exception. If it can read but not write, the exception should
1184 # be "attempt to write a read only database".
1186 # If the client can read and write, the operation should succeed.
1188 set b(0,0) {1 {unable to open database file}}
1189 set b(1,0) {1 {attempt to write a readonly database}}
1191 do_test wal2-13.$tn.4 {
1192 catchsql { INSERT INTO t1 DEFAULT VALUES }
1193 } $b($can_read,$can_write)
1199 #-------------------------------------------------------------------------
1200 # Test that "PRAGMA checkpoint_fullsync" appears to be working.
1202 foreach {tn sql reslist} {
1203 1 { } {10 0 4 0 6 0}
1204 2 { PRAGMA checkpoint_fullfsync = 1 } {10 6 4 3 6 3}
1205 3 { PRAGMA checkpoint_fullfsync = 0 } {10 0 4 0 6 0}
1207 ifcapable default_ckptfullfsync {
1208 if {[string trim $sql]==""} continue
1210 faultsim_delete_and_reopen
1212 execsql {PRAGMA auto_vacuum = 0; PRAGMA synchronous = FULL;}
1214 do_execsql_test wal2-14.$tn.0 { PRAGMA page_size = 4096 } {}
1215 do_execsql_test wal2-14.$tn.1 { PRAGMA journal_mode = WAL } {wal}
1217 set sqlite_sync_count 0
1218 set sqlite_fullsync_count 0
1220 do_execsql_test wal2-14.$tn.2 {
1221 PRAGMA wal_autocheckpoint = 10;
1222 CREATE TABLE t1(a, b); -- 2 wal syncs
1223 INSERT INTO t1 VALUES(1, 2); -- 2 wal sync
1224 PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1226 INSERT INTO t1 VALUES(3, 4);
1227 INSERT INTO t1 VALUES(5, 6);
1228 COMMIT; -- 2 wal sync
1229 PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1232 do_test wal2-14.$tn.3 {
1233 cond_incr_sync_count 1
1234 list $sqlite_sync_count $sqlite_fullsync_count
1235 } [lrange $reslist 0 1]
1237 set sqlite_sync_count 0
1238 set sqlite_fullsync_count 0
1240 do_test wal2-14.$tn.4 {
1241 execsql { INSERT INTO t1 VALUES(7, zeroblob(12*4096)) }
1242 list $sqlite_sync_count $sqlite_fullsync_count
1243 } [lrange $reslist 2 3]
1245 set sqlite_sync_count 0
1246 set sqlite_fullsync_count 0
1248 do_test wal2-14.$tn.5 {
1249 execsql { PRAGMA wal_autocheckpoint = 1000 }
1250 execsql { INSERT INTO t1 VALUES(9, 10) }
1251 execsql { INSERT INTO t1 VALUES(11, 12) }
1252 execsql { INSERT INTO t1 VALUES(13, 14) }
1254 list $sqlite_sync_count $sqlite_fullsync_count
1255 } [lrange $reslist 4 5]
1260 # PRAGMA checkpoint_fullsync
1262 # PRAGMA synchronous
1264 foreach {tn settings restart_sync commit_sync ckpt_sync} {
1265 1 {0 0 off} {0 0} {0 0} {0 0}
1266 2 {0 0 normal} {1 0} {0 0} {2 0}
1267 3 {0 0 full} {2 0} {1 0} {2 0}
1269 4 {0 1 off} {0 0} {0 0} {0 0}
1270 5 {0 1 normal} {0 1} {0 0} {0 2}
1271 6 {0 1 full} {0 2} {0 1} {0 2}
1273 7 {1 0 off} {0 0} {0 0} {0 0}
1274 8 {1 0 normal} {0 1} {0 0} {0 2}
1275 9 {1 0 full} {1 1} {1 0} {0 2}
1277 10 {1 1 off} {0 0} {0 0} {0 0}
1278 11 {1 1 normal} {0 1} {0 0} {0 2}
1279 12 {1 1 full} {0 2} {0 1} {0 2}
1283 testvfs tvfs -default 1
1286 proc xSyncCb {method file fileid flags} {
1291 do_execsql_test 15.$tn.1 "
1292 PRAGMA page_size = 4096;
1294 PRAGMA wal_autocheckpoint = OFF;
1295 PRAGMA journal_mode = WAL;
1296 PRAGMA checkpoint_fullfsync = [lindex $settings 0];
1297 PRAGMA fullfsync = [lindex $settings 1];
1298 PRAGMA synchronous = [lindex $settings 2];
1304 execsql { INSERT INTO t1 VALUES('abc') }
1305 list $::sync(normal) $::sync(full)
1311 execsql { INSERT INTO t1 VALUES('abc') }
1312 list $::sync(normal) $::sync(full)
1318 execsql { INSERT INTO t1 VALUES('def') }
1319 list $::sync(normal) $::sync(full)
1325 execsql { PRAGMA wal_checkpoint }
1326 list $::sync(normal) $::sync(full)