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 15set testdir [file dirname $argv0] 16source $testdir/tester.tcl 17 18ifcapable !compound { 19 finish_test 20 return 21} 22 23# Memory statistics must be enabled for this test. 24db close 25sqlite3_shutdown 26sqlite3_config_memstatus 1 27sqlite3_initialize 28sqlite3 db test.db 29 30 31# Make sure sqlite3_db_config() and sqlite3_db_status are working. 32# 33unset -nocomplain PAGESZ 34unset -nocomplain BASESZ 35do_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} 50do_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}] 56 57 58proc lookaside {db} { 59 expr { $::lookaside_buffer_size * 60 [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] 61 } 62} 63 64ifcapable stat4||stat3 { 65 set STAT3 1 66} else { 67 set STAT3 0 68} 69 70ifcapable malloc_usable_size { 71 finish_test 72 return 73} 74 75#--------------------------------------------------------------------------- 76# Run the dbstatus-2 and dbstatus-3 tests with several of different 77# lookaside buffer sizes. 78# 79foreach ::lookaside_buffer_size {0 64 120} { 80 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 86 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" 171 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 178 179 catch { register_echo_module db } 180 ifcapable !vtab { if {[string match *x $tn]} continue } 181 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] 187 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] 193 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] 199 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}] 206 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 } 237 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 } 241 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" 318 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 325 326 catch { register_echo_module db } 327 ifcapable !vtab { if {[string match *x $tn]} continue } 328 329 execsql $schema 330 db cache flush 331 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 338 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] 344 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 351 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] 357 358 set nFree [expr {$nAlloc1-$nAlloc2}] 359 360 do_test dbstatus-3.$tn.a { expr $nStmt2 } {0} 361 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 } 374 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} 379 380finish_test 381