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