Annotation of embedaddon/sqlite3/test/io.test, revision 1.1
1.1 ! misho 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:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: db close
! 21: sqlite3_simulate_device
! 22: sqlite3 db test.db -vfs devsym
! 23:
! 24: # Test summary:
! 25: #
! 26: # io-1.* - Test that quick-balance does not journal pages unnecessarily.
! 27: #
! 28: # io-2.* - Test the "atomic-write optimization".
! 29: #
! 30: # io-3.* - Test the IO traffic enhancements triggered when the
! 31: # IOCAP_SEQUENTIAL device capability flag is set (no
! 32: # fsync() calls on the journal file).
! 33: #
! 34: # io-4.* - Test the IO traffic enhancements triggered when the
! 35: # IOCAP_SAFE_APPEND device capability flag is set (fewer
! 36: # fsync() calls on the journal file, no need to set nRec
! 37: # field in the single journal header).
! 38: #
! 39: # io-5.* - Test that the default page size is selected and used
! 40: # correctly.
! 41: #
! 42:
! 43: set ::nWrite 0
! 44: proc nWrite {db} {
! 45: set bt [btree_from_db $db]
! 46: db_enter $db
! 47: array set stats [btree_pager_stats $bt]
! 48: db_leave $db
! 49: set res [expr $stats(write) - $::nWrite]
! 50: set ::nWrite $stats(write)
! 51: set res
! 52: }
! 53:
! 54: set ::nSync 0
! 55: proc nSync {} {
! 56: set res [expr {$::sqlite_sync_count - $::nSync}]
! 57: set ::nSync $::sqlite_sync_count
! 58: set res
! 59: }
! 60:
! 61: do_test io-1.1 {
! 62: execsql {
! 63: PRAGMA auto_vacuum = OFF;
! 64: PRAGMA page_size = 1024;
! 65: CREATE TABLE abc(a,b);
! 66: }
! 67: nWrite db
! 68: } {2}
! 69:
! 70: # Insert into the table 4 records of aproximately 240 bytes each.
! 71: # This should completely fill the root-page of the table. Each
! 72: # INSERT causes 2 db pages to be written - the root-page of "abc"
! 73: # and page 1 (db change-counter page).
! 74: do_test io-1.2 {
! 75: set ret [list]
! 76: execsql { INSERT INTO abc VALUES(1,randstr(230,230)); }
! 77: lappend ret [nWrite db]
! 78: execsql { INSERT INTO abc VALUES(2,randstr(230,230)); }
! 79: lappend ret [nWrite db]
! 80: execsql { INSERT INTO abc VALUES(3,randstr(230,230)); }
! 81: lappend ret [nWrite db]
! 82: execsql { INSERT INTO abc VALUES(4,randstr(230,230)); }
! 83: lappend ret [nWrite db]
! 84: } {2 2 2 2}
! 85:
! 86: # Insert another 240 byte record. This causes two leaf pages
! 87: # to be added to the root page of abc. 4 pages in total
! 88: # are written to the db file - the two leaf pages, the root
! 89: # of abc and the change-counter page.
! 90: do_test io-1.3 {
! 91: execsql { INSERT INTO abc VALUES(5,randstr(230,230)); }
! 92: nWrite db
! 93: } {4}
! 94:
! 95: # Insert another 3 240 byte records. After this, the tree consists of
! 96: # the root-node, which is close to empty, and two leaf pages, both of
! 97: # which are full.
! 98: do_test io-1.4 {
! 99: set ret [list]
! 100: execsql { INSERT INTO abc VALUES(6,randstr(230,230)); }
! 101: lappend ret [nWrite db]
! 102: execsql { INSERT INTO abc VALUES(7,randstr(230,230)); }
! 103: lappend ret [nWrite db]
! 104: execsql { INSERT INTO abc VALUES(8,randstr(230,230)); }
! 105: lappend ret [nWrite db]
! 106: } {2 2 2}
! 107:
! 108: # This insert should use the quick-balance trick to add a third leaf
! 109: # to the b-tree used to store table abc. It should only be necessary to
! 110: # write to 3 pages to do this: the change-counter, the root-page and
! 111: # the new leaf page.
! 112: do_test io-1.5 {
! 113: execsql { INSERT INTO abc VALUES(9,randstr(230,230)); }
! 114: nWrite db
! 115: } {3}
! 116:
! 117: ifcapable atomicwrite {
! 118:
! 119: #----------------------------------------------------------------------
! 120: # Test cases io-2.* test the atomic-write optimization.
! 121: #
! 122: do_test io-2.1 {
! 123: execsql { DELETE FROM abc; VACUUM; }
! 124: } {}
! 125:
! 126: # Clear the write and sync counts.
! 127: nWrite db ; nSync
! 128:
! 129: # The following INSERT updates 2 pages and requires 4 calls to fsync():
! 130: #
! 131: # 1) The directory in which the journal file is created,
! 132: # 2) The journal file (to sync the page data),
! 133: # 3) The journal file (to sync the journal file header),
! 134: # 4) The database file.
! 135: #
! 136: do_test io-2.2 {
! 137: execsql { INSERT INTO abc VALUES(1, 2) }
! 138: list [nWrite db] [nSync]
! 139: } {2 4}
! 140:
! 141: # Set the device-characteristic mask to include the SQLITE_IOCAP_ATOMIC,
! 142: # then do another INSERT similar to the one in io-2.2. This should
! 143: # only write 1 page and require a single fsync().
! 144: #
! 145: # The single fsync() is the database file. Only one page is reported as
! 146: # written because page 1 - the change-counter page - is written using
! 147: # an out-of-band method that bypasses the write counter.
! 148: #
! 149: sqlite3_simulate_device -char atomic
! 150: do_test io-2.3 {
! 151: execsql { INSERT INTO abc VALUES(3, 4) }
! 152: list [nWrite db] [nSync]
! 153: } {1 1}
! 154:
! 155: # Test that the journal file is not created and the change-counter is
! 156: # updated when the atomic-write optimization is used.
! 157: #
! 158: do_test io-2.4.1 {
! 159: execsql {
! 160: BEGIN;
! 161: INSERT INTO abc VALUES(5, 6);
! 162: }
! 163: sqlite3 db2 test.db -vfs devsym
! 164: execsql { SELECT * FROM abc } db2
! 165: } {1 2 3 4}
! 166: do_test io-2.4.2 {
! 167: file exists test.db-journal
! 168: } {0}
! 169: do_test io-2.4.3 {
! 170: execsql { COMMIT }
! 171: execsql { SELECT * FROM abc } db2
! 172: } {1 2 3 4 5 6}
! 173: db2 close
! 174:
! 175: # Test that the journal file is created and sync()d if the transaction
! 176: # modifies more than one database page, even if the IOCAP_ATOMIC flag
! 177: # is set.
! 178: #
! 179: do_test io-2.5.1 {
! 180: execsql { CREATE TABLE def(d, e) }
! 181: nWrite db ; nSync
! 182: execsql {
! 183: BEGIN;
! 184: INSERT INTO abc VALUES(7, 8);
! 185: }
! 186: file exists test.db-journal
! 187: } {0}
! 188: do_test io-2.5.2 {
! 189: execsql { INSERT INTO def VALUES('a', 'b'); }
! 190: file exists test.db-journal
! 191: } {1}
! 192: do_test io-2.5.3 {
! 193: execsql { COMMIT }
! 194: list [nWrite db] [nSync]
! 195: } {3 4}
! 196:
! 197: # Test that the journal file is created and sync()d if the transaction
! 198: # modifies a single database page and also appends a page to the file.
! 199: # Internally, this case is handled differently to the one above. The
! 200: # journal file is not actually created until the 'COMMIT' statement
! 201: # is executed.
! 202: #
! 203: # Changed 2010-03-27: The size of the database is now stored in
! 204: # bytes 28..31 and so when a page is added to the database, page 1
! 205: # is immediately modified and the journal file immediately comes into
! 206: # existance. To fix this test, the BEGIN is changed into a a
! 207: # BEGIN IMMEDIATE and the INSERT is omitted.
! 208: #
! 209: do_test io-2.6.1 {
! 210: execsql {
! 211: BEGIN IMMEDIATE;
! 212: -- INSERT INTO abc VALUES(9, randstr(1000,1000));
! 213: }
! 214: file exists test.db-journal
! 215: } {0}
! 216: do_test io-2.6.2 {
! 217: # Create a file at "test.db-journal". This will prevent SQLite from
! 218: # opening the journal for exclusive access. As a result, the COMMIT
! 219: # should fail with SQLITE_CANTOPEN and the transaction rolled back.
! 220: #
! 221: file mkdir test.db-journal
! 222: catchsql {
! 223: INSERT INTO abc VALUES(9, randstr(1000,1000));
! 224: COMMIT
! 225: }
! 226: } {1 {unable to open database file}}
! 227: do_test io-2.6.3 {
! 228: forcedelete test.db-journal
! 229: catchsql { COMMIT }
! 230: } {0 {}}
! 231: do_test io-2.6.4 {
! 232: execsql { SELECT * FROM abc }
! 233: } {1 2 3 4 5 6 7 8}
! 234:
! 235: # Test that if the database modification is part of multi-file commit,
! 236: # the journal file is always created. In this case, the journal file
! 237: # is created during execution of the COMMIT statement, so we have to
! 238: # use the same technique to check that it is created as in the above
! 239: # block.
! 240: forcedelete test2.db test2.db-journal
! 241: ifcapable attach {
! 242: do_test io-2.7.1 {
! 243: execsql {
! 244: ATTACH 'test2.db' AS aux;
! 245: PRAGMA aux.page_size = 1024;
! 246: CREATE TABLE aux.abc2(a, b);
! 247: BEGIN;
! 248: INSERT INTO abc VALUES(9, 10);
! 249: }
! 250: file exists test.db-journal
! 251: } {0}
! 252: do_test io-2.7.2 {
! 253: execsql { INSERT INTO abc2 SELECT * FROM abc }
! 254: file exists test2.db-journal
! 255: } {0}
! 256: do_test io-2.7.3 {
! 257: execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
! 258: } {1 2 3 4 5 6 7 8 9 10 1 2 3 4 5 6 7 8 9 10}
! 259: do_test io-2.7.4 {
! 260: file mkdir test2.db-journal
! 261: catchsql { COMMIT }
! 262: } {1 {unable to open database file}}
! 263: do_test io-2.7.5 {
! 264: forcedelete test2.db-journal
! 265: catchsql { COMMIT }
! 266: } {1 {cannot commit - no transaction is active}}
! 267: do_test io-2.7.6 {
! 268: execsql { SELECT * FROM abc UNION ALL SELECT * FROM abc2 }
! 269: } {1 2 3 4 5 6 7 8}
! 270: }
! 271:
! 272: # Try an explicit ROLLBACK before the journal file is created.
! 273: #
! 274: do_test io-2.8.1 {
! 275: execsql {
! 276: BEGIN;
! 277: DELETE FROM abc;
! 278: }
! 279: file exists test.db-journal
! 280: } {0}
! 281: do_test io-2.8.2 {
! 282: execsql { SELECT * FROM abc }
! 283: } {}
! 284: do_test io-2.8.3 {
! 285: execsql {
! 286: ROLLBACK;
! 287: SELECT * FROM abc;
! 288: }
! 289: } {1 2 3 4 5 6 7 8}
! 290:
! 291: # Test that the atomic write optimisation is not enabled if the sector
! 292: # size is larger than the page-size.
! 293: #
! 294: do_test io-2.9.1 {
! 295: db close
! 296: sqlite3 db test.db
! 297: sqlite3_simulate_device -char atomic -sectorsize 2048
! 298: execsql {
! 299: BEGIN;
! 300: INSERT INTO abc VALUES(9, 10);
! 301: }
! 302: file exists test.db-journal
! 303: } {1}
! 304: do_test io-2.9.2 {
! 305: execsql { ROLLBACK; }
! 306: db close
! 307: forcedelete test.db test.db-journal
! 308: sqlite3 db test.db -vfs devsym
! 309: execsql {
! 310: PRAGMA auto_vacuum = OFF;
! 311: PRAGMA page_size = 2048;
! 312: CREATE TABLE abc(a, b);
! 313: }
! 314: execsql {
! 315: BEGIN;
! 316: INSERT INTO abc VALUES(9, 10);
! 317: }
! 318: file exists test.db-journal
! 319: } {0}
! 320: do_test io-2.9.3 {
! 321: execsql { COMMIT }
! 322: } {}
! 323:
! 324: # Test a couple of the more specific IOCAP_ATOMIC flags
! 325: # (i.e IOCAP_ATOMIC2K etc.).
! 326: #
! 327: do_test io-2.10.1 {
! 328: sqlite3_simulate_device -char atomic1k
! 329: execsql {
! 330: BEGIN;
! 331: INSERT INTO abc VALUES(11, 12);
! 332: }
! 333: file exists test.db-journal
! 334: } {1}
! 335: do_test io-2.10.2 {
! 336: execsql { ROLLBACK }
! 337: sqlite3_simulate_device -char atomic2k
! 338: execsql {
! 339: BEGIN;
! 340: INSERT INTO abc VALUES(11, 12);
! 341: }
! 342: file exists test.db-journal
! 343: } {0}
! 344: do_test io-2.10.3 {
! 345: execsql { ROLLBACK }
! 346: } {}
! 347:
! 348: do_test io-2.11.0 {
! 349: execsql {
! 350: PRAGMA locking_mode = exclusive;
! 351: PRAGMA locking_mode;
! 352: }
! 353: } {exclusive exclusive}
! 354: do_test io-2.11.1 {
! 355: execsql {
! 356: INSERT INTO abc VALUES(11, 12);
! 357: }
! 358: file exists test.db-journal
! 359: } {0}
! 360:
! 361: do_test io-2.11.2 {
! 362: execsql {
! 363: PRAGMA locking_mode = normal;
! 364: INSERT INTO abc VALUES(13, 14);
! 365: }
! 366: file exists test.db-journal
! 367: } {0}
! 368:
! 369: } ;# /* ifcapable atomicwrite */
! 370:
! 371: #----------------------------------------------------------------------
! 372: # Test cases io-3.* test the IOCAP_SEQUENTIAL optimization.
! 373: #
! 374: sqlite3_simulate_device -char sequential -sectorsize 0
! 375: ifcapable pager_pragmas {
! 376: do_test io-3.1 {
! 377: db close
! 378: forcedelete test.db test.db-journal
! 379: sqlite3 db test.db -vfs devsym
! 380: db eval {
! 381: PRAGMA auto_vacuum=OFF;
! 382: }
! 383: # File size might be 1 due to the hack to work around ticket #3260.
! 384: # Search for #3260 in os_unix.c for additional information.
! 385: expr {[file size test.db]>1}
! 386: } {0}
! 387: do_test io-3.2 {
! 388: execsql { CREATE TABLE abc(a, b) }
! 389: nSync
! 390: execsql {
! 391: PRAGMA temp_store = memory;
! 392: PRAGMA cache_size = 10;
! 393: BEGIN;
! 394: INSERT INTO abc VALUES('hello', 'world');
! 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: INSERT INTO abc SELECT * FROM abc;
! 402: INSERT INTO abc SELECT * FROM abc;
! 403: INSERT INTO abc SELECT * FROM abc;
! 404: INSERT INTO abc SELECT * FROM abc;
! 405: INSERT INTO abc SELECT * FROM abc;
! 406: }
! 407: # File has grown - showing there was a cache-spill - but there
! 408: # have been no calls to fsync(). The file is probably about 30KB.
! 409: # But some VFS implementations (symbian) buffer writes so the actual
! 410: # size may be a little less than that. So this test case just tests
! 411: # that the file is now greater than 20000 bytes in size.
! 412: list [expr [file size test.db]>20000] [nSync]
! 413: } {1 0}
! 414: do_test io-3.3 {
! 415: # The COMMIT requires a single fsync() - to the database file.
! 416: execsql { COMMIT }
! 417: list [file size test.db] [nSync]
! 418: } {39936 1}
! 419: }
! 420:
! 421: #----------------------------------------------------------------------
! 422: # Test cases io-4.* test the IOCAP_SAFE_APPEND optimization.
! 423: #
! 424: sqlite3_simulate_device -char safe_append
! 425:
! 426: # With the SAFE_APPEND flag set, simple transactions require 3, rather
! 427: # than 4, calls to fsync(). The fsync() calls are on:
! 428: #
! 429: # 1) The directory in which the journal file is created, (unix only)
! 430: # 2) The journal file (to sync the page data),
! 431: # 3) The database file.
! 432: #
! 433: # Normally, when the SAFE_APPEND flag is not set, there is another fsync()
! 434: # on the journal file between steps (2) and (3) above.
! 435: #
! 436: set expected_sync_count 2
! 437: if {$::tcl_platform(platform)=="unix"} {
! 438: ifcapable dirsync {
! 439: incr expected_sync_count
! 440: }
! 441: }
! 442:
! 443: do_test io-4.1 {
! 444: execsql { DELETE FROM abc }
! 445: nSync
! 446: execsql { INSERT INTO abc VALUES('a', 'b') }
! 447: nSync
! 448: } $expected_sync_count
! 449:
! 450: # With SAFE_APPEND set, the nRec field of the journal file header should
! 451: # be set to 0xFFFFFFFF before the first journal sync. The nRec field
! 452: # occupies bytes 8-11 of the journal file.
! 453: #
! 454: do_test io-4.2.1 {
! 455: execsql { BEGIN }
! 456: execsql { INSERT INTO abc VALUES('c', 'd') }
! 457: file exists test.db-journal
! 458: } {1}
! 459: if {$::tcl_platform(platform)=="unix"} {
! 460: do_test io-4.2.2 {
! 461: hexio_read test.db-journal 8 4
! 462: } {FFFFFFFF}
! 463: }
! 464: do_test io-4.2.3 {
! 465: execsql { COMMIT }
! 466: nSync
! 467: } $expected_sync_count
! 468: sqlite3_simulate_device -char safe_append
! 469:
! 470: # With SAFE_APPEND set, there should only ever be one journal-header
! 471: # written to the database, even though the sync-mode is "full".
! 472: #
! 473: do_test io-4.3.1 {
! 474: execsql {
! 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: INSERT INTO abc SELECT * FROM abc;
! 480: INSERT INTO abc SELECT * FROM abc;
! 481: INSERT INTO abc SELECT * FROM abc;
! 482: INSERT INTO abc SELECT * FROM abc;
! 483: INSERT INTO abc SELECT * FROM abc;
! 484: INSERT INTO abc SELECT * FROM abc;
! 485: INSERT INTO abc SELECT * FROM abc;
! 486: }
! 487: expr {[file size test.db]/1024}
! 488: } {43}
! 489: ifcapable pager_pragmas {
! 490: do_test io-4.3.2 {
! 491: execsql {
! 492: PRAGMA synchronous = full;
! 493: PRAGMA cache_size = 10;
! 494: PRAGMA synchronous;
! 495: }
! 496: } {2}
! 497: }
! 498: do_test io-4.3.3 {
! 499: execsql {
! 500: BEGIN;
! 501: UPDATE abc SET a = 'x';
! 502: }
! 503: file exists test.db-journal
! 504: } {1}
! 505: if {$tcl_platform(platform) != "symbian"} {
! 506: # This test is not run on symbian because the file-buffer makes it
! 507: # difficult to predict the exact size of the file as reported by
! 508: # [file size].
! 509: do_test io-4.3.4 {
! 510: # The UPDATE statement in the statement above modifies 41 pages
! 511: # (all pages in the database except page 1 and the root page of
! 512: # abc). Because the cache_size is set to 10, this must have required
! 513: # at least 4 cache-spills. If there were no journal headers written
! 514: # to the journal file after the cache-spill, then the size of the
! 515: # journal file is give by:
! 516: #
! 517: # <jrnl file size> = <jrnl header size> + nPage * (<page-size> + 8)
! 518: #
! 519: # If the journal file contains additional headers, this formula
! 520: # will not predict the size of the journal file.
! 521: #
! 522: file size test.db-journal
! 523: } [expr 512 + (1024+8)*41]
! 524: }
! 525:
! 526: #----------------------------------------------------------------------
! 527: # Test cases io-5.* test that the default page size is selected and
! 528: # used correctly.
! 529: #
! 530: set tn 0
! 531: foreach {char sectorsize pgsize} {
! 532: {} 512 1024
! 533: {} 1024 1024
! 534: {} 2048 2048
! 535: {} 8192 8192
! 536: {} 16384 8192
! 537: {atomic} 512 8192
! 538: {atomic512} 512 1024
! 539: {atomic2K} 512 2048
! 540: {atomic2K} 4096 4096
! 541: {atomic2K atomic} 512 8192
! 542: {atomic64K} 512 1024
! 543: } {
! 544: incr tn
! 545: if {$pgsize>$::SQLITE_MAX_PAGE_SIZE} continue
! 546: db close
! 547: forcedelete test.db test.db-journal
! 548: sqlite3_simulate_device -char $char -sectorsize $sectorsize
! 549: sqlite3 db test.db -vfs devsym
! 550: db eval {
! 551: PRAGMA auto_vacuum=OFF;
! 552: }
! 553: ifcapable !atomicwrite {
! 554: if {[regexp {^atomic} $char]} continue
! 555: }
! 556: do_test io-5.$tn {
! 557: execsql {
! 558: CREATE TABLE abc(a, b, c);
! 559: }
! 560: expr {[file size test.db]/2}
! 561: } $pgsize
! 562: }
! 563:
! 564: sqlite3_simulate_device -char {} -sectorsize 0
! 565: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to io.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test