# 2010 March 10 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # Tests for the sqlite3_db_status() function # set testdir [file dirname $argv0] source $testdir/tester.tcl # Memory statistics must be enabled for this test. db close sqlite3_shutdown sqlite3_config_memstatus 1 sqlite3_initialize sqlite3 db test.db # Make sure sqlite3_db_config() and sqlite3_db_status are working. # unset -nocomplain PAGESZ unset -nocomplain BASESZ do_test dbstatus-1.1 { db close sqlite3 db :memory: db eval { CREATE TABLE t1(x); } set sz1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1] db eval { CREATE TABLE t2(y); } set sz2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1] set ::PAGESZ [expr {$sz2-$sz1}] set ::BASESZ [expr {$sz1-$::PAGESZ}] expr {$::PAGESZ>1024 && $::PAGESZ<1300} } {1} do_test dbstatus-1.2 { db eval { INSERT INTO t1 VALUES(zeroblob(9000)); } lindex [sqlite3_db_status db SQLITE_DBSTATUS_CACHE_USED 0] 1 } [expr {$BASESZ + 10*$PAGESZ}] proc lookaside {db} { expr { $::lookaside_buffer_size * [lindex [sqlite3_db_status $db SQLITE_DBSTATUS_LOOKASIDE_USED 0] 1] } } #--------------------------------------------------------------------------- # Run the dbstatus-2 and dbstatus-3 tests with several of different # lookaside buffer sizes. # foreach ::lookaside_buffer_size {0 64 120} { # Do not run any of these tests if there is SQL configured to run # as part of the [sqlite3] command. This prevents the script from # configuring the size of the lookaside buffer after [sqlite3] has # returned. if {[presql] != ""} break #------------------------------------------------------------------------- # Tests for SQLITE_DBSTATUS_SCHEMA_USED. # # Each test in the following block works as follows. Each test uses a # different database schema. # # 1. Open a connection to an empty database. Disable statement caching. # # 2. Execute the SQL to create the database schema. Measure the total # heap and lookaside memory allocated by SQLite, and the memory # allocated for the database schema according to sqlite3_db_status(). # # 3. Drop all tables in the database schema. Measure the total memory # and the schema memory again. # # 4. Repeat step 2. # # 5. Repeat step 3. # # Then test that: # # a) The difference in schema memory quantities in steps 2 and 3 is the # same as the difference in total memory in steps 2 and 3. # # b) Step 4 reports the same amount of schema and total memory used as # in step 2. # # c) Step 5 reports the same amount of schema and total memory used as # in step 3. # foreach {tn schema} { 1 { CREATE TABLE t1(a, b) } 2 { CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1, c UNIQUE) } 3 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a, b); } 4 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); CREATE TRIGGER AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.a, new.b); SELECT * FROM t1, t2 WHERE a=c AND b=d GROUP BY b HAVING a>5 ORDER BY a; END; } 5 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2; } 6 { CREATE TABLE t1(a, b); CREATE INDEX i1 ON t1(a); CREATE INDEX i2 ON t1(a,b); CREATE INDEX i3 ON t1(b,b); INSERT INTO t1 VALUES(randomblob(20), randomblob(25)); INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; INSERT INTO t1 SELECT randomblob(20), randomblob(25) FROM t1; ANALYZE; } 7 { CREATE TABLE t1(a, b); CREATE TABLE t2(c, d); CREATE VIEW v1 AS SELECT * FROM t1 UNION SELECT * FROM t2 UNION ALL SELECT c||b, d||a FROM t2 LEFT OUTER JOIN t1 GROUP BY c, d ORDER BY 1, 2 ; CREATE TRIGGER tr1 INSTEAD OF INSERT ON v1 BEGIN SELECT * FROM v1; UPDATE t1 SET a=5, b=(SELECT c FROM t2); END; SELECT * FROM v1; } 8x { CREATE TABLE t1(a, b, UNIQUE(a, b)); CREATE VIRTUAL TABLE t2 USING echo(t1); } } { set tn "$::lookaside_buffer_size-$tn" # Step 1. db close file delete -force test.db sqlite3 db test.db sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 db cache size 0 catch { register_echo_module db } ifcapable !vtab { if {[string match *x $tn]} continue } # Step 2. execsql $schema set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc1 [lookaside db] set nSchema1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] # Step 3. drop_all_tables set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc2 [lookaside db] set nSchema2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] # Step 4. execsql $schema set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc3 [lookaside db] set nSchema3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] # Step 5. drop_all_tables set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc4 [lookaside db] set nSchema4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_SCHEMA_USED 0] 1] set nFree [expr {$nAlloc1-$nAlloc2}] # Tests for which the test name ends in an "x" report slightly less # memory than is actually freed when all schema items are finalized. # This is because memory allocated by virtual table implementations # for any reason is not counted as "schema memory". # # Additionally, in auto-vacuum mode, dropping tables and indexes causes # the page-cache to shrink. So the amount of memory freed is always # much greater than just that reported by DBSTATUS_SCHEMA_USED in this # case. # if {[string match *x $tn] || $AUTOVACUUM} { do_test dbstatus-2.$tn.ax { expr {($nSchema1-$nSchema2)<=$nFree} } 1 } else { do_test dbstatus-2.$tn.a { expr {$nSchema1-$nSchema2} } $nFree } do_test dbstatus-2.$tn.b { list $nAlloc1 $nSchema1 } "$nAlloc3 $nSchema3" do_test dbstatus-2.$tn.c { list $nAlloc2 $nSchema2 } "$nAlloc4 $nSchema4" } #------------------------------------------------------------------------- # Tests for SQLITE_DBSTATUS_STMT_USED. # # Each test in the following block works as follows. Each test uses a # different database schema. # # 1. Open a connection to an empty database. Initialized the database # schema. # # 2. Prepare a bunch of SQL statements. Measure the total heap and # lookaside memory allocated by SQLite, and the memory allocated # for the prepared statements according to sqlite3_db_status(). # # 3. Finalize all prepared statements Measure the total memory # and the prepared statement memory again. # # 4. Repeat step 2. # # 5. Repeat step 3. # # Then test that: # # a) The difference in schema memory quantities in steps 2 and 3 is the # same as the difference in total memory in steps 2 and 3. # # b) Step 4 reports the same amount of schema and total memory used as # in step 2. # # c) Step 5 reports the same amount of schema and total memory used as # in step 3. # foreach {tn schema statements} { 1 { CREATE TABLE t1(a, b) } { SELECT * FROM t1; INSERT INTO t1 VALUES(1, 2); INSERT INTO t1 SELECT * FROM t1; UPDATE t1 SET a=5; DELETE FROM t1; } 2 { PRAGMA recursive_triggers = 1; CREATE TABLE t1(a, b); CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN INSERT INTO t1 VALUES(new.a-1, new.b); END; } { INSERT INTO t1 VALUES(5, 'x'); } 3 { PRAGMA recursive_triggers = 1; CREATE TABLE t1(a, b); CREATE TABLE t2(a, b); CREATE TRIGGER tr1 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN INSERT INTO t2 VALUES(new.a-1, new.b); END; CREATE TRIGGER tr2 AFTER INSERT ON t1 WHEN (new.a>0) BEGIN INSERT INTO t1 VALUES(new.a-1, new.b); END; } { INSERT INTO t1 VALUES(10, 'x'); } 4 { CREATE TABLE t1(a, b); } { SELECT count(*) FROM t1 WHERE upper(a)='ABC'; } 5x { CREATE TABLE t1(a, b UNIQUE); CREATE VIRTUAL TABLE t2 USING echo(t1); } { SELECT count(*) FROM t2; SELECT * FROM t2 WHERE b>5; SELECT * FROM t2 WHERE b='abcdefg'; } } { set tn "$::lookaside_buffer_size-$tn" # Step 1. db close file delete -force test.db sqlite3 db test.db sqlite3_db_config_lookaside db 0 $::lookaside_buffer_size 500 db cache size 1000 catch { register_echo_module db } ifcapable !vtab { if {[string match *x $tn]} continue } execsql $schema db cache flush # Step 2. execsql $statements set nAlloc1 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc1 [lookaside db] set nStmt1 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] execsql $statements # Step 3. db cache flush set nAlloc2 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc2 [lookaside db] set nStmt2 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] # Step 3. execsql $statements set nAlloc3 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc3 [lookaside db] set nStmt3 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] execsql $statements # Step 4. db cache flush set nAlloc4 [lindex [sqlite3_status SQLITE_STATUS_MEMORY_USED 0] 1] incr nAlloc4 [lookaside db] set nStmt4 [lindex [sqlite3_db_status db SQLITE_DBSTATUS_STMT_USED 0] 1] set nFree [expr {$nAlloc1-$nAlloc2}] do_test dbstatus-3.$tn.a { expr $nStmt2 } {0} # Tests for which the test name ends in an "x" report slightly less # memory than is actually freed when all statements are finalized. # This is because a small amount of memory allocated by a virtual table # implementation using sqlite3_mprintf() is technically considered # external and so is not counted as "statement memory". # #puts "$nStmt1 $nFree" if {[string match *x $tn]} { do_test dbstatus-3.$tn.bx { expr $nStmt1<=$nFree } {1} } else { do_test dbstatus-3.$tn.b { expr $nStmt1==$nFree } {1} } do_test dbstatus-3.$tn.c { list $nAlloc1 $nStmt1 } [list $nAlloc3 $nStmt3] do_test dbstatus-3.$tn.d { list $nAlloc2 $nStmt2 } [list $nAlloc4 $nStmt4] } } finish_test