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