3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for SQLite library. The
12 # focus of this file is testing the DELETE FROM statement.
14 # $Id: delete.test,v 1.19 2005/05/27 09:41:13 danielk1977 Exp $
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
19 # Try to delete from a non-existant table.
22 set v [catch {execsql {DELETE FROM test1}} msg]
24 } {1 {no such table: test1}}
26 # Try to delete from sqlite_master
29 set v [catch {execsql {DELETE FROM sqlite_master}} msg]
31 } {1 {table sqlite_master may not be modified}}
33 # Delete selected entries from a table with and without an index.
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}
43 do_test delete-3.1.2 {
44 execsql {DELETE FROM table1 WHERE f1=3}
46 do_test delete-3.1.3 {
47 execsql {SELECT * FROM table1 ORDER BY f1}
49 do_test delete-3.1.4 {
50 execsql {CREATE INDEX index1 ON table1(f1)}
51 execsql {PRAGMA count_changes=on}
52 execsql {DELETE FROM 'table1' WHERE f1=3}
54 do_test delete-3.1.5 {
55 execsql {SELECT * FROM table1 ORDER BY f1}
57 do_test delete-3.1.6.1 {
58 execsql {DELETE FROM table1 WHERE f1=2}
60 do_test delete-3.1.6.2 {
63 do_test delete-3.1.7 {
64 execsql {SELECT * FROM table1 ORDER BY f1}
66 integrity_check delete-3.2
69 # Semantic errors in the WHERE clause
72 execsql {CREATE TABLE table2(f1 int, f2 int)}
73 set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
75 } {1 {no such column: f3}}
78 set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
80 } {1 {no such function: xyzzy}}
81 integrity_check delete-4.3
85 do_test delete-5.1.1 {
86 execsql {DELETE FROM table1}
88 do_test delete-5.1.2 {
89 execsql {SELECT count(*) FROM table1}
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}])"
97 execsql {SELECT count(*) FROM table1}
99 do_test delete-5.2.2 {
100 execsql {DELETE FROM table1}
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}])"
108 execsql {SELECT count(*) FROM table1}
110 do_test delete-5.2.4 {
111 execsql {PRAGMA count_changes=off}
112 execsql {DELETE FROM table1}
114 do_test delete-5.2.5 {
115 execsql {SELECT count(*) FROM table1}
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}])"
123 execsql {SELECT count(*) FROM table1}
126 for {set i 1} {$i<=200} {incr i 4} {
127 execsql "DELETE FROM table1 WHERE f1==$i"
129 execsql {SELECT count(*) FROM table1}
131 do_test delete-5.4.1 {
132 execsql "DELETE FROM table1 WHERE f1>50"
134 } [db one {SELECT count(*) FROM table1 WHERE f1>50}]
135 do_test delete-5.4.2 {
136 execsql {SELECT count(*) FROM table1}
139 for {set i 1} {$i<=70} {incr i 3} {
140 execsql "DELETE FROM table1 WHERE f1==$i"
142 execsql {SELECT f1 FROM table1 ORDER BY f1}
143 } {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}
145 for {set i 1} {$i<40} {incr i} {
146 execsql "DELETE FROM table1 WHERE f1==$i"
148 execsql {SELECT f1 FROM table1 ORDER BY f1}
151 execsql "DELETE FROM table1 WHERE f1!=48"
152 execsql {SELECT f1 FROM table1 ORDER BY f1}
154 integrity_check delete-5.8
157 # Delete large quantities of data. We want to test the List overflow
158 # mechanism in the vdbe.
161 execsql {BEGIN; DELETE FROM table1}
162 for {set i 1} {$i<=3000} {incr i} {
163 execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
165 execsql {DELETE FROM table2}
166 for {set i 1} {$i<=3000} {incr i} {
167 execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
170 execsql {SELECT count(*) FROM table1}
173 execsql {SELECT count(*) FROM table2}
176 execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
177 } {1 2 3 4 5 6 7 8 9}
179 execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
180 } {1 2 3 4 5 6 7 8 9}
181 do_test delete-6.5.1 {
182 execsql {DELETE FROM table1 WHERE f1>7}
185 do_test delete-6.5.2 {
186 execsql {SELECT f1 FROM table1 ORDER BY f1}
189 execsql {DELETE FROM table2 WHERE f1>7}
190 execsql {SELECT f1 FROM table2 ORDER BY f1}
193 execsql {DELETE FROM table1}
194 execsql {SELECT f1 FROM table1}
197 execsql {INSERT INTO table1 VALUES(2,3)}
198 execsql {SELECT f1 FROM table1}
201 execsql {DELETE FROM table2}
202 execsql {SELECT f1 FROM table2}
204 do_test delete-6.10 {
205 execsql {INSERT INTO table2 VALUES(2,3)}
206 execsql {SELECT f1 FROM table2}
208 integrity_check delete-6.11
213 INSERT INTO t3 VALUES(1);
214 INSERT INTO t3 SELECT a+1 FROM t3;
215 INSERT INTO t3 SELECT a+2 FROM t3;
219 ifcapable {trigger} {
222 CREATE TABLE cnt(del);
223 INSERT INTO cnt VALUES(0);
224 CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
225 UPDATE cnt SET del=del+1;
227 DELETE FROM t3 WHERE a<2;
249 INSERT INTO t3 VALUES(1);
250 INSERT INTO t3 SELECT a+1 FROM t3;
251 INSERT INTO t3 SELECT a+2 FROM t3;
252 CREATE TABLE t4 AS SELECT * FROM t3;
253 PRAGMA count_changes=ON;
259 ifcapable {!trigger} {
260 execsql {DELETE FROM t3}
262 integrity_check delete-7.7
264 # Make sure error messages are consistent when attempting to delete
265 # from a read-only database. Ticket #304.
269 PRAGMA count_changes=OFF;
270 INSERT INTO t3 VALUES(123);
275 catch {file attributes test.db -permissions 0444}
276 catch {file attributes test.db -readonly 1}
277 set ::DB [sqlite3 db test.db]
282 } {1 {attempt to write a readonly database}}
284 execsql {SELECT * FROM t3}
288 DELETE FROM t3 WHERE 1;
290 } {1 {attempt to write a readonly database}}
292 execsql {SELECT * FROM t3}
295 # Update for v3: In v2 the DELETE statement would succeed because no
296 # database writes actually occur. Version 3 refuses to open a transaction
297 # on a read-only file, so the statement fails.
300 DELETE FROM t3 WHERE a<100;
303 } {1 {attempt to write a readonly database}}
305 execsql {SELECT * FROM t3}
307 integrity_check delete-8.7