# 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.21 2006/01/03 00:33:50 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}  ifcapable explain {    execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}  }  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.1 {  execsql {DELETE FROM table1 WHERE f1=2}} {1}do_test delete-3.1.6.2 {  db changes} 1do_test delete-3.1.7 {  execsql {SELECT * FROM table1 ORDER BY f1}} {1 2 4 16}integrity_check delete-3.2# 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}}integrity_check delete-4.3# 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.1 {  execsql "DELETE FROM table1 WHERE f1>50"  db changes} [db one {SELECT count(*) FROM table1 WHERE f1>50}]do_test delete-5.4.2 {  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}integrity_check delete-5.8# Delete large quantities of data.  We want to test the List overflow# mechanism in the vdbe.#do_test delete-6.1 {  execsql {BEGIN; DELETE FROM table1}  for {set i 1} {$i<=3000} {incr i} {    execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"  }  execsql {DELETE FROM table2}  for {set i 1} {$i<=3000} {incr i} {    execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"  }  execsql {COMMIT}  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.1 {  execsql {DELETE FROM table1 WHERE f1>7}  db changes} {2993}do_test delete-6.5.2 {  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}integrity_check delete-6.11do_test delete-7.1 {  execsql {    CREATE TABLE t3(a);    INSERT INTO t3 VALUES(1);    INSERT INTO t3 SELECT a+1 FROM t3;    INSERT INTO t3 SELECT a+2 FROM t3;    SELECT * FROM t3;  }} {1 2 3 4}ifcapable {trigger} {  do_test delete-7.2 {    execsql {      CREATE TABLE cnt(del);      INSERT INTO cnt VALUES(0);      CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN        UPDATE cnt SET del=del+1;      END;      DELETE FROM t3 WHERE a<2;      SELECT * FROM t3;    }  } {2 3 4}  do_test delete-7.3 {    execsql {      SELECT * FROM cnt;    }  } {1}  do_test delete-7.4 {    execsql {      DELETE FROM t3;      SELECT * FROM t3;    }  } {}  do_test delete-7.5 {    execsql {      SELECT * FROM cnt;    }  } {4}  do_test delete-7.6 {    execsql {      INSERT INTO t3 VALUES(1);      INSERT INTO t3 SELECT a+1 FROM t3;      INSERT INTO t3 SELECT a+2 FROM t3;      CREATE TABLE t4 AS SELECT * FROM t3;      PRAGMA count_changes=ON;      DELETE FROM t3;      DELETE FROM t4;    }  } {4 4}} ;# endif triggerifcapable {!trigger} {  execsql {DELETE FROM t3}}integrity_check delete-7.7# Make sure error messages are consistent when attempting to delete# from a read-only database.  Ticket #304.#do_test delete-8.0 {  execsql {    PRAGMA count_changes=OFF;    INSERT INTO t3 VALUES(123);    SELECT * FROM t3;  }} {123}db closecatch {file attributes test.db -permissions 0444}catch {file attributes test.db -readonly 1}sqlite3 db test.dbset ::DB [sqlite3_connection_pointer db]do_test delete-8.1 {  catchsql {    DELETE FROM t3;  }} {1 {attempt to write a readonly database}}do_test delete-8.2 {  execsql {SELECT * FROM t3} } {123}do_test delete-8.3 {  catchsql {    DELETE FROM t3 WHERE 1;  }} {1 {attempt to write a readonly database}}do_test delete-8.4 {  execsql {SELECT * FROM t3} } {123}# Update for v3: In v2 the DELETE statement would succeed because no# database writes actually occur. Version 3 refuses to open a transaction# on a read-only file, so the statement fails.do_test delete-8.5 {  catchsql {    DELETE FROM t3 WHERE a<100;  }# v2 result: {0 {}}} {1 {attempt to write a readonly database}}do_test delete-8.6 {  execsql {SELECT * FROM t3}} {123}integrity_check delete-8.7finish_test