AryaWu/sqlite
0
1# The author disclaims copyright to this source code. In place of2# 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 triggers12#13# Note 1: insert into table is now the only statement which changes LIRID14# 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 exits18# Note 4: LIRID is not changed by an insert into a view (since everything19# is done within instead of trigger context)20#21 22set testdir [file dirname $argv0]23source $testdir/tester.tcl24 25# ----------------------------------------------------------------------------26# 1.x - basic tests (no triggers)27 28# LIRID changed properly after an insert into a table29do_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# EVIDENCE-OF: R-47220-63683 The sqlite3_last_insert_rowid() function40# does not work for WITHOUT ROWID tables.41#42do_test lastinsert-1.1w {43 catchsql {44 create table t1w (k integer primary key) WITHOUT ROWID;45 insert into t1w values (123456);46 select last_insert_rowid(); -- returns 3 from above.47 }48} {0 3}49 50# LIRID unchanged after an update on a table51do_test lastinsert-1.2 {52 catchsql {53 update t1 set k=4 where k=2;54 select last_insert_rowid();55 }56} {0 3}57 58# LIRID unchanged after a delete from a table59do_test lastinsert-1.3 {60 catchsql {61 delete from t1 where k=4;62 select last_insert_rowid();63 }64} {0 3}65 66# LIRID unchanged after create table/view statements67do_test lastinsert-1.4.1 {68 catchsql {69 create table t2 (k integer primary key, val1, val2, val3);70 select last_insert_rowid();71 }72} {0 3}73ifcapable view {74do_test lastinsert-1.4.2 {75 catchsql {76 create view v as select * from t1;77 select last_insert_rowid();78 }79} {0 3}80} ;# ifcapable view81 82# All remaining tests involve triggers. Skip them if triggers are not83# supported in this build.84#85ifcapable {!trigger} {86 finish_test87 return88}89 90# ----------------------------------------------------------------------------91# 2.x - tests with after insert trigger92 93# LIRID changed properly after an insert into table containing an after trigger94do_test lastinsert-2.1 {95 catchsql {96 delete from t2;97 create trigger r1 after insert on t1 for each row begin98 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);99 update t2 set k=k+10, val2=100+last_insert_rowid();100 update t2 set val3=1000+last_insert_rowid();101 end;102 insert into t1 values (13);103 select last_insert_rowid();104 }105} {0 13}106 107# LIRID equals NEW.k upon entry into after insert trigger108do_test lastinsert-2.2 {109 catchsql {110 select val1 from t2;111 }112} {0 13}113 114# LIRID changed properly by insert within context of after insert trigger115do_test lastinsert-2.3 {116 catchsql {117 select val2 from t2;118 }119} {0 126}120 121# LIRID unchanged by update within context of after insert trigger122do_test lastinsert-2.4 {123 catchsql {124 select val3 from t2;125 }126} {0 1026}127 128# ----------------------------------------------------------------------------129# 3.x - tests with after update trigger130 131# LIRID not changed after an update onto a table containing an after trigger132do_test lastinsert-3.1 {133 catchsql {134 delete from t2;135 drop trigger r1;136 create trigger r1 after update on t1 for each row begin137 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);138 update t2 set k=k+10, val2=100+last_insert_rowid();139 update t2 set val3=1000+last_insert_rowid();140 end;141 update t1 set k=14 where k=3;142 select last_insert_rowid();143 }144} {0 13}145 146# LIRID unchanged upon entry into after update trigger147do_test lastinsert-3.2 {148 catchsql {149 select val1 from t2;150 }151} {0 13}152 153# LIRID changed properly by insert within context of after update trigger154do_test lastinsert-3.3 {155 catchsql {156 select val2 from t2;157 }158} {0 128}159 160# LIRID unchanged by update within context of after update trigger161do_test lastinsert-3.4 {162 catchsql {163 select val3 from t2;164 }165} {0 1028}166 167# ----------------------------------------------------------------------------168# 4.x - tests with instead of insert trigger169# These may not be run if either views or triggers were disabled at 170# compile-time171 172ifcapable {view && trigger} {173# LIRID not changed after an insert into view containing an instead of trigger174do_test lastinsert-4.1 {175 catchsql {176 delete from t2;177 drop trigger r1;178 create trigger r1 instead of insert on v for each row begin179 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);180 update t2 set k=k+10, val2=100+last_insert_rowid();181 update t2 set val3=1000+last_insert_rowid();182 end;183 insert into v values (15);184 select last_insert_rowid();185 }186} {0 13}187 188# LIRID unchanged upon entry into instead of trigger189do_test lastinsert-4.2 {190 catchsql {191 select val1 from t2;192 }193} {0 13}194 195# LIRID changed properly by insert within context of instead of trigger196do_test lastinsert-4.3 {197 catchsql {198 select val2 from t2;199 }200} {0 130}201 202# LIRID unchanged by update within context of instead of trigger203do_test lastinsert-4.4 {204 catchsql {205 select val3 from t2;206 }207} {0 1030}208} ;# ifcapable (view && trigger)209 210# ----------------------------------------------------------------------------211# 5.x - tests with before delete trigger212 213# LIRID not changed after a delete on a table containing a before trigger214do_test lastinsert-5.1 {215 catchsql {216 drop trigger r1; -- This was not created if views are disabled.217 }218 catchsql {219 delete from t2;220 create trigger r1 before delete on t1 for each row begin221 insert into t2 values (77, last_insert_rowid(), NULL, NULL);222 update t2 set k=k+10, val2=100+last_insert_rowid();223 update t2 set val3=1000+last_insert_rowid();224 end;225 delete from t1 where k=1;226 select last_insert_rowid();227 }228} {0 13}229 230# LIRID unchanged upon entry into delete trigger231do_test lastinsert-5.2 {232 catchsql {233 select val1 from t2;234 }235} {0 13}236 237# LIRID changed properly by insert within context of delete trigger238do_test lastinsert-5.3 {239 catchsql {240 select val2 from t2;241 }242} {0 177}243 244# LIRID unchanged by update within context of delete trigger245do_test lastinsert-5.4 {246 catchsql {247 select val3 from t2;248 }249} {0 1077}250 251# ----------------------------------------------------------------------------252# 6.x - tests with instead of update trigger253# These tests may not run if either views or triggers are disabled.254 255ifcapable {view && trigger} {256# LIRID not changed after an update on a view containing an instead of trigger257do_test lastinsert-6.1 {258 catchsql {259 delete from t2;260 drop trigger r1;261 create trigger r1 instead of update on v for each row begin262 insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);263 update t2 set k=k+10, val2=100+last_insert_rowid();264 update t2 set val3=1000+last_insert_rowid();265 end;266 update v set k=16 where k=14;267 select last_insert_rowid();268 }269} {0 13}270 271# LIRID unchanged upon entry into instead of trigger272do_test lastinsert-6.2 {273 catchsql {274 select val1 from t2;275 }276} {0 13}277 278# LIRID changed properly by insert within context of instead of trigger279do_test lastinsert-6.3 {280 catchsql {281 select val2 from t2;282 }283} {0 132}284 285# LIRID unchanged by update within context of instead of trigger286do_test lastinsert-6.4 {287 catchsql {288 select val3 from t2;289 }290} {0 1032}291} ;# ifcapable (view && trigger)292 293# ----------------------------------------------------------------------------294# 7.x - complex tests with temporary tables and nested instead of triggers295# These do not run if views or triggers are disabled.296 297ifcapable {trigger && view && tempdb} {298do_test lastinsert-7.1 {299 catchsql {300 drop table t1; drop table t2; drop trigger r1;301 create temp table t1 (k integer primary key);302 create temp table t2 (k integer primary key);303 create temp view v1 as select * from t1;304 create temp view v2 as select * from t2;305 create temp table rid (k integer primary key, rin, rout);306 insert into rid values (1, NULL, NULL);307 insert into rid values (2, NULL, NULL);308 create temp trigger r1 instead of insert on v1 for each row begin309 update rid set rin=last_insert_rowid() where k=1;310 insert into t1 values (100+NEW.k);311 insert into v2 values (100+last_insert_rowid());312 update rid set rout=last_insert_rowid() where k=1;313 end;314 create temp trigger r2 instead of insert on v2 for each row begin315 update rid set rin=last_insert_rowid() where k=2;316 insert into t2 values (1000+NEW.k);317 update rid set rout=last_insert_rowid() where k=2;318 end;319 insert into t1 values (77);320 select last_insert_rowid();321 }322} {0 77}323 324do_test lastinsert-7.2 {325 catchsql {326 insert into v1 values (5);327 select last_insert_rowid();328 }329} {0 77}330 331do_test lastinsert-7.3 {332 catchsql {333 select rin from rid where k=1;334 }335} {0 77}336 337do_test lastinsert-7.4 {338 catchsql {339 select rout from rid where k=1;340 }341} {0 105}342 343do_test lastinsert-7.5 {344 catchsql {345 select rin from rid where k=2;346 }347} {0 105}348 349do_test lastinsert-7.6 {350 catchsql {351 select rout from rid where k=2;352 }353} {0 1205}354 355do_test lastinsert-8.1 {356 db close357 sqlite3 db test.db358 execsql {359 CREATE TABLE t2(x INTEGER PRIMARY KEY, y);360 CREATE TABLE t3(a, b);361 CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN362 INSERT INTO t3 VALUES(new.x, new.y);363 END;364 INSERT INTO t2 VALUES(5000000000, 1);365 SELECT last_insert_rowid();366 }367} 5000000000368 369do_test lastinsert-9.1 {370 db eval {INSERT INTO t2 VALUES(123456789012345,0)}371 db last_insert_rowid372} {123456789012345}373 374 375} ;# ifcapable (view && trigger)376 377finish_test378