| blob | 82234330fadcd12baec9acb9eeb7b64f5d30a53e |
1 # 2010 May 5
2 #
3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
5 #
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.
9 #
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.
14 #
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
22 set testprefix wal2
24 ifcapable !wal {finish_test ; return }
26 proc set_tvfs_hdr {file args} {
28 # Set $nHdr to the number of bytes in the wal-index header:
29 set nHdr 48
30 set nInt [expr {$nHdr/4}]
32 if {[llength $args]>2} {
33 error {wrong # args: should be "set_tvfs_hdr fileName ?val1? ?val2?"}
34 }
36 set blob [tvfs shm $file]
38 if {[llength $args]} {
39 set ia [lindex $args 0]
40 set ib $ia
41 if {[llength $args]==2} {
42 set ib [lindex $args 1]
43 }
44 binary scan $blob a[expr $nHdr*2]a* dummy tail
45 set blob [binary format i${nInt}i${nInt}a* $ia $ib $tail]
46 tvfs shm $file $blob
47 }
49 binary scan $blob i${nInt} ints
50 return $ints
51 }
53 proc incr_tvfs_hdr {file idx incrval} {
54 set ints [set_tvfs_hdr $file]
55 set v [lindex $ints $idx]
56 incr v $incrval
57 lset ints $idx $v
58 set_tvfs_hdr $file $ints
59 }
62 #-------------------------------------------------------------------------
63 # Test case wal2-1.*:
64 #
65 # Set up a small database containing a single table. The database is not
66 # checkpointed during the test - all content resides in the log file.
67 #
68 # Two connections are established to the database file - a writer ([db])
69 # and a reader ([db2]). For each of the 8 integer fields in the wal-index
70 # header (6 fields and 2 checksum values), do the following:
71 #
72 # 1. Modify the database using the writer.
73 #
74 # 2. Attempt to read the database using the reader. Before the reader
75 # has a chance to snapshot the wal-index header, increment one
76 # of the the integer fields (so that the reader ends up with a corrupted
77 # header).
78 #
79 # 3. Check that the reader recovers the wal-index and reads the correct
80 # database content.
81 #
82 do_test wal2-1.0 {
83 proc tvfs_cb {method filename args} {
84 set ::filename $filename
85 return SQLITE_OK
86 }
88 testvfs tvfs
89 tvfs script tvfs_cb
90 tvfs filter xShmOpen
92 sqlite3 db test.db -vfs tvfs
93 sqlite3 db2 test.db -vfs tvfs
95 execsql {
96 PRAGMA journal_mode = WAL;
97 CREATE TABLE t1(a);
98 } db2
99 execsql {
100 INSERT INTO t1 VALUES(1);
101 INSERT INTO t1 VALUES(2);
102 INSERT INTO t1 VALUES(3);
103 INSERT INTO t1 VALUES(4);
104 SELECT count(a), sum(a) FROM t1;
105 }
106 } {4 10}
107 do_test wal2-1.1 {
108 execsql { SELECT count(a), sum(a) FROM t1 } db2
109 } {4 10}
111 set RECOVER [list \
112 {0 1 lock exclusive} {1 7 lock exclusive} \
113 {1 7 unlock exclusive} {0 1 unlock exclusive} \
114 ]
115 set READ [list \
116 {4 1 lock exclusive} {4 1 unlock exclusive} \
117 {4 1 lock shared} {4 1 unlock shared} \
118 ]
120 foreach {tn iInsert res wal_index_hdr_mod wal_locks} "
121 2 5 {5 15} 0 {$RECOVER $READ}
122 3 6 {6 21} 1 {$RECOVER $READ}
123 4 7 {7 28} 2 {$RECOVER $READ}
124 5 8 {8 36} 3 {$RECOVER $READ}
125 6 9 {9 45} 4 {$RECOVER $READ}
126 7 10 {10 55} 5 {$RECOVER $READ}
127 8 11 {11 66} 6 {$RECOVER $READ}
128 9 12 {12 78} 7 {$RECOVER $READ}
129 10 13 {13 91} 8 {$RECOVER $READ}
130 11 14 {14 105} 9 {$RECOVER $READ}
131 12 15 {15 120} -1 {$READ}
132 " {
134 do_test wal2-1.$tn.1 {
135 execsql { INSERT INTO t1 VALUES($iInsert) }
136 set ::locks [list]
137 proc tvfs_cb {method args} {
138 lappend ::locks [lindex $args 2]
139 return SQLITE_OK
140 }
141 tvfs filter xShmLock
142 if {$::wal_index_hdr_mod >= 0} {
143 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
144 }
145 execsql { SELECT count(a), sum(a) FROM t1 } db2
146 } $res
148 do_test wal2-1.$tn.2 {
149 set ::locks
150 } $wal_locks
151 }
152 db close
153 db2 close
154 tvfs delete
155 file delete -force test.db test.db-wal test.db-journal
157 #-------------------------------------------------------------------------
158 # This test case is very similar to the previous one, except, after
159 # the reader reads the corrupt wal-index header, but before it has
160 # a chance to re-read it under the cover of the RECOVER lock, the
161 # wal-index header is replaced with a valid, but out-of-date, header.
162 #
163 # Because the header checksum looks Ok, the reader does not run recovery,
164 # it simply drops back to a READ lock and proceeds. But because the
165 # header is out-of-date, the reader reads the out-of-date snapshot.
166 #
167 # After this, the header is corrupted again and the reader is allowed
168 # to run recovery. This time, it sees an up-to-date snapshot of the
169 # database file.
170 #
171 set WRITER [list 0 1 lock exclusive]
172 set LOCKS [list \
173 {0 1 lock exclusive} {0 1 unlock exclusive} \
174 {4 1 lock exclusive} {4 1 unlock exclusive} \
175 {4 1 lock shared} {4 1 unlock shared} \
176 ]
177 do_test wal2-2.0 {
179 testvfs tvfs
180 tvfs script tvfs_cb
181 tvfs filter xShmOpen
182 proc tvfs_cb {method args} {
183 set ::filename [lindex $args 0]
184 return SQLITE_OK
185 }
187 sqlite3 db test.db -vfs tvfs
188 sqlite3 db2 test.db -vfs tvfs
190 execsql {
191 PRAGMA journal_mode = WAL;
192 CREATE TABLE t1(a);
193 } db2
194 execsql {
195 INSERT INTO t1 VALUES(1);
196 INSERT INTO t1 VALUES(2);
197 INSERT INTO t1 VALUES(3);
198 INSERT INTO t1 VALUES(4);
199 SELECT count(a), sum(a) FROM t1;
200 }
201 } {4 10}
202 do_test wal2-2.1 {
203 execsql { SELECT count(a), sum(a) FROM t1 } db2
204 } {4 10}
206 foreach {tn iInsert res0 res1 wal_index_hdr_mod} {
207 2 5 {4 10} {5 15} 0
208 3 6 {5 15} {6 21} 1
209 4 7 {6 21} {7 28} 2
210 5 8 {7 28} {8 36} 3
211 6 9 {8 36} {9 45} 4
212 7 10 {9 45} {10 55} 5
213 8 11 {10 55} {11 66} 6
214 9 12 {11 66} {12 78} 7
215 } {
216 tvfs filter xShmLock
218 do_test wal2-2.$tn.1 {
219 set oldhdr [set_tvfs_hdr $::filename]
220 execsql { INSERT INTO t1 VALUES($iInsert) }
221 execsql { SELECT count(a), sum(a) FROM t1 }
222 } $res1
224 do_test wal2-2.$tn.2 {
225 set ::locks [list]
226 proc tvfs_cb {method args} {
227 set lock [lindex $args 2]
228 lappend ::locks $lock
229 if {$lock == $::WRITER} {
230 set_tvfs_hdr $::filename $::oldhdr
231 }
232 return SQLITE_OK
233 }
235 if {$::wal_index_hdr_mod >= 0} {
236 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
237 }
238 execsql { SELECT count(a), sum(a) FROM t1 } db2
239 } $res0
241 do_test wal2-2.$tn.3 {
242 set ::locks
243 } $LOCKS
245 do_test wal2-2.$tn.4 {
246 set ::locks [list]
247 proc tvfs_cb {method args} {
248 set lock [lindex $args 2]
249 lappend ::locks $lock
250 return SQLITE_OK
251 }
253 if {$::wal_index_hdr_mod >= 0} {
254 incr_tvfs_hdr $::filename $::wal_index_hdr_mod 1
255 }
256 execsql { SELECT count(a), sum(a) FROM t1 } db2
257 } $res1
258 }
259 db close
260 db2 close
261 tvfs delete
262 file delete -force test.db test.db-wal test.db-journal
265 if 0 {
266 #-------------------------------------------------------------------------
267 # This test case - wal2-3.* - tests the response of the library to an
268 # SQLITE_BUSY when attempting to obtain a READ or RECOVER lock.
269 #
270 # wal2-3.0 - 2: SQLITE_BUSY when obtaining a READ lock
271 # wal2-3.3 - 6: SQLITE_BUSY when obtaining a RECOVER lock
272 #
273 do_test wal2-3.0 {
274 proc tvfs_cb {method args} {
275 if {$method == "xShmLock"} {
276 if {[info exists ::locked]} { return SQLITE_BUSY }
277 }
278 return SQLITE_OK
279 }
281 proc busyhandler x {
282 if {$x>3} { unset -nocomplain ::locked }
283 return 0
284 }
286 testvfs tvfs
287 tvfs script tvfs_cb
288 sqlite3 db test.db -vfs tvfs
289 db busy busyhandler
291 execsql {
292 PRAGMA journal_mode = WAL;
293 CREATE TABLE t1(a);
294 INSERT INTO t1 VALUES(1);
295 INSERT INTO t1 VALUES(2);
296 INSERT INTO t1 VALUES(3);
297 INSERT INTO t1 VALUES(4);
298 }
300 set ::locked 1
301 info exists ::locked
302 } {1}
303 do_test wal2-3.1 {
304 execsql { SELECT count(a), sum(a) FROM t1 }
305 } {4 10}
306 do_test wal2-3.2 {
307 info exists ::locked
308 } {0}
310 do_test wal2-3.3 {
311 proc tvfs_cb {method args} {
312 if {$method == "xShmLock"} {
313 if {[info exists ::sabotage]} {
314 unset -nocomplain ::sabotage
315 incr_tvfs_hdr [lindex $args 0] 1 1
316 }
317 if {[info exists ::locked] && [lindex $args 2] == "RECOVER"} {
318 return SQLITE_BUSY
319 }
320 }
321 return SQLITE_OK
322 }
323 set ::sabotage 1
324 set ::locked 1
325 list [info exists ::sabotage] [info exists ::locked]
326 } {1 1}
327 do_test wal2-3.4 {
328 execsql { SELECT count(a), sum(a) FROM t1 }
329 } {4 10}
330 do_test wal2-3.5 {
331 list [info exists ::sabotage] [info exists ::locked]
332 } {0 0}
333 db close
334 tvfs delete
335 file delete -force test.db test.db-wal test.db-journal
337 }
339 #-------------------------------------------------------------------------
340 # Test that a database connection using a VFS that does not support the
341 # xShmXXX interfaces cannot open a WAL database.
342 #
343 do_test wal2-4.1 {
344 sqlite3 db test.db
345 execsql {
346 PRAGMA auto_vacuum = 0;
347 PRAGMA journal_mode = WAL;
348 CREATE TABLE data(x);
349 INSERT INTO data VALUES('need xShmOpen to see this');
350 PRAGMA wal_checkpoint;
351 }
352 } {wal 0 5 5}
353 do_test wal2-4.2 {
354 db close
355 testvfs tvfs -noshm 1
356 sqlite3 db test.db -vfs tvfs
357 catchsql { SELECT * FROM data }
358 } {1 {unable to open database file}}
359 do_test wal2-4.3 {
360 db close
361 testvfs tvfs
362 sqlite3 db test.db -vfs tvfs
363 catchsql { SELECT * FROM data }
364 } {0 {{need xShmOpen to see this}}}
365 db close
366 tvfs delete
368 #-------------------------------------------------------------------------
369 # Test that if a database connection is forced to run recovery before it
370 # can perform a checkpoint, it does not transition into RECOVER state.
371 #
372 # UPDATE: This has now changed. When running a checkpoint, if recovery is
373 # required the client grabs all exclusive locks (just as it would for a
374 # recovery performed as a pre-cursor to a normal database transaction).
375 #
376 set expected_locks [list]
377 lappend expected_locks {1 1 lock exclusive} ;# Lock checkpoint
378 lappend expected_locks {0 1 lock exclusive} ;# Lock writer
379 lappend expected_locks {2 6 lock exclusive} ;# Lock recovery & all aReadMark[]
380 lappend expected_locks {2 6 unlock exclusive} ;# Unlock recovery & aReadMark[]
381 lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer
382 lappend expected_locks {3 1 lock exclusive} ;# Lock aReadMark[0]
383 lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0]
384 lappend expected_locks {1 1 unlock exclusive} ;# Unlock checkpoint
385 do_test wal2-5.1 {
386 proc tvfs_cb {method args} {
387 set ::shm_file [lindex $args 0]
388 if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
389 return $::tvfs_cb_return
390 }
391 set tvfs_cb_return SQLITE_OK
393 testvfs tvfs
394 tvfs script tvfs_cb
396 sqlite3 db test.db -vfs tvfs
397 execsql {
398 PRAGMA journal_mode = WAL;
399 CREATE TABLE x(y);
400 INSERT INTO x VALUES(1);
401 }
403 incr_tvfs_hdr $::shm_file 1 1
404 set ::locks [list]
405 execsql { PRAGMA wal_checkpoint }
406 set ::locks
407 } $expected_locks
408 db close
409 tvfs delete
411 #-------------------------------------------------------------------------
412 # This block, test cases wal2-6.*, tests the operation of WAL with
413 # "PRAGMA locking_mode=EXCLUSIVE" set.
414 #
415 # wal2-6.1.*: Changing to WAL mode before setting locking_mode=exclusive.
416 #
417 # wal2-6.2.*: Changing to WAL mode after setting locking_mode=exclusive.
418 #
419 # wal2-6.3.*: Changing back to rollback mode from WAL mode after setting
420 # locking_mode=exclusive.
421 #
422 # wal2-6.4.*: Check that xShmLock calls are omitted in exclusive locking
423 # mode.
424 #
425 # wal2-6.5.*:
426 #
427 # wal2-6.6.*: Check that if the xShmLock() to reaquire a WAL read-lock when
428 # exiting exclusive mode fails (i.e. SQLITE_IOERR), then the
429 # connection silently remains in exclusive mode.
430 #
431 do_test wal2-6.1.1 {
432 file delete -force test.db test.db-wal test.db-journal
433 sqlite3 db test.db
434 execsql {
435 Pragma Journal_Mode = Wal;
436 }
437 } {wal}
438 do_test wal2-6.1.2 {
439 execsql { PRAGMA lock_status }
440 } {main unlocked temp closed}
441 do_test wal2-6.1.3 {
442 execsql {
443 SELECT * FROM sqlite_master;
444 Pragma Locking_Mode = Exclusive;
445 }
446 execsql {
447 BEGIN;
448 CREATE TABLE t1(a, b);
449 INSERT INTO t1 VALUES(1, 2);
450 COMMIT;
451 PRAGMA lock_status;
452 }
453 } {main exclusive temp closed}
454 do_test wal2-6.1.4 {
455 execsql {
456 PRAGMA locking_mode = normal;
457 PRAGMA lock_status;
458 }
459 } {normal main exclusive temp closed}
460 do_test wal2-6.1.5 {
461 execsql {
462 SELECT * FROM t1;
463 PRAGMA lock_status;
464 }
465 } {1 2 main shared temp closed}
466 do_test wal2-6.1.6 {
467 execsql {
468 INSERT INTO t1 VALUES(3, 4);
469 PRAGMA lock_status;
470 }
471 } {main shared temp closed}
472 db close
474 do_test wal2-6.2.1 {
475 file delete -force test.db test.db-wal test.db-journal
476 sqlite3 db test.db
477 execsql {
478 Pragma Locking_Mode = Exclusive;
479 Pragma Journal_Mode = Wal;
480 Pragma Lock_Status;
481 }
482 } {exclusive wal main exclusive temp closed}
483 do_test wal2-6.2.2 {
484 execsql {
485 BEGIN;
486 CREATE TABLE t1(a, b);
487 INSERT INTO t1 VALUES(1, 2);
488 COMMIT;
489 Pragma loCK_STATus;
490 }
491 } {main exclusive temp closed}
492 do_test wal2-6.2.3 {
493 db close
494 sqlite3 db test.db
495 execsql { SELECT * FROM sqlite_master }
496 execsql { PRAGMA LOCKING_MODE = EXCLUSIVE }
497 } {exclusive}
498 do_test wal2-6.2.4 {
499 execsql {
500 SELECT * FROM t1;
501 pragma lock_status;
502 }
503 } {1 2 main shared temp closed}
504 do_test wal2-6.2.5 {
505 execsql {
506 INSERT INTO t1 VALUES(3, 4);
507 pragma lock_status;
508 }
509 } {main exclusive temp closed}
510 do_test wal2-6.2.6 {
511 execsql {
512 PRAGMA locking_mode = NORMAL;
513 pragma lock_status;
514 }
515 } {normal main exclusive temp closed}
516 do_test wal2-6.2.7 {
517 execsql {
518 BEGIN IMMEDIATE; COMMIT;
519 pragma lock_status;
520 }
521 } {main shared temp closed}
522 do_test wal2-6.2.8 {
523 execsql {
524 PRAGMA locking_mode = EXCLUSIVE;
525 BEGIN IMMEDIATE; COMMIT;
526 PRAGMA locking_mode = NORMAL;
527 }
528 execsql {
529 SELECT * FROM t1;
530 pragma lock_status;
531 }
532 } {1 2 3 4 main shared temp closed}
533 do_test wal2-6.2.9 {
534 execsql {
535 INSERT INTO t1 VALUES(5, 6);
536 SELECT * FROM t1;
537 pragma lock_status;
538 }
539 } {1 2 3 4 5 6 main shared temp closed}
540 db close
542 do_test wal2-6.3.1 {
543 file delete -force test.db test.db-wal test.db-journal
544 sqlite3 db test.db
545 execsql {
546 PRAGMA journal_mode = WAL;
547 PRAGMA locking_mode = exclusive;
548 BEGIN;
549 CREATE TABLE t1(x);
550 INSERT INTO t1 VALUES('Chico');
551 INSERT INTO t1 VALUES('Harpo');
552 COMMIT;
553 }
554 list [file exists test.db-wal] [file exists test.db-journal]
555 } {1 0}
556 do_test wal2-6.3.2 {
557 execsql { PRAGMA journal_mode = DELETE }
558 file exists test.db-wal
559 } {0}
560 do_test wal2-6.3.3 {
561 execsql { PRAGMA lock_status }
562 } {main exclusive temp closed}
563 do_test wal2-6.3.4 {
564 execsql {
565 BEGIN;
566 INSERT INTO t1 VALUES('Groucho');
567 }
568 list [file exists test.db-wal] [file exists test.db-journal]
569 } {0 1}
570 do_test wal2-6.3.5 {
571 execsql { PRAGMA lock_status }
572 } {main exclusive temp closed}
573 do_test wal2-6.3.6 {
574 execsql { COMMIT }
575 list [file exists test.db-wal] [file exists test.db-journal]
576 } {0 1}
577 do_test wal2-6.3.7 {
578 execsql { PRAGMA lock_status }
579 } {main exclusive temp closed}
580 db close
583 # This test - wal2-6.4.* - uses a single database connection and the
584 # [testvfs] instrumentation to test that xShmLock() is being called
585 # as expected when a WAL database is used with locking_mode=exclusive.
586 #
587 do_test wal2-6.4.1 {
588 file delete -force test.db test.db-wal test.db-journal
589 proc tvfs_cb {method args} {
590 set ::shm_file [lindex $args 0]
591 if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
592 return "SQLITE_OK"
593 }
594 testvfs tvfs
595 tvfs script tvfs_cb
596 sqlite3 db test.db -vfs tvfs
597 } {}
599 set RECOVERY {
600 {0 1 lock exclusive} {1 7 lock exclusive}
601 {1 7 unlock exclusive} {0 1 unlock exclusive}
602 }
603 set READMARK0_READ {
604 {3 1 lock shared} {3 1 unlock shared}
605 }
606 set READMARK0_WRITE {
607 {3 1 lock shared}
608 {0 1 lock exclusive} {3 1 unlock shared}
609 {4 1 lock exclusive} {4 1 unlock exclusive} {4 1 lock shared}
610 {0 1 unlock exclusive} {4 1 unlock shared}
611 }
612 set READMARK1_SET {
613 {4 1 lock exclusive} {4 1 unlock exclusive}
614 }
615 set READMARK1_READ {
616 {4 1 lock shared} {4 1 unlock shared}
617 }
618 set READMARK1_WRITE {
619 {4 1 lock shared}
620 {0 1 lock exclusive} {0 1 unlock exclusive}
621 {4 1 unlock shared}
622 }
624 foreach {tn sql res expected_locks} {
625 2 {
626 PRAGMA auto_vacuum = 0;
627 PRAGMA journal_mode = WAL;
628 BEGIN;
629 CREATE TABLE t1(x);
630 INSERT INTO t1 VALUES('Leonard');
631 INSERT INTO t1 VALUES('Arthur');
632 COMMIT;
633 } {wal} {
634 $RECOVERY
635 $READMARK0_WRITE
636 }
638 3 {
639 # This test should do the READMARK1_SET locking to populate the
640 # aReadMark[1] slot with the current mxFrame value. Followed by
641 # READMARK1_READ to read the database.
642 #
643 SELECT * FROM t1
644 } {Leonard Arthur} {
645 $READMARK1_SET
646 $READMARK1_READ
647 }
649 4 {
650 # aReadMark[1] is already set to mxFrame. So just READMARK1_READ
651 # this time, not READMARK1_SET.
652 #
653 SELECT * FROM t1 ORDER BY x
654 } {Arthur Leonard} {
655 $READMARK1_READ
656 }
658 5 {
659 PRAGMA locking_mode = exclusive
660 } {exclusive} { }
662 6 {
663 INSERT INTO t1 VALUES('Julius Henry');
664 SELECT * FROM t1;
665 } {Leonard Arthur {Julius Henry}} {
666 $READMARK1_READ
667 }
669 7 {
670 INSERT INTO t1 VALUES('Karl');
671 SELECT * FROM t1;
672 } {Leonard Arthur {Julius Henry} Karl} { }
674 8 {
675 PRAGMA locking_mode = normal
676 } {normal} { }
678 9 {
679 SELECT * FROM t1 ORDER BY x
680 } {Arthur {Julius Henry} Karl Leonard} $READMARK1_READ
682 10 { DELETE FROM t1 } {} $READMARK1_WRITE
684 11 {
685 SELECT * FROM t1
686 } {} {
687 $READMARK1_SET
688 $READMARK1_READ
689 }
690 } {
692 set L [list]
693 foreach el [subst $expected_locks] { lappend L $el }
695 set S ""
696 foreach sq [split $sql "\n"] {
697 set sq [string trim $sq]
698 if {[string match {#*} $sq]==0} {append S "$sq\n"}
699 }
701 set ::locks [list]
702 do_test wal2-6.4.$tn.1 { execsql $S } $res
703 do_test wal2-6.4.$tn.2 { set ::locks } $L
704 }
706 db close
707 tvfs delete
709 do_test wal2-6.5.1 {
710 sqlite3 db test.db
711 execsql {
712 PRAGMA auto_vacuum = 0;
713 PRAGMA journal_mode = wal;
714 PRAGMA locking_mode = exclusive;
715 CREATE TABLE t2(a, b);
716 PRAGMA wal_checkpoint;
717 INSERT INTO t2 VALUES('I', 'II');
718 PRAGMA journal_mode;
719 }
720 } {wal exclusive 0 3 3 wal}
721 do_test wal2-6.5.2 {
722 execsql {
723 PRAGMA locking_mode = normal;
724 INSERT INTO t2 VALUES('III', 'IV');
725 PRAGMA locking_mode = exclusive;
726 SELECT * FROM t2;
727 }
728 } {normal exclusive I II III IV}
729 do_test wal2-6.5.3 {
730 execsql { PRAGMA wal_checkpoint }
731 } {0 4 4}
732 db close
734 proc lock_control {method filename handle spec} {
735 foreach {start n op type} $spec break
736 if {$op == "lock"} { return SQLITE_IOERR }
737 return SQLITE_OK
738 }
739 do_test wal2-6.6.1 {
740 testvfs T
741 T script lock_control
742 T filter {}
743 sqlite3 db test.db -vfs T
744 execsql { SELECT * FROM sqlite_master }
745 execsql { PRAGMA locking_mode = exclusive }
746 execsql { INSERT INTO t2 VALUES('V', 'VI') }
747 } {}
748 do_test wal2-6.6.2 {
749 execsql { PRAGMA locking_mode = normal }
750 T filter xShmLock
751 execsql { INSERT INTO t2 VALUES('VII', 'VIII') }
752 } {}
753 do_test wal2-6.6.3 {
754 # At this point the connection should still be in exclusive-mode, even
755 # though it tried to exit exclusive-mode when committing the INSERT
756 # statement above. To exit exclusive mode, SQLite has to take a read-lock
757 # on the WAL file using xShmLock(). Since that call failed, it remains
758 # in exclusive mode.
759 #
760 sqlite3 db2 test.db -vfs T
761 catchsql { SELECT * FROM t2 } db2
762 } {1 {database is locked}}
763 do_test wal2-6.6.2 {
764 db2 close
765 T filter {}
766 execsql { INSERT INTO t2 VALUES('IX', 'X') }
767 } {}
768 do_test wal2-6.6.4 {
769 # This time, we have successfully exited exclusive mode. So the second
770 # connection can read the database.
771 sqlite3 db2 test.db -vfs T
772 catchsql { SELECT * FROM t2 } db2
773 } {0 {I II III IV V VI VII VIII IX X}}
775 db close
776 db2 close
777 T delete
779 #-------------------------------------------------------------------------
780 # Test a theory about the checksum algorithm. Theory was false and this
781 # test did not provoke a bug.
782 #
783 file delete -force test.db test.db-wal test.db-journal
784 do_test wal2-7.1.1 {
785 sqlite3 db test.db
786 execsql {
787 PRAGMA page_size = 4096;
788 PRAGMA journal_mode = WAL;
789 CREATE TABLE t1(a, b);
790 }
791 file size test.db
792 } {4096}
793 do_test wal2-7.1.2 {
794 file copy -force test.db test2.db
795 file copy -force test.db-wal test2.db-wal
796 hexio_write test2.db-wal 48 FF
797 } {1}
798 do_test wal2-7.1.3 {
799 sqlite3 db2 test2.db
800 execsql { PRAGMA wal_checkpoint } db2
801 execsql { SELECT * FROM sqlite_master } db2
802 } {}
803 db close
804 db2 close
805 file delete -force test.db test.db-wal test.db-journal
806 do_test wal2-8.1.2 {
807 sqlite3 db test.db
808 execsql {
809 PRAGMA auto_vacuum=OFF;
810 PRAGMA page_size = 1024;
811 PRAGMA journal_mode = WAL;
812 CREATE TABLE t1(x);
813 INSERT INTO t1 VALUES(zeroblob(8188*1020));
814 CREATE TABLE t2(y);
815 PRAGMA wal_checkpoint;
816 }
817 execsql {
818 SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
819 }
820 } {1}
821 do_test wal2-8.1.3 {
822 execsql {
823 PRAGMA cache_size = 10;
824 CREATE TABLE t3(z);
825 BEGIN;
826 INSERT INTO t3 VALUES(randomblob(900));
827 INSERT INTO t3 SELECT randomblob(900) FROM t3;
828 INSERT INTO t2 VALUES('hello');
829 INSERT INTO t3 SELECT randomblob(900) FROM t3;
830 INSERT INTO t3 SELECT randomblob(900) FROM t3;
831 INSERT INTO t3 SELECT randomblob(900) FROM t3;
832 INSERT INTO t3 SELECT randomblob(900) FROM t3;
833 INSERT INTO t3 SELECT randomblob(900) FROM t3;
834 INSERT INTO t3 SELECT randomblob(900) FROM t3;
835 ROLLBACK;
836 }
837 execsql {
838 INSERT INTO t2 VALUES('goodbye');
839 INSERT INTO t3 SELECT randomblob(900) FROM t3;
840 INSERT INTO t3 SELECT randomblob(900) FROM t3;
841 }
842 } {}
843 do_test wal2-8.1.4 {
844 sqlite3 db2 test.db
845 execsql { SELECT * FROM t2 }
846 } {goodbye}
847 db2 close
848 db close
850 #-------------------------------------------------------------------------
851 # Test that even if the checksums for both are valid, if the two copies
852 # of the wal-index header in the wal-index do not match, the client
853 # runs (or at least tries to run) database recovery.
854 #
855 #
856 proc get_name {method args} { set ::filename [lindex $args 0] ; tvfs filter {} }
857 testvfs tvfs
858 tvfs script get_name
859 tvfs filter xShmOpen
861 file delete -force test.db test.db-wal test.db-journal
862 do_test wal2-9.1 {
863 sqlite3 db test.db -vfs tvfs
864 execsql {
865 PRAGMA journal_mode = WAL;
866 CREATE TABLE x(y);
867 INSERT INTO x VALUES('Barton');
868 INSERT INTO x VALUES('Deakin');
869 }
871 # Set $wih(1) to the contents of the wal-index header after
872 # the frames associated with the first two rows in table 'x' have
873 # been inserted. Then insert one more row and set $wih(2)
874 # to the new value of the wal-index header.
875 #
876 # If the $wih(1) is written into the wal-index before running
877 # a read operation, the client will see only the first two rows. If
878 # $wih(2) is written into the wal-index, the client will see
879 # three rows. If an invalid header is written into the wal-index, then
880 # the client will run recovery and see three rows.
881 #
882 set wih(1) [set_tvfs_hdr $::filename]
883 execsql { INSERT INTO x VALUES('Watson') }
884 set wih(2) [set_tvfs_hdr $::filename]
886 sqlite3 db2 test.db -vfs tvfs
887 execsql { SELECT * FROM x } db2
888 } {Barton Deakin Watson}
890 foreach {tn hdr1 hdr2 res} [list \
891 3 $wih(1) $wih(1) {Barton Deakin} \
892 4 $wih(1) $wih(2) {Barton Deakin Watson} \
893 5 $wih(2) $wih(1) {Barton Deakin Watson} \
894 6 $wih(2) $wih(2) {Barton Deakin Watson} \
895 7 $wih(1) $wih(1) {Barton Deakin} \
896 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}
897 ] {
898 do_test wal2-9.$tn {
899 set_tvfs_hdr $::filename $hdr1 $hdr2
900 execsql { SELECT * FROM x } db2
901 } $res
902 }
904 db2 close
905 db close
907 #-------------------------------------------------------------------------
908 # This block of tests - wal2-10.* - focus on the libraries response to
909 # new versions of the wal or wal-index formats.
910 #
911 # wal2-10.1.*: Test that the library refuses to "recover" a new WAL
912 # format.
913 #
914 # wal2-10.2.*: Test that the library refuses to read or write a database
915 # if the wal-index version is newer than it understands.
916 #
917 # At time of writing, the only versions of the wal and wal-index formats
918 # that exist are versions 3007000 (corresponding to SQLite version 3.7.0,
919 # the first version of SQLite to feature wal mode).
920 #
921 do_test wal2-10.1.1 {
922 faultsim_delete_and_reopen
923 execsql {
924 PRAGMA journal_mode = WAL;
925 CREATE TABLE t1(a, b);
926 PRAGMA wal_checkpoint;
927 INSERT INTO t1 VALUES(1, 2);
928 INSERT INTO t1 VALUES(3, 4);
929 }
930 faultsim_save_and_close
931 } {}
932 do_test wal2-10.1.2 {
933 faultsim_restore_and_reopen
934 execsql { SELECT * FROM t1 }
935 } {1 2 3 4}
936 do_test wal2-10.1.3 {
937 faultsim_restore_and_reopen
938 set hdr [wal_set_walhdr test.db-wal]
939 lindex $hdr 1
940 } {3007000}
941 do_test wal2-10.1.4 {
942 lset hdr 1 3007001
943 wal_set_walhdr test.db-wal $hdr
944 catchsql { SELECT * FROM t1 }
945 } {1 {unable to open database file}}
947 testvfs tvfs -default 1
948 do_test wal2-10.2.1 {
949 faultsim_restore_and_reopen
950 execsql { SELECT * FROM t1 }
951 } {1 2 3 4}
952 do_test wal2-10.2.2 {
953 set hdr [set_tvfs_hdr $::filename]
954 lindex $hdr 0
955 } {3007000}
956 do_test wal2-10.2.3 {
957 lset hdr 0 3007001
958 wal_fix_walindex_cksum hdr
959 set_tvfs_hdr $::filename $hdr
960 catchsql { SELECT * FROM t1 }
961 } {1 {unable to open database file}}
962 db close
963 tvfs delete
965 #-------------------------------------------------------------------------
966 # This block of tests - wal2-11.* - tests that it is not possible to put
967 # the library into an infinite loop by presenting it with a corrupt
968 # hash table (one that appears to contain a single chain of infinite
969 # length).
970 #
971 # wal2-11.1.*: While reading the hash-table.
972 #
973 # wal2-11.2.*: While writing the hash-table.
974 #
975 testvfs tvfs -default 1
976 do_test wal2-11.0 {
977 faultsim_delete_and_reopen
978 execsql {
979 PRAGMA journal_mode = WAL;
980 CREATE TABLE t1(a, b, c);
981 INSERT INTO t1 VALUES(1, 2, 3);
982 INSERT INTO t1 VALUES(4, 5, 6);
983 INSERT INTO t1 VALUES(7, 8, 9);
984 SELECT * FROM t1;
985 }
986 } {wal 1 2 3 4 5 6 7 8 9}
988 do_test wal2-11.1.1 {
989 sqlite3 db2 test.db
990 execsql { SELECT name FROM sqlite_master } db2
991 } {t1}
993 if {$::tcl_version>=8.5} {
994 # Set all zeroed slots in the first hash table to invalid values.
995 #
996 set blob [string range [tvfs shm $::filename] 0 16383]
997 set I [string range [tvfs shm $::filename] 16384 end]
998 binary scan $I t* L
999 set I [list]
1000 foreach p $L {
1001 lappend I [expr $p ? $p : 400]
1002 }
1003 append blob [binary format t* $I]
1004 tvfs shm $::filename $blob
1005 do_test wal2-11.2 {
1006 catchsql { INSERT INTO t1 VALUES(10, 11, 12) }
1007 } {1 {database disk image is malformed}}
1009 # Fill up the hash table on the first page of shared memory with 0x55 bytes.
1010 #
1011 set blob [string range [tvfs shm $::filename] 0 16383]
1012 append blob [string repeat [binary format c 55] 16384]
1013 tvfs shm $::filename $blob
1014 do_test wal2-11.3 {
1015 catchsql { SELECT * FROM t1 } db2
1016 } {1 {database disk image is malformed}}
1017 }
1019 db close
1020 db2 close
1021 tvfs delete
1023 #-------------------------------------------------------------------------
1024 # If a connection is required to create a WAL or SHM file, it creates
1025 # the new files with the same file-system permissions as the database
1026 # file itself. Test this.
1027 #
1028 if {$::tcl_platform(platform) == "unix"} {
1029 faultsim_delete_and_reopen
1030 set umask [exec /bin/sh -c umask]
1032 do_test wal2-12.1 {
1033 sqlite3 db test.db
1034 execsql {
1035 CREATE TABLE tx(y, z);
1036 PRAGMA journal_mode = WAL;
1037 }
1038 db close
1039 list [file exists test.db-wal] [file exists test.db-shm]
1040 } {0 0}
1042 foreach {tn permissions} {
1043 1 00644
1044 2 00666
1045 3 00600
1046 4 00755
1047 } {
1048 set effective [format %.5o [expr $permissions & ~$umask]]
1049 do_test wal2-12.2.$tn.1 {
1050 file attributes test.db -permissions $permissions
1051 file attributes test.db -permissions
1052 } $permissions
1053 do_test wal2-12.2.$tn.2 {
1054 list [file exists test.db-wal] [file exists test.db-shm]
1055 } {0 0}
1056 do_test wal2-12.2.$tn.3 {
1057 sqlite3 db test.db
1058 execsql { INSERT INTO tx DEFAULT VALUES }
1059 list [file exists test.db-wal] [file exists test.db-shm]
1060 } {1 1}
1061 do_test wal2-12.2.$tn.4 {
1062 list [file attr test.db-wal -perm] [file attr test.db-shm -perm]
1063 } [list $effective $effective]
1064 do_test wal2-12.2.$tn.5 {
1065 db close
1066 list [file exists test.db-wal] [file exists test.db-shm]
1067 } {0 0}
1068 }
1069 }
1071 #-------------------------------------------------------------------------
1072 # Test the libraries response to discovering that one or more of the
1073 # database, wal or shm files cannot be opened, or can only be opened
1074 # read-only.
1075 #
1076 if {$::tcl_platform(platform) == "unix"} {
1077 proc perm {} {
1078 set L [list]
1079 foreach f {test.db test.db-wal test.db-shm} {
1080 if {[file exists $f]} {
1081 lappend L [file attr $f -perm]
1082 } else {
1083 lappend L {}
1084 }
1085 }
1086 set L
1087 }
1089 faultsim_delete_and_reopen
1090 execsql {
1091 PRAGMA journal_mode = WAL;
1092 CREATE TABLE t1(a, b);
1093 PRAGMA wal_checkpoint;
1094 INSERT INTO t1 VALUES('3.14', '2.72');
1095 }
1096 do_test wal2-13.1.1 {
1097 list [file exists test.db-shm] [file exists test.db-wal]
1098 } {1 1}
1099 faultsim_save_and_close
1101 foreach {tn db_perm wal_perm shm_perm can_open can_read can_write} {
1102 2 00644 00644 00644 1 1 1
1103 3 00644 00400 00644 1 1 0
1104 4 00644 00644 00400 1 0 0
1105 5 00400 00644 00644 1 1 0
1107 7 00644 00000 00644 1 0 0
1108 8 00644 00644 00000 1 0 0
1109 9 00000 00644 00644 0 0 0
1110 } {
1111 faultsim_restore
1112 do_test wal2-13.$tn.1 {
1113 file attr test.db -perm $db_perm
1114 file attr test.db-wal -perm $wal_perm
1115 file attr test.db-shm -perm $shm_perm
1117 set L [file attr test.db -perm]
1118 lappend L [file attr test.db-wal -perm]
1119 lappend L [file attr test.db-shm -perm]
1120 } [list $db_perm $wal_perm $shm_perm]
1122 # If $can_open is true, then it should be possible to open a database
1123 # handle. Otherwise, if $can_open is 0, attempting to open the db
1124 # handle throws an "unable to open database file" exception.
1125 #
1126 set r(1) {0 ok}
1127 set r(0) {1 {unable to open database file}}
1128 do_test wal2-13.$tn.2 {
1129 list [catch {sqlite3 db test.db ; set {} ok} msg] $msg
1130 } $r($can_open)
1132 if {$can_open} {
1134 # If $can_read is true, then the client should be able to read from
1135 # the database file. If $can_read is false, attempting to read should
1136 # throw the "unable to open database file" exception.
1137 #
1138 set a(0) {1 {unable to open database file}}
1139 set a(1) {0 {3.14 2.72}}
1140 do_test wal2-13.$tn.3 {
1141 catchsql { SELECT * FROM t1 }
1142 } $a($can_read)
1144 # Now try to write to the db file. If the client can read but not
1145 # write, then it should throw the familiar "unable to open db file"
1146 # exception. If it can read but not write, the exception should
1147 # be "attempt to write a read only database".
1148 #
1149 # If the client can read and write, the operation should succeed.
1150 #
1151 set b(0,0) {1 {unable to open database file}}
1152 set b(1,0) {1 {attempt to write a readonly database}}
1153 set b(1,1) {0 {}}
1154 do_test wal2-13.$tn.4 {
1155 catchsql { INSERT INTO t1 DEFAULT VALUES }
1156 } $b($can_read,$can_write)
1157 }
1158 catch { db close }
1159 }
1160 }
1162 #-------------------------------------------------------------------------
1163 # Test that "PRAGMA checkpoint_fullsync" appears to be working.
1164 #
1165 foreach {tn sql reslist} {
1166 1 { } {8 0 3 0 5 0}
1167 2 { PRAGMA checkpoint_fullfsync = 1 } {8 4 3 2 5 2}
1168 3 { PRAGMA checkpoint_fullfsync = 0 } {8 0 3 0 5 0}
1169 } {
1170 faultsim_delete_and_reopen
1172 execsql {PRAGMA auto_vacuum = 0}
1173 execsql $sql
1174 do_execsql_test wal2-14.$tn.1 { PRAGMA journal_mode = WAL } {wal}
1176 set sqlite_sync_count 0
1177 set sqlite_fullsync_count 0
1179 do_execsql_test wal2-14.$tn.2 {
1180 PRAGMA wal_autocheckpoint = 10;
1181 CREATE TABLE t1(a, b); -- 2 wal syncs
1182 INSERT INTO t1 VALUES(1, 2); -- 1 wal sync
1183 PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1184 BEGIN;
1185 INSERT INTO t1 VALUES(3, 4);
1186 INSERT INTO t1 VALUES(5, 6);
1187 COMMIT; -- 1 wal sync
1188 PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1189 } {10 0 5 5 0 2 2}
1191 do_test wal2-14.$tn.3 {
1192 list $sqlite_sync_count $sqlite_fullsync_count
1193 } [lrange $reslist 0 1]
1195 set sqlite_sync_count 0
1196 set sqlite_fullsync_count 0
1198 do_test wal2-14.$tn.4 {
1199 execsql { INSERT INTO t1 VALUES(7, zeroblob(12*4096)) }
1200 list $sqlite_sync_count $sqlite_fullsync_count
1201 } [lrange $reslist 2 3]
1203 set sqlite_sync_count 0
1204 set sqlite_fullsync_count 0
1206 do_test wal2-14.$tn.5 {
1207 execsql { PRAGMA wal_autocheckpoint = 1000 }
1208 execsql { INSERT INTO t1 VALUES(9, 10) }
1209 execsql { INSERT INTO t1 VALUES(11, 12) }
1210 execsql { INSERT INTO t1 VALUES(13, 14) }
1211 db close
1212 list $sqlite_sync_count $sqlite_fullsync_count
1213 } [lrange $reslist 4 5]
1214 }
1216 catch { db close }
1218 # PRAGMA checkpoint_fullsync
1219 # PRAGMA fullfsync
1220 # PRAGMA synchronous
1221 #
1222 foreach {tn settings commit_sync ckpt_sync} {
1223 1 {0 0 off} {0 0} {0 0}
1224 2 {0 0 normal} {0 0} {2 0}
1225 3 {0 0 full} {1 0} {2 0}
1227 4 {0 1 off} {0 0} {0 0}
1228 5 {0 1 normal} {0 0} {0 2}
1229 6 {0 1 full} {0 1} {0 2}
1231 7 {1 0 off} {0 0} {0 0}
1232 8 {1 0 normal} {0 0} {0 2}
1233 9 {1 0 full} {1 0} {0 2}
1235 10 {1 1 off} {0 0} {0 0}
1236 11 {1 1 normal} {0 0} {0 2}
1237 12 {1 1 full} {0 1} {0 2}
1238 } {
1239 forcedelete test.db
1241 testvfs tvfs -default 1
1242 tvfs filter xSync
1243 tvfs script xSyncCb
1244 proc xSyncCb {method file fileid flags} {
1245 incr ::sync($flags)
1246 }
1248 sqlite3 db test.db
1249 do_execsql_test 15.$tn.1 "
1250 CREATE TABLE t1(x);
1251 PRAGMA journal_mode = WAL;
1252 PRAGMA checkpoint_fullfsync = [lindex $settings 0];
1253 PRAGMA fullfsync = [lindex $settings 1];
1254 PRAGMA synchronous = [lindex $settings 2];
1255 " {wal}
1257 do_test 15.$tn.2 {
1258 set sync(normal) 0
1259 set sync(full) 0
1260 execsql { INSERT INTO t1 VALUES('abc') }
1261 list $::sync(normal) $::sync(full)
1262 } $commit_sync
1264 do_test 15.$tn.3 {
1265 set sync(normal) 0
1266 set sync(full) 0
1267 execsql { INSERT INTO t1 VALUES('def') }
1268 list $::sync(normal) $::sync(full)
1269 } $commit_sync
1271 do_test 15.$tn.4 {
1272 set sync(normal) 0
1273 set sync(full) 0
1274 execsql { PRAGMA wal_checkpoint }
1275 list $::sync(normal) $::sync(full)
1276 } $ckpt_sync
1278 db close
1279 tvfs delete
1280 }
1284 finish_test