1 files changed, 232 insertions, 0 deletions
diff --git a/test/trans2.test b/test/trans2.test
new file mode 100644
index 0000000..cfd3e67
--- /dev/null
+++ b/test/trans2.test
@@ -0,0 +1,232 @@
+# 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 2008/08/27 18:56:36 drh 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