Debian Codegraph @

test/wal2.test

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