| 1 |
# 2010 August 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 |
# This file implements regression tests for SQLite library. The |
| 12 |
# focus of this file is testing that the current version of SQLite |
| 13 |
# is capable of reading and writing databases created by previous |
| 14 |
# versions, and vice-versa. |
| 15 |
# |
| 16 |
# To use this test, old versions of the testfixture process should be |
| 17 |
# copied into the working directory alongside the new version. The old |
| 18 |
# versions should be named "testfixtureXXX" (or testfixtureXXX.exe on |
| 19 |
# windows), where XXX can be any string. |
| 20 |
# |
| 21 |
# This test file uses the tcl code for controlling a second testfixture |
| 22 |
# process located in lock_common.tcl. See the commments in lock_common.tcl |
| 23 |
# for documentation of the available commands. |
| 24 |
# |
| 25 |
|
| 26 |
set testdir [file dirname $argv0] |
| 27 |
source $testdir/tester.tcl |
| 28 |
source $testdir/lock_common.tcl |
| 29 |
source $testdir/malloc_common.tcl |
| 30 |
source $testdir/bc_common.tcl |
| 31 |
db close |
| 32 |
|
| 33 |
if {"" == [bc_find_binaries backcompat.test]} { |
| 34 |
finish_test |
| 35 |
return |
| 36 |
} |
| 37 |
|
| 38 |
proc do_backcompat_test {rv bin1 bin2 script} { |
| 39 |
|
| 40 |
forcedelete test.db |
| 41 |
|
| 42 |
if {$bin1 != ""} { set ::bc_chan1 [launch_testfixture $bin1] } |
| 43 |
set ::bc_chan2 [launch_testfixture $bin2] |
| 44 |
|
| 45 |
if { $rv } { |
| 46 |
proc code2 {tcl} { uplevel #0 $tcl } |
| 47 |
if {$bin1 != ""} { proc code2 {tcl} { testfixture $::bc_chan1 $tcl } } |
| 48 |
proc code1 {tcl} { testfixture $::bc_chan2 $tcl } |
| 49 |
} else { |
| 50 |
proc code1 {tcl} { uplevel #0 $tcl } |
| 51 |
if {$bin1 != ""} { proc code1 {tcl} { testfixture $::bc_chan1 $tcl } } |
| 52 |
proc code2 {tcl} { testfixture $::bc_chan2 $tcl } |
| 53 |
} |
| 54 |
|
| 55 |
proc sql1 sql { code1 [list db eval $sql] } |
| 56 |
proc sql2 sql { code2 [list db eval $sql] } |
| 57 |
|
| 58 |
code1 { sqlite3 db test.db } |
| 59 |
code2 { sqlite3 db test.db } |
| 60 |
|
| 61 |
foreach c {code1 code2} { |
| 62 |
$c { |
| 63 |
set v [split [db version] .] |
| 64 |
if {[llength $v]==3} {lappend v 0} |
| 65 |
set ::sqlite_libversion [format \ |
| 66 |
"%d%.2d%.2d%.2d" [lindex $v 0] [lindex $v 1] [lindex $v 2] [lindex $v 3] |
| 67 |
] |
| 68 |
} |
| 69 |
} |
| 70 |
|
| 71 |
uplevel $script |
| 72 |
|
| 73 |
catch { code1 { db close } } |
| 74 |
catch { code2 { db close } } |
| 75 |
catch { close $::bc_chan2 } |
| 76 |
catch { close $::bc_chan1 } |
| 77 |
|
| 78 |
|
| 79 |
} |
| 80 |
|
| 81 |
array set ::incompatible [list] |
| 82 |
proc do_allbackcompat_test {script} { |
| 83 |
|
| 84 |
foreach bin $::BC(binaries) { |
| 85 |
set nErr [set_test_counter errors] |
| 86 |
foreach dir {0 1} { |
| 87 |
|
| 88 |
set bintag $bin |
| 89 |
regsub {.*testfixture\.} $bintag {} bintag |
| 90 |
set bintag [string map {\.exe {}} $bintag] |
| 91 |
if {$bintag == ""} {set bintag self} |
| 92 |
set ::bcname ".$bintag.$dir." |
| 93 |
|
| 94 |
rename do_test _do_test |
| 95 |
proc do_test {nm sql res} { |
| 96 |
set nm [regsub {\.} $nm $::bcname] |
| 97 |
uplevel [list _do_test $nm $sql $res] |
| 98 |
} |
| 99 |
|
| 100 |
do_backcompat_test $dir {} $bin $script |
| 101 |
|
| 102 |
rename do_test {} |
| 103 |
rename _do_test do_test |
| 104 |
} |
| 105 |
if { $nErr < [set_test_counter errors] } { |
| 106 |
set ::incompatible([get_version $bin]) 1 |
| 107 |
} |
| 108 |
} |
| 109 |
} |
| 110 |
|
| 111 |
proc read_file {zFile} { |
| 112 |
set zData {} |
| 113 |
if {[file exists $zFile]} { |
| 114 |
set fd [open $zFile] |
| 115 |
fconfigure $fd -translation binary |
| 116 |
|
| 117 |
if {[file size $zFile]<=$::sqlite_pending_byte || $zFile != "test.db"} { |
| 118 |
set zData [read $fd] |
| 119 |
} else { |
| 120 |
set zData [read $fd $::sqlite_pending_byte] |
| 121 |
append zData [string repeat x 512] |
| 122 |
seek $fd [expr $::sqlite_pending_byte+512] start |
| 123 |
append zData [read $fd] |
| 124 |
} |
| 125 |
|
| 126 |
close $fd |
| 127 |
} |
| 128 |
return $zData |
| 129 |
} |
| 130 |
proc write_file {zFile zData} { |
| 131 |
set fd [open $zFile w] |
| 132 |
fconfigure $fd -translation binary |
| 133 |
puts -nonewline $fd $zData |
| 134 |
close $fd |
| 135 |
} |
| 136 |
proc read_file_system {} { |
| 137 |
set ret [list] |
| 138 |
foreach f {test.db test.db-journal test.db-wal} { lappend ret [read_file $f] } |
| 139 |
set ret |
| 140 |
} |
| 141 |
proc write_file_system {data} { |
| 142 |
foreach f {test.db test.db-journal test.db-wal} d $data { |
| 143 |
if {[string length $d] == 0} { |
| 144 |
forcedelete $f |
| 145 |
} else { |
| 146 |
write_file $f $d |
| 147 |
} |
| 148 |
} |
| 149 |
} |
| 150 |
|
| 151 |
#------------------------------------------------------------------------- |
| 152 |
# Actual tests begin here. |
| 153 |
# |
| 154 |
# This first block of tests checks to see that the same database and |
| 155 |
# journal files can be used by old and new versions. WAL and wal-index |
| 156 |
# files are tested separately below. |
| 157 |
# |
| 158 |
do_allbackcompat_test { |
| 159 |
|
| 160 |
# Test that database files are backwards compatible. |
| 161 |
# |
| 162 |
do_test backcompat-1.1.1 { sql1 { |
| 163 |
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); |
| 164 |
INSERT INTO t1 VALUES('abc', 'def'); |
| 165 |
} } {} |
| 166 |
do_test backcompat-1.1.2 { sql2 { SELECT * FROM t1; } } {abc def} |
| 167 |
do_test backcompat-1.1.3 { sql2 { INSERT INTO t1 VALUES('ghi', 'jkl'); } } {} |
| 168 |
do_test backcompat-1.1.4 { sql1 { SELECT * FROM t1; } } {abc def ghi jkl} |
| 169 |
do_test backcompat-1.1.5 { sql1 { PRAGMA integrity_check } } {ok} |
| 170 |
do_test backcompat-1.1.6 { sql2 { PRAGMA integrity_check } } {ok} |
| 171 |
|
| 172 |
# Test that one version can roll back a hot-journal file left in the |
| 173 |
# file-system by the other version. |
| 174 |
# |
| 175 |
# Each test case is named "backcompat-1.X...", where X is either 0 or |
| 176 |
# 1. If it is 0, then the current version creates a journal file that |
| 177 |
# the old versions try to read. Otherwise, if X is 1, then the old version |
| 178 |
# creates the journal file and we try to read it with the current version. |
| 179 |
# |
| 180 |
do_test backcompat-1.2.1 { sql1 { |
| 181 |
PRAGMA cache_size = 10; |
| 182 |
BEGIN; |
| 183 |
INSERT INTO t1 VALUES(randomblob(400), randomblob(400)); |
| 184 |
INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM t1; |
| 185 |
INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM t1; |
| 186 |
INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM t1; |
| 187 |
INSERT INTO t1 SELECT randomblob(400), randomblob(400) FROM t1; |
| 188 |
COMMIT; |
| 189 |
} } {} |
| 190 |
set cksum1 [sql1 {SELECT md5sum(a), md5sum(b) FROM t1}] |
| 191 |
set cksum2 [sql2 {SELECT md5sum(a), md5sum(b) FROM t1}] |
| 192 |
do_test backcompat-1.2.2 [list string compare $cksum1 $cksum2] 0 |
| 193 |
|
| 194 |
do_test backcompat-1.2.3 { sql1 { |
| 195 |
BEGIN; |
| 196 |
UPDATE t1 SET a = randomblob(500); |
| 197 |
} } {} |
| 198 |
set data [read_file_system] |
| 199 |
|
| 200 |
do_test backcompat-1.2.4 { sql1 { COMMIT } } {} |
| 201 |
|
| 202 |
set same [expr {[sql2 {SELECT md5sum(a), md5sum(b) FROM t1}] == $cksum2}] |
| 203 |
do_test backcompat-1.2.5 [list set {} $same] 0 |
| 204 |
|
| 205 |
code1 { db close } |
| 206 |
code2 { db close } |
| 207 |
write_file_system $data |
| 208 |
code1 { sqlite3 db test.db } |
| 209 |
code2 { sqlite3 db test.db } |
| 210 |
|
| 211 |
set same [expr {[sql2 {SELECT md5sum(a), md5sum(b) FROM t1}] == $cksum2}] |
| 212 |
do_test backcompat-1.2.6 [list set {} $same] 1 |
| 213 |
|
| 214 |
do_test backcompat-1.2.7 { sql1 { PRAGMA integrity_check } } {ok} |
| 215 |
do_test backcompat-1.2.8 { sql2 { PRAGMA integrity_check } } {ok} |
| 216 |
|
| 217 |
do_test backcompat-2.1 { |
| 218 |
sql1 { |
| 219 |
CREATE TABLE t2(a UNIQUE, b PRIMARY KEY, c UNIQUE); |
| 220 |
INSERT INTO t2 VALUES(1,9,5); |
| 221 |
INSERT INTO t2 VALUES(5,5,1); |
| 222 |
INSERT INTO t2 VALUES(9,1,9); |
| 223 |
SELECT * FROM t2 ORDER BY a; |
| 224 |
} |
| 225 |
} {1 9 5 5 5 1 9 1 9} |
| 226 |
do_test backcompat-2.2 { |
| 227 |
sql2 { |
| 228 |
SELECT * FROM sqlite_master WHERE rootpage=-1; |
| 229 |
SELECT * FROM t2 ORDER BY a; |
| 230 |
} |
| 231 |
} {1 9 5 5 5 1 9 1 9} |
| 232 |
do_test backcompat-2.3 { |
| 233 |
sql1 { |
| 234 |
SELECT * FROM t2 ORDER BY b; |
| 235 |
} |
| 236 |
} {9 1 9 5 5 1 1 9 5} |
| 237 |
do_test backcompat-2.4 { |
| 238 |
sql2 { |
| 239 |
SELECT * FROM t2 ORDER BY b; |
| 240 |
} |
| 241 |
} {9 1 9 5 5 1 1 9 5} |
| 242 |
do_test backcompat-2.5 { |
| 243 |
sql1 { |
| 244 |
SELECT * FROM t2 ORDER BY c; |
| 245 |
} |
| 246 |
} {5 5 1 1 9 5 9 1 9} |
| 247 |
do_test backcompat-2.6 { |
| 248 |
sql2 { |
| 249 |
SELECT * FROM t2 ORDER BY c; |
| 250 |
} |
| 251 |
} {5 5 1 1 9 5 9 1 9} |
| 252 |
} |
| 253 |
foreach k [lsort [array names ::incompatible]] { |
| 254 |
puts "ERROR: Detected journal incompatibility with version $k" |
| 255 |
} |
| 256 |
unset ::incompatible |
| 257 |
|
| 258 |
|
| 259 |
#------------------------------------------------------------------------- |
| 260 |
# Test that WAL and wal-index files may be shared between different |
| 261 |
# SQLite versions. |
| 262 |
# |
| 263 |
do_allbackcompat_test { |
| 264 |
if {[code1 {sqlite3 -version}] >= "3.7.0" |
| 265 |
&& [code1 {set ::sqlite_options(wal)}] |
| 266 |
&& [code2 {sqlite3 -version}] >= "3.7.0" |
| 267 |
&& [code2 {set ::sqlite_options(wal)}] |
| 268 |
} { |
| 269 |
|
| 270 |
do_test backcompat-2.1.1 { sql1 { |
| 271 |
PRAGMA journal_mode = WAL; |
| 272 |
CREATE TABLE t1(a PRIMARY KEY, b UNIQUE); |
| 273 |
INSERT INTO t1 VALUES('I', 1); |
| 274 |
INSERT INTO t1 VALUES('II', 2); |
| 275 |
INSERT INTO t1 VALUES('III', 3); |
| 276 |
SELECT * FROM t1; |
| 277 |
} } {wal I 1 II 2 III 3} |
| 278 |
do_test backcompat-2.1.2 { sql2 { |
| 279 |
SELECT * FROM t1; |
| 280 |
} } {I 1 II 2 III 3} |
| 281 |
|
| 282 |
set data [read_file_system] |
| 283 |
code1 {db close} |
| 284 |
code2 {db close} |
| 285 |
write_file_system $data |
| 286 |
code1 {sqlite3 db test.db} |
| 287 |
code2 {sqlite3 db test.db} |
| 288 |
|
| 289 |
# The WAL file now in the file-system was created by the [code1] |
| 290 |
# process. Check that the [code2] process can recover the log. |
| 291 |
# |
| 292 |
do_test backcompat-2.1.3 { sql2 { |
| 293 |
SELECT * FROM t1; |
| 294 |
} } {I 1 II 2 III 3} |
| 295 |
do_test backcompat-2.1.4 { sql1 { |
| 296 |
SELECT * FROM t1; |
| 297 |
} } {I 1 II 2 III 3} |
| 298 |
} |
| 299 |
} |
| 300 |
|
| 301 |
#------------------------------------------------------------------------- |
| 302 |
# Test that FTS3 tables may be read/written by different versions of |
| 303 |
# SQLite. |
| 304 |
# |
| 305 |
ifcapable fts3 { |
| 306 |
set contents { |
| 307 |
CREATE VIRTUAL TABLE t1 USING fts3(a, b); |
| 308 |
} |
| 309 |
foreach {num doc} { |
| 310 |
one "jk zm jk eczkjblu urvysbnykk sk gnl jk ttvgf hmjf" |
| 311 |
two "jk bnhc jjrxpjkb mjpavjuhw fibokdry igju jk zm zm xh" |
| 312 |
three "wxe ogttbykvt uhzq xr iaf zf urvysbnykk aayxpmve oacaxgjoo mjpavjuhw" |
| 313 |
four "gazrt jk ephknonq myjp uenvbm wuvajhwqz jk zm xnxhf nvfasfh" |
| 314 |
five "zm aayxpmve csjqxhgj xnxhf xr jk aayxpmve xnxhf zm zm" |
| 315 |
six "sokcyf zm ogyavjvv jk zm fibokdry zm jk igju igju" |
| 316 |
seven "vgsld bvgimjik xuprtlyle jk akmikrqyt jk aayxpmve hkfoudzftq ddjj" |
| 317 |
eight "zm uhzq ovkyevlgv zk uenvbm csjqxhgj jk vgsld pgybs jk" |
| 318 |
nine "zm agmckuiu zexh fibokdry jk uhzq bu tugflixoex xnxhf sk" |
| 319 |
} { |
| 320 |
append contents "INSERT INTO t1 VALUES('$num', '$doc');" |
| 321 |
} |
| 322 |
do_allbackcompat_test { |
| 323 |
if {[code1 {set ::sqlite_options(fts3)}] |
| 324 |
&& [code2 {set ::sqlite_options(fts3)}] |
| 325 |
} { |
| 326 |
|
| 327 |
do_test backcompat-3.1 { sql1 $contents } {} |
| 328 |
|
| 329 |
foreach {n q} { |
| 330 |
1 "SELECT * FROM t1 ORDER BY a, b" |
| 331 |
2 "SELECT rowid FROM t1 WHERE a MATCH 'five'" |
| 332 |
3 "SELECT * FROM t1 WHERE a MATCH 'five'" |
| 333 |
4 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'" |
| 334 |
5 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'" |
| 335 |
} { |
| 336 |
do_test backcompat-3.2 [list sql1 $q] [sql2 $q] |
| 337 |
} |
| 338 |
|
| 339 |
do_test backcompat-3.3 { sql1 { |
| 340 |
INSERT INTO t1 SELECT * FROM t1; |
| 341 |
INSERT INTO t1 SELECT * FROM t1; |
| 342 |
INSERT INTO t1 SELECT * FROM t1; |
| 343 |
INSERT INTO t1 SELECT * FROM t1; |
| 344 |
INSERT INTO t1 SELECT * FROM t1; |
| 345 |
INSERT INTO t1 SELECT * FROM t1; |
| 346 |
INSERT INTO t1 SELECT * FROM t1; |
| 347 |
INSERT INTO t1 SELECT * FROM t1; |
| 348 |
} } {} |
| 349 |
|
| 350 |
foreach {n q} { |
| 351 |
1 "SELECT * FROM t1 ORDER BY a, b" |
| 352 |
2 "SELECT rowid FROM t1 WHERE a MATCH 'five'" |
| 353 |
3 "SELECT * FROM t1 WHERE a MATCH 'five'" |
| 354 |
4 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'" |
| 355 |
5 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'" |
| 356 |
} { |
| 357 |
do_test backcompat-3.4 [list sql1 $q] [sql2 $q] |
| 358 |
} |
| 359 |
|
| 360 |
set alphabet "a b c d e f g h i j k l m n o p q r s t u v w x y z 1 2 3 4" |
| 361 |
for {set i 0} {$i < 900} {incr i} { |
| 362 |
set term "[lindex $alphabet [expr $i/30]][lindex $alphabet [expr $i%30]] " |
| 363 |
sql1 "INSERT INTO t1 VALUES($i, '[string repeat $term 14]')" |
| 364 |
} |
| 365 |
|
| 366 |
foreach {n q} { |
| 367 |
1 "SELECT * FROM t1 ORDER BY a, b" |
| 368 |
2 "SELECT rowid FROM t1 WHERE a MATCH 'five'" |
| 369 |
3 "SELECT * FROM t1 WHERE a MATCH 'five'" |
| 370 |
4 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'" |
| 371 |
5 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'" |
| 372 |
|
| 373 |
6 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'aa'" |
| 374 |
7 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH '44'" |
| 375 |
8 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'a*'" |
| 376 |
} { |
| 377 |
do_test backcompat-3.5 [list sql1 $q] [sql2 $q] |
| 378 |
} |
| 379 |
|
| 380 |
do_test backcompat-3.6 { |
| 381 |
sql1 "SELECT optimize(t1) FROM t1 LIMIT 1" |
| 382 |
} {{Index optimized}} |
| 383 |
|
| 384 |
foreach {n q} { |
| 385 |
1 "SELECT * FROM t1 ORDER BY a, b" |
| 386 |
2 "SELECT rowid FROM t1 WHERE a MATCH 'five'" |
| 387 |
3 "SELECT * FROM t1 WHERE a MATCH 'five'" |
| 388 |
4 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'jk'" |
| 389 |
5 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'tug* OR eight'" |
| 390 |
|
| 391 |
6 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'aa'" |
| 392 |
7 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH '44'" |
| 393 |
8 "SELECT offsets(t1) FROM t1 WHERE t1 MATCH 'a*'" |
| 394 |
} { |
| 395 |
do_test backcompat-3.7 [list sql1 $q] [sql2 $q] |
| 396 |
} |
| 397 |
|
| 398 |
# Now test that an incremental merge can be started by one version |
| 399 |
# and finished by another. And that the integrity-check still |
| 400 |
# passes. |
| 401 |
do_test backcompat-3.8 { |
| 402 |
sql1 { |
| 403 |
DROP TABLE IF EXISTS t1; |
| 404 |
DROP TABLE IF EXISTS t2; |
| 405 |
CREATE TABLE t1(docid, words); |
| 406 |
CREATE VIRTUAL TABLE t2 USING fts3(words); |
| 407 |
} |
| 408 |
code1 [list source $testdir/genesis.tcl] |
| 409 |
code1 { fts_kjv_genesis } |
| 410 |
sql1 { |
| 411 |
INSERT INTO t2 SELECT words FROM t1; |
| 412 |
INSERT INTO t2 SELECT words FROM t1; |
| 413 |
INSERT INTO t2 SELECT words FROM t1; |
| 414 |
INSERT INTO t2 SELECT words FROM t1; |
| 415 |
INSERT INTO t2 SELECT words FROM t1; |
| 416 |
INSERT INTO t2 SELECT words FROM t1; |
| 417 |
SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level; |
| 418 |
} |
| 419 |
} {0 {0 1 2 3 4 5}} |
| 420 |
|
| 421 |
if {[code1 { set ::sqlite_libversion }] >=3071200 |
| 422 |
&& [code2 { set ::sqlite_libversion }] >=3071200 |
| 423 |
} { |
| 424 |
if {[code1 { set ::sqlite_libversion }]<3120000} { |
| 425 |
set res {0 {0 1} 1 0} |
| 426 |
} else { |
| 427 |
set res {1 0} |
| 428 |
} |
| 429 |
|
| 430 |
do_test backcompat-3.9 { |
| 431 |
sql1 { INSERT INTO t2(t2) VALUES('merge=100,4'); } |
| 432 |
sql2 { INSERT INTO t2(t2) VALUES('merge=100,4'); } |
| 433 |
sql1 { INSERT INTO t2(t2) VALUES('merge=100,4'); } |
| 434 |
sql2 { INSERT INTO t2(t2) VALUES('merge=2500,4'); } |
| 435 |
sql2 { |
| 436 |
SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level; |
| 437 |
} |
| 438 |
} $res |
| 439 |
|
| 440 |
do_test backcompat-3.10 { |
| 441 |
sql1 { INSERT INTO t2(t2) VALUES('integrity-check') } |
| 442 |
sql2 { INSERT INTO t2(t2) VALUES('integrity-check') } |
| 443 |
} {} |
| 444 |
} |
| 445 |
} |
| 446 |
} |
| 447 |
} |
| 448 |
|
| 449 |
#------------------------------------------------------------------------- |
| 450 |
# Test that Rtree tables may be read/written by different versions of |
| 451 |
# SQLite. |
| 452 |
# |
| 453 |
ifcapable rtree { |
| 454 |
set contents { |
| 455 |
CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2); |
| 456 |
} |
| 457 |
foreach {id x1 x2 y1 y2} { |
| 458 |
1 -47.64 43.87 33.86 34.42 2 -21.51 17.32 2.05 31.04 |
| 459 |
3 -43.67 -38.33 -19.79 3.43 4 32.41 35.16 9.12 19.82 |
| 460 |
5 33.28 34.87 14.78 28.26 6 49.31 116.59 -9.87 75.09 |
| 461 |
7 -14.93 34.51 -17.64 64.09 8 -43.05 23.43 -1.19 69.44 |
| 462 |
9 44.79 133.56 28.09 80.30 10 -2.66 81.47 -41.38 -10.46 |
| 463 |
11 -42.89 -3.54 15.76 71.63 12 -3.50 84.96 -11.64 64.95 |
| 464 |
13 -45.69 26.25 11.14 55.06 14 -44.09 11.23 17.52 44.45 |
| 465 |
15 36.23 133.49 -19.38 53.67 16 -17.89 81.54 14.64 50.61 |
| 466 |
17 -41.97 -24.04 -39.43 28.95 18 -5.85 7.76 -6.38 47.02 |
| 467 |
19 18.82 27.10 42.82 100.09 20 39.17 113.45 26.14 73.47 |
| 468 |
21 22.31 103.17 49.92 106.05 22 -43.06 40.38 -1.75 76.08 |
| 469 |
23 2.43 57.27 -14.19 -3.83 24 -47.57 -4.35 8.93 100.06 |
| 470 |
25 -37.47 49.14 -29.11 8.81 26 -7.86 75.72 49.34 107.42 |
| 471 |
27 1.53 45.49 20.36 49.74 28 -48.48 32.54 28.81 54.45 |
| 472 |
29 2.67 39.77 -4.05 13.67 30 4.11 62.88 -47.44 -5.72 |
| 473 |
31 -21.47 51.75 37.25 116.09 32 45.59 111.37 -6.43 43.64 |
| 474 |
33 35.23 48.29 23.54 113.33 34 16.61 68.35 -14.69 65.97 |
| 475 |
35 13.98 16.60 48.66 102.87 36 19.74 23.84 31.15 77.27 |
| 476 |
37 -27.61 24.43 7.96 94.91 38 -34.77 12.05 -22.60 -6.29 |
| 477 |
39 -25.83 8.71 -13.48 -12.53 40 -17.11 -1.01 18.06 67.89 |
| 478 |
41 14.13 71.72 -3.78 39.25 42 23.75 76.00 -16.30 8.23 |
| 479 |
43 -39.15 28.63 38.12 125.88 44 48.62 86.09 36.49 102.95 |
| 480 |
45 -31.39 -21.98 2.52 89.78 46 5.65 56.04 15.94 89.10 |
| 481 |
47 18.28 95.81 46.46 143.08 48 30.93 102.82 -20.08 37.36 |
| 482 |
49 -20.78 -3.48 -5.58 35.46 50 49.85 90.58 -24.48 46.29 |
| 483 |
} { |
| 484 |
if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" } |
| 485 |
append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);" |
| 486 |
} |
| 487 |
set queries { |
| 488 |
1 "SELECT id FROM t1 WHERE x1>10 AND x2<44" |
| 489 |
2 "SELECT id FROM t1 WHERE y1<100" |
| 490 |
3 "SELECT id FROM t1 WHERE y1<100 AND x1>0" |
| 491 |
4 "SELECT id FROM t1 WHERE y1>10 AND x1>0 AND x2<50 AND y2<550" |
| 492 |
} |
| 493 |
do_allbackcompat_test { |
| 494 |
if {[code1 {set ::sqlite_options(fts3)}] |
| 495 |
&& [code2 {set ::sqlite_options(fts3)}] |
| 496 |
} { |
| 497 |
|
| 498 |
do_test backcompat-4.1 { sql1 $contents } {} |
| 499 |
|
| 500 |
foreach {n q} $::queries { |
| 501 |
do_test backcompat-4.2.$n [list sql1 $q] [sql2 $q] |
| 502 |
} |
| 503 |
|
| 504 |
do_test backcompat-4.3 { sql1 { |
| 505 |
INSERT INTO t1 SELECT id+100, x1+10.0, x2+10.0, y1-10.0, y2-10.0 FROM t1; |
| 506 |
} } {} |
| 507 |
|
| 508 |
foreach {n q} $::queries { |
| 509 |
do_test backcompat-4.4.$n [list sql1 $q] [sql2 $q] |
| 510 |
} |
| 511 |
|
| 512 |
do_test backcompat-4.5 { sql2 { |
| 513 |
INSERT INTO t1 SELECT id+200, x1+20.0, x2+20.0, y1-20.0, y2-20.0 FROM t1; |
| 514 |
} } {} |
| 515 |
|
| 516 |
foreach {n q} $::queries { |
| 517 |
do_test backcompat-4.6.$n [list sql1 $q] [sql2 $q] |
| 518 |
} |
| 519 |
|
| 520 |
} |
| 521 |
} |
| 522 |
} |
| 523 |
|
| 524 |
finish_test |