Annotation of embedaddon/sqlite3/test/wal2.test, revision 1.1.1.1
1.1 misho 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: forcedelete 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: forcedelete 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: forcedelete 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: # Three pages in the WAL file at this point: One copy of page 1 and two
365: # of the root page for table "data".
366: } {wal 0 3 3}
367: do_test wal2-4.2 {
368: db close
369: testvfs tvfs -noshm 1
370: sqlite3 db test.db -vfs tvfs
371: catchsql { SELECT * FROM data }
372: } {1 {unable to open database file}}
373: do_test wal2-4.3 {
374: db close
375: testvfs tvfs
376: sqlite3 db test.db -vfs tvfs
377: catchsql { SELECT * FROM data }
378: } {0 {{need xShmOpen to see this}}}
379: db close
380: tvfs delete
381:
382: #-------------------------------------------------------------------------
383: # Test that if a database connection is forced to run recovery before it
384: # can perform a checkpoint, it does not transition into RECOVER state.
385: #
386: # UPDATE: This has now changed. When running a checkpoint, if recovery is
387: # required the client grabs all exclusive locks (just as it would for a
388: # recovery performed as a pre-cursor to a normal database transaction).
389: #
390: set expected_locks [list]
391: lappend expected_locks {1 1 lock exclusive} ;# Lock checkpoint
392: lappend expected_locks {0 1 lock exclusive} ;# Lock writer
393: lappend expected_locks {2 6 lock exclusive} ;# Lock recovery & all aReadMark[]
394: lappend expected_locks {2 6 unlock exclusive} ;# Unlock recovery & aReadMark[]
395: lappend expected_locks {0 1 unlock exclusive} ;# Unlock writer
396: lappend expected_locks {3 1 lock exclusive} ;# Lock aReadMark[0]
397: lappend expected_locks {3 1 unlock exclusive} ;# Unlock aReadMark[0]
398: lappend expected_locks {1 1 unlock exclusive} ;# Unlock checkpoint
399: do_test wal2-5.1 {
400: proc tvfs_cb {method args} {
401: set ::shm_file [lindex $args 0]
402: if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
403: return $::tvfs_cb_return
404: }
405: set tvfs_cb_return SQLITE_OK
406:
407: testvfs tvfs
408: tvfs script tvfs_cb
409:
410: sqlite3 db test.db -vfs tvfs
411: execsql {
412: PRAGMA journal_mode = WAL;
413: CREATE TABLE x(y);
414: INSERT INTO x VALUES(1);
415: }
416:
417: incr_tvfs_hdr $::shm_file 1 1
418: set ::locks [list]
419: execsql { PRAGMA wal_checkpoint }
420: set ::locks
421: } $expected_locks
422: db close
423: tvfs delete
424:
425: #-------------------------------------------------------------------------
426: # This block, test cases wal2-6.*, tests the operation of WAL with
427: # "PRAGMA locking_mode=EXCLUSIVE" set.
428: #
429: # wal2-6.1.*: Changing to WAL mode before setting locking_mode=exclusive.
430: #
431: # wal2-6.2.*: Changing to WAL mode after setting locking_mode=exclusive.
432: #
433: # wal2-6.3.*: Changing back to rollback mode from WAL mode after setting
434: # locking_mode=exclusive.
435: #
436: # wal2-6.4.*: Check that xShmLock calls are omitted in exclusive locking
437: # mode.
438: #
439: # wal2-6.5.*:
440: #
441: # wal2-6.6.*: Check that if the xShmLock() to reaquire a WAL read-lock when
442: # exiting exclusive mode fails (i.e. SQLITE_IOERR), then the
443: # connection silently remains in exclusive mode.
444: #
445: do_test wal2-6.1.1 {
446: forcedelete test.db test.db-wal test.db-journal
447: sqlite3 db test.db
448: execsql {
449: Pragma Journal_Mode = Wal;
450: }
451: } {wal}
452: do_test wal2-6.1.2 {
453: execsql { PRAGMA lock_status }
454: } {main unlocked temp closed}
455: do_test wal2-6.1.3 {
456: execsql {
457: SELECT * FROM sqlite_master;
458: Pragma Locking_Mode = Exclusive;
459: }
460: execsql {
461: BEGIN;
462: CREATE TABLE t1(a, b);
463: INSERT INTO t1 VALUES(1, 2);
464: COMMIT;
465: PRAGMA lock_status;
466: }
467: } {main exclusive temp closed}
468: do_test wal2-6.1.4 {
469: execsql {
470: PRAGMA locking_mode = normal;
471: PRAGMA lock_status;
472: }
473: } {normal main exclusive temp closed}
474: do_test wal2-6.1.5 {
475: execsql {
476: SELECT * FROM t1;
477: PRAGMA lock_status;
478: }
479: } {1 2 main shared temp closed}
480: do_test wal2-6.1.6 {
481: execsql {
482: INSERT INTO t1 VALUES(3, 4);
483: PRAGMA lock_status;
484: }
485: } {main shared temp closed}
486: db close
487:
488: do_test wal2-6.2.1 {
489: forcedelete test.db test.db-wal test.db-journal
490: sqlite3 db test.db
491: execsql {
492: Pragma Locking_Mode = Exclusive;
493: Pragma Journal_Mode = Wal;
494: Pragma Lock_Status;
495: }
496: } {exclusive wal main exclusive temp closed}
497: do_test wal2-6.2.2 {
498: execsql {
499: BEGIN;
500: CREATE TABLE t1(a, b);
501: INSERT INTO t1 VALUES(1, 2);
502: COMMIT;
503: Pragma loCK_STATus;
504: }
505: } {main exclusive temp closed}
506: do_test wal2-6.2.3 {
507: db close
508: sqlite3 db test.db
509: execsql { SELECT * FROM sqlite_master }
510: execsql { PRAGMA LOCKING_MODE = EXCLUSIVE }
511: } {exclusive}
512: do_test wal2-6.2.4 {
513: execsql {
514: SELECT * FROM t1;
515: pragma lock_status;
516: }
517: } {1 2 main shared temp closed}
518: do_test wal2-6.2.5 {
519: execsql {
520: INSERT INTO t1 VALUES(3, 4);
521: pragma lock_status;
522: }
523: } {main exclusive temp closed}
524: do_test wal2-6.2.6 {
525: execsql {
526: PRAGMA locking_mode = NORMAL;
527: pragma lock_status;
528: }
529: } {normal main exclusive temp closed}
530: do_test wal2-6.2.7 {
531: execsql {
532: BEGIN IMMEDIATE; COMMIT;
533: pragma lock_status;
534: }
535: } {main shared temp closed}
536: do_test wal2-6.2.8 {
537: execsql {
538: PRAGMA locking_mode = EXCLUSIVE;
539: BEGIN IMMEDIATE; COMMIT;
540: PRAGMA locking_mode = NORMAL;
541: }
542: execsql {
543: SELECT * FROM t1;
544: pragma lock_status;
545: }
546: } {1 2 3 4 main shared temp closed}
547: do_test wal2-6.2.9 {
548: execsql {
549: INSERT INTO t1 VALUES(5, 6);
550: SELECT * FROM t1;
551: pragma lock_status;
552: }
553: } {1 2 3 4 5 6 main shared temp closed}
554: db close
555:
556: do_test wal2-6.3.1 {
557: forcedelete test.db test.db-wal test.db-journal
558: sqlite3 db test.db
559: execsql {
560: PRAGMA journal_mode = WAL;
561: PRAGMA locking_mode = exclusive;
562: BEGIN;
563: CREATE TABLE t1(x);
564: INSERT INTO t1 VALUES('Chico');
565: INSERT INTO t1 VALUES('Harpo');
566: COMMIT;
567: }
568: list [file exists test.db-wal] [file exists test.db-journal]
569: } {1 0}
570: do_test wal2-6.3.2 {
571: execsql { PRAGMA journal_mode = DELETE }
572: file exists test.db-wal
573: } {0}
574: do_test wal2-6.3.3 {
575: execsql { PRAGMA lock_status }
576: } {main exclusive temp closed}
577: do_test wal2-6.3.4 {
578: execsql {
579: BEGIN;
580: INSERT INTO t1 VALUES('Groucho');
581: }
582: list [file exists test.db-wal] [file exists test.db-journal]
583: } {0 1}
584: do_test wal2-6.3.5 {
585: execsql { PRAGMA lock_status }
586: } {main exclusive temp closed}
587: do_test wal2-6.3.6 {
588: execsql { COMMIT }
589: list [file exists test.db-wal] [file exists test.db-journal]
590: } {0 1}
591: do_test wal2-6.3.7 {
592: execsql { PRAGMA lock_status }
593: } {main exclusive temp closed}
594: db close
595:
596:
597: # This test - wal2-6.4.* - uses a single database connection and the
598: # [testvfs] instrumentation to test that xShmLock() is being called
599: # as expected when a WAL database is used with locking_mode=exclusive.
600: #
601: do_test wal2-6.4.1 {
602: forcedelete test.db test.db-wal test.db-journal
603: proc tvfs_cb {method args} {
604: set ::shm_file [lindex $args 0]
605: if {$method == "xShmLock"} { lappend ::locks [lindex $args 2] }
606: return "SQLITE_OK"
607: }
608: testvfs tvfs
609: tvfs script tvfs_cb
610: sqlite3 db test.db -vfs tvfs
611: set {} {}
612: } {}
613:
614: set RECOVERY {
615: {0 1 lock exclusive} {1 7 lock exclusive}
616: {1 7 unlock exclusive} {0 1 unlock exclusive}
617: }
618: set READMARK0_READ {
619: {3 1 lock shared} {3 1 unlock shared}
620: }
621: set READMARK0_WRITE {
622: {3 1 lock shared}
623: {0 1 lock exclusive} {3 1 unlock shared}
624: {4 1 lock exclusive} {4 1 unlock exclusive} {4 1 lock shared}
625: {0 1 unlock exclusive} {4 1 unlock shared}
626: }
627: set READMARK1_SET {
628: {4 1 lock exclusive} {4 1 unlock exclusive}
629: }
630: set READMARK1_READ {
631: {4 1 lock shared} {4 1 unlock shared}
632: }
633: set READMARK1_WRITE {
634: {4 1 lock shared}
635: {0 1 lock exclusive} {0 1 unlock exclusive}
636: {4 1 unlock shared}
637: }
638:
639: foreach {tn sql res expected_locks} {
640: 2 {
641: PRAGMA auto_vacuum = 0;
642: PRAGMA journal_mode = WAL;
643: BEGIN;
644: CREATE TABLE t1(x);
645: INSERT INTO t1 VALUES('Leonard');
646: INSERT INTO t1 VALUES('Arthur');
647: COMMIT;
648: } {wal} {
649: $RECOVERY
650: $READMARK0_WRITE
651: }
652:
653: 3 {
654: # This test should do the READMARK1_SET locking to populate the
655: # aReadMark[1] slot with the current mxFrame value. Followed by
656: # READMARK1_READ to read the database.
657: #
658: SELECT * FROM t1
659: } {Leonard Arthur} {
660: $READMARK1_SET
661: $READMARK1_READ
662: }
663:
664: 4 {
665: # aReadMark[1] is already set to mxFrame. So just READMARK1_READ
666: # this time, not READMARK1_SET.
667: #
668: SELECT * FROM t1 ORDER BY x
669: } {Arthur Leonard} {
670: $READMARK1_READ
671: }
672:
673: 5 {
674: PRAGMA locking_mode = exclusive
675: } {exclusive} { }
676:
677: 6 {
678: INSERT INTO t1 VALUES('Julius Henry');
679: SELECT * FROM t1;
680: } {Leonard Arthur {Julius Henry}} {
681: $READMARK1_READ
682: }
683:
684: 7 {
685: INSERT INTO t1 VALUES('Karl');
686: SELECT * FROM t1;
687: } {Leonard Arthur {Julius Henry} Karl} { }
688:
689: 8 {
690: PRAGMA locking_mode = normal
691: } {normal} { }
692:
693: 9 {
694: SELECT * FROM t1 ORDER BY x
695: } {Arthur {Julius Henry} Karl Leonard} $READMARK1_READ
696:
697: 10 { DELETE FROM t1 } {} $READMARK1_WRITE
698:
699: 11 {
700: SELECT * FROM t1
701: } {} {
702: $READMARK1_SET
703: $READMARK1_READ
704: }
705: } {
706:
707: set L [list]
708: foreach el [subst $expected_locks] { lappend L $el }
709:
710: set S ""
711: foreach sq [split $sql "\n"] {
712: set sq [string trim $sq]
713: if {[string match {#*} $sq]==0} {append S "$sq\n"}
714: }
715:
716: set ::locks [list]
717: do_test wal2-6.4.$tn.1 { execsql $S } $res
718: do_test wal2-6.4.$tn.2 { set ::locks } $L
719: }
720:
721: db close
722: tvfs delete
723:
724: do_test wal2-6.5.1 {
725: sqlite3 db test.db
726: execsql {
727: PRAGMA auto_vacuum = 0;
728: PRAGMA journal_mode = wal;
729: PRAGMA locking_mode = exclusive;
730: CREATE TABLE t2(a, b);
731: PRAGMA wal_checkpoint;
732: INSERT INTO t2 VALUES('I', 'II');
733: PRAGMA journal_mode;
734: }
735: } {wal exclusive 0 2 2 wal}
736: do_test wal2-6.5.2 {
737: execsql {
738: PRAGMA locking_mode = normal;
739: INSERT INTO t2 VALUES('III', 'IV');
740: PRAGMA locking_mode = exclusive;
741: SELECT * FROM t2;
742: }
743: } {normal exclusive I II III IV}
744: do_test wal2-6.5.3 {
745: execsql { PRAGMA wal_checkpoint }
746: } {0 2 2}
747: db close
748:
749: proc lock_control {method filename handle spec} {
750: foreach {start n op type} $spec break
751: if {$op == "lock"} { return SQLITE_IOERR }
752: return SQLITE_OK
753: }
754: do_test wal2-6.6.1 {
755: testvfs T
756: T script lock_control
757: T filter {}
758: sqlite3 db test.db -vfs T
759: execsql { SELECT * FROM sqlite_master }
760: execsql { PRAGMA locking_mode = exclusive }
761: execsql { INSERT INTO t2 VALUES('V', 'VI') }
762: } {}
763: do_test wal2-6.6.2 {
764: execsql { PRAGMA locking_mode = normal }
765: T filter xShmLock
766: execsql { INSERT INTO t2 VALUES('VII', 'VIII') }
767: } {}
768: do_test wal2-6.6.3 {
769: # At this point the connection should still be in exclusive-mode, even
770: # though it tried to exit exclusive-mode when committing the INSERT
771: # statement above. To exit exclusive mode, SQLite has to take a read-lock
772: # on the WAL file using xShmLock(). Since that call failed, it remains
773: # in exclusive mode.
774: #
775: sqlite3 db2 test.db -vfs T
776: catchsql { SELECT * FROM t2 } db2
777: } {1 {database is locked}}
778: do_test wal2-6.6.2 {
779: db2 close
780: T filter {}
781: execsql { INSERT INTO t2 VALUES('IX', 'X') }
782: } {}
783: do_test wal2-6.6.4 {
784: # This time, we have successfully exited exclusive mode. So the second
785: # connection can read the database.
786: sqlite3 db2 test.db -vfs T
787: catchsql { SELECT * FROM t2 } db2
788: } {0 {I II III IV V VI VII VIII IX X}}
789:
790: db close
791: db2 close
792: T delete
793:
794: #-------------------------------------------------------------------------
795: # Test a theory about the checksum algorithm. Theory was false and this
796: # test did not provoke a bug.
797: #
798: forcedelete test.db test.db-wal test.db-journal
799: do_test wal2-7.1.1 {
800: sqlite3 db test.db
801: execsql {
802: PRAGMA page_size = 4096;
803: PRAGMA journal_mode = WAL;
804: CREATE TABLE t1(a, b);
805: }
806: file size test.db
807: } {4096}
808: do_test wal2-7.1.2 {
809: forcecopy test.db test2.db
810: forcecopy test.db-wal test2.db-wal
811: hexio_write test2.db-wal 48 FF
812: } {1}
813: do_test wal2-7.1.3 {
814: sqlite3 db2 test2.db
815: execsql { PRAGMA wal_checkpoint } db2
816: execsql { SELECT * FROM sqlite_master } db2
817: } {}
818: db close
819: db2 close
820: forcedelete test.db test.db-wal test.db-journal
821: do_test wal2-8.1.2 {
822: sqlite3 db test.db
823: execsql {
824: PRAGMA auto_vacuum=OFF;
825: PRAGMA page_size = 1024;
826: PRAGMA journal_mode = WAL;
827: CREATE TABLE t1(x);
828: INSERT INTO t1 VALUES(zeroblob(8188*1020));
829: CREATE TABLE t2(y);
830: PRAGMA wal_checkpoint;
831: }
832: execsql {
833: SELECT rootpage>=8192 FROM sqlite_master WHERE tbl_name = 't2';
834: }
835: } {1}
836: do_test wal2-8.1.3 {
837: execsql {
838: PRAGMA cache_size = 10;
839: CREATE TABLE t3(z);
840: BEGIN;
841: INSERT INTO t3 VALUES(randomblob(900));
842: INSERT INTO t3 SELECT randomblob(900) FROM t3;
843: INSERT INTO t2 VALUES('hello');
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: INSERT INTO t3 SELECT randomblob(900) FROM t3;
849: INSERT INTO t3 SELECT randomblob(900) FROM t3;
850: ROLLBACK;
851: }
852: execsql {
853: INSERT INTO t2 VALUES('goodbye');
854: INSERT INTO t3 SELECT randomblob(900) FROM t3;
855: INSERT INTO t3 SELECT randomblob(900) FROM t3;
856: }
857: } {}
858: do_test wal2-8.1.4 {
859: sqlite3 db2 test.db
860: execsql { SELECT * FROM t2 }
861: } {goodbye}
862: db2 close
863: db close
864:
865: #-------------------------------------------------------------------------
866: # Test that even if the checksums for both are valid, if the two copies
867: # of the wal-index header in the wal-index do not match, the client
868: # runs (or at least tries to run) database recovery.
869: #
870: #
871: proc get_name {method args} { set ::filename [lindex $args 0] ; tvfs filter {} }
872: testvfs tvfs
873: tvfs script get_name
874: tvfs filter xShmOpen
875:
876: forcedelete test.db test.db-wal test.db-journal
877: do_test wal2-9.1 {
878: sqlite3 db test.db -vfs tvfs
879: execsql {
880: PRAGMA journal_mode = WAL;
881: CREATE TABLE x(y);
882: INSERT INTO x VALUES('Barton');
883: INSERT INTO x VALUES('Deakin');
884: }
885:
886: # Set $wih(1) to the contents of the wal-index header after
887: # the frames associated with the first two rows in table 'x' have
888: # been inserted. Then insert one more row and set $wih(2)
889: # to the new value of the wal-index header.
890: #
891: # If the $wih(1) is written into the wal-index before running
892: # a read operation, the client will see only the first two rows. If
893: # $wih(2) is written into the wal-index, the client will see
894: # three rows. If an invalid header is written into the wal-index, then
895: # the client will run recovery and see three rows.
896: #
897: set wih(1) [set_tvfs_hdr $::filename]
898: execsql { INSERT INTO x VALUES('Watson') }
899: set wih(2) [set_tvfs_hdr $::filename]
900:
901: sqlite3 db2 test.db -vfs tvfs
902: execsql { SELECT * FROM x } db2
903: } {Barton Deakin Watson}
904:
905: foreach {tn hdr1 hdr2 res} [list \
906: 3 $wih(1) $wih(1) {Barton Deakin} \
907: 4 $wih(1) $wih(2) {Barton Deakin Watson} \
908: 5 $wih(2) $wih(1) {Barton Deakin Watson} \
909: 6 $wih(2) $wih(2) {Barton Deakin Watson} \
910: 7 $wih(1) $wih(1) {Barton Deakin} \
911: 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}
912: ] {
913: do_test wal2-9.$tn {
914: set_tvfs_hdr $::filename $hdr1 $hdr2
915: execsql { SELECT * FROM x } db2
916: } $res
917: }
918:
919: db2 close
920: db close
921:
922: #-------------------------------------------------------------------------
923: # This block of tests - wal2-10.* - focus on the libraries response to
924: # new versions of the wal or wal-index formats.
925: #
926: # wal2-10.1.*: Test that the library refuses to "recover" a new WAL
927: # format.
928: #
929: # wal2-10.2.*: Test that the library refuses to read or write a database
930: # if the wal-index version is newer than it understands.
931: #
932: # At time of writing, the only versions of the wal and wal-index formats
933: # that exist are versions 3007000 (corresponding to SQLite version 3.7.0,
934: # the first version of SQLite to feature wal mode).
935: #
936: do_test wal2-10.1.1 {
937: faultsim_delete_and_reopen
938: execsql {
939: PRAGMA journal_mode = WAL;
940: CREATE TABLE t1(a, b);
941: PRAGMA wal_checkpoint;
942: INSERT INTO t1 VALUES(1, 2);
943: INSERT INTO t1 VALUES(3, 4);
944: }
945: faultsim_save_and_close
946: } {}
947: do_test wal2-10.1.2 {
948: faultsim_restore_and_reopen
949: execsql { SELECT * FROM t1 }
950: } {1 2 3 4}
951: do_test wal2-10.1.3 {
952: faultsim_restore_and_reopen
953: set hdr [wal_set_walhdr test.db-wal]
954: lindex $hdr 1
955: } {3007000}
956: do_test wal2-10.1.4 {
957: lset hdr 1 3007001
958: wal_set_walhdr test.db-wal $hdr
959: catchsql { SELECT * FROM t1 }
960: } {1 {unable to open database file}}
961:
962: testvfs tvfs -default 1
963: do_test wal2-10.2.1 {
964: faultsim_restore_and_reopen
965: execsql { SELECT * FROM t1 }
966: } {1 2 3 4}
967: do_test wal2-10.2.2 {
968: set hdr [set_tvfs_hdr $::filename]
969: lindex $hdr 0
970: } {3007000}
971: do_test wal2-10.2.3 {
972: lset hdr 0 3007001
973: wal_fix_walindex_cksum hdr
974: set_tvfs_hdr $::filename $hdr
975: catchsql { SELECT * FROM t1 }
976: } {1 {unable to open database file}}
977: db close
978: tvfs delete
979:
980: #-------------------------------------------------------------------------
981: # This block of tests - wal2-11.* - tests that it is not possible to put
982: # the library into an infinite loop by presenting it with a corrupt
983: # hash table (one that appears to contain a single chain of infinite
984: # length).
985: #
986: # wal2-11.1.*: While reading the hash-table.
987: #
988: # wal2-11.2.*: While writing the hash-table.
989: #
990: testvfs tvfs -default 1
991: do_test wal2-11.0 {
992: faultsim_delete_and_reopen
993: execsql {
994: PRAGMA journal_mode = WAL;
995: CREATE TABLE t1(a, b, c);
996: INSERT INTO t1 VALUES(1, 2, 3);
997: INSERT INTO t1 VALUES(4, 5, 6);
998: INSERT INTO t1 VALUES(7, 8, 9);
999: SELECT * FROM t1;
1000: }
1001: } {wal 1 2 3 4 5 6 7 8 9}
1002:
1003: do_test wal2-11.1.1 {
1004: sqlite3 db2 test.db
1005: execsql { SELECT name FROM sqlite_master } db2
1006: } {t1}
1007:
1008: if {$::tcl_version>=8.5} {
1009: # Set all zeroed slots in the first hash table to invalid values.
1010: #
1011: set blob [string range [tvfs shm $::filename] 0 16383]
1012: set I [string range [tvfs shm $::filename] 16384 end]
1013: binary scan $I t* L
1014: set I [list]
1015: foreach p $L {
1016: lappend I [expr $p ? $p : 400]
1017: }
1018: append blob [binary format t* $I]
1019: tvfs shm $::filename $blob
1020: do_test wal2-11.2 {
1021: catchsql { INSERT INTO t1 VALUES(10, 11, 12) }
1022: } {1 {database disk image is malformed}}
1023:
1024: # Fill up the hash table on the first page of shared memory with 0x55 bytes.
1025: #
1026: set blob [string range [tvfs shm $::filename] 0 16383]
1027: append blob [string repeat [binary format c 55] 16384]
1028: tvfs shm $::filename $blob
1029: do_test wal2-11.3 {
1030: catchsql { SELECT * FROM t1 } db2
1031: } {1 {database disk image is malformed}}
1032: }
1033:
1034: db close
1035: db2 close
1036: tvfs delete
1037:
1038: #-------------------------------------------------------------------------
1039: # If a connection is required to create a WAL or SHM file, it creates
1040: # the new files with the same file-system permissions as the database
1041: # file itself. Test this.
1042: #
1043: if {$::tcl_platform(platform) == "unix"} {
1044: faultsim_delete_and_reopen
1045: set umask [exec /bin/sh -c umask]
1046:
1047: do_test wal2-12.1 {
1048: sqlite3 db test.db
1049: execsql {
1050: CREATE TABLE tx(y, z);
1051: PRAGMA journal_mode = WAL;
1052: }
1053: db close
1054: list [file exists test.db-wal] [file exists test.db-shm]
1055: } {0 0}
1056:
1057: foreach {tn permissions} {
1058: 1 00644
1059: 2 00666
1060: 3 00600
1061: 4 00755
1062: } {
1063: set effective [format %.5o [expr $permissions & ~$umask]]
1064: do_test wal2-12.2.$tn.1 {
1065: file attributes test.db -permissions $permissions
1066: file attributes test.db -permissions
1067: } $permissions
1068: do_test wal2-12.2.$tn.2 {
1069: list [file exists test.db-wal] [file exists test.db-shm]
1070: } {0 0}
1071: do_test wal2-12.2.$tn.3 {
1072: sqlite3 db test.db
1073: execsql { INSERT INTO tx DEFAULT VALUES }
1074: list [file exists test.db-wal] [file exists test.db-shm]
1075: } {1 1}
1076: do_test wal2-12.2.$tn.4 {
1077: list [file attr test.db-wal -perm] [file attr test.db-shm -perm]
1078: } [list $effective $effective]
1079: do_test wal2-12.2.$tn.5 {
1080: db close
1081: list [file exists test.db-wal] [file exists test.db-shm]
1082: } {0 0}
1083: }
1084: }
1085:
1086: #-------------------------------------------------------------------------
1087: # Test the libraries response to discovering that one or more of the
1088: # database, wal or shm files cannot be opened, or can only be opened
1089: # read-only.
1090: #
1091: if {$::tcl_platform(platform) == "unix"} {
1092: proc perm {} {
1093: set L [list]
1094: foreach f {test.db test.db-wal test.db-shm} {
1095: if {[file exists $f]} {
1096: lappend L [file attr $f -perm]
1097: } else {
1098: lappend L {}
1099: }
1100: }
1101: set L
1102: }
1103:
1104: faultsim_delete_and_reopen
1105: execsql {
1106: PRAGMA journal_mode = WAL;
1107: CREATE TABLE t1(a, b);
1108: PRAGMA wal_checkpoint;
1109: INSERT INTO t1 VALUES('3.14', '2.72');
1110: }
1111: do_test wal2-13.1.1 {
1112: list [file exists test.db-shm] [file exists test.db-wal]
1113: } {1 1}
1114: faultsim_save_and_close
1115:
1116: foreach {tn db_perm wal_perm shm_perm can_open can_read can_write} {
1117: 2 00644 00644 00644 1 1 1
1118: 3 00644 00400 00644 1 1 0
1119: 4 00644 00644 00400 1 0 0
1120: 5 00400 00644 00644 1 1 0
1121:
1122: 7 00644 00000 00644 1 0 0
1123: 8 00644 00644 00000 1 0 0
1124: 9 00000 00644 00644 0 0 0
1125: } {
1126: faultsim_restore
1127: do_test wal2-13.$tn.1 {
1128: file attr test.db -perm $db_perm
1129: file attr test.db-wal -perm $wal_perm
1130: file attr test.db-shm -perm $shm_perm
1131:
1132: set L [file attr test.db -perm]
1133: lappend L [file attr test.db-wal -perm]
1134: lappend L [file attr test.db-shm -perm]
1135: } [list $db_perm $wal_perm $shm_perm]
1136:
1137: # If $can_open is true, then it should be possible to open a database
1138: # handle. Otherwise, if $can_open is 0, attempting to open the db
1139: # handle throws an "unable to open database file" exception.
1140: #
1141: set r(1) {0 ok}
1142: set r(0) {1 {unable to open database file}}
1143: do_test wal2-13.$tn.2 {
1144: list [catch {sqlite3 db test.db ; set {} ok} msg] $msg
1145: } $r($can_open)
1146:
1147: if {$can_open} {
1148:
1149: # If $can_read is true, then the client should be able to read from
1150: # the database file. If $can_read is false, attempting to read should
1151: # throw the "unable to open database file" exception.
1152: #
1153: set a(0) {1 {unable to open database file}}
1154: set a(1) {0 {3.14 2.72}}
1155: do_test wal2-13.$tn.3 {
1156: catchsql { SELECT * FROM t1 }
1157: } $a($can_read)
1158:
1159: # Now try to write to the db file. If the client can read but not
1160: # write, then it should throw the familiar "unable to open db file"
1161: # exception. If it can read but not write, the exception should
1162: # be "attempt to write a read only database".
1163: #
1164: # If the client can read and write, the operation should succeed.
1165: #
1166: set b(0,0) {1 {unable to open database file}}
1167: set b(1,0) {1 {attempt to write a readonly database}}
1168: set b(1,1) {0 {}}
1169: do_test wal2-13.$tn.4 {
1170: catchsql { INSERT INTO t1 DEFAULT VALUES }
1171: } $b($can_read,$can_write)
1172: }
1173: catch { db close }
1174: }
1175: }
1176:
1177: #-------------------------------------------------------------------------
1178: # Test that "PRAGMA checkpoint_fullsync" appears to be working.
1179: #
1180: foreach {tn sql reslist} {
1181: 1 { } {10 0 4 0 6 0}
1182: 2 { PRAGMA checkpoint_fullfsync = 1 } {10 4 4 2 6 2}
1183: 3 { PRAGMA checkpoint_fullfsync = 0 } {10 0 4 0 6 0}
1184: } {
1185: faultsim_delete_and_reopen
1186:
1187: execsql {PRAGMA auto_vacuum = 0}
1188: execsql $sql
1189: do_execsql_test wal2-14.$tn.0 { PRAGMA page_size = 4096 } {}
1190: do_execsql_test wal2-14.$tn.1 { PRAGMA journal_mode = WAL } {wal}
1191:
1192: set sqlite_sync_count 0
1193: set sqlite_fullsync_count 0
1194:
1195: do_execsql_test wal2-14.$tn.2 {
1196: PRAGMA wal_autocheckpoint = 10;
1197: CREATE TABLE t1(a, b); -- 2 wal syncs
1198: INSERT INTO t1 VALUES(1, 2); -- 2 wal sync
1199: PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1200: BEGIN;
1201: INSERT INTO t1 VALUES(3, 4);
1202: INSERT INTO t1 VALUES(5, 6);
1203: COMMIT; -- 2 wal sync
1204: PRAGMA wal_checkpoint; -- 1 wal sync, 1 db sync
1205: } {10 0 3 3 0 1 1}
1206:
1207: do_test wal2-14.$tn.3 {
1208: cond_incr_sync_count 1
1209: list $sqlite_sync_count $sqlite_fullsync_count
1210: } [lrange $reslist 0 1]
1211:
1212: set sqlite_sync_count 0
1213: set sqlite_fullsync_count 0
1214:
1215: do_test wal2-14.$tn.4 {
1216: execsql { INSERT INTO t1 VALUES(7, zeroblob(12*4096)) }
1217: list $sqlite_sync_count $sqlite_fullsync_count
1218: } [lrange $reslist 2 3]
1219:
1220: set sqlite_sync_count 0
1221: set sqlite_fullsync_count 0
1222:
1223: do_test wal2-14.$tn.5 {
1224: execsql { PRAGMA wal_autocheckpoint = 1000 }
1225: execsql { INSERT INTO t1 VALUES(9, 10) }
1226: execsql { INSERT INTO t1 VALUES(11, 12) }
1227: execsql { INSERT INTO t1 VALUES(13, 14) }
1228: db close
1229: list $sqlite_sync_count $sqlite_fullsync_count
1230: } [lrange $reslist 4 5]
1231: }
1232:
1233: catch { db close }
1234:
1235: # PRAGMA checkpoint_fullsync
1236: # PRAGMA fullfsync
1237: # PRAGMA synchronous
1238: #
1239: foreach {tn settings restart_sync commit_sync ckpt_sync} {
1240: 1 {0 0 off} {0 0} {0 0} {0 0}
1241: 2 {0 0 normal} {1 0} {0 0} {2 0}
1242: 3 {0 0 full} {2 0} {1 0} {2 0}
1243:
1244: 4 {0 1 off} {0 0} {0 0} {0 0}
1245: 5 {0 1 normal} {0 1} {0 0} {0 2}
1246: 6 {0 1 full} {0 2} {0 1} {0 2}
1247:
1248: 7 {1 0 off} {0 0} {0 0} {0 0}
1249: 8 {1 0 normal} {1 0} {0 0} {0 2}
1250: 9 {1 0 full} {2 0} {1 0} {0 2}
1251:
1252: 10 {1 1 off} {0 0} {0 0} {0 0}
1253: 11 {1 1 normal} {0 1} {0 0} {0 2}
1254: 12 {1 1 full} {0 2} {0 1} {0 2}
1255: } {
1256: forcedelete test.db
1257:
1258: testvfs tvfs -default 1
1259: tvfs filter xSync
1260: tvfs script xSyncCb
1261: proc xSyncCb {method file fileid flags} {
1262: incr ::sync($flags)
1263: }
1264:
1265: sqlite3 db test.db
1266: do_execsql_test 15.$tn.1 "
1267: PRAGMA page_size = 4096;
1268: CREATE TABLE t1(x);
1269: PRAGMA wal_autocheckpoint = OFF;
1270: PRAGMA journal_mode = WAL;
1271: PRAGMA checkpoint_fullfsync = [lindex $settings 0];
1272: PRAGMA fullfsync = [lindex $settings 1];
1273: PRAGMA synchronous = [lindex $settings 2];
1274: " {0 wal}
1275:
1276: if { $tn==2} breakpoint
1277: do_test 15.$tn.2 {
1278: set sync(normal) 0
1279: set sync(full) 0
1280: execsql { INSERT INTO t1 VALUES('abc') }
1281: list $::sync(normal) $::sync(full)
1282: } $restart_sync
1283:
1284: do_test 15.$tn.3 {
1285: set sync(normal) 0
1286: set sync(full) 0
1287: execsql { INSERT INTO t1 VALUES('abc') }
1288: list $::sync(normal) $::sync(full)
1289: } $commit_sync
1290:
1291: do_test 15.$tn.4 {
1292: set sync(normal) 0
1293: set sync(full) 0
1294: execsql { INSERT INTO t1 VALUES('def') }
1295: list $::sync(normal) $::sync(full)
1296: } $commit_sync
1297:
1298: do_test 15.$tn.5 {
1299: set sync(normal) 0
1300: set sync(full) 0
1301: execsql { PRAGMA wal_checkpoint }
1302: list $::sync(normal) $::sync(full)
1303: } $ckpt_sync
1304:
1305: db close
1306: tvfs delete
1307: }
1308:
1309:
1310:
1311: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to wal2.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test