1 # 2007 August 21

     2 #

     3 # The author disclaims copyright to this source code.  In place of

     4 # a legal notice, here is a blessing:

     5 #

     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.

     9 #

    10 #***********************************************************************

    11 #

    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).

    15 #

    16 # $Id: io.test,v 1.19 2008/09/18 11:18:41 danielk1977 Exp $

    17 

    18 set testdir [file dirname $argv0]

    19 source $testdir/tester.tcl

    20 

    21 db close

    22 sqlite3_simulate_device

    23 sqlite3 db test.db -vfs devsym

    24 

    25 # Test summary:

    26 #

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

    28 #

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

    30 #

    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).

    34 #

    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).

    39 #

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

    41 #           correctly.

    42 #           

    43 

    44 set ::nWrite 0

    45 proc nWrite {db} {

    46   set bt [btree_from_db $db]

    47   db_enter $db

    48   array set stats [btree_pager_stats $bt]

    49   db_leave $db

    50   set res [expr $stats(write) - $::nWrite]

    51   set ::nWrite $stats(write)

    52   set res

    53 }

    54 

    55 set ::nSync 0

    56 proc nSync {} {

    57   set res [expr {$::sqlite_sync_count - $::nSync}]

    58   set ::nSync $::sqlite_sync_count

    59   set res

    60 }

    61 

    62 do_test io-1.1 {

    63   execsql {

    64     PRAGMA auto_vacuum = OFF;

    65     PRAGMA page_size = 1024;

    66     CREATE TABLE abc(a,b);

    67   }

    68   nWrite db

    69 } {2}

    70 

    71 # Insert into the table 4 records of aproximately 240 bytes each.

    72 # This should completely fill the root-page of the table. Each

    73 # INSERT causes 2 db pages to be written - the root-page of "abc"

    74 # and page 1 (db change-counter page).

    75 do_test io-1.2 {

    76   set ret [list]

    77   execsql { INSERT INTO abc VALUES(1,randstr(230,230)); }

    78   lappend ret [nWrite db]

    79   execsql { INSERT INTO abc VALUES(2,randstr(230,230)); }

    80   lappend ret [nWrite db]

    81   execsql { INSERT INTO abc VALUES(3,randstr(230,230)); }

    82   lappend ret [nWrite db]

    83   execsql { INSERT INTO abc VALUES(4,randstr(230,230)); }

    84   lappend ret [nWrite db]

    85 } {2 2 2 2}

    86 

    87 # Insert another 240 byte record. This causes two leaf pages

    88 # to be added to the root page of abc. 4 pages in total

    89 # are written to the db file - the two leaf pages, the root

    90 # of abc and the change-counter page.

    91 do_test io-1.3 {

    92   execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }

    93   nWrite db

    94 } {4}

    95 

    96 # Insert another 3 240 byte records. After this, the tree consists of 

    97 # the root-node, which is close to empty, and two leaf pages, both of 

    98 # which are full. 

    99 do_test io-1.4 {

   100   set ret [list]

   101   execsql { INSERT INTO abc VALUES(6,randstr(230,230)); }

   102   lappend ret [nWrite db]

   103   execsql { INSERT INTO abc VALUES(7,randstr(230,230)); }

   104   lappend ret [nWrite db]

   105   execsql { INSERT INTO abc VALUES(8,randstr(230,230)); }

   106   lappend ret [nWrite db]

   107 } {2 2 2}

   108 

   109 # This insert should use the quick-balance trick to add a third leaf

   110 # to the b-tree used to store table abc. It should only be necessary to

   111 # write to 3 pages to do this: the change-counter, the root-page and

   112 # the new leaf page.

   113 do_test io-1.5 {

   114   execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }

   115   nWrite db

   116 } {3}

   117 

   118 ifcapable atomicwrite {

   119 

   120 #----------------------------------------------------------------------

   121 # Test cases io-2.* test the atomic-write optimization.

   122 #

   123 do_test io-2.1 {

   124   execsql { DELETE FROM abc; VACUUM; }

   125 } {}

   126 

   127 # Clear the write and sync counts.

   128 nWrite db ; nSync

   129 

   130 # The following INSERT updates 2 pages and requires 4 calls to fsync():

   131 #

   132 #   1) The directory in which the journal file is created,

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

   134 #   3) The journal file (to sync the journal file header),

   135 #   4) The database file.

   136 #

   137 do_test io-2.2 {

   138   execsql { INSERT INTO abc VALUES(1, 2) }

   139   list [nWrite db] [nSync]

   140 } {2 4}

   141 

   142 # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,

   143 # then do another INSERT similar to the one in io-2.2. This should

   144 # only write 1 page and require a single fsync().

   145 # 

   146 # The single fsync() is the database file. Only one page is reported as

   147 # written because page 1 - the change-counter page - is written using

   148 # an out-of-band method that bypasses the write counter.

   149 #

   150 sqlite3_simulate_device -char atomic

   151 do_test io-2.3 {

   152   execsql { INSERT INTO abc VALUES(3, 4) }

   153   list [nWrite db] [nSync]

   154 } {1 1}

   155 

   156 # Test that the journal file is not created and the change-counter is

   157 # updated when the atomic-write optimization is used.

   158 #

   159 do_test io-2.4.1 {

   160   execsql {

   161     BEGIN;

   162     INSERT INTO abc VALUES(5, 6);

   163   }

   164   sqlite3 db2 test.db -vfs devsym

   165   execsql { SELECT * FROM abc } db2

   166 } {1 2 3 4}

   167 do_test io-2.4.2 {

   168   file exists test.db-journal

   169 } {0}

   170 do_test io-2.4.3 {

   171   execsql { COMMIT }

   172   execsql { SELECT * FROM abc } db2

   173 } {1 2 3 4 5 6}

   174 db2 close

   175 

   176 # Test that the journal file is created and sync()d if the transaction

   177 # modifies more than one database page, even if the IOCAP_ATOMIC flag

   178 # is set.

   179 #

   180 do_test io-2.5.1 {

   181   execsql { CREATE TABLE def(d, e) }

   182   nWrite db ; nSync

   183   execsql {

   184     BEGIN;

   185     INSERT INTO abc VALUES(7, 8);

   186   }

   187   file exists test.db-journal

   188 } {0}

   189 do_test io-2.5.2 {

   190   execsql { INSERT INTO def VALUES('a', 'b'); }

   191   file exists test.db-journal

   192 } {1}

   193 do_test io-2.5.3 {

   194   execsql { COMMIT }

   195   list [nWrite db] [nSync]

   196 } {3 4}

   197 

   198 # Test that the journal file is created and sync()d if the transaction

   199 # modifies a single database page and also appends a page to the file.

   200 # Internally, this case is handled differently to the one above. The

   201 # journal file is not actually created until the 'COMMIT' statement

   202 # is executed.

   203 #

   204 do_test io-2.6.1 {

   205   execsql {

   206     BEGIN;

   207     INSERT INTO abc VALUES(9, randstr(1000,1000));

   208   }

   209   file exists test.db-journal

   210 } {0}

   211 do_test io-2.6.2 {

   212   # Create a file at "test.db-journal". This will prevent SQLite from

   213   # opening the journal for exclusive access. As a result, the COMMIT

   214   # should fail with SQLITE_CANTOPEN and the transaction rolled back.

   215   #

   216   set fd [open test.db-journal w]

   217   puts $fd "This is not a journal file"

   218   close $fd

   219   catchsql { COMMIT }

   220 } {1 {unable to open database file}}

   221 do_test io-2.6.3 {

   222   file delete -force test.db-journal

   223   catchsql { COMMIT }

   224 } {1 {cannot commit - no transaction is active}}

   225 do_test io-2.6.4 {

   226   execsql { SELECT * FROM abc }

   227 } {1 2 3 4 5 6 7 8}

   228 

   229 

   230 # Test that if the database modification is part of multi-file commit,

   231 # the journal file is always created. In this case, the journal file

   232 # is created during execution of the COMMIT statement, so we have to

   233 # use the same technique to check that it is created as in the above 

   234 # block.

   235 file delete -force test2.db test2.db-journal

   236 ifcapable attach {

   237   do_test io-2.7.1 {

   238     execsql {

   239       ATTACH 'test2.db' AS aux;

   240       PRAGMA aux.page_size = 1024;

   241       CREATE TABLE aux.abc2(a, b);

   242       BEGIN;

   243       INSERT INTO abc VALUES(9, 10);

   244     }

   245     file exists test.db-journal

   246   } {0}

   247   do_test io-2.7.2 {

   248     execsql { INSERT INTO abc2 SELECT * FROM abc }

   249     file exists test2.db-journal

   250   } {0}

   251   do_test io-2.7.3 {

   252     execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }

   253   } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}

   254   do_test io-2.7.4 {

   255     set fd [open test2.db-journal w]

   256     puts $fd "This is not a journal file"

   257     close $fd

   258     catchsql { COMMIT }

   259   } {1 {unable to open database file}}

   260   do_test io-2.7.5 {

   261     file delete -force test2.db-journal

   262     catchsql { COMMIT }

   263   } {1 {cannot commit - no transaction is active}}

   264   do_test io-2.7.6 {

   265     execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }

   266   } {1 2 3 4 5 6 7 8}

   267 }

   268 

   269 # Try an explicit ROLLBACK before the journal file is created.

   270 #

   271 do_test io-2.8.1 {

   272   execsql {

   273     BEGIN;

   274     DELETE FROM abc;

   275   }

   276   file exists test.db-journal

   277 } {0}

   278 do_test io-2.8.2 {

   279   execsql { SELECT * FROM abc }

   280 } {}

   281 do_test io-2.8.3 {

   282   execsql {

   283     ROLLBACK;

   284     SELECT * FROM abc;

   285   }

   286 } {1 2 3 4 5 6 7 8}

   287 

   288 # Test that the atomic write optimisation is not enabled if the sector

   289 # size is larger than the page-size.

   290 #

   291 do_test io-2.9.1 {

   292   sqlite3_simulate_device -char atomic -sectorsize 2048

   293   execsql {

   294     BEGIN;

   295     INSERT INTO abc VALUES(9, 10);

   296   }

   297   file exists test.db-journal

   298 } {1}

   299 do_test io-2.9.2 {

   300   execsql { ROLLBACK; }

   301   db close

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

   303   sqlite3 db test.db -vfs devsym

   304   execsql {

   305     PRAGMA auto_vacuum = OFF;

   306     PRAGMA page_size = 2048;

   307     CREATE TABLE abc(a, b);

   308   }

   309   execsql {

   310     BEGIN;

   311     INSERT INTO abc VALUES(9, 10);

   312   }

   313   file exists test.db-journal

   314 } {0}

   315 do_test io-2.9.3 {

   316   execsql { COMMIT }

   317 } {}

   318 

   319 # Test a couple of the more specific IOCAP_ATOMIC flags 

   320 # (i.e IOCAP_ATOMIC2K etc.).

   321 #

   322 do_test io-2.10.1 {

   323   sqlite3_simulate_device -char atomic1k

   324   execsql {

   325     BEGIN;

   326     INSERT INTO abc VALUES(11, 12);

   327   }

   328   file exists test.db-journal

   329 } {1}

   330 do_test io-2.10.2 {

   331   execsql { ROLLBACK }

   332   sqlite3_simulate_device -char atomic2k

   333   execsql {

   334     BEGIN;

   335     INSERT INTO abc VALUES(11, 12);

   336   }

   337   file exists test.db-journal

   338 } {0}

   339 do_test io-2.10.3 {

   340   execsql { ROLLBACK }

   341 } {}

   342 

   343 do_test io-2.11.0 {

   344   execsql { 

   345     PRAGMA locking_mode = exclusive;

   346     PRAGMA locking_mode;

   347   }

   348 } {exclusive exclusive}

   349 do_test io-2.11.1 {

   350   execsql { 

   351     INSERT INTO abc VALUES(11, 12);

   352   }

   353   file exists test.db-journal

   354 } {0}

   355 

   356 do_test io-2.11.2 {

   357   execsql { 

   358     PRAGMA locking_mode = normal;

   359     INSERT INTO abc VALUES(13, 14);

   360   }

   361   file exists test.db-journal

   362 } {0}

   363 

   364 } ;# /* ifcapable atomicwrite */

   365 

   366 #----------------------------------------------------------------------

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

   368 #

   369 sqlite3_simulate_device -char sequential -sectorsize 0

   370 ifcapable pager_pragmas {

   371   do_test io-3.1 {

   372     db close

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

   374     sqlite3 db test.db -vfs devsym

   375     db eval {

   376       PRAGMA auto_vacuum=OFF;

   377     }

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

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

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

   381   } {0}

   382   do_test io-3.2 {

   383     execsql { CREATE TABLE abc(a, b) }

   384     nSync

   385     execsql {

   386       PRAGMA temp_store = memory;

   387       PRAGMA cache_size = 10;

   388       BEGIN;

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

   390       INSERT INTO abc SELECT * FROM abc;

   391       INSERT INTO abc SELECT * FROM abc;

   392       INSERT INTO abc SELECT * FROM abc;

   393       INSERT INTO abc SELECT * FROM abc;

   394       INSERT INTO abc SELECT * FROM abc;

   395       INSERT INTO abc SELECT * FROM abc;

   396       INSERT INTO abc SELECT * FROM abc;

   397       INSERT INTO abc SELECT * FROM abc;

   398       INSERT INTO abc SELECT * FROM abc;

   399       INSERT INTO abc SELECT * FROM abc;

   400       INSERT INTO abc SELECT * FROM abc;

   401     }

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

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

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

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

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

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

   408   } {1 0}

   409   do_test io-3.3 {

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

   411     execsql { COMMIT }

   412     list [file size test.db] [nSync]

   413   } {39936 1}

   414 }

   415 

   416 #----------------------------------------------------------------------

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

   418 #

   419 sqlite3_simulate_device -char safe_append

   420 

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

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

   423 #

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

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

   426 #   3) The database file.

   427 #

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

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

   430 #

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

   432   set expected_sync_count 3

   433 } else {

   434   set expected_sync_count 2

   435 }

   436 do_test io-4.1 {

   437   execsql { DELETE FROM abc }

   438   nSync

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

   440   nSync

   441 } $expected_sync_count

   442 

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

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

   445 # occupies bytes 8-11 of the journal file.

   446 #

   447 do_test io-4.2.1 {

   448   execsql { BEGIN }

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

   450   file exists test.db-journal

   451 } {1}

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

   453   do_test io-4.2.2 {

   454     hexio_read test.db-journal 8 4

   455   } {FFFFFFFF}

   456 }

   457 do_test io-4.2.3 {

   458   execsql { COMMIT }

   459   nSync

   460 } $expected_sync_count

   461 sqlite3_simulate_device -char safe_append

   462 

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

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

   465 #

   466 do_test io-4.3.1 {

   467   execsql {

   468     INSERT INTO abc SELECT * FROM abc;

   469     INSERT INTO abc SELECT * FROM abc;

   470     INSERT INTO abc SELECT * FROM abc;

   471     INSERT INTO abc SELECT * FROM abc;

   472     INSERT INTO abc SELECT * FROM abc;

   473     INSERT INTO abc SELECT * FROM abc;

   474     INSERT INTO abc SELECT * FROM abc;

   475     INSERT INTO abc SELECT * FROM abc;

   476     INSERT INTO abc SELECT * FROM abc;

   477     INSERT INTO abc SELECT * FROM abc;

   478     INSERT INTO abc SELECT * FROM abc;

   479   }

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

   481 } {43}

   482 ifcapable pager_pragmas {

   483   do_test io-4.3.2 {

   484     execsql {

   485       PRAGMA synchronous = full;

   486       PRAGMA cache_size = 10;

   487       PRAGMA synchronous;

   488     }

   489   } {2}

   490 }

   491 do_test io-4.3.3 {

   492   execsql {

   493     BEGIN;

   494     UPDATE abc SET a = 'x';

   495   }

   496   file exists test.db-journal

   497 } {1}

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

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

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

   501   # [file size].

   502   do_test io-4.3.4 {

   503     # The UPDATE statement in the statement above modifies 41 pages 

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

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

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

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

   508     # journal file is give by:

   509     #

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

   511     #

   512     # If the journal file contains additional headers, this formula

   513     # will not predict the size of the journal file.

   514     #

   515     file size test.db-journal

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

   517 }

   518 

   519 #----------------------------------------------------------------------

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

   521 # used correctly.

   522 #

   523 set tn 0

   524 foreach {char                 sectorsize pgsize} {

   525          {}                     512      1024

   526          {}                    1024      1024

   527          {}                    2048      2048

   528          {}                    8192      8192

   529          {}                   16384      8192

   530          {atomic}               512      8192

   531          {atomic512}            512      1024

   532          {atomic2K}             512      2048

   533          {atomic2K}            4096      4096

   534          {atomic2K atomic}      512      8192

   535          {atomic64K}            512      1024

   536 } {

   537   incr tn

   538   if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue

   539   db close

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

   541   sqlite3_simulate_device -char $char -sectorsize $sectorsize

   542   sqlite3 db test.db -vfs devsym

   543   db eval {

   544     PRAGMA auto_vacuum=OFF;

   545   }

   546   ifcapable !atomicwrite {

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

   548   }

   549   do_test io-5.$tn {

   550     execsql {

   551       CREATE TABLE abc(a, b, c);

   552     }

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

   554   } $pgsize

   555 }

   556 

   557 sqlite3_simulate_device -char {} -sectorsize 0

   558 finish_test