| blob | 275b13264f71c56da63d0a3c7ac842eb09266a94 |
1 # 2008 Feb 19
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 # The focus of this file is testing the r-tree extension.
13 #
15 if {![info exists testdir]} {
16 set testdir [file join [file dirname [info script]] .. .. test]
17 }
18 source [file join [file dirname [info script]] rtree_util.tcl]
19 source $testdir/tester.tcl
20 set testprefix rtree1
22 # Test plan:
23 #
24 # rtree-1.*: Creating/destroying r-tree tables.
25 # rtree-2.*: Test the implicit constraints - unique rowid and
26 # (coord[N]<=coord[N+1]) for even values of N. Also
27 # automatic assigning of rowid values.
28 # rtree-3.*: Linear scans of r-tree data.
29 # rtree-4.*: Test INSERT
30 # rtree-5.*: Test DELETE
31 # rtree-6.*: Test UPDATE
32 # rtree-7.*: Test renaming an r-tree table.
33 # rtree-8.*: Test constrained scans of r-tree data.
34 #
35 # rtree-12.*: Test that on-conflict clauses are supported.
36 #
38 ifcapable !rtree {
39 finish_test
40 return
41 }
43 #----------------------------------------------------------------------------
44 # Test cases rtree-1.* test CREATE and DROP table statements.
45 #
47 # Test creating and dropping an rtree table.
48 #
49 do_test rtree-1.1.1 {
50 execsql { CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2) }
51 } {}
52 do_test rtree-1.1.2 {
53 execsql { SELECT name FROM sqlite_master ORDER BY name }
54 } {t1 t1_node t1_parent t1_rowid}
55 do_test rtree-1.1.3 {
56 execsql {
57 DROP TABLE t1;
58 SELECT name FROM sqlite_master ORDER BY name;
59 }
60 } {}
62 # Test creating and dropping an rtree table with an odd name in
63 # an attached database.
64 #
65 do_test rtree-1.2.1 {
66 file delete -force test2.db
67 execsql {
68 ATTACH 'test2.db' AS aux;
69 CREATE VIRTUAL TABLE aux.'a" "b' USING rtree(ii, x1, x2, y1, y2);
70 }
71 } {}
72 do_test rtree-1.2.2 {
73 execsql { SELECT name FROM sqlite_master ORDER BY name }
74 } {}
75 do_test rtree-1.2.3 {
76 execsql { SELECT name FROM aux.sqlite_master ORDER BY name }
77 } {{a" "b} {a" "b_node} {a" "b_parent} {a" "b_rowid}}
78 do_test rtree-1.2.4 {
79 execsql {
80 DROP TABLE aux.'a" "b';
81 SELECT name FROM aux.sqlite_master ORDER BY name;
82 }
83 } {}
85 # Test that the logic for checking the number of columns specified
86 # for an rtree table. Acceptable values are odd numbers between 3 and
87 # 11, inclusive.
88 #
89 set cols [list i1 i2 i3 i4 i5 i6 i7 i8 i9 iA iB iC iD iE iF iG iH iI iJ iK]
90 for {set nCol 1} {$nCol<[llength $cols]} {incr nCol} {
92 set columns [join [lrange $cols 0 [expr {$nCol-1}]] ,]
94 set X {0 {}}
95 if {$nCol%2 == 0} { set X {1 {Wrong number of columns for an rtree table}} }
96 if {$nCol < 3} { set X {1 {Too few columns for an rtree table}} }
97 if {$nCol > 11} { set X {1 {Too many columns for an rtree table}} }
99 do_test rtree-1.3.$nCol {
100 catchsql "
101 CREATE VIRTUAL TABLE t1 USING rtree($columns);
102 "
103 } $X
105 catchsql { DROP TABLE t1 }
106 }
108 # Like execsql except display output as integer where that can be
109 # done without loss of information.
110 #
111 proc execsql_intout {sql} {
112 set out {}
113 foreach term [execsql $sql] {
114 regsub {\.0$} $term {} term
115 lappend out $term
116 }
117 return $out
118 }
120 # Test that it is possible to open an existing database that contains
121 # r-tree tables.
122 #
123 do_test rtree-1.4.1 {
124 execsql {
125 CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2);
126 INSERT INTO t1 VALUES(1, 5.0, 10.0);
127 INSERT INTO t1 VALUES(2, 15.0, 20.0);
128 }
129 } {}
130 do_test rtree-1.4.2 {
131 db close
132 sqlite3 db test.db
133 execsql_intout { SELECT * FROM t1 ORDER BY ii }
134 } {1 5 10 2 15 20}
135 do_test rtree-1.4.3 {
136 execsql { DROP TABLE t1 }
137 } {}
139 # Test that it is possible to create an r-tree table with ridiculous
140 # column names.
141 #
142 do_test rtree-1.5.1 {
143 execsql_intout {
144 CREATE VIRTUAL TABLE t1 USING rtree("the key", "x dim.", "x2'dim");
145 INSERT INTO t1 VALUES(1, 2, 3);
146 SELECT "the key", "x dim.", "x2'dim" FROM t1;
147 }
148 } {1 2 3}
149 do_test rtree-1.5.1 {
150 execsql { DROP TABLE t1 }
151 } {}
153 # Force the r-tree constructor to fail.
154 #
155 do_test rtree-1.6.1 {
156 execsql { CREATE TABLE t1_rowid(a); }
157 catchsql {
158 CREATE VIRTUAL TABLE t1 USING rtree("the key", "x dim.", "x2'dim");
159 }
160 } {1 {table "t1_rowid" already exists}}
161 do_test rtree-1.6.1 {
162 execsql { DROP TABLE t1_rowid }
163 } {}
165 #----------------------------------------------------------------------------
166 # Test cases rtree-2.*
167 #
168 do_test rtree-2.1.1 {
169 execsql {
170 CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
171 SELECT * FROM t1;
172 }
173 } {}
175 do_test rtree-2.1.2 {
176 execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
177 execsql_intout { SELECT * FROM t1 }
178 } {1 1 3 2 4}
179 do_test rtree-2.1.3 {
180 execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
181 execsql { SELECT rowid FROM t1 ORDER BY rowid }
182 } {1 2}
183 do_test rtree-2.1.3 {
184 execsql { INSERT INTO t1 VALUES(NULL, 1, 3, 2, 4) }
185 execsql { SELECT ii FROM t1 ORDER BY ii }
186 } {1 2 3}
188 do_test rtree-2.2.1 {
189 catchsql { INSERT INTO t1 VALUES(2, 1, 3, 2, 4) }
190 } {1 {constraint failed}}
191 do_test rtree-2.2.2 {
192 catchsql { INSERT INTO t1 VALUES(4, 1, 3, 4, 2) }
193 } {1 {constraint failed}}
194 do_test rtree-2.2.3 {
195 catchsql { INSERT INTO t1 VALUES(4, 3, 1, 2, 4) }
196 } {1 {constraint failed}}
197 do_test rtree-2.2.4 {
198 execsql { SELECT ii FROM t1 ORDER BY ii }
199 } {1 2 3}
201 do_test rtree-2.X {
202 execsql { DROP TABLE t1 }
203 } {}
205 #----------------------------------------------------------------------------
206 # Test cases rtree-3.* test linear scans of r-tree table data. To test
207 # this we have to insert some data into an r-tree, but that is not the
208 # focus of these tests.
209 #
210 do_test rtree-3.1.1 {
211 execsql {
212 CREATE VIRTUAL TABLE t1 USING rtree(ii, x1, x2, y1, y2);
213 SELECT * FROM t1;
214 }
215 } {}
216 do_test rtree-3.1.2 {
217 execsql_intout {
218 INSERT INTO t1 VALUES(5, 1, 3, 2, 4);
219 SELECT * FROM t1;
220 }
221 } {5 1 3 2 4}
222 do_test rtree-3.1.3 {
223 execsql_intout {
224 INSERT INTO t1 VALUES(6, 2, 6, 4, 8);
225 SELECT * FROM t1;
226 }
227 } {5 1 3 2 4 6 2 6 4 8}
229 # Test the constraint on the coordinates (c[i]<=c[i+1] where (i%2==0)):
230 do_test rtree-3.2.1 {
231 catchsql { INSERT INTO t1 VALUES(7, 2, 6, 4, 3) }
232 } {1 {constraint failed}}
233 do_test rtree-3.2.2 {
234 catchsql { INSERT INTO t1 VALUES(8, 2, 6, 3, 3) }
235 } {0 {}}
237 #----------------------------------------------------------------------------
238 # Test cases rtree-5.* test DELETE operations.
239 #
240 do_test rtree-5.1.1 {
241 execsql { CREATE VIRTUAL TABLE t2 USING rtree(ii, x1, x2) }
242 } {}
243 do_test rtree-5.1.2 {
244 execsql_intout {
245 INSERT INTO t2 VALUES(1, 10, 20);
246 INSERT INTO t2 VALUES(2, 30, 40);
247 INSERT INTO t2 VALUES(3, 50, 60);
248 SELECT * FROM t2 ORDER BY ii;
249 }
250 } {1 10 20 2 30 40 3 50 60}
251 do_test rtree-5.1.3 {
252 execsql_intout {
253 DELETE FROM t2 WHERE ii=2;
254 SELECT * FROM t2 ORDER BY ii;
255 }
256 } {1 10 20 3 50 60}
257 do_test rtree-5.1.4 {
258 execsql_intout {
259 DELETE FROM t2 WHERE ii=1;
260 SELECT * FROM t2 ORDER BY ii;
261 }
262 } {3 50 60}
263 do_test rtree-5.1.5 {
264 execsql {
265 DELETE FROM t2 WHERE ii=3;
266 SELECT * FROM t2 ORDER BY ii;
267 }
268 } {}
269 do_test rtree-5.1.6 {
270 execsql { SELECT * FROM t2_rowid }
271 } {}
273 #----------------------------------------------------------------------------
274 # Test cases rtree-5.* test UPDATE operations.
275 #
276 do_test rtree-6.1.1 {
277 execsql { CREATE VIRTUAL TABLE t3 USING rtree(ii, x1, x2, y1, y2) }
278 } {}
279 do_test rtree-6.1.2 {
280 execsql_intout {
281 INSERT INTO t3 VALUES(1, 2, 3, 4, 5);
282 UPDATE t3 SET x2=5;
283 SELECT * FROM t3;
284 }
285 } {1 2 5 4 5}
286 do_test rtree-6.1.3 {
287 execsql { UPDATE t3 SET ii = 2 }
288 execsql_intout { SELECT * FROM t3 }
289 } {2 2 5 4 5}
291 #----------------------------------------------------------------------------
292 # Test cases rtree-7.* test rename operations.
293 #
294 do_test rtree-7.1.1 {
295 execsql {
296 CREATE VIRTUAL TABLE t4 USING rtree(ii, x1, x2, y1, y2, z1, z2);
297 INSERT INTO t4 VALUES(1, 2, 3, 4, 5, 6, 7);
298 }
299 } {}
300 do_test rtree-7.1.2 {
301 execsql { ALTER TABLE t4 RENAME TO t5 }
302 execsql_intout { SELECT * FROM t5 }
303 } {1 2 3 4 5 6 7}
304 do_test rtree-7.1.3 {
305 db close
306 sqlite3 db test.db
307 execsql_intout { SELECT * FROM t5 }
308 } {1 2 3 4 5 6 7}
309 do_test rtree-7.1.4 {
310 execsql { ALTER TABLE t5 RENAME TO 'raisara "one"'''}
311 execsql_intout { SELECT * FROM "raisara ""one""'" }
312 } {1 2 3 4 5 6 7}
313 do_test rtree-7.1.5 {
314 execsql_intout { SELECT * FROM 'raisara "one"''' }
315 } {1 2 3 4 5 6 7}
316 do_test rtree-7.1.6 {
317 execsql { ALTER TABLE "raisara ""one""'" RENAME TO "abc 123" }
318 execsql_intout { SELECT * FROM "abc 123" }
319 } {1 2 3 4 5 6 7}
320 do_test rtree-7.1.7 {
321 db close
322 sqlite3 db test.db
323 execsql_intout { SELECT * FROM "abc 123" }
324 } {1 2 3 4 5 6 7}
326 # An error midway through a rename operation.
327 do_test rtree-7.2.1 {
328 execsql {
329 CREATE TABLE t4_node(a);
330 }
331 catchsql { ALTER TABLE "abc 123" RENAME TO t4 }
332 } {1 {SQL logic error or missing database}}
333 do_test rtree-7.2.2 {
334 execsql_intout { SELECT * FROM "abc 123" }
335 } {1 2 3 4 5 6 7}
336 do_test rtree-7.2.3 {
337 execsql {
338 DROP TABLE t4_node;
339 CREATE TABLE t4_rowid(a);
340 }
341 catchsql { ALTER TABLE "abc 123" RENAME TO t4 }
342 } {1 {SQL logic error or missing database}}
343 do_test rtree-7.2.4 {
344 db close
345 sqlite3 db test.db
346 execsql_intout { SELECT * FROM "abc 123" }
347 } {1 2 3 4 5 6 7}
348 do_test rtree-7.2.5 {
349 execsql { DROP TABLE t4_rowid }
350 execsql { ALTER TABLE "abc 123" RENAME TO t4 }
351 execsql_intout { SELECT * FROM t4 }
352 } {1 2 3 4 5 6 7}
355 #----------------------------------------------------------------------------
356 # Test cases rtree-8.*
357 #
359 # Test that the function to determine if a leaf cell is part of the
360 # result set works.
361 do_test rtree-8.1.1 {
362 execsql {
363 CREATE VIRTUAL TABLE t6 USING rtree(ii, x1, x2);
364 INSERT INTO t6 VALUES(1, 3, 7);
365 INSERT INTO t6 VALUES(2, 4, 6);
366 }
367 } {}
368 do_test rtree-8.1.2 { execsql { SELECT ii FROM t6 WHERE x1>2 } } {1 2}
369 do_test rtree-8.1.3 { execsql { SELECT ii FROM t6 WHERE x1>3 } } {2}
370 do_test rtree-8.1.4 { execsql { SELECT ii FROM t6 WHERE x1>4 } } {}
371 do_test rtree-8.1.5 { execsql { SELECT ii FROM t6 WHERE x1>5 } } {}
372 do_test rtree-8.1.6 { execsql { SELECT ii FROM t6 WHERE x1<3 } } {}
373 do_test rtree-8.1.7 { execsql { SELECT ii FROM t6 WHERE x1<4 } } {1}
374 do_test rtree-8.1.8 { execsql { SELECT ii FROM t6 WHERE x1<5 } } {1 2}
376 #----------------------------------------------------------------------------
377 # Test cases rtree-9.*
378 #
379 # Test that ticket #3549 is fixed.
380 do_test rtree-9.1 {
381 execsql {
382 CREATE TABLE foo (id INTEGER PRIMARY KEY);
383 CREATE VIRTUAL TABLE bar USING rtree (id, minX, maxX, minY, maxY);
384 INSERT INTO foo VALUES (null);
385 INSERT INTO foo SELECT null FROM foo;
386 INSERT INTO foo SELECT null FROM foo;
387 INSERT INTO foo SELECT null FROM foo;
388 INSERT INTO foo SELECT null FROM foo;
389 INSERT INTO foo SELECT null FROM foo;
390 INSERT INTO foo SELECT null FROM foo;
391 DELETE FROM foo WHERE id > 40;
392 INSERT INTO bar SELECT NULL, 0, 0, 0, 0 FROM foo;
393 }
394 } {}
396 # This used to crash.
397 do_test rtree-9.2 {
398 execsql {
399 SELECT count(*) FROM bar b1, bar b2, foo s1 WHERE s1.id = b1.id;
400 }
401 } {1600}
402 do_test rtree-9.3 {
403 execsql {
404 SELECT count(*) FROM bar b1, bar b2, foo s1
405 WHERE b1.minX <= b2.maxX AND s1.id = b1.id;
406 }
407 } {1600}
409 #-------------------------------------------------------------------------
410 # Ticket #3970: Check that the error message is meaningful when a
411 # keyword is used as a column name.
412 #
413 do_test rtree-10.1 {
414 catchsql { CREATE VIRTUAL TABLE t7 USING rtree(index, x1, y1, x2, y2) }
415 } {1 {near "index": syntax error}}
417 #-------------------------------------------------------------------------
418 # Test last_insert_rowid().
419 #
420 do_test rtree-11.1 {
421 execsql {
422 CREATE VIRTUAL TABLE t8 USING rtree(idx, x1, x2, y1, y2);
423 INSERT INTO t8 VALUES(1, 1.0, 1.0, 2.0, 2.0);
424 SELECT last_insert_rowid();
425 }
426 } {1}
427 do_test rtree-11.2 {
428 execsql {
429 INSERT INTO t8 VALUES(NULL, 1.0, 1.0, 2.0, 2.0);
430 SELECT last_insert_rowid();
431 }
432 } {2}
434 #-------------------------------------------------------------------------
435 # Test on-conflict clause handling.
436 #
437 db_delete_and_reopen
438 do_execsql_test 12.0 {
439 CREATE VIRTUAL TABLE t1 USING rtree_i32(idx, x1, x2, y1, y2);
440 INSERT INTO t1 VALUES(1, 1, 2, 3, 4);
441 INSERT INTO t1 VALUES(2, 2, 3, 4, 5);
442 INSERT INTO t1 VALUES(3, 3, 4, 5, 6);
444 CREATE TABLE source(idx, x1, x2, y1, y2);
445 INSERT INTO source VALUES(5, 8, 8, 8, 8);
446 INSERT INTO source VALUES(2, 7, 7, 7, 7);
448 }
449 db_save_and_close
450 foreach {tn sql_template testdata} {
451 1 "INSERT %CONF% INTO t1 VALUES(2, 7, 7, 7, 7)" {
452 ROLLBACK 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6}
453 ABORT 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
454 IGNORE 0 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
455 FAIL 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
456 REPLACE 0 0 {1 1 2 3 4 2 7 7 7 7 3 3 4 5 6 4 4 5 6 7}
457 }
459 2 "INSERT %CONF% INTO t1 SELECT * FROM source" {
460 ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6}
461 ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
462 IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8}
463 FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8}
464 REPLACE 1 0 {1 1 2 3 4 2 7 7 7 7 3 3 4 5 6 4 4 5 6 7 5 8 8 8 8}
465 }
467 3 "UPDATE %CONF% t1 SET idx = 2 WHERE idx = 4" {
468 ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6}
469 ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
470 IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
471 FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
472 REPLACE 1 0 {1 1 2 3 4 2 4 5 6 7 3 3 4 5 6}
473 }
475 3 "UPDATE %CONF% t1 SET idx = ((idx+1)%5)+1 WHERE idx > 2" {
476 ROLLBACK 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6}
477 ABORT 1 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
478 IGNORE 1 0 {1 1 2 3 4 2 2 3 4 5 4 4 5 6 7 5 3 4 5 6}
479 FAIL 1 1 {1 1 2 3 4 2 2 3 4 5 4 4 5 6 7 5 3 4 5 6}
480 REPLACE 1 0 {1 4 5 6 7 2 2 3 4 5 5 3 4 5 6}
481 }
483 4 "INSERT %CONF% INTO t1 VALUES(2, 7, 6, 7, 7)" {
484 ROLLBACK 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6}
485 ABORT 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
486 IGNORE 0 0 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
487 FAIL 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
488 REPLACE 0 1 {1 1 2 3 4 2 2 3 4 5 3 3 4 5 6 4 4 5 6 7}
489 }
491 } {
492 foreach {mode uses error data} $testdata {
493 db_restore_and_reopen
495 set sql [string map [list %CONF% "OR $mode"] $sql_template]
496 set testname "12.$tn.[string tolower $mode]"
498 execsql {
499 BEGIN;
500 INSERT INTO t1 VALUES(4, 4, 5, 6, 7);
501 }
503 set res(0) {0 {}}
504 set res(1) {1 {constraint failed}}
505 do_catchsql_test $testname.1 $sql $res($error)
506 do_test $testname.2 [list sql_uses_stmt db $sql] $uses
507 do_execsql_test $testname.3 { SELECT * FROM t1 ORDER BY idx } $data
509 do_test $testname.4 { rtree_check db t1 } 0
510 db close
511 }
512 }
513 finish_test