wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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