Debian Codegraph @

www/fts5.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>SQLite FTS5 Extension</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 SQLite FTS5 Extension
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#overview_of_fts5">1. Overview of FTS5</a></div>
126 <div class="fancy-toc1"><a href="#compiling_and_using_fts5">2. Compiling and Using FTS5</a></div>
127 <div class="fancy-toc2"><a href="#building_fts5_as_part_of_sqlite">2.1. Building FTS5 as part of SQLite</a></div>
128 <div class="fancy-toc2"><a href="#building_a_loadable_extension">2.2. Building a Loadable Extension</a></div>
129 <div class="fancy-toc1"><a href="#full_text_query_syntax">3. Full-text Query Syntax</a></div>
130 <div class="fancy-toc2"><a href="#fts5_strings">3.1. FTS5 Strings</a></div>
131 <div class="fancy-toc2"><a href="#fts5_phrases">3.2. FTS5 Phrases</a></div>
132 <div class="fancy-toc2"><a href="#fts5_prefix_queries">3.3. FTS5 Prefix Queries</a></div>
133 <div class="fancy-toc2"><a href="#fts5_initial_token_queries">3.4. FTS5 Initial Token Queries</a></div>
134 <div class="fancy-toc2"><a href="#fts5_near_queries">3.5. FTS5 NEAR Queries</a></div>
135 <div class="fancy-toc2"><a href="#fts5_column_filters">3.6. FTS5 Column Filters</a></div>
136 <div class="fancy-toc2"><a href="#fts5_boolean_operators">3.7. FTS5 Boolean Operators</a></div>
137 <div class="fancy-toc1"><a href="#fts5_table_creation_and_initialization">4. FTS5 Table Creation and Initialization</a></div>
138 <div class="fancy-toc2"><a href="#the_unindexed_column_option">4.1. The UNINDEXED column option</a></div>
139 <div class="fancy-toc2"><a href="#prefix_indexes">4.2. Prefix Indexes</a></div>
140 <div class="fancy-toc2"><a href="#tokenizers">4.3. Tokenizers</a></div>
141 <div class="fancy-toc3"><a href="#unicode61_tokenizer">4.3.1. Unicode61 Tokenizer</a></div>
142 <div class="fancy-toc3"><a href="#ascii_tokenizer">4.3.2. Ascii Tokenizer</a></div>
143 <div class="fancy-toc3"><a href="#porter_tokenizer">4.3.3. Porter Tokenizer</a></div>
144 <div class="fancy-toc3"><a href="#the_trigram_tokenizer">4.3.4. The Trigram Tokenizer</a></div>
145 <div class="fancy-toc2"><a href="#external_content_and_contentless_tables">4.4. External Content and Contentless Tables</a></div>
146 <div class="fancy-toc3"><a href="#contentless_tables">4.4.1. Contentless Tables</a></div>
147 <div class="fancy-toc3"><a href="#contentless_delete_tables">4.4.2. Contentless-Delete Tables</a></div>
148 <div class="fancy-toc3"><a href="#external_content_tables">4.4.3. External Content Tables</a></div>
149 <div class="fancy-toc3"><a href="#external_content_table_pitfalls">4.4.4. External Content Table Pitfalls</a></div>
150 <div class="fancy-toc4"><a href="#updates_and_deletes_on_contentless_tables">4.4.4.1. Updates and Deletes on Contentless Tables</a></div>
151 <div class="fancy-toc2"><a href="#the_columnsize_option">4.5. The Columnsize Option</a></div>
152 <div class="fancy-toc2"><a href="#the_detail_option">4.6. The Detail Option</a></div>
153 <div class="fancy-toc2"><a href="#the_tokendata_option">4.7. The Tokendata Option</a></div>
154 <div class="fancy-toc2"><a href="#the_locale_option">4.8. The Locale Option</a></div>
155 <div class="fancy-toc2"><a href="#the_contentless_unindexed_option">4.9. The Contentless-Unindexed Option</a></div>
156 <div class="fancy-toc1"><a href="#_auxiliary_functions_">5. Auxiliary Functions </a></div>
157 <div class="fancy-toc2"><a href="#built_in_auxiliary_functions">5.1. Built-in Auxiliary Functions</a></div>
158 <div class="fancy-toc3"><a href="#the_bm25_function">5.1.1. The bm25() function</a></div>
159 <div class="fancy-toc3"><a href="#the_highlight_function">5.1.2. The highlight() function</a></div>
160 <div class="fancy-toc3"><a href="#the_snippet_function">5.1.3. The snippet() function</a></div>
161 <div class="fancy-toc3"><a href="#the_fts5_get_locale_function">5.1.4. The fts5_get_locale() function</a></div>
162 <div class="fancy-toc3"><a href="#the_fts5_insttoken_function">5.1.5. The fts5_insttoken() function</a></div>
163 <div class="fancy-toc2"><a href="#sorting_by_auxiliary_function_results">5.2. Sorting by Auxiliary Function Results</a></div>
164 <div class="fancy-toc1"><a href="#special_insert_commands">6. Special INSERT Commands</a></div>
165 <div class="fancy-toc2"><a href="#the_automerge_configuration_option">6.1. The 'automerge' Configuration Option</a></div>
166 <div class="fancy-toc2"><a href="#the_crisismerge_configuration_option">6.2. The 'crisismerge' Configuration Option</a></div>
167 <div class="fancy-toc2"><a href="#the_delete_command">6.3. The 'delete' Command</a></div>
168 <div class="fancy-toc2"><a href="#the_delete_all_command">6.4. The 'delete-all' Command</a></div>
169 <div class="fancy-toc2"><a href="#the_deletemerge_configuration_option">6.5. The 'deletemerge' Configuration Option</a></div>
170 <div class="fancy-toc2"><a href="#the_insttoken_configuration_option">6.6. The 'insttoken' Configuration Option</a></div>
171 <div class="fancy-toc2"><a href="#the_integrity_check_command">6.7. The 'integrity-check' Command</a></div>
172 <div class="fancy-toc2"><a href="#the_merge_command">6.8. The 'merge' Command</a></div>
173 <div class="fancy-toc2"><a href="#the_optimize_command">6.9. The 'optimize' Command</a></div>
174 <div class="fancy-toc2"><a href="#the_pgsz_configuration_option">6.10. The 'pgsz' Configuration Option</a></div>
175 <div class="fancy-toc2"><a href="#the_rank_configuration_option">6.11. The 'rank' Configuration Option</a></div>
176 <div class="fancy-toc2"><a href="#the_rebuild_command">6.12. The 'rebuild' Command</a></div>
177 <div class="fancy-toc2"><a href="#the_secure_delete_configuration_option">6.13. The 'secure-delete' Configuration Option</a></div>
178 <div class="fancy-toc2"><a href="#the_usermerge_configuration_option">6.14. The 'usermerge' Configuration Option</a></div>
179 <div class="fancy-toc1"><a href="#extending_fts5">7. Extending FTS5</a></div>
180 <div class="fancy-toc2"><a href="#custom_tokenizers">7.1. Custom Tokenizers</a></div>
181 <div class="fancy-toc3"><a href="#synonym_support">7.1.1. Synonym Support</a></div>
182 <div class="fancy-toc2"><a href="#custom_auxiliary_functions">7.2. Custom Auxiliary Functions</a></div>
183 <div class="fancy-toc3"><a href="#custom_auxiliary_functions_api_overview">7.2.1. Custom Auxiliary Functions API Overview</a></div>
184 <div class="fancy-toc3"><a href="#custom_auxiliary_functions_api_reference">7.2.2. Custom Auxiliary Functions API Reference</a></div>
185 <div class="fancy-toc1"><a href="#the_fts5vocab_virtual_table_module">8. The fts5vocab Virtual Table Module</a></div>
186 <div class="fancy-toc1"><a href="#fts5_data_structures">9. FTS5 Data Structures</a></div>
187 <div class="fancy-toc2"><a href="#varint_format">9.1. Varint Format</a></div>
188 <div class="fancy-toc2"><a href="#the_fts_index_idx_and_data_tables_">9.2. The FTS Index (%_idx and %_data tables)</a></div>
189 <div class="fancy-toc3"><a href="#data_structure">9.2.1. The %_data Table Rowid Space</a></div>
190 <div class="fancy-toc3"><a href="#structure_record_format">9.2.2. Structure Record Format</a></div>
191 <div class="fancy-toc3"><a href="#averages_record_format">9.2.3. Averages Record Format</a></div>
192 <div class="fancy-toc3"><a href="#segment_b_tree_format">9.2.4. Segment B-Tree Format</a></div>
193 <div class="fancy-toc4"><a href="#the_key_doclist_format">9.2.4.1. The Key/Doclist Format</a></div>
194 <div class="fancy-toc4"><a href="#data_pagination">9.2.4.2. Pagination</a></div>
195 <div class="fancy-toc4"><a href="#data_term_index">9.2.4.3. Segment Index Format</a></div>
196 <div class="fancy-toc4"><a href="#doclist_index_format">9.2.4.4. Doclist Index Format</a></div>
197 <div class="fancy-toc2"><a href="#document_sizes_table_docsize_table_">9.3. Document Sizes Table (%_docsize table)</a></div>
198 <div class="fancy-toc2"><a href="#the_table_contents_content_table_">9.4. The Table Contents (%_content table)</a></div>
199 <div class="fancy-toc2"><a href="#configuration_options_config_table_">9.5. Configuration Options (%_config table)</a></div>
200 <div class="fancy-toc1"><a href="#appendix_a">
201 Appendix A: Comparison with FTS3/4
202 </a></div>
203 <div class="fancy-toc2"><a href="#_application_porting_guide_"> Application Porting Guide </a></div>
204 <div class="fancy-toc3"><a href="#_changes_to_create_virtual_table_statements_"> Changes to CREATE VIRTUAL TABLE statements </a></div>
205 <div class="fancy-toc3"><a href="#_changes_to_select_statements_"> Changes to SELECT statements </a></div>
206 <div class="fancy-toc3"><a href="#_auxiliary_function_changes_"> Auxiliary Function Changes </a></div>
207 <div class="fancy-toc3"><a href="#_other_issues"> Other Issues</a></div>
208 <div class="fancy-toc2"><a href="#_summary_of_technical_differences_">
209 Summary of Technical Differences
210 </a></div>
211 </div>
212 </details>
213 </div>
214
215
216
217
218
219
220 <h1 id="overview_of_fts5"><span>1. </span>Overview of FTS5</h1>
221
222 <p>FTS5 is an SQLite <a href="c3ref/module.html">virtual table module</a> that provides
223 <a href="https://en.wikipedia.org/wiki/Full_text_search">full-text search</a>
224 functionality to database applications. In their most elementary form,
225 full-text search engines allow the user to efficiently search a large
226 collection of documents for the subset that contain one or more instances of a
227 search term. The search functionality provided to world wide web users by
228 <a href="https://www.google.com/">Google</a> is, among other things, a full-text search
229 engine, as it allows users to search for all documents on the web that contain,
230 for example, the term "fts5".
231
232 </p><p>To use FTS5, the user creates an FTS5 virtual table with one or more
233 columns. For example:
234
235 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
236 </pre></div>
237
238 <p>It is an error to add types, constraints or <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a> declarations to
239 a CREATE VIRTUAL TABLE statement used to create an FTS5 table. Once created,
240 an FTS5 table may be populated using <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> or <a href="lang_delete.html">DELETE</a> statements
241 like any other table. Like any other table with no PRIMARY KEY declaration, an
242 FTS5 table has an implicit INTEGER PRIMARY KEY field named rowid.
243
244 </p><p>Not shown in the example above is that there are also
245 <a href="fts5.html#fts5_table_creation_and_initialization">various options</a> that may be provided to FTS5 as
246 part of the CREATE VIRTUAL TABLE statement to configure various aspects of the
247 new table. These may be used to modify the way in which the FTS5 table extracts
248 terms from documents and queries, to create extra indexes on disk to speed up
249 prefix queries, or to create an FTS5 table that acts as an index on content
250 stored elsewhere.
251
252 </p><p>Once populated, there are three ways to execute a full-text query against
253 the contents of an FTS5 table:
254
255 </p><ul><li> Using a MATCH operator in the WHERE clause of a SELECT statement, or
256 </li><li> Using an equals ("=") operator in the WHERE clause of a SELECT statement, or
257 </li><li> using the <a href="vtab.html#tabfunc2">table-valued function</a> syntax.
258 </li></ul>
259
260 <p>If using the MATCH or = operators, the expression to the left of the MATCH
261 operator is usually the name of the FTS5 table (the exception is when
262 <a href="fts5.html#fts5_column_filters">specifying a column-filter</a>). The expression on the right
263 must be a text value specifying the term to search for. For the table-valued
264 function syntax, the term to search for is specified as the first table argument.
265 For example:
266
267 </p><div class="codeblock"><pre><i>-- Query for all rows that contain at least once instance of the term</i>
268 <i>-- "fts5" (in any column). The following three queries are equivalent.</i>
269 SELECT * FROM email WHERE email MATCH 'fts5';
270 SELECT * FROM email WHERE email = 'fts5';
271 SELECT * FROM email('fts5');
272 </pre></div>
273
274 <p> By default, FTS5 full-text searches are case-independent. Like any other
275 SQL query that does not contain an ORDER BY clause, the example above returns
276 results in an arbitrary order. To sort results by relevance (most to least
277 relevant), an ORDER BY may be added to a full-text query as follows:
278
279 </p><div class="codeblock"><pre><i>-- Query for all rows that contain at least once instance of the term</i>
280 <i>-- "fts5" (in any column). Return results in order from best to worst</i>
281 <i>-- match. </i>
282 SELECT * FROM email WHERE email MATCH 'fts5' ORDER BY rank;
283 </pre></div>
284
285 <p> As well as the column values and rowid of a matching row, an application
286 may use <a href="fts5.html#_auxiliary_functions_">FTS5 auxiliary functions</a> to retrieve extra information regarding
287 the matched row. For example, an auxiliary function may be used to retrieve
288 a copy of a column value for a matched row with all instances of the matched
289 term surrounded by html &lt;b&gt;&lt;/b&gt; tags. Auxiliary functions are
290 invoked in the same way as SQLite <a href="lang_corefunc.html">scalar functions</a>, except that the name
291 of the FTS5 table is specified as the first argument. For example:
292
293 </p><div class="codeblock"><pre><i>-- Query for rows that match "fts5". Return a copy of the "body" column</i>
294 <i>-- of each row with the matches surrounded by &lt;b&gt;&lt;/b&gt; tags.</i>
295 SELECT highlight(email, 2, '&lt;b&gt;', '&lt;/b&gt;') FROM email('fts5');
296 </pre></div>
297
298 <p>A description of the available auxiliary functions, and more details
299 regarding configuration of the special "rank" column, are
300 <a href="fts5.html#_auxiliary_functions_">available below</a>. <a href="fts5.html#custom_auxiliary_functions">Custom auxiliary functions</a> may also be implemented in C and registered with
301 FTS5, just as custom SQL functions may be registered with the SQLite core.
302
303 </p><p> As well as searching for all rows that contain a term, FTS5 allows
304 the user to search for rows that contain:
305
306 </p><ul>
307 <li> any terms that begin with a specified prefix,
308 </li><li> "phrases" - sequences of terms or prefix terms that must feature in a
309 document for it to match the query,
310 </li><li> sets of terms, prefix terms or phrases that appear within a specified
311 proximity of each other (these are called "NEAR queries"), or
312 </li><li> boolean combinations of any of the above.
313 </li></ul>
314
315 <p> Such advanced searches are requested by providing a more complicated
316 FTS5 query string as the text to the right of the MATCH operator (or =
317 operator, or as the first argument to a table-valued function syntax). The
318 full query syntax is <a href="fts5.html#full_text_query_syntax">described here</a>.
319
320 </p><a name="compiling_and_using_fts5"></a>
321 <h1 tags="FTS5 building" id="compiling_and_using_fts5"><span>2. </span>Compiling and Using FTS5</h1>
322
323 <h2 id="building_fts5_as_part_of_sqlite"><span>2.1. </span>Building FTS5 as part of SQLite</h2>
324
325 <p>As of <a href="releaselog/3_9_0.html">version 3.9.0</a> (2015-10-14),
326 FTS5 is included as part of the SQLite <a href="amalgamation.html">amalgamation</a>.
327 If using the canonical source tree, FTS5 is
328 enabled by specifying the "--enable-fts5" option when running the configure
329 script. (FTS5 is currently disabled by default for the
330 source-tree configure script and enabled by default for
331 the amalgamation configure script, but these defaults might
332 change in the future.)
333
334 </p><p>Or, if sqlite3.c is compiled using some other build system, by arranging for
335 the SQLITE_ENABLE_FTS5 pre-processor symbol to be defined.
336
337 </p><h2 id="building_a_loadable_extension"><span>2.2. </span>Building a Loadable Extension</h2>
338
339 <p>Alternatively, FTS5 may be built as a loadable extension.
340
341 </p><p>The canonical FTS5 source code consists of a series of *.c and other files
342 in the "ext/fts5" directory of the SQLite source tree. A build process reduces
343 this to just two files - "fts5.c" and "fts5.h" - which may be used to build an
344 SQLite loadable extension.
345
346 </p><ol>
347 <li> Obtain the latest SQLite code from fossil.
348 </li><li> Create a Makefile as described in <a href="howtocompile.html">How To Compile SQLite</a>.
349 </li><li> Build the "fts5.c" target, which also creates fts5.h.
350 </li></ol>
351
352 <div class="codeblock"><pre>$ wget -c https://sqlite.org/src/tarball/SQLite-trunk.tgz?uuid=trunk -O SQLite-trunk.tgz
353 .... output ...
354 $ tar -xzf SQLite-trunk.tgz
355 $ cd SQLite-trunk
356 $ ./configure && make fts5.c
357 ... lots of output ...
358 $ ls fts5.&#91;ch]
359 fts5.c fts5.h
360 </pre></div>
361
362 <p>
363 The code in "fts5.c" may then be compiled into a loadable extension or
364 statically linked into an application as described in
365 <a href="loadext.html#build">Compiling Loadable Extensions</a>. There are two entry points defined, both
366 of which do the same thing:
367
368 </p><ul>
369 <li> sqlite3_fts_init
370 </li><li> sqlite3_fts5_init
371 </li></ul>
372
373 <p>
374 The other file, "fts5.h", is not required to compile the FTS5 extension.
375 It is used by applications that implement <a href="fts5.html#extending_fts5">custom FTS5 tokenizers or auxiliary functions</a>.
376
377 </p><a name="full_text_query_syntax"></a>
378 <h1 tags="FTS5 query syntax" id="full_text_query_syntax"><span>3. </span>Full-text Query Syntax</h1>
379
380 <p>
381 The following block contains a summary of the FTS query syntax in BNF form.
382 A detailed explanation follows.
383
384 </p><div class="codeblock"><pre>&lt;phrase&gt; := string &#91;*]
385 &lt;phrase&gt; := &lt;phrase&gt; + &lt;phrase&gt;
386 &lt;neargroup&gt; := NEAR ( &lt;phrase&gt; &lt;phrase&gt; ... &#91;, N] )
387 &lt;query&gt; := &#91; &#91;-] &lt;colspec&gt; :] &#91;&#94;] &lt;phrase&gt;
388 &lt;query&gt; := &#91; &#91;-] &lt;colspec&gt; :] &lt;neargroup&gt;
389 &lt;query&gt; := &#91; &#91;-] &lt;colspec&gt; :] ( &lt;query&gt; )
390 &lt;query&gt; := &lt;query&gt; AND &lt;query&gt;
391 &lt;query&gt; := &lt;query&gt; OR &lt;query&gt;
392 &lt;query&gt; := &lt;query&gt; NOT &lt;query&gt;
393 &lt;colspec&gt; := colname
394 &lt;colspec&gt; := { colname1 colname2 ... }
395 </pre></div>
396
397 <a name="fts5_strings"></a>
398 <h2 tags="FTS5 Strings" id="fts5_strings"><span>3.1. </span>FTS5 Strings</h2>
399 <p>
400 Within an FTS expression a <b>string</b> may be specified in one of two ways:
401
402 </p><ul>
403 <li> <p>By enclosing it in double quotes ("). Within a string, any embedded
404 double quote characters may be escaped SQL-style - by adding a second
405 double-quote character.
406
407 </p></li><li> <p>As an FTS5 bareword that is not "AND", "OR" or "NOT" (case sensitive).
408 An FTS5 bareword is a string of one or more consecutive characters that
409 are all either:
410
411 </p><ul>
412 <li> Non-ASCII range characters (i.e. unicode codepoints greater
413 than 127), or
414 </li><li> One of the 52 upper and lower case ASCII characters, or
415 </li><li> One of the 10 decimal digit ASCII characters, or
416 </li><li> The underscore character (unicode codepoint 95).
417 </li><li> The substitute character (unicode codepoint 26).
418 </li></ul>
419
420 Strings that include any other characters must be quoted. Characters
421 that are not currently allowed in barewords, are not quote characters and
422 do not currently serve any special purpose in FTS5 query expressions may
423 at some point in the future be allowed in barewords or used to implement
424 new query functionality. This means that queries that are currently
425 syntax errors because they include such a character outside of a quoted
426 string may be interpreted differently by some future version of FTS5.
427 </li></ul>
428
429 <a name="fts5_phrases"></a>
430 <h2 tags="FTS5 Phrases" id="fts5_phrases"><span>3.2. </span>FTS5 Phrases</h2>
431 <p>
432 Each string in an fts5 query is parsed ("tokenized") by the
433 <a href="fts5.html#tokenizers">tokenizer</a> and a list of zero or more <b>tokens</b>, or
434 terms, extracted. For example, the default tokenizer tokenizes the string "alpha
435 beta gamma" to three separate tokens - "alpha", "beta" and "gamma" - in that
436 order.
437
438 </p><p>
439 FTS queries are made up of <b>phrases</b>. A phrase is an ordered list of
440 one or more tokens. The tokens from each string in the query each make up a
441 single phrase. Two phrases can be concatenated into a single large phrase
442 using the "+" operator. For example, assuming the tokenizer module being used
443 tokenizes the input "one.two.three" to three separate tokens, the following
444 four queries all specify the same phrase:
445
446 </p><div class="codeblock"><pre>... MATCH '"one two three"'
447 ... MATCH 'one + two + three'
448 ... MATCH '"one two" + three'
449 ... MATCH 'one.two.three'
450 </pre></div>
451
452 <p>
453 A phrase matches a document if the document contains at least one sub-sequence
454 of tokens that matches the sequence of tokens that make up the phrase.
455
456 </p><a name="fts5_prefix_queries"></a>
457 <h2 tags="FTS5 prefix queries" id="fts5_prefix_queries"><span>3.3. </span>FTS5 Prefix Queries</h2>
458 <p>
459 If a "*" character follows a string within an FTS expression, then the final
460 token extracted from the string is marked as a <b>prefix token</b>. As you
461 might expect, a prefix token matches any document token of which it is a
462 prefix. For example, the first two queries in the following block will match
463 any document that contains the token "one" immediately followed by the token
464 "two" and then any token that begins with "thr".
465
466 </p><div class="codeblock"><pre>... MATCH '"one two thr" * '
467 ... MATCH 'one + two + thr*'
468 ... MATCH '"one two thr*"' <b>-- May not work as expected!</b>
469 </pre></div>
470
471 <p>The final query in the block above may not work as expected. Because the
472 "*" character is inside the double-quotes, it will be passed to the tokenizer,
473 which will likely discard it (or perhaps, depending on the specific tokenizer
474 in use, include it as part of the final token) instead of recognizing it as
475 a special FTS character.
476
477 <a name="carrotq"></a>
478
479 </p><a name="fts5_initial_token_queries"></a>
480 <h2 tags="FTS5 initial token queries" id="fts5_initial_token_queries"><span>3.4. </span>FTS5 Initial Token Queries</h2>
481 <p>
482 If a "&#94;" character appears immediately before a phrase that is not part of a
483 NEAR query, then that phrase only matches a document only if it starts at the
484 first token in a column. The "&#94;" syntax may be combined with a
485 <a href="fts5.html#fts5_column_filters">column filter</a>, but may not be inserted into the middle of
486 a phrase.
487
488 </p><div class="codeblock"><pre>... MATCH '&#94;one' <i>-- first token in any column must be "one"</i>
489 ... MATCH '&#94; one + two' <i>-- phrase "one two" must appear at start of a column</i>
490 ... MATCH '&#94; "one two"' <i>-- same as previous </i>
491 ... MATCH 'a : &#94;two' <i>-- first token of column "a" must be "two"</i>
492 ... MATCH 'NEAR(&#94;one, two)' <b>-- syntax error! </b>
493 ... MATCH 'one + &#94;two' <b>-- syntax error! </b>
494 ... MATCH '"&#94;one two"' <b>-- May not work as expected!</b>
495 </pre></div>
496
497 <a name="fts5_near_queries"></a>
498 <h2 tags="FTS5 NEAR queries" id="fts5_near_queries"><span>3.5. </span>FTS5 NEAR Queries</h2>
499
500 <p>Two or more phrases may be grouped into a <b>NEAR group</b>. A NEAR group
501 is specified by the token "NEAR" (case sensitive) followed by an open
502 parenthesis character, followed by two or more whitespace separated phrases, optionally followed by a comma and the numeric parameter <i>N</i>, followed by
503 a close parenthesis. For example:
504
505 </p><div class="codeblock"><pre>... MATCH 'NEAR("one two" "three four", 10)'
506 ... MATCH 'NEAR("one two" thr* + four)'
507 </pre></div>
508
509 <p>If no <i>N</i> parameter is supplied, it defaults to 10. A NEAR group
510 matches a document if the document contains at least one clump of tokens that:
511
512 </p><ol>
513 <li> contains at least one instance of each phrase, and
514 </li><li> for which the number of tokens between the end of the first phrase
515 and the beginning of the last phrase in the clump is less than or equal to <i>N</i>.
516 </li></ol>
517
518 <p>For example:
519
520 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(x);
521 INSERT INTO ft(rowid, x) VALUES(1, 'A B C D x x x E F x');
522
523 ... MATCH 'NEAR(e d, 4)'; <i>-- Matches!</i>
524 ... MATCH 'NEAR(e d, 3)'; <i>-- Matches!</i>
525 ... MATCH 'NEAR(e d, 2)'; <i>-- Does not match!</i>
526
527 ... MATCH 'NEAR("c d" "e f", 3)'; <i>-- Matches!</i>
528 ... MATCH 'NEAR("c" "e f", 3)'; <i>-- Does not match!</i>
529
530 ... MATCH 'NEAR(a d e, 6)'; <i>-- Matches!</i>
531 ... MATCH 'NEAR(a d e, 5)'; <i>-- Does not match!</i>
532
533 ... MATCH 'NEAR("a b c d" "b c" "e f", 4)'; <i>-- Matches!</i>
534 ... MATCH 'NEAR("a b c d" "b c" "e f", 3)'; <i>-- Does not match!</i>
535 </pre></div>
536
537 <a name="fts5_column_filters"></a>
538 <h2 tags="FTS5 column filters" id="fts5_column_filters"><span>3.6. </span>FTS5 Column Filters</h2>
539
540 <p>
541 A single phrase or NEAR group may be restricted to matching text within a
542 specified column of the FTS table by prefixing it with the column name
543 followed by a colon character. Or to a set of columns by prefixing it
544 with a whitespace separated list of column names enclosed in braces
545 ("curly brackets") followed by a colon character. Column names may be specified
546 using either of the two forms described for strings above. Unlike strings that
547 are part of phrases, column names are not passed to the tokenizer module.
548 Column names are case-insensitive in the usual way for SQLite column names -
549 upper/lower case equivalence is understood for ASCII-range characters only.
550
551 </p><div class="codeblock"><pre>... MATCH 'colname : NEAR("one two" "three four", 10)'
552 ... MATCH '"colname" : one + two + three'
553
554 ... MATCH '{col1 col2} : NEAR("one two" "three four", 10)'
555 ... MATCH '{col2 col1 col3} : one + two + three'
556 </pre></div>
557
558 <p>
559 If a column filter specification is preceded by a "-" character, then
560 it is interpreted as a list of column not to match against. For example:
561
562 </p><div class="codeblock"><pre><i>-- Search for matches in all columns except "colname"</i>
563 ... MATCH '- colname : NEAR("one two" "three four", 10)'
564
565 <i>-- Search for matches in all columns except "col1", "col2" and "col3"</i>
566 ... MATCH '- {col2 col1 col3} : one + two + three'
567 </pre></div>
568
569 <p>
570 Column filter specifications may also be applied to arbitrary expressions
571 enclosed in parentheses. In this case the column filter applies to all
572 phrases within the expression. Nested column filter operations may only
573 further restrict the subset of columns matched, they can not be used to
574 re-enable filtered columns. For example:
575
576 </p><div class="codeblock"><pre><i>-- The following are equivalent:</i>
577 ... MATCH '{a b} : ( {b c} : "hello" AND "world" )'
578 ... MATCH '(b : "hello") AND ({a b} : "world")'
579 </pre></div>
580
581 <p>
582 Finally, a column filter for a single column may be specified by using
583 the column name as the LHS of a MATCH operator (instead of the usual
584 table name). For example:
585
586 </p><div class="codeblock"><pre><i>-- Given the following table</i>
587 CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
588
589 <i>-- The following are equivalent</i>
590 SELECT * FROM ft WHERE b MATCH 'uvw AND xyz';
591 SELECT * FROM ft WHERE ft MATCH 'b : (uvw AND xyz)';
592
593 <i>-- This query cannot match any rows (since all columns are filtered out): </i>
594 SELECT * FROM ft WHERE b MATCH 'a : xyz';
595 </pre></div>
596
597 <a name="fts5_boolean_operators"></a>
598 <h2 tags="FTS5 boolean operators" id="fts5_boolean_operators"><span>3.7. </span>FTS5 Boolean Operators</h2>
599
600 <p>
601 Phrases and NEAR groups may be arranged into expressions using <b>boolean
602 operators</b>. In order of precedence, from highest (tightest grouping) to
603 lowest (loosest grouping), the operators are:
604
605 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
606 <tr style="text-align:left"><th>Operator </th><th>Function
607
608 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td><code>&lt;query1&gt; NOT &lt;query2&gt;</code>
609 </td><td>Matches if query1 matches and query2 does not match.
610
611 </td></tr><tr style="text-align:left"><td><code>&lt;query1&gt; AND &lt;query2&gt;</code>
612 </td><td>Matches if both query1 and query2 match.
613
614 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td><code>&lt;query1&gt; OR &lt;query2&gt;</code>
615 </td><td>Matches if either query1 or query2 match.
616
617 </td></tr></table>
618
619 <p>
620 Parentheses may be used to group expressions in order to modify operator
621 precedence in the usual ways. For example:
622
623 </p><div class="codeblock"><pre><i>-- Because NOT groups more tightly than OR, either of the following may</i>
624 <i>-- be used to match all documents that contain the token "two" but not</i>
625 <i>-- "three", or contain the token "one". </i>
626 ... MATCH 'one OR two NOT three'
627 ... MATCH 'one OR (two NOT three)'
628
629 <i>-- Matches documents that contain at least one instance of either "one"</i>
630 <i>-- or "two", but do not contain any instances of token "three".</i>
631 ... MATCH '(one OR two) NOT three'
632 </pre></div>
633
634 <p>
635 Phrases and NEAR groups may also be connected by <b>implicit AND operators</b>.
636 For simplicity, these are not shown in the BNF grammar above. Essentially, any
637 sequence of phrases or NEAR groups (including those restricted to matching
638 specified columns) separated only by whitespace are handled as if there were an
639 implicit AND operator between each pair of phrases or NEAR groups. Implicit
640 AND operators are never inserted after or before an expression enclosed in
641 parentheses. Implicit AND operators group more tightly than all other
642 operators, including NOT. For example:
643
644 </p><div class="codeblock"><pre>... MATCH 'one two three' <i>-- 'one AND two AND three'</i>
645 ... MATCH 'three "one two"' <i>-- 'three AND "one two"'</i>
646 ... MATCH 'NEAR(one two) three' <i>-- 'NEAR(one two) AND three'</i>
647 ... MATCH 'one OR two three' <i>-- 'one OR two AND three'</i>
648 ... MATCH 'one NOT two three' <i>-- 'one NOT (two AND three)'</i>
649
650 ... MATCH '(one OR two) three' <i>-- Syntax error!</i>
651 ... MATCH 'func(one two)' <i>-- Syntax error!</i>
652 </pre></div>
653
654 <a name="fts5_table_creation_and_initialization"></a>
655 <h1 tags="FTS5 CREATE TABLE Options" id="fts5_table_creation_and_initialization"><span>4. </span>FTS5 Table Creation and Initialization</h1>
656
657 <p>Each argument specified as part of a "CREATE VIRTUAL TABLE ... USING fts5
658 ..." statement is either a column declaration or a configuration option. A
659 <b>column declaration</b> consists of one or more whitespace separated FTS5
660 barewords or string literals quoted in any manner acceptable to SQLite.
661
662 </p><p>The first string or bareword in a column declaration is the column name. It
663 is an error to attempt to name an fts5 table column "rowid" or "rank", or to
664 assign the same name to a column as is used by the table itself. This is not
665 supported.
666
667 </p><p>Each subsequent string or bareword in a column declaration is a column
668 option that modifies the behavior of that column. Column options are
669 case-independent. Unlike the SQLite core, FTS5 considers unrecognized column
670 options to be errors. Currently, the only option recognized is
671 <a href="fts5.html#the_unindexed_column_option">"UNINDEXED" (see below)</a>.
672
673 </p><p>A <b>configuration option</b> consists of an FTS5 bareword - the option name -
674 followed by an "=" character, followed by the option value. The option value is
675 specified using either a single FTS5 bareword or a string literal, again quoted
676 in any manner acceptable to the SQLite core. For example:
677
678 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE mail USING fts5(sender, title, body, tokenize = 'porter ascii');
679 </pre></div>
680
681 <p> There are currently the following configuration options:
682
683 </p><ul>
684 <li> The "tokenize" option, used to configure a <a href="fts5.html#tokenizers">custom tokenizer</a>.
685 </li><li> The "prefix" option, used to add <a href="fts5.html#prefix_indexes">prefix indexes</a>
686 to an FTS5 table.
687 </li><li> The "content" option, used to make the FTS5 table an
688 <a href="fts5.html#external_content_and_contentless_tables">external content or contentless table</a>.
689 </li><li> The "content_rowid" option, used to set the rowid field of an
690 <a href="fts5.html#external_content_tables">external content table</a>.
691 </li><li> The <a href="fts5.html#the_columnsize_option">"columnsize" option</a>, used to configure
692 whether or not the size in tokens of each value in the FTS5 table is
693 stored separately within the database.
694 </li><li> The <a href="fts5.html#the_detail_option">"detail" option</a>. This option may be used
695 to reduce the size of the FTS index on disk by omitting some information
696 from it.
697 </li></ul>
698
699 <a name="the_unindexed_column_option"></a>
700 <h2 tags="unindexed" id="the_unindexed_column_option"><span>4.1. </span>The UNINDEXED column option</h2>
701
702 <p>The contents of columns qualified with the UNINDEXED column option are not
703 added to the FTS index. This means that for the purposes of MATCH queries and
704 <a href="fts5.html#_auxiliary_functions_">FTS5 auxiliary functions</a>, the column contains no matchable tokens.
705
706 </p><p>For example, to avoid adding the contents of the "uuid" field to the FTS
707 index:
708 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE customers USING fts5(name, addr, uuid UNINDEXED);
709 </pre></div>
710
711 <a name="prefix_indexes"></a>
712 <h2 tags="FTS5 prefix indexes" id="prefix_indexes"><span>4.2. </span>Prefix Indexes</h2>
713
714 <p> By default, FTS5 maintains a single index recording the location of each
715 token instance within the document set. This means that querying for complete
716 tokens is fast, as it requires a single lookup, but querying for a prefix
717 token can be slow, as it requires a range scan. For example, to query for
718 the prefix token "abc*" requires a range scan of all tokens greater than
719 or equal to "abc" and less than "abd".
720
721 </p><p> A prefix index is a separate index that records the location of all
722 instances of prefix tokens of a certain length in characters used to speed
723 up queries for prefix tokens. For example, optimizing a query for prefix
724 token "abc*" requires a prefix index of three-character prefixes.
725
726 </p><p> To add prefix indexes to an FTS5 table, the "prefix" option is set to
727 either a single positive integer or a text value containing a white-space
728 separated list of one or more positive integer values. A prefix index is
729 created for each integer specified. If more than one "prefix" option is
730 specified as part of a single CREATE VIRTUAL TABLE statement, all apply.
731
732 </p><div class="codeblock"><pre><i>-- Two ways to create an FTS5 table that maintains prefix indexes for
733 -- two and three character prefix tokens.</i>
734 CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix='2 3');
735 CREATE VIRTUAL TABLE ft USING fts5(a, b, prefix=2, prefix=3);
736 </pre></div>
737
738 <a name="tokenizers"></a>
739 <h2 tags="FTS5 tokenizers" id="tokenizers"><span>4.3. </span>Tokenizers</h2>
740
741 <p> The CREATE VIRTUAL TABLE "tokenize" option is used to configure the
742 specific tokenizer used by the FTS5 table. The option argument must be either
743 an FTS5 bareword, or an SQL text literal. The text of the argument is itself
744 treated as a white-space series of one or more FTS5 barewords or SQL text
745 literals. The first of these is the name of the tokenizer to use. The second
746 and subsequent list elements, if they exist, are arguments passed to the
747 tokenizer implementation.
748
749 </p><p> Unlike option values and column names, SQL text literals intended as
750 tokenizers must be quoted using single quote characters. For example:
751
752 </p><div class="codeblock"><pre><i>-- The following are all equivalent</i>
753 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = 'porter ascii');
754 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = "porter ascii");
755 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = "'porter' 'ascii'");
756 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = '''porter'' ''ascii''');
757
758 <i>-- But this will fail:</i>
759 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = '"porter" "ascii"');
760
761 <i>-- This will fail too:</i>
762 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = 'porter' 'ascii');
763 </pre></div>
764
765
766 <p>
767 FTS5 features four built-in tokenizer modules, described in subsequent
768 sections:
769
770 </p><ul>
771 <li> The <b>unicode61</b> tokenizer, based on the Unicode 6.1 standard. This
772 is the default.
773
774 </li><li> The <b>ascii</b> tokenizer, which assumes all characters outside of
775 the ASCII codepoint range (0-127) are to be treated as token characters.
776
777 </li><li> The <b>porter</b> tokenizer, which implements the
778 <a href="https://tartarus.org/martin/PorterStemmer/">porter stemming algorithm</a>.
779
780 </li><li> The <b>trigram</b> tokenizer, which treats each contiguous sequence of
781 three characters as a token, allowing FTS5 to support more general substring matching.
782 </li></ul>
783
784 <p> It is also possible to create custom tokenizers for FTS5. The API for doing so is <a href="fts5.html#custom_tokenizers">described here</a>.
785
786 </p><h3 id="unicode61_tokenizer"><span>4.3.1. </span>Unicode61 Tokenizer</h3>
787
788 <p> The unicode tokenizer classifies all unicode characters as either
789 "separator" or "token" characters. By default all space and punctuation
790 characters, as defined by Unicode 6.1, are considered separators, and all
791 other characters as token characters. More specifically, all unicode
792 characters assigned to a
793 <a href="https://en.wikipedia.org/wiki/Unicode_character_property#General_Category">
794 general category</a> beginning with "L" or "N" (letters and numbers,
795 specifically) or to category "Co" ("other, private use") are considered tokens.
796 All other characters are separators.
797
798 </p><p>Each contiguous run of one or more token characters is considered to be a
799 token. The tokenizer is case-insensitive according to the rules defined by
800 Unicode 6.1.
801
802 </p><p> By default, diacritics are removed from all Latin script characters. This
803 means, for example, that "A", "a", "&#192;", "&#224;", "&#194;" and "&#226;"
804 are all considered to be equivalent.
805
806 </p><p> Any arguments following "unicode61" in the token specification are treated
807 as a list of alternating option names and values. Unicode61 supports the
808 following options:
809
810 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
811 <tr style="text-align:left"><th> Option </th><th> Usage
812 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> remove_diacritics
813 </td><td>This option should be set to "0", "1" or "2". The default value is "1".
814 If it is set to "1" or "2", then diacritics are removed from Latin script
815 characters as described above. However, if it is set to "1", then diacritics
816 are not removed in the fairly uncommon case where a single unicode codepoint
817 is used to represent a character with more that one diacritic. For example,
818 diacritics are not removed from codepoint 0x1ED9 ("LATIN SMALL LETTER O WITH
819 CIRCUMFLEX AND DOT BELOW"). This is technically a bug, but cannot be fixed
820 without creating backwards compatibility problems. If this option is set to
821 "2", then diacritics are correctly removed from all Latin characters.
822
823 </td></tr><tr style="text-align:left"><td> categories
824 </td><td>This option may be used to modify the set of Unicode general categories
825 that are considered to correspond to token characters. The argument must
826 consist of a space separated list of two-character general category
827 abbreviations (e.g. "Lu" or "Nd"), or of the same with the second character
828 replaced with an asterisk ("*"), interpreted as a glob pattern. The default
829 value is "L* N* Co".
830
831 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td> tokenchars
832 </td><td> This option is used to specify additional unicode characters that
833 should be considered token characters, even if they are white-space or
834 punctuation characters according to Unicode 6.1. All characters in the
835 string that this option is set to are considered token characters.
836
837 </td></tr><tr style="text-align:left"><td> separators
838 </td><td> This option is used to specify additional unicode characters that
839 should be considered as separator characters, even if they are token
840 characters according to Unicode 6.1. All characters in the string that
841 this option is set to are considered separators.
842 </td></tr></table>
843
844 <p> For example:
845
846 </p><div class="codeblock"><pre><i>-- Create an FTS5 table that does not remove diacritics from Latin
847 -- script characters, and that considers hyphens and underscore characters
848 -- to be part of tokens. </i>
849 CREATE VIRTUAL TABLE ft USING fts5(a, b,
850 tokenize = "unicode61 remove_diacritics 0 tokenchars '-_'"
851 );
852 </pre></div>
853
854 <p> or:
855
856 </p><div class="codeblock"><pre><i>-- Create an FTS5 table that, as well as the default token character classes,</i>
857 <i>-- considers characters in class "Mn" to be token characters.</i>
858 CREATE VIRTUAL TABLE ft USING fts5(a, b,
859 tokenize = "unicode61 categories 'L* N* Co Mn'"
860 );
861 </pre></div>
862
863 <p> The fts5 unicode61 tokenizer is byte-for-byte compatible with the fts3/4
864 unicode61 tokenizer.
865
866 </p><h3 id="ascii_tokenizer"><span>4.3.2. </span>Ascii Tokenizer</h3>
867
868 <p> The Ascii tokenizer is similar to the Unicode61 tokenizer, except that:
869
870 </p><ul>
871 <li> All non-ASCII characters (those with codepoints greater than 127) are
872 always considered token characters. If any non-ASCII characters are specified
873 as part of the separators option, they are ignored.
874
875 </li><li> Case-folding is only performed for ASCII characters. So while "A" and
876 "a" are considered to be equivalent, "&#195;" and "&#227;" are distinct.
877
878 </li><li> The remove_diacritics option is not supported.
879 </li></ul>
880
881 <p> For example:
882
883 </p><div class="codeblock"><pre><i>-- Create an FTS5 table that uses the ascii tokenizer, but does not
884 -- consider numeric characters to be part of tokens.</i>
885 CREATE VIRTUAL TABLE ft USING fts5(a, b,
886 tokenize = "ascii separators '0123456789'"
887 );
888 </pre></div>
889
890 <h3 id="porter_tokenizer"><span>4.3.3. </span>Porter Tokenizer</h3>
891
892 <p> The porter tokenizer is a wrapper tokenizer. It takes the output of some
893 other tokenizer and applies the
894 <a href="https://tartarus.org/martin/PorterStemmer/">porter stemming algorithm</a>
895 to each token before it returns it to FTS5. This allows search terms like
896 "correction" to match similar words such as "corrected" or "correcting". The
897 porter stemmer algorithm is designed for use with English language terms
898 only - using it with other languages may or may not improve search utility.
899
900 </p><p> By default, the porter tokenizer operates as a wrapper around the default
901 tokenizer (unicode61). Or, if one or more extra arguments are added to the
902 "tokenize" option following "porter", they are treated as a specification for
903 the underlying tokenizer that the porter stemmer uses. For example:
904
905 </p><div class="codeblock"><pre><i>-- Two ways to create an FTS5 table that uses the porter tokenizer to
906 -- stem the output of the default tokenizer (unicode61). </i>
907 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = porter);
908 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = 'porter unicode61');
909
910 <i>-- A porter tokenizer used to stem the output of the unicode61 tokenizer,
911 -- with diacritics removed before stemming.</i>
912 CREATE VIRTUAL TABLE ft USING fts5(x, tokenize = 'porter unicode61 remove_diacritics 1');
913 </pre></div>
914
915 <a name="trigramidx"></a>
916
917 <h3 id="the_trigram_tokenizer"><span>4.3.4. </span>The Trigram Tokenizer</h3>
918
919 <p>
920 The trigram tokenizer extends FTS5 to support substring
921 matching in general, instead of the usual token matching. When using the
922 trigram tokenizer, a query or phrase token may match any sequence of characters
923 within a row, not just a complete token. For example:
924
925 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tri USING fts5(a, tokenize="trigram");
926 INSERT INTO tri VALUES('abcdefghij KLMNOPQRST uvwxyz');
927
928 <i>-- The following queries all match the single row in the table</i>
929 SELECT * FROM tri('cdefg');
930 SELECT * FROM tri('cdefg AND pqr');
931 SELECT * FROM tri('"hij klm" NOT stuv');
932 </pre></div>
933
934 <p>
935 The trigram tokenizer supports the following options:
936
937 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
938 <tr style="text-align:left"><th> Option </th><th> Usage
939 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td> case_sensitive
940 </td><td> This value may be set to 1 or 0 (the default). If it is set to 1,
941 then matching is case sensitive. Otherwise, if this option is set to
942 0, matching is case insensitive.
943
944 </td></tr><tr style="text-align:left"><td> remove_diacritics
945 </td><td> This value may also be set to 1 or 0 (the default). It may only
946 be set to 1 if the case_sensitive options is set to 0 - setting both
947 options to 1 is an error. If this option is set, then diacritics are
948 removed from the text before matching (e.g. so that "á" matches "a").
949 </td></tr></table>
950
951 <div class="codeblock"><pre><i>-- A case-sensitive trigram index</i>
952 CREATE VIRTUAL TABLE tri USING fts5(a, tokenize="trigram case_sensitive 1");
953 </pre></div>
954
955 <p>
956 Unless the remove_diacritics option is set, FTS5 tables that use the trigram
957 tokenizer also support indexed GLOB and LIKE pattern matching. For example:
958
959 </p><div class="codeblock"><pre>SELECT * FROM tri WHERE a LIKE '%cdefg%';
960 SELECT * FROM tri WHERE a GLOB '*ij klm*xyz';
961 </pre></div>
962
963 <p>
964 If an FTS5 trigram tokenizer is created with the case_sensitive option set to 1,
965 it may only index GLOB queries, not LIKE.
966
967 </p><p>
968 Notes:
969
970 </p><ul>
971 <li> Substrings consisting of fewer than 3 unicode characters do not match any
972 rows when used with a full-text query. If a LIKE or GLOB pattern does not
973 contain at least one sequence of non-wildcard unicode characters, FTS5
974 falls back to a linear scan of the entire table.
975
976 </li><li> If the FTS5 table is created with the detail=none or detail=column option
977 specified, full-text queries may not contain any tokens longer than 3
978 unicode characters. LIKE and GLOB pattern matching may be slightly slower,
979 but still works. If the index is to be used only for LIKE and/or GLOB
980 pattern matching, these options are worth experimenting with to reduce
981 the index size.
982
983 </li><li> The index cannot be used to optimize LIKE patterns if the LIKE operator
984 has an ESCAPE clause.
985 </li></ul>
986
987 <a name="external_content_and_contentless_tables"></a>
988 <h2 tags="FTS5 content option" id="external_content_and_contentless_tables"><span>4.4. </span>External Content and Contentless Tables</h2>
989
990 <p>
991 Normally, when a row is inserted into an FTS5 table, in addition to building
992 the index, FTS5 makes a copy of the original row content.
993 When column values are requested from the FTS5 table by the user or by an
994 auxiliary function implementation, those values are
995 read from that private copy of the content. The "content" option may be used
996 to create an FTS5 table that stores only FTS full-text index entries.
997 Because the column values themselves are usually much larger than the
998 associated full-text index entries, this can save significant database space.
999
1000 </p><p>
1001 There are two ways to use the "content" option:
1002 </p><ul>
1003 <li> By setting it to an empty string to create a contentless FTS5 table. In
1004 this case FTS5 assumes that the original column values are unavailable
1005 to it when processing queries. Full-text queries and some auxiliary
1006 functions can still be used, but no column values apart from the rowid
1007 may be read from the table.
1008
1009 </li><li> By setting it to the name of a database object (table, virtual table or
1010 view) that may be queried by FTS5 at any time to retrieve the column
1011 values. This is known as an "external content" table. In this case all
1012 FTS5 functionality may be used, but it is the responsibility of the user
1013 to ensure that the contents of the full-text index are consistent with
1014 the named database object. If they are not, query results may be
1015 unpredictable.
1016 </li></ul>
1017
1018 <a name="contentless_tables"></a>
1019 <h3 tags="FTS5 contentless tables" id="contentless_tables"><span>4.4.1. </span>Contentless Tables</h3>
1020
1021 <p> A contentless FTS5 table is created by setting the "content" option to
1022 an empty string. For example:
1023
1024 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(a, b, c, content='');
1025 </pre></div>
1026
1027 <p> Contentless FTS5 tables do not support UPDATE or DELETE statements, or
1028 INSERT statements that do not supply a non-NULL value for the rowid field.
1029 Contentless tables do not support REPLACE conflict handling. REPLACE
1030 and INSERT OR REPLACE statements are treated as regular INSERT statements.
1031 Rows may be deleted from a contentless table using an <a href="fts5.html#the_delete_command">FTS5 delete command</a>.
1032
1033 </p><p> Attempting to read any column value except the rowid from a contentless
1034 FTS5 table returns an SQL NULL value.
1035
1036 <a name="clssdeltab"></a>
1037
1038 </p><a name="contentless_delete_tables"></a>
1039 <h3 tags="FTS5 contentless-delete tables" id="contentless_delete_tables"><span>4.4.2. </span>Contentless-Delete Tables</h3>
1040
1041 <p>As of version 3.43.0, also available are contentless-delete tables.
1042 A contentless-delete table is created by setting the content option to an
1043 empty string and also setting the contentless_delete option to 1. For example:
1044
1045 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(a, b, c, content='', contentless_delete=1);
1046 </pre></div>
1047
1048 <p>A contentless-delete table differs from a contentless table in that:
1049
1050 </p><ul>
1051 <li> Contentless-delete tables support both DELETE and "INSERT OR REPLACE
1052 INTO" statements.
1053 </li><li> Contentless-delete tables support UPDATE statements, but only if new
1054 values are supplied for all user-defined columns of the fts5 table.
1055 </li><li> Contentless-delete tables do <b>not</b> support the
1056 <a href="fts5.html#the_delete_command">FTS5 delete command</a>.
1057 </li></ul>
1058
1059 <div class="codeblock"><pre><i>-- Supported UPDATE statement:</i>
1060 UPDATE ft SET a=?, b=?, c=? WHERE rowid=?;
1061
1062 <i>-- This UPDATE is not supported, as it does not supply a new value</i>
1063 <i>-- for column "c".</i>
1064 UPDATE ft SET a=?, b=? WHERE rowid=?;
1065 </pre></div>
1066
1067 <p> Unless backwards compatibility is required, new code should prefer
1068 contentless-delete tables to contentless tables.
1069
1070 </p><a name="external_content_tables"></a>
1071 <h3 tags="FTS5 external content tables" id="external_content_tables"><span>4.4.3. </span>External Content Tables</h3>
1072
1073 <p> An external content FTS5 table is created by setting the content
1074 option to the name of a table, virtual table or view (hereafter the "content
1075 table") within the same database. Whenever column values are required by
1076 FTS5, it queries the content table as follows, with the rowid of the row
1077 for which values are required bound to the SQL variable:
1078
1079 </p><div class="codeblock"><pre>SELECT &lt;content_rowid&gt;, &lt;cols&gt; FROM &lt;content&gt; WHERE &lt;content_rowid&gt; = ?;
1080 </pre></div>
1081
1082 <p> In the above, &lt;content&gt; is replaced by the name of the content table.
1083 By default, &lt;content_rowid&gt; is replaced by the literal text "rowid". Or,
1084 if the "content_rowid" option is set within the CREATE VIRTUAL TABLE statement,
1085 by the value of that option. &lt;cols&gt; is replaced by a comma-separated list
1086 of the FTS5 table column names. For example:
1087
1088 </p><div class="codeblock"><pre><i>-- If the database schema is: </i>
1089 CREATE TABLE t1 (a, b, c, d INTEGER PRIMARY KEY);
1090 CREATE VIRTUAL TABLE ft USING fts5(a, c, content=t1, content_rowid=d);
1091
1092 <i>-- Fts5 may issue queries such as:</i>
1093 SELECT d, a, c FROM t1 WHERE d = ?;
1094 </pre></div>
1095
1096 <p> The content table may also be queried as follows:
1097
1098 </p><div class="codeblock"><pre>SELECT &lt;content_rowid&gt;, &lt;cols&gt; FROM &lt;content&gt; ORDER BY &lt;content_rowid&gt; ASC;
1099 SELECT &lt;content_rowid&gt;, &lt;cols&gt; FROM &lt;content&gt; ORDER BY &lt;content_rowid&gt; DESC;
1100 </pre></div>
1101
1102 <p> It is still the responsibility of the user to ensure that the contents of
1103 an external content FTS5 table are kept up to date with the content table.
1104 One way to do this is with triggers. For example:
1105
1106 </p><div class="codeblock"><pre><i>-- Create a table. And an external content fts5 table to index it.</i>
1107 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
1108 CREATE VIRTUAL TABLE fts_idx USING fts5(b, c, content='t1', content_rowid='a');
1109
1110 <i>-- Triggers to keep the FTS index up to date.</i>
1111 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
1112 INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
1113 END;
1114 CREATE TRIGGER t1_ad AFTER DELETE ON t1 BEGIN
1115 INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
1116 END;
1117 CREATE TRIGGER t1_au AFTER UPDATE ON t1 BEGIN
1118 INSERT INTO fts_idx(fts_idx, rowid, b, c) VALUES('delete', old.a, old.b, old.c);
1119 INSERT INTO fts_idx(rowid, b, c) VALUES (new.a, new.b, new.c);
1120 END;
1121 </pre></div>
1122
1123 <p> Like contentless tables, external content tables do not support REPLACE
1124 conflict handling. Any operations that specify REPLACE conflict handling are
1125 handled using ABORT.
1126
1127 </p><a name="external_content_table_pitfalls"></a>
1128 <h3 tags="FTS5 external content pitfalls" id="external_content_table_pitfalls"><span>4.4.4. </span>External Content Table Pitfalls</h3>
1129
1130 <p>
1131 It is the responsibility of the user to ensure that an FTS5 external content
1132 table (one with a non-empty content= option) is kept consistent with the
1133 content table itself (the table named by the content= option). If these are
1134 allowed to become inconsistent, then the results of queries against the FTS5
1135 table may become unintuitive and appear inconsistent.
1136
1137 </p><p>
1138 In these situations, the apparently inconsistent results produced by queries
1139 against the FTS5 external content table may be understood as follows:
1140
1141 </p><ul>
1142 <li><p> If the query does not use the full-text index - does not contain a
1143 MATCH operator or equivalent table-valued function syntax - then the
1144 query is effectively passed through to the external content table. In
1145 this case the contents of the FTS index have no effect on the results
1146 of the query.
1147
1148 </p></li><li><p> If the query does use the full text index, then the FTS5 module
1149 queries it for the set of rowid values corresponding to documents that match
1150 the query. For each such rowid, it then runs a query similar to the following
1151 to retrieve any required column values, where '?' is replaced by the rowid
1152 value, and &lt;content&gt; and &lt;content_rowid&gt; by the values specified
1153 for the content= and content_rowid= options:
1154 </p></li></ul>
1155
1156 <div class="codeblock"><pre>SELECT &lt;content_rowid&gt;, &lt;cols&gt; FROM &lt;content&gt; WHERE &lt;content_rowid&gt; = ?;
1157 </pre></div>
1158
1159 <p>
1160 For example, if a database is created using the following script:
1161
1162 </p><div class="codeblock"><pre><i>-- Create and populate a table. </i>
1163 CREATE TABLE t1(a INTEGER PRIMARY KEY, t TEXT);
1164 INSERT INTO t1 VALUES(1, 'all that glitters');
1165 INSERT INTO t1 VALUES(2, 'is not gold');
1166
1167 <i>-- Create an external content FTS5 table </i>
1168 CREATE VIRTUAL TABLE ft USING fts5(t, content='t1', content_rowid='a');
1169 </pre></div>
1170
1171 <p>then the content table contains two rows, but the FTS index contains no
1172 entries corresponding to them. In this case the following queries will return
1173 inconsistent results as follows:
1174
1175 </p><div class="codeblock"><pre><i>-- Returns 2 rows. Because the query does not use the FTS index, it is</i>
1176 <i>-- effectively executed against table 't1' directly, and so returns</i>
1177 <i>-- both rows.</i>
1178 SELECT * FROM ft;
1179
1180 <i>-- Returns 0 rows. This query does use the FTS index, which currently</i>
1181 <i>-- contains no entries. So it returns 0 rows.</i>
1182 SELECT rowid, t FROM ft('gold')
1183 </pre></div>
1184
1185 <p>
1186 Alternatively, if the database were created and populated as follows:
1187
1188 </p><div class="codeblock"><pre><i>-- Create and populate a table. </i>
1189 CREATE TABLE t1(a INTEGER PRIMARY KEY, t TEXT);
1190
1191 <i>-- Create an external content FTS5 table </i>
1192 CREATE VIRTUAL TABLE ft USING fts5(t, content='t1', content_rowid='a');
1193 INSERT INTO ft(rowid, t) VALUES(1, 'all that glitters');
1194 INSERT INTO ft(rowid, t) VALUES(2, 'is not gold');
1195 </pre></div>
1196
1197 <p>then the content table is empty, but the FTS index contains entries for
1198 6 different tokens. In this case the following queries will return
1199 inconsistent results as follows:
1200
1201 </p><div class="codeblock"><pre><i>-- Returns 0 rows. Since it does not use the FTS index, the query is</i>
1202 <i>-- passed directly through to table 't1', which contains no data.</i>
1203 SELECT * FROM ft;
1204
1205 <i>-- Returns 1 row. The "rowid" field of the returned row is 2, and</i>
1206 <i>-- the "t" field set to NULL. "t" is set to NULL because when the external</i>
1207 <i>-- content table "t1" was queried for the data associated with the row</i>
1208 <i>-- with a=2 ("a" is the content_rowid column), none could be found.</i>
1209 SELECT rowid, t FROM ft('gold')
1210 </pre></div>
1211
1212 <p>As described in the previous section, triggers on the content table are
1213 a good way to ensure that an FTS5 external content table is kept consistent.
1214 However, triggers are only fired when rows are inserted, updated or deleted
1215 in the content table. This means that if, for example, a database is created
1216 as follows:
1217
1218 </p><div class="codeblock"><pre><i>-- Create and populate a table. </i>
1219 CREATE TABLE t1(a INTEGER PRIMARY KEY, t TEXT);
1220 INSERT INTO t1 VALUES(1, 'all that glitters');
1221 INSERT INTO t1 VALUES(2, 'is not gold');
1222
1223 <i>-- Create an external content FTS5 table </i>
1224 CREATE VIRTUAL TABLE ft USING fts5(t, content='t1', content_rowid='a');
1225
1226 <i>-- Create triggers to keep the FTS5 table up to date</i>
1227 CREATE TRIGGER t1_ai AFTER INSERT ON t1 BEGIN
1228 INSERT INTO ft(rowid, t) VALUES (new.a, new.t);
1229 END;
1230 &lt;similar triggers for update + delete&gt;
1231 </pre></div>
1232
1233 <p>then the content table and external content FTS5 table are inconsistent, as
1234 creating the triggers does not copy existing rows from the content table
1235 into the FTS index. The triggers are only able to ensure that updates made to
1236 the content table after they are created are reflected in the FTS index.
1237
1238 </p><p>In this, and any other situation where the FTS index and its content table
1239 have become inconsistent, the <a href="#the_rebuild_command">'rebuild'</a>
1240 command may be used to completely discard the contents of the FTS index and
1241 rebuild it based on the current contents of the content table.
1242
1243 </p><h4 id="updates_and_deletes_on_contentless_tables"><span>4.4.4.1. </span>Updates and Deletes on Contentless Tables</h4>
1244
1245 <p>Contentless tables do support UPDATE and DELETE statements, although they
1246 must be used with care. When a DELETE is executed against the contentless
1247 table, for each affected row the current values are retrieved by querying the
1248 content table as follows:
1249
1250 </p><div class="codeblock"><pre>SELECT &lt;content_rowid&gt;, &lt;cols&gt; FROM &lt;content&gt; WHERE &lt;content_rowid&gt; = ?;
1251 </pre></div>
1252
1253 <p>The values retrieved are then passed to the table's configured tokenizer,
1254 which extracts from them the list of tokens that need to be removed from the
1255 FTS index. If the retrieved values do not match the set of entries in the
1256 FTS index for the row, then some tokens may be left in the FTS index, leading
1257 to inconsistent query results later on.
1258
1259 </p><p>In practice, this means that to delete a row from both the content table and
1260 the FTS5 external content table, the FTS5 table must be updated first (so that
1261 the content table row is still available to it).
1262
1263 </p><p>In FTS5, an UPDATE statement is implemented as a DELETE followed by an
1264 INSERT of the new values. It follows that when UPDATEing a row in both the
1265 content table and FTS5 external content table, the FTS5 table must be
1266 updated first (again, so that the content table row is available to it).
1267
1268 </p><a name="the_columnsize_option"></a>
1269 <h2 tags="FTS5 columnsize option" id="the_columnsize_option"><span>4.5. </span>The Columnsize Option</h2>
1270
1271 <p>Normally, FTS5 maintains a special backing table within the database that
1272 stores the size of each column value in tokens inserted into the main FTS5
1273 table in a separate table. This backing table is used by the
1274 <a href="#xColumnSize">xColumnSize</a> API function, which is in turn used by
1275 the built-in <a href="fts5.html#the_bm25_function">bm25 ranking function</a> (and is likely to be useful
1276 to other ranking functions as well).
1277
1278 </p><p>In order to save space, this backing table may be omitted by setting the
1279 columnsize option to zero. For example:
1280
1281 </p><div class="codeblock"><pre><i>-- A table without the xColumnSize() values stored on disk:</i>
1282 CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=0);
1283
1284 <i>-- Three equivalent ways of creating a table that does store the</i>
1285 <i>-- xColumnSize() values on disk:</i>
1286 CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
1287 CREATE VIRTUAL TABLE ft USING fts5(a, b, c, columnsize=1);
1288 CREATE VIRTUAL TABLE ft USING fts5(a, b, columnsize='1', c);
1289 </pre></div>
1290
1291 <p> It is an error to set the columnsize option to any value other than
1292 0 or 1.
1293
1294 </p><p> If an FTS5 table is configured with columnsize=0 but is not a
1295 <a href="fts5.html#contentless_tables">contentless table</a>, the xColumnSize API function
1296 still works, but runs much more slowly. In this case, instead of reading
1297 the value to return directly from the database, it reads the text value
1298 itself and count the tokens within it on demand.
1299
1300 </p><p>Or, if the table is also a <a href="fts5.html#contentless_tables">contentless table</a>,
1301 then the following apply:
1302
1303 </p><ul>
1304 <li> <p>The xColumnSize API always returns -1. There is no way to determine
1305 the number of tokens in a value stored within a contentless FTS5 table
1306 configured with columnsize=0.
1307
1308 </p></li><li> <p>Each inserted row must be accompanied by an explicitly specified rowid
1309 value. If a contentless table is configured with columnsize=0,
1310 attempting to insert a NULL value into the rowid is an SQLITE_MISMATCH
1311 error.
1312
1313 </p></li><li> <p>All queries on the table must be full-text queries. In other words,
1314 they must use the MATCH or = operator with the table-name column as the
1315 left-hand operand, or else use the table-valued function syntax. Any
1316 query that is not a full-text query results in an error.
1317 </p></li></ul>
1318
1319 <p> The name of the table in which the xColumnSize values are stored
1320 (unless columnsize=0 is specified) is "&lt;name&gt;_docsize", where
1321 &lt;name&gt; is the name of the FTS5 table itself. The
1322 <a href="https://sqlite.org/download.html">sqlite3_analyzer</a>
1323 tool may be used on an existing database in order to determine how much
1324 space might be saved by recreating an FTS5 table using columnsize=0.
1325
1326 </p><a name="the_detail_option"></a>
1327 <h2 tags="FTS5 detail option" id="the_detail_option"><span>4.6. </span>The Detail Option</h2>
1328
1329 <p> For each term in a document, the FTS index maintained by FTS5
1330 stores the rowid of the document, the column number of the column that contains
1331 the term and the offset of the term within the column value. The "detail"
1332 option may be used to omit some of this information. This reduces the space
1333 that the index consumes within the database file, but also reduces the
1334 capability and efficiency of the system.
1335
1336 </p><p> The detail option may be set to "full" (the default value), "column" or
1337 "none". For example:
1338
1339 </p><div class="codeblock"><pre><i>-- The following two lines are equivalent (because the default value</i>
1340 <i>-- of "detail" is "full". </i>
1341 CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
1342 CREATE VIRTUAL TABLE ft USING fts5(a, b, c, detail=full);
1343
1344 CREATE VIRTUAL TABLE ft USING fts5(a, b, c, detail=column);
1345 CREATE VIRTUAL TABLE ft USING fts5(a, b, c, detail=none);
1346 </pre></div>
1347
1348 <p>If the detail option is set to <b>column</b>, then for each term the FTS
1349 index records the rowid and column number only, omitting the term offset
1350 information. This results in the following restrictions:
1351
1352 </p><ul>
1353 <li> NEAR queries are not available.
1354 </li><li> Phrase queries are not available.
1355 </li><li> Assuming the table is not also a
1356 <a href="fts5.html#contentless_tables">contentless table</a>, the
1357 <a href="#xInstCount">xInstCount</a>, <a href="#xInst">xInst</a>,
1358 <a href="#xPhraseFirst">xPhraseFirst</a> and <a href="#xPhraseNext">xPhraseNext</a>
1359 are slower than usual. This is because instead of reading the required data
1360 directly from the FTS index they have to load and tokenize the document text
1361 on demand.
1362 </li><li> If the table is also a contentless table, the xInstCount, xInst,
1363 xPhraseFirst and xPhraseNext APIs behave as if the current row contains no
1364 phrase matches at all (i.e. xInstCount() returns 0).
1365 </li></ul>
1366
1367 <p>If the detail option is set to <b>none</b>, then for each term the FTS
1368 index records just the rowid is stored. Both column and offset information
1369 are omitted. As well as the restrictions itemized above for detail=column
1370 mode, this imposes the following extra limitations:
1371
1372 </p><ul>
1373 <li> Column filter queries are not available.
1374 </li><li> Assuming the table is not also a contentless table, the
1375 <a href="#xPhraseFirstColumn">xPhraseFirstColumn</a> and
1376 <a href="#xPhraseNextColumn">xPhraseNextColumn</a> are slower than usual.
1377
1378 </li><li> If the table is also a contentless table, the xPhraseFirstColumn and
1379 xPhraseNextColumn APIs behave as if the current row contains no phrase
1380 matches at all (i.e. xPhraseFirstColumn() sets the iterator to EOF).
1381 </li></ul>
1382
1383 <p> In one test that indexed a large set of emails (1636 MiB on disk), the FTS
1384 index was 743 MiB on disk with detail=full, 340 MiB with detail=column and 134
1385 MiB with detail=none.
1386
1387 </p><a name="the_tokendata_option"></a>
1388 <h2 tags="FTS5 tokendata option" id="the_tokendata_option"><span>4.7. </span>The Tokendata Option</h2>
1389
1390 <p>This option is only useful to applications that implement
1391 <a href="#custom_tokenizers">custom tokenizers</a>. Usually, tokenizers may
1392 return tokens that consist of any sequence of bytes, including 0x00 bytes.
1393 However, if the table specifies the tokendata=1 option, then fts5 ignores
1394 the first 0x00 byte and any trailing data in the token for the purposes
1395 of matching. It still stores the entire token as returned by the tokenizer,
1396 but the remainder is ignored by the fts5 core.
1397
1398 </p><p>The full version of the token, including any 0x00 byte and trailing data,
1399 is available to <a href="#custom_auxiliary_functions">custom auxiliary
1400 functions</a> via the <a href="#xQueryToken">xQueryToken</a> and
1401 <a href="#xInstToken">xInstToken</a> APIs.
1402
1403 </p><p>This may be useful for ranking functions. A custom tokenizer may
1404 add extra data to some document tokens allowing a ranking function to give
1405 more weight to hits of some tokens (e.g. those in document headings).
1406
1407 </p><p>The combination of a custom tokenizer and a custom auxiliary function may be
1408 used to implement
1409 <a href="https://www.unicode.org/reports/tr10/tr10-41.html#Asymmetric_Search">
1410 asymmetric search</a>. The tokenizer could (say) for each document token return
1411 the case-normalized and unmarked version of the token, followed by an 0x00
1412 byte, followed by the full text of the token from the document. When queried,
1413 fts5 would provide results as if all characters in the query were
1414 case-normalized and unmarked. The custom auxiliary function could then be used
1415 in the WHERE clause of the query to filter out any rows that do not match based
1416 on secondary or tertiary markings in the document or query terms.
1417
1418 </p><a name="the_locale_option"></a>
1419 <h2 tags="FTS5 locale option" id="the_locale_option"><span>4.8. </span>The Locale Option</h2>
1420
1421 <p>This option is only useful to applications that implement
1422 <a href="#custom_tokenizers">custom tokenizers</a>. If an fts5 table is created
1423 with the "locale=1" option specified, then the fts5_locale() SQL function may
1424 be used to associate a locale value (e.g. "th_TH" or "en_US") with strings
1425 passed to FTS5. FTS5 itself does not use locale values, but makes them
1426 available to the tokenizer implementation whenever the string is tokenized.
1427 The tokenizer may then adjust its behavior based on the locale.
1428
1429 </p><div class="codeblock"><pre><i>-- The following statement creates an fts5 table with locale support.
1430 -- The "tokenizer=..." option below must be replaced with a real tokenizer
1431 -- specification for a tokenizer that supports locales.</i>
1432 CREATE VIRTUAL TABLE ft USING fts5(a, b, locale=1, tokenizer=...);
1433
1434 <i>-- This statement inserts a row into the table. The value inserted into
1435 -- column "a" uses locale "th_TH", the value written to column "b" uses the
1436 -- tokenizer's default locale</i>
1437 INSERT INTO ft(a, b) VALUES(
1438 fts5_locale('th_TH', 'Tokenize this in Thai locale'),
1439 'Tokenize this in the default locale.'
1440 );
1441
1442 <i>-- The "en_US" locale is used to tokenize the query terms in the
1443 --following query.</i>
1444 SELECT * FROM ft( fts5_locale('en_US', 'query terms') );
1445 </pre></div>
1446
1447 <p>Attempting to pass an fts5_locale() string to an fts5 table that was not
1448 created with the locale=1 option is an error.
1449
1450 </p><p>When an fts5_locale() string is stored in a normal content table (i.e. not a
1451 contentless or external content table), the attached locale is stored along
1452 with it. If the string is tokenized by FTS5 again, for example because its
1453 row is being deleted or as part of an auxiliary function evaluation, the
1454 attached locale is again passed to the tokenizer implementation.
1455
1456 </p><p>In order to support locales, an FTS5 external-content table may use an SQL
1457 view that returns fts5_locale() values as the content table. For example:
1458
1459 </p><div class="codeblock"><pre><i>-- Each row of this table contains a string and its locale.</i>
1460 CREATE TABLE t1(val, locale);
1461 INSERT INTO t1 VALUES('a text value', 'en_US');
1462
1463 <i>-- A view to combine the string and locale from table t1.</i>
1464 CREATE VIEW v1 AS SELECT rowid, fts5_locale(val, locale) AS val FROM t1;
1465
1466 <i>-- An FTS5 table to read locale-enabled strings from view v1.</i>
1467 CREATE VIRTUAL TABLE ft USING fts5(val, locale=1, content=v1, tokenize=...);
1468 </pre></div>
1469
1470 <p>If an fts5_locale() value is written to an UNINDEXED column of an fts5
1471 table, the locale value is discarded and the string stored by itself.
1472
1473 </p><p>The <a href="fts5.html#the_fts5_get_locale_function">fts5_get_locale()</a> function may be used to retreive
1474 the locale of a value stored in an FTS5 table.
1475
1476 </p><a name="the_contentless_unindexed_option"></a>
1477 <h2 tags="FTS5 contentless_unindexed option" id="the_contentless_unindexed_option"><span>4.9. </span>The Contentless-Unindexed Option</h2>
1478
1479 <p>Usually, <a href="#the_unindexed_column_option">UNINDEXED</a> columns
1480 belonging to <a href="#contentless_tables">contentless tables</a> are not very
1481 useful. Values written to them are not indexed or stored, and reading from such
1482 an UNINDEXED column always returns NULL. However, if the
1483 "contentless_unindexed=1" option is specified on a contentless table, then the
1484 values of UNINDEXED columns are stored persistently, even though values written
1485 to other columns are not.
1486
1487 </p><div class="codeblock"><pre><i>-- Create a contentless table with the contentless_unindexed=1 option.
1488 -- Of the row written to it, the value 'one' will be indexed and then
1489 -- discarded, and the value "1" will be stored but not indexed.</i>
1490 CREATE VIRTUAL TABLE ft USING fts5(a, b UNINDEXED, content='', contentless_unindexed=1);
1491 INSERT INTO ft(a, b) VALUES('one', 1);
1492
1493 <i>-- This query returns 1 row with 2 columns - (NULL, 1). Reading from
1494 -- column "a" is always NULL, as the table is contentless. But reading from
1495 -- "b" returns the value, as the table uses contentless_unindexed=1.</i>
1496 SELECT a, b FROM ft('one');
1497 </pre></div>
1498
1499 <p>It is an error to specify contentless_unindexed=1 for an fts5 table that is
1500 not a contentless or contentless-delete table.
1501
1502 </p><a name="_auxiliary_functions_"></a>
1503 <h1 tags="FTS5 auxiliary functions" id="_auxiliary_functions_"><span>5. </span> Auxiliary Functions </h1>
1504
1505 <p> Auxiliary functions are similar to <a href="lang_corefunc.html">SQL scalar functions</a>,
1506 except that they may only be used within full-text queries (those that use
1507 the MATCH operator, or LIKE/GLOB with the trigram tokenizer) on an FTS5 table.
1508 Their results are calculated based not only on the arguments passed to them,
1509 but also on the current match and matched row. For example, an auxiliary
1510 function may return a numeric value indicating the accuracy of the match (see
1511 the <a href="fts5.html#the_bm25_function">bm25()</a> function), or a fragment of text from the matched row
1512 that contains one or more instances of the search terms (see the <a href="fts5.html#the_snippet_function">snippet()</a> function).
1513
1514 </p><p>To invoke an auxiliary function, the name of the FTS5 table should be
1515 specified as the first argument. Other arguments may follow the first,
1516 depending on the specific auxiliary function being invoked. For example, to
1517 invoke the "highlight" function:
1518
1519 </p><div class="codeblock"><pre><i>-- Assuming fts5 table:</i>
1520 CREATE VIRTUAL TABLE ft USING fts5(a, b, c);
1521
1522 <i>-- Invoke the highlight() function:</i>
1523 SELECT highlight(ft, 2, '&lt;b&gt;', '&lt;/b&gt;') FROM ft WHERE ft MATCH 'fts5'
1524 </pre></div>
1525
1526 <p>The built-in auxiliary functions provided as part of FTS5 are described in
1527 the following section. Applications may also implement
1528 <a href="fts5.html#custom_auxiliary_functions">custom auxiliary functions in C</a>.
1529
1530 </p><h2 id="built_in_auxiliary_functions"><span>5.1. </span>Built-in Auxiliary Functions</h2>
1531
1532 <p> FTS5 provides three built-in auxiliary functions:
1533
1534 </p><ul>
1535 <li> The <a href="fts5.html#the_bm25_function">bm25() auxiliary function</a> returns a real value
1536 reflecting the accuracy of the current match. Better matches are
1537 assigned numerically lower values.
1538
1539 </li><li> The <a href="fts5.html#the_highlight_function">highlight() auxiliary function</a> returns a copy
1540 of the text from one of the columns of the current match with each
1541 instance of a queried term within the result surrounded by specified
1542 markup (for example "&lt;b&gt;" and "&lt;/b&gt;").
1543
1544 </li><li> The <a href="fts5.html#the_snippet_function">snippet() auxiliary function</a> selects a short
1545 fragment of text from one of the columns of the matched row and returns
1546 it with each instance of a queried term surrounded by markup in
1547 the same manner as the highlight() function. The fragment of text is
1548 selected so as to maximize the number of distinct queried terms it
1549 contains. Higher weight is given to snippets that occur at the start
1550 of a column value, or that immediately follow "." or ":" characters
1551 in the text.
1552
1553 </li><li> The <a href="fts5.html#the_fts5_get_locale_function">fts5_get_locale() auxiliary function</a> is used
1554 to retrieve the locale, if any, associated with a value stored in
1555 an FTS5 table.
1556 </li></ul>
1557
1558 <a name="the_bm25_function"></a>
1559 <h3 tags="FTS5 bm25" id="the_bm25_function"><span>5.1.1. </span>The bm25() function</h3>
1560
1561 <p> The built-in auxiliary function bm25() returns a real value indicating
1562 how well the current row matches the full-text query. The better the match,
1563 the numerically smaller the value returned. A query such as the following may
1564 be used to return matches in order from best to worst match:
1565
1566 </p><div class="codeblock"><pre>SELECT * FROM ft WHERE ft MATCH ? ORDER BY bm25(ft)
1567 </pre></div>
1568
1569 <p> In order to calculate a documents score, the full-text query is separated
1570 into its component phrases. The bm25 score for document <i>D</i> and
1571 query <i>Q</i> is then calculated as follows:
1572
1573 </p><p> <img src="images/fts5_formula1.png" style="width:75ex;margin-left:5ex">
1574
1575 </p><p> In the above, <i>nPhrase</i> is the number of phrases in the query.
1576 <i>|D|</i> is the number of tokens in the current document, and
1577 <i>avgdl</i> is the average number of tokens in all documents within the
1578 FTS5 table. <i>k<sub>1</sub></i> and <i>b</i> are both constants,
1579 hard-coded at 1.2 and 0.75 respectively.
1580
1581 </p><p> The "-1" term at the start of the formula is not found in most
1582 implementations of the BM25 algorithm. Without it, a better match is assigned
1583 a numerically higher BM25 score. Since the default sorting order is
1584 "ascending", this means that appending "ORDER BY bm25(ft)" to a query would
1585 cause results to be returned in order from worst to best. The "DESC" keyword
1586 would be required in order to return the best matches first. In order to
1587 avoid this pitfall, the FTS5 implementation of BM25 multiplies the result
1588 by -1 before returning it, ensuring that better matches are assigned
1589 numerically lower scores.
1590
1591 </p><p> <i>IDF(q<sub>i</sub>)</i> is the inverse-document-frequency of query
1592 phrase <i>i</i>. It is calculated as follows, where <i>N</i> is the total
1593 number of rows in the FTS5 table and <i>n(q<sub>i</sub>)</i> is the total
1594 number of rows that contain at least one instance of phrase <i>i</i>:
1595
1596 </p><p> <img src="images/fts5_formula2.png" style="width:75ex;margin-left:5ex">
1597
1598 </p><p> Finally, <i>f(q<sub>i</sub>,D)</i> is the phrase frequency of phrase
1599 <i>i</i>. By default, this is simply the number of occurrences of the phrase
1600 within the current row. However, by passing extra real value arguments to
1601 the bm25() SQL function, each column of the table may be assigned a different
1602 weight and the phrase frequency calculated as follows:
1603
1604 </p><p> <img src="images/fts5_formula3.png" style="width:75ex;margin-left:5ex">
1605
1606 </p><p> where <i>w<sub>c</sub></i> is the weight assigned to column <i>c</i> and
1607 <i>n(q<sub>i</sub>,c)</i> is the number of occurrences of phrase <i>i</i> in
1608 column <i>c</i> of the current row. The first argument passed to bm25()
1609 following the table name is the weight assigned to the leftmost column of
1610 the FTS5 table. The second is the weight assigned to the second leftmost
1611 column, and so on. If there are not enough arguments for all table columns,
1612 remaining columns are assigned a weight of 1.0. If there are too many
1613 trailing arguments, the extras are ignored. For example:
1614
1615 </p><div class="codeblock"><pre><i>-- Assuming the following schema:</i>
1616 CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
1617
1618 <i>-- Return results in bm25 order, with each phrase hit in the "sender"</i>
1619 <i>-- column considered the equal of 10 hits in the "body" column, and</i>
1620 <i>-- each hit in the "title" column considered as valuable as 5 hits in</i>
1621 <i>-- the "body" column.</i>
1622 SELECT * FROM email WHERE email MATCH ? ORDER BY bm25(email, 10.0, 5.0);
1623 </pre></div>
1624
1625 <p>Refer to wikipedia for
1626 <a href="https://en.wikipedia.org/wiki/Okapi_BM25">more information regarding
1627 BM25</a> and its variants.
1628
1629 </p><a name="the_highlight_function"></a>
1630 <h3 tags="FTS5 highlight" id="the_highlight_function"><span>5.1.2. </span>The highlight() function</h3>
1631
1632 <p> The highlight() function returns a copy of the text from a specified
1633 column of the current row with extra markup text inserted to mark the start
1634 and end of phrase matches.
1635
1636 </p><p>The highlight() must be invoked with exactly three arguments following
1637 the table name. To be interpreted as follows:
1638
1639 </p><ol>
1640 <li> An integer indicating the index of the FTS table column to read the
1641 text from. Columns are numbered from left to right starting at zero.
1642
1643 </li><li> The text to insert before each phrase match.
1644
1645 </li><li> The text to insert after each phrase match.
1646 </li></ol>
1647
1648 <p>For example:
1649
1650 </p><div class="codeblock"><pre><i>-- Return a copy of the text from the leftmost column of the current</i>
1651 <i>-- row, with phrase matches marked using html "b" tags.</i>
1652 SELECT highlight(ft, 0, '&lt;b&gt;', '&lt;/b&gt;') FROM ft WHERE ft MATCH ?
1653 </pre></div>
1654
1655 <p>In cases where two or more phrase instances overlap (share one or more
1656 tokens in common), a single open and close marker is inserted for each set
1657 of overlapping phrases. For example:
1658
1659 </p><div class="codeblock"><pre><i>-- Assuming this:</i>
1660 CREATE VIRTUAL TABLE ft USING fts5(a);
1661 INSERT INTO ft VALUES('a b c x c d e');
1662 INSERT INTO ft VALUES('a b c c d e');
1663 INSERT INTO ft VALUES('a b c d e');
1664
1665 <i>-- The following SELECT statement returns these three rows:</i>
1666 <i>-- '&#91;a b c&#93; x &#91;c d e&#93;'</i>
1667 <i>-- '&#91;a b c&#93; &#91;c d e&#93;'</i>
1668 <i>-- '&#91;a b c d e&#93;'</i>
1669 SELECT highlight(ft, 0, '&#91;', '&#93;') FROM ft WHERE ft MATCH 'a+b+c AND c+d+e';
1670 </pre></div>
1671
1672 <a name="the_snippet_function"></a>
1673 <h3 tags="FTS5 snippet" id="the_snippet_function"><span>5.1.3. </span>The snippet() function</h3>
1674
1675 <p>The snippet() function is similar to highlight(), except that instead of
1676 returning entire column values, it automatically selects and extracts a
1677 short fragment of document text to process and return. The snippet() function
1678 must be passed five parameters following the table name argument:
1679
1680 </p><ol>
1681 <li> An integer indicating the index of the FTS table column to select
1682 the returned text from. Columns are numbered from left to right
1683 starting at zero. A negative value indicates that the column should
1684 be automatically selected.
1685
1686 </li><li> The text to insert before each phrase match within the returned text.
1687
1688 </li><li> The text to insert after each phrase match within the returned text.
1689
1690 </li><li> The text to add to the start or end of the selected text to indicate
1691 that the returned text does not occur at the start or end of its column,
1692 respectively.
1693
1694 </li><li> The maximum number of tokens in the returned text. This must be greater
1695 than zero and equal to or less than 64.
1696 </li></ol>
1697
1698 <a name="the_fts5_get_locale_function"></a>
1699 <h3 tags="FTS5 get_locale" id="the_fts5_get_locale_function"><span>5.1.4. </span>The fts5_get_locale() function</h3>
1700
1701 <p>The fts5_get_locale() function is used to retrieve the locale, if any,
1702 associated with a value stored in an FTS5 table. It accepts a single argument
1703 following the table name, the index of the column of the current row to query.
1704 Columns are numbered in the order they appeared in the CREATE VIRTUAL TABLE
1705 statement starting from 0.
1706
1707 </p><p>If the FTS5 table does not support locales (i.e. was not created with
1708 the <a href="#the_locale_option">locale=1</a> option), or if there is no
1709 locale associated with the nominated value, then this function returns
1710 NULL. Otherwise it returns a text value, the name of the locale that the
1711 value in question is associated with.
1712
1713 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(a, b, c, locale=1);
1714 INSERT INTO ft VALUES(
1715 'no locale',
1716 fts5_locale('th_TH', 'Thai locale'),
1717 fts5_locale('en_US', 'US locale')
1718 );
1719
1720 <i>-- The following statement returns a single row containing three values:
1721 -- NULL, text value 'th_TH', and text value 'en_US'.</i>
1722 SELECT
1723 fts5_get_locale(ft, 0),
1724 fts5_get_locale(ft, 1),
1725 fts5_get_locale(ft, 2)
1726 FROM ft;
1727 </pre></div>
1728
1729 <a name="the_fts5_insttoken_function"></a>
1730 <h3 tags="fts5_insttoken" id="the_fts5_insttoken_function"><span>5.1.5. </span>The fts5_insttoken() function</h3>
1731
1732 <p> The fts5_insttoken() function may be used to mark a query as requiring the
1733 extra data for the <a href="#xInstToken">xInstToken</a> API to work efficiently
1734 with prefix queries. Refer to the linked documentation for xInstToken for
1735 details. It is used as follows:
1736
1737 </p><div class="codeblock"><pre><i>-- Use the fts5_insttoken() function to ensure that if the custom API
1738 -- function used by this query invokes the xInstToken() API on a token
1739 -- matched by the prefix 'quer*' , it does not have to make a second pass
1740 -- of the index to gather the required data.</i>
1741 SELECT custom_api_function(ft) FROM ft( fts5_insttoken('prefix quer*') );
1742
1743 <i> -- This query returns the same results, but may be less efficient if
1744 -- the custom function invokes the xInstToken() API as described above. </i>
1745 SELECT custom_api_function(ft) FROM ft( 'prefix quer*' );
1746 </pre></div>
1747
1748 <a name="sorting_by_auxiliary_function_results"></a>
1749 <h2 tags="auxiliary function mapping" id="sorting_by_auxiliary_function_results"><span>5.2. </span>Sorting by Auxiliary Function Results</h2>
1750
1751 <p> All FTS5 tables feature a special hidden column named "rank". If the
1752 current query is not a full-text query (i.e. if it does not include a MATCH
1753 operator), the value of the "rank" column is always NULL. Otherwise, in a
1754 full-text query, column rank contains by default the same value as would be
1755 returned by executing the bm25() auxiliary function with no trailing
1756 arguments.
1757
1758 </p><p> The difference between reading from the rank column and using the bm25()
1759 function directly within the query is only significant when sorting by the
1760 returned value. In this case, using "rank" is faster than using bm25().
1761
1762 </p><div class="codeblock"><pre><i>-- The following queries are logically equivalent. But the second may</i>
1763 <i>-- be faster, particularly if the caller abandons the query before</i>
1764 <i>-- all rows have been returned (or if the queries were modified to </i>
1765 <i>-- include LIMIT clauses).</i>
1766 SELECT * FROM ft WHERE ft MATCH ? ORDER BY bm25(ft);
1767 SELECT * FROM ft WHERE ft MATCH ? ORDER BY rank;
1768 </pre></div>
1769
1770 <p> Instead of using bm25() with no trailing arguments, the specific auxiliary
1771 function mapped to the rank column may be configured either on a per-query
1772 basis, or by setting a different persistent default for the FTS table.
1773
1774 </p><p> In order to change the mapping of the rank column for a single query,
1775 a term similar to either of the following is added to the WHERE clause of a
1776 query:
1777
1778 </p><div class="codeblock"><pre>rank MATCH 'auxiliary-function-name(arg1, arg2, ...)'
1779 rank = 'auxiliary-function-name(arg1, arg2, ...)'
1780 </pre></div>
1781
1782 <p> The right-hand-side of the MATCH or = operator must be a constant
1783 expression that evaluates to a string consisting of the auxiliary function to
1784 invoke, followed by zero or more comma separated arguments within parentheses.
1785 Arguments must be SQL literals. For example:
1786
1787 </p><div class="codeblock"><pre><i>-- The following queries are logically equivalent. But the second may</i>
1788 <i>-- be faster. See above. </i>
1789 SELECT * FROM ft WHERE ft MATCH ? ORDER BY bm25(ft, 10.0, 5.0);
1790 SELECT * FROM ft WHERE ft MATCH ? AND rank MATCH 'bm25(10.0, 5.0)' ORDER BY rank;
1791 </pre></div>
1792
1793 <p> The table-valued function syntax may also be used to specify an alternative
1794 ranking function. In this case the text describing the ranking function should
1795 be specified as the second table-valued function argument. The following three
1796 queries are equivalent:
1797
1798 </p><div class="codeblock"><pre>SELECT * FROM ft WHERE ft MATCH ? AND rank MATCH 'bm25(10.0, 5.0)' ORDER BY rank;
1799 SELECT * FROM ft WHERE ft = ? AND rank = 'bm25(10.0, 5.0)' ORDER BY rank;
1800 SELECT * FROM ft WHERE ft(?, 'bm25(10.0, 5.0)') ORDER BY rank;
1801 </pre></div>
1802
1803 <p> The default mapping of the rank column for a table may be modified
1804 using the <a href="fts5.html#the_rank_configuration_option">FTS5 rank configuration option</a>.
1805
1806 </p><h1 id="special_insert_commands"><span>6. </span>Special INSERT Commands</h1>
1807
1808 <a name="the_automerge_configuration_option"></a>
1809 <h2 tags="FTS5 automerge option" id="the_automerge_configuration_option"><span>6.1. </span>The 'automerge' Configuration Option</h2>
1810
1811 <p>
1812 Instead of using a single data structure on disk to store the full-text
1813 index, FTS5 uses a series of b-trees. Each time a new transaction is
1814 committed, a new b-tree containing the contents of the committed transaction
1815 is written into the database file. When the full-text index is queried, each
1816 b-tree must be queried individually and the results merged before being
1817 returned to the user.
1818
1819 </p><p>
1820 In order to prevent the number of b-trees in the database from becoming too
1821 large (slowing down queries), smaller b-trees are periodically merged into
1822 single larger b-trees containing the same data. By default, this happens
1823 automatically within INSERT, UPDATE or DELETE statements that modify the
1824 full-text index. The 'automerge' parameter determines how many smaller
1825 b-trees are merged together at a time. Setting it to a small value can
1826 speed up queries (as they have to query and merge the results from fewer
1827 b-trees), but can also slow down writing to the database (as each INSERT,
1828 UPDATE or DELETE statement has to do more work as part of the automatic
1829 merging process).
1830
1831 </p><p>
1832 Each of the b-trees that make up the full-text index is assigned to a "level"
1833 based on its size. Level-0 b-trees are the smallest, as they contain the
1834 contents of a single transaction. Higher level b-trees are the result of
1835 merging two or more level-0 b-trees together and so they are larger. FTS5
1836 begins to merge b-trees together once there exist <i>M</i> or more b-trees
1837 with the same level, where <i>M</i> is the value of the 'automerge'
1838 parameter.
1839
1840 </p><p>
1841 The maximum allowed value for the 'automerge' parameter is 16. The default
1842 value is 4. Setting the 'automerge' parameter to 0 disables the automatic
1843 incremental merging of b-trees altogether.
1844
1845 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('automerge', 8);
1846 </pre></div>
1847
1848 <h2 id="the_crisismerge_configuration_option"><span>6.2. </span>The 'crisismerge' Configuration Option</h2>
1849
1850 <p>The 'crisismerge' option is similar to 'automerge', in that it determines
1851 how and how often the component b-trees that make up the full-text index are
1852 merged together. Once there exist <i>C</i> or more b-trees on a single level
1853 within the full-text index, where <i>C</i> is the value of the 'crisismerge'
1854 option, all b-trees on the level are immediately merged into a single b-tree.
1855
1856 </p><p>The difference between this option and the 'automerge' option is that when
1857 the 'automerge' limit is reached FTS5 only begins to merge the b-trees
1858 together. Most of the work is performed as part of subsequent INSERT,
1859 UPDATE or DELETE operations. Whereas when the 'crisismerge' limit is reached,
1860 the offending b-trees are all merged immediately. This means that an INSERT,
1861 UPDATE or DELETE that triggers a crisis-merge may take a long time to
1862 complete.
1863
1864 </p><p>The default 'crisismerge' value is 16. There is no maximum limit. Attempting
1865 to set the 'crisismerge' parameter to a value of 0 or 1 is equivalent to
1866 setting it to the default value (16). It is an error to attempt to set the
1867 'crisismerge' option to a negative value.
1868
1869 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('crisismerge', 16);
1870 </pre></div>
1871
1872 <a name="the_delete_command"></a>
1873 <h2 tags="FTS5 delete command" id="the_delete_command"><span>6.3. </span>The 'delete' Command</h2>
1874
1875 <p> This command is only available with <a href="fts5.html#external_content_tables">external content</a> and <a href="fts5.html#contentless_tables">contentless</a> tables. It
1876 is used to delete the index entries associated with a single row from the
1877 full-text index. This command and the <a href="fts5.html#the_delete_all_command">delete-all</a>
1878 command are the only ways to remove entries from the full-text index of a
1879 contentless table.
1880
1881 </p><p> In order to use this command to delete a row, the text value 'delete'
1882 must be inserted into the special column with the same name as the table.
1883 The rowid of the row to delete is inserted into the rowid column. The
1884 values inserted into the other columns must match the values currently
1885 stored in the table. For example:
1886
1887 </p><div class="codeblock"><pre><i>-- Insert a row with rowid=14 into the fts5 table.</i>
1888 INSERT INTO ft(rowid, a, b, c) VALUES(14, $a, $b, $c);
1889
1890 <i>-- Remove the same row from the fts5 table.</i>
1891 INSERT INTO ft(ft, rowid, a, b, c) VALUES('delete', 14, $a, $b, $c);
1892 </pre></div>
1893
1894 <p> If the values "inserted" into the text columns as part of a 'delete'
1895 command are not the same as those currently stored within the table, the
1896 results may be unpredictable.
1897
1898 </p><p> The reason for this is easy to understand: When a document is inserted
1899 into the FTS5 table, an entry is added to the full-text index to record the
1900 position of each token within the new document. When a document is removed,
1901 the original data is required in order to determine the set of entries that
1902 need to be removed from the full-text index. So if the data supplied to FTS5
1903 when a row is deleted using this command is different from that used to
1904 determine the set of token instances when it was inserted, some full-text
1905 index entries may not be correctly deleted, or FTS5 may try to remove index
1906 entries that do not exist. This can leave the full-text index in an
1907 unpredictable state, making future query results unreliable.
1908
1909 </p><a name="the_delete_all_command"></a>
1910 <h2 tags="FTS5 delete-all command" id="the_delete_all_command"><span>6.4. </span>The 'delete-all' Command</h2>
1911
1912 <p> This command is only available with <a href="fts5.html#external_content_tables">external content</a> and <a href="fts5.html#contentless_tables">contentless</a> tables (including
1913 <a href="fts5.html#contentless_delete_tables">contentless-delete</a> tables). It deletes all
1914 entries from the full-text index.
1915
1916 </p><div class="codeblock"><pre>INSERT INTO ft(ft) VALUES('delete-all');
1917 </pre></div>
1918
1919 <a name="the_deletemerge_configuration_option"></a>
1920 <h2 tags="deletemerge" id="the_deletemerge_configuration_option"><span>6.5. </span>The 'deletemerge' Configuration Option</h2>
1921
1922 <p> The 'deletemerge' option is only used by
1923 <a href="fts5.html#contentless_delete_tables">contentless-delete</a> tables.
1924
1925 </p><p> When a row is deleted from a contentless-delete table, the entries
1926 associated with its tokens are not immediately removed from the FTS index.
1927 Instead, a "tombstone" marker containing the rowid of the deleted row is
1928 attached to the b-tree that contains the row's FTS index entries. When the
1929 b-tree is queried, any query result rows for which there exist tombstone
1930 markers are omitted from the results. When the b-tree is merged with other
1931 b-trees, both the deleted rows and their tombstone markers are discarded.
1932
1933 </p><p> This option specifies a minimum percentage of rows in a b-tree that must
1934 have tombstone markers before the b-tree is made eligible for merging -
1935 either by <a href="#the_automerge_configuration_option">automatic</a> merges or
1936 explicit user <a href="the_merge_command">'merge'</a> commands - even if it
1937 does not meet the usual criteria as determined by the 'automerge' and
1938 <a href="#the_usermerge_configuration_option">'usermerge'</a> options.
1939
1940 </p><p> For example, to specify that FTS5 should consider merging a component
1941 b-tree after 15% of its rows have associated tombstone markers:
1942
1943 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('deletemerge', 15);
1944 </pre></div>
1945
1946 <p> The default value of this option is 10. Attempting to set it to less than
1947 zero restores the default value. Setting this option to 0 or to greater than
1948 100 ensures that b-trees are never made eligible for merging due to tombstone
1949 markers.
1950
1951 </p><a name="the_insttoken_configuration_option"></a>
1952 <h2 tags="FTS5 insttoken" id="the_insttoken_configuration_option"><span>6.6. </span>The 'insttoken' Configuration Option</h2>
1953
1954 <p> This boolean option is 0 by default. If set to 1, then FTS5 collects
1955 the extra data required by the <a href="#xInstToken">xInstToken</a> API for
1956 all prefix queries made against the table. Refer to the linked documentation
1957 for xInstToken for details.
1958
1959 </p><div class="codeblock"><pre><i>-- Enable collection of extra xInstToken data for prefix queries.</i>
1960 INSERT INTO ft(ft, rank) VALUES('insttoken', 1);
1961
1962 <i>-- Disable collection of extra xInstToken data for prefix queries.</i>
1963 INSERT INTO ft(ft, rank) VALUES('insttoken', 0);
1964 </pre></div>
1965
1966 <h2 id="the_integrity_check_command"><span>6.7. </span>The 'integrity-check' Command</h2>
1967
1968 <p> This command is used to verify that the full-text index is internally
1969 consistent, and, optionally, that it is consistent with any
1970 <a href="fts5.html#external_content_tables">external content</a> table.
1971
1972 </p><p>The integrity-check command is invoked by inserting the text value
1973 'integrity-check' into the special column with the same name as the FTS5
1974 table. If a value is supplied for the "rank" column, it must be either
1975 0 or 1. For example:
1976
1977 </p><div class="codeblock"><pre>INSERT INTO ft(ft) VALUES('integrity-check');
1978 INSERT INTO ft(ft, rank) VALUES('integrity-check', 0);
1979 INSERT INTO ft(ft, rank) VALUES('integrity-check', 1);
1980 </pre></div>
1981
1982 <p>The three forms above are equivalent for all FTS tables that are
1983 not external content tables. They check that the index data structures are
1984 not corrupt, and, if the FTS table is not contentless, that the contents of
1985 the index match the contents of the table itself.
1986
1987 </p><p>For an external content table, the contents of the index are only
1988 compared to the contents of the external content table if the value
1989 specified for the rank column is 1.
1990
1991 </p><p>In all cases, if any discrepancies are found, the command fails
1992 with an <a href="rescode.html#corrupt_vtab">SQLITE_CORRUPT_VTAB</a> error.
1993
1994 </p><a name="the_merge_command"></a>
1995 <h2 tags="FTS5 merge command" id="the_merge_command"><span>6.8. </span>The 'merge' Command</h2>
1996
1997 <div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('merge', 500);
1998 </pre></div>
1999
2000 <p> This command merges b-tree structures together until roughly N pages
2001 of merged data have been written to the database, where N is the absolute
2002 value of the parameter specified as part of the 'merge' command. The size of
2003 each page is as configured by the <a href="fts5.html#the_pgsz_configuration_option">FTS5 pgsz option</a>.
2004
2005 </p><p> If the parameter is a positive value, B-tree structures are only eligible
2006 for merging if one of the following is true:
2007
2008 </p><ul>
2009 <li> There are U or more such b-trees on a
2010 single level (see the documentation for the <a href="fts5.html#the_automerge_configuration_option">FTS5 automerge option</a>
2011 for an explanation of b-tree levels), where U is the value assigned
2012 to the <a href="fts5.html#the_usermerge_configuration_option">FTS5 usermerge option</a> option.
2013 </li><li> A merge has already been started (perhaps by a 'merge' command that
2014 specified a negative parameter).
2015 </li></ul>
2016
2017 <p> It is possible to tell whether or not the 'merge' command found any
2018 b-trees to merge together by checking the value returned by the
2019 <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> API before and after the command is executed. If
2020 the difference between the two values is 2 or greater, then work was performed.
2021 If the difference is less than 2, then the 'merge' command was a no-op. In this
2022 case there is no reason to execute the same 'merge' command again, at least
2023 until after the FTS table is next updated.
2024
2025 </p><p> If the parameter is negative, and there are B-tree structures on more than
2026 one level within the FTS index, all B-tree structures are assigned to the same
2027 level before the merge operation is commenced. Additionally, if the parameter
2028 is negative, the value of the usermerge configuration option is not
2029 respected - as few as two b-trees from the same level may be merged together.
2030
2031 </p><p> The above means that executing the 'merge' command with a negative
2032 parameter until the before and after difference in the return value of
2033 <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> is less than two optimizes the FTS index in the
2034 same way as the <a href="fts5.html#the_optimize_command">FTS5 optimize command</a>. However, if a new b-tree is added
2035 to the FTS index while this process is ongoing, FTS5 will move the new
2036 b-tree to the same level as the existing b-trees and restart the merge. To
2037 avoid this, only the first call to 'merge' should specify a negative parameter.
2038 Each subsequent call to 'merge' should specify a positive value so that the
2039 merge started by the first call is run to completion even if new b-trees are
2040 added to the FTS index.
2041
2042 </p><a name="the_optimize_command"></a>
2043 <h2 tags="FTS5 optimize command" id="the_optimize_command"><span>6.9. </span>The 'optimize' Command</h2>
2044
2045 <p>This command merges all individual b-trees that currently make up the
2046 full-text index into a single large b-tree structure. This ensures that the
2047 full-text index consumes the minimum space within the database and is in the
2048 fastest form to query.
2049
2050 </p><p>Refer to the documentation for the <a href="fts5.html#the_automerge_configuration_option">FTS5 automerge option</a> for more details
2051 regarding the relationship between the full-text index and its component
2052 b-trees.
2053
2054 </p><div class="codeblock"><pre>INSERT INTO ft(ft) VALUES('optimize');
2055 </pre></div>
2056
2057 <p>Because it reorganizes the entire FTS index, the optimize command can
2058 take a long time to run. The <a href="fts5.html#the_merge_command">FTS5 merge command</a> can be used to divide
2059 the work of optimizing the FTS index into multiple steps. To do this:
2060
2061 </p><ul>
2062 <li> Invoke the 'merge' command once with the parameter set to -N, then
2063 </li><li> Invoke the 'merge' command zero or more times with the parameter set to N.
2064 </li></ul>
2065
2066 <p>where N is the number of pages of data to merge within each invocation of
2067 the merge command. The application should stop invoking merge when the
2068 difference in the value returned by the sqlite3_total_changes() function before
2069 and after the merge command drops to below two. The merge commands may be
2070 issued as part of the same or separate transactions, and by the same or
2071 different database clients. Refer to the documentation for the
2072 <a href="fts5.html#the_merge_command">merge command</a> for further details.
2073
2074 </p><a name="the_pgsz_configuration_option"></a>
2075 <h2 tags="FTS5 pgsz option" id="the_pgsz_configuration_option"><span>6.10. </span>The 'pgsz' Configuration Option</h2>
2076
2077 <p> This command is used to set the persistent "pgsz" option.
2078
2079 </p><p> The full-text index maintained by FTS5 is stored as a series of fixed-size
2080 blobs in a database table. It is not strictly necessary for all blobs that make
2081 up a full-text index to be the same size. The pgsz option determines the size
2082 of all blobs created by subsequent index writers. The default value is 4050.
2083
2084 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('pgsz', 4072);
2085 </pre></div>
2086
2087 <a name="the_rank_configuration_option"></a>
2088 <h2 tags="FTS5 rank configuration option" id="the_rank_configuration_option"><span>6.11. </span>The 'rank' Configuration Option</h2>
2089
2090 <p> This command is used to set the persistent "rank" option.
2091
2092 </p><p> The rank option is used to change the default auxiliary function mapping
2093 for the rank column. The option should be set to a text value in the same
2094 format as described for <a href="fts5.html#sorting_by_auxiliary_function_results">"rank MATCH ?"</a> terms
2095 above. For example:
2096
2097 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('rank', 'bm25(10.0, 5.0)');
2098 </pre></div>
2099
2100 <a name="the_rebuild_command"></a>
2101 <h2 tags="FTS5 rebuild command" id="the_rebuild_command"><span>6.12. </span>The 'rebuild' Command</h2>
2102
2103 <p> This command first deletes the entire full-text index, then rebuilds it
2104 based on the contents of the table or <a href="fts5.html#external_content_tables">content
2105 table</a>. It is not available with <a href="fts5.html#contentless_tables">contentless
2106 tables</a>.
2107
2108 </p><div class="codeblock"><pre>INSERT INTO ft(ft) VALUES('rebuild');
2109 </pre></div>
2110
2111 <a name="the_secure_delete_configuration_option"></a>
2112 <h2 tags="FTS5 secure-delete command" id="the_secure_delete_configuration_option"><span>6.13. </span>The 'secure-delete' Configuration Option</h2>
2113
2114 <p> This command is used to set the persistent boolean "secure-delete" option.
2115 For example:
2116
2117 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('secure-delete', 1);
2118 </pre></div>
2119
2120 <p> Normally, when an entry in an fts5 table is updated or deleted, instead
2121 of removing entries from the full-text index, delete-keys are added to the <a href="#the_automerge_configuration_option">new b-tree</a> created by the
2122 transaction. This is efficient, but it means that the old full-text index
2123 entries remain in the database file until they are eventually removed
2124 by merge operations on the full-text index. Anyone with access to the
2125 database can use these entries to trivially reconstruct the contents of
2126 deleted FTS5 table rows. However, if the 'secure-delete' option is set
2127 to 1, then full-text entries are actually removed from the database when
2128 existing FTS5 table rows are updated or deleted. This is slower, but
2129 it prevents old full-text entries from being used to reconstruct deleted
2130 table rows.
2131
2132 </p><p> This option ensures that old full-text entries are not available to
2133 attackers with SQL access to the database. To also ensure that they may
2134 not be recovered by attackers with access to the SQLite database file
2135 itself, the application must also enable the SQLite core secure-delete
2136 option with a command like <a href="pragma.html#pragma_secure_delete">
2137 "PRAGMA secure_delete = 1"</a>.
2138
2139 </p><p> <b>Warning:</b> Once one or more table rows have been updated or
2140 deleted with this option set, the FTS5 table may no longer be read or
2141 written by any version of FTS5 earlier than 3.42.0 (the first version
2142 in which this option was available). Attempting to do so results in
2143 an error, with an error message like "invalid fts5 file format (found 5,
2144 expected 4) - run 'rebuild'". The FTS5 file format may be reverted, so
2145 that it may be read by earlier versions of FTS5, by running the
2146 <a href="#the_rebuild_command">'rebuild' command</a> on the table using
2147 version 3.42.0 or later.
2148
2149 </p><p>The default value of the secure-delete option is 0.
2150
2151 </p><a name="the_usermerge_configuration_option"></a>
2152 <h2 tags="FTS5 usermerge option" id="the_usermerge_configuration_option"><span>6.14. </span>The 'usermerge' Configuration Option</h2>
2153
2154 <p> This command is used to set the persistent "usermerge" option.
2155
2156 </p><p> The usermerge option is similar to the automerge and crisismerge options.
2157 It is the minimum number of b-tree segments that will be merged together by
2158 a 'merge' command with a positive parameter. For example:
2159
2160 </p><div class="codeblock"><pre>INSERT INTO ft(ft, rank) VALUES('usermerge', 4);
2161 </pre></div>
2162
2163 <p> The default value of the usermerge option is 4. The minimum allowed value
2164 is 2, and the maximum 16.
2165
2166 </p><a name="extending_fts5"></a>
2167 <h1 tags="Extending FTS5" id="extending_fts5"><span>7. </span>Extending FTS5</h1>
2168
2169 <p>FTS5 features APIs allowing it to be extended by:
2170
2171 </p><ul>
2172 <li> Adding new auxiliary functions implemented in C, and
2173 </li><li> Adding new tokenizers, also implemented in C.
2174 </li></ul>
2175
2176 <p> The built-in tokenizers and auxiliary functions described in this
2177 document are all implemented using the publicly available API described
2178 below.
2179
2180 </p><p> Before a new auxiliary function or tokenizer implementation may be
2181 registered with FTS5, an application must obtain a pointer to the "fts5_api"
2182 structure. There is one fts5_api structure for each database connection with
2183 which the FTS5 extension is registered. To obtain the pointer, the application
2184 invokes the SQL user-defined function fts5() with a single argument. That
2185 argument must be set to a pointer to a pointer to an fts5_api object
2186 using the <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> interface.
2187 The following example code demonstrates the technique:
2188
2189 </p><div class="codeblock"><pre><i>/*
2190 ** Return a pointer to the fts5_api pointer for database connection db.
2191 ** If an error occurs, return NULL and leave an error in the database
2192 ** handle (accessible using sqlite3_errcode()/errmsg()).
2193 */</i>
2194 fts5_api *fts5_api_from_db(sqlite3 *db){
2195 fts5_api *pRet = 0;
2196 sqlite3_stmt *pStmt = 0;
2197
2198 if( SQLITE_OK==sqlite3_prepare(db, "SELECT fts5(?1)", -1, &pStmt, 0) ){
2199 sqlite3_bind_pointer(pStmt, 1, (void*)&pRet, "fts5_api_ptr", NULL);
2200 sqlite3_step(pStmt);
2201 }
2202 sqlite3_finalize(pStmt);
2203 return pRet;
2204 }
2205 </pre></div>
2206
2207 <p><b>Backwards Compatibility Warning:</b>
2208 Prior to SQLite version 3.20.0 (2017-08-01), the fts5() worked slightly
2209 differently. Older applications that extend FTS5 must be revised to use
2210 the new technique shown above.
2211
2212 </p><p> The fts5_api structure is defined as follows. It exposes five methods:
2213
2214 </p><ul>
2215 <li> xCreateTokenizer() and xCreateTokenizer_v2(), for registering new
2216 custom tokenizer implementations.
2217 </li><li> xFindTokenizer() and xFindTokenizer_v2(), for retrieving existing
2218 tokenizer implementations. This can be useful for implementing
2219 "tokenizer wrappers", similar to the built-in porter tokenizer.
2220 </li><li> xCreateFunction(), for registering new auxiliary function
2221 implementations.
2222 </li></ul>
2223
2224 <p>The two "v2" methods above are only available if the fts5_api.iVersion
2225 field is set to 3 or greater. Attempting to access the "v2" APIs via an
2226 fts5_api object with a lower value for iVersion results in undefined behavior.
2227
2228 </p><div class="codeblock"><pre>typedef struct fts5_api fts5_api;
2229 struct fts5_api {
2230 int iVersion; <i>/* Currently always set to 3 */</i>
2231
2232 <i>/* Create a new tokenizer */</i>
2233 int (*xCreateTokenizer)(
2234 fts5_api *pApi,
2235 const char *zName,
2236 void *pUserData,
2237 fts5_tokenizer *pTokenizer,
2238 void (*xDestroy)(void*)
2239 );
2240
2241 <i>/* Find an existing tokenizer */</i>
2242 int (*xFindTokenizer)(
2243 fts5_api *pApi,
2244 const char *zName,
2245 void **ppUserData,
2246 fts5_tokenizer *pTokenizer
2247 );
2248
2249 <i>/* Create a new auxiliary function */</i>
2250 int (*xCreateFunction)(
2251 fts5_api *pApi,
2252 const char *zName,
2253 void *pUserData,
2254 fts5_extension_function xFunction,
2255 void (*xDestroy)(void*)
2256 );
2257
2258 <i>/* APIs below this point are only available if iVersion>=3 */</i>
2259
2260 <i>/* Create a new tokenizer */</i>
2261 int (*xCreateTokenizer_v2)(
2262 fts5_api *pApi,
2263 const char *zName,
2264 void *pUserData,
2265 fts5_tokenizer_v2 *pTokenizer,
2266 void (*xDestroy)(void*)
2267 );
2268
2269 <i>/* Find an existing tokenizer */</i>
2270 int (*xFindTokenizer_v2)(
2271 fts5_api *pApi,
2272 const char *zName,
2273 void **ppUserData,
2274 fts5_tokenizer_v2 **ppTokenizer
2275 );
2276 };
2277 </pre></div>
2278
2279 <p> To invoke a method of the fts5_api object, the fts5_api pointer itself
2280 should be passed as the methods first argument followed by the other, method
2281 specific, arguments. For example:
2282
2283 </p><div class="codeblock"><pre>rc = pFts5Api->xCreateTokenizer(pFts5Api, ... other args ...);
2284 </pre></div>
2285
2286 <p> The fts5_api structure methods are described individually in the following
2287 sections.
2288
2289 </p><a name="custom_tokenizers"></a>
2290 <h2 tags="custom tokenizers" id="custom_tokenizers"><span>7.1. </span>Custom Tokenizers</h2>
2291
2292 <p> To create a custom tokenizer, an application must implement three
2293 functions: a tokenizer constructor (xCreate), a destructor (xDelete) and a
2294 function to do the actual tokenization (xTokenize). The type of each
2295 function is as for the member variables of the fts5_tokenizer_v2 struct:
2296
2297 </p><div class="codeblock"><pre>typedef struct Fts5Tokenizer Fts5Tokenizer;
2298 typedef struct fts5_tokenizer_v2 fts5_tokenizer_v2;
2299 struct fts5_tokenizer_v2 {
2300 int iVersion; <i>/* Currently always 2 */</i>
2301
2302 int (*xCreate)(void*, const char **azArg, int nArg, Fts5Tokenizer **ppOut);
2303 void (*xDelete)(Fts5Tokenizer*);
2304 int (*xTokenize)(Fts5Tokenizer*,
2305 void *pCtx,
2306 int flags, <i>/* Mask of FTS5_TOKENIZE_* flags */</i>
2307 const char *pText, int nText,
2308 const char *pLocale, int nLocale,
2309 int (*xToken)(
2310 void *pCtx, <i>/* Copy of 2nd argument to xTokenize() */</i>
2311 int tflags, <i>/* Mask of FTS5_TOKEN_* flags */</i>
2312 const char *pToken, <i>/* Pointer to buffer containing token */</i>
2313 int nToken, <i>/* Size of token in bytes */</i>
2314 int iStart, <i>/* Byte offset of token within input text */</i>
2315 int iEnd <i>/* Byte offset of end of token within input text */</i>
2316 )
2317 );
2318 };
2319
2320 <i>/* Flags that may be passed as the third argument to xTokenize() */</i>
2321 #define FTS5_TOKENIZE_QUERY 0x0001
2322 #define FTS5_TOKENIZE_PREFIX 0x0002
2323 #define FTS5_TOKENIZE_DOCUMENT 0x0004
2324 #define FTS5_TOKENIZE_AUX 0x0008
2325
2326 <i>/* Flags that may be passed by the tokenizer implementation back to FTS5
2327 ** as the third argument to the supplied xToken callback. */</i>
2328 #define FTS5_TOKEN_COLOCATED 0x0001 <i>/* Same position as prev. token */</i>
2329 </pre></div>
2330
2331 <p> The implementation is registered with the FTS5 module by populating an
2332 instance of the fts5_tokenizer_v2 struct and passing a pointer to it to the
2333 xCreateTokenizer_v2() method of the fts5_api object. If there is already a
2334 tokenizer with the same name, it is replaced. If a non-NULL xDestroy parameter
2335 is passed to xCreateTokenizer(), it is invoked with a copy of the pUserData
2336 pointer passed as the only argument when the database handle is closed or when
2337 the tokenizer is replaced.
2338
2339 </p><p> If successful, xCreateTokenizer() returns SQLITE_OK. Otherwise, it
2340 returns an SQLite error code. In this case the xDestroy function is <b>not</b>
2341 invoked.
2342
2343 </p><p> When an FTS5 table uses the custom tokenizer, the FTS5 core calls xCreate()
2344 once to create a tokenizer, then xTokenize() zero or more times to tokenize
2345 strings, then xDelete() to free any resources allocated by xCreate(). More
2346 specifically:
2347
2348 </p><dl>
2349 <dt><b>xCreate:</b></dt><dd><p style="margin-top:0">
2350 This function is used to allocate and initialize a tokenizer instance.
2351 A tokenizer instance is required to actually tokenize text.
2352 </p><p>
2353 The first argument passed to this function is a copy of the (void*)
2354 pointer provided by the application when the fts5_tokenizer_v2 object
2355 was registered with FTS5 (the third argument to xCreateTokenizer()).
2356 The second and third arguments are an array of nul-terminated strings
2357 containing the tokenizer arguments, if any, specified following the
2358 tokenizer name as part of the CREATE VIRTUAL TABLE statement used
2359 to create the FTS5 table.
2360 </p><p>
2361 The final argument is an output variable. If successful, (*ppOut)
2362 should be set to point to the new tokenizer handle and SQLITE_OK
2363 returned. If an error occurs, some value other than SQLITE_OK should
2364 be returned. In this case, fts5 assumes that the final value of *ppOut
2365 is undefined.
2366 </p><p>
2367 </p></dd><dt><b> xDelete:</b></dt><dd><p style="margin-top:0">
2368 This function is invoked to delete a tokenizer handle previously
2369 allocated using xCreate(). Fts5 guarantees that this function will
2370 be invoked exactly once for each successful call to xCreate().
2371 </p><p>
2372 </p></dd><dt><b> xTokenize:</b></dt><dd><p style="margin-top:0">
2373 This function is expected to tokenize the nText byte string indicated
2374 by argument pText. pText may or may not be nul-terminated. The first
2375 argument passed to this function is a pointer to an Fts5Tokenizer object
2376 returned by an earlier call to xCreate().
2377 </p><p>
2378 The third argument indicates the reason that FTS5 is requesting
2379 tokenization of the supplied text. This is always one of the following
2380 four values:
2381 </p><p>
2382 </p><ul><li> <b>FTS5_TOKENIZE_DOCUMENT</b> - A document is being inserted into
2383 or removed from the FTS table. The tokenizer is being invoked to
2384 determine the set of tokens to add to (or delete from) the
2385 FTS index.
2386 <p>
2387 </p></li><li> <b>FTS5_TOKENIZE_QUERY</b> - A MATCH query is being executed
2388 against the FTS index. The tokenizer is being called to tokenize
2389 a bareword or quoted string specified as part of the query.
2390 <p>
2391 </p></li><li> <b>(FTS5_TOKENIZE_QUERY | FTS5_TOKENIZE_PREFIX)</b> - Same as
2392 FTS5_TOKENIZE_QUERY, except that the bareword or quoted string is
2393 followed by a "*" character, indicating that the last token
2394 returned by the tokenizer will be treated as a token prefix.
2395 <p>
2396 </p></li><li> <b>FTS5_TOKENIZE_AUX</b> - The tokenizer is being invoked to
2397 satisfy an fts5_api.xTokenize() request made by an auxiliary
2398 function. Or an fts5_api.xColumnSize() request made by the same
2399 on a columnsize=0 database.
2400 </li></ul>
2401 <p>
2402 The sixth and seventh arguments passed to xTokenize() - pLocale and
2403 nLocale - are a pointer to a buffer containing the locale to use for
2404 tokenization (e.g. "en_US") and its size in bytes, respectively. The
2405 pLocale buffer is not nul-terminated. pLocale may be passed NULL (in
2406 which case nLocale is always 0) to indicate that the tokenizer should
2407 use its default locale.
2408 </p><p>
2409 For each token in the input string, the supplied callback xToken() must
2410 be invoked. The first argument to it should be a copy of the pointer
2411 passed as the second argument to xTokenize(). The third and fourth
2412 arguments are a pointer to a buffer containing the token text, and the
2413 size of the token in bytes. The 4th and 5th arguments are the byte offsets
2414 of the first byte of and first byte immediately following the text from
2415 which the token is derived within the input.
2416 </p><p>
2417 The second argument passed to the xToken() callback ("tflags") should
2418 normally be set to 0. The exception is if the tokenizer supports
2419 synonyms. In this case see the discussion below for details.
2420 </p><p>
2421 FTS5 assumes the xToken() callback is invoked for each token in the
2422 order that they occur within the input text.
2423 </p><p>
2424 If an xToken() callback returns any value other than SQLITE_OK, then
2425 the tokenization should be abandoned and the xTokenize() method should
2426 immediately return a copy of the xToken() return value. Or, if the
2427 input buffer is exhausted, xTokenize() should return SQLITE_OK. Finally,
2428 if an error occurs with the xTokenize() implementation itself, it
2429 may abandon the tokenization and return any error code other than
2430 SQLITE_OK or SQLITE_DONE.
2431 </p><p>
2432 If the tokenizer is registered using an fts5_tokenizer_v2 object,
2433 then the xTokenize() method has two additional arguments - pLocale
2434 and nLocale. These specify the locale that the tokenizer should use
2435 for the current request. If pLocale and nLocale are both 0, then the
2436 tokenizer should use its default locale. Otherwise, pLocale points to
2437 an nLocale byte buffer containing the name of the locale to use as utf-8
2438 text. pLocale is not nul-terminated.
2439 </p><p>
2440 </p></dd></dl><p>
2441 </p><p>
2442 There is also an fts5_tokenizer object. This is an older, deprecated,
2443 version of fts5_tokenizer_v2. It is similar except that:
2444 </p><p>
2445 </p><ul>
2446 <li> There is no "iVersion" field, and
2447 </li><li> The xTokenize() method does not take a locale argument.
2448 </li></ul>
2449 <p>
2450 Legacy fts5_tokenizer tokenizers must be registered using the
2451 legacy xCreateTokenizer() function, instead of xCreateTokenizer_v2().
2452 </p><p>
2453 Tokenizer implementations registered using either API may be retrieved
2454 using both xFindTokenizer() and xFindTokenizer_v2().
2455 </p><p>
2456 </p><h3 id="synonym_support"><span>7.1.1. </span>Synonym Support</h3>
2457 <p>
2458 Custom tokenizers may also support synonyms. Consider a case in which a
2459 user wishes to query for a phrase such as "first place". Using the
2460 built-in tokenizers, the FTS5 query 'first + place' will match instances
2461 of "first place" within the document set, but not alternative forms
2462 such as "1st place". In some applications, it would be better to match
2463 all instances of "first place" or "1st place" regardless of which form
2464 the user specified in the MATCH query text.
2465 </p><p>
2466 There are several ways to approach this in FTS5:
2467 </p><p>
2468 </p><ol><li> By mapping all synonyms to a single token. In this case, using
2469 the above example, this means that the tokenizer returns the
2470 same token for inputs "first" and "1st". Say that token is in
2471 fact "first", so that when the user inserts the document "I won
2472 1st place" entries are added to the index for tokens "i", "won",
2473 "first" and "place". If the user then queries for '1st + place',
2474 the tokenizer substitutes "first" for "1st" and the query works
2475 as expected.
2476 <p>
2477 </p></li><li> By querying the index for all synonyms of each query term
2478 separately. In this case, when tokenizing query text, the
2479 tokenizer may provide multiple synonyms for a single term
2480 within the document. FTS5 then queries the index for each
2481 synonym individually. For example, faced with the query:
2482 <p>
2483 </p><div class="codeblock"><pre>... MATCH 'first place'
2484 </pre></div>
2485 <p>
2486 the tokenizer offers both "1st" and "first" as synonyms for the
2487 first token in the MATCH query and FTS5 effectively runs a query
2488 similar to:
2489 </p><p>
2490 </p><div class="codeblock"><pre>... MATCH '(first OR 1st) place'
2491 </pre></div>
2492 <p>
2493 except that, for the purposes of auxiliary functions, the query
2494 still appears to contain just two phrases - "(first OR 1st)"
2495 being treated as a single phrase.
2496 </p><p>
2497 </p></li><li> By adding multiple synonyms for a single term to the FTS index.
2498 Using this method, when tokenizing document text, the tokenizer
2499 provides multiple synonyms for each token. So that when a
2500 document such as "I won first place" is tokenized, entries are
2501 added to the FTS index for "i", "won", "first", "1st" and
2502 "place".
2503 <p>
2504 This way, even if the tokenizer does not provide synonyms
2505 when tokenizing query text (it should not - to do so would be
2506 inefficient), it doesn't matter if the user queries for
2507 'first + place' or '1st + place', as there are entries in the
2508 FTS index corresponding to both forms of the first token.
2509 </p></li></ol>
2510 <p>
2511 Whether it is parsing document or query text, any call to xToken that
2512 specifies a <i>tflags</i> argument with the FTS5_TOKEN_COLOCATED bit
2513 is considered to supply a synonym for the previous token. For example,
2514 when parsing the document "I won first place", a tokenizer that supports
2515 synonyms would call xToken() 5 times, as follows:
2516 </p><p>
2517 </p><div class="codeblock"><pre>xToken(pCtx, 0, "i", 1, 0, 1);
2518 xToken(pCtx, 0, "won", 3, 2, 5);
2519 xToken(pCtx, 0, "first", 5, 6, 11);
2520 xToken(pCtx, FTS5_TOKEN_COLOCATED, "1st", 3, 6, 11);
2521 xToken(pCtx, 0, "place", 5, 12, 17);
2522 </pre></div>
2523 <p>
2524 It is an error to specify the FTS5_TOKEN_COLOCATED flag the first time
2525 xToken() is called. Multiple synonyms may be specified for a single token
2526 by making multiple calls to xToken(FTS5_TOKEN_COLOCATED) in sequence.
2527 There is no limit to the number of synonyms that may be provided for a
2528 single token.
2529 </p><p>
2530 In many cases, method (1) above is the best approach. It does not add
2531 extra data to the FTS index or require FTS5 to query for multiple terms,
2532 so it is efficient in terms of disk space and query speed. However, it
2533 does not support prefix queries very well. If, as suggested above, the
2534 token "first" is substituted for "1st" by the tokenizer, then the query:
2535 </p><p>
2536 </p><div class="codeblock"><pre>... MATCH '1s*'
2537 </pre></div>
2538 <p>
2539 will not match documents that contain the token "1st" (as the tokenizer
2540 will probably not map "1s" to any prefix of "first").
2541 </p><p>
2542 For full prefix support, method (3) may be preferred. In this case,
2543 because the index contains entries for both "first" and "1st", prefix
2544 queries such as 'fi*' or '1s*' will match correctly. However, because
2545 extra entries are added to the FTS index, this method uses more space
2546 within the database.
2547 </p><p>
2548 Method (2) offers a midpoint between (1) and (3). Using this method,
2549 a query such as '1s*' will match documents that contain the literal
2550 token "1st", but not "first" (assuming the tokenizer is not able to
2551 provide synonyms for prefixes). However, a non-prefix query like '1st'
2552 will match against "1st" and "first". This method does not require
2553 extra disk space, as no extra entries are added to the FTS index.
2554 On the other hand, it may require more CPU cycles to run MATCH queries,
2555 as separate queries of the FTS index are required for each synonym.
2556 </p><p>
2557 When using methods (2) or (3), it is important that the tokenizer only
2558 provide synonyms when tokenizing document text (method (3)) or query
2559 text (method (2)), not both. Doing so will not cause any errors, but is
2560 inefficient.
2561
2562
2563
2564 </p><a name="custom_auxiliary_functions"></a>
2565 <h2 tags="FTS5 custom auxiliary functions" id="custom_auxiliary_functions"><span>7.2. </span>Custom Auxiliary Functions</h2>
2566
2567 <p> Implementing a custom auxiliary function is similar to implementing a
2568 <a href="appfunc.html">scalar SQL function</a>. The implementation
2569 should be a C function of type fts5_extension_function, defined as follows:
2570
2571 </p><div class="codeblock"><pre>typedef struct Fts5ExtensionApi Fts5ExtensionApi;
2572 typedef struct Fts5Context Fts5Context;
2573 typedef struct Fts5PhraseIter Fts5PhraseIter;
2574
2575 typedef void (*fts5_extension_function)(
2576 const Fts5ExtensionApi *pApi, <i>/* API offered by current FTS version */</i>
2577 Fts5Context *pFts, <i>/* First arg to pass to pApi functions */</i>
2578 sqlite3_context *pCtx, <i>/* Context for returning result/error */</i>
2579 int nVal, <i>/* Number of values in apVal[] array */</i>
2580 sqlite3_value **apVal <i>/* Array of trailing arguments */</i>
2581 );
2582 </pre></div>
2583
2584 <p> The implementation is registered with the FTS5 module by calling the
2585 xCreateFunction() method of the fts5_api object. If there is already an
2586 auxiliary function with the same name, it is replaced by the new function.
2587 If a non-NULL xDestroy parameter is passed to xCreateFunction(), it is invoked
2588 with a copy of the pUserData pointer passed as the only argument when the
2589 database handle is closed or when the registered auxiliary function is
2590 replaced.
2591
2592 </p><p> If successful, xCreateFunction() returns SQLITE_OK. Otherwise, it
2593 returns an SQLite error code. In this case the xDestroy function is <b>not</b>
2594 invoked.
2595
2596 </p><p> The final three arguments passed to the auxiliary function callback
2597 (pCtx, nVal and apVal above) are similar to the three arguments passed to the
2598 implementation of a scalar SQL function. The apVal&#91;&#93; array contains all
2599 SQL arguments except the first passed to the auxiliary function. The
2600 implementation should return a result or error via the content handle pCtx.
2601
2602 </p><p> The first argument passed to an auxiliary function callback is a pointer
2603 to a structure (pApi above) containing methods that may be invoked
2604 in order to obtain information regarding the current query or row. The second
2605 argument is an opaque handle (pFts above) that should be passed as the
2606 first argument to any such method invocation. For example, the following
2607 auxiliary function returns the total number of tokens in all columns of the
2608 current row:
2609
2610 </p><div class="codeblock"><pre><i>/*
2611 ** Implementation of an auxiliary function that returns the number
2612 ** of tokens in the current row (including all columns).
2613 */</i>
2614 static void column_size_imp(
2615 const Fts5ExtensionApi *pApi,
2616 Fts5Context *pFts,
2617 sqlite3_context *pCtx,
2618 int nVal,
2619 sqlite3_value **apVal
2620 ){
2621 int rc;
2622 int nToken;
2623 rc = pApi->xColumnSize(pFts, -1, &nToken);
2624 if( rc==SQLITE_OK ){
2625 sqlite3_result_int(pCtx, nToken);
2626 }else{
2627 sqlite3_result_error_code(pCtx, rc);
2628 }
2629 }
2630 </pre></div>
2631
2632 <p>The following section describes the API offered to auxiliary function
2633 implementations in detail. Further examples may be found in the "fts5_aux.c"
2634 file of the source code.
2635
2636 </p><a name="custom_auxiliary_functions_api_overview"></a>
2637 <h3 tags="custom auxiliary overview" id="custom_auxiliary_functions_api_overview"><span>7.2.1. </span>Custom Auxiliary Functions API Overview</h3>
2638
2639 <p>This section provides an overview of the capabilities of the auxiliary
2640 function API. It does not describe every function. Refer to the <a href="#custom_auxiliary_functions_api_reference">reference text</a> below for a
2641 complete description.
2642
2643 </p><p>When invoked, an auxiliary function implementation has access to APIs that
2644 allow it to query FTS5 for various information. Some of these APIs return
2645 information relating to the current row of the FTS5 table being visited,
2646 some relating to the entire set of rows that will be visited by the FTS5
2647 query, and some relating to the FTS5 table. Given an FTS5 table populated as
2648 follows:
2649
2650 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft USING fts5(a, b);
2651 INSERT INTO ft(rowid, a, b) VALUES
2652 (1, 'ab cd', 'cd de one'),
2653 (2, 'de fg', 'fg gh'),
2654 (3, 'gh ij', 'ij ab three four');
2655 </pre></div>
2656
2657 <p>and the query:
2658
2659 </p><div class="codeblock"><pre>SELECT my_aux_function(ft) FROM ft('ab')
2660 </pre></div>
2661
2662 <p>then the custom auxiliary function will be invoked for rows 1 and 3 (all
2663 rows that contain the token "ab" and therefore match the query).
2664
2665 </p><p><b>Number of rows/columns in table: xRowCount, xColumnCount
2666
2667 </b></p><p>The system may be queried for the total number of rows in the FTS5 table
2668 using the <a href="#xRowCount">xRowCount</a> API. This provides the total number
2669 of rows in the table, not the number that match the current query.
2670
2671 </p><p>Table columns are numbered from left to right starting from 0. The
2672 "rowid" column does not count - only user declared columns - so in the example
2673 above column "a" is column 0 and column "b" is column 1. From within an
2674 auxiliary function implementation, the <a href="#xColumnCount">xColumnCount</a>
2675 API may be used to determine how many columns the table being queried has. If
2676 the xColumnCount() API is invoked from within the implementation of the
2677 auxiliary function my_aux_function in the example above, it returns 2.
2678
2679 </p><p><b>Data From Current Row: xColumnText, xRowid
2680
2681 </b></p><p>The <a href="#xRowid">xRowid</a> API may be used to find the rowid value
2682 for the current row. The <a href="#xColumnText">xColumnText</a> may be used
2683 to obtain the text stored in a specified column of the current row.
2684
2685 </p><p><b>Token Counts: xColumnSize, xColumnTotalSize
2686
2687 </b></p><p>FTS5 divides documents inserted into an fts5 table into tokens. These are
2688 usually just words, perhaps folded to either upper or lower case and with any
2689 punctuation removed. For example, the default
2690 <a href="#unicode61_tokenizer">unicode61 tokenizer</a> tokenizes the text "The
2691 tokenizer is case-insensitive" to a list of 5 tokens - "the", "tokenizer", is",
2692 "case" and "insensitive". Exactly how tokens are extracted from text is
2693 determined by the <a href="#tokenizers">tokenizer</a>.
2694
2695 </p><p>The auxiliary functions API provides functions to query for both the number
2696 of tokens in a specified column of the current row (the
2697 <a href="#xColumnSize">xColumnSize</a> API), or for the number of tokens in a
2698 specified column of all rows of the table (the <a href="#xColumnTotalSize">xColumnTotalSize</a> API). For the example at the
2699 top of this section, when visiting row 1, xColumnSize returns 2 for column 0
2700 and 3 for column 1. xColumnTotalSize returns 6 for column 0 and 9 for column 1
2701 regardless of the current row.
2702
2703 </p><p><b>The Current Full-Text Query: xPhraseCount, xPhraseSize, xQueryToken
2704
2705 </b></p><p>An FTS5 query contains one or more <a href="#fts5_phrases">phrases</a>. The
2706 <a href="#xPhraseCount">xPhraseCount</a>, <a href="#xPhraseSize">xPhraseSize</a>
2707 and <a href="#xQueryToken">xQueryToken</a> APIs allow an auxiliary function
2708 implementation to query the system for details of the current query. The
2709 xPhraseCount API returns the number of phrases in the current query. For
2710 example, if an FTS5 table is queried as follows:
2711
2712 </p><div class="codeblock"><pre>SELECT my_aux_function(ft) FROM ft('ab AND "cd ef gh" OR ij + kl')
2713 </pre></div>
2714
2715 <p>and the xPhraseCount() API invoked from within the implementation of the
2716 auxiliary function, it returns 3 (the three phrases being "ab", "ce ef gh" and
2717 "ij kl").
2718
2719 </p><p>Phrases are numbered in order of appearance within a query starting from 0.
2720 The xPhraseSize() API may be used to query for the number of tokens in a
2721 specified phrase of the query. In the example above, phrase 0 contains 1 token,
2722 phrase 1 contains 3 tokens, and phrase 2 contains 2.
2723
2724 </p><p>The xQueryToken API may be used to access the text of a specified token
2725 within a specified phrase of the query. Tokens are numbered within their
2726 phrases from left to right starting from 0. For example, if the xQueryToken
2727 API is used to request token 1 of phrase 2 in the example above, it returns
2728 the text "kl". Token 0 of phrase 0 is "ab".
2729
2730 </p><p><b>Phrase Hits in the Current Row: xPhraseFirst, xPhraseNext
2731
2732 </b></p><p>These two API functions may be used to iterate through the matches for
2733 a specified phrase of the query within the current row. Phrase matches are
2734 identified by the column and token offset within the current row. For
2735 example, say the following example table:
2736
2737 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE ft2 USING fts5(x, y);
2738 INSERT INTO ft2(rowid, x, y) VALUES
2739 (1, 'xxx one two xxx five xxx six', 'seven four'),
2740 (2, 'five four four xxx six', 'three four five six four five six');
2741 </pre></div>
2742
2743 <p>is queried with:
2744
2745 </p><div class="codeblock"><pre>SELECT my_aux_function(ft2) FROM ft2(
2746 '("one two" OR "three") AND y:four NEAR(five six, 2)'
2747 );
2748 </pre></div>
2749
2750 <p>The query above contains 5 phrases - "one two", "three", "four",
2751 "five" and "six". It matches all rows of the table, so the auxiliary
2752 function is invoked for each row.
2753
2754 </p><p>In row 1, for phrase 0, "one two", there is exactly one match to iterate
2755 through - at column 0 token offset 1. The column number is 0 because the
2756 match appears in the left most column. The token offset is 1 because there
2757 is exactly one token ("xxx") before the phrase match in the column value.
2758 For phrase 1, "three", there are no matches. Phrase 2, "four", has one
2759 match, at column 1, token offset 0. Phrase 3, "five", has one match at
2760 column 0, token offset 4, and phrase 4, "six", has one match at column 0
2761 token offset 6.
2762
2763 </p><p>The set of matches for each phrase in each row of the example is presented
2764 in the table below. Each match is notated as (column-number, token-offset):
2765
2766 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
2767 <tr style="text-align:left"><th>Row</th><th>Phrase 0</th><th>Phrase 1</th><th>Phrase 2</th><th>Phrase 3</th><th>Phrase 4
2768 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>1</td><td>(0, 1) </td><td></td><td>(1, 1)</td><td>(0, 4)</td><td>(0, 6)
2769 </td></tr><tr style="text-align:left"><td>2</td><td></td><td>(1,0)</td><td>(1, 1), (1,4)</td><td>(1, 2), (1, 5)</td><td>(1, 3), (1, 6)
2770 </td></tr></table>
2771
2772 <p>The second row is slightly more complicated. There were no occurrences of
2773 phrase 0. Phrase 1 ("three") appears once, at column 1 token offset 0. Although
2774 there are instances of phrase 2 ("four") in column 0, none of them are reported
2775 by the API, as phrase 4 has a <a href="#fts5_column_filters">column filter</a> -
2776 "y:". Matches that are filtered out by column filters do not count. Similarly,
2777 although phrases 3 and 4 do occur in column "x" of row 2, they are filtered
2778 out by the <a href="#fts5_near_queries">NEAR filter</a>. Matches that are
2779 filtered out by NEAR filters do not count either.
2780
2781 </p><p><b>Phrase Hits in the Current Row (2): xInstCount, xInst
2782
2783 </b></p><p>The <a href="#xInstCount">xInstCount</a> and <a href="#xInst">xInst</a> APIs
2784 provide access to the same information as the xPhraseFirst and xPhraseNext
2785 described above. The difference is that instead of iterating through the
2786 matches for a single, specified phrase, the xInstCount/xInst APIs collate
2787 all matches into a single flat array, sorted in order of occurrence within
2788 the current row. Elements of this array may then be accessed randomly.
2789
2790 </p><p>Each array element consists of three values:
2791
2792 </p><ul>
2793 <li> A phrase number,
2794 </li><li> A column number, and
2795 </li><li> A token offset
2796 </li></ul>
2797
2798 <p>Using the same example data and query as for xPhraseFirst/xPhraseNext
2799 above, the array accessible via xInstCount/xInst consists of the following
2800 entries for each row:
2801
2802 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
2803 <tr style="text-align:left"><th>Row</th><th>xInstCount/xInst array
2804 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>1</td><td>(0, 0, 1), (3, 0, 4), (4, 0, 6), (2, 1, 1)
2805 </td></tr><tr style="text-align:left"><td>2</td><td>(1, 1, 0), (2, 1, 1), (3, 1, 2), (4, 1, 3), (2, 1, 4), (3, 1, 5), (4, 1, 6)
2806 </td></tr></table>
2807
2808 <p>Each entry of the array is called a phrase match. Phrase matches are
2809 numbered in order, starting from 0. So, in the example above, in row 2, phrase
2810 match 3 is (4, 1, 3) - phrase 4 of the query matches at column 1, token offset
2811 3.
2812
2813 </p><a name="custom_auxiliary_functions_api_reference"></a>
2814 <h3 tags="custom auxiliary functions" id="custom_auxiliary_functions_api_reference"><span>7.2.2. </span>Custom Auxiliary Functions API Reference</h3>
2815
2816 <div class="codeblock"><pre>struct Fts5ExtensionApi {
2817 int iVersion; <i>/* Currently always set to 4 */</i>
2818
2819 void *(*<a href="#xUserData">xUserData</a>)(Fts5Context*);
2820
2821 int (*<a href="#xColumnCount">xColumnCount</a>)(Fts5Context*);
2822 int (*<a href="#xRowCount">xRowCount</a>)(Fts5Context*, sqlite3_int64 *pnRow);
2823 int (*<a href="#xColumnTotalSize">xColumnTotalSize</a>)(Fts5Context*, int iCol, sqlite3_int64 *pnToken);
2824
2825 int (*<a href="#xTokenize">xTokenize</a>)(Fts5Context*,
2826 const char *pText, int nText, <i>/* Text to tokenize */</i>
2827 void *pCtx, <i>/* Context passed to xToken() */</i>
2828 int (*xToken)(void*, int, const char*, int, int, int) <i>/* Callback */</i>
2829 );
2830
2831 int (*<a href="#xPhraseCount">xPhraseCount</a>)(Fts5Context*);
2832 int (*<a href="#xPhraseSize">xPhraseSize</a>)(Fts5Context*, int iPhrase);
2833
2834 int (*<a href="#xInstCount">xInstCount</a>)(Fts5Context*, int *pnInst);
2835 int (*<a href="#xInst">xInst</a>)(Fts5Context*, int iIdx, int *piPhrase, int *piCol, int *piOff);
2836
2837 sqlite3_int64 (*<a href="#xRowid">xRowid</a>)(Fts5Context*);
2838 int (*<a href="#xColumnText">xColumnText</a>)(Fts5Context*, int iCol, const char **pz, int *pn);
2839 int (*<a href="#xColumnSize">xColumnSize</a>)(Fts5Context*, int iCol, int *pnToken);
2840
2841 int (*<a href="#xQueryPhrase">xQueryPhrase</a>)(Fts5Context*, int iPhrase, void *pUserData,
2842 int(*)(const Fts5ExtensionApi*,Fts5Context*,void*)
2843 );
2844 int (*<a href="#xSetAuxdata">xSetAuxdata</a>)(Fts5Context*, void *pAux, void(*xDelete)(void*));
2845 void *(*<a href="#xGetAuxdata">xGetAuxdata</a>)(Fts5Context*, int bClear);
2846
2847 int (*<a href="#xPhraseFirst">xPhraseFirst</a>)(Fts5Context*, int iPhrase, Fts5PhraseIter*, int*, int*);
2848 void (*<a href="#xPhraseNext">xPhraseNext</a>)(Fts5Context*, Fts5PhraseIter*, int *piCol, int *piOff);
2849
2850 int (*<a href="#xPhraseFirstColumn">xPhraseFirstColumn</a>)(Fts5Context*, int iPhrase, Fts5PhraseIter*, int*);
2851 void (*<a href="#xPhraseNextColumn">xPhraseNextColumn</a>)(Fts5Context*, Fts5PhraseIter*, int *piCol);
2852
2853 <i>/* Below this point are iVersion>=3 only */</i>
2854 int (*<a href="#xQueryToken">xQueryToken</a>)(Fts5Context*,
2855 int iPhrase, int iToken,
2856 const char **ppToken, int *pnToken
2857 );
2858 int (*<a href="#xInstToken">xInstToken</a>)(Fts5Context*, int iIdx, int iToken, const char**, int*);
2859
2860 <i>/* Below this point are iVersion>=4 only */</i>
2861 int (*<a href="#xColumnLocale">xColumnLocale</a>)(Fts5Context*, int iCol, const char **pz, int *pn);
2862 int (*<a href="#xTokenize_v2">xTokenize_v2</a>)(Fts5Context*,
2863 const char *pText, int nText, <i>/* Text to tokenize */</i>
2864 const char *pLocale, int nLocale, <i>/* Locale to pass to tokenizer */</i>
2865 void *pCtx, <i>/* Context passed to xToken() */</i>
2866 int (*xToken)(void*, int, const char*, int, int, int) <i>/* Callback */</i>
2867 );
2868 };
2869 </pre></div>
2870
2871 <dl>
2872 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xUserData">
2873 <b>void *(*xUserData)(Fts5Context*)</b></dt><dd>
2874 <p style="margin-top:0.1em">
2875 Return a copy of the pUserData pointer passed to the xCreateFunction()
2876 API when the extension function was registered.
2877 </p>
2878 </dd>
2879 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xColumnTotalSize">
2880 <b>int (*xColumnTotalSize)(Fts5Context*, int iCol, sqlite3_int64 *pnToken)</b></dt><dd>
2881 <p style="margin-top:0.1em">
2882 If parameter iCol is less than zero, set output variable *pnToken
2883 to the total number of tokens in the FTS5 table. Or, if iCol is
2884 non-negative but less than the number of columns in the table, return
2885 the total number of tokens in column iCol, considering all rows in
2886 the FTS5 table.
2887 </p>
2888
2889 <p>
2890 If parameter iCol is greater than or equal to the number of columns
2891 in the table, SQLITE_RANGE is returned. Or, if an error occurs (e.g.
2892 an OOM condition or IO error), an appropriate SQLite error code is
2893 returned.
2894 </p>
2895 </dd>
2896 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xColumnCount">
2897 <b>int (*xColumnCount)(Fts5Context*)</b></dt><dd>
2898 <p style="margin-top:0.1em">
2899 Return the number of columns in the table.
2900 </p>
2901 </dd>
2902 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xColumnSize">
2903 <b>int (*xColumnSize)(Fts5Context*, int iCol, int *pnToken)</b></dt><dd>
2904 <p style="margin-top:0.1em">
2905 If parameter iCol is less than zero, set output variable *pnToken
2906 to the total number of tokens in the current row. Or, if iCol is
2907 non-negative but less than the number of columns in the table, set
2908 *pnToken to the number of tokens in column iCol of the current row.
2909 </p>
2910
2911 <p>
2912 If parameter iCol is greater than or equal to the number of columns
2913 in the table, SQLITE_RANGE is returned. Or, if an error occurs (e.g.
2914 an OOM condition or IO error), an appropriate SQLite error code is
2915 returned.
2916 </p>
2917
2918 <p>
2919 This function may be quite inefficient if used with an FTS5 table
2920 created with the "columnsize=0" option.
2921 </p>
2922 </dd>
2923 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xColumnText">
2924 <b>int (*xColumnText)(Fts5Context*, int iCol, const char **pz, int *pn)</b></dt><dd>
2925 <p style="margin-top:0.1em">
2926 If parameter iCol is less than zero, or greater than or equal to the
2927 number of columns in the table, SQLITE_RANGE is returned.
2928 </p>
2929
2930 <p>
2931 Otherwise, this function attempts to retrieve the text of column iCol of
2932 the current document. If successful, (*pz) is set to point to a buffer
2933 containing the text in utf-8 encoding, (*pn) is set to the size in bytes
2934 (not characters) of the buffer and SQLITE_OK is returned. Otherwise,
2935 if an error occurs, an SQLite error code is returned and the final values
2936 of (*pz) and (*pn) are undefined.
2937 </p>
2938 </dd>
2939 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseCount">
2940 <b>int (*xPhraseCount)(Fts5Context*)</b></dt><dd>
2941 <p style="margin-top:0.1em">
2942 Returns the number of phrases in the current query expression.
2943 </p>
2944 </dd>
2945 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseSize">
2946 <b>int (*xPhraseSize)(Fts5Context*, int iPhrase)</b></dt><dd>
2947 <p style="margin-top:0.1em">
2948 If parameter iCol is less than zero, or greater than or equal to the
2949 number of phrases in the current query, as returned by xPhraseCount,
2950 0 is returned. Otherwise, this function returns the number of tokens in
2951 phrase iPhrase of the query. Phrases are numbered starting from zero.
2952 </p>
2953 </dd>
2954 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xInstCount">
2955 <b>int (*xInstCount)(Fts5Context*, int *pnInst)</b></dt><dd>
2956 <p style="margin-top:0.1em">
2957 Set *pnInst to the total number of occurrences of all phrases within
2958 the query within the current row. Return SQLITE_OK if successful, or
2959 an error code (i.e. SQLITE_NOMEM) if an error occurs.
2960 </p>
2961
2962 <p>
2963 This API can be quite slow if used with an FTS5 table created with the
2964 "detail=none" or "detail=column" option. If the FTS5 table is created
2965 with either "detail=none" or "detail=column" and "content=" option
2966 (i.e. if it is a contentless table), then this API always returns 0.
2967 </p>
2968 </dd>
2969 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xInst">
2970 <b>int (*xInst)(Fts5Context*, int iIdx, int *piPhrase, int *piCol, int *piOff)</b></dt><dd>
2971 <p style="margin-top:0.1em">
2972 Query for the details of phrase match iIdx within the current row.
2973 Phrase matches are numbered starting from zero, so the iIdx argument
2974 should be greater than or equal to zero and smaller than the value
2975 output by xInstCount(). If iIdx is less than zero or greater than
2976 or equal to the value returned by xInstCount(), SQLITE_RANGE is returned.
2977 </p>
2978
2979 <p>
2980 Otherwise, output parameter *piPhrase is set to the phrase number, *piCol
2981 to the column in which it occurs and *piOff the token offset of the
2982 first token of the phrase. SQLITE_OK is returned if successful, or an
2983 error code (i.e. SQLITE_NOMEM) if an error occurs.
2984 </p>
2985
2986 <p>
2987 This API can be quite slow if used with an FTS5 table created with the
2988 "detail=none" or "detail=column" option.
2989 </p>
2990 </dd>
2991 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xRowid">
2992 <b>sqlite3_int64 (*xRowid)(Fts5Context*)</b></dt><dd>
2993 <p style="margin-top:0.1em">
2994 Returns the rowid of the current row.
2995 </p>
2996 </dd>
2997 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xTokenize">
2998 <b>int (*xTokenize)(Fts5Context*,
2999 const char *pText, int nText,
3000 void *pCtx,
3001 int (*xToken)(void*, int, const char*, int, int, int)
3002 )</b></dt><dd>
3003 <p style="margin-top:0.1em">
3004 Tokenize text using the tokenizer belonging to the FTS5 table.
3005 </p>
3006 </dd>
3007 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xQueryPhrase">
3008 <b>int (*xQueryPhrase)(Fts5Context*, int iPhrase, void *pUserData,
3009 int(*)(const Fts5ExtensionApi*,Fts5Context*,void*)
3010 )</b></dt><dd>
3011 <p style="margin-top:0.1em">
3012 This API function is used to query the FTS table for phrase iPhrase
3013 of the current query. Specifically, a query equivalent to:
3014 </p>
3015
3016 <div class="codeblock"><pre>... FROM ftstable WHERE ftstable MATCH $p ORDER BY rowid
3017 </pre></div>
3018
3019 <p>
3020 with $p set to a phrase equivalent to the phrase iPhrase of the
3021 current query is executed. Any column filter that applies to
3022 phrase iPhrase of the current query is included in $p. For each
3023 row visited, the callback function passed as the fourth argument
3024 is invoked. The context and API objects passed to the callback
3025 function may be used to access the properties of each matched row.
3026 Invoking Api.xUserData() returns a copy of the pointer passed as
3027 the third argument to pUserData.
3028 </p>
3029
3030 <p>
3031 If parameter iPhrase is less than zero, or greater than or equal to
3032 the number of phrases in the query, as returned by xPhraseCount(),
3033 this function returns SQLITE_RANGE.
3034 </p>
3035
3036 <p>
3037 If the callback function returns any value other than SQLITE_OK, the
3038 query is abandoned and the xQueryPhrase function returns immediately.
3039 If the returned value is SQLITE_DONE, xQueryPhrase returns SQLITE_OK.
3040 Otherwise, the error code is propagated upwards.
3041 </p>
3042
3043 <p>
3044 If the query runs to completion without incident, SQLITE_OK is returned.
3045 Or, if some error occurs before the query completes or is aborted by
3046 the callback, an SQLite error code is returned.
3047 </p>
3048 </dd>
3049 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xSetAuxdata">
3050 <b>int (*xSetAuxdata)(Fts5Context*, void *pAux, void(*xDelete)(void*))</b></dt><dd>
3051 <p style="margin-top:0.1em">
3052 Save the pointer passed as the second argument as the extension function's
3053 "auxiliary data". The pointer may then be retrieved by the current or any
3054 future invocation of the same fts5 extension function made as part of
3055 the same MATCH query using the xGetAuxdata() API.
3056 </p>
3057
3058 <p>
3059 Each extension function is allocated a single auxiliary data slot for
3060 each FTS query (MATCH expression). If the extension function is invoked
3061 more than once for a single FTS query, then all invocations share a
3062 single auxiliary data context.
3063 </p>
3064
3065 <p>
3066 If there is already an auxiliary data pointer when this function is
3067 invoked, then it is replaced by the new pointer. If an xDelete callback
3068 was specified along with the original pointer, it is invoked at this
3069 point.
3070 </p>
3071
3072 <p>
3073 The xDelete callback, if one is specified, is also invoked on the
3074 auxiliary data pointer after the FTS5 query has finished.
3075 </p>
3076
3077 <p>
3078 If an error (e.g. an OOM condition) occurs within this function,
3079 the auxiliary data is set to NULL and an error code returned. If the
3080 xDelete parameter was not NULL, it is invoked on the auxiliary data
3081 pointer before returning.
3082 </p>
3083 </dd>
3084 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xGetAuxdata">
3085 <b>void *(*xGetAuxdata)(Fts5Context*, int bClear)</b></dt><dd>
3086 <p style="margin-top:0.1em">
3087 Returns the current auxiliary data pointer for the fts5 extension
3088 function. See the xSetAuxdata() method for details.
3089 </p>
3090
3091 <p>
3092 If the bClear argument is non-zero, then the auxiliary data is cleared
3093 (set to NULL) before this function returns. In this case the xDelete,
3094 if any, is not invoked.
3095 </p>
3096 </dd>
3097 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xRowCount">
3098 <b>int (*xRowCount)(Fts5Context*, sqlite3_int64 *pnRow)</b></dt><dd>
3099 <p style="margin-top:0.1em">
3100 This function is used to retrieve the total number of rows in the table.
3101 In other words, the same value that would be returned by:
3102 </p>
3103
3104 <div class="codeblock"><pre>SELECT count(*) FROM ftstable;
3105 </pre></div>
3106 </dd>
3107 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseFirst">
3108 <b>int (*xPhraseFirst)(Fts5Context*, int iPhrase, Fts5PhraseIter*, int*, int*)</b></dt><dd>
3109 <p style="margin-top:0.1em">
3110 This function is used, along with type Fts5PhraseIter and the xPhraseNext
3111 method, to iterate through all instances of a single query phrase within
3112 the current row. This is the same information as is accessible via the
3113 xInstCount/xInst APIs. While the xInstCount/xInst APIs are more convenient
3114 to use, this API may be faster under some circumstances. To iterate
3115 through instances of phrase iPhrase, use the following code:
3116 </p>
3117
3118 <div class="codeblock"><pre>Fts5PhraseIter iter;
3119 int iCol, iOff;
3120 for(pApi->xPhraseFirst(pFts, iPhrase, &iter, &iCol, &iOff);
3121 iCol>=0;
3122 pApi->xPhraseNext(pFts, &iter, &iCol, &iOff)
3123 ){
3124 // An instance of phrase iPhrase at offset iOff of column iCol
3125 }
3126 </pre></div>
3127
3128 <p>
3129 The Fts5PhraseIter structure is defined above. Applications should not
3130 modify this structure directly - it should only be used as shown above
3131 with the xPhraseFirst() and xPhraseNext() API methods (and by
3132 xPhraseFirstColumn() and xPhraseNextColumn() as illustrated below).
3133 </p>
3134
3135 <p>
3136 This API can be quite slow if used with an FTS5 table created with the
3137 "detail=none" or "detail=column" option. If the FTS5 table is created
3138 with either "detail=none" or "detail=column" and "content=" option
3139 (i.e. if it is a contentless table), then this API always iterates
3140 through an empty set (all calls to xPhraseFirst() set iCol to -1).
3141 </p>
3142
3143 <p>
3144 In all cases, matches are visited in (column ASC, offset ASC) order.
3145 i.e. all those in column 0, sorted by offset, followed by those in
3146 column 1, etc.
3147 </p>
3148 </dd>
3149 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseNext">
3150 <b>void (*xPhraseNext)(Fts5Context*, Fts5PhraseIter*, int *piCol, int *piOff)</b></dt><dd>
3151 <p style="margin-top:0.1em">
3152 See xPhraseFirst above.
3153 </p>
3154 </dd>
3155 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseFirstColumn">
3156 <b>int (*xPhraseFirstColumn)(Fts5Context*, int iPhrase, Fts5PhraseIter*, int*)</b></dt><dd>
3157 <p style="margin-top:0.1em">
3158 This function and xPhraseNextColumn() are similar to the xPhraseFirst()
3159 and xPhraseNext() APIs described above. The difference is that instead
3160 of iterating through all instances of a phrase in the current row, these
3161 APIs are used to iterate through the set of columns in the current row
3162 that contain one or more instances of a specified phrase. For example:
3163 </p>
3164
3165 <div class="codeblock"><pre>Fts5PhraseIter iter;
3166 int iCol;
3167 for(pApi->xPhraseFirstColumn(pFts, iPhrase, &iter, &iCol);
3168 iCol>=0;
3169 pApi->xPhraseNextColumn(pFts, &iter, &iCol)
3170 ){
3171 // Column iCol contains at least one instance of phrase iPhrase
3172 }
3173 </pre></div>
3174
3175 <p>
3176 This API can be quite slow if used with an FTS5 table created with the
3177 "detail=none" option. If the FTS5 table is created with either
3178 "detail=none" "content=" option (i.e. if it is a contentless table),
3179 then this API always iterates through an empty set (all calls to
3180 xPhraseFirstColumn() set iCol to -1).
3181 </p>
3182
3183 <p>
3184 The information accessed using this API and its companion
3185 xPhraseFirstColumn() may also be obtained using xPhraseFirst/xPhraseNext
3186 (or xInst/xInstCount). The chief advantage of this API is that it is
3187 significantly more efficient than those alternatives when used with
3188 "detail=column" tables.
3189 </p>
3190 </dd>
3191 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xPhraseNextColumn">
3192 <b>void (*xPhraseNextColumn)(Fts5Context*, Fts5PhraseIter*, int *piCol)</b></dt><dd>
3193 <p style="margin-top:0.1em">
3194 See xPhraseFirstColumn above.
3195 </p>
3196 </dd>
3197 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xQueryToken">
3198 <b>int (*xQueryToken)(Fts5Context*,
3199 int iPhrase, int iToken,
3200 const char **ppToken, int *pnToken
3201 )</b></dt><dd>
3202 <p style="margin-top:0.1em">
3203 This is used to access token iToken of phrase iPhrase of the current
3204 query. Before returning, output parameter *ppToken is set to point
3205 to a buffer containing the requested token, and *pnToken to the
3206 size of this buffer in bytes.
3207 </p>
3208
3209 <p>
3210 If iPhrase or iToken are less than zero, or if iPhrase is greater than
3211 or equal to the number of phrases in the query as reported by
3212 xPhraseCount(), or if iToken is equal to or greater than the number of
3213 tokens in the phrase, SQLITE_RANGE is returned and *ppToken and *pnToken
3214 are both zeroed.
3215 </p>
3216
3217 <p>
3218 The output text is not a copy of the query text that specified the
3219 token. It is the output of the tokenizer module. For tokendata=1
3220 tables, this includes any embedded 0x00 and trailing data.
3221 </p>
3222 </dd>
3223 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xInstToken">
3224 <b>int (*xInstToken)(Fts5Context*, int iIdx, int iToken, const char**, int*)</b></dt><dd>
3225 <p style="margin-top:0.1em">
3226 This is used to access token iToken of phrase hit iIdx within the
3227 current row. If iIdx is less than zero or greater than or equal to the
3228 value returned by xInstCount(), SQLITE_RANGE is returned. Otherwise,
3229 output variable (*ppToken) is set to point to a buffer containing the
3230 matching document token, and (*pnToken) to the size of that buffer in
3231 bytes.
3232 </p>
3233
3234 <p>
3235 The output text is not a copy of the document text that was tokenized.
3236 It is the output of the tokenizer module. For tokendata=1 tables, this
3237 includes any embedded 0x00 and trailing data.
3238 </p>
3239
3240 <p>
3241 This API may be slow in some cases if the token identified by parameters
3242 iIdx and iToken matched a prefix token in the query. In most cases, the
3243 first call to this API for each prefix token in the query is forced
3244 to scan the portion of the full-text index that matches the prefix
3245 token to collect the extra data required by this API. If the prefix
3246 token matches a large number of token instances in the document set,
3247 this may be a performance problem.
3248 </p>
3249
3250 <p>
3251 If the user knows in advance that a query may use this API for a
3252 prefix token, FTS5 may be configured to collect all required data as part
3253 of the initial querying of the full-text index, avoiding the second scan
3254 entirely. This also causes prefix queries that do not use this API to
3255 run more slowly and use more memory. FTS5 may be configured in this way
3256 either on a per-table basis using the <a href="fts5.html#the_insttoken_configuration_option">'insttoken'</a>
3257 option, or on a per-query basis using the
3258 <a href="fts5.html#the_fts5_insttoken_function">fts5_insttoken()</a> user function.
3259 </p>
3260
3261 <p>
3262 This API can be quite slow if used with an FTS5 table created with the
3263 "detail=none" or "detail=column" option.
3264 </p>
3265 </dd>
3266 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xColumnLocale">
3267 <b>int (*xColumnLocale)(Fts5Context*, int iCol, const char **pz, int *pn)</b></dt><dd>
3268 <p style="margin-top:0.1em">
3269 If parameter iCol is less than zero, or greater than or equal to the
3270 number of columns in the table, SQLITE_RANGE is returned.
3271 </p>
3272
3273 <p>
3274 Otherwise, this function attempts to retrieve the locale associated
3275 with column iCol of the current row. Usually, there is no associated
3276 locale, and output parameters (*pzLocale) and (*pnLocale) are set
3277 to NULL and 0, respectively. However, if the fts5_locale() function
3278 was used to associate a locale with the value when it was inserted
3279 into the fts5 table, then (*pzLocale) is set to point to a nul-terminated
3280 buffer containing the name of the locale in utf-8 encoding. (*pnLocale)
3281 is set to the size in bytes of the buffer, not including the
3282 nul-terminator.
3283 </p>
3284
3285 <p>
3286 If successful, SQLITE_OK is returned. Or, if an error occurs, an
3287 SQLite error code is returned. The final value of the output parameters
3288 is undefined in this case.
3289 </p>
3290 </dd>
3291 <dt style="white-space:pre;font-family:monospace;font-size:120%" id="xTokenize_v2">
3292 <b>int (*xTokenize_v2)(Fts5Context*,
3293 const char *pText, int nText,
3294 const char *pLocale, int nLocale,
3295 void *pCtx,
3296 int (*xToken)(void*, int, const char*, int, int, int)
3297 )</b></dt><dd>
3298 <p style="margin-top:0.1em">
3299 Tokenize text using the tokenizer belonging to the FTS5 table. This
3300 API is the same as the xTokenize() API, except that it allows a tokenizer
3301 locale to be specified.
3302 </p>
3303 </dd>
3304 </dl>
3305
3306
3307 <a name="the_fts5vocab_virtual_table_module"></a>
3308 <h1 tags="fts5vocab" id="the_fts5vocab_virtual_table_module"><span>8. </span>The fts5vocab Virtual Table Module</h1>
3309
3310 <p> The fts5vocab virtual table module allows users to extract information from
3311 an FTS5 full-text index directly. The fts5vocab module is a part of FTS5 - it
3312 is available whenever FTS5 is.
3313
3314 </p><p> Each fts5vocab table is associated with a single FTS5 table. An fts5vocab
3315 table is usually created by specifying two arguments in place of column names
3316 in the CREATE VIRTUAL TABLE statement - the name of the associated FTS5 table
3317 and the type of fts5vocab table. Currently there are three types of fts5vocab
3318 table; "row", "col" and "instance". Unless the fts5vocab table is created
3319 within the "temp" database, it must be part of the same database as the
3320 associated FTS5 table.
3321
3322 </p><div class="codeblock"><pre><i>-- Create an fts5vocab "row" table to query the full-text index belonging
3323 -- to FTS5 table "ft1".</i>
3324 CREATE VIRTUAL TABLE ft1_v USING fts5vocab('ft1', 'row');
3325
3326 <i>-- Create an fts5vocab "col" table to query the full-text index belonging
3327 -- to FTS5 table "ft2".</i>
3328 CREATE VIRTUAL TABLE ft2_v USING fts5vocab(ft2, col);
3329
3330 <i>-- Create an fts5vocab "instance" table to query the full-text index
3331 -- belonging to FTS5 table "ft3".</i>
3332 CREATE VIRTUAL TABLE ft3_v USING fts5vocab(ft3, instance);
3333 </pre></div>
3334
3335 <p> If an fts5vocab table is created in the temp database, it may be associated
3336 with an FTS5 table in any attached database. In order to attach the fts5vocab
3337 table to an FTS5 table located in a database other than "temp", the name of the
3338 database is inserted before the FTS5 table name in the CREATE VIRTUAL TABLE
3339 arguments. For example:
3340
3341 </p><div class="codeblock"><pre><i>-- Create an fts5vocab "row" table to query the full-text index belonging
3342 -- to FTS5 table "ft1" in database "main".</i>
3343 CREATE VIRTUAL TABLE temp.ft1_v USING fts5vocab(main, 'ft1', 'row');
3344
3345 <i>-- Create an fts5vocab "col" table to query the full-text index belonging
3346 -- to FTS5 table "ft2" in attached database "aux".</i>
3347 CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft2, col);
3348
3349 <i>-- Create an fts5vocab "instance" table to query the full-text index
3350 -- belonging to FTS5 table "ft3" in attached database "other".</i>
3351 CREATE VIRTUAL TABLE temp.ft2_v USING fts5vocab('aux', ft3, 'instance');
3352 </pre></div>
3353
3354 <p> Specifying three arguments when creating an fts5vocab table in any database
3355 other than "temp" results in an error.
3356
3357 </p><p> An fts5vocab table of type "row" contains one row for each distinct term
3358 in the associated FTS5 table. The table columns are as follows:
3359
3360 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
3361 <tr style="text-align:left"><th>Column</th><th>Contents
3362 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>term</td><td> The term, as stored in the FTS5 index.
3363 </td></tr><tr style="text-align:left"><td>doc</td><td> The number of rows that contain at least one instance of the term.
3364 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>cnt</td><td> The total number of instances of the term in the entire FTS5 table.
3365 </td></tr></table>
3366
3367 <p> An fts5vocab table of type "col" contains one row for each distinct term/column
3368 combination in the associated FTS5 table. Table columns are as follows:
3369
3370 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
3371 <tr style="text-align:left"><th>Column</th><th>Contents
3372 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>term</td><td> The term, as stored in the FTS5 index.
3373 </td></tr><tr style="text-align:left"><td>col</td><td> The name of the FTS5 table column that contains the term.
3374 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>doc</td><td> The number of rows in the FTS5 table for which column $col
3375 contains at least one instance of the term.
3376 </td></tr><tr style="text-align:left"><td>cnt</td><td> The total number of instances of the term that appear in
3377 column $col of the FTS5 table (considering all rows).
3378 </td></tr></table>
3379
3380 <p> An fts5vocab table of type "instance" contains one row for each term
3381 instance stored in the associated FTS index. Assuming the FTS5 table is
3382 created with the 'detail' option set to 'full', table columns are as follows:
3383
3384 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
3385 <tr style="text-align:left"><th>Column</th><th>Contents
3386 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>term</td><td> The term, as stored in the FTS5 index.
3387 </td></tr><tr style="text-align:left"><td>doc</td><td> The rowid of the document that contains the term instance.
3388 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>col</td><td> The name of the column that contains the term instance.
3389 </td></tr><tr style="text-align:left"><td>offset</td><td> The index of the term instance within its column. Terms
3390 are numbered in order of occurrence starting from 0.
3391 </td></tr></table>
3392
3393 <p> If the FTS5 table is created with the 'detail' option set to 'col', then
3394 the <i>offset</i> column of an instance virtual table always contains NULL.
3395 In this case there is one row in the table for each unique term/doc/col
3396 combination. Or, if the FTS5 table is created with 'detail' set to 'none',
3397 then both <i>offset</i> and <i>col</i> always contain NULL values. For
3398 detail=none FTS5 tables, there is one row in the fts5vocab table for each
3399 unique term/doc combination.
3400
3401 </p><p>Example:
3402
3403 </p><div class="codeblock"><pre><i>-- Assuming a database created using:</i>
3404 CREATE VIRTUAL TABLE ft USING fts5(c1, c2);
3405 INSERT INTO ft VALUES('apple banana cherry', 'banana banana cherry');
3406 INSERT INTO ft VALUES('cherry cherry cherry', 'date date date');
3407
3408 <i>-- Then querying the following fts5vocab table (type "col") returns:
3409 --
3410 -- apple | c1 | 1 | 1
3411 -- banana | c1 | 1 | 1
3412 -- banana | c2 | 1 | 2
3413 -- cherry | c1 | 2 | 4
3414 -- cherry | c2 | 1 | 1
3415 -- date | c3 | 1 | 3
3416 --</i>
3417 CREATE VIRTUAL TABLE ft_v_col USING fts5vocab(ft, col);
3418
3419 <i>-- Querying an fts5vocab table of type "row" returns:
3420 --
3421 -- apple | 1 | 1
3422 -- banana | 1 | 3
3423 -- cherry | 2 | 5
3424 -- date | 1 | 3
3425 --</i>
3426 CREATE VIRTUAL TABLE ft_v_row USING fts5vocab(ft, row);
3427
3428 <i>-- And, for type "instance"
3429 INSERT INTO ft VALUES('apple banana cherry', 'banana banana cherry');
3430 INSERT INTO ft VALUES('cherry cherry cherry', 'date date date');
3431 --
3432 -- apple | 1 | c1 | 0
3433 -- banana | 1 | c1 | 1
3434 -- banana | 1 | c2 | 0
3435 -- banana | 1 | c2 | 1
3436 -- cherry | 1 | c1 | 2
3437 -- cherry | 1 | c2 | 2
3438 -- cherry | 2 | c1 | 0
3439 -- cherry | 2 | c1 | 1
3440 -- cherry | 2 | c1 | 2
3441 -- date | 2 | c2 | 0
3442 -- date | 2 | c2 | 1
3443 -- date | 2 | c2 | 2
3444 --</i>
3445 CREATE VIRTUAL TABLE ft_v_instance USING fts5vocab(ft, instance);
3446 </pre></div>
3447
3448 <a name="fts5shadowtables"></a>
3449
3450 <h1 id="fts5_data_structures"><span>9. </span>FTS5 Data Structures</h1>
3451
3452 <p>This section describes at a high-level the way the FTS module stores its
3453 index and content in the database. It is not necessary to read or understand
3454 the material in this section in order to use FTS in an application. However, it
3455 may be useful to application developers attempting to analyze and understand
3456 FTS performance characteristics, or to developers contemplating enhancements to
3457 the existing FTS feature set.
3458
3459 </p><p>
3460 When an FTS5 virtual table is created in a database, between 3 and 5 real
3461 tables are created in the database. These are known as "<a href="vtab.html#xshadowname">shadow tables</a>", and
3462 are used by the virtual table module to store persistent data. They should not
3463 be accessed directly by the user. Many other virtual table modules, including
3464 <a href="fts3.html">FTS3</a> and <a href="rtree.html">rtree</a>, also create and use shadow tables.
3465
3466 </p><p>FTS5 creates the following shadow tables. In each case the actual table name
3467 is based on the name of the FTS5 virtual table (in the following, replace
3468 % with the name of the virtual table to find the actual shadow table name).
3469
3470 </p><div class="codeblock"><pre><i>-- This table contains most of the full-text index data. </i>
3471 CREATE TABLE %_data(id INTEGER PRIMARY KEY, block BLOB);
3472
3473 <i>-- This table contains the remainder of the full-text index data. </i>
3474 <i>-- It is almost always much smaller than the %_data table. </i>
3475 CREATE TABLE %_idx(segid, term, pgno, PRIMARY KEY(segid, term)) WITHOUT ROWID;
3476
3477 <i>-- Contains the values of persistent configuration parameters.</i>
3478 CREATE TABLE %_config(k PRIMARY KEY, v) WITHOUT ROWID;
3479
3480 <i>-- Contains the size of each column of each row in the virtual table</i>
3481 <i>-- in tokens. This shadow table is not present if the "columnsize"</i>
3482 <i>-- option is set to 0.</i>
3483 CREATE TABLE %_docsize(id INTEGER PRIMARY KEY, sz BLOB);
3484
3485 <i>-- Contains the actual data inserted into the FTS5 table. There</i>
3486 <i>-- is one "cN" column for each indexed column in the FTS5 table.</i>
3487 <i>-- This shadow table is not present for contentless or external </i>
3488 <i>-- content FTS5 tables. </i>
3489 CREATE TABLE %_content(id INTEGER PRIMARY KEY, c0, c1...);
3490 </pre></div>
3491
3492 <p>The following sections describe in more detail how these five tables are
3493 used to store FTS5 data.
3494
3495 </p><h2 id="varint_format"><span>9.1. </span>Varint Format</h2>
3496
3497 <p>The sections below refer to 64-bit signed integers stored in "varint" form.
3498 FTS5 uses the same varint format as used in various places by the SQLite core.
3499
3500 </p><p>A varint is between 1 and 9 bytes in length. The varint consists of either
3501 zero or more bytes which have the high-order bit set followed by a single byte
3502 with the high-order bit clear, or nine bytes, whichever is shorter. The lower
3503 seven bits of each of the first eight bytes and all 8 bits of the ninth byte
3504 are used to reconstruct the 64-bit twos-complement integer. Varints are
3505 big-endian: bits taken from the earlier byte of the varint are more significant
3506 than bits taken from the later bytes.
3507
3508 </p><h2 id="the_fts_index_idx_and_data_tables_"><span>9.2. </span>The FTS Index (%_idx and %_data tables)</h2>
3509
3510 <p>The FTS index is an ordered key-value store where the keys are document
3511 terms or term prefixes and the associated values are "doclists". A doclist is a
3512 packed array of varints that encodes the position of each instance of the term
3513 within the FTS5 table. The position of a single term instance is defined as the
3514 combination of:
3515
3516 </p><ul>
3517 <li> The rowid of the FTS5 table row it appears in,
3518 </li><li> The index of the column the term instance appears in (columns are
3519 numbered from left to right starting from zero), and
3520 </li><li> The offset of the term within the column value (i.e. the number of
3521 tokens that appear within the column value before this one).
3522 </li></ul>
3523
3524 <p>The FTS index contains up to (nPrefix+1) entries for each token in the
3525 data set, where nPrefix is the number of defined <a href="#prefix_indexes">
3526 prefix indexes</a>.
3527
3528 </p><p>Keys associated with the main FTS index (the one that is not a prefix
3529 index) are prefixed with the character "0". Keys for the first prefix
3530 index are prefixed with "1". Keys for the second prefix index are
3531 prefixed with "2", and so on. For example, if the token "document" is
3532 inserted into an FTS5 table with <a href="#prefix_indexes">prefix indexes</a>
3533 specified by prefix="2 4", then the keys added to the FTS index would be
3534 "0document", "1do" and "2docu".
3535
3536 </p><p>The FTS index entries are not stored in a single tree or hash table
3537 structure. Instead, they are stored in a series of immutable b-tree like
3538 structures referred to as "segment b-trees". Each time a write to the FTS5
3539 table is committed, one or more (but usually just one) new segment b-trees
3540 are added containing both the new entries and tombstones for any deleted
3541 entries. When the FTS index is queried, the reader queries each segment
3542 b-tree in turn and merges the results, giving priority to newer data.
3543
3544 </p><p>Each segment b-tree is assigned a numerical level. When a new segment
3545 b-tree is written to the database as part of committing a transaction,
3546 it is assigned to level 0. Segment b-trees belonging to a single level are
3547 periodically merged together to create a single, larger segment b-tree
3548 that is assigned to the next level (i.e. level 0 segment b-trees are
3549 merged to become a single level 1 segment b-tree). Thus the numerically
3550 larger levels contain older data in (usually) larger segment b-trees.
3551 Refer to the
3552 <a href="#the_automerge_configuration_option">'automerge'</a>,
3553 <a href="#the_crisismerge_configuration_option">'crisismerge'</a> and
3554 <a href="#the_usermerge_configuration_option">'usermerge'</a> options, along
3555 with the
3556 <a href="#the_merge_command">'merge'</a> and
3557 <a href="#the_optimize_command">'optimize'</a> commands for details on how to
3558 control the merging.
3559
3560 </p><p>In cases where the doclist associated with a term or term prefix is very
3561 large, there may be an associated <a href="#doclist_index_format">doclist
3562 index</a>. A doclist index is similar to the set of internal nodes of a b-tree.
3563 It allows a large doclist to be efficiently queried for rowids or ranges of
3564 rowids. For example, when processing a query like:
3565
3566 </p><div class="codeblock"><pre>SELECT ... FROM ft('term') WHERE rowid BETWEEN ? AND ?
3567 </pre></div>
3568
3569 <p>FTS5 uses the segment b-tree index to locate the doclist for term "term",
3570 then uses its doclist index (assuming it is present) to efficiently identify
3571 the subset of matches with rowids in the required range.
3572
3573 </p><h3 id="data_structure"><span>9.2.1. </span>The %_data Table Rowid Space</h3>
3574
3575 <div class="codeblock"><pre>CREATE TABLE %_data(
3576 id INTEGER PRIMARY KEY,
3577 block BLOB
3578 );
3579 </pre></div>
3580
3581 <p>The %_data table is used to store three types of records:
3582
3583 </p><ul>
3584 <li> The special <a href="#structure_record_format">structure record</a>,
3585 stored with id=10.
3586 </li><li> The special <a href="#averages_record_format">averages record</a>,
3587 stored with id=1.
3588 </li><li> A record to store each <a href="#segment_b_tree_format">segment b-tree</a>
3589 leaf and <a href="#doclist_index_format">doclist index</a> leaf and
3590 internal node. See below for how id values are calculated for these
3591 records.
3592 </li></ul>
3593
3594 <p>Each segment b-tree in the system is assigned a unique 16-bit segment id.
3595 Segment ids may only be reused after the original owner segment b-tree is
3596 completely merged into a higher level segment b-tree. Within a segment b-tree,
3597 each leaf page is assigned a unique page number - 1 for the first leaf page, 2
3598 for the second, and so on.
3599
3600 </p><p>Each doclist index leaf page is also assigned a page number. The first
3601 (leftmost) leaf page in a doclist index is assigned the same page number as
3602 the segment b-tree leaf page on which its term appears (because doclist indexes
3603 are only created for terms with very long doclists, at most one term per
3604 segment b-tree leaf has an associated doclist index). Call this page number P.
3605 If the doclist is so large that it requires a second leaf, the second leaf is
3606 assigned page number P+1. The third leaf P+2. Each tier of a doclist index
3607 b-tree (leaves, parents of leaves, grandparents etc.) is assigned page numbers
3608 in this fashion, starting with page number P.
3609
3610 </p><p>The "id" value used in the %_data table to store any given segment b-tree
3611 leaf or doclist index leaf or node is composed as follows:
3612
3613 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
3614 <tr style="text-align:left"><th>Rowid&nbsp;Bits </th><th>Contents
3615 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>38..43 </td><td> (16 bit) Segment b-tree id value.
3616 </td></tr><tr style="text-align:left"><td>37 </td><td> (1 bit) Doclist index flag. Set for doclist index pages, clear
3617 for segment b-tree leaves.
3618 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>32..36 </td><td> (5 bits) Height in tree. This is set to 0 for segment b-tree
3619 and doclist index leaves, to 1 for the parents of doclist
3620 index leaves, 2 for the grandparents, etc.
3621 </td></tr><tr style="text-align:left"><td>0..31 </td><td> (32 bits) Page number
3622 </td></tr></table>
3623
3624 <h3 id="structure_record_format"><span>9.2.2. </span>Structure Record Format</h3>
3625
3626 <p>The structure record identifies the set of segment b-trees that make up the
3627 current FTS index, along with details of any ongoing incremental merge
3628 operations. It is stored in the %_data table with id=10.
3629
3630 A structure record begins with a single 32-bit unsigned value - the cookie
3631 value. This value is incremented each time the structure is modified.
3632 Following the cookie value are three varint values, as follows:
3633
3634 </p><ul>
3635 <li> The number of levels in the index (i.e. the maximum level associated
3636 with any segment b-tree plus one).
3637 </li><li> The total number of segment b-trees in the index.
3638 </li><li> The total number of segment b-tree leaves written to level 0 trees
3639 since the FTS5 table was created.
3640 </li></ul>
3641
3642 <p>Then, for each level from 0 to nLevel:
3643
3644 </p><ul>
3645 <li> The number of input segments from the previous level being used as
3646 inputs for the current incremental merge, or zero if there is no
3647 ongoing incremental merge to create a new segment b-tree for this level.
3648 </li><li> The total number of segment b-trees on the level.
3649 </li><li> Then, for each segment b-tree, from oldest to newest:
3650 <ul>
3651 <li> The segment id.
3652 </li><li> Page number of first leaf (often 1, always >0).
3653 </li><li> Page number of last leaf (always >0).
3654 </li></ul>
3655 </li></ul>
3656
3657 <h3 id="averages_record_format"><span>9.2.3. </span>Averages Record Format</h3>
3658
3659 <p>The averages record, which is always stored with id=1 in the %_data table,
3660 does not store the average of anything. Instead, it contains a vector of
3661 (nCol+1) packed varint values, where nCol is the number of columns in the FTS5
3662 table, including unindexed columns. The first varint contains the total
3663 number of rows in the FTS5 table. The second contains the total number of
3664 tokens in all values stored in the leftmost FTS5 table column. The third contains the
3665 number of tokens in all values for the next leftmost, and so on. The value for
3666 unindexed columns is always zero.
3667
3668 </p><h3 id="segment_b_tree_format"><span>9.2.4. </span>Segment B-Tree Format</h3>
3669
3670 <h4 id="the_key_doclist_format"><span>9.2.4.1. </span>The Key/Doclist Format</h4>
3671
3672 <p>The key/doclist format is a format used to store a series of keys (document
3673 terms or term prefixes prefixed by a single character to identify the specific
3674 index to which they belong) in sorted order, each with their associated
3675 doclist. The format consists of alternating keys and doclists packed together.
3676
3677 </p><p>The first key is stored as:
3678 </p><ul>
3679 <li> A varint indicating the number of bytes in the key (N), followed by
3680 </li><li> The key data itself (N bytes).
3681 </li></ul>
3682
3683 <p>Each subsequent key is stored as:
3684 </p><ul>
3685 <li> A varint indicating the size of the prefix that the key has in common
3686 with the previous key in bytes,
3687 </li><li> A varint indicating the number of bytes in the key following the
3688 common prefix (N), followed by
3689 </li><li> The key suffix data itself (N bytes).
3690 </li></ul>
3691
3692 <p>For example, if the first two keys in an FTS5 key/doclist record are
3693 "0challenger" and "0chandelier", then the first key is stored as varint 11
3694 followed by the 11 bytes "0challenger", and the second key is stored as varints
3695 4 and 7, followed by the 7 bytes "ndelier".
3696
3697 </p><center><div style="max-width:967px;"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 967.622 136.8" data-pikchr-date="20250319161943">
3698 <path d="M68.6304,82.8L97.4304,82.8L97.4304,54L68.6304,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3699 <path d="M97.4304,82.8L155.03,82.8L155.03,54L97.4304,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3700 <path d="M155.03,82.8L443.03,82.8L443.03,54L155.03,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3701 <text x="299.03" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">doclist 0</text>
3702 <path d="M443.03,82.8L471.83,82.8L471.83,54L443.03,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3703 <path d="M471.83,82.8L500.63,82.8L500.63,54L471.83,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3704 <path d="M500.63,82.8L543.83,82.8L543.83,54L500.63,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3705 <path d="M543.83,82.8L831.83,82.8L831.83,54L543.83,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3706 <text x="687.83" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">doclist 1</text>
3707 <text x="898.646" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key/doclist 2...</text>
3708 <path d="M831.83,54L889.43,54" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3709 <path d="M831.83,82.8L889.43,82.8" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3710 <text x="126.23" y="119.52" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key 0 data</text>
3711 <polygon points="126.23,82.8 130.55,94.32 121.91,94.32" style="fill:rgb(0,0,0)"/>
3712 <path d="M126.23,104.4L126.23,88.56" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3713 <text x="83.0304" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key 0 size (varint)</text>
3714 <polygon points="83.0304,54 78.7104,42.48 87.3504,42.48" style="fill:rgb(0,0,0)"/>
3715 <path d="M83.0304,32.4L83.0304,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3716 <text x="363.83" y="119.52" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key 1 prefix size (varint)</text>
3717 <polygon points="457.43,82.8 447.177,89.5998 445.234,81.181" style="fill:rgb(0,0,0)"/>
3718 <path d="M363.83,104.4L451.818,84.0952" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3719 <text x="486.23" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key 1 suffix size (varint)</text>
3720 <polygon points="486.23,54 481.91,42.48 490.55,42.48" style="fill:rgb(0,0,0)"/>
3721 <path d="M486.23,32.4L486.23,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3722 <text x="591.466" y="119.52" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">key 1 prefix data</text>
3723 <polygon points="522.23,82.8 534.514,82.107 531.941,90.3549" style="fill:rgb(0,0,0)"/>
3724 <path d="M591.466,104.4L527.729,84.5155" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3725 </svg>
3726 </div>
3727 <p><i>Figure 1 - Term/Doclist Format
3728 </i></p></center>
3729
3730 <p>Each doclist identifies the rows (by their rowid values) that contain at
3731 least one instance of the term or term prefix and an associated position list,
3732 or "poslist" enumerating the position of each term instance within the row. In
3733 this sense a "position" is defined as a column number and term offset within
3734 the column value.
3735
3736 </p><p>Within a doclist, documents are always stored in order sorted by rowid. The
3737 first rowid in a doclist is stored as is, as a varint. It is immediately
3738 followed by its associated position list. Following this, the difference
3739 between the first rowid and the second, as a varint, followed by the doclist
3740 associated with the second rowid in the doclist. And so on.
3741
3742 </p><p>There is no way to determine the size of a doclist by parsing it. This must
3743 be stored externally. See the <a href="#data_pagination">section below</a> for
3744 details of how this is accomplished in FTS5.
3745
3746 </p><center><div style="max-width:900px;"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 900.979 85.68" data-pikchr-date="20250319161943">
3747 <path d="M51.8688,82.8L95.0688,82.8L95.0688,54L51.8688,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3748 <path d="M95.0688,82.8L383.069,82.8L383.069,54L95.0688,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3749 <text x="239.069" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">position list 0</text>
3750 <path d="M383.069,82.8L426.269,82.8L426.269,54L383.069,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3751 <path d="M426.269,82.8L714.269,82.8L714.269,54L426.269,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3752 <text x="570.269" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">position list 1</text>
3753 <path d="M714.269,82.8L757.469,82.8L757.469,54L714.269,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3754 <text x="828.144" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">position list 2...</text>
3755 <path d="M757.469,54L815.069,54" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3756 <path d="M757.469,82.8L815.069,82.8" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3757 <text x="73.4688" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">rowid 0 (varint)</text>
3758 <polygon points="73.4688,54 69.1488,42.48 77.7888,42.48" style="fill:rgb(0,0,0)"/>
3759 <path d="M73.4688,32.4L73.4688,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3760 <text x="404.669" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">rowid 1 (delta-encoded varint)</text>
3761 <polygon points="404.669,54 400.349,42.48 408.989,42.48" style="fill:rgb(0,0,0)"/>
3762 <path d="M404.669,32.4L404.669,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3763 <text x="735.869" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">rowid 3 (delta-encoded varint)</text>
3764 <polygon points="735.869,54 731.549,42.48 740.189,42.48" style="fill:rgb(0,0,0)"/>
3765 <path d="M735.869,32.4L735.869,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3766 </svg>
3767 </div>
3768 <p><i>Figure 2 - Doclist Format
3769 </i></p></center>
3770
3771 <p>A position list - often shortened to "poslist" - identifies the column
3772 and token offset within the row of each instance of the token in question.
3773 The format of a poslist is:
3774
3775 </p><ul>
3776 <li> Varint set to twice the size of the poslist, not including this field,
3777 plus one if the "delete" flag is set on the entry.
3778 </li><li> A (possibly empty) list of offsets for column 0 (the leftmost column) of
3779 the row. Each offset is stored as a varint. The first varint contains
3780 the value of the first offset, plus 2. The second variant contains the
3781 difference between the second and first offsets, plus 2. etc. For
3782 example, if the offset list is to contain offsets 0, 10, 15 and 16, it
3783 is encoded by packing the following values, encoded as varints, end to
3784 end:
3785 <pre>
3786 2, 12, 7, 3
3787 </pre>
3788 </li><li> For each column other than column 0 that contains one or more instances
3789 of the token:
3790 <ul>
3791 <li> Byte value 0x01.
3792 </li><li> The column number, as a varint.
3793 </li><li> An offset list, in the same format as the offset list for column 0.
3794 </li></ul>
3795 </li></ul>
3796
3797 <center><div style="max-width:791px;"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 791.366 144" data-pikchr-date="20250319161943">
3798 <path d="M83.6064,82.8L126.806,82.8L126.806,54L83.6064,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3799 <path d="M126.806,82.8L414.806,82.8L414.806,54L126.806,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3800 <text x="270.806" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">col 0 offset-list</text>
3801 <path d="M414.806,82.8L458.006,82.8L458.006,54L414.806,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3802 <text x="436.406" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">0x01</text>
3803 <path d="M458.006,82.8L501.206,82.8L501.206,54L458.006,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3804 <path d="M501.206,82.8L789.206,82.8L789.206,54L501.206,54Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3805 <text x="645.206" y="68.4" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">col i offset-list</text>
3806 <text x="105.206" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">nSize*2 + bDel (varint)</text>
3807 <polygon points="105.206,54 100.886,42.48 109.526,42.48" style="fill:rgb(0,0,0)"/>
3808 <path d="M105.206,32.4L105.206,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3809 <text x="479.606" y="17.28" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">column number (i)</text>
3810 <polygon points="479.606,54 475.286,42.48 483.926,42.48" style="fill:rgb(0,0,0)"/>
3811 <path d="M479.606,32.4L479.606,48.24" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3812 <path d="M126.806,97.2L126.806,111.6L789.206,111.6L789.206,97.2" style="fill:none;stroke-width:2.16;stroke-linejoin:round;stroke:rgb(0,0,0);" />
3813 <text x="458.006" y="126.72" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">nSize bytes</text>
3814 </svg>
3815 </div>
3816 <p><i>Figure 3 - Position List (poslist) With Offsets in Columns 0 and i
3817 </i></p></center>
3818
3819 <h4 id="data_pagination"><span>9.2.4.2. </span>Pagination</h4>
3820
3821 <p>If it is small enough (by default this means smaller than 4000 bytes), the
3822 entire contents of a segment b-tree may be stored in the key/doclist format
3823 described in the previous section as a single blob within the %_data table.
3824 Otherwise, the key/doclist is split into pages (by default, of approximately
3825 4000 bytes each) and stored in a contiguous set of entries in the %_data table
3826 (<a href="#data_structure">see above</a> for details).
3827
3828 </p><p>When a key/doclist is divided into pages, the following modifications are
3829 made to the format:
3830
3831 </p><ul>
3832 <li> A single varint or key data field never spans two pages.
3833 </li><li> The first key on each page is not prefix-compressed. It is stored in
3834 the format described above for the first key of a doclist - its size as
3835 a varint followed by the key data.
3836 </li><li> If there are one or more rowids on a page before the first key, then
3837 the first of them is not delta compressed. It is stored as is, just as
3838 if it were the first rowid of its doclist (which it may or may not be).
3839 </li></ul>
3840
3841 <p>Each page also has a fixed-size 4-byte header and a variably-sized footer.
3842 The header is divided into 2 16-bit big-endian integer fields. They
3843 contain:
3844
3845 </p><ul>
3846 <li> The byte offset of the first rowid value on the page, if it occurs
3847 before the first key, or 0 otherwise.
3848 </li><li> The byte offset of the page footer.
3849 </li></ul>
3850
3851 <p>The page footer consists of a series of varints containing the byte offset
3852 of each key that appears on the page. The page footer is zero bytes in size
3853 if there are no keys on the page.
3854
3855 </p><center><div style="max-width:732px;"><svg xmlns='http://www.w3.org/2000/svg' style='font-size:initial;' class="pikchr" viewBox="0 0 732.787 84.96" data-pikchr-date="20250319161943">
3856 <path d="M16.6176,30.96L59.8176,30.96L59.8176,2.16L16.6176,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3857 <text x="38.2176" y="16.56" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">hdr</text>
3858 <path d="M59.8176,30.96L635.818,30.96L635.818,2.16L59.8176,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3859 <text x="347.818" y="16.56" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">modified key/doclist data</text>
3860 <path d="M635.818,30.96L707.818,30.96L707.818,2.16L635.818,2.16Z" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3861 <text x="671.818" y="16.56" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">footer</text>
3862 <text x="38.2176" y="67.68" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">4 bytes</text>
3863 <text x="671.818" y="67.68" text-anchor="middle" fill="rgb(0,0,0)" dominant-baseline="central">variable size</text>
3864 <polygon points="38.2176,30.96 42.5376,42.48 33.8976,42.48" style="fill:rgb(0,0,0)"/>
3865 <path d="M38.2176,52.56L38.2176,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3866 <polygon points="671.818,30.96 676.138,42.48 667.498,42.48" style="fill:rgb(0,0,0)"/>
3867 <path d="M671.818,52.56L671.818,36.72" style="fill:none;stroke-width:2.16;stroke:rgb(0,0,0);" />
3868 </svg>
3869 </div>
3870 <p><i>Figure 4 - Page Format
3871 </i></p></center>
3872
3873 <h4 id="data_term_index"><span>9.2.4.3. </span>Segment Index Format</h4>
3874
3875 <p>The result of formatting the contents of the segment b-tree in the
3876 key/doclist format and then splitting it into pages is something very
3877 similar to the leaves of a b+tree. Instead of creating a format for
3878 the internal nodes of this b+tree and storing them in the %_data table
3879 alongside the leaves, the keys that would have been stored on such nodes are
3880 added to the %_idx table, defined as:
3881
3882 </p><div class="codeblock"><pre>CREATE TABLE %_idx(
3883 segid INTEGER, <i>-- segment id</i>
3884 term TEXT, <i>-- prefix of first key on page</i>
3885 pgno INTEGER, <i>-- (2*pgno + bDoclistIndex)</i>
3886 PRIMARY KEY(segid, term)
3887 );
3888 </pre></div>
3889
3890 <p>For each "leaf" page that contains at least one key, an entry is added
3891 to the %_idx table. Fields are set as follows:
3892
3893 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
3894 <tr style="text-align:left"><th>Column</th><th>Contents
3895 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>segid</td><td> The integer segment id.
3896 </td></tr><tr style="text-align:left"><td>term</td><td> The smallest prefix of the first key on the page that
3897 is larger than all keys on the previous page. For the
3898 first page in a segment, this prefix is zero bytes in
3899 size.
3900 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>pgno</td><td> This field encodes both the page number (within the
3901 segment - starting from 1) and the doclist index flag.
3902 The doclist index flag is set if the final key on the
3903 page has an <a href="#doclist_index_format">associated
3904 doclist index</a>. The value of this field is:
3905 <pre>
3906 (pgno*2 + bDoclistIndexFlag)
3907 </pre>
3908 </td></tr></table>
3909
3910 <p>Then, to find the leaf for segment i that may contain term t, instead of
3911 searching through internal nodes, FTS5 runs the query:
3912
3913 </p><div class="codeblock"><pre>SELECT pgno FROM %_idx WHERE segid=$i AND term>=$t ORDER BY term LIMIT 1
3914 </pre></div>
3915
3916 <h4 id="doclist_index_format"><span>9.2.4.4. </span>Doclist Index Format</h4>
3917
3918 <p>The segment index described in the <a href="#data_term_index">previous
3919 section</a> allows a segment b-tree to be efficiently queried by term or,
3920 assuming there is a prefix index of the required size, a term prefix. The data
3921 structure described in this section, doclist indexes, allows FTS5 to
3922 efficiently search for a rowid or range or rowids within the doclist associated
3923 with a single term or term prefix.
3924
3925 </p><p>Not all keys have associated doclist indexes. By default, a doclist index
3926 is only added for a key if its doclist spans more than 4 segment b-tree leaf
3927 pages. Doclist indexes are themselves b-trees, with both leaves and internal
3928 nodes stored as entries in the %_data table, but in practice most doclists are
3929 small enough to fit on a single leaf. FTS5 uses the same rough size for doclist
3930 index node and leaves as it does for segment b-tree leaves (by default 4000
3931 bytes).
3932
3933 </p><p>Doclist index leaves and internal nodes use the same page format. The first
3934 byte is a "flags" byte. This is set to 0x00 for the root page of the doclist
3935 index b-tree, and 0x01 for all other pages. The remainder of the page is a
3936 series of tightly packed varints, as follows:
3937
3938 </p><ul>
3939 <li> page number of leftmost child page, followed by
3940 </li><li> the smallest rowid value on the left most child page, followed by
3941 </li><li> one varint for each subsequent child page, containing the value:
3942 <ul>
3943 <li> 0x00 if there are no rowids on the child page (this can only happen
3944 when the "child" page is actually a segment b-tree leaf), or
3945 </li><li> the difference between the smallest rowid on the child page and
3946 the previous rowid value stored on the doclist index page.
3947 </li></ul>
3948 </li></ul>
3949
3950 <p>For the leftmost doclist index leaf in a doclist index, the leftmost child
3951 page is the first segment b-tree leaf after the one that contains the key
3952 itself.
3953
3954 </p><h2 id="document_sizes_table_docsize_table_"><span>9.3. </span>Document Sizes Table (%_docsize table)</h2>
3955
3956 <div class="codeblock"><pre>CREATE TABLE %_docsize(
3957 id INTEGER PRIMARY KEY, -- id of FTS5 row this record pertains to
3958 sz BLOB -- blob containing nCol packed varints
3959 );
3960 </pre></div>
3961
3962 <p>Many common search result ranking functions require as an input the size
3963 in tokens of the result document (as a search term hit in a short document is
3964 considered more significant than one in a long document). To provide fast
3965 access to this information, for each row in the FTS5 table there exists a
3966 corresponding record (with the same rowid) in the %_docsize shadow table
3967 that contains the size of each column value in the row, in tokens.
3968
3969 </p><p>The column value sizes are stored in a blob containing one packed varint for
3970 each column of the FTS5 table, from left to right. The varint contains, of
3971 course, the total number of tokens in the corresponding column value. Unindexed
3972 columns are included in this vector of varints; for them the value is always
3973 set to zero.
3974
3975 </p><p>This table is used by the <a href="#xColumnSize">xColumnSize</a> API. It can
3976 be omitted altogether by specifying the
3977 <a href="#the_columnsize_option">columnsize=0</a> option. In that case the
3978 xColumnSize API is still available to auxiliary functions, but runs much more
3979 slowly.
3980
3981 </p><h2 id="the_table_contents_content_table_"><span>9.4. </span>The Table Contents (%_content table)</h2>
3982
3983 <div class="codeblock"><pre><i>-- locale=0 (the default) table </i>
3984 CREATE TABLE %_content(id INTEGER PRIMARY KEY, c0, c1...);
3985
3986 <i>-- locale=1 table </i>
3987 CREATE TABLE %_content(id INTEGER PRIMARY KEY, c0, c1..., l0, l1...);
3988 </pre></div>
3989
3990 <p>The actual table content - the values inserted into the FTS5 table, is
3991 stored in the %_content table. This table is created with one "c*" column for
3992 each column of the FTS5 table, including any unindexed columns. The values for
3993 the leftmost FTS5 table column are stored in column "c0" of the %_content
3994 table, the values from the next FTS5 table column in column "c1", and so on.
3995
3996 </p><p>For an FTS5 table with <a href="#the_locale_option">the locale option</a> set
3997 to 1, the %_content table also contains one "l*" column for each indexed (i.e.
3998 not UNINDEXED) column of the table. For values that were written to the fts5
3999 table using the default locale, this column contains a NULL. Or, for values
4000 that were written with an associated locale (fts5_locale() values), this
4001 column contains the name of the locale, as text.
4002
4003 </p><p>Each "l*" column name has the same integer component as its associated "c*"
4004 column. This means that if the fts5 table has one or more UNINDEXED columns,
4005 the set of "l*" column names may not contain a contiguous set of integer
4006 components. For example:
4007
4008 </p><div class="codeblock"><pre><i>-- This fts5 table: </i>
4009 CREATE VIRTUAL TABLE ft USING fts5(a, b UNINDEXED, c, locale=1);
4010
4011 <i>-- uses a %_content table with no "l1" column:</i>
4012 CREATE TABLE ft_content(id INTEGER PRIMARY KEY, c0, c1, c2, l0, l2);
4013 </pre></div>
4014
4015 <p>Unless the
4016 <a href="#the_contentless_unindexed_option"">contentless_unindexed=1</a> option
4017 is specified, this table is omitted completely for <a href="#external_content_and_contentless_tables"> external content or
4018 contentless</a> FTS5 tables. For contentless tables that do specify the
4019 contentless_unindexed=1 option, the %_content table is created, but contains
4020 only those "c*" columns that correspond to UNINDEXED columns of the fts5
4021 table. For example:
4022
4023 </p><div class="codeblock"><pre><i>-- This fts5 table: </i>
4024 CREATE VIRTUAL TABLE ft USING fts5(a, b UNINDEXED, c, contentless_unindexed=1);
4025
4026 <i>-- uses a %_content table with only the "c1" (b) column</i>
4027 CREATE TABLE ft_content(id INTEGER PRIMARY KEY, c1);
4028 </pre></div>
4029
4030 <h2 id="configuration_options_config_table_"><span>9.5. </span>Configuration Options (%_config table)</h2>
4031
4032 <div class="codeblock"><pre>CREATE TABLE %_config(k PRIMARY KEY, v) WITHOUT ROWID;
4033 </pre></div>
4034
4035 <p>This table stores the values of any persistent configuration options.
4036 Column "k" stores the name of the option (text) and column "v" the value.
4037 Example contents:
4038
4039 </p><div class="codeblock"><pre>sqlite> SELECT * FROM ft_config;
4040 ┌─────────────┬──────┐
4041 │ k │ v │
4042 ├─────────────┼──────┤
4043 │ crisismerge │ 8 │
4044 │ pgsz │ 8000 │
4045 │ usermerge │ 4 │
4046 │ version │ 4 │
4047 └─────────────┴──────┘
4048 </pre></div>
4049
4050 <a name="appendix_a"></a>
4051 <h1 id="appendix_a" nonumber="1" tags="comparison with fts4">
4052 Appendix A: Comparison with FTS3/4
4053 </h1>
4054
4055 <p> Also available is the similar but more mature <a href="fts3.html">FTS3/4</a> module.
4056 FTS5 is a new version of FTS4 that includes various fixes and solutions for
4057 problems that could not be fixed in FTS4 without sacrificing backwards
4058 compatibility. Some of these problems are
4059 <a href="fts5.html#_summary_of_technical_differences_">described below</a>.
4060
4061 </p><h2 nonumber="1" id="_application_porting_guide_"> Application Porting Guide </h2>
4062
4063 <p> In order to use FTS5 instead of FTS3 or FTS4, applications usually require
4064 minimal modifications. Most of these fall into three categories - changes
4065 required to the CREATE VIRTUAL TABLE statement used to create the FTS table,
4066 changes required to SELECT queries used to execute queries against the table,
4067 and changes required to applications that use <a href="fts3.html#snippet">FTS auxiliary functions</a>.
4068
4069 </p><h3 nonumber="1" id="_changes_to_create_virtual_table_statements_"> Changes to CREATE VIRTUAL TABLE statements </h3>
4070
4071 <ol>
4072 <li> <p>The module name must be changed from "fts3" or "fts4" to "fts5".
4073
4074 </p></li><li> <p>All type information or constraint specifications must be removed from
4075 column definitions. FTS3/4 ignores everything following the column name in
4076 a column definition, FTS5 attempts to parse it (and will report an error
4077 if it fails to).
4078
4079 </p></li><li> <p>The "matchinfo=fts3" option is not available. The
4080 <a href="fts5.html#the_columnsize_option">"columnsize=0"</a> option is equivalent.
4081
4082 </p></li><li> <p>The notindexed= option is not available. Adding <a href="fts5.html#the_unindexed_column_option">UNINDEXED</a>
4083 to the column definition is equivalent.
4084
4085 </p></li><li> <p>The ICU tokenizer is not available.
4086
4087 </p></li><li> <p>The compress=, uncompress= and languageid= options are not available.
4088 There is as of yet no equivalent for their functionality.
4089 </p></li></ol>
4090
4091 <div class="codeblock"><pre><i> -- FTS3/4 statement </i>
4092 CREATE VIRTUAL TABLE ft USING fts4(
4093 linkid INTEGER,
4094 header CHAR(20),
4095 text VARCHAR,
4096 notindexed=linkid,
4097 matchinfo=fts3,
4098 tokenizer=unicode61
4099 );
4100
4101 <i> -- FTS5 equivalent (note - the "tokenizer=unicode61" option is not</i>
4102 <i> -- required as this is the default for FTS5 anyway)</i>
4103 CREATE VIRTUAL TABLE ft USING fts5(
4104 linkid UNINDEXED,
4105 header,
4106 text,
4107 columnsize=0
4108 );
4109 </pre></div>
4110
4111 <h3 nonumber="1" id="_changes_to_select_statements_"> Changes to SELECT statements </h3>
4112
4113 <ol>
4114 <li> <p>The "docid" alias does not exist. Applications must use "rowid"
4115 instead.
4116
4117 </p></li><li> <p>The behavior of queries when a column-filter is specified both as
4118 part of the FTS query and by using a column as the LHS of a MATCH
4119 operator is slightly different. For a table with columns "a" and "b"
4120 and a query similar to:
4121 </p><div class="codeblock"><pre>... a MATCH 'b: string'
4122 </pre></div>
4123 <p>FTS3/4 searches for matches in column "b". However, FTS5 always
4124 returns zero rows, as results are first filtered for column "b", then
4125 for column "a", leaving no results. In other words, in FTS3/4 the
4126 inner filter overrides the outer, in FTS5 both filters are applied.
4127
4128 </p></li><li> <p>The FTS query syntax (right hand side of the MATCH operator) has
4129 changed in some ways. The FTS5 syntax is quite close to the FTS4
4130 "enhanced syntax". The main difference is that FTS5 is fussier
4131 about unrecognized punctuation characters and similar within query
4132 strings. Most queries that work with FTS3/4 should also work with
4133 FTS5, and those that do not should return parse errors.
4134 </p></li></ol>
4135
4136 <h3 nonumber="1" id="_auxiliary_function_changes_"> Auxiliary Function Changes </h3>
4137
4138 <p> FTS5 has no matchinfo() or offsets() function, and the snippet() function
4139 is not as fully-featured as in FTS3/4. However, since FTS5 does provide
4140 an API allowing applications to create <a href="fts5.html#custom_auxiliary_functions_api_reference">custom auxiliary functions</a>, any
4141 required functionality may be implemented within the application code.
4142
4143 </p><p> The set of built-in auxiliary functions provided by FTS5 may be
4144 improved upon in the future.
4145
4146 </p><h3 nonumber="1" id="_other_issues"> Other Issues</h3>
4147
4148 <ol>
4149 <li><p> The functionality provided by the fts4aux module is now provided
4150 by <a href="fts5.html#the_fts5vocab_virtual_table_module">fts5vocab</a>. The schema of these two tables is slightly different.
4151
4152 </p></li><li><p> The FTS3/4 "merge=X,Y" command has been replaced by the
4153 <a href="fts5.html#the_merge_command">FTS5 merge command</a>.
4154
4155 </p></li><li><p> The FTS3/4 "automerge=X" command has been replaced by the
4156 <a href="fts5.html#the_automerge_configuration_option">FTS5 automerge option</a>.
4157 </p></li></ol>
4158
4159 <a name="_summary_of_technical_differences_"></a>
4160 <h2 nonumber="1" tags="fts5 technical differences" id="_summary_of_technical_differences_">
4161 Summary of Technical Differences
4162 </h2>
4163
4164 <p>FTS5 is similar to FTS3/4 in that the primary task of each is to maintain
4165 an index mapping from each unique token to a list of instances of that token
4166 within a set of documents, where each instance is identified by the document
4167 in which it appears and its position within that document. For example:
4168
4169 </p><div class="codeblock"><pre><i>-- Given the following SQL:</i>
4170 CREATE VIRTUAL TABLE ft USING fts5(a, b);
4171 INSERT INTO ft(rowid, a, b) VALUES(1, 'X Y', 'Y Z');
4172 INSERT INTO ft(rowid, a, b) VALUES(2, 'A Z', 'Y Y');
4173
4174 <i>-- The FTS5 module creates the following mapping on disk:</i>
4175 A --&gt; (2, 0, 0)
4176 X --&gt; (1, 0, 0)
4177 Y --&gt; (1, 0, 1) (1, 1, 0) (2, 1, 0) (2, 1, 1)
4178 Z --&gt; (1, 1, 1) (2, 0, 1)
4179 </pre></div>
4180
4181 <p>In the example above, each triple identifies the location of a token
4182 instance by rowid, column number (columns are numbered sequentially
4183 starting at 0 from left to right) and position within the column value (the
4184 first token in a column value is 0, the second is 1, and so on). Using this
4185 index, FTS5 is able to provide timely answers to queries such as "the set
4186 of all documents that contain the token 'A'", or "the set of all documents
4187 that contain the sequence 'Y Z'". The list of instances associated with a
4188 single token is called an "instance-list".
4189
4190 </p><p>The principle difference between FTS3/4 and FTS5 is that in FTS3/4,
4191 each instance-list is stored as a single large database record, whereas
4192 in FTS5 large instance-lists are divided between multiple database records.
4193 This has the following implications for dealing with large databases that
4194 contain large lists:
4195
4196 </p><ul>
4197 <li> <p>FTS5 is able to load instance-lists into memory incrementally in
4198 order to reduce memory usage and peak allocation size. FTS3/4 very
4199 often loads entire instance-lists into memory.
4200
4201 </p></li><li> <p>When processing queries that feature more than one token, FTS5 is
4202 sometimes able to determine that the query can be answered by
4203 inspecting a subset of a large instance-list. FTS3/4 almost always
4204 has to traverse entire instance-lists.
4205
4206 </p></li><li> If an instance-list grows so large that it exceeds
4207 the <a href="limits.html#max_length">SQLITE_MAX_LENGTH</a> limit, FTS3/4 is unable to handle it. FTS5
4208 does not have this problem.
4209 </li></ul>
4210
4211 <p>For these reasons, many complex queries may use less memory and run faster
4212 using FTS5.
4213
4214 </p><p>Some other ways in which FTS5 differs from FTS3/4 are:
4215
4216 </p><ul>
4217 <li> <p>FTS5 supports "ORDER BY rank" for returning results in order of
4218 decreasing relevancy.
4219
4220 </p></li><li> <p>FTS5 features an API allowing users to create custom auxiliary
4221 functions for advanced ranking and text processing applications. The
4222 special "rank" column may be mapped to a custom auxiliary function
4223 so that adding "ORDER BY rank" to a query works as expected.
4224
4225 </p></li><li> <p>FTS5 recognizes unicode separator characters and case equivalence by
4226 default. This is also possible using FTS3/4, but must be explicitly
4227 enabled.
4228
4229 </p></li><li> <p>The query syntax has been revised where necessary to remove
4230 ambiguities and to make it possible to escape special characters
4231 in query terms.
4232
4233 </p></li><li> <p>By default, FTS3/4 occasionally merges together two or more of the
4234 b-trees that make up its full-text index within an INSERT, UPDATE or
4235 DELETE statement executed by the user. This means that any operation
4236 on an FTS3/4 table may turn out to be surprisingly slow, as FTS3/4
4237 may unpredictably choose to merge together two or more large b-trees
4238 within it. FTS5 uses incremental merging by default, which limits
4239 the amount of processing that may take place within any given
4240 INSERT, UPDATE or DELETE operation.
4241 </p></li></ul>
4242 <p align="center"><small><i>This page was last updated on 2025-07-12 15:11:36Z </small></i></p>
4243