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