wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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 | |||
17 | ifcapable {!trigger || !shared_cache} { finish_test ; return } |
||
18 | |||
19 | # Test cases: |
||
20 | # |
||
21 | # temptrigger-1.*: Shared cache problem. |
||
22 | # temptrigger-2.*: A similar shared cache problem. |
||
23 | # temptrigger-3.*: Attached database problem. |
||
24 | # |
||
25 | |||
26 | #------------------------------------------------------------------------- |
||
27 | # Test case temptrigger-1.* demonstrates a problem with temp triggers |
||
28 | # in shared-cache mode. If process 1 connections to a shared-cache and |
||
29 | # creates a temp trigger, the temp trigger is linked into the shared-cache |
||
30 | # schema. If process 2 reloads the shared-cache schema from disk, then |
||
31 | # it does not recreate the temp trigger belonging to process 1. From the |
||
32 | # point of view of process 1, the temp trigger just disappeared. |
||
33 | # |
||
34 | # temptrigger-1.1: In shared cache mode, create a table in the main |
||
35 | # database and add a temp trigger to it. |
||
36 | # |
||
37 | # temptrigger-1.2: Check that the temp trigger is correctly fired. Check |
||
38 | # that the temp trigger is not fired by statements |
||
39 | # executed by a second connection connected to the |
||
40 | # same shared cache. |
||
41 | # |
||
42 | # temptrigger-1.3: Using the second connection to the shared-cache, cause |
||
43 | # the shared-cache schema to be reloaded. |
||
44 | # |
||
45 | # temptrigger-1.4: Check that the temp trigger is still fired correctly. |
||
46 | # |
||
47 | # temptrigger-1.5: Check that the temp trigger can be dropped without error. |
||
48 | # |
||
49 | db close |
||
50 | set ::enable_shared_cache [sqlite3_enable_shared_cache] |
||
51 | sqlite3_enable_shared_cache 1 |
||
52 | |||
53 | sqlite3 db test.db |
||
54 | sqlite3 db2 test.db |
||
55 | |||
56 | do_test temptrigger-1.1 { |
||
57 | execsql { |
||
58 | CREATE TABLE t1(a, b); |
||
59 | CREATE TEMP TABLE tt1(a, b); |
||
60 | CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
||
61 | INSERT INTO tt1 VALUES(new.a, new.b); |
||
62 | END; |
||
63 | } |
||
64 | } {} |
||
65 | |||
66 | do_test temptrigger-1.2.1 { |
||
67 | execsql { INSERT INTO t1 VALUES(1, 2) } |
||
68 | execsql { SELECT * FROM t1 } |
||
69 | } {1 2} |
||
70 | do_test temptrigger-1.2.2 { |
||
71 | execsql { SELECT * FROM tt1 } |
||
72 | } {1 2} |
||
73 | do_test temptrigger-1.2.3 { |
||
74 | execsql { INSERT INTO t1 VALUES(3, 4) } db2 |
||
75 | execsql { SELECT * FROM t1 } |
||
76 | } {1 2 3 4} |
||
77 | do_test temptrigger-1.2.4 { |
||
78 | execsql { SELECT * FROM tt1 } |
||
79 | } {1 2} |
||
80 | |||
81 | # Cause the shared-cache schema to be reloaded. |
||
82 | # |
||
83 | do_test temptrigger-1.3 { |
||
84 | execsql { BEGIN; CREATE TABLE t3(a, b); ROLLBACK; } db2 |
||
85 | } {} |
||
86 | |||
87 | do_test temptrigger-1.4 { |
||
88 | execsql { INSERT INTO t1 VALUES(5, 6) } |
||
89 | execsql { SELECT * FROM tt1 } |
||
90 | } {1 2 5 6} |
||
91 | |||
92 | do_test temptrigger-1.5 { |
||
93 | # Before the bug was fixed, the following 'DROP TRIGGER' hit an |
||
94 | # assert if executed. |
||
95 | #execsql { DROP TRIGGER tr1 } |
||
96 | } {} |
||
97 | |||
98 | catch {db close} |
||
99 | catch {db2 close} |
||
100 | |||
101 | #------------------------------------------------------------------------- |
||
102 | # Tests temptrigger-2.* are similar to temptrigger-1.*, except that |
||
103 | # temptrigger-2.3 simply opens and closes a connection to the shared-cache. |
||
104 | # It does not do anything special to cause the schema to be reloaded. |
||
105 | # |
||
106 | do_test temptrigger-2.1 { |
||
107 | sqlite3 db test.db |
||
108 | execsql { |
||
109 | DELETE FROM t1; |
||
110 | CREATE TEMP TABLE tt1(a, b); |
||
111 | CREATE TEMP TRIGGER tr1 AFTER INSERT ON t1 BEGIN |
||
112 | INSERT INTO tt1 VALUES(new.a, new.b); |
||
113 | END; |
||
114 | } |
||
115 | } {} |
||
116 | do_test temptrigger-2.2 { |
||
117 | execsql { |
||
118 | INSERT INTO t1 VALUES(10, 20); |
||
119 | SELECT * FROM tt1; |
||
120 | } |
||
121 | } {10 20} |
||
122 | do_test temptrigger-2.3 { |
||
123 | sqlite3 db2 test.db |
||
124 | db2 close |
||
125 | } {} |
||
126 | do_test temptrigger-2.4 { |
||
127 | execsql { |
||
128 | INSERT INTO t1 VALUES(30, 40); |
||
129 | SELECT * FROM tt1; |
||
130 | } |
||
131 | } {10 20 30 40} |
||
132 | do_test temptrigger-2.5 { |
||
133 | #execsql { DROP TRIGGER tr1 } |
||
134 | } {} |
||
135 | |||
136 | catch {db close} |
||
137 | catch {db2 close} |
||
138 | sqlite3_enable_shared_cache $::enable_shared_cache |
||
139 | |||
140 | #------------------------------------------------------------------------- |
||
141 | # Test case temptrigger-3.* demonstrates a problem with temp triggers |
||
142 | # on tables located in attached databases. At one point when SQLite reloaded |
||
143 | # the schema of an attached database (because some other connection had |
||
144 | # changed the schema cookie) it was not re-creating temp triggers attached |
||
145 | # to tables located within the attached database. |
||
146 | # |
||
147 | # temptrigger-3.1: Attach database 'test2.db' to connection [db]. Add a |
||
148 | # temp trigger to a table in 'test2.db'. |
||
149 | # |
||
150 | # temptrigger-3.2: Check that the temp trigger is correctly fired. |
||
151 | # |
||
152 | # temptrigger-3.3: Update the schema of 'test2.db' using an external |
||
153 | # connection. This forces [db] to reload the 'test2.db' |
||
154 | # schema. Check that the temp trigger is still fired |
||
155 | # correctly. |
||
156 | # |
||
157 | # temptrigger-3.4: Check that the temp trigger can be dropped without error. |
||
158 | # |
||
159 | do_test temptrigger-3.1 { |
||
160 | catch { file delete -force test2.db test2.db-journal } |
||
161 | catch { file delete -force test.db test.db-journal } |
||
162 | sqlite3 db test.db |
||
163 | sqlite3 db2 test2.db |
||
164 | execsql { CREATE TABLE t2(a, b) } db2 |
||
165 | execsql { |
||
166 | ATTACH 'test2.db' AS aux; |
||
167 | CREATE TEMP TABLE tt2(a, b); |
||
168 | CREATE TEMP TRIGGER tr2 AFTER INSERT ON aux.t2 BEGIN |
||
169 | INSERT INTO tt2 VALUES(new.a, new.b); |
||
170 | END; |
||
171 | } |
||
172 | } {} |
||
173 | |||
174 | do_test temptrigger-3.2.1 { |
||
175 | execsql { |
||
176 | INSERT INTO aux.t2 VALUES(1, 2); |
||
177 | SELECT * FROM aux.t2; |
||
178 | } |
||
179 | } {1 2} |
||
180 | do_test temptrigger-3.2.2 { |
||
181 | execsql { SELECT * FROM tt2 } |
||
182 | } {1 2} |
||
183 | |||
184 | do_test temptrigger-3.3.1 { |
||
185 | execsql { CREATE TABLE t3(a, b) } db2 |
||
186 | execsql { |
||
187 | INSERT INTO aux.t2 VALUES(3, 4); |
||
188 | SELECT * FROM aux.t2; |
||
189 | } |
||
190 | } {1 2 3 4} |
||
191 | do_test temptrigger-3.3.2 { |
||
192 | execsql { SELECT * FROM tt2 } |
||
193 | } {1 2 3 4} |
||
194 | |||
195 | do_test temptrigger-3.4 { |
||
196 | # Before the bug was fixed, the following 'DROP TRIGGER' hit an |
||
197 | # assert if executed. |
||
198 | #execsql { DROP TRIGGER tr2 } |
||
199 | } {} |
||
200 | |||
201 | catch { db close } |
||
202 | catch { db2 close } |
||
203 | |||
204 | finish_test |