xref: /sqlite-3.40.0/test/cacheflush.test (revision 6fa255fd) 
1# 2011 November 16
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#
12# This file contains test cases for sqlite3_db_cacheflush API.
13#
14
15set testdir [file dirname $argv0]
16source $testdir/tester.tcl
17set testprefix cacheflush
18test_set_config_pagecache 0 0
19
20# Run the supplied SQL on a copy of the database currently stored on
21# disk in file $dbfile.
22proc diskquery {dbfile sql} {
23  forcecopy $dbfile dq.db
24  sqlite3 dq dq.db
25  set res [execsql $sql dq]
26  dq close
27  set res
28}
29
30# Simplest possible test.
31#
32do_execsql_test 1.1.0 {
33  CREATE TABLE t1(a, b);
34  INSERT INTO t1 VALUES(1, 2);
35  BEGIN;
36    INSERT INTO t1 VALUES(3, 4);
37}
38do_test 1.1.1 {
39  diskquery test.db { SELECT * FROM t1 }
40} {1 2}
41do_test 1.1.2 {
42  sqlite3_db_cacheflush db
43  diskquery test.db { SELECT * FROM t1 }
44} {1 2 3 4}
45
46# Test that multiple pages may be flushed to disk.
47#
48do_execsql_test 1.2.0 {
49  COMMIT;
50  CREATE TABLE t2(a, b);
51  BEGIN;
52    INSERT INTO t1 VALUES(5, 6);
53    INSERT INTO t2 VALUES('a', 'b');
54}
55do_test 1.2.1 {
56  diskquery test.db {
57    SELECT * FROM t1;
58    SELECT * FROM t2;
59  }
60} {1 2 3 4}
61do_test 1.2.2 {
62  sqlite3_db_cacheflush db
63  diskquery test.db {
64    SELECT * FROM t1;
65    SELECT * FROM t2;
66  }
67} {1 2 3 4 5 6 a b}
68
69# Test that pages with nRef!=0 are not flushed to disk.
70#
71do_execsql_test 1.3.0 {
72  COMMIT;
73  CREATE TABLE t3(a, b);
74  BEGIN;
75    INSERT INTO t1 VALUES(7, 8);
76    INSERT INTO t2 VALUES('c', 'd');
77    INSERT INTO t3 VALUES('i', 'ii');
78}
79do_test 1.3.1 {
80  diskquery test.db {
81    SELECT * FROM t1;
82    SELECT * FROM t2;
83    SELECT * FROM t3;
84  }
85} {1 2 3 4 5 6 a b}
86do_test 1.3.2 {
87  db eval { SELECT a FROM t1 } {
88    if {$a==3} {
89      sqlite3_db_cacheflush db
90    }
91  }
92  diskquery test.db {
93    SELECT * FROM t1;
94    SELECT * FROM t2;
95    SELECT * FROM t3;
96  }
97} {1 2 3 4 5 6 a b c d i ii}
98do_test 1.3.2 {
99  sqlite3_db_cacheflush db
100  diskquery test.db {
101    SELECT * FROM t1;
102    SELECT * FROM t2;
103    SELECT * FROM t3;
104  }
105} {1 2 3 4 5 6 7 8 a b c d i ii}
106
107# Check that SQLITE_BUSY is returned if pages cannot be flushed due to
108# conflicting read locks.
109#
110do_execsql_test 1.4.0 {
111  COMMIT;
112  BEGIN;
113    INSERT INTO t1 VALUES(9, 10);
114}
115do_test 1.4.1 {
116  sqlite3 db2 test.db
117  db2 eval {
118    BEGIN;
119      SELECT * FROM t1;
120  }
121  diskquery test.db {
122    SELECT * FROM t1;
123  }
124} {1 2 3 4 5 6 7 8}
125do_test 1.4.2 {
126  list [catch { sqlite3_db_cacheflush db } msg] $msg
127} {1 {database is locked}}
128do_test 1.4.3 {
129  diskquery test.db {
130    SELECT * FROM t1;
131  }
132} {1 2 3 4 5 6 7 8}
133do_test 1.4.4 {
134  db2 close
135  sqlite3_db_cacheflush db
136  diskquery test.db {
137    SELECT * FROM t1;
138  }
139} {1 2 3 4 5 6 7 8 9 10}
140do_execsql_test 1.4.5 { COMMIT }
141
142#-------------------------------------------------------------------------
143# Test that ATTACHed database caches are also flushed.
144#
145forcedelete test.db2
146do_execsql_test 2.1.0 {
147  ATTACH 'test.db2' AS aux;
148  CREATE TABLE aux.t4(x, y);
149  INSERT INTO t4 VALUES('A', 'B');
150  BEGIN;
151    INSERT INTO t1 VALUES(11, 12);
152    INSERT INTO t4 VALUES('C', 'D');
153}
154do_test 2.1.1 {
155  diskquery test.db { SELECT * FROM t1; }
156} {1 2 3 4 5 6 7 8 9 10}
157do_test 2.1.2 {
158  diskquery test.db2 { SELECT * FROM t4; }
159} {A B}
160do_test 2.1.3 {
161  sqlite3_db_cacheflush db
162  diskquery test.db { SELECT * FROM t1; }
163} {1 2 3 4 5 6 7 8 9 10 11 12}
164do_test 2.1.4 {
165  sqlite3_db_cacheflush db
166  diskquery test.db2 { SELECT * FROM t4; }
167} {A B C D}
168do_execsql_test 2.1.5 { COMMIT }
169
170# And that hitting an SQLITE_BUSY when flushing "main" does not stop
171# SQLite from going on to flush "aux".
172#
173do_execsql_test 2.2.0 {
174  BEGIN;
175    INSERT INTO t1 VALUES(13, 14);
176    INSERT INTO t4 VALUES('E', 'F');
177}
178do_test 2.2.1 {
179  diskquery test.db { SELECT * FROM t1; }
180} {1 2 3 4 5 6 7 8 9 10 11 12}
181do_test 2.2.2 {
182  diskquery test.db2 { SELECT * FROM t4; }
183} {A B C D}
184do_test 2.2.3 {
185  sqlite3 db2 test.db
186  execsql {
187    BEGIN;
188      SELECT * FROM t1;
189  } db2
190  list [catch { sqlite3_db_cacheflush db } msg] $msg
191} {1 {database is locked}}
192do_test 2.2.4 {
193  diskquery test.db { SELECT * FROM t1; }
194} {1 2 3 4 5 6 7 8 9 10 11 12}
195do_test 2.2.5 {
196  diskquery test.db2 { SELECT * FROM t4; }
197} {A B C D E F}
198do_test 2.2.6 {
199  db2 close
200  sqlite3_db_cacheflush db
201  diskquery test.db { SELECT * FROM t1; }
202} {1 2 3 4 5 6 7 8 9 10 11 12 13 14}
203do_execsql_test 2.2.7 { COMMIT }
204
205test_restore_config_pagecache
206finish_test
207
208