wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2010 March 10 |
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 | # Tests for the sqlite3_db_status() function |
||
13 | # |
||
14 | |||
15 | set testdir [file dirname $argv0] |
||
16 | source $testdir/tester.tcl |
||
17 | |||
18 | # Memory statistics must be enabled for this test. |
||
19 | db close |
||
20 | sqlite3_shutdown |
||
21 | sqlite3_config_memstatus 1 |
||
22 | sqlite3_initialize |
||
23 | sqlite3 db test.db |
||
24 | |||
25 | |||
26 | # Make sure sqlite3_db_config() and sqlite3_db_status are working. |
||
27 | # |
||
28 | unset -nocomplain PAGESZ |
||
29 | unset -nocomplain BASESZ |
||
30 | do_test dbstatus-1.1 { |
||
31 | db close |
||
32 | sqlite3 db :memory: |
||
33 | db eval { |
||
34 | CREATE TABLE t1(x); |
||
35 | } |
||
36 | set sz1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1] |
||
37 | db eval { |
||
38 | CREATE TABLE t2(y); |
||
39 | } |
||
40 | set sz2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1] |
||
41 | set ::PAGESZ [expr {$sz2-$sz1}] |
||
42 | set ::BASESZ [expr {$sz1-$::PAGESZ}] |
||
43 | expr {$::PAGESZ>1024 && $::PAGESZ<1300} |
||
44 | } {1} |
||
45 | do_test dbstatus-1.2 { |
||
46 | db eval { |
||
47 | INSERT INTO t1 VALUES(zeroblob(9000)); |
||
48 | } |
||
49 | lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1 |
||
50 | } [expr {$BASESZ + 10*$PAGESZ}] |
||
51 | |||
52 | |||
53 | proc lookaside {db} { |
||
54 | expr { $::lookaside_buffer_size * |
||
55 | [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] |
||
56 | } |
||
57 | } |
||
58 | |||
59 | #--------------------------------------------------------------------------- |
||
60 | # Run the dbstatus-2 and dbstatus-3 tests with several of different |
||
61 | # lookaside buffer sizes. |
||
62 | # |
||
63 | foreach ::lookaside_buffer_size {0 64 120} { |
||
64 | |||
65 | # Do not run any of these tests if there is SQL configured to run |
||
66 | # as part of the [sqlite3] command. This prevents the script from |
||
67 | # configuring the size of the lookaside buffer after [sqlite3] has |
||
68 | # returned. |
||
69 | if {[presql] != ""} break |
||
70 | |||
71 | #------------------------------------------------------------------------- |
||
72 | # Tests for SQLITE_DBSTATUS_SCHEMA_USED. |
||
73 | # |
||
74 | # Each test in the following block works as follows. Each test uses a |
||
75 | # different database schema. |
||
76 | # |
||
77 | # 1. Open a connection to an empty database. Disable statement caching. |
||
78 | # |
||
79 | # 2. Execute the SQL to create the database schema. Measure the total |
||
80 | # heap and lookaside memory allocated by SQLite, and the memory |
||
81 | # allocated for the database schema according to sqlite3_db_status(). |
||
82 | # |
||
83 | # 3. Drop all tables in the database schema. Measure the total memory |
||
84 | # and the schema memory again. |
||
85 | # |
||
86 | # 4. Repeat step 2. |
||
87 | # |
||
88 | # 5. Repeat step 3. |
||
89 | # |
||
90 | # Then test that: |
||
91 | # |
||
92 | # a) The difference in schema memory quantities in steps 2 and 3 is the |
||
93 | # same as the difference in total memory in steps 2 and 3. |
||
94 | # |
||
95 | # b) Step 4 reports the same amount of schema and total memory used as |
||
96 | # in step 2. |
||
97 | # |
||
98 | # c) Step 5 reports the same amount of schema and total memory used as |
||
99 | # in step 3. |
||
100 | # |
||
101 | foreach {tn schema} { |
||
102 | 1 { CREATE TABLE t1(a, b) } |
||
103 | 2 { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1, c UNIQUE) } |
||
104 | 3 { |
||
105 | CREATE TABLE t1(a, b); |
||
106 | CREATE INDEX i1 ON t1(a, b); |
||
107 | } |
||
108 | 4 { |
||
109 | CREATE TABLE t1(a, b); |
||
110 | CREATE TABLE t2(c, d); |
||
111 | CREATE TRIGGER AFTER INSERT ON t1 BEGIN |
||
112 | INSERT INTO t2 VALUES(new.a, new.b); |
||
113 | SELECT * FROM t1, t2 WHERE a=c AND b=d GROUP BY b HAVING a>5 ORDER BY a; |
||
114 | END; |
||
115 | } |
||
116 | 5 { |
||
117 | CREATE TABLE t1(a, b); |
||
118 | CREATE TABLE t2(c, d); |
||
119 | CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2; |
||
120 | } |
||
121 | 6 { |
||
122 | CREATE TABLE t1(a, b); |
||
123 | CREATE INDEX i1 ON t1(a); |
||
124 | CREATE INDEX i2 ON t1(a,b); |
||
125 | CREATE INDEX i3 ON t1(b,b); |
||
126 | INSERT INTO t1 VALUES(randomblob(20), randomblob(25)); |
||
127 | INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; |
||
128 | INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; |
||
129 | INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; |
||
130 | ANALYZE; |
||
131 | } |
||
132 | 7 { |
||
133 | CREATE TABLE t1(a, b); |
||
134 | CREATE TABLE t2(c, d); |
||
135 | CREATE VIEW v1 AS |
||
136 | SELECT * FROM t1 |
||
137 | UNION |
||
138 | SELECT * FROM t2 |
||
139 | UNION ALL |
||
140 | SELECT c||b, d||a FROM t2 LEFT OUTER JOIN t1 GROUP BY c, d |
||
141 | ORDER BY 1, 2 |
||
142 | ; |
||
143 | CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN |
||
144 | SELECT * FROM v1; |
||
145 | UPDATE t1 SET a=5, b=(SELECT c FROM t2); |
||
146 | END; |
||
147 | SELECT * FROM v1; |
||
148 | } |
||
149 | 8x { |
||
150 | CREATE TABLE t1(a, b, UNIQUE(a, b)); |
||
151 | CREATE VIRTUAL TABLE t2 USING echo(t1); |
||
152 | } |
||
153 | } { |
||
154 | set tn "$::lookaside_buffer_size-$tn" |
||
155 | |||
156 | # Step 1. |
||
157 | db close |
||
158 | file delete -force test.db |
||
159 | sqlite3 db test.db |
||
160 | sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 |
||
161 | db cache size 0 |
||
162 | |||
163 | catch { register_echo_module db } |
||
164 | ifcapable !vtab { if {[string match *x $tn]} continue } |
||
165 | |||
166 | # Step 2. |
||
167 | execsql $schema |
||
168 | set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
169 | incr nAlloc1 [lookaside db] |
||
170 | set nSchema1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] |
||
171 | |||
172 | # Step 3. |
||
173 | drop_all_tables |
||
174 | set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
175 | incr nAlloc2 [lookaside db] |
||
176 | set nSchema2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] |
||
177 | |||
178 | # Step 4. |
||
179 | execsql $schema |
||
180 | set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
181 | incr nAlloc3 [lookaside db] |
||
182 | set nSchema3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] |
||
183 | |||
184 | # Step 5. |
||
185 | drop_all_tables |
||
186 | set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
187 | incr nAlloc4 [lookaside db] |
||
188 | set nSchema4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] |
||
189 | set nFree [expr {$nAlloc1-$nAlloc2}] |
||
190 | |||
191 | # Tests for which the test name ends in an "x" report slightly less |
||
192 | # memory than is actually freed when all schema items are finalized. |
||
193 | # This is because memory allocated by virtual table implementations |
||
194 | # for any reason is not counted as "schema memory". |
||
195 | # |
||
196 | # Additionally, in auto-vacuum mode, dropping tables and indexes causes |
||
197 | # the page-cache to shrink. So the amount of memory freed is always |
||
198 | # much greater than just that reported by DBSTATUS_SCHEMA_USED in this |
||
199 | # case. |
||
200 | # |
||
201 | if {[string match *x $tn] || $AUTOVACUUM} { |
||
202 | do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1 |
||
203 | } else { |
||
204 | do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree |
||
205 | } |
||
206 | |||
207 | do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3" |
||
208 | do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4" |
||
209 | } |
||
210 | |||
211 | #------------------------------------------------------------------------- |
||
212 | # Tests for SQLITE_DBSTATUS_STMT_USED. |
||
213 | # |
||
214 | # Each test in the following block works as follows. Each test uses a |
||
215 | # different database schema. |
||
216 | # |
||
217 | # 1. Open a connection to an empty database. Initialized the database |
||
218 | # schema. |
||
219 | # |
||
220 | # 2. Prepare a bunch of SQL statements. Measure the total heap and |
||
221 | # lookaside memory allocated by SQLite, and the memory allocated |
||
222 | # for the prepared statements according to sqlite3_db_status(). |
||
223 | # |
||
224 | # 3. Finalize all prepared statements Measure the total memory |
||
225 | # and the prepared statement memory again. |
||
226 | # |
||
227 | # 4. Repeat step 2. |
||
228 | # |
||
229 | # 5. Repeat step 3. |
||
230 | # |
||
231 | # Then test that: |
||
232 | # |
||
233 | # a) The difference in schema memory quantities in steps 2 and 3 is the |
||
234 | # same as the difference in total memory in steps 2 and 3. |
||
235 | # |
||
236 | # b) Step 4 reports the same amount of schema and total memory used as |
||
237 | # in step 2. |
||
238 | # |
||
239 | # c) Step 5 reports the same amount of schema and total memory used as |
||
240 | # in step 3. |
||
241 | # |
||
242 | foreach {tn schema statements} { |
||
243 | 1 { CREATE TABLE t1(a, b) } { |
||
244 | SELECT * FROM t1; |
||
245 | INSERT INTO t1 VALUES(1, 2); |
||
246 | INSERT INTO t1 SELECT * FROM t1; |
||
247 | UPDATE t1 SET a=5; |
||
248 | DELETE FROM t1; |
||
249 | } |
||
250 | 2 { |
||
251 | PRAGMA recursive_triggers = 1; |
||
252 | CREATE TABLE t1(a, b); |
||
253 | CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN |
||
254 | INSERT INTO t1 VALUES(new.a-1, new.b); |
||
255 | END; |
||
256 | } { |
||
257 | INSERT INTO t1 VALUES(5, 'x'); |
||
258 | } |
||
259 | 3 { |
||
260 | PRAGMA recursive_triggers = 1; |
||
261 | CREATE TABLE t1(a, b); |
||
262 | CREATE TABLE t2(a, b); |
||
263 | CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN |
||
264 | INSERT INTO t2 VALUES(new.a-1, new.b); |
||
265 | END; |
||
266 | CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN |
||
267 | INSERT INTO t1 VALUES(new.a-1, new.b); |
||
268 | END; |
||
269 | } { |
||
270 | INSERT INTO t1 VALUES(10, 'x'); |
||
271 | } |
||
272 | 4 { |
||
273 | CREATE TABLE t1(a, b); |
||
274 | } { |
||
275 | SELECT count(*) FROM t1 WHERE upper(a)='ABC'; |
||
276 | } |
||
277 | 5x { |
||
278 | CREATE TABLE t1(a, b UNIQUE); |
||
279 | CREATE VIRTUAL TABLE t2 USING echo(t1); |
||
280 | } { |
||
281 | SELECT count(*) FROM t2; |
||
282 | SELECT * FROM t2 WHERE b>5; |
||
283 | SELECT * FROM t2 WHERE b='abcdefg'; |
||
284 | } |
||
285 | } { |
||
286 | set tn "$::lookaside_buffer_size-$tn" |
||
287 | |||
288 | # Step 1. |
||
289 | db close |
||
290 | file delete -force test.db |
||
291 | sqlite3 db test.db |
||
292 | sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 |
||
293 | db cache size 1000 |
||
294 | |||
295 | catch { register_echo_module db } |
||
296 | ifcapable !vtab { if {[string match *x $tn]} continue } |
||
297 | |||
298 | execsql $schema |
||
299 | db cache flush |
||
300 | |||
301 | # Step 2. |
||
302 | execsql $statements |
||
303 | set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
304 | incr nAlloc1 [lookaside db] |
||
305 | set nStmt1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] |
||
306 | execsql $statements |
||
307 | |||
308 | # Step 3. |
||
309 | db cache flush |
||
310 | set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
311 | incr nAlloc2 [lookaside db] |
||
312 | set nStmt2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] |
||
313 | |||
314 | # Step 3. |
||
315 | execsql $statements |
||
316 | set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
317 | incr nAlloc3 [lookaside db] |
||
318 | set nStmt3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] |
||
319 | execsql $statements |
||
320 | |||
321 | # Step 4. |
||
322 | db cache flush |
||
323 | set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] |
||
324 | incr nAlloc4 [lookaside db] |
||
325 | set nStmt4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] |
||
326 | |||
327 | set nFree [expr {$nAlloc1-$nAlloc2}] |
||
328 | |||
329 | do_test dbstatus-3.$tn.a { expr $nStmt2 } {0} |
||
330 | |||
331 | # Tests for which the test name ends in an "x" report slightly less |
||
332 | # memory than is actually freed when all statements are finalized. |
||
333 | # This is because a small amount of memory allocated by a virtual table |
||
334 | # implementation using sqlite3_mprintf() is technically considered |
||
335 | # external and so is not counted as "statement memory". |
||
336 | # |
||
337 | #puts "$nStmt1 $nFree" |
||
338 | if {[string match *x $tn]} { |
||
339 | do_test dbstatus-3.$tn.bx { expr $nStmt1<=$nFree } {1} |
||
340 | } else { |
||
341 | do_test dbstatus-3.$tn.b { expr $nStmt1==$nFree } {1} |
||
342 | } |
||
343 | |||
344 | do_test dbstatus-3.$tn.c { list $nAlloc1 $nStmt1 } [list $nAlloc3 $nStmt3] |
||
345 | do_test dbstatus-3.$tn.d { list $nAlloc2 $nStmt2 } [list $nAlloc4 $nStmt4] |
||
346 | } |
||
347 | } |
||
348 | |||
349 | finish_test |