sl@0: # 2008 August 27
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 implements regression tests for SQLite library.  The
sl@0: # focus of this script is transactions
sl@0: #
sl@0: # $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $
sl@0: #
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # A procedure to scramble the elements of list $inlist into a random order.
sl@0: #
sl@0: proc scramble {inlist} {
sl@0:   set y {}
sl@0:   foreach x $inlist {
sl@0:     lappend y [list [expr {rand()}] $x]
sl@0:   }
sl@0:   set y [lsort $y]
sl@0:   set outlist {}
sl@0:   foreach x $y {
sl@0:     lappend outlist [lindex $x 1]
sl@0:   }
sl@0:   return $outlist
sl@0: }
sl@0: 
sl@0: # Generate a UUID using randomness.
sl@0: #
sl@0: expr srand(1)
sl@0: proc random_uuid {} {
sl@0:   set u {}
sl@0:   for {set i 0} {$i<5} {incr i} {
sl@0:     append u [format %06x [expr {int(rand()*16777216)}]]
sl@0:   }
sl@0:   return $u
sl@0: }
sl@0: 
sl@0: # Compute hashes on the u1 and u2 fields of the sample data.
sl@0: #
sl@0: proc hash1 {} {
sl@0:   global data
sl@0:   set x ""
sl@0:   foreach rec [lsort -integer -index 0 $data] {
sl@0:     append x [lindex $rec 1]
sl@0:   }
sl@0:   return [md5 $x]
sl@0: }
sl@0: proc hash2 {} {
sl@0:   global data
sl@0:   set x ""
sl@0:   foreach rec [lsort -integer -index 0 $data] {
sl@0:     append x [lindex $rec 3]
sl@0:   }
sl@0:   return [md5 $x]
sl@0: }
sl@0: 
sl@0: # Create the initial data set
sl@0: #
sl@0: unset -nocomplain data i max_rowid todel n rec max1 id origres newres
sl@0: unset -nocomplain inssql modsql s j z
sl@0: set data {}
sl@0: for {set i 0} {$i<400} {incr i} {
sl@0:   set rec [list $i [random_uuid] [expr {int(rand()*5000)+1000}] [random_uuid]]
sl@0:   lappend data $rec
sl@0: }
sl@0: set max_rowid [expr {$i-1}]
sl@0: 
sl@0: # Create the T1 table used to hold test data.  Populate that table with
sl@0: # the initial data set and check hashes to make sure everything is correct.
sl@0: #
sl@0: do_test trans2-1.1 {
sl@0:   execsql {
sl@0:     PRAGMA cache_size=100;
sl@0:     CREATE TABLE t1(
sl@0:       id INTEGER PRIMARY KEY,
sl@0:       u1 TEXT UNIQUE,
sl@0:       z BLOB NOT NULL,
sl@0:       u2 TEXT UNIQUE
sl@0:     );
sl@0:   }
sl@0:   foreach rec [scramble $data] {
sl@0:     foreach {id u1 z u2} $rec break
sl@0:     db eval {INSERT INTO t1 VALUES($id,$u1,zeroblob($z),$u2)}
sl@0:   }
sl@0:   db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0: } [list [hash1] [hash2]]
sl@0: 
sl@0: # Repeat the main test loop multiple times.
sl@0: #
sl@0: for {set i 2} {$i<=30} {incr i} {
sl@0:   # Delete one row out of every 10 in the database.  This will add
sl@0:   # many pages to the freelist.
sl@0:   #
sl@0:   set todel {}
sl@0:   set n [expr {[llength $data]/10}]
sl@0:   set data [scramble $data]
sl@0:   foreach rec [lrange $data 0 $n] {
sl@0:     lappend todel [lindex $rec 0]
sl@0:   }
sl@0:   set data [lrange $data [expr {$n+1}] end]
sl@0:   set max1 [lindex [lindex $data 0] 0]
sl@0:   foreach rec $data {
sl@0:     set id [lindex $rec 0]
sl@0:     if {$id>$max1} {set max1 $id}
sl@0:   }
sl@0:   set origres [list [hash1] [hash2]]
sl@0:   do_test trans2-$i.1 {
sl@0:     db eval "DELETE FROM t1 WHERE id IN ([join $todel ,])"
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $origres
sl@0:   integrity_check trans2-$i.2
sl@0: 
sl@0:   # Begin a transaction and insert many new records.
sl@0:   #
sl@0:   set newdata {}
sl@0:   foreach id $todel {
sl@0:     set rec [list $id [random_uuid] \
sl@0:                       [expr {int(rand()*5000)+1000}] [random_uuid]]
sl@0:     lappend newdata $rec
sl@0:     lappend data $rec
sl@0:   }
sl@0:   for {set j 1} {$j<50} {incr j} {
sl@0:     set id [expr {$max_rowid+$j}]
sl@0:     lappend todel $id
sl@0:     set rec [list $id [random_uuid] \
sl@0:                       [expr {int(rand()*5000)+1000}] [random_uuid]]
sl@0:     lappend newdata $rec
sl@0:     lappend data $rec
sl@0:   }
sl@0:   set max_rowid [expr {$max_rowid+$j-1}]
sl@0:   set modsql {}
sl@0:   set inssql {}
sl@0:   set newres [list [hash1] [hash2]]
sl@0:   do_test trans2-$i.3 {
sl@0:     db eval BEGIN
sl@0:     foreach rec [scramble $newdata] {
sl@0:       foreach {id u1 z u2} $rec break
sl@0:       set s "INSERT INTO t1 VALUES($id,'$u1',zeroblob($z),'$u2');"
sl@0:       append modsql $s\n
sl@0:       append inssql $s\n
sl@0:       db eval $s
sl@0:     }
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $newres
sl@0:   integrity_check trans2-$i.4
sl@0: 
sl@0:   # Do a large update that aborts do to a constraint failure near
sl@0:   # the end.  This stresses the statement journal mechanism.
sl@0:   #
sl@0:   do_test trans2-$i.10 {
sl@0:     catchsql {
sl@0:       UPDATE t1 SET u1=u1||'x',
sl@0:           z = CASE WHEN id<$max_rowid
sl@0:                    THEN zeroblob((random()&65535)%5000 + 1000) END;
sl@0:     }
sl@0:   } {1 {t1.z may not be NULL}}
sl@0:   do_test trans2-$i.11 {
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $newres
sl@0: 
sl@0:   # Delete all of the newly inserted records.  Verify that the database
sl@0:   # is back to its original state.
sl@0:   #
sl@0:   do_test trans2-$i.20 {
sl@0:     set s "DELETE FROM t1 WHERE id IN ([join $todel ,]);"
sl@0:     append modsql $s\n
sl@0:     db eval $s
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $origres
sl@0: 
sl@0:   # Do another large update that aborts do to a constraint failure near
sl@0:   # the end.  This stresses the statement journal mechanism.
sl@0:   #
sl@0:   do_test trans2-$i.30 {
sl@0:     catchsql {
sl@0:       UPDATE t1 SET u1=u1||'x',
sl@0:           z = CASE WHEN id<$max1
sl@0:                    THEN zeroblob((random()&65535)%5000 + 1000) END;
sl@0:     }
sl@0:   } {1 {t1.z may not be NULL}}
sl@0:   do_test trans2-$i.31 {
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $origres
sl@0: 
sl@0:   # Redo the inserts
sl@0:   #
sl@0:   do_test trans2-$i.40 {
sl@0:     db eval $inssql
sl@0:     append modsql $inssql
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $newres
sl@0: 
sl@0:   # Rollback the transaction.  Verify that the content is restored.
sl@0:   #
sl@0:   do_test trans2-$i.90 {
sl@0:     db eval ROLLBACK
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $origres
sl@0:   integrity_check trans2-$i.91
sl@0: 
sl@0:   # Repeat all the changes, but this time commit.
sl@0:   #
sl@0:   do_test trans2-$i.92 {
sl@0:     db eval BEGIN
sl@0:     catchsql {
sl@0:       UPDATE t1 SET u1=u1||'x',
sl@0:           z = CASE WHEN id<$max1
sl@0:                    THEN zeroblob((random()&65535)%5000 + 1000) END;
sl@0:     }
sl@0:     db eval $modsql
sl@0:     catchsql {
sl@0:       UPDATE t1 SET u1=u1||'x',
sl@0:           z = CASE WHEN id<$max1
sl@0:                    THEN zeroblob((random()&65535)%5000 + 1000) END;
sl@0:     }
sl@0:     db eval COMMIT
sl@0:     db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
sl@0:   } $newres
sl@0:   integrity_check trans2-$i.93
sl@0: }
sl@0: 
sl@0: unset -nocomplain data i max_rowid todel n rec max1 id origres newres
sl@0: unset -nocomplain inssql modsql s j z
sl@0: finish_test