path: root/test/dbstatus.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

ifcapable !compound {
  finish_test
  return
}

# 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
}

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;
    }
    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.
    #
    # Finally, on osx the estimate of memory used by the schema may be
    # slightly low. 
    #
    if {[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]
  }
}

finish_test