000001  # 2002 March 6
000002  #
000003  # The author disclaims copyright to this source code.  In place of
000004  # a legal notice, here is a blessing:
000005  #
000006  #    May you do good and not evil.
000007  #    May you find forgiveness for yourself and forgive others.
000008  #    May you share freely, never taking more than you give.
000009  #
000010  #***********************************************************************
000011  # This file implements regression tests for SQLite library.
000012  #
000013  # This file implements tests for the PRAGMA command.
000014  #
000015  # $Id: pragma2.test,v 1.4 2007/10/09 08:29:33 danielk1977 Exp $
000016  
000017  set testdir [file dirname $argv0]
000018  source $testdir/tester.tcl
000019  
000020  # Test organization:
000021  #
000022  # pragma2-1.*: Test freelist_count pragma on the main database.
000023  # pragma2-2.*: Test freelist_count pragma on an attached database.
000024  # pragma2-3.*: Test trying to write to the freelist_count is a no-op.
000025  # pragma2-4.*: Tests for PRAGMA cache_spill
000026  #
000027  
000028  ifcapable !pragma||!schema_pragmas {
000029    finish_test
000030    return
000031  }
000032  
000033  test_set_config_pagecache 0 0
000034  
000035  # Delete the preexisting database to avoid the special setup
000036  # that the "all.test" script does.
000037  #
000038  db close
000039  delete_file test.db test.db-journal
000040  delete_file test3.db test3.db-journal
000041  sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
000042  db eval {PRAGMA auto_vacuum=0}
000043  
000044  
000045  # EVIDENCE-OF: R-11211-21323 PRAGMA schema.freelist_count; Return the
000046  # number of unused pages in the database file.
000047  #
000048  do_test pragma2-1.1 {
000049    execsql {
000050      PRAGMA freelist_count;
000051    }
000052  } {0}
000053  do_test pragma2-1.2 {
000054    execsql {
000055      CREATE TABLE abc(a, b, c);
000056      PRAGMA freelist_count;
000057    }
000058  } {0}
000059  do_test pragma2-1.3 {
000060    execsql {
000061      DROP TABLE abc;
000062      PRAGMA freelist_count;
000063    }
000064  } {1}
000065  do_test pragma2-1.4 {
000066    execsql {
000067      PRAGMA main.freelist_count;
000068    }
000069  } {1}
000070  
000071  forcedelete test2.db
000072  forcedelete test2.db-journal
000073  
000074  ifcapable attach {
000075    do_test pragma2-2.1 {
000076      execsql {
000077        ATTACH 'test2.db' AS aux;
000078        PRAGMA aux.auto_vacuum=OFF;
000079        PRAGMA aux.freelist_count;
000080      }
000081    } {0}
000082    do_test pragma2-2.2 {
000083      execsql {
000084        CREATE TABLE aux.abc(a, b, c);
000085        PRAGMA aux.freelist_count;
000086      }
000087    } {0}
000088    do_test pragma2-2.3 {
000089      set ::val [string repeat 0123456789 1000]
000090      execsql {
000091        INSERT INTO aux.abc VALUES(1, 2, $::val);
000092        PRAGMA aux.freelist_count;
000093      }
000094    } {0}
000095    do_test pragma2-2.4 {
000096      expr {[file size test2.db] / 1024}
000097    } {11}
000098    do_test pragma2-2.5 {
000099      execsql {
000100        DELETE FROM aux.abc;
000101        PRAGMA aux.freelist_count;
000102      }
000103    } {9}
000104    
000105    do_test pragma2-3.1 {
000106      execsql {
000107        PRAGMA aux.freelist_count;
000108        PRAGMA main.freelist_count;
000109        PRAGMA freelist_count;
000110      }
000111    } {9 1 1}
000112    do_test pragma2-3.2 {
000113      execsql {
000114        PRAGMA freelist_count = 500;
000115        PRAGMA freelist_count;
000116      }
000117    } {1 1}
000118    do_test pragma2-3.3 {
000119      execsql {
000120        PRAGMA aux.freelist_count = 500;
000121        PRAGMA aux.freelist_count;
000122      }
000123    } {9 9}
000124  }
000125  
000126  # Default setting of PRAGMA cache_spill is always ON
000127  #
000128  # EVIDENCE-OF: R-63549-59887 PRAGMA cache_spill; PRAGMA
000129  # cache_spill=boolean; PRAGMA schema.cache_spill=N;
000130  #
000131  # EVIDENCE-OF: R-23955-02765 Cache_spill is enabled by default
000132  #
000133  db close
000134  delete_file test.db test.db-journal
000135  delete_file test2.db test2.db-journal
000136  sqlite3 db test.db
000137  do_execsql_test pragma2-4.1 {
000138    PRAGMA main.cache_size=2000;
000139    PRAGMA temp.cache_size=2000;
000140    PRAGMA cache_spill;
000141    PRAGMA main.cache_spill;
000142    PRAGMA temp.cache_spill;
000143  } {2000 2000 2000}
000144  do_execsql_test pragma2-4.2 {
000145    PRAGMA cache_spill=OFF;
000146    PRAGMA cache_spill;
000147    PRAGMA main.cache_spill;
000148    PRAGMA temp.cache_spill;
000149  } {0 0 0}
000150  do_execsql_test pragma2-4.3 {
000151    PRAGMA page_size=1024;
000152    PRAGMA cache_size=50;
000153    BEGIN;
000154    CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
000155    INSERT INTO t1 VALUES(1, randomblob(400), 1, randomblob(400));
000156    INSERT INTO t1 SELECT a+1, randomblob(400), a+1, randomblob(400) FROM t1;
000157    INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
000158    INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
000159    INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
000160    INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
000161    INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
000162    INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
000163    COMMIT;
000164    ATTACH 'test2.db' AS aux1;
000165    CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
000166    INSERT INTO t2 SELECT * FROM t1;
000167    DETACH aux1;
000168    PRAGMA cache_spill=ON;
000169  } {}
000170  sqlite3_release_memory
000171  #
000172  # EVIDENCE-OF: R-07634-40532 The cache_spill pragma enables or disables
000173  # the ability of the pager to spill dirty cache pages to the database
000174  # file in the middle of a transaction.
000175  #
000176  do_test pragma2-4.4 {
000177    db eval {
000178      BEGIN;
000179      UPDATE t1 SET c=c+1;
000180      PRAGMA lock_status;
000181    }
000182  } {main exclusive temp unknown}  ;# EXCLUSIVE lock due to cache spill
000183  do_test pragma2-4.5.1 {
000184    db eval {
000185      ROLLBACK;
000186      PRAGMA cache_spill=OFF;
000187      PRAGMA Cache_Spill;
000188      BEGIN;
000189      UPDATE t1 SET c=c+1;
000190      PRAGMA lock_status;
000191    }
000192  } {0 main reserved temp unknown}   ;# No cache spill, so no exclusive lock
000193  
000194  
000195  # EVIDENCE-OF: R-34657-61226 The "PRAGMA cache_spill=N" form of this
000196  # pragma sets a minimum cache size threshold required for spilling to
000197  # occur.
000198  do_test pragma2-4.5.2 {
000199    db eval {
000200      ROLLBACK;
000201      PRAGMA cache_spill=100000;
000202      PRAGMA cache_spill;
000203      BEGIN;
000204      UPDATE t1 SET c=c+1;
000205      PRAGMA lock_status;
000206    }
000207  } {100000 main reserved temp unknown}   ;# Big spill threshold -> no excl lock
000208  ifcapable !memorymanage {
000209    do_test pragma2-4.5.3 {
000210      db eval {
000211        ROLLBACK;
000212        PRAGMA cache_spill=25;
000213        PRAGMA main.cache_spill;
000214        BEGIN;
000215        UPDATE t1 SET c=c+1;
000216        PRAGMA lock_status;
000217      }
000218    } {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock
000219    do_test pragma2-4.5.4 {
000220      db eval {
000221        ROLLBACK;
000222        PRAGMA cache_spill(-25);
000223        PRAGMA main.cache_spill;
000224        BEGIN;
000225        UPDATE t1 SET c=c+1;
000226        PRAGMA lock_status;
000227      }
000228    } {50 main exclusive temp unknown}   ;# Small cache spill -> exclusive lock
000229  }
000230  
000231  
000232  # Verify that newly attached databases inherit the cache_spill=OFF
000233  # setting.
000234  #
000235  do_execsql_test pragma2-4.6 {
000236    ROLLBACK;
000237    PRAGMA cache_spill=OFF;
000238    ATTACH 'test2.db' AS aux1;
000239    PRAGMA aux1.cache_size=50;
000240    BEGIN;
000241    UPDATE t2 SET c=c+1;
000242    PRAGMA lock_status;
000243  } {main unlocked temp unknown aux1 reserved}
000244  do_execsql_test pragma2-4.7 {
000245    COMMIT;
000246  }
000247  sqlite3_release_memory
000248  do_execsql_test pragma2-4.8 {
000249    PRAGMA cache_spill=ON; -- Applies to all databases
000250    BEGIN;
000251    UPDATE t2 SET c=c-1;
000252    PRAGMA lock_status;
000253  } {main unlocked temp unknown aux1 exclusive}
000254  db close
000255  forcedelete test.db
000256  sqlite3 db test.db
000257  
000258  do_execsql_test pragma2-5.1 {
000259    PRAGMA page_size=16384;
000260    CREATE TABLE t1(x);
000261    PRAGMA cache_size=2;
000262    PRAGMA cache_spill=YES;
000263    PRAGMA cache_spill;
000264  } {2}
000265  do_execsql_test pragma2-5.2 {
000266    PRAGMA cache_spill=NO;
000267    PRAGMA cache_spill;
000268  } {0}
000269  do_execsql_test pragma2-5.3 {
000270    PRAGMA cache_spill(-51);
000271    PRAGMA cache_spill;
000272  } {3}
000273     
000274  test_restore_config_pagecache
000275  finish_test