# 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.23 2008/04/19 20:53:26 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

 } 1

 do_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.11

 do_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 trigger

 ifcapable {!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 close

 catch {file delete -force test.db-journal}

 catch {file attributes test.db -permissions 0444}

 catch {file attributes test.db -readonly 1}

 sqlite3 db test.db

 set ::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.7

 # Need to do the following for tcl 8.5 on mac. On that configuration, the

 # -readonly flag is taken so seriously that a subsequent [file delete -force]

 # (required before the next test file can be executed) will fail.

 #

 catch {file attributes test.db -readonly 0}

 finish_test