2N/A#pragma ident "%Z%%M% %I% %E% SMI"
2N/A# The author disclaims copyright to this source code. In place of
2N/A# a legal notice, here is a blessing:
2N/A# May you do good and not evil.
2N/A# May you find forgiveness for yourself and forgive others.
2N/A# May you share freely, never taking more than you give.
2N/A#***********************************************************************
2N/A# This file implements regression tests for SQLite library. The
2N/A# focus of this file is testing the DELETE FROM statement.
2N/Aset testdir [file dirname $argv0]
2N/A# Try to delete from a non-existant table.
2N/A set v [catch {execsql {DELETE FROM test1}} msg]
2N/A} {1 {no such table: test1}}
2N/A# Try to delete from sqlite_master
2N/A set v [catch {execsql {DELETE FROM sqlite_master}} msg]
2N/A} {1 {table sqlite_master may not be modified}}
2N/A# Delete selected entries from a table with and without an index.
2N/Ado_test delete-3.1.1 {
2N/A execsql {CREATE TABLE table1(f1 int, f2 int)}
2N/A execsql {INSERT INTO table1 VALUES(1,2)}
2N/A execsql {INSERT INTO table1 VALUES(2,4)}
2N/A execsql {INSERT INTO table1 VALUES(3,8)}
2N/A execsql {INSERT INTO table1 VALUES(4,16)}
2N/A execsql {SELECT * FROM table1 ORDER BY f1}
2N/Ado_test delete-3.1.2 {
2N/A execsql {DELETE FROM table1 WHERE f1=3}
2N/Ado_test delete-3.1.3 {
2N/A execsql {SELECT * FROM table1 ORDER BY f1}
2N/Ado_test delete-3.1.4 {
2N/A execsql {CREATE INDEX index1 ON table1(f1)}
2N/A execsql {PRAGMA count_changes=on}
2N/A execsql {DELETE FROM 'table1' WHERE f1=3}
2N/Ado_test delete-3.1.5 {
2N/A execsql {SELECT * FROM table1 ORDER BY f1}
2N/Ado_test delete-3.1.6 {
2N/A execsql {DELETE FROM table1 WHERE f1=2}
2N/Ado_test delete-3.1.7 {
2N/A execsql {SELECT * FROM table1 ORDER BY f1}
2N/Aintegrity_check delete-3.2
2N/A# Semantic errors in the WHERE clause
2N/A execsql {CREATE TABLE table2(f1 int, f2 int)}
2N/A set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
2N/A} {1 {no such column: f3}}
2N/A set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
2N/A} {1 {no such function: xyzzy}}
2N/Aintegrity_check delete-4.3
2N/Ado_test delete-5.1.1 {
2N/A execsql {DELETE FROM table1}
2N/Ado_test delete-5.1.2 {
2N/A execsql {SELECT count(*) FROM table1}
2N/Ado_test delete-5.2.1 {
2N/A execsql {BEGIN TRANSACTION}
2N/A for {set i 1} {$i<=200} {incr i} {
2N/A execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
2N/A execsql {SELECT count(*) FROM table1}
2N/Ado_test delete-5.2.2 {
2N/A execsql {DELETE FROM table1}
2N/Ado_test delete-5.2.3 {
2N/A execsql {BEGIN TRANSACTION}
2N/A for {set i 1} {$i<=200} {incr i} {
2N/A execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
2N/A execsql {SELECT count(*) FROM table1}
2N/Ado_test delete-5.2.4 {
2N/A execsql {PRAGMA count_changes=off}
2N/A execsql {DELETE FROM table1}
2N/Ado_test delete-5.2.5 {
2N/A execsql {SELECT count(*) FROM table1}
2N/Ado_test delete-5.2.6 {
2N/A execsql {BEGIN TRANSACTION}
2N/A for {set i 1} {$i<=200} {incr i} {
2N/A execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
2N/A execsql {SELECT count(*) FROM table1}
2N/A for {set i 1} {$i<=200} {incr i 4} {
2N/A execsql "DELETE FROM table1 WHERE f1==$i"
2N/A execsql {SELECT count(*) FROM table1}
2N/A execsql "DELETE FROM table1 WHERE f1>50"
2N/A execsql {SELECT count(*) FROM table1}
2N/A for {set i 1} {$i<=70} {incr i 3} {
2N/A execsql "DELETE FROM table1 WHERE f1==$i"
2N/A execsql {SELECT f1 FROM table1 ORDER BY f1}
2N/A} {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}
2N/A for {set i 1} {$i<40} {incr i} {
2N/A execsql "DELETE FROM table1 WHERE f1==$i"
2N/A execsql {SELECT f1 FROM table1 ORDER BY f1}
2N/A execsql "DELETE FROM table1 WHERE f1!=48"
2N/A execsql {SELECT f1 FROM table1 ORDER BY f1}
2N/Aintegrity_check delete-5.8
2N/A# Delete large quantities of data. We want to test the List overflow
2N/A# mechanism in the vdbe.
2N/A for {set i 1} {$i<=3000} {incr i} {
2N/A puts $fd "[expr {$i}]\t[expr {$i*$i}]"
2N/A execsql {DELETE FROM table1}
2N/A execsql {DELETE FROM table2}
2N/A execsql {SELECT count(*) FROM table1}
2N/A execsql {SELECT count(*) FROM table2}
2N/A execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
2N/A} {1 2 3 4 5 6 7 8 9}
2N/A execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
2N/A} {1 2 3 4 5 6 7 8 9}
2N/A execsql {DELETE FROM table1 WHERE f1>7}
2N/A execsql {SELECT f1 FROM table1 ORDER BY f1}
2N/A execsql {DELETE FROM table2 WHERE f1>7}
2N/A execsql {SELECT f1 FROM table2 ORDER BY f1}
2N/A execsql {DELETE FROM table1}
2N/A execsql {SELECT f1 FROM table1}
2N/A execsql {INSERT INTO table1 VALUES(2,3)}
2N/A execsql {SELECT f1 FROM table1}
2N/A execsql {DELETE FROM table2}
2N/A execsql {SELECT f1 FROM table2}
2N/Ado_test delete-6.10 {
2N/A execsql {INSERT INTO table2 VALUES(2,3)}
2N/A execsql {SELECT f1 FROM table2}
2N/Aintegrity_check delete-6.11
2N/A INSERT INTO t3 VALUES(1);
2N/A INSERT INTO t3 SELECT a+1 FROM t3;
2N/A INSERT INTO t3 SELECT a+2 FROM t3;
2N/A CREATE TABLE cnt(del);
2N/A INSERT INTO cnt VALUES(0);
2N/A CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
2N/A UPDATE cnt SET del=del+1;
2N/A DELETE FROM t3 WHERE a<2;
2N/A INSERT INTO t3 VALUES(1);
2N/A INSERT INTO t3 SELECT a+1 FROM t3;
2N/A INSERT INTO t3 SELECT a+2 FROM t3;
2N/A CREATE TABLE t4 AS SELECT * FROM t3;
2N/A PRAGMA count_changes=ON;
2N/Aintegrity_check delete-7.7
2N/A# Make sure error messages are consistent when attempting to delete
2N/A# from a read-only database. Ticket #304.
2N/A PRAGMA count_changes=OFF;
2N/A INSERT INTO t3 VALUES(123);
2N/A} {1 {attempt to write a readonly database}}
2N/A execsql {SELECT * FROM t3}
2N/A DELETE FROM t3 WHERE 1;
2N/A} {1 {attempt to write a readonly database}}
2N/A execsql {SELECT * FROM t3}
2N/A DELETE FROM t3 WHERE a<100;
2N/A execsql {SELECT * FROM t3}
2N/Aintegrity_check delete-8.7