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.1.1.1 2012/02/21 17:04:16 misho Exp $
24:
25: set testdir [file dirname $argv0]
26: source $testdir/tester.tcl
27:
28: # ----------------------------------------------------------------------------
29: # 1.x - basic tests (no triggers)
30:
31: # changes() set properly after insert
32: do_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
48: do_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: #
59: do_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
66: do_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
75: do_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
83: do_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: #
93: ifcapable {!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
103: do_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
119: do_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
126: do_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: #
139: do_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
147: do_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
162: do_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
169: do_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
179: do_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
194: do_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
201: do_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:
211: ifcapable view&&tempdb {
212:
213: do_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:
255: do_test laststmtchanges-5.2 {
256: catchsql {
257: delete from t1 where k=88;
258: select changes();
259: }
260: } {0 0}
261:
262: do_test laststmtchanges-5.3 {
263: catchsql {
264: insert into v1 values (5);
265: select changes();
266: }
267: } {0 0}
268:
269: do_test laststmtchanges-5.4 {
270: catchsql {
271: select n from n1;
272: }
273: } {0 {0 5 1 0}}
274:
275: do_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: #
287: do_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: } {}
294: do_test laststmtchanges-6.2 {
295: execsql {
296: BEGIN;
297: DELETE FROM t3;
298: SELECT changes();
299: }
300: } {2}
301: do_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}
309: do_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}
318: do_test laststmtchanges-6.5 {
319: execsql { ROLLBACK }
320: set nTotalChange [execsql {SELECT total_changes()}]
321: expr 0
322: } {0}
323: do_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:
331: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to laststmtchanges.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test