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