000001 # The author disclaims copyright to this source code. In place of
000002 # a legal notice, here is a blessing:
000003 #
000004 # May you do good and not evil.
000005 # May you find forgiveness for yourself and forgive others.
000006 # May you share freely, never taking more than you give.
000007 #
000008 #***********************************************************************
000009 #
000010 # Tests to make sure that value returned by last_insert_rowid() (LIRID)
000011 # is updated properly, especially inside triggers
000012 #
000013 # Note 1: insert into table is now the only statement which changes LIRID
000014 # Note 2: upon entry into before or instead of triggers,
000015 # LIRID is unchanged (rather than -1)
000016 # Note 3: LIRID is changed within the context of a trigger,
000017 # but is restored once the trigger exits
000018 # Note 4: LIRID is not changed by an insert into a view (since everything
000019 # is done within instead of trigger context)
000020 #
000021
000022 set testdir [file dirname $argv0]
000023 source $testdir/tester.tcl
000024
000025 # ----------------------------------------------------------------------------
000026 # 1.x - basic tests (no triggers)
000027
000028 # LIRID changed properly after an insert into a table
000029 do_test lastinsert-1.1 {
000030 catchsql {
000031 create table t1 (k integer primary key);
000032 insert into t1 values (1);
000033 insert into t1 values (NULL);
000034 insert into t1 values (NULL);
000035 select last_insert_rowid();
000036 }
000037 } {0 3}
000038
000039 # EVIDENCE-OF: R-47220-63683 The sqlite3_last_insert_rowid() function
000040 # does not work for WITHOUT ROWID tables.
000041 #
000042 do_test lastinsert-1.1w {
000043 catchsql {
000044 create table t1w (k integer primary key) WITHOUT ROWID;
000045 insert into t1w values (123456);
000046 select last_insert_rowid(); -- returns 3 from above.
000047 }
000048 } {0 3}
000049
000050 # LIRID unchanged after an update on a table
000051 do_test lastinsert-1.2 {
000052 catchsql {
000053 update t1 set k=4 where k=2;
000054 select last_insert_rowid();
000055 }
000056 } {0 3}
000057
000058 # LIRID unchanged after a delete from a table
000059 do_test lastinsert-1.3 {
000060 catchsql {
000061 delete from t1 where k=4;
000062 select last_insert_rowid();
000063 }
000064 } {0 3}
000065
000066 # LIRID unchanged after create table/view statements
000067 do_test lastinsert-1.4.1 {
000068 catchsql {
000069 create table t2 (k integer primary key, val1, val2, val3);
000070 select last_insert_rowid();
000071 }
000072 } {0 3}
000073 ifcapable view {
000074 do_test lastinsert-1.4.2 {
000075 catchsql {
000076 create view v as select * from t1;
000077 select last_insert_rowid();
000078 }
000079 } {0 3}
000080 } ;# ifcapable view
000081
000082 # All remaining tests involve triggers. Skip them if triggers are not
000083 # supported in this build.
000084 #
000085 ifcapable {!trigger} {
000086 finish_test
000087 return
000088 }
000089
000090 # ----------------------------------------------------------------------------
000091 # 2.x - tests with after insert trigger
000092
000093 # LIRID changed properly after an insert into table containing an after trigger
000094 do_test lastinsert-2.1 {
000095 catchsql {
000096 delete from t2;
000097 create trigger r1 after insert on t1 for each row begin
000098 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
000099 update t2 set k=k+10, val2=100+last_insert_rowid();
000100 update t2 set val3=1000+last_insert_rowid();
000101 end;
000102 insert into t1 values (13);
000103 select last_insert_rowid();
000104 }
000105 } {0 13}
000106
000107 # LIRID equals NEW.k upon entry into after insert trigger
000108 do_test lastinsert-2.2 {
000109 catchsql {
000110 select val1 from t2;
000111 }
000112 } {0 13}
000113
000114 # LIRID changed properly by insert within context of after insert trigger
000115 do_test lastinsert-2.3 {
000116 catchsql {
000117 select val2 from t2;
000118 }
000119 } {0 126}
000120
000121 # LIRID unchanged by update within context of after insert trigger
000122 do_test lastinsert-2.4 {
000123 catchsql {
000124 select val3 from t2;
000125 }
000126 } {0 1026}
000127
000128 # ----------------------------------------------------------------------------
000129 # 3.x - tests with after update trigger
000130
000131 # LIRID not changed after an update onto a table containing an after trigger
000132 do_test lastinsert-3.1 {
000133 catchsql {
000134 delete from t2;
000135 drop trigger r1;
000136 create trigger r1 after update on t1 for each row begin
000137 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
000138 update t2 set k=k+10, val2=100+last_insert_rowid();
000139 update t2 set val3=1000+last_insert_rowid();
000140 end;
000141 update t1 set k=14 where k=3;
000142 select last_insert_rowid();
000143 }
000144 } {0 13}
000145
000146 # LIRID unchanged upon entry into after update trigger
000147 do_test lastinsert-3.2 {
000148 catchsql {
000149 select val1 from t2;
000150 }
000151 } {0 13}
000152
000153 # LIRID changed properly by insert within context of after update trigger
000154 do_test lastinsert-3.3 {
000155 catchsql {
000156 select val2 from t2;
000157 }
000158 } {0 128}
000159
000160 # LIRID unchanged by update within context of after update trigger
000161 do_test lastinsert-3.4 {
000162 catchsql {
000163 select val3 from t2;
000164 }
000165 } {0 1028}
000166
000167 # ----------------------------------------------------------------------------
000168 # 4.x - tests with instead of insert trigger
000169 # These may not be run if either views or triggers were disabled at
000170 # compile-time
000171
000172 ifcapable {view && trigger} {
000173 # LIRID not changed after an insert into view containing an instead of trigger
000174 do_test lastinsert-4.1 {
000175 catchsql {
000176 delete from t2;
000177 drop trigger r1;
000178 create trigger r1 instead of insert on v for each row begin
000179 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
000180 update t2 set k=k+10, val2=100+last_insert_rowid();
000181 update t2 set val3=1000+last_insert_rowid();
000182 end;
000183 insert into v values (15);
000184 select last_insert_rowid();
000185 }
000186 } {0 13}
000187
000188 # LIRID unchanged upon entry into instead of trigger
000189 do_test lastinsert-4.2 {
000190 catchsql {
000191 select val1 from t2;
000192 }
000193 } {0 13}
000194
000195 # LIRID changed properly by insert within context of instead of trigger
000196 do_test lastinsert-4.3 {
000197 catchsql {
000198 select val2 from t2;
000199 }
000200 } {0 130}
000201
000202 # LIRID unchanged by update within context of instead of trigger
000203 do_test lastinsert-4.4 {
000204 catchsql {
000205 select val3 from t2;
000206 }
000207 } {0 1030}
000208 } ;# ifcapable (view && trigger)
000209
000210 # ----------------------------------------------------------------------------
000211 # 5.x - tests with before delete trigger
000212
000213 # LIRID not changed after a delete on a table containing a before trigger
000214 do_test lastinsert-5.1 {
000215 catchsql {
000216 drop trigger r1; -- This was not created if views are disabled.
000217 }
000218 catchsql {
000219 delete from t2;
000220 create trigger r1 before delete on t1 for each row begin
000221 insert into t2 values (77, last_insert_rowid(), NULL, NULL);
000222 update t2 set k=k+10, val2=100+last_insert_rowid();
000223 update t2 set val3=1000+last_insert_rowid();
000224 end;
000225 delete from t1 where k=1;
000226 select last_insert_rowid();
000227 }
000228 } {0 13}
000229
000230 # LIRID unchanged upon entry into delete trigger
000231 do_test lastinsert-5.2 {
000232 catchsql {
000233 select val1 from t2;
000234 }
000235 } {0 13}
000236
000237 # LIRID changed properly by insert within context of delete trigger
000238 do_test lastinsert-5.3 {
000239 catchsql {
000240 select val2 from t2;
000241 }
000242 } {0 177}
000243
000244 # LIRID unchanged by update within context of delete trigger
000245 do_test lastinsert-5.4 {
000246 catchsql {
000247 select val3 from t2;
000248 }
000249 } {0 1077}
000250
000251 # ----------------------------------------------------------------------------
000252 # 6.x - tests with instead of update trigger
000253 # These tests may not run if either views or triggers are disabled.
000254
000255 ifcapable {view && trigger} {
000256 # LIRID not changed after an update on a view containing an instead of trigger
000257 do_test lastinsert-6.1 {
000258 catchsql {
000259 delete from t2;
000260 drop trigger r1;
000261 create trigger r1 instead of update on v for each row begin
000262 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
000263 update t2 set k=k+10, val2=100+last_insert_rowid();
000264 update t2 set val3=1000+last_insert_rowid();
000265 end;
000266 update v set k=16 where k=14;
000267 select last_insert_rowid();
000268 }
000269 } {0 13}
000270
000271 # LIRID unchanged upon entry into instead of trigger
000272 do_test lastinsert-6.2 {
000273 catchsql {
000274 select val1 from t2;
000275 }
000276 } {0 13}
000277
000278 # LIRID changed properly by insert within context of instead of trigger
000279 do_test lastinsert-6.3 {
000280 catchsql {
000281 select val2 from t2;
000282 }
000283 } {0 132}
000284
000285 # LIRID unchanged by update within context of instead of trigger
000286 do_test lastinsert-6.4 {
000287 catchsql {
000288 select val3 from t2;
000289 }
000290 } {0 1032}
000291 } ;# ifcapable (view && trigger)
000292
000293 # ----------------------------------------------------------------------------
000294 # 7.x - complex tests with temporary tables and nested instead of triggers
000295 # These do not run if views or triggers are disabled.
000296
000297 ifcapable {trigger && view && tempdb} {
000298 do_test lastinsert-7.1 {
000299 catchsql {
000300 drop table t1; drop table t2; drop trigger r1;
000301 create temp table t1 (k integer primary key);
000302 create temp table t2 (k integer primary key);
000303 create temp view v1 as select * from t1;
000304 create temp view v2 as select * from t2;
000305 create temp table rid (k integer primary key, rin, rout);
000306 insert into rid values (1, NULL, NULL);
000307 insert into rid values (2, NULL, NULL);
000308 create temp trigger r1 instead of insert on v1 for each row begin
000309 update rid set rin=last_insert_rowid() where k=1;
000310 insert into t1 values (100+NEW.k);
000311 insert into v2 values (100+last_insert_rowid());
000312 update rid set rout=last_insert_rowid() where k=1;
000313 end;
000314 create temp trigger r2 instead of insert on v2 for each row begin
000315 update rid set rin=last_insert_rowid() where k=2;
000316 insert into t2 values (1000+NEW.k);
000317 update rid set rout=last_insert_rowid() where k=2;
000318 end;
000319 insert into t1 values (77);
000320 select last_insert_rowid();
000321 }
000322 } {0 77}
000323
000324 do_test lastinsert-7.2 {
000325 catchsql {
000326 insert into v1 values (5);
000327 select last_insert_rowid();
000328 }
000329 } {0 77}
000330
000331 do_test lastinsert-7.3 {
000332 catchsql {
000333 select rin from rid where k=1;
000334 }
000335 } {0 77}
000336
000337 do_test lastinsert-7.4 {
000338 catchsql {
000339 select rout from rid where k=1;
000340 }
000341 } {0 105}
000342
000343 do_test lastinsert-7.5 {
000344 catchsql {
000345 select rin from rid where k=2;
000346 }
000347 } {0 105}
000348
000349 do_test lastinsert-7.6 {
000350 catchsql {
000351 select rout from rid where k=2;
000352 }
000353 } {0 1205}
000354
000355 do_test lastinsert-8.1 {
000356 db close
000357 sqlite3 db test.db
000358 execsql {
000359 CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
000360 CREATE TABLE t3(a, b);
000361 CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN
000362 INSERT INTO t3 VALUES(new.x, new.y);
000363 END;
000364 INSERT INTO t2 VALUES(5000000000, 1);
000365 SELECT last_insert_rowid();
000366 }
000367 } 5000000000
000368
000369 do_test lastinsert-9.1 {
000370 db eval {INSERT INTO t2 VALUES(123456789012345,0)}
000371 db last_insert_rowid
000372 } {123456789012345}
000373
000374
000375 } ;# ifcapable (view && trigger)
000376
000377 finish_test