1# 2003 September 6 2# 3# The author disclaims copyright to this source code. In place of 4# a legal notice, here is a blessing: 5# 6# May you do good and not evil. 7# May you find forgiveness for yourself and forgive others. 8# May you share freely, never taking more than you give. 9# 10#*********************************************************************** 11# This file implements regression tests for SQLite library. The 12# focus of this script is a test to replicate the bug reported by 13# ticket #842. 14# 15# Ticket #842 was a database corruption problem caused by a DELETE that 16# removed an index entry by not the main table entry. To recreate the 17# problem do this: 18# 19# (1) Create a table with an index. Insert some data into that table. 20# (2) Start a query on the table but do not complete the query. 21# (3) Try to delete a single entry from the table. 22# 23# Step 3 will fail because there is still a read cursor on the table. 24# But the database is corrupted by the DELETE. It turns out that the 25# index entry was deleted first, before the table entry. And the index 26# delete worked. Thus an entry was deleted from the index but not from 27# the table. 28# 29# The solution to the problem was to detect that the table is locked 30# before the index entry is deleted. 31# 32# $Id: delete2.test,v 1.7 2006/08/16 16:42:48 drh Exp $ 33# 34 35set testdir [file dirname $argv0] 36source $testdir/tester.tcl 37 38# Create a table that has an index. 39# 40do_test delete2-1.1 { 41 set DB [sqlite3_connection_pointer db] 42 execsql { 43 CREATE TABLE q(s string, id string, constraint pk_q primary key(id)); 44 BEGIN; 45 INSERT INTO q(s,id) VALUES('hello','id.1'); 46 INSERT INTO q(s,id) VALUES('goodbye','id.2'); 47 INSERT INTO q(s,id) VALUES('again','id.3'); 48 END; 49 SELECT * FROM q; 50 } 51} {hello id.1 goodbye id.2 again id.3} 52do_test delete2-1.2 { 53 execsql { 54 SELECT * FROM q WHERE id='id.1'; 55 } 56} {hello id.1} 57integrity_check delete2-1.3 58 59# Start a query on the table. The query should not use the index. 60# Do not complete the query, thus leaving the table locked. 61# 62do_test delete2-1.4 { 63 set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL] 64 sqlite3_step $STMT 65} SQLITE_ROW 66integrity_check delete2-1.5 67 68# Try to delete a row from the table while a read is in process. 69# As of 2006-08-16, this is allowed. (It used to fail with SQLITE_LOCKED.) 70# 71do_test delete2-1.6 { 72 catchsql { 73 DELETE FROM q WHERE rowid=1 74 } 75} {0 {}} 76integrity_check delete2-1.7 77do_test delete2-1.8 { 78 execsql { 79 SELECT * FROM q; 80 } 81} {goodbye id.2 again id.3} 82 83# Finalize the query, thus clearing the lock on the table. Then 84# retry the delete. The delete should work this time. 85# 86do_test delete2-1.9 { 87 sqlite3_finalize $STMT 88 catchsql { 89 DELETE FROM q WHERE rowid=1 90 } 91} {0 {}} 92integrity_check delete2-1.10 93do_test delete2-1.11 { 94 execsql { 95 SELECT * FROM q; 96 } 97} {goodbye id.2 again id.3} 98 99finish_test 100