wasCSharpSQLite – Blame information for rev 1

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