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