Annotation of embedaddon/sqlite3/test/backcompat.test, revision 1.1
1.1 ! misho 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:
! 26: set testdir [file dirname $argv0]
! 27: source $testdir/tester.tcl
! 28: source $testdir/lock_common.tcl
! 29: source $testdir/malloc_common.tcl
! 30: db 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: #
! 36: set binaries [list]
! 37: set pattern "[file tail [info nameofexec]]?*"
! 38: if {$tcl_platform(platform)=="windows"} {
! 39: set pattern [string map {\.exe {}} $pattern]
! 40: }
! 41: foreach file [glob -nocomplain $pattern] {
! 42: if {[file executable $file] && [file isfile $file]} {lappend binaries $file}
! 43: }
! 44: if {[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: }
! 50: proc get_version {binary} {
! 51: set chan [launch_testfixture $binary]
! 52: set v [testfixture $chan { sqlite3 -version }]
! 53: close $chan
! 54: set v
! 55: }
! 56: foreach bin $binaries {
! 57: puts -nonewline "Testing against $bin - "
! 58: flush stdout
! 59: puts "version [get_version $bin]"
! 60: }
! 61:
! 62: proc do_backcompat_test {rv bin1 bin2 script} {
! 63:
! 64: forcedelete 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:
! 93: array set ::incompatible [list]
! 94: proc 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:
! 122: proc 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: }
! 141: proc 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: }
! 147: proc 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: }
! 152: proc write_file_system {data} {
! 153: foreach f {test.db test.db-journal test.db-wal} d $data {
! 154: if {[string length $d] == 0} {
! 155: forcedelete $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: #
! 169: do_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: }
! 228: foreach k [lsort [array names ::incompatible]] {
! 229: puts "ERROR: Detected journal incompatibility with version $k"
! 230: }
! 231: unset ::incompatible
! 232:
! 233:
! 234: #-------------------------------------------------------------------------
! 235: # Test that WAL and wal-index files may be shared between different
! 236: # SQLite versions.
! 237: #
! 238: do_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: #
! 278: set contents {
! 279: CREATE VIRTUAL TABLE t1 USING fts3(a, b);
! 280: }
! 281: foreach {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: }
! 294: do_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: #
! 376: set contents {
! 377: CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2);
! 378: }
! 379: foreach {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: } {
! 406: if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" }
! 407: append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);"
! 408: }
! 409: set 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: }
! 415: do_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:
! 445: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to backcompat.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test