| blob | 167632350709af7368694421e60dbfd0a0a2522a |
2 #pragma ident "%Z%%M% %I% %E% SMI"
4 # 2001 September 15
5 #
6 # The author disclaims copyright to this source code. In place of
7 # a legal notice, here is a blessing:
8 #
9 # May you do good and not evil.
10 # May you find forgiveness for yourself and forgive others.
11 # May you share freely, never taking more than you give.
12 #
13 #***********************************************************************
14 # This file implements regression tests for SQLite library. The
15 # focus of this file is testing the DELETE FROM statement.
16 #
17 # $Id: delete.test,v 1.13 2003/06/15 23:42:25 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Try to delete from a non-existant table.
23 #
24 do_test delete-1.1 {
25 set v [catch {execsql {DELETE FROM test1}} msg]
26 lappend v $msg
27 } {1 {no such table: test1}}
29 # Try to delete from sqlite_master
30 #
31 do_test delete-2.1 {
32 set v [catch {execsql {DELETE FROM sqlite_master}} msg]
33 lappend v $msg
34 } {1 {table sqlite_master may not be modified}}
36 # Delete selected entries from a table with and without an index.
37 #
38 do_test delete-3.1.1 {
39 execsql {CREATE TABLE table1(f1 int, f2 int)}
40 execsql {INSERT INTO table1 VALUES(1,2)}
41 execsql {INSERT INTO table1 VALUES(2,4)}
42 execsql {INSERT INTO table1 VALUES(3,8)}
43 execsql {INSERT INTO table1 VALUES(4,16)}
44 execsql {SELECT * FROM table1 ORDER BY f1}
45 } {1 2 2 4 3 8 4 16}
46 do_test delete-3.1.2 {
47 execsql {DELETE FROM table1 WHERE f1=3}
48 } {}
49 do_test delete-3.1.3 {
50 execsql {SELECT * FROM table1 ORDER BY f1}
51 } {1 2 2 4 4 16}
52 do_test delete-3.1.4 {
53 execsql {CREATE INDEX index1 ON table1(f1)}
54 execsql {PRAGMA count_changes=on}
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 {
61 execsql {DELETE FROM table1 WHERE f1=2}
62 } {1}
63 do_test delete-3.1.7 {
64 execsql {SELECT * FROM table1 ORDER BY f1}
65 } {1 2 4 16}
66 integrity_check delete-3.2
69 # Semantic errors in the WHERE clause
70 #
71 do_test delete-4.1 {
72 execsql {CREATE TABLE table2(f1 int, f2 int)}
73 set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
74 lappend v $msg
75 } {1 {no such column: f3}}
77 do_test delete-4.2 {
78 set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
79 lappend v $msg
80 } {1 {no such function: xyzzy}}
81 integrity_check delete-4.3
83 # Lots of deletes
84 #
85 do_test delete-5.1.1 {
86 execsql {DELETE FROM table1}
87 } {2}
88 do_test delete-5.1.2 {
89 execsql {SELECT count(*) FROM table1}
90 } {0}
91 do_test delete-5.2.1 {
92 execsql {BEGIN TRANSACTION}
93 for {set i 1} {$i<=200} {incr i} {
94 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
95 }
96 execsql {COMMIT}
97 execsql {SELECT count(*) FROM table1}
98 } {200}
99 do_test delete-5.2.2 {
100 execsql {DELETE FROM table1}
101 } {200}
102 do_test delete-5.2.3 {
103 execsql {BEGIN TRANSACTION}
104 for {set i 1} {$i<=200} {incr i} {
105 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
106 }
107 execsql {COMMIT}
108 execsql {SELECT count(*) FROM table1}
109 } {200}
110 do_test delete-5.2.4 {
111 execsql {PRAGMA count_changes=off}
112 execsql {DELETE FROM table1}
113 } {}
114 do_test delete-5.2.5 {
115 execsql {SELECT count(*) FROM table1}
116 } {0}
117 do_test delete-5.2.6 {
118 execsql {BEGIN TRANSACTION}
119 for {set i 1} {$i<=200} {incr i} {
120 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
121 }
122 execsql {COMMIT}
123 execsql {SELECT count(*) FROM table1}
124 } {200}
125 do_test delete-5.3 {
126 for {set i 1} {$i<=200} {incr i 4} {
127 execsql "DELETE FROM table1 WHERE f1==$i"
128 }
129 execsql {SELECT count(*) FROM table1}
130 } {150}
131 do_test delete-5.4 {
132 execsql "DELETE FROM table1 WHERE f1>50"
133 execsql {SELECT count(*) FROM table1}
134 } {37}
135 do_test delete-5.5 {
136 for {set i 1} {$i<=70} {incr i 3} {
137 execsql "DELETE FROM table1 WHERE f1==$i"
138 }
139 execsql {SELECT f1 FROM table1 ORDER BY f1}
140 } {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}
141 do_test delete-5.6 {
142 for {set i 1} {$i<40} {incr i} {
143 execsql "DELETE FROM table1 WHERE f1==$i"
144 }
145 execsql {SELECT f1 FROM table1 ORDER BY f1}
146 } {42 44 47 48 50}
147 do_test delete-5.7 {
148 execsql "DELETE FROM table1 WHERE f1!=48"
149 execsql {SELECT f1 FROM table1 ORDER BY f1}
150 } {48}
151 integrity_check delete-5.8
154 # Delete large quantities of data. We want to test the List overflow
155 # mechanism in the vdbe.
156 #
157 do_test delete-6.1 {
158 set fd [open data1.txt w]
159 for {set i 1} {$i<=3000} {incr i} {
160 puts $fd "[expr {$i}]\t[expr {$i*$i}]"
161 }
162 close $fd
163 execsql {DELETE FROM table1}
164 execsql {COPY table1 FROM 'data1.txt'}
165 execsql {DELETE FROM table2}
166 execsql {COPY table2 FROM 'data1.txt'}
167 file delete data1.txt
168 execsql {SELECT count(*) FROM table1}
169 } {3000}
170 do_test delete-6.2 {
171 execsql {SELECT count(*) FROM table2}
172 } {3000}
173 do_test delete-6.3 {
174 execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
175 } {1 2 3 4 5 6 7 8 9}
176 do_test delete-6.4 {
177 execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
178 } {1 2 3 4 5 6 7 8 9}
179 do_test delete-6.5 {
180 execsql {DELETE FROM table1 WHERE f1>7}
181 execsql {SELECT f1 FROM table1 ORDER BY f1}
182 } {1 2 3 4 5 6 7}
183 do_test delete-6.6 {
184 execsql {DELETE FROM table2 WHERE f1>7}
185 execsql {SELECT f1 FROM table2 ORDER BY f1}
186 } {1 2 3 4 5 6 7}
187 do_test delete-6.7 {
188 execsql {DELETE FROM table1}
189 execsql {SELECT f1 FROM table1}
190 } {}
191 do_test delete-6.8 {
192 execsql {INSERT INTO table1 VALUES(2,3)}
193 execsql {SELECT f1 FROM table1}
194 } {2}
195 do_test delete-6.9 {
196 execsql {DELETE FROM table2}
197 execsql {SELECT f1 FROM table2}
198 } {}
199 do_test delete-6.10 {
200 execsql {INSERT INTO table2 VALUES(2,3)}
201 execsql {SELECT f1 FROM table2}
202 } {2}
203 integrity_check delete-6.11
205 do_test delete-7.1 {
206 execsql {
207 CREATE TABLE t3(a);
208 INSERT INTO t3 VALUES(1);
209 INSERT INTO t3 SELECT a+1 FROM t3;
210 INSERT INTO t3 SELECT a+2 FROM t3;
211 SELECT * FROM t3;
212 }
213 } {1 2 3 4}
214 do_test delete-7.2 {
215 execsql {
216 CREATE TABLE cnt(del);
217 INSERT INTO cnt VALUES(0);
218 CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
219 UPDATE cnt SET del=del+1;
220 END;
221 DELETE FROM t3 WHERE a<2;
222 SELECT * FROM t3;
223 }
224 } {2 3 4}
225 do_test delete-7.3 {
226 execsql {
227 SELECT * FROM cnt;
228 }
229 } {1}
230 do_test delete-7.4 {
231 execsql {
232 DELETE FROM t3;
233 SELECT * FROM t3;
234 }
235 } {}
236 do_test delete-7.5 {
237 execsql {
238 SELECT * FROM cnt;
239 }
240 } {4}
241 do_test delete-7.6 {
242 execsql {
243 INSERT INTO t3 VALUES(1);
244 INSERT INTO t3 SELECT a+1 FROM t3;
245 INSERT INTO t3 SELECT a+2 FROM t3;
246 CREATE TABLE t4 AS SELECT * FROM t3;
247 PRAGMA count_changes=ON;
248 DELETE FROM t3;
249 DELETE FROM t4;
250 }
251 } {4 4}
252 integrity_check delete-7.7
254 # Make sure error messages are consistent when attempting to delete
255 # from a read-only database. Ticket #304.
256 #
257 do_test delete-8.0 {
258 execsql {
259 PRAGMA count_changes=OFF;
260 INSERT INTO t3 VALUES(123);
261 SELECT * FROM t3;
262 }
263 } {123}
264 db close
265 catch {file attributes test.db -permissions 0444}
266 catch {file attributes test.db -readonly 1}
267 sqlite db test.db
268 do_test delete-8.1 {
269 catchsql {
270 DELETE FROM t3;
271 }
272 } {1 {attempt to write a readonly database}}
273 do_test delete-8.2 {
274 execsql {SELECT * FROM t3}
275 } {123}
276 do_test delete-8.3 {
277 catchsql {
278 DELETE FROM t3 WHERE 1;
279 }
280 } {1 {attempt to write a readonly database}}
281 do_test delete-8.4 {
282 execsql {SELECT * FROM t3}
283 } {123}
284 do_test delete-8.5 {
285 catchsql {
286 DELETE FROM t3 WHERE a<100;
287 }
288 } {0 {}}
289 do_test delete-8.6 {
290 execsql {SELECT * FROM t3}
291 } {123}
292 integrity_check delete-8.7
294 finish_test