Annotation of embedaddon/sqlite3/test/pageropt.test, revision 1.1.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