1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/delete.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,320 @@
1.4 +# 2001 September 15
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. The
1.15 +# focus of this file is testing the DELETE FROM statement.
1.16 +#
1.17 +# $Id: delete.test,v 1.23 2008/04/19 20:53:26 drh Exp $
1.18 +
1.19 +set testdir [file dirname $argv0]
1.20 +source $testdir/tester.tcl
1.21 +
1.22 +# Try to delete from a non-existant table.
1.23 +#
1.24 +do_test delete-1.1 {
1.25 + set v [catch {execsql {DELETE FROM test1}} msg]
1.26 + lappend v $msg
1.27 +} {1 {no such table: test1}}
1.28 +
1.29 +# Try to delete from sqlite_master
1.30 +#
1.31 +do_test delete-2.1 {
1.32 + set v [catch {execsql {DELETE FROM sqlite_master}} msg]
1.33 + lappend v $msg
1.34 +} {1 {table sqlite_master may not be modified}}
1.35 +
1.36 +# Delete selected entries from a table with and without an index.
1.37 +#
1.38 +do_test delete-3.1.1 {
1.39 + execsql {CREATE TABLE table1(f1 int, f2 int)}
1.40 + execsql {INSERT INTO table1 VALUES(1,2)}
1.41 + execsql {INSERT INTO table1 VALUES(2,4)}
1.42 + execsql {INSERT INTO table1 VALUES(3,8)}
1.43 + execsql {INSERT INTO table1 VALUES(4,16)}
1.44 + execsql {SELECT * FROM table1 ORDER BY f1}
1.45 +} {1 2 2 4 3 8 4 16}
1.46 +do_test delete-3.1.2 {
1.47 + execsql {DELETE FROM table1 WHERE f1=3}
1.48 +} {}
1.49 +do_test delete-3.1.3 {
1.50 + execsql {SELECT * FROM table1 ORDER BY f1}
1.51 +} {1 2 2 4 4 16}
1.52 +do_test delete-3.1.4 {
1.53 + execsql {CREATE INDEX index1 ON table1(f1)}
1.54 + execsql {PRAGMA count_changes=on}
1.55 + ifcapable explain {
1.56 + execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
1.57 + }
1.58 + execsql {DELETE FROM 'table1' WHERE f1=3}
1.59 +} {0}
1.60 +do_test delete-3.1.5 {
1.61 + execsql {SELECT * FROM table1 ORDER BY f1}
1.62 +} {1 2 2 4 4 16}
1.63 +do_test delete-3.1.6.1 {
1.64 + execsql {DELETE FROM table1 WHERE f1=2}
1.65 +} {1}
1.66 +do_test delete-3.1.6.2 {
1.67 + db changes
1.68 +} 1
1.69 +do_test delete-3.1.7 {
1.70 + execsql {SELECT * FROM table1 ORDER BY f1}
1.71 +} {1 2 4 16}
1.72 +integrity_check delete-3.2
1.73 +
1.74 +
1.75 +# Semantic errors in the WHERE clause
1.76 +#
1.77 +do_test delete-4.1 {
1.78 + execsql {CREATE TABLE table2(f1 int, f2 int)}
1.79 + set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
1.80 + lappend v $msg
1.81 +} {1 {no such column: f3}}
1.82 +
1.83 +do_test delete-4.2 {
1.84 + set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
1.85 + lappend v $msg
1.86 +} {1 {no such function: xyzzy}}
1.87 +integrity_check delete-4.3
1.88 +
1.89 +# Lots of deletes
1.90 +#
1.91 +do_test delete-5.1.1 {
1.92 + execsql {DELETE FROM table1}
1.93 +} {2}
1.94 +do_test delete-5.1.2 {
1.95 + execsql {SELECT count(*) FROM table1}
1.96 +} {0}
1.97 +do_test delete-5.2.1 {
1.98 + execsql {BEGIN TRANSACTION}
1.99 + for {set i 1} {$i<=200} {incr i} {
1.100 + execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
1.101 + }
1.102 + execsql {COMMIT}
1.103 + execsql {SELECT count(*) FROM table1}
1.104 +} {200}
1.105 +do_test delete-5.2.2 {
1.106 + execsql {DELETE FROM table1}
1.107 +} {200}
1.108 +do_test delete-5.2.3 {
1.109 + execsql {BEGIN TRANSACTION}
1.110 + for {set i 1} {$i<=200} {incr i} {
1.111 + execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
1.112 + }
1.113 + execsql {COMMIT}
1.114 + execsql {SELECT count(*) FROM table1}
1.115 +} {200}
1.116 +do_test delete-5.2.4 {
1.117 + execsql {PRAGMA count_changes=off}
1.118 + execsql {DELETE FROM table1}
1.119 +} {}
1.120 +do_test delete-5.2.5 {
1.121 + execsql {SELECT count(*) FROM table1}
1.122 +} {0}
1.123 +do_test delete-5.2.6 {
1.124 + execsql {BEGIN TRANSACTION}
1.125 + for {set i 1} {$i<=200} {incr i} {
1.126 + execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
1.127 + }
1.128 + execsql {COMMIT}
1.129 + execsql {SELECT count(*) FROM table1}
1.130 +} {200}
1.131 +do_test delete-5.3 {
1.132 + for {set i 1} {$i<=200} {incr i 4} {
1.133 + execsql "DELETE FROM table1 WHERE f1==$i"
1.134 + }
1.135 + execsql {SELECT count(*) FROM table1}
1.136 +} {150}
1.137 +do_test delete-5.4.1 {
1.138 + execsql "DELETE FROM table1 WHERE f1>50"
1.139 + db changes
1.140 +} [db one {SELECT count(*) FROM table1 WHERE f1>50}]
1.141 +do_test delete-5.4.2 {
1.142 + execsql {SELECT count(*) FROM table1}
1.143 +} {37}
1.144 +do_test delete-5.5 {
1.145 + for {set i 1} {$i<=70} {incr i 3} {
1.146 + execsql "DELETE FROM table1 WHERE f1==$i"
1.147 + }
1.148 + execsql {SELECT f1 FROM table1 ORDER BY f1}
1.149 +} {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}
1.150 +do_test delete-5.6 {
1.151 + for {set i 1} {$i<40} {incr i} {
1.152 + execsql "DELETE FROM table1 WHERE f1==$i"
1.153 + }
1.154 + execsql {SELECT f1 FROM table1 ORDER BY f1}
1.155 +} {42 44 47 48 50}
1.156 +do_test delete-5.7 {
1.157 + execsql "DELETE FROM table1 WHERE f1!=48"
1.158 + execsql {SELECT f1 FROM table1 ORDER BY f1}
1.159 +} {48}
1.160 +integrity_check delete-5.8
1.161 +
1.162 +
1.163 +# Delete large quantities of data. We want to test the List overflow
1.164 +# mechanism in the vdbe.
1.165 +#
1.166 +do_test delete-6.1 {
1.167 + execsql {BEGIN; DELETE FROM table1}
1.168 + for {set i 1} {$i<=3000} {incr i} {
1.169 + execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
1.170 + }
1.171 + execsql {DELETE FROM table2}
1.172 + for {set i 1} {$i<=3000} {incr i} {
1.173 + execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
1.174 + }
1.175 + execsql {COMMIT}
1.176 + execsql {SELECT count(*) FROM table1}
1.177 +} {3000}
1.178 +do_test delete-6.2 {
1.179 + execsql {SELECT count(*) FROM table2}
1.180 +} {3000}
1.181 +do_test delete-6.3 {
1.182 + execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
1.183 +} {1 2 3 4 5 6 7 8 9}
1.184 +do_test delete-6.4 {
1.185 + execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
1.186 +} {1 2 3 4 5 6 7 8 9}
1.187 +do_test delete-6.5.1 {
1.188 + execsql {DELETE FROM table1 WHERE f1>7}
1.189 + db changes
1.190 +} {2993}
1.191 +do_test delete-6.5.2 {
1.192 + execsql {SELECT f1 FROM table1 ORDER BY f1}
1.193 +} {1 2 3 4 5 6 7}
1.194 +do_test delete-6.6 {
1.195 + execsql {DELETE FROM table2 WHERE f1>7}
1.196 + execsql {SELECT f1 FROM table2 ORDER BY f1}
1.197 +} {1 2 3 4 5 6 7}
1.198 +do_test delete-6.7 {
1.199 + execsql {DELETE FROM table1}
1.200 + execsql {SELECT f1 FROM table1}
1.201 +} {}
1.202 +do_test delete-6.8 {
1.203 + execsql {INSERT INTO table1 VALUES(2,3)}
1.204 + execsql {SELECT f1 FROM table1}
1.205 +} {2}
1.206 +do_test delete-6.9 {
1.207 + execsql {DELETE FROM table2}
1.208 + execsql {SELECT f1 FROM table2}
1.209 +} {}
1.210 +do_test delete-6.10 {
1.211 + execsql {INSERT INTO table2 VALUES(2,3)}
1.212 + execsql {SELECT f1 FROM table2}
1.213 +} {2}
1.214 +integrity_check delete-6.11
1.215 +
1.216 +do_test delete-7.1 {
1.217 + execsql {
1.218 + CREATE TABLE t3(a);
1.219 + INSERT INTO t3 VALUES(1);
1.220 + INSERT INTO t3 SELECT a+1 FROM t3;
1.221 + INSERT INTO t3 SELECT a+2 FROM t3;
1.222 + SELECT * FROM t3;
1.223 + }
1.224 +} {1 2 3 4}
1.225 +ifcapable {trigger} {
1.226 + do_test delete-7.2 {
1.227 + execsql {
1.228 + CREATE TABLE cnt(del);
1.229 + INSERT INTO cnt VALUES(0);
1.230 + CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
1.231 + UPDATE cnt SET del=del+1;
1.232 + END;
1.233 + DELETE FROM t3 WHERE a<2;
1.234 + SELECT * FROM t3;
1.235 + }
1.236 + } {2 3 4}
1.237 + do_test delete-7.3 {
1.238 + execsql {
1.239 + SELECT * FROM cnt;
1.240 + }
1.241 + } {1}
1.242 + do_test delete-7.4 {
1.243 + execsql {
1.244 + DELETE FROM t3;
1.245 + SELECT * FROM t3;
1.246 + }
1.247 + } {}
1.248 + do_test delete-7.5 {
1.249 + execsql {
1.250 + SELECT * FROM cnt;
1.251 + }
1.252 + } {4}
1.253 + do_test delete-7.6 {
1.254 + execsql {
1.255 + INSERT INTO t3 VALUES(1);
1.256 + INSERT INTO t3 SELECT a+1 FROM t3;
1.257 + INSERT INTO t3 SELECT a+2 FROM t3;
1.258 + CREATE TABLE t4 AS SELECT * FROM t3;
1.259 + PRAGMA count_changes=ON;
1.260 + DELETE FROM t3;
1.261 + DELETE FROM t4;
1.262 + }
1.263 + } {4 4}
1.264 +} ;# endif trigger
1.265 +ifcapable {!trigger} {
1.266 + execsql {DELETE FROM t3}
1.267 +}
1.268 +integrity_check delete-7.7
1.269 +
1.270 +# Make sure error messages are consistent when attempting to delete
1.271 +# from a read-only database. Ticket #304.
1.272 +#
1.273 +do_test delete-8.0 {
1.274 + execsql {
1.275 + PRAGMA count_changes=OFF;
1.276 + INSERT INTO t3 VALUES(123);
1.277 + SELECT * FROM t3;
1.278 + }
1.279 +} {123}
1.280 +db close
1.281 +catch {file delete -force test.db-journal}
1.282 +catch {file attributes test.db -permissions 0444}
1.283 +catch {file attributes test.db -readonly 1}
1.284 +sqlite3 db test.db
1.285 +set ::DB [sqlite3_connection_pointer db]
1.286 +do_test delete-8.1 {
1.287 + catchsql {
1.288 + DELETE FROM t3;
1.289 + }
1.290 +} {1 {attempt to write a readonly database}}
1.291 +do_test delete-8.2 {
1.292 + execsql {SELECT * FROM t3}
1.293 +} {123}
1.294 +do_test delete-8.3 {
1.295 + catchsql {
1.296 + DELETE FROM t3 WHERE 1;
1.297 + }
1.298 +} {1 {attempt to write a readonly database}}
1.299 +do_test delete-8.4 {
1.300 + execsql {SELECT * FROM t3}
1.301 +} {123}
1.302 +
1.303 +# Update for v3: In v2 the DELETE statement would succeed because no
1.304 +# database writes actually occur. Version 3 refuses to open a transaction
1.305 +# on a read-only file, so the statement fails.
1.306 +do_test delete-8.5 {
1.307 + catchsql {
1.308 + DELETE FROM t3 WHERE a<100;
1.309 + }
1.310 +# v2 result: {0 {}}
1.311 +} {1 {attempt to write a readonly database}}
1.312 +do_test delete-8.6 {
1.313 + execsql {SELECT * FROM t3}
1.314 +} {123}
1.315 +integrity_check delete-8.7
1.316 +
1.317 +# Need to do the following for tcl 8.5 on mac. On that configuration, the
1.318 +# -readonly flag is taken so seriously that a subsequent [file delete -force]
1.319 +# (required before the next test file can be executed) will fail.
1.320 +#
1.321 +catch {file attributes test.db -readonly 0}
1.322 +
1.323 +finish_test