# 2003 September 6

#

# 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 script is a test to replicate the bug reported by

# ticket #842.

#

# Ticket #842 was a database corruption problem caused by a DELETE that

# removed an index entry by not the main table entry.  To recreate the

# problem do this:

#

#   (1) Create a table with an index.  Insert some data into that table.

#   (2) Start a query on the table but do not complete the query.

#   (3) Try to delete a single entry from the table.

#

# Step 3 will fail because there is still a read cursor on the table.

# But the database is corrupted by the DELETE.  It turns out that the

# index entry was deleted first, before the table entry.  And the index

# delete worked.  Thus an entry was deleted from the index but not from

# the table.

#

# The solution to the problem was to detect that the table is locked

# before the index entry is deleted.

#

# $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $

#

set testdir [file dirname $argv0]

source $testdir/tester.tcl

# Create a table that has an index.

#

do_test delete2-1.1 {

  set DB [sqlite3_connection_pointer db]

  execsql {

    CREATE TABLE q(s string, id string, constraint pk_q primary key(id));

    BEGIN;

    INSERT INTO q(s,id) VALUES('hello','id.1');

    INSERT INTO q(s,id) VALUES('goodbye','id.2');

    INSERT INTO q(s,id) VALUES('again','id.3');

    END;

    SELECT * FROM q;

  }

} {hello id.1 goodbye id.2 again id.3}

do_test delete2-1.2 {

  execsql {

    SELECT * FROM q WHERE id='id.1';

  }

} {hello id.1}

integrity_check delete2-1.3

# Start a query on the table.  The query should not use the index.

# Do not complete the query, thus leaving the table locked.

#

do_test delete2-1.4 {

  set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]

  sqlite3_step $STMT

} SQLITE_ROW

integrity_check delete2-1.5

# Try to delete a row from the table while a read is in process.

# As of 2006-08-16, this is allowed.  (It used to fail with SQLITE_LOCKED.)

#

do_test delete2-1.6 {

  catchsql {

    DELETE FROM q WHERE rowid=1

  }

} {0 {}}

integrity_check delete2-1.7

do_test delete2-1.8 {

  execsql {

    SELECT * FROM q;

  }

} {goodbye id.2 again id.3}

# Finalize the query, thus clearing the lock on the table.  Then

# retry the delete.  The delete should work this time.

#

do_test delete2-1.9 {

  sqlite3_finalize $STMT

  catchsql {

    DELETE FROM q WHERE rowid=1

  }

} {0 {}}

integrity_check delete2-1.10

do_test delete2-1.11 {

  execsql {

    SELECT * FROM q;

  }

} {goodbye id.2 again id.3}

do_test delete2-2.1 {

  execsql {

    CREATE TABLE t1(a, b);

    CREATE TABLE t2(c, d);

    INSERT INTO t1 VALUES(1, 2);

    INSERT INTO t2 VALUES(3, 4);

    INSERT INTO t2 VALUES(5, 6);

  }

} {}

do_test delete2-2.2 {

  set res [list]

  db eval {

    SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2

  } {

    db eval {DELETE FROM t1}

    lappend res $b $c $d

  }

  set res

} {{} 3 4 {} 5 6}

finish_test