wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 $cmdlinearg(soft-heap-limit)
198 catch {db2 close}
199 finish_test