| 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 |
|
| 15 |
set testdir [file dirname $argv0] |
| 16 |
source $testdir/tester.tcl |
| 17 |
set testprefix dbstatus |
| 18 |
|
| 19 |
ifcapable !compound { |
| 20 |
finish_test |
| 21 |
return |
| 22 |
} |
| 23 |
|
| 24 |
# Memory statistics must be enabled for this test. |
| 25 |
db close |
| 26 |
sqlite3_shutdown |
| 27 |
sqlite3_config_memstatus 1 |
| 28 |
sqlite3_config_uri 1 |
| 29 |
sqlite3_initialize |
| 30 |
sqlite3 db test.db |
| 31 |
|
| 32 |
|
| 33 |
# Make sure sqlite3_db_config() and sqlite3_db_status are working. |
| 34 |
# |
| 35 |
unset -nocomplain PAGESZ |
| 36 |
unset -nocomplain BASESZ |
| 37 |
do_test dbstatus-1.1 { |
| 38 |
db close |
| 39 |
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} |
| 52 |
do_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] 1 |
| 57 |
} [expr {$BASESZ + 10*$PAGESZ}] |
| 58 |
|
| 59 |
|
| 60 |
proc lookaside {db} { |
| 61 |
expr { $::lookaside_buffer_size * |
| 62 |
[lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] |
| 63 |
} |
| 64 |
} |
| 65 |
|
| 66 |
ifcapable stat4 { |
| 67 |
set STAT3 1 |
| 68 |
} else { |
| 69 |
set STAT3 0 |
| 70 |
} |
| 71 |
|
| 72 |
#--------------------------------------------------------------------------- |
| 73 |
# Run the dbstatus-2 and dbstatus-3 tests with several of different |
| 74 |
# lookaside buffer sizes. |
| 75 |
# |
| 76 |
foreach ::lookaside_buffer_size {0 64 120} { |
| 77 |
ifcapable malloc_usable_size break |
| 78 |
|
| 79 |
# Do not run any of these tests if there is SQL configured to run |
| 80 |
# as part of the [sqlite3] command. This prevents the script from |
| 81 |
# configuring the size of the lookaside buffer after [sqlite3] has |
| 82 |
# returned. |
| 83 |
if {[presql] != ""} break |
| 84 |
|
| 85 |
#------------------------------------------------------------------------- |
| 86 |
# Tests for SQLITE_DBSTATUS_SCHEMA_USED. |
| 87 |
# |
| 88 |
# Each test in the following block works as follows. Each test uses a |
| 89 |
# 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 the |
| 107 |
# 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 as |
| 110 |
# in step 2. |
| 111 |
# |
| 112 |
# c) Step 5 reports the same amount of schema and total memory used as |
| 113 |
# 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 BEGIN |
| 126 |
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 t2 |
| 153 |
UNION ALL |
| 154 |
SELECT c||b, d||a FROM t2 LEFT OUTER JOIN t1 GROUP BY c, d |
| 155 |
ORDER BY 1, 2 |
| 156 |
; |
| 157 |
CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN |
| 158 |
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 close |
| 172 |
forcedelete test.db |
| 173 |
sqlite3 db test.db |
| 174 |
sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 |
| 175 |
db cache size 0 |
| 176 |
|
| 177 |
catch { register_echo_module db } |
| 178 |
ifcapable !vtab { if {[string match *x $tn]} continue } |
| 179 |
|
| 180 |
# Step 2. |
| 181 |
execsql $schema |
| 182 |
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_tables |
| 188 |
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 $schema |
| 194 |
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_tables |
| 200 |
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 less |
| 206 |
# memory than is actually freed when all schema items are finalized. |
| 207 |
# This is because memory allocated by KeyInfo objects is no longer |
| 208 |
# counted as "schema memory". |
| 209 |
# |
| 210 |
# Tests for which the test name ends in an "x" report slightly less |
| 211 |
# memory than is actually freed when all schema items are finalized. |
| 212 |
# This is because memory allocated by virtual table implementations |
| 213 |
# for any reason is not counted as "schema memory". |
| 214 |
# |
| 215 |
# Additionally, in auto-vacuum mode, dropping tables and indexes causes |
| 216 |
# the page-cache to shrink. So the amount of memory freed is always |
| 217 |
# much greater than just that reported by DBSTATUS_SCHEMA_USED in this |
| 218 |
# case. |
| 219 |
# |
| 220 |
# Some of the memory used for sqlite_stat4 is unaccounted for by |
| 221 |
# dbstatus. |
| 222 |
# |
| 223 |
# Finally, on osx the estimate of memory used by the schema may be |
| 224 |
# slightly low. |
| 225 |
# |
| 226 |
if {[string match *k $tn] |
| 227 |
|| [string match *x $tn] || $AUTOVACUUM |
| 228 |
|| ([string match *y $tn] && $STAT3) |
| 229 |
|| ($::tcl_platform(os) == "Darwin") |
| 230 |
} { |
| 231 |
do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1 |
| 232 |
} else { |
| 233 |
do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree |
| 234 |
} |
| 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 a |
| 244 |
# different database schema. |
| 245 |
# |
| 246 |
# 1. Open a connection to an empty database. Initialized the database |
| 247 |
# 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 the |
| 263 |
# 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 as |
| 266 |
# in step 2. |
| 267 |
# |
| 268 |
# c) Step 5 reports the same amount of schema and total memory used as |
| 269 |
# 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) BEGIN |
| 283 |
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) BEGIN |
| 293 |
INSERT INTO t2 VALUES(new.a-1, new.b); |
| 294 |
END; |
| 295 |
CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN |
| 296 |
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 close |
| 319 |
forcedelete test.db |
| 320 |
sqlite3 db test.db |
| 321 |
sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 |
| 322 |
db cache size 1000 |
| 323 |
|
| 324 |
catch { register_echo_module db } |
| 325 |
ifcapable !vtab { if {[string match *x $tn]} continue } |
| 326 |
|
| 327 |
execsql $schema |
| 328 |
db cache flush |
| 329 |
|
| 330 |
# Step 2. |
| 331 |
execsql $statements |
| 332 |
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 $statements |
| 336 |
|
| 337 |
# Step 3. |
| 338 |
db cache flush |
| 339 |
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 $statements |
| 345 |
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 $statements |
| 349 |
|
| 350 |
# Step 4. |
| 351 |
db cache flush |
| 352 |
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 less |
| 361 |
# memory than is actually freed when all statements are finalized. |
| 362 |
# This is because a small amount of memory allocated by a virtual table |
| 363 |
# implementation using sqlite3_mprintf() is technically considered |
| 364 |
# 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_SHARED |
| 380 |
# |
| 381 |
ifcapable 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_db |
| 393 |
sqlite3 db file:test.db?cache=shared |
| 394 |
|
| 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=shared |
| 408 |
do_cacheused_test 4.2.1 db2 { 4568 2284 } |
| 409 |
do_cacheused_test 4.2.2 db { 9000 6716 } |
| 410 |
db close |
| 411 |
do_cacheused_test 4.2.3 db2 { 4568 4568 } |
| 412 |
sqlite3 db file:test.db?cache=shared |
| 413 |
do_cacheused_test 4.2.4 db2 { 4568 2284 } |
| 414 |
db2 close |
| 415 |
} |
| 416 |
} |
| 417 |
|
| 418 |
#------------------------------------------------------------------------- |
| 419 |
# Test that passing an out-of-range value to sqlite3_stmt_status does |
| 420 |
# not cause a crash. |
| 421 |
reset_db |
| 422 |
do_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 |
|
| 428 |
do_test 5.1 { |
| 429 |
set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy] |
| 430 |
sqlite3_step $::stmt |
| 431 |
sqlite3_step $::stmt |
| 432 |
sqlite3_step $::stmt |
| 433 |
sqlite3_reset $::stmt |
| 434 |
} {SQLITE_OK} |
| 435 |
|
| 436 |
ifcapable api_armor { |
| 437 |
do_test 5.2 { sqlite3_stmt_status $::stmt -1 0 } 0 |
| 438 |
} |
| 439 |
do_test 5.3 { sqlite3_stmt_status $::stmt 0 0 } 0 |
| 440 |
do_test 5.4 { |
| 441 |
expr [sqlite3_stmt_status $::stmt 99 0]>0 |
| 442 |
} 1 |
| 443 |
foreach {tn id res} { |
| 444 |
1 SQLITE_STMTSTATUS_MEMUSED 1 |
| 445 |
2 SQLITE_STMTSTATUS_FULLSCAN_STEP 1 |
| 446 |
3 SQLITE_STMTSTATUS_SORT 0 |
| 447 |
4 SQLITE_STMTSTATUS_AUTOINDEX 0 |
| 448 |
5 SQLITE_STMTSTATUS_VM_STEP 1 |
| 449 |
6 SQLITE_STMTSTATUS_REPREPARE 0 |
| 450 |
7 SQLITE_STMTSTATUS_RUN 1 |
| 451 |
} { |
| 452 |
if {$tn==2} breakpoint |
| 453 |
do_test 5.5.$tn { expr [sqlite3_stmt_status $::stmt $id 0]>0 } $res |
| 454 |
} |
| 455 |
|
| 456 |
sqlite3_finalize $::stmt |
| 457 |
finish_test |