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 do_test backcompat-3.9 { 424 sql1 { INSERT INTO t2(t2) VALUES('merge=100,4'); } 425 sql2 { INSERT INTO t2(t2) VALUES('merge=100,4'); } 426 sql1 { INSERT INTO t2(t2) VALUES('merge=100,4'); } 427 sql2 { INSERT INTO t2(t2) VALUES('merge=2500,4'); } 428 sql2 { 429 SELECT level, group_concat(idx, ' ') FROM t2_segdir GROUP BY level; 430 } 431 } {0 {0 1} 1 0} 432 433 do_test backcompat-3.10 { 434 sql1 { INSERT INTO t2(t2) VALUES('integrity-check') } 435 sql2 { INSERT INTO t2(t2) VALUES('integrity-check') } 436 } {} 437 } 438 } 439 } 440} 441 442#------------------------------------------------------------------------- 443# Test that Rtree tables may be read/written by different versions of 444# SQLite. 445# 446ifcapable rtree { 447 set contents { 448 CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2); 449 } 450 foreach {id x1 x2 y1 y2} { 451 1 -47.64 43.87 33.86 34.42 2 -21.51 17.32 2.05 31.04 452 3 -43.67 -38.33 -19.79 3.43 4 32.41 35.16 9.12 19.82 453 5 33.28 34.87 14.78 28.26 6 49.31 116.59 -9.87 75.09 454 7 -14.93 34.51 -17.64 64.09 8 -43.05 23.43 -1.19 69.44 455 9 44.79 133.56 28.09 80.30 10 -2.66 81.47 -41.38 -10.46 456 11 -42.89 -3.54 15.76 71.63 12 -3.50 84.96 -11.64 64.95 457 13 -45.69 26.25 11.14 55.06 14 -44.09 11.23 17.52 44.45 458 15 36.23 133.49 -19.38 53.67 16 -17.89 81.54 14.64 50.61 459 17 -41.97 -24.04 -39.43 28.95 18 -5.85 7.76 -6.38 47.02 460 19 18.82 27.10 42.82 100.09 20 39.17 113.45 26.14 73.47 461 21 22.31 103.17 49.92 106.05 22 -43.06 40.38 -1.75 76.08 462 23 2.43 57.27 -14.19 -3.83 24 -47.57 -4.35 8.93 100.06 463 25 -37.47 49.14 -29.11 8.81 26 -7.86 75.72 49.34 107.42 464 27 1.53 45.49 20.36 49.74 28 -48.48 32.54 28.81 54.45 465 29 2.67 39.77 -4.05 13.67 30 4.11 62.88 -47.44 -5.72 466 31 -21.47 51.75 37.25 116.09 32 45.59 111.37 -6.43 43.64 467 33 35.23 48.29 23.54 113.33 34 16.61 68.35 -14.69 65.97 468 35 13.98 16.60 48.66 102.87 36 19.74 23.84 31.15 77.27 469 37 -27.61 24.43 7.96 94.91 38 -34.77 12.05 -22.60 -6.29 470 39 -25.83 8.71 -13.48 -12.53 40 -17.11 -1.01 18.06 67.89 471 41 14.13 71.72 -3.78 39.25 42 23.75 76.00 -16.30 8.23 472 43 -39.15 28.63 38.12 125.88 44 48.62 86.09 36.49 102.95 473 45 -31.39 -21.98 2.52 89.78 46 5.65 56.04 15.94 89.10 474 47 18.28 95.81 46.46 143.08 48 30.93 102.82 -20.08 37.36 475 49 -20.78 -3.48 -5.58 35.46 50 49.85 90.58 -24.48 46.29 476 } { 477 if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" } 478 append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);" 479 } 480 set queries { 481 1 "SELECT id FROM t1 WHERE x1>10 AND x2<44" 482 2 "SELECT id FROM t1 WHERE y1<100" 483 3 "SELECT id FROM t1 WHERE y1<100 AND x1>0" 484 4 "SELECT id FROM t1 WHERE y1>10 AND x1>0 AND x2<50 AND y2<550" 485 } 486 do_allbackcompat_test { 487 if {[code1 {set ::sqlite_options(fts3)}] 488 && [code2 {set ::sqlite_options(fts3)}] 489 } { 490 491 do_test backcompat-4.1 { sql1 $contents } {} 492 493 foreach {n q} $::queries { 494 do_test backcompat-4.2.$n [list sql1 $q] [sql2 $q] 495 } 496 497 do_test backcompat-4.3 { sql1 { 498 INSERT INTO t1 SELECT id+100, x1+10.0, x2+10.0, y1-10.0, y2-10.0 FROM t1; 499 } } {} 500 501 foreach {n q} $::queries { 502 do_test backcompat-4.4.$n [list sql1 $q] [sql2 $q] 503 } 504 505 do_test backcompat-4.5 { sql2 { 506 INSERT INTO t1 SELECT id+200, x1+20.0, x2+20.0, y1-20.0, y2-20.0 FROM t1; 507 } } {} 508 509 foreach {n q} $::queries { 510 do_test backcompat-4.6.$n [list sql1 $q] [sql2 $q] 511 } 512 513 } 514 } 515} 516 517finish_test 518