Annotation of embedaddon/sqlite3/test/pageropt.test, revision 1.1
1.1 ! misho 1: # 2007 April 12
! 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: # This file implements regression tests for SQLite library.
! 12: # The focus of the tests in this file are to verify that the
! 13: # pager optimizations implemented in version 3.3.14 work.
! 14: #
! 15: # $Id: pageropt.test,v 1.5 2008/08/20 14:49:25 danielk1977 Exp $
! 16:
! 17: set testdir [file dirname $argv0]
! 18: source $testdir/tester.tcl
! 19:
! 20: ifcapable {!pager_pragmas||secure_delete||direct_read} {
! 21: finish_test
! 22: return
! 23: }
! 24:
! 25: # Run the SQL statement supplied by the argument and return
! 26: # the results. Prepend four integers to the beginning of the
! 27: # result which are
! 28: #
! 29: # (1) The number of page reads from the database
! 30: # (2) The number of page writes to the database
! 31: # (3) The number of page writes to the journal
! 32: # (4) The number of cache pages freed
! 33: #
! 34: proc pagercount_sql {sql {db db}} {
! 35: global sqlite3_pager_readdb_count
! 36: global sqlite3_pager_writedb_count
! 37: global sqlite3_pager_writej_count
! 38: global sqlite3_pager_pgfree_count
! 39: set sqlite3_pager_readdb_count 0
! 40: set sqlite3_pager_writedb_count 0
! 41: set sqlite3_pager_writej_count 0
! 42: set r [$db eval $sql]
! 43: set cnt [list $sqlite3_pager_readdb_count \
! 44: $sqlite3_pager_writedb_count \
! 45: $sqlite3_pager_writej_count ]
! 46: return [concat $cnt $r]
! 47: }
! 48:
! 49: # Setup the test database
! 50: #
! 51: do_test pageropt-1.1 {
! 52: sqlite3_soft_heap_limit 0
! 53: execsql {
! 54: PRAGMA auto_vacuum = OFF;
! 55: PRAGMA page_size = 1024;
! 56: }
! 57: pagercount_sql {
! 58: CREATE TABLE t1(x);
! 59: }
! 60: } {0 2 0}
! 61: do_test pageropt-1.2 {
! 62: pagercount_sql {
! 63: INSERT INTO t1 VALUES(randomblob(5000));
! 64: }
! 65: } {0 6 2}
! 66:
! 67: # Verify that values remain in cache on for subsequent reads.
! 68: # We should not have to go back to disk.
! 69: #
! 70: do_test pageropt-1.3 {
! 71: pagercount_sql {
! 72: SELECT length(x) FROM t1
! 73: }
! 74: } {0 0 0 5000}
! 75:
! 76: # If another thread reads the database, the original cache
! 77: # remains valid.
! 78: #
! 79: sqlite3 db2 test.db
! 80: set blobcontent [db2 one {SELECT hex(x) FROM t1}]
! 81: do_test pageropt-1.4 {
! 82: pagercount_sql {
! 83: SELECT hex(x) FROM t1
! 84: }
! 85: } [list 0 0 0 $blobcontent]
! 86:
! 87: # But if the other thread modifies the database, then the cache
! 88: # must refill.
! 89: #
! 90: do_test pageropt-1.5 {
! 91: db2 eval {CREATE TABLE t2(y)}
! 92: pagercount_sql {
! 93: SELECT hex(x) FROM t1
! 94: }
! 95: } [list 6 0 0 $blobcontent]
! 96: do_test pageropt-1.6 {
! 97: pagercount_sql {
! 98: SELECT hex(x) FROM t1
! 99: }
! 100: } [list 0 0 0 $blobcontent]
! 101:
! 102: # Verify that the last page of an overflow chain is not read from
! 103: # disk when deleting a row. The one row of t1(x) has four pages
! 104: # of overflow. So deleting that row from t1 should involve reading
! 105: # the sqlite_master table (1 page) the main page of t1 (1 page) and
! 106: # the three overflow pages of t1 for a total of 5 pages.
! 107: #
! 108: # Pages written are page 1 (for the freelist pointer), the root page
! 109: # of the table, and one of the overflow chain pointers because it
! 110: # becomes the trunk of the freelist. Total 3.
! 111: #
! 112: do_test pageropt-2.1 {
! 113: db close
! 114: sqlite3 db test.db
! 115: pagercount_sql {
! 116: DELETE FROM t1 WHERE rowid=1
! 117: }
! 118: } {5 3 3}
! 119:
! 120: # When pulling pages off of the freelist, there is no reason
! 121: # to actually bring in the old content.
! 122: #
! 123: do_test pageropt-2.2 {
! 124: db close
! 125: sqlite3 db test.db
! 126: pagercount_sql {
! 127: INSERT INTO t1 VALUES(randomblob(1500));
! 128: }
! 129: } {3 4 3}
! 130: do_test pageropt-2.3 {
! 131: pagercount_sql {
! 132: INSERT INTO t1 VALUES(randomblob(1500));
! 133: }
! 134: } {0 4 3}
! 135:
! 136: # Note the new optimization that when pulling the very last page off of the
! 137: # freelist we do not read the content of that page.
! 138: #
! 139: do_test pageropt-2.4 {
! 140: pagercount_sql {
! 141: INSERT INTO t1 VALUES(randomblob(1500));
! 142: }
! 143: } {0 5 3}
! 144:
! 145: # Appending a large quantity of data does not involve writing much
! 146: # to the journal file.
! 147: #
! 148: do_test pageropt-3.1 {
! 149: pagercount_sql {
! 150: INSERT INTO t2 SELECT * FROM t1;
! 151: }
! 152: } {1 7 2}
! 153:
! 154: # Once again, we do not need to read the last page of an overflow chain
! 155: # while deleting.
! 156: #
! 157: do_test pageropt-3.2 {
! 158: pagercount_sql {
! 159: DROP TABLE t2;
! 160: }
! 161: } {0 2 3}
! 162: do_test pageropt-3.3 {
! 163: pagercount_sql {
! 164: DELETE FROM t1;
! 165: }
! 166: } {0 3 3}
! 167:
! 168: # There are now 11 pages on the freelist. Move them all into an
! 169: # overflow chain by inserting a single large record. Starting from
! 170: # a cold cache, only page 1, the root page of table t1, and the trunk
! 171: # of the freelist need to be read (3 pages). And only those three
! 172: # pages need to be journalled. But 13 pages need to be written:
! 173: # page1, the root page of table t1, and an 11 page overflow chain.
! 174: #
! 175: do_test pageropt-4.1 {
! 176: db close
! 177: sqlite3 db test.db
! 178: pagercount_sql {
! 179: INSERT INTO t1 VALUES(randomblob(11300))
! 180: }
! 181: } {3 13 3}
! 182:
! 183: # Now we delete that big entries starting from a cold cache and an
! 184: # empty freelist. The first 10 of the 11 pages overflow chain have
! 185: # to be read, together with page1 and the root of the t1 table. 12
! 186: # reads total. But only page1, the t1 root, and the trunk of the
! 187: # freelist need to be journalled and written back.
! 188: #
! 189: do_test pageropt-4.2 {
! 190: db close
! 191: sqlite3 db test.db
! 192: pagercount_sql {
! 193: DELETE FROM t1
! 194: }
! 195: } {12 3 3}
! 196:
! 197: sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
! 198: catch {db2 close}
! 199: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to pageropt.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