/trunk/test/delete2.test |
@@ -0,0 +1,119 @@ |
# 2003 September 6 |
# |
# The author disclaims copyright to this source code. In place of |
# a legal notice, here is a blessing: |
# |
# May you do good and not evil. |
# May you find forgiveness for yourself and forgive others. |
# May you share freely, never taking more than you give. |
# |
#*********************************************************************** |
# This file implements regression tests for SQLite library. The |
# focus of this script is a test to replicate the bug reported by |
# ticket #842. |
# |
# Ticket #842 was a database corruption problem caused by a DELETE that |
# removed an index entry by not the main table entry. To recreate the |
# problem do this: |
# |
# (1) Create a table with an index. Insert some data into that table. |
# (2) Start a query on the table but do not complete the query. |
# (3) Try to delete a single entry from the table. |
# |
# Step 3 will fail because there is still a read cursor on the table. |
# But the database is corrupted by the DELETE. It turns out that the |
# index entry was deleted first, before the table entry. And the index |
# delete worked. Thus an entry was deleted from the index but not from |
# the table. |
# |
# The solution to the problem was to detect that the table is locked |
# before the index entry is deleted. |
# |
# $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $ |
# |
|
set testdir [file dirname $argv0] |
source $testdir/tester.tcl |
|
# Create a table that has an index. |
# |
do_test delete2-1.1 { |
set DB [sqlite3_connection_pointer db] |
execsql { |
CREATE TABLE q(s string, id string, constraint pk_q primary key(id)); |
BEGIN; |
INSERT INTO q(s,id) VALUES('hello','id.1'); |
INSERT INTO q(s,id) VALUES('goodbye','id.2'); |
INSERT INTO q(s,id) VALUES('again','id.3'); |
END; |
SELECT * FROM q; |
} |
} {hello id.1 goodbye id.2 again id.3} |
do_test delete2-1.2 { |
execsql { |
SELECT * FROM q WHERE id='id.1'; |
} |
} {hello id.1} |
integrity_check delete2-1.3 |
|
# Start a query on the table. The query should not use the index. |
# Do not complete the query, thus leaving the table locked. |
# |
do_test delete2-1.4 { |
set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL] |
sqlite3_step $STMT |
} SQLITE_ROW |
integrity_check delete2-1.5 |
|
# Try to delete a row from the table while a read is in process. |
# As of 2006-08-16, this is allowed. (It used to fail with SQLITE_LOCKED.) |
# |
do_test delete2-1.6 { |
catchsql { |
DELETE FROM q WHERE rowid=1 |
} |
} {0 {}} |
integrity_check delete2-1.7 |
do_test delete2-1.8 { |
execsql { |
SELECT * FROM q; |
} |
} {goodbye id.2 again id.3} |
|
# Finalize the query, thus clearing the lock on the table. Then |
# retry the delete. The delete should work this time. |
# |
do_test delete2-1.9 { |
sqlite3_finalize $STMT |
catchsql { |
DELETE FROM q WHERE rowid=1 |
} |
} {0 {}} |
integrity_check delete2-1.10 |
do_test delete2-1.11 { |
execsql { |
SELECT * FROM q; |
} |
} {goodbye id.2 again id.3} |
|
do_test delete2-2.1 { |
execsql { |
CREATE TABLE t1(a, b); |
CREATE TABLE t2(c, d); |
INSERT INTO t1 VALUES(1, 2); |
INSERT INTO t2 VALUES(3, 4); |
INSERT INTO t2 VALUES(5, 6); |
} |
} {} |
do_test delete2-2.2 { |
set res [list] |
db eval { |
SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2 |
} { |
db eval {DELETE FROM t1} |
lappend res $b $c $d |
} |
set res |
} {{} 3 4 {} 5 6} |
|
finish_test |