AryaWu/sqlite

1# 2014 November 122#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# This file containst tests to verify that ROLLBACK or ROLLBACK TO 13# operations interact correctly with ongoing SELECT statements.14#15 16set testdir [file dirname $argv0]17source $testdir/tester.tcl18set ::testprefix rollback219 20proc int2hex {i} { format %.2X $i }21db func int2hex int2hex22do_execsql_test 1.0 {23  SELECT int2hex(0), int2hex(100), int2hex(255)24} {00 64 FF}25do_execsql_test 1.1 {26  CREATE TABLE t1(i, h);27  CREATE INDEX i1 ON t1(h);28  WITH data(a, b) AS (29    SELECT 1, int2hex(1)30      UNION ALL31    SELECT a+1, int2hex(a+1) FROM data WHERE a<4032  )33  INSERT INTO t1 SELECT * FROM data;34} {}35 36 37# do_rollback_test ID SWITCHES38#39# where SWITCHES are:40#41#   -setup      SQL script to open transaction and begin writing.42#   -select     SELECT to execute after -setup script43#   -result     Expected result of -select statement44#   -rollback   Use this SQL command ("ROLLBACK" or "ROLLBACK TO ...") to45#               rollback the transaction in the middle of the -select statment46#               execution.47#48proc do_rollback_test {tn args} {49  set A(-setup)    ""50  set A(-select)   ""51  set A(-result)   ""52  set A(-rollback) ROLLBACK53 54  array set O $args55  foreach k [array names O] {56    if {[info exists A($k)]==0} { error "unknown option: $k" }57    set A($k) $O($k)58  }59 60  for {set iRollback 0} 1 {incr iRollback} {61    catch { db eval ROLLBACK }62    set res [list]63    db eval $A(-setup)64 65    set i 066    db eval $A(-select) x {67      if {$i==$iRollback} { db eval $A(-rollback) }68      foreach k $x(*) { lappend res $x($k) }69      incr i70    }71 72    do_test $tn.$iRollback [list set {} $res] [list {*}$A(-result)]73    if {$i < $iRollback} break74  }75}76 77do_rollback_test 2.1 -setup {78  BEGIN;79    DELETE FROM t1 WHERE (i%2)==1;80} -select {81  SELECT i FROM t1 WHERE (i%2)==082} -result {83  2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 4084}85 86do_rollback_test 2.2 -setup {87  BEGIN;88    DELETE FROM t1 WHERE (i%4)==1;89    SAVEPOINT one;90      DELETE FROM t1 WHERE (i%2)==1;91} -rollback {92  ROLLBACK TO one;93} -select {94  SELECT i FROM t1 WHERE (i%2)==095} -result {96  2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 4097}98 99#--------------------------------------------------------------------100# Try with some index scans101#102do_eqp_test 3.1 {103  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;104} {SCAN t1 USING INDEX i1}105do_rollback_test 3.2 -setup {106  BEGIN;107    DELETE FROM t1 WHERE (i%2)==1;108} -select {109  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;110} -result {111  40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10  8  6  4  2112}113do_rollback_test 3.3 -setup {114  BEGIN;115    DELETE FROM t1 WHERE (i%4)==1;116    SAVEPOINT one;117      DELETE FROM t1 WHERE (i%2)==1;118} -rollback {119  ROLLBACK TO one;120} -select {121  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h DESC;122} -result {123  40 38 36 34 32 30 28 26 24 22 20 18 16 14 12 10  8  6  4  2124}125 126#--------------------------------------------------------------------127# Now with some index scans that feature overflow keys.128#129set leader [string repeat "abcdefghij" 70]130do_execsql_test 4.1 { UPDATE t1 SET h = $leader || h; }131 132do_eqp_test 4.2 {133  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;134} {SCAN t1 USING INDEX i1}135do_rollback_test 4.3 -setup {136  BEGIN;137    DELETE FROM t1 WHERE (i%2)==1;138} -select {139  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;140} -result {141  2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40142}143do_rollback_test 4.4 -setup {144  BEGIN;145    DELETE FROM t1 WHERE (i%4)==1;146    SAVEPOINT one;147      DELETE FROM t1 WHERE (i%2)==1;148} -rollback {149  ROLLBACK TO one;150} -select {151  SELECT i FROM t1 WHERE (i%2)==0 ORDER BY h ASC;152} -result {153  2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40154}155 156finish_test157