# 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.19 2008/09/18 11:18:41 danielk1977 Exp $

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 db close

 sqlite3_simulate_device

 sqlite3 db test.db -vfs devsym

 # Test summary:

 #

 # io-1.* -  Test that quick-balance does not journal pages unnecessarily.

 #

 # io-2.* -  Test the "atomic-write optimization".

 #

 # io-3.* -  Test the IO traffic enhancements triggered when the 

 #           IOCAP_SEQUENTIAL device capability flag is set (no 

 #           fsync() calls on the journal file).

 #

 # io-4.* -  Test the IO traffic enhancements triggered when the 

 #           IOCAP_SAFE_APPEND device capability flag is set (fewer 

 #           fsync() calls on the journal file, no need to set nRec

 #           field in the single journal header).

 #

 # io-5.* -  Test that the default page size is selected and used 

 #           correctly.

 #           

 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 auto_vacuum = OFF;

     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}

 ifcapable atomicwrite {

 #----------------------------------------------------------------------

 # 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 -vfs devsym

   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

 ifcapable attach {

   do_test io-2.7.1 {

     execsql {

       ATTACH 'test2.db' AS aux;

       PRAGMA aux.page_size = 1024;

       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 -vfs devsym

   execsql {

     PRAGMA auto_vacuum = OFF;

     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}

 do_test io-2.10.3 {

   execsql { ROLLBACK }

 } {}

 do_test io-2.11.0 {

   execsql { 

     PRAGMA locking_mode = exclusive;

     PRAGMA locking_mode;

   }

 } {exclusive exclusive}

 do_test io-2.11.1 {

   execsql { 

     INSERT INTO abc VALUES(11, 12);

   }

   file exists test.db-journal

 } {0}

 do_test io-2.11.2 {

   execsql { 

     PRAGMA locking_mode = normal;

     INSERT INTO abc VALUES(13, 14);

   }

   file exists test.db-journal

 } {0}

 } ;# /* ifcapable atomicwrite */

 #----------------------------------------------------------------------

 # Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.

 #

 sqlite3_simulate_device -char sequential -sectorsize 0

 ifcapable pager_pragmas {

   do_test io-3.1 {

     db close

     file delete -force test.db test.db-journal

     sqlite3 db test.db -vfs devsym

     db eval {

       PRAGMA auto_vacuum=OFF;

     }

     # File size might be 1 due to the hack to work around ticket #3260.

     # Search for #3260 in os_unix.c for additional information.

     expr {[file size test.db]>1}

   } {0}

   do_test io-3.2 {

     execsql { CREATE TABLE abc(a, b) }

     nSync

     execsql {

       PRAGMA temp_store = memory;

       PRAGMA cache_size = 10;

       BEGIN;

       INSERT INTO abc VALUES('hello', 'world');

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

       INSERT INTO abc SELECT * FROM abc;

     }

     # File has grown - showing there was a cache-spill - but there 

     # have been no calls to fsync(). The file is probably about 30KB.

     # But some VFS implementations (symbian) buffer writes so the actual

     # size may be a little less than that. So this test case just tests

     # that the file is now greater than 20000 bytes in size.

     list [expr [file size test.db]>20000] [nSync]

   } {1 0}

   do_test io-3.3 {

     # The COMMIT requires a single fsync() - to the database file.

     execsql { COMMIT }

     list [file size test.db] [nSync]

   } {39936 1}

 }

 #----------------------------------------------------------------------

 # Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.

 #

 sqlite3_simulate_device -char safe_append

 # With the SAFE_APPEND flag set, simple transactions require 3, rather

 # than 4, calls to fsync(). The fsync() calls are on:

 #

 #   1) The directory in which the journal file is created, (unix only)

 #   2) The journal file (to sync the page data),

 #   3) The database file.

 #

 # Normally, when the SAFE_APPEND flag is not set, there is another fsync()

 # on the journal file between steps (2) and (3) above.

 #

 if {$::tcl_platform(platform)=="unix"} {

   set expected_sync_count 3

 } else {

   set expected_sync_count 2

 }

 do_test io-4.1 {

   execsql { DELETE FROM abc }

   nSync

   execsql { INSERT INTO abc VALUES('a', 'b') }

   nSync

 } $expected_sync_count

 # With SAFE_APPEND set, the nRec field of the journal file header should

 # be set to 0xFFFFFFFF before the first journal sync. The nRec field

 # occupies bytes 8-11 of the journal file.

 #

 do_test io-4.2.1 {

   execsql { BEGIN }

   execsql { INSERT INTO abc VALUES('c', 'd') }

   file exists test.db-journal

 } {1}

 if {$::tcl_platform(platform)=="unix"} {

   do_test io-4.2.2 {

     hexio_read test.db-journal 8 4

   } {FFFFFFFF}

 }

 do_test io-4.2.3 {

   execsql { COMMIT }

   nSync

 } $expected_sync_count

 sqlite3_simulate_device -char safe_append

 # With SAFE_APPEND set, there should only ever be one journal-header

 # written to the database, even though the sync-mode is "full".

 #

 do_test io-4.3.1 {

   execsql {

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

     INSERT INTO abc SELECT * FROM abc;

   }

   expr {[file size test.db]/1024}

 } {43}

 ifcapable pager_pragmas {

   do_test io-4.3.2 {

     execsql {

       PRAGMA synchronous = full;

       PRAGMA cache_size = 10;

       PRAGMA synchronous;

     }

   } {2}

 }

 do_test io-4.3.3 {

   execsql {

     BEGIN;

     UPDATE abc SET a = 'x';

   }

   file exists test.db-journal

 } {1}

 if {$tcl_platform(platform) != "symbian"} {

   # This test is not run on symbian because the file-buffer makes it

   # difficult to predict the exact size of the file as reported by 

   # [file size].

   do_test io-4.3.4 {

     # The UPDATE statement in the statement above modifies 41 pages 

     # (all pages in the database except page 1 and the root page of 

     # abc). Because the cache_size is set to 10, this must have required

     # at least 4 cache-spills. If there were no journal headers written

     # to the journal file after the cache-spill, then the size of the

     # journal file is give by:

     #

     #    <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8)

     #

     # If the journal file contains additional headers, this formula

     # will not predict the size of the journal file.

     #

     file size test.db-journal

   } [expr 512 + (1024+8)*41]

 }

 #----------------------------------------------------------------------

 # Test cases io-5.* test that the default page size is selected and

 # used correctly.

 #

 set tn 0

 foreach {char                 sectorsize pgsize} {

          {}                     512      1024

          {}                    1024      1024

          {}                    2048      2048

          {}                    8192      8192

          {}                   16384      8192

          {atomic}               512      8192

          {atomic512}            512      1024

          {atomic2K}             512      2048

          {atomic2K}            4096      4096

          {atomic2K atomic}      512      8192

          {atomic64K}            512      1024

 } {

   incr tn

   if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue

   db close

   file delete -force test.db test.db-journal

   sqlite3_simulate_device -char $char -sectorsize $sectorsize

   sqlite3 db test.db -vfs devsym

   db eval {

     PRAGMA auto_vacuum=OFF;

   }

   ifcapable !atomicwrite {

     if {[regexp {^atomic} $char]} continue

   }

   do_test io-5.$tn {

     execsql {

       CREATE TABLE abc(a, b, c);

     }

     expr {[file size test.db]/2}

   } $pgsize

 }

 sqlite3_simulate_device -char {} -sectorsize 0

 finish_test