AryaWu/sqlite

1# 2003 September 62#3# The author disclaims copyright to this source code.  In place of4# 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.  The12# focus of this script is a test to replicate the bug reported by13# ticket #842.14#15# Ticket #842 was a database corruption problem caused by a DELETE that16# removed an index entry by not the main table entry.  To recreate the17# 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 the25# index entry was deleted first, before the table entry.  And the index26# delete worked.  Thus an entry was deleted from the index but not from27# the table.28#29# The solution to the problem was to detect that the table is locked30# before the index entry is deleted.31#32# $Id: delete2.test,v 1.8 2008/07/08 15:59:52 danielk1977 Exp $33#34 35set testdir [file dirname $argv0]36source $testdir/tester.tcl37 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.358 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 $STMT65} SQLITE_ROW66integrity_check delete2-1.567 68# Try to delete a row from the table while a read is in process.69# As of 2006-08-16, this is allowed.  (It used to fail with SQLITE_LOCKED.)70#71do_test delete2-1.6 {72  catchsql {73    DELETE FROM q WHERE rowid=174  }75} {0 {}}76integrity_check delete2-1.777do_test delete2-1.8 {78  execsql {79    SELECT * FROM q;80  }81} {goodbye id.2 again id.3}82 83# Finalize the query, thus clearing the lock on the table.  Then84# retry the delete.  The delete should work this time.85#86do_test delete2-1.9 {87  sqlite3_finalize $STMT88  catchsql {89    DELETE FROM q WHERE rowid=190  }91} {0 {}}92integrity_check delete2-1.1093do_test delete2-1.11 {94  execsql {95    SELECT * FROM q;96  }97} {goodbye id.2 again id.3}98 99do_test delete2-2.1 {100  execsql {101    CREATE TABLE t1(a, b);102    CREATE TABLE t2(c, d);103    INSERT INTO t1 VALUES(1, 2);104    INSERT INTO t2 VALUES(3, 4);105    INSERT INTO t2 VALUES(5, 6);106  }107} {}108do_test delete2-2.2 {109  set res [list]110  db eval {111    SELECT CASE WHEN c = 5 THEN b ELSE NULL END AS b, c, d FROM t1, t2112  } {113    db eval {DELETE FROM t1}114    lappend res $b $c $d115  }116  set res117} {{} 3 4 {} 5 6}118 119finish_test120