gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/dbstatus.test (about) 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