1# 2014 November 12 2# 3# The author disclaims copyright to this source code. In place of 4# 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.tcl 18set ::testprefix rollback2 19 20proc int2hex {i} { format %.2X $i } 21db func int2hex int2hex 22do_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 ALL 31 SELECT a+1, int2hex(a+1) FROM data WHERE a<40 32 ) 33 INSERT INTO t1 SELECT * FROM data; 34} {} 35 36 37# do_rollback_test ID SWITCHES 38# 39# where SWITCHES are: 40# 41# -setup SQL script to open transaction and begin writing. 42# -select SELECT to execute after -setup script 43# -result Expected result of -select statement 44# -rollback Use this SQL command ("ROLLBACK" or "ROLLBACK TO ...") to 45# rollback the transaction in the middle of the -select statment 46# execution. 47# 48proc do_rollback_test {tn args} { 49 set A(-setup) "" 50 set A(-select) "" 51 set A(-result) "" 52 set A(-rollback) ROLLBACK 53 54 array set O $args 55 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 0 66 db eval $A(-select) x { 67 if {$i==$iRollback} { db eval $A(-rollback) } 68 foreach k $x(*) { lappend res $x($k) } 69 incr i 70 } 71 72 do_test $tn.$iRollback [list set {} $res] [list {*}$A(-result)] 73 if {$i < $iRollback} break 74 } 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)==0 82} -result { 83 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 84} 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)==0 95} -result { 96 2 4 6 8 10 12 14 16 18 20 22 24 26 28 30 32 34 36 38 40 97} 98 99#-------------------------------------------------------------------- 100# Try with some index scans 101# 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 2 112} 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 2 124} 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 40 142} 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 40 154} 155 156finish_test 157