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 #***********************************************************************
12 # The focus of this file is testing some specific characteristics of the
13 # IO traffic generated by SQLite (making sure SQLite is not writing out
14 # more database pages than it has to, stuff like that).
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
22 sqlite3_simulate_device
23 sqlite3 db test.db -vfs devsym
27 # io-1.* - Test that quick-balance does not journal pages unnecessarily.
29 # io-2.* - Test the "atomic-write optimization".
31 # io-3.* - Test the IO traffic enhancements triggered when the
32 # IOCAP_SEQUENTIAL device capability flag is set (no
33 # fsync() calls on the journal file).
35 # io-4.* - Test the IO traffic enhancements triggered when the
36 # IOCAP_SAFE_APPEND device capability flag is set (fewer
37 # fsync() calls on the journal file, no need to set nRec
38 # field in the single journal header).
40 # io-5.* - Test that the default page size is selected and used
43 # io-6.* - Test that the pager-cache is not being flushed unnecessarily
44 # after a transaction that uses the special atomic-write path
50 set bt [btree_from_db $db]
52 array set stats [btree_pager_stats $bt]
54 set res [expr $stats(write) - $::nWrite]
55 set ::nWrite $stats(write)
61 set res [expr {$::sqlite_sync_count - $::nSync}]
62 set ::nSync $::sqlite_sync_count
68 PRAGMA auto_vacuum = OFF;
69 PRAGMA page_size = 1024;
70 CREATE TABLE abc(a,b);
75 # Insert into the table 4 records of aproximately 240 bytes each.
76 # This should completely fill the root-page of the table. Each
77 # INSERT causes 2 db pages to be written - the root-page of "abc"
78 # and page 1 (db change-counter page).
81 execsql { INSERT INTO abc VALUES(1,randstr(230,230)); }
82 lappend ret [nWrite db]
83 execsql { INSERT INTO abc VALUES(2,randstr(230,230)); }
84 lappend ret [nWrite db]
85 execsql { INSERT INTO abc VALUES(3,randstr(230,230)); }
86 lappend ret [nWrite db]
87 execsql { INSERT INTO abc VALUES(4,randstr(230,230)); }
88 lappend ret [nWrite db]
91 # Insert another 240 byte record. This causes two leaf pages
92 # to be added to the root page of abc. 4 pages in total
93 # are written to the db file - the two leaf pages, the root
94 # of abc and the change-counter page.
96 execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }
100 # Insert another 3 240 byte records. After this, the tree consists of
101 # the root-node, which is close to empty, and two leaf pages, both of
105 execsql { INSERT INTO abc VALUES(6,randstr(230,230)); }
106 lappend ret [nWrite db]
107 execsql { INSERT INTO abc VALUES(7,randstr(230,230)); }
108 lappend ret [nWrite db]
109 execsql { INSERT INTO abc VALUES(8,randstr(230,230)); }
110 lappend ret [nWrite db]
113 # This insert should use the quick-balance trick to add a third leaf
114 # to the b-tree used to store table abc. It should only be necessary to
115 # write to 3 pages to do this: the change-counter, the root-page and
118 execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }
122 ifcapable atomicwrite {
124 #----------------------------------------------------------------------
125 # Test cases io-2.* test the atomic-write optimization.
128 execsql { DELETE FROM abc; VACUUM; }
131 # Clear the write and sync counts.
134 # The following INSERT updates 2 pages and requires 4 calls to fsync():
136 # 1) The directory in which the journal file is created,
137 # 2) The journal file (to sync the page data),
138 # 3) The journal file (to sync the journal file header),
139 # 4) The database file.
142 execsql { INSERT INTO abc VALUES(1, 2) }
143 list [nWrite db] [nSync]
146 # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,
147 # then do another INSERT similar to the one in io-2.2. This should
148 # only write 1 page and require a single fsync().
150 # The single fsync() is the database file. Only one page is reported as
151 # written because page 1 - the change-counter page - is written using
152 # an out-of-band method that bypasses the write counter.
154 # UPDATE: As of [05f98d4eec] (adding SQLITE_DBSTATUS_CACHE_WRITE), the
155 # second write is also counted. So this now reports two writes and a
158 sqlite3_simulate_device -char atomic
160 execsql { INSERT INTO abc VALUES(3, 4) }
161 list [nWrite db] [nSync]
164 # Test that the journal file is not created and the change-counter is
165 # updated when the atomic-write optimization is used.
170 INSERT INTO abc VALUES(5, 6);
172 sqlite3 db2 test.db -vfs devsym
173 execsql { SELECT * FROM abc } db2
176 file exists test.db-journal
180 execsql { SELECT * FROM abc } db2
184 # Test that the journal file is created and sync()d if the transaction
185 # modifies more than one database page, even if the IOCAP_ATOMIC flag
189 execsql { CREATE TABLE def(d, e) }
193 INSERT INTO abc VALUES(7, 8);
195 file exists test.db-journal
198 execsql { INSERT INTO def VALUES('a', 'b'); }
199 file exists test.db-journal
203 list [nWrite db] [nSync]
206 # Test that the journal file is created and sync()d if the transaction
207 # modifies a single database page and also appends a page to the file.
208 # Internally, this case is handled differently to the one above. The
209 # journal file is not actually created until the 'COMMIT' statement
212 # Changed 2010-03-27: The size of the database is now stored in
213 # bytes 28..31 and so when a page is added to the database, page 1
214 # is immediately modified and the journal file immediately comes into
215 # existence. To fix this test, the BEGIN is changed into a a
216 # BEGIN IMMEDIATE and the INSERT is omitted.
221 -- INSERT INTO abc VALUES(9, randstr(1000,1000));
223 file exists test.db-journal
226 # Create a file at "test.db-journal". This will prevent SQLite from
227 # opening the journal for exclusive access. As a result, the COMMIT
228 # should fail with SQLITE_CANTOPEN and the transaction rolled back.
230 file mkdir test.db-journal
232 INSERT INTO abc VALUES(9, randstr(1000,1000));
235 } {1 {unable to open database file}}
237 forcedelete test.db-journal
241 execsql { SELECT * FROM abc }
244 # Test that if the database modification is part of multi-file commit,
245 # the journal file is always created. In this case, the journal file
246 # is created during execution of the COMMIT statement, so we have to
247 # use the same technique to check that it is created as in the above
249 forcedelete test2.db test2.db-journal
253 ATTACH 'test2.db' AS aux;
254 PRAGMA aux.page_size = 1024;
255 CREATE TABLE aux.abc2(a, b);
257 INSERT INTO abc VALUES(9, 10);
259 file exists test.db-journal
262 execsql { INSERT INTO abc2 SELECT * FROM abc }
263 file exists test2.db-journal
266 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
267 } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}
269 file mkdir test2.db-journal
271 } {1 {unable to open database file}}
273 forcedelete test2.db-journal
275 } {1 {cannot commit - no transaction is active}}
277 execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
281 # Try an explicit ROLLBACK before the journal file is created.
288 file exists test.db-journal
291 execsql { SELECT * FROM abc }
300 # Test that the atomic write optimisation is not enabled if the sector
301 # size is larger than the page-size.
306 sqlite3_simulate_device -char atomic -sectorsize 2048
309 INSERT INTO abc VALUES(9, 10);
311 file exists test.db-journal
314 execsql { ROLLBACK; }
316 forcedelete test.db test.db-journal
317 sqlite3 db test.db -vfs devsym
319 PRAGMA auto_vacuum = OFF;
320 PRAGMA page_size = 2048;
321 CREATE TABLE abc(a, b);
325 INSERT INTO abc VALUES(9, 10);
327 file exists test.db-journal
333 # Test a couple of the more specific IOCAP_ATOMIC flags
334 # (i.e IOCAP_ATOMIC2K etc.).
337 sqlite3_simulate_device -char atomic1k
340 INSERT INTO abc VALUES(11, 12);
342 file exists test.db-journal
346 sqlite3_simulate_device -char atomic2k
349 INSERT INTO abc VALUES(11, 12);
351 file exists test.db-journal
359 PRAGMA locking_mode = exclusive;
362 } {exclusive exclusive}
365 INSERT INTO abc VALUES(11, 12);
367 file exists test.db-journal
372 PRAGMA locking_mode = normal;
373 INSERT INTO abc VALUES(13, 14);
375 file exists test.db-journal
378 } ;# /* ifcapable atomicwrite */
380 #----------------------------------------------------------------------
381 # Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.
383 sqlite3_simulate_device -char sequential -sectorsize 0
384 ifcapable pager_pragmas {
387 forcedelete test.db test.db-journal
388 sqlite3 db test.db -vfs devsym
390 PRAGMA auto_vacuum=OFF;
392 # File size might be 1 due to the hack to work around ticket #3260.
393 # Search for #3260 in os_unix.c for additional information.
394 expr {[file size test.db]>1}
397 execsql { CREATE TABLE abc(a, b) }
400 PRAGMA temp_store = memory;
401 PRAGMA cache_size = 10;
403 INSERT INTO abc VALUES('hello', 'world');
404 INSERT INTO abc SELECT * FROM abc;
405 INSERT INTO abc SELECT * FROM abc;
406 INSERT INTO abc SELECT * FROM abc;
407 INSERT INTO abc SELECT * FROM abc;
408 INSERT INTO abc SELECT * FROM abc;
409 INSERT INTO abc SELECT * FROM abc;
410 INSERT INTO abc SELECT * FROM abc;
411 INSERT INTO abc SELECT * FROM abc;
412 INSERT INTO abc SELECT * FROM abc;
413 INSERT INTO abc SELECT * FROM abc;
414 INSERT INTO abc SELECT * FROM abc;
416 # File has grown - showing there was a cache-spill - but there
417 # have been no calls to fsync(). The file is probably about 30KB.
418 # But some VFS implementations (symbian) buffer writes so the actual
419 # size may be a little less than that. So this test case just tests
420 # that the file is now greater than 20000 bytes in size.
421 list [expr [file size test.db]>20000] [nSync]
424 # The COMMIT requires a single fsync() - to the database file.
426 list [file size test.db] [nSync]
427 } "[expr {[nonzero_reserved_bytes]?40960:39936}] 1"
430 #----------------------------------------------------------------------
431 # Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.
433 sqlite3_simulate_device -char safe_append
435 # With the SAFE_APPEND flag set, simple transactions require 3, rather
436 # than 4, calls to fsync(). The fsync() calls are on:
438 # 1) The directory in which the journal file is created, (unix only)
439 # 2) The journal file (to sync the page data),
440 # 3) The database file.
442 # Normally, when the SAFE_APPEND flag is not set, there is another fsync()
443 # on the journal file between steps (2) and (3) above.
445 set expected_sync_count 2
446 if {$::tcl_platform(platform)=="unix"} {
448 incr expected_sync_count
453 execsql { DELETE FROM abc }
455 execsql { INSERT INTO abc VALUES('a', 'b') }
457 } $expected_sync_count
459 # With SAFE_APPEND set, the nRec field of the journal file header should
460 # be set to 0xFFFFFFFF before the first journal sync. The nRec field
461 # occupies bytes 8-11 of the journal file.
465 execsql { INSERT INTO abc VALUES('c', 'd') }
466 file exists test.db-journal
468 if {$::tcl_platform(platform)=="unix"} {
470 hexio_read test.db-journal 8 4
476 } $expected_sync_count
477 sqlite3_simulate_device -char safe_append
479 # With SAFE_APPEND set, there should only ever be one journal-header
480 # written to the database, even though the sync-mode is "full".
484 INSERT INTO abc SELECT * FROM abc;
485 INSERT INTO abc SELECT * FROM abc;
486 INSERT INTO abc SELECT * FROM abc;
487 INSERT INTO abc SELECT * FROM abc;
488 INSERT INTO abc SELECT * FROM abc;
489 INSERT INTO abc SELECT * FROM abc;
490 INSERT INTO abc SELECT * FROM abc;
491 INSERT INTO abc SELECT * FROM abc;
492 INSERT INTO abc SELECT * FROM abc;
493 INSERT INTO abc SELECT * FROM abc;
494 INSERT INTO abc SELECT * FROM abc;
496 expr {[file size test.db]/1024}
498 ifcapable pager_pragmas {
501 PRAGMA synchronous = full;
502 PRAGMA cache_size = 10;
510 UPDATE abc SET a = 'x';
512 file exists test.db-journal
514 if {$tcl_platform(platform) != "symbian"} {
515 # This test is not run on symbian because the file-buffer makes it
516 # difficult to predict the exact size of the file as reported by
519 # The UPDATE statement in the statement above modifies 41 pages
520 # (all pages in the database except page 1 and the root page of
521 # abc). Because the cache_size is set to 10, this must have required
522 # at least 4 cache-spills. If there were no journal headers written
523 # to the journal file after the cache-spill, then the size of the
524 # journal file is give by:
526 # <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8)
528 # If the journal file contains additional headers, this formula
529 # will not predict the size of the journal file.
531 file size test.db-journal
532 } [expr 512 + (1024+8)*41]
535 #----------------------------------------------------------------------
536 # Test cases io-5.* test that the default page size is selected and
540 foreach {char sectorsize pgsize} {
550 {atomic2K atomic} 512 8192
554 if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue
556 forcedelete test.db test.db-journal
557 sqlite3_simulate_device -char $char -sectorsize $sectorsize
558 sqlite3 db test.db -vfs devsym
560 PRAGMA auto_vacuum=OFF;
562 ifcapable !atomicwrite {
563 if {[regexp {^atomic} $char]} continue
567 CREATE TABLE abc(a, b, c);
569 expr {[file size test.db]/2}
573 #----------------------------------------------------------------------
577 sqlite3_simulate_device -char atomic
579 sqlite3 db test.db -vfs devsym
581 PRAGMA mmap_size = 0;
582 PRAGMA page_size = 1024;
583 PRAGMA cache_size = 2000;
587 CREATE INDEX i3 ON t3(x);
588 INSERT INTO t3 VALUES(randomblob(100));
589 INSERT INTO t3 SELECT randomblob(100) FROM t3;
590 INSERT INTO t3 SELECT randomblob(100) FROM t3;
591 INSERT INTO t3 SELECT randomblob(100) FROM t3;
592 INSERT INTO t3 SELECT randomblob(100) FROM t3;
593 INSERT INTO t3 SELECT randomblob(100) FROM t3;
594 INSERT INTO t3 SELECT randomblob(100) FROM t3;
595 INSERT INTO t3 SELECT randomblob(100) FROM t3;
596 INSERT INTO t3 SELECT randomblob(100) FROM t3;
597 INSERT INTO t3 SELECT randomblob(100) FROM t3;
598 INSERT INTO t3 SELECT randomblob(100) FROM t3;
599 INSERT INTO t3 SELECT randomblob(100) FROM t3;
607 INSERT INTO t1 VALUES('123');
608 INSERT INTO t2 VALUES('456');
612 INSERT INTO t1 VALUES('123');
617 # These tests don't work with memsubsys1, as it causes the effective page
618 # cache size to become too small to hold the entire db in memory.
619 if {[permutation] == "memsubsys1"} continue
622 sqlite3 db test.db -vfs devsym
624 PRAGMA cache_size = 2000;
625 PRAGMA mmap_size = 0;
626 SELECT x FROM t3 ORDER BY rowid;
627 SELECT x FROM t3 ORDER BY x;
629 do_execsql_test 6.2.$tn.1 { PRAGMA integrity_check } {ok}
630 do_execsql_test 6.2.$tn.2 $sql
632 # Corrupt the database file on disk. This should not matter for the
633 # purposes of the following "PRAGMA integrity_check", as the entire
634 # database should be cached in the pager-cache. If corruption is
635 # reported, it indicates that executing $sql caused the pager cache
636 # to be flushed. Which is a bug.
637 hexio_write test.db [expr 1024 * 5] [string repeat 00 2048]
638 do_execsql_test 6.2.$tn.3 { PRAGMA integrity_check } {ok}
642 sqlite3_simulate_device -char {} -sectorsize 0