Debian Codegraph @

test/pager1.test

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