1 # The author disclaims copyright to this source code. In place of
2 # a legal notice, here is a blessing:
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.
8 #***********************************************************************
10 # Tests to make sure that values returned by changes() and total_changes()
11 # are updated properly, especially inside triggers
13 # Note 1: changes() remains constant within a statement and only updates
14 # once the statement is finished (triggers count as part of
16 # Note 2: changes() is changed within the context of a trigger much like
17 # last_insert_rowid() (see lastinsert.test), but is restored once
19 # Note 3: changes() is not changed by a change to a view (since everything
20 # is done within instead of trigger context).
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
26 # ----------------------------------------------------------------------------
27 # 1.x - basic tests (no triggers)
29 # changes() set properly after insert
30 do_test laststmtchanges-1.1 {
33 insert into t0 values (1);
34 insert into t0 values (1);
35 insert into t0 values (2);
36 insert into t0 values (2);
37 insert into t0 values (1);
38 insert into t0 values (1);
39 insert into t0 values (1);
40 insert into t0 values (2);
41 select changes(), total_changes();
45 # changes() set properly after update
46 do_test laststmtchanges-1.2 {
48 update t0 set x=3 where x=1;
49 select changes(), total_changes();
53 # changes() unchanged within an update statement
54 do_test laststmtchanges-1.3 {
56 update t0 set x=x+changes() where x=3;
57 select count() from t0 where x=8;
61 # changes() set properly after update on table where no rows changed
62 do_test laststmtchanges-1.4 {
64 update t0 set x=77 where x=88;
69 # changes() set properly after delete from table
70 do_test laststmtchanges-1.5 {
72 delete from t0 where x=2;
77 # All remaining tests involve triggers. Skip them if triggers are not
78 # supported in this build.
80 ifcapable {!trigger} {
86 # ----------------------------------------------------------------------------
87 # 2.x - tests with after insert trigger
89 # changes() changed properly after insert into table containing after trigger
90 do_test laststmtchanges-2.1 {
91 set ::tc [db total_changes]
93 create table t1 (k integer primary key);
94 create table t2 (k integer primary key, v1, v2);
95 create trigger r1 after insert on t1 for each row begin
96 insert into t2 values (NULL, changes(), NULL);
98 update t2 set v2=changes();
100 insert into t1 values (77);
105 # changes() unchanged upon entry into after insert trigger
106 do_test laststmtchanges-2.2 {
112 # changes() changed properly by update within context of after insert trigger
113 do_test laststmtchanges-2.3 {
119 # Total changes caused by firing the trigger above:
121 # 1 from "insert into t1 values(77)" +
122 # 1 from "insert into t2 values (NULL, changes(), NULL);" +
123 # 5 from "update t0 set x=x;" +
124 # 1 from "update t2 set v2=changes();"
126 do_test laststmtchanges-2.4 {
127 expr [db total_changes] - $::tc
130 # ----------------------------------------------------------------------------
131 # 3.x - tests with after update trigger
133 # changes() changed properly after update into table containing after trigger
134 do_test laststmtchanges-3.1 {
137 delete from t2; delete from t2;
138 create trigger r1 after update on t1 for each row begin
139 insert into t2 values (NULL, changes(), NULL);
140 delete from t0 where oid=1 or oid=2;
141 update t2 set v2=changes();
148 # changes() unchanged upon entry into after update trigger
149 do_test laststmtchanges-3.2 {
155 # changes() changed properly by delete within context of after update trigger
156 do_test laststmtchanges-3.3 {
162 # ----------------------------------------------------------------------------
163 # 4.x - tests with before delete trigger
165 # changes() changed properly on delete from table containing before trigger
166 do_test laststmtchanges-4.1 {
169 delete from t2; delete from t2;
170 create trigger r1 before delete on t1 for each row begin
171 insert into t2 values (NULL, changes(), NULL);
172 insert into t0 values (5);
173 update t2 set v2=changes();
180 # changes() unchanged upon entry into before delete trigger
181 do_test laststmtchanges-4.2 {
187 # changes() changed properly by insert within context of before delete trigger
188 do_test laststmtchanges-4.3 {
194 # ----------------------------------------------------------------------------
195 # 5.x - complex tests with temporary tables and nested instead of triggers
196 # These tests cannot run if the library does not have view support enabled.
198 ifcapable view&&tempdb {
200 do_test laststmtchanges-5.1 {
202 drop table t0; drop table t1; drop table t2;
203 create temp table t0(x);
204 create temp table t1 (k integer primary key);
205 create temp table t2 (k integer primary key);
206 create temp view v1 as select * from t1;
207 create temp view v2 as select * from t2;
208 create temp table n1 (k integer primary key, n);
209 create temp table n2 (k integer primary key, n);
210 insert into t0 values (1);
211 insert into t0 values (2);
212 insert into t0 values (1);
213 insert into t0 values (1);
214 insert into t0 values (1);
215 insert into t0 values (2);
216 insert into t0 values (2);
217 insert into t0 values (1);
218 create temp trigger r1 instead of insert on v1 for each row begin
219 insert into n1 values (NULL, changes());
220 update t0 set x=x*10 where x=1;
221 insert into n1 values (NULL, changes());
222 insert into t1 values (NEW.k);
223 insert into n1 values (NULL, changes());
224 update t0 set x=x*10 where x=0;
225 insert into v2 values (100+NEW.k);
226 insert into n1 values (NULL, changes());
228 create temp trigger r2 instead of insert on v2 for each row begin
229 insert into n2 values (NULL, changes());
230 insert into t2 values (1000+NEW.k);
231 insert into n2 values (NULL, changes());
232 update t0 set x=x*100 where x=0;
233 insert into n2 values (NULL, changes());
234 delete from t0 where x=2;
235 insert into n2 values (NULL, changes());
237 insert into t1 values (77);
242 do_test laststmtchanges-5.2 {
244 delete from t1 where k=88;
249 do_test laststmtchanges-5.3 {
251 insert into v1 values (5);
256 do_test laststmtchanges-5.4 {
262 do_test laststmtchanges-5.5 {