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 26set testdir [file dirname $argv0] 27source $testdir/tester.tcl 28source $testdir/lock_common.tcl 29source $testdir/malloc_common.tcl 30db 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# 36set binaries [list] 37set pattern "[file tail [info nameofexec]]?*" 38if {$tcl_platform(platform)=="windows"} { 39 set pattern [string map {\.exe {}} $pattern] 40} 41foreach file [glob -nocomplain $pattern] { 42 if {[file executable $file] && [file isfile $file]} {lappend binaries $file} 43} 44if {[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} 50proc get_version {binary} { 51 set chan [launch_testfixture $binary] 52 set v [testfixture $chan { sqlite3 -version }] 53 close $chan 54 set v 55} 56foreach bin $binaries { 57 puts -nonewline "Testing against $bin - " 58 flush stdout 59 puts "version [get_version $bin]" 60} 61 62proc 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 93array set ::incompatible [list] 94proc 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 122proc 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} 141proc 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} 147proc 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} 152proc 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# 169do_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} 228foreach k [lsort [array names ::incompatible]] { 229 puts "ERROR: Detected journal incompatibility with version $k" 230} 231unset ::incompatible 232 233 234#------------------------------------------------------------------------- 235# Test that WAL and wal-index files may be shared between different 236# SQLite versions. 237# 238do_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# 278set contents { 279 CREATE VIRTUAL TABLE t1 USING fts3(a, b); 280} 281foreach {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} 294do_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# 376set contents { 377 CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2); 378} 379foreach {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} { 406if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" } 407 append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);" 408} 409set 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} 415do_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 445finish_test 446