/trunk/test/dbstatus.test |
@@ -0,0 +1,349 @@ |
# 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 |