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