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 |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
6734c923395ca41ce453f56dd7d6e779 |
User & Date: | rdc 2004-02-20 22:54:25.000 |
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: b1b23c4102 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: 6734c92339 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: 3383413a53 user: rdc tags: trunk) | |
Changes
Added test/lastinsert.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 | # 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 { catchsql { create table t2 (k integer primary key, val1, val2, val3); create view v as select * from t1; select last_insert_rowid(); } } {0 3} # ---------------------------------------------------------------------------- # 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 # 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} # ---------------------------------------------------------------------------- # 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 { delete from t2; drop trigger r1; 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 # 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} # ---------------------------------------------------------------------------- # 7.x - complex tests with temporary tables and nested instead of triggers 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} finish_test |
Added test/laststmtchanges.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 | # 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_statement_change_count() # (LSCC) is updated properly, especially inside triggers # # Note 1: LSCC remains constant within a statement and only updates once # the statement is finished (triggers count as part of statement) # Note 2: LSCC is changed within the context of a trigger # much like last_insert_rowid() (see lastinsert.test), # but is restored once the trigger exits # Note 3: LSCC is not changed by a change to 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) # LSCC set properly after insert do_test laststmtchanges-1.1 { catchsql { create table t0 (x); insert into t0 values (1); insert into t0 values (1); insert into t0 values (2); insert into t0 values (2); insert into t0 values (1); insert into t0 values (1); insert into t0 values (1); insert into t0 values (2); select last_statement_change_count(); } } {0 1} # LSCC set properly after update do_test laststmtchanges-1.2 { catchsql { update t0 set x=3 where x=1; select last_statement_change_count(); } } {0 5} # LSCC unchanged within an update statement do_test laststmtchanges-1.3 { catchsql { update t0 set x=x+last_statement_change_count() where x=3; select count() from t0 where x=8; } } {0 5} # LSCC set properly after update on table where no rows changed do_test laststmtchanges-1.4 { catchsql { update t0 set x=77 where x=88; select last_statement_change_count(); } } {0 0} # LSCC set properly after delete from table do_test laststmtchanges-1.5 { catchsql { delete from t0 where x=2; select last_statement_change_count(); } } {0 3} # ---------------------------------------------------------------------------- # 2.x - tests with after insert trigger # LSCC changed properly after insert into table containing after trigger do_test laststmtchanges-2.1 { catchsql { create table t1 (k integer primary key); create table t2 (k integer primary key, v1, v2); create trigger r1 after insert on t1 for each row begin insert into t2 values (NULL, last_statement_change_count(), NULL); update t0 set x=x; update t2 set v2=last_statement_change_count(); end; insert into t1 values (77); select last_statement_change_count(); } } {0 1} # LSCC unchanged upon entry into after insert trigger do_test laststmtchanges-2.2 { catchsql { select v1 from t2; } } {0 3} # LSCC changed properly by update within context of after insert trigger do_test laststmtchanges-2.3 { catchsql { select v2 from t2; } } {0 5} # ---------------------------------------------------------------------------- # 3.x - tests with after update trigger # LSCC changed properly after update into table containing after trigger do_test laststmtchanges-3.1 { catchsql { drop trigger r1; delete from t2; delete from t2; create trigger r1 after update on t1 for each row begin insert into t2 values (NULL, last_statement_change_count(), NULL); delete from t0 where oid=1 or oid=2; update t2 set v2=last_statement_change_count(); end; update t1 set k=k; select last_statement_change_count(); } } {0 1} # LSCC unchanged upon entry into after update trigger do_test laststmtchanges-3.2 { catchsql { select v1 from t2; } } {0 0} # LSCC changed properly by delete within context of after update trigger do_test laststmtchanges-3.3 { catchsql { select v2 from t2; } } {0 2} # ---------------------------------------------------------------------------- # 4.x - tests with before delete trigger # LSCC changed properly on delete from table containing before trigger do_test laststmtchanges-4.1 { catchsql { drop trigger r1; delete from t2; delete from t2; create trigger r1 before delete on t1 for each row begin insert into t2 values (NULL, last_statement_change_count(), NULL); insert into t0 values (5); update t2 set v2=last_statement_change_count(); end; delete from t1; select last_statement_change_count(); } } {0 1} # LSCC unchanged upon entry into before delete trigger do_test laststmtchanges-4.2 { catchsql { select v1 from t2; } } {0 0} # LSCC changed properly by insert within context of before delete trigger do_test laststmtchanges-4.3 { catchsql { select v2 from t2; } } {0 1} # ---------------------------------------------------------------------------- # 5.x - complex tests with temporary tables and nested instead of triggers do_test laststmtchanges-5.1 { catchsql { drop table t0; drop table t1; drop table t2; create temp table t0(x); 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 n1 (k integer primary key, n); create temp table n2 (k integer primary key, n); insert into t0 values (1); insert into t0 values (2); insert into t0 values (1); insert into t0 values (1); insert into t0 values (1); insert into t0 values (2); insert into t0 values (2); insert into t0 values (1); create temp trigger r1 instead of insert on v1 for each row begin insert into n1 values (NULL, last_statement_change_count()); update t0 set x=x*10 where x=1; insert into n1 values (NULL, last_statement_change_count()); insert into t1 values (NEW.k); insert into n1 values (NULL, last_statement_change_count()); update t0 set x=x*10 where x=0; insert into v2 values (100+NEW.k); insert into n1 values (NULL, last_statement_change_count()); end; create temp trigger r2 instead of insert on v2 for each row begin insert into n2 values (NULL, last_statement_change_count()); insert into t2 values (1000+NEW.k); insert into n2 values (NULL, last_statement_change_count()); update t0 set x=x*100 where x=0; insert into n2 values (NULL, last_statement_change_count()); delete from t0 where x=2; insert into n2 values (NULL, last_statement_change_count()); end; insert into t1 values (77); select last_statement_change_count(); } } {0 1} do_test laststmtchanges-5.2 { catchsql { delete from t1 where k=88; select last_statement_change_count(); } } {0 0} do_test laststmtchanges-5.3 { catchsql { insert into v1 values (5); select last_statement_change_count(); } } {0 0} do_test laststmtchanges-5.4 { catchsql { select n from n1; } } {0 {0 5 1 0}} do_test laststmtchanges-5.5 { catchsql { select n from n2; } } {0 {0 1 0 3}} finish_test |