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