1# 2# The author disclaims copyright to this source code. In place of 3# a legal notice, here is a blessing: 4# 5# May you do good and not evil. 6# May you find forgiveness for yourself and forgive others. 7# May you share freely, never taking more than you give. 8# 9#*********************************************************************** 10# 11# Tests to make sure that values returned by changes() and total_changes() 12# are updated properly, especially inside triggers 13# 14# Note 1: changes() remains constant within a statement and only updates 15# once the statement is finished (triggers count as part of 16# statement). 17# Note 2: changes() is changed within the context of a trigger much like 18# last_insert_rowid() (see lastinsert.test), but is restored once 19# the trigger exits. 20# Note 3: changes() is not changed by a change to a view (since everything 21# is done within instead of trigger context). 22# 23# $Id: laststmtchanges.test,v 1.7 2008/10/27 13:59:34 danielk1977 Exp $ 24 25set testdir [file dirname $argv0] 26source $testdir/tester.tcl 27 28# ---------------------------------------------------------------------------- 29# 1.x - basic tests (no triggers) 30 31# changes() set properly after insert 32do_test laststmtchanges-1.1 { 33 catchsql { 34 create table t0 (x); 35 insert into t0 values (1); 36 insert into t0 values (1); 37 insert into t0 values (2); 38 insert into t0 values (2); 39 insert into t0 values (1); 40 insert into t0 values (1); 41 insert into t0 values (1); 42 insert into t0 values (2); 43 select changes(), total_changes(); 44 } 45} {0 {1 8}} 46 47# changes() set properly after update 48do_test laststmtchanges-1.2 { 49 catchsql { 50 update t0 set x=3 where x=1; 51 select changes(), total_changes(); 52 } 53} {0 {5 13}} 54 55# There was some goofy change-counting logic in sqlite3_exec() that 56# appears to have been left over from SQLite version 2. This test 57# makes sure it has been removed. 58# 59do_test laststmtchanges-1.2.1 { 60 db cache flush 61 sqlite3_exec_printf db {update t0 set x=4 where x=3; select 1;} {} 62 execsql {select changes()} 63} {5} 64 65# changes() unchanged within an update statement 66do_test laststmtchanges-1.3 { 67 execsql {update t0 set x=3 where x=4} 68 catchsql { 69 update t0 set x=x+changes() where x=3; 70 select count() from t0 where x=8; 71 } 72} {0 5} 73 74# changes() set properly after update on table where no rows changed 75do_test laststmtchanges-1.4 { 76 catchsql { 77 update t0 set x=77 where x=88; 78 select changes(); 79 } 80} {0 0} 81 82# changes() set properly after delete from table 83do_test laststmtchanges-1.5 { 84 catchsql { 85 delete from t0 where x=2; 86 select changes(); 87 } 88} {0 3} 89 90# All remaining tests involve triggers. Skip them if triggers are not 91# supported in this build. 92# 93ifcapable {!trigger} { 94 finish_test 95 return 96} 97 98 99# ---------------------------------------------------------------------------- 100# 2.x - tests with after insert trigger 101 102# changes() changed properly after insert into table containing after trigger 103do_test laststmtchanges-2.1 { 104 set ::tc [db total_changes] 105 catchsql { 106 create table t1 (k integer primary key); 107 create table t2 (k integer primary key, v1, v2); 108 create trigger r1 after insert on t1 for each row begin 109 insert into t2 values (NULL, changes(), NULL); 110 update t0 set x=x; 111 update t2 set v2=changes(); 112 end; 113 insert into t1 values (77); 114 select changes(); 115 } 116} {0 1} 117 118# changes() unchanged upon entry into after insert trigger 119do_test laststmtchanges-2.2 { 120 catchsql { 121 select v1 from t2; 122 } 123} {0 3} 124 125# changes() changed properly by update within context of after insert trigger 126do_test laststmtchanges-2.3 { 127 catchsql { 128 select v2 from t2; 129 } 130} {0 5} 131 132# Total changes caused by firing the trigger above: 133# 134# 1 from "insert into t1 values(77)" + 135# 1 from "insert into t2 values (NULL, changes(), NULL);" + 136# 5 from "update t0 set x=x;" + 137# 1 from "update t2 set v2=changes();" 138# 139do_test laststmtchanges-2.4 { 140 expr [db total_changes] - $::tc 141} {8} 142 143# ---------------------------------------------------------------------------- 144# 3.x - tests with after update trigger 145 146# changes() changed properly after update into table containing after trigger 147do_test laststmtchanges-3.1 { 148 catchsql { 149 drop trigger r1; 150 delete from t2; delete from t2; 151 create trigger r1 after update on t1 for each row begin 152 insert into t2 values (NULL, changes(), NULL); 153 delete from t0 where oid=1 or oid=2; 154 update t2 set v2=changes(); 155 end; 156 update t1 set k=k; 157 select changes(); 158 } 159} {0 1} 160 161# changes() unchanged upon entry into after update trigger 162do_test laststmtchanges-3.2 { 163 catchsql { 164 select v1 from t2; 165 } 166} {0 0} 167 168# changes() changed properly by delete within context of after update trigger 169do_test laststmtchanges-3.3 { 170 catchsql { 171 select v2 from t2; 172 } 173} {0 2} 174 175# ---------------------------------------------------------------------------- 176# 4.x - tests with before delete trigger 177 178# changes() changed properly on delete from table containing before trigger 179do_test laststmtchanges-4.1 { 180 catchsql { 181 drop trigger r1; 182 delete from t2; delete from t2; 183 create trigger r1 before delete on t1 for each row begin 184 insert into t2 values (NULL, changes(), NULL); 185 insert into t0 values (5); 186 update t2 set v2=changes(); 187 end; 188 delete from t1; 189 select changes(); 190 } 191} {0 1} 192 193# changes() unchanged upon entry into before delete trigger 194do_test laststmtchanges-4.2 { 195 catchsql { 196 select v1 from t2; 197 } 198} {0 0} 199 200# changes() changed properly by insert within context of before delete trigger 201do_test laststmtchanges-4.3 { 202 catchsql { 203 select v2 from t2; 204 } 205} {0 1} 206 207# ---------------------------------------------------------------------------- 208# 5.x - complex tests with temporary tables and nested instead of triggers 209# These tests cannot run if the library does not have view support enabled. 210 211ifcapable view&&tempdb { 212 213do_test laststmtchanges-5.1 { 214 catchsql { 215 drop table t0; drop table t1; drop table t2; 216 create temp table t0(x); 217 create temp table t1 (k integer primary key); 218 create temp table t2 (k integer primary key); 219 create temp view v1 as select * from t1; 220 create temp view v2 as select * from t2; 221 create temp table n1 (k integer primary key, n); 222 create temp table n2 (k integer primary key, n); 223 insert into t0 values (1); 224 insert into t0 values (2); 225 insert into t0 values (1); 226 insert into t0 values (1); 227 insert into t0 values (1); 228 insert into t0 values (2); 229 insert into t0 values (2); 230 insert into t0 values (1); 231 create temp trigger r1 instead of insert on v1 for each row begin 232 insert into n1 values (NULL, changes()); 233 update t0 set x=x*10 where x=1; 234 insert into n1 values (NULL, changes()); 235 insert into t1 values (NEW.k); 236 insert into n1 values (NULL, changes()); 237 update t0 set x=x*10 where x=0; 238 insert into v2 values (100+NEW.k); 239 insert into n1 values (NULL, changes()); 240 end; 241 create temp trigger r2 instead of insert on v2 for each row begin 242 insert into n2 values (NULL, changes()); 243 insert into t2 values (1000+NEW.k); 244 insert into n2 values (NULL, changes()); 245 update t0 set x=x*100 where x=0; 246 insert into n2 values (NULL, changes()); 247 delete from t0 where x=2; 248 insert into n2 values (NULL, changes()); 249 end; 250 insert into t1 values (77); 251 select changes(); 252 } 253} {0 1} 254 255do_test laststmtchanges-5.2 { 256 catchsql { 257 delete from t1 where k=88; 258 select changes(); 259 } 260} {0 0} 261 262do_test laststmtchanges-5.3 { 263 catchsql { 264 insert into v1 values (5); 265 select changes(); 266 } 267} {0 0} 268 269do_test laststmtchanges-5.4 { 270 catchsql { 271 select n from n1; 272 } 273} {0 {0 5 1 0}} 274 275do_test laststmtchanges-5.5 { 276 catchsql { 277 select n from n2; 278 } 279} {0 {0 1 0 3}} 280 281} ;# ifcapable view 282 283 284# ---------------------------------------------------------------------------- 285# 6.x - Test "DELETE FROM <table>" in the absence of triggers 286# 287do_test laststmtchanges-6.1 { 288 execsql { 289 CREATE TABLE t3(a, b, c); 290 INSERT INTO t3 VALUES(1, 2, 3); 291 INSERT INTO t3 VALUES(4, 5, 6); 292 } 293} {} 294do_test laststmtchanges-6.2 { 295 execsql { 296 BEGIN; 297 DELETE FROM t3; 298 SELECT changes(); 299 } 300} {2} 301do_test laststmtchanges-6.3 { 302 execsql { 303 ROLLBACK; 304 BEGIN; 305 DELETE FROM t3 WHERE a IS NOT NULL; 306 SELECT changes(); 307 } 308} {2} 309do_test laststmtchanges-6.4 { 310 execsql { 311 ROLLBACK; 312 CREATE INDEX t3_i1 ON t3(a); 313 BEGIN; 314 DELETE FROM t3; 315 SELECT changes(); 316 } 317} {2} 318do_test laststmtchanges-6.5 { 319 execsql { ROLLBACK } 320 set nTotalChange [execsql {SELECT total_changes()}] 321 expr 0 322} {0} 323do_test laststmtchanges-6.6 { 324 execsql { 325 SELECT total_changes(); 326 DELETE FROM t3; 327 SELECT total_changes(); 328 } 329} [list $nTotalChange [expr $nTotalChange+2]] 330 331finish_test 332