wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # The author disclaims copyright to this source code. In place of |
2 | # a legal notice, here is a blessing: |
||
3 | # |
||
4 | # May you do good and not evil. |
||
5 | # May you find forgiveness for yourself and forgive others. |
||
6 | # May you share freely, never taking more than you give. |
||
7 | # |
||
8 | #*********************************************************************** |
||
9 | # |
||
10 | # Tests to make sure that value returned by last_insert_rowid() (LIRID) |
||
11 | # is updated properly, especially inside triggers |
||
12 | # |
||
13 | # Note 1: insert into table is now the only statement which changes LIRID |
||
14 | # Note 2: upon entry into before or instead of triggers, |
||
15 | # LIRID is unchanged (rather than -1) |
||
16 | # Note 3: LIRID is changed within the context of a trigger, |
||
17 | # but is restored once the trigger exits |
||
18 | # Note 4: LIRID is not changed by an insert into a view (since everything |
||
19 | # is done within instead of trigger context) |
||
20 | # |
||
21 | |||
22 | set testdir [file dirname $argv0] |
||
23 | source $testdir/tester.tcl |
||
24 | |||
25 | # ---------------------------------------------------------------------------- |
||
26 | # 1.x - basic tests (no triggers) |
||
27 | |||
28 | # LIRID changed properly after an insert into a table |
||
29 | do_test lastinsert-1.1 { |
||
30 | catchsql { |
||
31 | create table t1 (k integer primary key); |
||
32 | insert into t1 values (1); |
||
33 | insert into t1 values (NULL); |
||
34 | insert into t1 values (NULL); |
||
35 | select last_insert_rowid(); |
||
36 | } |
||
37 | } {0 3} |
||
38 | |||
39 | # LIRID unchanged after an update on a table |
||
40 | do_test lastinsert-1.2 { |
||
41 | catchsql { |
||
42 | update t1 set k=4 where k=2; |
||
43 | select last_insert_rowid(); |
||
44 | } |
||
45 | } {0 3} |
||
46 | |||
47 | # LIRID unchanged after a delete from a table |
||
48 | do_test lastinsert-1.3 { |
||
49 | catchsql { |
||
50 | delete from t1 where k=4; |
||
51 | select last_insert_rowid(); |
||
52 | } |
||
53 | } {0 3} |
||
54 | |||
55 | # LIRID unchanged after create table/view statements |
||
56 | do_test lastinsert-1.4.1 { |
||
57 | catchsql { |
||
58 | create table t2 (k integer primary key, val1, val2, val3); |
||
59 | select last_insert_rowid(); |
||
60 | } |
||
61 | } {0 3} |
||
62 | ifcapable view { |
||
63 | do_test lastinsert-1.4.2 { |
||
64 | catchsql { |
||
65 | create view v as select * from t1; |
||
66 | select last_insert_rowid(); |
||
67 | } |
||
68 | } {0 3} |
||
69 | } ;# ifcapable view |
||
70 | |||
71 | # All remaining tests involve triggers. Skip them if triggers are not |
||
72 | # supported in this build. |
||
73 | # |
||
74 | ifcapable {!trigger} { |
||
75 | finish_test |
||
76 | return |
||
77 | } |
||
78 | |||
79 | # ---------------------------------------------------------------------------- |
||
80 | # 2.x - tests with after insert trigger |
||
81 | |||
82 | # LIRID changed properly after an insert into table containing an after trigger |
||
83 | do_test lastinsert-2.1 { |
||
84 | catchsql { |
||
85 | delete from t2; |
||
86 | create trigger r1 after insert on t1 for each row begin |
||
87 | insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); |
||
88 | update t2 set k=k+10, val2=100+last_insert_rowid(); |
||
89 | update t2 set val3=1000+last_insert_rowid(); |
||
90 | end; |
||
91 | insert into t1 values (13); |
||
92 | select last_insert_rowid(); |
||
93 | } |
||
94 | } {0 13} |
||
95 | |||
96 | # LIRID equals NEW.k upon entry into after insert trigger |
||
97 | do_test lastinsert-2.2 { |
||
98 | catchsql { |
||
99 | select val1 from t2; |
||
100 | } |
||
101 | } {0 13} |
||
102 | |||
103 | # LIRID changed properly by insert within context of after insert trigger |
||
104 | do_test lastinsert-2.3 { |
||
105 | catchsql { |
||
106 | select val2 from t2; |
||
107 | } |
||
108 | } {0 126} |
||
109 | |||
110 | # LIRID unchanged by update within context of after insert trigger |
||
111 | do_test lastinsert-2.4 { |
||
112 | catchsql { |
||
113 | select val3 from t2; |
||
114 | } |
||
115 | } {0 1026} |
||
116 | |||
117 | # ---------------------------------------------------------------------------- |
||
118 | # 3.x - tests with after update trigger |
||
119 | |||
120 | # LIRID not changed after an update onto a table containing an after trigger |
||
121 | do_test lastinsert-3.1 { |
||
122 | catchsql { |
||
123 | delete from t2; |
||
124 | drop trigger r1; |
||
125 | create trigger r1 after update on t1 for each row begin |
||
126 | insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); |
||
127 | update t2 set k=k+10, val2=100+last_insert_rowid(); |
||
128 | update t2 set val3=1000+last_insert_rowid(); |
||
129 | end; |
||
130 | update t1 set k=14 where k=3; |
||
131 | select last_insert_rowid(); |
||
132 | } |
||
133 | } {0 13} |
||
134 | |||
135 | # LIRID unchanged upon entry into after update trigger |
||
136 | do_test lastinsert-3.2 { |
||
137 | catchsql { |
||
138 | select val1 from t2; |
||
139 | } |
||
140 | } {0 13} |
||
141 | |||
142 | # LIRID changed properly by insert within context of after update trigger |
||
143 | do_test lastinsert-3.3 { |
||
144 | catchsql { |
||
145 | select val2 from t2; |
||
146 | } |
||
147 | } {0 128} |
||
148 | |||
149 | # LIRID unchanged by update within context of after update trigger |
||
150 | do_test lastinsert-3.4 { |
||
151 | catchsql { |
||
152 | select val3 from t2; |
||
153 | } |
||
154 | } {0 1028} |
||
155 | |||
156 | # ---------------------------------------------------------------------------- |
||
157 | # 4.x - tests with instead of insert trigger |
||
158 | # These may not be run if either views or triggers were disabled at |
||
159 | # compile-time |
||
160 | |||
161 | ifcapable {view && trigger} { |
||
162 | # LIRID not changed after an insert into view containing an instead of trigger |
||
163 | do_test lastinsert-4.1 { |
||
164 | catchsql { |
||
165 | delete from t2; |
||
166 | drop trigger r1; |
||
167 | create trigger r1 instead of insert on v for each row begin |
||
168 | insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); |
||
169 | update t2 set k=k+10, val2=100+last_insert_rowid(); |
||
170 | update t2 set val3=1000+last_insert_rowid(); |
||
171 | end; |
||
172 | insert into v values (15); |
||
173 | select last_insert_rowid(); |
||
174 | } |
||
175 | } {0 13} |
||
176 | |||
177 | # LIRID unchanged upon entry into instead of trigger |
||
178 | do_test lastinsert-4.2 { |
||
179 | catchsql { |
||
180 | select val1 from t2; |
||
181 | } |
||
182 | } {0 13} |
||
183 | |||
184 | # LIRID changed properly by insert within context of instead of trigger |
||
185 | do_test lastinsert-4.3 { |
||
186 | catchsql { |
||
187 | select val2 from t2; |
||
188 | } |
||
189 | } {0 130} |
||
190 | |||
191 | # LIRID unchanged by update within context of instead of trigger |
||
192 | do_test lastinsert-4.4 { |
||
193 | catchsql { |
||
194 | select val3 from t2; |
||
195 | } |
||
196 | } {0 1030} |
||
197 | } ;# ifcapable (view && trigger) |
||
198 | |||
199 | # ---------------------------------------------------------------------------- |
||
200 | # 5.x - tests with before delete trigger |
||
201 | |||
202 | # LIRID not changed after a delete on a table containing a before trigger |
||
203 | do_test lastinsert-5.1 { |
||
204 | catchsql { |
||
205 | drop trigger r1; -- This was not created if views are disabled. |
||
206 | } |
||
207 | catchsql { |
||
208 | delete from t2; |
||
209 | create trigger r1 before delete on t1 for each row begin |
||
210 | insert into t2 values (77, last_insert_rowid(), NULL, NULL); |
||
211 | update t2 set k=k+10, val2=100+last_insert_rowid(); |
||
212 | update t2 set val3=1000+last_insert_rowid(); |
||
213 | end; |
||
214 | delete from t1 where k=1; |
||
215 | select last_insert_rowid(); |
||
216 | } |
||
217 | } {0 13} |
||
218 | |||
219 | # LIRID unchanged upon entry into delete trigger |
||
220 | do_test lastinsert-5.2 { |
||
221 | catchsql { |
||
222 | select val1 from t2; |
||
223 | } |
||
224 | } {0 13} |
||
225 | |||
226 | # LIRID changed properly by insert within context of delete trigger |
||
227 | do_test lastinsert-5.3 { |
||
228 | catchsql { |
||
229 | select val2 from t2; |
||
230 | } |
||
231 | } {0 177} |
||
232 | |||
233 | # LIRID unchanged by update within context of delete trigger |
||
234 | do_test lastinsert-5.4 { |
||
235 | catchsql { |
||
236 | select val3 from t2; |
||
237 | } |
||
238 | } {0 1077} |
||
239 | |||
240 | # ---------------------------------------------------------------------------- |
||
241 | # 6.x - tests with instead of update trigger |
||
242 | # These tests may not run if either views or triggers are disabled. |
||
243 | |||
244 | ifcapable {view && trigger} { |
||
245 | # LIRID not changed after an update on a view containing an instead of trigger |
||
246 | do_test lastinsert-6.1 { |
||
247 | catchsql { |
||
248 | delete from t2; |
||
249 | drop trigger r1; |
||
250 | create trigger r1 instead of update on v for each row begin |
||
251 | insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL); |
||
252 | update t2 set k=k+10, val2=100+last_insert_rowid(); |
||
253 | update t2 set val3=1000+last_insert_rowid(); |
||
254 | end; |
||
255 | update v set k=16 where k=14; |
||
256 | select last_insert_rowid(); |
||
257 | } |
||
258 | } {0 13} |
||
259 | |||
260 | # LIRID unchanged upon entry into instead of trigger |
||
261 | do_test lastinsert-6.2 { |
||
262 | catchsql { |
||
263 | select val1 from t2; |
||
264 | } |
||
265 | } {0 13} |
||
266 | |||
267 | # LIRID changed properly by insert within context of instead of trigger |
||
268 | do_test lastinsert-6.3 { |
||
269 | catchsql { |
||
270 | select val2 from t2; |
||
271 | } |
||
272 | } {0 132} |
||
273 | |||
274 | # LIRID unchanged by update within context of instead of trigger |
||
275 | do_test lastinsert-6.4 { |
||
276 | catchsql { |
||
277 | select val3 from t2; |
||
278 | } |
||
279 | } {0 1032} |
||
280 | } ;# ifcapable (view && trigger) |
||
281 | |||
282 | # ---------------------------------------------------------------------------- |
||
283 | # 7.x - complex tests with temporary tables and nested instead of triggers |
||
284 | # These do not run if views or triggers are disabled. |
||
285 | |||
286 | ifcapable {trigger && view && tempdb} { |
||
287 | do_test lastinsert-7.1 { |
||
288 | catchsql { |
||
289 | drop table t1; drop table t2; drop trigger r1; |
||
290 | create temp table t1 (k integer primary key); |
||
291 | create temp table t2 (k integer primary key); |
||
292 | create temp view v1 as select * from t1; |
||
293 | create temp view v2 as select * from t2; |
||
294 | create temp table rid (k integer primary key, rin, rout); |
||
295 | insert into rid values (1, NULL, NULL); |
||
296 | insert into rid values (2, NULL, NULL); |
||
297 | create temp trigger r1 instead of insert on v1 for each row begin |
||
298 | update rid set rin=last_insert_rowid() where k=1; |
||
299 | insert into t1 values (100+NEW.k); |
||
300 | insert into v2 values (100+last_insert_rowid()); |
||
301 | update rid set rout=last_insert_rowid() where k=1; |
||
302 | end; |
||
303 | create temp trigger r2 instead of insert on v2 for each row begin |
||
304 | update rid set rin=last_insert_rowid() where k=2; |
||
305 | insert into t2 values (1000+NEW.k); |
||
306 | update rid set rout=last_insert_rowid() where k=2; |
||
307 | end; |
||
308 | insert into t1 values (77); |
||
309 | select last_insert_rowid(); |
||
310 | } |
||
311 | } {0 77} |
||
312 | |||
313 | do_test lastinsert-7.2 { |
||
314 | catchsql { |
||
315 | insert into v1 values (5); |
||
316 | select last_insert_rowid(); |
||
317 | } |
||
318 | } {0 77} |
||
319 | |||
320 | do_test lastinsert-7.3 { |
||
321 | catchsql { |
||
322 | select rin from rid where k=1; |
||
323 | } |
||
324 | } {0 77} |
||
325 | |||
326 | do_test lastinsert-7.4 { |
||
327 | catchsql { |
||
328 | select rout from rid where k=1; |
||
329 | } |
||
330 | } {0 105} |
||
331 | |||
332 | do_test lastinsert-7.5 { |
||
333 | catchsql { |
||
334 | select rin from rid where k=2; |
||
335 | } |
||
336 | } {0 105} |
||
337 | |||
338 | do_test lastinsert-7.6 { |
||
339 | catchsql { |
||
340 | select rout from rid where k=2; |
||
341 | } |
||
342 | } {0 1205} |
||
343 | |||
344 | do_test lastinsert-8.1 { |
||
345 | db close |
||
346 | sqlite3 db test.db |
||
347 | execsql { |
||
348 | CREATE TABLE t2(x INTEGER PRIMARY KEY, y); |
||
349 | CREATE TABLE t3(a, b); |
||
350 | CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN |
||
351 | INSERT INTO t3 VALUES(new.x, new.y); |
||
352 | END; |
||
353 | INSERT INTO t2 VALUES(5000000000, 1); |
||
354 | SELECT last_insert_rowid(); |
||
355 | } |
||
356 | } 5000000000 |
||
357 | |||
358 | do_test lastinsert-9.1 { |
||
359 | db eval {INSERT INTO t2 VALUES(123456789012345,0)} |
||
360 | db last_insert_rowid |
||
361 | } {123456789012345} |
||
362 | |||
363 | |||
364 | } ;# ifcapable (view && trigger) |
||
365 | |||
366 | finish_test |