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