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.3 2004/11/16 15:50:21 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. Before version 3.10 the DELETE 70# would fail because of the SELECT active on the table. In 3.10 the 71# DELETE is legal. 72# 73do_test delete2-1.6 { 74 catchsql { 75 DELETE FROM q WHERE rowid=1 76 } 77} {0 {}} 78integrity_check delete2-1.7 79do_test delete2-1.8 { 80 execsql { 81 SELECT * FROM q; 82 } 83} {goodbye id.2 again id.3} 84 85do_test delete2-1.9 { 86 sqlite3_finalize $STMT 87 catchsql { 88 DELETE FROM q WHERE rowid=2 89 } 90} {0 {}} 91integrity_check delete2-1.10 92do_test delete2-1.11 { 93 execsql { 94 SELECT * FROM q; 95 } 96} {again id.3} 97 98finish_test 99