sl@0: # 2003 September 6
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 script is a test to replicate the bug reported by
sl@0: # ticket #842.
sl@0: #
sl@0: # Ticket #842 was a database corruption problem caused by a DELETE that
sl@0: # removed an index entry by not the main table entry.  To recreate the
sl@0: # problem do this:
sl@0: #
sl@0: #   (1) Create a table with an index.  Insert some data into that table.
sl@0: #   (2) Start a query on the table but do not complete the query.
sl@0: #   (3) Try to delete a single entry from the table.
sl@0: #
sl@0: # Step 3 will fail because there is still a read cursor on the table.
sl@0: # But the database is corrupted by the DELETE.  It turns out that the
sl@0: # index entry was deleted first, before the table entry.  And the index
sl@0: # delete worked.  Thus an entry was deleted from the index but not from
sl@0: # the table.
sl@0: #
sl@0: # The solution to the problem was to detect that the table is locked
sl@0: # before the index entry is deleted.
sl@0: #
sl@0: # $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $
sl@0: #
sl@0: 
sl@0: set testdir [file dirname $argv0]
sl@0: source $testdir/tester.tcl
sl@0: 
sl@0: # Create a table that has an index.
sl@0: #
sl@0: do_test delete2-1.1 {
sl@0:   set DB [sqlite3_connection_pointer db]
sl@0:   execsql {
sl@0:     CREATE TABLE q(s string, id string, constraint pk_q primary key(id));
sl@0:     BEGIN;
sl@0:     INSERT INTO q(s,id) VALUES('hello','id.1');
sl@0:     INSERT INTO q(s,id) VALUES('goodbye','id.2');
sl@0:     INSERT INTO q(s,id) VALUES('again','id.3');
sl@0:     END;
sl@0:     SELECT * FROM q;
sl@0:   }
sl@0: } {hello id.1 goodbye id.2 again id.3}
sl@0: do_test delete2-1.2 {
sl@0:   execsql {
sl@0:     SELECT * FROM q WHERE id='id.1';
sl@0:   }
sl@0: } {hello id.1}
sl@0: integrity_check delete2-1.3
sl@0: 
sl@0: # Start a query on the table.  The query should not use the index.
sl@0: # Do not complete the query, thus leaving the table locked.
sl@0: #
sl@0: do_test delete2-1.4 {
sl@0:   set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
sl@0:   sqlite3_step $STMT
sl@0: } SQLITE_ROW
sl@0: integrity_check delete2-1.5
sl@0: 
sl@0: # Try to delete a row from the table while a read is in process.
sl@0: # As of 2006-08-16, this is allowed.  (It used to fail with SQLITE_LOCKED.)
sl@0: #
sl@0: do_test delete2-1.6 {
sl@0:   catchsql {
sl@0:     DELETE FROM q WHERE rowid=1
sl@0:   }
sl@0: } {0 {}}
sl@0: integrity_check delete2-1.7
sl@0: do_test delete2-1.8 {
sl@0:   execsql {
sl@0:     SELECT * FROM q;
sl@0:   }
sl@0: } {goodbye id.2 again id.3}
sl@0: 
sl@0: # Finalize the query, thus clearing the lock on the table.  Then
sl@0: # retry the delete.  The delete should work this time.
sl@0: #
sl@0: do_test delete2-1.9 {
sl@0:   sqlite3_finalize $STMT
sl@0:   catchsql {
sl@0:     DELETE FROM q WHERE rowid=1
sl@0:   }
sl@0: } {0 {}}
sl@0: integrity_check delete2-1.10
sl@0: do_test delete2-1.11 {
sl@0:   execsql {
sl@0:     SELECT * FROM q;
sl@0:   }
sl@0: } {goodbye id.2 again id.3}
sl@0: 
sl@0: do_test delete2-2.1 {
sl@0:   execsql {
sl@0:     CREATE TABLE t1(a, b);
sl@0:     CREATE TABLE t2(c, d);
sl@0:     INSERT INTO t1 VALUES(1, 2);
sl@0:     INSERT INTO t2 VALUES(3, 4);
sl@0:     INSERT INTO t2 VALUES(5, 6);
sl@0:   }
sl@0: } {}
sl@0: do_test delete2-2.2 {
sl@0:   set res [list]
sl@0:   db eval {
sl@0:     SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2
sl@0:   } {
sl@0:     db eval {DELETE FROM t1}
sl@0:     lappend res $b $c $d
sl@0:   }
sl@0:   set res
sl@0: } {{} 3 4 {} 5 6}
sl@0: 
sl@0: finish_test