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
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
|
# 2012 March 26
#
# 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 testing the FTS 'integrity-check' function,
# used to check if the current FTS index accurately reflects the content
# of the table.
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl
source $testdir/fts3_common.tcl
set ::testprefix fts4check
# If SQLITE_ENABLE_FTS3 is defined, omit this file.
ifcapable !fts3 {
finish_test
return
}
# Run the integrity-check on FTS table $tbl using database handle $db. If
# the integrity-check passes, return "ok". Otherwise, throw an exception.
#
proc fts_integrity {db tbl} {
$db eval "INSERT INTO $tbl ($tbl) VALUES('integrity-check')"
return "ok"
}
#-------------------------------------------------------------------------
# Test cases 1.*
#
# 1.0: Build a reasonably sized FTS table (5000 rows).
#
# 1.1: Run the integrity check code to check it passes.
#
# 1.2: Make a series of minor changes to the underlying FTS data structures
# (e.g. delete or insert a row from the %_content table). Check that
# this causes the integrity-check code to fail.
#
# Build an FTS table and check the integrity-check passes.
#
do_test 1.0 { fts3_build_db_1 5000 } {}
do_test 1.1 { fts_integrity db t1 } {ok}
# Mess around with the underlying tables. Check that this causes the
# integrity-check test to fail.
#
foreach {tn disruption} {
1 {
INSERT INTO t1_content(docid, c0x, c1y) VALUES(NULL, 'a', 'b');
}
2 {
DELETE FROM t1_content WHERE docid = (SELECT max(docid) FROM t1_content);
}
3 {
DELETE FROM t1_segdir WHERE level=0 AND idx=(
SELECT max(idx) FROM t1_segdir WHERE level=0
);
}
} {
do_execsql_test 1.2.1.$tn "BEGIN; $disruption"
do_catchsql_test 1.2.2.$tn {
INSERT INTO t1 (t1) VALUES('integrity-check')
} {1 {database disk image is malformed}}
do_execsql_test 1.2.3.$tn "ROLLBACK"
}
do_test 1.3 { fts_integrity db t1 } {ok}
#-------------------------------------------------------------------------
# Test cases 2.*
#
# 2.0: Build a reasonably sized FTS table (20000 rows) that includes
# prefix indexes.
#
# 2.1: Run the integrity check code to check it passes.
#
# 2.2: Make a series of minor changes to the underlying FTS data structures
# (e.g. delete or insert a row from the %_content table). Check that
# this causes the integrity-check code to fail.
#
do_test 2.0 { fts3_build_db_2 -extra {prefix="3,1"} 20000 } {}
do_test 2.1 { fts_integrity db t2 } {ok}
foreach {tn disruption} {
1 {
INSERT INTO t2_content VALUES(NULL, 'xyz')
}
3 {
DELETE FROM t2_segdir WHERE level=0 AND idx=(
SELECT max(idx) FROM t2_segdir WHERE level=1024
);
}
} {
do_execsql_test 2.2.1.$tn "BEGIN; $disruption"
do_catchsql_test 2.2.2.$tn {
INSERT INTO t2 (t2) VALUES('integrity-check')
} {1 {database disk image is malformed}}
do_execsql_test 2.2.3.$tn "ROLLBACK"
}
#-------------------------------------------------------------------------
# Test cases 3.*
#
# 3.0: Build a reasonably sized FTS table (5000 rows) that includes
# prefix indexes and uses the languageid= feature.
#
# 3.1: Run the integrity check code to check it passes.
#
# 3.2: Make a series of minor changes to the underlying FTS data structures
# (e.g. delete or insert a row from the %_content table). Check that
# this causes the integrity-check code to fail.
#
do_test 3.0 {
reset_db
fts3_build_db_1 5000
execsql {
CREATE VIRTUAL TABLE t3 USING fts4(x, y, prefix="2,3", languageid=langid);
}
foreach docid [execsql {SELECT docid FROM t1 ORDER BY 1 ASC}] {
execsql {
INSERT INTO t3(x, y, langid)
SELECT x, y, (docid%9)*4 FROM t1 WHERE docid=$docid;
}
}
} {}
do_test 3.1 { fts_integrity db t3 } {ok}
foreach {tn disruption} {
1 {
INSERT INTO t3_content(c0x, c1y, langid) VALUES(NULL, 'a', 0);
}
2 {
UPDATE t3_content SET langid=langid+1 WHERE rowid = (
SELECT max(rowid) FROM t3_content
)
}
} {
do_execsql_test 3.2.1.$tn "BEGIN; $disruption"
do_catchsql_test 3.2.2.$tn {
INSERT INTO t3 (t3) VALUES('integrity-check')
} {1 {database disk image is malformed}}
do_execsql_test 3.2.3.$tn "ROLLBACK"
}
#--------------------------------------------------------------------------
# Test case 4.*
#
# Test that the integrity-check works if there are "notindexed" columns.
#
do_execsql_test 4.0 {
CREATE VIRTUAL TABLE t4 USING fts4(a, b, c, notindexed=b);
INSERT INTO t4 VALUES('text one', 'text two', 'text three');
INSERT INTO t4(t4) VALUES('integrity-check');
}
do_execsql_test 4.1 {
PRAGMA writable_schema = 1;
UPDATE sqlite_master
SET sql = 'CREATE VIRTUAL TABLE t4 USING fts4(a, b, c)'
WHERE name = 't4';
}
do_test 4.2 {
db close
sqlite3 db test.db
catchsql {
INSERT INTO t4(t4) VALUES('integrity-check');
}
} {1 {database disk image is malformed}}
reset_db
finish_test
|
