sl@0: # 2007 August 23 sl@0: # sl@0: # The author disclaims copyright to this source code. In place of sl@0: # a legal notice, here is a blessing: sl@0: # sl@0: # May you do good and not evil. sl@0: # May you find forgiveness for yourself and forgive others. sl@0: # May you share freely, never taking more than you give. sl@0: # sl@0: #*********************************************************************** sl@0: # sl@0: # This file contains tests that verify that SQLite can correctly rollback sl@0: # databases after crashes when using the special IO modes triggered sl@0: # by device IOCAP flags. sl@0: # sl@0: # $Id: crash3.test,v 1.4 2008/07/12 14:52:20 drh Exp $ sl@0: sl@0: set testdir [file dirname $argv0] sl@0: source $testdir/tester.tcl sl@0: sl@0: ifcapable !crashtest { sl@0: finish_test sl@0: return sl@0: } sl@0: sl@0: proc do_test2 {name tcl res1 res2} { sl@0: set script [subst -nocommands { sl@0: do_test $name { sl@0: set res1 {$res1} sl@0: set res2 {$res2} sl@0: set res [eval {$tcl}] sl@0: if {[set res] eq [set res1] || [set res] eq [set res2]} { sl@0: set res "{[set res1]} or {[set res2]}" sl@0: } sl@0: set res sl@0: } {{$res1} or {$res2}} sl@0: }] sl@0: uplevel $script sl@0: } sl@0: sl@0: # This block tests crash-recovery when the IOCAP_ATOMIC flags is set. sl@0: # sl@0: # Each iteration of the following loop sets up the database to contain sl@0: # the following schema and data: sl@0: # sl@0: # CREATE TABLE abc(a, b, c); sl@0: # INSERT INTO abc VALUES(1, 2, 3); sl@0: # sl@0: # Then execute the SQL statement, scheduling a crash for part-way through sl@0: # the first sync() of either the database file or the journal file (often sl@0: # the journal file is not required - meaning no crash occurs). sl@0: # sl@0: # After the crash (or absence of a crash), open the database and sl@0: # verify that: sl@0: # sl@0: # * The integrity check passes, and sl@0: # * The contents of table abc is either {1 2 3} or the value specified sl@0: # to the right of the SQL statement below. sl@0: # sl@0: # The procedure is repeated 10 times for each SQL statement. Five times sl@0: # with the crash scheduled for midway through the first journal sync (if sl@0: # any), and five times with the crash midway through the database sync. sl@0: # sl@0: set tn 1 sl@0: foreach {sql res2} [list \ sl@0: {INSERT INTO abc VALUES(4, 5, 6)} {1 2 3 4 5 6} \ sl@0: {DELETE FROM abc} {} \ sl@0: {INSERT INTO abc SELECT * FROM abc} {1 2 3 1 2 3} \ sl@0: {UPDATE abc SET a = 2} {2 2 3} \ sl@0: {INSERT INTO abc VALUES(4, 5, randstr(1000,1000))} {n/a} \ sl@0: {CREATE TABLE def(d, e, f)} {n/a} \ sl@0: ] { sl@0: for {set ii 0} {$ii < 10} {incr ii} { sl@0: sl@0: db close sl@0: file delete -force test.db test.db-journal sl@0: sqlite3 db test.db sl@0: do_test crash3-1.$tn.1 { sl@0: execsql { sl@0: PRAGMA page_size = 1024; sl@0: BEGIN; sl@0: CREATE TABLE abc(a, b, c); sl@0: INSERT INTO abc VALUES(1, 2, 3); sl@0: COMMIT; sl@0: } sl@0: } {} sl@0: db close sl@0: sl@0: set crashfile test.db sl@0: if {($ii%2)==0} { append crashfile -journal } sl@0: set rand "SELECT randstr($tn,$tn);" sl@0: do_test crash3-1.$tn.2 [subst { sl@0: crashsql -file $crashfile -char atomic {$rand $sql} sl@0: sqlite3 db test.db sl@0: execsql { PRAGMA integrity_check; } sl@0: }] {ok} sl@0: sl@0: do_test2 crash3-1.$tn.3 { sl@0: execsql { SELECT * FROM abc } sl@0: } {1 2 3} $res2 sl@0: sl@0: incr tn sl@0: } sl@0: } sl@0: sl@0: # This block tests both the IOCAP_SEQUENTIAL and IOCAP_SAFE_APPEND flags. sl@0: # sl@0: db close sl@0: file delete -force test.db test.db-journal sl@0: sqlite3 db test.db sl@0: do_test crash3-2.0 { sl@0: execsql { sl@0: BEGIN; sl@0: CREATE TABLE abc(a PRIMARY KEY, b, c); sl@0: CREATE TABLE def(d PRIMARY KEY, e, f); sl@0: PRAGMA default_cache_size = 10; sl@0: INSERT INTO abc VALUES(randstr(10,1000),randstr(10,1000),randstr(10,1000)); sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc; sl@0: COMMIT; sl@0: } sl@0: } {} sl@0: sl@0: set tn 1 sl@0: foreach {::crashfile ::delay ::char} { sl@0: test.db 1 sequential sl@0: test.db 1 safe_append sl@0: test.db-journal 1 sequential sl@0: test.db-journal 1 safe_append sl@0: test.db-journal 2 safe_append sl@0: test.db-journal 2 sequential sl@0: test.db-journal 3 sequential sl@0: test.db-journal 3 safe_append sl@0: } { sl@0: for {set ii 0} {$ii < 100} {incr ii} { sl@0: set ::SQL [subst { sl@0: SELECT randstr($ii,$ii+10); sl@0: BEGIN; sl@0: DELETE FROM abc WHERE random()%5; sl@0: INSERT INTO abc sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) sl@0: FROM abc sl@0: WHERE (random()%5)==0; sl@0: DELETE FROM def WHERE random()%5; sl@0: INSERT INTO def sl@0: SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) sl@0: FROM def sl@0: WHERE (random()%5)==0; sl@0: COMMIT; sl@0: }] sl@0: sl@0: do_test crash3-2.$tn.$ii { sl@0: crashsql -file $::crashfile -delay $::delay -char $::char $::SQL sl@0: db close sl@0: sqlite3 db test.db sl@0: execsql {PRAGMA integrity_check} sl@0: } {ok} sl@0: } sl@0: incr tn sl@0: } sl@0: sl@0: # The following block tests an interaction between IOCAP_ATOMIC and sl@0: # IOCAP_SEQUENTIAL. At one point, if both flags were set, small sl@0: # journal files that contained only a single page, but were required sl@0: # for some other reason (i.e. nTrunk) were not being written to sl@0: # disk. sl@0: # sl@0: for {set ii 0} {$ii < 10} {incr ii} { sl@0: db close sl@0: file delete -force test.db test.db-journal sl@0: crashsql -file test.db -char {sequential atomic} { sl@0: CREATE TABLE abc(a, b, c); sl@0: } sl@0: sqlite3 db test.db sl@0: do_test crash3-3.$ii { sl@0: execsql {PRAGMA integrity_check} sl@0: } {ok} sl@0: } sl@0: sl@0: finish_test