1# 2010 April 13 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/wal_common.tcl 20source $testdir/malloc_common.tcl 21ifcapable !wal {finish_test ; return } 22 23set a_string_counter 1 24proc a_string {n} { 25 global a_string_counter 26 incr a_string_counter 27 string range [string repeat "${a_string_counter}." $n] 1 $n 28} 29db func a_string a_string 30 31#------------------------------------------------------------------------- 32# When a rollback or savepoint rollback occurs, the client may remove 33# elements from one of the hash tables in the wal-index. This block 34# of test cases tests that nothing appears to go wrong when this is 35# done. 36# 37do_test wal3-1.0 { 38 execsql { 39 PRAGMA cache_size = 2000; 40 PRAGMA page_size = 1024; 41 PRAGMA auto_vacuum = off; 42 PRAGMA synchronous = normal; 43 PRAGMA journal_mode = WAL; 44 PRAGMA wal_autocheckpoint = 0; 45 BEGIN; 46 CREATE TABLE t1(x); 47 INSERT INTO t1 VALUES( a_string(800) ); /* 1 */ 48 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ 49 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ 50 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ 51 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ 52 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ 53 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 64 */ 54 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 128*/ 55 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 256 */ 56 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 512 */ 57 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 1024 */ 58 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2048 */ 59 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 1970; /* 4018 */ 60 COMMIT; 61 PRAGMA cache_size = 10; 62 } 63 wal_frame_count test.db-wal 1024 64} 4056 65 66for {set i 1} {$i < 50} {incr i} { 67 68 do_test wal3-1.$i.1 { 69 set str [a_string 800] 70 execsql { UPDATE t1 SET x = $str WHERE rowid = $i } 71 lappend L [wal_frame_count test.db-wal 1024] 72 execsql { 73 BEGIN; 74 INSERT INTO t1 SELECT a_string(800) FROM t1 LIMIT 100; 75 ROLLBACK; 76 PRAGMA integrity_check; 77 } 78 } {ok} 79 80 # Check that everything looks OK from the point of view of an 81 # external connection. 82 # 83 sqlite3 db2 test.db 84 do_test wal3-1.$i.2 { 85 execsql { SELECT count(*) FROM t1 } db2 86 } 4018 87 do_test wal3-1.$i.3 { 88 execsql { SELECT x FROM t1 WHERE rowid = $i } 89 } $str 90 do_test wal3-1.$i.4 { 91 execsql { PRAGMA integrity_check } db2 92 } {ok} 93 db2 close 94 95 # Check that the file-system in its current state can be recovered. 96 # 97 file copy -force test.db test2.db 98 file copy -force test.db-wal test2.db-wal 99 file delete -force test2.db-journal 100 sqlite3 db2 test2.db 101 do_test wal3-1.$i.5 { 102 execsql { SELECT count(*) FROM t1 } db2 103 } 4018 104 do_test wal3-1.$i.6 { 105 execsql { SELECT x FROM t1 WHERE rowid = $i } 106 } $str 107 do_test wal3-1.$i.7 { 108 execsql { PRAGMA integrity_check } db2 109 } {ok} 110 db2 close 111} 112 113proc byte_is_zero {file offset} { 114 if {[file size test.db] <= $offset} { return 1 } 115 expr { [hexio_read $file $offset 1] == "00" } 116} 117 118do_multiclient_test i { 119 120 set testname(1) multiproc 121 set testname(2) singleproc 122 set tn $testname($i) 123 124 do_test wal3-2.$tn.1 { 125 sql1 { 126 PRAGMA page_size = 1024; 127 PRAGMA journal_mode = WAL; 128 } 129 sql1 { 130 CREATE TABLE t1(a, b); 131 INSERT INTO t1 VALUES(1, 'one'); 132 BEGIN; 133 SELECT * FROM t1; 134 } 135 } {1 one} 136 do_test wal3-2.$tn.2 { 137 sql2 { 138 CREATE TABLE t2(a, b); 139 INSERT INTO t2 VALUES(2, 'two'); 140 BEGIN; 141 SELECT * FROM t2; 142 } 143 } {2 two} 144 do_test wal3-2.$tn.3 { 145 sql3 { 146 CREATE TABLE t3(a, b); 147 INSERT INTO t3 VALUES(3, 'three'); 148 BEGIN; 149 SELECT * FROM t3; 150 } 151 } {3 three} 152 153 # Try to checkpoint the database using [db]. It should be possible to 154 # checkpoint everything except the table added by [db3] (checkpointing 155 # these frames would clobber the snapshot currently being used by [db2]). 156 # 157 # After [db2] has committed, a checkpoint can copy the entire log to the 158 # database file. Checkpointing after [db3] has committed is therefore a 159 # no-op, as the entire log has already been backfilled. 160 # 161 do_test wal3-2.$tn.4 { 162 sql1 { 163 COMMIT; 164 PRAGMA wal_checkpoint; 165 } 166 byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024] 167 } {1} 168 do_test wal3-2.$tn.5 { 169 sql2 { 170 COMMIT; 171 PRAGMA wal_checkpoint; 172 } 173 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \ 174 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]] 175 } {0 1} 176 do_test wal3-2.$tn.6 { 177 sql3 { 178 COMMIT; 179 PRAGMA wal_checkpoint; 180 } 181 list [byte_is_zero test.db [expr $AUTOVACUUM ? 4*1024 : 3*1024]] \ 182 [byte_is_zero test.db [expr $AUTOVACUUM ? 5*1024 : 4*1024]] 183 } {0 1} 184} 185catch {db close} 186 187#------------------------------------------------------------------------- 188# Test that that for the simple test: 189# 190# CREATE TABLE x(y); 191# INSERT INTO x VALUES('z'); 192# PRAGMA wal_checkpoint; 193# 194# in WAL mode the xSync method is invoked as expected for each of 195# synchronous=off, synchronous=normal and synchronous=full. 196# 197foreach {tn syncmode synccount} { 198 1 off 199 {} 200 2 normal 201 {test.db-wal normal test.db normal} 202 3 full 203 {test.db-wal normal test.db-wal normal test.db-wal normal test.db normal} 204} { 205 206 proc sync_counter {args} { 207 foreach {method filename id flags} $args break 208 lappend ::syncs [file tail $filename] $flags 209 } 210 do_test wal3-3.$tn { 211 file delete -force test.db test.db-wal test.db-journal 212 213 testvfs T 214 T filter {} 215 T script sync_counter 216 sqlite3 db test.db -vfs T 217 218 execsql "PRAGMA synchronous = $syncmode" 219 execsql { PRAGMA journal_mode = WAL } 220 221 set ::syncs [list] 222 T filter xSync 223 execsql { 224 CREATE TABLE x(y); 225 INSERT INTO x VALUES('z'); 226 PRAGMA wal_checkpoint; 227 } 228 T filter {} 229 set ::syncs 230 } $synccount 231 232 db close 233 T delete 234} 235 236#------------------------------------------------------------------------- 237# When recovering the contents of a WAL file, a process obtains the WRITER 238# lock, then locks all other bytes before commencing recovery. If it fails 239# to lock all other bytes (because some other process is holding a read 240# lock) it should retry up to 100 times. Then return SQLITE_PROTOCOL to the 241# caller. Test this (test case wal3-4.3). 242# 243# Also test the effect of hitting an SQLITE_BUSY while attempting to obtain 244# the WRITER lock (should be the same). Test case wal3-4.4. 245# 246proc lock_callback {method filename handle lock} { 247 lappend ::locks $lock 248} 249do_test wal3-4.1 { 250 testvfs T 251 T filter xShmLock 252 T script lock_callback 253 set ::locks [list] 254 sqlite3 db test.db -vfs T 255 execsql { SELECT * FROM x } 256 lrange $::locks 0 3 257} [list {0 1 lock exclusive} {1 7 lock exclusive} \ 258 {1 7 unlock exclusive} {0 1 unlock exclusive} \ 259] 260do_test wal3-4.2 { 261 db close 262 set ::locks [list] 263 sqlite3 db test.db -vfs T 264 execsql { SELECT * FROM x } 265 lrange $::locks 0 3 266} [list {0 1 lock exclusive} {1 7 lock exclusive} \ 267 {1 7 unlock exclusive} {0 1 unlock exclusive} \ 268] 269proc lock_callback {method filename handle lock} { 270 if {$lock == "1 7 lock exclusive"} { return SQLITE_BUSY } 271 return SQLITE_OK 272} 273puts " Warning: This next test case causes SQLite to call xSleep(1) 100 times." 274puts " Normally this equates to a 100ms delay, but if SQLite is built on unix" 275puts " without HAVE_USLEEP defined, it may be 100 seconds." 276do_test wal3-4.3 { 277 db close 278 set ::locks [list] 279 sqlite3 db test.db -vfs T 280 catchsql { SELECT * FROM x } 281} {1 {locking protocol}} 282 283puts " Warning: Same again!" 284proc lock_callback {method filename handle lock} { 285 if {$lock == "0 1 lock exclusive"} { return SQLITE_BUSY } 286 return SQLITE_OK 287} 288do_test wal3-4.4 { 289 db close 290 set ::locks [list] 291 sqlite3 db test.db -vfs T 292 catchsql { SELECT * FROM x } 293} {1 {locking protocol}} 294db close 295T delete 296 297 298#------------------------------------------------------------------------- 299# Only one client may run recovery at a time. Test this mechanism. 300# 301# When client-2 tries to open a read transaction while client-1 is 302# running recovery, it fails to obtain a lock on an aReadMark[] slot 303# (because they are all locked by recovery). It then tries to obtain 304# a shared lock on the RECOVER lock to see if there really is a 305# recovery running or not. 306# 307# This block of tests checks the effect of an SQLITE_BUSY or SQLITE_IOERR 308# being returned when client-2 attempts a shared lock on the RECOVER byte. 309# 310# An SQLITE_BUSY should be converted to an SQLITE_BUSY_RECOVERY. An 311# SQLITE_IOERR should be returned to the caller. 312# 313do_test wal3-5.1 { 314 faultsim_delete_and_reopen 315 execsql { 316 PRAGMA journal_mode = WAL; 317 CREATE TABLE t1(a, b); 318 INSERT INTO t1 VALUES(1, 2); 319 INSERT INTO t1 VALUES(3, 4); 320 } 321 faultsim_save_and_close 322} {} 323 324testvfs T -default 1 325T script method_callback 326 327proc method_callback {method args} { 328 if {$method == "xShmBarrier"} { 329 incr ::barrier_count 330 if {$::barrier_count == 2} { 331 # This code is executed within the xShmBarrier() callback invoked 332 # by the client running recovery as part of writing the recovered 333 # wal-index header. If a second client attempts to access the 334 # database now, it reads a corrupt (partially written) wal-index 335 # header. But it cannot even get that far, as the first client 336 # is still holding all the locks (recovery takes an exclusive lock 337 # on *all* db locks, preventing access by any other client). 338 # 339 # If global variable ::wal3_do_lockfailure is non-zero, then set 340 # things up so that an IO error occurs within an xShmLock() callback 341 # made by the second client (aka [db2]). 342 # 343 sqlite3 db2 test.db 344 if { $::wal3_do_lockfailure } { T filter xShmLock } 345 set ::testrc [ catch { db2 eval "SELECT * FROM t1" } ::testmsg ] 346 T filter {} 347 db2 close 348 } 349 } 350 351 if {$method == "xShmLock"} { 352 foreach {file handle spec} $args break 353 if { $spec == "2 1 lock shared" } { 354 return SQLITE_IOERR 355 } 356 } 357 358 return SQLITE_OK 359} 360 361# Test a normal SQLITE_BUSY return. 362# 363T filter xShmBarrier 364set testrc "" 365set testmsg "" 366set barrier_count 0 367set wal3_do_lockfailure 0 368do_test wal3-5.2 { 369 faultsim_restore_and_reopen 370 execsql { SELECT * FROM t1 } 371} {1 2 3 4} 372do_test wal3-5.3 { 373 list $::testrc $::testmsg 374} {1 {database is locked}} 375db close 376 377# Test an SQLITE_IOERR return. 378# 379T filter xShmBarrier 380set barrier_count 0 381set wal3_do_lockfailure 1 382set testrc "" 383set testmsg "" 384do_test wal3-5.4 { 385 faultsim_restore_and_reopen 386 execsql { SELECT * FROM t1 } 387} {1 2 3 4} 388do_test wal3-5.5 { 389 list $::testrc $::testmsg 390} {1 {disk I/O error}} 391 392db close 393T delete 394 395#------------------------------------------------------------------------- 396# When opening a read-transaction on a database, if the entire log has 397# already been copied to the database file, the reader grabs a special 398# kind of read lock (on aReadMark[0]). This set of test cases tests the 399# outcome of the following: 400# 401# + The reader discovering that between the time when it determined 402# that the log had been completely backfilled and the lock is obtained 403# that a writer has written to the log. In this case the reader should 404# acquire a different read-lock (not aReadMark[0]) and read the new 405# snapshot. 406# 407# + The attempt to obtain the lock on aReadMark[0] fails with SQLITE_BUSY. 408# This can happen if a checkpoint is ongoing. In this case also simply 409# obtain a different read-lock. 410# 411catch {db close} 412testvfs T -default 1 413do_test wal3-6.1.1 { 414 file delete -force test.db test.db-journal test.db wal 415 sqlite3 db test.db 416 execsql { PRAGMA journal_mode = WAL } 417 execsql { 418 CREATE TABLE t1(a, b); 419 INSERT INTO t1 VALUES('o', 't'); 420 INSERT INTO t1 VALUES('t', 'f'); 421 } 422} {} 423do_test wal3-6.1.2 { 424 sqlite3 db2 test.db 425 sqlite3 db3 test.db 426 execsql { BEGIN ; SELECT * FROM t1 } db3 427} {o t t f} 428do_test wal3-6.1.3 { 429 execsql { PRAGMA wal_checkpoint } db2 430} {} 431 432# At this point the log file has been fully checkpointed. However, 433# connection [db3] holds a lock that prevents the log from being wrapped. 434# Test case 3.6.1.4 has [db] attempt a read-lock on aReadMark[0]. But 435# as it is obtaining the lock, [db2] appends to the log file. 436# 437T filter xShmLock 438T script lock_callback 439proc lock_callback {method file handle spec} { 440 if {$spec == "3 1 lock shared"} { 441 # This is the callback for [db] to obtain the read lock on aReadMark[0]. 442 # Disable future callbacks using [T filter {}] and write to the log 443 # file using [db2]. [db3] is preventing [db2] from wrapping the log 444 # here, so this is an append. 445 T filter {} 446 db2 eval { INSERT INTO t1 VALUES('f', 's') } 447 } 448 return SQLITE_OK 449} 450do_test wal3-6.1.4 { 451 execsql { 452 BEGIN; 453 SELECT * FROM t1; 454 } 455} {o t t f f s} 456 457# [db] should be left holding a read-lock on some slot other than 458# aReadMark[0]. Test this by demonstrating that the read-lock is preventing 459# the log from being wrapped. 460# 461do_test wal3-6.1.5 { 462 db3 eval COMMIT 463 db2 eval { PRAGMA wal_checkpoint } 464 set sz1 [file size test.db-wal] 465 db2 eval { INSERT INTO t1 VALUES('s', 'e') } 466 set sz2 [file size test.db-wal] 467 expr {$sz2>$sz1} 468} {1} 469 470# Test that if [db2] had not interfered when [db] was trying to grab 471# aReadMark[0], it would have been possible to wrap the log in 3.6.1.5. 472# 473do_test wal3-6.1.6 { 474 execsql { COMMIT } 475 execsql { PRAGMA wal_checkpoint } db2 476 execsql { 477 BEGIN; 478 SELECT * FROM t1; 479 } 480} {o t t f f s s e} 481do_test wal3-6.1.7 { 482 db2 eval { PRAGMA wal_checkpoint } 483 set sz1 [file size test.db-wal] 484 db2 eval { INSERT INTO t1 VALUES('n', 't') } 485 set sz2 [file size test.db-wal] 486 expr {$sz2==$sz1} 487} {1} 488 489db3 close 490db2 close 491db close 492 493do_test wal3-6.2.1 { 494 file delete -force test.db test.db-journal test.db wal 495 sqlite3 db test.db 496 sqlite3 db2 test.db 497 execsql { PRAGMA journal_mode = WAL } 498 execsql { 499 CREATE TABLE t1(a, b); 500 INSERT INTO t1 VALUES('h', 'h'); 501 INSERT INTO t1 VALUES('l', 'b'); 502 } 503} {} 504 505T filter xShmLock 506T script lock_callback 507proc lock_callback {method file handle spec} { 508 if {$spec == "3 1 unlock exclusive"} { 509 T filter {} 510 set ::R [db2 eval { 511 BEGIN; 512 SELECT * FROM t1; 513 }] 514 } 515} 516do_test wal3-6.2.2 { 517 execsql { PRAGMA wal_checkpoint } 518} {} 519do_test wal3-6.2.3 { 520 set ::R 521} {h h l b} 522do_test wal3-6.2.4 { 523 set sz1 [file size test.db-wal] 524 execsql { INSERT INTO t1 VALUES('b', 'c'); } 525 set sz2 [file size test.db-wal] 526 expr {$sz2 > $sz1} 527} {1} 528do_test wal3-6.2.5 { 529 db2 eval { COMMIT } 530 execsql { PRAGMA wal_checkpoint } 531 set sz1 [file size test.db-wal] 532 execsql { INSERT INTO t1 VALUES('n', 'o'); } 533 set sz2 [file size test.db-wal] 534 expr {$sz2 == $sz1} 535} {1} 536 537db2 close 538db close 539T delete 540 541#------------------------------------------------------------------------- 542# When opening a read-transaction on a database, if the entire log has 543# not yet been copied to the database file, the reader grabs a read 544# lock on aReadMark[x], where x>0. The following test cases experiment 545# with the outcome of the following: 546# 547# + The reader discovering that between the time when it read the 548# wal-index header and the lock was obtained that a writer has 549# written to the log. In this case the reader should re-read the 550# wal-index header and lock a snapshot corresponding to the new 551# header. 552# 553# + The value in the aReadMark[x] slot has been modified since it was 554# read. 555# 556catch {db close} 557testvfs T -default 1 558do_test wal3-7.1.1 { 559 file delete -force test.db test.db-journal test.db wal 560 sqlite3 db test.db 561 execsql { 562 PRAGMA journal_mode = WAL; 563 CREATE TABLE blue(red PRIMARY KEY, green); 564 } 565} {wal} 566 567T script method_callback 568T filter xOpen 569proc method_callback {method args} { 570 if {$method == "xOpen"} { return "reader" } 571} 572do_test wal3-7.1.2 { 573 sqlite3 db2 test.db 574 execsql { SELECT * FROM blue } db2 575} {} 576 577T filter xShmLock 578set ::locks [list] 579proc method_callback {method file handle spec} { 580 if {$handle != "reader" } { return } 581 if {$method == "xShmLock"} { 582 catch { execsql { INSERT INTO blue VALUES(1, 2) } } 583 catch { execsql { INSERT INTO blue VALUES(3, 4) } } 584 } 585 lappend ::locks $spec 586} 587do_test wal3-7.1.3 { 588 execsql { SELECT * FROM blue } db2 589} {1 2 3 4} 590do_test wal3-7.1.4 { 591 set ::locks 592} {{4 1 lock shared} {4 1 unlock shared} {5 1 lock shared} {5 1 unlock shared}} 593 594set ::locks [list] 595proc method_callback {method file handle spec} { 596 if {$handle != "reader" } { return } 597 if {$method == "xShmLock"} { 598 catch { execsql { INSERT INTO blue VALUES(5, 6) } } 599 } 600 lappend ::locks $spec 601} 602do_test wal3-7.2.1 { 603 execsql { SELECT * FROM blue } db2 604} {1 2 3 4 5 6} 605do_test wal3-7.2.2 { 606 set ::locks 607} {{5 1 lock shared} {5 1 unlock shared} {4 1 lock shared} {4 1 unlock shared}} 608 609db close 610db2 close 611T delete 612 613#------------------------------------------------------------------------- 614# 615do_test wal3-8.1 { 616 file delete -force test.db test.db-journal test.db wal 617 sqlite3 db test.db 618 sqlite3 db2 test.db 619 execsql { 620 PRAGMA journal_mode = WAL; 621 CREATE TABLE b(c); 622 INSERT INTO b VALUES('Tehran'); 623 INSERT INTO b VALUES('Qom'); 624 INSERT INTO b VALUES('Markazi'); 625 PRAGMA wal_checkpoint; 626 } 627} {wal} 628do_test wal3-8.2 { 629 execsql { SELECT * FROM b } 630} {Tehran Qom Markazi} 631do_test wal3-8.3 { 632 db eval { SELECT * FROM b } { 633 db eval { INSERT INTO b VALUES('Qazvin') } 634 set r [db2 eval { SELECT * FROM b }] 635 break 636 } 637 set r 638} {Tehran Qom Markazi Qazvin} 639do_test wal3-8.4 { 640 execsql { 641 INSERT INTO b VALUES('Gilan'); 642 INSERT INTO b VALUES('Ardabil'); 643 } 644} {} 645db2 close 646 647faultsim_save_and_close 648testvfs T -default 1 649faultsim_restore_and_reopen 650T filter xShmLock 651T script lock_callback 652 653proc lock_callback {method file handle spec} { 654 if {$spec == "4 1 unlock exclusive"} { 655 T filter {} 656 set ::r [catchsql { SELECT * FROM b } db2] 657 } 658} 659sqlite3 db test.db 660sqlite3 db2 test.db 661do_test wal3-8.5 { 662 execsql { SELECT * FROM b } 663} {Tehran Qom Markazi Qazvin Gilan Ardabil} 664do_test wal3-8.6 { 665 set ::r 666} {1 {locking protocol}} 667 668db close 669db2 close 670 671faultsim_restore_and_reopen 672sqlite3 db2 test.db 673T filter xShmLock 674T script lock_callback 675proc lock_callback {method file handle spec} { 676 if {$spec == "1 7 unlock exclusive"} { 677 T filter {} 678 set ::r [catchsql { SELECT * FROM b } db2] 679 } 680} 681unset ::r 682do_test wal3-8.5 { 683 execsql { SELECT * FROM b } 684} {Tehran Qom Markazi Qazvin Gilan Ardabil} 685do_test wal3-8.6 { 686 set ::r 687} {1 {locking protocol}} 688 689db close 690db2 close 691T delete 692 693#------------------------------------------------------------------------- 694# When a connection opens a read-lock on the database, it searches for 695# an aReadMark[] slot that is already set to the mxFrame value for the 696# new transaction. If it cannot find one, it attempts to obtain an 697# exclusive lock on an aReadMark[] slot for the purposes of modifying 698# the value, then drops back to a shared-lock for the duration of the 699# transaction. 700# 701# This test case verifies that if an exclusive lock cannot be obtained 702# on any aReadMark[] slot (because there are already several readers), 703# the client takes a shared-lock on a slot without modifying the value 704# and continues. 705# 706do_test wal3-9.0 { 707 file delete -force test.db test.db-journal test.db wal 708 sqlite3 db test.db 709 execsql { 710 PRAGMA page_size = 1024; 711 PRAGMA journal_mode = WAL; 712 CREATE TABLE whoami(x); 713 INSERT INTO whoami VALUES('nobody'); 714 } 715} {wal} 716for {set i 0} {$i < 50} {incr i} { 717 set c db$i 718 do_test wal3-9.1.$i { 719 sqlite3 $c test.db 720 execsql { UPDATE whoami SET x = $c } 721 execsql { 722 BEGIN; 723 SELECT * FROM whoami 724 } $c 725 } $c 726} 727for {set i 0} {$i < 50} {incr i} { 728 set c db$i 729 do_test wal3-9.2.$i { 730 execsql { SELECT * FROM whoami } $c 731 } $c 732} 733 734set sz [expr 1024 * (2+$AUTOVACUUM)] 735do_test wal3-9.3 { 736 for {set i 0} {$i < 49} {incr i} { db$i close } 737 execsql { PRAGMA wal_checkpoint } 738 byte_is_zero test.db [expr $sz-1024] 739} {1} 740do_test wal3-9.4 { 741 db49 close 742 execsql { PRAGMA wal_checkpoint } 743 set sz2 [file size test.db] 744 byte_is_zero test.db [expr $sz-1024] 745} {0} 746 747do_multiclient_test tn { 748 do_test wal3-10.$tn.1 { 749 sql1 { 750 PRAGMA page_size = 1024; 751 CREATE TABLE t1(x); 752 PRAGMA journal_mode = WAL; 753 PRAGMA wal_autocheckpoint = 100000; 754 BEGIN; 755 INSERT INTO t1 VALUES(randomblob(800)); 756 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2 757 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4 758 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8 759 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 16 760 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 32 761 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 64 762 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 128 763 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 256 764 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 512 765 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 1024 766 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 2048 767 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 4096 768 INSERT INTO t1 SELECT randomblob(800) FROM t1; -- 8192 769 COMMIT; 770 CREATE INDEX i1 ON t1(x); 771 } 772 773 expr {[file size test.db-wal] > [expr 1032*9000]} 774 } 1 775 776 do_test wal3-10.$tn.2 { 777 sql2 {PRAGMA integrity_check} 778 } {ok} 779} 780 781finish_test 782 783