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} {} 1399do_test pager1-9.3.2 { 1400 sqlite3 db2 test.db2 1401 1402 execsql { 1403 PRAGMA page_size = 4096; 1404 PRAGMA synchronous = OFF; 1405 CREATE TABLE t1(a, b); 1406 CREATE TABLE t2(a, b); 1407 } db2 1408 1409 sqlite3_backup B db2 main db main 1410 B step 30 1411 list [B step 10000] [B finish] 1412} {SQLITE_DONE SQLITE_OK} 1413do_test pager1-9.3.3 { 1414 db2 close 1415 db close 1416 tv delete 1417 file size test.db2 1418} [file size test.db] 1419 1420do_test pager1-9.4.1 { 1421 faultsim_delete_and_reopen 1422 sqlite3 db2 test.db2 1423 execsql { 1424 PRAGMA page_size = 4096; 1425 CREATE TABLE t1(a, b); 1426 CREATE TABLE t2(a, b); 1427 } db2 1428 sqlite3_backup B db2 main db main 1429 list [B step 10000] [B finish] 1430} {SQLITE_DONE SQLITE_OK} 1431do_test pager1-9.4.2 { 1432 list [file size test.db2] [file size test.db] 1433} {1024 0} 1434db2 close 1435 1436#------------------------------------------------------------------------- 1437# Test that regardless of the value returned by xSectorSize(), the 1438# minimum effective sector-size is 512 and the maximum 65536 bytes. 1439# 1440testvfs tv -default 1 1441foreach sectorsize { 1442 16 1443 32 64 128 256 512 1024 2048 1444 4096 8192 16384 32768 65536 131072 262144 1445} { 1446 tv sectorsize $sectorsize 1447 tv devchar {} 1448 set eff $sectorsize 1449 if {$sectorsize < 512} { set eff 512 } 1450 if {$sectorsize > 65536} { set eff 65536 } 1451 1452 do_test pager1-10.$sectorsize.1 { 1453 faultsim_delete_and_reopen 1454 db func a_string a_string 1455 execsql { 1456 PRAGMA journal_mode = PERSIST; 1457 PRAGMA page_size = 1024; 1458 BEGIN; 1459 CREATE TABLE t1(a, b); 1460 CREATE TABLE t2(a, b); 1461 CREATE TABLE t3(a, b); 1462 COMMIT; 1463 } 1464 file size test.db-journal 1465 } [expr $sectorsize > 65536 ? 65536 : ($sectorsize<32 ? 512 : $sectorsize)] 1466 1467 do_test pager1-10.$sectorsize.2 { 1468 execsql { 1469 INSERT INTO t3 VALUES(a_string(300), a_string(300)); 1470 INSERT INTO t3 SELECT * FROM t3; /* 2 */ 1471 INSERT INTO t3 SELECT * FROM t3; /* 4 */ 1472 INSERT INTO t3 SELECT * FROM t3; /* 8 */ 1473 INSERT INTO t3 SELECT * FROM t3; /* 16 */ 1474 INSERT INTO t3 SELECT * FROM t3; /* 32 */ 1475 } 1476 } {} 1477 1478 do_test pager1-10.$sectorsize.3 { 1479 db close 1480 sqlite3 db test.db 1481 execsql { 1482 PRAGMA cache_size = 10; 1483 BEGIN; 1484 } 1485 recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"} 1486 execsql { 1487 COMMIT; 1488 SELECT * FROM t2; 1489 } 1490 } {1 2} 1491 1492 do_test pager1-10.$sectorsize.4 { 1493 execsql { 1494 CREATE TABLE t6(a, b); 1495 CREATE TABLE t7(a, b); 1496 CREATE TABLE t5(a, b); 1497 DROP TABLE t6; 1498 DROP TABLE t7; 1499 } 1500 execsql { 1501 BEGIN; 1502 CREATE TABLE t6(a, b); 1503 } 1504 recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"} 1505 execsql { 1506 COMMIT; 1507 SELECT * FROM t5; 1508 } 1509 } {1 2} 1510 1511} 1512db close 1513 1514tv sectorsize 4096 1515do_test pager1.10.x.1 { 1516 faultsim_delete_and_reopen 1517 execsql { 1518 PRAGMA auto_vacuum = none; 1519 PRAGMA page_size = 1024; 1520 CREATE TABLE t1(x); 1521 } 1522 for {set i 0} {$i<30} {incr i} { 1523 execsql { INSERT INTO t1 VALUES(zeroblob(900)) } 1524 } 1525 file size test.db 1526} {32768} 1527do_test pager1.10.x.2 { 1528 execsql { 1529 CREATE TABLE t2(x); 1530 DROP TABLE t2; 1531 } 1532 file size test.db 1533} {33792} 1534do_test pager1.10.x.3 { 1535 execsql { 1536 BEGIN; 1537 CREATE TABLE t2(x); 1538 } 1539 recursive_select 30 t1 1540 execsql { 1541 CREATE TABLE t3(x); 1542 COMMIT; 1543 } 1544} {} 1545 1546db close 1547tv delete 1548 1549testvfs tv -default 1 1550faultsim_delete_and_reopen 1551db func a_string a_string 1552do_execsql_test pager1-11.1 { 1553 PRAGMA journal_mode = DELETE; 1554 PRAGMA cache_size = 10; 1555 BEGIN; 1556 CREATE TABLE zz(top PRIMARY KEY); 1557 INSERT INTO zz VALUES(a_string(222)); 1558 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1559 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1560 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1561 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1562 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; 1563 COMMIT; 1564 BEGIN; 1565 UPDATE zz SET top = a_string(345); 1566} {delete} 1567 1568proc lockout {method args} { return SQLITE_IOERR } 1569tv script lockout 1570tv filter {xWrite xTruncate xSync} 1571do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}} 1572 1573tv script {} 1574do_test pager1-11.3 { 1575 sqlite3 db2 test.db 1576 execsql { 1577 PRAGMA journal_mode = TRUNCATE; 1578 PRAGMA integrity_check; 1579 } db2 1580} {truncate ok} 1581do_test pager1-11.4 { 1582 db2 close 1583 file exists test.db-journal 1584} {0} 1585do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32} 1586db close 1587tv delete 1588 1589#------------------------------------------------------------------------- 1590# Test "PRAGMA page_size" 1591# 1592testvfs tv -default 1 1593tv sectorsize 1024 1594foreach pagesize { 1595 512 1024 2048 4096 8192 16384 32768 1596} { 1597 faultsim_delete_and_reopen 1598 1599 # The sector-size (according to the VFS) is 1024 bytes. So if the 1600 # page-size requested using "PRAGMA page_size" is greater than the 1601 # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective 1602 # page-size remains 1024 bytes. 1603 # 1604 set eff $pagesize 1605 if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 } 1606 1607 do_test pager1-12.$pagesize.1 { 1608 sqlite3 db2 test.db 1609 execsql " 1610 PRAGMA page_size = $pagesize; 1611 CREATE VIEW v AS SELECT * FROM sqlite_master; 1612 " db2 1613 file size test.db 1614 } $eff 1615 do_test pager1-12.$pagesize.2 { 1616 sqlite3 db2 test.db 1617 execsql { 1618 SELECT count(*) FROM v; 1619 PRAGMA main.page_size; 1620 } db2 1621 } [list 1 $eff] 1622 do_test pager1-12.$pagesize.3 { 1623 execsql { 1624 SELECT count(*) FROM v; 1625 PRAGMA main.page_size; 1626 } 1627 } [list 1 $eff] 1628 db2 close 1629} 1630db close 1631tv delete 1632 1633#------------------------------------------------------------------------- 1634# Test specal "PRAGMA journal_mode=PERSIST" test cases. 1635# 1636# pager1-13.1.*: This tests a special case encountered in persistent 1637# journal mode: If the journal associated with a transaction 1638# is smaller than the journal file (because a previous 1639# transaction left a very large non-hot journal file in the 1640# file-system), then SQLite has to be careful that there is 1641# not a journal-header left over from a previous transaction 1642# immediately following the journal content just written. 1643# If there is, and the process crashes so that the journal 1644# becomes a hot-journal and must be rolled back by another 1645# process, there is a danger that the other process may roll 1646# back the aborted transaction, then continue copying data 1647# from an older transaction from the remainder of the journal. 1648# See the syncJournal() function for details. 1649# 1650# pager1-13.2.*: Same test as the previous. This time, throw an index into 1651# the mix to make the integrity-check more likely to catch 1652# errors. 1653# 1654testvfs tv -default 1 1655tv script xSyncCb 1656tv filter xSync 1657proc xSyncCb {method filename args} { 1658 set t [file tail $filename] 1659 if {$t == "test.db"} faultsim_save 1660 return SQLITE_OK 1661} 1662faultsim_delete_and_reopen 1663db func a_string a_string 1664 1665# The UPDATE statement at the end of this test case creates a really big 1666# journal. Since the cache-size is only 10 pages, the journal contains 1667# frequent journal headers. 1668# 1669do_execsql_test pager1-13.1.1 { 1670 PRAGMA page_size = 1024; 1671 PRAGMA journal_mode = PERSIST; 1672 PRAGMA cache_size = 10; 1673 BEGIN; 1674 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB); 1675 INSERT INTO t1 VALUES(NULL, a_string(400)); 1676 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */ 1677 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */ 1678 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */ 1679 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */ 1680 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */ 1681 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */ 1682 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */ 1683 COMMIT; 1684 UPDATE t1 SET b = a_string(400); 1685} {persist} 1686 1687if {$::tcl_platform(platform)!="windows"} { 1688# Run transactions of increasing sizes. Eventually, one (or more than one) 1689# of these will write just enough content that one of the old headers created 1690# by the transaction in the block above lies immediately after the content 1691# journalled by the current transaction. 1692# 1693for {set nUp 1} {$nUp<64} {incr nUp} { 1694 do_execsql_test pager1-13.1.2.$nUp.1 { 1695 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp 1696 } {} 1697 do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok} 1698 1699 # Try to access the snapshot of the file-system. 1700 # 1701 sqlite3 db2 sv_test.db 1702 do_test pager1-13.1.2.$nUp.3 { 1703 execsql { SELECT sum(length(b)) FROM t1 } db2 1704 } [expr {128*400 - ($nUp-1)}] 1705 do_test pager1-13.1.2.$nUp.4 { 1706 execsql { PRAGMA integrity_check } db2 1707 } {ok} 1708 db2 close 1709} 1710} 1711 1712if {$::tcl_platform(platform)!="windows"} { 1713# Same test as above. But this time with an index on the table. 1714# 1715do_execsql_test pager1-13.2.1 { 1716 CREATE INDEX i1 ON t1(b); 1717 UPDATE t1 SET b = a_string(400); 1718} {} 1719for {set nUp 1} {$nUp<64} {incr nUp} { 1720 do_execsql_test pager1-13.2.2.$nUp.1 { 1721 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp 1722 } {} 1723 do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok} 1724 sqlite3 db2 sv_test.db 1725 do_test pager1-13.2.2.$nUp.3 { 1726 execsql { SELECT sum(length(b)) FROM t1 } db2 1727 } [expr {128*400 - ($nUp-1)}] 1728 do_test pager1-13.2.2.$nUp.4 { 1729 execsql { PRAGMA integrity_check } db2 1730 } {ok} 1731 db2 close 1732} 1733} 1734 1735db close 1736tv delete 1737 1738#------------------------------------------------------------------------- 1739# Test specal "PRAGMA journal_mode=OFF" test cases. 1740# 1741faultsim_delete_and_reopen 1742do_execsql_test pager1-14.1.1 { 1743 PRAGMA journal_mode = OFF; 1744 CREATE TABLE t1(a, b); 1745 BEGIN; 1746 INSERT INTO t1 VALUES(1, 2); 1747 COMMIT; 1748 SELECT * FROM t1; 1749} {off 1 2} 1750do_catchsql_test pager1-14.1.2 { 1751 BEGIN; 1752 INSERT INTO t1 VALUES(3, 4); 1753 ROLLBACK; 1754} {0 {}} 1755do_execsql_test pager1-14.1.3 { 1756 SELECT * FROM t1; 1757} {1 2} 1758do_catchsql_test pager1-14.1.4 { 1759 BEGIN; 1760 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; 1761 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; 1762} {1 {UNIQUE constraint failed: t1.rowid}} 1763do_execsql_test pager1-14.1.5 { 1764 COMMIT; 1765 SELECT * FROM t1; 1766} {1 2 2 2} 1767 1768#------------------------------------------------------------------------- 1769# Test opening and closing the pager sub-system with different values 1770# for the sqlite3_vfs.szOsFile variable. 1771# 1772faultsim_delete_and_reopen 1773do_execsql_test pager1-15.0 { 1774 CREATE TABLE tx(y, z); 1775 INSERT INTO tx VALUES('Ayutthaya', 'Beijing'); 1776 INSERT INTO tx VALUES('London', 'Tokyo'); 1777} {} 1778db close 1779for {set i 0} {$i<513} {incr i 3} { 1780 testvfs tv -default 1 -szosfile $i 1781 sqlite3 db test.db 1782 do_execsql_test pager1-15.$i.1 { 1783 SELECT * FROM tx; 1784 } {Ayutthaya Beijing London Tokyo} 1785 db close 1786 tv delete 1787} 1788 1789#------------------------------------------------------------------------- 1790# Check that it is not possible to open a database file if the full path 1791# to the associated journal file will be longer than sqlite3_vfs.mxPathname. 1792# 1793testvfs tv -default 1 1794tv script xOpenCb 1795tv filter xOpen 1796proc xOpenCb {method filename args} { 1797 set ::file_len [string length $filename] 1798} 1799sqlite3 db test.db 1800db close 1801tv delete 1802 1803for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} { 1804 testvfs tv -default 1 -mxpathname $ii 1805 1806 # The length of the full path to file "test.db-journal" is ($::file_len+8). 1807 # If the configured sqlite3_vfs.mxPathname value greater than or equal to 1808 # this, then the file can be opened. Otherwise, it cannot. 1809 # 1810 if {$ii >= [expr $::file_len+8]} { 1811 set res {0 {}} 1812 } else { 1813 set res {1 {unable to open database file}} 1814 } 1815 1816 do_test pager1-16.1.$ii { 1817 list [catch { sqlite3 db test.db } msg] $msg 1818 } $res 1819 1820 catch {db close} 1821 tv delete 1822} 1823 1824 1825#------------------------------------------------------------------------- 1826# Test the pagers response to the b-tree layer requesting illegal page 1827# numbers: 1828# 1829# + The locking page, 1830# + Page 0, 1831# + A page with a page number greater than (2^31-1). 1832# 1833# These tests will not work if SQLITE_DIRECT_OVERFLOW_READ is defined. In 1834# that case IO errors are sometimes reported instead of SQLITE_CORRUPT. 1835# 1836ifcapable !direct_read { 1837do_test pager1-18.1 { 1838 faultsim_delete_and_reopen 1839 db func a_string a_string 1840 execsql { 1841 PRAGMA page_size = 1024; 1842 CREATE TABLE t1(a, b); 1843 INSERT INTO t1 VALUES(a_string(500), a_string(200)); 1844 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1845 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1846 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1847 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1848 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1849 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1850 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; 1851 } 1852} {} 1853do_test pager1-18.2 { 1854 set root [db one "SELECT rootpage FROM sqlite_master"] 1855 set lockingpage [expr (0x10000/1024) + 1] 1856 execsql { 1857 PRAGMA writable_schema = 1; 1858 UPDATE sqlite_master SET rootpage = $lockingpage; 1859 } 1860 sqlite3 db2 test.db 1861 catchsql { SELECT count(*) FROM t1 } db2 1862} {1 {database disk image is malformed}} 1863db2 close 1864do_test pager1-18.3.1 { 1865 execsql { 1866 CREATE TABLE t2(x); 1867 INSERT INTO t2 VALUES(a_string(5000)); 1868 } 1869 set pgno [expr ([file size test.db] / 1024)-2] 1870 hexio_write test.db [expr ($pgno-1)*1024] 00000000 1871 sqlite3 db2 test.db 1872 # even though x is malformed, because typeof() does 1873 # not load the content of x, the error is not noticed. 1874 catchsql { SELECT typeof(x) FROM t2 } db2 1875} {0 text} 1876do_test pager1-18.3.2 { 1877 # in this case, the value of x is loaded and so the error is 1878 # detected 1879 catchsql { SELECT length(x||'') FROM t2 } db2 1880} {1 {database disk image is malformed}} 1881db2 close 1882do_test pager1-18.3.3 { 1883 execsql { 1884 DELETE FROM t2; 1885 INSERT INTO t2 VALUES(randomblob(5000)); 1886 } 1887 set pgno [expr ([file size test.db] / 1024)-2] 1888 hexio_write test.db [expr ($pgno-1)*1024] 00000000 1889 sqlite3 db2 test.db 1890 # even though x is malformed, because length() and typeof() do 1891 # not load the content of x, the error is not noticed. 1892 catchsql { SELECT length(x), typeof(x) FROM t2 } db2 1893} {0 {5000 blob}} 1894do_test pager1-18.3.4 { 1895 # in this case, the value of x is loaded and so the error is 1896 # detected 1897 catchsql { SELECT length(x||'') FROM t2 } db2 1898} {1 {database disk image is malformed}} 1899db2 close 1900do_test pager1-18.4 { 1901 hexio_write test.db [expr ($pgno-1)*1024] 90000000 1902 sqlite3 db2 test.db 1903 catchsql { SELECT length(x||'') FROM t2 } db2 1904} {1 {database disk image is malformed}} 1905db2 close 1906do_test pager1-18.5 { 1907 sqlite3 db "" 1908 execsql { 1909 CREATE TABLE t1(a, b); 1910 CREATE TABLE t2(a, b); 1911 PRAGMA writable_schema = 1; 1912 UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1'; 1913 PRAGMA writable_schema = 0; 1914 ALTER TABLE t1 RENAME TO x1; 1915 } 1916 catchsql { SELECT * FROM x1 } 1917} {1 {database disk image is malformed}} 1918db close 1919 1920do_test pager1-18.6 { 1921 faultsim_delete_and_reopen 1922 db func a_string a_string 1923 execsql { 1924 PRAGMA page_size = 1024; 1925 CREATE TABLE t1(x); 1926 INSERT INTO t1 VALUES(a_string(800)); 1927 INSERT INTO t1 VALUES(a_string(800)); 1928 } 1929 1930 set root [db one "SELECT rootpage FROM sqlite_master"] 1931 db close 1932 1933 hexio_write test.db [expr ($root-1)*1024 + 8] 00000000 1934 sqlite3 db test.db 1935 catchsql { SELECT length(x) FROM t1 } 1936} {1 {database disk image is malformed}} 1937} 1938 1939do_test pager1-19.1 { 1940 sqlite3 db "" 1941 db func a_string a_string 1942 execsql { 1943 PRAGMA page_size = 512; 1944 PRAGMA auto_vacuum = 1; 1945 CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, 1946 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, 1947 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, 1948 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, 1949 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, 1950 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, 1951 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, 1952 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, 1953 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, 1954 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, 1955 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, 1956 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, 1957 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn 1958 ); 1959 CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, 1960 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, 1961 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, 1962 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, 1963 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, 1964 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, 1965 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, 1966 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, 1967 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, 1968 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, 1969 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, 1970 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, 1971 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn 1972 ); 1973 INSERT INTO t1(aa) VALUES( a_string(100000) ); 1974 INSERT INTO t2(aa) VALUES( a_string(100000) ); 1975 VACUUM; 1976 } 1977} {} 1978 1979#------------------------------------------------------------------------- 1980# Test a couple of special cases that come up while committing 1981# transactions: 1982# 1983# pager1-20.1.*: Committing an in-memory database transaction when the 1984# database has not been modified at all. 1985# 1986# pager1-20.2.*: As above, but with a normal db in exclusive-locking mode. 1987# 1988# pager1-20.3.*: Committing a transaction in WAL mode where the database has 1989# been modified, but all dirty pages have been flushed to 1990# disk before the commit. 1991# 1992do_test pager1-20.1.1 { 1993 catch {db close} 1994 sqlite3 db :memory: 1995 execsql { 1996 CREATE TABLE one(two, three); 1997 INSERT INTO one VALUES('a', 'b'); 1998 } 1999} {} 2000do_test pager1-20.1.2 { 2001 execsql { 2002 BEGIN EXCLUSIVE; 2003 COMMIT; 2004 } 2005} {} 2006 2007do_test pager1-20.2.1 { 2008 faultsim_delete_and_reopen 2009 execsql { 2010 PRAGMA locking_mode = exclusive; 2011 PRAGMA journal_mode = persist; 2012 CREATE TABLE one(two, three); 2013 INSERT INTO one VALUES('a', 'b'); 2014 } 2015} {exclusive persist} 2016do_test pager1-20.2.2 { 2017 execsql { 2018 BEGIN EXCLUSIVE; 2019 COMMIT; 2020 } 2021} {} 2022 2023ifcapable wal { 2024 do_test pager1-20.3.1 { 2025 faultsim_delete_and_reopen 2026 db func a_string a_string 2027 execsql { 2028 PRAGMA cache_size = 10; 2029 PRAGMA journal_mode = wal; 2030 BEGIN; 2031 CREATE TABLE t1(x); 2032 CREATE TABLE t2(y); 2033 INSERT INTO t1 VALUES(a_string(800)); 2034 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ 2035 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ 2036 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ 2037 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ 2038 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ 2039 COMMIT; 2040 } 2041 } {wal} 2042 do_test pager1-20.3.2 { 2043 execsql { 2044 BEGIN; 2045 INSERT INTO t2 VALUES('xxxx'); 2046 } 2047 recursive_select 32 t1 2048 execsql COMMIT 2049 } {} 2050} 2051 2052#------------------------------------------------------------------------- 2053# Test that a WAL database may not be opened if: 2054# 2055# pager1-21.1.*: The VFS has an iVersion less than 2, or 2056# pager1-21.2.*: The VFS does not provide xShmXXX() methods. 2057# 2058ifcapable wal { 2059 do_test pager1-21.0 { 2060 faultsim_delete_and_reopen 2061 execsql { 2062 PRAGMA journal_mode = WAL; 2063 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); 2064 INSERT INTO ko DEFAULT VALUES; 2065 } 2066 } {wal} 2067 do_test pager1-21.1 { 2068 testvfs tv -noshm 1 2069 sqlite3 db2 test.db -vfs tv 2070 catchsql { SELECT * FROM ko } db2 2071 } {1 {unable to open database file}} 2072 db2 close 2073 tv delete 2074 do_test pager1-21.2 { 2075 testvfs tv -iversion 1 2076 sqlite3 db2 test.db -vfs tv 2077 catchsql { SELECT * FROM ko } db2 2078 } {1 {unable to open database file}} 2079 db2 close 2080 tv delete 2081} 2082 2083#------------------------------------------------------------------------- 2084# Test that a "PRAGMA wal_checkpoint": 2085# 2086# pager1-22.1.*: is a no-op on a non-WAL db, and 2087# pager1-22.2.*: does not cause xSync calls with a synchronous=off db. 2088# 2089ifcapable wal { 2090 do_test pager1-22.1.1 { 2091 faultsim_delete_and_reopen 2092 execsql { 2093 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); 2094 INSERT INTO ko DEFAULT VALUES; 2095 } 2096 execsql { PRAGMA wal_checkpoint } 2097 } {0 -1 -1} 2098 do_test pager1-22.2.1 { 2099 testvfs tv -default 1 2100 tv filter xSync 2101 tv script xSyncCb 2102 proc xSyncCb {args} {incr ::synccount} 2103 set ::synccount 0 2104 sqlite3 db test.db 2105 execsql { 2106 PRAGMA synchronous = off; 2107 PRAGMA journal_mode = WAL; 2108 INSERT INTO ko DEFAULT VALUES; 2109 } 2110 execsql { PRAGMA wal_checkpoint } 2111 set synccount 2112 } {0} 2113 db close 2114 tv delete 2115} 2116 2117#------------------------------------------------------------------------- 2118# Tests for changing journal mode. 2119# 2120# pager1-23.1.*: Test that when changing from PERSIST to DELETE mode, 2121# the journal file is deleted. 2122# 2123# pager1-23.2.*: Same test as above, but while a shared lock is held 2124# on the database file. 2125# 2126# pager1-23.3.*: Same test as above, but while a reserved lock is held 2127# on the database file. 2128# 2129# pager1-23.4.*: And, for fun, while holding an exclusive lock. 2130# 2131# pager1-23.5.*: Try to set various different journal modes with an 2132# in-memory database (only MEMORY and OFF should work). 2133# 2134# pager1-23.6.*: Try to set locking_mode=normal on an in-memory database 2135# (doesn't work - in-memory databases always use 2136# locking_mode=exclusive). 2137# 2138do_test pager1-23.1.1 { 2139 faultsim_delete_and_reopen 2140 execsql { 2141 PRAGMA journal_mode = PERSIST; 2142 CREATE TABLE t1(a, b); 2143 } 2144 file exists test.db-journal 2145} {1} 2146do_test pager1-23.1.2 { 2147 execsql { PRAGMA journal_mode = DELETE } 2148 file exists test.db-journal 2149} {0} 2150 2151do_test pager1-23.2.1 { 2152 execsql { 2153 PRAGMA journal_mode = PERSIST; 2154 INSERT INTO t1 VALUES('Canberra', 'ACT'); 2155 } 2156 db eval { SELECT * FROM t1 } { 2157 db eval { PRAGMA journal_mode = DELETE } 2158 } 2159 execsql { PRAGMA journal_mode } 2160} {delete} 2161do_test pager1-23.2.2 { 2162 file exists test.db-journal 2163} {0} 2164 2165do_test pager1-23.3.1 { 2166 execsql { 2167 PRAGMA journal_mode = PERSIST; 2168 INSERT INTO t1 VALUES('Darwin', 'NT'); 2169 BEGIN IMMEDIATE; 2170 } 2171 db eval { PRAGMA journal_mode = DELETE } 2172 execsql { PRAGMA journal_mode } 2173} {delete} 2174do_test pager1-23.3.2 { 2175 file exists test.db-journal 2176} {0} 2177do_test pager1-23.3.3 { 2178 execsql COMMIT 2179} {} 2180 2181do_test pager1-23.4.1 { 2182 execsql { 2183 PRAGMA journal_mode = PERSIST; 2184 INSERT INTO t1 VALUES('Adelaide', 'SA'); 2185 BEGIN EXCLUSIVE; 2186 } 2187 db eval { PRAGMA journal_mode = DELETE } 2188 execsql { PRAGMA journal_mode } 2189} {delete} 2190do_test pager1-23.4.2 { 2191 file exists test.db-journal 2192} {0} 2193do_test pager1-23.4.3 { 2194 execsql COMMIT 2195} {} 2196 2197do_test pager1-23.5.1 { 2198 faultsim_delete_and_reopen 2199 sqlite3 db :memory: 2200} {} 2201foreach {tn mode possible} { 2202 2 off 1 2203 3 memory 1 2204 4 persist 0 2205 5 delete 0 2206 6 wal 0 2207 7 truncate 0 2208} { 2209 do_test pager1-23.5.$tn.1 { 2210 execsql "PRAGMA journal_mode = off" 2211 execsql "PRAGMA journal_mode = $mode" 2212 } [if $possible {list $mode} {list off}] 2213 do_test pager1-23.5.$tn.2 { 2214 execsql "PRAGMA journal_mode = memory" 2215 execsql "PRAGMA journal_mode = $mode" 2216 } [if $possible {list $mode} {list memory}] 2217} 2218do_test pager1-23.6.1 { 2219 execsql {PRAGMA locking_mode = normal} 2220} {exclusive} 2221do_test pager1-23.6.2 { 2222 execsql {PRAGMA locking_mode = exclusive} 2223} {exclusive} 2224do_test pager1-23.6.3 { 2225 execsql {PRAGMA locking_mode} 2226} {exclusive} 2227do_test pager1-23.6.4 { 2228 execsql {PRAGMA main.locking_mode} 2229} {exclusive} 2230 2231#------------------------------------------------------------------------- 2232# 2233do_test pager1-24.1.1 { 2234 faultsim_delete_and_reopen 2235 db func a_string a_string 2236 execsql { 2237 PRAGMA cache_size = 10; 2238 PRAGMA auto_vacuum = FULL; 2239 CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z)); 2240 CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z)); 2241 INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600)); 2242 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; 2243 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; 2244 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; 2245 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; 2246 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; 2247 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; 2248 INSERT INTO x1 SELECT * FROM x2; 2249 } 2250} {} 2251do_test pager1-24.1.2 { 2252 execsql { 2253 BEGIN; 2254 DELETE FROM x1 WHERE rowid<32; 2255 } 2256 recursive_select 64 x2 2257} {} 2258do_test pager1-24.1.3 { 2259 execsql { 2260 UPDATE x1 SET z = a_string(300) WHERE rowid>40; 2261 COMMIT; 2262 PRAGMA integrity_check; 2263 SELECT count(*) FROM x1; 2264 } 2265} {ok 33} 2266 2267do_test pager1-24.1.4 { 2268 execsql { 2269 DELETE FROM x1; 2270 INSERT INTO x1 SELECT * FROM x2; 2271 BEGIN; 2272 DELETE FROM x1 WHERE rowid<32; 2273 UPDATE x1 SET z = a_string(299) WHERE rowid>40; 2274 } 2275 recursive_select 64 x2 {db eval COMMIT} 2276 execsql { 2277 PRAGMA integrity_check; 2278 SELECT count(*) FROM x1; 2279 } 2280} {ok 33} 2281 2282do_test pager1-24.1.5 { 2283 execsql { 2284 DELETE FROM x1; 2285 INSERT INTO x1 SELECT * FROM x2; 2286 } 2287 recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} } 2288 execsql { SELECT * FROM x3 } 2289} {} 2290 2291#------------------------------------------------------------------------- 2292# 2293do_test pager1-25-1 { 2294 faultsim_delete_and_reopen 2295 execsql { 2296 BEGIN; 2297 SAVEPOINT abc; 2298 CREATE TABLE t1(a, b); 2299 ROLLBACK TO abc; 2300 COMMIT; 2301 } 2302 db close 2303} {} 2304do_test pager1-25-2 { 2305 faultsim_delete_and_reopen 2306 execsql { 2307 SAVEPOINT abc; 2308 CREATE TABLE t1(a, b); 2309 ROLLBACK TO abc; 2310 COMMIT; 2311 } 2312 db close 2313} {} 2314 2315#------------------------------------------------------------------------- 2316# Sector-size tests. 2317# 2318do_test pager1-26.1 { 2319 testvfs tv -default 1 2320 tv sectorsize 4096 2321 faultsim_delete_and_reopen 2322 db func a_string a_string 2323 execsql { 2324 PRAGMA page_size = 512; 2325 CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE); 2326 BEGIN; 2327 INSERT INTO tbl VALUES(a_string(25), a_string(600)); 2328 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2329 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2330 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2331 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2332 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2333 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2334 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; 2335 COMMIT; 2336 } 2337} {} 2338do_execsql_test pager1-26.1 { 2339 UPDATE tbl SET b = a_string(550); 2340} {} 2341db close 2342tv delete 2343 2344#------------------------------------------------------------------------- 2345# 2346do_test pager1.27.1 { 2347 faultsim_delete_and_reopen 2348 sqlite3_pager_refcounts db 2349 execsql { 2350 BEGIN; 2351 CREATE TABLE t1(a, b); 2352 } 2353 sqlite3_pager_refcounts db 2354 execsql COMMIT 2355} {} 2356 2357#------------------------------------------------------------------------- 2358# Test that attempting to open a write-transaction with 2359# locking_mode=exclusive in WAL mode fails if there are other clients on 2360# the same database. 2361# 2362catch { db close } 2363ifcapable wal { 2364 do_multiclient_test tn { 2365 do_test pager1-28.$tn.1 { 2366 sql1 { 2367 PRAGMA journal_mode = WAL; 2368 CREATE TABLE t1(a, b); 2369 INSERT INTO t1 VALUES('a', 'b'); 2370 } 2371 } {wal} 2372 do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b} 2373 2374 do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive} 2375 do_test pager1-28.$tn.4 { 2376 csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); } 2377 } {1 {database is locked}} 2378 code2 { db2 close ; sqlite3 db2 test.db } 2379 do_test pager1-28.$tn.4 { 2380 sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT } 2381 } {} 2382 } 2383} 2384 2385#------------------------------------------------------------------------- 2386# Normally, when changing from journal_mode=PERSIST to DELETE the pager 2387# attempts to delete the journal file. However, if it cannot obtain a 2388# RESERVED lock on the database file, this step is skipped. 2389# 2390do_multiclient_test tn { 2391 do_test pager1-28.$tn.1 { 2392 sql1 { 2393 PRAGMA journal_mode = PERSIST; 2394 CREATE TABLE t1(a, b); 2395 INSERT INTO t1 VALUES('a', 'b'); 2396 } 2397 } {persist} 2398 do_test pager1-28.$tn.2 { file exists test.db-journal } 1 2399 do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete 2400 do_test pager1-28.$tn.4 { file exists test.db-journal } 0 2401 2402 do_test pager1-28.$tn.5 { 2403 sql1 { 2404 PRAGMA journal_mode = PERSIST; 2405 INSERT INTO t1 VALUES('c', 'd'); 2406 } 2407 } {persist} 2408 do_test pager1-28.$tn.6 { file exists test.db-journal } 1 2409 do_test pager1-28.$tn.7 { 2410 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } 2411 } {} 2412 do_test pager1-28.$tn.8 { file exists test.db-journal } 1 2413 do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete 2414 do_test pager1-28.$tn.10 { file exists test.db-journal } 1 2415 2416 do_test pager1-28.$tn.11 { sql2 COMMIT } {} 2417 do_test pager1-28.$tn.12 { file exists test.db-journal } 0 2418 2419 do_test pager1-28-$tn.13 { 2420 code1 { set channel [db incrblob -readonly t1 a 2] } 2421 sql1 { 2422 PRAGMA journal_mode = PERSIST; 2423 INSERT INTO t1 VALUES('g', 'h'); 2424 } 2425 } {persist} 2426 do_test pager1-28.$tn.14 { file exists test.db-journal } 1 2427 do_test pager1-28.$tn.15 { 2428 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } 2429 } {} 2430 do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete 2431 do_test pager1-28.$tn.17 { file exists test.db-journal } 1 2432 2433 do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}} 2434 do_test pager1-28-$tn.18 { code1 { read $channel } } c 2435 do_test pager1-28-$tn.19 { code1 { close $channel } } {} 2436 do_test pager1-28.$tn.20 { sql2 { COMMIT } } {} 2437} 2438 2439do_test pager1-29.1 { 2440 faultsim_delete_and_reopen 2441 execsql { 2442 PRAGMA page_size = 1024; 2443 PRAGMA auto_vacuum = full; 2444 PRAGMA locking_mode=exclusive; 2445 CREATE TABLE t1(a, b); 2446 INSERT INTO t1 VALUES(1, 2); 2447 } 2448 file size test.db 2449} [expr 1024*3] 2450do_test pager1-29.2 { 2451 execsql { 2452 PRAGMA page_size = 4096; 2453 VACUUM; 2454 } 2455 file size test.db 2456} [expr 4096*3] 2457 2458#------------------------------------------------------------------------- 2459# Test that if an empty database file (size 0 bytes) is opened in 2460# exclusive-locking mode, any journal file is deleted from the file-system 2461# without being rolled back. And that the RESERVED lock obtained while 2462# doing this is not released. 2463# 2464do_test pager1-30.1 { 2465 db close 2466 delete_file test.db 2467 delete_file test.db-journal 2468 set fd [open test.db-journal w] 2469 seek $fd [expr 512+1032*2] 2470 puts -nonewline $fd x 2471 close $fd 2472 2473 sqlite3 db test.db 2474 execsql { 2475 PRAGMA locking_mode=EXCLUSIVE; 2476 SELECT count(*) FROM sqlite_master; 2477 PRAGMA lock_status; 2478 } 2479} {exclusive 0 main reserved temp closed} 2480 2481#------------------------------------------------------------------------- 2482# Test that if the "page-size" field in a journal-header is 0, the journal 2483# file can still be rolled back. This is required for backward compatibility - 2484# versions of SQLite prior to 3.5.8 always set this field to zero. 2485# 2486if {$tcl_platform(platform)=="unix"} { 2487do_test pager1-31.1 { 2488 faultsim_delete_and_reopen 2489 execsql { 2490 PRAGMA cache_size = 10; 2491 PRAGMA page_size = 1024; 2492 CREATE TABLE t1(x, y, UNIQUE(x, y)); 2493 INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500)); 2494 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2495 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2496 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2497 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2498 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2499 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2500 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2501 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2502 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2503 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; 2504 BEGIN; 2505 UPDATE t1 SET y = randomblob(1499); 2506 } 2507 copy_file test.db test.db2 2508 copy_file test.db-journal test.db2-journal 2509 2510 hexio_write test.db2-journal 24 00000000 2511 sqlite3 db2 test.db2 2512 execsql { PRAGMA integrity_check } db2 2513} {ok} 2514} 2515 2516#------------------------------------------------------------------------- 2517# Test that a database file can be "pre-hinted" to a certain size and that 2518# subsequent spilling of the pager cache does not result in the database 2519# file being shrunk. 2520# 2521catch {db close} 2522forcedelete test.db 2523 2524do_test pager1-32.1 { 2525 sqlite3 db test.db 2526 execsql { 2527 CREATE TABLE t1(x, y); 2528 } 2529 db close 2530 sqlite3 db test.db 2531 execsql { 2532 BEGIN; 2533 INSERT INTO t1 VALUES(1, randomblob(10000)); 2534 } 2535 file_control_chunksize_test db main 1024 2536 file_control_sizehint_test db main 20971520; # 20MB 2537 execsql { 2538 PRAGMA cache_size = 10; 2539 INSERT INTO t1 VALUES(1, randomblob(10000)); 2540 INSERT INTO t1 VALUES(2, randomblob(10000)); 2541 INSERT INTO t1 SELECT x+2, randomblob(10000) from t1; 2542 INSERT INTO t1 SELECT x+4, randomblob(10000) from t1; 2543 INSERT INTO t1 SELECT x+8, randomblob(10000) from t1; 2544 INSERT INTO t1 SELECT x+16, randomblob(10000) from t1; 2545 SELECT count(*) FROM t1; 2546 COMMIT; 2547 } 2548 db close 2549 file size test.db 2550} {20971520} 2551 2552# Cleanup 20MB file left by the previous test. 2553forcedelete test.db 2554 2555#------------------------------------------------------------------------- 2556# Test that if a transaction is committed in journal_mode=DELETE mode, 2557# and the call to unlink() returns an ENOENT error, the COMMIT does not 2558# succeed. 2559# 2560if {$::tcl_platform(platform)=="unix"} { 2561 do_test pager1-33.1 { 2562 sqlite3 db test.db 2563 execsql { 2564 CREATE TABLE t1(x); 2565 INSERT INTO t1 VALUES('one'); 2566 INSERT INTO t1 VALUES('two'); 2567 BEGIN; 2568 INSERT INTO t1 VALUES('three'); 2569 INSERT INTO t1 VALUES('four'); 2570 } 2571 forcedelete bak-journal 2572 file rename test.db-journal bak-journal 2573 2574 catchsql COMMIT 2575 } {1 {disk I/O error}} 2576 2577 do_test pager1-33.2 { 2578 file rename bak-journal test.db-journal 2579 execsql { SELECT * FROM t1 } 2580 } {one two} 2581} 2582 2583#------------------------------------------------------------------------- 2584# Test that appending pages to the database file then moving those pages 2585# to the free-list before the transaction is committed does not cause 2586# an error. 2587# 2588foreach {tn pragma strsize} { 2589 1 { PRAGMA mmap_size = 0 } 2400 2590 2 { } 2400 2591 3 { PRAGMA mmap_size = 0 } 4400 2592 4 { } 4400 2593} { 2594 reset_db 2595 db func a_string a_string 2596 db eval $pragma 2597 do_execsql_test 34.$tn.1 { 2598 CREATE TABLE t1(a, b); 2599 INSERT INTO t1 VALUES(1, 2); 2600 } 2601 do_execsql_test 34.$tn.2 { 2602 BEGIN; 2603 INSERT INTO t1 VALUES(2, a_string($strsize)); 2604 DELETE FROM t1 WHERE oid=2; 2605 COMMIT; 2606 PRAGMA integrity_check; 2607 } {ok} 2608} 2609 2610#------------------------------------------------------------------------- 2611# 2612reset_db 2613do_test 35 { 2614 sqlite3 db test.db 2615 2616 execsql { 2617 CREATE TABLE t1(x, y); 2618 PRAGMA journal_mode = WAL; 2619 INSERT INTO t1 VALUES(1, 2); 2620 } 2621 2622 execsql { 2623 BEGIN; 2624 CREATE TABLE t2(a, b); 2625 } 2626 2627 hexio_write test.db-shm [expr 16*1024] [string repeat 0055 8192] 2628 catchsql ROLLBACK 2629} {0 {}} 2630 2631do_multiclient_test tn { 2632 sql1 { 2633 PRAGMA auto_vacuum = 0; 2634 CREATE TABLE t1(x, y); 2635 INSERT INTO t1 VALUES(1, 2); 2636 } 2637 2638 do_test 36.$tn.1 { 2639 sql2 { PRAGMA max_page_count = 2 } 2640 list [catch { sql2 { CREATE TABLE t2(x) } } msg] $msg 2641 } {1 {database or disk is full}} 2642 2643 sql1 { PRAGMA checkpoint_fullfsync = 1 } 2644 sql1 { CREATE TABLE t2(x) } 2645 2646 do_test 36.$tn.2 { 2647 sql2 { INSERT INTO t2 VALUES('xyz') } 2648 list [catch { sql2 { CREATE TABLE t3(x) } } msg] $msg 2649 } {1 {database or disk is full}} 2650} 2651 2652forcedelete test1 test2 2653foreach {tn uri} { 2654 1 {file:?mode=memory&cache=shared} 2655 2 {file:one?mode=memory&cache=shared} 2656 3 {file:test1?cache=shared} 2657 4 {file:test2?another=parameter&yet=anotherone} 2658} { 2659 do_test 37.$tn { 2660 catch { db close } 2661 sqlite3_shutdown 2662 sqlite3_config_uri 1 2663 sqlite3 db $uri 2664 2665 db eval { 2666 CREATE TABLE t1(x); 2667 INSERT INTO t1 VALUES(1); 2668 SELECT * FROM t1; 2669 } 2670 } {1} 2671 2672 do_execsql_test 37.$tn.2 { 2673 VACUUM; 2674 SELECT * FROM t1; 2675 } {1} 2676 2677 db close 2678 sqlite3_shutdown 2679 sqlite3_config_uri 0 2680} 2681 2682do_test 38.1 { 2683 catch { db close } 2684 forcedelete test.db 2685 set fd [open test.db w] 2686 puts $fd "hello world" 2687 close $fd 2688 sqlite3 db test.db 2689 catchsql { CREATE TABLE t1(x) } 2690} {1 {file is encrypted or is not a database}} 2691do_test 38.2 { 2692 catch { db close } 2693 forcedelete test.db 2694} {} 2695 2696do_test 39.1 { 2697 sqlite3 db test.db 2698 execsql { 2699 PRAGMA auto_vacuum = 1; 2700 CREATE TABLE t1(x); 2701 INSERT INTO t1 VALUES('xxx'); 2702 INSERT INTO t1 VALUES('two'); 2703 INSERT INTO t1 VALUES(randomblob(400)); 2704 INSERT INTO t1 VALUES(randomblob(400)); 2705 INSERT INTO t1 VALUES(randomblob(400)); 2706 INSERT INTO t1 VALUES(randomblob(400)); 2707 BEGIN; 2708 UPDATE t1 SET x = 'one' WHERE rowid=1; 2709 } 2710 set ::stmt [sqlite3_prepare db "SELECT * FROM t1 ORDER BY rowid" -1 dummy] 2711 sqlite3_step $::stmt 2712 sqlite3_column_text $::stmt 0 2713} {one} 2714do_test 39.2 { 2715 execsql { CREATE TABLE t2(x) } 2716 sqlite3_step $::stmt 2717 sqlite3_column_text $::stmt 0 2718} {two} 2719do_test 39.3 { 2720 sqlite3_finalize $::stmt 2721 execsql COMMIT 2722} {} 2723 2724do_execsql_test 39.4 { 2725 PRAGMA auto_vacuum = 2; 2726 CREATE TABLE t3(x); 2727 CREATE TABLE t4(x); 2728 2729 DROP TABLE t2; 2730 DROP TABLE t3; 2731 DROP TABLE t4; 2732} 2733do_test 39.5 { 2734 db close 2735 sqlite3 db test.db 2736 execsql { 2737 PRAGMA cache_size = 1; 2738 PRAGMA incremental_vacuum; 2739 PRAGMA integrity_check; 2740 } 2741} {ok} 2742 2743do_test 40.1 { 2744 reset_db 2745 execsql { 2746 PRAGMA auto_vacuum = 1; 2747 CREATE TABLE t1(x PRIMARY KEY); 2748 INSERT INTO t1 VALUES(randomblob(1200)); 2749 PRAGMA page_count; 2750 } 2751} {6} 2752do_test 40.2 { 2753 execsql { 2754 INSERT INTO t1 VALUES(randomblob(1200)); 2755 INSERT INTO t1 VALUES(randomblob(1200)); 2756 INSERT INTO t1 VALUES(randomblob(1200)); 2757 } 2758} {} 2759do_test 40.3 { 2760 db close 2761 sqlite3 db test.db 2762 execsql { 2763 PRAGMA cache_size = 1; 2764 CREATE TABLE t2(x); 2765 PRAGMA integrity_check; 2766 } 2767} {ok} 2768 2769do_test 41.1 { 2770 reset_db 2771 execsql { 2772 CREATE TABLE t1(x PRIMARY KEY); 2773 INSERT INTO t1 VALUES(randomblob(200)); 2774 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2775 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2776 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2777 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2778 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2779 INSERT INTO t1 SELECT randomblob(200) FROM t1; 2780 } 2781} {} 2782do_test 41.2 { 2783 testvfs tv -default 1 2784 tv sectorsize 16384; 2785 tv devchar [list] 2786 db close 2787 sqlite3 db test.db 2788 execsql { 2789 PRAGMA cache_size = 1; 2790 DELETE FROM t1 WHERE rowid%4; 2791 PRAGMA integrity_check; 2792 } 2793} {ok} 2794db close 2795tv delete 2796 2797set pending_prev [sqlite3_test_control_pending_byte 0x1000000] 2798do_test 42.1 { 2799 reset_db 2800 execsql { 2801 CREATE TABLE t1(x, y); 2802 INSERT INTO t1 VALUES(randomblob(200), randomblob(200)); 2803 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2804 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2805 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2806 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2807 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2808 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2809 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2810 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2811 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2812 } 2813 db close 2814 sqlite3_test_control_pending_byte 0x0010000 2815 sqlite3 db test.db 2816 db eval { PRAGMA mmap_size = 0 } 2817 catchsql { SELECT sum(length(y)) FROM t1 } 2818} {1 {database disk image is malformed}} 2819do_test 42.2 { 2820 reset_db 2821 execsql { 2822 CREATE TABLE t1(x, y); 2823 INSERT INTO t1 VALUES(randomblob(200), randomblob(200)); 2824 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2825 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2826 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2827 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2828 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2829 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2830 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2831 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1; 2832 } 2833 db close 2834 2835 testvfs tv -default 1 2836 tv sectorsize 16384; 2837 tv devchar [list] 2838 sqlite3 db test.db -vfs tv 2839 execsql { UPDATE t1 SET x = randomblob(200) } 2840} {} 2841db close 2842tv delete 2843sqlite3_test_control_pending_byte $pending_prev 2844 2845do_test 43.1 { 2846 reset_db 2847 execsql { 2848 CREATE TABLE t1(x, y); 2849 INSERT INTO t1 VALUES(1, 2); 2850 CREATE TABLE t2(x, y); 2851 INSERT INTO t2 VALUES(1, 2); 2852 CREATE TABLE t3(x, y); 2853 INSERT INTO t3 VALUES(1, 2); 2854 } 2855 db close 2856 sqlite3 db test.db 2857 2858 db eval { PRAGMA mmap_size = 0 } 2859 db eval { SELECT * FROM t1 } 2860 sqlite3_db_status db CACHE_MISS 0 2861} {0 2 0} 2862 2863do_test 43.2 { 2864 db eval { SELECT * FROM t2 } 2865 sqlite3_db_status db CACHE_MISS 1 2866} {0 3 0} 2867 2868do_test 43.3 { 2869 db eval { SELECT * FROM t3 } 2870 sqlite3_db_status db CACHE_MISS 0 2871} {0 1 0} 2872 2873finish_test 2874