/ Check-in [6734c923]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fixed behaviour of last_insert_rowid() with triggers and add last_statement_change_count() function that works correctly with triggers. (CVS 1252)
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 6734c923395ca41ce453f56dd7d6e7794fbb3bb5
User & Date: rdc 2004-02-20 22:54:25
Context
2004-02-20
22:55
Add sqlite.def file required for correct operation of "make implib" option in Makefile.in (CVS 1253) check-in: b1b23c41 user: rdc tags: trunk
22:54
Fixed behaviour of last_insert_rowid() with triggers and add last_statement_change_count() function that works correctly with triggers. (CVS 1252) check-in: 6734c923 user: rdc tags: trunk
22:53
Fixed behaviour of last_insert_rowid() with triggers and add last_statement_change_count() function that works correctly with triggers. (CVS 1251) check-in: 3383413a user: rdc tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added test/lastinsert.test.

            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 {
           57  +    catchsql {
           58  +        create table t2 (k integer primary key, val1, val2, val3);
           59  +        create view v as select * from t1;
           60  +        select last_insert_rowid();
           61  +    }
           62  +} {0 3}
           63  +
           64  +# ----------------------------------------------------------------------------
           65  +# 2.x - tests with after insert trigger
           66  +
           67  +# LIRID changed properly after an insert into table containing an after trigger
           68  +do_test lastinsert-2.1 {
           69  +    catchsql {
           70  +        delete from t2;
           71  +        create trigger r1 after insert on t1 for each row begin
           72  +            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
           73  +            update t2 set k=k+10, val2=100+last_insert_rowid();
           74  +            update t2 set val3=1000+last_insert_rowid();
           75  +        end;
           76  +        insert into t1 values (13);
           77  +        select last_insert_rowid();
           78  +    }
           79  +} {0 13}
           80  +
           81  +# LIRID equals NEW.k upon entry into after insert trigger
           82  +do_test lastinsert-2.2 {
           83  +    catchsql {
           84  +        select val1 from t2;
           85  +    }
           86  +} {0 13}
           87  +
           88  +# LIRID changed properly by insert within context of after insert trigger
           89  +do_test lastinsert-2.3 {
           90  +    catchsql {
           91  +        select val2 from t2;
           92  +    }
           93  +} {0 126}
           94  +
           95  +# LIRID unchanged by update within context of after insert trigger
           96  +do_test lastinsert-2.4 {
           97  +    catchsql {
           98  +        select val3 from t2;
           99  +    }
          100  +} {0 1026}
          101  +
          102  +# ----------------------------------------------------------------------------
          103  +# 3.x - tests with after update trigger
          104  +
          105  +# LIRID not changed after an update onto a table containing an after trigger
          106  +do_test lastinsert-3.1 {
          107  +    catchsql {
          108  +        delete from t2;
          109  +        drop trigger r1;
          110  +        create trigger r1 after update on t1 for each row begin
          111  +            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
          112  +            update t2 set k=k+10, val2=100+last_insert_rowid();
          113  +            update t2 set val3=1000+last_insert_rowid();
          114  +        end;
          115  +        update t1 set k=14 where k=3;
          116  +        select last_insert_rowid();
          117  +    }
          118  +} {0 13}
          119  +
          120  +# LIRID unchanged upon entry into after update trigger
          121  +do_test lastinsert-3.2 {
          122  +    catchsql {
          123  +        select val1 from t2;
          124  +    }
          125  +} {0 13}
          126  +
          127  +# LIRID changed properly by insert within context of after update trigger
          128  +do_test lastinsert-3.3 {
          129  +    catchsql {
          130  +        select val2 from t2;
          131  +    }
          132  +} {0 128}
          133  +
          134  +# LIRID unchanged by update within context of after update trigger
          135  +do_test lastinsert-3.4 {
          136  +    catchsql {
          137  +        select val3 from t2;
          138  +    }
          139  +} {0 1028}
          140  +
          141  +# ----------------------------------------------------------------------------
          142  +# 4.x - tests with instead of insert trigger
          143  +
          144  +# LIRID not changed after an insert into view containing an instead of trigger
          145  +do_test lastinsert-4.1 {
          146  +    catchsql {
          147  +        delete from t2;
          148  +        drop trigger r1;
          149  +        create trigger r1 instead of insert on v for each row begin
          150  +            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
          151  +            update t2 set k=k+10, val2=100+last_insert_rowid();
          152  +            update t2 set val3=1000+last_insert_rowid();
          153  +        end;
          154  +        insert into v values (15);
          155  +        select last_insert_rowid();
          156  +    }
          157  +} {0 13}
          158  +
          159  +# LIRID unchanged upon entry into instead of trigger
          160  +do_test lastinsert-4.2 {
          161  +    catchsql {
          162  +        select val1 from t2;
          163  +    }
          164  +} {0 13}
          165  +
          166  +# LIRID changed properly by insert within context of instead of trigger
          167  +do_test lastinsert-4.3 {
          168  +    catchsql {
          169  +        select val2 from t2;
          170  +    }
          171  +} {0 130}
          172  +
          173  +# LIRID unchanged by update within context of instead of trigger
          174  +do_test lastinsert-4.4 {
          175  +    catchsql {
          176  +        select val3 from t2;
          177  +    }
          178  +} {0 1030}
          179  +
          180  +# ----------------------------------------------------------------------------
          181  +# 5.x - tests with before delete trigger
          182  +
          183  +# LIRID not changed after a delete on a table containing a before trigger
          184  +do_test lastinsert-5.1 {
          185  +    catchsql {
          186  +        delete from t2;
          187  +        drop trigger r1;
          188  +        create trigger r1 before delete on t1 for each row begin
          189  +            insert into t2 values (77, last_insert_rowid(), NULL, NULL);
          190  +            update t2 set k=k+10, val2=100+last_insert_rowid();
          191  +            update t2 set val3=1000+last_insert_rowid();
          192  +        end;
          193  +        delete from t1 where k=1;
          194  +        select last_insert_rowid();
          195  +    }
          196  +} {0 13}
          197  +
          198  +# LIRID unchanged upon entry into delete trigger
          199  +do_test lastinsert-5.2 {
          200  +    catchsql {
          201  +        select val1 from t2;
          202  +    }
          203  +} {0 13}
          204  +
          205  +# LIRID changed properly by insert within context of delete trigger
          206  +do_test lastinsert-5.3 {
          207  +    catchsql {
          208  +        select val2 from t2;
          209  +    }
          210  +} {0 177}
          211  +
          212  +# LIRID unchanged by update within context of delete trigger
          213  +do_test lastinsert-5.4 {
          214  +    catchsql {
          215  +        select val3 from t2;
          216  +    }
          217  +} {0 1077}
          218  +
          219  +# ----------------------------------------------------------------------------
          220  +# 6.x - tests with instead of update trigger
          221  +
          222  +# LIRID not changed after an update on a view containing an instead of trigger
          223  +do_test lastinsert-6.1 {
          224  +    catchsql {
          225  +        delete from t2;
          226  +        drop trigger r1;
          227  +        create trigger r1 instead of update on v for each row begin
          228  +            insert into t2 values (NEW.k*2, last_insert_rowid(), NULL, NULL);
          229  +            update t2 set k=k+10, val2=100+last_insert_rowid();
          230  +            update t2 set val3=1000+last_insert_rowid();
          231  +        end;
          232  +        update v set k=16 where k=14;
          233  +        select last_insert_rowid();
          234  +    }
          235  +} {0 13}
          236  +
          237  +# LIRID unchanged upon entry into instead of trigger
          238  +do_test lastinsert-6.2 {
          239  +    catchsql {
          240  +        select val1 from t2;
          241  +    }
          242  +} {0 13}
          243  +
          244  +# LIRID changed properly by insert within context of instead of trigger
          245  +do_test lastinsert-6.3 {
          246  +    catchsql {
          247  +        select val2 from t2;
          248  +    }
          249  +} {0 132}
          250  +
          251  +# LIRID unchanged by update within context of instead of trigger
          252  +do_test lastinsert-6.4 {
          253  +    catchsql {
          254  +        select val3 from t2;
          255  +    }
          256  +} {0 1032}
          257  +
          258  +# ----------------------------------------------------------------------------
          259  +# 7.x - complex tests with temporary tables and nested instead of triggers
          260  +
          261  +do_test lastinsert-7.1 {
          262  +    catchsql {
          263  +        drop table t1; drop table t2; drop trigger r1;
          264  +        create temp table t1 (k integer primary key);
          265  +        create temp table t2 (k integer primary key);
          266  +        create temp view v1 as select * from t1;
          267  +        create temp view v2 as select * from t2;
          268  +        create temp table rid (k integer primary key, rin, rout);
          269  +        insert into rid values (1, NULL, NULL);
          270  +        insert into rid values (2, NULL, NULL);
          271  +        create temp trigger r1 instead of insert on v1 for each row begin
          272  +            update rid set rin=last_insert_rowid() where k=1;
          273  +            insert into t1 values (100+NEW.k);
          274  +            insert into v2 values (100+last_insert_rowid());
          275  +            update rid set rout=last_insert_rowid() where k=1;
          276  +        end;
          277  +        create temp trigger r2 instead of insert on v2 for each row begin
          278  +            update rid set rin=last_insert_rowid() where k=2;
          279  +            insert into t2 values (1000+NEW.k);
          280  +            update rid set rout=last_insert_rowid() where k=2;
          281  +        end;
          282  +        insert into t1 values (77);
          283  +        select last_insert_rowid();
          284  +    }
          285  +} {0 77}
          286  +
          287  +do_test lastinsert-7.2 {
          288  +    catchsql {
          289  +        insert into v1 values (5);
          290  +        select last_insert_rowid();
          291  +    }
          292  +} {0 77}
          293  +
          294  +do_test lastinsert-7.3 {
          295  +    catchsql {
          296  +        select rin from rid where k=1;
          297  +    }
          298  +} {0 77}
          299  +
          300  +do_test lastinsert-7.4 {
          301  +    catchsql {
          302  +        select rout from rid where k=1;
          303  +    }
          304  +} {0 105}
          305  +
          306  +do_test lastinsert-7.5 {
          307  +    catchsql {
          308  +        select rin from rid where k=2;
          309  +    }
          310  +} {0 105}
          311  +
          312  +do_test lastinsert-7.6 {
          313  +    catchsql {
          314  +        select rout from rid where k=2;
          315  +    }
          316  +} {0 1205}
          317  +
          318  +finish_test
          319  +

