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.5 2006/01/03 00:33:50 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. The delete should fail. 69# 70do_test delete2-1.6 { 71 catchsql { 72 DELETE FROM q WHERE rowid=1 73 } 74} {1 {database table is locked}} 75integrity_check delete2-1.7 76do_test delete2-1.8 { 77 execsql { 78 SELECT * FROM q; 79 } 80} {hello id.1 goodbye id.2 again id.3} 81 82# Finalize the query, thus clearing the lock on the table. Then 83# retry the delete. The delete should work this time. 84# 85do_test delete2-1.9 { 86 sqlite3_finalize $STMT 87 catchsql { 88 DELETE FROM q WHERE rowid=1 89 } 90} {0 {}} 91integrity_check delete2-1.10 92do_test delete2-1.11 { 93 execsql { 94 SELECT * FROM q; 95 } 96} {goodbye id.2 again id.3} 97 98finish_test 99