wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
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