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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to lastinsert.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test