1# 2017 March 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# This file implements regression tests for SQLite library. 12# 13# Specificly, it tests that "PRAGMA synchronous" appears to work. 14# 15 16set testdir [file dirname $argv0] 17source $testdir/tester.tcl 18set testprefix sync2 19 20# 21# These tests are only applicable when pager pragma are 22# enabled. Also, since every test uses an ATTACHed database, they 23# are only run when ATTACH is enabled. 24# 25ifcapable !pager_pragmas||!attach { 26 finish_test 27 return 28} 29if {$::tcl_platform(platform)!="unix"} { 30 finish_test 31 return 32} 33 34proc execsql_sync {sql} { 35 set s $::sqlite_sync_count 36 set res [execsql $sql] 37 concat [expr $::sqlite_sync_count-$s] $res 38} 39 40proc do_execsql_sync_test {tn sql res} { 41 uplevel [list do_test $tn [list execsql_sync $sql] [list {*}$res]] 42} 43 44#----------------------------------------------------------------------- 45# Tests for journal mode. 46# 47sqlite3 db test.db 48do_execsql_test 1.0 { 49 CREATE TABLE t1(a, b); 50 INSERT INTO t1 VALUES(1, 2); 51} 52 53do_execsql_sync_test 1.1 { INSERT INTO t1 VALUES(3, 4) } 4 54 55# synchronous=normal. So, 1 sync on the directory, 1 on the journal, 1 56# on the db file. 3 in total. 57do_execsql_test 1.2.1 { PRAGMA main.synchronous = NORMAL } 58do_execsql_test 1.2.2 { PRAGMA main.synchronous } 1 59do_execsql_sync_test 1.2.3 { INSERT INTO t1 VALUES(5, 6) } 3 60 61# synchronous=off. No syncs. 62do_execsql_test 1.3.1 { PRAGMA main.synchronous = OFF } 63do_execsql_test 1.3.2 { PRAGMA main.synchronous } 0 64do_execsql_sync_test 1.3.3 { INSERT INTO t1 VALUES(7, 8) } 0 65 66# synchronous=full, journal_mode=delete. So, 1 sync on the directory, 67# 2 on the journal, 1 on the db file. 4 in total. 68do_execsql_test 1.4.1 { PRAGMA main.synchronous = FULL } 69do_execsql_test 1.4.2 { PRAGMA main.synchronous } 2 70do_execsql_sync_test 1.4.3 { INSERT INTO t1 VALUES(9, 10) } 4 71 72#----------------------------------------------------------------------- 73# Tests for wal mode. 74# 75do_execsql_test 1.5 { PRAGMA journal_mode = wal } {wal} 76 77# sync=full, journal_mode=wal. One sync on the directory, two on the 78# wal file. 79do_execsql_sync_test 1.6 { INSERT INTO t1 VALUES(11, 12) } 3 80 81# One sync on the wal file. 82do_execsql_sync_test 1.7 { INSERT INTO t1 VALUES(13, 14) } 1 83 84# No syncs. 85do_execsql_test 1.8.1 { PRAGMA main.synchronous = NORMAL } 86do_execsql_test 1.8.2 { PRAGMA main.synchronous } 1 87do_execsql_sync_test 1.8.3 { INSERT INTO t1 VALUES(15, 16) } 0 88 89# One sync on wal file, one on the db file. 90do_execsql_sync_test 1.9 { PRAGMA wal_checkpoint } {2 0 3 3} 91 92# No syncs. 93do_execsql_test 1.10.1 { PRAGMA main.synchronous = OFF } 94do_execsql_test 1.10.2 { PRAGMA main.synchronous } 0 95do_execsql_sync_test 1.10.3 { INSERT INTO t1 VALUES(17, 18) } 0 96 97#----------------------------------------------------------------------- 98# Tests for the compile time settings SQLITE_DEFAULT_SYNCHRONOUS and 99# SQLITE_DEFAULT_WAL_SYNCHRONOUS. These tests only run if the former 100# is set to "2" and the latter to "1". This is not the default, but 101# it is currently the recommended configuration. 102# 103# https://sqlite.org/compile.html#recommended_compile_time_options 104# 105if {$SQLITE_DEFAULT_SYNCHRONOUS==2 && $SQLITE_DEFAULT_WAL_SYNCHRONOUS==1} { 106 107 db close 108 sqlite3 db test.db 109 110 # Wal mode, sync=normal. The first transaction does one sync on directory, 111 # one on the wal file. The second does no syncs. 112 do_execsql_sync_test 1.11.1 { INSERT INTO t1 VALUES(19, 20) } 2 113 do_execsql_sync_test 1.11.2 { INSERT INTO t1 VALUES(21, 22) } 0 114 do_execsql_test 1.11.3 { PRAGMA main.synchronous } 1 115 116 # One sync on wal file, one on the db file. 117 do_execsql_sync_test 1.12 { PRAGMA wal_checkpoint } {2 0 2 2} 118 119 # First transaction syncs the wal file once, the second not at all. 120 # one on the wal file. The second does no syncs. 121 do_execsql_sync_test 1.13.1 { INSERT INTO t1 VALUES(22, 23) } 1 122 do_execsql_sync_test 1.13.2 { INSERT INTO t1 VALUES(24, 25) } 0 123 124 do_execsql_test 1.14 { PRAGMA journal_mode = delete } {delete} 125 126 # Delete mode, sync=full. The first transaction does one sync on 127 # directory, two on the journal file, one on the db. The second does 128 # the same. 129 do_execsql_sync_test 1.15.1 { INSERT INTO t1 VALUES(26, 27) } 4 130 do_execsql_sync_test 1.15.2 { INSERT INTO t1 VALUES(28, 29) } 4 131 do_execsql_test 1.15.3 { PRAGMA main.synchronous } 2 132 133 # Switch back to wal mode. 134 do_execsql_test 1.16 { PRAGMA journal_mode = wal } {wal} 135 136 do_execsql_sync_test 1.17.1 { INSERT INTO t1 VALUES(30, 31) } 2 137 do_execsql_sync_test 1.17.2 { INSERT INTO t1 VALUES(32, 33) } 0 138 do_execsql_test 1.17.3 { PRAGMA main.synchronous } 1 139 140 # Now set synchronous=off, then switch back to delete mode. Check 141 # that the db handle is still using synchronous=off. 142 do_execsql_test 1.18.3 { PRAGMA main.synchronous=off } 143 do_execsql_test 1.18 { PRAGMA journal_mode = delete } {delete} 144 145 do_execsql_sync_test 1.19.1 { INSERT INTO t1 VALUES(34, 35) } 0 146 do_execsql_sync_test 1.19.2 { INSERT INTO t1 VALUES(36, 37) } 0 147 do_execsql_test 1.19.3 { PRAGMA main.synchronous } 0 148 149 # Close and reopen the db. Back to synchronous=normal. 150 db close 151 sqlite3 db test.db 152 do_execsql_sync_test 1.20.1 { INSERT INTO t1 VALUES(38, 39) } 4 153 do_execsql_sync_test 1.20.2 { INSERT INTO t1 VALUES(40, 41) } 4 154 do_execsql_test 1.20.3 { PRAGMA main.synchronous } 2 155} 156 157finish_test 158