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