# 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