3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
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
20 # Do not use a codec for tests in this file, as the database file is
21 # manipulated directly using tcl scripts (using the [hexio_write] command).
26 # pager1-1.*: Test inter-process locking (clients in multiple processes).
28 # pager1-2.*: Test intra-process locking (multiple clients in this process).
30 # pager1-3.*: Savepoint related tests.
32 # pager1-4.*: Hot-journal related tests.
34 # pager1-5.*: Cases related to multi-file commits.
36 # pager1-6.*: Cases related to "PRAGMA max_page_count"
38 # pager1-7.*: Cases specific to "PRAGMA journal_mode=TRUNCATE"
40 # pager1-8.*: Cases using temporary and in-memory databases.
42 # pager1-9.*: Tests related to the backup API.
44 # pager1-10.*: Test that the assumed file-system sector-size is limited to
47 # pager1-12.*: Tests involving "PRAGMA page_size"
49 # pager1-13.*: Cases specific to "PRAGMA journal_mode=PERSIST"
51 # pager1-14.*: Cases specific to "PRAGMA journal_mode=OFF"
53 # pager1-15.*: Varying sqlite3_vfs.szOsFile
55 # pager1-16.*: Varying sqlite3_vfs.mxPathname
57 # pager1-17.*: Tests related to "PRAGMA omit_readlock"
58 # (The omit_readlock pragma has been removed and so have
61 # pager1-18.*: Test that the pager layer responds correctly if the b-tree
62 # requests an invalid page number (due to db corruption).
65 proc recursive_select {id table {script {}}} {
67 db eval "SELECT rowid, * FROM $table WHERE rowid = ($id-1)" {
68 recursive_select $rowid $table $script
71 if {$cnt==0} { eval $script }
74 set a_string_counter 1
76 global a_string_counter
78 string range [string repeat "${a_string_counter}." $n] 1 $n
80 db func a_string a_string
82 do_multiclient_test tn {
84 # Create and populate a database table using connection [db]. Check
85 # that connections [db2] and [db3] can see the schema and content.
87 do_test pager1-$tn.1 {
89 CREATE TABLE t1(a PRIMARY KEY, b);
90 CREATE INDEX i1 ON t1(b);
91 INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two');
94 do_test pager1-$tn.2 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
95 do_test pager1-$tn.3 { sql3 { SELECT * FROM t1 } } {1 one 2 two}
97 # Open a transaction and add a row using [db]. This puts [db] in
98 # RESERVED state. Check that connections [db2] and [db3] can still
99 # read the database content as it was before the transaction was
100 # opened. [db] should see the inserted row.
102 do_test pager1-$tn.4 {
105 INSERT INTO t1 VALUES(3, 'three');
108 do_test pager1-$tn.5 { sql2 { SELECT * FROM t1 } } {1 one 2 two}
109 do_test pager1-$tn.7 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
111 # [db] still has an open write transaction. Check that this prevents
112 # other connections (specifically [db2]) from writing to the database.
114 # Even if [db2] opens a transaction first, it may not write to the
115 # database. After the attempt to write the db within a transaction,
116 # [db2] is left with an open transaction, but not a read-lock on
117 # the main database. So it does not prevent [db] from committing.
119 do_test pager1-$tn.8 {
120 csql2 { UPDATE t1 SET a = a + 10 }
121 } {1 {database is locked}}
122 do_test pager1-$tn.9 {
125 UPDATE t1 SET a = a + 10;
127 } {1 {database is locked}}
129 # Have [db] commit its transactions. Check the other connections can
130 # now see the new database content.
132 do_test pager1-$tn.10 { sql1 { COMMIT } } {}
133 do_test pager1-$tn.11 { sql1 { SELECT * FROM t1 } } {1 one 2 two 3 three}
134 do_test pager1-$tn.12 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
135 do_test pager1-$tn.13 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
137 # Check that, as noted above, [db2] really did keep an open transaction
138 # after the attempt to write the database failed.
140 do_test pager1-$tn.14 {
142 } {1 {cannot start a transaction within a transaction}}
143 do_test pager1-$tn.15 { sql2 { ROLLBACK } } {}
145 # Have [db2] open a transaction and take a read-lock on the database.
146 # Check that this prevents [db] from writing to the database (outside
147 # of any transaction). After this fails, check that [db3] can read
148 # the db (showing that [db] did not take a PENDING lock etc.)
150 do_test pager1-$tn.15 {
151 sql2 { BEGIN; SELECT * FROM t1; }
152 } {1 one 2 two 3 three}
153 do_test pager1-$tn.16 {
154 csql1 { UPDATE t1 SET a = a + 10 }
155 } {1 {database is locked}}
156 do_test pager1-$tn.17 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
158 # This time, have [db] open a transaction before writing the database.
159 # This works - [db] gets a RESERVED lock which does not conflict with
160 # the SHARED lock [db2] is holding.
162 do_test pager1-$tn.18 {
165 UPDATE t1 SET a = a + 10;
168 do_test pager1-$tn-19 {
169 sql1 { PRAGMA lock_status }
170 } {main reserved temp closed}
171 do_test pager1-$tn-20 {
172 sql2 { PRAGMA lock_status }
173 } {main shared temp closed}
175 # Check that all connections can still read the database. Only [db] sees
176 # the updated content (as the transaction has not been committed yet).
178 do_test pager1-$tn.21 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
179 do_test pager1-$tn.22 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
180 do_test pager1-$tn.23 { sql3 { SELECT * FROM t1 } } {1 one 2 two 3 three}
182 # Because [db2] still has the SHARED lock, [db] is unable to commit the
183 # transaction. If it tries, an error is returned and the connection
184 # upgrades to a PENDING lock.
186 # Once this happens, [db] can read the database and see the new content,
187 # [db2] (still holding SHARED) can still read the old content, but [db3]
188 # (not holding any lock) is prevented by [db]'s PENDING from reading
191 do_test pager1-$tn.24 { csql1 { COMMIT } } {1 {database is locked}}
192 do_test pager1-$tn-25 {
193 sql1 { PRAGMA lock_status }
194 } {main pending temp closed}
195 do_test pager1-$tn.26 { sql1 { SELECT * FROM t1 } } {11 one 12 two 13 three}
196 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {1 one 2 two 3 three}
197 do_test pager1-$tn.28 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
199 # Have [db2] commit its read transaction, releasing the SHARED lock it
200 # is holding. Now, neither [db2] nor [db3] may read the database (as [db]
201 # is still holding a PENDING).
203 do_test pager1-$tn.29 { sql2 { COMMIT } } {}
204 do_test pager1-$tn.30 { csql2 { SELECT * FROM t1 } } {1 {database is locked}}
205 do_test pager1-$tn.31 { csql3 { SELECT * FROM t1 } } {1 {database is locked}}
207 # [db] is now able to commit the transaction. Once the transaction is
208 # committed, all three connections can read the new content.
210 do_test pager1-$tn.25 { sql1 { UPDATE t1 SET a = a+10 } } {}
211 do_test pager1-$tn.26 { sql1 { COMMIT } } {}
212 do_test pager1-$tn.27 { sql1 { SELECT * FROM t1 } } {21 one 22 two 23 three}
213 do_test pager1-$tn.27 { sql2 { SELECT * FROM t1 } } {21 one 22 two 23 three}
214 do_test pager1-$tn.28 { sql3 { SELECT * FROM t1 } } {21 one 22 two 23 three}
216 # Install a busy-handler for connection [db].
221 if {$n>5} { sql2 COMMIT }
226 do_test pager1-$tn.29 {
227 sql1 { BEGIN ; INSERT INTO t1 VALUES('x', 'y') }
229 do_test pager1-$tn.30 {
230 sql2 { BEGIN ; SELECT * FROM t1 }
231 } {21 one 22 two 23 three}
232 do_test pager1-$tn.31 { sql1 COMMIT } {}
233 do_test pager1-$tn.32 { set ::nbusy } {0 1 2 3 4 5 6}
236 #-------------------------------------------------------------------------
237 # Savepoint related test cases.
239 # pager1-3.1.2.*: Force a savepoint rollback to cause the database file
242 # pager1-3.1.3.*: Use a journal created in synchronous=off mode as part
243 # of a savepoint rollback.
245 do_test pager1-3.1.1 {
246 faultsim_delete_and_reopen
248 CREATE TABLE t1(a PRIMARY KEY, b);
249 CREATE TABLE counter(
253 INSERT INTO counter VALUES(0, 0);
254 CREATE TRIGGER tr1 AFTER INSERT ON t1 BEGIN
255 UPDATE counter SET i = i+1;
257 CREATE TRIGGER tr2 AFTER UPDATE ON t1 BEGIN
258 UPDATE counter SET u = u+1;
261 execsql { SELECT * FROM counter }
264 do_execsql_test pager1-3.1.2 {
265 PRAGMA cache_size = 10;
267 INSERT INTO t1 VALUES(1, randomblob(1500));
268 INSERT INTO t1 VALUES(2, randomblob(1500));
269 INSERT INTO t1 VALUES(3, randomblob(1500));
270 SELECT * FROM counter;
272 do_catchsql_test pager1-3.1.3 {
273 INSERT INTO t1 SELECT a+3, randomblob(1500) FROM t1
274 } {1 {CHECK constraint failed: counter}}
275 do_execsql_test pager1-3.4 { SELECT * FROM counter } {3 0}
276 do_execsql_test pager1-3.5 { SELECT a FROM t1 } {1 2 3}
277 do_execsql_test pager1-3.6 { COMMIT } {}
279 foreach {tn sql tcl} {
280 7 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 0 } {
281 testvfs tv -default 1
282 tv devchar safe_append
284 8 { PRAGMA synchronous = NORMAL ; PRAGMA temp_store = 2 } {
285 testvfs tv -default 1
286 tv devchar sequential
288 9 { PRAGMA synchronous = FULL } { }
289 10 { PRAGMA synchronous = NORMAL } { }
290 11 { PRAGMA synchronous = OFF } { }
291 12 { PRAGMA synchronous = FULL ; PRAGMA fullfsync = 1 } { }
292 13 { PRAGMA synchronous = FULL } {
293 testvfs tv -default 1
294 tv devchar sequential
296 14 { PRAGMA locking_mode = EXCLUSIVE } {
299 do_test pager1-3.$tn.1 {
301 faultsim_delete_and_reopen
302 db func a_string a_string
305 PRAGMA auto_vacuum = 2;
306 PRAGMA cache_size = 10;
307 CREATE TABLE z(x INTEGER PRIMARY KEY, y);
309 INSERT INTO z VALUES(NULL, a_string(800));
310 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 2
311 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 4
312 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 8
313 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 16
314 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 32
315 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 64
316 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 128
317 INSERT INTO z SELECT NULL, a_string(800) FROM z; -- 256
320 execsql { PRAGMA auto_vacuum }
322 do_execsql_test pager1-3.$tn.2 {
324 INSERT INTO z VALUES(NULL, a_string(800));
325 INSERT INTO z VALUES(NULL, a_string(800));
327 UPDATE z SET y = NULL WHERE x>256;
328 PRAGMA incremental_vacuum;
329 SELECT count(*) FROM z WHERE x < 100;
334 do_execsql_test pager1-3.$tn.3 {
337 UPDATE z SET y = y||x;
340 SELECT count(*) FROM z;
343 do_execsql_test pager1-3.$tn.4 {
345 UPDATE z SET y = y||x;
348 do_execsql_test pager1-3.$tn.5 {
349 SELECT count(*) FROM z;
351 PRAGMA integrity_check;
354 do_execsql_test pager1-3.$tn.6 {
363 #-------------------------------------------------------------------------
364 # Hot journal rollback related test cases.
366 # pager1.4.1.*: Test that the pager module deletes very small invalid
369 # pager1.4.2.*: Test that if the master journal pointer at the end of a
370 # hot-journal file appears to be corrupt (checksum does not
371 # compute) the associated journal is rolled back (and no
372 # xAccess() call to check for the presence of any master
373 # journal file is made).
375 # pager1.4.3.*: Test that the contents of a hot-journal are ignored if the
376 # page-size or sector-size in the journal header appear to
377 # be invalid (too large, too small or not a power of 2).
379 # pager1.4.4.*: Test hot-journal rollback of journal file with a master
380 # journal pointer generated in various "PRAGMA synchronous"
383 # pager1.4.5.*: Test that hot-journal rollback stops if it encounters a
384 # journal-record for which the checksum fails.
386 # pager1.4.6.*: Test that when rolling back a hot-journal that contains a
387 # master journal pointer, the master journal file is deleted
388 # after all the hot-journals that refer to it are deleted.
390 # pager1.4.7.*: Test that if a hot-journal file exists but a client can
391 # open it for reading only, the database cannot be accessed and
392 # SQLITE_CANTOPEN is returned.
394 do_test pager1.4.1.1 {
395 faultsim_delete_and_reopen
397 CREATE TABLE x(y, z);
398 INSERT INTO x VALUES(1, 2);
400 set fd [open test.db-journal w]
401 puts -nonewline $fd "helloworld"
403 file exists test.db-journal
405 do_test pager1.4.1.2 { execsql { SELECT * FROM x } } {1 2}
406 do_test pager1.4.1.3 { file exists test.db-journal } {0}
408 # Set up a [testvfs] to snapshot the file-system just before SQLite
409 # deletes the master-journal to commit a multi-file transaction.
411 # In subsequent test cases, invoking [faultsim_restore_and_reopen] sets
412 # up the file system to contain two databases, two hot-journal files and
415 do_test pager1.4.2.1 {
416 testvfs tstvfs -default 1
417 tstvfs filter xDelete
418 tstvfs script xDeleteCallback
419 proc xDeleteCallback {method file args} {
420 set file [file tail $file]
421 if { [string match *mj* $file] } { faultsim_save }
423 faultsim_delete_and_reopen
424 db func a_string a_string
426 ATTACH 'test.db2' AS aux;
427 PRAGMA journal_mode = DELETE;
428 PRAGMA main.cache_size = 10;
429 PRAGMA aux.cache_size = 10;
430 CREATE TABLE t1(a UNIQUE, b UNIQUE);
431 CREATE TABLE aux.t2(a UNIQUE, b UNIQUE);
432 INSERT INTO t1 VALUES(a_string(200), a_string(300));
433 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
434 INSERT INTO t1 SELECT a_string(200), a_string(300) FROM t1;
435 INSERT INTO t2 SELECT * FROM t1;
437 INSERT INTO t1 SELECT a_string(201), a_string(301) FROM t1;
438 INSERT INTO t1 SELECT a_string(202), a_string(302) FROM t1;
439 INSERT INTO t1 SELECT a_string(203), a_string(303) FROM t1;
440 INSERT INTO t1 SELECT a_string(204), a_string(304) FROM t1;
441 REPLACE INTO t2 SELECT * FROM t1;
448 if {$::tcl_platform(platform)!="windows"} {
449 do_test pager1.4.2.2 {
450 faultsim_restore_and_reopen
452 SELECT count(*) FROM t1;
453 PRAGMA integrity_check;
456 do_test pager1.4.2.3 {
457 faultsim_restore_and_reopen
458 foreach f [glob test.db-mj*] { forcedelete $f }
460 SELECT count(*) FROM t1;
461 PRAGMA integrity_check;
464 do_test pager1.4.2.4 {
465 faultsim_restore_and_reopen
466 hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456
468 SELECT count(*) FROM t1;
469 PRAGMA integrity_check;
472 do_test pager1.4.2.5 {
473 faultsim_restore_and_reopen
474 hexio_write test.db-journal [expr [file size test.db-journal]-30] 123456
475 foreach f [glob test.db-mj*] { forcedelete $f }
477 SELECT count(*) FROM t1;
478 PRAGMA integrity_check;
483 do_test pager1.4.3.1 {
484 testvfs tstvfs -default 1
486 tstvfs script xSyncCallback
487 proc xSyncCallback {method file args} {
488 set file [file tail $file]
489 if { 0==[string match *journal $file] } { faultsim_save }
491 faultsim_delete_and_reopen
493 PRAGMA journal_mode = DELETE;
494 CREATE TABLE t1(a, b);
495 INSERT INTO t1 VALUES(1, 2);
496 INSERT INTO t1 VALUES(3, 4);
502 foreach {tn ofst value result} {
507 6 20 131072 {1 2 3 4}
511 9 24 131072 {1 2 3 4}
515 do_test pager1.4.3.$tn {
516 faultsim_restore_and_reopen
517 hexio_write test.db-journal $ofst [format %.8x $value]
518 execsql { SELECT * FROM t1 }
523 # Set up a VFS that snapshots the file-system just before a master journal
524 # file is deleted to commit a multi-file transaction. Specifically, the
525 # file-system is saved just before the xDelete() call to remove the
526 # master journal file from the file-system.
529 testvfs tv -default 1
530 tv script copy_on_mj_delete
531 set ::mj_filename_length 0
532 set ::mj_delete_cnt 0
533 proc copy_on_mj_delete {method filename args} {
534 if {[string match *mj* [file tail $filename]]} {
536 # NOTE: Is the file name relative? If so, add the length of the current
539 if {[is_relative_file $filename]} {
540 set ::mj_filename_length \
541 [expr {[string length $filename] + [string length $::pwd]}]
543 set ::mj_filename_length [string length $filename]
552 1 { set prefix "test.db" }
554 # This test depends on the underlying VFS being able to open paths
555 # 512 bytes in length. The idea is to create a hot-journal file that
556 # contains a master-journal pointer so large that it could contain
557 # a valid page record (if the file page-size is 512 bytes). So as to
558 # make sure SQLite doesn't get confused by this.
560 set nPadding [expr 511 - $::mj_filename_length]
561 if {$tcl_platform(platform)=="windows"} {
562 # TBD need to figure out how to do this correctly for Windows!!!
563 set nPadding [expr 255 - $::mj_filename_length]
566 # We cannot just create a really long database file name to open, as
567 # Linux limits a single component of a path to 255 bytes by default
568 # (and presumably other systems have limits too). So create a directory
569 # hierarchy to work in.
571 set dirname "d123456789012345678901234567890/"
572 set nDir [expr $nPadding / 32]
574 set p [string repeat $dirname $nDir]
579 set padding [string repeat x [expr $nPadding %32]]
580 set prefix "test.db${padding}"
584 foreach {tn2 sql usesMJ} {
586 PRAGMA main.synchronous=OFF;
587 PRAGMA aux.synchronous=OFF;
588 PRAGMA journal_mode = DELETE;
591 PRAGMA main.synchronous=OFF;
592 PRAGMA aux.synchronous=OFF;
593 PRAGMA main.page_size = 512;
594 PRAGMA aux.page_size = 512;
595 PRAGMA journal_mode = DELETE;
598 PRAGMA main.synchronous=NORMAL;
599 PRAGMA aux.synchronous=NORMAL;
600 PRAGMA journal_mode = DELETE;
603 PRAGMA main.synchronous=FULL;
604 PRAGMA aux.synchronous=FULL;
605 PRAGMA journal_mode = DELETE;
608 PRAGMA main.synchronous=NORMAL;
609 PRAGMA aux.synchronous=NORMAL;
610 PRAGMA journal_mode = WAL;
613 PRAGMA main.synchronous=NORMAL;
614 PRAGMA aux.synchronous=NORMAL;
615 PRAGMA main.journal_mode=DELETE;
616 PRAGMA aux.journal_mode=WAL;
619 PRAGMA main.synchronous=FULL;
620 PRAGMA aux.synchronous=OFF;
621 PRAGMA journal_mode=DELETE;
624 PRAGMA main.synchronous=OFF;
625 PRAGMA aux.synchronous=NORMAL;
626 PRAGMA journal_mode=DELETE;
629 PRAGMA main.synchronous=NORMAL;
630 PRAGMA aux.synchronous=NORMAL;
631 PRAGMA main.journal_mode=DELETE;
632 PRAGMA aux.journal_mode = MEMORY;
635 PRAGMA main.synchronous=NORMAL;
636 PRAGMA aux.synchronous=NORMAL;
637 PRAGMA main.journal_mode=DELETE;
638 PRAGMA aux.journal_mode = TRUNCATE;
641 PRAGMA main.synchronous=NORMAL;
642 PRAGMA aux.synchronous=NORMAL;
643 PRAGMA main.journal_mode=DELETE;
644 PRAGMA aux.journal_mode = PERSIST;
648 set tn "${tn1}.${tn2}"
650 # Set up a connection to have two databases, test.db (main) and
651 # test.db2 (aux). Then run a multi-file transaction on them. The
652 # VFS will snapshot the file-system just before the master-journal
653 # file is deleted to commit the transaction.
656 do_test pager1-4.4.$tn.1 {
657 set ::mj_delete_cnt 0
658 faultsim_delete_and_reopen $prefix
660 ATTACH '${prefix}2' AS aux;
663 CREATE TABLE aux.b(x);
664 INSERT INTO a VALUES('double-you');
665 INSERT INTO a VALUES('why');
666 INSERT INTO a VALUES('zed');
667 INSERT INTO b VALUES('won');
668 INSERT INTO b VALUES('too');
669 INSERT INTO b VALUES('free');
673 INSERT INTO a SELECT * FROM b WHERE rowid<=3;
674 INSERT INTO b SELECT * FROM a WHERE rowid<=3;
680 # Verify that a master journal was deleted only for those cases where
681 # master journals really ought to be used
683 do_test pager1-4.4.$tn.1b {
687 # Check that the transaction was committed successfully.
689 do_execsql_test pager1-4.4.$tn.2 {
691 } {double-you why zed won too free}
692 do_execsql_test pager1-4.4.$tn.3 {
694 } {won too free double-you why zed}
697 # Restore the file-system and reopen the databases. Check that it now
698 # appears that the transaction was not committed (because the file-system
699 # was restored to the state where it had not been).
701 do_test pager1-4.4.$tn.4 {
702 faultsim_restore_and_reopen $prefix
703 execsql "ATTACH '${prefix}2' AS aux"
705 do_execsql_test pager1-4.4.$tn.5 {SELECT * FROM a} {double-you why zed}
706 do_execsql_test pager1-4.4.$tn.6 {SELECT * FROM b} {won too free}
709 # Restore the file-system again. This time, before reopening the databases,
710 # delete the master-journal file from the file-system. It now appears that
711 # the transaction was committed (no master-journal file == no rollback).
713 do_test pager1-4.4.$tn.7 {
714 if {$::mj_delete_cnt>0} {
715 faultsim_restore_and_reopen $prefix
716 foreach f [glob ${prefix}-mj*] { forcedelete $f }
721 execsql "ATTACH '${prefix}2' AS aux"
722 glob -nocomplain ${prefix}-mj*
724 do_execsql_test pager1-4.4.$tn.8 {
726 } {double-you why zed won too free}
727 do_execsql_test pager1-4.4.$tn.9 {
729 } {won too free double-you why zed}
738 # Set up a VFS to make a copy of the file-system just before deleting a
739 # journal file to commit a transaction. The transaction modifies exactly
740 # two database pages (and page 1 - the change counter).
742 testvfs tv -default 1
744 tv script copy_on_journal_delete
746 proc copy_on_journal_delete {method filename args} {
747 if {[string match *journal $filename]} faultsim_save
750 faultsim_delete_and_reopen
751 do_execsql_test pager1.4.5.1 {
752 PRAGMA journal_mode = DELETE;
753 PRAGMA page_size = 1024;
754 CREATE TABLE t1(a, b);
755 CREATE TABLE t2(a, b);
756 INSERT INTO t1 VALUES('I', 'II');
757 INSERT INTO t2 VALUES('III', 'IV');
759 INSERT INTO t1 VALUES(1, 2);
760 INSERT INTO t2 VALUES(3, 4);
765 # Check the transaction was committed:
767 do_execsql_test pager1.4.5.2 {
770 } {I II 1 2 III IV 3 4}
772 # Now try four tests:
774 # pager1-4.5.3: Restore the file-system. Check that the whole transaction
777 # pager1-4.5.4: Restore the file-system. Corrupt the first record in the
778 # journal. Check the transaction is not rolled back.
780 # pager1-4.5.5: Restore the file-system. Corrupt the second record in the
781 # journal. Check that the first record in the transaction is
782 # played back, but not the second.
784 # pager1-4.5.6: Restore the file-system. Try to open the database with a
785 # readonly connection. This should fail, as a read-only
786 # connection cannot roll back the database file.
788 faultsim_restore_and_reopen
789 do_execsql_test pager1.4.5.3 {
793 faultsim_restore_and_reopen
794 hexio_write test.db-journal [expr 512+4+1024 - 202] 0123456789ABCDEF
795 do_execsql_test pager1.4.5.4 {
798 } {I II 1 2 III IV 3 4}
799 faultsim_restore_and_reopen
800 hexio_write test.db-journal [expr 512+4+1024+4+4+1024 - 202] 0123456789ABCDEF
801 do_execsql_test pager1.4.5.5 {
806 faultsim_restore_and_reopen
808 sqlite3 db test.db -readonly 1
809 do_catchsql_test pager1.4.5.6 {
812 } {1 {attempt to write a readonly database}}
815 # Snapshot the file-system just before multi-file commit. Save the name
816 # of the master journal file in $::mj_filename.
818 tv script copy_on_mj_delete
820 proc copy_on_mj_delete {method filename args} {
821 if {[string match *mj* [file tail $filename]]} {
822 set ::mj_filename $filename
827 do_test pager1.4.6.1 {
828 faultsim_delete_and_reopen
830 PRAGMA journal_mode = DELETE;
831 ATTACH 'test.db2' AS two;
832 CREATE TABLE t1(a, b);
833 CREATE TABLE two.t2(a, b);
834 INSERT INTO t1 VALUES(1, 't1.1');
835 INSERT INTO t2 VALUES(1, 't2.1');
837 UPDATE t1 SET b = 't1.2';
838 UPDATE t2 SET b = 't2.2';
845 faultsim_restore_and_reopen
846 do_execsql_test pager1.4.6.2 { SELECT * FROM t1 } {1 t1.1}
847 do_test pager1.4.6.3 { file exists $::mj_filename } {1}
848 do_execsql_test pager1.4.6.4 {
849 ATTACH 'test.db2' AS two;
852 do_test pager1.4.6.5 { file exists $::mj_filename } {0}
854 faultsim_restore_and_reopen
856 do_test pager1.4.6.8 {
857 set ::mj_filename1 $::mj_filename
861 PRAGMA journal_mode = DELETE;
862 ATTACH 'test.db3' AS three;
863 CREATE TABLE three.t3(a, b);
864 INSERT INTO t3 VALUES(1, 't3.1');
866 UPDATE t2 SET b = 't2.3';
867 UPDATE t3 SET b = 't3.3';
870 expr {$::mj_filename1 != $::mj_filename}
872 faultsim_restore_and_reopen
875 # The file-system now contains:
878 # * three hot-journal files
879 # * two master-journal files.
881 # The hot-journals associated with test.db2 and test.db3 point to
882 # master journal $::mj_filename. The hot-journal file associated with
883 # test.db points to master journal $::mj_filename1. So reading from
884 # test.db should delete $::mj_filename1.
886 do_test pager1.4.6.9 {
887 lsort [glob test.db*]
889 test.db test.db2 test.db3 \
890 test.db-journal test.db2-journal test.db3-journal \
891 [file tail $::mj_filename] [file tail $::mj_filename1]
894 # The master-journal $::mj_filename1 contains pointers to test.db and
895 # test.db2. However the hot-journal associated with test.db2 points to
896 # a different master-journal. Therefore, reading from test.db only should
897 # be enough to cause SQLite to delete $::mj_filename1.
899 do_test pager1.4.6.10 { file exists $::mj_filename } {1}
900 do_test pager1.4.6.11 { file exists $::mj_filename1 } {1}
901 do_execsql_test pager1.4.6.12 { SELECT * FROM t1 } {1 t1.1}
902 do_test pager1.4.6.13 { file exists $::mj_filename } {1}
903 do_test pager1.4.6.14 { file exists $::mj_filename1 } {0}
905 do_execsql_test pager1.4.6.12 {
906 ATTACH 'test.db2' AS two;
909 do_test pager1.4.6.13 { file exists $::mj_filename } {1}
910 do_execsql_test pager1.4.6.14 {
911 ATTACH 'test.db3' AS three;
914 do_test pager1.4.6.15 { file exists $::mj_filename } {0}
919 testvfs tv -default 1
921 tv script copy_on_journal_delete
923 proc copy_on_journal_delete {method filename args} {
924 if {[string match *journal $filename]} faultsim_save
927 faultsim_delete_and_reopen
928 do_execsql_test pager1.4.7.1 {
929 PRAGMA journal_mode = DELETE;
930 CREATE TABLE t1(x PRIMARY KEY, y);
931 CREATE INDEX i1 ON t1(y);
932 INSERT INTO t1 VALUES('I', 'one');
933 INSERT INTO t1 VALUES('II', 'four');
934 INSERT INTO t1 VALUES('III', 'nine');
936 INSERT INTO t1 VALUES('IV', 'sixteen');
937 INSERT INTO t1 VALUES('V' , 'twentyfive');
944 test_syscall install fchmod
945 test_syscall fault 1 1
947 do_test pager1.4.7.2 {
948 faultsim_restore_and_reopen
949 catch {file attributes test.db-journal -permissions r--------}
950 catch {file attributes test.db-journal -readonly 1}
951 catchsql { SELECT * FROM t1 }
952 } {1 {unable to open database file}}
955 test_syscall fault 0 0
957 do_test pager1.4.7.3 {
959 catch {file attributes test.db-journal -permissions rw-rw-rw-}
960 catch {file attributes test.db-journal -readonly 0}
961 delete_file test.db-journal
962 file exists test.db-journal
964 do_test pager1.4.8.1 {
965 catch {file attributes test.db -permissions r--------}
966 catch {file attributes test.db -readonly 1}
968 db eval { SELECT * FROM t1 }
969 sqlite3_db_readonly db main
971 do_test pager1.4.8.2 {
972 sqlite3_db_readonly db xyz
974 do_test pager1.4.8.3 {
976 catch {file attributes test.db -readonly 0}
977 catch {file attributes test.db -permissions rw-rw-rw-} msg
979 db eval { SELECT * FROM t1 }
980 sqlite3_db_readonly db main
983 #-------------------------------------------------------------------------
984 # The following tests deal with multi-file commits.
986 # pager1-5.1.*: The case where a multi-file cannot be committed because
987 # another connection is holding a SHARED lock on one of the
988 # files. After the SHARED lock is removed, the COMMIT succeeds.
990 # pager1-5.2.*: Multi-file commits with journal_mode=memory.
992 # pager1-5.3.*: Multi-file commits with journal_mode=memory.
994 # pager1-5.4.*: Check that with synchronous=normal, the master-journal file
995 # name is added to a journal file immediately after the last
996 # journal record. But with synchronous=full, extra unused space
997 # is allocated between the last journal record and the
998 # master-journal file name so that the master-journal file
999 # name does not lie on the same sector as the last journal file
1002 # pager1-5.5.*: Check that in journal_mode=PERSIST mode, a journal file is
1003 # truncated to zero bytes when a multi-file transaction is
1004 # committed (instead of the first couple of bytes being zeroed).
1007 do_test pager1-5.1.1 {
1008 faultsim_delete_and_reopen
1010 ATTACH 'test.db2' AS aux;
1011 CREATE TABLE t1(a, b);
1012 CREATE TABLE aux.t2(a, b);
1013 INSERT INTO t1 VALUES(17, 'Lenin');
1014 INSERT INTO t1 VALUES(22, 'Stalin');
1015 INSERT INTO t1 VALUES(53, 'Khrushchev');
1018 do_test pager1-5.1.2 {
1021 INSERT INTO t1 VALUES(64, 'Brezhnev');
1022 INSERT INTO t2 SELECT * FROM t1;
1024 sqlite3 db2 test.db2
1030 do_test pager1-5.1.3 {
1032 } {1 {database is locked}}
1033 do_test pager1-5.1.4 {
1036 execsql { SELECT * FROM t2 } db2
1037 } {17 Lenin 22 Stalin 53 Khrushchev 64 Brezhnev}
1038 do_test pager1-5.1.5 {
1042 do_test pager1-5.2.1 {
1044 PRAGMA journal_mode = memory;
1046 INSERT INTO t1 VALUES(84, 'Andropov');
1047 INSERT INTO t2 VALUES(84, 'Andropov');
1051 do_test pager1-5.3.1 {
1053 PRAGMA journal_mode = off;
1055 INSERT INTO t1 VALUES(85, 'Gorbachev');
1056 INSERT INTO t2 VALUES(85, 'Gorbachev');
1061 do_test pager1-5.4.1 {
1064 sqlite3 db test.db -vfs tv
1065 execsql { ATTACH 'test.db2' AS aux }
1068 tv script max_journal_size
1071 proc max_journal_size {method args} {
1073 catch { set sz [file size test.db-journal] }
1074 if {$sz > $::max_journal} {
1075 set ::max_journal $sz
1080 PRAGMA journal_mode = DELETE;
1081 PRAGMA synchronous = NORMAL;
1083 INSERT INTO t1 VALUES(85, 'Gorbachev');
1084 INSERT INTO t2 VALUES(85, 'Gorbachev');
1088 # The size of the journal file is now:
1090 # 1) 512 byte header +
1091 # 2) 2 * (1024+8) byte records +
1092 # 3) 20+N bytes of master-journal pointer, where N is the size of
1093 # the master-journal name encoded as utf-8 with no nul term.
1095 set mj_pointer [expr {
1096 20 + [string length "test.db-mjXXXXXX9XX"]
1099 # NOTE: For item 3 above, if the current SQLite VFS lacks the concept of a
1100 # current directory, the length of the current directory name plus 1
1101 # character for the directory separator character are NOT counted as
1102 # part of the total size; otherwise, they are.
1105 set mj_pointer [expr {$mj_pointer + [string length [get_pwd]] + 1}]
1107 expr {$::max_journal==(512+2*(1024+8)+$mj_pointer)}
1109 do_test pager1-5.4.2 {
1112 PRAGMA synchronous = full;
1114 DELETE FROM t1 WHERE b = 'Lenin';
1115 DELETE FROM t2 WHERE b = 'Lenin';
1119 # In synchronous=full mode, the master-journal pointer is not written
1120 # directly after the last record in the journal file. Instead, it is
1121 # written starting at the next (in this case 512 byte) sector boundary.
1123 set mj_pointer [expr {
1124 20 + [string length "test.db-mjXXXXXX9XX"]
1127 # NOTE: If the current SQLite VFS lacks the concept of a current directory,
1128 # the length of the current directory name plus 1 character for the
1129 # directory separator character are NOT counted as part of the total
1130 # size; otherwise, they are.
1133 set mj_pointer [expr {$mj_pointer + [string length [get_pwd]] + 1}]
1135 expr {$::max_journal==(((512+2*(1024+8)+511)/512)*512 + $mj_pointer)}
1140 do_test pager1-5.5.1 {
1143 ATTACH 'test.db2' AS aux;
1144 PRAGMA journal_mode = PERSIST;
1145 CREATE TABLE t3(a, b);
1146 INSERT INTO t3 SELECT randomblob(1500), randomblob(1500) FROM t1;
1147 UPDATE t3 SET b = randomblob(1500);
1149 expr [file size test.db-journal] > 15000
1151 do_test pager1-5.5.2 {
1153 PRAGMA synchronous = full;
1155 DELETE FROM t1 WHERE b = 'Stalin';
1156 DELETE FROM t2 WHERE b = 'Stalin';
1159 file size test.db-journal
1163 #-------------------------------------------------------------------------
1164 # The following tests work with "PRAGMA max_page_count"
1166 do_test pager1-6.1 {
1167 faultsim_delete_and_reopen
1169 PRAGMA auto_vacuum = none;
1170 PRAGMA max_page_count = 10;
1171 CREATE TABLE t2(a, b);
1172 CREATE TABLE t3(a, b);
1173 CREATE TABLE t4(a, b);
1174 CREATE TABLE t5(a, b);
1175 CREATE TABLE t6(a, b);
1176 CREATE TABLE t7(a, b);
1177 CREATE TABLE t8(a, b);
1178 CREATE TABLE t9(a, b);
1179 CREATE TABLE t10(a, b);
1182 do_catchsql_test pager1-6.2 {
1183 CREATE TABLE t11(a, b)
1184 } {1 {database or disk is full}}
1185 do_execsql_test pager1-6.4 { PRAGMA max_page_count } {10}
1186 do_execsql_test pager1-6.5 { PRAGMA max_page_count = 15 } {15}
1187 do_execsql_test pager1-6.6 { CREATE TABLE t11(a, b) } {}
1188 do_execsql_test pager1-6.7 {
1190 INSERT INTO t11 VALUES(1, 2);
1191 PRAGMA max_page_count = 13;
1193 do_execsql_test pager1-6.8 {
1194 INSERT INTO t11 VALUES(3, 4);
1195 PRAGMA max_page_count = 10;
1197 do_execsql_test pager1-6.9 { COMMIT } {}
1199 do_execsql_test pager1-6.10 { PRAGMA max_page_count = 10 } {11}
1200 do_execsql_test pager1-6.11 { SELECT * FROM t11 } {1 2 3 4}
1201 do_execsql_test pager1-6.12 { PRAGMA max_page_count } {11}
1204 #-------------------------------------------------------------------------
1205 # The following tests work with "PRAGMA journal_mode=TRUNCATE" and
1206 # "PRAGMA locking_mode=EXCLUSIVE".
1208 # Each test is specified with 5 variables. As follows:
1210 # $tn: Test Number. Used as part of the [do_test] test names.
1211 # $sql: SQL to execute.
1212 # $res: Expected result of executing $sql.
1213 # $js: The expected size of the journal file, in bytes, after executing
1214 # the SQL script. Or -1 if the journal is not expected to exist.
1215 # $ws: The expected size of the WAL file, in bytes, after executing
1216 # the SQL script. Or -1 if the WAL is not expected to exist.
1219 faultsim_delete_and_reopen
1220 foreach {tn sql res js ws} [subst {
1223 CREATE TABLE t1(a, b);
1224 PRAGMA auto_vacuum=OFF;
1225 PRAGMA synchronous=NORMAL;
1226 PRAGMA page_size=1024;
1227 PRAGMA locking_mode=EXCLUSIVE;
1228 PRAGMA journal_mode=TRUNCATE;
1229 INSERT INTO t1 VALUES(1, 2);
1230 } {exclusive truncate} 0 -1
1244 4 { PRAGMA journal_mode = WAL } wal -1 -1
1245 5 { INSERT INTO t1 VALUES(3, 4) } {} -1 [wal_file_size 1 1024]
1246 6 { PRAGMA locking_mode = NORMAL } exclusive -1 [wal_file_size 1 1024]
1247 7 { INSERT INTO t1 VALUES(5, 6); } {} -1 [wal_file_size 2 1024]
1249 8 { PRAGMA journal_mode = TRUNCATE } truncate 0 -1
1250 9 { INSERT INTO t1 VALUES(7, 8) } {} 0 -1
1251 10 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8} 0 -1
1254 do_execsql_test pager1-7.1.$tn.1 $sql $res
1255 catch { set J -1 ; set J [file size test.db-journal] }
1256 catch { set W -1 ; set W [file size test.db-wal] }
1257 do_test pager1-7.1.$tn.2 { list $J $W } [list $js $ws]
1261 do_test pager1-7.2.1 {
1262 faultsim_delete_and_reopen
1264 PRAGMA locking_mode = EXCLUSIVE;
1265 CREATE TABLE t1(a, b);
1267 PRAGMA journal_mode = delete;
1268 PRAGMA journal_mode = truncate;
1270 } {exclusive delete truncate}
1271 do_test pager1-7.2.2 {
1272 execsql { INSERT INTO t1 VALUES(1, 2) }
1273 execsql { PRAGMA journal_mode = persist }
1275 do_test pager1-7.2.3 {
1278 PRAGMA journal_mode = persist;
1279 PRAGMA journal_size_limit;
1283 #-------------------------------------------------------------------------
1284 # The following tests, pager1-8.*, test that the special filenames
1285 # ":memory:" and "" open temporary databases.
1287 foreach {tn filename} {
1291 do_test pager1-8.$tn.1 {
1292 faultsim_delete_and_reopen
1294 sqlite3 db $filename
1296 PRAGMA auto_vacuum = 1;
1298 INSERT INTO x1 VALUES('Charles');
1299 INSERT INTO x1 VALUES('James');
1300 INSERT INTO x1 VALUES('Mary');
1303 } {Charles James Mary}
1305 do_test pager1-8.$tn.2 {
1306 sqlite3 db2 $filename
1307 catchsql { SELECT * FROM x1 } db2
1308 } {1 {no such table: x1}}
1310 do_execsql_test pager1-8.$tn.3 {
1312 INSERT INTO x1 VALUES('William');
1313 INSERT INTO x1 VALUES('Anne');
1318 #-------------------------------------------------------------------------
1319 # The next block of tests - pager1-9.* - deal with interactions between
1320 # the pager and the backup API. Test cases:
1322 # pager1-9.1.*: Test that a backup completes successfully even if the
1323 # source db is written to during the backup op.
1325 # pager1-9.2.*: Test that a backup completes successfully even if the
1326 # source db is written to and then rolled back during a
1329 do_test pager1-9.0.1 {
1330 faultsim_delete_and_reopen
1331 db func a_string a_string
1333 PRAGMA cache_size = 10;
1335 CREATE TABLE ab(a, b, UNIQUE(a, b));
1336 INSERT INTO ab VALUES( a_string(200), a_string(300) );
1337 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1338 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1339 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1340 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1341 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1342 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1343 INSERT INTO ab SELECT a_string(200), a_string(300) FROM ab;
1347 do_test pager1-9.0.2 {
1348 sqlite3 db2 test.db2
1349 db2 eval { PRAGMA cache_size = 10 }
1350 sqlite3_backup B db2 main db main
1351 list [B step 10000] [B finish]
1352 } {SQLITE_DONE SQLITE_OK}
1353 do_test pager1-9.0.3 {
1354 db one {SELECT md5sum(a, b) FROM ab}
1355 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1357 do_test pager1-9.1.1 {
1358 execsql { UPDATE ab SET a = a_string(201) }
1359 sqlite3_backup B db2 main db main
1362 do_test pager1-9.1.2 {
1363 execsql { UPDATE ab SET b = a_string(301) }
1364 list [B step 10000] [B finish]
1365 } {SQLITE_DONE SQLITE_OK}
1366 do_test pager1-9.1.3 {
1367 db one {SELECT md5sum(a, b) FROM ab}
1368 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1369 do_test pager1-9.1.4 { execsql { SELECT count(*) FROM ab } } {128}
1371 do_test pager1-9.2.1 {
1372 execsql { UPDATE ab SET a = a_string(202) }
1373 sqlite3_backup B db2 main db main
1376 do_test pager1-9.2.2 {
1379 UPDATE ab SET b = a_string(301);
1382 list [B step 10000] [B finish]
1383 } {SQLITE_DONE SQLITE_OK}
1384 do_test pager1-9.2.3 {
1385 db one {SELECT md5sum(a, b) FROM ab}
1386 } [db2 one {SELECT md5sum(a, b) FROM ab}]
1387 do_test pager1-9.2.4 { execsql { SELECT count(*) FROM ab } } {128}
1391 do_test pager1-9.3.1 {
1392 testvfs tv -default 1
1394 faultsim_delete_and_reopen
1396 execsql { PRAGMA page_size = 1024 }
1397 for {set ii 0} {$ii < 4} {incr ii} { execsql "CREATE TABLE t${ii}(a, b)" }
1399 if {[nonzero_reserved_bytes]} {
1400 # backup with a page size changes is not possible with the codec
1402 do_test pager1-9.3.2codec {
1403 sqlite3 db2 test.db2
1405 PRAGMA page_size = 4096;
1406 PRAGMA synchronous = OFF;
1407 CREATE TABLE t1(a, b);
1408 CREATE TABLE t2(a, b);
1410 sqlite3_backup B db2 main db main
1412 list [B step 10000] [B finish]
1413 } {SQLITE_READONLY SQLITE_READONLY}
1414 do_test pager1-9.3.3codec {
1419 } [file size test.db2]
1421 do_test pager1-9.3.2 {
1422 sqlite3 db2 test.db2
1424 PRAGMA page_size = 4096;
1425 PRAGMA synchronous = OFF;
1426 CREATE TABLE t1(a, b);
1427 CREATE TABLE t2(a, b);
1429 sqlite3_backup B db2 main db main
1431 list [B step 10000] [B finish]
1432 } {SQLITE_DONE SQLITE_OK}
1433 do_test pager1-9.3.3 {
1438 } [file size test.db]
1441 do_test pager1-9.4.1 {
1442 faultsim_delete_and_reopen
1443 sqlite3 db2 test.db2
1445 PRAGMA page_size = 4096;
1446 CREATE TABLE t1(a, b);
1447 CREATE TABLE t2(a, b);
1449 sqlite3_backup B db2 main db main
1450 list [B step 10000] [B finish]
1451 } {SQLITE_DONE SQLITE_OK}
1452 do_test pager1-9.4.2 {
1453 list [file size test.db2] [file size test.db]
1457 #-------------------------------------------------------------------------
1458 # Test that regardless of the value returned by xSectorSize(), the
1459 # minimum effective sector-size is 512 and the maximum 65536 bytes.
1461 testvfs tv -default 1
1462 foreach sectorsize {
1464 32 64 128 256 512 1024 2048
1465 4096 8192 16384 32768 65536 131072 262144
1467 tv sectorsize $sectorsize
1470 if {$sectorsize < 512} { set eff 512 }
1471 if {$sectorsize > 65536} { set eff 65536 }
1473 do_test pager1-10.$sectorsize.1 {
1474 faultsim_delete_and_reopen
1475 db func a_string a_string
1477 PRAGMA journal_mode = PERSIST;
1478 PRAGMA page_size = 1024;
1480 CREATE TABLE t1(a, b);
1481 CREATE TABLE t2(a, b);
1482 CREATE TABLE t3(a, b);
1485 file size test.db-journal
1486 } [expr $sectorsize > 65536 ? 65536 : ($sectorsize<32 ? 512 : $sectorsize)]
1488 do_test pager1-10.$sectorsize.2 {
1490 INSERT INTO t3 VALUES(a_string(300), a_string(300));
1491 INSERT INTO t3 SELECT * FROM t3; /* 2 */
1492 INSERT INTO t3 SELECT * FROM t3; /* 4 */
1493 INSERT INTO t3 SELECT * FROM t3; /* 8 */
1494 INSERT INTO t3 SELECT * FROM t3; /* 16 */
1495 INSERT INTO t3 SELECT * FROM t3; /* 32 */
1499 do_test pager1-10.$sectorsize.3 {
1503 PRAGMA cache_size = 10;
1506 recursive_select 32 t3 {db eval "INSERT INTO t2 VALUES(1, 2)"}
1513 do_test pager1-10.$sectorsize.4 {
1515 CREATE TABLE t6(a, b);
1516 CREATE TABLE t7(a, b);
1517 CREATE TABLE t5(a, b);
1523 CREATE TABLE t6(a, b);
1525 recursive_select 32 t3 {db eval "INSERT INTO t5 VALUES(1, 2)"}
1536 do_test pager1.10.x.1 {
1537 faultsim_delete_and_reopen
1539 PRAGMA auto_vacuum = none;
1540 PRAGMA page_size = 1024;
1543 for {set i 0} {$i<30} {incr i} {
1544 execsql { INSERT INTO t1 VALUES(zeroblob(900)) }
1548 do_test pager1.10.x.2 {
1555 do_test pager1.10.x.3 {
1560 recursive_select 30 t1
1570 testvfs tv -default 1
1571 faultsim_delete_and_reopen
1572 db func a_string a_string
1573 do_execsql_test pager1-11.1 {
1574 PRAGMA journal_mode = DELETE;
1575 PRAGMA cache_size = 10;
1577 CREATE TABLE zz(top PRIMARY KEY);
1578 INSERT INTO zz VALUES(a_string(222));
1579 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1580 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1581 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1582 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1583 INSERT INTO zz SELECT a_string((SELECT 222+max(rowid) FROM zz)) FROM zz;
1586 UPDATE zz SET top = a_string(345);
1589 proc lockout {method args} { return SQLITE_IOERR }
1591 tv filter {xWrite xTruncate xSync}
1592 do_catchsql_test pager1-11.2 { COMMIT } {1 {disk I/O error}}
1595 do_test pager1-11.3 {
1598 PRAGMA journal_mode = TRUNCATE;
1599 PRAGMA integrity_check;
1602 do_test pager1-11.4 {
1604 file exists test.db-journal
1606 do_execsql_test pager1-11.5 { SELECT count(*) FROM zz } {32}
1610 #-------------------------------------------------------------------------
1611 # Test "PRAGMA page_size"
1613 testvfs tv -default 1
1616 512 1024 2048 4096 8192 16384 32768
1618 faultsim_delete_and_reopen
1620 # The sector-size (according to the VFS) is 1024 bytes. So if the
1621 # page-size requested using "PRAGMA page_size" is greater than the
1622 # compile time value of SQLITE_MAX_PAGE_SIZE, then the effective
1623 # page-size remains 1024 bytes.
1626 if {$eff > $::SQLITE_MAX_PAGE_SIZE} { set eff 1024 }
1628 do_test pager1-12.$pagesize.1 {
1631 PRAGMA page_size = $pagesize;
1632 CREATE VIEW v AS SELECT * FROM sqlite_master;
1636 do_test pager1-12.$pagesize.2 {
1639 SELECT count(*) FROM v;
1640 PRAGMA main.page_size;
1643 do_test pager1-12.$pagesize.3 {
1645 SELECT count(*) FROM v;
1646 PRAGMA main.page_size;
1654 #-------------------------------------------------------------------------
1655 # Test specal "PRAGMA journal_mode=PERSIST" test cases.
1657 # pager1-13.1.*: This tests a special case encountered in persistent
1658 # journal mode: If the journal associated with a transaction
1659 # is smaller than the journal file (because a previous
1660 # transaction left a very large non-hot journal file in the
1661 # file-system), then SQLite has to be careful that there is
1662 # not a journal-header left over from a previous transaction
1663 # immediately following the journal content just written.
1664 # If there is, and the process crashes so that the journal
1665 # becomes a hot-journal and must be rolled back by another
1666 # process, there is a danger that the other process may roll
1667 # back the aborted transaction, then continue copying data
1668 # from an older transaction from the remainder of the journal.
1669 # See the syncJournal() function for details.
1671 # pager1-13.2.*: Same test as the previous. This time, throw an index into
1672 # the mix to make the integrity-check more likely to catch
1675 testvfs tv -default 1
1678 proc xSyncCb {method filename args} {
1679 set t [file tail $filename]
1680 if {$t == "test.db"} faultsim_save
1683 faultsim_delete_and_reopen
1684 db func a_string a_string
1686 # The UPDATE statement at the end of this test case creates a really big
1687 # journal. Since the cache-size is only 10 pages, the journal contains
1688 # frequent journal headers.
1690 do_execsql_test pager1-13.1.1 {
1691 PRAGMA page_size = 1024;
1692 PRAGMA journal_mode = PERSIST;
1693 PRAGMA cache_size = 10;
1695 CREATE TABLE t1(a INTEGER PRIMARY KEY, b BLOB);
1696 INSERT INTO t1 VALUES(NULL, a_string(400));
1697 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 2 */
1698 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 4 */
1699 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 8 */
1700 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 16 */
1701 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 32 */
1702 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 64 */
1703 INSERT INTO t1 SELECT NULL, a_string(400) FROM t1; /* 128 */
1705 UPDATE t1 SET b = a_string(400);
1708 if {$::tcl_platform(platform)!="windows"} {
1709 # Run transactions of increasing sizes. Eventually, one (or more than one)
1710 # of these will write just enough content that one of the old headers created
1711 # by the transaction in the block above lies immediately after the content
1712 # journalled by the current transaction.
1714 for {set nUp 1} {$nUp<64} {incr nUp} {
1715 do_execsql_test pager1-13.1.2.$nUp.1 {
1716 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1718 do_execsql_test pager1-13.1.2.$nUp.2 { PRAGMA integrity_check } {ok}
1720 # Try to access the snapshot of the file-system.
1722 sqlite3 db2 sv_test.db
1723 do_test pager1-13.1.2.$nUp.3 {
1724 execsql { SELECT sum(length(b)) FROM t1 } db2
1725 } [expr {128*400 - ($nUp-1)}]
1726 do_test pager1-13.1.2.$nUp.4 {
1727 execsql { PRAGMA integrity_check } db2
1733 if {$::tcl_platform(platform)!="windows"} {
1734 # Same test as above. But this time with an index on the table.
1736 do_execsql_test pager1-13.2.1 {
1737 CREATE INDEX i1 ON t1(b);
1738 UPDATE t1 SET b = a_string(400);
1740 for {set nUp 1} {$nUp<64} {incr nUp} {
1741 do_execsql_test pager1-13.2.2.$nUp.1 {
1742 UPDATE t1 SET b = a_string(399) WHERE a <= $nUp
1744 do_execsql_test pager1-13.2.2.$nUp.2 { PRAGMA integrity_check } {ok}
1745 sqlite3 db2 sv_test.db
1746 do_test pager1-13.2.2.$nUp.3 {
1747 execsql { SELECT sum(length(b)) FROM t1 } db2
1748 } [expr {128*400 - ($nUp-1)}]
1749 do_test pager1-13.2.2.$nUp.4 {
1750 execsql { PRAGMA integrity_check } db2
1759 #-------------------------------------------------------------------------
1760 # Test specal "PRAGMA journal_mode=OFF" test cases.
1762 faultsim_delete_and_reopen
1763 do_execsql_test pager1-14.1.1 {
1764 PRAGMA journal_mode = OFF;
1765 CREATE TABLE t1(a, b);
1767 INSERT INTO t1 VALUES(1, 2);
1771 do_catchsql_test pager1-14.1.2 {
1773 INSERT INTO t1 VALUES(3, 4);
1776 do_execsql_test pager1-14.1.3 {
1779 do_catchsql_test pager1-14.1.4 {
1781 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1782 INSERT INTO t1(rowid, a, b) SELECT a+3, b, b FROM t1;
1783 } {1 {UNIQUE constraint failed: t1.rowid}}
1784 do_execsql_test pager1-14.1.5 {
1789 #-------------------------------------------------------------------------
1790 # Test opening and closing the pager sub-system with different values
1791 # for the sqlite3_vfs.szOsFile variable.
1793 faultsim_delete_and_reopen
1794 do_execsql_test pager1-15.0 {
1795 CREATE TABLE tx(y, z);
1796 INSERT INTO tx VALUES('Ayutthaya', 'Beijing');
1797 INSERT INTO tx VALUES('London', 'Tokyo');
1800 for {set i 0} {$i<513} {incr i 3} {
1801 testvfs tv -default 1 -szosfile $i
1803 do_execsql_test pager1-15.$i.1 {
1805 } {Ayutthaya Beijing London Tokyo}
1810 #-------------------------------------------------------------------------
1811 # Check that it is not possible to open a database file if the full path
1812 # to the associated journal file will be longer than sqlite3_vfs.mxPathname.
1814 testvfs tv -default 1
1817 proc xOpenCb {method filename args} {
1818 set ::file_len [string length $filename]
1824 for {set ii [expr $::file_len-5]} {$ii < [expr $::file_len+20]} {incr ii} {
1825 testvfs tv -default 1 -mxpathname $ii
1827 # The length of the full path to file "test.db-journal" is ($::file_len+8).
1828 # If the configured sqlite3_vfs.mxPathname value greater than or equal to
1829 # this, then the file can be opened. Otherwise, it cannot.
1831 if {$ii >= [expr $::file_len+8]} {
1834 set res {1 {unable to open database file}}
1837 do_test pager1-16.1.$ii {
1838 list [catch { sqlite3 db test.db } msg] $msg
1846 #-------------------------------------------------------------------------
1847 # Test the pagers response to the b-tree layer requesting illegal page
1850 # + The locking page,
1852 # + A page with a page number greater than (2^31-1).
1854 # These tests will not work if SQLITE_DIRECT_OVERFLOW_READ is defined. In
1855 # that case IO errors are sometimes reported instead of SQLITE_CORRUPT.
1857 ifcapable !direct_read {
1858 do_test pager1-18.1 {
1859 faultsim_delete_and_reopen
1860 db func a_string a_string
1862 PRAGMA page_size = 1024;
1863 CREATE TABLE t1(a, b);
1864 INSERT INTO t1 VALUES(a_string(500), a_string(200));
1865 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1866 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1867 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1868 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1869 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1870 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1871 INSERT INTO t1 SELECT a_string(500), a_string(200) FROM t1;
1874 do_test pager1-18.2 {
1875 set root [db one "SELECT rootpage FROM sqlite_master"]
1876 set lockingpage [expr (0x10000/1024) + 1]
1878 PRAGMA writable_schema = 1;
1879 UPDATE sqlite_master SET rootpage = $lockingpage;
1882 catchsql { SELECT count(*) FROM t1 } db2
1883 } {1 {database disk image is malformed}}
1885 do_test pager1-18.3.1 {
1888 INSERT INTO t2 VALUES(a_string(5000));
1890 set pgno [expr ([file size test.db] / 1024)-2]
1891 hexio_write test.db [expr ($pgno-1)*1024] 00000000
1893 # even though x is malformed, because typeof() does
1894 # not load the content of x, the error is not noticed.
1895 catchsql { SELECT typeof(x) FROM t2 } db2
1897 do_test pager1-18.3.2 {
1898 # in this case, the value of x is loaded and so the error is
1900 catchsql { SELECT length(x||'') FROM t2 } db2
1901 } {1 {database disk image is malformed}}
1903 do_test pager1-18.3.3 {
1906 INSERT INTO t2 VALUES(randomblob(5000));
1908 set pgno [expr ([file size test.db] / 1024)-2]
1909 hexio_write test.db [expr ($pgno-1)*1024] 00000000
1911 # even though x is malformed, because length() and typeof() do
1912 # not load the content of x, the error is not noticed.
1913 catchsql { SELECT length(x), typeof(x) FROM t2 } db2
1915 do_test pager1-18.3.4 {
1916 # in this case, the value of x is loaded and so the error is
1918 catchsql { SELECT length(x||'') FROM t2 } db2
1919 } {1 {database disk image is malformed}}
1921 do_test pager1-18.4 {
1922 hexio_write test.db [expr ($pgno-1)*1024] 90000000
1924 catchsql { SELECT length(x||'') FROM t2 } db2
1925 } {1 {database disk image is malformed}}
1927 do_test pager1-18.5 {
1930 CREATE TABLE t1(a, b);
1931 CREATE TABLE t2(a, b);
1932 PRAGMA writable_schema = 1;
1933 UPDATE sqlite_master SET rootpage=5 WHERE tbl_name = 't1';
1934 PRAGMA writable_schema = 0;
1935 ALTER TABLE t1 RENAME TO x1;
1937 catchsql { SELECT * FROM x1 }
1938 } {1 {database disk image is malformed}}
1941 do_test pager1-18.6 {
1942 faultsim_delete_and_reopen
1943 db func a_string a_string
1945 PRAGMA page_size = 1024;
1947 INSERT INTO t1 VALUES(a_string(800));
1948 INSERT INTO t1 VALUES(a_string(800));
1951 set root [db one "SELECT rootpage FROM sqlite_master"]
1954 hexio_write test.db [expr ($root-1)*1024 + 8] 00000000
1956 catchsql { SELECT length(x) FROM t1 }
1957 } {1 {database disk image is malformed}}
1960 do_test pager1-19.1 {
1962 db func a_string a_string
1964 PRAGMA page_size = 512;
1965 PRAGMA auto_vacuum = 1;
1966 CREATE TABLE t1(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1967 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1968 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1969 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1970 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1971 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1972 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1973 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1974 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1975 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1976 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1977 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1978 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1980 CREATE TABLE t2(aa, ab, ac, ad, ae, af, ag, ah, ai, aj, ak, al, am, an,
1981 ba, bb, bc, bd, be, bf, bg, bh, bi, bj, bk, bl, bm, bn,
1982 ca, cb, cc, cd, ce, cf, cg, ch, ci, cj, ck, cl, cm, cn,
1983 da, db, dc, dd, de, df, dg, dh, di, dj, dk, dl, dm, dn,
1984 ea, eb, ec, ed, ee, ef, eg, eh, ei, ej, ek, el, em, en,
1985 fa, fb, fc, fd, fe, ff, fg, fh, fi, fj, fk, fl, fm, fn,
1986 ga, gb, gc, gd, ge, gf, gg, gh, gi, gj, gk, gl, gm, gn,
1987 ha, hb, hc, hd, he, hf, hg, hh, hi, hj, hk, hl, hm, hn,
1988 ia, ib, ic, id, ie, if, ig, ih, ii, ij, ik, il, im, ix,
1989 ja, jb, jc, jd, je, jf, jg, jh, ji, jj, jk, jl, jm, jn,
1990 ka, kb, kc, kd, ke, kf, kg, kh, ki, kj, kk, kl, km, kn,
1991 la, lb, lc, ld, le, lf, lg, lh, li, lj, lk, ll, lm, ln,
1992 ma, mb, mc, md, me, mf, mg, mh, mi, mj, mk, ml, mm, mn
1994 INSERT INTO t1(aa) VALUES( a_string(100000) );
1995 INSERT INTO t2(aa) VALUES( a_string(100000) );
2000 #-------------------------------------------------------------------------
2001 # Test a couple of special cases that come up while committing
2004 # pager1-20.1.*: Committing an in-memory database transaction when the
2005 # database has not been modified at all.
2007 # pager1-20.2.*: As above, but with a normal db in exclusive-locking mode.
2009 # pager1-20.3.*: Committing a transaction in WAL mode where the database has
2010 # been modified, but all dirty pages have been flushed to
2011 # disk before the commit.
2013 do_test pager1-20.1.1 {
2017 CREATE TABLE one(two, three);
2018 INSERT INTO one VALUES('a', 'b');
2021 do_test pager1-20.1.2 {
2028 do_test pager1-20.2.1 {
2029 faultsim_delete_and_reopen
2031 PRAGMA locking_mode = exclusive;
2032 PRAGMA journal_mode = persist;
2033 CREATE TABLE one(two, three);
2034 INSERT INTO one VALUES('a', 'b');
2036 } {exclusive persist}
2037 do_test pager1-20.2.2 {
2045 do_test pager1-20.3.1 {
2046 faultsim_delete_and_reopen
2047 db func a_string a_string
2049 PRAGMA cache_size = 10;
2050 PRAGMA journal_mode = wal;
2054 INSERT INTO t1 VALUES(a_string(800));
2055 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 2 */
2056 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 4 */
2057 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 8 */
2058 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 16 */
2059 INSERT INTO t1 SELECT a_string(800) FROM t1; /* 32 */
2063 do_test pager1-20.3.2 {
2066 INSERT INTO t2 VALUES('xxxx');
2068 recursive_select 32 t1
2073 #-------------------------------------------------------------------------
2074 # Test that a WAL database may not be opened if:
2076 # pager1-21.1.*: The VFS has an iVersion less than 2, or
2077 # pager1-21.2.*: The VFS does not provide xShmXXX() methods.
2080 do_test pager1-21.0 {
2081 faultsim_delete_and_reopen
2083 PRAGMA journal_mode = WAL;
2084 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
2085 INSERT INTO ko DEFAULT VALUES;
2088 do_test pager1-21.1 {
2090 sqlite3 db2 test.db -vfs tv
2091 catchsql { SELECT * FROM ko } db2
2092 } {1 {unable to open database file}}
2095 do_test pager1-21.2 {
2096 testvfs tv -iversion 1
2097 sqlite3 db2 test.db -vfs tv
2098 catchsql { SELECT * FROM ko } db2
2099 } {1 {unable to open database file}}
2104 #-------------------------------------------------------------------------
2105 # Test that a "PRAGMA wal_checkpoint":
2107 # pager1-22.1.*: is a no-op on a non-WAL db, and
2108 # pager1-22.2.*: does not cause xSync calls with a synchronous=off db.
2111 do_test pager1-22.1.1 {
2112 faultsim_delete_and_reopen
2114 CREATE TABLE ko(c DEFAULT 'abc', b DEFAULT 'def');
2115 INSERT INTO ko DEFAULT VALUES;
2117 execsql { PRAGMA wal_checkpoint }
2119 do_test pager1-22.2.1 {
2120 testvfs tv -default 1
2123 proc xSyncCb {args} {incr ::synccount}
2127 PRAGMA synchronous = off;
2128 PRAGMA journal_mode = WAL;
2129 INSERT INTO ko DEFAULT VALUES;
2131 execsql { PRAGMA wal_checkpoint }
2138 #-------------------------------------------------------------------------
2139 # Tests for changing journal mode.
2141 # pager1-23.1.*: Test that when changing from PERSIST to DELETE mode,
2142 # the journal file is deleted.
2144 # pager1-23.2.*: Same test as above, but while a shared lock is held
2145 # on the database file.
2147 # pager1-23.3.*: Same test as above, but while a reserved lock is held
2148 # on the database file.
2150 # pager1-23.4.*: And, for fun, while holding an exclusive lock.
2152 # pager1-23.5.*: Try to set various different journal modes with an
2153 # in-memory database (only MEMORY and OFF should work).
2155 # pager1-23.6.*: Try to set locking_mode=normal on an in-memory database
2156 # (doesn't work - in-memory databases always use
2157 # locking_mode=exclusive).
2159 do_test pager1-23.1.1 {
2160 faultsim_delete_and_reopen
2162 PRAGMA journal_mode = PERSIST;
2163 CREATE TABLE t1(a, b);
2165 file exists test.db-journal
2167 do_test pager1-23.1.2 {
2168 execsql { PRAGMA journal_mode = DELETE }
2169 file exists test.db-journal
2172 do_test pager1-23.2.1 {
2174 PRAGMA journal_mode = PERSIST;
2175 INSERT INTO t1 VALUES('Canberra', 'ACT');
2177 db eval { SELECT * FROM t1 } {
2178 db eval { PRAGMA journal_mode = DELETE }
2180 execsql { PRAGMA journal_mode }
2182 do_test pager1-23.2.2 {
2183 file exists test.db-journal
2186 do_test pager1-23.3.1 {
2188 PRAGMA journal_mode = PERSIST;
2189 INSERT INTO t1 VALUES('Darwin', 'NT');
2192 db eval { PRAGMA journal_mode = DELETE }
2193 execsql { PRAGMA journal_mode }
2195 do_test pager1-23.3.2 {
2196 file exists test.db-journal
2198 do_test pager1-23.3.3 {
2202 do_test pager1-23.4.1 {
2204 PRAGMA journal_mode = PERSIST;
2205 INSERT INTO t1 VALUES('Adelaide', 'SA');
2208 db eval { PRAGMA journal_mode = DELETE }
2209 execsql { PRAGMA journal_mode }
2211 do_test pager1-23.4.2 {
2212 file exists test.db-journal
2214 do_test pager1-23.4.3 {
2218 do_test pager1-23.5.1 {
2219 faultsim_delete_and_reopen
2222 foreach {tn mode possible} {
2230 do_test pager1-23.5.$tn.1 {
2231 execsql "PRAGMA journal_mode = off"
2232 execsql "PRAGMA journal_mode = $mode"
2233 } [if $possible {list $mode} {list off}]
2234 do_test pager1-23.5.$tn.2 {
2235 execsql "PRAGMA journal_mode = memory"
2236 execsql "PRAGMA journal_mode = $mode"
2237 } [if $possible {list $mode} {list memory}]
2239 do_test pager1-23.6.1 {
2240 execsql {PRAGMA locking_mode = normal}
2242 do_test pager1-23.6.2 {
2243 execsql {PRAGMA locking_mode = exclusive}
2245 do_test pager1-23.6.3 {
2246 execsql {PRAGMA locking_mode}
2248 do_test pager1-23.6.4 {
2249 execsql {PRAGMA main.locking_mode}
2252 #-------------------------------------------------------------------------
2254 do_test pager1-24.1.1 {
2255 faultsim_delete_and_reopen
2256 db func a_string a_string
2258 PRAGMA cache_size = 10;
2259 PRAGMA auto_vacuum = FULL;
2260 CREATE TABLE x1(x, y, z, PRIMARY KEY(y, z));
2261 CREATE TABLE x2(x, y, z, PRIMARY KEY(y, z));
2262 INSERT INTO x2 VALUES(a_string(400), a_string(500), a_string(600));
2263 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2264 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2265 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2266 INSERT INTO x2 SELECT a_string(600), a_string(400), a_string(500) FROM x2;
2267 INSERT INTO x2 SELECT a_string(500), a_string(600), a_string(400) FROM x2;
2268 INSERT INTO x2 SELECT a_string(400), a_string(500), a_string(600) FROM x2;
2269 INSERT INTO x1 SELECT * FROM x2;
2272 do_test pager1-24.1.2 {
2275 DELETE FROM x1 WHERE rowid<32;
2277 recursive_select 64 x2
2279 do_test pager1-24.1.3 {
2281 UPDATE x1 SET z = a_string(300) WHERE rowid>40;
2283 PRAGMA integrity_check;
2284 SELECT count(*) FROM x1;
2288 do_test pager1-24.1.4 {
2291 INSERT INTO x1 SELECT * FROM x2;
2293 DELETE FROM x1 WHERE rowid<32;
2294 UPDATE x1 SET z = a_string(299) WHERE rowid>40;
2296 recursive_select 64 x2 {db eval COMMIT}
2298 PRAGMA integrity_check;
2299 SELECT count(*) FROM x1;
2303 do_test pager1-24.1.5 {
2306 INSERT INTO x1 SELECT * FROM x2;
2308 recursive_select 64 x2 { db eval {CREATE TABLE x3(x, y, z)} }
2309 execsql { SELECT * FROM x3 }
2312 #-------------------------------------------------------------------------
2314 do_test pager1-25-1 {
2315 faultsim_delete_and_reopen
2319 CREATE TABLE t1(a, b);
2325 do_test pager1-25-2 {
2326 faultsim_delete_and_reopen
2329 CREATE TABLE t1(a, b);
2336 #-------------------------------------------------------------------------
2337 # Sector-size tests.
2339 do_test pager1-26.1 {
2340 testvfs tv -default 1
2342 faultsim_delete_and_reopen
2343 db func a_string a_string
2345 PRAGMA page_size = 512;
2346 CREATE TABLE tbl(a PRIMARY KEY, b UNIQUE);
2348 INSERT INTO tbl VALUES(a_string(25), a_string(600));
2349 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2350 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2351 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2352 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2353 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2354 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2355 INSERT INTO tbl SELECT a_string(25), a_string(600) FROM tbl;
2359 do_execsql_test pager1-26.1 {
2360 UPDATE tbl SET b = a_string(550);
2365 #-------------------------------------------------------------------------
2367 do_test pager1.27.1 {
2368 faultsim_delete_and_reopen
2369 sqlite3_pager_refcounts db
2372 CREATE TABLE t1(a, b);
2374 sqlite3_pager_refcounts db
2378 #-------------------------------------------------------------------------
2379 # Test that attempting to open a write-transaction with
2380 # locking_mode=exclusive in WAL mode fails if there are other clients on
2381 # the same database.
2385 do_multiclient_test tn {
2386 do_test pager1-28.$tn.1 {
2388 PRAGMA journal_mode = WAL;
2389 CREATE TABLE t1(a, b);
2390 INSERT INTO t1 VALUES('a', 'b');
2393 do_test pager1-28.$tn.2 { sql2 { SELECT * FROM t1 } } {a b}
2395 do_test pager1-28.$tn.3 { sql1 { PRAGMA locking_mode=exclusive } } {exclusive}
2396 do_test pager1-28.$tn.4 {
2397 csql1 { BEGIN; INSERT INTO t1 VALUES('c', 'd'); }
2398 } {1 {database is locked}}
2399 code2 { db2 close ; sqlite3 db2 test.db }
2400 do_test pager1-28.$tn.4 {
2401 sql1 { INSERT INTO t1 VALUES('c', 'd'); COMMIT }
2406 #-------------------------------------------------------------------------
2407 # Normally, when changing from journal_mode=PERSIST to DELETE the pager
2408 # attempts to delete the journal file. However, if it cannot obtain a
2409 # RESERVED lock on the database file, this step is skipped.
2411 do_multiclient_test tn {
2412 do_test pager1-28.$tn.1 {
2414 PRAGMA journal_mode = PERSIST;
2415 CREATE TABLE t1(a, b);
2416 INSERT INTO t1 VALUES('a', 'b');
2419 do_test pager1-28.$tn.2 { file exists test.db-journal } 1
2420 do_test pager1-28.$tn.3 { sql1 { PRAGMA journal_mode = DELETE } } delete
2421 do_test pager1-28.$tn.4 { file exists test.db-journal } 0
2423 do_test pager1-28.$tn.5 {
2425 PRAGMA journal_mode = PERSIST;
2426 INSERT INTO t1 VALUES('c', 'd');
2429 do_test pager1-28.$tn.6 { file exists test.db-journal } 1
2430 do_test pager1-28.$tn.7 {
2431 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2433 do_test pager1-28.$tn.8 { file exists test.db-journal } 1
2434 do_test pager1-28.$tn.9 { sql1 { PRAGMA journal_mode = DELETE } } delete
2435 do_test pager1-28.$tn.10 { file exists test.db-journal } 1
2437 do_test pager1-28.$tn.11 { sql2 COMMIT } {}
2438 do_test pager1-28.$tn.12 { file exists test.db-journal } 0
2440 do_test pager1-28-$tn.13 {
2441 code1 { set channel [db incrblob -readonly t1 a 2] }
2443 PRAGMA journal_mode = PERSIST;
2444 INSERT INTO t1 VALUES('g', 'h');
2447 do_test pager1-28.$tn.14 { file exists test.db-journal } 1
2448 do_test pager1-28.$tn.15 {
2449 sql2 { BEGIN; INSERT INTO t1 VALUES('e', 'f'); }
2451 do_test pager1-28.$tn.16 { sql1 { PRAGMA journal_mode = DELETE } } delete
2452 do_test pager1-28.$tn.17 { file exists test.db-journal } 1
2454 do_test pager1-28.$tn.17 { csql2 { COMMIT } } {1 {database is locked}}
2455 do_test pager1-28-$tn.18 { code1 { read $channel } } c
2456 do_test pager1-28-$tn.19 { code1 { close $channel } } {}
2457 do_test pager1-28.$tn.20 { sql2 { COMMIT } } {}
2460 do_test pager1-29.1 {
2461 faultsim_delete_and_reopen
2463 PRAGMA page_size = 1024;
2464 PRAGMA auto_vacuum = full;
2465 PRAGMA locking_mode=exclusive;
2466 CREATE TABLE t1(a, b);
2467 INSERT INTO t1 VALUES(1, 2);
2471 if {[nonzero_reserved_bytes]} {
2472 # VACUUM with size changes is not possible with the codec.
2473 do_test pager1-29.2 {
2475 PRAGMA page_size = 4096;
2478 } {1 {attempt to write a readonly database}}
2480 do_test pager1-29.2 {
2482 PRAGMA page_size = 4096;
2489 #-------------------------------------------------------------------------
2490 # Test that if an empty database file (size 0 bytes) is opened in
2491 # exclusive-locking mode, any journal file is deleted from the file-system
2492 # without being rolled back. And that the RESERVED lock obtained while
2493 # doing this is not released.
2495 do_test pager1-30.1 {
2498 delete_file test.db-journal
2499 set fd [open test.db-journal w]
2500 seek $fd [expr 512+1032*2]
2501 puts -nonewline $fd x
2506 PRAGMA locking_mode=EXCLUSIVE;
2507 SELECT count(*) FROM sqlite_master;
2510 } {exclusive 0 main reserved temp closed}
2512 #-------------------------------------------------------------------------
2513 # Test that if the "page-size" field in a journal-header is 0, the journal
2514 # file can still be rolled back. This is required for backward compatibility -
2515 # versions of SQLite prior to 3.5.8 always set this field to zero.
2517 if {$tcl_platform(platform)=="unix"} {
2518 do_test pager1-31.1 {
2519 faultsim_delete_and_reopen
2521 PRAGMA cache_size = 10;
2522 PRAGMA page_size = 1024;
2523 CREATE TABLE t1(x, y, UNIQUE(x, y));
2524 INSERT INTO t1 VALUES(randomblob(1500), randomblob(1500));
2525 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2526 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2527 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2528 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2529 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2530 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2531 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2532 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2533 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2534 INSERT INTO t1 SELECT randomblob(1500), randomblob(1500) FROM t1;
2536 UPDATE t1 SET y = randomblob(1499);
2538 copy_file test.db test.db2
2539 copy_file test.db-journal test.db2-journal
2541 hexio_write test.db2-journal 24 00000000
2542 sqlite3 db2 test.db2
2543 execsql { PRAGMA integrity_check } db2
2547 #-------------------------------------------------------------------------
2548 # Test that a database file can be "pre-hinted" to a certain size and that
2549 # subsequent spilling of the pager cache does not result in the database
2550 # file being shrunk.
2555 do_test pager1-32.1 {
2558 CREATE TABLE t1(x, y);
2564 INSERT INTO t1 VALUES(1, randomblob(10000));
2566 file_control_chunksize_test db main 1024
2567 file_control_sizehint_test db main 20971520; # 20MB
2569 PRAGMA cache_size = 10;
2570 INSERT INTO t1 VALUES(1, randomblob(10000));
2571 INSERT INTO t1 VALUES(2, randomblob(10000));
2572 INSERT INTO t1 SELECT x+2, randomblob(10000) from t1;
2573 INSERT INTO t1 SELECT x+4, randomblob(10000) from t1;
2574 INSERT INTO t1 SELECT x+8, randomblob(10000) from t1;
2575 INSERT INTO t1 SELECT x+16, randomblob(10000) from t1;
2576 SELECT count(*) FROM t1;
2583 # Cleanup 20MB file left by the previous test.
2586 #-------------------------------------------------------------------------
2587 # Test that if a transaction is committed in journal_mode=DELETE mode,
2588 # and the call to unlink() returns an ENOENT error, the COMMIT does not
2591 if {$::tcl_platform(platform)=="unix"} {
2592 do_test pager1-33.1 {
2596 INSERT INTO t1 VALUES('one');
2597 INSERT INTO t1 VALUES('two');
2599 INSERT INTO t1 VALUES('three');
2600 INSERT INTO t1 VALUES('four');
2602 forcedelete bak-journal
2603 file rename test.db-journal bak-journal
2606 } {1 {disk I/O error}}
2608 do_test pager1-33.2 {
2609 file rename bak-journal test.db-journal
2610 execsql { SELECT * FROM t1 }
2614 #-------------------------------------------------------------------------
2615 # Test that appending pages to the database file then moving those pages
2616 # to the free-list before the transaction is committed does not cause
2619 foreach {tn pragma strsize} {
2620 1 { PRAGMA mmap_size = 0 } 2400
2622 3 { PRAGMA mmap_size = 0 } 4400
2626 db func a_string a_string
2628 do_execsql_test 34.$tn.1 {
2629 CREATE TABLE t1(a, b);
2630 INSERT INTO t1 VALUES(1, 2);
2632 do_execsql_test 34.$tn.2 {
2634 INSERT INTO t1 VALUES(2, a_string($strsize));
2635 DELETE FROM t1 WHERE oid=2;
2637 PRAGMA integrity_check;
2641 #-------------------------------------------------------------------------
2648 CREATE TABLE t1(x, y);
2649 PRAGMA journal_mode = WAL;
2650 INSERT INTO t1 VALUES(1, 2);
2655 CREATE TABLE t2(a, b);
2658 hexio_write test.db-shm [expr 16*1024] [string repeat 0055 8192]
2662 do_multiclient_test tn {
2664 PRAGMA auto_vacuum = 0;
2665 CREATE TABLE t1(x, y);
2666 INSERT INTO t1 VALUES(1, 2);
2670 sql2 { PRAGMA max_page_count = 2 }
2671 list [catch { sql2 { CREATE TABLE t2(x) } } msg] $msg
2672 } {1 {database or disk is full}}
2674 sql1 { PRAGMA checkpoint_fullfsync = 1 }
2675 sql1 { CREATE TABLE t2(x) }
2678 sql2 { INSERT INTO t2 VALUES('xyz') }
2679 list [catch { sql2 { CREATE TABLE t3(x) } } msg] $msg
2680 } {1 {database or disk is full}}
2683 forcedelete test1 test2
2685 1 {file:?mode=memory&cache=shared}
2686 2 {file:one?mode=memory&cache=shared}
2687 3 {file:test1?cache=shared}
2688 4 {file:test2?another=parameter&yet=anotherone}
2693 sqlite3_config_uri 1
2698 INSERT INTO t1 VALUES(1);
2703 do_execsql_test 37.$tn.2 {
2710 sqlite3_config_uri 0
2716 set fd [open test.db w]
2717 puts $fd "hello world"
2720 catchsql { CREATE TABLE t1(x) }
2721 } {1 {file is not a database}}
2730 PRAGMA auto_vacuum = 1;
2732 INSERT INTO t1 VALUES('xxx');
2733 INSERT INTO t1 VALUES('two');
2734 INSERT INTO t1 VALUES(randomblob(400));
2735 INSERT INTO t1 VALUES(randomblob(400));
2736 INSERT INTO t1 VALUES(randomblob(400));
2737 INSERT INTO t1 VALUES(randomblob(400));
2739 UPDATE t1 SET x = 'one' WHERE rowid=1;
2741 set ::stmt [sqlite3_prepare db "SELECT * FROM t1 ORDER BY rowid" -1 dummy]
2742 sqlite3_step $::stmt
2743 sqlite3_column_text $::stmt 0
2746 execsql { CREATE TABLE t2(x) }
2747 sqlite3_step $::stmt
2748 sqlite3_column_text $::stmt 0
2751 sqlite3_finalize $::stmt
2755 do_execsql_test 39.4 {
2756 PRAGMA auto_vacuum = 2;
2768 PRAGMA cache_size = 1;
2769 PRAGMA incremental_vacuum;
2770 PRAGMA integrity_check;
2777 PRAGMA auto_vacuum = 1;
2778 CREATE TABLE t1(x PRIMARY KEY);
2779 INSERT INTO t1 VALUES(randomblob(1200));
2785 INSERT INTO t1 VALUES(randomblob(1200));
2786 INSERT INTO t1 VALUES(randomblob(1200));
2787 INSERT INTO t1 VALUES(randomblob(1200));
2794 PRAGMA cache_size = 1;
2796 PRAGMA integrity_check;
2803 CREATE TABLE t1(x PRIMARY KEY);
2804 INSERT INTO t1 VALUES(randomblob(200));
2805 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2806 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2807 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2808 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2809 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2810 INSERT INTO t1 SELECT randomblob(200) FROM t1;
2814 testvfs tv -default 1
2815 tv sectorsize 16384;
2820 PRAGMA cache_size = 1;
2821 DELETE FROM t1 WHERE rowid%4;
2822 PRAGMA integrity_check;
2828 set pending_prev [sqlite3_test_control_pending_byte 0x1000000]
2832 CREATE TABLE t1(x, y);
2833 INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
2834 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2835 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2836 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2837 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2838 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2839 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2840 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2841 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2842 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2845 sqlite3_test_control_pending_byte 0x0010000
2847 db eval { PRAGMA mmap_size = 0 }
2848 catchsql { SELECT sum(length(y)) FROM t1 }
2849 } {1 {database disk image is malformed}}
2853 CREATE TABLE t1(x, y);
2854 INSERT INTO t1 VALUES(randomblob(200), randomblob(200));
2855 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2856 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
2857 INSERT INTO t1 SELECT randomblob(200), randomblob(200) FROM t1;
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;
2866 testvfs tv -default 1
2867 tv sectorsize 16384;
2869 sqlite3 db test.db -vfs tv
2870 execsql { UPDATE t1 SET x = randomblob(200) }
2874 sqlite3_test_control_pending_byte $pending_prev
2879 CREATE TABLE t1(x, y);
2880 INSERT INTO t1 VALUES(1, 2);
2881 CREATE TABLE t2(x, y);
2882 INSERT INTO t2 VALUES(1, 2);
2883 CREATE TABLE t3(x, y);
2884 INSERT INTO t3 VALUES(1, 2);
2889 db eval { PRAGMA mmap_size = 0 }
2890 db eval { SELECT * FROM t1 }
2891 sqlite3_db_status db CACHE_MISS 0
2895 db eval { SELECT * FROM t2 }
2896 sqlite3_db_status db CACHE_MISS 1
2900 db eval { SELECT * FROM t3 }
2901 sqlite3_db_status db CACHE_MISS 0