test/dbstatus.test - external/github.com/pwnall/sqlite - Git at Google

 # 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||stat3 {
   set STAT3 1
 } else {
   set STAT3 0
 }

 ifcapable malloc_usable_size {
   finish_test
   return
 }

 #---------------------------------------------------------------------------
 # 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;
     }
     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 {
   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] [list {*}$res]]
   }
   reset_db
   sqlite3 db file:test.db?cache=shared

   do_execsql_test 4.0 {
     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
 }

 finish_test
	# 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\|\|stat3 {
	set STAT3 1
	} else {
	set STAT3 0
	}

	ifcapable malloc_usable_size {
	finish_test
	return
	}

	#---------------------------------------------------------------------------
	# 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;
	}
	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 {
	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] [list {*}$res]]
	}
	reset_db
	sqlite3 db file:test.db?cache=shared

	do_execsql_test 4.0 {
	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
	}

	finish_test