Debian Codegraph @

ext/session/sessionnoop2.test

raw
1 # 2011 March 07
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
14 if {![info exists testdir]} {
15 set testdir [file join [file dirname [info script]] .. .. test]
16 }
17 source [file join [file dirname [info script]] session_common.tcl]
18 source $testdir/tester.tcl
19 ifcapable !session {finish_test; return}
20
21 set testprefix sessionnoop2
22
23 foreach {tn wo} {
24 1 ""
25 2 " WITHOUT ROWID "
26 } {
27 reset_db
28 eval [string map [list %WO% $wo] {
29 do_execsql_test $tn.1.0 {
30 CREATE TABLE t1(a PRIMARY KEY, b, c) %WO%;
31 INSERT INTO t1 VALUES('a', 'A', 'AAA');
32 INSERT INTO t1 VALUES('b', 'B', 'BBB');
33 INSERT INTO t1 VALUES('c', 'C', 'CCC');
34 INSERT INTO t1 VALUES('d', 'D', 'DDD');
35 INSERT INTO t1 VALUES('e', 'E', 'EEE');
36 }
37
38 forcedelete test.db2
39 sqlite3 db2 test.db2
40
41 do_execsql_test -db db2 $tn.1.1 {
42 CREATE TABLE t1(a PRIMARY KEY, b, c) %WO%;
43 INSERT INTO t1 VALUES('a', 'A', 'AAA');
44 INSERT INTO t1 VALUES('b', 'B', '123');
45 INSERT INTO t1 VALUES('c', 'C', 'CCC');
46 INSERT INTO t1 VALUES('e', 'E', 'EEE');
47 INSERT INTO t1 VALUES('f', 'F', 'FFF');
48 }
49
50 set C [changeset_from_sql {
51 UPDATE t1 SET c='123' WHERE a='b';
52 DELETE FROM t1 WHERE a='d';
53 INSERT INTO t1 VALUES('f', 'F', 'FFF');
54 }]
55
56
57 set ::conflict_list [list]
58 proc xConflict {args} {
59 lappend ::conflict_list $args
60 return "OMIT"
61 }
62 do_test $tn.1.2 {
63 sqlite3changeset_apply_v2 db2 $C xConflict
64 set ::conflict_list
65 } [list {*}{
66 {UPDATE t1 DATA {t b {} {} t BBB} {{} {} {} {} t 123} {t b t B t 123}}
67 {INSERT t1 CONFLICT {t f t F t FFF} {t f t F t FFF}}
68 {DELETE t1 NOTFOUND {t d t D t DDD}}
69 }]
70 do_test $tn.1.3 {
71 set ::conflict_list [list]
72 sqlite3changeset_apply_v2 db2 $C xConflict
73 set ::conflict_list
74 } [list {*}{
75 {UPDATE t1 DATA {t b {} {} t BBB} {{} {} {} {} t 123} {t b t B t 123}}
76 {INSERT t1 CONFLICT {t f t F t FFF} {t f t F t FFF}}
77 {DELETE t1 NOTFOUND {t d t D t DDD}}
78 }]
79
80 do_test $tn.1.4 {
81 set ::conflict_list [list]
82 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
83 set ::conflict_list
84 } {}
85
86 do_execsql_test -db db2 1.5 {
87 UPDATE t1 SET b='G' WHERE a='f';
88 UPDATE t1 SET c='456' WHERE a='b';
89 }
90
91 do_test $tn.1.6 {
92 set ::conflict_list [list]
93 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
94 set ::conflict_list
95 } [list {*}{
96 {UPDATE t1 DATA {t b {} {} t BBB} {{} {} {} {} t 123} {t b t B t 456}}
97 {INSERT t1 CONFLICT {t f t F t FFF} {t f t G t FFF}}
98 }]
99
100 db2 close
101
102 #--------------------------------------------------------------------------
103
104 reset_db
105 forcedelete test.db2
106 sqlite3 db2 test.db2
107 do_execsql_test $tn.2.0 {
108 CREATE TABLE t1(a PRIMARY KEY, b) %WO%;
109 }
110 do_execsql_test -db db2 $tn.2.1 {
111 CREATE TABLE t1(a PRIMARY KEY, b, c DEFAULT 'val') %WO%;
112 }
113
114 do_test $tn.2.2 {
115 do_then_apply_sql -ignorenoop {
116 INSERT INTO t1 VALUES(1, 2);
117 }
118 do_then_apply_sql -ignorenoop {
119 UPDATE t1 SET b=2 WHERE a=1
120 }
121 } {}
122
123 db2 close
124
125 }]
126 }
127
128
129 #-------------------------------------------------------------------------
130 reset_db
131 forcedelete test.db2
132 do_execsql_test 3.0 {
133 CREATE TABLE xyz(a, b, c, PRIMARY KEY(a, b), UNIQUE(c));
134 ANALYZE;
135 WITH s(i) AS (
136 VALUES(1) UNION ALL SELECT i+1 FROM s WHERE i<100
137 )
138 INSERT INTO xyz SELECT i, i, i FROM s;
139 VACUUM INTO 'test.db2';
140 }
141
142 set C [changeset_from_sql { ANALYZE }]
143 sqlite3 db2 test.db2
144
145 set ::conflict_list [list]
146 proc xConflict {args} { lappend ::conflict_list $args ; return "OMIT" }
147 do_test 3.1 {
148 sqlite3changeset_apply_v2 db2 $C xConflict
149 set ::conflict_list
150 } {}
151
152 do_test 3.2 {
153 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
154 set ::conflict_list
155 } {}
156
157 do_test 3.3 {
158 sqlite3changeset_apply_v2 db2 $C xConflict
159 set ::conflict_list
160 } [list {*}{
161 {INSERT sqlite_stat1 CONFLICT {t xyz t sqlite_autoindex_xyz_1 t {100 1 1}} {t xyz t sqlite_autoindex_xyz_1 t {100 1 1}}}
162 {INSERT sqlite_stat1 CONFLICT {t xyz t sqlite_autoindex_xyz_2 t {100 1}} {t xyz t sqlite_autoindex_xyz_2 t {100 1}}}
163 }]
164
165 do_execsql_test -db db2 3.4 {
166 UPDATE sqlite_stat1 SET stat='200 1 1' WHERE idx='sqlite_autoindex_xyz_1';
167 }
168
169 do_test 3.5 {
170 set ::conflict_list [list]
171 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
172 set ::conflict_list
173 } [list {*}{
174 {INSERT sqlite_stat1 CONFLICT {t xyz t sqlite_autoindex_xyz_1 t {100 1 1}} {t xyz t sqlite_autoindex_xyz_1 t {200 1 1}}}
175 }]
176
177 #-------------------------------------------------------------------------
178 # Test that the conflict-handler is invoked for conflicts caused by
179 # DELETE ops even if SQLITE_CHANGESETAPPLY_IGNORENOOP is specified.
180 #
181 set ::conflict_list [list]
182 proc xConflict {args} {
183 lappend ::conflict_list $args
184 return "OMIT"
185 }
186
187 foreach {tn wo} {
188 1 ""
189 2 " WITHOUT ROWID "
190 } {
191 reset_db
192 catch { db2 close }
193 forcedelete test.db2
194 do_execsql_test 4.$tn.0 "
195 CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c) $wo;
196 INSERT INTO x1 VALUES(1, 'one', 'i');
197 INSERT INTO x1 VALUES(2, 'two', 'ii');
198 INSERT INTO x1 VALUES(3, 'three', 'iii');
199 VACUUM INTO 'test.db2';
200 "
201
202 set C [changeset_from_sql {
203 DELETE FROM x1 WHERE a=2;
204 }]
205
206 sqlite3 db2 test.db2
207 do_execsql_test -db db2 4.$tn.1 {
208 UPDATE x1 SET b='four hundred' WHERE a=2;
209 }
210
211 do_test 4.$tn.2.1 {
212 set ::conflict_list [list]
213 sqlite3changeset_apply_v2 db2 $C xConflict
214 set ::conflict_list
215 } [list {*}{
216 {DELETE x1 DATA {i 2 t two t ii} {i 2 t {four hundred} t ii}}
217 }]
218
219 do_test 4.$tn.2.2 {
220 set ::conflict_list [list]
221 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
222 set ::conflict_list
223 } [list {*}{
224 {DELETE x1 DATA {i 2 t two t ii} {i 2 t {four hundred} t ii}}
225 }]
226 }
227
228 foreach {tn wo} {
229 1 ""
230 2 " WITHOUT ROWID "
231 } {
232 reset_db
233 catch { db2 close }
234 forcedelete test.db2
235 do_execsql_test 5.$tn.0 "
236 PRAGMA foreign_keys = ON;
237 CREATE TABLE p1(a INTEGER PRIMARY KEY, b, c) $wo;
238 CREATE TABLE c1(a INTEGER PRIMARY KEY REFERENCES p1, b);
239 INSERT INTO p1 VALUES(1, 'one', 'i');
240 INSERT INTO p1 VALUES(2, 'two', 'ii');
241 INSERT INTO p1 VALUES(3, 'three', 'iii');
242 VACUUM INTO 'test.db2';
243 "
244
245 set C [changeset_from_sql {
246 DELETE FROM p1 WHERE a=2;
247 }]
248
249 sqlite3 db2 test.db2
250 do_execsql_test -db db2 5.$tn.1 {
251 PRAGMA foreign_keys = ON;
252 INSERT INTO c1 VALUES(2, 'xyz');
253 }
254
255 do_test 5.$tn.2.1 {
256 set ::conflict_list [list]
257 sqlite3changeset_apply_v2 db2 $C xConflict
258 set ::conflict_list
259 } [list {*}{
260 {FOREIGN_KEY 1}
261 }]
262
263 # Reinsert the row deleted by test case 5.$tn.2.1
264 execsql { INSERT INTO p1 VALUES(2, 'two', 'ii') } db2
265
266 do_test 5.$tn.2.2 {
267 set ::conflict_list [list]
268 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
269 set ::conflict_list
270 } [list {*}{
271 {FOREIGN_KEY 1}
272 }]
273 }
274
275 foreach {tn wo} {
276 1 ""
277 2 " WITHOUT ROWID "
278 } {
279 reset_db
280 catch { db2 close }
281 forcedelete test.db2
282 do_execsql_test 6.$tn.0 "
283 CREATE TABLE x1(a INTEGER PRIMARY KEY, b, c) $wo;
284 CREATE TABLE x2(x UNIQUE);
285
286 INSERT INTO x1 VALUES(1, 'one', 'i');
287 INSERT INTO x1 VALUES(2, 'two', 'ii');
288 INSERT INTO x1 VALUES(3, 'three', 'iii');
289 VACUUM INTO 'test.db2';
290 "
291
292 set C [changeset_from_sql {
293 DELETE FROM x1 WHERE a=2;
294 }]
295
296 sqlite3 db2 test.db2
297 do_execsql_test -db db2 6.$tn.1 {
298 INSERT INTO x2 VALUES(0);
299 CREATE TRIGGER tr1 AFTER DELETE ON x1 BEGIN
300 INSERT INTO x2 VALUES(0);
301 END;
302 }
303
304 do_test 6.$tn.2.1 {
305 set ::conflict_list [list]
306 sqlite3changeset_apply_v2 db2 $C xConflict
307 set ::conflict_list
308 } [list {*}{
309 {DELETE x1 CONSTRAINT {i 2 t two t ii}}
310 }]
311
312 breakpoint
313 do_test 6.$tn.2.2 {
314 set ::conflict_list [list]
315 sqlite3changeset_apply_v2 -ignorenoop db2 $C xConflict
316 set ::conflict_list
317 } [list {*}{
318 {DELETE x1 CONSTRAINT {i 2 t two t ii}}
319 }]
320 }
321
322
323 finish_test
324