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 $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 111proc read_file {zFile} { 112 set zData {} 113 if {[file exists $zFile]} { 114 set fd [open $zFile] 115 fconfigure $fd -translation binary -encoding 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} 130proc write_file {zFile zData} { 131 set fd [open $zFile w] 132 fconfigure $fd -translation binary -encoding binary 133 puts -nonewline $fd $zData 134 close $fd 135} 136proc 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} 141proc 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# 158do_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} 253foreach k [lsort [array names ::incompatible]] { 254 puts "ERROR: Detected journal incompatibility with version $k" 255} 256unset ::incompatible 257 258 259#------------------------------------------------------------------------- 260# Test that WAL and wal-index files may be shared between different 261# SQLite versions. 262# 263do_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# 305ifcapable 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# 453ifcapable 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 524finish_test 525