# Copyright (c) 1999, 2000 D. Richard Hipp
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public
# License as published by the Free Software Foundation; either
# version 2 of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public
# License along with this library; if not, write to the
# Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA 02111-1307, USA.
#
# Author contact information:
# drh@hwaci.com
# http://www.hwaci.com/drh/
#
#***********************************************************************
# This file implements regression tests for SQLite library. The
# focus of this file is testing the DELETE FROM statement.
#
# $Id: delete.test,v 1.7 2001/03/20 12:55:14 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.1a {
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.1b {
execsql {DELETE FROM table1 WHERE f1=3}
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1c {
execsql {CREATE INDEX index1 ON table1(f1)}
execsql {DELETE FROM 'table1' WHERE f1=3}
execsql {SELECT * FROM table1 ORDER BY f1}
} {1 2 2 4 4 16}
do_test delete-3.1d {
execsql {DELETE FROM table1 WHERE f1=2}
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 {
execsql {DELETE FROM table1}
execsql {SELECT count(*) FROM table1}
} {}
do_test delete-5.2 {
for {set i 1} {$i<=200} {incr i} {
execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
}
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}
finish_test