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