wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 file delete -force 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 file delete -force $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