sl@0: # 2007 August 21
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: #
sl@0: # The focus of this file is testing some specific characteristics of the 
sl@0: # IO traffic generated by SQLite (making sure SQLite is not writing out
sl@0: # more database pages than it has to, stuff like that).
sl@0: #
sl@0: # $Id: io.test,v 1.19 2008/09/18 11:18:41 danielk1977 Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: db close
sl@0: sqlite3_simulate_device
sl@0: sqlite3 db test.db -vfs devsym
sl@0: 
sl@0: # Test summary:
sl@0: #
sl@0: # io-1.* -  Test that quick-balance does not journal pages unnecessarily.
sl@0: #
sl@0: # io-2.* -  Test the "atomic-write optimization".
sl@0: #
sl@0: # io-3.* -  Test the IO traffic enhancements triggered when the 
sl@0: #           IOCAP_SEQUENTIAL device capability flag is set (no 
sl@0: #           fsync() calls on the journal file).
sl@0: #
sl@0: # io-4.* -  Test the IO traffic enhancements triggered when the 
sl@0: #           IOCAP_SAFE_APPEND device capability flag is set (fewer 
sl@0: #           fsync() calls on the journal file, no need to set nRec
sl@0: #           field in the single journal header).
sl@0: #
sl@0: # io-5.* -  Test that the default page size is selected and used 
sl@0: #           correctly.
sl@0: #           
sl@0: 
sl@0: set ::nWrite 0
sl@0: proc nWrite {db} {
sl@0:   set bt [btree_from_db $db]
sl@0:   db_enter $db
sl@0:   array set stats [btree_pager_stats $bt]
sl@0:   db_leave $db
sl@0:   set res [expr $stats(write) - $::nWrite]
sl@0:   set ::nWrite $stats(write)
sl@0:   set res
sl@0: }
sl@0: 
sl@0: set ::nSync 0
sl@0: proc nSync {} {
sl@0:   set res [expr {$::sqlite_sync_count - $::nSync}]
sl@0:   set ::nSync $::sqlite_sync_count
sl@0:   set res
sl@0: }
sl@0: 
sl@0: do_test io-1.1 {
sl@0:   execsql {
sl@0:     PRAGMA auto_vacuum = OFF;
sl@0:     PRAGMA page_size = 1024;
sl@0:     CREATE TABLE abc(a,b);
sl@0:   }
sl@0:   nWrite db
sl@0: } {2}
sl@0: 
sl@0: # Insert into the table 4 records of aproximately 240 bytes each.
sl@0: # This should completely fill the root-page of the table. Each
sl@0: # INSERT causes 2 db pages to be written - the root-page of "abc"
sl@0: # and page 1 (db change-counter page).
sl@0: do_test io-1.2 {
sl@0:   set ret [list]
sl@0:   execsql { INSERT INTO abc VALUES(1,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0:   execsql { INSERT INTO abc VALUES(2,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0:   execsql { INSERT INTO abc VALUES(3,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0:   execsql { INSERT INTO abc VALUES(4,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0: } {2 2 2 2}
sl@0: 
sl@0: # Insert another 240 byte record. This causes two leaf pages
sl@0: # to be added to the root page of abc. 4 pages in total
sl@0: # are written to the db file - the two leaf pages, the root
sl@0: # of abc and the change-counter page.
sl@0: do_test io-1.3 {
sl@0:   execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }
sl@0:   nWrite db
sl@0: } {4}
sl@0: 
sl@0: # Insert another 3 240 byte records. After this, the tree consists of 
sl@0: # the root-node, which is close to empty, and two leaf pages, both of 
sl@0: # which are full. 
sl@0: do_test io-1.4 {
sl@0:   set ret [list]
sl@0:   execsql { INSERT INTO abc VALUES(6,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0:   execsql { INSERT INTO abc VALUES(7,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0:   execsql { INSERT INTO abc VALUES(8,randstr(230,230)); }
sl@0:   lappend ret [nWrite db]
sl@0: } {2 2 2}
sl@0: 
sl@0: # This insert should use the quick-balance trick to add a third leaf
sl@0: # to the b-tree used to store table abc. It should only be necessary to
sl@0: # write to 3 pages to do this: the change-counter, the root-page and
sl@0: # the new leaf page.
sl@0: do_test io-1.5 {
sl@0:   execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }
sl@0:   nWrite db
sl@0: } {3}
sl@0: 
sl@0: ifcapable atomicwrite {
sl@0: 
sl@0: #----------------------------------------------------------------------
sl@0: # Test cases io-2.* test the atomic-write optimization.
sl@0: #
sl@0: do_test io-2.1 {
sl@0:   execsql { DELETE FROM abc; VACUUM; }
sl@0: } {}
sl@0: 
sl@0: # Clear the write and sync counts.
sl@0: nWrite db ; nSync
sl@0: 
sl@0: # The following INSERT updates 2 pages and requires 4 calls to fsync():
sl@0: #
sl@0: #   1) The directory in which the journal file is created,
sl@0: #   2) The journal file (to sync the page data),
sl@0: #   3) The journal file (to sync the journal file header),
sl@0: #   4) The database file.
sl@0: #
sl@0: do_test io-2.2 {
sl@0:   execsql { INSERT INTO abc VALUES(1, 2) }
sl@0:   list [nWrite db] [nSync]
sl@0: } {2 4}
sl@0: 
sl@0: # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,
sl@0: # then do another INSERT similar to the one in io-2.2. This should
sl@0: # only write 1 page and require a single fsync().
sl@0: # 
sl@0: # The single fsync() is the database file. Only one page is reported as
sl@0: # written because page 1 - the change-counter page - is written using
sl@0: # an out-of-band method that bypasses the write counter.
sl@0: #
sl@0: sqlite3_simulate_device -char atomic
sl@0: do_test io-2.3 {
sl@0:   execsql { INSERT INTO abc VALUES(3, 4) }
sl@0:   list [nWrite db] [nSync]
sl@0: } {1 1}
sl@0: 
sl@0: # Test that the journal file is not created and the change-counter is
sl@0: # updated when the atomic-write optimization is used.
sl@0: #
sl@0: do_test io-2.4.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(5, 6);
sl@0:   }
sl@0:   sqlite3 db2 test.db -vfs devsym
sl@0:   execsql { SELECT * FROM abc } db2
sl@0: } {1 2 3 4}
sl@0: do_test io-2.4.2 {
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.4.3 {
sl@0:   execsql { COMMIT }
sl@0:   execsql { SELECT * FROM abc } db2
sl@0: } {1 2 3 4 5 6}
sl@0: db2 close
sl@0: 
sl@0: # Test that the journal file is created and sync()d if the transaction
sl@0: # modifies more than one database page, even if the IOCAP_ATOMIC flag
sl@0: # is set.
sl@0: #
sl@0: do_test io-2.5.1 {
sl@0:   execsql { CREATE TABLE def(d, e) }
sl@0:   nWrite db ; nSync
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(7, 8);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.5.2 {
sl@0:   execsql { INSERT INTO def VALUES('a', 'b'); }
sl@0:   file exists test.db-journal
sl@0: } {1}
sl@0: do_test io-2.5.3 {
sl@0:   execsql { COMMIT }
sl@0:   list [nWrite db] [nSync]
sl@0: } {3 4}
sl@0: 
sl@0: # Test that the journal file is created and sync()d if the transaction
sl@0: # modifies a single database page and also appends a page to the file.
sl@0: # Internally, this case is handled differently to the one above. The
sl@0: # journal file is not actually created until the 'COMMIT' statement
sl@0: # is executed.
sl@0: #
sl@0: do_test io-2.6.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(9, randstr(1000,1000));
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.6.2 {
sl@0:   # Create a file at "test.db-journal". This will prevent SQLite from
sl@0:   # opening the journal for exclusive access. As a result, the COMMIT
sl@0:   # should fail with SQLITE_CANTOPEN and the transaction rolled back.
sl@0:   #
sl@0:   set fd [open test.db-journal w]
sl@0:   puts $fd "This is not a journal file"
sl@0:   close $fd
sl@0:   catchsql { COMMIT }
sl@0: } {1 {unable to open database file}}
sl@0: do_test io-2.6.3 {
sl@0:   file delete -force test.db-journal
sl@0:   catchsql { COMMIT }
sl@0: } {1 {cannot commit - no transaction is active}}
sl@0: do_test io-2.6.4 {
sl@0:   execsql { SELECT * FROM abc }
sl@0: } {1 2 3 4 5 6 7 8}
sl@0: 
sl@0: 
sl@0: # Test that if the database modification is part of multi-file commit,
sl@0: # the journal file is always created. In this case, the journal file
sl@0: # is created during execution of the COMMIT statement, so we have to
sl@0: # use the same technique to check that it is created as in the above 
sl@0: # block.
sl@0: file delete -force test2.db test2.db-journal
sl@0: ifcapable attach {
sl@0:   do_test io-2.7.1 {
sl@0:     execsql {
sl@0:       ATTACH 'test2.db' AS aux;
sl@0:       PRAGMA aux.page_size = 1024;
sl@0:       CREATE TABLE aux.abc2(a, b);
sl@0:       BEGIN;
sl@0:       INSERT INTO abc VALUES(9, 10);
sl@0:     }
sl@0:     file exists test.db-journal
sl@0:   } {0}
sl@0:   do_test io-2.7.2 {
sl@0:     execsql { INSERT INTO abc2 SELECT * FROM abc }
sl@0:     file exists test2.db-journal
sl@0:   } {0}
sl@0:   do_test io-2.7.3 {
sl@0:     execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
sl@0:   } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}
sl@0:   do_test io-2.7.4 {
sl@0:     set fd [open test2.db-journal w]
sl@0:     puts $fd "This is not a journal file"
sl@0:     close $fd
sl@0:     catchsql { COMMIT }
sl@0:   } {1 {unable to open database file}}
sl@0:   do_test io-2.7.5 {
sl@0:     file delete -force test2.db-journal
sl@0:     catchsql { COMMIT }
sl@0:   } {1 {cannot commit - no transaction is active}}
sl@0:   do_test io-2.7.6 {
sl@0:     execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
sl@0:   } {1 2 3 4 5 6 7 8}
sl@0: }
sl@0: 
sl@0: # Try an explicit ROLLBACK before the journal file is created.
sl@0: #
sl@0: do_test io-2.8.1 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     DELETE FROM abc;
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.8.2 {
sl@0:   execsql { SELECT * FROM abc }
sl@0: } {}
sl@0: do_test io-2.8.3 {
sl@0:   execsql {
sl@0:     ROLLBACK;
sl@0:     SELECT * FROM abc;
sl@0:   }
sl@0: } {1 2 3 4 5 6 7 8}
sl@0: 
sl@0: # Test that the atomic write optimisation is not enabled if the sector
sl@0: # size is larger than the page-size.
sl@0: #
sl@0: do_test io-2.9.1 {
sl@0:   sqlite3_simulate_device -char atomic -sectorsize 2048
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(9, 10);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {1}
sl@0: do_test io-2.9.2 {
sl@0:   execsql { ROLLBACK; }
sl@0:   db close
sl@0:   file delete -force test.db test.db-journal
sl@0:   sqlite3 db test.db -vfs devsym
sl@0:   execsql {
sl@0:     PRAGMA auto_vacuum = OFF;
sl@0:     PRAGMA page_size = 2048;
sl@0:     CREATE TABLE abc(a, b);
sl@0:   }
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(9, 10);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.9.3 {
sl@0:   execsql { COMMIT }
sl@0: } {}
sl@0: 
sl@0: # Test a couple of the more specific IOCAP_ATOMIC flags 
sl@0: # (i.e IOCAP_ATOMIC2K etc.).
sl@0: #
sl@0: do_test io-2.10.1 {
sl@0:   sqlite3_simulate_device -char atomic1k
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(11, 12);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {1}
sl@0: do_test io-2.10.2 {
sl@0:   execsql { ROLLBACK }
sl@0:   sqlite3_simulate_device -char atomic2k
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     INSERT INTO abc VALUES(11, 12);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: do_test io-2.10.3 {
sl@0:   execsql { ROLLBACK }
sl@0: } {}
sl@0: 
sl@0: do_test io-2.11.0 {
sl@0:   execsql { 
sl@0:     PRAGMA locking_mode = exclusive;
sl@0:     PRAGMA locking_mode;
sl@0:   }
sl@0: } {exclusive exclusive}
sl@0: do_test io-2.11.1 {
sl@0:   execsql { 
sl@0:     INSERT INTO abc VALUES(11, 12);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: 
sl@0: do_test io-2.11.2 {
sl@0:   execsql { 
sl@0:     PRAGMA locking_mode = normal;
sl@0:     INSERT INTO abc VALUES(13, 14);
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {0}
sl@0: 
sl@0: } ;# /* ifcapable atomicwrite */
sl@0: 
sl@0: #----------------------------------------------------------------------
sl@0: # Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.
sl@0: #
sl@0: sqlite3_simulate_device -char sequential -sectorsize 0
sl@0: ifcapable pager_pragmas {
sl@0:   do_test io-3.1 {
sl@0:     db close
sl@0:     file delete -force test.db test.db-journal
sl@0:     sqlite3 db test.db -vfs devsym
sl@0:     db eval {
sl@0:       PRAGMA auto_vacuum=OFF;
sl@0:     }
sl@0:     # File size might be 1 due to the hack to work around ticket #3260.
sl@0:     # Search for #3260 in os_unix.c for additional information.
sl@0:     expr {[file size test.db]>1}
sl@0:   } {0}
sl@0:   do_test io-3.2 {
sl@0:     execsql { CREATE TABLE abc(a, b) }
sl@0:     nSync
sl@0:     execsql {
sl@0:       PRAGMA temp_store = memory;
sl@0:       PRAGMA cache_size = 10;
sl@0:       BEGIN;
sl@0:       INSERT INTO abc VALUES('hello', 'world');
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:       INSERT INTO abc SELECT * FROM abc;
sl@0:     }
sl@0:     # File has grown - showing there was a cache-spill - but there 
sl@0:     # have been no calls to fsync(). The file is probably about 30KB.
sl@0:     # But some VFS implementations (symbian) buffer writes so the actual
sl@0:     # size may be a little less than that. So this test case just tests
sl@0:     # that the file is now greater than 20000 bytes in size.
sl@0:     list [expr [file size test.db]>20000] [nSync]
sl@0:   } {1 0}
sl@0:   do_test io-3.3 {
sl@0:     # The COMMIT requires a single fsync() - to the database file.
sl@0:     execsql { COMMIT }
sl@0:     list [file size test.db] [nSync]
sl@0:   } {39936 1}
sl@0: }
sl@0: 
sl@0: #----------------------------------------------------------------------
sl@0: # Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.
sl@0: #
sl@0: sqlite3_simulate_device -char safe_append
sl@0: 
sl@0: # With the SAFE_APPEND flag set, simple transactions require 3, rather
sl@0: # than 4, calls to fsync(). The fsync() calls are on:
sl@0: #
sl@0: #   1) The directory in which the journal file is created, (unix only)
sl@0: #   2) The journal file (to sync the page data),
sl@0: #   3) The database file.
sl@0: #
sl@0: # Normally, when the SAFE_APPEND flag is not set, there is another fsync()
sl@0: # on the journal file between steps (2) and (3) above.
sl@0: #
sl@0: if {$::tcl_platform(platform)=="unix"} {
sl@0:   set expected_sync_count 3
sl@0: } else {
sl@0:   set expected_sync_count 2
sl@0: }
sl@0: do_test io-4.1 {
sl@0:   execsql { DELETE FROM abc }
sl@0:   nSync
sl@0:   execsql { INSERT INTO abc VALUES('a', 'b') }
sl@0:   nSync
sl@0: } $expected_sync_count
sl@0: 
sl@0: # With SAFE_APPEND set, the nRec field of the journal file header should
sl@0: # be set to 0xFFFFFFFF before the first journal sync. The nRec field
sl@0: # occupies bytes 8-11 of the journal file.
sl@0: #
sl@0: do_test io-4.2.1 {
sl@0:   execsql { BEGIN }
sl@0:   execsql { INSERT INTO abc VALUES('c', 'd') }
sl@0:   file exists test.db-journal
sl@0: } {1}
sl@0: if {$::tcl_platform(platform)=="unix"} {
sl@0:   do_test io-4.2.2 {
sl@0:     hexio_read test.db-journal 8 4
sl@0:   } {FFFFFFFF}
sl@0: }
sl@0: do_test io-4.2.3 {
sl@0:   execsql { COMMIT }
sl@0:   nSync
sl@0: } $expected_sync_count
sl@0: sqlite3_simulate_device -char safe_append
sl@0: 
sl@0: # With SAFE_APPEND set, there should only ever be one journal-header
sl@0: # written to the database, even though the sync-mode is "full".
sl@0: #
sl@0: do_test io-4.3.1 {
sl@0:   execsql {
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:     INSERT INTO abc SELECT * FROM abc;
sl@0:   }
sl@0:   expr {[file size test.db]/1024}
sl@0: } {43}
sl@0: ifcapable pager_pragmas {
sl@0:   do_test io-4.3.2 {
sl@0:     execsql {
sl@0:       PRAGMA synchronous = full;
sl@0:       PRAGMA cache_size = 10;
sl@0:       PRAGMA synchronous;
sl@0:     }
sl@0:   } {2}
sl@0: }
sl@0: do_test io-4.3.3 {
sl@0:   execsql {
sl@0:     BEGIN;
sl@0:     UPDATE abc SET a = 'x';
sl@0:   }
sl@0:   file exists test.db-journal
sl@0: } {1}
sl@0: if {$tcl_platform(platform) != "symbian"} {
sl@0:   # This test is not run on symbian because the file-buffer makes it
sl@0:   # difficult to predict the exact size of the file as reported by 
sl@0:   # [file size].
sl@0:   do_test io-4.3.4 {
sl@0:     # The UPDATE statement in the statement above modifies 41 pages 
sl@0:     # (all pages in the database except page 1 and the root page of 
sl@0:     # abc). Because the cache_size is set to 10, this must have required
sl@0:     # at least 4 cache-spills. If there were no journal headers written
sl@0:     # to the journal file after the cache-spill, then the size of the
sl@0:     # journal file is give by:
sl@0:     #
sl@0:     #    <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8)
sl@0:     #
sl@0:     # If the journal file contains additional headers, this formula
sl@0:     # will not predict the size of the journal file.
sl@0:     #
sl@0:     file size test.db-journal
sl@0:   } [expr 512 + (1024+8)*41]
sl@0: }
sl@0: 
sl@0: #----------------------------------------------------------------------
sl@0: # Test cases io-5.* test that the default page size is selected and
sl@0: # used correctly.
sl@0: #
sl@0: set tn 0
sl@0: foreach {char                 sectorsize pgsize} {
sl@0:          {}                     512      1024
sl@0:          {}                    1024      1024
sl@0:          {}                    2048      2048
sl@0:          {}                    8192      8192
sl@0:          {}                   16384      8192
sl@0:          {atomic}               512      8192
sl@0:          {atomic512}            512      1024
sl@0:          {atomic2K}             512      2048
sl@0:          {atomic2K}            4096      4096
sl@0:          {atomic2K atomic}      512      8192
sl@0:          {atomic64K}            512      1024
sl@0: } {
sl@0:   incr tn
sl@0:   if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue
sl@0:   db close
sl@0:   file delete -force test.db test.db-journal
sl@0:   sqlite3_simulate_device -char $char -sectorsize $sectorsize
sl@0:   sqlite3 db test.db -vfs devsym
sl@0:   db eval {
sl@0:     PRAGMA auto_vacuum=OFF;
sl@0:   }
sl@0:   ifcapable !atomicwrite {
sl@0:     if {[regexp {^atomic} $char]} continue
sl@0:   }
sl@0:   do_test io-5.$tn {
sl@0:     execsql {
sl@0:       CREATE TABLE abc(a, b, c);
sl@0:     }
sl@0:     expr {[file size test.db]/2}
sl@0:   } $pgsize
sl@0: }
sl@0: 
sl@0: sqlite3_simulate_device -char {} -sectorsize 0
sl@0: finish_test