AryaWu/sqlite

1# 2009 January 32#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11#12# $Id: savepoint6.test,v 1.4 2009/06/05 17:09:12 drh Exp $13 14set testdir [file dirname $argv0]15source $testdir/tester.tcl16 17proc sql {zSql} {18  if {0 && $::debug_op} {19    puts stderr "$zSql ;"20    flush stderr21  }22  uplevel db eval [list $zSql]23  #puts stderr "$zSql ;"24}25 26set DATABASE_SCHEMA {27    PRAGMA auto_vacuum = incremental;28    CREATE TABLE t1(x, y);29    CREATE UNIQUE INDEX i1 ON t1(x);30    CREATE INDEX i2 ON t1(y);31}32 33if {0==[info exists ::G(savepoint6_iterations)]} {34  set ::G(savepoint6_iterations) 100035}36 37#--------------------------------------------------------------------------38# In memory database state.39#40# ::lSavepoint is a list containing one entry for each active savepoint. The41# first entry in the list corresponds to the most recently opened savepoint.42# Each entry consists of two elements:43#44#   1. The savepoint name.45#46#   2. A serialized Tcl array representing the contents of table t1 at the47#      start of the savepoint. The keys of the array are the x values. The48#      values are the y values.49#  50# Array ::aEntry contains the contents of database table t1. Array keys are51# x values, the array data values are y values.52#53set lSavepoint [list]54array set aEntry [list]55 56proc x_to_y {x} {57  set nChar [expr int(rand()*250) + 250]58  set str " $nChar [string repeat $x. $nChar]"59  string range $str 1 $nChar60}61#--------------------------------------------------------------------------62 63#-------------------------------------------------------------------------64# Procs to operate on database:65#66#   savepoint NAME67#   rollback  NAME68#   release   NAME69#70#   insert_rows XVALUES71#   delete_rows XVALUES72#73proc savepoint {zName} {74  if {$::debug_op} { puts stderr "savepoint $zName" ; flush stderr }75  catch { sql "SAVEPOINT $zName" }76  lappend ::lSavepoint [list $zName [array get ::aEntry]]77}78 79proc rollback {zName} {80  if {$::debug_op} { puts stderr "rollback $zName" ; flush stderr }81  catch { sql "ROLLBACK TO $zName" }82  for {set i [expr {[llength $::lSavepoint]-1}]} {$i>=0} {incr i -1} {83    set zSavepoint [lindex $::lSavepoint $i 0]84    if {$zSavepoint eq $zName} {85      unset -nocomplain ::aEntry86      array set ::aEntry [lindex $::lSavepoint $i 1]87 88 89      if {$i+1 < [llength $::lSavepoint]} {90        set ::lSavepoint [lreplace $::lSavepoint [expr $i+1] end]91      }92      break93    }94  }95}96 97proc release {zName} {98  if {$::debug_op} { puts stderr "release $zName" ; flush stderr }99  catch { sql "RELEASE $zName" }100  for {set i [expr {[llength $::lSavepoint]-1}]} {$i>=0} {incr i -1} {101    set zSavepoint [lindex $::lSavepoint $i 0]102    if {$zSavepoint eq $zName} {103      set ::lSavepoint [lreplace $::lSavepoint $i end]104      break105    }106  }107 108  if {[llength $::lSavepoint] == 0} {109    #puts stderr "-- End of transaction!!!!!!!!!!!!!"110  }111}112 113proc insert_rows {lX} {114  if {$::debug_op} { puts stderr "insert_rows $lX" ; flush stderr }115  foreach x $lX {116    set y [x_to_y $x]117 118    # Update database [db]119    sql "INSERT OR REPLACE INTO t1 VALUES($x, '$y')"120 121    # Update the Tcl database.122    set ::aEntry($x) $y123  }124}125 126proc delete_rows {lX} {127  if {$::debug_op} { puts stderr "delete_rows $lX" ; flush stderr }128  foreach x $lX {129    # Update database [db]130    sql "DELETE FROM t1 WHERE x = $x"131 132    # Update the Tcl database.133    unset -nocomplain ::aEntry($x)134  }135}136#-------------------------------------------------------------------------137 138#-------------------------------------------------------------------------139# Proc to compare database content with the in-memory representation.140#141#   checkdb142#143proc checkdb {} {144  set nEntry [db one {SELECT count(*) FROM t1}]145  set nEntry2 [array size ::aEntry]146  if {$nEntry != $nEntry2} {147    error "$nEntry entries in database, $nEntry2 entries in array"148  }149  db eval {SELECT x, y FROM t1} {150    if {![info exists ::aEntry($x)]} {151      error "Entry $x exists in database, but not in array"152    }153    if {$::aEntry($x) ne $y} {154      error "Entry $x is set to {$y} in database, {$::aEntry($x)} in array"155    }156  }157 158  db eval { PRAGMA integrity_check }159}160#-------------------------------------------------------------------------161 162#-------------------------------------------------------------------------163# Proc to return random set of x values.164#165#   random_integers166#167proc random_integers {nRes nRange} {168  set ret [list]169  for {set i 0} {$i<$nRes} {incr i} {170    lappend ret [expr int(rand()*$nRange)]171  }172  return $ret173} 174#-------------------------------------------------------------------------175 176set ::debug_op 0177proc debug_ops {} {178  set ::debug_op 1179}180 181proc database_op {} {182  set i [expr int(rand()*2)] 183  if {$i==0} {184    insert_rows [random_integers 100 1000]185  }186  if {$i==1} {187    delete_rows [random_integers 100 1000]188    set i [expr int(rand()*3)] 189    if {$i==0} {190      sql {PRAGMA incremental_vacuum}191    }192  }193}194 195proc savepoint_op {} {196  set names {one two three four five}197  set cmds  {savepoint savepoint savepoint savepoint release rollback}198 199  set C [lindex $cmds [expr int(rand()*6)]]200  set N [lindex $names [expr int(rand()*5)]]201 202  $C $N203  return ok204}205 206expr srand(0)207 208############################################################################209############################################################################210# Start of test cases.211 212do_test savepoint6-1.1 {213  sql $DATABASE_SCHEMA214} {}215do_test savepoint6-1.2 {216  insert_rows {217    497 166 230 355 779 588 394 317 290 475 362 193 805 851 564 218    763 44 930 389 819 765 760 966 280 538 414 500 18 25 287 320 219    30 382 751 87 283 981 429 630 974 421 270 810 405 220  }221 222  savepoint one223  insert_rows 858224  delete_rows 930225  savepoint two226    execsql {PRAGMA incremental_vacuum}227    savepoint three228      insert_rows 144229     rollback three230    rollback two231  release one232 233  execsql {SELECT count(*) FROM t1}234} {44}235 236foreach zSetup [list {237  set testname normal238  sqlite3 db test.db239} {240  if {[wal_is_wal_mode]} continue241  set testname tempdb242  sqlite3 db ""243} {244  if {[permutation] eq "journaltest"} {245    continue246  }247  set testname nosync248  sqlite3 db test.db249  sql { PRAGMA synchronous = off }250} {251  set testname smallcache252  sqlite3 db test.db253  sql { PRAGMA cache_size = 10 }254}] {255 256  unset -nocomplain ::lSavepoint257  unset -nocomplain ::aEntry258 259  catch { db close }260  forcedelete test.db test.db-wal test.db-journal261  eval $zSetup262  sql $DATABASE_SCHEMA263 264  wal_set_journal_mode265 266  do_test savepoint6-$testname.setup {267    savepoint one268    insert_rows [random_integers 100 1000]269    release one270    checkdb271  } {ok}272  273  for {set i 0} {$i < $::G(savepoint6_iterations)} {incr i} {274    do_test savepoint6-$testname.$i.1 {275      savepoint_op276      checkdb277    } {ok}278  279    do_test savepoint6-$testname.$i.2 {280      database_op281      database_op282      checkdb283    } {ok}284  }285 286  wal_check_journal_mode savepoint6-$testname.walok287}288 289unset -nocomplain ::lSavepoint290unset -nocomplain ::aEntry291 292finish_test293