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
22set testdir [file dirname $argv0]
23source $testdir/tester.tcl
24
25# ----------------------------------------------------------------------------
26# 1.x - basic tests (no triggers)
27
28# LSCC set properly after insert
29do_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
45do_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
53do_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
61do_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
69do_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
80do_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
95do_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
102do_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
112do_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
127do_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
134do_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
144do_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
159do_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
166do_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
175do_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
217do_test laststmtchanges-5.2 {
218    catchsql {
219        delete from t1 where k=88;
220        select last_statement_change_count();
221    }
222} {0 0}
223
224do_test laststmtchanges-5.3 {
225    catchsql {
226        insert into v1 values (5);
227        select last_statement_change_count();
228    }
229} {0 0}
230
231do_test laststmtchanges-5.4 {
232    catchsql {
233        select n from n1;
234    }
235} {0 {0 5 1 0}}
236
237do_test laststmtchanges-5.5 {
238    catchsql {
239        select n from n2;
240    }
241} {0 {0 1 0 3}}
242
243finish_test
244
245