AryaWu/sqlite
0
1# 2010 June 152#3# The author disclaims copyright to this source code. In place of4# 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.tcl15source $testdir/lock_common.tcl16source $testdir/malloc_common.tcl17source $testdir/wal_common.tcl18set testprefix pager119 20if {[atomic_batch_write test.db]} {21 finish_test22 return23}24ifcapable !incrblob {25 finish_test26 return27}28 29# Do not use a codec for tests in this file, as the database file is30# manipulated directly using tcl scripts (using the [hexio_write] command).31#32do_not_use_codec33 34#35# pager1-1.*: Test inter-process locking (clients in multiple processes).36#37# pager1-2.*: Test intra-process locking (multiple clients in this process).38#39# pager1-3.*: Savepoint related tests.40#41# pager1-4.*: Hot-journal related tests.42#43# pager1-5.*: Cases related to multi-file commits.44#45# pager1-6.*: Cases related to "PRAGMA max_page_count"46#47# pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"48#49# pager1-8.*: Cases using temporary and in-memory databases.50#51# pager1-9.*: Tests related to the backup API.52#53# pager1-10.*: Test that the assumed file-system sector-size is limited to54# 64KB.55#56# pager1-12.*: Tests involving "PRAGMA page_size"57#58# pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"59#60# pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"61#62# pager1-15.*: Varying sqlite3_vfs.szOsFile63#64# pager1-16.*: Varying sqlite3_vfs.mxPathname65#66# pager1-17.*: Tests related to "PRAGMA omit_readlock"67# (The omit_readlock pragma has been removed and so have68# these tests.)69#70# pager1-18.*: Test that the pager layer responds correctly if the b-tree71# requests an invalid page number (due to db corruption).72#73 74proc recursive_select {id table {script {}}} {75 set cnt 076 db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" {77 recursive_select $rowid $table $script78 incr cnt79 }80 if {$cnt==0} { eval $script }81}82 83set a_string_counter 184proc a_string {n} {85 global a_string_counter86 incr a_string_counter87 string range [string repeat "${a_string_counter}." $n] 1 $n88}89db func a_string a_string90 91do_multiclient_test tn {92 93 # Create and populate a database table using connection [db]. Check 94 # that connections [db2] and [db3] can see the schema and content.95 #96 do_test pager1-$tn.1 {97 sql1 {98 CREATE TABLE t1(a PRIMARY KEY, b);99 CREATE INDEX i1 ON t1(b);100 INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two');101 }102 } {}103 do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}104 do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}105 106 # Open a transaction and add a row using [db]. This puts [db] in107 # RESERVED state. Check that connections [db2] and [db3] can still108 # read the database content as it was before the transaction was109 # opened. [db] should see the inserted row.110 #111 do_test pager1-$tn.4 {112 sql1 {113 BEGIN;114 INSERT INTO t1 VALUES(3, 'three');115 }116 } {}117 do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}118 do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}119 120 # [db] still has an open write transaction. Check that this prevents121 # other connections (specifically [db2]) from writing to the database.122 #123 # Even if [db2] opens a transaction first, it may not write to the124 # database. After the attempt to write the db within a transaction, 125 # [db2] is left with an open transaction, but not a read-lock on126 # the main database. So it does not prevent [db] from committing.127 #128 do_test pager1-$tn.8 { 129 csql2 { UPDATE t1 SET a = a + 10 }130 } {1 {database is locked}}131 do_test pager1-$tn.9 { 132 csql2 { 133 BEGIN;134 UPDATE t1 SET a = a + 10;135 }136 } {1 {database is locked}}137 138 # Have [db] commit its transactions. Check the other connections can139 # now see the new database content.140 #141 do_test pager1-$tn.10 { sql1 { COMMIT } } {}142 do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}143 do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}144 do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}145 146 # Check that, as noted above, [db2] really did keep an open transaction147 # after the attempt to write the database failed.148 #149 do_test pager1-$tn.14 { 150 csql2 { BEGIN } 151 } {1 {cannot start a transaction within a transaction}}152 do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}153 154 # Have [db2] open a transaction and take a read-lock on the database.155 # Check that this prevents [db] from writing to the database (outside156 # of any transaction). After this fails, check that [db3] can read157 # the db (showing that [db] did not take a PENDING lock etc.)158 #159 do_test pager1-$tn.15 { 160 sql2 { BEGIN; SELECT * FROM t1; }161 } {1 one 2 two 3 three}162 do_test pager1-$tn.16 { 163 csql1 { UPDATE t1 SET a = a + 10 }164 } {1 {database is locked}}165 do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}166 167 # This time, have [db] open a transaction before writing the database.168 # This works - [db] gets a RESERVED lock which does not conflict with169 # the SHARED lock [db2] is holding.170 #171 do_test pager1-$tn.18 { 172 sql1 { 173 BEGIN; 174 UPDATE t1 SET a = a + 10; 175 }176 } {}177 do_test pager1-$tn-19 { 178 sql1 { PRAGMA lock_status } 179 } {main reserved temp closed}180 do_test pager1-$tn-20 { 181 sql2 { PRAGMA lock_status } 182 } {main shared temp closed}183 184 # Check that all connections can still read the database. Only [db] sees185 # the updated content (as the transaction has not been committed yet).186 #187 do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}188 do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}189 do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}190 191 # Because [db2] still has the SHARED lock, [db] is unable to commit the192 # transaction. If it tries, an error is returned and the connection 193 # upgrades to a PENDING lock.194 #195 # Once this happens, [db] can read the database and see the new content,196 # [db2] (still holding SHARED) can still read the old content, but [db3]197 # (not holding any lock) is prevented by [db]'s PENDING from reading198 # the database.199 #200 do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}201 do_test pager1-$tn-25 { 202 sql1 { PRAGMA lock_status } 203 } {main pending temp closed}204 do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}205 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}206 do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}207 208 # Have [db2] commit its read transaction, releasing the SHARED lock it209 # is holding. Now, neither [db2] nor [db3] may read the database (as [db]210 # is still holding a PENDING).211 #212 do_test pager1-$tn.29 { sql2 { COMMIT } } {}213 do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}214 do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}215 216 # [db] is now able to commit the transaction. Once the transaction is 217 # committed, all three connections can read the new content.218 #219 do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}220 do_test pager1-$tn.26 { sql1 { COMMIT } } {}221 do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}222 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}223 do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}224 225 # Install a busy-handler for connection [db].226 #227 set ::nbusy [list]228 proc busy {n} {229 lappend ::nbusy $n230 if {$n>5} { sql2 COMMIT }231 return 0232 }233 db busy busy234 235 do_test pager1-$tn.29 { 236 sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') } 237 } {}238 do_test pager1-$tn.30 { 239 sql2 { BEGIN ; SELECT * FROM t1 } 240 } {21 one 22 two 23 three}241 do_test pager1-$tn.31 { sql1 COMMIT } {}242 do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}243}244 245#-------------------------------------------------------------------------246# Savepoint related test cases.247#248# pager1-3.1.2.*: Force a savepoint rollback to cause the database file249# to grow.250#251# pager1-3.1.3.*: Use a journal created in synchronous=off mode as part252# of a savepoint rollback.253# 254do_test pager1-3.1.1 {255 faultsim_delete_and_reopen256 execsql {257 CREATE TABLE t1(a PRIMARY KEY, b);258 CREATE TABLE counter(259 i CHECK (i<5), 260 u CHECK (u<10)261 );262 INSERT INTO counter VALUES(0, 0);263 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN264 UPDATE counter SET i = i+1;265 END;266 CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN267 UPDATE counter SET u = u+1;268 END;269 }270 execsql { SELECT * FROM counter }271} {0 0}272 273do_execsql_test pager1-3.1.2 {274 PRAGMA cache_size = 10;275 BEGIN;276 INSERT INTO t1 VALUES(1, randomblob(1500));277 INSERT INTO t1 VALUES(2, randomblob(1500));278 INSERT INTO t1 VALUES(3, randomblob(1500));279 SELECT * FROM counter;280} {3 0}281do_catchsql_test pager1-3.1.3 {282 INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1283} {1 {CHECK constraint failed: i<5}}284do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}285do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}286do_execsql_test pager1-3.6 { COMMIT } {}287 288foreach {tn sql tcl} {289 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {290 testvfs tv -default 1291 tv devchar safe_append292 }293 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {294 testvfs tv -default 1295 tv devchar sequential296 }297 9 { PRAGMA synchronous = FULL } { }298 10 { PRAGMA synchronous = NORMAL } { }299 11 { PRAGMA synchronous = OFF } { }300 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }301 13 { PRAGMA synchronous = FULL } {302 testvfs tv -default 1303 tv devchar sequential304 }305 14 { PRAGMA locking_mode = EXCLUSIVE } {306 }307} {308 do_test pager1-3.$tn.1 {309 eval $tcl310 faultsim_delete_and_reopen311 db func a_string a_string312 execsql $sql313 execsql {314 PRAGMA auto_vacuum = 2;315 PRAGMA cache_size = 10;316 CREATE TABLE z(x INTEGER PRIMARY KEY, y);317 BEGIN;318 INSERT INTO z VALUES(NULL, a_string(800));319 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2320 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4321 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8322 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16323 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32324 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64325 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128326 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256327 COMMIT;328 }329 execsql { PRAGMA auto_vacuum }330 } {2}331 do_execsql_test pager1-3.$tn.2 {332 BEGIN;333 INSERT INTO z VALUES(NULL, a_string(800));334 INSERT INTO z VALUES(NULL, a_string(800));335 SAVEPOINT one;336 UPDATE z SET y = NULL WHERE x>256;337 PRAGMA incremental_vacuum;338 SELECT count(*) FROM z WHERE x < 100;339 ROLLBACK TO one;340 COMMIT;341 } {99}342 343 do_execsql_test pager1-3.$tn.3 {344 BEGIN;345 SAVEPOINT one;346 UPDATE z SET y = y||x;347 ROLLBACK TO one;348 COMMIT;349 SELECT count(*) FROM z;350 } {258}351 352 do_execsql_test pager1-3.$tn.4 {353 SAVEPOINT one;354 UPDATE z SET y = y||x;355 ROLLBACK TO one;356 } {}357 do_execsql_test pager1-3.$tn.5 {358 SELECT count(*) FROM z;359 RELEASE one;360 PRAGMA integrity_check;361 } {258 ok}362 363 do_execsql_test pager1-3.$tn.6 {364 SAVEPOINT one;365 RELEASE one;366 } {}367 368 db close369 catch { tv delete }370}371 372#-------------------------------------------------------------------------373# Hot journal rollback related test cases.374#375# pager1.4.1.*: Test that the pager module deletes very small invalid376# journal files.377#378# pager1.4.2.*: Test that if the master journal pointer at the end of a379# hot-journal file appears to be corrupt (checksum does not380# compute) the associated journal is rolled back (and no381# xAccess() call to check for the presence of any master 382# journal file is made).383#384# pager1.4.3.*: Test that the contents of a hot-journal are ignored if the385# page-size or sector-size in the journal header appear to386# be invalid (too large, too small or not a power of 2).387#388# pager1.4.4.*: Test hot-journal rollback of journal file with a master389# journal pointer generated in various "PRAGMA synchronous"390# modes.391#392# pager1.4.5.*: Test that hot-journal rollback stops if it encounters a393# journal-record for which the checksum fails.394#395# pager1.4.6.*: Test that when rolling back a hot-journal that contains a396# master journal pointer, the master journal file is deleted397# after all the hot-journals that refer to it are deleted.398#399# pager1.4.7.*: Test that if a hot-journal file exists but a client can400# open it for reading only, the database cannot be accessed and401# SQLITE_CANTOPEN is returned.402# 403do_test pager1.4.1.1 {404 faultsim_delete_and_reopen405 execsql { 406 CREATE TABLE x(y, z);407 INSERT INTO x VALUES(1, 2);408 }409 set fd [open test.db-journal w]410 puts -nonewline $fd "helloworld"411 close $fd412 file exists test.db-journal413} {1}414do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}415do_test pager1.4.1.3 { file exists test.db-journal } {0}416 417# Set up a [testvfs] to snapshot the file-system just before SQLite418# deletes the master-journal to commit a multi-file transaction.419#420# In subsequent test cases, invoking [faultsim_restore_and_reopen] sets421# up the file system to contain two databases, two hot-journal files and422# a master-journal.423#424do_test pager1.4.2.1 {425 testvfs tstvfs -default 1426 tstvfs filter xDelete427 tstvfs script xDeleteCallback428 proc xDeleteCallback {method file args} {429 set file [file tail $file]430 if { [string match *mj* $file] } { faultsim_save }431 }432 faultsim_delete_and_reopen433 db func a_string a_string434 execsql {435 ATTACH 'test.db2' AS aux;436 PRAGMA journal_mode = DELETE;437 PRAGMA main.cache_size = 10;438 PRAGMA aux.cache_size = 10;439 CREATE TABLE t1(a UNIQUE, b UNIQUE);440 CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);441 INSERT INTO t1 VALUES(a_string(200), a_string(300));442 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;443 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;444 INSERT INTO t2 SELECT * FROM t1;445 BEGIN;446 INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;447 INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;448 INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;449 INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;450 REPLACE INTO t2 SELECT * FROM t1;451 COMMIT;452 }453 db close454 tstvfs delete455} {}456 457if {$::tcl_platform(os) ne "Windows NT"} {458do_test pager1.4.2.2 {459 faultsim_restore_and_reopen460 execsql {461 SELECT count(*) FROM t1;462 PRAGMA integrity_check;463 }464} {4 ok}465do_test pager1.4.2.3 {466 faultsim_restore_and_reopen467 foreach f [glob test.db-mj*] { forcedelete $f }468 execsql {469 SELECT count(*) FROM t1;470 PRAGMA integrity_check;471 }472} {64 ok}473do_test pager1.4.2.4 {474 faultsim_restore_and_reopen475 hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456476 execsql {477 SELECT count(*) FROM t1;478 PRAGMA integrity_check;479 }480} {4 ok}481do_test pager1.4.2.5 {482 faultsim_restore_and_reopen483 hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456484 foreach f [glob test.db-mj*] { forcedelete $f }485 execsql {486 SELECT count(*) FROM t1;487 PRAGMA integrity_check;488 }489} {4 ok}490}491 492do_test pager1.4.3.1 {493 testvfs tstvfs -default 1494 tstvfs filter xSync495 tstvfs script xSyncCallback496 proc xSyncCallback {method file args} {497 set file [file tail $file]498 if { 0==[string match *journal $file] } { faultsim_save }499 }500 faultsim_delete_and_reopen501 execsql {502 PRAGMA journal_mode = DELETE;503 CREATE TABLE t1(a, b);504 INSERT INTO t1 VALUES(1, 2);505 INSERT INTO t1 VALUES(3, 4);506 }507 db close508 tstvfs delete509} {}510 511foreach {tn ofst value result} {512 2 20 31 {1 2 3 4}513 3 20 32 {1 2 3 4}514 4 20 33 {1 2 3 4}515 5 20 65536 {1 2 3 4}516 6 20 131072 {1 2 3 4}517 518 7 24 511 {1 2 3 4}519 8 24 513 {1 2 3 4}520 9 24 131072 {1 2 3 4}521 522 10 32 65536 {1 2}523} {524 do_test pager1.4.3.$tn {525 faultsim_restore_and_reopen526 hexio_write test.db-journal $ofst [format %.8x $value]527 execsql { SELECT * FROM t1 }528 } $result529}530db close531 532# Set up a VFS that snapshots the file-system just before a master journal533# file is deleted to commit a multi-file transaction. Specifically, the534# file-system is saved just before the xDelete() call to remove the 535# master journal file from the file-system.536#537set pwd [get_pwd]538testvfs tv -default 1539tv script copy_on_mj_delete540set ::mj_filename_length 0541set ::mj_delete_cnt 0542proc copy_on_mj_delete {method filename args} {543 if {[string match *mj* [file tail $filename]]} { 544 #545 # NOTE: Is the file name relative? If so, add the length of the current546 # directory.547 #548 if {[is_relative_file $filename]} {549 set ::mj_filename_length \550 [expr {[string length $filename] + [string length $::pwd]}]551 } else {552 set ::mj_filename_length [string length $filename]553 }554 faultsim_save 555 incr ::mj_delete_cnt556 }557 return SQLITE_OK558}559 560foreach {tn1 tcl} {561 1 { set prefix "test.db" }562 2 { 563 # This test depends on the underlying VFS being able to open paths564 # 512 bytes in length. The idea is to create a hot-journal file that565 # contains a master-journal pointer so large that it could contain566 # a valid page record (if the file page-size is 512 bytes). So as to567 # make sure SQLite doesn't get confused by this.568 #569 set nPadding [expr 511 - $::mj_filename_length]570 if {$tcl_platform(platform) eq "windows"} {571 # TBD need to figure out how to do this correctly for Windows!!!572 set nPadding [expr 255 - $::mj_filename_length]573 }574 575 # We cannot just create a really long database file name to open, as576 # Linux limits a single component of a path to 255 bytes by default577 # (and presumably other systems have limits too). So create a directory578 # hierarchy to work in.579 #580 set dirname "d123456789012345678901234567890/"581 set nDir [expr $nPadding / 32]582 if { $nDir } {583 set p [string repeat $dirname $nDir]584 file mkdir $p585 cd $p586 }587 588 set padding [string repeat x [expr $nPadding %32]]589 set prefix "test.db${padding}"590 }591} {592 eval $tcl593 foreach {tn2 sql usesMJ} {594 o { 595 PRAGMA main.synchronous=OFF;596 PRAGMA aux.synchronous=OFF;597 PRAGMA journal_mode = DELETE;598 } 0599 o512 { 600 PRAGMA main.synchronous=OFF;601 PRAGMA aux.synchronous=OFF;602 PRAGMA main.page_size = 512;603 PRAGMA aux.page_size = 512;604 PRAGMA journal_mode = DELETE;605 } 0606 n { 607 PRAGMA main.synchronous=NORMAL;608 PRAGMA aux.synchronous=NORMAL;609 PRAGMA journal_mode = DELETE;610 } 1611 f { 612 PRAGMA main.synchronous=FULL;613 PRAGMA aux.synchronous=FULL;614 PRAGMA journal_mode = DELETE;615 } 1616 w1 { 617 PRAGMA main.synchronous=NORMAL;618 PRAGMA aux.synchronous=NORMAL;619 PRAGMA journal_mode = WAL;620 } 0621 w2 { 622 PRAGMA main.synchronous=NORMAL;623 PRAGMA aux.synchronous=NORMAL;624 PRAGMA main.journal_mode=DELETE;625 PRAGMA aux.journal_mode=WAL;626 } 0627 o1a { 628 PRAGMA main.synchronous=FULL;629 PRAGMA aux.synchronous=OFF;630 PRAGMA journal_mode=DELETE;631 } 0632 o1b { 633 PRAGMA main.synchronous=OFF;634 PRAGMA aux.synchronous=NORMAL;635 PRAGMA journal_mode=DELETE;636 } 0637 m1 { 638 PRAGMA main.synchronous=NORMAL;639 PRAGMA aux.synchronous=NORMAL;640 PRAGMA main.journal_mode=DELETE;641 PRAGMA aux.journal_mode = MEMORY;642 } 0643 t1 { 644 PRAGMA main.synchronous=NORMAL;645 PRAGMA aux.synchronous=NORMAL;646 PRAGMA main.journal_mode=DELETE;647 PRAGMA aux.journal_mode = TRUNCATE;648 } 1649 p1 { 650 PRAGMA main.synchronous=NORMAL;651 PRAGMA aux.synchronous=NORMAL;652 PRAGMA main.journal_mode=DELETE;653 PRAGMA aux.journal_mode = PERSIST;654 } 1655 } {656 657 set tn "${tn1}.${tn2}"658 659 # Set up a connection to have two databases, test.db (main) and 660 # test.db2 (aux). Then run a multi-file transaction on them. The661 # VFS will snapshot the file-system just before the master-journal662 # file is deleted to commit the transaction.663 #664 tv filter xDelete665 do_test pager1-4.4.$tn.1 {666 set ::mj_delete_cnt 0667 faultsim_delete_and_reopen $prefix668 execsql "669 ATTACH '${prefix}2' AS aux;670 $sql671 CREATE TABLE a(x);672 CREATE TABLE aux.b(x);673 INSERT INTO a VALUES('double-you');674 INSERT INTO a VALUES('why');675 INSERT INTO a VALUES('zed');676 INSERT INTO b VALUES('won');677 INSERT INTO b VALUES('too');678 INSERT INTO b VALUES('free');679 "680 execsql {681 BEGIN;682 INSERT INTO a SELECT * FROM b WHERE rowid<=3;683 INSERT INTO b SELECT * FROM a WHERE rowid<=3;684 COMMIT;685 }686 } {}687 tv filter {}688 689 # Verify that a master journal was deleted only for those cases where690 # master journals really ought to be used691 #692 do_test pager1-4.4.$tn.1b {693 set ::mj_delete_cnt694 } $usesMJ695 696 # Check that the transaction was committed successfully.697 #698 do_execsql_test pager1-4.4.$tn.2 {699 SELECT * FROM a700 } {double-you why zed won too free}701 do_execsql_test pager1-4.4.$tn.3 {702 SELECT * FROM b703 } {won too free double-you why zed}704 705 if {$usesMJ} {706 # Restore the file-system and reopen the databases. Check that it now707 # appears that the transaction was not committed (because the file-system708 # was restored to the state where it had not been).709 #710 do_test pager1-4.4.$tn.4 {711 faultsim_restore_and_reopen $prefix712 execsql "ATTACH '${prefix}2' AS aux"713 } {}714 do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}715 do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}716 }717 718 # Restore the file-system again. This time, before reopening the databases,719 # delete the master-journal file from the file-system. It now appears that720 # the transaction was committed (no master-journal file == no rollback).721 #722 do_test pager1-4.4.$tn.7 {723 if {$::mj_delete_cnt>0} {724 faultsim_restore_and_reopen $prefix725 foreach f [glob ${prefix}-mj*] { forcedelete $f }726 } else {727 db close728 sqlite3 db $prefix729 }730 execsql "ATTACH '${prefix}2' AS aux"731 glob -nocomplain ${prefix}-mj*732 } {}733 do_execsql_test pager1-4.4.$tn.8 {734 SELECT * FROM a735 } {double-you why zed won too free}736 do_execsql_test pager1-4.4.$tn.9 {737 SELECT * FROM b738 } {won too free double-you why zed}739 }740 741 cd $pwd742}743db close744tv delete745forcedelete $dirname746 747# Set up a VFS to make a copy of the file-system just before deleting a748# journal file to commit a transaction. The transaction modifies exactly749# two database pages (and page 1 - the change counter).750#751testvfs tv -default 1752tv sectorsize 512753tv script copy_on_journal_delete754tv filter xDelete755proc copy_on_journal_delete {method filename args} {756 if {[string match *journal $filename]} faultsim_save 757 return SQLITE_OK758}759faultsim_delete_and_reopen760do_execsql_test pager1.4.5.1 {761 PRAGMA journal_mode = DELETE;762 PRAGMA page_size = 1024;763 CREATE TABLE t1(a, b);764 CREATE TABLE t2(a, b);765 INSERT INTO t1 VALUES('I', 'II');766 INSERT INTO t2 VALUES('III', 'IV');767 BEGIN;768 INSERT INTO t1 VALUES(1, 2);769 INSERT INTO t2 VALUES(3, 4);770 COMMIT;771} {delete}772tv filter {}773 774# Check the transaction was committed:775#776do_execsql_test pager1.4.5.2 {777 SELECT * FROM t1;778 SELECT * FROM t2;779} {I II 1 2 III IV 3 4}780 781# Now try four tests:782#783# pager1-4.5.3: Restore the file-system. Check that the whole transaction 784# is rolled back.785#786# pager1-4.5.4: Restore the file-system. Corrupt the first record in the787# journal. Check the transaction is not rolled back.788#789# pager1-4.5.5: Restore the file-system. Corrupt the second record in the790# journal. Check that the first record in the transaction is 791# played back, but not the second.792#793# pager1-4.5.6: Restore the file-system. Try to open the database with a794# readonly connection. This should fail, as a read-only795# connection cannot roll back the database file.796#797faultsim_restore_and_reopen798do_execsql_test pager1.4.5.3 {799 SELECT * FROM t1;800 SELECT * FROM t2;801} {I II III IV}802faultsim_restore_and_reopen803hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF804do_execsql_test pager1.4.5.4 {805 SELECT * FROM t1;806 SELECT * FROM t2;807} {I II 1 2 III IV 3 4}808faultsim_restore_and_reopen809hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF810do_execsql_test pager1.4.5.5 {811 SELECT * FROM t1;812 SELECT * FROM t2;813} {I II III IV 3 4}814 815faultsim_restore_and_reopen816db close817sqlite3 db test.db -readonly 1818do_catchsql_test pager1.4.5.6 {819 SELECT * FROM t1;820 SELECT * FROM t2;821} {1 {attempt to write a readonly database}}822db close823 824# Snapshot the file-system just before multi-file commit. Save the name825# of the master journal file in $::mj_filename.826#827tv script copy_on_mj_delete828tv filter xDelete829proc copy_on_mj_delete {method filename args} {830 if {[string match *mj* [file tail $filename]]} { 831 set ::mj_filename $filename832 faultsim_save 833 }834 return SQLITE_OK835}836do_test pager1.4.6.1 {837 faultsim_delete_and_reopen838 execsql {839 PRAGMA journal_mode = DELETE;840 ATTACH 'test.db2' AS two;841 CREATE TABLE t1(a, b);842 CREATE TABLE two.t2(a, b);843 INSERT INTO t1 VALUES(1, 't1.1');844 INSERT INTO t2 VALUES(1, 't2.1');845 BEGIN;846 UPDATE t1 SET b = 't1.2';847 UPDATE t2 SET b = 't2.2';848 COMMIT;849 }850 tv filter {}851 db close852} {}853 854faultsim_restore_and_reopen855do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1}856do_test pager1.4.6.3 { file exists $::mj_filename } {1}857do_execsql_test pager1.4.6.4 {858 ATTACH 'test.db2' AS two;859 SELECT * FROM t2;860} {1 t2.1}861do_test pager1.4.6.5 { file exists $::mj_filename } {0}862 863faultsim_restore_and_reopen864db close865do_test pager1.4.6.8 {866 set ::mj_filename1 $::mj_filename867 tv filter xDelete868 sqlite3 db test.db2869 execsql {870 PRAGMA journal_mode = DELETE;871 ATTACH 'test.db3' AS three;872 CREATE TABLE three.t3(a, b);873 INSERT INTO t3 VALUES(1, 't3.1');874 BEGIN;875 UPDATE t2 SET b = 't2.3';876 UPDATE t3 SET b = 't3.3';877 COMMIT;878 }879 expr {$::mj_filename1 != $::mj_filename}880} {1}881faultsim_restore_and_reopen882tv filter {}883 884# The file-system now contains:885#886# * three databases887# * three hot-journal files888# * two master-journal files.889#890# The hot-journals associated with test.db2 and test.db3 point to891# master journal $::mj_filename. The hot-journal file associated with892# test.db points to master journal $::mj_filename1. So reading from893# test.db should delete $::mj_filename1.894#895do_test pager1.4.6.9 {896 lsort [glob test.db*]897} [lsort [list \898 test.db test.db2 test.db3 \899 test.db-journal test.db2-journal test.db3-journal \900 [file tail $::mj_filename] [file tail $::mj_filename1]901]]902 903# The master-journal $::mj_filename1 contains pointers to test.db and 904# test.db2. However the hot-journal associated with test.db2 points to905# a different master-journal. Therefore, reading from test.db only should906# be enough to cause SQLite to delete $::mj_filename1.907#908do_test pager1.4.6.10 { file exists $::mj_filename } {1}909do_test pager1.4.6.11 { file exists $::mj_filename1 } {1}910do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1}911do_test pager1.4.6.13 { file exists $::mj_filename } {1}912do_test pager1.4.6.14 { file exists $::mj_filename1 } {0}913 914do_execsql_test pager1.4.6.12 {915 ATTACH 'test.db2' AS two;916 SELECT * FROM t2;917} {1 t2.1}918do_test pager1.4.6.13 { file exists $::mj_filename } {1}919do_execsql_test pager1.4.6.14 {920 ATTACH 'test.db3' AS three;921 SELECT * FROM t3;922} {1 t3.1}923do_test pager1.4.6.15 { file exists $::mj_filename } {0}924 925db close926tv delete927 928testvfs tv -default 1929tv sectorsize 512930tv script copy_on_journal_delete931tv filter xDelete932proc copy_on_journal_delete {method filename args} {933 if {[string match *journal $filename]} faultsim_save 934 return SQLITE_OK935}936faultsim_delete_and_reopen937do_execsql_test pager1.4.7.1 {938 PRAGMA journal_mode = DELETE;939 CREATE TABLE t1(x PRIMARY KEY, y);940 CREATE INDEX i1 ON t1(y);941 INSERT INTO t1 VALUES('I', 'one');942 INSERT INTO t1 VALUES('II', 'four');943 INSERT INTO t1 VALUES('III', 'nine');944 BEGIN;945 INSERT INTO t1 VALUES('IV', 'sixteen');946 INSERT INTO t1 VALUES('V' , 'twentyfive');947 COMMIT;948} {delete}949tv filter {}950db close951tv delete 952catch {953 test_syscall install fchmod954 test_syscall fault 1 1955}956do_test pager1.4.7.2 {957 faultsim_restore_and_reopen958 catch {file attributes test.db-journal -permissions r--------}959 catch {file attributes test.db-journal -readonly 1}960 catchsql { SELECT * FROM t1 }961} {1 {unable to open database file}}962catch {963 test_syscall reset964 test_syscall fault 0 0965}966do_test pager1.4.7.3 {967 db close968 catch {file attributes test.db-journal -permissions rw-rw-rw-}969 catch {file attributes test.db-journal -readonly 0}970 delete_file test.db-journal971 file exists test.db-journal972} {0}973do_test pager1.4.8.1 {974 catch {file attributes test.db -permissions r--------}975 catch {file attributes test.db -readonly 1}976 sqlite3 db test.db977 db eval { SELECT * FROM t1 }978 sqlite3_db_readonly db main979} {1}980do_test pager1.4.8.2 {981 sqlite3_db_readonly db xyz982} {-1}983do_test pager1.4.8.3 {984 db close985 catch {file attributes test.db -readonly 0}986 catch {file attributes test.db -permissions rw-rw-rw-} msg987 sqlite3 db test.db988 db eval { SELECT * FROM t1 }989 sqlite3_db_readonly db main990} {0}991 992#-------------------------------------------------------------------------993# The following tests deal with multi-file commits.994#995# pager1-5.1.*: The case where a multi-file cannot be committed because996# another connection is holding a SHARED lock on one of the997# files. After the SHARED lock is removed, the COMMIT succeeds.998#999# pager1-5.2.*: Multi-file commits with journal_mode=memory.1000#1001# pager1-5.3.*: Multi-file commits with journal_mode=memory.1002#1003# pager1-5.4.*: Check that with synchronous=normal, the master-journal file1004# name is added to a journal file immediately after the last1005# journal record. But with synchronous=full, extra unused space1006# is allocated between the last journal record and the 1007# master-journal file name so that the master-journal file1008# name does not lie on the same sector as the last journal file1009# record.1010#1011# pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is1012# truncated to zero bytes when a multi-file transaction is 1013# committed (instead of the first couple of bytes being zeroed).1014#1015#1016do_test pager1-5.1.1 {1017 faultsim_delete_and_reopen1018 execsql {1019 ATTACH 'test.db2' AS aux;1020 CREATE TABLE t1(a, b);1021 CREATE TABLE aux.t2(a, b);1022 INSERT INTO t1 VALUES(17, 'Lenin');1023 INSERT INTO t1 VALUES(22, 'Stalin');1024 INSERT INTO t1 VALUES(53, 'Khrushchev');1025 }1026} {}1027do_test pager1-5.1.2 {1028 execsql {1029 BEGIN;1030 INSERT INTO t1 VALUES(64, 'Brezhnev');1031 INSERT INTO t2 SELECT * FROM t1;1032 }1033 sqlite3 db2 test.db21034 execsql {1035 BEGIN;1036 SELECT * FROM t2;1037 } db21038} {}1039do_test pager1-5.1.3 {1040 catchsql COMMIT1041} {1 {database is locked}}1042do_test pager1-5.1.4 {1043 execsql COMMIT db21044 execsql COMMIT1045 execsql { SELECT * FROM t2 } db21046} {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}1047do_test pager1-5.1.5 {1048 db2 close1049} {}1050 1051do_test pager1-5.2.1 {1052 execsql {1053 PRAGMA journal_mode = memory;1054 BEGIN;1055 INSERT INTO t1 VALUES(84, 'Andropov');1056 INSERT INTO t2 VALUES(84, 'Andropov');1057 COMMIT;1058 }1059} {memory}1060do_test pager1-5.3.1 {1061 execsql {1062 PRAGMA journal_mode = off;1063 BEGIN;1064 INSERT INTO t1 VALUES(85, 'Gorbachev');1065 INSERT INTO t2 VALUES(85, 'Gorbachev');1066 COMMIT;1067 }1068} {off}1069 1070do_test pager1-5.4.1 {1071 db close1072 testvfs tv1073 sqlite3 db test.db -vfs tv1074 execsql { ATTACH 'test.db2' AS aux }1075 1076 tv filter xDelete1077 tv script max_journal_size1078 tv sectorsize 5121079 set ::max_journal 01080 proc max_journal_size {method args} {1081 set sz 01082 catch { set sz [file size test.db-journal] }1083 if {$sz > $::max_journal} {1084 set ::max_journal $sz1085 }1086 return SQLITE_OK1087 }1088 execsql {1089 PRAGMA journal_mode = DELETE;1090 PRAGMA synchronous = NORMAL;1091 BEGIN;1092 INSERT INTO t1 VALUES(85, 'Gorbachev');1093 INSERT INTO t2 VALUES(85, 'Gorbachev');1094 COMMIT;1095 }1096 1097 # The size of the journal file is now:1098 # 1099 # 1) 512 byte header +1100 # 2) 2 * (1024+8) byte records +1101 # 3) 20+N bytes of master-journal pointer, where N is the size of 1102 # the master-journal name encoded as utf-8 with no nul term.1103 #1104 set mj_pointer [expr {1105 20 + [string length "test.db-mjXXXXXX9XX"]1106 }]1107 #1108 # NOTE: For item 3 above, if the current SQLite VFS lacks the concept of a1109 # current directory, the length of the current directory name plus 11110 # character for the directory separator character are NOT counted as1111 # part of the total size; otherwise, they are.1112 #1113 ifcapable curdir {1114 set mj_pointer [expr {$mj_pointer + [string length [get_pwd]] + 1}]1115 }1116 expr {$::max_journal==(512+2*(1024+8)+$mj_pointer)}1117} 11118do_test pager1-5.4.2 {1119 set ::max_journal 01120 execsql {1121 PRAGMA synchronous = full;1122 BEGIN;1123 DELETE FROM t1 WHERE b = 'Lenin';1124 DELETE FROM t2 WHERE b = 'Lenin';1125 COMMIT;1126 }1127 1128 # In synchronous=full mode, the master-journal pointer is not written1129 # directly after the last record in the journal file. Instead, it is1130 # written starting at the next (in this case 512 byte) sector boundary.1131 #1132 set mj_pointer [expr {1133 20 + [string length "test.db-mjXXXXXX9XX"]1134 }]1135 #1136 # NOTE: If the current SQLite VFS lacks the concept of a current directory,1137 # the length of the current directory name plus 1 character for the1138 # directory separator character are NOT counted as part of the total1139 # size; otherwise, they are.1140 #1141 ifcapable curdir {1142 set mj_pointer [expr {$mj_pointer + [string length [get_pwd]] + 1}]1143 }1144 expr {$::max_journal==(((512+2*(1024+8)+511)/512)*512 + $mj_pointer)}1145} 11146db close1147tv delete1148 1149do_test pager1-5.5.1 {1150 sqlite3 db test.db1151 execsql { 1152 ATTACH 'test.db2' AS aux;1153 PRAGMA journal_mode = PERSIST;1154 CREATE TABLE t3(a, b);1155 INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;1156 UPDATE t3 SET b = randomblob(1501);1157 }1158 expr [file size test.db-journal] > 150001159} {1}1160do_test pager1-5.5.2 {1161 execsql {1162 PRAGMA synchronous = full;1163 BEGIN;1164 DELETE FROM t1 WHERE b = 'Stalin';1165 DELETE FROM t2 WHERE b = 'Stalin';1166 COMMIT;1167 }1168 file size test.db-journal1169} {0}1170 1171 1172#-------------------------------------------------------------------------1173# The following tests work with "PRAGMA max_page_count"1174#1175do_test pager1-6.1 {1176 faultsim_delete_and_reopen1177 execsql {1178 PRAGMA auto_vacuum = none;1179 PRAGMA max_page_count = 10;1180 CREATE TABLE t2(a, b);1181 CREATE TABLE t3(a, b);1182 CREATE TABLE t4(a, b);1183 CREATE TABLE t5(a, b);1184 CREATE TABLE t6(a, b);1185 CREATE TABLE t7(a, b);1186 CREATE TABLE t8(a, b);1187 CREATE TABLE t9(a, b);1188 CREATE TABLE t10(a, b);1189 }1190} {10}1191do_catchsql_test pager1-6.2 {1192 CREATE TABLE t11(a, b)1193} {1 {database or disk is full}}1194do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}1195do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}1196do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}1197do_execsql_test pager1-6.7 {1198 BEGIN;1199 INSERT INTO t11 VALUES(1, 2);1200 PRAGMA max_page_count = 13;