File: delete.test

package info (click to toggle)
sqlite 2.4.7-1
  • links: PTS
  • area: main
  • in suites: woody
  • size: 3,080 kB
  • ctags: 1,881
  • sloc: ansic: 21,330; tcl: 7,046; sh: 6,830; yacc: 474; makefile: 464; awk: 73
file content (198 lines) | stat: -rw-r--r-- 5,457 bytes parent folder | download
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