1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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
|
