AryaWu/sqlite

1# 2002 March 62#3# The author disclaims copyright to this source code.  In place of4# 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# This file implements regression tests for SQLite library.12#13# This file implements tests for the PRAGMA command.14#15# $Id: pragma2.test,v 1.4 2007/10/09 08:29:33 danielk1977 Exp $16 17set testdir [file dirname $argv0]18source $testdir/tester.tcl19 20# Test organization:21#22# pragma2-1.*: Test freelist_count pragma on the main database.23# pragma2-2.*: Test freelist_count pragma on an attached database.24# pragma2-3.*: Test trying to write to the freelist_count is a no-op.25# pragma2-4.*: Tests for PRAGMA cache_spill26#27 28ifcapable !pragma||!schema_pragmas {29  finish_test30  return31}32 33test_set_config_pagecache 0 034 35# Delete the preexisting database to avoid the special setup36# that the "all.test" script does.37#38db close39delete_file test.db test.db-journal40delete_file test3.db test3.db-journal41sqlite3 db test.db; set DB [sqlite3_connection_pointer db]42db eval {PRAGMA auto_vacuum=0}43 44 45# EVIDENCE-OF: R-11211-21323 PRAGMA schema.freelist_count; Return the46# number of unused pages in the database file.47#48do_test pragma2-1.1 {49  execsql {50    PRAGMA freelist_count;51  }52} {0}53do_test pragma2-1.2 {54  execsql {55    CREATE TABLE abc(a, b, c);56    PRAGMA freelist_count;57  }58} {0}59do_test pragma2-1.3 {60  execsql {61    DROP TABLE abc;62    PRAGMA freelist_count;63  }64} {1}65do_test pragma2-1.4 {66  execsql {67    PRAGMA main.freelist_count;68  }69} {1}70 71forcedelete test2.db72forcedelete test2.db-journal73 74ifcapable attach {75  do_test pragma2-2.1 {76    execsql {77      ATTACH 'test2.db' AS aux;78      PRAGMA aux.auto_vacuum=OFF;79      PRAGMA aux.freelist_count;80    }81  } {0}82  do_test pragma2-2.2 {83    execsql {84      CREATE TABLE aux.abc(a, b, c);85      PRAGMA aux.freelist_count;86    }87  } {0}88  do_test pragma2-2.3 {89    set ::val [string repeat 0123456789 1000]90    execsql {91      INSERT INTO aux.abc VALUES(1, 2, $::val);92      PRAGMA aux.freelist_count;93    }94  } {0}95  do_test pragma2-2.4 {96    expr {[file size test2.db] / 1024}97  } {11}98  do_test pragma2-2.5 {99    execsql {100      DELETE FROM aux.abc;101      PRAGMA aux.freelist_count;102    }103  } {9}104  105  do_test pragma2-3.1 {106    execsql {107      PRAGMA aux.freelist_count;108      PRAGMA main.freelist_count;109      PRAGMA freelist_count;110    }111  } {9 1 1}112  do_test pragma2-3.2 {113    execsql {114      PRAGMA freelist_count = 500;115      PRAGMA freelist_count;116    }117  } {1 1}118  do_test pragma2-3.3 {119    execsql {120      PRAGMA aux.freelist_count = 500;121      PRAGMA aux.freelist_count;122    }123  } {9 9}124}125 126# Default setting of PRAGMA cache_spill is always ON127#128# EVIDENCE-OF: R-63549-59887 PRAGMA cache_spill; PRAGMA129# cache_spill=boolean; PRAGMA schema.cache_spill=N;130#131# EVIDENCE-OF: R-23955-02765 Cache_spill is enabled by default132#133db close134delete_file test.db test.db-journal135delete_file test2.db test2.db-journal136sqlite3 db test.db137do_execsql_test pragma2-4.1 {138  PRAGMA main.cache_size=2000;139  PRAGMA temp.cache_size=2000;140  PRAGMA cache_spill;141  PRAGMA main.cache_spill;142  PRAGMA temp.cache_spill;143} {2000 2000 2000}144do_execsql_test pragma2-4.2 {145  PRAGMA cache_spill=OFF;146  PRAGMA cache_spill;147  PRAGMA main.cache_spill;148  PRAGMA temp.cache_spill;149} {0 0 0}150do_execsql_test pragma2-4.3 {151  PRAGMA page_size=1024;152  PRAGMA cache_size=50;153  BEGIN;154  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);155  INSERT INTO t1 VALUES(1, randomblob(400), 1, randomblob(400));156  INSERT INTO t1 SELECT a+1, randomblob(400), a+1, randomblob(400) FROM t1;157  INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;158  INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;159  INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;160  INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;161  INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;162  INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;163  COMMIT;164  ATTACH 'test2.db' AS aux1;165  CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);166  INSERT INTO t2 SELECT * FROM t1;167  DETACH aux1;168  PRAGMA cache_spill=ON;169} {}170sqlite3_release_memory171#172# EVIDENCE-OF: R-07634-40532 The cache_spill pragma enables or disables173# the ability of the pager to spill dirty cache pages to the database174# file in the middle of a transaction.175#176do_test pragma2-4.4 {177  db eval {178    BEGIN;179    UPDATE t1 SET c=c+1;180    PRAGMA lock_status;181  }182} {main exclusive temp unknown}  ;# EXCLUSIVE lock due to cache spill183do_test pragma2-4.5.1 {184  db eval {185    ROLLBACK;186    PRAGMA cache_spill=OFF;187    PRAGMA Cache_Spill;188    BEGIN;189    UPDATE t1 SET c=c+1;190    PRAGMA lock_status;191  }192} {0 main reserved temp unknown}   ;# No cache spill, so no exclusive lock193 194 195# EVIDENCE-OF: R-34657-61226 The "PRAGMA cache_spill=N" form of this196# pragma sets a minimum cache size threshold required for spilling to197# occur.198do_test pragma2-4.5.2 {199  db eval {200    ROLLBACK;201    PRAGMA cache_spill=100000;202    PRAGMA cache_spill;203    BEGIN;204    UPDATE t1 SET c=c+1;205    PRAGMA lock_status;206  }207} {100000 main reserved temp unknown}   ;# Big spill threshold -> no excl lock208ifcapable !memorymanage {209  do_test pragma2-4.5.3 {210    db eval {211      ROLLBACK;212      PRAGMA cache_spill=25;213      PRAGMA main.cache_spill;214      BEGIN;215      UPDATE t1 SET c=c+1;216      PRAGMA lock_status;217    }218  } {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock219  do_test pragma2-4.5.4 {220    db eval {221      ROLLBACK;222      PRAGMA cache_spill(-25);223      PRAGMA main.cache_spill;224      BEGIN;225      UPDATE t1 SET c=c+1;226      PRAGMA lock_status;227    }228  } {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock229}230 231 232# Verify that newly attached databases inherit the cache_spill=OFF233# setting.234#235do_execsql_test pragma2-4.6 {236  ROLLBACK;237  PRAGMA cache_spill=OFF;238  ATTACH 'test2.db' AS aux1;239  PRAGMA aux1.cache_size=50;240  BEGIN;241  UPDATE t2 SET c=c+1;242  PRAGMA lock_status;243} {main unlocked temp unknown aux1 reserved}244do_execsql_test pragma2-4.7 {245  COMMIT;246}247sqlite3_release_memory248do_execsql_test pragma2-4.8 {249  PRAGMA cache_spill=ON; -- Applies to all databases250  BEGIN;251  UPDATE t2 SET c=c-1;252  PRAGMA lock_status;253} {main unlocked temp unknown aux1 exclusive}254db close255forcedelete test.db256sqlite3 db test.db257 258do_execsql_test pragma2-5.1 {259  PRAGMA page_size=16384;260  CREATE TABLE t1(x);261  PRAGMA cache_size=2;262  PRAGMA cache_spill=YES;263  PRAGMA cache_spill;264} {2}265do_execsql_test pragma2-5.2 {266  PRAGMA cache_spill=NO;267  PRAGMA cache_spill;268} {0}269do_execsql_test pragma2-5.3 {270  PRAGMA cache_spill(-51);271  PRAGMA cache_spill;272} {3}273   274test_restore_config_pagecache275finish_test276