gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/cache.test (about)

     1  # 2007 March 24
     2  #
     3  # The author disclaims copyright to this source code.  In place of
     4  # a legal notice, here is a blessing:
     5  #
     6  #    May you do good and not evil.
     7  #    May you find forgiveness for yourself and forgive others.
     8  #    May you share freely, never taking more than you give.
     9  #
    10  #***********************************************************************
    11  #
    12  # $Id: cache.test,v 1.4 2007/08/22 02:56:44 drh Exp $
    13  
    14  set testdir [file dirname $argv0]
    15  source $testdir/tester.tcl
    16  
    17  ifcapable !pager_pragmas||!compound {
    18    finish_test
    19    return
    20  }
    21  sqlite3_soft_heap_limit 0
    22  
    23  proc pager_cache_size {db} {
    24    set bt [btree_from_db $db]
    25    db_enter $db
    26    array set stats [btree_pager_stats $bt]
    27    db_leave $db
    28    return $stats(page)
    29  }
    30  
    31  if {[permutation] == ""} { 
    32    do_test cache-1.1 { pager_cache_size db } {0}
    33  }
    34  
    35  do_test cache-1.2 {
    36    execsql {
    37      PRAGMA auto_vacuum=OFF;
    38      CREATE TABLE abc(a, b, c);
    39      INSERT INTO abc VALUES(1, 2, 3);
    40    }
    41    pager_cache_size db
    42  } {2}
    43  
    44  # At one point, repeatedly locking and unlocking the cache was causing
    45  # a resource leak of one page per repetition. The page wasn't actually
    46  # leaked, but would not be reused until the pager-cache was full (i.e. 
    47  # 2000 pages by default).
    48  #
    49  # This tests that once the pager-cache is initialized, it can be locked
    50  # and unlocked repeatedly without internally allocating any new pages.
    51  #
    52  set cache_size [pager_cache_size db]
    53  for {set ii 0} {$ii < 10} {incr ii} {
    54    do_test cache-1.3.$ii {
    55      execsql {SELECT * FROM abc}
    56      pager_cache_size db
    57    } $::cache_size
    58  }
    59  
    60  #-------------------------------------------------------------------------
    61  # This block of tests checks that it is possible to set the cache_size of a
    62  # database to a small (< 10) value. More specifically:
    63  #
    64  #   cache-2.1.*: Test that "PRAGMA cache_size" appears to work with small 
    65  #                values.
    66  #   cache-2.2.*: Test that "PRAGMA main.cache_size" appears to work with 
    67  #                small values.
    68  #   cache-2.3.*: Test cache_size=1 correctly spills/flushes the cache. 
    69  #   cache-2.4.*: Test cache_size=0 correctly spills/flushes the cache. 
    70  #
    71  #
    72  db_delete_and_reopen
    73  do_execsql_test cache-2.0 {
    74    PRAGMA auto_vacuum=OFF;
    75    PRAGMA journal_mode=DELETE;
    76    CREATE TABLE t1(a, b);
    77    CREATE TABLE t2(c, d);
    78    INSERT INTO t1 VALUES('x', 'y');
    79    INSERT INTO t2 VALUES('i', 'j');
    80  } {delete}
    81  
    82  for {set i 0} {$i < 20} {incr i} {
    83    do_execsql_test cache-2.1.$i.1 "PRAGMA cache_size = $i"
    84    do_execsql_test cache-2.1.$i.2 "PRAGMA cache_size" $i
    85    do_execsql_test cache-2.1.$i.3 "SELECT * FROM t1" {x y}
    86    do_execsql_test cache-2.1.$i.4 "PRAGMA cache_size" $i
    87  }
    88  for {set i 0} {$i < 20} {incr i} {
    89    do_execsql_test cache-2.2.$i.1 "PRAGMA main.cache_size = $i"
    90    do_execsql_test cache-2.2.$i.2 "PRAGMA main.cache_size" $i
    91    do_execsql_test cache-2.2.$i.3 "SELECT * FROM t1" {x y}
    92    do_execsql_test cache-2.2.$i.4 "PRAGMA main.cache_size" $i
    93  }
    94  
    95  # Tests for cache_size = 1.
    96  #
    97  do_execsql_test cache-2.3.1 {
    98    PRAGMA cache_size = 1;
    99    BEGIN;
   100      INSERT INTO t1 VALUES(1, 2);
   101      PRAGMA lock_status;
   102  } {main reserved temp closed}
   103  do_test cache-2.3.2 { pager_cache_size db } 2
   104  do_execsql_test cache-2.3.3 {
   105      INSERT INTO t2 VALUES(1, 2);
   106      PRAGMA lock_status;
   107  } {main exclusive temp closed}
   108  do_test cache-2.3.4 { pager_cache_size db } 2
   109  do_execsql_test cache-2.3.5 COMMIT
   110  do_test cache-2.3.6 { pager_cache_size db } 1
   111  
   112  do_execsql_test cache-2.3.7 {
   113    SELECT * FROM t1 UNION SELECT * FROM t2;
   114  } {1 2 i j x y}
   115  do_test cache-2.3.8 { pager_cache_size db } 1
   116  
   117  # Tests for cache_size = 0.
   118  #
   119  do_execsql_test cache-2.4.1 {
   120    PRAGMA cache_size = 0;
   121    BEGIN;
   122      INSERT INTO t1 VALUES(1, 2);
   123      PRAGMA lock_status;
   124  } {main reserved temp closed}
   125  do_test cache-2.4.2 { pager_cache_size db } 2
   126  do_execsql_test cache-2.4.3 {
   127      INSERT INTO t2 VALUES(1, 2);
   128      PRAGMA lock_status;
   129  } {main exclusive temp closed}
   130  do_test cache-2.4.4 { pager_cache_size db } 2
   131  do_execsql_test cache-2.4.5 COMMIT
   132  
   133  do_test cache-2.4.6 { pager_cache_size db } 0
   134  do_execsql_test cache-2.4.7 {
   135    SELECT * FROM t1 UNION SELECT * FROM t2;
   136  } {1 2 i j x y}
   137  do_test cache-2.4.8 { pager_cache_size db } 0
   138  
   139  sqlite3_soft_heap_limit $cmdlinearg(soft-heap-limit)
   140  finish_test