1 # 2008 August 27

     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 implements regression tests for SQLite library.  The

    13 # focus of this script is transactions

    14 #

    15 # $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $

    16 #

    17 set testdir [file dirname $argv0]

    18 source $testdir/tester.tcl

    19 

    20 # A procedure to scramble the elements of list $inlist into a random order.

    21 #

    22 proc scramble {inlist} {

    23   set y {}

    24   foreach x $inlist {

    25     lappend y [list [expr {rand()}] $x]

    26   }

    27   set y [lsort $y]

    28   set outlist {}

    29   foreach x $y {

    30     lappend outlist [lindex $x 1]

    31   }

    32   return $outlist

    33 }

    34 

    35 # Generate a UUID using randomness.

    36 #

    37 expr srand(1)

    38 proc random_uuid {} {

    39   set u {}

    40   for {set i 0} {$i<5} {incr i} {

    41     append u [format %06x [expr {int(rand()*16777216)}]]

    42   }

    43   return $u

    44 }

    45 

    46 # Compute hashes on the u1 and u2 fields of the sample data.

    47 #

    48 proc hash1 {} {

    49   global data

    50   set x ""

    51   foreach rec [lsort -integer -index 0 $data] {

    52     append x [lindex $rec 1]

    53   }

    54   return [md5 $x]

    55 }

    56 proc hash2 {} {

    57   global data

    58   set x ""

    59   foreach rec [lsort -integer -index 0 $data] {

    60     append x [lindex $rec 3]

    61   }

    62   return [md5 $x]

    63 }

    64 

    65 # Create the initial data set

    66 #

    67 unset -nocomplain data i max_rowid todel n rec max1 id origres newres

    68 unset -nocomplain inssql modsql s j z

    69 set data {}

    70 for {set i 0} {$i<400} {incr i} {

    71   set rec [list $i [random_uuid] [expr {int(rand()*5000)+1000}] [random_uuid]]

    72   lappend data $rec

    73 }

    74 set max_rowid [expr {$i-1}]

    75 

    76 # Create the T1 table used to hold test data.  Populate that table with

    77 # the initial data set and check hashes to make sure everything is correct.

    78 #

    79 do_test trans2-1.1 {

    80   execsql {

    81     PRAGMA cache_size=100;

    82     CREATE TABLE t1(

    83       id INTEGER PRIMARY KEY,

    84       u1 TEXT UNIQUE,

    85       z BLOB NOT NULL,

    86       u2 TEXT UNIQUE

    87     );

    88   }

    89   foreach rec [scramble $data] {

    90     foreach {id u1 z u2} $rec break

    91     db eval {INSERT INTO t1 VALUES($id,$u1,zeroblob($z),$u2)}

    92   }

    93   db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

    94 } [list [hash1] [hash2]]

    95 

    96 # Repeat the main test loop multiple times.

    97 #

    98 for {set i 2} {$i<=30} {incr i} {

    99   # Delete one row out of every 10 in the database.  This will add

   100   # many pages to the freelist.

   101   #

   102   set todel {}

   103   set n [expr {[llength $data]/10}]

   104   set data [scramble $data]

   105   foreach rec [lrange $data 0 $n] {

   106     lappend todel [lindex $rec 0]

   107   }

   108   set data [lrange $data [expr {$n+1}] end]

   109   set max1 [lindex [lindex $data 0] 0]

   110   foreach rec $data {

   111     set id [lindex $rec 0]

   112     if {$id>$max1} {set max1 $id}

   113   }

   114   set origres [list [hash1] [hash2]]

   115   do_test trans2-$i.1 {

   116     db eval "DELETE FROM t1 WHERE id IN ([join $todel ,])"

   117     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   118   } $origres

   119   integrity_check trans2-$i.2

   120 

   121   # Begin a transaction and insert many new records.

   122   #

   123   set newdata {}

   124   foreach id $todel {

   125     set rec [list $id [random_uuid] \

   126                       [expr {int(rand()*5000)+1000}] [random_uuid]]

   127     lappend newdata $rec

   128     lappend data $rec

   129   }

   130   for {set j 1} {$j<50} {incr j} {

   131     set id [expr {$max_rowid+$j}]

   132     lappend todel $id

   133     set rec [list $id [random_uuid] \

   134                       [expr {int(rand()*5000)+1000}] [random_uuid]]

   135     lappend newdata $rec

   136     lappend data $rec

   137   }

   138   set max_rowid [expr {$max_rowid+$j-1}]

   139   set modsql {}

   140   set inssql {}

   141   set newres [list [hash1] [hash2]]

   142   do_test trans2-$i.3 {

   143     db eval BEGIN

   144     foreach rec [scramble $newdata] {

   145       foreach {id u1 z u2} $rec break

   146       set s "INSERT INTO t1 VALUES($id,'$u1',zeroblob($z),'$u2');"

   147       append modsql $s\n

   148       append inssql $s\n

   149       db eval $s

   150     }

   151     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   152   } $newres

   153   integrity_check trans2-$i.4

   154 

   155   # Do a large update that aborts do to a constraint failure near

   156   # the end.  This stresses the statement journal mechanism.

   157   #

   158   do_test trans2-$i.10 {

   159     catchsql {

   160       UPDATE t1 SET u1=u1||'x',

   161           z = CASE WHEN id<$max_rowid

   162                    THEN zeroblob((random()&65535)%5000 + 1000) END;

   163     }

   164   } {1 {t1.z may not be NULL}}

   165   do_test trans2-$i.11 {

   166     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   167   } $newres

   168 

   169   # Delete all of the newly inserted records.  Verify that the database

   170   # is back to its original state.

   171   #

   172   do_test trans2-$i.20 {

   173     set s "DELETE FROM t1 WHERE id IN ([join $todel ,]);"

   174     append modsql $s\n

   175     db eval $s

   176     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   177   } $origres

   178 

   179   # Do another large update that aborts do to a constraint failure near

   180   # the end.  This stresses the statement journal mechanism.

   181   #

   182   do_test trans2-$i.30 {

   183     catchsql {

   184       UPDATE t1 SET u1=u1||'x',

   185           z = CASE WHEN id<$max1

   186                    THEN zeroblob((random()&65535)%5000 + 1000) END;

   187     }

   188   } {1 {t1.z may not be NULL}}

   189   do_test trans2-$i.31 {

   190     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   191   } $origres

   192 

   193   # Redo the inserts

   194   #

   195   do_test trans2-$i.40 {

   196     db eval $inssql

   197     append modsql $inssql

   198     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   199   } $newres

   200 

   201   # Rollback the transaction.  Verify that the content is restored.

   202   #

   203   do_test trans2-$i.90 {

   204     db eval ROLLBACK

   205     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   206   } $origres

   207   integrity_check trans2-$i.91

   208 

   209   # Repeat all the changes, but this time commit.

   210   #

   211   do_test trans2-$i.92 {

   212     db eval BEGIN

   213     catchsql {

   214       UPDATE t1 SET u1=u1||'x',

   215           z = CASE WHEN id<$max1

   216                    THEN zeroblob((random()&65535)%5000 + 1000) END;

   217     }

   218     db eval $modsql

   219     catchsql {

   220       UPDATE t1 SET u1=u1||'x',

   221           z = CASE WHEN id<$max1

   222                    THEN zeroblob((random()&65535)%5000 + 1000) END;

   223     }

   224     db eval COMMIT

   225     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}

   226   } $newres

   227   integrity_check trans2-$i.93

   228 }

   229 

   230 unset -nocomplain data i max_rowid todel n rec max1 id origres newres

   231 unset -nocomplain inssql modsql s j z

   232 finish_test