AryaWu/sqlite
0
1# 2010 March 102#3# The author disclaims copyright to this source code. In place of4# 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() function13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17set testprefix dbstatus18 19ifcapable !compound {20 finish_test21 return22}23 24# Memory statistics must be enabled for this test.25db close26sqlite3_shutdown27sqlite3_config_memstatus 128sqlite3_config_uri 129sqlite3_initialize30sqlite3 db test.db31 32 33# Make sure sqlite3_db_config() and sqlite3_db_status are working.34#35unset -nocomplain PAGESZ36unset -nocomplain BASESZ37do_test dbstatus-1.1 {38 db close39 sqlite3 db :memory:40 db eval {41 CREATE TABLE t1(x);42 }43 set sz1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1]44 db eval {45 CREATE TABLE t2(y);46 }47 set sz2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1]48 set ::PAGESZ [expr {$sz2-$sz1}]49 set ::BASESZ [expr {$sz1-$::PAGESZ}]50 expr {$::PAGESZ>1024 && $::PAGESZ<1300}51} {1}52do_test dbstatus-1.2 {53 db eval {54 INSERT INTO t1 VALUES(zeroblob(9000));55 }56 lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 157} [expr {$BASESZ + 10*$PAGESZ}]58 59 60proc lookaside {db} {61 expr { $::lookaside_buffer_size *62 [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1]63 }64}65 66ifcapable stat4 {67 set STAT3 168} else {69 set STAT3 070}71 72#---------------------------------------------------------------------------73# Run the dbstatus-2 and dbstatus-3 tests with several of different74# lookaside buffer sizes.75#76foreach ::lookaside_buffer_size {0 64 120} {77 ifcapable malloc_usable_size break78 79 # Do not run any of these tests if there is SQL configured to run80 # as part of the [sqlite3] command. This prevents the script from81 # configuring the size of the lookaside buffer after [sqlite3] has82 # returned.83 if {[presql] != ""} break84 85 #-------------------------------------------------------------------------86 # Tests for SQLITE_DBSTATUS_SCHEMA_USED.87 #88 # Each test in the following block works as follows. Each test uses a89 # different database schema.90 #91 # 1. Open a connection to an empty database. Disable statement caching.92 #93 # 2. Execute the SQL to create the database schema. Measure the total 94 # heap and lookaside memory allocated by SQLite, and the memory 95 # allocated for the database schema according to sqlite3_db_status().96 #97 # 3. Drop all tables in the database schema. Measure the total memory 98 # and the schema memory again.99 #100 # 4. Repeat step 2.101 #102 # 5. Repeat step 3.103 #104 # Then test that:105 #106 # a) The difference in schema memory quantities in steps 2 and 3 is the107 # same as the difference in total memory in steps 2 and 3.108 #109 # b) Step 4 reports the same amount of schema and total memory used as110 # in step 2.111 #112 # c) Step 5 reports the same amount of schema and total memory used as113 # in step 3.114 #115 foreach {tn schema} { 116 1 { CREATE TABLE t1(a, b) }117 2 { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1, c UNIQUE) }118 3 {119 CREATE TABLE t1(a, b);120 CREATE INDEX i1 ON t1(a, b);121 }122 4 {123 CREATE TABLE t1(a, b);124 CREATE TABLE t2(c, d);125 CREATE TRIGGER AFTER INSERT ON t1 BEGIN126 INSERT INTO t2 VALUES(new.a, new.b);127 SELECT * FROM t1, t2 WHERE a=c AND b=d GROUP BY b HAVING a>5 ORDER BY a;128 END;129 }130 5 {131 CREATE TABLE t1(a, b);132 CREATE TABLE t2(c, d);133 CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2;134 }135 6k {136 CREATE TABLE t1(a, b);137 CREATE INDEX i1 ON t1(a);138 CREATE INDEX i2 ON t1(a,b);139 CREATE INDEX i3 ON t1(b,b);140 INSERT INTO t1 VALUES(randomblob(20), randomblob(25));141 INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;142 INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;143 INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1;144 ANALYZE;145 }146 7 {147 CREATE TABLE t1(a, b);148 CREATE TABLE t2(c, d);149 CREATE VIEW v1 AS 150 SELECT * FROM t1 151 UNION 152 SELECT * FROM t2153 UNION ALL154 SELECT c||b, d||a FROM t2 LEFT OUTER JOIN t1 GROUP BY c, d155 ORDER BY 1, 2156 ;157 CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN158 SELECT * FROM v1;159 UPDATE t1 SET a=5, b=(SELECT c FROM t2);160 END;161 SELECT * FROM v1;162 }163 8x {164 CREATE TABLE t1(a, b, UNIQUE(a, b));165 CREATE VIRTUAL TABLE t2 USING echo(t1);166 }167 } {168 set tn "$::lookaside_buffer_size-$tn"169 170 # Step 1.171 db close172 forcedelete test.db173 sqlite3 db test.db174 sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500175 db cache size 0176 177 catch { register_echo_module db }178 ifcapable !vtab { if {[string match *x $tn]} continue }179 180 # Step 2.181 execsql $schema182 set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]183 incr nAlloc1 [lookaside db]184 set nSchema1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]185 186 # Step 3.187 drop_all_tables188 set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]189 incr nAlloc2 [lookaside db]190 set nSchema2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]191 192 # Step 4.193 execsql $schema194 set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]195 incr nAlloc3 [lookaside db]196 set nSchema3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]197 198 # Step 5.199 drop_all_tables200 set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]201 incr nAlloc4 [lookaside db]202 set nSchema4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1]203 set nFree [expr {$nAlloc1-$nAlloc2}]204 205 # Tests for which the test name ends in an "k" report slightly less206 # memory than is actually freed when all schema items are finalized.207 # This is because memory allocated by KeyInfo objects is no longer208 # counted as "schema memory".209 #210 # Tests for which the test name ends in an "x" report slightly less211 # memory than is actually freed when all schema items are finalized.212 # This is because memory allocated by virtual table implementations213 # for any reason is not counted as "schema memory".214 #215 # Additionally, in auto-vacuum mode, dropping tables and indexes causes216 # the page-cache to shrink. So the amount of memory freed is always217 # much greater than just that reported by DBSTATUS_SCHEMA_USED in this218 # case.219 #220 # Some of the memory used for sqlite_stat4 is unaccounted for by221 # dbstatus.222 #223 # Finally, on osx the estimate of memory used by the schema may be224 # slightly low. 225 #226 if {[string match *k $tn]227 || [string match *x $tn] || $AUTOVACUUM228 || ([string match *y $tn] && $STAT3)229 || ($::tcl_platform(os) == "Darwin")230 } {231 do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1232 } else {233 do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree234 }235 236 do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3"237 do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4"238 }239 240 #-------------------------------------------------------------------------241 # Tests for SQLITE_DBSTATUS_STMT_USED.242 #243 # Each test in the following block works as follows. Each test uses a244 # different database schema.245 #246 # 1. Open a connection to an empty database. Initialized the database247 # schema.248 #249 # 2. Prepare a bunch of SQL statements. Measure the total heap and 250 # lookaside memory allocated by SQLite, and the memory allocated 251 # for the prepared statements according to sqlite3_db_status().252 #253 # 3. Finalize all prepared statements. Measure the total memory 254 # and the prepared statement memory again.255 #256 # 4. Repeat step 2.257 #258 # 5. Repeat step 3.259 #260 # Then test that:261 #262 # a) The difference in schema memory quantities in steps 2 and 3 is the263 # same as the difference in total memory in steps 2 and 3.264 #265 # b) Step 4 reports the same amount of schema and total memory used as266 # in step 2.267 #268 # c) Step 5 reports the same amount of schema and total memory used as269 # in step 3.270 #271 foreach {tn schema statements} { 272 1 { CREATE TABLE t1(a, b) } {273 SELECT * FROM t1;274 INSERT INTO t1 VALUES(1, 2);275 INSERT INTO t1 SELECT * FROM t1;276 UPDATE t1 SET a=5;277 DELETE FROM t1;278 }279 2 {280 PRAGMA recursive_triggers = 1;281 CREATE TABLE t1(a, b);282 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN283 INSERT INTO t1 VALUES(new.a-1, new.b);284 END;285 } {286 INSERT INTO t1 VALUES(5, 'x');287 } 288 3 {289 PRAGMA recursive_triggers = 1;290 CREATE TABLE t1(a, b);291 CREATE TABLE t2(a, b);292 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN293 INSERT INTO t2 VALUES(new.a-1, new.b);294 END;295 CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN296 INSERT INTO t1 VALUES(new.a-1, new.b);297 END;298 } {299 INSERT INTO t1 VALUES(10, 'x');300 } 301 4 {302 CREATE TABLE t1(a, b);303 } {304 SELECT count(*) FROM t1 WHERE upper(a)='ABC';305 }306 5x {307 CREATE TABLE t1(a, b UNIQUE);308 CREATE VIRTUAL TABLE t2 USING echo(t1);309 } {310 SELECT count(*) FROM t2;311 SELECT * FROM t2 WHERE b>5;312 SELECT * FROM t2 WHERE b='abcdefg';313 }314 } {315 set tn "$::lookaside_buffer_size-$tn"316 317 # Step 1.318 db close319 forcedelete test.db320 sqlite3 db test.db321 sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500322 db cache size 1000323 324 catch { register_echo_module db }325 ifcapable !vtab { if {[string match *x $tn]} continue }326 327 execsql $schema328 db cache flush329 330 # Step 2.331 execsql $statements332 set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]333 incr nAlloc1 [lookaside db]334 set nStmt1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]335 execsql $statements336 337 # Step 3.338 db cache flush339 set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]340 incr nAlloc2 [lookaside db]341 set nStmt2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]342 343 # Step 3.344 execsql $statements345 set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]346 incr nAlloc3 [lookaside db]347 set nStmt3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]348 execsql $statements349 350 # Step 4.351 db cache flush352 set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1]353 incr nAlloc4 [lookaside db]354 set nStmt4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1]355 356 set nFree [expr {$nAlloc1-$nAlloc2}]357 358 do_test dbstatus-3.$tn.a { expr $nStmt2 } {0}359 360 # Tests for which the test name ends in an "x" report slightly less361 # memory than is actually freed when all statements are finalized.362 # This is because a small amount of memory allocated by a virtual table363 # implementation using sqlite3_mprintf() is technically considered364 # external and so is not counted as "statement memory".365 #366#puts "$nStmt1 $nFree"367 if {[string match *x $tn]} {368 do_test dbstatus-3.$tn.bx { expr $nStmt1<=$nFree } {1}369 } else {370 do_test dbstatus-3.$tn.b { expr $nStmt1==$nFree } {1}371 }372 373 do_test dbstatus-3.$tn.c { list $nAlloc1 $nStmt1 } [list $nAlloc3 $nStmt3]374 do_test dbstatus-3.$tn.d { list $nAlloc2 $nStmt2 } [list $nAlloc4 $nStmt4]375 }376}377 378#-------------------------------------------------------------------------379# The following tests focus on DBSTATUS_CACHE_USED_SHARED380#381ifcapable shared_cache {382 if {([permutation]=="memsys3"383 || [permutation]=="memsys5"384 || $::tcl_platform(os)=="Linux") && ![sqlite3 -has-codec]} {385 proc do_cacheused_test {tn db res} {386 set cu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED 0]387 set pcu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED_SHARED 0]388 set cu [lindex $cu 1]389 set pcu [lindex $pcu 1]390 uplevel [list do_test $tn [list list $cu $pcu] "#/$res/"]391 }392 reset_db393 sqlite3 db file:test.db?cache=shared394 395 do_execsql_test 4.0 {396 PRAGMA auto_vacuum=NONE;397 CREATE TABLE t1(a, b, c);398 INSERT INTO t1 VALUES(1, 2, 3);399 }400 do_cacheused_test 4.0.1 db { 4568 4568 }401 do_execsql_test 4.1 {402 CREATE TEMP TABLE tt(a, b, c);403 INSERT INTO tt VALUES(1, 2, 3);404 }405 do_cacheused_test 4.1.1 db { 9000 9000 }406 407 sqlite3 db2 file:test.db?cache=shared408 do_cacheused_test 4.2.1 db2 { 4568 2284 }409 do_cacheused_test 4.2.2 db { 9000 6716 }410 db close411 do_cacheused_test 4.2.3 db2 { 4568 4568 }412 sqlite3 db file:test.db?cache=shared413 do_cacheused_test 4.2.4 db2 { 4568 2284 }414 db2 close415 }416}417 418#-------------------------------------------------------------------------419# Test that passing an out-of-range value to sqlite3_stmt_status does420# not cause a crash.421reset_db422do_execsql_test 5.0 {423 CREATE TABLE t1(x, y);424 INSERT INTO t1 VALUES(1, 2);425 INSERT INTO t1 VALUES(3, 4);426}427 428do_test 5.1 {429 set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy]430 sqlite3_step $::stmt431 sqlite3_step $::stmt432 sqlite3_step $::stmt433 sqlite3_reset $::stmt434} {SQLITE_OK}435 436ifcapable api_armor {437 do_test 5.2 { sqlite3_stmt_status $::stmt -1 0 } 0438}439do_test 5.3 { sqlite3_stmt_status $::stmt 0 0 } 0440do_test 5.4 { 441 expr [sqlite3_stmt_status $::stmt 99 0]>0 442} 1443foreach {tn id res} {444 1 SQLITE_STMTSTATUS_MEMUSED 1445 2 SQLITE_STMTSTATUS_FULLSCAN_STEP 1446 3 SQLITE_STMTSTATUS_SORT 0447 4 SQLITE_STMTSTATUS_AUTOINDEX 0448 5 SQLITE_STMTSTATUS_VM_STEP 1449 6 SQLITE_STMTSTATUS_REPREPARE 0450 7 SQLITE_STMTSTATUS_RUN 1451} {452if {$tn==2} breakpoint453 do_test 5.5.$tn { expr [sqlite3_stmt_status $::stmt $id 0]>0 } $res454}455 456sqlite3_finalize $::stmt457finish_test458