1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/io.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,558 @@
1.4 +# 2007 August 21
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +#
1.15 +# The focus of this file is testing some specific characteristics of the
1.16 +# IO traffic generated by SQLite (making sure SQLite is not writing out
1.17 +# more database pages than it has to, stuff like that).
1.18 +#
1.19 +# $Id: io.test,v 1.19 2008/09/18 11:18:41 danielk1977 Exp $
1.20 +
1.21 +set testdir [file dirname $argv0]
1.22 +source $testdir/tester.tcl
1.23 +
1.24 +db close
1.25 +sqlite3_simulate_device
1.26 +sqlite3 db test.db -vfs devsym
1.27 +
1.28 +# Test summary:
1.29 +#
1.30 +# io-1.* - Test that quick-balance does not journal pages unnecessarily.
1.31 +#
1.32 +# io-2.* - Test the "atomic-write optimization".
1.33 +#
1.34 +# io-3.* - Test the IO traffic enhancements triggered when the
1.35 +# IOCAP_SEQUENTIAL device capability flag is set (no
1.36 +# fsync() calls on the journal file).
1.37 +#
1.38 +# io-4.* - Test the IO traffic enhancements triggered when the
1.39 +# IOCAP_SAFE_APPEND device capability flag is set (fewer
1.40 +# fsync() calls on the journal file, no need to set nRec
1.41 +# field in the single journal header).
1.42 +#
1.43 +# io-5.* - Test that the default page size is selected and used
1.44 +# correctly.
1.45 +#
1.46 +
1.47 +set ::nWrite 0
1.48 +proc nWrite {db} {
1.49 + set bt [btree_from_db $db]
1.50 + db_enter $db
1.51 + array set stats [btree_pager_stats $bt]
1.52 + db_leave $db
1.53 + set res [expr $stats(write) - $::nWrite]
1.54 + set ::nWrite $stats(write)
1.55 + set res
1.56 +}
1.57 +
1.58 +set ::nSync 0
1.59 +proc nSync {} {
1.60 + set res [expr {$::sqlite_sync_count - $::nSync}]
1.61 + set ::nSync $::sqlite_sync_count
1.62 + set res
1.63 +}
1.64 +
1.65 +do_test io-1.1 {
1.66 + execsql {
1.67 + PRAGMA auto_vacuum = OFF;
1.68 + PRAGMA page_size = 1024;
1.69 + CREATE TABLE abc(a,b);
1.70 + }
1.71 + nWrite db
1.72 +} {2}
1.73 +
1.74 +# Insert into the table 4 records of aproximately 240 bytes each.
1.75 +# This should completely fill the root-page of the table. Each
1.76 +# INSERT causes 2 db pages to be written - the root-page of "abc"
1.77 +# and page 1 (db change-counter page).
1.78 +do_test io-1.2 {
1.79 + set ret [list]
1.80 + execsql { INSERT INTO abc VALUES(1,randstr(230,230)); }
1.81 + lappend ret [nWrite db]
1.82 + execsql { INSERT INTO abc VALUES(2,randstr(230,230)); }
1.83 + lappend ret [nWrite db]
1.84 + execsql { INSERT INTO abc VALUES(3,randstr(230,230)); }
1.85 + lappend ret [nWrite db]
1.86 + execsql { INSERT INTO abc VALUES(4,randstr(230,230)); }
1.87 + lappend ret [nWrite db]
1.88 +} {2 2 2 2}
1.89 +
1.90 +# Insert another 240 byte record. This causes two leaf pages
1.91 +# to be added to the root page of abc. 4 pages in total
1.92 +# are written to the db file - the two leaf pages, the root
1.93 +# of abc and the change-counter page.
1.94 +do_test io-1.3 {
1.95 + execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }
1.96 + nWrite db
1.97 +} {4}
1.98 +
1.99 +# Insert another 3 240 byte records. After this, the tree consists of
1.100 +# the root-node, which is close to empty, and two leaf pages, both of
1.101 +# which are full.
1.102 +do_test io-1.4 {
1.103 + set ret [list]
1.104 + execsql { INSERT INTO abc VALUES(6,randstr(230,230)); }
1.105 + lappend ret [nWrite db]
1.106 + execsql { INSERT INTO abc VALUES(7,randstr(230,230)); }
1.107 + lappend ret [nWrite db]
1.108 + execsql { INSERT INTO abc VALUES(8,randstr(230,230)); }
1.109 + lappend ret [nWrite db]
1.110 +} {2 2 2}
1.111 +
1.112 +# This insert should use the quick-balance trick to add a third leaf
1.113 +# to the b-tree used to store table abc. It should only be necessary to
1.114 +# write to 3 pages to do this: the change-counter, the root-page and
1.115 +# the new leaf page.
1.116 +do_test io-1.5 {
1.117 + execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }
1.118 + nWrite db
1.119 +} {3}
1.120 +
1.121 +ifcapable atomicwrite {
1.122 +
1.123 +#----------------------------------------------------------------------
1.124 +# Test cases io-2.* test the atomic-write optimization.
1.125 +#
1.126 +do_test io-2.1 {
1.127 + execsql { DELETE FROM abc; VACUUM; }
1.128 +} {}
1.129 +
1.130 +# Clear the write and sync counts.
1.131 +nWrite db ; nSync
1.132 +
1.133 +# The following INSERT updates 2 pages and requires 4 calls to fsync():
1.134 +#
1.135 +# 1) The directory in which the journal file is created,
1.136 +# 2) The journal file (to sync the page data),
1.137 +# 3) The journal file (to sync the journal file header),
1.138 +# 4) The database file.
1.139 +#
1.140 +do_test io-2.2 {
1.141 + execsql { INSERT INTO abc VALUES(1, 2) }
1.142 + list [nWrite db] [nSync]
1.143 +} {2 4}
1.144 +
1.145 +# Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,
1.146 +# then do another INSERT similar to the one in io-2.2. This should
1.147 +# only write 1 page and require a single fsync().
1.148 +#
1.149 +# The single fsync() is the database file. Only one page is reported as
1.150 +# written because page 1 - the change-counter page - is written using
1.151 +# an out-of-band method that bypasses the write counter.
1.152 +#
1.153 +sqlite3_simulate_device -char atomic
1.154 +do_test io-2.3 {
1.155 + execsql { INSERT INTO abc VALUES(3, 4) }
1.156 + list [nWrite db] [nSync]
1.157 +} {1 1}
1.158 +
1.159 +# Test that the journal file is not created and the change-counter is
1.160 +# updated when the atomic-write optimization is used.
1.161 +#
1.162 +do_test io-2.4.1 {
1.163 + execsql {
1.164 + BEGIN;
1.165 + INSERT INTO abc VALUES(5, 6);
1.166 + }
1.167 + sqlite3 db2 test.db -vfs devsym
1.168 + execsql { SELECT * FROM abc } db2
1.169 +} {1 2 3 4}
1.170 +do_test io-2.4.2 {
1.171 + file exists test.db-journal
1.172 +} {0}
1.173 +do_test io-2.4.3 {
1.174 + execsql { COMMIT }
1.175 + execsql { SELECT * FROM abc } db2
1.176 +} {1 2 3 4 5 6}
1.177 +db2 close
1.178 +
1.179 +# Test that the journal file is created and sync()d if the transaction
1.180 +# modifies more than one database page, even if the IOCAP_ATOMIC flag
1.181 +# is set.
1.182 +#
1.183 +do_test io-2.5.1 {
1.184 + execsql { CREATE TABLE def(d, e) }
1.185 + nWrite db ; nSync
1.186 + execsql {
1.187 + BEGIN;
1.188 + INSERT INTO abc VALUES(7, 8);
1.189 + }
1.190 + file exists test.db-journal
1.191 +} {0}
1.192 +do_test io-2.5.2 {
1.193 + execsql { INSERT INTO def VALUES('a', 'b'); }
1.194 + file exists test.db-journal
1.195 +} {1}
1.196 +do_test io-2.5.3 {
1.197 + execsql { COMMIT }
1.198 + list [nWrite db] [nSync]
1.199 +} {3 4}
1.200 +
1.201 +# Test that the journal file is created and sync()d if the transaction
1.202 +# modifies a single database page and also appends a page to the file.
1.203 +# Internally, this case is handled differently to the one above. The
1.204 +# journal file is not actually created until the 'COMMIT' statement
1.205 +# is executed.
1.206 +#
1.207 +do_test io-2.6.1 {
1.208 + execsql {
1.209 + BEGIN;
1.210 + INSERT INTO abc VALUES(9, randstr(1000,1000));
1.211 + }
1.212 + file exists test.db-journal
1.213 +} {0}
1.214 +do_test io-2.6.2 {
1.215 + # Create a file at "test.db-journal". This will prevent SQLite from
1.216 + # opening the journal for exclusive access. As a result, the COMMIT
1.217 + # should fail with SQLITE_CANTOPEN and the transaction rolled back.
1.218 + #
1.219 + set fd [open test.db-journal w]
1.220 + puts $fd "This is not a journal file"
1.221 + close $fd
1.222 + catchsql { COMMIT }
1.223 +} {1 {unable to open database file}}
1.224 +do_test io-2.6.3 {
1.225 + file delete -force test.db-journal
1.226 + catchsql { COMMIT }
1.227 +} {1 {cannot commit - no transaction is active}}
1.228 +do_test io-2.6.4 {
1.229 + execsql { SELECT * FROM abc }
1.230 +} {1 2 3 4 5 6 7 8}
1.231 +
1.232 +
1.233 +# Test that if the database modification is part of multi-file commit,
1.234 +# the journal file is always created. In this case, the journal file
1.235 +# is created during execution of the COMMIT statement, so we have to
1.236 +# use the same technique to check that it is created as in the above
1.237 +# block.
1.238 +file delete -force test2.db test2.db-journal
1.239 +ifcapable attach {
1.240 + do_test io-2.7.1 {
1.241 + execsql {
1.242 + ATTACH 'test2.db' AS aux;
1.243 + PRAGMA aux.page_size = 1024;
1.244 + CREATE TABLE aux.abc2(a, b);
1.245 + BEGIN;
1.246 + INSERT INTO abc VALUES(9, 10);
1.247 + }
1.248 + file exists test.db-journal
1.249 + } {0}
1.250 + do_test io-2.7.2 {
1.251 + execsql { INSERT INTO abc2 SELECT * FROM abc }
1.252 + file exists test2.db-journal
1.253 + } {0}
1.254 + do_test io-2.7.3 {
1.255 + execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
1.256 + } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}
1.257 + do_test io-2.7.4 {
1.258 + set fd [open test2.db-journal w]
1.259 + puts $fd "This is not a journal file"
1.260 + close $fd
1.261 + catchsql { COMMIT }
1.262 + } {1 {unable to open database file}}
1.263 + do_test io-2.7.5 {
1.264 + file delete -force test2.db-journal
1.265 + catchsql { COMMIT }
1.266 + } {1 {cannot commit - no transaction is active}}
1.267 + do_test io-2.7.6 {
1.268 + execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
1.269 + } {1 2 3 4 5 6 7 8}
1.270 +}
1.271 +
1.272 +# Try an explicit ROLLBACK before the journal file is created.
1.273 +#
1.274 +do_test io-2.8.1 {
1.275 + execsql {
1.276 + BEGIN;
1.277 + DELETE FROM abc;
1.278 + }
1.279 + file exists test.db-journal
1.280 +} {0}
1.281 +do_test io-2.8.2 {
1.282 + execsql { SELECT * FROM abc }
1.283 +} {}
1.284 +do_test io-2.8.3 {
1.285 + execsql {
1.286 + ROLLBACK;
1.287 + SELECT * FROM abc;
1.288 + }
1.289 +} {1 2 3 4 5 6 7 8}
1.290 +
1.291 +# Test that the atomic write optimisation is not enabled if the sector
1.292 +# size is larger than the page-size.
1.293 +#
1.294 +do_test io-2.9.1 {
1.295 + sqlite3_simulate_device -char atomic -sectorsize 2048
1.296 + execsql {
1.297 + BEGIN;
1.298 + INSERT INTO abc VALUES(9, 10);
1.299 + }
1.300 + file exists test.db-journal
1.301 +} {1}
1.302 +do_test io-2.9.2 {
1.303 + execsql { ROLLBACK; }
1.304 + db close
1.305 + file delete -force test.db test.db-journal
1.306 + sqlite3 db test.db -vfs devsym
1.307 + execsql {
1.308 + PRAGMA auto_vacuum = OFF;
1.309 + PRAGMA page_size = 2048;
1.310 + CREATE TABLE abc(a, b);
1.311 + }
1.312 + execsql {
1.313 + BEGIN;
1.314 + INSERT INTO abc VALUES(9, 10);
1.315 + }
1.316 + file exists test.db-journal
1.317 +} {0}
1.318 +do_test io-2.9.3 {
1.319 + execsql { COMMIT }
1.320 +} {}
1.321 +
1.322 +# Test a couple of the more specific IOCAP_ATOMIC flags
1.323 +# (i.e IOCAP_ATOMIC2K etc.).
1.324 +#
1.325 +do_test io-2.10.1 {
1.326 + sqlite3_simulate_device -char atomic1k
1.327 + execsql {
1.328 + BEGIN;
1.329 + INSERT INTO abc VALUES(11, 12);
1.330 + }
1.331 + file exists test.db-journal
1.332 +} {1}
1.333 +do_test io-2.10.2 {
1.334 + execsql { ROLLBACK }
1.335 + sqlite3_simulate_device -char atomic2k
1.336 + execsql {
1.337 + BEGIN;
1.338 + INSERT INTO abc VALUES(11, 12);
1.339 + }
1.340 + file exists test.db-journal
1.341 +} {0}
1.342 +do_test io-2.10.3 {
1.343 + execsql { ROLLBACK }
1.344 +} {}
1.345 +
1.346 +do_test io-2.11.0 {
1.347 + execsql {
1.348 + PRAGMA locking_mode = exclusive;
1.349 + PRAGMA locking_mode;
1.350 + }
1.351 +} {exclusive exclusive}
1.352 +do_test io-2.11.1 {
1.353 + execsql {
1.354 + INSERT INTO abc VALUES(11, 12);
1.355 + }
1.356 + file exists test.db-journal
1.357 +} {0}
1.358 +
1.359 +do_test io-2.11.2 {
1.360 + execsql {
1.361 + PRAGMA locking_mode = normal;
1.362 + INSERT INTO abc VALUES(13, 14);
1.363 + }
1.364 + file exists test.db-journal
1.365 +} {0}
1.366 +
1.367 +} ;# /* ifcapable atomicwrite */
1.368 +
1.369 +#----------------------------------------------------------------------
1.370 +# Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.
1.371 +#
1.372 +sqlite3_simulate_device -char sequential -sectorsize 0
1.373 +ifcapable pager_pragmas {
1.374 + do_test io-3.1 {
1.375 + db close
1.376 + file delete -force test.db test.db-journal
1.377 + sqlite3 db test.db -vfs devsym
1.378 + db eval {
1.379 + PRAGMA auto_vacuum=OFF;
1.380 + }
1.381 + # File size might be 1 due to the hack to work around ticket #3260.
1.382 + # Search for #3260 in os_unix.c for additional information.
1.383 + expr {[file size test.db]>1}
1.384 + } {0}
1.385 + do_test io-3.2 {
1.386 + execsql { CREATE TABLE abc(a, b) }
1.387 + nSync
1.388 + execsql {
1.389 + PRAGMA temp_store = memory;
1.390 + PRAGMA cache_size = 10;
1.391 + BEGIN;
1.392 + INSERT INTO abc VALUES('hello', 'world');
1.393 + INSERT INTO abc SELECT * FROM abc;
1.394 + INSERT INTO abc SELECT * FROM abc;
1.395 + INSERT INTO abc SELECT * FROM abc;
1.396 + INSERT INTO abc SELECT * FROM abc;
1.397 + INSERT INTO abc SELECT * FROM abc;
1.398 + INSERT INTO abc SELECT * FROM abc;
1.399 + INSERT INTO abc SELECT * FROM abc;
1.400 + INSERT INTO abc SELECT * FROM abc;
1.401 + INSERT INTO abc SELECT * FROM abc;
1.402 + INSERT INTO abc SELECT * FROM abc;
1.403 + INSERT INTO abc SELECT * FROM abc;
1.404 + }
1.405 + # File has grown - showing there was a cache-spill - but there
1.406 + # have been no calls to fsync(). The file is probably about 30KB.
1.407 + # But some VFS implementations (symbian) buffer writes so the actual
1.408 + # size may be a little less than that. So this test case just tests
1.409 + # that the file is now greater than 20000 bytes in size.
1.410 + list [expr [file size test.db]>20000] [nSync]
1.411 + } {1 0}
1.412 + do_test io-3.3 {
1.413 + # The COMMIT requires a single fsync() - to the database file.
1.414 + execsql { COMMIT }
1.415 + list [file size test.db] [nSync]
1.416 + } {39936 1}
1.417 +}
1.418 +
1.419 +#----------------------------------------------------------------------
1.420 +# Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.
1.421 +#
1.422 +sqlite3_simulate_device -char safe_append
1.423 +
1.424 +# With the SAFE_APPEND flag set, simple transactions require 3, rather
1.425 +# than 4, calls to fsync(). The fsync() calls are on:
1.426 +#
1.427 +# 1) The directory in which the journal file is created, (unix only)
1.428 +# 2) The journal file (to sync the page data),
1.429 +# 3) The database file.
1.430 +#
1.431 +# Normally, when the SAFE_APPEND flag is not set, there is another fsync()
1.432 +# on the journal file between steps (2) and (3) above.
1.433 +#
1.434 +if {$::tcl_platform(platform)=="unix"} {
1.435 + set expected_sync_count 3
1.436 +} else {
1.437 + set expected_sync_count 2
1.438 +}
1.439 +do_test io-4.1 {
1.440 + execsql { DELETE FROM abc }
1.441 + nSync
1.442 + execsql { INSERT INTO abc VALUES('a', 'b') }
1.443 + nSync
1.444 +} $expected_sync_count
1.445 +
1.446 +# With SAFE_APPEND set, the nRec field of the journal file header should
1.447 +# be set to 0xFFFFFFFF before the first journal sync. The nRec field
1.448 +# occupies bytes 8-11 of the journal file.
1.449 +#
1.450 +do_test io-4.2.1 {
1.451 + execsql { BEGIN }
1.452 + execsql { INSERT INTO abc VALUES('c', 'd') }
1.453 + file exists test.db-journal
1.454 +} {1}
1.455 +if {$::tcl_platform(platform)=="unix"} {
1.456 + do_test io-4.2.2 {
1.457 + hexio_read test.db-journal 8 4
1.458 + } {FFFFFFFF}
1.459 +}
1.460 +do_test io-4.2.3 {
1.461 + execsql { COMMIT }
1.462 + nSync
1.463 +} $expected_sync_count
1.464 +sqlite3_simulate_device -char safe_append
1.465 +
1.466 +# With SAFE_APPEND set, there should only ever be one journal-header
1.467 +# written to the database, even though the sync-mode is "full".
1.468 +#
1.469 +do_test io-4.3.1 {
1.470 + execsql {
1.471 + INSERT INTO abc SELECT * FROM abc;
1.472 + INSERT INTO abc SELECT * FROM abc;
1.473 + INSERT INTO abc SELECT * FROM abc;
1.474 + INSERT INTO abc SELECT * FROM abc;
1.475 + INSERT INTO abc SELECT * FROM abc;
1.476 + INSERT INTO abc SELECT * FROM abc;
1.477 + INSERT INTO abc SELECT * FROM abc;
1.478 + INSERT INTO abc SELECT * FROM abc;
1.479 + INSERT INTO abc SELECT * FROM abc;
1.480 + INSERT INTO abc SELECT * FROM abc;
1.481 + INSERT INTO abc SELECT * FROM abc;
1.482 + }
1.483 + expr {[file size test.db]/1024}
1.484 +} {43}
1.485 +ifcapable pager_pragmas {
1.486 + do_test io-4.3.2 {
1.487 + execsql {
1.488 + PRAGMA synchronous = full;
1.489 + PRAGMA cache_size = 10;
1.490 + PRAGMA synchronous;
1.491 + }
1.492 + } {2}
1.493 +}
1.494 +do_test io-4.3.3 {
1.495 + execsql {
1.496 + BEGIN;
1.497 + UPDATE abc SET a = 'x';
1.498 + }
1.499 + file exists test.db-journal
1.500 +} {1}
1.501 +if {$tcl_platform(platform) != "symbian"} {
1.502 + # This test is not run on symbian because the file-buffer makes it
1.503 + # difficult to predict the exact size of the file as reported by
1.504 + # [file size].
1.505 + do_test io-4.3.4 {
1.506 + # The UPDATE statement in the statement above modifies 41 pages
1.507 + # (all pages in the database except page 1 and the root page of
1.508 + # abc). Because the cache_size is set to 10, this must have required
1.509 + # at least 4 cache-spills. If there were no journal headers written
1.510 + # to the journal file after the cache-spill, then the size of the
1.511 + # journal file is give by:
1.512 + #
1.513 + # <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8)
1.514 + #
1.515 + # If the journal file contains additional headers, this formula
1.516 + # will not predict the size of the journal file.
1.517 + #
1.518 + file size test.db-journal
1.519 + } [expr 512 + (1024+8)*41]
1.520 +}
1.521 +
1.522 +#----------------------------------------------------------------------
1.523 +# Test cases io-5.* test that the default page size is selected and
1.524 +# used correctly.
1.525 +#
1.526 +set tn 0
1.527 +foreach {char sectorsize pgsize} {
1.528 + {} 512 1024
1.529 + {} 1024 1024
1.530 + {} 2048 2048
1.531 + {} 8192 8192
1.532 + {} 16384 8192
1.533 + {atomic} 512 8192
1.534 + {atomic512} 512 1024
1.535 + {atomic2K} 512 2048
1.536 + {atomic2K} 4096 4096
1.537 + {atomic2K atomic} 512 8192
1.538 + {atomic64K} 512 1024
1.539 +} {
1.540 + incr tn
1.541 + if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue
1.542 + db close
1.543 + file delete -force test.db test.db-journal
1.544 + sqlite3_simulate_device -char $char -sectorsize $sectorsize
1.545 + sqlite3 db test.db -vfs devsym
1.546 + db eval {
1.547 + PRAGMA auto_vacuum=OFF;
1.548 + }
1.549 + ifcapable !atomicwrite {
1.550 + if {[regexp {^atomic} $char]} continue
1.551 + }
1.552 + do_test io-5.$tn {
1.553 + execsql {
1.554 + CREATE TABLE abc(a, b, c);
1.555 + }
1.556 + expr {[file size test.db]/2}
1.557 + } $pgsize
1.558 +}
1.559 +
1.560 +sqlite3_simulate_device -char {} -sectorsize 0
1.561 +finish_test