1 # 2010 June 15 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 # 12 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 source $testdir/lock_common.tcl 16 source $testdir/malloc_common.tcl 17 source $testdir/wal_common.tcl 18 19 # Do not use a codec for tests in this file, as the database file is 20 # manipulated directly using tcl scripts (using the [hexio_write] command). 21 # 22 do_not_use_codec 23 24 # 25 # pager1-1.*: Test inter-process locking (clients in multiple processes). 26 # 27 # pager1-2.*: Test intra-process locking (multiple clients in this process). 28 # 29 # pager1-3.*: Savepoint related tests. 30 # 31 # pager1-4.*: Hot-journal related tests. 32 # 33 # pager1-5.*: Cases related to multi-file commits. 34 # 35 # pager1-6.*: Cases related to "PRAGMA max_page_count" 36 # 37 # pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE" 38 # 39 # pager1-8.*: Cases using temporary and in-memory databases. 40 # 41 # pager1-9.*: Tests related to the backup API. 42 # 43 # pager1-10.*: Test that the assumed file-system sector-size is limited to 44 # 64KB. 45 # 46 # pager1-12.*: Tests involving "PRAGMA page_size" 47 # 48 # pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST" 49 # 50 # pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF" 51 # 52 # pager1-15.*: Varying sqlite3_vfs.szOsFile 53 # 54 # pager1-16.*: Varying sqlite3_vfs.mxPathname 55 # 56 # pager1-17.*: Tests related to "PRAGMA omit_readlock" 57 # 58 # pager1-18.*: Test that the pager layer responds correctly if the b-tree 59 # requests an invalid page number (due to db corruption). 60 # 61 62 proc recursive_select {id table {script {}}} { 63 set cnt 0 64 db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" { 65 recursive_select $rowid $table $script 66 incr cnt 67 } 68 if {$cnt==0} { eval $script } 69 } 70 71 set a_string_counter 1 72 proc a_string {n} { 73 global a_string_counter 74 incr a_string_counter 75 string range [string repeat "${a_string_counter}." $n] 1 $n 76 } 77 db func a_string a_string 78 79 do_multiclient_test tn { 80 81 # Create and populate a database table using connection [db]. Check 82 # that connections [db2] and [db3] can see the schema and content. 83 # 84 do_test pager1-$tn.1 { 85 sql1 { 86 CREATE TABLE t1(a PRIMARY KEY, b); 87 CREATE INDEX i1 ON t1(b); 88 INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); 89 } 90 } {} 91 do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two} 92 do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two} 93 94 # Open a transaction and add a row using [db]. This puts [db] in 95 # RESERVED state. Check that connections [db2] and [db3] can still 96 # read the database content as it was before the transaction was 97 # opened. [db] should see the inserted row. 98 # 99 do_test pager1-$tn.4 { 100 sql1 { 101 BEGIN; 102 INSERT INTO t1 VALUES(3, 'three'); 103 } 104 } {} 105 do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two} 106 do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} 107 108 # [db] still has an open write transaction. Check that this prevents 109 # other connections (specifically [db2]) from writing to the database. 110 # 111 # Even if [db2] opens a transaction first, it may not write to the 112 # database. After the attempt to write the db within a transaction, 113 # [db2] is left with an open transaction, but not a read-lock on 114 # the main database. So it does not prevent [db] from committing. 115 # 116 do_test pager1-$tn.8 { 117 csql2 { UPDATE t1 SET a = a + 10 } 118 } {1 {database is locked}} 119 do_test pager1-$tn.9 { 120 csql2 { 121 BEGIN; 122 UPDATE t1 SET a = a + 10; 123 } 124 } {1 {database is locked}} 125 126 # Have [db] commit its transactions. Check the other connections can 127 # now see the new database content. 128 # 129 do_test pager1-$tn.10 { sql1 { COMMIT } } {} 130 do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} 131 do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} 132 do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} 133 134 # Check that, as noted above, [db2] really did keep an open transaction 135 # after the attempt to write the database failed. 136 # 137 do_test pager1-$tn.14 { 138 csql2 { BEGIN } 139 } {1 {cannot start a transaction within a transaction}} 140 do_test pager1-$tn.15 { sql2 { ROLLBACK } } {} 141 142 # Have [db2] open a transaction and take a read-lock on the database. 143 # Check that this prevents [db] from writing to the database (outside 144 # of any transaction). After this fails, check that [db3] can read 145 # the db (showing that [db] did not take a PENDING lock etc.) 146 # 147 do_test pager1-$tn.15 { 148 sql2 { BEGIN; SELECT * FROM t1; } 149 } {1 one 2 two 3 three} 150 do_test pager1-$tn.16 { 151 csql1 { UPDATE t1 SET a = a + 10 } 152 } {1 {database is locked}} 153 do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} 154 155 # This time, have [db] open a transaction before writing the database. 156 # This works - [db] gets a RESERVED lock which does not conflict with 157 # the SHARED lock [db2] is holding. 158 # 159 do_test pager1-$tn.18 { 160 sql1 { 161 BEGIN; 162 UPDATE t1 SET a = a + 10; 163 } 164 } {} 165 do_test pager1-$tn-19 { 166 sql1 { PRAGMA lock_status } 167 } {main reserved temp closed} 168 do_test pager1-$tn-20 { 169 sql2 { PRAGMA lock_status } 170 } {main shared temp closed} 171 172 # Check that all connections can still read the database. Only [db] sees 173 # the updated content (as the transaction has not been committed yet). 174 # 175 do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} 176 do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} 177 do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} 178 179 # Because [db2] still has the SHARED lock, [db] is unable to commit the 180 # transaction. If it tries, an error is returned and the connection 181 # upgrades to a PENDING lock. 182 # 183 # Once this happens, [db] can read the database and see the new content, 184 # [db2] (still holding SHARED) can still read the old content, but [db3] 185 # (not holding any lock) is prevented by [db]'s PENDING from reading 186 # the database. 187 # 188 do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}} 189 do_test pager1-$tn-25 { 190 sql1 { PRAGMA lock_status } 191 } {main pending temp closed} 192 do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} 193 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} 194 do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} 195 196 # Have [db2] commit its read transaction, releasing the SHARED lock it 197 # is holding. Now, neither [db2] nor [db3] may read the database (as [db] 198 # is still holding a PENDING). 199 # 200 do_test pager1-$tn.29 { sql2 { COMMIT } } {} 201 do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}} 202 do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} 203 204 # [db] is now able to commit the transaction. Once the transaction is 205 # committed, all three connections can read the new content. 206 # 207 do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {} 208 do_test pager1-$tn.26 { sql1 { COMMIT } } {} 209 do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three} 210 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three} 211 do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three} 212 213 # Install a busy-handler for connection [db]. 214 # 215 set ::nbusy [list] 216 proc busy {n} { 217 lappend ::nbusy $n 218 if {$n>5} { sql2 COMMIT } 219 return 0 220 } 221 db busy busy 222 223 do_test pager1-$tn.29 { 224 sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') } 225 } {} 226 do_test pager1-$tn.30 { 227 sql2 { BEGIN ; SELECT * FROM t1 } 228 } {21 one 22 two 23 three} 229 do_test pager1-$tn.31 { sql1 COMMIT } {} 230 do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6} 231 } 232 233 #------------------------------------------------------------------------- 234 # Savepoint related test cases. 235 # 236 # pager1-3.1.2.*: Force a savepoint rollback to cause the database file 237 # to grow. 238 # 239 # pager1-3.1.3.*: Use a journal created in synchronous=off mode as part 240 # of a savepoint rollback. 241 # 242 do_test pager1-3.1.1 { 243 faultsim_delete_and_reopen 244 execsql { 245 CREATE TABLE t1(a PRIMARY KEY, b); 246 CREATE TABLE counter( 247 i CHECK (i<5), 248 u CHECK (u<10) 249 ); 250 INSERT INTO counter VALUES(0, 0); 251 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN 252 UPDATE counter SET i = i+1; 253 END; 254 CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN 255 UPDATE counter SET u = u+1; 256 END; 257 } 258 execsql { SELECT * FROM counter } 259 } {0 0} 260 261 do_execsql_test pager1-3.1.2 { 262 PRAGMA cache_size = 10; 263 BEGIN; 264 INSERT INTO t1 VALUES(1, randomblob(1500)); 265 INSERT INTO t1 VALUES(2, randomblob(1500)); 266 INSERT INTO t1 VALUES(3, randomblob(1500)); 267 SELECT * FROM counter; 268 } {3 0} 269 do_catchsql_test pager1-3.1.3 { 270 INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1 271 } {1 {constraint failed}} 272 do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0} 273 do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3} 274 do_execsql_test pager1-3.6 { COMMIT } {} 275 276 foreach {tn sql tcl} { 277 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } { 278 testvfs tv -default 1 279 tv devchar safe_append 280 } 281 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } { 282 testvfs tv -default 1 283 tv devchar sequential 284 } 285 9 { PRAGMA synchronous = FULL } { } 286 10 { PRAGMA synchronous = NORMAL } { } 287 11 { PRAGMA synchronous = OFF } { } 288 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { } 289 13 { PRAGMA synchronous = FULL } { 290 testvfs tv -default 1 291 tv devchar sequential 292 } 293 14 { PRAGMA locking_mode = EXCLUSIVE } { 294 } 295 } { 296 do_test pager1-3.$tn.1 { 297 eval $tcl 298 faultsim_delete_and_reopen 299 db func a_string a_string 300 execsql $sql 301 execsql { 302 PRAGMA auto_vacuum = 2; 303 PRAGMA cache_size = 10; 304 CREATE TABLE z(x INTEGER PRIMARY KEY, y); 305 BEGIN; 306 INSERT INTO z VALUES(NULL, a_string(800)); 307 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2 308 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4 309 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8 310 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16 311 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32 312 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64 313 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128 314 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256 315 COMMIT; 316 } 317 execsql { PRAGMA auto_vacuum } 318 } {2} 319 do_execsql_test pager1-3.$tn.2 { 320 BEGIN; 321 INSERT INTO z VALUES(NULL, a_string(800)); 322 INSERT INTO z VALUES(NULL, a_string(800)); 323 SAVEPOINT one; 324 UPDATE z SET y = NULL WHERE x>256; 325 PRAGMA incremental_vacuum; 326 SELECT count(*) FROM z WHERE x < 100; 327 ROLLBACK TO one; 328 COMMIT; 329 } {99} 330 331 do_execsql_test pager1-3.$tn.3 { 332 BEGIN; 333 SAVEPOINT one; 334 UPDATE z SET y = y||x; 335 ROLLBACK TO one; 336 COMMIT; 337 SELECT count(*) FROM z; 338 } {258} 339 340 do_execsql_test pager1-3.$tn.4 { 341 SAVEPOINT one; 342 UPDATE z SET y = y||x; 343 ROLLBACK TO one; 344 } {} 345 do_execsql_test pager1-3.$tn.5 { 346 SELECT count(*) FROM z; 347 RELEASE one; 348 PRAGMA integrity_check; 349 } {258 ok} 350 351 do_execsql_test pager1-3.$tn.6 { 352 SAVEPOINT one; 353 RELEASE one; 354 } {} 355 356 db close 357 catch { tv delete } 358 } 359 360 #------------------------------------------------------------------------- 361 # Hot journal rollback related test cases. 362 # 363 # pager1.4.1.*: Test that the pager module deletes very small invalid 364 # journal files. 365 # 366 # pager1.4.2.*: Test that if the master journal pointer at the end of a 367 # hot-journal file appears to be corrupt (checksum does not 368 # compute) the associated journal is rolled back (and no 369 # xAccess() call to check for the presence of any master 370 # journal file is made). 371 # 372 # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the 373 # page-size or sector-size in the journal header appear to 374 # be invalid (too large, too small or not a power of 2). 375 # 376 # pager1.4.4.*: Test hot-journal rollback of journal file with a master 377 # journal pointer generated in various "PRAGMA synchronous" 378 # modes. 379 # 380 # pager1.4.5.*: Test that hot-journal rollback stops if it encounters a 381 # journal-record for which the checksum fails. 382 # 383 # pager1.4.6.*: Test that when rolling back a hot-journal that contains a 384 # master journal pointer, the master journal file is deleted 385 # after all the hot-journals that refer to it are deleted. 386 # 387 # pager1.4.7.*: Test that if a hot-journal file exists but a client can 388 # open it for reading only, the database cannot be accessed and 389 # SQLITE_CANTOPEN is returned. 390 # 391 do_test pager1.4.1.1 { 392 faultsim_delete_and_reopen 393 execsql { 394 CREATE TABLE x(y, z); 395 INSERT INTO x VALUES(1, 2); 396 } 397 set fd [open test.db-journal w] 398 puts -nonewline $fd "helloworld" 399 close $fd 400 file exists test.db-journal 401 } {1} 402 do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2} 403 do_test pager1.4.1.3 { file exists test.db-journal } {0} 404 405 # Set up a [testvfs] to snapshot the file-system just before SQLite 406 # deletes the master-journal to commit a multi-file transaction. 407 # 408 # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets 409 # up the file system to contain two databases, two hot-journal files and 410 # a master-journal. 411 # 412 do_test pager1.4.2.1 { 413 testvfs tstvfs -default 1 414 tstvfs filter xDelete 415 tstvfs script xDeleteCallback 416 proc xDeleteCallback {method file args} { 417 set file [file tail $file] 418 if { [string match *mj* $file] } { faultsim_save } 419 } 420 faultsim_delete_and_reopen 421 db func a_string a_string 422 execsql { 423 ATTACH 'test.db2' AS aux; 424 PRAGMA journal_mode = DELETE; 425 PRAGMA main.cache_size = 10; 426 PRAGMA aux.cache_size = 10; 427 CREATE TABLE t1(a UNIQUE, b UNIQUE); 428 CREATE TABLE aux.t2(a UNIQUE, b UNIQUE); 429 INSERT INTO t1 VALUES(a_string(200), a_string(300)); 430 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; 431 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; 432 INSERT INTO t2 SELECT * FROM t1; 433 BEGIN; 434 INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1; 435 INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1; 436 INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1; 437 INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1; 438 REPLACE INTO t2 SELECT * FROM t1; 439 COMMIT; 440 } 441 db close 442 tstvfs delete 443 } {} 444 do_test pager1.4.2.2 { 445 faultsim_restore_and_reopen 446 execsql { 447 SELECT count(*) FROM t1; 448 PRAGMA integrity_check; 449 } 450 } {4 ok} 451 do_test pager1.4.2.3 { 452 faultsim_restore_and_reopen 453 foreach f [glob test.db-mj*] { file delete -force $f } 454 execsql { 455 SELECT count(*) FROM t1; 456 PRAGMA integrity_check; 457 } 458 } {64 ok} 459 do_test pager1.4.2.4 { 460 faultsim_restore_and_reopen 461 hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 462 execsql { 463 SELECT count(*) FROM t1; 464 PRAGMA integrity_check; 465 } 466 } {4 ok} 467 do_test pager1.4.2.5 { 468 faultsim_restore_and_reopen 469 hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 470 foreach f [glob test.db-mj*] { file delete -force $f } 471 execsql { 472 SELECT count(*) FROM t1; 473 PRAGMA integrity_check; 474 } 475 } {4 ok} 476 477 do_test pager1.4.3.1 { 478 testvfs tstvfs -default 1 479 tstvfs filter xSync 480 tstvfs script xSyncCallback 481 proc xSyncCallback {method file args} { 482 set file [file tail $file] 483 if { 0==[string match *journal $file] } { faultsim_save } 484 } 485 faultsim_delete_and_reopen 486 execsql { 487 PRAGMA journal_mode = DELETE; 488 CREATE TABLE t1(a, b); 489 INSERT INTO t1 VALUES(1, 2); 490 INSERT INTO t1 VALUES(3, 4); 491 } 492 db close 493 tstvfs delete 494 } {} 495 496 foreach {tn ofst value result} { 497 2 20 31 {1 2 3 4} 498 3 20 32 {1 2 3 4} 499 4 20 33 {1 2 3 4} 500 5 20 65536 {1 2 3 4} 501 6 20 131072 {1 2 3 4} 502 503 7 24 511 {1 2 3 4} 504 8 24 513 {1 2 3 4} 505 9 24 131072 {1 2 3 4} 506 507 10 32 65536 {1 2} 508 } { 509 do_test pager1.4.3.$tn { 510 faultsim_restore_and_reopen 511 hexio_write test.db-journal $ofst [format %.8x $value] 512 execsql { SELECT * FROM t1 } 513 } $result 514 } 515 db close 516 517 # Set up a VFS that snapshots the file-system just before a master journal 518 # file is deleted to commit a multi-file transaction. Specifically, the 519 # file-system is saved just before the xDelete() call to remove the 520 # master journal file from the file-system. 521 # 522 testvfs tv -default 1 523 tv script copy_on_mj_delete 524 set ::mj_filename_length 0 525 proc copy_on_mj_delete {method filename args} { 526 if {[string match *mj* [file tail $filename]]} { 527 set ::mj_filename_length [string length $filename] 528 faultsim_save 529 } 530 return SQLITE_OK 531 } 532 533 set pwd [pwd] 534 foreach {tn1 tcl} { 535 1 { set prefix "test.db" } 536 2 { 537 # This test depends on the underlying VFS being able to open paths 538 # 512 bytes in length. The idea is to create a hot-journal file that 539 # contains a master-journal pointer so large that it could contain 540 # a valid page record (if the file page-size is 512 bytes). So as to 541 # make sure SQLite doesn't get confused by this. 542 # 543 set nPadding [expr 511 - $::mj_filename_length] 544 if {$tcl_platform(platform)=="windows"} { 545 # TBD need to figure out how to do this correctly for Windows!!! 546 set nPadding [expr 255 - $::mj_filename_length] 547 } 548 549 # We cannot just create a really long database file name to open, as 550 # Linux limits a single component of a path to 255 bytes by default 551 # (and presumably other systems have limits too). So create a directory 552 # hierarchy to work in. 553 # 554 set dirname "d123456789012345678901234567890/" 555 set nDir [expr $nPadding / 32] 556 if { $nDir } { 557 set p [string repeat $dirname $nDir] 558 file mkdir $p 559 cd $p 560 } 561 562 set padding [string repeat x [expr $nPadding %32]] 563 set prefix "test.db${padding}" 564 } 565 } { 566 eval $tcl 567 foreach {tn2 sql} { 568 o { 569 PRAGMA main.synchronous=OFF; 570 PRAGMA aux.synchronous=OFF; 571 PRAGMA journal_mode = DELETE; 572 } 573 o512 { 574 PRAGMA main.synchronous=OFF; 575 PRAGMA aux.synchronous=OFF; 576 PRAGMA main.page_size = 512; 577 PRAGMA aux.page_size = 512; 578 PRAGMA journal_mode = DELETE; 579 } 580 n { 581 PRAGMA main.synchronous=NORMAL; 582 PRAGMA aux.synchronous=NORMAL; 583 PRAGMA journal_mode = DELETE; 584 } 585 f { 586 PRAGMA main.synchronous=FULL; 587 PRAGMA aux.synchronous=FULL; 588 PRAGMA journal_mode = DELETE; 589 } 590 } { 591 592 set tn "${tn1}.${tn2}" 593 594 # Set up a connection to have two databases, test.db (main) and 595 # test.db2 (aux). Then run a multi-file transaction on them. The 596 # VFS will snapshot the file-system just before the master-journal 597 # file is deleted to commit the transaction. 598 # 599 tv filter xDelete 600 do_test pager1-4.4.$tn.1 { 601 faultsim_delete_and_reopen $prefix 602 execsql " 603 ATTACH '${prefix}2' AS aux; 604 $sql 605 CREATE TABLE a(x); 606 CREATE TABLE aux.b(x); 607 INSERT INTO a VALUES('double-you'); 608 INSERT INTO a VALUES('why'); 609 INSERT INTO a VALUES('zed'); 610 INSERT INTO b VALUES('won'); 611 INSERT INTO b VALUES('too'); 612 INSERT INTO b VALUES('free'); 613 " 614 execsql { 615 BEGIN; 616 INSERT INTO a SELECT * FROM b WHERE rowid<=3; 617 INSERT INTO b SELECT * FROM a WHERE rowid<=3; 618 COMMIT; 619 } 620 } {} 621 tv filter {} 622 623 # Check that the transaction was committed successfully. 624 # 625 do_execsql_test pager1-4.4.$tn.2 { 626 SELECT * FROM a 627 } {double-you why zed won too free} 628 do_execsql_test pager1-4.4.$tn.3 { 629 SELECT * FROM b 630 } {won too free double-you why zed} 631 632 # Restore the file-system and reopen the databases. Check that it now 633 # appears that the transaction was not committed (because the file-system 634 # was restored to the state where it had not been). 635 # 636 do_test pager1-4.4.$tn.4 { 637 faultsim_restore_and_reopen $prefix 638 execsql "ATTACH '${prefix}2' AS aux" 639 } {} 640 do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed} 641 do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free} 642 643 # Restore the file-system again. This time, before reopening the databases, 644 # delete the master-journal file from the file-system. It now appears that 645 # the transaction was committed (no master-journal file == no rollback). 646 # 647 do_test pager1-4.4.$tn.7 { 648 faultsim_restore_and_reopen $prefix 649 foreach f [glob ${prefix}-mj*] { file delete -force $f } 650 execsql "ATTACH '${prefix}2' AS aux" 651 } {} 652 do_execsql_test pager1-4.4.$tn.8 { 653 SELECT * FROM a 654 } {double-you why zed won too free} 655 do_execsql_test pager1-4.4.$tn.9 { 656 SELECT * FROM b 657 } {won too free double-you why zed} 658 } 659 660 cd $pwd 661 } 662 db close 663 tv delete 664 file delete -force $dirname 665 666 667 # Set up a VFS to make a copy of the file-system just before deleting a 668 # journal file to commit a transaction. The transaction modifies exactly 669 # two database pages (and page 1 - the change counter). 670 # 671 testvfs tv -default 1 672 tv sectorsize 512 673 tv script copy_on_journal_delete 674 tv filter xDelete 675 proc copy_on_journal_delete {method filename args} { 676 if {[string match *journal $filename]} faultsim_save 677 return SQLITE_OK 678 } 679 faultsim_delete_and_reopen 680 do_execsql_test pager1.4.5.1 { 681 PRAGMA journal_mode = DELETE; 682 PRAGMA page_size = 1024; 683 CREATE TABLE t1(a, b); 684 CREATE TABLE t2(a, b); 685 INSERT INTO t1 VALUES('I', 'II'); 686 INSERT INTO t2 VALUES('III', 'IV'); 687 BEGIN; 688 INSERT INTO t1 VALUES(1, 2); 689 INSERT INTO t2 VALUES(3, 4); 690 COMMIT; 691 } {delete} 692 tv filter {} 693 694 # Check the transaction was committed: 695 # 696 do_execsql_test pager1.4.5.2 { 697 SELECT * FROM t1; 698 SELECT * FROM t2; 699 } {I II 1 2 III IV 3 4} 700 701 # Now try four tests: 702 # 703 # pager1-4.5.3: Restore the file-system. Check that the whole transaction 704 # is rolled back. 705 # 706 # pager1-4.5.4: Restore the file-system. Corrupt the first record in the 707 # journal. Check the transaction is not rolled back. 708 # 709 # pager1-4.5.5: Restore the file-system. Corrupt the second record in the 710 # journal. Check that the first record in the transaction is 711 # played back, but not the second. 712 # 713 # pager1-4.5.6: Restore the file-system. Try to open the database with a 714 # readonly connection. This should fail, as a read-only 715 # connection cannot roll back the database file. 716 # 717 faultsim_restore_and_reopen 718 do_execsql_test pager1.4.5.3 { 719 SELECT * FROM t1; 720 SELECT * FROM t2; 721 } {I II III IV} 722 faultsim_restore_and_reopen 723 hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF 724 do_execsql_test pager1.4.5.4 { 725 SELECT * FROM t1; 726 SELECT * FROM t2; 727 } {I II 1 2 III IV 3 4} 728 faultsim_restore_and_reopen 729 hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF 730 do_execsql_test pager1.4.5.5 { 731 SELECT * FROM t1; 732 SELECT * FROM t2; 733 } {I II III IV 3 4} 734 735 faultsim_restore_and_reopen 736 db close 737 sqlite3 db test.db -readonly 1 738 do_catchsql_test pager1.4.5.6 { 739 SELECT * FROM t1; 740 SELECT * FROM t2; 741 } {1 {disk I/O error}} 742 db close 743 744 # Snapshot the file-system just before multi-file commit. Save the name 745 # of the master journal file in $::mj_filename. 746 # 747 tv script copy_on_mj_delete 748 tv filter xDelete 749 proc copy_on_mj_delete {method filename args} { 750 if {[string match *mj* [file tail $filename]]} { 751 set ::mj_filename $filename 752 faultsim_save 753 } 754 return SQLITE_OK 755 } 756 do_test pager1.4.6.1 { 757 faultsim_delete_and_reopen 758 execsql { 759 PRAGMA journal_mode = DELETE; 760 ATTACH 'test.db2' AS two; 761 CREATE TABLE t1(a, b); 762 CREATE TABLE two.t2(a, b); 763 INSERT INTO t1 VALUES(1, 't1.1'); 764 INSERT INTO t2 VALUES(1, 't2.1'); 765 BEGIN; 766 UPDATE t1 SET b = 't1.2'; 767 UPDATE t2 SET b = 't2.2'; 768 COMMIT; 769 } 770 tv filter {} 771 db close 772 } {} 773 774 faultsim_restore_and_reopen 775 do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1} 776 do_test pager1.4.6.3 { file exists $::mj_filename } {1} 777 do_execsql_test pager1.4.6.4 { 778 ATTACH 'test.db2' AS two; 779 SELECT * FROM t2; 780 } {1 t2.1} 781 do_test pager1.4.6.5 { file exists $::mj_filename } {0} 782 783 faultsim_restore_and_reopen 784 db close 785 do_test pager1.4.6.8 { 786 set ::mj_filename1 $::mj_filename 787 tv filter xDelete 788 sqlite3 db test.db2 789 execsql { 790 PRAGMA journal_mode = DELETE; 791 ATTACH 'test.db3' AS three; 792 CREATE TABLE three.t3(a, b); 793 INSERT INTO t3 VALUES(1, 't3.1'); 794 BEGIN; 795 UPDATE t2 SET b = 't2.3'; 796 UPDATE t3 SET b = 't3.3'; 797 COMMIT; 798 } 799 expr {$::mj_filename1 != $::mj_filename} 800 } {1} 801 faultsim_restore_and_reopen 802 tv filter {} 803 804 # The file-system now contains: 805 # 806 # * three databases 807 # * three hot-journal files 808 # * two master-journal files. 809 # 810 # The hot-journals associated with test.db2 and test.db3 point to 811 # master journal $::mj_filename. The hot-journal file associated with 812 # test.db points to master journal $::mj_filename1. So reading from 813 # test.db should delete $::mj_filename1. 814 # 815 do_test pager1.4.6.9 { 816 lsort [glob test.db*] 817 } [lsort [list \ 818 test.db test.db2 test.db3 \ 819 test.db-journal test.db2-journal test.db3-journal \ 820 [file tail $::mj_filename] [file tail $::mj_filename1] 821 ]] 822 823 # The master-journal $::mj_filename1 contains pointers to test.db and 824 # test.db2. However the hot-journal associated with test.db2 points to 825 # a different master-journal. Therefore, reading from test.db only should 826 # be enough to cause SQLite to delete $::mj_filename1. 827 # 828 do_test pager1.4.6.10 { file exists $::mj_filename } {1} 829 do_test pager1.4.6.11 { file exists $::mj_filename1 } {1} 830 do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1} 831 do_test pager1.4.6.13 { file exists $::mj_filename } {1} 832 do_test pager1.4.6.14 { file exists $::mj_filename1 } {0} 833 834 do_execsql_test pager1.4.6.12 { 835 ATTACH 'test.db2' AS two; 836 SELECT * FROM t2; 837 } {1 t2.1} 838 do_test pager1.4.6.13 { file exists $::mj_filename } {1} 839 do_execsql_test pager1.4.6.14 { 840 ATTACH 'test.db3' AS three; 841 SELECT * FROM t3; 842 } {1 t3.1} 843 do_test pager1.4.6.15 { file exists $::mj_filename } {0} 844 845 db close 846 tv delete 847 848 testvfs tv -default 1 849 tv sectorsize 512 850 tv script copy_on_journal_delete 851 tv filter xDelete 852 proc copy_on_journal_delete {method filename args} { 853 if {[string match *journal $filename]} faultsim_save 854 return SQLITE_OK 855 } 856 faultsim_delete_and_reopen 857 do_execsql_test pager1.4.7.1 { 858 PRAGMA journal_mode = DELETE; 859 CREATE TABLE t1(x PRIMARY KEY, y); 860 CREATE INDEX i1 ON t1(y); 861 INSERT INTO t1 VALUES('I', 'one'); 862 INSERT INTO t1 VALUES('II', 'four'); 863 INSERT INTO t1 VALUES('III', 'nine'); 864 BEGIN; 865 INSERT INTO t1 VALUES('IV', 'sixteen'); 866 INSERT INTO t1 VALUES('V' , 'twentyfive'); 867 COMMIT; 868 } {delete} 869 tv filter {} 870 db close 871 tv delete 872 do_test pager1.4.7.2 { 873 faultsim_restore_and_reopen 874 catch {file attributes test.db-journal -permissions r--------} 875 catch {file attributes test.db-journal -readonly 1} 876 catchsql { SELECT * FROM t1 } 877 } {1 {unable to open database file}} 878 do_test pager1.4.7.3 { 879 db close 880 catch {file attributes test.db-journal -permissions rw-rw-rw-} 881 catch {file attributes test.db-journal -readonly 0} 882 file delete test.db-journal 883 file exists test.db-journal 884 } {0} 885 886 #------------------------------------------------------------------------- 887 # The following tests deal with multi-file commits. 888 # 889 # pager1-5.1.*: The case where a multi-file cannot be committed because 890 # another connection is holding a SHARED lock on one of the 891 # files. After the SHARED lock is removed, the COMMIT succeeds. 892 # 893 # pager1-5.2.*: Multi-file commits with journal_mode=memory. 894 # 895 # pager1-5.3.*: Multi-file commits with journal_mode=memory. 896 # 897 # pager1-5.4.*: Check that with synchronous=normal, the master-journal file 898 # name is added to a journal file immediately after the last 899 # journal record. But with synchronous=full, extra unused space 900 # is allocated between the last journal record and the 901 # master-journal file name so that the master-journal file 902 # name does not lie on the same sector as the last journal file 903 # record. 904 # 905 # pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is 906 # truncated to zero bytes when a multi-file transaction is 907 # committed (instead of the first couple of bytes being zeroed). 908 # 909 # 910 do_test pager1-5.1.1 { 911 faultsim_delete_and_reopen 912 execsql { 913 ATTACH 'test.db2' AS aux; 914 CREATE TABLE t1(a, b); 915 CREATE TABLE aux.t2(a, b); 916 INSERT INTO t1 VALUES(17, 'Lenin'); 917 INSERT INTO t1 VALUES(22, 'Stalin'); 918 INSERT INTO t1 VALUES(53, 'Khrushchev'); 919 } 920 } {} 921 do_test pager1-5.1.2 { 922 execsql { 923 BEGIN; 924 INSERT INTO t1 VALUES(64, 'Brezhnev'); 925 INSERT INTO t2 SELECT * FROM t1; 926 } 927 sqlite3 db2 test.db2 928 execsql { 929 BEGIN; 930 SELECT * FROM t2; 931 } db2 932 } {} 933 do_test pager1-5.1.3 { 934 catchsql COMMIT 935 } {1 {database is locked}} 936 do_test pager1-5.1.4 { 937 execsql COMMIT db2 938 execsql COMMIT 939 execsql { SELECT * FROM t2 } db2 940 } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev} 941 do_test pager1-5.1.5 { 942 db2 close 943 } {} 944 945 do_test pager1-5.2.1 { 946 execsql { 947 PRAGMA journal_mode = memory; 948 BEGIN; 949 INSERT INTO t1 VALUES(84, 'Andropov'); 950 INSERT INTO t2 VALUES(84, 'Andropov'); 951 COMMIT; 952 } 953 } {memory} 954 do_test pager1-5.3.1 { 955 execsql { 956 PRAGMA journal_mode = off; 957 BEGIN; 958 INSERT INTO t1 VALUES(85, 'Gorbachev'); 959 INSERT INTO t2 VALUES(85, 'Gorbachev'); 960 COMMIT; 961 } 962 } {off} 963 964 do_test pager1-5.4.1 { 965 db close 966 testvfs tv 967 sqlite3 db test.db -vfs tv 968 execsql { ATTACH 'test.db2' AS aux } 969 970 tv filter xDelete 971 tv script max_journal_size 972 tv sectorsize 512 973 set ::max_journal 0 974 proc max_journal_size {method args} { 975 set sz 0 976 catch { set sz [file size test.db-journal] } 977 if {$sz > $::max_journal} { 978 set ::max_journal $sz 979 } 980 return SQLITE_OK 981 } 982 execsql { 983 PRAGMA journal_mode = DELETE; 984 PRAGMA synchronous = NORMAL; 985 BEGIN; 986 INSERT INTO t1 VALUES(85, 'Gorbachev'); 987 INSERT INTO t2 VALUES(85, 'Gorbachev'); 988 COMMIT; 989 } 990 set ::max_journal 991 } [expr 2615+[string length [pwd]]] 992 do_test pager1-5.4.2 { 993 set ::max_journal 0 994 execsql { 995 PRAGMA synchronous = full; 996 BEGIN; 997 DELETE FROM t1 WHERE b = 'Lenin'; 998 DELETE FROM t2 WHERE b = 'Lenin'; 999 COMMIT; 1000 } 1001 set ::max_journal 1002 } [expr 3111+[string length [pwd]]] 1003 db close 1004 tv delete 1005 1006 do_test pager1-5.5.1 { 1007 sqlite3 db test.db 1008 execsql { 1009 ATTACH 'test.db2' AS aux; 1010 PRAGMA journal_mode = PERSIST; 1011 CREATE TABLE t3(a, b); 1012 INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1; 1013 UPDATE t3 SET b = randomblob(1500); 1014 } 1015 expr [file size test.db-journal] > 15000 1016 } {1} 1017 do_test pager1-5.5.2 { 1018 execsql { 1019 PRAGMA synchronous = full; 1020 BEGIN; 1021 DELETE FROM t1 WHERE b = 'Stalin'; 1022 DELETE FROM t2 WHERE b = 'Stalin'; 1023 COMMIT; 1024 } 1025 file size test.db-journal 1026 } {0} 1027 1028 1029 #------------------------------------------------------------------------- 1030 # The following tests work with "PRAGMA max_page_count" 1031 # 1032 do_test pager1-6.1 { 1033 faultsim_delete_and_reopen 1034 execsql { 1035 PRAGMA auto_vacuum = none; 1036 PRAGMA max_page_count = 10; 1037 CREATE TABLE t2(a, b); 1038 CREATE TABLE t3(a, b); 1039 CREATE TABLE t4(a, b); 1040 CREATE TABLE t5(a, b); 1041 CREATE TABLE t6(a, b); 1042 CREATE TABLE t7(a, b); 1043 CREATE TABLE t8(a, b); 1044 CREATE TABLE t9(a, b); 1045 CREATE TABLE t10(a, b); 1046 } 1047 } {10} 1048 do_catchsql_test pager1-6.2 { 1049 CREATE TABLE t11(a, b) 1050 } {1 {database or disk is full}} 1051 do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10} 1052 do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15} 1053 do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {} 1054 do_execsql_test pager1-6.7 { 1055 BEGIN; 1056 INSERT INTO t11 VALUES(1, 2); 1057 PRAGMA max_page_count = 13; 1058 } {13} 1059 do_execsql_test pager1-6.8 { 1060 INSERT INTO t11 VALUES(3, 4); 1061 PRAGMA max_page_count = 10; 1062 } {11} 1063 do_execsql_test pager1-6.9 { COMMIT } {} 1064 1065 do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11} 1066 do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4} 1067 do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11} 1068 1069 1070 #------------------------------------------------------------------------- 1071 # The following tests work with "PRAGMA journal_mode=TRUNCATE" and 1072 # "PRAGMA locking_mode=EXCLUSIVE". 1073 # 1074 # Each test is specified with 5 variables. As follows: 1075 # 1076 # $tn: Test Number. Used as part of the [do_test] test names. 1077 # $sql: SQL to execute. 1078 # $res: Expected result of executing $sql. 1079 # $js: The expected size of the journal file, in bytes, after executing 1080 # the SQL script. Or -1 if the journal is not expected to exist. 1081 # $ws: The expected size of the WAL file, in bytes, after executing 1082 # the SQL script. Or -1 if the WAL is not expected to exist. 1083 # 1084 ifcapable wal { 1085 faultsim_delete_and_reopen 1086 foreach {tn sql res js ws} [subst { 1087 1088 1 { 1089 CREATE TABLE t1(a, b); 1090 PRAGMA auto_vacuum=OFF; 1091 PRAGMA synchronous=NORMAL; 1092 PRAGMA page_size=1024; 1093 PRAGMA locking_mode=EXCLUSIVE; 1094 PRAGMA journal_mode=TRUNCATE; 1095 INSERT INTO t1 VALUES(1, 2); 1096 } {exclusive truncate} 0 -1 1097 1098 2 { 1099 BEGIN IMMEDIATE; 1100 SELECT * FROM t1; 1101 COMMIT; 1102 } {1 2} 0 -1 1103 1104 3 { 1105 BEGIN; 1106 SELECT * FROM t1; 1107 COMMIT; 1108 } {1 2} 0 -1 1109 1110 4 { PRAGMA journal_mode = WAL } wal -1 -1 1111 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024] 1112 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024] 1113 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024] 1114 1115 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1 1116 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1 1117 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1 1118 1119 }] { 1120 do_execsql_test pager1-7.1.$tn.1 $sql $res 1121 catch { set J -1 ; set J [file size test.db-journal] } 1122 catch { set W -1 ; set W [file size test.db-wal] } 1123 do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws] 1124 } 1125 } 1126 1127 do_test pager1-7.2.1 { 1128 faultsim_delete_and_reopen 1129 execsql { 1130 PRAGMA locking_mode = EXCLUSIVE; 1131 CREATE TABLE t1(a, b); 1132 BEGIN; 1133 PRAGMA journal_mode = delete; 1134 PRAGMA journal_mode = truncate; 1135 } 1136 } {exclusive delete truncate} 1137 do_test pager1-7.2.2 { 1138 execsql { INSERT INTO t1 VALUES(1, 2) } 1139 execsql { PRAGMA journal_mode = persist } 1140 } {truncate} 1141 do_test pager1-7.2.3 { 1142 execsql { COMMIT } 1143 execsql { 1144 PRAGMA journal_mode = persist; 1145 PRAGMA journal_size_limit; 1146 } 1147 } {persist -1} 1148 1149 #------------------------------------------------------------------------- 1150 # The following tests, pager1-8.*, test that the special filenames 1151 # ":memory:" and "" open temporary databases. 1152 # 1153 foreach {tn filename} { 1154 1 :memory: 1155 2 "" 1156 } { 1157 do_test pager1-8.$tn.1 { 1158 faultsim_delete_and_reopen 1159 db close 1160 sqlite3 db $filename 1161 execsql { 1162 PRAGMA auto_vacuum = 1; 1163 CREATE TABLE x1(x); 1164 INSERT INTO x1 VALUES('Charles'); 1165 INSERT INTO x1 VALUES('James'); 1166 INSERT INTO x1 VALUES('Mary'); 1167 SELECT * FROM x1; 1168 } 1169 } {Charles James Mary} 1170 1171 do_test pager1-8.$tn.2 { 1172 sqlite3 db2 $filename 1173 catchsql { SELECT * FROM x1 } db2 1174 } {1 {no such table: x1}} 1175 1176 do_execsql_test pager1-8.$tn.3 { 1177 BEGIN; 1178 INSERT INTO x1 VALUES('William'); 1179 INSERT INTO x1 VALUES('Anne'); 1180 ROLLBACK; 1181 } {} 1182 } 1183 1184 #------------------------------------------------------------------------- 1185 # The next block of tests - pager1-9.* - deal with interactions between 1186 # the pager and the backup API. Test cases: 1187 # 1188 # pager1-9.1.*: Test that a backup completes successfully even if the 1189 # source db is written to during the backup op. 1190 # 1191 # pager1-9.2.*: Test that a backup completes successfully even if the 1192 # source db is written to and then rolled back during a 1193 # backup operation. 1194 # 1195 do_test pager1-9.0.1 { 1196 faultsim_delete_and_reopen 1197 db func a_string a_string 1198 execsql { 1199 PRAGMA cache_size = 10; 1200 BEGIN; 1201 CREATE TABLE ab(a, b, UNIQUE(a, b)); 1202 INSERT INTO ab VALUES( a_string(200), a_string(300) ); 1203 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1204 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1205 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1206 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1207 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1208 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1209 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; 1210 COMMIT; 1211 } 1212 } {} 1213 do_test pager1-9.0.2 { 1214 sqlite3 db2 test.db2 1215 db2 eval { PRAGMA cache_size = 10 } 1216 sqlite3_backup B db2 main db main 1217 list [B step 10000] [B finish] 1218 } {SQLITE_DONE SQLITE_OK} 1219 do_test pager1-9.0.3 { 1220 db one {SELECT md5sum(a, b) FROM ab} 1221 } [db2 one {SELECT md5sum(a, b) FROM ab}] 1222 1223 do_test pager1-9.1.1 { 1224 execsql { UPDATE ab SET a = a_string(201) } 1225 sqlite3_backup B db2 main db main 1226 B step 30 1227 } {SQLITE_OK} 1228 do_test pager1-9.1.2 { 1229 execsql { UPDATE ab SET b = a_string(301) } 1230 list [B step 10000] [B finish] 1231 } {SQLITE_DONE SQLITE_OK} 1232 do_test pager1-9.1.3 { 1233 db one {SELECT md5sum(a, b) FROM ab} 1234 } [db2 one {SELECT md5sum(a, b) FROM ab}] 1235 do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128} 1236 1237 do_test pager1-9.2.1 { 1238 execsql { UPDATE ab SET a = a_string(202) } 1239 sqlite3_backup B db2 main db main 1240 B step 30 1241 } {SQLITE_OK} 1242 do_test pager1-9.2.2 { 1243 execsql { 1244 BEGIN; 1245 UPDATE ab SET b = a_string(301); 1246 ROLLBACK; 1247 } 1248 list [B step 10000] [B finish] 1249 } {SQLITE_DONE SQLITE_OK} 1250 do_test pager1-9.2.3 { 1251 db one {SELECT md5sum(a, b) FROM ab} 1252 } [db2 one {SELECT md5sum(a, b) FROM ab}] 1253 do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128} 1254 db close 1255 db2 close 1256 1257 do_test pager1-9.3.1 { 1258 testvfs tv -default 1 1259 tv sectorsize 4096 1260 faultsim_delete_and_reopen 1261 1262 execsql { PRAGMA page_size = 1024 } 1263 for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" } 1264 } {} 1265 do_test pager1-9.3.2 { 1266 sqlite3 db2 test.db2 1267 1268 execsql { 1269 PRAGMA page_size = 4096; 1270 PRAGMA synchronous = OFF; 1271 CREATE TABLE t1(a, b); 1272 CREATE TABLE t2(a, b); 1273 } db2 1274 1275 sqlite3_backup B db2 main db main 1276 B step 30 1277 list [B step 10000] [B finish] 1278 } {SQLITE_DONE SQLITE_OK} 1279 do_test pager1-9.3.3 { 1280 db2 close 1281 db close 1282 tv delete 1283 file size test.db2 1284 } [file size test.db] 1285 1286 do_test pager1-9.4.1 { 1287 faultsim_delete_and_reopen 1288 sqlite3 db2 test.db2 1289 execsql { 1290 PRAGMA page_size = 4096; 1291 CREATE TABLE t1(a, b); 1292 CREATE TABLE t2(a, b); 1293 } db2 1294 sqlite3_backup B db2 main db main 1295 list [B step 10000] [B finish] 1296 } {SQLITE_DONE SQLITE_OK} 1297 do_test pager1-9.4.2 { 1298 list [file size test.db2] [file size test.db] 1299 } {0 0} 1300 db2 close 1301 1302 #------------------------------------------------------------------------- 1303 # Test that regardless of the value returned by xSectorSize(), the 1304 # minimum effective sector-size is 512 and the maximum 65536 bytes. 1305 # 1306 testvfs tv -default 1 1307 foreach sectorsize { 1308 32 64 128 256 512 1024 2048 1309 4096 8192 16384 32768 65536 131072 262144 1310 } { 1311 tv sectorsize $sectorsize 1312 set eff $sectorsize 1313 if {$sectorsize < 512} { set eff 512 } 1314 if {$sectorsize > 65536} { set eff 65536 } 1315 1316 do_test pager1-10.$sectorsize.1 { 1317 faultsim_delete_and_reopen 1318 db func a_string a_string 1319 execsql { 1320 PRAGMA journal_mode = PERSIST; 1321 PRAGMA page_size = 1024; 1322 BEGIN; 1323 CREATE TABLE t1(a, b); 1324 CREATE TABLE t2(a, b); 1325 CREATE TABLE t3(a, b); 1326 COMMIT; 1327 } 1328 file size test.db-journal 1329 } [expr $sectorsize > 65536 ? 65536 : $sectorsize] 1330 1331 do_test pager1-10.$sectorsize.2 { 1332 execsql { 1333 INSERT INTO t3 VALUES(a_string(300), a_string(300)); 1334 INSERT INTO t3 SELECT * FROM t3; /* 2 */ 1335 INSERT INTO t3 SELECT * FROM t3; /* 4 */ 1336 INSERT INTO t3 SELECT * FROM t3; /* 8 */ 1337 INSERT INTO t3 SELECT * FROM t3; /* 16 */ 1338 INSERT INTO t3 SELECT * FROM t3; /* 32 */ 1339 } 1340 } {} 1341 1342 do_test pager1-10.$sectorsize.3 { 1343 db close 1344 sqlite3 db test.db 1345 execsql { 1346 PRAGMA cache_size = 10; 1347 BEGIN; 1348 } 1349 recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"} 1350 execsql { 1351 COMMIT; 1352 SELECT * FROM t2; 1353 } 1354 } {1 2} 1355 1356 do_test pager1-10.$sectorsize.4 { 1357 execsql { 1358 CREATE TABLE t6(a, b); 1359 CREATE TABLE t7(a, b); 1360 CREATE TABLE t5(a, b); 1361 DROP TABLE t6; 1362 DROP TABLE t7; 1363 } 1364 execsql { 1365 BEGIN; 1366 CREATE TABLE t6(a, b); 1367 } 1368 recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"} 1369 execsql { 1370 COMMIT; 1371 SELECT * FROM t5; 1372 } 1373 } {1 2} 1374 1375 } 1376 db close 1377 1378 tv sectorsize 4096 1379 do_test pager1.10.x.1 { 1380 faultsim_delete_and_reopen 1381 execsql { 1382 PRAGMA auto_vacuum = none; 1383 PRAGMA page_size = 1024; 1384 CREATE TABLE t1(x); 1385 } 1386 for {set i 0} {$i<30} {incr i} { 1387 execsql { INSERT INTO t1 VALUES(zeroblob(900)) } 1388 } 1389 file size test.db 1390 } {32768} 1391 do_test pager1.10.x.2 { 1392 execsql { 1393 CREATE TABLE t2(x); 1394 DROP TABLE t2; 1395 } 1396 file size test.db 1397 } {33792} 1398 do_test pager1.10.x.3 { 1399 execsql { 1400 BEGIN; 1401 CREATE TABLE t2(x); 1402 } 1403 recursive_select 30 t1 1404 execsql { 1405 CREATE TABLE t3(x); 1406 COMMIT; 1407 } 1408 } {} 1409 1410 db close 1411 tv delete 1412 1413 testvfs tv -default 1 1414 faultsim_delete_and_reopen 1415 db func a_string a_string 1416 do_execsql_test pager1-11.1 { 1417 PRAGMA journal_mode = DELETE; 1418 PRAGMA cache_size = 10; 1419 BEGIN; 1420 CREATE TABLE zz(top PRIMARY KEY); 1421 INSERT INTO zz VALUES(a_string(222)); 1422 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1423 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1424 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1425 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1426 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1427 COMMIT; 1428 BEGIN; 1429 UPDATE zz SET top = a_string(345); 1430 } {delete} 1431 1432 proc lockout {method args} { return SQLITE_IOERR } 1433 tv script lockout 1434 tv filter {xWrite xTruncate xSync} 1435 do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}} 1436 1437 tv script {} 1438 do_test pager1-11.3 { 1439 sqlite3 db2 test.db 1440 execsql { 1441 PRAGMA journal_mode = TRUNCATE; 1442 PRAGMA integrity_check; 1443 } db2 1444 } {truncate ok} 1445 do_test pager1-11.4 { 1446 db2 close 1447 file exists test.db-journal 1448 } {0} 1449 do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32} 1450 db close 1451 tv delete 1452 1453 #------------------------------------------------------------------------- 1454 # Test "PRAGMA page_size" 1455 # 1456 testvfs tv -default 1 1457 tv sectorsize 1024 1458 foreach pagesize { 1459 512 1024 2048 4096 8192 16384 32768 1460 } { 1461 faultsim_delete_and_reopen 1462 1463 # The sector-size (according to the VFS) is 1024 bytes. So if the 1464 # page-size requested using "PRAGMA page_size" is greater than the 1465 # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective 1466 # page-size remains 1024 bytes. 1467 # 1468 set eff $pagesize 1469 if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 } 1470 1471 do_test pager1-12.$pagesize.1 { 1472 sqlite3 db2 test.db 1473 execsql " 1474 PRAGMA page_size = $pagesize; 1475 CREATE VIEW v AS SELECT * FROM sqlite_master; 1476 " db2 1477 file size test.db 1478 } $eff 1479 do_test pager1-12.$pagesize.2 { 1480 sqlite3 db2 test.db 1481 execsql { 1482 SELECT count(*) FROM v; 1483 PRAGMA main.page_size; 1484 } db2 1485 } [list 1 $eff] 1486 do_test pager1-12.$pagesize.3 { 1487 execsql { 1488 SELECT count(*) FROM v; 1489 PRAGMA main.page_size; 1490 } 1491 } [list 1 $eff] 1492 db2 close 1493 } 1494 db close 1495 tv delete 1496 1497 #------------------------------------------------------------------------- 1498 # Test specal "PRAGMA journal_mode=PERSIST" test cases. 1499 # 1500 # pager1-13.1.*: This tests a special case encountered in persistent 1501 # journal mode: If the journal associated with a transaction 1502 # is smaller than the journal file (because a previous 1503 # transaction left a very large non-hot journal file in the 1504 # file-system), then SQLite has to be careful that there is 1505 # not a journal-header left over from a previous transaction 1506 # immediately following the journal content just written. 1507 # If there is, and the process crashes so that the journal 1508 # becomes a hot-journal and must be rolled back by another 1509 # process, there is a danger that the other process may roll 1510 # back the aborted transaction, then continue copying data 1511 # from an older transaction from the remainder of the journal. 1512 # See the syncJournal() function for details. 1513 # 1514 # pager1-13.2.*: Same test as the previous. This time, throw an index into 1515 # the mix to make the integrity-check more likely to catch 1516 # errors. 1517 # 1518 testvfs tv -default 1 1519 tv script xSyncCb 1520 tv filter xSync 1521 proc xSyncCb {method filename args} { 1522 set t [file tail $filename] 1523 if {$t == "test.db"} faultsim_save 1524 return SQLITE_OK 1525 } 1526 faultsim_delete_and_reopen 1527 db func a_string a_string 1528 1529 # The UPDATE statement at the end of this test case creates a really big 1530 # journal. Since the cache-size is only 10 pages, the journal contains 1531 # frequent journal headers. 1532 # 1533 do_execsql_test pager1-13.1.1 { 1534 PRAGMA page_size = 1024; 1535 PRAGMA journal_mode = PERSIST; 1536 PRAGMA cache_size = 10; 1537 BEGIN; 1538 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB); 1539 INSERT INTO t1 VALUES(NULL, a_string(400)); 1540 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */ 1541 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */ 1542 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */ 1543 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */ 1544 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */ 1545 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */ 1546 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */ 1547 COMMIT; 1548 UPDATE t1 SET b = a_string(400); 1549 } {persist} 1550 1551 # Run transactions of increasing sizes. Eventually, one (or more than one) 1552 # of these will write just enough content that one of the old headers created 1553 # by the transaction in the block above lies immediately after the content 1554 # journalled by the current transaction. 1555 # 1556 for {set nUp 1} {$nUp<64} {incr nUp} { 1557 do_execsql_test pager1-13.1.2.$nUp.1 { 1558 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp 1559 } {} 1560 do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok} 1561 1562 # Try to access the snapshot of the file-system. 1563 # 1564 sqlite3 db2 sv_test.db 1565 do_test pager1-13.1.2.$nUp.3 { 1566 execsql { SELECT sum(length(b)) FROM t1 } db2 1567 } [expr {128*400 - ($nUp-1)}] 1568 do_test pager1-13.1.2.$nUp.4 { 1569 execsql { PRAGMA integrity_check } db2 1570 } {ok} 1571 db2 close 1572 } 1573 1574 # Same test as above. But this time with an index on the table. 1575 # 1576 do_execsql_test pager1-13.2.1 { 1577 CREATE INDEX i1 ON t1(b); 1578 UPDATE t1 SET b = a_string(400); 1579 } {} 1580 for {set nUp 1} {$nUp<64} {incr nUp} { 1581 do_execsql_test pager1-13.2.2.$nUp.1 { 1582 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp 1583 } {} 1584 do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok} 1585 sqlite3 db2 sv_test.db 1586 do_test pager1-13.2.2.$nUp.3 { 1587 execsql { SELECT sum(length(b)) FROM t1 } db2 1588 } [expr {128*400 - ($nUp-1)}] 1589 do_test pager1-13.2.2.$nUp.4 { 1590 execsql { PRAGMA integrity_check } db2 1591 } {ok} 1592 db2 close 1593 } 1594 1595 db close 1596 tv delete 1597 1598 #------------------------------------------------------------------------- 1599 # Test specal "PRAGMA journal_mode=OFF" test cases. 1600 # 1601 faultsim_delete_and_reopen 1602 do_execsql_test pager1-14.1.1 { 1603 PRAGMA journal_mode = OFF; 1604 CREATE TABLE t1(a, b); 1605 BEGIN; 1606 INSERT INTO t1 VALUES(1, 2); 1607 COMMIT; 1608 SELECT * FROM t1; 1609 } {off 1 2} 1610 do_catchsql_test pager1-14.1.2 { 1611 BEGIN; 1612 INSERT INTO t1 VALUES(3, 4); 1613 ROLLBACK; 1614 } {0 {}} 1615 do_execsql_test pager1-14.1.3 { 1616 SELECT * FROM t1; 1617 } {1 2} 1618 do_catchsql_test pager1-14.1.4 { 1619 BEGIN; 1620 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; 1621 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; 1622 } {1 {PRIMARY KEY must be unique}} 1623 do_execsql_test pager1-14.1.5 { 1624 COMMIT; 1625 SELECT * FROM t1; 1626 } {1 2 2 2} 1627 1628 #------------------------------------------------------------------------- 1629 # Test opening and closing the pager sub-system with different values 1630 # for the sqlite3_vfs.szOsFile variable. 1631 # 1632 faultsim_delete_and_reopen 1633 do_execsql_test pager1-15.0 { 1634 CREATE TABLE tx(y, z); 1635 INSERT INTO tx VALUES('Ayutthaya', 'Beijing'); 1636 INSERT INTO tx VALUES('London', 'Tokyo'); 1637 } {} 1638 db close 1639 for {set i 0} {$i<513} {incr i 3} { 1640 testvfs tv -default 1 -szosfile $i 1641 sqlite3 db test.db 1642 do_execsql_test pager1-15.$i.1 { 1643 SELECT * FROM tx; 1644 } {Ayutthaya Beijing London Tokyo} 1645 db close 1646 tv delete 1647 } 1648 1649 #------------------------------------------------------------------------- 1650 # Check that it is not possible to open a database file if the full path 1651 # to the associated journal file will be longer than sqlite3_vfs.mxPathname. 1652 # 1653 testvfs tv -default 1 1654 tv script xOpenCb 1655 tv filter xOpen 1656 proc xOpenCb {method filename} { 1657 set ::file_len [string length $filename] 1658 } 1659 sqlite3 db test.db 1660 db close 1661 tv delete 1662 1663 for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} { 1664 testvfs tv -default 1 -mxpathname $ii 1665 1666 # The length of the full path to file "test.db-journal" is ($::file_len+8). 1667 # If the configured sqlite3_vfs.mxPathname value greater than or equal to 1668 # this, then the file can be opened. Otherwise, it cannot. 1669 # 1670 if {$ii >= [expr $::file_len+8]} { 1671 set res {0 {}} 1672 } else { 1673 set res {1 {unable to open database file}} 1674 } 1675 1676 do_test pager1-16.1.$ii { 1677 list [catch { sqlite3 db test.db } msg] $msg 1678 } $res 1679 1680 catch {db close} 1681 tv delete 1682 } 1683 1684 #------------------------------------------------------------------------- 1685 # Test "PRAGMA omit_readlock". 1686 # 1687 # pager1-17.$tn.1.*: Test that if a second connection has an open 1688 # read-transaction, it is not usually possible to write 1689 # the database. 1690 # 1691 # pager1-17.$tn.2.*: Test that if the second connection was opened with 1692 # the SQLITE_OPEN_READONLY flag, and 1693 # "PRAGMA omit_readlock = 1" is executed before attaching 1694 # the database and opening a read-transaction on it, it is 1695 # possible to write the db. 1696 # 1697 # pager1-17.$tn.3.*: Test that if the second connection was *not* opened with 1698 # the SQLITE_OPEN_READONLY flag, executing 1699 # "PRAGMA omit_readlock = 1" has no effect. 1700 # 1701 do_multiclient_test tn { 1702 do_test pager1-17.$tn.1.1 { 1703 sql1 { 1704 CREATE TABLE t1(a, b); 1705 INSERT INTO t1 VALUES(1, 2); 1706 } 1707 sql2 { 1708 BEGIN; 1709 SELECT * FROM t1; 1710 } 1711 } {1 2} 1712 do_test pager1-17.$tn.1.2 { 1713 csql1 { INSERT INTO t1 VALUES(3, 4) } 1714 } {1 {database is locked}} 1715 do_test pager1-17.$tn.1.3 { 1716 sql2 { COMMIT } 1717 sql1 { INSERT INTO t1 VALUES(3, 4) } 1718 } {} 1719 1720 do_test pager1-17.$tn.2.1 { 1721 code2 { 1722 db2 close 1723 sqlite3 db2 :memory: -readonly 1 1724 } 1725 sql2 { 1726 PRAGMA omit_readlock = 1; 1727 ATTACH 'test.db' AS two; 1728 BEGIN; 1729 SELECT * FROM t1; 1730 } 1731 } {1 2 3 4} 1732 do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {} 1733 do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4} 1734 do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6} 1735 1736 do_test pager1-17.$tn.3.1 { 1737 code2 { 1738 db2 close 1739 sqlite3 db2 :memory: 1740 } 1741 sql2 { 1742 PRAGMA omit_readlock = 1; 1743 ATTACH 'test.db' AS two; 1744 BEGIN; 1745 SELECT * FROM t1; 1746 } 1747 } {1 2 3 4 5 6} 1748 do_test pager1-17.$tn.3.2 { 1749 csql1 { INSERT INTO t1 VALUES(3, 4) } 1750 } {1 {database is locked}} 1751 do_test pager1-17.$tn.3.3 { sql2 COMMIT } {} 1752 } 1753 1754 #------------------------------------------------------------------------- 1755 # Test the pagers response to the b-tree layer requesting illegal page 1756 # numbers: 1757 # 1758 # + The locking page, 1759 # + Page 0, 1760 # + A page with a page number greater than (2^31-1). 1761 # 1762 do_test pager1-18.1 { 1763 faultsim_delete_and_reopen 1764 db func a_string a_string 1765 execsql { 1766 PRAGMA page_size = 1024; 1767 CREATE TABLE t1(a, b); 1768 INSERT INTO t1 VALUES(a_string(500), a_string(200)); 1769 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1770 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1771 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1772 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1773 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1774 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1775 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1776 } 1777 } {} 1778 do_test pager1-18.2 { 1779 set root [db one "SELECT rootpage FROM sqlite_master"] 1780 set lockingpage [expr (0x10000/1024) + 1] 1781 execsql { 1782 PRAGMA writable_schema = 1; 1783 UPDATE sqlite_master SET rootpage = $lockingpage; 1784 } 1785 sqlite3 db2 test.db 1786 catchsql { SELECT count(*) FROM t1 } db2 1787 } {1 {database disk image is malformed}} 1788 db2 close 1789 do_test pager1-18.3 { 1790 execsql { 1791 CREATE TABLE t2(x); 1792 INSERT INTO t2 VALUES(a_string(5000)); 1793 } 1794 set pgno [expr ([file size test.db] / 1024)-2] 1795 hexio_write test.db [expr ($pgno-1)*1024] 00000000 1796 sqlite3 db2 test.db 1797 catchsql { SELECT length(x) FROM t2 } db2 1798 } {1 {database disk image is malformed}} 1799 db2 close 1800 do_test pager1-18.4 { 1801 hexio_write test.db [expr ($pgno-1)*1024] 90000000 1802 sqlite3 db2 test.db 1803 catchsql { SELECT length(x) FROM t2 } db2 1804 } {1 {database disk image is malformed}} 1805 db2 close 1806 do_test pager1-18.5 { 1807 sqlite3 db "" 1808 execsql { 1809 CREATE TABLE t1(a, b); 1810 CREATE TABLE t2(a, b); 1811 PRAGMA writable_schema = 1; 1812 UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1'; 1813 PRAGMA writable_schema = 0; 1814 ALTER TABLE t1 RENAME TO x1; 1815 } 1816 catchsql { SELECT * FROM x1 } 1817 } {1 {database disk image is malformed}} 1818 db close 1819 1820 do_test pager1-18.6 { 1821 faultsim_delete_and_reopen 1822 db func a_string a_string 1823 execsql { 1824 PRAGMA page_size = 1024; 1825 CREATE TABLE t1(x); 1826 INSERT INTO t1 VALUES(a_string(800)); 1827 INSERT INTO t1 VALUES(a_string(800)); 1828 } 1829 1830 set root [db one "SELECT rootpage FROM sqlite_master"] 1831 db close 1832 1833 hexio_write test.db [expr ($root-1)*1024 + 8] 00000000 1834 sqlite3 db test.db 1835 catchsql { SELECT length(x) FROM t1 } 1836 } {1 {database disk image is malformed}} 1837 1838 do_test pager1-19.1 { 1839 sqlite3 db "" 1840 db func a_string a_string 1841 execsql { 1842 PRAGMA page_size = 512; 1843 PRAGMA auto_vacuum = 1; 1844 CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, 1845 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, 1846 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, 1847 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, 1848 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, 1849 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, 1850 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, 1851 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, 1852 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, 1853 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, 1854 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, 1855 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, 1856 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn 1857 ); 1858 CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, 1859 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, 1860 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, 1861 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, 1862 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, 1863 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, 1864 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, 1865 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, 1866 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, 1867 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, 1868 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, 1869 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, 1870 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn 1871 ); 1872 INSERT INTO t1(aa) VALUES( a_string(100000) ); 1873 INSERT INTO t2(aa) VALUES( a_string(100000) ); 1874 VACUUM; 1875 } 1876 } {} 1877 1878 #------------------------------------------------------------------------- 1879 # Test a couple of special cases that come up while committing 1880 # transactions: 1881 # 1882 # pager1-20.1.*: Committing an in-memory database transaction when the 1883 # database has not been modified at all. 1884 # 1885 # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode. 1886 # 1887 # pager1-20.3.*: Committing a transaction in WAL mode where the database has 1888 # been modified, but all dirty pages have been flushed to 1889 # disk before the commit. 1890 # 1891 do_test pager1-20.1.1 { 1892 catch {db close} 1893 sqlite3 db :memory: 1894 execsql { 1895 CREATE TABLE one(two, three); 1896 INSERT INTO one VALUES('a', 'b'); 1897 } 1898 } {} 1899 do_test pager1-20.1.2 { 1900 execsql { 1901 BEGIN EXCLUSIVE; 1902 COMMIT; 1903 } 1904 } {} 1905 1906 do_test pager1-20.2.1 { 1907 faultsim_delete_and_reopen 1908 execsql { 1909 PRAGMA locking_mode = exclusive; 1910 PRAGMA journal_mode = persist; 1911 CREATE TABLE one(two, three); 1912 INSERT INTO one VALUES('a', 'b'); 1913 } 1914 } {exclusive persist} 1915 do_test pager1-20.2.2 { 1916 execsql { 1917 BEGIN EXCLUSIVE; 1918 COMMIT; 1919 } 1920 } {} 1921 1922 ifcapable wal { 1923 do_test pager1-20.3.1 { 1924 faultsim_delete_and_reopen 1925 db func a_string a_string 1926 execsql { 1927 PRAGMA cache_size = 10; 1928 PRAGMA journal_mode = wal; 1929 BEGIN; 1930 CREATE TABLE t1(x); 1931 CREATE TABLE t2(y); 1932 INSERT INTO t1 VALUES(a_string(800)); 1933 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ 1934 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ 1935 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ 1936 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ 1937 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ 1938 COMMIT; 1939 } 1940 } {wal} 1941 do_test pager1-20.3.2 { 1942 execsql { 1943 BEGIN; 1944 INSERT INTO t2 VALUES('xxxx'); 1945 } 1946 recursive_select 32 t1 1947 execsql COMMIT 1948 } {} 1949 } 1950 1951 #------------------------------------------------------------------------- 1952 # Test that a WAL database may not be opened if: 1953 # 1954 # pager1-21.1.*: The VFS has an iVersion less than 2, or 1955 # pager1-21.2.*: The VFS does not provide xShmXXX() methods. 1956 # 1957 ifcapable wal { 1958 do_test pager1-21.0 { 1959 faultsim_delete_and_reopen 1960 execsql { 1961 PRAGMA journal_mode = WAL; 1962 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); 1963 INSERT INTO ko DEFAULT VALUES; 1964 } 1965 } {wal} 1966 do_test pager1-21.1 { 1967 testvfs tv -noshm 1 1968 sqlite3 db2 test.db -vfs tv 1969 catchsql { SELECT * FROM ko } db2 1970 } {1 {unable to open database file}} 1971 db2 close 1972 tv delete 1973 do_test pager1-21.2 { 1974 testvfs tv -iversion 1 1975 sqlite3 db2 test.db -vfs tv 1976 catchsql { SELECT * FROM ko } db2 1977 } {1 {unable to open database file}} 1978 db2 close 1979 tv delete 1980 } 1981 1982 #------------------------------------------------------------------------- 1983 # Test that a "PRAGMA wal_checkpoint": 1984 # 1985 # pager1-22.1.*: is a no-op on a non-WAL db, and 1986 # pager1-22.2.*: does not cause xSync calls with a synchronous=off db. 1987 # 1988 do_test pager1-22.1.1 { 1989 faultsim_delete_and_reopen 1990 execsql { 1991 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); 1992 INSERT INTO ko DEFAULT VALUES; 1993 } 1994 execsql { PRAGMA wal_checkpoint } 1995 } {0 -1 -1} 1996 do_test pager1-22.2.1 { 1997 testvfs tv -default 1 1998 tv filter xSync 1999 tv script xSyncCb 2000 proc xSyncCb {args} {incr ::synccount} 2001 set ::synccount 0 2002 sqlite3 db test.db 2003 execsql { 2004 PRAGMA synchronous = off; 2005 PRAGMA journal_mode = WAL; 2006 INSERT INTO ko DEFAULT VALUES; 2007 } 2008 execsql { PRAGMA wal_checkpoint } 2009 set synccount 2010 } {0} 2011 db close 2012 tv delete 2013 2014 #------------------------------------------------------------------------- 2015 # Tests for changing journal mode. 2016 # 2017 # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode, 2018 # the journal file is deleted. 2019 # 2020 # pager1-23.2.*: Same test as above, but while a shared lock is held 2021 # on the database file. 2022 # 2023 # pager1-23.3.*: Same test as above, but while a reserved lock is held 2024 # on the database file. 2025 # 2026 # pager1-23.4.*: And, for fun, while holding an exclusive lock. 2027 # 2028 # pager1-23.5.*: Try to set various different journal modes with an 2029 # in-memory database (only MEMORY and OFF should work). 2030 # 2031 # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database 2032 # (doesn't work - in-memory databases always use 2033 # locking_mode=exclusive). 2034 # 2035 do_test pager1-23.1.1 { 2036 faultsim_delete_and_reopen 2037 execsql { 2038 PRAGMA journal_mode = PERSIST; 2039 CREATE TABLE t1(a, b); 2040 } 2041 file exists test.db-journal 2042 } {1} 2043 do_test pager1-23.1.2 { 2044 execsql { PRAGMA journal_mode = DELETE } 2045 file exists test.db-journal 2046 } {0} 2047 2048 do_test pager1-23.2.1 { 2049 execsql { 2050 PRAGMA journal_mode = PERSIST; 2051 INSERT INTO t1 VALUES('Canberra', 'ACT'); 2052 } 2053 db eval { SELECT * FROM t1 } { 2054 db eval { PRAGMA journal_mode = DELETE } 2055 } 2056 execsql { PRAGMA journal_mode } 2057 } {delete} 2058 do_test pager1-23.2.2 { 2059 file exists test.db-journal 2060 } {0} 2061 2062 do_test pager1-23.3.1 { 2063 execsql { 2064 PRAGMA journal_mode = PERSIST; 2065 INSERT INTO t1 VALUES('Darwin', 'NT'); 2066 BEGIN IMMEDIATE; 2067 } 2068 db eval { PRAGMA journal_mode = DELETE } 2069 execsql { PRAGMA journal_mode } 2070 } {delete} 2071 do_test pager1-23.3.2 { 2072 file exists test.db-journal 2073 } {0} 2074 do_test pager1-23.3.3 { 2075 execsql COMMIT 2076 } {} 2077 2078 do_test pager1-23.4.1 { 2079 execsql { 2080 PRAGMA journal_mode = PERSIST; 2081 INSERT INTO t1 VALUES('Adelaide', 'SA'); 2082 BEGIN EXCLUSIVE; 2083 } 2084 db eval { PRAGMA journal_mode = DELETE } 2085 execsql { PRAGMA journal_mode } 2086 } {delete} 2087 do_test pager1-23.4.2 { 2088 file exists test.db-journal 2089 } {0} 2090 do_test pager1-23.4.3 { 2091 execsql COMMIT 2092 } {} 2093 2094 do_test pager1-23.5.1 { 2095 faultsim_delete_and_reopen 2096 sqlite3 db :memory: 2097 } {} 2098 foreach {tn mode possible} { 2099 2 off 1 2100 3 memory 1 2101 4 persist 0 2102 5 delete 0 2103 6 wal 0 2104 7 truncate 0 2105 } { 2106 do_test pager1-23.5.$tn.1 { 2107 execsql "PRAGMA journal_mode = off" 2108 execsql "PRAGMA journal_mode = $mode" 2109 } [if $possible {list $mode} {list off}] 2110 do_test pager1-23.5.$tn.2 { 2111 execsql "PRAGMA journal_mode = memory" 2112 execsql "PRAGMA journal_mode = $mode" 2113 } [if $possible {list $mode} {list memory}] 2114 } 2115 do_test pager1-23.6.1 { 2116 execsql {PRAGMA locking_mode = normal} 2117 } {exclusive} 2118 do_test pager1-23.6.2 { 2119 execsql {PRAGMA locking_mode = exclusive} 2120 } {exclusive} 2121 do_test pager1-23.6.3 { 2122 execsql {PRAGMA locking_mode} 2123 } {exclusive} 2124 do_test pager1-23.6.4 { 2125 execsql {PRAGMA main.locking_mode} 2126 } {exclusive} 2127 2128 #------------------------------------------------------------------------- 2129 # 2130 do_test pager1-24.1.1 { 2131 faultsim_delete_and_reopen 2132 db func a_string a_string 2133 execsql { 2134 PRAGMA cache_size = 10; 2135 PRAGMA auto_vacuum = FULL; 2136 CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z)); 2137 CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z)); 2138 INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600)); 2139 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; 2140 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; 2141 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; 2142 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; 2143 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; 2144 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; 2145 INSERT INTO x1 SELECT * FROM x2; 2146 } 2147 } {} 2148 do_test pager1-24.1.2 { 2149 execsql { 2150 BEGIN; 2151 DELETE FROM x1 WHERE rowid<32; 2152 } 2153 recursive_select 64 x2 2154 } {} 2155 do_test pager1-24.1.3 { 2156 execsql { 2157 UPDATE x1 SET z = a_string(300) WHERE rowid>40; 2158 COMMIT; 2159 PRAGMA integrity_check; 2160 SELECT count(*) FROM x1; 2161 } 2162 } {ok 33} 2163 2164 do_test pager1-24.1.4 { 2165 execsql { 2166 DELETE FROM x1; 2167 INSERT INTO x1 SELECT * FROM x2; 2168 BEGIN; 2169 DELETE FROM x1 WHERE rowid<32; 2170 UPDATE x1 SET z = a_string(299) WHERE rowid>40; 2171 } 2172 recursive_select 64 x2 {db eval COMMIT} 2173 execsql { 2174 PRAGMA integrity_check; 2175 SELECT count(*) FROM x1; 2176 } 2177 } {ok 33} 2178 2179 do_test pager1-24.1.5 { 2180 execsql { 2181 DELETE FROM x1; 2182 INSERT INTO x1 SELECT * FROM x2; 2183 } 2184 recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} } 2185 execsql { SELECT * FROM x3 } 2186 } {} 2187 2188 #------------------------------------------------------------------------- 2189 # 2190 do_test pager1-25-1 { 2191 faultsim_delete_and_reopen 2192 execsql { 2193 BEGIN; 2194 SAVEPOINT abc; 2195 CREATE TABLE t1(a, b); 2196 ROLLBACK TO abc; 2197 COMMIT; 2198 } 2199 db close 2200 } {} 2201 breakpoint 2202 do_test pager1-25-2 { 2203 faultsim_delete_and_reopen 2204 execsql { 2205 SAVEPOINT abc; 2206 CREATE TABLE t1(a, b); 2207 ROLLBACK TO abc; 2208 COMMIT; 2209 } 2210 db close 2211 } {} 2212 2213 #------------------------------------------------------------------------- 2214 # Sector-size tests. 2215 # 2216 do_test pager1-26.1 { 2217 testvfs tv -default 1 2218 tv sectorsize 4096 2219 faultsim_delete_and_reopen 2220 db func a_string a_string 2221 execsql { 2222 PRAGMA page_size = 512; 2223 CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE); 2224 BEGIN; 2225 INSERT INTO tbl VALUES(a_string(25), a_string(600)); 2226 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2227 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2228 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2229 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2230 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2231 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2232 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2233 COMMIT; 2234 } 2235 } {} 2236 do_execsql_test pager1-26.1 { 2237 UPDATE tbl SET b = a_string(550); 2238 } {} 2239 db close 2240 tv delete 2241 2242 #------------------------------------------------------------------------- 2243 # 2244 do_test pager1.27.1 { 2245 faultsim_delete_and_reopen 2246 sqlite3_pager_refcounts db 2247 execsql { 2248 BEGIN; 2249 CREATE TABLE t1(a, b); 2250 } 2251 sqlite3_pager_refcounts db 2252 execsql COMMIT 2253 } {} 2254 2255 #------------------------------------------------------------------------- 2256 # Test that attempting to open a write-transaction with 2257 # locking_mode=exclusive in WAL mode fails if there are other clients on 2258 # the same database. 2259 # 2260 catch { db close } 2261 ifcapable wal { 2262 do_multiclient_test tn { 2263 do_test pager1-28.$tn.1 { 2264 sql1 { 2265 PRAGMA journal_mode = WAL; 2266 CREATE TABLE t1(a, b); 2267 INSERT INTO t1 VALUES('a', 'b'); 2268 } 2269 } {wal} 2270 do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b} 2271 2272 do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive} 2273 do_test pager1-28.$tn.4 { 2274 csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); } 2275 } {1 {database is locked}} 2276 code2 { db2 close ; sqlite3 db2 test.db } 2277 do_test pager1-28.$tn.4 { 2278 sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT } 2279 } {} 2280 } 2281 } 2282 2283 #------------------------------------------------------------------------- 2284 # Normally, when changing from journal_mode=PERSIST to DELETE the pager 2285 # attempts to delete the journal file. However, if it cannot obtain a 2286 # RESERVED lock on the database file, this step is skipped. 2287 # 2288 do_multiclient_test tn { 2289 do_test pager1-28.$tn.1 { 2290 sql1 { 2291 PRAGMA journal_mode = PERSIST; 2292 CREATE TABLE t1(a, b); 2293 INSERT INTO t1 VALUES('a', 'b'); 2294 } 2295 } {persist} 2296 do_test pager1-28.$tn.2 { file exists test.db-journal } 1 2297 do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete 2298 do_test pager1-28.$tn.4 { file exists test.db-journal } 0 2299 2300 do_test pager1-28.$tn.5 { 2301 sql1 { 2302 PRAGMA journal_mode = PERSIST; 2303 INSERT INTO t1 VALUES('c', 'd'); 2304 } 2305 } {persist} 2306 do_test pager1-28.$tn.6 { file exists test.db-journal } 1 2307 do_test pager1-28.$tn.7 { 2308 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } 2309 } {} 2310 do_test pager1-28.$tn.8 { file exists test.db-journal } 1 2311 do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete 2312 do_test pager1-28.$tn.10 { file exists test.db-journal } 1 2313 2314 do_test pager1-28.$tn.11 { sql2 COMMIT } {} 2315 do_test pager1-28.$tn.12 { file exists test.db-journal } 0 2316 2317 do_test pager1-28-$tn.13 { 2318 code1 { set channel [db incrblob -readonly t1 a 2] } 2319 sql1 { 2320 PRAGMA journal_mode = PERSIST; 2321 INSERT INTO t1 VALUES('g', 'h'); 2322 } 2323 } {persist} 2324 do_test pager1-28.$tn.14 { file exists test.db-journal } 1 2325 do_test pager1-28.$tn.15 { 2326 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } 2327 } {} 2328 do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete 2329 do_test pager1-28.$tn.17 { file exists test.db-journal } 1 2330 2331 do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}} 2332 do_test pager1-28-$tn.18 { code1 { read $channel } } c 2333 do_test pager1-28-$tn.19 { code1 { close $channel } } {} 2334 do_test pager1-28.$tn.20 { sql2 { COMMIT } } {} 2335 } 2336 2337 do_test pager1-29.1 { 2338 faultsim_delete_and_reopen 2339 execsql { 2340 PRAGMA page_size = 1024; 2341 PRAGMA auto_vacuum = full; 2342 PRAGMA locking_mode=exclusive; 2343 CREATE TABLE t1(a, b); 2344 INSERT INTO t1 VALUES(1, 2); 2345 } 2346 file size test.db 2347 } [expr 1024*3] 2348 do_test pager1-29.2 { 2349 execsql { 2350 PRAGMA page_size = 4096; 2351 VACUUM; 2352 } 2353 file size test.db 2354 } [expr 4096*3] 2355 2356 #------------------------------------------------------------------------- 2357 # Test that if an empty database file (size 0 bytes) is opened in 2358 # exclusive-locking mode, any journal file is deleted from the file-system 2359 # without being rolled back. And that the RESERVED lock obtained while 2360 # doing this is not released. 2361 # 2362 do_test pager1-30.1 { 2363 db close 2364 file delete test.db 2365 file delete test.db-journal 2366 set fd [open test.db-journal w] 2367 seek $fd [expr 512+1032*2] 2368 puts -nonewline $fd x 2369 close $fd 2370 2371 sqlite3 db test.db 2372 execsql { 2373 PRAGMA locking_mode=EXCLUSIVE; 2374 SELECT count(*) FROM sqlite_master; 2375 PRAGMA lock_status; 2376 } 2377 } {exclusive 0 main reserved temp closed} 2378 2379 #------------------------------------------------------------------------- 2380 # Test that if the "page-size" field in a journal-header is 0, the journal 2381 # file can still be rolled back. This is required for backward compatibility - 2382 # versions of SQLite prior to 3.5.8 always set this field to zero. 2383 # 2384 do_test pager1-31.1 { 2385 faultsim_delete_and_reopen 2386 execsql { 2387 PRAGMA cache_size = 10; 2388 PRAGMA page_size = 1024; 2389 CREATE TABLE t1(x, y, UNIQUE(x, y)); 2390 INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500)); 2391 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2392 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2393 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2394 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2395 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2396 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2397 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2398 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2399 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2400 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2401 BEGIN; 2402 UPDATE t1 SET y = randomblob(1499); 2403 } 2404 file copy test.db test.db2 2405 file copy test.db-journal test.db2-journal 2406 2407 hexio_write test.db2-journal 24 00000000 2408 sqlite3 db2 test.db2 2409 execsql { PRAGMA integrity_check } db2 2410 } {ok} 2411 2412 2413 2414 finish_test 2415
