# 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