sl@0: # 2001 September 15
sl@0: #
sl@0: # The author disclaims copyright to this source code.  In place of
sl@0: # a legal notice, here is a blessing:
sl@0: #
sl@0: #    May you do good and not evil.
sl@0: #    May you find forgiveness for yourself and forgive others.
sl@0: #    May you share freely, never taking more than you give.
sl@0: #
sl@0: #***********************************************************************
sl@0: # This file implements regression tests for SQLite library.  The
sl@0: # focus of this file is testing the DELETE FROM statement.
sl@0: #
sl@0: # $Id: delete.test,v 1.23 2008/04/19 20:53:26 drh Exp $
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Try to delete from a non-existant table.
sl@0: #
sl@0: do_test delete-1.1 {
sl@0:   set v [catch {execsql {DELETE FROM test1}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {no such table: test1}}
sl@0: 
sl@0: # Try to delete from sqlite_master
sl@0: #
sl@0: do_test delete-2.1 {
sl@0:   set v [catch {execsql {DELETE FROM sqlite_master}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {table sqlite_master may not be modified}}
sl@0: 
sl@0: # Delete selected entries from a table with and without an index.
sl@0: #
sl@0: do_test delete-3.1.1 {
sl@0:   execsql {CREATE TABLE table1(f1 int, f2 int)}
sl@0:   execsql {INSERT INTO table1 VALUES(1,2)}
sl@0:   execsql {INSERT INTO table1 VALUES(2,4)}
sl@0:   execsql {INSERT INTO table1 VALUES(3,8)}
sl@0:   execsql {INSERT INTO table1 VALUES(4,16)}
sl@0:   execsql {SELECT * FROM table1 ORDER BY f1}
sl@0: } {1 2 2 4 3 8 4 16}
sl@0: do_test delete-3.1.2 {
sl@0:   execsql {DELETE FROM table1 WHERE f1=3}
sl@0: } {}
sl@0: do_test delete-3.1.3 {
sl@0:   execsql {SELECT * FROM table1 ORDER BY f1}
sl@0: } {1 2 2 4 4 16}
sl@0: do_test delete-3.1.4 {
sl@0:   execsql {CREATE INDEX index1 ON table1(f1)}
sl@0:   execsql {PRAGMA count_changes=on}
sl@0:   ifcapable explain {
sl@0:     execsql {EXPLAIN DELETE FROM table1 WHERE f1=3}
sl@0:   }
sl@0:   execsql {DELETE FROM 'table1' WHERE f1=3}
sl@0: } {0}
sl@0: do_test delete-3.1.5 {
sl@0:   execsql {SELECT * FROM table1 ORDER BY f1}
sl@0: } {1 2 2 4 4 16}
sl@0: do_test delete-3.1.6.1 {
sl@0:   execsql {DELETE FROM table1 WHERE f1=2}
sl@0: } {1}
sl@0: do_test delete-3.1.6.2 {
sl@0:   db changes
sl@0: } 1
sl@0: do_test delete-3.1.7 {
sl@0:   execsql {SELECT * FROM table1 ORDER BY f1}
sl@0: } {1 2 4 16}
sl@0: integrity_check delete-3.2
sl@0: 
sl@0: 
sl@0: # Semantic errors in the WHERE clause
sl@0: #
sl@0: do_test delete-4.1 {
sl@0:   execsql {CREATE TABLE table2(f1 int, f2 int)}
sl@0:   set v [catch {execsql {DELETE FROM table2 WHERE f3=5}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {no such column: f3}}
sl@0: 
sl@0: do_test delete-4.2 {
sl@0:   set v [catch {execsql {DELETE FROM table2 WHERE xyzzy(f1+4)}} msg]
sl@0:   lappend v $msg
sl@0: } {1 {no such function: xyzzy}}
sl@0: integrity_check delete-4.3
sl@0: 
sl@0: # Lots of deletes
sl@0: #
sl@0: do_test delete-5.1.1 {
sl@0:   execsql {DELETE FROM table1}
sl@0: } {2}
sl@0: do_test delete-5.1.2 {
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {0}
sl@0: do_test delete-5.2.1 {
sl@0:   execsql {BEGIN TRANSACTION}
sl@0:   for {set i 1} {$i<=200} {incr i} {
sl@0:      execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {200}
sl@0: do_test delete-5.2.2 {
sl@0:   execsql {DELETE FROM table1}
sl@0: } {200}
sl@0: do_test delete-5.2.3 {
sl@0:   execsql {BEGIN TRANSACTION}
sl@0:   for {set i 1} {$i<=200} {incr i} {
sl@0:      execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {200}
sl@0: do_test delete-5.2.4 {
sl@0:   execsql {PRAGMA count_changes=off}
sl@0:   execsql {DELETE FROM table1}
sl@0: } {}
sl@0: do_test delete-5.2.5 {
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {0}
sl@0: do_test delete-5.2.6 {
sl@0:   execsql {BEGIN TRANSACTION}
sl@0:   for {set i 1} {$i<=200} {incr i} {
sl@0:      execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {200}
sl@0: do_test delete-5.3 {
sl@0:   for {set i 1} {$i<=200} {incr i 4} {
sl@0:      execsql "DELETE FROM table1 WHERE f1==$i"
sl@0:   }
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {150}
sl@0: do_test delete-5.4.1 {
sl@0:   execsql "DELETE FROM table1 WHERE f1>50"
sl@0:   db changes
sl@0: } [db one {SELECT count(*) FROM table1 WHERE f1>50}]
sl@0: do_test delete-5.4.2 {
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {37}
sl@0: do_test delete-5.5 {
sl@0:   for {set i 1} {$i<=70} {incr i 3} {
sl@0:      execsql "DELETE FROM table1 WHERE f1==$i"
sl@0:   }
sl@0:   execsql {SELECT f1 FROM table1 ORDER BY f1}
sl@0: } {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}
sl@0: do_test delete-5.6 {
sl@0:   for {set i 1} {$i<40} {incr i} {
sl@0:      execsql "DELETE FROM table1 WHERE f1==$i"
sl@0:   }
sl@0:   execsql {SELECT f1 FROM table1 ORDER BY f1}
sl@0: } {42 44 47 48 50}
sl@0: do_test delete-5.7 {
sl@0:   execsql "DELETE FROM table1 WHERE f1!=48"
sl@0:   execsql {SELECT f1 FROM table1 ORDER BY f1}
sl@0: } {48}
sl@0: integrity_check delete-5.8
sl@0: 
sl@0: 
sl@0: # Delete large quantities of data.  We want to test the List overflow
sl@0: # mechanism in the vdbe.
sl@0: #
sl@0: do_test delete-6.1 {
sl@0:   execsql {BEGIN; DELETE FROM table1}
sl@0:   for {set i 1} {$i<=3000} {incr i} {
sl@0:     execsql "INSERT INTO table1 VALUES($i,[expr {$i*$i}])"
sl@0:   }
sl@0:   execsql {DELETE FROM table2}
sl@0:   for {set i 1} {$i<=3000} {incr i} {
sl@0:     execsql "INSERT INTO table2 VALUES($i,[expr {$i*$i}])"
sl@0:   }
sl@0:   execsql {COMMIT}
sl@0:   execsql {SELECT count(*) FROM table1}
sl@0: } {3000}
sl@0: do_test delete-6.2 {
sl@0:   execsql {SELECT count(*) FROM table2}
sl@0: } {3000}
sl@0: do_test delete-6.3 {
sl@0:   execsql {SELECT f1 FROM table1 WHERE f1<10 ORDER BY f1}
sl@0: } {1 2 3 4 5 6 7 8 9}
sl@0: do_test delete-6.4 {
sl@0:   execsql {SELECT f1 FROM table2 WHERE f1<10 ORDER BY f1}
sl@0: } {1 2 3 4 5 6 7 8 9}
sl@0: do_test delete-6.5.1 {
sl@0:   execsql {DELETE FROM table1 WHERE f1>7}
sl@0:   db changes
sl@0: } {2993}
sl@0: do_test delete-6.5.2 {
sl@0:   execsql {SELECT f1 FROM table1 ORDER BY f1}
sl@0: } {1 2 3 4 5 6 7}
sl@0: do_test delete-6.6 {
sl@0:   execsql {DELETE FROM table2 WHERE f1>7}
sl@0:   execsql {SELECT f1 FROM table2 ORDER BY f1}
sl@0: } {1 2 3 4 5 6 7}
sl@0: do_test delete-6.7 {
sl@0:   execsql {DELETE FROM table1}
sl@0:   execsql {SELECT f1 FROM table1}
sl@0: } {}
sl@0: do_test delete-6.8 {
sl@0:   execsql {INSERT INTO table1 VALUES(2,3)}
sl@0:   execsql {SELECT f1 FROM table1}
sl@0: } {2}
sl@0: do_test delete-6.9 {
sl@0:   execsql {DELETE FROM table2}
sl@0:   execsql {SELECT f1 FROM table2}
sl@0: } {}
sl@0: do_test delete-6.10 {
sl@0:   execsql {INSERT INTO table2 VALUES(2,3)}
sl@0:   execsql {SELECT f1 FROM table2}
sl@0: } {2}
sl@0: integrity_check delete-6.11
sl@0: 
sl@0: do_test delete-7.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t3(a);
sl@0:     INSERT INTO t3 VALUES(1);
sl@0:     INSERT INTO t3 SELECT a+1 FROM t3;
sl@0:     INSERT INTO t3 SELECT a+2 FROM t3;
sl@0:     SELECT * FROM t3;
sl@0:   }
sl@0: } {1 2 3 4}
sl@0: ifcapable {trigger} {
sl@0:   do_test delete-7.2 {
sl@0:     execsql {
sl@0:       CREATE TABLE cnt(del);
sl@0:       INSERT INTO cnt VALUES(0);
sl@0:       CREATE TRIGGER r1 AFTER DELETE ON t3 FOR EACH ROW BEGIN
sl@0:         UPDATE cnt SET del=del+1;
sl@0:       END;
sl@0:       DELETE FROM t3 WHERE a<2;
sl@0:       SELECT * FROM t3;
sl@0:     }
sl@0:   } {2 3 4}
sl@0:   do_test delete-7.3 {
sl@0:     execsql {
sl@0:       SELECT * FROM cnt;
sl@0:     }
sl@0:   } {1}
sl@0:   do_test delete-7.4 {
sl@0:     execsql {
sl@0:       DELETE FROM t3;
sl@0:       SELECT * FROM t3;
sl@0:     }
sl@0:   } {}
sl@0:   do_test delete-7.5 {
sl@0:     execsql {
sl@0:       SELECT * FROM cnt;
sl@0:     }
sl@0:   } {4}
sl@0:   do_test delete-7.6 {
sl@0:     execsql {
sl@0:       INSERT INTO t3 VALUES(1);
sl@0:       INSERT INTO t3 SELECT a+1 FROM t3;
sl@0:       INSERT INTO t3 SELECT a+2 FROM t3;
sl@0:       CREATE TABLE t4 AS SELECT * FROM t3;
sl@0:       PRAGMA count_changes=ON;
sl@0:       DELETE FROM t3;
sl@0:       DELETE FROM t4;
sl@0:     }
sl@0:   } {4 4}
sl@0: } ;# endif trigger
sl@0: ifcapable {!trigger} {
sl@0:   execsql {DELETE FROM t3}
sl@0: }
sl@0: integrity_check delete-7.7
sl@0: 
sl@0: # Make sure error messages are consistent when attempting to delete
sl@0: # from a read-only database.  Ticket #304.
sl@0: #
sl@0: do_test delete-8.0 {
sl@0:   execsql {
sl@0:     PRAGMA count_changes=OFF;
sl@0:     INSERT INTO t3 VALUES(123);
sl@0:     SELECT * FROM t3;
sl@0:   }
sl@0: } {123}
sl@0: db close
sl@0: catch {file delete -force test.db-journal}
sl@0: catch {file attributes test.db -permissions 0444}
sl@0: catch {file attributes test.db -readonly 1}
sl@0: sqlite3 db test.db
sl@0: set ::DB [sqlite3_connection_pointer db]
sl@0: do_test delete-8.1 {
sl@0:   catchsql {
sl@0:     DELETE FROM t3;
sl@0:   }
sl@0: } {1 {attempt to write a readonly database}}
sl@0: do_test delete-8.2 {
sl@0:   execsql {SELECT * FROM t3} 
sl@0: } {123}
sl@0: do_test delete-8.3 {
sl@0:   catchsql {
sl@0:     DELETE FROM t3 WHERE 1;
sl@0:   }
sl@0: } {1 {attempt to write a readonly database}}
sl@0: do_test delete-8.4 {
sl@0:   execsql {SELECT * FROM t3} 
sl@0: } {123}
sl@0: 
sl@0: # Update for v3: In v2 the DELETE statement would succeed because no
sl@0: # database writes actually occur. Version 3 refuses to open a transaction
sl@0: # on a read-only file, so the statement fails.
sl@0: do_test delete-8.5 {
sl@0:   catchsql {
sl@0:     DELETE FROM t3 WHERE a<100;
sl@0:   }
sl@0: # v2 result: {0 {}}
sl@0: } {1 {attempt to write a readonly database}}
sl@0: do_test delete-8.6 {
sl@0:   execsql {SELECT * FROM t3}
sl@0: } {123}
sl@0: integrity_check delete-8.7
sl@0: 
sl@0: # Need to do the following for tcl 8.5 on mac. On that configuration, the
sl@0: # -readonly flag is taken so seriously that a subsequent [file delete -force]
sl@0: # (required before the next test file can be executed) will fail.
sl@0: #
sl@0: catch {file attributes test.db -readonly 0}
sl@0: 
sl@0: finish_test