Debian Codegraph @

ext/fts5/test/fts5ab.test

raw
1 # 2014 June 17
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 testing the FTS5 module.
13 #
14 #
15
16 source [file join [file dirname [info script]] fts5_common.tcl]
17 set testprefix fts5ab
18
19 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
20 ifcapable !fts5 {
21 finish_test
22 return
23 }
24
25 foreach_detail_mode $testprefix {
26
27 do_execsql_test 1.0 {
28 CREATE VIRTUAL TABLE t1 USING fts5(a, b, detail=%DETAIL%);
29 INSERT INTO t1 VALUES('hello', 'world');
30 INSERT INTO t1 VALUES('one two', 'three four');
31 INSERT INTO t1(rowid, a, b) VALUES(45, 'forty', 'five');
32 }
33
34 do_execsql_test 1.1 {
35 SELECT * FROM t1 ORDER BY rowid DESC;
36 } { forty five {one two} {three four} hello world }
37
38 do_execsql_test 1.2 {
39 SELECT rowid FROM t1 ORDER BY rowid DESC;
40 } {45 2 1}
41
42 do_execsql_test 1.3 {
43 SELECT rowid FROM t1 ORDER BY rowid ASC;
44 } {1 2 45}
45
46 do_execsql_test 1.4 {
47 SELECT * FROM t1 WHERE rowid=2;
48 } {{one two} {three four}}
49
50 do_execsql_test 1.5 {
51 SELECT * FROM t1 WHERE rowid=2.01;
52 } {}
53
54 do_execsql_test 1.6 {
55 SELECT * FROM t1 WHERE rowid=1.99;
56 } {}
57
58 #-------------------------------------------------------------------------
59
60 reset_db
61 do_execsql_test 2.1 {
62 CREATE VIRTUAL TABLE t1 USING fts5(x, detail=%DETAIL%);
63 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
64 INSERT INTO t1 VALUES('one');
65 INSERT INTO t1 VALUES('two');
66 INSERT INTO t1 VALUES('three');
67 }
68
69 do_catchsql_test 2.2 {
70 SELECT rowid, * FROM t1 WHERE t1 MATCH 'AND AND'
71 } {1 {fts5: syntax error near "AND"}}
72
73 do_execsql_test 2.3 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'two' } {2 two}
74 do_execsql_test 2.4 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'three' } {3 three}
75 do_execsql_test 2.5 { SELECT rowid, * FROM t1 WHERE t1 MATCH 'one' } {1 one}
76
77 do_execsql_test 2.6 {
78 INSERT INTO t1 VALUES('a b c d e f g');
79 INSERT INTO t1 VALUES('b d e a a a i');
80 INSERT INTO t1 VALUES('x y z b c c c');
81 }
82
83 foreach {tn expr res} {
84 1 a {5 4}
85 2 b {6 5 4}
86 3 c {6 4}
87 4 d {5 4}
88 5 e {5 4}
89 6 f {4}
90 7 g {4}
91 8 x {6}
92 9 y {6}
93 10 z {6}
94 } {
95 do_execsql_test 2.7.$tn.1 {
96 SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
97 } $res
98 do_execsql_test 2.7.$tn.2 {
99 SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid ASC
100 } [lsort -integer $res]
101 }
102
103 #-------------------------------------------------------------------------
104 #
105 reset_db
106 do_execsql_test 3.0 {
107 CREATE VIRTUAL TABLE t1 USING fts5(a,b);
108 INSERT INTO t1(t1, rank) VALUES('pgsz', 32);
109 }
110
111 foreach {tn a b} {
112 1 {abashed abandons abase abash abaft} {abases abased}
113 2 {abasing abases abaft abated abandons} {abases abandoned}
114 3 {abatement abash abash abated abase} {abasements abashing}
115 4 {abaft abasements abase abasement abasing} {abasement abases}
116 5 {abaft abashing abatement abash abasements} {abandons abandoning}
117 6 {aback abate abasements abashes abandoned} {abasement abased}
118 7 {abandons abated abased aback abandoning} {abases abandoned}
119 8 {abashing abases abasement abaft abashing} {abashed abate}
120 9 {abash abase abate abashing abashed} {abandon abandoned}
121 10 {abate abandoning abandons abasement aback} {abandon abandoning}
122 } {
123 do_execsql_test 3.1.$tn.1 { INSERT INTO t1 VALUES($a, $b) }
124 do_execsql_test 3.1.$tn.2 { INSERT INTO t1(t1) VALUES('integrity-check') }
125 }
126
127 foreach {tn expr res} {
128 1 {abash} {9 5 3 1}
129 2 {abase} {9 4 3 1}
130 3 {abase + abash} {1}
131 4 {abash + abase} {9}
132 5 {abaft + abashing} {8 5}
133 6 {abandon + abandoning} {10}
134 7 {"abashing abases abasement abaft abashing"} {8}
135 } {
136 do_execsql_test 3.2.$tn {
137 SELECT rowid FROM t1 WHERE t1 MATCH $expr ORDER BY rowid DESC
138 } $res
139 }
140
141 do_execsql_test 3.3 {
142 SELECT rowid FROM t1 WHERE t1 MATCH 'NEAR(aback abate, 2)'
143 } {6}
144
145 foreach {tn expr res} {
146 1 {abash} {1 3 5 9}
147 2 {abase} {1 3 4 9}
148 3 {abase + abash} {1}
149 4 {abash + abase} {9}
150 5 {abaft + abashing} {5 8}
151 6 {abandon + abandoning} {10}
152 7 {"abashing abases abasement abaft abashing"} {8}
153 } {
154 do_execsql_test 3.4.$tn {
155 SELECT rowid FROM t1 WHERE t1 MATCH $expr
156 } $res
157 }
158
159 #-------------------------------------------------------------------------
160 # Documents with more than 2M tokens.
161 #
162
163 do_execsql_test 4.0 {
164 CREATE VIRTUAL TABLE s1 USING fts5(x, detail=%DETAIL%);
165 }
166 foreach {tn doc} [list \
167 1 [string repeat {a x } 1500000] \
168 2 "[string repeat {a a } 1500000] x" \
169 ] {
170 do_execsql_test 4.$tn { INSERT INTO s1 VALUES($doc) }
171 }
172
173 do_execsql_test 4.3 {
174 SELECT rowid FROM s1 WHERE s1 MATCH 'x'
175 } {1 2}
176
177 if {[detail_is_full]} {
178 do_execsql_test 4.4 {
179 SELECT rowid FROM s1 WHERE s1 MATCH '"a x"'
180 } {1 2}
181 }
182
183 do_execsql_test 4.5.1 {
184 SELECT rowid FROM s1 WHERE s1 MATCH 'a AND x'
185 } {1 2}
186
187 do_execsql_test 4.5.2 {
188 SELECT rowid FROM s1 WHERE s1 MATCH 'a x'
189 } {1 2}
190
191 #-------------------------------------------------------------------------
192 # Check that a special case of segment promotion works. The case is where
193 # a new segment is written to level L, but the oldest segment within level
194 # (L-2) is larger than it.
195 #
196 do_execsql_test 5.0 {
197 CREATE VIRTUAL TABLE s2 USING fts5(x, detail=%DETAIL%);
198 INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
199 INSERT INTO s2(s2, rank) VALUES('automerge', 0);
200 }
201
202 proc rnddoc {n} {
203 set map [list 0 a 1 b 2 c 3 d 4 e 5 f 6 g 7 h 8 i 9 j]
204 set doc [list]
205 for {set i 0} {$i < $n} {incr i} {
206 lappend doc [string map $map [format %.3d [expr int(rand()*1000)]]]
207 }
208 set doc
209 }
210 db func rnddoc rnddoc
211
212 do_test 5.1 {
213 for {set i 1} {$i <= 65} {incr i} {
214 execsql { INSERT INTO s2 VALUES(rnddoc(10)) }
215 }
216 for {set i 1} {$i <= 63} {incr i} {
217 execsql { DELETE FROM s2 WHERE rowid = $i }
218 }
219 fts5_level_segs s2
220 } {0 8}
221
222 do_test 5.2 {
223 execsql {
224 INSERT INTO s2(s2, rank) VALUES('automerge', 8);
225 }
226 for {set i 0} {$i < 7} {incr i} {
227 execsql { INSERT INTO s2 VALUES(rnddoc(50)) }
228 }
229 fts5_level_segs s2
230 } {8 0 0}
231
232 # Test also the other type of segment promotion - when a new segment is written
233 # that is larger than segments immediately following it.
234 do_test 5.3 {
235 execsql {
236 DROP TABLE s2;
237 CREATE VIRTUAL TABLE s2 USING fts5(x, detail=%DETAIL%);
238 INSERT INTO s2(s2, rank) VALUES('pgsz', 32);
239 INSERT INTO s2(s2, rank) VALUES('automerge', 0);
240 }
241
242 for {set i 1} {$i <= 16} {incr i} {
243 execsql { INSERT INTO s2 VALUES(rnddoc(5)) }
244 }
245 fts5_level_segs s2
246 } {0 1}
247
248 do_test 5.4 {
249 execsql { INSERT INTO s2 VALUES(rnddoc(160)) }
250 fts5_level_segs s2
251 } {2 0}
252
253 #-------------------------------------------------------------------------
254 #
255 do_execsql_test 6.0 {
256 CREATE VIRTUAL TABLE s3 USING fts5(x, detail=%DETAIL%);
257 BEGIN;
258 INSERT INTO s3 VALUES('a b c');
259 INSERT INTO s3 VALUES('A B C');
260 }
261
262 do_execsql_test 6.1.1 {
263 SELECT rowid FROM s3 WHERE s3 MATCH 'a'
264 } {1 2}
265
266 do_execsql_test 6.1.2 {
267 SELECT rowid FROM s3 WHERE s3 MATCH 'a' ORDER BY rowid DESC
268 } {2 1}
269
270 do_execsql_test 6.2 {
271 COMMIT;
272 }
273
274 do_execsql_test 6.3 {
275 SELECT rowid FROM s3 WHERE s3 MATCH 'a'
276 } {1 2}
277
278 do_test 6.4 {
279 db close
280 sqlite3 db test.db
281 execsql {
282 BEGIN;
283 INSERT INTO s3(s3) VALUES('optimize');
284 ROLLBACK;
285 }
286 } {}
287
288 #-------------------------------------------------------------------------
289 #
290 set doc [string repeat "a b c " 500]
291 do_execsql_test 7.0 {
292 CREATE VIRTUAL TABLE x1 USING fts5(x, detail=%DETAIL%);
293 INSERT INTO x1(x1, rank) VALUES('pgsz', 32);
294 INSERT INTO x1 VALUES($doc);
295 }
296
297 #-------------------------------------------------------------------------
298 # Forum post: https://sqlite.org/forum/forumpost/ea4d8c9acb
299 #
300 reset_db
301 do_execsql_test 8.0 {
302 PRAGMA encoding = 'UTF-16le';
303 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
304 }
305 set v [db one {SELECT x'2a12'}]
306 do_execsql_test 8.1 {
307 INSERT INTO vt0 VALUES ($v);
308 }
309 do_execsql_test 8.2 {
310 SELECT quote(c0) FROM vt0
311 } {X'2A12'}
312 do_execsql_test 8.3 {
313 INSERT INTO vt0(vt0) VALUES('integrity-check');
314 } {}
315 reset_db
316 do_execsql_test 8.4 {
317 PRAGMA encoding = 'UTF-16le';
318 CREATE VIRTUAL TABLE vt0 USING fts5(c0);
319 }
320 do_execsql_test 8.5 {
321 INSERT INTO vt0 VALUES (x'2a12');
322 }
323 do_execsql_test 8.6 {
324 SELECT quote(c0) FROM vt0
325 } {X'2A12'}
326 do_execsql_test 8.7 {
327 INSERT INTO vt0(vt0) VALUES('integrity-check');
328 } {}
329
330 } ;# foreach_detail_mode...
331
332
333 finish_test