sqlite/libsql
0
0
Fork 0
mirror of https://github.com/tursodatabase/libsql.git synced 2024-12-16 13:48:52 +00:00
Code
libsql/libsql-sqlite3/test/dbstatus.test
ad hoc bf899d0b9a
restructure repository
2023-10-16 13:58:16 +02:00

458 lines
14 KiB
Plaintext
Raw Permalink Blame History

# 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
set testprefix dbstatus
ifcapable !compound {
finish_test
return
}
# Memory statistics must be enabled for this test.
db close
sqlite3_shutdown
sqlite3_config_memstatus 1
sqlite3_config_uri 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 stat4 {
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} {
ifcapable malloc_usable_size break
# 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;
}
6k {
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 "k" report slightly less
# memory than is actually freed when all schema items are finalized.
# This is because memory allocated by KeyInfo objects is no longer
# counted as "schema memory".
#
# 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_stat4 is unaccounted for by
# dbstatus.
#
# Finally, on osx the estimate of memory used by the schema may be
# slightly low.
#
if {[string match *k $tn]
|| [string match *x $tn] || $AUTOVACUUM
|| ([string match *y $tn] && $STAT3)
|| ($::tcl_platform(os) == "Darwin")
} {
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]
}
}
#-------------------------------------------------------------------------
# The following tests focus on DBSTATUS_CACHE_USED_SHARED
#
ifcapable shared_cache {
if {([permutation]=="memsys3"
|| [permutation]=="memsys5"
|| $::tcl_platform(os)=="Linux") && ![sqlite3 -has-codec]} {
proc do_cacheused_test {tn db res} {
set cu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED 0]
set pcu [sqlite3_db_status $db SQLITE_DBSTATUS_CACHE_USED_SHARED 0]
set cu [lindex $cu 1]
set pcu [lindex $pcu 1]
uplevel [list do_test $tn [list list $cu $pcu] "#/$res/"]
}
reset_db
sqlite3 db file:test.db?cache=shared
do_execsql_test 4.0 {
PRAGMA auto_vacuum=NONE;
CREATE TABLE t1(a, b, c);
INSERT INTO t1 VALUES(1, 2, 3);
}
do_cacheused_test 4.0.1 db { 4568 4568 }
do_execsql_test 4.1 {
CREATE TEMP TABLE tt(a, b, c);
INSERT INTO tt VALUES(1, 2, 3);
}
do_cacheused_test 4.1.1 db { 9000 9000 }
sqlite3 db2 file:test.db?cache=shared
do_cacheused_test 4.2.1 db2 { 4568 2284 }
do_cacheused_test 4.2.2 db { 9000 6716 }
db close
do_cacheused_test 4.2.3 db2 { 4568 4568 }
sqlite3 db file:test.db?cache=shared
do_cacheused_test 4.2.4 db2 { 4568 2284 }
db2 close
}
}
#-------------------------------------------------------------------------
# Test that passing an out-of-range value to sqlite3_stmt_status does
# not cause a crash.
reset_db
do_execsql_test 5.0 {
CREATE TABLE t1(x, y);
INSERT INTO t1 VALUES(1, 2);
INSERT INTO t1 VALUES(3, 4);
}
do_test 5.1 {
set ::stmt [sqlite3_prepare db "SELECT * FROM t1" -1 dummy]
sqlite3_step $::stmt
sqlite3_step $::stmt
sqlite3_step $::stmt
sqlite3_reset $::stmt
} {SQLITE_OK}
ifcapable api_armor {
do_test 5.2 { sqlite3_stmt_status $::stmt -1 0 } 0
}
do_test 5.3 { sqlite3_stmt_status $::stmt 0 0 } 0
do_test 5.4 {
expr [sqlite3_stmt_status $::stmt 99 0]>0
} 1
foreach {tn id res} {
1 SQLITE_STMTSTATUS_MEMUSED 1
2 SQLITE_STMTSTATUS_FULLSCAN_STEP 1
3 SQLITE_STMTSTATUS_SORT 0
4 SQLITE_STMTSTATUS_AUTOINDEX 0
5 SQLITE_STMTSTATUS_VM_STEP 1
6 SQLITE_STMTSTATUS_REPREPARE 0
7 SQLITE_STMTSTATUS_RUN 1
} {
if {$tn==2} breakpoint
do_test 5.5.$tn { expr [sqlite3_stmt_status $::stmt $id 0]>0 } $res
}
sqlite3_finalize $::stmt
finish_test
View Git Blame Copy Permalink