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