Annotation of embedaddon/sqlite3/test/delete.test, revision 1.1.1.1
1.1 misho 1: # 2001 September 15
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. The
12: # focus of this file is testing the DELETE FROM statement.
13: #
14: # $Id: delete.test,v 1.26 2009/06/05 17:09:12 drh Exp $
15:
16: set testdir [file dirname $argv0]
17: source $testdir/tester.tcl
18:
19: # Try to delete from a non-existant table.
20: #
21: do_test delete-1.1 {
22: set v [catch {execsql {DELETE FROM test1}} msg]
23: lappend v $msg
24: } {1 {no such table: test1}}
25:
26: # Try to delete from sqlite_master
27: #
28: do_test delete-2.1 {
29: set v [catch {execsql {DELETE FROM sqlite_master}} msg]
30: lappend v $msg
31: } {1 {table sqlite_master may not be modified}}
32:
33: # Delete selected entries from a table with and without an index.
34: #
35: do_test delete-3.1.1 {
36: execsql {CREATE TABLE table1(f1 int, f2 int)}
37: execsql {INSERT INTO table1 VALUES(1,2)}
38: execsql {INSERT INTO table1 VALUES(2,4)}
39: execsql {INSERT INTO table1 VALUES(3,8)}
40: execsql {INSERT INTO table1 VALUES(4,16)}
41: execsql {SELECT * FROM table1 ORDER BY f1}
42: } {1 2 2 4 3 8 4 16}
43: do_test delete-3.1.2 {
44: execsql {DELETE FROM table1 WHERE f1=3}
45: } {}
46: do_test delete-3.1.3 {
47: execsql {SELECT * FROM table1 ORDER BY f1}
48: } {1 2 2 4 4 16}
49: do_test delete-3.1.4 {
50: execsql {CREATE INDEX index1 ON table1(f1)}
51: execsql {PRAGMA count_changes=on}
52: ifcapable explain {
53: execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
54: }
55: execsql {DELETE FROM 'table1' WHERE f1=3}
56: } {0}
57: do_test delete-3.1.5 {
58: execsql {SELECT * FROM table1 ORDER BY f1}
59: } {1 2 2 4 4 16}
60: do_test delete-3.1.6.1 {
61: execsql {DELETE FROM table1 WHERE f1=2}
62: } {1}
63: do_test delete-3.1.6.2 {
64: db changes
65: } 1
66: do_test delete-3.1.7 {
67: execsql {SELECT * FROM table1 ORDER BY f1}
68: } {1 2 4 16}
69: integrity_check delete-3.2
70:
71:
72: # Semantic errors in the WHERE clause
73: #
74: do_test delete-4.1 {
75: execsql {CREATE TABLE table2(f1 int, f2 int)}
76: set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
77: lappend v $msg
78: } {1 {no such column: f3}}
79:
80: do_test delete-4.2 {
81: set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
82: lappend v $msg
83: } {1 {no such function: xyzzy}}
84: integrity_check delete-4.3
85:
86: # Lots of deletes
87: #
88: do_test delete-5.1.1 {
89: execsql {DELETE FROM table1}
90: } {2}
91: do_test delete-5.1.2 {
92: execsql {SELECT count(*) FROM table1}
93: } {0}
94: do_test delete-5.2.1 {
95: execsql {BEGIN TRANSACTION}
96: for {set i 1} {$i<=200} {incr i} {
97: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
98: }
99: execsql {COMMIT}
100: execsql {SELECT count(*) FROM table1}
101: } {200}
102: do_test delete-5.2.2 {
103: execsql {DELETE FROM table1}
104: } {200}
105: do_test delete-5.2.3 {
106: execsql {BEGIN TRANSACTION}
107: for {set i 1} {$i<=200} {incr i} {
108: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
109: }
110: execsql {COMMIT}
111: execsql {SELECT count(*) FROM table1}
112: } {200}
113: do_test delete-5.2.4 {
114: execsql {PRAGMA count_changes=off}
115: execsql {DELETE FROM table1}
116: } {}
117: do_test delete-5.2.5 {
118: execsql {SELECT count(*) FROM table1}
119: } {0}
120: do_test delete-5.2.6 {
121: execsql {BEGIN TRANSACTION}
122: for {set i 1} {$i<=200} {incr i} {
123: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
124: }
125: execsql {COMMIT}
126: execsql {SELECT count(*) FROM table1}
127: } {200}
128: do_test delete-5.3 {
129: for {set i 1} {$i<=200} {incr i 4} {
130: execsql "DELETE FROM table1 WHERE f1==$i"
131: }
132: execsql {SELECT count(*) FROM table1}
133: } {150}
134: do_test delete-5.4.1 {
135: execsql "DELETE FROM table1 WHERE f1>50"
136: db changes
137: } [db one {SELECT count(*) FROM table1 WHERE f1>50}]
138: do_test delete-5.4.2 {
139: execsql {SELECT count(*) FROM table1}
140: } {37}
141: do_test delete-5.5 {
142: for {set i 1} {$i<=70} {incr i 3} {
143: execsql "DELETE FROM table1 WHERE f1==$i"
144: }
145: execsql {SELECT f1 FROM table1 ORDER BY f1}
146: } {2 3 6 8 11 12 14 15 18 20 23 24 26 27 30 32 35 36 38 39 42 44 47 48 50}
147: do_test delete-5.6 {
148: for {set i 1} {$i<40} {incr i} {
149: execsql "DELETE FROM table1 WHERE f1==$i"
150: }
151: execsql {SELECT f1 FROM table1 ORDER BY f1}
152: } {42 44 47 48 50}
153: do_test delete-5.7 {
154: execsql "DELETE FROM table1 WHERE f1!=48"
155: execsql {SELECT f1 FROM table1 ORDER BY f1}
156: } {48}
157: integrity_check delete-5.8
158:
159:
160: # Delete large quantities of data. We want to test the List overflow
161: # mechanism in the vdbe.
162: #
163: do_test delete-6.1 {
164: execsql {BEGIN; DELETE FROM table1}
165: for {set i 1} {$i<=3000} {incr i} {
166: execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
167: }
168: execsql {DELETE FROM table2}
169: for {set i 1} {$i<=3000} {incr i} {
170: execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
171: }
172: execsql {COMMIT}
173: execsql {SELECT count(*) FROM table1}
174: } {3000}
175: do_test delete-6.2 {
176: execsql {SELECT count(*) FROM table2}
177: } {3000}
178: do_test delete-6.3 {
179: execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
180: } {1 2 3 4 5 6 7 8 9}
181: do_test delete-6.4 {
182: execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
183: } {1 2 3 4 5 6 7 8 9}
184: do_test delete-6.5.1 {
185: execsql {DELETE FROM table1 WHERE f1>7}
186: db changes
187: } {2993}
188: do_test delete-6.5.2 {
189: execsql {SELECT f1 FROM table1 ORDER BY f1}
190: } {1 2 3 4 5 6 7}
191: do_test delete-6.6 {
192: execsql {DELETE FROM table2 WHERE f1>7}
193: execsql {SELECT f1 FROM table2 ORDER BY f1}
194: } {1 2 3 4 5 6 7}
195: do_test delete-6.7 {
196: execsql {DELETE FROM table1}
197: execsql {SELECT f1 FROM table1}
198: } {}
199: do_test delete-6.8 {
200: execsql {INSERT INTO table1 VALUES(2,3)}
201: execsql {SELECT f1 FROM table1}
202: } {2}
203: do_test delete-6.9 {
204: execsql {DELETE FROM table2}
205: execsql {SELECT f1 FROM table2}
206: } {}
207: do_test delete-6.10 {
208: execsql {INSERT INTO table2 VALUES(2,3)}
209: execsql {SELECT f1 FROM table2}
210: } {2}
211: integrity_check delete-6.11
212:
213: do_test delete-7.1 {
214: execsql {
215: CREATE TABLE t3(a);
216: INSERT INTO t3 VALUES(1);
217: INSERT INTO t3 SELECT a+1 FROM t3;
218: INSERT INTO t3 SELECT a+2 FROM t3;
219: SELECT * FROM t3;
220: }
221: } {1 2 3 4}
222: ifcapable {trigger} {
223: do_test delete-7.2 {
224: execsql {
225: CREATE TABLE cnt(del);
226: INSERT INTO cnt VALUES(0);
227: CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
228: UPDATE cnt SET del=del+1;
229: END;
230: DELETE FROM t3 WHERE a<2;
231: SELECT * FROM t3;
232: }
233: } {2 3 4}
234: do_test delete-7.3 {
235: execsql {
236: SELECT * FROM cnt;
237: }
238: } {1}
239: do_test delete-7.4 {
240: execsql {
241: DELETE FROM t3;
242: SELECT * FROM t3;
243: }
244: } {}
245: do_test delete-7.5 {
246: execsql {
247: SELECT * FROM cnt;
248: }
249: } {4}
250: do_test delete-7.6 {
251: execsql {
252: INSERT INTO t3 VALUES(1);
253: INSERT INTO t3 SELECT a+1 FROM t3;
254: INSERT INTO t3 SELECT a+2 FROM t3;
255: CREATE TABLE t4 AS SELECT * FROM t3;
256: PRAGMA count_changes=ON;
257: DELETE FROM t3;
258: DELETE FROM t4;
259: }
260: } {4 4}
261: } ;# endif trigger
262: ifcapable {!trigger} {
263: execsql {DELETE FROM t3}
264: }
265: integrity_check delete-7.7
266:
267: # Make sure error messages are consistent when attempting to delete
268: # from a read-only database. Ticket #304.
269: #
270: do_test delete-8.0 {
271: execsql {
272: PRAGMA count_changes=OFF;
273: INSERT INTO t3 VALUES(123);
274: SELECT * FROM t3;
275: }
276: } {123}
277: db close
278: catch {forcedelete test.db-journal}
279: catch {file attributes test.db -permissions 0444}
280: catch {file attributes test.db -readonly 1}
281: sqlite3 db test.db
282: set ::DB [sqlite3_connection_pointer db]
283: do_test delete-8.1 {
284: catchsql {
285: DELETE FROM t3;
286: }
287: } {1 {attempt to write a readonly database}}
288: do_test delete-8.2 {
289: execsql {SELECT * FROM t3}
290: } {123}
291: do_test delete-8.3 {
292: catchsql {
293: DELETE FROM t3 WHERE 1;
294: }
295: } {1 {attempt to write a readonly database}}
296: do_test delete-8.4 {
297: execsql {SELECT * FROM t3}
298: } {123}
299:
300: # Update for v3: In v2 the DELETE statement would succeed because no
301: # database writes actually occur. Version 3 refuses to open a transaction
302: # on a read-only file, so the statement fails.
303: do_test delete-8.5 {
304: catchsql {
305: DELETE FROM t3 WHERE a<100;
306: }
307: # v2 result: {0 {}}
308: } {1 {attempt to write a readonly database}}
309: do_test delete-8.6 {
310: execsql {SELECT * FROM t3}
311: } {123}
312: integrity_check delete-8.7
313:
314: # Need to do the following for tcl 8.5 on mac. On that configuration, the
315: # -readonly flag is taken so seriously that a subsequent [forcedelete]
316: # (required before the next test file can be executed) will fail.
317: #
318: catch {file attributes test.db -readonly 0}
319: db close
320: forcedelete test.db test.db-journal
321:
322: # The following tests verify that SQLite correctly handles the case
323: # where an index B-Tree is being scanned, the rowid column being read
324: # from each index entry and another statement deletes some rows from
325: # the index B-Tree. At one point this (obscure) scenario was causing
326: # SQLite to return spurious SQLITE_CORRUPT errors and arguably incorrect
327: # query results.
328: #
329: do_test delete-9.1 {
330: sqlite3 db test.db
331: execsql {
332: CREATE TABLE t5(a, b);
333: CREATE TABLE t6(c, d);
334: INSERT INTO t5 VALUES(1, 2);
335: INSERT INTO t5 VALUES(3, 4);
336: INSERT INTO t5 VALUES(5, 6);
337: INSERT INTO t6 VALUES('a', 'b');
338: INSERT INTO t6 VALUES('c', 'd');
339: CREATE INDEX i5 ON t5(a);
340: CREATE INDEX i6 ON t6(c);
341: }
342: } {}
343: do_test delete-9.2 {
344: set res [list]
345: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
346: if {$r==2} { db eval { DELETE FROM t5 } }
347: lappend res $r $c $d
348: }
349: set res
350: } {1 a b 1 c d 2 a b {} c d}
351: do_test delete-9.3 {
352: execsql {
353: INSERT INTO t5 VALUES(1, 2);
354: INSERT INTO t5 VALUES(3, 4);
355: INSERT INTO t5 VALUES(5, 6);
356: }
357: set res [list]
358: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
359: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 2 } }
360: lappend res $r $c $d
361: }
362: set res
363: } {1 a b 1 c d 2 a b {} c d 3 a b 3 c d}
364: do_test delete-9.4 {
365: execsql {
366: DELETE FROM t5;
367: INSERT INTO t5 VALUES(1, 2);
368: INSERT INTO t5 VALUES(3, 4);
369: INSERT INTO t5 VALUES(5, 6);
370: }
371: set res [list]
372: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
373: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 1 } }
374: lappend res $r $c $d
375: }
376: set res
377: } {1 a b 1 c d 2 a b 2 c d 3 a b 3 c d}
378: do_test delete-9.5 {
379: execsql {
380: DELETE FROM t5;
381: INSERT INTO t5 VALUES(1, 2);
382: INSERT INTO t5 VALUES(3, 4);
383: INSERT INTO t5 VALUES(5, 6);
384: }
385: set res [list]
386: db eval { SELECT t5.rowid AS r, c, d FROM t5, t6 ORDER BY a } {
387: if {$r==2} { db eval { DELETE FROM t5 WHERE rowid = 3 } }
388: lappend res $r $c $d
389: }
390: set res
391: } {1 a b 1 c d 2 a b 2 c d}
392:
393:
394: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to delete.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