# 2010 June 15 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # set testdir [file dirname $argv0] source $testdir/tester.tcl source $testdir/lock_common.tcl source $testdir/malloc_common.tcl # # pager1-1.*: Test inter-process locking (clients in multiple processes). # # pager1-2.*: Test intra-process locking (multiple clients in this process). # # pager1-3.*: Savepoint related tests. # # pager1-4.*: Hot-journal related tests. # # pager1-5.*: Cases related to multi-file commits. # # pager1-6.*: Cases related to "PRAGMA max_page_count" # proc do_execsql_test {testname sql result} { uplevel do_test $testname [list "execsql {$sql}"] [list $result] } proc do_catchsql_test {testname sql result} { uplevel do_test $testname [list "catchsql {$sql}"] [list $result] } set a_string_counter 1 proc a_string {n} { global a_string_counter incr a_string_counter string range [string repeat "${a_string_counter}." $n] 1 $n } db func a_string a_string do_multiclient_test tn { # Create and populate a database table using connection [db]. Check # that connections [db2] and [db3] can see the schema and content. # do_test pager1-$tn.1 { sql1 { CREATE TABLE t1(a PRIMARY KEY, b); CREATE INDEX i1 ON t1(b); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); } } {} do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two} do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two} # Open a transaction and add a row using [db]. This puts [db] in # RESERVED state. Check that connections [db2] and [db3] can still # read the database content as it was before the transaction was # opened. [db] should see the inserted row. # do_test pager1-$tn.4 { sql1 { BEGIN; INSERT INTO t1 VALUES(3, 'three'); } } {} do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two} do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} # [db] still has an open write transaction. Check that this prevents # other connections (specifically [db2]) from writing to the database. # # Even if [db2] opens a transaction first, it may not write to the # database. After the attempt to write the db within a transaction, # [db2] is left with an open transaction, but not a read-lock on # the main database. So it does not prevent [db] from committing. # do_test pager1-$tn.8 { csql2 { UPDATE t1 SET a = a + 10 } } {1 {database is locked}} do_test pager1-$tn.9 { csql2 { BEGIN; UPDATE t1 SET a = a + 10; } } {1 {database is locked}} # Have [db] commit its transactions. Check the other connections can # now see the new database content. # do_test pager1-$tn.10 { sql1 { COMMIT } } {} do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} # Check that, as noted above, [db2] really did keep an open transaction # after the attempt to write the database failed. # do_test pager1-$tn.14 { csql2 { BEGIN } } {1 {cannot start a transaction within a transaction}} do_test pager1-$tn.15 { sql2 { ROLLBACK } } {} # Have [db2] open a transaction and take a read-lock on the database. # Check that this prevents [db] from writing to the database (outside # of any transaction). After this fails, check that [db3] can read # the db (showing that [db] did not take a PENDING lock etc.) # do_test pager1-$tn.15 { sql2 { BEGIN; SELECT * FROM t1; } } {1 one 2 two 3 three} do_test pager1-$tn.16 { csql1 { UPDATE t1 SET a = a + 10 } } {1 {database is locked}} do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} # This time, have [db] open a transaction before writing the database. # This works - [db] gets a RESERVED lock which does not conflict with # the SHARED lock [db2] is holding. # do_test pager1-$tn.18 { sql1 { BEGIN; UPDATE t1 SET a = a + 10; } } {} do_test pager1-$tn-19 { sql1 { PRAGMA lock_status } } {main reserved temp closed} do_test pager1-$tn-20 { sql2 { PRAGMA lock_status } } {main shared temp closed} # Check that all connections can still read the database. Only [db] sees # the updated content (as the transaction has not been committed yet). # do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} # Because [db2] still has the SHARED lock, [db] is unable to commit the # transaction. If it tries, an error is returned and the connection # upgrades to a PENDING lock. # # Once this happens, [db] can read the database and see the new content, # [db2] (still holding SHARED) can still read the old content, but [db3] # (not holding any lock) is prevented by [db]'s PENDING from reading # the database. # do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}} do_test pager1-$tn-25 { sql1 { PRAGMA lock_status } } {main pending temp closed} do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} # Have [db2] commit its read transaction, releasing the SHARED lock it # is holding. Now, neither [db2] nor [db3] may read the database (as [db] # is still holding a PENDING). # do_test pager1-$tn.29 { sql2 { COMMIT } } {} do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}} do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} # [db] is now able to commit the transaction. Once the transaction is # committed, all three connections can read the new content. # do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {} do_test pager1-$tn.26 { sql1 { COMMIT } } {} do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three} do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three} do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three} } #------------------------------------------------------------------------- # Savepoint related test cases. # do_test pager1-3.1 { faultsim_delete_and_reopen execsql { CREATE TABLE t1(a PRIMARY KEY, b); CREATE TABLE counter( i CHECK (i<5), u CHECK (u<10) ); INSERT INTO counter VALUES(0, 0); CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN UPDATE counter SET i = i+1; END; CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN UPDATE counter SET u = u+1; END; } execsql { SELECT * FROM counter } } {0 0} do_execsql_test pager1-3.2 { BEGIN; INSERT INTO t1 VALUES(1, randomblob(1500)); INSERT INTO t1 VALUES(2, randomblob(1500)); INSERT INTO t1 VALUES(3, randomblob(1500)); SELECT * FROM counter; } {3 0} do_catchsql_test pager1-3.3 { INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1 } {1 {constraint failed}} do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0} do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3} do_execsql_test pager1-3.6 { COMMIT } {} set otn 0 testvfs tv -default 1 foreach code [list { set s 512 } { set s 1024 set sql { PRAGMA journal_mode = memory } } { set s 1024 set sql { PRAGMA journal_mode = memory; PRAGMA locking_mode = exclusive; } } { set s 2048 tv devchar safe_append } { set s 4096 } { set s 4096 set sql { PRAGMA journal_mode = WAL } } { set s 8192 set sql { PRAGMA synchronous = off } }] { incr otn set sql "" tv devchar {} eval $code tv sectorsize $s do_test pager1-3.7.$otn.0 { faultsim_delete_and_reopen execsql $sql execsql { PRAGMA cache_size = 10; CREATE TABLE t1(i INTEGER PRIMARY KEY, j blob); } } {} set tn 0 set lowpoint 0 foreach x { 100 x 0 100 x 70 22 96 59 96 50 22 56 21 16 37 64 43 40 0 38 22 38 55 0 6 43 62 32 93 54 18 13 29 45 66 29 25 61 31 53 82 75 25 96 86 10 69 2 29 6 60 80 95 42 82 85 50 68 96 90 39 78 69 87 97 48 74 65 43 x 86 34 26 50 41 85 58 44 89 22 6 51 45 46 58 32 97 6 1 12 32 2 69 39 48 71 33 31 5 58 90 43 24 54 12 9 18 57 4 38 91 42 27 45 50 38 56 29 10 0 26 37 83 1 78 15 47 30 75 62 46 29 68 5 30 4 27 96 33 95 79 75 56 10 29 70 32 75 52 88 5 36 50 57 46 63 88 65 x 44 95 64 20 24 35 69 61 61 2 35 92 42 46 23 98 78 1 38 72 79 35 94 37 13 59 5 93 27 58 80 75 58 7 67 13 10 76 84 4 8 70 81 45 8 41 98 5 60 26 92 29 91 90 2 62 40 4 5 22 80 15 83 76 52 88 29 5 68 73 72 7 54 17 89 32 81 94 51 28 53 71 8 42 54 59 70 79 x } { incr tn set now [db one {SELECT count(i) FROM t1}] if {$x == "x"} { execsql { COMMIT ; BEGIN } set lowpoint $now do_test pager1.3.7.$otn.$tn { sqlite3 db2 test.db execsql { SELECT COALESCE(max(i), 0) FROM t1; PRAGMA integrity_check; } } [list $lowpoint ok] db2 close } else { if {$now > $x } { if { $x>=$lowpoint } { execsql "ROLLBACK TO sp_$x" } else { execsql "DELETE FROM t1 WHERE i>$x" set lowpoint $x } } elseif {$now < $x} { for {set k $now} {$k < $x} {incr k} { execsql "SAVEPOINT sp_$k" execsql { INSERT INTO t1(j) VALUES(randomblob(1500)) } } } do_execsql_test pager1.3.7.$otn.$tn { SELECT COALESCE(max(i), 0) FROM t1; PRAGMA integrity_check; } [list $x ok] } } } db close tv delete #------------------------------------------------------------------------- # Hot journal rollback related test cases. # # pager1.4.1.*: Test that the pager module deletes very small invalid # journal files. # # pager1.4.2.*: Test that if the master journal pointer at the end of a # hot-journal file appears to be corrupt (checksum does not # compute) the associated journal is rolled back (and no # xAccess() call to check for the presence of any master # journal file is made). # # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the # page-size or sector-size in the journal header appear to # be invalid (too large, too small or not a power of 2). # do_test pager1.4.1.1 { faultsim_delete_and_reopen execsql { CREATE TABLE x(y, z); INSERT INTO x VALUES(1, 2); } set fd [open test.db-journal w] puts -nonewline $fd "helloworld" close $fd file exists test.db-journal } {1} do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2} do_test pager1.4.1.3 { file exists test.db-journal } {0} # Set up a [testvfs] to snapshot the file-system just before SQLite # deletes the master-journal to commit a multi-file transaction. # # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets # up the file system to contain two databases, two hot-journal files and # a master-journal. # do_test pager1.4.2.1 { testvfs tstvfs -default 1 tstvfs filter xDelete tstvfs script xDeleteCallback proc xDeleteCallback {method file args} { set file [file tail $file] if { [string match *mj* $file] } { faultsim_save } } faultsim_delete_and_reopen db func a_string a_string execsql { ATTACH 'test.db2' AS aux; PRAGMA journal_mode = DELETE; PRAGMA main.cache_size = 10; PRAGMA aux.cache_size = 10; CREATE TABLE t1(a UNIQUE, b UNIQUE); CREATE TABLE aux.t2(a UNIQUE, b UNIQUE); INSERT INTO t1 VALUES(a_string(200), a_string(300)); INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; INSERT INTO t2 SELECT * FROM t1; BEGIN; INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1; INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1; INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1; INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1; REPLACE INTO t2 SELECT * FROM t1; COMMIT; } db close tstvfs delete } {} do_test pager1.4.2.2 { faultsim_restore_and_reopen execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {4 ok} do_test pager1.4.2.3 { faultsim_restore_and_reopen foreach f [glob test.db-mj*] { file delete -force $f } execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {64 ok} do_test pager1.4.2.4 { faultsim_restore_and_reopen hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {4 ok} do_test pager1.4.2.5 { faultsim_restore_and_reopen hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 foreach f [glob test.db-mj*] { file delete -force $f } execsql { SELECT count(*) FROM t1; PRAGMA integrity_check; } } {4 ok} do_test pager1.4.3.1 { testvfs tstvfs -default 1 tstvfs filter xSync tstvfs script xSyncCallback proc xSyncCallback {method file args} { set file [file tail $file] if { 0==[string match *journal $file] } { faultsim_save } } faultsim_delete_and_reopen execsql { PRAGMA journal_mode = DELETE; CREATE TABLE t1(a, b); INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 VALUES(3, 4); } db close tstvfs delete } {} foreach {tn ofst value result} { 2 20 31 {1 2 3 4} 3 20 32 {1 2 3 4} 4 20 33 {1 2 3 4} 5 20 65536 {1 2 3 4} 6 20 131072 {1 2 3 4} 7 24 511 {1 2 3 4} 8 24 513 {1 2 3 4} 9 24 65536 {1 2 3 4} 10 32 65536 {1 2} } { do_test pager1.4.3.$tn { faultsim_restore_and_reopen hexio_write test.db-journal $ofst [format %.8x $value] execsql { SELECT * FROM t1 } } $result } db close #------------------------------------------------------------------------- # The following tests deal with multi-file commits. # # pager1-5.1.*: The case where a multi-file cannot be committed because # another connection is holding a SHARED lock on one of the # files. After the SHARED lock is removed, the COMMIT succeeds. # # pager1-5.2.*: Multi-file commits with journal_mode=memory. # # pager1-5.3.*: Multi-file commits with journal_mode=memory. # # pager1-5.4.*: Check that with synchronous=normal, the master-journal file # name is added to a journal file immediately after the last # journal record. But with synchronous=full, extra unused space # is allocated between the last journal record and the # master-journal file name so that the master-journal file # name does not lie on the same sector as the last journal file # record. # # pager1-5.5.*: # do_test pager1-5.1.1 { faultsim_delete_and_reopen execsql { ATTACH 'test.db2' AS aux; CREATE TABLE t1(a, b); CREATE TABLE aux.t2(a, b); INSERT INTO t1 VALUES(17, 'Lenin'); INSERT INTO t1 VALUES(22, 'Stalin'); INSERT INTO t1 VALUES(53, 'Khrushchev'); } } {} do_test pager1-5.1.2 { execsql { BEGIN; INSERT INTO t1 VALUES(64, 'Brezhnev'); INSERT INTO t2 SELECT * FROM t1; } sqlite3 db2 test.db2 execsql { BEGIN; SELECT * FROM t2; } db2 } {} do_test pager1-5.1.3 { catchsql COMMIT } {1 {database is locked}} do_test pager1-5.1.4 { execsql COMMIT db2 execsql COMMIT execsql { SELECT * FROM t2 } db2 } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev} do_test pager1-5.1.5 { db2 close } {} do_test pager1-5.2.1 { execsql { PRAGMA journal_mode = memory; BEGIN; INSERT INTO t1 VALUES(84, 'Andropov'); INSERT INTO t2 VALUES(84, 'Andropov'); COMMIT; } } {memory} do_test pager1-5.3.1 { execsql { PRAGMA journal_mode = off; BEGIN; INSERT INTO t1 VALUES(85, 'Gorbachev'); INSERT INTO t2 VALUES(85, 'Gorbachev'); COMMIT; } } {off} do_test pager1-5.4.1 { db close testvfs tv sqlite3 db test.db -vfs tv execsql { ATTACH 'test.db2' AS aux } tv filter xDelete tv script max_journal_size tv sectorsize 512 set ::max_journal 0 proc max_journal_size {method args} { set sz 0 catch { set sz [file size test.db-journal] } if {$sz > $::max_journal} { set ::max_journal $sz } return SQLITE_OK } execsql { PRAGMA journal_mode = DELETE; PRAGMA synchronous = NORMAL; BEGIN; INSERT INTO t1 VALUES(85, 'Gorbachev'); INSERT INTO t2 VALUES(85, 'Gorbachev'); COMMIT; } set ::max_journal } [expr 2615+[string length [pwd]]] do_test pager1-5.4.2 { set ::max_journal 0 execsql { PRAGMA synchronous = full; BEGIN; DELETE FROM t1 WHERE b = 'Lenin'; DELETE FROM t2 WHERE b = 'Lenin'; COMMIT; } set ::max_journal } [expr 3111+[string length [pwd]]] db close tv delete do_test pager1-5.5.1 { sqlite3 db test.db execsql { ATTACH 'test.db2' AS aux; PRAGMA journal_mode = PERSIST; CREATE TABLE t3(a, b); INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1; UPDATE t3 SET b = randomblob(1500); } expr [file size test.db-journal] > 15000 } {1} do_test pager1-5.5.2 { execsql { PRAGMA synchronous = full; BEGIN; DELETE FROM t1 WHERE b = 'Stalin'; DELETE FROM t2 WHERE b = 'Stalin'; COMMIT; } file size test.db-journal } {0} #------------------------------------------------------------------------- # The following tests work with "PRAGMA max_page_count" # do_test pager1-6.1 { faultsim_delete_and_reopen execsql { PRAGMA max_page_count = 10; CREATE TABLE t2(a, b); CREATE TABLE t3(a, b); CREATE TABLE t4(a, b); CREATE TABLE t5(a, b); CREATE TABLE t6(a, b); CREATE TABLE t7(a, b); CREATE TABLE t8(a, b); CREATE TABLE t9(a, b); CREATE TABLE t10(a, b); } } {10} do_test pager1-6.2 { catchsql { CREATE TABLE t11(a, b); } } {1 {database or disk is full}} finish_test