diff options
Diffstat (limited to 'test/dbstatus.test')
| -rw-r--r-- | test/dbstatus.test | 359 |
1 files changed, 359 insertions, 0 deletions
diff --git a/test/dbstatus.test b/test/dbstatus.test new file mode 100644 index 0000000..e1c8f3e --- /dev/null +++ b/test/dbstatus.test @@ -0,0 +1,359 @@ +# 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] + } +} + +ifcapable stat3 { + set STAT3 1 +} else { + set STAT3 0 +} + +#--------------------------------------------------------------------------- +# 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; + } + 6y { + 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 + forcedelete 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. + # + # Some of the memory used for sqlite_stat3 is unaccounted for by + # dbstatus. + # + if {[string match *x $tn] || $AUTOVACUUM + || ([string match *y $tn] && $STAT3)} { + 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 + forcedelete 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 |