|
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} { |
|
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 $soft_limit |
|
198 catch {db2 close} |
|
199 finish_test |