Debian Codegraph @

ext/rtree/rtreeJ.test

raw
1 # 2024-02-03
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 # ROLLBACK in the middle of an RTREE query
13 #
14 if {![info exists testdir]} {
15 set testdir [file join [file dirname [info script]] .. .. test]
16 }
17 source $testdir/tester.tcl
18 set testprefix rtreeJ
19 ifcapable !rtree { finish_test ; return }
20
21 do_execsql_test 1.0 {
22 CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2);
23 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2);
24 } {}
25
26 do_execsql_test 1.1 {
27 SELECT * FROM t1
28 } {1 1.0 1.0 2 2.0 2.0}
29
30 # If a ROLLBACK occurs that backs out changes to the RTREE, then
31 # all pending queries to the RTREE are aborted.
32 #
33 do_test 1.2 {
34 db eval {
35 BEGIN;
36 INSERT INTO t1 VALUES(3, 3, 3);
37 INSERT INTO t1 VALUES(4, 4, 4);
38 }
39 set rc [catch {
40 db eval { SELECT * FROM t1 } {
41 if {$id==1} {
42 db eval { ROLLBACK }
43 }
44 lappend res $id $x1 $x2
45 }
46 } msg]
47 list $rc $msg
48 } {1 {query aborted}}
49
50 do_execsql_test 1.3 {
51 SELECT * FROM t1;
52 } {1 1.0 1.0 2 2.0 2.0}
53
54 # A COMMIT of changes to the RTREE does not affect pending queries
55 #
56 do_test 1.4 {
57 set res {}
58 db eval {
59 BEGIN;
60 INSERT INTO t1 VALUES(5, 5, 5);
61 INSERT INTO t1 VALUES(6, 6, 6);
62 }
63 db eval { SELECT * FROM t1 } {
64 if {$id==1} {
65 db eval { COMMIT }
66 }
67 lappend res $id $x1 $x2
68 }
69 set res
70 } {1 1.0 1.0 2 2.0 2.0 5 5.0 5.0 6 6.0 6.0}
71
72 do_execsql_test 1.5 {
73 SELECT * FROM t1;
74 } {1 1.0 1.0 2 2.0 2.0 5 5.0 5.0 6 6.0 6.0}
75
76 do_execsql_test 1.6 {
77 DELETE FROM t1;
78 INSERT INTO t1 VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4);
79 CREATE TABLE t2(x);
80 SELECT * FROM t1;
81 } {1 1.0 1.0 2 2.0 2.0 3 3.0 3.0 4 4.0 4.0}
82
83 # A rollback that does not affect the rtree table because
84 # the rtree table has not been written to does not cause
85 # a query abort.
86 #
87 do_test 1.7 {
88 set res {}
89 db eval {
90 BEGIN;
91 INSERT INTO t2(x) VALUES(12345);
92 }
93 db eval { SELECT * FROM t1 } {
94 if {$id==1} {
95 db eval { ROLLBACK }
96 }
97 lappend res $id $x1 $x2
98 }
99 set res
100 } {1 1.0 1.0 2 2.0 2.0 3 3.0 3.0 4 4.0 4.0}
101
102 # ROLLBACK TO that affects the RTREE does cause a query abort.
103 #
104 do_test 1.8 {
105 db eval {
106 DELETE FROM t1 WHERE rowid>1;
107 BEGIN;
108 DELETE FROM t2;
109 INSERT INTO t2(x) VALUES(23456);
110 SAVEPOINT 'one';
111 INSERT INTO t1 VALUES(2,2,2),(3,3,3);
112 }
113 set rc [catch {
114 db eval { SELECT * FROM t1 } {
115 if {$id==1} {
116 db eval { ROLLBACK TO 'one'; }
117 }
118 lappend res $id $x1 $x2
119 }
120 } msg]
121 list $rc $msg
122 } {1 {query aborted}}
123
124 do_execsql_test 1.9 {
125 COMMIT;
126 SELECT * FROM t1;
127 } {1 1.0 1.0}
128
129 # ROLLBACK TO that does not affect the RTREE does not cause a query abort.
130 #
131 do_execsql_test 1.10 {
132 DELETE FROM t1;
133 INSERT INTO t1 VALUES(1,1,1),(2,2,2),(3,3,3);
134 BEGIN;
135 DELETE FROM t2;
136 INSERT INTO t2(x) VALUES(34567);
137 SAVEPOINT 'one';
138 INSERT INTO t2(x) VALUES('a string');
139 SELECT * FROM t1;
140 } {1 1.0 1.0 2 2.0 2.0 3 3.0 3.0}
141 do_test 1.11 {
142 set rc [catch {
143 set res {}
144 db eval { SELECT * FROM t1 } {
145 if {$id==2} {
146 # db eval { ROLLBACK TO 'one'; }
147 }
148 lappend res $id $x1 $x2
149 }
150 set res
151 } msg]
152 list $rc $msg
153 } {0 {1 1.0 1.0 2 2.0 2.0 3 3.0 3.0}}
154
155 do_execsql_test 1.12 {
156 COMMIT;
157 SELECT * FROM t1;
158 } {1 1.0 1.0 2 2.0 2.0 3 3.0 3.0}
159
160 #----------------------------------------------------------------------
161
162 reset_db
163 do_execsql_test 2.0 {
164 CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2);
165 INSERT INTO t1 VALUES(1, 1, 1), (2, 2, 2);
166 CREATE TABLE t2(x);
167 } {}
168
169 do_test 2.1 {
170 db eval {
171 BEGIN;
172 INSERT INTO t1 VALUES(3, 3, 3);
173 PRAGMA writable_schema = RESET;
174 }
175
176 set rc [catch {
177 db eval { SELECT x1, x2 FROM t1 } {
178 if {$x1==1} {
179 db eval { ROLLBACK }
180 }
181 lappend res $x1 $x2
182 }
183 } msg]
184 list $rc $msg
185 } {1 {query aborted}}
186
187 do_execsql_test 2.1 {
188 CREATE TABLE bak_node(nodeno, data);
189 CREATE TABLE bak_parent(nodeno, parentnode);
190 CREATE TABLE bak_rowid(rowid, nodeno);
191 }
192 proc save_t1 {} {
193 db eval {
194 DELETE FROM bak_node;
195 DELETE FROM bak_parent;
196 DELETE FROM bak_rowid;
197 INSERT INTO bak_node SELECT * FROM t1_node;
198 INSERT INTO bak_parent SELECT * FROM t1_parent;
199 INSERT INTO bak_rowid SELECT * FROM t1_rowid;
200 }
201 }
202 proc restore_t1 {} {
203 db eval {
204 DELETE FROM t1_node;
205 DELETE FROM t1_parent;
206 DELETE FROM t1_rowid;
207 INSERT INTO t1_node SELECT * FROM bak_node;
208 INSERT INTO t1_parent SELECT * FROM bak_parent;
209 INSERT INTO t1_rowid SELECT * FROM bak_rowid;
210 }
211 }
212
213 do_test 2.3 {
214 save_t1
215 db eval {
216 INSERT INTO t1 VALUES(3, 3, 3);
217 }
218 set rc [catch {
219 db eval { SELECT rowid, x1, x2 FROM t1 } {
220 if {$x1==1} {
221 restore_t1
222 }
223 lappend res $x1 $x2
224 }
225 } msg]
226 list $rc $msg
227 } {1 {query aborted}}
228 do_execsql_test 2.4 {
229 SELECT * FROM t1
230 } {1 1.0 1.0 2 2.0 2.0}
231
232 do_test 2.5 {
233 save_t1
234 db eval {
235 INSERT INTO t1 VALUES(3, 3, 3);
236 }
237 set rc [catch {
238 db eval { SELECT x1 FROM t1 } {
239 if {$x1==1} {
240 restore_t1
241 }
242 lappend res $x1 $x2
243 }
244 } msg]
245 list $rc $msg
246 } {1 {query aborted}}
247 do_execsql_test 2.6 {
248 SELECT * FROM t1
249 } {1 1.0 1.0 2 2.0 2.0}
250
251 do_test 2.7 {
252 save_t1
253 db eval {
254 INSERT INTO t1 VALUES(3, 3, 3);
255 }
256 set ::res [list]
257 set rc [catch {
258 db eval { SELECT 'abc' FROM t1 } {
259 if {$::res==[list]} {
260 restore_t1
261 set ::bDone 1
262 }
263 lappend res abc
264 }
265 } msg]
266 set res
267 } {abc abc abc}
268 do_execsql_test 2.6 {
269 SELECT * FROM t1
270 } {1 1.0 1.0 2 2.0 2.0}
271
272
273 finish_test