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