# The author disclaims copyright to this source code.  In place of

 # a legal notice, here is a blessing:

 #

 #    May you do good and not evil.

 #    May you find forgiveness for yourself and forgive others.

 #    May you share freely, never taking more than you give.

 #

 #***********************************************************************

 #

 # Tests to make sure that value returned by last_insert_rowid() (LIRID)

 # is updated properly, especially inside triggers

 #

 # Note 1: insert into table is now the only statement which changes LIRID

 # Note 2: upon entry into before or instead of triggers,

 #           LIRID is unchanged (rather than -1)

 # Note 3: LIRID is changed within the context of a trigger,

 #           but is restored once the trigger exits

 # Note 4: LIRID is not changed by an insert into a view (since everything

 #           is done within instead of trigger context)

 #

 set testdir [file dirname $argv0]

 source $testdir/tester.tcl

 # ----------------------------------------------------------------------------

 # 1.x - basic tests (no triggers)

 # LIRID changed properly after an insert into a table

 do_test lastinsert-1.1 {

     catchsql {

         create table t1 (k integer primary key);

         insert into t1 values (1);

         insert into t1 values (NULL);

         insert into t1 values (NULL);

         select last_insert_rowid();

     }

 } {0 3}

 # LIRID unchanged after an update on a table

 do_test lastinsert-1.2 {

     catchsql {

         update t1 set k=4 where k=2;

         select last_insert_rowid();

     }

 } {0 3}

 # LIRID unchanged after a delete from a table

 do_test lastinsert-1.3 {

     catchsql {

         delete from t1 where k=4;

         select last_insert_rowid();

     }

 } {0 3}

 # LIRID unchanged after create table/view statements

 do_test lastinsert-1.4.1 {

     catchsql {

         create table t2 (k integer primary key, val1, val2, val3);

         select last_insert_rowid();

     }

 } {0 3}

 ifcapable view {

 do_test lastinsert-1.4.2 {

     catchsql {

         create view v as select * from t1;

         select last_insert_rowid();

     }

 } {0 3}

 } ;# ifcapable view

 # All remaining tests involve triggers.  Skip them if triggers are not

 # supported in this build.

 #

 ifcapable {!trigger} {

   finish_test

   return

 }

 # ----------------------------------------------------------------------------

 # 2.x - tests with after insert trigger

 # LIRID changed properly after an insert into table containing an after trigger

 do_test lastinsert-2.1 {

     catchsql {

         delete from t2;

         create trigger r1 after insert on t1 for each row begin

             insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);

             update t2 set k=k+10, val2=100+last_insert_rowid();

             update t2 set val3=1000+last_insert_rowid();

         end;

         insert into t1 values (13);

         select last_insert_rowid();

     }

 } {0 13}

 # LIRID equals NEW.k upon entry into after insert trigger

 do_test lastinsert-2.2 {

     catchsql {

         select val1 from t2;

     }

 } {0 13}

 # LIRID changed properly by insert within context of after insert trigger

 do_test lastinsert-2.3 {

     catchsql {

         select val2 from t2;

     }

 } {0 126}

 # LIRID unchanged by update within context of after insert trigger

 do_test lastinsert-2.4 {

     catchsql {

         select val3 from t2;

     }

 } {0 1026}

 # ----------------------------------------------------------------------------

 # 3.x - tests with after update trigger

 # LIRID not changed after an update onto a table containing an after trigger

 do_test lastinsert-3.1 {

     catchsql {

         delete from t2;

         drop trigger r1;

         create trigger r1 after update on t1 for each row begin

             insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);

             update t2 set k=k+10, val2=100+last_insert_rowid();

             update t2 set val3=1000+last_insert_rowid();

         end;

         update t1 set k=14 where k=3;

         select last_insert_rowid();

     }

 } {0 13}

 # LIRID unchanged upon entry into after update trigger

 do_test lastinsert-3.2 {

     catchsql {

         select val1 from t2;

     }

 } {0 13}

 # LIRID changed properly by insert within context of after update trigger

 do_test lastinsert-3.3 {

     catchsql {

         select val2 from t2;

     }

 } {0 128}

 # LIRID unchanged by update within context of after update trigger

 do_test lastinsert-3.4 {

     catchsql {

         select val3 from t2;

     }

 } {0 1028}

 # ----------------------------------------------------------------------------

 # 4.x - tests with instead of insert trigger

 # These may not be run if either views or triggers were disabled at 

 # compile-time

 ifcapable {view && trigger} {

 # LIRID not changed after an insert into view containing an instead of trigger

 do_test lastinsert-4.1 {

     catchsql {

         delete from t2;

         drop trigger r1;

         create trigger r1 instead of insert on v for each row begin

             insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);

             update t2 set k=k+10, val2=100+last_insert_rowid();

             update t2 set val3=1000+last_insert_rowid();

         end;

         insert into v values (15);

         select last_insert_rowid();

     }

 } {0 13}

 # LIRID unchanged upon entry into instead of trigger

 do_test lastinsert-4.2 {

     catchsql {

         select val1 from t2;

     }

 } {0 13}

 # LIRID changed properly by insert within context of instead of trigger

 do_test lastinsert-4.3 {

     catchsql {

         select val2 from t2;

     }

 } {0 130}

 # LIRID unchanged by update within context of instead of trigger

 do_test lastinsert-4.4 {

     catchsql {

         select val3 from t2;

     }

 } {0 1030}

 } ;# ifcapable (view && trigger)

 # ----------------------------------------------------------------------------

 # 5.x - tests with before delete trigger

 # LIRID not changed after a delete on a table containing a before trigger

 do_test lastinsert-5.1 {

     catchsql {

       drop trigger r1;  -- This was not created if views are disabled.

     }

     catchsql {

         delete from t2;

         create trigger r1 before delete on t1 for each row begin

             insert into t2 values (77, last_insert_rowid(), NULL, NULL);

             update t2 set k=k+10, val2=100+last_insert_rowid();

             update t2 set val3=1000+last_insert_rowid();

         end;

         delete from t1 where k=1;

         select last_insert_rowid();

     }

 } {0 13}

 # LIRID unchanged upon entry into delete trigger

 do_test lastinsert-5.2 {

     catchsql {

         select val1 from t2;

     }

 } {0 13}

 # LIRID changed properly by insert within context of delete trigger

 do_test lastinsert-5.3 {

     catchsql {

         select val2 from t2;

     }

 } {0 177}

 # LIRID unchanged by update within context of delete trigger

 do_test lastinsert-5.4 {

     catchsql {

         select val3 from t2;

     }

 } {0 1077}

 # ----------------------------------------------------------------------------

 # 6.x - tests with instead of update trigger

 # These tests may not run if either views or triggers are disabled.

 ifcapable {view && trigger} {

 # LIRID not changed after an update on a view containing an instead of trigger

 do_test lastinsert-6.1 {

     catchsql {

         delete from t2;

         drop trigger r1;

         create trigger r1 instead of update on v for each row begin

             insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);

             update t2 set k=k+10, val2=100+last_insert_rowid();

             update t2 set val3=1000+last_insert_rowid();

         end;

         update v set k=16 where k=14;

         select last_insert_rowid();

     }

 } {0 13}

 # LIRID unchanged upon entry into instead of trigger

 do_test lastinsert-6.2 {

     catchsql {

         select val1 from t2;

     }

 } {0 13}

 # LIRID changed properly by insert within context of instead of trigger

 do_test lastinsert-6.3 {

     catchsql {

         select val2 from t2;

     }

 } {0 132}

 # LIRID unchanged by update within context of instead of trigger

 do_test lastinsert-6.4 {

     catchsql {

         select val3 from t2;

     }

 } {0 1032}

 } ;# ifcapable (view && trigger)

 # ----------------------------------------------------------------------------

 # 7.x - complex tests with temporary tables and nested instead of triggers

 # These do not run if views or triggers are disabled.

 ifcapable {trigger && view && tempdb} {

 do_test lastinsert-7.1 {

     catchsql {

         drop table t1; drop table t2; drop trigger r1;

         create temp table t1 (k integer primary key);

         create temp table t2 (k integer primary key);

         create temp view v1 as select * from t1;

         create temp view v2 as select * from t2;

         create temp table rid (k integer primary key, rin, rout);

         insert into rid values (1, NULL, NULL);

         insert into rid values (2, NULL, NULL);

         create temp trigger r1 instead of insert on v1 for each row begin

             update rid set rin=last_insert_rowid() where k=1;

             insert into t1 values (100+NEW.k);

             insert into v2 values (100+last_insert_rowid());

             update rid set rout=last_insert_rowid() where k=1;

         end;

         create temp trigger r2 instead of insert on v2 for each row begin

             update rid set rin=last_insert_rowid() where k=2;

             insert into t2 values (1000+NEW.k);

             update rid set rout=last_insert_rowid() where k=2;

         end;

         insert into t1 values (77);

         select last_insert_rowid();

     }

 } {0 77}

 do_test lastinsert-7.2 {

     catchsql {

         insert into v1 values (5);

         select last_insert_rowid();

     }

 } {0 77}

 do_test lastinsert-7.3 {

     catchsql {

         select rin from rid where k=1;

     }

 } {0 77}

 do_test lastinsert-7.4 {

     catchsql {

         select rout from rid where k=1;

     }

 } {0 105}

 do_test lastinsert-7.5 {

     catchsql {

         select rin from rid where k=2;

     }

 } {0 105}

 do_test lastinsert-7.6 {

     catchsql {

         select rout from rid where k=2;

     }

 } {0 1205}

 do_test lastinsert-8.1 {

   db close

   sqlite3 db test.db

   execsql {

     CREATE TABLE t2(x INTEGER PRIMARY KEY, y);

     CREATE TABLE t3(a, b);

     CREATE TRIGGER after_t2 AFTER INSERT ON t2 BEGIN

       INSERT INTO t3 VALUES(new.x, new.y);

     END;

     INSERT INTO t2 VALUES(5000000000, 1);

     SELECT last_insert_rowid();

   }

 } 5000000000

 do_test lastinsert-9.1 {

   db eval {INSERT INTO t2 VALUES(123456789012345,0)}

   db last_insert_rowid

 } {123456789012345}

 } ;# ifcapable (view && trigger)

 finish_test