AryaWu/sqlite

1# 2016 November 182#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# This file implements regression tests for SQLite library. The focus12# of this file is the sqlite3_snapshot_xxx() APIs.13#14 15set testdir [file dirname $argv0]16source $testdir/tester.tcl17ifcapable !snapshot {finish_test; return}18set testprefix snapshot219 20# This test does not work with the inmemory_journal permutation. The reason21# is that each connection opened as part of this permutation executes22# "PRAGMA journal_mode=memory", which fails if the database is in wal mode23# and there are one or more existing connections.24if {[permutation]=="inmemory_journal"} {25  finish_test26  return27}28 29#-------------------------------------------------------------------------30# Check that it is not possible to obtain a snapshot immediately after31# a wal mode database with an empty wal file is opened. But it is after32# the file has been written, even by some other connection.33#34do_execsql_test 1.0 {35  PRAGMA journal_mode = wal;36  CREATE TABLE t1(a, b, c);37  INSERT INTO t1 VALUES(1, 2, 3);38  INSERT INTO t1 VALUES(4, 5, 6);39} {wal}40 41db close42do_test 1.1.1 { list [file exists test.db] [file exists test.db-wal] } {1 0}43 44sqlite3 db test.db45do_execsql_test 1.1.2 { SELECT * FROM t1 } {1 2 3 4 5 6}46 47do_test 1.1.3 {48  execsql BEGIN49  list [catch { sqlite3_snapshot_get_blob db main } msg] $msg50} {1 SQLITE_ERROR}51execsql COMMIT52 53do_test 1.1.4 {54  execsql { INSERT INTO t1 VALUES(7, 8, 9) }55  execsql BEGIN56  string length [sqlite3_snapshot_get_blob db main]57} 4858execsql COMMIT59 60db close61do_test 1.2.1 { list [file exists test.db] [file exists test.db-wal] } {1 0}62 63sqlite3 db test.db64do_execsql_test 1.2.2 { SELECT * FROM t1 } {1 2 3 4 5 6 7 8 9}65 66do_test 1.2.3 {67  execsql BEGIN68  list [catch { sqlite3_snapshot_get_blob db main } msg] $msg69} {1 SQLITE_ERROR}70execsql COMMIT71 72do_test 1.2.4 {73  sqlite3 db2 test.db74  execsql { INSERT INTO t1 VALUES(10, 11, 12) } db275  execsql BEGIN76  string length [sqlite3_snapshot_get_blob db main]77} 4878execsql COMMIT79db2 close80 81#-------------------------------------------------------------------------82# Simple tests for sqlite3_snapshot_recover().83#84reset_db85do_execsql_test 2.0 {86  CREATE TABLE t1(x);87  PRAGMA journal_mode = wal;88  INSERT INTO t1 VALUES(1);89  INSERT INTO t1 VALUES(2);90} {wal}91 92do_test 2.1 {93  db trans { set snap [sqlite3_snapshot_get_blob db main] }94  sqlite3_db_config db NO_CKPT_ON_CLOSE 195  db close96  sqlite3 db test.db97 98  execsql {SELECT * FROM sqlite_master}99  execsql BEGIN100  sqlite3_snapshot_open_blob db main $snap101  execsql COMMIT;102  execsql { INSERT INTO t1 VALUES(3); }103} {}104 105do_test 2.2 {106  sqlite3_db_config db NO_CKPT_ON_CLOSE 1107  db close108  sqlite3 db test.db109 110  execsql {SELECT * FROM sqlite_master}111  execsql BEGIN112  list [catch { sqlite3_snapshot_open_blob db main $snap } msg] $msg113} {1 SQLITE_ERROR_SNAPSHOT}114 115do_test 2.3 {116  execsql COMMIT117  sqlite3_snapshot_recover db main118  execsql BEGIN119  sqlite3_snapshot_open_blob db main $snap120  execsql { SELECT * FROM t1 }121} {1 2}122 123do_test 2.4 {124  execsql COMMIT125  execsql { SELECT * FROM t1 }126} {1 2 3}127 128do_test 2.5 {129  execsql { PRAGMA wal_checkpoint }130  sqlite3_db_config db NO_CKPT_ON_CLOSE 1131  db close132  sqlite3 db test.db133 134  sqlite3_snapshot_recover db main135  execsql BEGIN136  list [catch { sqlite3_snapshot_open_blob db main $snap } msg] $msg137} {1 SQLITE_ERROR_SNAPSHOT}138 139#-------------------------------------------------------------------------140# Check that calling sqlite3_snapshot_recover() does not confuse the141# pager cache.142reset_db143do_execsql_test 3.0 {144  PRAGMA journal_mode = wal;145  CREATE TABLE t1(x, y);146  INSERT INTO t1 VALUES('a', 'b');147  INSERT INTO t1 VALUES('c', 'd');148} {wal}149do_test 3.1 {150  sqlite3 db2 test.db151  execsql { INSERT INTO t1 VALUES('e', 'f') } db2152  db2 close153  sqlite3_snapshot_recover db main154} {}155do_execsql_test 3.2 {156  SELECT * FROM t1;157} {a b c d e f}158 159#-------------------------------------------------------------------------160# Check that sqlite3_snapshot_recover() returns an error if it is called161# with an open read-transaction. Or on a database that does not exist. Or162# on the temp database. Or on a db that is not in wal mode.163#164do_test 4.1 {165  sqlite3_snapshot_recover db main166} {}167do_test 4.2 {168  execsql {169    BEGIN;170      SELECT * FROM sqlite_master;171  }172  list [catch { sqlite3_snapshot_recover db main } msg] $msg173} {1 SQLITE_ERROR}174do_test 4.3 {175  execsql COMMIT176  sqlite3_snapshot_recover db main177} {}178do_test 4.4 {179  list [catch { sqlite3_snapshot_recover db aux } msg] $msg180} {1 SQLITE_ERROR}181do_test 4.5 {182  forcedelete test.db2183  execsql {184    ATTACH 'test.db2' AS aux;185    PRAGMA aux.journal_mode = wal;186    CREATE TABLE aux.t2(x, y);187  }188  list [catch { sqlite3_snapshot_recover db aux } msg] $msg189} {0 {}}190do_test 4.6 {191  list [catch { sqlite3_snapshot_recover db temp } msg] $msg192} {1 SQLITE_ERROR}193do_test 4.7 {194  execsql {195    PRAGMA aux.journal_mode = delete;196  }197  list [catch { sqlite3_snapshot_recover db aux } msg] $msg198} {1 SQLITE_ERROR}199 200#-------------------------------------------------------------------------201reset_db202sqlite3 db2 test.db 203do_execsql_test 5.0 {204  CREATE TABLE t2(x);205  PRAGMA journal_mode = wal;206  INSERT INTO t2 VALUES('abc');207  INSERT INTO t2 VALUES('def');208  INSERT INTO t2 VALUES('ghi');209} {wal}210 211do_test 5.1 {212  execsql { 213    SELECT * FROM t2;214    BEGIN;215  } db2216  set snap [sqlite3_snapshot_get_blob db2 main]217  db2 eval END218} {}219 220do_test 5.2 {221  execsql BEGIN db2222  sqlite3_snapshot_open_blob db2 main $snap223  db2 eval { SELECT * FROM t2 ; END }224} {abc def ghi}225 226do_test 5.3 {227  execsql { PRAGMA wal_checkpoint = RESTART }228  execsql BEGIN db2229  sqlite3_snapshot_open_blob db2 main $snap230  db2 eval { SELECT * FROM t2 ; END }231} {abc def ghi}232 233do_test 5.4 {234  execsql { INSERT INTO t2 VALUES('jkl') } 235  execsql BEGIN db2236  list [catch { sqlite3_snapshot_open_blob db2 main $snap } msg] $msg237} {1 SQLITE_ERROR_SNAPSHOT}238 239 240finish_test241