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