Debian Codegraph @

ext/session/session9.test

raw
1 # 2013 July 04
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 #
12 # This file tests that the sessions module handles foreign key constraint
13 # violations when applying changesets as required.
14 #
15
16 if {![info exists testdir]} {
17 set testdir [file join [file dirname [info script]] .. .. test]
18 }
19 source [file join [file dirname [info script]] session_common.tcl]
20 source $testdir/tester.tcl
21 ifcapable !session {finish_test; return}
22 set testprefix session9
23
24
25 #--------------------------------------------------------------------
26 # Basic tests.
27 #
28 proc populate_db {} {
29 drop_all_tables
30 execsql {
31 PRAGMA foreign_keys = 1;
32 CREATE TABLE p1(a PRIMARY KEY, b);
33 CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1);
34 CREATE TABLE c2(a PRIMARY KEY,
35 b REFERENCES p1 DEFERRABLE INITIALLY DEFERRED
36 );
37
38 INSERT INTO p1 VALUES(1, 'one');
39 INSERT INTO p1 VALUES(2, 'two');
40 INSERT INTO p1 VALUES(3, 'three');
41 INSERT INTO p1 VALUES(4, 'four');
42 }
43 }
44
45 proc capture_changeset {sql} {
46 sqlite3session S db main
47
48 foreach t [db eval {SELECT name FROM sqlite_master WHERE type='table'}] {
49 S attach $t
50 }
51 execsql $sql
52 set ret [S changeset]
53 S delete
54
55 return $ret
56 }
57
58 do_test 1.1 {
59 populate_db
60 set cc [capture_changeset {
61 INSERT INTO c1 VALUES('ii', 2);
62 INSERT INTO c2 VALUES('iii', 3);
63 }]
64 set {} {}
65 } {}
66
67 proc xConflict {args} {
68 lappend ::xConflict {*}$args
69 return $::conflictret
70 }
71
72 foreach {tn delrow trans conflictargs conflictret} {
73 1 2 0 {FOREIGN_KEY 1} OMIT
74 2 3 0 {FOREIGN_KEY 1} OMIT
75 3 2 1 {FOREIGN_KEY 1} OMIT
76 4 3 1 {FOREIGN_KEY 1} OMIT
77 5 2 0 {FOREIGN_KEY 1} ABORT
78 6 3 0 {FOREIGN_KEY 1} ABORT
79 7 2 1 {FOREIGN_KEY 1} ABORT
80 8 3 1 {FOREIGN_KEY 1} ABORT
81 } {
82
83 set A(OMIT,0) {0 {}}
84 set A(OMIT,1) {0 {}}
85 set A(ABORT,0) {1 SQLITE_CONSTRAINT}
86 set A(ABORT,1) {1 SQLITE_CONSTRAINT}
87 do_test 1.2.$tn.1 {
88 populate_db
89 execsql { DELETE FROM p1 WHERE a=($delrow+0) }
90 if {$trans} { execsql BEGIN }
91
92 set ::xConflict [list]
93 list [catch {sqlite3changeset_apply db $::cc xConflict} msg] $msg
94 } $A($conflictret,$trans)
95
96 do_test 1.2.$tn.2 { set ::xConflict } $conflictargs
97
98 set A(OMIT,0) {1 1}
99 set A(OMIT,1) {1 1}
100 set A(ABORT,0) {0 0}
101 set A(ABORT,1) {0 0}
102
103 do_test 1.2.$tn.3 {
104 execsql { SELECT count(*) FROM c1 UNION ALL SELECT count(*) FROM c2 }
105 } $A($conflictret,$trans)
106
107 do_test 1.2.$tn.4 { expr ![sqlite3_get_autocommit db] } $trans
108 do_test 1.2.$tn.5 {
109 if { $trans && $conflictret=="ABORT" } { execsql COMMIT }
110 } {}
111 catchsql ROLLBACK
112 }
113
114 #--------------------------------------------------------------------
115 # Test that closing a transaction clears the defer_foreign_keys flag.
116 #
117 foreach {tn open noclose close} {
118 1 BEGIN {} COMMIT
119 2 BEGIN {} ROLLBACK
120
121 3 {SAVEPOINT one} {} {RELEASE one}
122 4 {SAVEPOINT one} {ROLLBACK TO one} {RELEASE one}
123 } {
124 execsql $open
125 do_execsql_test 2.$tn.1 { PRAGMA defer_foreign_keys } {0}
126
127 do_execsql_test 2.$tn.2 {
128 PRAGMA defer_foreign_keys = 1;
129 PRAGMA defer_foreign_keys;
130 } {1}
131
132 execsql $noclose
133 do_execsql_test 2.$tn.3 { PRAGMA defer_foreign_keys } {1}
134
135 execsql $close
136 do_execsql_test 2.$tn.4 { PRAGMA defer_foreign_keys } {0}
137 }
138
139 #--------------------------------------------------------------------
140 # Test that a cyclic relationship can be inserted and deleted.
141 #
142 # This situation does not come up in practice, but testing it serves to
143 # show that it does not matter which order parent and child keys
144 # are processed in internally when applying a changeset.
145 #
146 drop_all_tables
147
148 do_execsql_test 3.1 {
149 CREATE TABLE t1(a PRIMARY KEY, b);
150 CREATE TABLE t2(x PRIMARY KEY, y);
151 }
152
153 # Create changesets as follows:
154 #
155 # $cc1 - Insert a row into t1.
156 # $cc2 - Insert a row into t2.
157 # $cc - Combination of $cc1 and $cc2.
158 #
159 # $ccdel1 - Delete the row from t1.
160 # $ccdel2 - Delete the row from t2.
161 # $ccdel - Combination of $cc1 and $cc2.
162 #
163 do_test 3.2 {
164 set cc1 [capture_changeset {
165 INSERT INTO t1 VALUES('one', 'value one');
166 }]
167 set ccdel1 [capture_changeset { DELETE FROM t1; }]
168 set cc2 [capture_changeset {
169 INSERT INTO t2 VALUES('value one', 'one');
170 }]
171 set ccdel2 [capture_changeset { DELETE FROM t2; }]
172 set cc [capture_changeset {
173 INSERT INTO t1 VALUES('one', 'value one');
174 INSERT INTO t2 VALUES('value one', 'one');
175 }]
176 set ccdel [capture_changeset {
177 DELETE FROM t1;
178 DELETE FROM t2;
179 }]
180 set {} {}
181 } {}
182
183 # Now modify the database schema to create a cyclic foreign key dependency
184 # between tables t1 and t2. This means that although changesets $cc and
185 # $ccdel can be applied, none of the others may without violating the
186 # foreign key constraints.
187 #
188 do_test 3.3 {
189
190 drop_all_tables
191 execsql {
192 CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t2);
193 CREATE TABLE t2(x PRIMARY KEY, y REFERENCES t1);
194 }
195
196
197 proc conflict_handler {args} { return "ABORT" }
198 sqlite3changeset_apply db $cc conflict_handler
199
200 execsql {
201 SELECT * FROM t1;
202 SELECT * FROM t2;
203 }
204 } {one {value one} {value one} one}
205
206 do_test 3.3.1 {
207 list [catch {sqlite3changeset_apply db $::ccdel1 conflict_handler} msg] $msg
208 } {1 SQLITE_CONSTRAINT}
209
210 do_test 3.3.2 {
211 list [catch {sqlite3changeset_apply db $::ccdel2 conflict_handler} msg] $msg
212 } {1 SQLITE_CONSTRAINT}
213
214 do_test 3.3.4.1 {
215 list [catch {sqlite3changeset_apply db $::ccdel conflict_handler} msg] $msg
216 } {0 {}}
217 do_execsql_test 3.3.4.2 {
218 SELECT * FROM t1;
219 SELECT * FROM t2;
220 } {}
221
222 do_test 3.5.1 {
223 list [catch {sqlite3changeset_apply db $::cc1 conflict_handler} msg] $msg
224 } {1 SQLITE_CONSTRAINT}
225 do_test 3.5.2 {
226 list [catch {sqlite3changeset_apply db $::cc2 conflict_handler} msg] $msg
227 } {1 SQLITE_CONSTRAINT}
228
229 #--------------------------------------------------------------------
230 # Test that if a change that affects FK processing is not applied
231 # due to a separate constraint, SQLite does not get confused and
232 # increment FK counters anyway.
233 #
234 drop_all_tables
235 do_execsql_test 4.1 {
236 CREATE TABLE p1(x PRIMARY KEY, y);
237 CREATE TABLE c1(a PRIMARY KEY, b REFERENCES p1);
238 INSERT INTO p1 VALUES(1,1);
239 }
240
241 do_execsql_test 4.2.1 {
242 BEGIN;
243 PRAGMA defer_foreign_keys = 1;
244 INSERT INTO c1 VALUES('x', 'x');
245 }
246 do_catchsql_test 4.2.2 { COMMIT } {1 {FOREIGN KEY constraint failed}}
247 do_catchsql_test 4.2.3 { ROLLBACK } {0 {}}
248
249 do_execsql_test 4.3.1 {
250 BEGIN;
251 PRAGMA defer_foreign_keys = 1;
252 INSERT INTO c1 VALUES(1, 1);
253 }
254 do_catchsql_test 4.3.2 {
255 INSERT INTO c1 VALUES(1, 'x')
256 } {1 {UNIQUE constraint failed: c1.a}}
257
258 do_catchsql_test 4.3.3 { COMMIT } {0 {}}
259 do_catchsql_test 4.3.4 { BEGIN ; COMMIT } {0 {}}
260
261 #--------------------------------------------------------------------
262 # Test that if a DELETE change cannot be applied due to an
263 # SQLITE_CONSTRAINT error thrown by a trigger program, things do not
264 # go awry.
265
266 drop_all_tables
267 reset_db
268 do_execsql_test 5.1 {
269 CREATE TABLE x1(x PRIMARY KEY, y);
270 CREATE TABLE x2(x PRIMARY KEY, y);
271 INSERT INTO x2 VALUES(1, 1);
272 INSERT INTO x1 VALUES(1, 1);
273 }
274
275 set ::cc [changeset_from_sql { DELETE FROM x1; }]
276
277 do_execsql_test 5.2 {
278 INSERT INTO x1 VALUES(1, 1);
279 CREATE TRIGGER tr1 AFTER DELETE ON x1 BEGIN
280 INSERT INTO x2 VALUES(old.x, old.y);
281 END;
282 } {}
283
284 proc conflict_handler {args} { return "ABORT" }
285 do_test 5.3 {
286 list [catch {sqlite3changeset_apply db $::cc conflict_handler} msg] $msg
287 } {1 SQLITE_ABORT}
288
289 do_execsql_test 5.4 {
290 SELECT * FROM X1;
291 } {1 1}
292
293 #--------------------------------------------------------------------
294 reset_db
295 do_execsql_test 6.0 {
296 CREATE TABLE p1(a INTEGER PRIMARY KEY, b);
297 CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES p1);
298 }
299
300 set ::cc [changeset_from_sql {
301 INSERT INTO c1 VALUES(10, 20);
302 }]
303
304 forcedelete test.db2
305 sqlite3 db2 test.db2
306 do_execsql_test -db db2 6.1 {
307 CREATE TABLE c1(x INTEGER PRIMARY KEY, y REFERENCES p1);
308 PRAGMA foreign_keys = ON;
309 PRAGMA foreign_keys;
310 } {1}
311
312 proc conflict_handler {args} {
313 puts $args
314 return "OMIT"
315 }
316
317 do_test 6.2 {
318 list [catch {sqlite3changeset_apply_v2 db2 $::cc conflict_handler} msg] $msg
319 } {1 SQLITE_ERROR}
320
321 do_test 6.3.1 { sqlite3_errmsg db2 } {no such table: main.p1}
322 do_test 6.3.2 { sqlite3_errcode db2 } {SQLITE_ERROR}
323
324 do_test 6.4 {
325 catchsql { INSERT INTO c1 VALUES(100, 200) } db2
326 } {1 {no such table: main.p1}}
327 db2 close
328
329 #-------------------------------------------------------------------------
330 reset_db
331
332 do_execsql_test 7.0 {
333 PRAGMA trusted_schema=OFF;
334 PRAGMA foreign_keys=ON;
335 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, d);
336 CREATE TABLE t2(e TEXT PRIMARY KEY NOT NULL, f, g);
337 CREATE TABLE t3(w REAL PRIMARY KEY NOT NULL, x, y);
338 CREATE TABLE t4(z PRIMARY KEY) WITHOUT ROWID;
339 CREATE TABLE tc(a INTEGER, b INTEGER, c BLOB, d TEXT, PRIMARY KEY(a,b));
340 CREATE TABLE wr(a TEXT, b INT, c, PRIMARY KEY(a,b)) WITHOUT ROWID;
341 CREATE TABLE child(x INTEGER PRIMARY KEY, y INTEGER REFERENCES t1(a) ON DELETE CASCADE, z TEXT);
342 INSERT INTO t1 VALUES(1,2,3,4),(2,3.5,'four',x'556677'),(3,NULL,'xyz',15),(4,'bubba',2147483648,0.0);
343 INSERT INTO t1 SELECT a+4,c,d,b FROM t1;
344 INSERT INTO t2 VALUES('x1y',2,3),('x2y','four',x'556677'),('x3y',NULL,'xyz');
345 INSERT INTO t3 VALUES(1.1,'a','b'),(2.2,x'00ff','c');
346 INSERT INTO t4 VALUES('alpha'),('beta'),('gamma');
347 INSERT INTO tc VALUES(1,1,x'0102','one'),(1,2,x'0304','two'),(2,1,x'0506','three');
348 INSERT INTO wr VALUES('a',1,'wa'),('b',2,'wb');
349 INSERT INTO child VALUES(1,1,'c1'),(2,2,'c2');
350 }
351
352 set C [db one "SELECT unhex('
353 54 0401 0200 0074 6300 1700 0100 0000
354 0000 0000 0101 0000 0000 0069 0001 0402
355 0102 0303 6f6e 6500 0004 2008 40a1 4c0b
356 0d3f 3730 5d92 3f18 f2c9 66a6 4220 a873
357 04cc 5281 ce5c d9d4 dc8b 7003 056f 6e65
358 2d75 1700 0100 0002 5004 0100 0000 6d69
359 7800 1700 0302 6b31 0409 1100
360 ', ' \n')"]
361
362
363 proc conflict_handler {args} {
364 return "OMIT"
365 }
366 do_test 7.1 {
367 list [catch {sqlite3changeset_apply_v2 db $::C conflict_handler} msg] $msg
368 } {1 SQLITE_CORRUPT}
369
370 finish_test