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.1 2004/08/08 19:43:30 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 db close 42 set DB [sqlite3 db test.db] 43 execsql { 44 CREATE TABLE q(s string, id string, constraint pk_q primary key(id)); 45 BEGIN; 46 INSERT INTO q(s,id) VALUES('hello','id.1'); 47 INSERT INTO q(s,id) VALUES('goodbye','id.2'); 48 INSERT INTO q(s,id) VALUES('again','id.3'); 49 END; 50 SELECT * FROM q; 51 } 52} {hello id.1 goodbye id.2 again id.3} 53do_test delete2-1.2 { 54 execsql { 55 SELECT * FROM q WHERE id='id.1'; 56 } 57} {hello id.1} 58do_test delete2-1.3 { 59 execsql { 60 PRAGMA integrity_check 61 } 62} ok 63 64# Start a query on the table. The query should not use the index. 65# Do not complete the query, thus leaving the table locked. 66# 67do_test delete2-1.4 { 68 set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL] 69 sqlite3_step $STMT 70} SQLITE_ROW 71do_test delete2-1.5 { 72 execsql {PRAGMA integrity_check} 73} {ok} 74 75# Try to delete a row from the table. The delete should fail. 76# 77do_test delete2-1.6 { 78 catchsql { 79 DELETE FROM q WHERE rowid=1 80 } 81} {1 {database table is locked}} 82do_test delete2-1.7 { 83 execsql {PRAGMA integrity_check} 84} {ok} 85do_test delete2-1.8 { 86 execsql { 87 SELECT * FROM q; 88 } 89} {hello id.1 goodbye id.2 again id.3} 90 91# Finalize the query, thus clearing the lock on the table. Then 92# retry the delete. The delete should work this time. 93# 94do_test delete2-1.9 { 95 sqlite3_finalize $STMT 96 catchsql { 97 DELETE FROM q WHERE rowid=1 98 } 99} {0 {}} 100do_test delete2-1.10 { 101 execsql {PRAGMA integrity_check} 102} {ok} 103do_test delete2-1.11 { 104 execsql { 105 SELECT * FROM q; 106 } 107} {goodbye id.2 again id.3} 108 109finish_test 110