AryaWu/sqlite
0
1#2# The author disclaims copyright to this source code. In place of3# 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 triggers13#14# Note 1: changes() remains constant within a statement and only updates15# once the statement is finished (triggers count as part of16# 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 once19# the trigger exits.20# Note 3: changes() is not changed by a change to a view (since everything21# 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.tcl27 28# ----------------------------------------------------------------------------29# 1.x - basic tests (no triggers)30 31# changes() set properly after insert32do_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 update48do_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() that56# appears to have been left over from SQLite version 2. This test57# makes sure it has been removed.58#59do_test laststmtchanges-1.2.1 {60 db cache flush61 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 statement66do_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 changed75do_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 table83do_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 not91# supported in this build.92#93ifcapable {!trigger} {94 finish_test95 return96}97 98 99# ----------------------------------------------------------------------------100# 2.x - tests with after insert trigger101 102# changes() changed properly after insert into table containing after trigger103do_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 begin109 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 trigger119do_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 trigger126do_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] - $::tc141} {8}142 143# ----------------------------------------------------------------------------144# 3.x - tests with after update trigger145 146# changes() changed properly after update into table containing after trigger147do_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 begin152 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 trigger162do_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 trigger169do_test laststmtchanges-3.3 {170 catchsql {171 select v2 from t2;172 }173} {0 2}174 175# ----------------------------------------------------------------------------176# 4.x - tests with before delete trigger177 178# changes() changed properly on delete from table containing before trigger179do_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 begin184 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 trigger194do_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 trigger201do_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 triggers209# 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 begin232 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 begin242 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 view282 283 284# ----------------------------------------------------------------------------285# 6.x - Test "DELETE FROM <table>" in the absence of triggers286#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 0322} {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_test332