wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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.8 2008/07/08 15:59:52 danielk1977 Exp $
33 #
34  
35 set testdir [file dirname $argv0]
36 source $testdir/tester.tcl
37  
38 # Create a table that has an index.
39 #
40 do_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}
52 do_test delete2-1.2 {
53 execsql {
54 SELECT * FROM q WHERE id='id.1';
55 }
56 } {hello id.1}
57 integrity_check delete2-1.3
58  
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 #
62 do_test delete2-1.4 {
63 set STMT [sqlite3_prepare $DB {SELECT * FROM q} -1 TAIL]
64 sqlite3_step $STMT
65 } SQLITE_ROW
66 integrity_check delete2-1.5
67  
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 #
71 do_test delete2-1.6 {
72 catchsql {
73 DELETE FROM q WHERE rowid=1
74 }
75 } {0 {}}
76 integrity_check delete2-1.7
77 do_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. Then
84 # retry the delete. The delete should work this time.
85 #
86 do_test delete2-1.9 {
87 sqlite3_finalize $STMT
88 catchsql {
89 DELETE FROM q WHERE rowid=1
90 }
91 } {0 {}}
92 integrity_check delete2-1.10
93 do_test delete2-1.11 {
94 execsql {
95 SELECT * FROM q;
96 }
97 } {goodbye id.2 again id.3}
98  
99 do_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 } {}
108 do_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, t2
112 } {
113 db eval {DELETE FROM t1}
114 lappend res $b $c $d
115 }
116 set res
117 } {{} 3 4 {} 5 6}
118  
119 finish_test