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