1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trans2.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,232 @@
1.4 +# 2008 August 27
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +#
1.15 +# This file implements regression tests for SQLite library. The
1.16 +# focus of this script is transactions
1.17 +#
1.18 +# $Id: trans2.test,v 1.1 2008/08/27 18:56:36 drh Exp $
1.19 +#
1.20 +set testdir [file dirname $argv0]
1.21 +source $testdir/tester.tcl
1.22 +
1.23 +# A procedure to scramble the elements of list $inlist into a random order.
1.24 +#
1.25 +proc scramble {inlist} {
1.26 + set y {}
1.27 + foreach x $inlist {
1.28 + lappend y [list [expr {rand()}] $x]
1.29 + }
1.30 + set y [lsort $y]
1.31 + set outlist {}
1.32 + foreach x $y {
1.33 + lappend outlist [lindex $x 1]
1.34 + }
1.35 + return $outlist
1.36 +}
1.37 +
1.38 +# Generate a UUID using randomness.
1.39 +#
1.40 +expr srand(1)
1.41 +proc random_uuid {} {
1.42 + set u {}
1.43 + for {set i 0} {$i<5} {incr i} {
1.44 + append u [format %06x [expr {int(rand()*16777216)}]]
1.45 + }
1.46 + return $u
1.47 +}
1.48 +
1.49 +# Compute hashes on the u1 and u2 fields of the sample data.
1.50 +#
1.51 +proc hash1 {} {
1.52 + global data
1.53 + set x ""
1.54 + foreach rec [lsort -integer -index 0 $data] {
1.55 + append x [lindex $rec 1]
1.56 + }
1.57 + return [md5 $x]
1.58 +}
1.59 +proc hash2 {} {
1.60 + global data
1.61 + set x ""
1.62 + foreach rec [lsort -integer -index 0 $data] {
1.63 + append x [lindex $rec 3]
1.64 + }
1.65 + return [md5 $x]
1.66 +}
1.67 +
1.68 +# Create the initial data set
1.69 +#
1.70 +unset -nocomplain data i max_rowid todel n rec max1 id origres newres
1.71 +unset -nocomplain inssql modsql s j z
1.72 +set data {}
1.73 +for {set i 0} {$i<400} {incr i} {
1.74 + set rec [list $i [random_uuid] [expr {int(rand()*5000)+1000}] [random_uuid]]
1.75 + lappend data $rec
1.76 +}
1.77 +set max_rowid [expr {$i-1}]
1.78 +
1.79 +# Create the T1 table used to hold test data. Populate that table with
1.80 +# the initial data set and check hashes to make sure everything is correct.
1.81 +#
1.82 +do_test trans2-1.1 {
1.83 + execsql {
1.84 + PRAGMA cache_size=100;
1.85 + CREATE TABLE t1(
1.86 + id INTEGER PRIMARY KEY,
1.87 + u1 TEXT UNIQUE,
1.88 + z BLOB NOT NULL,
1.89 + u2 TEXT UNIQUE
1.90 + );
1.91 + }
1.92 + foreach rec [scramble $data] {
1.93 + foreach {id u1 z u2} $rec break
1.94 + db eval {INSERT INTO t1 VALUES($id,$u1,zeroblob($z),$u2)}
1.95 + }
1.96 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.97 +} [list [hash1] [hash2]]
1.98 +
1.99 +# Repeat the main test loop multiple times.
1.100 +#
1.101 +for {set i 2} {$i<=30} {incr i} {
1.102 + # Delete one row out of every 10 in the database. This will add
1.103 + # many pages to the freelist.
1.104 + #
1.105 + set todel {}
1.106 + set n [expr {[llength $data]/10}]
1.107 + set data [scramble $data]
1.108 + foreach rec [lrange $data 0 $n] {
1.109 + lappend todel [lindex $rec 0]
1.110 + }
1.111 + set data [lrange $data [expr {$n+1}] end]
1.112 + set max1 [lindex [lindex $data 0] 0]
1.113 + foreach rec $data {
1.114 + set id [lindex $rec 0]
1.115 + if {$id>$max1} {set max1 $id}
1.116 + }
1.117 + set origres [list [hash1] [hash2]]
1.118 + do_test trans2-$i.1 {
1.119 + db eval "DELETE FROM t1 WHERE id IN ([join $todel ,])"
1.120 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.121 + } $origres
1.122 + integrity_check trans2-$i.2
1.123 +
1.124 + # Begin a transaction and insert many new records.
1.125 + #
1.126 + set newdata {}
1.127 + foreach id $todel {
1.128 + set rec [list $id [random_uuid] \
1.129 + [expr {int(rand()*5000)+1000}] [random_uuid]]
1.130 + lappend newdata $rec
1.131 + lappend data $rec
1.132 + }
1.133 + for {set j 1} {$j<50} {incr j} {
1.134 + set id [expr {$max_rowid+$j}]
1.135 + lappend todel $id
1.136 + set rec [list $id [random_uuid] \
1.137 + [expr {int(rand()*5000)+1000}] [random_uuid]]
1.138 + lappend newdata $rec
1.139 + lappend data $rec
1.140 + }
1.141 + set max_rowid [expr {$max_rowid+$j-1}]
1.142 + set modsql {}
1.143 + set inssql {}
1.144 + set newres [list [hash1] [hash2]]
1.145 + do_test trans2-$i.3 {
1.146 + db eval BEGIN
1.147 + foreach rec [scramble $newdata] {
1.148 + foreach {id u1 z u2} $rec break
1.149 + set s "INSERT INTO t1 VALUES($id,'$u1',zeroblob($z),'$u2');"
1.150 + append modsql $s\n
1.151 + append inssql $s\n
1.152 + db eval $s
1.153 + }
1.154 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.155 + } $newres
1.156 + integrity_check trans2-$i.4
1.157 +
1.158 + # Do a large update that aborts do to a constraint failure near
1.159 + # the end. This stresses the statement journal mechanism.
1.160 + #
1.161 + do_test trans2-$i.10 {
1.162 + catchsql {
1.163 + UPDATE t1 SET u1=u1||'x',
1.164 + z = CASE WHEN id<$max_rowid
1.165 + THEN zeroblob((random()&65535)%5000 + 1000) END;
1.166 + }
1.167 + } {1 {t1.z may not be NULL}}
1.168 + do_test trans2-$i.11 {
1.169 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.170 + } $newres
1.171 +
1.172 + # Delete all of the newly inserted records. Verify that the database
1.173 + # is back to its original state.
1.174 + #
1.175 + do_test trans2-$i.20 {
1.176 + set s "DELETE FROM t1 WHERE id IN ([join $todel ,]);"
1.177 + append modsql $s\n
1.178 + db eval $s
1.179 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.180 + } $origres
1.181 +
1.182 + # Do another large update that aborts do to a constraint failure near
1.183 + # the end. This stresses the statement journal mechanism.
1.184 + #
1.185 + do_test trans2-$i.30 {
1.186 + catchsql {
1.187 + UPDATE t1 SET u1=u1||'x',
1.188 + z = CASE WHEN id<$max1
1.189 + THEN zeroblob((random()&65535)%5000 + 1000) END;
1.190 + }
1.191 + } {1 {t1.z may not be NULL}}
1.192 + do_test trans2-$i.31 {
1.193 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.194 + } $origres
1.195 +
1.196 + # Redo the inserts
1.197 + #
1.198 + do_test trans2-$i.40 {
1.199 + db eval $inssql
1.200 + append modsql $inssql
1.201 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.202 + } $newres
1.203 +
1.204 + # Rollback the transaction. Verify that the content is restored.
1.205 + #
1.206 + do_test trans2-$i.90 {
1.207 + db eval ROLLBACK
1.208 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.209 + } $origres
1.210 + integrity_check trans2-$i.91
1.211 +
1.212 + # Repeat all the changes, but this time commit.
1.213 + #
1.214 + do_test trans2-$i.92 {
1.215 + db eval BEGIN
1.216 + catchsql {
1.217 + UPDATE t1 SET u1=u1||'x',
1.218 + z = CASE WHEN id<$max1
1.219 + THEN zeroblob((random()&65535)%5000 + 1000) END;
1.220 + }
1.221 + db eval $modsql
1.222 + catchsql {
1.223 + UPDATE t1 SET u1=u1||'x',
1.224 + z = CASE WHEN id<$max1
1.225 + THEN zeroblob((random()&65535)%5000 + 1000) END;
1.226 + }
1.227 + db eval COMMIT
1.228 + db eval {SELECT md5sum(u1), md5sum(u2) FROM t1 ORDER BY id}
1.229 + } $newres
1.230 + integrity_check trans2-$i.93
1.231 +}
1.232 +
1.233 +unset -nocomplain data i max_rowid todel n rec max1 id origres newres
1.234 +unset -nocomplain inssql modsql s j z
1.235 +finish_test