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