Added test/laststmtchanges.test.

            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_statement_change_count()
           11  +# (LSCC) is updated properly, especially inside triggers
           12  +#
           13  +# Note 1: LSCC remains constant within a statement and only updates once
           14  +#           the statement is finished (triggers count as part of statement)
           15  +# Note 2: LSCC is changed within the context of a trigger
           16  +#           much like last_insert_rowid() (see lastinsert.test),
           17  +#           but is restored once the trigger exits
           18  +# Note 3: LSCC is not changed by a change to 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  +# LSCC set properly after insert
           29  +do_test laststmtchanges-1.1 {
           30  +    catchsql {
           31  +        create table t0 (x);
           32  +        insert into t0 values (1);
           33  +        insert into t0 values (1);
           34  +        insert into t0 values (2);
           35  +        insert into t0 values (2);
           36  +        insert into t0 values (1);
           37  +        insert into t0 values (1);
           38  +        insert into t0 values (1);
           39  +        insert into t0 values (2);
           40  +        select last_statement_change_count();
           41  +    }
           42  +} {0 1}
           43  +
           44  +# LSCC set properly after update
           45  +do_test laststmtchanges-1.2 {
           46  +    catchsql {
           47  +        update t0 set x=3 where x=1;
           48  +        select last_statement_change_count();
           49  +    }
           50  +} {0 5}
           51  +
           52  +# LSCC unchanged within an update statement
           53  +do_test laststmtchanges-1.3 {
           54  +    catchsql {
           55  +        update t0 set x=x+last_statement_change_count() where x=3;
           56  +        select count() from t0 where x=8;
           57  +    }
           58  +} {0 5}
           59  +
           60  +# LSCC set properly after update on table where no rows changed
           61  +do_test laststmtchanges-1.4 {
           62  +    catchsql {
           63  +        update t0 set x=77 where x=88;
           64  +        select last_statement_change_count();
           65  +    }
           66  +} {0 0}
           67  +
           68  +# LSCC set properly after delete from table
           69  +do_test laststmtchanges-1.5 {
           70  +    catchsql {
           71  +        delete from t0 where x=2;
           72  +        select last_statement_change_count();
           73  +    }
           74  +} {0 3}
           75  +
           76  +# ----------------------------------------------------------------------------
           77  +# 2.x - tests with after insert trigger
           78  +
           79  +# LSCC changed properly after insert into table containing after trigger
           80  +do_test laststmtchanges-2.1 {
           81  +    catchsql {
           82  +        create table t1 (k integer primary key);
           83  +        create table t2 (k integer primary key, v1, v2);
           84  +        create trigger r1 after insert on t1 for each row begin
           85  +            insert into t2 values (NULL, last_statement_change_count(), NULL);
           86  +            update t0 set x=x;
           87  +            update t2 set v2=last_statement_change_count();
           88  +        end;
           89  +        insert into t1 values (77);
           90  +        select last_statement_change_count();
           91  +    }
           92  +} {0 1}
           93  +
           94  +# LSCC unchanged upon entry into after insert trigger
           95  +do_test laststmtchanges-2.2 {
           96  +    catchsql {
           97  +        select v1 from t2;
           98  +    }
           99  +} {0 3}
          100  +
          101  +# LSCC changed properly by update within context of after insert trigger
          102  +do_test laststmtchanges-2.3 {
          103  +    catchsql {
          104  +        select v2 from t2;
          105  +    }
          106  +} {0 5}
          107  +
          108  +# ----------------------------------------------------------------------------
          109  +# 3.x - tests with after update trigger
          110  +
          111  +# LSCC changed properly after update into table containing after trigger
          112  +do_test laststmtchanges-3.1 {
          113  +    catchsql {
          114  +        drop trigger r1;
          115  +        delete from t2; delete from t2;
          116  +        create trigger r1 after update on t1 for each row begin
          117  +            insert into t2 values (NULL, last_statement_change_count(), NULL);
          118  +            delete from t0 where oid=1 or oid=2;
          119  +            update t2 set v2=last_statement_change_count();
          120  +        end;
          121  +        update t1 set k=k;
          122  +        select last_statement_change_count();
          123  +    }
          124  +} {0 1}
          125  +
          126  +# LSCC unchanged upon entry into after update trigger
          127  +do_test laststmtchanges-3.2 {
          128  +    catchsql {
          129  +        select v1 from t2;
          130  +    }
          131  +} {0 0}
          132  +
          133  +# LSCC changed properly by delete within context of after update trigger
          134  +do_test laststmtchanges-3.3 {
          135  +    catchsql {
          136  +        select v2 from t2;
          137  +    }
          138  +} {0 2}
          139  +
          140  +# ----------------------------------------------------------------------------
          141  +# 4.x - tests with before delete trigger
          142  +
          143  +# LSCC changed properly on delete from table containing before trigger
          144  +do_test laststmtchanges-4.1 {
          145  +    catchsql {
          146  +        drop trigger r1;
          147  +        delete from t2; delete from t2;
          148  +        create trigger r1 before delete on t1 for each row begin
          149  +            insert into t2 values (NULL, last_statement_change_count(), NULL);
          150  +            insert into t0 values (5);
          151  +            update t2 set v2=last_statement_change_count();
          152  +        end;
          153  +        delete from t1;
          154  +        select last_statement_change_count();
          155  +    }
          156  +} {0 1}
          157  +
          158  +# LSCC unchanged upon entry into before delete trigger
          159  +do_test laststmtchanges-4.2 {
          160  +    catchsql {
          161  +        select v1 from t2;
          162  +    }
          163  +} {0 0}
          164  +
          165  +# LSCC changed properly by insert within context of before delete trigger
          166  +do_test laststmtchanges-4.3 {
          167  +    catchsql {
          168  +        select v2 from t2;
          169  +    }
          170  +} {0 1}
          171  +
          172  +# ----------------------------------------------------------------------------
          173  +# 5.x - complex tests with temporary tables and nested instead of triggers
          174  +
          175  +do_test laststmtchanges-5.1 {
          176  +    catchsql {
          177  +        drop table t0; drop table t1; drop table t2;
          178  +        create temp table t0(x);
          179  +        create temp table t1 (k integer primary key);
          180  +        create temp table t2 (k integer primary key);
          181  +        create temp view v1 as select * from t1;
          182  +        create temp view v2 as select * from t2;
          183  +        create temp table n1 (k integer primary key, n);
          184  +        create temp table n2 (k integer primary key, n);
          185  +        insert into t0 values (1);
          186  +        insert into t0 values (2);
          187  +        insert into t0 values (1);
          188  +        insert into t0 values (1);
          189  +        insert into t0 values (1);
          190  +        insert into t0 values (2);
          191  +        insert into t0 values (2);
          192  +        insert into t0 values (1);
          193  +        create temp trigger r1 instead of insert on v1 for each row begin
          194  +            insert into n1 values (NULL, last_statement_change_count());
          195  +            update t0 set x=x*10 where x=1;
          196  +            insert into n1 values (NULL, last_statement_change_count());
          197  +            insert into t1 values (NEW.k);
          198  +            insert into n1 values (NULL, last_statement_change_count());
          199  +            update t0 set x=x*10 where x=0;
          200  +            insert into v2 values (100+NEW.k);
          201  +            insert into n1 values (NULL, last_statement_change_count());
          202  +        end;
          203  +        create temp trigger r2 instead of insert on v2 for each row begin
          204  +            insert into n2 values (NULL, last_statement_change_count());
          205  +            insert into t2 values (1000+NEW.k);
          206  +            insert into n2 values (NULL, last_statement_change_count());
          207  +            update t0 set x=x*100 where x=0;
          208  +            insert into n2 values (NULL, last_statement_change_count());
          209  +            delete from t0 where x=2;
          210  +            insert into n2 values (NULL, last_statement_change_count());
          211  +        end;
          212  +        insert into t1 values (77);
          213  +        select last_statement_change_count();
          214  +    }
          215  +} {0 1}
          216  +
          217  +do_test laststmtchanges-5.2 {
          218  +    catchsql {
          219  +        delete from t1 where k=88;
          220  +        select last_statement_change_count();
          221  +    }
          222  +} {0 0}
          223  +
          224  +do_test laststmtchanges-5.3 {
          225  +    catchsql {
          226  +        insert into v1 values (5);
          227  +        select last_statement_change_count();
          228  +    }
          229  +} {0 0}
          230  +
          231  +do_test laststmtchanges-5.4 {
          232  +    catchsql {
          233  +        select n from n1;
          234  +    }
          235  +} {0 {0 5 1 0}}
          236  +
          237  +do_test laststmtchanges-5.5 {
          238  +    catchsql {
          239  +        select n from n2;
          240  +    }
          241  +} {0 {0 1 0 3}}
          242  +
          243  +finish_test
          244  +