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