# 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