Debian Codegraph @

test/temptrigger.test

raw
1 # 2009 February 27
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 # $Id: temptrigger.test,v 1.3 2009/04/15 13:07:19 drh Exp $
13
14 set testdir [file dirname $argv0]
15 source $testdir/tester.tcl
16 set testprefix temptrigger
17
18 ifcapable {!trigger || !shared_cache} { finish_test ; return }
19
20 # Test cases:
21 #
22 # temptrigger-1.*: Shared cache problem.
23 # temptrigger-2.*: A similar shared cache problem.
24 # temptrigger-3.*: Attached database problem.
25 #
26
27 #-------------------------------------------------------------------------
28 # Test case temptrigger-1.* demonstrates a problem with temp triggers
29 # in shared-cache mode. If process 1 connections to a shared-cache and
30 # creates a temp trigger, the temp trigger is linked into the shared-cache
31 # schema. If process 2 reloads the shared-cache schema from disk, then
32 # it does not recreate the temp trigger belonging to process 1. From the
33 # point of view of process 1, the temp trigger just disappeared.
34 #
35 # temptrigger-1.1: In shared cache mode, create a table in the main
36 # database and add a temp trigger to it.
37 #
38 # temptrigger-1.2: Check that the temp trigger is correctly fired. Check
39 # that the temp trigger is not fired by statements
40 # executed by a second connection connected to the
41 # same shared cache.
42 #
43 # temptrigger-1.3: Using the second connection to the shared-cache, cause
44 # the shared-cache schema to be reloaded.
45 #
46 # temptrigger-1.4: Check that the temp trigger is still fired correctly.
47 #
48 # temptrigger-1.5: Check that the temp trigger can be dropped without error.
49 #
50 db close
51 set ::enable_shared_cache [sqlite3_enable_shared_cache]
52 sqlite3_enable_shared_cache 1
53
54 sqlite3 db test.db
55 sqlite3 db2 test.db
56
57 do_test temptrigger-1.1 {
58 execsql {
59 CREATE TABLE t1(a, b);
60 CREATE TEMP TABLE tt1(a, b);
61 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
62 INSERT INTO tt1 VALUES(new.a, new.b);
63 END;
64 }
65 } {}
66
67 do_test temptrigger-1.2.1 {
68 execsql { INSERT INTO t1 VALUES(1, 2) }
69 execsql { SELECT * FROM t1 }
70 } {1 2}
71 do_test temptrigger-1.2.2 {
72 execsql { SELECT * FROM tt1 }
73 } {1 2}
74 do_test temptrigger-1.2.3 {
75 execsql { INSERT INTO t1 VALUES(3, 4) } db2
76 execsql { SELECT * FROM t1 }
77 } {1 2 3 4}
78 do_test temptrigger-1.2.4 {
79 execsql { SELECT * FROM tt1 }
80 } {1 2}
81
82 # Cause the shared-cache schema to be reloaded.
83 #
84 do_test temptrigger-1.3 {
85 execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db2
86 } {}
87
88 do_test temptrigger-1.4 {
89 execsql { INSERT INTO t1 VALUES(5, 6) }
90 execsql { SELECT * FROM tt1 }
91 } {1 2 5 6}
92
93 do_test temptrigger-1.5 {
94 # Before the bug was fixed, the following 'DROP TRIGGER' hit an
95 # assert if executed.
96 #execsql { DROP TRIGGER tr1 }
97 } {}
98
99 catch {db close}
100 catch {db2 close}
101
102 #-------------------------------------------------------------------------
103 # Tests temptrigger-2.* are similar to temptrigger-1.*, except that
104 # temptrigger-2.3 simply opens and closes a connection to the shared-cache.
105 # It does not do anything special to cause the schema to be reloaded.
106 #
107 do_test temptrigger-2.1 {
108 sqlite3 db test.db
109 execsql {
110 DELETE FROM t1;
111 CREATE TEMP TABLE tt1(a, b);
112 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN
113 INSERT INTO tt1 VALUES(new.a, new.b);
114 END;
115 }
116 } {}
117 do_test temptrigger-2.2 {
118 execsql {
119 INSERT INTO t1 VALUES(10, 20);
120 SELECT * FROM tt1;
121 }
122 } {10 20}
123 do_test temptrigger-2.3 {
124 sqlite3 db2 test.db
125 db2 close
126 } {}
127 do_test temptrigger-2.4 {
128 execsql {
129 INSERT INTO t1 VALUES(30, 40);
130 SELECT * FROM tt1;
131 }
132 } {10 20 30 40}
133 do_test temptrigger-2.5 {
134 #execsql { DROP TRIGGER tr1 }
135 } {}
136
137 catch {db close}
138 catch {db2 close}
139 sqlite3_enable_shared_cache $::enable_shared_cache
140
141 #-------------------------------------------------------------------------
142 # Test case temptrigger-3.* demonstrates a problem with temp triggers
143 # on tables located in attached databases. At one point when SQLite reloaded
144 # the schema of an attached database (because some other connection had
145 # changed the schema cookie) it was not re-creating temp triggers attached
146 # to tables located within the attached database.
147 #
148 # temptrigger-3.1: Attach database 'test2.db' to connection [db]. Add a
149 # temp trigger to a table in 'test2.db'.
150 #
151 # temptrigger-3.2: Check that the temp trigger is correctly fired.
152 #
153 # temptrigger-3.3: Update the schema of 'test2.db' using an external
154 # connection. This forces [db] to reload the 'test2.db'
155 # schema. Check that the temp trigger is still fired
156 # correctly.
157 #
158 # temptrigger-3.4: Check that the temp trigger can be dropped without error.
159 #
160 do_test temptrigger-3.1 {
161 catch { forcedelete test2.db test2.db-journal }
162 catch { forcedelete test.db test.db-journal }
163 sqlite3 db test.db
164 sqlite3 db2 test2.db
165 execsql { CREATE TABLE t2(a, b) } db2
166 execsql {
167 ATTACH 'test2.db' AS aux;
168 CREATE TEMP TABLE tt2(a, b);
169 CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN
170 INSERT INTO tt2 VALUES(new.a, new.b);
171 END;
172 }
173 } {}
174
175 do_test temptrigger-3.2.1 {
176 execsql {
177 INSERT INTO aux.t2 VALUES(1, 2);
178 SELECT * FROM aux.t2;
179 }
180 } {1 2}
181 do_test temptrigger-3.2.2 {
182 execsql { SELECT * FROM tt2 }
183 } {1 2}
184
185 do_test temptrigger-3.3.1 {
186 execsql { CREATE TABLE t3(a, b) } db2
187 execsql {
188 INSERT INTO aux.t2 VALUES(3, 4);
189 SELECT * FROM aux.t2;
190 }
191 } {1 2 3 4}
192 do_test temptrigger-3.3.2 {
193 execsql { SELECT * FROM tt2 }
194 } {1 2 3 4}
195
196 do_test temptrigger-3.4 {
197 # Before the bug was fixed, the following 'DROP TRIGGER' hit an
198 # assert if executed.
199 #execsql { DROP TRIGGER tr2 }
200 } {}
201
202 catch { db close }
203 catch { db2 close }
204
205
206 #-------------------------------------------------------------------------
207 # Test that creating a temp table after a temp trigger on the same name
208 # has been created is an error.
209 #
210 reset_db
211 do_execsql_test 4.0 {
212 CREATE TABLE t1(x);
213 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN
214 SELECT 1,2,3;
215 END;
216 }
217
218 do_execsql_test 4.1 {
219 CREATE TEMP TABLE t1(x);
220 }
221
222 #-------------------------------------------------------------------------
223 # Test that no harm is done if the table a temp trigger is attached to is
224 # deleted by an external connection.
225 #
226 reset_db
227 do_execsql_test 5.0 {
228 CREATE TABLE t1(x);
229 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN SELECT 1,2,3; END;
230 }
231
232 do_test 5.1 {
233 sqlite3 db2 test.db
234 execsql { DROP TABLE t1 } db2
235 } {}
236
237 do_execsql_test 5.2 {
238 SELECT * FROM sqlite_master;
239 SELECT * FROM temp.sqlite_master;
240 } {
241 trigger tr1 t1 0
242 {CREATE TRIGGER tr1 BEFORE INSERT ON t1 BEGIN SELECT 1,2,3; END}
243 }
244 db2 close
245
246 #-------------------------------------------------------------------------
247 # Check that if a second connection creates a table in an attached database
248 # with the same name as a table in the main database that has a temp
249 # trigger attached to it nothing goes awry.
250 #
251 reset_db
252 forcedelete test.db2
253
254 do_execsql_test 6.0 {
255 CREATE TABLE t1(x);
256 CREATE TEMP TRIGGER tr1 BEFORE INSERT ON t1 BEGIN
257 SELECT raise(ABORT, 'error');
258 END;
259 ATTACH 'test.db2' AS aux;
260 }
261
262 do_test 6.1 {
263 sqlite3 db2 test.db2
264 execsql { CREATE TABLE t1(a, b, c); } db2
265 } {}
266
267 do_execsql_test 6.2 {
268 SELECT type,name,tbl_name,sql FROM aux.sqlite_master;
269 INSERT INTO aux.t1 VALUES(1,2,3);
270 } {
271 table t1 t1 {CREATE TABLE t1(a, b, c)}
272 }
273
274 do_catchsql_test 6.3 {
275 INSERT INTO main.t1 VALUES(1);
276 } {1 error}
277 db2 close
278
279 #-------------------------------------------------------------------------
280 reset_db
281 forcedelete test.db2
282
283 do_execsql_test 7.0 {
284 CREATE TABLE m1(a, b);
285 ATTACH 'test.db2' AS aux;
286 CREATE TABLE aux.a1(c, d);
287 }
288
289 do_execsql_test 7.1 {
290 CREATE TEMP TRIGGER tr1 AFTER INSERT ON m1 BEGIN
291 INSERT INTO a1 VALUES(new.a, new.b);
292 END;
293
294 INSERT INTO m1 VALUES(5, 6);
295 SELECT * FROM aux.a1;
296 } {5 6}
297
298 do_execsql_test 7.2 {
299 CREATE TABLE a1(e, f);
300 INSERT INTO m1 VALUES(7, 8);
301 }
302
303 do_execsql_test 7.3.1 { SELECT * FROM main.a1 } {7 8}
304 do_execsql_test 7.3.2 { SELECT * FROM aux.a1 } {5 6}
305
306 do_execsql_test 7.4 {
307 DROP TRIGGER tr1;
308 CREATE TEMP TRIGGER tr1 AFTER INSERT ON m1 BEGIN
309 INSERT INTO a1 SELECT d, c FROM aux.a1;
310 END;
311
312 DELETE FROM aux.a1;
313 DELETE FROM main.a1;
314 INSERT INTO aux.a1 VALUES('hello', 'world');
315 }
316
317 do_execsql_test 7.5 {
318 INSERT INTO m1 VALUES(9, 10);
319 SELECT * FROM main.a1;
320 } {world hello}
321
322 do_catchsql_test 7.6 {
323 DROP TRIGGER tr1;
324 CREATE TRIGGER tr1 AFTER INSERT ON m1 BEGIN
325 INSERT INTO a1 SELECT d, c FROM aux.a1;
326 END;
327 } {1 {trigger tr1 cannot reference objects in database aux}}
328
329 #-------------------------------------------------------------------------
330 # Check that temp triggers may INSERT/UPDATE/DELETE to fully qualified
331 # table names.
332 reset_db
333 forcedelete {*}[glob -nocomplain *mj*]
334 forcedelete test.db2
335 do_execsql_test 8.0 {
336 ATTACH 'test.db2' AS aux;
337 CREATE TABLE t1(a, b);
338 CREATE TABLE t2(c, d);
339 CREATE TABLE aux.t1(e, f);
340 CREATE TABLE aux.t2(g, h);
341 }
342
343 do_catchsql_test 8.1.1 {
344 CREATE TRIGGER tr1 AFTER INSERT ON t2 BEGIN
345 INSERT INTO aux.t1 VALUES(new.c, new.d);
346 END;
347 } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
348
349 do_execsql_test 8.1.2 {
350 CREATE TEMP TRIGGER tr1 AFTER INSERT ON t2 BEGIN
351 INSERT INTO aux.t1 VALUES(new.c, new.d);
352 END;
353
354 INSERT INTO main.t2 VALUES('x', 'y');
355 SELECT * FROM aux.t1;
356 } {x y}
357
358 do_execsql_test 8.1.3 { SELECT * FROM t1 } {}
359
360 do_catchsql_test 8.2.1 {
361 CREATE TRIGGER aux.tr2 AFTER UPDATE ON aux.t1 BEGIN
362 UPDATE main.t2 SET c=new.e, d=new.f;
363 END;
364 } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
365
366 do_execsql_test 8.2.2 {
367 CREATE TEMP TRIGGER tr2 AFTER UPDATE ON aux.t1 BEGIN
368 UPDATE main.t2 SET c=new.e, d=new.f;
369 END;
370
371 UPDATE aux.t1 SET e=1, f=2;
372 SELECT * FROM t2;
373 } {1 2}
374
375 do_execsql_test 8.2.3 { SELECT * FROM aux.t2 } {}
376
377 do_catchsql_test 8.3.1 {
378 CREATE TRIGGER tr3 AFTER DELETE ON t2 BEGIN
379 DELETE FROM aux.t1;
380 END;
381 } {1 {qualified table names are not allowed on INSERT, UPDATE, and DELETE statements within triggers}}
382
383 do_execsql_test 8.3.2 {
384 INSERT INTO main.t1 VALUES('a', 'b');
385 CREATE TEMP TRIGGER tr3 AFTER DELETE ON t2 BEGIN
386 DELETE FROM aux.t1;
387 END;
388
389 DELETE FROM main.t2;
390 SELECT * FROM aux.t1;
391 } {}
392
393 do_execsql_test 8.3.3 { SELECT * FROM t1 } {a b}
394
395 #-------------------------------------------------------------------------
396 reset_db
397 set nDb 8
398 do_test 9.0 {
399 for {set ii 0} {$ii < $nDb} {incr ii} {
400 db eval "ATTACH ':memory:' AS db$ii"
401 db eval "CREATE TABLE db$ii.tbl(a, b, c)"
402 }
403
404 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {
405 set jj [expr $ii+1]
406 db eval "
407 CREATE TEMP TRIGGER tr$ii AFTER INSERT ON db$ii.tbl BEGIN
408 INSERT INTO db$jj.tbl VALUES(new.b, new.c, new.a);
409 END;
410 "
411 }
412 } {}
413
414 do_execsql_test 9.1 { INSERT INTO db0.tbl VALUES('a', 'b', 'c'); }
415 do_execsql_test 9.1.1 { SELECT * FROM db0.tbl } {a b c}
416 do_execsql_test 9.1.2 { SELECT * FROM db1.tbl } {b c a}
417 do_execsql_test 9.1.3 { SELECT * FROM db2.tbl } {c a b}
418 do_execsql_test 9.1.1 { SELECT * FROM db3.tbl } {a b c}
419 do_execsql_test 9.1.2 { SELECT * FROM db4.tbl } {b c a}
420 do_execsql_test 9.1.3 { SELECT * FROM db5.tbl } {c a b}
421 do_execsql_test 9.1.1 { SELECT * FROM db6.tbl } {a b c}
422 do_execsql_test 9.1.2 { SELECT * FROM db7.tbl } {b c a}
423
424 do_test 9.2 {
425 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {
426 set jj [expr $ii+1]
427 db eval "
428 CREATE TEMP TRIGGER tru$ii AFTER UPDATE ON db$ii.tbl BEGIN
429 UPDATE db$jj.tbl SET a=new.b, b=new.c, c=new.a;
430 END;
431 "
432 }
433 } {}
434
435 do_execsql_test 9.3 { UPDATE db0.tbl SET a=1, b=2, c=3 }
436 do_execsql_test 9.3.1 { SELECT * FROM db0.tbl } {1 2 3}
437 do_execsql_test 9.3.2 { SELECT * FROM db1.tbl } {2 3 1}
438 do_execsql_test 9.3.3 { SELECT * FROM db2.tbl } {3 1 2}
439 do_execsql_test 9.3.1 { SELECT * FROM db3.tbl } {1 2 3}
440 do_execsql_test 9.3.2 { SELECT * FROM db4.tbl } {2 3 1}
441 do_execsql_test 9.3.3 { SELECT * FROM db5.tbl } {3 1 2}
442 do_execsql_test 9.3.1 { SELECT * FROM db6.tbl } {1 2 3}
443 do_execsql_test 9.3.2 { SELECT * FROM db7.tbl } {2 3 1}
444
445 do_test 9.4 {
446 for {set ii 0} {$ii < ($nDb-1)} {incr ii} {
447 set jj [expr $ii+1]
448 db eval "
449 CREATE TEMP TRIGGER trd$ii BEFORE DELETE ON db$ii.tbl BEGIN
450 DELETE FROM db$jj.tbl;
451 END;
452 "
453 }
454 } {}
455
456 do_execsql_test 9.5 { DELETE FROM db0.tbl }
457 do_execsql_test 9.5.1 { SELECT * FROM db0.tbl } {}
458 do_execsql_test 9.5.2 { SELECT * FROM db1.tbl } {}
459 do_execsql_test 9.5.3 { SELECT * FROM db2.tbl } {}
460 do_execsql_test 9.5.1 { SELECT * FROM db3.tbl } {}
461 do_execsql_test 9.5.2 { SELECT * FROM db4.tbl } {}
462 do_execsql_test 9.5.3 { SELECT * FROM db5.tbl } {}
463 do_execsql_test 9.5.1 { SELECT * FROM db6.tbl } {}
464 do_execsql_test 9.5.2 { SELECT * FROM db7.tbl } {}
465
466 finish_test