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 } { 137 set tn "$::lookaside_buffer_size-$tn" 138 139 # Step 1. 140 db close 141 file delete -force test.db 142 sqlite3 db test.db 143 sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 144 db cache size 0 145 146 # Step 2. 147 execsql $schema 148 set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 149 incr nAlloc1 [lookaside db] 150 set nSchema1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] 151 152 # Step 3. 153 drop_all_tables 154 set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 155 incr nAlloc2 [lookaside db] 156 set nSchema2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] 157 158 # Step 4. 159 execsql $schema 160 set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 161 incr nAlloc3 [lookaside db] 162 set nSchema3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] 163 164 # Step 5. 165 drop_all_tables 166 set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 167 incr nAlloc4 [lookaside db] 168 set nSchema4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] 169 170 set nFree [expr {$nAlloc1-$nAlloc2}] 171 do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree 172 do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3" 173 do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4" 174 } 175 176 #------------------------------------------------------------------------- 177 # Tests for SQLITE_DBSTATUS_STMT_USED. 178 # 179 # Each test in the following block works as follows. Each test uses a 180 # different database schema. 181 # 182 # 1. Open a connection to an empty database. Initialized the database 183 # schema. 184 # 185 # 2. Prepare a bunch of SQL statements. Measure the total heap and 186 # lookaside memory allocated by SQLite, and the memory allocated 187 # for the prepared statements according to sqlite3_db_status(). 188 # 189 # 3. Finalize all prepared statements Measure the total memory 190 # and the prepared statement memory again. 191 # 192 # 4. Repeat step 2. 193 # 194 # 5. Repeat step 3. 195 # 196 # Then test that: 197 # 198 # a) The difference in schema memory quantities in steps 2 and 3 is the 199 # same as the difference in total memory in steps 2 and 3. 200 # 201 # b) Step 4 reports the same amount of schema and total memory used as 202 # in step 2. 203 # 204 # c) Step 5 reports the same amount of schema and total memory used as 205 # in step 3. 206 # 207 foreach {tn schema statements} { 208 1 { CREATE TABLE t1(a, b) } { 209 SELECT * FROM t1; 210 INSERT INTO t1 VALUES(1, 2); 211 INSERT INTO t1 SELECT * FROM t1; 212 UPDATE t1 SET a=5; 213 DELETE FROM t1; 214 } 215 2 { 216 PRAGMA recursive_triggers = 1; 217 CREATE TABLE t1(a, b); 218 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN 219 INSERT INTO t1 VALUES(new.a-1, new.b); 220 END; 221 } { 222 INSERT INTO t1 VALUES(5, 'x'); 223 } 224 3 { 225 PRAGMA recursive_triggers = 1; 226 CREATE TABLE t1(a, b); 227 CREATE TABLE t2(a, b); 228 CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN 229 INSERT INTO t2 VALUES(new.a-1, new.b); 230 END; 231 CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN 232 INSERT INTO t1 VALUES(new.a-1, new.b); 233 END; 234 } { 235 INSERT INTO t1 VALUES(10, 'x'); 236 } 237 4 { 238 CREATE TABLE t1(a, b); 239 } { 240 SELECT count(*) FROM t1 WHERE upper(a)='ABC'; 241 } 242 } { 243 set tn "$::lookaside_buffer_size-$tn" 244 245 # Step 1. 246 db close 247 file delete -force test.db 248 sqlite3 db test.db 249 sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 250 db cache size 1000 251 252 execsql $schema 253 db cache flush 254 255 # Step 2. 256 execsql $statements 257 set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 258 incr nAlloc1 [lookaside db] 259 set nStmt1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] 260 execsql $statements 261 262 # Step 3. 263 db cache flush 264 set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 265 incr nAlloc3 [lookaside db] 266 set nStmt2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] 267 268 # Step 3. 269 execsql $statements 270 set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 271 incr nAlloc3 [lookaside db] 272 set nStmt3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] 273 execsql $statements 274 275 # Step 4. 276 db cache flush 277 set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] 278 incr nAlloc4 [lookaside db] 279 set nStmt4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] 280 281 set nFree [expr {$nAlloc1-$nAlloc2}] 282 do_test dbstatus-3.$tn.a { list $nStmt2 } {0} 283 do_test dbstatus-3.$tn.b { list $nStmt1 } [list $nFree] 284 do_test dbstatus-3.$tn.c { list $nAlloc1 $nStmt1 } [list $nAlloc3 $nStmt3] 285 do_test dbstatus-3.$tn.d { list $nAlloc2 $nStmt2 } [list $nAlloc4 $nStmt4] 286 } 287} 288 289finish_test 290