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