xref: /sqlite-3.40.0/test/pragma2.test (revision 962f9669) 
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
33# Delete the preexisting database to avoid the special setup
34# that the "all.test" script does.
35#
36db close
37delete_file test.db test.db-journal
38delete_file test3.db test3.db-journal
39sqlite3 db test.db; set DB [sqlite3_connection_pointer db]
40db eval {PRAGMA auto_vacuum=0}
41
42do_test pragma2-1.1 {
43  execsql {
44    PRAGMA freelist_count;
45  }
46} {0}
47do_test pragma2-1.2 {
48  execsql {
49    CREATE TABLE abc(a, b, c);
50    PRAGMA freelist_count;
51  }
52} {0}
53do_test pragma2-1.3 {
54  execsql {
55    DROP TABLE abc;
56    PRAGMA freelist_count;
57  }
58} {1}
59do_test pragma2-1.4 {
60  execsql {
61    PRAGMA main.freelist_count;
62  }
63} {1}
64
65forcedelete test2.db
66forcedelete test2.db-journal
67
68ifcapable attach {
69  do_test pragma2-2.1 {
70    execsql {
71      ATTACH 'test2.db' AS aux;
72      PRAGMA aux.auto_vacuum=OFF;
73      PRAGMA aux.freelist_count;
74    }
75  } {0}
76  do_test pragma2-2.2 {
77    execsql {
78      CREATE TABLE aux.abc(a, b, c);
79      PRAGMA aux.freelist_count;
80    }
81  } {0}
82  do_test pragma2-2.3 {
83    set ::val [string repeat 0123456789 1000]
84    execsql {
85      INSERT INTO aux.abc VALUES(1, 2, $::val);
86      PRAGMA aux.freelist_count;
87    }
88  } {0}
89  do_test pragma2-2.4 {
90    expr {[file size test2.db] / 1024}
91  } {11}
92  do_test pragma2-2.5 {
93    execsql {
94      DELETE FROM aux.abc;
95      PRAGMA aux.freelist_count;
96    }
97  } {9}
98
99  do_test pragma2-3.1 {
100    execsql {
101      PRAGMA aux.freelist_count;
102      PRAGMA main.freelist_count;
103      PRAGMA freelist_count;
104    }
105  } {9 1 1}
106  do_test pragma2-3.2 {
107    execsql {
108      PRAGMA freelist_count = 500;
109      PRAGMA freelist_count;
110    }
111  } {1 1}
112  do_test pragma2-3.3 {
113    execsql {
114      PRAGMA aux.freelist_count = 500;
115      PRAGMA aux.freelist_count;
116    }
117  } {9 9}
118}
119
120# Default setting of PRAGMA cache_spill is always ON
121#
122# EVIDENCE-OF: R-51036-62828 PRAGMA cache_spill; PRAGMA
123# cache_spill=boolean;
124#
125# EVIDENCE-OF: R-23955-02765 Cache_spill is enabled by default
126#
127db close
128delete_file test.db test.db-journal
129delete_file test2.db test2.db-journal
130sqlite3 db test.db
131do_execsql_test pragma2-4.1 {
132  PRAGMA cache_spill;
133  PRAGMA main.cache_spill;
134  PRAGMA temp.cache_spill;
135} {1 1 1}
136do_execsql_test pragma2-4.2 {
137  PRAGMA cache_spill=OFF;
138  PRAGMA cache_spill;
139  PRAGMA main.cache_spill;
140  PRAGMA temp.cache_spill;
141} {0 0 0}
142do_execsql_test pragma2-4.3 {
143  PRAGMA page_size=1024;
144  PRAGMA cache_size=50;
145  BEGIN;
146  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
147  INSERT INTO t1 VALUES(1, randomblob(400), 1, randomblob(400));
148  INSERT INTO t1 SELECT a+1, randomblob(400), a+1, randomblob(400) FROM t1;
149  INSERT INTO t1 SELECT a+2, randomblob(400), a+2, randomblob(400) FROM t1;
150  INSERT INTO t1 SELECT a+4, randomblob(400), a+4, randomblob(400) FROM t1;
151  INSERT INTO t1 SELECT a+8, randomblob(400), a+8, randomblob(400) FROM t1;
152  INSERT INTO t1 SELECT a+16, randomblob(400), a+16, randomblob(400) FROM t1;
153  INSERT INTO t1 SELECT a+32, randomblob(400), a+32, randomblob(400) FROM t1;
154  INSERT INTO t1 SELECT a+64, randomblob(400), a+64, randomblob(400) FROM t1;
155  COMMIT;
156  ATTACH 'test2.db' AS aux1;
157  CREATE TABLE aux1.t2(a INTEGER PRIMARY KEY, b, c, d);
158  INSERT INTO t2 SELECT * FROM t1;
159  DETACH aux1;
160  PRAGMA cache_spill=ON;
161} {}
162sqlite3_release_memory
163#
164# EVIDENCE-OF: R-07634-40532 The cache_spill pragma enables or disables
165# the ability of the pager to spill dirty cache pages to the database
166# file in the middle of a transaction.
167#
168do_test pragma2-4.4 {
169  db eval {
170    BEGIN;
171    UPDATE t1 SET c=c+1;
172    PRAGMA lock_status;
173  }
174} {main exclusive temp unknown}  ;# EXCLUSIVE lock due to cache spill
175do_test pragma2-4.5 {
176  db eval {
177    COMMIT;
178    PRAGMA cache_spill=OFF;
179    BEGIN;
180    UPDATE t1 SET c=c-1;
181    PRAGMA lock_status;
182  }
183} {main reserved temp unknown}   ;# No cache spill, so no exclusive lock
184
185# Verify that newly attached databases inherit the cache_spill=OFF
186# setting.
187#
188do_execsql_test pragma2-4.6 {
189  COMMIT;
190  ATTACH 'test2.db' AS aux1;
191  PRAGMA aux1.cache_size=50;
192  BEGIN;
193  UPDATE t2 SET c=c+1;
194  PRAGMA lock_status;
195} {main unlocked temp unknown aux1 reserved}
196do_execsql_test pragma2-4.7 {
197  COMMIT;
198}
199sqlite3_release_memory
200do_execsql_test pragma2-4.8 {
201  PRAGMA cache_spill=ON; -- Applies to all databases
202  BEGIN;
203  UPDATE t2 SET c=c-1;
204  PRAGMA lock_status;
205} {main unlocked temp unknown aux1 exclusive}
206
207
208finish_test
209