wasCSharpSQLite – Blame information for rev 1
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 June 15 |
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 | |||
13 | set testdir [file dirname $argv0] |
||
14 | source $testdir/tester.tcl |
||
15 | source $testdir/lock_common.tcl |
||
16 | source $testdir/malloc_common.tcl |
||
17 | source $testdir/wal_common.tcl |
||
18 | |||
19 | # Do not use a codec for tests in this file, as the database file is |
||
20 | # manipulated directly using tcl scripts (using the [hexio_write] command). |
||
21 | # |
||
22 | do_not_use_codec |
||
23 | |||
24 | # |
||
25 | # pager1-1.*: Test inter-process locking (clients in multiple processes). |
||
26 | # |
||
27 | # pager1-2.*: Test intra-process locking (multiple clients in this process). |
||
28 | # |
||
29 | # pager1-3.*: Savepoint related tests. |
||
30 | # |
||
31 | # pager1-4.*: Hot-journal related tests. |
||
32 | # |
||
33 | # pager1-5.*: Cases related to multi-file commits. |
||
34 | # |
||
35 | # pager1-6.*: Cases related to "PRAGMA max_page_count" |
||
36 | # |
||
37 | # pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE" |
||
38 | # |
||
39 | # pager1-8.*: Cases using temporary and in-memory databases. |
||
40 | # |
||
41 | # pager1-9.*: Tests related to the backup API. |
||
42 | # |
||
43 | # pager1-10.*: Test that the assumed file-system sector-size is limited to |
||
44 | # 64KB. |
||
45 | # |
||
46 | # pager1-12.*: Tests involving "PRAGMA page_size" |
||
47 | # |
||
48 | # pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST" |
||
49 | # |
||
50 | # pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF" |
||
51 | # |
||
52 | # pager1-15.*: Varying sqlite3_vfs.szOsFile |
||
53 | # |
||
54 | # pager1-16.*: Varying sqlite3_vfs.mxPathname |
||
55 | # |
||
56 | # pager1-17.*: Tests related to "PRAGMA omit_readlock" |
||
57 | # |
||
58 | # pager1-18.*: Test that the pager layer responds correctly if the b-tree |
||
59 | # requests an invalid page number (due to db corruption). |
||
60 | # |
||
61 | |||
62 | proc recursive_select {id table {script {}}} { |
||
63 | set cnt 0 |
||
64 | db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" { |
||
65 | recursive_select $rowid $table $script |
||
66 | incr cnt |
||
67 | } |
||
68 | if {$cnt==0} { eval $script } |
||
69 | } |
||
70 | |||
71 | set a_string_counter 1 |
||
72 | proc a_string {n} { |
||
73 | global a_string_counter |
||
74 | incr a_string_counter |
||
75 | string range [string repeat "${a_string_counter}." $n] 1 $n |
||
76 | } |
||
77 | db func a_string a_string |
||
78 | |||
79 | do_multiclient_test tn { |
||
80 | |||
81 | # Create and populate a database table using connection [db]. Check |
||
82 | # that connections [db2] and [db3] can see the schema and content. |
||
83 | # |
||
84 | do_test pager1-$tn.1 { |
||
85 | sql1 { |
||
86 | CREATE TABLE t1(a PRIMARY KEY, b); |
||
87 | CREATE INDEX i1 ON t1(b); |
||
88 | INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); |
||
89 | } |
||
90 | } {} |
||
91 | do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two} |
||
92 | do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two} |
||
93 | |||
94 | # Open a transaction and add a row using [db]. This puts [db] in |
||
95 | # RESERVED state. Check that connections [db2] and [db3] can still |
||
96 | # read the database content as it was before the transaction was |
||
97 | # opened. [db] should see the inserted row. |
||
98 | # |
||
99 | do_test pager1-$tn.4 { |
||
100 | sql1 { |
||
101 | BEGIN; |
||
102 | INSERT INTO t1 VALUES(3, 'three'); |
||
103 | } |
||
104 | } {} |
||
105 | do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two} |
||
106 | do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
107 | |||
108 | # [db] still has an open write transaction. Check that this prevents |
||
109 | # other connections (specifically [db2]) from writing to the database. |
||
110 | # |
||
111 | # Even if [db2] opens a transaction first, it may not write to the |
||
112 | # database. After the attempt to write the db within a transaction, |
||
113 | # [db2] is left with an open transaction, but not a read-lock on |
||
114 | # the main database. So it does not prevent [db] from committing. |
||
115 | # |
||
116 | do_test pager1-$tn.8 { |
||
117 | csql2 { UPDATE t1 SET a = a + 10 } |
||
118 | } {1 {database is locked}} |
||
119 | do_test pager1-$tn.9 { |
||
120 | csql2 { |
||
121 | BEGIN; |
||
122 | UPDATE t1 SET a = a + 10; |
||
123 | } |
||
124 | } {1 {database is locked}} |
||
125 | |||
126 | # Have [db] commit its transactions. Check the other connections can |
||
127 | # now see the new database content. |
||
128 | # |
||
129 | do_test pager1-$tn.10 { sql1 { COMMIT } } {} |
||
130 | do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
131 | do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
132 | do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
133 | |||
134 | # Check that, as noted above, [db2] really did keep an open transaction |
||
135 | # after the attempt to write the database failed. |
||
136 | # |
||
137 | do_test pager1-$tn.14 { |
||
138 | csql2 { BEGIN } |
||
139 | } {1 {cannot start a transaction within a transaction}} |
||
140 | do_test pager1-$tn.15 { sql2 { ROLLBACK } } {} |
||
141 | |||
142 | # Have [db2] open a transaction and take a read-lock on the database. |
||
143 | # Check that this prevents [db] from writing to the database (outside |
||
144 | # of any transaction). After this fails, check that [db3] can read |
||
145 | # the db (showing that [db] did not take a PENDING lock etc.) |
||
146 | # |
||
147 | do_test pager1-$tn.15 { |
||
148 | sql2 { BEGIN; SELECT * FROM t1; } |
||
149 | } {1 one 2 two 3 three} |
||
150 | do_test pager1-$tn.16 { |
||
151 | csql1 { UPDATE t1 SET a = a + 10 } |
||
152 | } {1 {database is locked}} |
||
153 | do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
154 | |||
155 | # This time, have [db] open a transaction before writing the database. |
||
156 | # This works - [db] gets a RESERVED lock which does not conflict with |
||
157 | # the SHARED lock [db2] is holding. |
||
158 | # |
||
159 | do_test pager1-$tn.18 { |
||
160 | sql1 { |
||
161 | BEGIN; |
||
162 | UPDATE t1 SET a = a + 10; |
||
163 | } |
||
164 | } {} |
||
165 | do_test pager1-$tn-19 { |
||
166 | sql1 { PRAGMA lock_status } |
||
167 | } {main reserved temp closed} |
||
168 | do_test pager1-$tn-20 { |
||
169 | sql2 { PRAGMA lock_status } |
||
170 | } {main shared temp closed} |
||
171 | |||
172 | # Check that all connections can still read the database. Only [db] sees |
||
173 | # the updated content (as the transaction has not been committed yet). |
||
174 | # |
||
175 | do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} |
||
176 | do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
177 | do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
178 | |||
179 | # Because [db2] still has the SHARED lock, [db] is unable to commit the |
||
180 | # transaction. If it tries, an error is returned and the connection |
||
181 | # upgrades to a PENDING lock. |
||
182 | # |
||
183 | # Once this happens, [db] can read the database and see the new content, |
||
184 | # [db2] (still holding SHARED) can still read the old content, but [db3] |
||
185 | # (not holding any lock) is prevented by [db]'s PENDING from reading |
||
186 | # the database. |
||
187 | # |
||
188 | do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}} |
||
189 | do_test pager1-$tn-25 { |
||
190 | sql1 { PRAGMA lock_status } |
||
191 | } {main pending temp closed} |
||
192 | do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three} |
||
193 | do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three} |
||
194 | do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} |
||
195 | |||
196 | # Have [db2] commit its read transaction, releasing the SHARED lock it |
||
197 | # is holding. Now, neither [db2] nor [db3] may read the database (as [db] |
||
198 | # is still holding a PENDING). |
||
199 | # |
||
200 | do_test pager1-$tn.29 { sql2 { COMMIT } } {} |
||
201 | do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}} |
||
202 | do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}} |
||
203 | |||
204 | # [db] is now able to commit the transaction. Once the transaction is |
||
205 | # committed, all three connections can read the new content. |
||
206 | # |
||
207 | do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {} |
||
208 | do_test pager1-$tn.26 { sql1 { COMMIT } } {} |
||
209 | do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three} |
||
210 | do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three} |
||
211 | do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three} |
||
212 | |||
213 | # Install a busy-handler for connection [db]. |
||
214 | # |
||
215 | set ::nbusy [list] |
||
216 | proc busy {n} { |
||
217 | lappend ::nbusy $n |
||
218 | if {$n>5} { sql2 COMMIT } |
||
219 | return 0 |
||
220 | } |
||
221 | db busy busy |
||
222 | |||
223 | do_test pager1-$tn.29 { |
||
224 | sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') } |
||
225 | } {} |
||
226 | do_test pager1-$tn.30 { |
||
227 | sql2 { BEGIN ; SELECT * FROM t1 } |
||
228 | } {21 one 22 two 23 three} |
||
229 | do_test pager1-$tn.31 { sql1 COMMIT } {} |
||
230 | do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6} |
||
231 | } |
||
232 | |||
233 | #------------------------------------------------------------------------- |
||
234 | # Savepoint related test cases. |
||
235 | # |
||
236 | # pager1-3.1.2.*: Force a savepoint rollback to cause the database file |
||
237 | # to grow. |
||
238 | # |
||
239 | # pager1-3.1.3.*: Use a journal created in synchronous=off mode as part |
||
240 | # of a savepoint rollback. |
||
241 | # |
||
242 | do_test pager1-3.1.1 { |
||
243 | faultsim_delete_and_reopen |
||
244 | execsql { |
||
245 | CREATE TABLE t1(a PRIMARY KEY, b); |
||
246 | CREATE TABLE counter( |
||
247 | i CHECK (i<5), |
||
248 | u CHECK (u<10) |
||
249 | ); |
||
250 | INSERT INTO counter VALUES(0, 0); |
||
251 | CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
||
252 | UPDATE counter SET i = i+1; |
||
253 | END; |
||
254 | CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN |
||
255 | UPDATE counter SET u = u+1; |
||
256 | END; |
||
257 | } |
||
258 | execsql { SELECT * FROM counter } |
||
259 | } {0 0} |
||
260 | |||
261 | do_execsql_test pager1-3.1.2 { |
||
262 | PRAGMA cache_size = 10; |
||
263 | BEGIN; |
||
264 | INSERT INTO t1 VALUES(1, randomblob(1500)); |
||
265 | INSERT INTO t1 VALUES(2, randomblob(1500)); |
||
266 | INSERT INTO t1 VALUES(3, randomblob(1500)); |
||
267 | SELECT * FROM counter; |
||
268 | } {3 0} |
||
269 | do_catchsql_test pager1-3.1.3 { |
||
270 | INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1 |
||
271 | } {1 {constraint failed}} |
||
272 | do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0} |
||
273 | do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3} |
||
274 | do_execsql_test pager1-3.6 { COMMIT } {} |
||
275 | |||
276 | foreach {tn sql tcl} { |
||
277 | 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } { |
||
278 | testvfs tv -default 1 |
||
279 | tv devchar safe_append |
||
280 | } |
||
281 | 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } { |
||
282 | testvfs tv -default 1 |
||
283 | tv devchar sequential |
||
284 | } |
||
285 | 9 { PRAGMA synchronous = FULL } { } |
||
286 | 10 { PRAGMA synchronous = NORMAL } { } |
||
287 | 11 { PRAGMA synchronous = OFF } { } |
||
288 | 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { } |
||
289 | 13 { PRAGMA synchronous = FULL } { |
||
290 | testvfs tv -default 1 |
||
291 | tv devchar sequential |
||
292 | } |
||
293 | 14 { PRAGMA locking_mode = EXCLUSIVE } { |
||
294 | } |
||
295 | } { |
||
296 | do_test pager1-3.$tn.1 { |
||
297 | eval $tcl |
||
298 | faultsim_delete_and_reopen |
||
299 | db func a_string a_string |
||
300 | execsql $sql |
||
301 | execsql { |
||
302 | PRAGMA auto_vacuum = 2; |
||
303 | PRAGMA cache_size = 10; |
||
304 | CREATE TABLE z(x INTEGER PRIMARY KEY, y); |
||
305 | BEGIN; |
||
306 | INSERT INTO z VALUES(NULL, a_string(800)); |
||
307 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2 |
||
308 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4 |
||
309 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8 |
||
310 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16 |
||
311 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32 |
||
312 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64 |
||
313 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128 |
||
314 | INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256 |
||
315 | COMMIT; |
||
316 | } |
||
317 | execsql { PRAGMA auto_vacuum } |
||
318 | } {2} |
||
319 | do_execsql_test pager1-3.$tn.2 { |
||
320 | BEGIN; |
||
321 | INSERT INTO z VALUES(NULL, a_string(800)); |
||
322 | INSERT INTO z VALUES(NULL, a_string(800)); |
||
323 | SAVEPOINT one; |
||
324 | UPDATE z SET y = NULL WHERE x>256; |
||
325 | PRAGMA incremental_vacuum; |
||
326 | SELECT count(*) FROM z WHERE x < 100; |
||
327 | ROLLBACK TO one; |
||
328 | COMMIT; |
||
329 | } {99} |
||
330 | |||
331 | do_execsql_test pager1-3.$tn.3 { |
||
332 | BEGIN; |
||
333 | SAVEPOINT one; |
||
334 | UPDATE z SET y = y||x; |
||
335 | ROLLBACK TO one; |
||
336 | COMMIT; |
||
337 | SELECT count(*) FROM z; |
||
338 | } {258} |
||
339 | |||
340 | do_execsql_test pager1-3.$tn.4 { |
||
341 | SAVEPOINT one; |
||
342 | UPDATE z SET y = y||x; |
||
343 | ROLLBACK TO one; |
||
344 | } {} |
||
345 | do_execsql_test pager1-3.$tn.5 { |
||
346 | SELECT count(*) FROM z; |
||
347 | RELEASE one; |
||
348 | PRAGMA integrity_check; |
||
349 | } {258 ok} |
||
350 | |||
351 | do_execsql_test pager1-3.$tn.6 { |
||
352 | SAVEPOINT one; |
||
353 | RELEASE one; |
||
354 | } {} |
||
355 | |||
356 | db close |
||
357 | catch { tv delete } |
||
358 | } |
||
359 | |||
360 | #------------------------------------------------------------------------- |
||
361 | # Hot journal rollback related test cases. |
||
362 | # |
||
363 | # pager1.4.1.*: Test that the pager module deletes very small invalid |
||
364 | # journal files. |
||
365 | # |
||
366 | # pager1.4.2.*: Test that if the master journal pointer at the end of a |
||
367 | # hot-journal file appears to be corrupt (checksum does not |
||
368 | # compute) the associated journal is rolled back (and no |
||
369 | # xAccess() call to check for the presence of any master |
||
370 | # journal file is made). |
||
371 | # |
||
372 | # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the |
||
373 | # page-size or sector-size in the journal header appear to |
||
374 | # be invalid (too large, too small or not a power of 2). |
||
375 | # |
||
376 | # pager1.4.4.*: Test hot-journal rollback of journal file with a master |
||
377 | # journal pointer generated in various "PRAGMA synchronous" |
||
378 | # modes. |
||
379 | # |
||
380 | # pager1.4.5.*: Test that hot-journal rollback stops if it encounters a |
||
381 | # journal-record for which the checksum fails. |
||
382 | # |
||
383 | # pager1.4.6.*: Test that when rolling back a hot-journal that contains a |
||
384 | # master journal pointer, the master journal file is deleted |
||
385 | # after all the hot-journals that refer to it are deleted. |
||
386 | # |
||
387 | # pager1.4.7.*: Test that if a hot-journal file exists but a client can |
||
388 | # open it for reading only, the database cannot be accessed and |
||
389 | # SQLITE_CANTOPEN is returned. |
||
390 | # |
||
391 | do_test pager1.4.1.1 { |
||
392 | faultsim_delete_and_reopen |
||
393 | execsql { |
||
394 | CREATE TABLE x(y, z); |
||
395 | INSERT INTO x VALUES(1, 2); |
||
396 | } |
||
397 | set fd [open test.db-journal w] |
||
398 | puts -nonewline $fd "helloworld" |
||
399 | close $fd |
||
400 | file exists test.db-journal |
||
401 | } {1} |
||
402 | do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2} |
||
403 | do_test pager1.4.1.3 { file exists test.db-journal } {0} |
||
404 | |||
405 | # Set up a [testvfs] to snapshot the file-system just before SQLite |
||
406 | # deletes the master-journal to commit a multi-file transaction. |
||
407 | # |
||
408 | # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets |
||
409 | # up the file system to contain two databases, two hot-journal files and |
||
410 | # a master-journal. |
||
411 | # |
||
412 | do_test pager1.4.2.1 { |
||
413 | testvfs tstvfs -default 1 |
||
414 | tstvfs filter xDelete |
||
415 | tstvfs script xDeleteCallback |
||
416 | proc xDeleteCallback {method file args} { |
||
417 | set file [file tail $file] |
||
418 | if { [string match *mj* $file] } { faultsim_save } |
||
419 | } |
||
420 | faultsim_delete_and_reopen |
||
421 | db func a_string a_string |
||
422 | execsql { |
||
423 | ATTACH 'test.db2' AS aux; |
||
424 | PRAGMA journal_mode = DELETE; |
||
425 | PRAGMA main.cache_size = 10; |
||
426 | PRAGMA aux.cache_size = 10; |
||
427 | CREATE TABLE t1(a UNIQUE, b UNIQUE); |
||
428 | CREATE TABLE aux.t2(a UNIQUE, b UNIQUE); |
||
429 | INSERT INTO t1 VALUES(a_string(200), a_string(300)); |
||
430 | INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; |
||
431 | INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1; |
||
432 | INSERT INTO t2 SELECT * FROM t1; |
||
433 | BEGIN; |
||
434 | INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1; |
||
435 | INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1; |
||
436 | INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1; |
||
437 | INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1; |
||
438 | REPLACE INTO t2 SELECT * FROM t1; |
||
439 | COMMIT; |
||
440 | } |
||
441 | db close |
||
442 | tstvfs delete |
||
443 | } {} |
||
444 | |||
445 | if {$::tcl_platform(platform)!="windows"} { |
||
446 | do_test pager1.4.2.2 { |
||
447 | faultsim_restore_and_reopen |
||
448 | execsql { |
||
449 | SELECT count(*) FROM t1; |
||
450 | PRAGMA integrity_check; |
||
451 | } |
||
452 | } {4 ok} |
||
453 | do_test pager1.4.2.3 { |
||
454 | faultsim_restore_and_reopen |
||
455 | foreach f [glob test.db-mj*] { file delete -force $f } |
||
456 | execsql { |
||
457 | SELECT count(*) FROM t1; |
||
458 | PRAGMA integrity_check; |
||
459 | } |
||
460 | } {64 ok} |
||
461 | do_test pager1.4.2.4 { |
||
462 | faultsim_restore_and_reopen |
||
463 | hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 |
||
464 | execsql { |
||
465 | SELECT count(*) FROM t1; |
||
466 | PRAGMA integrity_check; |
||
467 | } |
||
468 | } {4 ok} |
||
469 | do_test pager1.4.2.5 { |
||
470 | faultsim_restore_and_reopen |
||
471 | hexio_write test.db-journal [expr [file size test.db-journal]-20] 123456 |
||
472 | foreach f [glob test.db-mj*] { file delete -force $f } |
||
473 | execsql { |
||
474 | SELECT count(*) FROM t1; |
||
475 | PRAGMA integrity_check; |
||
476 | } |
||
477 | } {4 ok} |
||
478 | } |
||
479 | |||
480 | do_test pager1.4.3.1 { |
||
481 | testvfs tstvfs -default 1 |
||
482 | tstvfs filter xSync |
||
483 | tstvfs script xSyncCallback |
||
484 | proc xSyncCallback {method file args} { |
||
485 | set file [file tail $file] |
||
486 | if { 0==[string match *journal $file] } { faultsim_save } |
||
487 | } |
||
488 | faultsim_delete_and_reopen |
||
489 | execsql { |
||
490 | PRAGMA journal_mode = DELETE; |
||
491 | CREATE TABLE t1(a, b); |
||
492 | INSERT INTO t1 VALUES(1, 2); |
||
493 | INSERT INTO t1 VALUES(3, 4); |
||
494 | } |
||
495 | db close |
||
496 | tstvfs delete |
||
497 | } {} |
||
498 | |||
499 | foreach {tn ofst value result} { |
||
500 | 2 20 31 {1 2 3 4} |
||
501 | 3 20 32 {1 2 3 4} |
||
502 | 4 20 33 {1 2 3 4} |
||
503 | 5 20 65536 {1 2 3 4} |
||
504 | 6 20 131072 {1 2 3 4} |
||
505 | |||
506 | 7 24 511 {1 2 3 4} |
||
507 | 8 24 513 {1 2 3 4} |
||
508 | 9 24 131072 {1 2 3 4} |
||
509 | |||
510 | 10 32 65536 {1 2} |
||
511 | } { |
||
512 | do_test pager1.4.3.$tn { |
||
513 | faultsim_restore_and_reopen |
||
514 | hexio_write test.db-journal $ofst [format %.8x $value] |
||
515 | execsql { SELECT * FROM t1 } |
||
516 | } $result |
||
517 | } |
||
518 | db close |
||
519 | |||
520 | # Set up a VFS that snapshots the file-system just before a master journal |
||
521 | # file is deleted to commit a multi-file transaction. Specifically, the |
||
522 | # file-system is saved just before the xDelete() call to remove the |
||
523 | # master journal file from the file-system. |
||
524 | # |
||
525 | testvfs tv -default 1 |
||
526 | tv script copy_on_mj_delete |
||
527 | set ::mj_filename_length 0 |
||
528 | proc copy_on_mj_delete {method filename args} { |
||
529 | if {[string match *mj* [file tail $filename]]} { |
||
530 | set ::mj_filename_length [string length $filename] |
||
531 | faultsim_save |
||
532 | } |
||
533 | return SQLITE_OK |
||
534 | } |
||
535 | |||
536 | set pwd [pwd] |
||
537 | foreach {tn1 tcl} { |
||
538 | 1 { set prefix "test.db" } |
||
539 | 2 { |
||
540 | # This test depends on the underlying VFS being able to open paths |
||
541 | # 512 bytes in length. The idea is to create a hot-journal file that |
||
542 | # contains a master-journal pointer so large that it could contain |
||
543 | # a valid page record (if the file page-size is 512 bytes). So as to |
||
544 | # make sure SQLite doesn't get confused by this. |
||
545 | # |
||
546 | set nPadding [expr 511 - $::mj_filename_length] |
||
547 | if {$tcl_platform(platform)=="windows"} { |
||
548 | # TBD need to figure out how to do this correctly for Windows!!! |
||
549 | set nPadding [expr 255 - $::mj_filename_length] |
||
550 | } |
||
551 | |||
552 | # We cannot just create a really long database file name to open, as |
||
553 | # Linux limits a single component of a path to 255 bytes by default |
||
554 | # (and presumably other systems have limits too). So create a directory |
||
555 | # hierarchy to work in. |
||
556 | # |
||
557 | set dirname "d123456789012345678901234567890/" |
||
558 | set nDir [expr $nPadding / 32] |
||
559 | if { $nDir } { |
||
560 | set p [string repeat $dirname $nDir] |
||
561 | file mkdir $p |
||
562 | cd $p |
||
563 | } |
||
564 | |||
565 | set padding [string repeat x [expr $nPadding %32]] |
||
566 | set prefix "test.db${padding}" |
||
567 | } |
||
568 | } { |
||
569 | eval $tcl |
||
570 | foreach {tn2 sql} { |
||
571 | o { |
||
572 | PRAGMA main.synchronous=OFF; |
||
573 | PRAGMA aux.synchronous=OFF; |
||
574 | PRAGMA journal_mode = DELETE; |
||
575 | } |
||
576 | o512 { |
||
577 | PRAGMA main.synchronous=OFF; |
||
578 | PRAGMA aux.synchronous=OFF; |
||
579 | PRAGMA main.page_size = 512; |
||
580 | PRAGMA aux.page_size = 512; |
||
581 | PRAGMA journal_mode = DELETE; |
||
582 | } |
||
583 | n { |
||
584 | PRAGMA main.synchronous=NORMAL; |
||
585 | PRAGMA aux.synchronous=NORMAL; |
||
586 | PRAGMA journal_mode = DELETE; |
||
587 | } |
||
588 | f { |
||
589 | PRAGMA main.synchronous=FULL; |
||
590 | PRAGMA aux.synchronous=FULL; |
||
591 | PRAGMA journal_mode = DELETE; |
||
592 | } |
||
593 | } { |
||
594 | |||
595 | set tn "${tn1}.${tn2}" |
||
596 | |||
597 | # Set up a connection to have two databases, test.db (main) and |
||
598 | # test.db2 (aux). Then run a multi-file transaction on them. The |
||
599 | # VFS will snapshot the file-system just before the master-journal |
||
600 | # file is deleted to commit the transaction. |
||
601 | # |
||
602 | tv filter xDelete |
||
603 | do_test pager1-4.4.$tn.1 { |
||
604 | faultsim_delete_and_reopen $prefix |
||
605 | execsql " |
||
606 | ATTACH '${prefix}2' AS aux; |
||
607 | $sql |
||
608 | CREATE TABLE a(x); |
||
609 | CREATE TABLE aux.b(x); |
||
610 | INSERT INTO a VALUES('double-you'); |
||
611 | INSERT INTO a VALUES('why'); |
||
612 | INSERT INTO a VALUES('zed'); |
||
613 | INSERT INTO b VALUES('won'); |
||
614 | INSERT INTO b VALUES('too'); |
||
615 | INSERT INTO b VALUES('free'); |
||
616 | " |
||
617 | execsql { |
||
618 | BEGIN; |
||
619 | INSERT INTO a SELECT * FROM b WHERE rowid<=3; |
||
620 | INSERT INTO b SELECT * FROM a WHERE rowid<=3; |
||
621 | COMMIT; |
||
622 | } |
||
623 | } {} |
||
624 | tv filter {} |
||
625 | |||
626 | # Check that the transaction was committed successfully. |
||
627 | # |
||
628 | do_execsql_test pager1-4.4.$tn.2 { |
||
629 | SELECT * FROM a |
||
630 | } {double-you why zed won too free} |
||
631 | do_execsql_test pager1-4.4.$tn.3 { |
||
632 | SELECT * FROM b |
||
633 | } {won too free double-you why zed} |
||
634 | |||
635 | # Restore the file-system and reopen the databases. Check that it now |
||
636 | # appears that the transaction was not committed (because the file-system |
||
637 | # was restored to the state where it had not been). |
||
638 | # |
||
639 | do_test pager1-4.4.$tn.4 { |
||
640 | faultsim_restore_and_reopen $prefix |
||
641 | execsql "ATTACH '${prefix}2' AS aux" |
||
642 | } {} |
||
643 | do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed} |
||
644 | do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free} |
||
645 | |||
646 | # Restore the file-system again. This time, before reopening the databases, |
||
647 | # delete the master-journal file from the file-system. It now appears that |
||
648 | # the transaction was committed (no master-journal file == no rollback). |
||
649 | # |
||
650 | do_test pager1-4.4.$tn.7 { |
||
651 | faultsim_restore_and_reopen $prefix |
||
652 | foreach f [glob ${prefix}-mj*] { file delete -force $f } |
||
653 | execsql "ATTACH '${prefix}2' AS aux" |
||
654 | } {} |
||
655 | do_execsql_test pager1-4.4.$tn.8 { |
||
656 | SELECT * FROM a |
||
657 | } {double-you why zed won too free} |
||
658 | do_execsql_test pager1-4.4.$tn.9 { |
||
659 | SELECT * FROM b |
||
660 | } {won too free double-you why zed} |
||
661 | } |
||
662 | |||
663 | cd $pwd |
||
664 | } |
||
665 | db close |
||
666 | tv delete |
||
667 | file delete -force $dirname |
||
668 | |||
669 | |||
670 | # Set up a VFS to make a copy of the file-system just before deleting a |
||
671 | # journal file to commit a transaction. The transaction modifies exactly |
||
672 | # two database pages (and page 1 - the change counter). |
||
673 | # |
||
674 | testvfs tv -default 1 |
||
675 | tv sectorsize 512 |
||
676 | tv script copy_on_journal_delete |
||
677 | tv filter xDelete |
||
678 | proc copy_on_journal_delete {method filename args} { |
||
679 | if {[string match *journal $filename]} faultsim_save |
||
680 | return SQLITE_OK |
||
681 | } |
||
682 | faultsim_delete_and_reopen |
||
683 | do_execsql_test pager1.4.5.1 { |
||
684 | PRAGMA journal_mode = DELETE; |
||
685 | PRAGMA page_size = 1024; |
||
686 | CREATE TABLE t1(a, b); |
||
687 | CREATE TABLE t2(a, b); |
||
688 | INSERT INTO t1 VALUES('I', 'II'); |
||
689 | INSERT INTO t2 VALUES('III', 'IV'); |
||
690 | BEGIN; |
||
691 | INSERT INTO t1 VALUES(1, 2); |
||
692 | INSERT INTO t2 VALUES(3, 4); |
||
693 | COMMIT; |
||
694 | } {delete} |
||
695 | tv filter {} |
||
696 | |||
697 | # Check the transaction was committed: |
||
698 | # |
||
699 | do_execsql_test pager1.4.5.2 { |
||
700 | SELECT * FROM t1; |
||
701 | SELECT * FROM t2; |
||
702 | } {I II 1 2 III IV 3 4} |
||
703 | |||
704 | # Now try four tests: |
||
705 | # |
||
706 | # pager1-4.5.3: Restore the file-system. Check that the whole transaction |
||
707 | # is rolled back. |
||
708 | # |
||
709 | # pager1-4.5.4: Restore the file-system. Corrupt the first record in the |
||
710 | # journal. Check the transaction is not rolled back. |
||
711 | # |
||
712 | # pager1-4.5.5: Restore the file-system. Corrupt the second record in the |
||
713 | # journal. Check that the first record in the transaction is |
||
714 | # played back, but not the second. |
||
715 | # |
||
716 | # pager1-4.5.6: Restore the file-system. Try to open the database with a |
||
717 | # readonly connection. This should fail, as a read-only |
||
718 | # connection cannot roll back the database file. |
||
719 | # |
||
720 | faultsim_restore_and_reopen |
||
721 | do_execsql_test pager1.4.5.3 { |
||
722 | SELECT * FROM t1; |
||
723 | SELECT * FROM t2; |
||
724 | } {I II III IV} |
||
725 | faultsim_restore_and_reopen |
||
726 | hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF |
||
727 | do_execsql_test pager1.4.5.4 { |
||
728 | SELECT * FROM t1; |
||
729 | SELECT * FROM t2; |
||
730 | } {I II 1 2 III IV 3 4} |
||
731 | faultsim_restore_and_reopen |
||
732 | hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF |
||
733 | do_execsql_test pager1.4.5.5 { |
||
734 | SELECT * FROM t1; |
||
735 | SELECT * FROM t2; |
||
736 | } {I II III IV 3 4} |
||
737 | |||
738 | faultsim_restore_and_reopen |
||
739 | db close |
||
740 | sqlite3 db test.db -readonly 1 |
||
741 | do_catchsql_test pager1.4.5.6 { |
||
742 | SELECT * FROM t1; |
||
743 | SELECT * FROM t2; |
||
744 | } {1 {disk I/O error}} |
||
745 | db close |
||
746 | |||
747 | # Snapshot the file-system just before multi-file commit. Save the name |
||
748 | # of the master journal file in $::mj_filename. |
||
749 | # |
||
750 | tv script copy_on_mj_delete |
||
751 | tv filter xDelete |
||
752 | proc copy_on_mj_delete {method filename args} { |
||
753 | if {[string match *mj* [file tail $filename]]} { |
||
754 | set ::mj_filename $filename |
||
755 | faultsim_save |
||
756 | } |
||
757 | return SQLITE_OK |
||
758 | } |
||
759 | do_test pager1.4.6.1 { |
||
760 | faultsim_delete_and_reopen |
||
761 | execsql { |
||
762 | PRAGMA journal_mode = DELETE; |
||
763 | ATTACH 'test.db2' AS two; |
||
764 | CREATE TABLE t1(a, b); |
||
765 | CREATE TABLE two.t2(a, b); |
||
766 | INSERT INTO t1 VALUES(1, 't1.1'); |
||
767 | INSERT INTO t2 VALUES(1, 't2.1'); |
||
768 | BEGIN; |
||
769 | UPDATE t1 SET b = 't1.2'; |
||
770 | UPDATE t2 SET b = 't2.2'; |
||
771 | COMMIT; |
||
772 | } |
||
773 | tv filter {} |
||
774 | db close |
||
775 | } {} |
||
776 | |||
777 | faultsim_restore_and_reopen |
||
778 | do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1} |
||
779 | do_test pager1.4.6.3 { file exists $::mj_filename } {1} |
||
780 | do_execsql_test pager1.4.6.4 { |
||
781 | ATTACH 'test.db2' AS two; |
||
782 | SELECT * FROM t2; |
||
783 | } {1 t2.1} |
||
784 | do_test pager1.4.6.5 { file exists $::mj_filename } {0} |
||
785 | |||
786 | faultsim_restore_and_reopen |
||
787 | db close |
||
788 | do_test pager1.4.6.8 { |
||
789 | set ::mj_filename1 $::mj_filename |
||
790 | tv filter xDelete |
||
791 | sqlite3 db test.db2 |
||
792 | execsql { |
||
793 | PRAGMA journal_mode = DELETE; |
||
794 | ATTACH 'test.db3' AS three; |
||
795 | CREATE TABLE three.t3(a, b); |
||
796 | INSERT INTO t3 VALUES(1, 't3.1'); |
||
797 | BEGIN; |
||
798 | UPDATE t2 SET b = 't2.3'; |
||
799 | UPDATE t3 SET b = 't3.3'; |
||
800 | COMMIT; |
||
801 | } |
||
802 | expr {$::mj_filename1 != $::mj_filename} |
||
803 | } {1} |
||
804 | faultsim_restore_and_reopen |
||
805 | tv filter {} |
||
806 | |||
807 | # The file-system now contains: |
||
808 | # |
||
809 | # * three databases |
||
810 | # * three hot-journal files |
||
811 | # * two master-journal files. |
||
812 | # |
||
813 | # The hot-journals associated with test.db2 and test.db3 point to |
||
814 | # master journal $::mj_filename. The hot-journal file associated with |
||
815 | # test.db points to master journal $::mj_filename1. So reading from |
||
816 | # test.db should delete $::mj_filename1. |
||
817 | # |
||
818 | do_test pager1.4.6.9 { |
||
819 | lsort [glob test.db*] |
||
820 | } [lsort [list \ |
||
821 | test.db test.db2 test.db3 \ |
||
822 | test.db-journal test.db2-journal test.db3-journal \ |
||
823 | [file tail $::mj_filename] [file tail $::mj_filename1] |
||
824 | ]] |
||
825 | |||
826 | # The master-journal $::mj_filename1 contains pointers to test.db and |
||
827 | # test.db2. However the hot-journal associated with test.db2 points to |
||
828 | # a different master-journal. Therefore, reading from test.db only should |
||
829 | # be enough to cause SQLite to delete $::mj_filename1. |
||
830 | # |
||
831 | do_test pager1.4.6.10 { file exists $::mj_filename } {1} |
||
832 | do_test pager1.4.6.11 { file exists $::mj_filename1 } {1} |
||
833 | do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1} |
||
834 | do_test pager1.4.6.13 { file exists $::mj_filename } {1} |
||
835 | do_test pager1.4.6.14 { file exists $::mj_filename1 } {0} |
||
836 | |||
837 | do_execsql_test pager1.4.6.12 { |
||
838 | ATTACH 'test.db2' AS two; |
||
839 | SELECT * FROM t2; |
||
840 | } {1 t2.1} |
||
841 | do_test pager1.4.6.13 { file exists $::mj_filename } {1} |
||
842 | do_execsql_test pager1.4.6.14 { |
||
843 | ATTACH 'test.db3' AS three; |
||
844 | SELECT * FROM t3; |
||
845 | } {1 t3.1} |
||
846 | do_test pager1.4.6.15 { file exists $::mj_filename } {0} |
||
847 | |||
848 | db close |
||
849 | tv delete |
||
850 | |||
851 | testvfs tv -default 1 |
||
852 | tv sectorsize 512 |
||
853 | tv script copy_on_journal_delete |
||
854 | tv filter xDelete |
||
855 | proc copy_on_journal_delete {method filename args} { |
||
856 | if {[string match *journal $filename]} faultsim_save |
||
857 | return SQLITE_OK |
||
858 | } |
||
859 | faultsim_delete_and_reopen |
||
860 | do_execsql_test pager1.4.7.1 { |
||
861 | PRAGMA journal_mode = DELETE; |
||
862 | CREATE TABLE t1(x PRIMARY KEY, y); |
||
863 | CREATE INDEX i1 ON t1(y); |
||
864 | INSERT INTO t1 VALUES('I', 'one'); |
||
865 | INSERT INTO t1 VALUES('II', 'four'); |
||
866 | INSERT INTO t1 VALUES('III', 'nine'); |
||
867 | BEGIN; |
||
868 | INSERT INTO t1 VALUES('IV', 'sixteen'); |
||
869 | INSERT INTO t1 VALUES('V' , 'twentyfive'); |
||
870 | COMMIT; |
||
871 | } {delete} |
||
872 | tv filter {} |
||
873 | db close |
||
874 | tv delete |
||
875 | do_test pager1.4.7.2 { |
||
876 | faultsim_restore_and_reopen |
||
877 | catch {file attributes test.db-journal -permissions r--------} |
||
878 | catch {file attributes test.db-journal -readonly 1} |
||
879 | catchsql { SELECT * FROM t1 } |
||
880 | } {1 {unable to open database file}} |
||
881 | do_test pager1.4.7.3 { |
||
882 | db close |
||
883 | catch {file attributes test.db-journal -permissions rw-rw-rw-} |
||
884 | catch {file attributes test.db-journal -readonly 0} |
||
885 | file delete test.db-journal |
||
886 | file exists test.db-journal |
||
887 | } {0} |
||
888 | |||
889 | #------------------------------------------------------------------------- |
||
890 | # The following tests deal with multi-file commits. |
||
891 | # |
||
892 | # pager1-5.1.*: The case where a multi-file cannot be committed because |
||
893 | # another connection is holding a SHARED lock on one of the |
||
894 | # files. After the SHARED lock is removed, the COMMIT succeeds. |
||
895 | # |
||
896 | # pager1-5.2.*: Multi-file commits with journal_mode=memory. |
||
897 | # |
||
898 | # pager1-5.3.*: Multi-file commits with journal_mode=memory. |
||
899 | # |
||
900 | # pager1-5.4.*: Check that with synchronous=normal, the master-journal file |
||
901 | # name is added to a journal file immediately after the last |
||
902 | # journal record. But with synchronous=full, extra unused space |
||
903 | # is allocated between the last journal record and the |
||
904 | # master-journal file name so that the master-journal file |
||
905 | # name does not lie on the same sector as the last journal file |
||
906 | # record. |
||
907 | # |
||
908 | # pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is |
||
909 | # truncated to zero bytes when a multi-file transaction is |
||
910 | # committed (instead of the first couple of bytes being zeroed). |
||
911 | # |
||
912 | # |
||
913 | do_test pager1-5.1.1 { |
||
914 | faultsim_delete_and_reopen |
||
915 | execsql { |
||
916 | ATTACH 'test.db2' AS aux; |
||
917 | CREATE TABLE t1(a, b); |
||
918 | CREATE TABLE aux.t2(a, b); |
||
919 | INSERT INTO t1 VALUES(17, 'Lenin'); |
||
920 | INSERT INTO t1 VALUES(22, 'Stalin'); |
||
921 | INSERT INTO t1 VALUES(53, 'Khrushchev'); |
||
922 | } |
||
923 | } {} |
||
924 | do_test pager1-5.1.2 { |
||
925 | execsql { |
||
926 | BEGIN; |
||
927 | INSERT INTO t1 VALUES(64, 'Brezhnev'); |
||
928 | INSERT INTO t2 SELECT * FROM t1; |
||
929 | } |
||
930 | sqlite3 db2 test.db2 |
||
931 | execsql { |
||
932 | BEGIN; |
||
933 | SELECT * FROM t2; |
||
934 | } db2 |
||
935 | } {} |
||
936 | do_test pager1-5.1.3 { |
||
937 | catchsql COMMIT |
||
938 | } {1 {database is locked}} |
||
939 | do_test pager1-5.1.4 { |
||
940 | execsql COMMIT db2 |
||
941 | execsql COMMIT |
||
942 | execsql { SELECT * FROM t2 } db2 |
||
943 | } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev} |
||
944 | do_test pager1-5.1.5 { |
||
945 | db2 close |
||
946 | } {} |
||
947 | |||
948 | do_test pager1-5.2.1 { |
||
949 | execsql { |
||
950 | PRAGMA journal_mode = memory; |
||
951 | BEGIN; |
||
952 | INSERT INTO t1 VALUES(84, 'Andropov'); |
||
953 | INSERT INTO t2 VALUES(84, 'Andropov'); |
||
954 | COMMIT; |
||
955 | } |
||
956 | } {memory} |
||
957 | do_test pager1-5.3.1 { |
||
958 | execsql { |
||
959 | PRAGMA journal_mode = off; |
||
960 | BEGIN; |
||
961 | INSERT INTO t1 VALUES(85, 'Gorbachev'); |
||
962 | INSERT INTO t2 VALUES(85, 'Gorbachev'); |
||
963 | COMMIT; |
||
964 | } |
||
965 | } {off} |
||
966 | |||
967 | do_test pager1-5.4.1 { |
||
968 | db close |
||
969 | testvfs tv |
||
970 | sqlite3 db test.db -vfs tv |
||
971 | execsql { ATTACH 'test.db2' AS aux } |
||
972 | |||
973 | tv filter xDelete |
||
974 | tv script max_journal_size |
||
975 | tv sectorsize 512 |
||
976 | set ::max_journal 0 |
||
977 | proc max_journal_size {method args} { |
||
978 | set sz 0 |
||
979 | catch { set sz [file size test.db-journal] } |
||
980 | if {$sz > $::max_journal} { |
||
981 | set ::max_journal $sz |
||
982 | } |
||
983 | return SQLITE_OK |
||
984 | } |
||
985 | execsql { |
||
986 | PRAGMA journal_mode = DELETE; |
||
987 | PRAGMA synchronous = NORMAL; |
||
988 | BEGIN; |
||
989 | INSERT INTO t1 VALUES(85, 'Gorbachev'); |
||
990 | INSERT INTO t2 VALUES(85, 'Gorbachev'); |
||
991 | COMMIT; |
||
992 | } |
||
993 | set ::max_journal |
||
994 | } [expr 2615+[string length [pwd]]] |
||
995 | do_test pager1-5.4.2 { |
||
996 | set ::max_journal 0 |
||
997 | execsql { |
||
998 | PRAGMA synchronous = full; |
||
999 | BEGIN; |
||
1000 | DELETE FROM t1 WHERE b = 'Lenin'; |
||
1001 | DELETE FROM t2 WHERE b = 'Lenin'; |
||
1002 | COMMIT; |
||
1003 | } |
||
1004 | set ::max_journal |
||
1005 | } [expr 3111+[string length [pwd]]] |
||
1006 | db close |
||
1007 | tv delete |
||
1008 | |||
1009 | do_test pager1-5.5.1 { |
||
1010 | sqlite3 db test.db |
||
1011 | execsql { |
||
1012 | ATTACH 'test.db2' AS aux; |
||
1013 | PRAGMA journal_mode = PERSIST; |
||
1014 | CREATE TABLE t3(a, b); |
||
1015 | INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
1016 | UPDATE t3 SET b = randomblob(1500); |
||
1017 | } |
||
1018 | expr [file size test.db-journal] > 15000 |
||
1019 | } {1} |
||
1020 | do_test pager1-5.5.2 { |
||
1021 | execsql { |
||
1022 | PRAGMA synchronous = full; |
||
1023 | BEGIN; |
||
1024 | DELETE FROM t1 WHERE b = 'Stalin'; |
||
1025 | DELETE FROM t2 WHERE b = 'Stalin'; |
||
1026 | COMMIT; |
||
1027 | } |
||
1028 | file size test.db-journal |
||
1029 | } {0} |
||
1030 | |||
1031 | |||
1032 | #------------------------------------------------------------------------- |
||
1033 | # The following tests work with "PRAGMA max_page_count" |
||
1034 | # |
||
1035 | do_test pager1-6.1 { |
||
1036 | faultsim_delete_and_reopen |
||
1037 | execsql { |
||
1038 | PRAGMA auto_vacuum = none; |
||
1039 | PRAGMA max_page_count = 10; |
||
1040 | CREATE TABLE t2(a, b); |
||
1041 | CREATE TABLE t3(a, b); |
||
1042 | CREATE TABLE t4(a, b); |
||
1043 | CREATE TABLE t5(a, b); |
||
1044 | CREATE TABLE t6(a, b); |
||
1045 | CREATE TABLE t7(a, b); |
||
1046 | CREATE TABLE t8(a, b); |
||
1047 | CREATE TABLE t9(a, b); |
||
1048 | CREATE TABLE t10(a, b); |
||
1049 | } |
||
1050 | } {10} |
||
1051 | do_catchsql_test pager1-6.2 { |
||
1052 | CREATE TABLE t11(a, b) |
||
1053 | } {1 {database or disk is full}} |
||
1054 | do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10} |
||
1055 | do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15} |
||
1056 | do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {} |
||
1057 | do_execsql_test pager1-6.7 { |
||
1058 | BEGIN; |
||
1059 | INSERT INTO t11 VALUES(1, 2); |
||
1060 | PRAGMA max_page_count = 13; |
||
1061 | } {13} |
||
1062 | do_execsql_test pager1-6.8 { |
||
1063 | INSERT INTO t11 VALUES(3, 4); |
||
1064 | PRAGMA max_page_count = 10; |
||
1065 | } {11} |
||
1066 | do_execsql_test pager1-6.9 { COMMIT } {} |
||
1067 | |||
1068 | do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11} |
||
1069 | do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4} |
||
1070 | do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11} |
||
1071 | |||
1072 | |||
1073 | #------------------------------------------------------------------------- |
||
1074 | # The following tests work with "PRAGMA journal_mode=TRUNCATE" and |
||
1075 | # "PRAGMA locking_mode=EXCLUSIVE". |
||
1076 | # |
||
1077 | # Each test is specified with 5 variables. As follows: |
||
1078 | # |
||
1079 | # $tn: Test Number. Used as part of the [do_test] test names. |
||
1080 | # $sql: SQL to execute. |
||
1081 | # $res: Expected result of executing $sql. |
||
1082 | # $js: The expected size of the journal file, in bytes, after executing |
||
1083 | # the SQL script. Or -1 if the journal is not expected to exist. |
||
1084 | # $ws: The expected size of the WAL file, in bytes, after executing |
||
1085 | # the SQL script. Or -1 if the WAL is not expected to exist. |
||
1086 | # |
||
1087 | ifcapable wal { |
||
1088 | faultsim_delete_and_reopen |
||
1089 | foreach {tn sql res js ws} [subst { |
||
1090 | |||
1091 | 1 { |
||
1092 | CREATE TABLE t1(a, b); |
||
1093 | PRAGMA auto_vacuum=OFF; |
||
1094 | PRAGMA synchronous=NORMAL; |
||
1095 | PRAGMA page_size=1024; |
||
1096 | PRAGMA locking_mode=EXCLUSIVE; |
||
1097 | PRAGMA journal_mode=TRUNCATE; |
||
1098 | INSERT INTO t1 VALUES(1, 2); |
||
1099 | } {exclusive truncate} 0 -1 |
||
1100 | |||
1101 | 2 { |
||
1102 | BEGIN IMMEDIATE; |
||
1103 | SELECT * FROM t1; |
||
1104 | COMMIT; |
||
1105 | } {1 2} 0 -1 |
||
1106 | |||
1107 | 3 { |
||
1108 | BEGIN; |
||
1109 | SELECT * FROM t1; |
||
1110 | COMMIT; |
||
1111 | } {1 2} 0 -1 |
||
1112 | |||
1113 | 4 { PRAGMA journal_mode = WAL } wal -1 -1 |
||
1114 | 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024] |
||
1115 | 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024] |
||
1116 | 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024] |
||
1117 | |||
1118 | 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1 |
||
1119 | 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1 |
||
1120 | 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1 |
||
1121 | |||
1122 | }] { |
||
1123 | do_execsql_test pager1-7.1.$tn.1 $sql $res |
||
1124 | catch { set J -1 ; set J [file size test.db-journal] } |
||
1125 | catch { set W -1 ; set W [file size test.db-wal] } |
||
1126 | do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws] |
||
1127 | } |
||
1128 | } |
||
1129 | |||
1130 | do_test pager1-7.2.1 { |
||
1131 | faultsim_delete_and_reopen |
||
1132 | execsql { |
||
1133 | PRAGMA locking_mode = EXCLUSIVE; |
||
1134 | CREATE TABLE t1(a, b); |
||
1135 | BEGIN; |
||
1136 | PRAGMA journal_mode = delete; |
||
1137 | PRAGMA journal_mode = truncate; |
||
1138 | } |
||
1139 | } {exclusive delete truncate} |
||
1140 | do_test pager1-7.2.2 { |
||
1141 | execsql { INSERT INTO t1 VALUES(1, 2) } |
||
1142 | execsql { PRAGMA journal_mode = persist } |
||
1143 | } {truncate} |
||
1144 | do_test pager1-7.2.3 { |
||
1145 | execsql { COMMIT } |
||
1146 | execsql { |
||
1147 | PRAGMA journal_mode = persist; |
||
1148 | PRAGMA journal_size_limit; |
||
1149 | } |
||
1150 | } {persist -1} |
||
1151 | |||
1152 | #------------------------------------------------------------------------- |
||
1153 | # The following tests, pager1-8.*, test that the special filenames |
||
1154 | # ":memory:" and "" open temporary databases. |
||
1155 | # |
||
1156 | foreach {tn filename} { |
||
1157 | 1 :memory: |
||
1158 | 2 "" |
||
1159 | } { |
||
1160 | do_test pager1-8.$tn.1 { |
||
1161 | faultsim_delete_and_reopen |
||
1162 | db close |
||
1163 | sqlite3 db $filename |
||
1164 | execsql { |
||
1165 | PRAGMA auto_vacuum = 1; |
||
1166 | CREATE TABLE x1(x); |
||
1167 | INSERT INTO x1 VALUES('Charles'); |
||
1168 | INSERT INTO x1 VALUES('James'); |
||
1169 | INSERT INTO x1 VALUES('Mary'); |
||
1170 | SELECT * FROM x1; |
||
1171 | } |
||
1172 | } {Charles James Mary} |
||
1173 | |||
1174 | do_test pager1-8.$tn.2 { |
||
1175 | sqlite3 db2 $filename |
||
1176 | catchsql { SELECT * FROM x1 } db2 |
||
1177 | } {1 {no such table: x1}} |
||
1178 | |||
1179 | do_execsql_test pager1-8.$tn.3 { |
||
1180 | BEGIN; |
||
1181 | INSERT INTO x1 VALUES('William'); |
||
1182 | INSERT INTO x1 VALUES('Anne'); |
||
1183 | ROLLBACK; |
||
1184 | } {} |
||
1185 | } |
||
1186 | |||
1187 | #------------------------------------------------------------------------- |
||
1188 | # The next block of tests - pager1-9.* - deal with interactions between |
||
1189 | # the pager and the backup API. Test cases: |
||
1190 | # |
||
1191 | # pager1-9.1.*: Test that a backup completes successfully even if the |
||
1192 | # source db is written to during the backup op. |
||
1193 | # |
||
1194 | # pager1-9.2.*: Test that a backup completes successfully even if the |
||
1195 | # source db is written to and then rolled back during a |
||
1196 | # backup operation. |
||
1197 | # |
||
1198 | do_test pager1-9.0.1 { |
||
1199 | faultsim_delete_and_reopen |
||
1200 | db func a_string a_string |
||
1201 | execsql { |
||
1202 | PRAGMA cache_size = 10; |
||
1203 | BEGIN; |
||
1204 | CREATE TABLE ab(a, b, UNIQUE(a, b)); |
||
1205 | INSERT INTO ab VALUES( a_string(200), a_string(300) ); |
||
1206 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1207 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1208 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1209 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1210 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1211 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1212 | INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab; |
||
1213 | COMMIT; |
||
1214 | } |
||
1215 | } {} |
||
1216 | do_test pager1-9.0.2 { |
||
1217 | sqlite3 db2 test.db2 |
||
1218 | db2 eval { PRAGMA cache_size = 10 } |
||
1219 | sqlite3_backup B db2 main db main |
||
1220 | list [B step 10000] [B finish] |
||
1221 | } {SQLITE_DONE SQLITE_OK} |
||
1222 | do_test pager1-9.0.3 { |
||
1223 | db one {SELECT md5sum(a, b) FROM ab} |
||
1224 | } [db2 one {SELECT md5sum(a, b) FROM ab}] |
||
1225 | |||
1226 | do_test pager1-9.1.1 { |
||
1227 | execsql { UPDATE ab SET a = a_string(201) } |
||
1228 | sqlite3_backup B db2 main db main |
||
1229 | B step 30 |
||
1230 | } {SQLITE_OK} |
||
1231 | do_test pager1-9.1.2 { |
||
1232 | execsql { UPDATE ab SET b = a_string(301) } |
||
1233 | list [B step 10000] [B finish] |
||
1234 | } {SQLITE_DONE SQLITE_OK} |
||
1235 | do_test pager1-9.1.3 { |
||
1236 | db one {SELECT md5sum(a, b) FROM ab} |
||
1237 | } [db2 one {SELECT md5sum(a, b) FROM ab}] |
||
1238 | do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128} |
||
1239 | |||
1240 | do_test pager1-9.2.1 { |
||
1241 | execsql { UPDATE ab SET a = a_string(202) } |
||
1242 | sqlite3_backup B db2 main db main |
||
1243 | B step 30 |
||
1244 | } {SQLITE_OK} |
||
1245 | do_test pager1-9.2.2 { |
||
1246 | execsql { |
||
1247 | BEGIN; |
||
1248 | UPDATE ab SET b = a_string(301); |
||
1249 | ROLLBACK; |
||
1250 | } |
||
1251 | list [B step 10000] [B finish] |
||
1252 | } {SQLITE_DONE SQLITE_OK} |
||
1253 | do_test pager1-9.2.3 { |
||
1254 | db one {SELECT md5sum(a, b) FROM ab} |
||
1255 | } [db2 one {SELECT md5sum(a, b) FROM ab}] |
||
1256 | do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128} |
||
1257 | db close |
||
1258 | db2 close |
||
1259 | |||
1260 | do_test pager1-9.3.1 { |
||
1261 | testvfs tv -default 1 |
||
1262 | tv sectorsize 4096 |
||
1263 | faultsim_delete_and_reopen |
||
1264 | |||
1265 | execsql { PRAGMA page_size = 1024 } |
||
1266 | for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" } |
||
1267 | } {} |
||
1268 | do_test pager1-9.3.2 { |
||
1269 | sqlite3 db2 test.db2 |
||
1270 | |||
1271 | execsql { |
||
1272 | PRAGMA page_size = 4096; |
||
1273 | PRAGMA synchronous = OFF; |
||
1274 | CREATE TABLE t1(a, b); |
||
1275 | CREATE TABLE t2(a, b); |
||
1276 | } db2 |
||
1277 | |||
1278 | sqlite3_backup B db2 main db main |
||
1279 | B step 30 |
||
1280 | list [B step 10000] [B finish] |
||
1281 | } {SQLITE_DONE SQLITE_OK} |
||
1282 | do_test pager1-9.3.3 { |
||
1283 | db2 close |
||
1284 | db close |
||
1285 | tv delete |
||
1286 | file size test.db2 |
||
1287 | } [file size test.db] |
||
1288 | |||
1289 | do_test pager1-9.4.1 { |
||
1290 | faultsim_delete_and_reopen |
||
1291 | sqlite3 db2 test.db2 |
||
1292 | execsql { |
||
1293 | PRAGMA page_size = 4096; |
||
1294 | CREATE TABLE t1(a, b); |
||
1295 | CREATE TABLE t2(a, b); |
||
1296 | } db2 |
||
1297 | sqlite3_backup B db2 main db main |
||
1298 | list [B step 10000] [B finish] |
||
1299 | } {SQLITE_DONE SQLITE_OK} |
||
1300 | do_test pager1-9.4.2 { |
||
1301 | list [file size test.db2] [file size test.db] |
||
1302 | } {0 0} |
||
1303 | db2 close |
||
1304 | |||
1305 | #------------------------------------------------------------------------- |
||
1306 | # Test that regardless of the value returned by xSectorSize(), the |
||
1307 | # minimum effective sector-size is 512 and the maximum 65536 bytes. |
||
1308 | # |
||
1309 | testvfs tv -default 1 |
||
1310 | foreach sectorsize { |
||
1311 | 32 64 128 256 512 1024 2048 |
||
1312 | 4096 8192 16384 32768 65536 131072 262144 |
||
1313 | } { |
||
1314 | tv sectorsize $sectorsize |
||
1315 | set eff $sectorsize |
||
1316 | if {$sectorsize < 512} { set eff 512 } |
||
1317 | if {$sectorsize > 65536} { set eff 65536 } |
||
1318 | |||
1319 | do_test pager1-10.$sectorsize.1 { |
||
1320 | faultsim_delete_and_reopen |
||
1321 | db func a_string a_string |
||
1322 | execsql { |
||
1323 | PRAGMA journal_mode = PERSIST; |
||
1324 | PRAGMA page_size = 1024; |
||
1325 | BEGIN; |
||
1326 | CREATE TABLE t1(a, b); |
||
1327 | CREATE TABLE t2(a, b); |
||
1328 | CREATE TABLE t3(a, b); |
||
1329 | COMMIT; |
||
1330 | } |
||
1331 | file size test.db-journal |
||
1332 | } [expr $sectorsize > 65536 ? 65536 : $sectorsize] |
||
1333 | |||
1334 | do_test pager1-10.$sectorsize.2 { |
||
1335 | execsql { |
||
1336 | INSERT INTO t3 VALUES(a_string(300), a_string(300)); |
||
1337 | INSERT INTO t3 SELECT * FROM t3; /* 2 */ |
||
1338 | INSERT INTO t3 SELECT * FROM t3; /* 4 */ |
||
1339 | INSERT INTO t3 SELECT * FROM t3; /* 8 */ |
||
1340 | INSERT INTO t3 SELECT * FROM t3; /* 16 */ |
||
1341 | INSERT INTO t3 SELECT * FROM t3; /* 32 */ |
||
1342 | } |
||
1343 | } {} |
||
1344 | |||
1345 | do_test pager1-10.$sectorsize.3 { |
||
1346 | db close |
||
1347 | sqlite3 db test.db |
||
1348 | execsql { |
||
1349 | PRAGMA cache_size = 10; |
||
1350 | BEGIN; |
||
1351 | } |
||
1352 | recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"} |
||
1353 | execsql { |
||
1354 | COMMIT; |
||
1355 | SELECT * FROM t2; |
||
1356 | } |
||
1357 | } {1 2} |
||
1358 | |||
1359 | do_test pager1-10.$sectorsize.4 { |
||
1360 | execsql { |
||
1361 | CREATE TABLE t6(a, b); |
||
1362 | CREATE TABLE t7(a, b); |
||
1363 | CREATE TABLE t5(a, b); |
||
1364 | DROP TABLE t6; |
||
1365 | DROP TABLE t7; |
||
1366 | } |
||
1367 | execsql { |
||
1368 | BEGIN; |
||
1369 | CREATE TABLE t6(a, b); |
||
1370 | } |
||
1371 | recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"} |
||
1372 | execsql { |
||
1373 | COMMIT; |
||
1374 | SELECT * FROM t5; |
||
1375 | } |
||
1376 | } {1 2} |
||
1377 | |||
1378 | } |
||
1379 | db close |
||
1380 | |||
1381 | tv sectorsize 4096 |
||
1382 | do_test pager1.10.x.1 { |
||
1383 | faultsim_delete_and_reopen |
||
1384 | execsql { |
||
1385 | PRAGMA auto_vacuum = none; |
||
1386 | PRAGMA page_size = 1024; |
||
1387 | CREATE TABLE t1(x); |
||
1388 | } |
||
1389 | for {set i 0} {$i<30} {incr i} { |
||
1390 | execsql { INSERT INTO t1 VALUES(zeroblob(900)) } |
||
1391 | } |
||
1392 | file size test.db |
||
1393 | } {32768} |
||
1394 | do_test pager1.10.x.2 { |
||
1395 | execsql { |
||
1396 | CREATE TABLE t2(x); |
||
1397 | DROP TABLE t2; |
||
1398 | } |
||
1399 | file size test.db |
||
1400 | } {33792} |
||
1401 | do_test pager1.10.x.3 { |
||
1402 | execsql { |
||
1403 | BEGIN; |
||
1404 | CREATE TABLE t2(x); |
||
1405 | } |
||
1406 | recursive_select 30 t1 |
||
1407 | execsql { |
||
1408 | CREATE TABLE t3(x); |
||
1409 | COMMIT; |
||
1410 | } |
||
1411 | } {} |
||
1412 | |||
1413 | db close |
||
1414 | tv delete |
||
1415 | |||
1416 | testvfs tv -default 1 |
||
1417 | faultsim_delete_and_reopen |
||
1418 | db func a_string a_string |
||
1419 | do_execsql_test pager1-11.1 { |
||
1420 | PRAGMA journal_mode = DELETE; |
||
1421 | PRAGMA cache_size = 10; |
||
1422 | BEGIN; |
||
1423 | CREATE TABLE zz(top PRIMARY KEY); |
||
1424 | INSERT INTO zz VALUES(a_string(222)); |
||
1425 | INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; |
||
1426 | INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; |
||
1427 | INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; |
||
1428 | INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; |
||
1429 | INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz; |
||
1430 | COMMIT; |
||
1431 | BEGIN; |
||
1432 | UPDATE zz SET top = a_string(345); |
||
1433 | } {delete} |
||
1434 | |||
1435 | proc lockout {method args} { return SQLITE_IOERR } |
||
1436 | tv script lockout |
||
1437 | tv filter {xWrite xTruncate xSync} |
||
1438 | do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}} |
||
1439 | |||
1440 | tv script {} |
||
1441 | do_test pager1-11.3 { |
||
1442 | sqlite3 db2 test.db |
||
1443 | execsql { |
||
1444 | PRAGMA journal_mode = TRUNCATE; |
||
1445 | PRAGMA integrity_check; |
||
1446 | } db2 |
||
1447 | } {truncate ok} |
||
1448 | do_test pager1-11.4 { |
||
1449 | db2 close |
||
1450 | file exists test.db-journal |
||
1451 | } {0} |
||
1452 | do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32} |
||
1453 | db close |
||
1454 | tv delete |
||
1455 | |||
1456 | #------------------------------------------------------------------------- |
||
1457 | # Test "PRAGMA page_size" |
||
1458 | # |
||
1459 | testvfs tv -default 1 |
||
1460 | tv sectorsize 1024 |
||
1461 | foreach pagesize { |
||
1462 | 512 1024 2048 4096 8192 16384 32768 |
||
1463 | } { |
||
1464 | faultsim_delete_and_reopen |
||
1465 | |||
1466 | # The sector-size (according to the VFS) is 1024 bytes. So if the |
||
1467 | # page-size requested using "PRAGMA page_size" is greater than the |
||
1468 | # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective |
||
1469 | # page-size remains 1024 bytes. |
||
1470 | # |
||
1471 | set eff $pagesize |
||
1472 | if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 } |
||
1473 | |||
1474 | do_test pager1-12.$pagesize.1 { |
||
1475 | sqlite3 db2 test.db |
||
1476 | execsql " |
||
1477 | PRAGMA page_size = $pagesize; |
||
1478 | CREATE VIEW v AS SELECT * FROM sqlite_master; |
||
1479 | " db2 |
||
1480 | file size test.db |
||
1481 | } $eff |
||
1482 | do_test pager1-12.$pagesize.2 { |
||
1483 | sqlite3 db2 test.db |
||
1484 | execsql { |
||
1485 | SELECT count(*) FROM v; |
||
1486 | PRAGMA main.page_size; |
||
1487 | } db2 |
||
1488 | } [list 1 $eff] |
||
1489 | do_test pager1-12.$pagesize.3 { |
||
1490 | execsql { |
||
1491 | SELECT count(*) FROM v; |
||
1492 | PRAGMA main.page_size; |
||
1493 | } |
||
1494 | } [list 1 $eff] |
||
1495 | db2 close |
||
1496 | } |
||
1497 | db close |
||
1498 | tv delete |
||
1499 | |||
1500 | #------------------------------------------------------------------------- |
||
1501 | # Test specal "PRAGMA journal_mode=PERSIST" test cases. |
||
1502 | # |
||
1503 | # pager1-13.1.*: This tests a special case encountered in persistent |
||
1504 | # journal mode: If the journal associated with a transaction |
||
1505 | # is smaller than the journal file (because a previous |
||
1506 | # transaction left a very large non-hot journal file in the |
||
1507 | # file-system), then SQLite has to be careful that there is |
||
1508 | # not a journal-header left over from a previous transaction |
||
1509 | # immediately following the journal content just written. |
||
1510 | # If there is, and the process crashes so that the journal |
||
1511 | # becomes a hot-journal and must be rolled back by another |
||
1512 | # process, there is a danger that the other process may roll |
||
1513 | # back the aborted transaction, then continue copying data |
||
1514 | # from an older transaction from the remainder of the journal. |
||
1515 | # See the syncJournal() function for details. |
||
1516 | # |
||
1517 | # pager1-13.2.*: Same test as the previous. This time, throw an index into |
||
1518 | # the mix to make the integrity-check more likely to catch |
||
1519 | # errors. |
||
1520 | # |
||
1521 | testvfs tv -default 1 |
||
1522 | tv script xSyncCb |
||
1523 | tv filter xSync |
||
1524 | proc xSyncCb {method filename args} { |
||
1525 | set t [file tail $filename] |
||
1526 | if {$t == "test.db"} faultsim_save |
||
1527 | return SQLITE_OK |
||
1528 | } |
||
1529 | faultsim_delete_and_reopen |
||
1530 | db func a_string a_string |
||
1531 | |||
1532 | # The UPDATE statement at the end of this test case creates a really big |
||
1533 | # journal. Since the cache-size is only 10 pages, the journal contains |
||
1534 | # frequent journal headers. |
||
1535 | # |
||
1536 | do_execsql_test pager1-13.1.1 { |
||
1537 | PRAGMA page_size = 1024; |
||
1538 | PRAGMA journal_mode = PERSIST; |
||
1539 | PRAGMA cache_size = 10; |
||
1540 | BEGIN; |
||
1541 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB); |
||
1542 | INSERT INTO t1 VALUES(NULL, a_string(400)); |
||
1543 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */ |
||
1544 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */ |
||
1545 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */ |
||
1546 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */ |
||
1547 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */ |
||
1548 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */ |
||
1549 | INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */ |
||
1550 | COMMIT; |
||
1551 | UPDATE t1 SET b = a_string(400); |
||
1552 | } {persist} |
||
1553 | |||
1554 | if {$::tcl_platform(platform)!="windows"} { |
||
1555 | # Run transactions of increasing sizes. Eventually, one (or more than one) |
||
1556 | # of these will write just enough content that one of the old headers created |
||
1557 | # by the transaction in the block above lies immediately after the content |
||
1558 | # journalled by the current transaction. |
||
1559 | # |
||
1560 | for {set nUp 1} {$nUp<64} {incr nUp} { |
||
1561 | do_execsql_test pager1-13.1.2.$nUp.1 { |
||
1562 | UPDATE t1 SET b = a_string(399) WHERE a <= $nUp |
||
1563 | } {} |
||
1564 | do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok} |
||
1565 | |||
1566 | # Try to access the snapshot of the file-system. |
||
1567 | # |
||
1568 | sqlite3 db2 sv_test.db |
||
1569 | do_test pager1-13.1.2.$nUp.3 { |
||
1570 | execsql { SELECT sum(length(b)) FROM t1 } db2 |
||
1571 | } [expr {128*400 - ($nUp-1)}] |
||
1572 | do_test pager1-13.1.2.$nUp.4 { |
||
1573 | execsql { PRAGMA integrity_check } db2 |
||
1574 | } {ok} |
||
1575 | db2 close |
||
1576 | } |
||
1577 | } |
||
1578 | |||
1579 | if {$::tcl_platform(platform)!="windows"} { |
||
1580 | # Same test as above. But this time with an index on the table. |
||
1581 | # |
||
1582 | do_execsql_test pager1-13.2.1 { |
||
1583 | CREATE INDEX i1 ON t1(b); |
||
1584 | UPDATE t1 SET b = a_string(400); |
||
1585 | } {} |
||
1586 | for {set nUp 1} {$nUp<64} {incr nUp} { |
||
1587 | do_execsql_test pager1-13.2.2.$nUp.1 { |
||
1588 | UPDATE t1 SET b = a_string(399) WHERE a <= $nUp |
||
1589 | } {} |
||
1590 | do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok} |
||
1591 | sqlite3 db2 sv_test.db |
||
1592 | do_test pager1-13.2.2.$nUp.3 { |
||
1593 | execsql { SELECT sum(length(b)) FROM t1 } db2 |
||
1594 | } [expr {128*400 - ($nUp-1)}] |
||
1595 | do_test pager1-13.2.2.$nUp.4 { |
||
1596 | execsql { PRAGMA integrity_check } db2 |
||
1597 | } {ok} |
||
1598 | db2 close |
||
1599 | } |
||
1600 | } |
||
1601 | |||
1602 | db close |
||
1603 | tv delete |
||
1604 | |||
1605 | #------------------------------------------------------------------------- |
||
1606 | # Test specal "PRAGMA journal_mode=OFF" test cases. |
||
1607 | # |
||
1608 | faultsim_delete_and_reopen |
||
1609 | do_execsql_test pager1-14.1.1 { |
||
1610 | PRAGMA journal_mode = OFF; |
||
1611 | CREATE TABLE t1(a, b); |
||
1612 | BEGIN; |
||
1613 | INSERT INTO t1 VALUES(1, 2); |
||
1614 | COMMIT; |
||
1615 | SELECT * FROM t1; |
||
1616 | } {off 1 2} |
||
1617 | do_catchsql_test pager1-14.1.2 { |
||
1618 | BEGIN; |
||
1619 | INSERT INTO t1 VALUES(3, 4); |
||
1620 | ROLLBACK; |
||
1621 | } {0 {}} |
||
1622 | do_execsql_test pager1-14.1.3 { |
||
1623 | SELECT * FROM t1; |
||
1624 | } {1 2} |
||
1625 | do_catchsql_test pager1-14.1.4 { |
||
1626 | BEGIN; |
||
1627 | INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; |
||
1628 | INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1; |
||
1629 | } {1 {PRIMARY KEY must be unique}} |
||
1630 | do_execsql_test pager1-14.1.5 { |
||
1631 | COMMIT; |
||
1632 | SELECT * FROM t1; |
||
1633 | } {1 2 2 2} |
||
1634 | |||
1635 | #------------------------------------------------------------------------- |
||
1636 | # Test opening and closing the pager sub-system with different values |
||
1637 | # for the sqlite3_vfs.szOsFile variable. |
||
1638 | # |
||
1639 | faultsim_delete_and_reopen |
||
1640 | do_execsql_test pager1-15.0 { |
||
1641 | CREATE TABLE tx(y, z); |
||
1642 | INSERT INTO tx VALUES('Ayutthaya', 'Beijing'); |
||
1643 | INSERT INTO tx VALUES('London', 'Tokyo'); |
||
1644 | } {} |
||
1645 | db close |
||
1646 | for {set i 0} {$i<513} {incr i 3} { |
||
1647 | testvfs tv -default 1 -szosfile $i |
||
1648 | sqlite3 db test.db |
||
1649 | do_execsql_test pager1-15.$i.1 { |
||
1650 | SELECT * FROM tx; |
||
1651 | } {Ayutthaya Beijing London Tokyo} |
||
1652 | db close |
||
1653 | tv delete |
||
1654 | } |
||
1655 | |||
1656 | #------------------------------------------------------------------------- |
||
1657 | # Check that it is not possible to open a database file if the full path |
||
1658 | # to the associated journal file will be longer than sqlite3_vfs.mxPathname. |
||
1659 | # |
||
1660 | testvfs tv -default 1 |
||
1661 | tv script xOpenCb |
||
1662 | tv filter xOpen |
||
1663 | proc xOpenCb {method filename args} { |
||
1664 | set ::file_len [string length $filename] |
||
1665 | } |
||
1666 | sqlite3 db test.db |
||
1667 | db close |
||
1668 | tv delete |
||
1669 | |||
1670 | for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} { |
||
1671 | testvfs tv -default 1 -mxpathname $ii |
||
1672 | |||
1673 | # The length of the full path to file "test.db-journal" is ($::file_len+8). |
||
1674 | # If the configured sqlite3_vfs.mxPathname value greater than or equal to |
||
1675 | # this, then the file can be opened. Otherwise, it cannot. |
||
1676 | # |
||
1677 | if {$ii >= [expr $::file_len+8]} { |
||
1678 | set res {0 {}} |
||
1679 | } else { |
||
1680 | set res {1 {unable to open database file}} |
||
1681 | } |
||
1682 | |||
1683 | do_test pager1-16.1.$ii { |
||
1684 | list [catch { sqlite3 db test.db } msg] $msg |
||
1685 | } $res |
||
1686 | |||
1687 | catch {db close} |
||
1688 | tv delete |
||
1689 | } |
||
1690 | |||
1691 | #------------------------------------------------------------------------- |
||
1692 | # Test "PRAGMA omit_readlock". |
||
1693 | # |
||
1694 | # pager1-17.$tn.1.*: Test that if a second connection has an open |
||
1695 | # read-transaction, it is not usually possible to write |
||
1696 | # the database. |
||
1697 | # |
||
1698 | # pager1-17.$tn.2.*: Test that if the second connection was opened with |
||
1699 | # the SQLITE_OPEN_READONLY flag, and |
||
1700 | # "PRAGMA omit_readlock = 1" is executed before attaching |
||
1701 | # the database and opening a read-transaction on it, it is |
||
1702 | # possible to write the db. |
||
1703 | # |
||
1704 | # pager1-17.$tn.3.*: Test that if the second connection was *not* opened with |
||
1705 | # the SQLITE_OPEN_READONLY flag, executing |
||
1706 | # "PRAGMA omit_readlock = 1" has no effect. |
||
1707 | # |
||
1708 | do_multiclient_test tn { |
||
1709 | do_test pager1-17.$tn.1.1 { |
||
1710 | sql1 { |
||
1711 | CREATE TABLE t1(a, b); |
||
1712 | INSERT INTO t1 VALUES(1, 2); |
||
1713 | } |
||
1714 | sql2 { |
||
1715 | BEGIN; |
||
1716 | SELECT * FROM t1; |
||
1717 | } |
||
1718 | } {1 2} |
||
1719 | do_test pager1-17.$tn.1.2 { |
||
1720 | csql1 { INSERT INTO t1 VALUES(3, 4) } |
||
1721 | } {1 {database is locked}} |
||
1722 | do_test pager1-17.$tn.1.3 { |
||
1723 | sql2 { COMMIT } |
||
1724 | sql1 { INSERT INTO t1 VALUES(3, 4) } |
||
1725 | } {} |
||
1726 | |||
1727 | do_test pager1-17.$tn.2.1 { |
||
1728 | code2 { |
||
1729 | db2 close |
||
1730 | sqlite3 db2 :memory: -readonly 1 |
||
1731 | } |
||
1732 | sql2 { |
||
1733 | PRAGMA omit_readlock = 1; |
||
1734 | ATTACH 'test.db' AS two; |
||
1735 | BEGIN; |
||
1736 | SELECT * FROM t1; |
||
1737 | } |
||
1738 | } {1 2 3 4} |
||
1739 | do_test pager1-17.$tn.2.2 { sql1 "INSERT INTO t1 VALUES(5, 6)" } {} |
||
1740 | do_test pager1-17.$tn.2.3 { sql2 "SELECT * FROM t1" } {1 2 3 4} |
||
1741 | do_test pager1-17.$tn.2.4 { sql2 "COMMIT ; SELECT * FROM t1" } {1 2 3 4 5 6} |
||
1742 | |||
1743 | do_test pager1-17.$tn.3.1 { |
||
1744 | code2 { |
||
1745 | db2 close |
||
1746 | sqlite3 db2 :memory: |
||
1747 | } |
||
1748 | sql2 { |
||
1749 | PRAGMA omit_readlock = 1; |
||
1750 | ATTACH 'test.db' AS two; |
||
1751 | BEGIN; |
||
1752 | SELECT * FROM t1; |
||
1753 | } |
||
1754 | } {1 2 3 4 5 6} |
||
1755 | do_test pager1-17.$tn.3.2 { |
||
1756 | csql1 { INSERT INTO t1 VALUES(3, 4) } |
||
1757 | } {1 {database is locked}} |
||
1758 | do_test pager1-17.$tn.3.3 { sql2 COMMIT } {} |
||
1759 | } |
||
1760 | |||
1761 | #------------------------------------------------------------------------- |
||
1762 | # Test the pagers response to the b-tree layer requesting illegal page |
||
1763 | # numbers: |
||
1764 | # |
||
1765 | # + The locking page, |
||
1766 | # + Page 0, |
||
1767 | # + A page with a page number greater than (2^31-1). |
||
1768 | # |
||
1769 | do_test pager1-18.1 { |
||
1770 | faultsim_delete_and_reopen |
||
1771 | db func a_string a_string |
||
1772 | execsql { |
||
1773 | PRAGMA page_size = 1024; |
||
1774 | CREATE TABLE t1(a, b); |
||
1775 | INSERT INTO t1 VALUES(a_string(500), a_string(200)); |
||
1776 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1777 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1778 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1779 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1780 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1781 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1782 | INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1; |
||
1783 | } |
||
1784 | } {} |
||
1785 | do_test pager1-18.2 { |
||
1786 | set root [db one "SELECT rootpage FROM sqlite_master"] |
||
1787 | set lockingpage [expr (0x10000/1024) + 1] |
||
1788 | execsql { |
||
1789 | PRAGMA writable_schema = 1; |
||
1790 | UPDATE sqlite_master SET rootpage = $lockingpage; |
||
1791 | } |
||
1792 | sqlite3 db2 test.db |
||
1793 | catchsql { SELECT count(*) FROM t1 } db2 |
||
1794 | } {1 {database disk image is malformed}} |
||
1795 | db2 close |
||
1796 | do_test pager1-18.3 { |
||
1797 | execsql { |
||
1798 | CREATE TABLE t2(x); |
||
1799 | INSERT INTO t2 VALUES(a_string(5000)); |
||
1800 | } |
||
1801 | set pgno [expr ([file size test.db] / 1024)-2] |
||
1802 | hexio_write test.db [expr ($pgno-1)*1024] 00000000 |
||
1803 | sqlite3 db2 test.db |
||
1804 | catchsql { SELECT length(x) FROM t2 } db2 |
||
1805 | } {1 {database disk image is malformed}} |
||
1806 | db2 close |
||
1807 | do_test pager1-18.4 { |
||
1808 | hexio_write test.db [expr ($pgno-1)*1024] 90000000 |
||
1809 | sqlite3 db2 test.db |
||
1810 | catchsql { SELECT length(x) FROM t2 } db2 |
||
1811 | } {1 {database disk image is malformed}} |
||
1812 | db2 close |
||
1813 | do_test pager1-18.5 { |
||
1814 | sqlite3 db "" |
||
1815 | execsql { |
||
1816 | CREATE TABLE t1(a, b); |
||
1817 | CREATE TABLE t2(a, b); |
||
1818 | PRAGMA writable_schema = 1; |
||
1819 | UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1'; |
||
1820 | PRAGMA writable_schema = 0; |
||
1821 | ALTER TABLE t1 RENAME TO x1; |
||
1822 | } |
||
1823 | catchsql { SELECT * FROM x1 } |
||
1824 | } {1 {database disk image is malformed}} |
||
1825 | db close |
||
1826 | |||
1827 | do_test pager1-18.6 { |
||
1828 | faultsim_delete_and_reopen |
||
1829 | db func a_string a_string |
||
1830 | execsql { |
||
1831 | PRAGMA page_size = 1024; |
||
1832 | CREATE TABLE t1(x); |
||
1833 | INSERT INTO t1 VALUES(a_string(800)); |
||
1834 | INSERT INTO t1 VALUES(a_string(800)); |
||
1835 | } |
||
1836 | |||
1837 | set root [db one "SELECT rootpage FROM sqlite_master"] |
||
1838 | db close |
||
1839 | |||
1840 | hexio_write test.db [expr ($root-1)*1024 + 8] 00000000 |
||
1841 | sqlite3 db test.db |
||
1842 | catchsql { SELECT length(x) FROM t1 } |
||
1843 | } {1 {database disk image is malformed}} |
||
1844 | |||
1845 | do_test pager1-19.1 { |
||
1846 | sqlite3 db "" |
||
1847 | db func a_string a_string |
||
1848 | execsql { |
||
1849 | PRAGMA page_size = 512; |
||
1850 | PRAGMA auto_vacuum = 1; |
||
1851 | CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, |
||
1852 | ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, |
||
1853 | ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, |
||
1854 | da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, |
||
1855 | ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, |
||
1856 | fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, |
||
1857 | ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, |
||
1858 | ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, |
||
1859 | ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, |
||
1860 | ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, |
||
1861 | ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, |
||
1862 | la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, |
||
1863 | ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn |
||
1864 | ); |
||
1865 | CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an, |
||
1866 | ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn, |
||
1867 | ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn, |
||
1868 | da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn, |
||
1869 | ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en, |
||
1870 | fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn, |
||
1871 | ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn, |
||
1872 | ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn, |
||
1873 | ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix, |
||
1874 | ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn, |
||
1875 | ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn, |
||
1876 | la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln, |
||
1877 | ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn |
||
1878 | ); |
||
1879 | INSERT INTO t1(aa) VALUES( a_string(100000) ); |
||
1880 | INSERT INTO t2(aa) VALUES( a_string(100000) ); |
||
1881 | VACUUM; |
||
1882 | } |
||
1883 | } {} |
||
1884 | |||
1885 | #------------------------------------------------------------------------- |
||
1886 | # Test a couple of special cases that come up while committing |
||
1887 | # transactions: |
||
1888 | # |
||
1889 | # pager1-20.1.*: Committing an in-memory database transaction when the |
||
1890 | # database has not been modified at all. |
||
1891 | # |
||
1892 | # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode. |
||
1893 | # |
||
1894 | # pager1-20.3.*: Committing a transaction in WAL mode where the database has |
||
1895 | # been modified, but all dirty pages have been flushed to |
||
1896 | # disk before the commit. |
||
1897 | # |
||
1898 | do_test pager1-20.1.1 { |
||
1899 | catch {db close} |
||
1900 | sqlite3 db :memory: |
||
1901 | execsql { |
||
1902 | CREATE TABLE one(two, three); |
||
1903 | INSERT INTO one VALUES('a', 'b'); |
||
1904 | } |
||
1905 | } {} |
||
1906 | do_test pager1-20.1.2 { |
||
1907 | execsql { |
||
1908 | BEGIN EXCLUSIVE; |
||
1909 | COMMIT; |
||
1910 | } |
||
1911 | } {} |
||
1912 | |||
1913 | do_test pager1-20.2.1 { |
||
1914 | faultsim_delete_and_reopen |
||
1915 | execsql { |
||
1916 | PRAGMA locking_mode = exclusive; |
||
1917 | PRAGMA journal_mode = persist; |
||
1918 | CREATE TABLE one(two, three); |
||
1919 | INSERT INTO one VALUES('a', 'b'); |
||
1920 | } |
||
1921 | } {exclusive persist} |
||
1922 | do_test pager1-20.2.2 { |
||
1923 | execsql { |
||
1924 | BEGIN EXCLUSIVE; |
||
1925 | COMMIT; |
||
1926 | } |
||
1927 | } {} |
||
1928 | |||
1929 | ifcapable wal { |
||
1930 | do_test pager1-20.3.1 { |
||
1931 | faultsim_delete_and_reopen |
||
1932 | db func a_string a_string |
||
1933 | execsql { |
||
1934 | PRAGMA cache_size = 10; |
||
1935 | PRAGMA journal_mode = wal; |
||
1936 | BEGIN; |
||
1937 | CREATE TABLE t1(x); |
||
1938 | CREATE TABLE t2(y); |
||
1939 | INSERT INTO t1 VALUES(a_string(800)); |
||
1940 | INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */ |
||
1941 | INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */ |
||
1942 | INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */ |
||
1943 | INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */ |
||
1944 | INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */ |
||
1945 | COMMIT; |
||
1946 | } |
||
1947 | } {wal} |
||
1948 | do_test pager1-20.3.2 { |
||
1949 | execsql { |
||
1950 | BEGIN; |
||
1951 | INSERT INTO t2 VALUES('xxxx'); |
||
1952 | } |
||
1953 | recursive_select 32 t1 |
||
1954 | execsql COMMIT |
||
1955 | } {} |
||
1956 | } |
||
1957 | |||
1958 | #------------------------------------------------------------------------- |
||
1959 | # Test that a WAL database may not be opened if: |
||
1960 | # |
||
1961 | # pager1-21.1.*: The VFS has an iVersion less than 2, or |
||
1962 | # pager1-21.2.*: The VFS does not provide xShmXXX() methods. |
||
1963 | # |
||
1964 | ifcapable wal { |
||
1965 | do_test pager1-21.0 { |
||
1966 | faultsim_delete_and_reopen |
||
1967 | execsql { |
||
1968 | PRAGMA journal_mode = WAL; |
||
1969 | CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); |
||
1970 | INSERT INTO ko DEFAULT VALUES; |
||
1971 | } |
||
1972 | } {wal} |
||
1973 | do_test pager1-21.1 { |
||
1974 | testvfs tv -noshm 1 |
||
1975 | sqlite3 db2 test.db -vfs tv |
||
1976 | catchsql { SELECT * FROM ko } db2 |
||
1977 | } {1 {unable to open database file}} |
||
1978 | db2 close |
||
1979 | tv delete |
||
1980 | do_test pager1-21.2 { |
||
1981 | testvfs tv -iversion 1 |
||
1982 | sqlite3 db2 test.db -vfs tv |
||
1983 | catchsql { SELECT * FROM ko } db2 |
||
1984 | } {1 {unable to open database file}} |
||
1985 | db2 close |
||
1986 | tv delete |
||
1987 | } |
||
1988 | |||
1989 | #------------------------------------------------------------------------- |
||
1990 | # Test that a "PRAGMA wal_checkpoint": |
||
1991 | # |
||
1992 | # pager1-22.1.*: is a no-op on a non-WAL db, and |
||
1993 | # pager1-22.2.*: does not cause xSync calls with a synchronous=off db. |
||
1994 | # |
||
1995 | do_test pager1-22.1.1 { |
||
1996 | faultsim_delete_and_reopen |
||
1997 | execsql { |
||
1998 | CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def'); |
||
1999 | INSERT INTO ko DEFAULT VALUES; |
||
2000 | } |
||
2001 | execsql { PRAGMA wal_checkpoint } |
||
2002 | } {0 -1 -1} |
||
2003 | do_test pager1-22.2.1 { |
||
2004 | testvfs tv -default 1 |
||
2005 | tv filter xSync |
||
2006 | tv script xSyncCb |
||
2007 | proc xSyncCb {args} {incr ::synccount} |
||
2008 | set ::synccount 0 |
||
2009 | sqlite3 db test.db |
||
2010 | execsql { |
||
2011 | PRAGMA synchronous = off; |
||
2012 | PRAGMA journal_mode = WAL; |
||
2013 | INSERT INTO ko DEFAULT VALUES; |
||
2014 | } |
||
2015 | execsql { PRAGMA wal_checkpoint } |
||
2016 | set synccount |
||
2017 | } {0} |
||
2018 | db close |
||
2019 | tv delete |
||
2020 | |||
2021 | #------------------------------------------------------------------------- |
||
2022 | # Tests for changing journal mode. |
||
2023 | # |
||
2024 | # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode, |
||
2025 | # the journal file is deleted. |
||
2026 | # |
||
2027 | # pager1-23.2.*: Same test as above, but while a shared lock is held |
||
2028 | # on the database file. |
||
2029 | # |
||
2030 | # pager1-23.3.*: Same test as above, but while a reserved lock is held |
||
2031 | # on the database file. |
||
2032 | # |
||
2033 | # pager1-23.4.*: And, for fun, while holding an exclusive lock. |
||
2034 | # |
||
2035 | # pager1-23.5.*: Try to set various different journal modes with an |
||
2036 | # in-memory database (only MEMORY and OFF should work). |
||
2037 | # |
||
2038 | # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database |
||
2039 | # (doesn't work - in-memory databases always use |
||
2040 | # locking_mode=exclusive). |
||
2041 | # |
||
2042 | do_test pager1-23.1.1 { |
||
2043 | faultsim_delete_and_reopen |
||
2044 | execsql { |
||
2045 | PRAGMA journal_mode = PERSIST; |
||
2046 | CREATE TABLE t1(a, b); |
||
2047 | } |
||
2048 | file exists test.db-journal |
||
2049 | } {1} |
||
2050 | do_test pager1-23.1.2 { |
||
2051 | execsql { PRAGMA journal_mode = DELETE } |
||
2052 | file exists test.db-journal |
||
2053 | } {0} |
||
2054 | |||
2055 | do_test pager1-23.2.1 { |
||
2056 | execsql { |
||
2057 | PRAGMA journal_mode = PERSIST; |
||
2058 | INSERT INTO t1 VALUES('Canberra', 'ACT'); |
||
2059 | } |
||
2060 | db eval { SELECT * FROM t1 } { |
||
2061 | db eval { PRAGMA journal_mode = DELETE } |
||
2062 | } |
||
2063 | execsql { PRAGMA journal_mode } |
||
2064 | } {delete} |
||
2065 | do_test pager1-23.2.2 { |
||
2066 | file exists test.db-journal |
||
2067 | } {0} |
||
2068 | |||
2069 | do_test pager1-23.3.1 { |
||
2070 | execsql { |
||
2071 | PRAGMA journal_mode = PERSIST; |
||
2072 | INSERT INTO t1 VALUES('Darwin', 'NT'); |
||
2073 | BEGIN IMMEDIATE; |
||
2074 | } |
||
2075 | db eval { PRAGMA journal_mode = DELETE } |
||
2076 | execsql { PRAGMA journal_mode } |
||
2077 | } {delete} |
||
2078 | do_test pager1-23.3.2 { |
||
2079 | file exists test.db-journal |
||
2080 | } {0} |
||
2081 | do_test pager1-23.3.3 { |
||
2082 | execsql COMMIT |
||
2083 | } {} |
||
2084 | |||
2085 | do_test pager1-23.4.1 { |
||
2086 | execsql { |
||
2087 | PRAGMA journal_mode = PERSIST; |
||
2088 | INSERT INTO t1 VALUES('Adelaide', 'SA'); |
||
2089 | BEGIN EXCLUSIVE; |
||
2090 | } |
||
2091 | db eval { PRAGMA journal_mode = DELETE } |
||
2092 | execsql { PRAGMA journal_mode } |
||
2093 | } {delete} |
||
2094 | do_test pager1-23.4.2 { |
||
2095 | file exists test.db-journal |
||
2096 | } {0} |
||
2097 | do_test pager1-23.4.3 { |
||
2098 | execsql COMMIT |
||
2099 | } {} |
||
2100 | |||
2101 | do_test pager1-23.5.1 { |
||
2102 | faultsim_delete_and_reopen |
||
2103 | sqlite3 db :memory: |
||
2104 | } {} |
||
2105 | foreach {tn mode possible} { |
||
2106 | 2 off 1 |
||
2107 | 3 memory 1 |
||
2108 | 4 persist 0 |
||
2109 | 5 delete 0 |
||
2110 | 6 wal 0 |
||
2111 | 7 truncate 0 |
||
2112 | } { |
||
2113 | do_test pager1-23.5.$tn.1 { |
||
2114 | execsql "PRAGMA journal_mode = off" |
||
2115 | execsql "PRAGMA journal_mode = $mode" |
||
2116 | } [if $possible {list $mode} {list off}] |
||
2117 | do_test pager1-23.5.$tn.2 { |
||
2118 | execsql "PRAGMA journal_mode = memory" |
||
2119 | execsql "PRAGMA journal_mode = $mode" |
||
2120 | } [if $possible {list $mode} {list memory}] |
||
2121 | } |
||
2122 | do_test pager1-23.6.1 { |
||
2123 | execsql {PRAGMA locking_mode = normal} |
||
2124 | } {exclusive} |
||
2125 | do_test pager1-23.6.2 { |
||
2126 | execsql {PRAGMA locking_mode = exclusive} |
||
2127 | } {exclusive} |
||
2128 | do_test pager1-23.6.3 { |
||
2129 | execsql {PRAGMA locking_mode} |
||
2130 | } {exclusive} |
||
2131 | do_test pager1-23.6.4 { |
||
2132 | execsql {PRAGMA main.locking_mode} |
||
2133 | } {exclusive} |
||
2134 | |||
2135 | #------------------------------------------------------------------------- |
||
2136 | # |
||
2137 | do_test pager1-24.1.1 { |
||
2138 | faultsim_delete_and_reopen |
||
2139 | db func a_string a_string |
||
2140 | execsql { |
||
2141 | PRAGMA cache_size = 10; |
||
2142 | PRAGMA auto_vacuum = FULL; |
||
2143 | CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z)); |
||
2144 | CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z)); |
||
2145 | INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600)); |
||
2146 | INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; |
||
2147 | INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; |
||
2148 | INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; |
||
2149 | INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2; |
||
2150 | INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2; |
||
2151 | INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2; |
||
2152 | INSERT INTO x1 SELECT * FROM x2; |
||
2153 | } |
||
2154 | } {} |
||
2155 | do_test pager1-24.1.2 { |
||
2156 | execsql { |
||
2157 | BEGIN; |
||
2158 | DELETE FROM x1 WHERE rowid<32; |
||
2159 | } |
||
2160 | recursive_select 64 x2 |
||
2161 | } {} |
||
2162 | do_test pager1-24.1.3 { |
||
2163 | execsql { |
||
2164 | UPDATE x1 SET z = a_string(300) WHERE rowid>40; |
||
2165 | COMMIT; |
||
2166 | PRAGMA integrity_check; |
||
2167 | SELECT count(*) FROM x1; |
||
2168 | } |
||
2169 | } {ok 33} |
||
2170 | |||
2171 | do_test pager1-24.1.4 { |
||
2172 | execsql { |
||
2173 | DELETE FROM x1; |
||
2174 | INSERT INTO x1 SELECT * FROM x2; |
||
2175 | BEGIN; |
||
2176 | DELETE FROM x1 WHERE rowid<32; |
||
2177 | UPDATE x1 SET z = a_string(299) WHERE rowid>40; |
||
2178 | } |
||
2179 | recursive_select 64 x2 {db eval COMMIT} |
||
2180 | execsql { |
||
2181 | PRAGMA integrity_check; |
||
2182 | SELECT count(*) FROM x1; |
||
2183 | } |
||
2184 | } {ok 33} |
||
2185 | |||
2186 | do_test pager1-24.1.5 { |
||
2187 | execsql { |
||
2188 | DELETE FROM x1; |
||
2189 | INSERT INTO x1 SELECT * FROM x2; |
||
2190 | } |
||
2191 | recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} } |
||
2192 | execsql { SELECT * FROM x3 } |
||
2193 | } {} |
||
2194 | |||
2195 | #------------------------------------------------------------------------- |
||
2196 | # |
||
2197 | do_test pager1-25-1 { |
||
2198 | faultsim_delete_and_reopen |
||
2199 | execsql { |
||
2200 | BEGIN; |
||
2201 | SAVEPOINT abc; |
||
2202 | CREATE TABLE t1(a, b); |
||
2203 | ROLLBACK TO abc; |
||
2204 | COMMIT; |
||
2205 | } |
||
2206 | db close |
||
2207 | } {} |
||
2208 | breakpoint |
||
2209 | do_test pager1-25-2 { |
||
2210 | faultsim_delete_and_reopen |
||
2211 | execsql { |
||
2212 | SAVEPOINT abc; |
||
2213 | CREATE TABLE t1(a, b); |
||
2214 | ROLLBACK TO abc; |
||
2215 | COMMIT; |
||
2216 | } |
||
2217 | db close |
||
2218 | } {} |
||
2219 | |||
2220 | #------------------------------------------------------------------------- |
||
2221 | # Sector-size tests. |
||
2222 | # |
||
2223 | do_test pager1-26.1 { |
||
2224 | testvfs tv -default 1 |
||
2225 | tv sectorsize 4096 |
||
2226 | faultsim_delete_and_reopen |
||
2227 | db func a_string a_string |
||
2228 | execsql { |
||
2229 | PRAGMA page_size = 512; |
||
2230 | CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE); |
||
2231 | BEGIN; |
||
2232 | INSERT INTO tbl VALUES(a_string(25), a_string(600)); |
||
2233 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2234 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2235 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2236 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2237 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2238 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2239 | INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl; |
||
2240 | COMMIT; |
||
2241 | } |
||
2242 | } {} |
||
2243 | do_execsql_test pager1-26.1 { |
||
2244 | UPDATE tbl SET b = a_string(550); |
||
2245 | } {} |
||
2246 | db close |
||
2247 | tv delete |
||
2248 | |||
2249 | #------------------------------------------------------------------------- |
||
2250 | # |
||
2251 | do_test pager1.27.1 { |
||
2252 | faultsim_delete_and_reopen |
||
2253 | sqlite3_pager_refcounts db |
||
2254 | execsql { |
||
2255 | BEGIN; |
||
2256 | CREATE TABLE t1(a, b); |
||
2257 | } |
||
2258 | sqlite3_pager_refcounts db |
||
2259 | execsql COMMIT |
||
2260 | } {} |
||
2261 | |||
2262 | #------------------------------------------------------------------------- |
||
2263 | # Test that attempting to open a write-transaction with |
||
2264 | # locking_mode=exclusive in WAL mode fails if there are other clients on |
||
2265 | # the same database. |
||
2266 | # |
||
2267 | catch { db close } |
||
2268 | ifcapable wal { |
||
2269 | do_multiclient_test tn { |
||
2270 | do_test pager1-28.$tn.1 { |
||
2271 | sql1 { |
||
2272 | PRAGMA journal_mode = WAL; |
||
2273 | CREATE TABLE t1(a, b); |
||
2274 | INSERT INTO t1 VALUES('a', 'b'); |
||
2275 | } |
||
2276 | } {wal} |
||
2277 | do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b} |
||
2278 | |||
2279 | do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive} |
||
2280 | do_test pager1-28.$tn.4 { |
||
2281 | csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); } |
||
2282 | } {1 {database is locked}} |
||
2283 | code2 { db2 close ; sqlite3 db2 test.db } |
||
2284 | do_test pager1-28.$tn.4 { |
||
2285 | sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT } |
||
2286 | } {} |
||
2287 | } |
||
2288 | } |
||
2289 | |||
2290 | #------------------------------------------------------------------------- |
||
2291 | # Normally, when changing from journal_mode=PERSIST to DELETE the pager |
||
2292 | # attempts to delete the journal file. However, if it cannot obtain a |
||
2293 | # RESERVED lock on the database file, this step is skipped. |
||
2294 | # |
||
2295 | do_multiclient_test tn { |
||
2296 | do_test pager1-28.$tn.1 { |
||
2297 | sql1 { |
||
2298 | PRAGMA journal_mode = PERSIST; |
||
2299 | CREATE TABLE t1(a, b); |
||
2300 | INSERT INTO t1 VALUES('a', 'b'); |
||
2301 | } |
||
2302 | } {persist} |
||
2303 | do_test pager1-28.$tn.2 { file exists test.db-journal } 1 |
||
2304 | do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete |
||
2305 | do_test pager1-28.$tn.4 { file exists test.db-journal } 0 |
||
2306 | |||
2307 | do_test pager1-28.$tn.5 { |
||
2308 | sql1 { |
||
2309 | PRAGMA journal_mode = PERSIST; |
||
2310 | INSERT INTO t1 VALUES('c', 'd'); |
||
2311 | } |
||
2312 | } {persist} |
||
2313 | do_test pager1-28.$tn.6 { file exists test.db-journal } 1 |
||
2314 | do_test pager1-28.$tn.7 { |
||
2315 | sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } |
||
2316 | } {} |
||
2317 | do_test pager1-28.$tn.8 { file exists test.db-journal } 1 |
||
2318 | do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete |
||
2319 | do_test pager1-28.$tn.10 { file exists test.db-journal } 1 |
||
2320 | |||
2321 | do_test pager1-28.$tn.11 { sql2 COMMIT } {} |
||
2322 | do_test pager1-28.$tn.12 { file exists test.db-journal } 0 |
||
2323 | |||
2324 | do_test pager1-28-$tn.13 { |
||
2325 | code1 { set channel [db incrblob -readonly t1 a 2] } |
||
2326 | sql1 { |
||
2327 | PRAGMA journal_mode = PERSIST; |
||
2328 | INSERT INTO t1 VALUES('g', 'h'); |
||
2329 | } |
||
2330 | } {persist} |
||
2331 | do_test pager1-28.$tn.14 { file exists test.db-journal } 1 |
||
2332 | do_test pager1-28.$tn.15 { |
||
2333 | sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); } |
||
2334 | } {} |
||
2335 | do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete |
||
2336 | do_test pager1-28.$tn.17 { file exists test.db-journal } 1 |
||
2337 | |||
2338 | do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}} |
||
2339 | do_test pager1-28-$tn.18 { code1 { read $channel } } c |
||
2340 | do_test pager1-28-$tn.19 { code1 { close $channel } } {} |
||
2341 | do_test pager1-28.$tn.20 { sql2 { COMMIT } } {} |
||
2342 | } |
||
2343 | |||
2344 | do_test pager1-29.1 { |
||
2345 | faultsim_delete_and_reopen |
||
2346 | execsql { |
||
2347 | PRAGMA page_size = 1024; |
||
2348 | PRAGMA auto_vacuum = full; |
||
2349 | PRAGMA locking_mode=exclusive; |
||
2350 | CREATE TABLE t1(a, b); |
||
2351 | INSERT INTO t1 VALUES(1, 2); |
||
2352 | } |
||
2353 | file size test.db |
||
2354 | } [expr 1024*3] |
||
2355 | do_test pager1-29.2 { |
||
2356 | execsql { |
||
2357 | PRAGMA page_size = 4096; |
||
2358 | VACUUM; |
||
2359 | } |
||
2360 | file size test.db |
||
2361 | } [expr 4096*3] |
||
2362 | |||
2363 | #------------------------------------------------------------------------- |
||
2364 | # Test that if an empty database file (size 0 bytes) is opened in |
||
2365 | # exclusive-locking mode, any journal file is deleted from the file-system |
||
2366 | # without being rolled back. And that the RESERVED lock obtained while |
||
2367 | # doing this is not released. |
||
2368 | # |
||
2369 | do_test pager1-30.1 { |
||
2370 | db close |
||
2371 | file delete test.db |
||
2372 | file delete test.db-journal |
||
2373 | set fd [open test.db-journal w] |
||
2374 | seek $fd [expr 512+1032*2] |
||
2375 | puts -nonewline $fd x |
||
2376 | close $fd |
||
2377 | |||
2378 | sqlite3 db test.db |
||
2379 | execsql { |
||
2380 | PRAGMA locking_mode=EXCLUSIVE; |
||
2381 | SELECT count(*) FROM sqlite_master; |
||
2382 | PRAGMA lock_status; |
||
2383 | } |
||
2384 | } {exclusive 0 main reserved temp closed} |
||
2385 | |||
2386 | #------------------------------------------------------------------------- |
||
2387 | # Test that if the "page-size" field in a journal-header is 0, the journal |
||
2388 | # file can still be rolled back. This is required for backward compatibility - |
||
2389 | # versions of SQLite prior to 3.5.8 always set this field to zero. |
||
2390 | # |
||
2391 | if {$tcl_platform(platform)=="unix"} { |
||
2392 | do_test pager1-31.1 { |
||
2393 | faultsim_delete_and_reopen |
||
2394 | execsql { |
||
2395 | PRAGMA cache_size = 10; |
||
2396 | PRAGMA page_size = 1024; |
||
2397 | CREATE TABLE t1(x, y, UNIQUE(x, y)); |
||
2398 | INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500)); |
||
2399 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2400 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2401 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2402 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2403 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2404 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2405 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2406 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2407 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2408 | INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1; |
||
2409 | BEGIN; |
||
2410 | UPDATE t1 SET y = randomblob(1499); |
||
2411 | } |
||
2412 | file copy test.db test.db2 |
||
2413 | file copy test.db-journal test.db2-journal |
||
2414 | |||
2415 | hexio_write test.db2-journal 24 00000000 |
||
2416 | sqlite3 db2 test.db2 |
||
2417 | execsql { PRAGMA integrity_check } db2 |
||
2418 | } {ok} |
||
2419 | } |
||
2420 | |||
2421 | |||
2422 | finish_test |