# 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