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