Debian Codegraph @

test/pragma3.test

raw
1 # 2014-12-19
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 # This file implements tests for PRAGMA data_version command.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18
19 if {[sqlite3 -has-codec]} {
20 finish_test
21 return
22 }
23
24 do_execsql_test pragma3-100 {
25 PRAGMA data_version;
26 } {1}
27 do_execsql_test pragma3-101 {
28 PRAGMA temp.data_version;
29 } {1}
30
31 # Writing to the pragma is a no-op
32 do_execsql_test pragma3-102 {
33 PRAGMA main.data_version=1234;
34 PRAGMA main.data_version;
35 } {1 1}
36
37 # EVIDENCE-OF: R-27726-60934 The "PRAGMA data_version" command provides
38 # an indication that the database file has been modified.
39 #
40 # EVIDENCE-OF: R-47505-58569 The "PRAGMA data_version" value is
41 # unchanged for commits made on the same database connection.
42 #
43 do_execsql_test pragma3-110 {
44 PRAGMA data_version;
45 BEGIN IMMEDIATE;
46 PRAGMA data_version;
47 CREATE TABLE t1(a);
48 INSERT INTO t1 VALUES(100),(200),(300);
49 PRAGMA data_version;
50 COMMIT;
51 SELECT * FROM t1;
52 PRAGMA data_version;
53 } {1 1 1 100 200 300 1}
54
55 sqlite3 db2 test.db
56 do_test pragma3-120 {
57 db2 eval {
58 SELECT * FROM t1;
59 PRAGMA data_version;
60 }
61 } {100 200 300 1}
62
63 do_execsql_test pragma3-130 {
64 PRAGMA data_version;
65 BEGIN IMMEDIATE;
66 PRAGMA data_version;
67 INSERT INTO t1 VALUES(400),(500);
68 PRAGMA data_version;
69 COMMIT;
70 SELECT * FROM t1;
71 PRAGMA data_version;
72 PRAGMA shrink_memory;
73 } {1 1 1 100 200 300 400 500 1}
74
75 # EVIDENCE-OF: R-63005-41812 The integer values returned by two
76 # invocations of "PRAGMA data_version" from the same connection will be
77 # different if changes were committed to the database by any other
78 # connection in the interim.
79 #
80 # Value went from 1 in pragma3-120 to 2 here.
81 #
82 do_test pragma3-140 {
83 db2 eval {
84 SELECT * FROM t1;
85 PRAGMA data_version;
86 BEGIN IMMEDIATE;
87 PRAGMA data_version;
88 UPDATE t1 SET a=a+1;
89 COMMIT;
90 SELECT * FROM t1;
91 PRAGMA data_version;
92 }
93 } {100 200 300 400 500 2 2 101 201 301 401 501 2}
94 do_execsql_test pragma3-150 {
95 SELECT * FROM t1;
96 PRAGMA data_version;
97 } {101 201 301 401 501 2}
98
99 #
100 do_test pragma3-160 {
101 db eval {
102 BEGIN;
103 PRAGMA data_version;
104 UPDATE t1 SET a=555 WHERE a=501;
105 PRAGMA data_version;
106 SELECT * FROM t1 ORDER BY a;
107 PRAGMA data_version;
108 }
109 } {2 2 101 201 301 401 555 2}
110 do_test pragma3-170 {
111 db2 eval {
112 PRAGMA data_version;
113 }
114 } {2}
115 do_test pragma3-180 {
116 db eval {
117 COMMIT;
118 PRAGMA data_version;
119 }
120 } {2}
121 do_test pragma3-190 {
122 db2 eval {
123 PRAGMA data_version;
124 }
125 } {3}
126
127 # EVIDENCE-OF: R-19326-44825 The "PRAGMA data_version" value is a local
128 # property of each database connection and so values returned by two
129 # concurrent invocations of "PRAGMA data_version" on separate database
130 # connections are often different even though the underlying database is
131 # identical.
132 #
133 do_test pragma3-195 {
134 expr {[db eval {PRAGMA data_version}]!=[db2 eval {PRAGMA data_version}]}
135 } {1}
136
137 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
138 # the same for all database connections, including database connections
139 # in separate processes and shared cache database connections.
140 #
141 # The next block checks the behavior for separate processes.
142 #
143 do_test pragma3-200 {
144 db eval {PRAGMA data_version; SELECT * FROM t1;}
145 } {2 101 201 301 401 555}
146 do_test pragma3-201 {
147 set fd [open pragma3.txt wb]
148 puts $fd {
149 sqlite3 db test.db;
150 db eval {DELETE FROM t1 WHERE a>300};
151 db close;
152 exit;
153 }
154 close $fd
155 exec [info nameofexec] pragma3.txt
156 forcedelete pragma3.txt
157 db eval {
158 PRAGMA data_version;
159 SELECT * FROM t1;
160 }
161 } {3 101 201}
162 db2 close
163 db close
164
165 # EVIDENCE-OF: R-54562-06892 The behavior of "PRAGMA data_version" is
166 # the same for all database connections, including database connections
167 # in separate processes and shared cache database connections.
168 #
169 # The next block checks that behavior is the same for shared-cache.
170 #
171 ifcapable shared_cache {
172 set ::enable_shared_cache [sqlite3_enable_shared_cache 1]
173 sqlite3 db test.db
174 sqlite3 db2 test.db
175 do_test pragma3-300 {
176 db eval {
177 PRAGMA data_version;
178 BEGIN;
179 CREATE TABLE t3(a,b,c);
180 CREATE TABLE t4(x,y,z);
181 INSERT INTO t4 VALUES(123,456,789);
182 PRAGMA data_version;
183 COMMIT;
184 PRAGMA data_version;
185 }
186 } {1 1 1}
187 do_test pragma3-310 {
188 db2 eval {
189 PRAGMA data_version;
190 BEGIN;
191 INSERT INTO t3(a,b,c) VALUES('abc','def','ghi');
192 SELECT * FROM t3;
193 PRAGMA data_version;
194 }
195 } {2 abc def ghi 2}
196 # The transaction in db2 has not yet committed, so the data_version in
197 # db is unchanged.
198 do_test pragma3-320 {
199 db eval {
200 PRAGMA data_version;
201 SELECT * FROM t4;
202 }
203 } {1 123 456 789}
204 do_test pragma3-330 {
205 db2 eval {
206 COMMIT;
207 PRAGMA data_version;
208 SELECT * FROM t4;
209 }
210 } {2 123 456 789}
211 do_test pragma3-340 {
212 db eval {
213 PRAGMA data_version;
214 SELECT * FROM t3;
215 SELECT * FROM t4;
216 }
217 } {2 abc def ghi 123 456 789}
218 db2 close
219 db close
220 sqlite3_enable_shared_cache $::enable_shared_cache
221 }
222
223 # Make sure this also works in WAL mode
224 #
225 # This will not work with the in-memory journal permutation, as opening
226 # [db2] switches the journal mode back to "memory"
227 #
228 if {[wal_is_capable]} {
229 if {[permutation]!="inmemory_journal"} {
230
231 sqlite3 db test.db
232 db eval {PRAGMA journal_mode=WAL}
233 sqlite3 db2 test.db
234 do_test pragma3-400 {
235 db eval {
236 PRAGMA data_version;
237 PRAGMA journal_mode;
238 SELECT * FROM t1;
239 }
240 } {2 wal 101 201}
241 do_test pragma3-410 {
242 db2 eval {
243 PRAGMA data_version;
244 PRAGMA journal_mode;
245 SELECT * FROM t1;
246 }
247 } {2 wal 101 201}
248 do_test pragma3-420 {
249 db eval {UPDATE t1 SET a=111*(a/100); PRAGMA data_version; SELECT * FROM t1}
250 } {2 111 222}
251 do_test pragma3-430 {
252 db2 eval {PRAGMA data_version; SELECT * FROM t1;}
253 } {3 111 222}
254 db2 close
255 }
256 }
257
258 #-------------------------------------------------------------------------
259 # Check that empty write transactions do not cause the return of "PRAGMA
260 # data_version" to be decremented with journal_mode=PERSIST and
261 # locking_mode=EXCLUSIVE
262 #
263 foreach {tn sql} {
264 A {
265 }
266 B {
267 PRAGMA journal_mode = PERSIST;
268 PRAGMA locking_mode = EXCLUSIVE;
269 }
270 } {
271 reset_db
272 execsql $sql
273
274 do_execsql_test pragma3-510$tn {
275 CREATE TABLE t1(x, y);
276 INSERT INTO t1 VALUES(1, 2);
277 PRAGMA data_version;
278 } {1}
279
280 do_execsql_test pragma3-520$tn {
281 BEGIN EXCLUSIVE;
282 COMMIT;
283 PRAGMA data_version;
284 } {1}
285 }
286
287 finish_test