1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198
|
# 2001 September 15
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.10 2001/10/15 00:44:36 drh Exp $
set testdir [file dirname $argv0]
source $testdir/tester.tcl
# Try to delete from a non-existant table.
#
do_test delete-1.1 {
set v [catch {execsql {DELETE FROM test1}} msg]
lappend v $msg
} {1 {no such table: test1}}
# Try to delete from sqlite_master
#
do_test delete-2.1 {
set v [catch {execsql {DELETE FROM sqlite_master}} msg]
lappend v $msg
} {1 {table sqlite_master may not be modified}}
# Delete selected entries from a table with and without an index.
#
do_test delete-3.1.1 {
execsql {CREATE TABLE table1(f1 int, f2 int)}
execsql {INSERT INTO table1 VALUES(1,2)}
execsql {INSERT INTO table1 VALUES(2,4)}
execsql {INSERT INTO table1 VALUES(3,8)}
execsql {INSERT INTO table1 VALUES(4,16)}
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 3 8 4 16}
do_test delete-3.1.2 {
execsql {DELETE FROM table1 WHERE f1=3}
} {}
do_test delete-3.1.3 {
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1.4 {
execsql {CREATE INDEX index1 ON table1(f1)}
execsql {PRAGMA count_changes=on}
execsql {DELETE FROM 'table1' WHERE f1=3}
} {0}
do_test delete-3.1.5 {
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1.6 {
execsql {DELETE FROM table1 WHERE f1=2}
} {1}
do_test delete-3.1.7 {
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 4 16}
# Semantic errors in the WHERE clause
#
do_test delete-4.1 {
execsql {CREATE TABLE table2(f1 int, f2 int)}
set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
lappend v $msg
} {1 {no such column: f3}}
do_test delete-4.2 {
set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
lappend v $msg
} {1 {no such function: xyzzy}}
# Lots of deletes
#
do_test delete-5.1.1 {
execsql {DELETE FROM table1}
} {2}
do_test delete-5.1.2 {
execsql {SELECT count(*) FROM table1}
} {0}
do_test delete-5.2.1 {
execsql {BEGIN TRANSACTION}
for {set i 1} {$i<=200} {incr i} {
execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
}
execsql {COMMIT}
execsql {SELECT count(*) FROM table1}
} {200}
do_test delete-5.2.2 {
execsql {DELETE FROM table1}
} {200}
do_test delete-5.2.3 {
execsql {BEGIN TRANSACTION}
for {set i 1} {$i<=200} {incr i} {
execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
}
execsql {COMMIT}
execsql {SELECT count(*) FROM table1}
} {200}
do_test delete-5.2.4 {
execsql {PRAGMA count_changes=off}
execsql {DELETE FROM table1}
} {}
do_test delete-5.2.5 {
execsql {SELECT count(*) FROM table1}
} {0}
do_test delete-5.2.6 {
execsql {BEGIN TRANSACTION}
for {set i 1} {$i<=200} {incr i} {
execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
}
execsql {COMMIT}
execsql {SELECT count(*) FROM table1}
} {200}
do_test delete-5.3 {
for {set i 1} {$i<=200} {incr i 4} {
execsql "DELETE FROM table1 WHERE f1==$i"
}
execsql {SELECT count(*) FROM table1}
} {150}
do_test delete-5.4 {
execsql "DELETE FROM table1 WHERE f1>50"
execsql {SELECT count(*) FROM table1}
} {37}
do_test delete-5.5 {
for {set i 1} {$i<=70} {incr i 3} {
execsql "DELETE FROM table1 WHERE f1==$i"
}
execsql {SELECT f1 FROM table1 ORDER BY f1}
} {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}
do_test delete-5.6 {
for {set i 1} {$i<40} {incr i} {
execsql "DELETE FROM table1 WHERE f1==$i"
}
execsql {SELECT f1 FROM table1 ORDER BY f1}
} {42 44 47 48 50}
do_test delete-5.7 {
execsql "DELETE FROM table1 WHERE f1!=48"
execsql {SELECT f1 FROM table1 ORDER BY f1}
} {48}
# Delete large quantities of data. We want to test the List overflow
# mechanism in the vdbe.
#
do_test delete-6.1 {
set fd [open data1.txt w]
for {set i 1} {$i<=3000} {incr i} {
puts $fd "[expr {$i}]\t[expr {$i*$i}]"
}
close $fd
execsql {DELETE FROM table1}
execsql {COPY table1 FROM 'data1.txt'}
execsql {DELETE FROM table2}
execsql {COPY table2 FROM 'data1.txt'}
file delete data1.txt
execsql {SELECT count(*) FROM table1}
} {3000}
do_test delete-6.2 {
execsql {SELECT count(*) FROM table2}
} {3000}
do_test delete-6.3 {
execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
} {1 2 3 4 5 6 7 8 9}
do_test delete-6.4 {
execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
} {1 2 3 4 5 6 7 8 9}
do_test delete-6.5 {
execsql {DELETE FROM table1 WHERE f1>7}
execsql {SELECT f1 FROM table1 ORDER BY f1}
} {1 2 3 4 5 6 7}
do_test delete-6.6 {
execsql {DELETE FROM table2 WHERE f1>7}
execsql {SELECT f1 FROM table2 ORDER BY f1}
} {1 2 3 4 5 6 7}
do_test delete-6.7 {
execsql {DELETE FROM table1}
execsql {SELECT f1 FROM table1}
} {}
do_test delete-6.8 {
execsql {INSERT INTO table1 VALUES(2,3)}
execsql {SELECT f1 FROM table1}
} {2}
do_test delete-6.9 {
execsql {DELETE FROM table2}
execsql {SELECT f1 FROM table2}
} {}
do_test delete-6.10 {
execsql {INSERT INTO table2 VALUES(2,3)}
execsql {SELECT f1 FROM table2}
} {2}
finish_test
|