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