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.4 2004/11/22 10:02:23 danielk1977 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} 58integrity_check delete2-1.3 59 60# Start a query on the table. The query should not use the index. 61# Do not complete the query, thus leaving the table locked. 62# 63do_test delete2-1.4 { 64 set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL] 65 sqlite3_step $STMT 66} SQLITE_ROW 67integrity_check delete2-1.5 68 69# Try to delete a row from the table. The delete should fail. 70# 71do_test delete2-1.6 { 72 catchsql { 73 DELETE FROM q WHERE rowid=1 74 } 75} {1 {database table is locked}} 76integrity_check delete2-1.7 77do_test delete2-1.8 { 78 execsql { 79 SELECT * FROM q; 80 } 81} {hello id.1 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