Debian Codegraph @

ext/fts5/test/fts5unindexed2.test

raw
1 # 2024 Sep 13
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 #
12 # The tests in this file focus on "unindexed" columns in contentless
13 # tables.
14 #
15
16 source [file join [file dirname [info script]] fts5_common.tcl]
17 set testprefix fts5unindexed2
18
19 # If SQLITE_ENABLE_FTS5 is not defined, omit this file.
20 ifcapable !fts5 {
21 finish_test
22 return
23 }
24
25
26 do_execsql_test 1.1 {
27 CREATE VIRTUAL TABLE t1 USING fts5(
28 a, b UNINDEXED, content=, contentless_unindexed=1
29 );
30 } {}
31
32 do_execsql_test 1.2 {
33 INSERT INTO t1 VALUES('abc def', 'ghi jkl');
34 }
35
36 do_execsql_test 1.3 {
37 SELECT rowid, a, b FROM t1
38 } {1 {} {ghi jkl}}
39
40 do_execsql_test 1.4 {
41 INSERT INTO t1(rowid, a, b) VALUES(11, 'hello world', 'one two three');
42 }
43
44 do_execsql_test 1.5 {
45 INSERT INTO t1(t1, rowid, a, b) VALUES('delete', 1, 'abc def', 'ghi jkl');
46 }
47
48 do_execsql_test 1.6 {
49 SELECT rowid, a, b FROM t1
50 } {
51 11 {} {one two three}
52 }
53
54 do_execsql_test 1.7 {
55 PRAGMA integrity_check
56 } {ok}
57
58 do_execsql_test 1.8 {
59 INSERT INTO t1(rowid, a, b) VALUES(12, 'abc def', 'ghi jkl');
60 }
61
62 do_execsql_test 1.9 {
63 SELECT rowid, a, b FROM t1('def')
64 } {12 {} {ghi jkl}}
65
66 do_execsql_test 1.10 {
67 SELECT rowid, a, b FROM t1('def OR hello') ORDER BY rank
68 } {11 {} {one two three} 12 {} {ghi jkl}}
69
70 do_execsql_test 1.11 {
71 SELECT rowid, a, b FROM t1 WHERE rowid=11
72 } {11 {} {one two three}}
73
74 do_execsql_test 1.12 {
75 SELECT rowid, a, b FROM t1
76 } {11 {} {one two three} 12 {} {ghi jkl}}
77
78
79 fts5_aux_test_functions db
80 do_execsql_test 1.12.2 {
81 SELECT rowid, fts5_test_columntext(t1) FROM t1('def OR hello')
82 } {11 {{} {one two three}} 12 {{} {ghi jkl}}}
83
84 do_execsql_test 1.13 {
85 INSERT INTO t1(t1) VALUES('delete-all');
86 }
87
88 do_execsql_test 1.14 {
89 SELECT rowid, a, b FROM t1
90 }
91
92 do_execsql_test 1.15 {
93 PRAGMA integrity_check
94 } {ok}
95
96 do_execsql_test 2.0 {
97 CREATE VIRTUAL TABLE t4 USING fts5(
98 x, y UNINDEXED, z, columnsize=0, content='', contentless_unindexed=1
99 );
100 }
101
102 do_execsql_test 2.1 {
103 INSERT INTO t4(rowid, x, y, z) VALUES(1, 'a a', 'b b b', 'c');
104 }
105
106 #-------------------------------------------------------------------------
107 reset_db
108
109 do_execsql_test 3.0 {
110 CREATE VIRTUAL TABLE x1 USING fts5(
111 a UNINDEXED, b, c UNINDEXED, d, content=, contentless_delete=1,
112 contentless_unindexed=1
113 );
114 }
115
116 do_execsql_test 3.1 {
117 INSERT INTO x1(rowid, a, b, c, d) VALUES(131, 'aaa', 'bbb', 'ccc', 'ddd');
118 }
119
120 do_execsql_test 3.2 {
121 SELECT * FROM x1
122 } {aaa {} ccc {}}
123
124 do_execsql_test 3.3 {
125 INSERT INTO x1(rowid, a, b, c, d) VALUES(1000, 'AAA', 'BBB', 'CCC', 'DDD');
126 }
127
128 do_execsql_test 3.4 {
129 SELECT rowid, * FROM x1
130 } {
131 131 aaa {} ccc {}
132 1000 AAA {} CCC {}
133 }
134
135 do_execsql_test 3.5 {
136 DELETE FROM x1 WHERE rowid=131;
137 SELECT rowid, * FROM x1
138 } {
139 1000 AAA {} CCC {}
140 }
141
142 do_execsql_test 3.6 {
143 INSERT INTO x1(rowid, a, b, c, d) VALUES(112, 'aaa', 'bbb', 'ccc', 'ddd');
144 SELECT rowid, * FROM x1
145 } {
146 112 aaa {} ccc {}
147 1000 AAA {} CCC {}
148 }
149
150 do_execsql_test 3.7 {
151 UPDATE x1 SET b='hello', d='world', rowid=1120 WHERE rowid=112
152 }
153
154 do_execsql_test 3.8 {
155 SELECT rowid, * FROM x1
156 } {
157 1000 AAA {} CCC {}
158 1120 aaa {} ccc {}
159 }
160
161 do_execsql_test 3.9 {
162 SELECT rowid, * FROM x1('hello');
163 } {
164 1120 aaa {} ccc {}
165 }
166
167 do_execsql_test 3.9 {
168 SELECT rowid, * FROM x1('bbb');
169 } {
170 1000 AAA {} CCC {}
171 }
172
173 fts5_aux_test_functions db
174 do_execsql_test 3.10 {
175 SELECT rowid, fts5_test_columntext(x1) FROM x1('b*')
176 } {1000 {AAA {} CCC {}}}
177
178 #-------------------------------------------------------------------------
179 # Check that if contentless_unindexed=1 is not specified, the values
180 # of UNINDEXED columns are not stored in the database.
181 #
182 # Also check that contentless_unindexed=1 is not allowed unless the table
183 # is actually contentless.
184 #
185 reset_db
186 do_execsql_test 4.0 {
187 CREATE VIRTUAL TABLE ft USING fts5(a, b, c UNINDEXED, content='');
188 INSERT INTO ft VALUES('one', 'two', 'three');
189 SELECT rowid, * FROM ft;
190 } {1 {} {} {}}
191
192 do_execsql_test 4.1 {
193 SELECT name FROM sqlite_schema ORDER BY 1
194 } {
195 ft ft_config ft_data ft_docsize ft_idx
196 }
197
198 do_catchsql_test 4.2 {
199 CREATE VIRTUAL TABLE ft2 USING fts5(
200 a, b, c UNINDEXED, contentless_unindexed=1
201 );
202 } {1 {contentless_unindexed=1 requires a contentless table}}
203
204 do_catchsql_test 4.3 {
205 DELETE FROM ft WHERE rowid=1
206 } {1 {cannot DELETE from contentless fts5 table: ft}}
207
208 #-------------------------------------------------------------------------
209 # Check that the usual restrictions on contentless tables apply to
210 # contentless_unindexed=1 tables.
211 #
212 reset_db
213 do_execsql_test 5.0 {
214 CREATE VIRTUAL TABLE ft USING fts5(
215 a, b UNINDEXED, c, content='', contentless_unindexed=1
216 );
217 INSERT INTO ft VALUES('one', 'two', 'three');
218 INSERT INTO ft VALUES('four', 'five', 'six');
219 INSERT INTO ft VALUES('seven', 'eight', 'nine');
220 SELECT rowid, * FROM ft;
221 } {
222 1 {} two {}
223 2 {} five {}
224 3 {} eight {}
225 }
226
227 do_execsql_test 5.1 {
228 PRAGMA integrity_check
229 } {ok}
230
231 do_catchsql_test 5.2 {
232 DELETE FROM ft WHERE rowid=2
233 } {1 {cannot DELETE from contentless fts5 table: ft}}
234
235 do_execsql_test 5.3 {
236 SELECT rowid, * FROM ft('six')
237 } {
238 2 {} five {}
239 }
240
241 do_catchsql_test 5.4 {
242 UPDATE ft SET a='x', b='y', c='z' WHERE rowid=3
243 } {1 {cannot UPDATE contentless fts5 table: ft}}
244
245 fts5_aux_test_functions db
246
247 do_execsql_test 5.5 {
248 SELECT fts5_test_columntext(ft) FROM ft WHERE rowid=3
249 } {
250 {{} eight {}}
251 }
252 do_execsql_test 5.6 {
253 SELECT fts5_test_columntext(ft) FROM ft('three');
254 } {
255 {{} two {}}
256 }
257
258 #-------------------------------------------------------------------------
259 # Check that it is possible to UPDATE a contentless_unindexed=1 table
260 # if the only columns being modified are UNINDEXED.
261 #
262 # If the contentless_unindexed=1 table is also contentless_delete=1, then
263 # it is also possible to update indexed columns - but only if *all* indexed
264 # columns are updated.
265 #
266 reset_db
267 do_execsql_test 6.0 {
268 CREATE VIRTUAL TABLE ft1 USING fts5(a, b UNINDEXED, c UNINDEXED, d,
269 contentless_unindexed=1, content=''
270 );
271
272 INSERT INTO ft1(rowid, a, b, c, d) VALUES
273 (100, 'x y', 'b1', 'c1', 'a b'),
274 (200, 'c d', 'b2', 'c2', 'a b'),
275 (300, 'e f', 'b3', 'c3', 'a b');
276 }
277
278 do_execsql_test 6.1 {
279 UPDATE ft1 SET b='b1.1', c='c1.1' WHERE rowid=100;
280 }
281 do_execsql_test 6.2 {
282 UPDATE ft1 SET b='b2.1' WHERE rowid=200;
283 }
284 do_execsql_test 6.3 {
285 UPDATE ft1 SET c='c3.1' WHERE rowid=300;
286 }
287
288 do_execsql_test 6.4 {
289 SELECT rowid, a, b, c, d FROM ft1
290 } {
291 100 {} b1.1 c1.1 {}
292 200 {} b2.1 c2 {}
293 300 {} b3 c3.1 {}
294 }
295
296 finish_test
297