| blob | ea7e6a9eeda02b695aa16ab99162de28e1431196 |
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 #
26 set testdir [file dirname $argv0]
27 source $testdir/tester.tcl
28 source $testdir/lock_common.tcl
29 source $testdir/malloc_common.tcl
30 source $testdir/bc_common.tcl
31 db close
33 if {"" == [bc_find_binaries backcompat.test]} {
34 finish_test
35 return
36 }
38 proc do_backcompat_test {rv bin1 bin2 script} {
40 forcedelete test.db
42 if {$bin1 != ""} { set ::bc_chan1 [launch_testfixture $bin1] }
43 set ::bc_chan2 [launch_testfixture $bin2]
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 }
55 proc sql1 sql { code1 [list db eval $sql] }
56 proc sql2 sql { code2 [list db eval $sql] }
58 code1 { sqlite3 db test.db }
59 code2 { sqlite3 db test.db }
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 }
71 uplevel $script
73 catch { code1 { db close } }
74 catch { code2 { db close } }
75 catch { close $::bc_chan2 }
76 catch { close $::bc_chan1 }
79 }
81 array set ::incompatible [list]
82 proc do_allbackcompat_test {script} {
84 foreach bin $::BC(binaries) {
85 set nErr [set_test_counter errors]
86 foreach dir {0 1} {
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."
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 }
99 do_backcompat_test $dir {} $bin $script
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 }
110 proc read_file {zFile} {
111 set zData {}
112 if {[file exists $zFile]} {
113 set fd [open $zFile]
114 fconfigure $fd -translation binary -encoding binary
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 }
125 close $fd
126 }
127 return $zData
128 }
129 proc 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 }
135 proc 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 }
140 proc 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 }
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 #
157 do_allbackcompat_test {
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}
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
193 do_test backcompat-1.2.3 { sql1 {
194 BEGIN;
195 UPDATE t1 SET a = randomblob(500);
196 } } {}
197 set data [read_file_system]
199 do_test backcompat-1.2.4 { sql1 { COMMIT } } {}
201 set same [expr {[sql2 {SELECT md5sum(a), md5sum(b) FROM t1}] == $cksum2}]
202 do_test backcompat-1.2.5 [list set {} $same] 0
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 }
210 set same [expr {[sql2 {SELECT md5sum(a), md5sum(b) FROM t1}] == $cksum2}]
211 do_test backcompat-1.2.6 [list set {} $same] 1
213 do_test backcompat-1.2.7 { sql1 { PRAGMA integrity_check } } {ok}
214 do_test backcompat-1.2.8 { sql2 { PRAGMA integrity_check } } {ok}
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 }
252 foreach k [lsort [array names ::incompatible]] {
253 puts "ERROR: Detected journal incompatibility with version $k"
254 }
255 unset ::incompatible
258 #-------------------------------------------------------------------------
259 # Test that WAL and wal-index files may be shared between different
260 # SQLite versions.
261 #
262 do_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 } {
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}
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}
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 }
300 #-------------------------------------------------------------------------
301 # Test that FTS3 tables may be read/written by different versions of
302 # SQLite.
303 #
304 ifcapable 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 } {
326 do_test backcompat-3.1 { sql1 $contents } {}
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 }
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 } } {}
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 }
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 }
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'"
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 }
379 do_test backcompat-3.6 {
380 sql1 "SELECT optimize(t1) FROM t1 LIMIT 1"
381 } {{Index optimized}}
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'"
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 }
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}}
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}
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 }
442 #-------------------------------------------------------------------------
443 # Test that Rtree tables may be read/written by different versions of
444 # SQLite.
445 #
446 ifcapable 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 } {
491 do_test backcompat-4.1 { sql1 $contents } {}
493 foreach {n q} $::queries {
494 do_test backcompat-4.2.$n [list sql1 $q] [sql2 $q]
495 }
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 } } {}
501 foreach {n q} $::queries {
502 do_test backcompat-4.4.$n [list sql1 $q] [sql2 $q]
503 }
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 } } {}
509 foreach {n q} $::queries {
510 do_test backcompat-4.6.$n [list sql1 $q] [sql2 $q]
511 }
513 }
514 }
515 }
517 finish_test