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