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