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