gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/cacheflush.test (about) 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 15 set testdir [file dirname $argv0] 16 source $testdir/tester.tcl 17 set testprefix cacheflush 18 test_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. 22 proc 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 # 32 do_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 } 38 do_test 1.1.1 { 39 diskquery test.db { SELECT * FROM t1 } 40 } {1 2} 41 do_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 # 48 do_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 } 55 do_test 1.2.1 { 56 diskquery test.db { 57 SELECT * FROM t1; 58 SELECT * FROM t2; 59 } 60 } {1 2 3 4} 61 do_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 # 71 do_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 } 79 do_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} 86 do_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} 98 do_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 # 110 do_execsql_test 1.4.0 { 111 COMMIT; 112 BEGIN; 113 INSERT INTO t1 VALUES(9, 10); 114 } 115 do_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} 125 do_test 1.4.2 { 126 list [catch { sqlite3_db_cacheflush db } msg] $msg 127 } {1 {database is locked}} 128 do_test 1.4.3 { 129 diskquery test.db { 130 SELECT * FROM t1; 131 } 132 } {1 2 3 4 5 6 7 8} 133 do_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} 140 do_execsql_test 1.4.5 { COMMIT } 141 142 #------------------------------------------------------------------------- 143 # Test that ATTACHed database caches are also flushed. 144 # 145 forcedelete test.db2 146 do_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 } 154 do_test 2.1.1 { 155 diskquery test.db { SELECT * FROM t1; } 156 } {1 2 3 4 5 6 7 8 9 10} 157 do_test 2.1.2 { 158 diskquery test.db2 { SELECT * FROM t4; } 159 } {A B} 160 do_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} 164 do_test 2.1.4 { 165 sqlite3_db_cacheflush db 166 diskquery test.db2 { SELECT * FROM t4; } 167 } {A B C D} 168 do_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 # 173 do_execsql_test 2.2.0 { 174 BEGIN; 175 INSERT INTO t1 VALUES(13, 14); 176 INSERT INTO t4 VALUES('E', 'F'); 177 } 178 do_test 2.2.1 { 179 diskquery test.db { SELECT * FROM t1; } 180 } {1 2 3 4 5 6 7 8 9 10 11 12} 181 do_test 2.2.2 { 182 diskquery test.db2 { SELECT * FROM t4; } 183 } {A B C D} 184 do_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}} 192 do_test 2.2.4 { 193 diskquery test.db { SELECT * FROM t1; } 194 } {1 2 3 4 5 6 7 8 9 10 11 12} 195 do_test 2.2.5 { 196 diskquery test.db2 { SELECT * FROM t4; } 197 } {A B C D E F} 198 do_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} 203 do_execsql_test 2.2.7 { COMMIT } 204 205 #------------------------------------------------------------------------- 206 # Test that nothing terrible happens if sqlite3_db_cacheflush() is 207 # called on an in-memory database. 208 # 209 do_test 3.0 { 210 db close 211 sqlite3 db :memory: 212 db eval { 213 CREATE TABLE t1(x PRIMARY KEY); 214 CREATE TABLE t2(y PRIMARY KEY); 215 BEGIN; 216 INSERT INTO t1 VALUES(randomblob(100)); 217 INSERT INTO t2 VALUES(randomblob(100)); 218 INSERT INTO t1 VALUES(randomblob(100)); 219 INSERT INTO t2 VALUES(randomblob(100)); 220 } 221 sqlite3_db_cacheflush db 222 } {} 223 224 do_execsql_test 3.1 { PRAGMA integrity_check } ok 225 do_execsql_test 3.2 { COMMIT } 226 do_execsql_test 3.3 { PRAGMA integrity_check } ok 227 do_execsql_test 3.4 { 228 SELECT count(*) FROM t1; 229 SELECT count(*) FROM t2; 230 } {2 2} 231 232 #------------------------------------------------------------------------- 233 # Test that calling sqlite3_db_cacheflush() does not interfere with 234 # savepoint transactions. 235 # 236 do_test 4.0 { 237 reset_db 238 execsql { 239 CREATE TABLE ta(a, aa); 240 CREATE TABLE tb(b, bb); 241 INSERT INTO ta VALUES('a', randomblob(500)); 242 INSERT INTO tb VALUES('b', randomblob(500)); 243 BEGIN; 244 UPDATE ta SET a = 'A'; 245 SAVEPOINT one; 246 UPDATE tb SET b = 'B'; 247 } 248 249 sqlite3_db_cacheflush db 250 diskquery test.db { 251 SELECT a FROM ta; 252 SELECT b FROM tb; 253 } 254 } {A B} 255 256 do_test 4.1 { 257 execsql { 258 ROLLBACK TO one; 259 } 260 sqlite3_db_cacheflush db 261 diskquery test.db { 262 SELECT a FROM ta; 263 SELECT b FROM tb; 264 } 265 } {A b} 266 267 do_test 4.2 { 268 execsql { 269 INSERT INTO tb VALUES('c', randomblob(10)); 270 INSERT INTO tb VALUES('d', randomblob(10)); 271 INSERT INTO tb VALUES('e', randomblob(10)); 272 } 273 sqlite3_db_cacheflush db 274 diskquery test.db { 275 SELECT a FROM ta; 276 SELECT b FROM tb; 277 } 278 } {A b c d e} 279 280 do_test 4.3 { 281 execsql { 282 SAVEPOINT two; 283 UPDATE tb SET b = upper(b); 284 } 285 sqlite3_db_cacheflush db 286 diskquery test.db { 287 SELECT a FROM ta; 288 SELECT b FROM tb; 289 } 290 } {A B C D E} 291 292 do_test 4.4 { 293 execsql { 294 ROLLBACK TO two; 295 } 296 sqlite3_db_cacheflush db 297 diskquery test.db { 298 SELECT a FROM ta; 299 SELECT b FROM tb; 300 } 301 } {A b c d e} 302 303 do_test 4.4 { 304 execsql { 305 ROLLBACK TO one; 306 } 307 sqlite3_db_cacheflush db 308 diskquery test.db { 309 SELECT a FROM ta; 310 SELECT b FROM tb; 311 } 312 } {A b} 313 314 do_test 4.5 { 315 execsql { 316 ROLLBACK; 317 SELECT a FROM ta; 318 SELECT b FROM tb; 319 } 320 } {a b} 321 322 test_restore_config_pagecache 323 finish_test