Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
| 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: |
6734c923395ca41ce453f56dd7d6e779 |
| User & Date: | rdc 2004-02-20 22:54:25 |
|
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) | |
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 +