PageRenderTime 28ms CodeModel.GetById 24ms app.highlight 1ms RepoModel.GetById 1ms app.codeStats 0ms

/trunk/src/sqlite/test/backcompat.test

#
Unknown | 445 lines | 392 code | 53 blank | 0 comment | 0 complexity | 8f7e2ba9378087fed43ce3c95aefd090 MD5 | raw file
Possible License(s): BSD-3-Clause
  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
 30db 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#
 36set binaries [list]
 37set pattern "[file tail [info nameofexec]]?*"
 38if {$tcl_platform(platform)=="windows"} {
 39  set pattern [string map {\.exe {}} $pattern]
 40}
 41foreach file [glob -nocomplain $pattern] {
 42  if {[file executable $file] && [file isfile $file]} {lappend binaries $file}
 43}
 44if {[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}
 50proc get_version {binary} {
 51  set chan [launch_testfixture $binary]
 52  set v [testfixture $chan { sqlite3 -version }]
 53  close $chan
 54  set v
 55}
 56foreach bin $binaries {
 57  puts -nonewline "Testing against $bin - "
 58  flush stdout
 59  puts "version [get_version $bin]"
 60}
 61
 62proc 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
 93array set ::incompatible [list]
 94proc 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
122proc 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}
141proc 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}
147proc 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}
152proc 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#
169do_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}
228foreach k [lsort [array names ::incompatible]] {
229  puts "ERROR: Detected journal incompatibility with version $k"
230}
231unset ::incompatible
232
233
234#-------------------------------------------------------------------------
235# Test that WAL and wal-index files may be shared between different 
236# SQLite versions.
237#
238do_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#
278set contents {
279  CREATE VIRTUAL TABLE t1 USING fts3(a, b);
280}
281foreach {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}
294do_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#
376set contents {
377  CREATE VIRTUAL TABLE t1 USING rtree(id, x1, x2, y1, y2);
378}
379foreach {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} {
406if {$x1 >= $x2 || $y1 >= $y2} { error "$x1 $x2 $y1 $y2" }
407  append contents "INSERT INTO t1 VALUES($id, $x1, $x2, $y1, $y2);"
408}
409set 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}
415do_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
445finish_test