# 2007 August 21 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # The focus of this file is testing some specific characteristics of the # IO traffic generated by SQLite (making sure SQLite is not writing out # more database pages than it has to, stuff like that). # # $Id: io.test,v 1.4 2007/08/23 11:07:10 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Test summary: # # io-1.* - Test that quick-balance does not journal pages unnecessarily. # io-2.* - Test that when the atomic-write optimisation is used no # journal file is created. # set ::nWrite 0 proc nWrite {db} { set bt [btree_from_db $db] db_enter $db array set stats [btree_pager_stats $bt] db_leave $db set res [expr $stats(write) - $::nWrite] set ::nWrite $stats(write) set res } set ::nSync 0 proc nSync {} { set res [expr {$::sqlite_sync_count - $::nSync}] set ::nSync $::sqlite_sync_count set res } do_test io-1.1 { execsql { PRAGMA page_size = 1024; CREATE TABLE abc(a,b); } nWrite db } {2} # Insert into the table 4 records of aproximately 240 bytes each. # This should completely fill the root-page of the table. Each # INSERT causes 2 db pages to be written - the root-page of "abc" # and page 1 (db change-counter page). do_test io-1.2 { set ret [list] execsql { INSERT INTO abc VALUES(1,randstr(230,230)); } lappend ret [nWrite db] execsql { INSERT INTO abc VALUES(2,randstr(230,230)); } lappend ret [nWrite db] execsql { INSERT INTO abc VALUES(3,randstr(230,230)); } lappend ret [nWrite db] execsql { INSERT INTO abc VALUES(4,randstr(230,230)); } lappend ret [nWrite db] } {2 2 2 2} # Insert another 240 byte record. This causes two leaf pages # to be added to the root page of abc. 4 pages in total # are written to the db file - the two leaf pages, the root # of abc and the change-counter page. do_test io-1.3 { execsql { INSERT INTO abc VALUES(5,randstr(230,230)); } nWrite db } {4} # Insert another 3 240 byte records. After this, the tree consists of # the root-node, which is close to empty, and two leaf pages, both of # which are full. do_test io-1.4 { set ret [list] execsql { INSERT INTO abc VALUES(6,randstr(230,230)); } lappend ret [nWrite db] execsql { INSERT INTO abc VALUES(7,randstr(230,230)); } lappend ret [nWrite db] execsql { INSERT INTO abc VALUES(8,randstr(230,230)); } lappend ret [nWrite db] } {2 2 2} # This insert should use the quick-balance trick to add a third leaf # to the b-tree used to store table abc. It should only be necessary to # write to 3 pages to do this: the change-counter, the root-page and # the new leaf page. do_test io-1.5 { execsql { INSERT INTO abc VALUES(9,randstr(230,230)); } nWrite db } {3} #---------------------------------------------------------------------- # Test cases io-2.* test the atomic-write optimization. # do_test io-2.1 { execsql { DELETE FROM abc; VACUUM; } } {} # Clear the write and sync counts. nWrite db ; nSync # The following INSERT updates 2 pages and requires 4 calls to fsync(): # # 1) The directory in which the journal file is created, # 2) The journal file (to sync the page data), # 3) The journal file (to sync the journal file header), # 4) The database file. # do_test io-2.2 { execsql { INSERT INTO abc VALUES(1, 2) } list [nWrite db] [nSync] } {2 4} # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC, # then do another INSERT similar to the one in io-2.2. This should # only write 1 page and require a single fsync(). # # The single fsync() is the database file. Only one page is reported as # written because page 1 - the change-counter page - is written using # an out-of-band method that bypasses the write counter. # sqlite3_simulate_device -char atomic do_test io-2.3 { execsql { INSERT INTO abc VALUES(3, 4) } list [nWrite db] [nSync] } {1 1} # Test that the journal file is not created and the change-counter is # updated when the atomic-write optimization is used. # do_test io-2.4.1 { execsql { BEGIN; INSERT INTO abc VALUES(5, 6); } sqlite3 db2 test.db execsql { SELECT * FROM abc } db2 } {1 2 3 4} do_test io-2.4.2 { file exists test.db-journal } {0} do_test io-2.4.3 { execsql { COMMIT } execsql { SELECT * FROM abc } db2 } {1 2 3 4 5 6} db2 close # Test that the journal file is created and sync()d if the transaction # modifies more than one database page, even if the IOCAP_ATOMIC flag # is set. # do_test io-2.5.1 { execsql { CREATE TABLE def(d, e) } nWrite db ; nSync execsql { BEGIN; INSERT INTO abc VALUES(7, 8); } file exists test.db-journal } {0} do_test io-2.5.2 { execsql { INSERT INTO def VALUES('a', 'b'); } file exists test.db-journal } {1} do_test io-2.5.3 { execsql { COMMIT } list [nWrite db] [nSync] } {3 4} # Test that the journal file is created and sync()d if the transaction # modifies a single database page and also appends a page to the file. # Internally, this case is handled differently to the one above. The # journal file is not actually created until the 'COMMIT' statement # is executed. # do_test io-2.6.1 { execsql { BEGIN; INSERT INTO abc VALUES(9, randstr(1000,1000)); } file exists test.db-journal } {0} do_test io-2.6.2 { # Create a file at "test.db-journal". This will prevent SQLite from # opening the journal for exclusive access. As a result, the COMMIT # should fail with SQLITE_CANTOPEN and the transaction rolled back. # set fd [open test.db-journal w] puts $fd "This is not a journal file" close $fd catchsql { COMMIT } } {1 {unable to open database file}} do_test io-2.6.3 { file delete -force test.db-journal catchsql { COMMIT } } {1 {cannot commit - no transaction is active}} do_test io-2.6.4 { execsql { SELECT * FROM abc } } {1 2 3 4 5 6 7 8} # Test that if the database modification is part of multi-file commit, # the journal file is always created. In this case, the journal file # is created during execution of the COMMIT statement, so we have to # use the same technique to check that it is created as in the above # block. file delete -force test2.db test2.db-journal do_test io-2.7.1 { execsql { ATTACH 'test2.db' AS aux; CREATE TABLE aux.abc2(a, b); BEGIN; INSERT INTO abc VALUES(9, 10); } file exists test.db-journal } {0} do_test io-2.7.2 { execsql { INSERT INTO abc2 SELECT * FROM abc } file exists test2.db-journal } {0} do_test io-2.7.3 { execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10} do_test io-2.7.4 { set fd [open test2.db-journal w] puts $fd "This is not a journal file" close $fd catchsql { COMMIT } } {1 {unable to open database file}} do_test io-2.7.5 { file delete -force test2.db-journal catchsql { COMMIT } } {1 {cannot commit - no transaction is active}} do_test io-2.7.6 { execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 } } {1 2 3 4 5 6 7 8} # Try an explicit ROLLBACK before the journal file is created. # do_test io-2.8.1 { execsql { BEGIN; DELETE FROM abc; } file exists test.db-journal } {0} do_test io-2.8.2 { execsql { SELECT * FROM abc } } {} do_test io-2.8.3 { execsql { ROLLBACK; SELECT * FROM abc; } } {1 2 3 4 5 6 7 8} # Test that the atomic write optimisation is not enabled if the sector # size is larger than the page-size. # do_test io-2.9.1 { sqlite3_simulate_device -char atomic -sectorsize 2048 execsql { BEGIN; INSERT INTO abc VALUES(9, 10); } file exists test.db-journal } {1} do_test io-2.9.2 { execsql { ROLLBACK; } db close file delete -force test.db test.db-journal sqlite3 db test.db execsql { PRAGMA page_size = 2048; CREATE TABLE abc(a, b); } execsql { BEGIN; INSERT INTO abc VALUES(9, 10); } file exists test.db-journal } {0} do_test io-2.9.3 { execsql { COMMIT } } {} # Test a couple of the more specific IOCAP_ATOMIC flags # (i.e IOCAP_ATOMIC2K etc.). # do_test io-2.10.1 { sqlite3_simulate_device -char atomic1k execsql { BEGIN; INSERT INTO abc VALUES(11, 12); } file exists test.db-journal } {1} do_test io-2.10.2 { execsql { ROLLBACK } sqlite3_simulate_device -char atomic2k execsql { BEGIN; INSERT INTO abc VALUES(11, 12); } file exists test.db-journal } {0} sqlite3_simulate_device -char {} -sectorsize 0 finish_test