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