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