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