AryaWu/sqlite

1# 2011 November 162#3# The author disclaims copyright to this source code.  In place of4# 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.tcl17set testprefix cacheflush18test_set_config_pagecache 0 019 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.db24  sqlite3 dq dq.db25  set res [execsql $sql dq]26  dq close27  set res28}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 db43  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 db63  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 db90    }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 db100  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 to108# 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.db117  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] $msg127} {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 close135  sqlite3_db_cacheflush db136  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.db2146do_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 db162  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 db166  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 stop171# 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.db186  execsql {187    BEGIN;188      SELECT * FROM t1;189  } db2190  list [catch { sqlite3_db_cacheflush db } msg] $msg191} {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 close200  sqlite3_db_cacheflush db201  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 205#-------------------------------------------------------------------------206# Test that nothing terrible happens if sqlite3_db_cacheflush() is207# called on an in-memory database.208#209do_test 3.0 {210  db close211  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 db222} {}223 224do_execsql_test 3.1 { PRAGMA integrity_check } ok225do_execsql_test 3.2 { COMMIT }226do_execsql_test 3.3 { PRAGMA integrity_check } ok227do_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 with234# savepoint transactions.235#236do_test 4.0 {237  reset_db238  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 db250  diskquery test.db {251    SELECT a FROM ta;252    SELECT b FROM tb;253  }254} {A B}255 256do_test 4.1 {257  execsql { 258    ROLLBACK TO one;259  }260  sqlite3_db_cacheflush db261  diskquery test.db {262    SELECT a FROM ta;263    SELECT b FROM tb;264  }265} {A b}266 267do_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 db274  diskquery test.db {275    SELECT a FROM ta;276    SELECT b FROM tb;277  }278} {A b c d e}279 280do_test 4.3 {281  execsql { 282    SAVEPOINT two;283    UPDATE tb SET b = upper(b);284  }285  sqlite3_db_cacheflush db286  diskquery test.db {287    SELECT a FROM ta;288    SELECT b FROM tb;289  }290} {A B C D E}291 292do_test 4.4 {293  execsql { 294    ROLLBACK TO two;295  }296  sqlite3_db_cacheflush db297  diskquery test.db {298    SELECT a FROM ta;299    SELECT b FROM tb;300  }301} {A b c d e}302 303do_test 4.4 {304  execsql { 305    ROLLBACK TO one;306  }307  sqlite3_db_cacheflush db308  diskquery test.db {309    SELECT a FROM ta;310    SELECT b FROM tb;311  }312} {A b}313 314do_test 4.5 {315  execsql { 316    ROLLBACK;317    SELECT a FROM ta;318    SELECT b FROM tb;319  }320} {a b}321 322test_restore_config_pagecache323finish_test324