xref: /sqlite-3.40.0/test/walro2.test (revision cda185d3) 
1# 2011 May 09
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#
12# This file contains tests for using WAL databases in read-only mode.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17source $testdir/lock_common.tcl
18source $testdir/wal_common.tcl
19set ::testprefix walro2
20
21# And only if the build is WAL-capable.
22#
23ifcapable !wal {
24  finish_test
25  return
26}
27
28proc copy_to_test2 {bZeroShm} {
29  forcecopy test.db test.db2
30  forcecopy test.db-wal test.db2-wal
31  if {$bZeroShm} {
32    forcedelete test.db2-shm
33    set fd [open test.db2-shm w]
34    seek $fd [expr [file size test.db-shm]-1]
35    puts -nonewline $fd "\0"
36    close $fd
37  } else {
38    forcecopy test.db-shm test.db2-shm
39  }
40}
41
42foreach bZeroShm {0 1} {
43set TN [expr $bZeroShm+1]
44do_multiclient_test tn {
45
46  # Close all connections and delete the database.
47  #
48  code1 { db close  }
49  code2 { db2 close }
50  code3 { db3 close }
51  forcedelete test.db
52
53  # Do not run tests with the connections in the same process.
54  #
55  if {$tn==2} continue
56
57  foreach c {code1 code2 code3} {
58    $c {
59      sqlite3_shutdown
60      sqlite3_config_uri 1
61    }
62  }
63
64  do_test $TN.1.1 {
65    code2 { sqlite3 db2 test.db }
66    sql2 {
67      CREATE TABLE t1(x, y);
68      PRAGMA journal_mode = WAL;
69      INSERT INTO t1 VALUES('a', 'b');
70      INSERT INTO t1 VALUES('c', 'd');
71    }
72    file exists test.db-shm
73  } {1}
74
75  do_test $TN.1.2.1 {
76    copy_to_test2 $bZeroShm
77    code1 {
78      sqlite3 db file:test.db2?readonly_shm=1
79    }
80
81    sql1 { SELECT * FROM t1 }
82  } {a b c d}
83  do_test $TN.1.2.2 {
84    sql1 { SELECT * FROM t1 }
85  } {a b c d}
86
87  do_test $TN.1.3.1 {
88    code3 { sqlite3 db3 test.db2 }
89    sql3 { SELECT * FROM t1 }
90  } {a b c d}
91
92  do_test $TN.1.3.2 {
93    sql1 { SELECT * FROM t1 }
94  } {a b c d}
95
96  code1 { db close  }
97  code2 { db2 close }
98  code3 { db3 close }
99
100  do_test $TN.2.1 {
101    code2 { sqlite3 db2 test.db }
102    sql2 {
103      INSERT INTO t1 VALUES('e', 'f');
104      INSERT INTO t1 VALUES('g', 'h');
105    }
106    file exists test.db-shm
107  } {1}
108
109  do_test $TN.2.2 {
110    copy_to_test2 $bZeroShm
111    code1 {
112      sqlite3 db file:test.db2?readonly_shm=1
113    }
114    sql1 {
115      BEGIN;
116      SELECT * FROM t1;
117    }
118  } {a b c d e f g h}
119
120  do_test $TN.2.3.1 {
121    code3 { sqlite3 db3 test.db2 }
122    sql3 { SELECT * FROM t1 }
123  } {a b c d e f g h}
124  do_test $TN.2.3.2 {
125    sql3 { INSERT INTO t1 VALUES('i', 'j') }
126    code3 { db3 close }
127    sql1 { COMMIT }
128  } {}
129  do_test $TN.2.3.3 {
130    sql1 { SELECT * FROM t1 }
131  } {a b c d e f g h i j}
132
133
134  #-----------------------------------------------------------------------
135  # 3.1.*: That a readonly_shm connection can read a database file if both
136  #        the *-wal and *-shm files are zero bytes in size.
137  #
138  # 3.2.*: That it flushes the cache if, between transactions on a db with a
139  #        zero byte *-wal file, some other connection modifies the db, then
140  #        does "PRAGMA wal_checkpoint=truncate" to truncate the wal file
141  #        back to zero bytes in size.
142  #
143  # 3.3.*: That, if between transactions some other process wraps the wal
144  #        file, the readonly_shm client reruns recovery.
145  #
146  catch { code1 { db close } }
147  catch { code2 { db2 close } }
148  catch { code3 { db3 close } }
149  do_test $TN.3.1.0 {
150    list [file exists test.db-wal] [file exists test.db-shm]
151  } {0 0}
152  do_test $TN.3.1.1 {
153    close [open test.db-wal w]
154    close [open test.db-shm w]
155    code1 {
156      sqlite3 db file:test.db?readonly_shm=1
157    }
158    sql1 { SELECT * FROM t1 }
159  } {a b c d e f g h}
160
161  do_test $TN.3.2.0 {
162    list [file size test.db-wal] [file size test.db-shm]
163  } {0 0}
164  do_test $TN.3.2.1 {
165    code2 { sqlite3 db2 test.db }
166    sql2 { INSERT INTO t1 VALUES(1, 2) ; PRAGMA wal_checkpoint=truncate }
167    code2 { db2 close }
168    sql1 { SELECT * FROM t1 }
169  } {a b c d e f g h 1 2}
170  do_test $TN.3.2.2 {
171    list [file size test.db-wal] [file size test.db-shm]
172  } {0 32768}
173
174  do_test $TN.3.3.0 {
175    code2 { sqlite3 db2 test.db }
176    sql2 {
177      INSERT INTO t1 VALUES(3, 4);
178      INSERT INTO t1 VALUES(5, 6);
179      INSERT INTO t1 VALUES(7, 8);
180      INSERT INTO t1 VALUES(9, 10);
181    }
182    code2 { db2 close }
183    code1 { db close }
184    list [file size test.db-wal] [file size test.db-shm]
185  } [list [wal_file_size 4 1024] 32768]
186  do_test $TN.3.3.1 {
187    code1 { sqlite3 db file:test.db?readonly_shm=1 }
188    sql1 { SELECT * FROM t1 }
189  } {a b c d e f g h 1 2 3 4 5 6 7 8 9 10}
190  do_test $TN.3.3.2 {
191    code2 { sqlite3 db2 test.db }
192    sql2 {
193      PRAGMA wal_checkpoint;
194      DELETE FROM t1;
195      INSERT INTO t1 VALUES('i', 'ii');
196    }
197    code2 { db2 close }
198    list [file size test.db-wal] [file size test.db-shm]
199  } [list [wal_file_size 4 1024] 32768]
200  do_test $TN.3.3.3 {
201    sql1 { SELECT * FROM t1 }
202  } {i ii}
203
204  #-----------------------------------------------------------------------
205  #
206  #
207  catch { code1 { db close } }
208  catch { code2 { db2 close } }
209  catch { code3 { db3 close } }
210
211  do_test $TN.4.0 {
212    code1 { forcedelete test.db }
213    code1 { sqlite3 db test.db }
214    sql1 {
215      PRAGMA journal_mode = wal;
216      CREATE TABLE t1(x);
217      INSERT INTO t1 VALUES('hello');
218      INSERT INTO t1 VALUES('world');
219    }
220
221    copy_to_test2 $bZeroShm
222
223    code1 { db close }
224  } {}
225
226  do_test $TN.4.1.1 {
227    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
228    sql2 { SELECT * FROM t1 }
229  } {hello world}
230
231  do_test $TN.4.1.2 {
232    code3 { sqlite3 db3 test.db2 }
233    sql3 {
234      INSERT INTO t1 VALUES('!');
235      PRAGMA wal_checkpoint = truncate;
236    }
237    code3 { db3 close }
238  } {}
239  do_test $TN.4.1.3 {
240    sql2 { SELECT * FROM t1 }
241  } {hello world !}
242
243  catch { code1 { db close } }
244  catch { code2 { db2 close } }
245  catch { code3 { db3 close } }
246
247  do_test $TN.4.2.1 {
248    code1 { sqlite3 db test.db }
249    sql1 {
250      INSERT INTO t1 VALUES('!');
251      INSERT INTO t1 VALUES('!');
252
253      PRAGMA cache_size = 10;
254      CREATE TABLE t2(x);
255
256      BEGIN;
257        WITH s(i) AS (
258          SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<500
259          )
260        INSERT INTO t2 SELECT randomblob(500) FROM s;
261        SELECT count(*) FROM t2;
262    }
263  } {500}
264  do_test $TN.4.2.2 {
265    file size test.db-wal
266  } {461152}
267  do_test $TN.4.2.4 {
268    file_control_persist_wal db 1; db close
269
270    copy_to_test2 $bZeroShm
271    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
272    sql2 {
273      SELECT * FROM t1;
274      SELECT count(*) FROM t2;
275    }
276  } {hello world ! ! 0}
277
278  #-----------------------------------------------------------------------
279  #
280  #
281  catch { code1 { db close } }
282  catch { code2 { db2 close } }
283  catch { code3 { db3 close } }
284
285  do_test $TN.5.0 {
286    code1 { forcedelete test.db }
287    code1 { sqlite3 db test.db }
288    sql1 {
289      PRAGMA journal_mode = wal;
290      CREATE TABLE t1(x);
291      INSERT INTO t1 VALUES('hello');
292      INSERT INTO t1 VALUES('world');
293      INSERT INTO t1 VALUES('!');
294      INSERT INTO t1 VALUES('world');
295      INSERT INTO t1 VALUES('hello');
296    }
297
298    copy_to_test2 $bZeroShm
299
300    code1 { db close }
301  } {}
302
303  do_test $TN.5.1 {
304    code2 { sqlite3 db2 file:test.db2?readonly_shm=1 }
305    sql2 {
306      SELECT * FROM t1;
307    }
308  } {hello world ! world hello}
309
310  do_test $TN.5.2 {
311    code1 {
312      proc handle_read {op args} {
313        if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
314          set ::res2 [sql2 { SELECT * FROM t1 }]
315        }
316        puts "$msg xRead $args"
317        return "SQLITE_OK"
318      }
319      testvfs tvfs -fullshm 1
320
321      sqlite3 db file:test.db2?vfs=tvfs
322      db eval { SELECT * FROM sqlite_master }
323
324      tvfs filter xRead
325      tvfs script handle_read
326    }
327    sql1 {
328      PRAGMA wal_checkpoint = truncate;
329    }
330    code1 { set ::res2 }
331  } {hello world ! world hello}
332
333  do_test $TN.5.3 {
334    code1 { db close }
335    code1 { tvfs delete }
336  } {}
337
338  #-----------------------------------------------------------------------
339  #
340  #
341  catch { code1 { db close } }
342  catch { code2 { db2 close } }
343  catch { code3 { db3 close } }
344
345  do_test $TN.6.1 {
346    code1 { forcedelete test.db }
347    code1 { sqlite3 db test.db }
348    sql1 {
349      PRAGMA journal_mode = wal;
350      CREATE TABLE t1(x);
351      INSERT INTO t1 VALUES('hello');
352      INSERT INTO t1 VALUES('world');
353      INSERT INTO t1 VALUES('!');
354      INSERT INTO t1 VALUES('world');
355      INSERT INTO t1 VALUES('hello');
356    }
357
358    copy_to_test2 $bZeroShm
359
360    code1 { db close }
361  } {}
362
363  do_test $TN.6.2 {
364    code1 {
365      set ::nRem 5
366      proc handle_read {op args} {
367        if {$op=="xRead" && [file tail [lindex $args 0]]=="test.db2-wal"} {
368          incr ::nRem -1
369          if {$::nRem==0} {
370            code2 { sqlite3 db2 test.db2 }
371            sql2  { PRAGMA wal_checkpoint = truncate }
372          }
373        }
374        return "SQLITE_OK"
375      }
376      testvfs tvfs -fullshm 1
377
378      tvfs filter xRead
379      tvfs script handle_read
380
381      sqlite3 db file:test.db2?readonly_shm=1&vfs=tvfs
382      db eval { SELECT * FROM t1 }
383    }
384  } {hello world ! world hello}
385
386  do_test $TN.6.3 {
387    code1 { db close }
388    code1 { tvfs delete }
389  } {}
390}
391} ;# foreach bZeroShm
392
393finish_test
394