xref: /sqlite-3.40.0/test/pragma2.test (revision a23bc8a3) 
1# 2002 March 6
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# 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.tcl
19
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_spill
26#
27
28ifcapable !pragma||!schema_pragmas {
29  finish_test
30  return
31}
32
33test_set_config_pagecache 0 0
34
35# Delete the preexisting database to avoid the special setup
36# that the "all.test" script does.
37#
38db close
39delete_file test.db test.db-journal
40delete_file test3.db test3.db-journal
41sqlite3 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 the
46# 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.db
72forcedelete test2.db-journal
73
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 ON
127#
128# EVIDENCE-OF: R-63549-59887 PRAGMA cache_spill; PRAGMA
129# cache_spill=boolean; PRAGMA schema.cache_spill=N;
130#
131# EVIDENCE-OF: R-23955-02765 Cache_spill is enabled by default
132#
133db close
134delete_file test.db test.db-journal
135delete_file test2.db test2.db-journal
136sqlite3 db test.db
137do_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_memory
171#
172# EVIDENCE-OF: R-07634-40532 The cache_spill pragma enables or disables
173# the ability of the pager to spill dirty cache pages to the database
174# 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 spill
183do_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 lock
193
194
195# EVIDENCE-OF: R-34657-61226 The "PRAGMA cache_spill=N" form of this
196# pragma sets a minimum cache size threshold required for spilling to
197# 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 lock
208ifcapable !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 lock
219  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 lock
229}
230
231
232# Verify that newly attached databases inherit the cache_spill=OFF
233# 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_memory
248do_execsql_test pragma2-4.8 {
249  PRAGMA cache_spill=ON; -- Applies to all databases
250  BEGIN;
251  UPDATE t2 SET c=c-1;
252  PRAGMA lock_status;
253} {main unlocked temp unknown aux1 exclusive}
254db close
255forcedelete test.db
256sqlite3 db test.db
257
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_pagecache
275finish_test
276