1 # 2007 August 23

     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 # This file contains tests that verify that SQLite can correctly rollback

    13 # databases after crashes when using the special IO modes triggered 

    14 # by device IOCAP flags.

    15 #

    16 # $Id: crash3.test,v 1.4 2008/07/12 14:52:20 drh Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 ifcapable !crashtest {

    22   finish_test

    23   return

    24 }

    25 

    26 proc do_test2 {name tcl res1 res2} {

    27   set script [subst -nocommands {

    28     do_test $name {

    29       set res1 {$res1}

    30       set res2 {$res2}

    31       set res [eval {$tcl}]

    32       if {[set res] eq [set res1] || [set res] eq [set res2]} {

    33         set res "{[set res1]} or {[set res2]}"

    34       }

    35       set res

    36     } {{$res1} or {$res2}}

    37   }]

    38   uplevel $script

    39 }

    40 

    41 # This block tests crash-recovery when the IOCAP_ATOMIC flags is set.

    42 #

    43 # Each iteration of the following loop sets up the database to contain

    44 # the following schema and data:

    45 #

    46 #    CREATE TABLE abc(a, b, c);

    47 #    INSERT INTO abc VALUES(1, 2, 3);

    48 #

    49 # Then execute the SQL statement, scheduling a crash for part-way through

    50 # the first sync() of either the database file or the journal file (often

    51 # the journal file is not required - meaning no crash occurs).

    52 #

    53 # After the crash (or absence of a crash), open the database and 

    54 # verify that:

    55 #

    56 #   * The integrity check passes, and

    57 #   * The contents of table abc is either {1 2 3} or the value specified

    58 #     to the right of the SQL statement below.

    59 #

    60 # The procedure is repeated 10 times for each SQL statement. Five times

    61 # with the crash scheduled for midway through the first journal sync (if 

    62 # any), and five times with the crash midway through the database sync.

    63 #

    64 set tn 1

    65 foreach {sql res2} [list \

    66   {INSERT INTO abc VALUES(4, 5, 6)}                    {1 2 3 4 5 6} \

    67   {DELETE FROM abc}                                    {}    \

    68   {INSERT INTO abc SELECT * FROM abc}                  {1 2 3 1 2 3} \

    69   {UPDATE abc SET a = 2}                               {2 2 3}       \

    70   {INSERT INTO abc VALUES(4, 5, randstr(1000,1000))}   {n/a} \

    71   {CREATE TABLE def(d, e, f)}                          {n/a} \

    72 ] {

    73   for {set ii 0} {$ii < 10} {incr ii} {

    74 

    75     db close

    76     file delete -force test.db test.db-journal

    77     sqlite3 db test.db

    78     do_test crash3-1.$tn.1 {

    79       execsql {

    80         PRAGMA page_size = 1024;

    81         BEGIN;

    82         CREATE TABLE abc(a, b, c);

    83         INSERT INTO abc VALUES(1, 2, 3);

    84         COMMIT;

    85       }

    86     } {}

    87     db close

    88   

    89     set crashfile test.db

    90     if {($ii%2)==0} { append crashfile -journal }

    91     set rand "SELECT randstr($tn,$tn);"

    92     do_test crash3-1.$tn.2 [subst {

    93       crashsql -file $crashfile -char atomic {$rand $sql}

    94       sqlite3 db test.db

    95       execsql { PRAGMA integrity_check; }

    96     }] {ok}

    97   

    98     do_test2 crash3-1.$tn.3 {

    99       execsql { SELECT * FROM abc }

   100     } {1 2 3} $res2

   101 

   102     incr tn

   103   }

   104 }

   105 

   106 # This block tests both the IOCAP_SEQUENTIAL and IOCAP_SAFE_APPEND flags.

   107 #

   108 db close

   109 file delete -force test.db test.db-journal

   110 sqlite3 db test.db

   111 do_test crash3-2.0 {

   112   execsql {

   113     BEGIN;

   114     CREATE TABLE abc(a PRIMARY KEY, b, c);

   115     CREATE TABLE def(d PRIMARY KEY, e, f);

   116     PRAGMA default_cache_size = 10;

   117     INSERT INTO abc VALUES(randstr(10,1000),randstr(10,1000),randstr(10,1000));

   118     INSERT INTO abc 

   119       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   120     INSERT INTO abc 

   121       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   122     INSERT INTO abc 

   123       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   124     INSERT INTO abc 

   125       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   126     INSERT INTO abc 

   127       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   128     INSERT INTO abc 

   129       SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) FROM abc;

   130     COMMIT;

   131   }

   132 } {}

   133 

   134 set tn 1

   135 foreach {::crashfile ::delay ::char} {

   136   test.db         1 sequential

   137   test.db         1 safe_append

   138   test.db-journal 1 sequential

   139   test.db-journal 1 safe_append

   140   test.db-journal 2 safe_append

   141   test.db-journal 2 sequential

   142   test.db-journal 3 sequential

   143   test.db-journal 3 safe_append

   144 } {

   145   for {set ii 0} {$ii < 100} {incr ii} {

   146     set ::SQL [subst {

   147       SELECT randstr($ii,$ii+10);

   148       BEGIN;

   149       DELETE FROM abc WHERE random()%5;

   150       INSERT INTO abc 

   151         SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) 

   152         FROM abc

   153         WHERE (random()%5)==0;

   154       DELETE FROM def WHERE random()%5;

   155       INSERT INTO def 

   156         SELECT randstr(10,1000),randstr(10,1000),randstr(10,1000) 

   157         FROM def

   158         WHERE (random()%5)==0;

   159       COMMIT;

   160     }]

   161 

   162     do_test crash3-2.$tn.$ii {

   163       crashsql -file $::crashfile -delay $::delay -char $::char $::SQL

   164       db close

   165       sqlite3 db test.db

   166       execsql {PRAGMA integrity_check}

   167     } {ok}

   168   }

   169   incr tn

   170 }

   171 

   172 # The following block tests an interaction between IOCAP_ATOMIC and

   173 # IOCAP_SEQUENTIAL. At one point, if both flags were set, small

   174 # journal files that contained only a single page, but were required 

   175 # for some other reason (i.e. nTrunk) were not being written to

   176 # disk.

   177 #

   178 for {set ii 0} {$ii < 10} {incr ii} {

   179   db close

   180   file delete -force test.db test.db-journal

   181   crashsql -file test.db -char {sequential atomic} {

   182     CREATE TABLE abc(a, b, c);

   183   }

   184   sqlite3 db test.db

   185   do_test crash3-3.$ii {

   186     execsql {PRAGMA integrity_check}

   187   } {ok}

   188 }

   189 

   190 finish_test