Debian Codegraph @

www/fts3.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 FTS3 and FTS4 Extensions</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 FTS3 and FTS4 Extensions
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction_to_fts3_and_fts4">1. Introduction to FTS3 and FTS4</a></div>
126 <div class="fancy-toc2"><a href="#differences_between_fts3_and_fts4">1.1. Differences between FTS3 and FTS4</a></div>
127 <div class="fancy-toc2"><a href="#creating_and_destroying_fts_tables">1.2. Creating and Destroying FTS Tables</a></div>
128 <div class="fancy-toc2"><a href="#populating_fts_tables">1.3. Populating FTS Tables</a></div>
129 <div class="fancy-toc2"><a href="#simple_fts_queries">1.4. Simple FTS Queries</a></div>
130 <div class="fancy-toc2"><a href="#summary">1.5. Summary</a></div>
131 <div class="fancy-toc1"><a href="#compiling_and_enabling_fts3_and_fts4">2. Compiling and Enabling FTS3 and FTS4</a></div>
132 <div class="fancy-toc1"><a href="#full_text_index_queries">3. Full-text Index Queries</a></div>
133 <div class="fancy-toc2"><a href="#_set_operations_using_the_enhanced_query_syntax">3.1.
134 Set Operations Using The Enhanced Query Syntax</a></div>
135 <div class="fancy-toc2"><a href="#set_operations_using_the_standard_query_syntax">3.2. Set Operations Using The Standard Query Syntax</a></div>
136 <div class="fancy-toc1"><a href="#auxiliary_functions_snippet_offsets_and_matchinfo">4. Auxiliary Functions - Snippet, Offsets and Matchinfo</a></div>
137 <div class="fancy-toc2"><a href="#the_offsets_function">4.1. The Offsets Function</a></div>
138 <div class="fancy-toc2"><a href="#the_snippet_function">4.2. The Snippet Function</a></div>
139 <div class="fancy-toc2"><a href="#matchinfo">4.3. The Matchinfo Function</a></div>
140 <div class="fancy-toc1"><a href="#fts4aux">5. Fts4aux - Direct Access to the Full-Text Index</a></div>
141 <div class="fancy-toc1"><a href="#fts4_options">6. FTS4 Options</a></div>
142 <div class="fancy-toc2"><a href="#the_compress_and_uncompress_options">6.1. The compress= and uncompress= options</a></div>
143 <div class="fancy-toc2"><a href="#the_content_option_">6.2. The content= option </a></div>
144 <div class="fancy-toc3"><a href="#_contentless_fts4_tables_">6.2.1. Contentless FTS4 Tables </a></div>
145 <div class="fancy-toc3"><a href="#_external_content_fts4_tables_">6.2.2. External Content FTS4 Tables </a></div>
146 <div class="fancy-toc2"><a href="#the_languageid_option">6.3. The languageid= option</a></div>
147 <div class="fancy-toc2"><a href="#the_matchinfo_option">6.4. The matchinfo= option</a></div>
148 <div class="fancy-toc2"><a href="#the_notindexed_option">6.5. The notindexed= option</a></div>
149 <div class="fancy-toc2"><a href="#the_prefix_option">6.6. The prefix= option</a></div>
150 <div class="fancy-toc1"><a href="#commands">7. Special Commands For FTS3 and FTS4</a></div>
151 <div class="fancy-toc2"><a href="#optimize">7.1. The "optimize" command</a></div>
152 <div class="fancy-toc2"><a href="#rebuild">7.2. The "rebuild" command</a></div>
153 <div class="fancy-toc2"><a href="#integcheck">7.3. The "integrity-check" command</a></div>
154 <div class="fancy-toc2"><a href="#mergecmd">7.4. The "merge=X,Y" command</a></div>
155 <div class="fancy-toc2"><a href="#automerge"">7.5. The "automerge=N" command</a></div>
156 <div class="fancy-toc1"><a href="#tokenizer">8. Tokenizers</a></div>
157 <div class="fancy-toc2"><a href="#custom_application_defined_tokenizers">8.1. Custom (Application Defined) Tokenizers</a></div>
158 <div class="fancy-toc2"><a href="#querying_tokenizers">8.2. Querying Tokenizers</a></div>
159 <div class="fancy-toc1"><a href="#data_structures">9. Data Structures</a></div>
160 <div class="fancy-toc2"><a href="#shadow_tables">9.1. Shadow Tables</a></div>
161 <div class="fancy-toc2"><a href="#variable_length_integer_varint_format">9.2. Variable Length Integer (varint) Format</a></div>
162 <div class="fancy-toc2"><a href="#segment_b_tree_format">9.3. Segment B-Tree Format</a></div>
163 <div class="fancy-toc3"><a href="#segment_b_tree_leaf_nodes">9.3.1. Segment B-Tree Leaf Nodes</a></div>
164 <div class="fancy-toc3"><a href="#segment_b_tree_interior_nodes">9.3.2. Segment B-Tree Interior Nodes</a></div>
165 <div class="fancy-toc2"><a href="#doclist_format">9.4. Doclist Format</a></div>
166 <div class="fancy-toc1"><a href="#limitations">10. Limitations</a></div>
167 <div class="fancy-toc2"><a href="#_utf_16_byte_order_mark_problem_">10.1. UTF-16 byte-order-mark problem </a></div>
168 <div class="fancy-toc1"><a href="#appendix_a">
169 Appendix A: Search Application Tips
170 </a></div>
171 </div>
172 </details>
173 </div>
174
175
176
177
178
179
180 <h2 id="overview" style="margin-left:1.0em" notoc="1"> Overview</h2>
181
182 <p>
183 FTS3 and FTS4 are SQLite virtual table modules that allow users to perform
184 full-text searches on a set of documents. The most common (and effective)
185 way to describe full-text searches is "what Google, Yahoo, and Bing do
186 with documents placed on the World Wide Web". Users input a term, or series
187 of terms, perhaps connected by a binary operator or grouped together into a
188 phrase, and the full-text query system finds the set of documents that best
189 matches those terms considering the operators and groupings the user has
190 specified. This article describes the deployment and usage of FTS3 and FTS4.
191
192 </p><p>
193 FTS1 and FTS2 are obsolete full-text search modules for SQLite. There are known
194 issues with these older modules and their use should be avoided.
195 Portions of the original FTS3 code were contributed to the SQLite project
196 by Scott Hess of <a href="http://www.google.com">Google</a>. It is now
197 developed and maintained as part of SQLite.
198
199 </p><h1 id="introduction_to_fts3_and_fts4"><span>1. </span>Introduction to FTS3 and FTS4</h1>
200
201 <p>
202 The FTS3 and FTS4 extension modules allow users to create special tables with a
203 built-in full-text index (hereafter "FTS tables"). The full-text index
204 allows the user to efficiently query the database for all rows that contain
205 one or more words (hereafter "tokens"), even if the table
206 contains many large documents.
207
208 </p><p>
209 For example, if each of the 517430 documents in the
210 "<a href="http://www.cs.cmu.edu/~enron/">Enron E-Mail Dataset</a>"
211 is inserted into both an FTS table and an ordinary SQLite table
212 created using the following SQL script:
213
214 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT); /* FTS3 table */
215 CREATE TABLE enrondata2(content TEXT); /* Ordinary table */
216 </pre></div>
217
218 <p>
219 Then either of the two queries below may be executed to find the number of
220 documents in the database that contain the word "linux" (351). Using one
221 desktop PC hardware configuration, the query on the FTS3 table returns in
222 approximately 0.03 seconds, versus 22.5 for querying the ordinary table.
223
224 </p><div class="codeblock"><pre>SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux'; /* 0.03 seconds */
225 SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */
226 </pre></div>
227
228 <p>
229 Of course, the two queries above are not entirely equivalent. For example
230 the LIKE query matches rows that contain terms such as "linuxophobe"
231 or "EnterpriseLinux" (as it happens, the Enron E-Mail Dataset does not
232 actually contain any such terms), whereas the MATCH query on the FTS3 table
233 selects only those rows that contain "linux" as a discrete token. Both
234 searches are case-insensitive. The FTS3 table consumes around 2006 MB on
235 disk compared to just 1453 MB for the ordinary table. Using the same
236 hardware configuration used to perform the SELECT queries above, the FTS3
237 table took just under 31 minutes to populate, versus 25 for the ordinary
238 table.
239
240 </p><h2 id="differences_between_fts3_and_fts4"><span>1.1. </span>Differences between FTS3 and FTS4</h2>
241 <a name="fts4"></a>
242
243
244 <p>
245 FTS3 and FTS4 are nearly identical. They share most of their code in common,
246 and their interfaces are the same. The differences are:
247
248 </p><ul>
249 <li> <p>FTS4 contains query performance optimizations that may significantly
250 improve the performance of full-text queries that contain terms that are
251 very common (present in a large percentage of table rows).
252
253 </p></li><li> <p>FTS4 supports some additional options that may be used with the
254 <a href="fts3.html#matchinfo">matchinfo()</a> function.
255
256 </p></li><li> <p>Because it stores extra information on disk in two new
257 <a href="fts3.html#*shadowtab">shadow tables</a> in order to support the performance
258 optimizations and extra matchinfo() options, FTS4 tables may consume more
259 disk space than the equivalent table created using FTS3. Usually the overhead
260 is 1-2% or less, but may be as high as 10% if the documents stored in the
261 FTS table are very small. The overhead may be reduced by specifying the
262 directive <a href="fts3.html#fts4matchinfo">"matchinfo=fts3"</a> as part of the FTS4 table
263 declaration, but this comes at the expense of sacrificing some of the
264 extra supported matchinfo() options.
265
266 </p></li><li> <p>FTS4 provides hooks (the compress and uncompress
267 <a href="fts3.html#fts4_options">options</a>) allowing data to be stored in a compressed
268 form, reducing disk usage and IO.
269 </p></li></ul>
270
271 <p>
272 FTS4 is an enhancement to FTS3.
273 FTS3 has been available since SQLite <a href="releaselog/3_5_0.html">version 3.5.0</a> (2007-09-04)
274 The enhancements for FTS4 were added with SQLite <a href="releaselog/3_7_4.html">version 3.7.4</a>
275 (2010-12-07).
276
277 </p><p>
278 Which module, FTS3 or FTS4, should you use in your application? FTS4 is
279 sometimes significantly faster than FTS3, even orders of magnitude faster
280 depending on the query, though in the common case the performance of the two
281 modules is similar. FTS4 also offers the enhanced <a href="fts3.html#matchinfo">matchinfo()</a> outputs which
282 can be useful in ranking the results of a <a href="fts3.html#full_text_index_queries">MATCH</a> operation. On the
283 other hand, in the absence of a <a href="fts3.html#fts4matchinfo">matchinfo=fts3</a> directive FTS4 requires a little
284 more disk space than FTS3, though only a percent of two in most cases.
285
286 </p><p>
287 For newer applications, FTS4 is recommended; though if compatibility with older
288 versions of SQLite is important, then FTS3 will usually serve just as well.
289
290 </p><h2 id="creating_and_destroying_fts_tables"><span>1.2. </span>Creating and Destroying FTS Tables</h2>
291
292 <p>
293 Like other virtual table types, new FTS tables are created using a
294 <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. The module name, which follows
295 the USING keyword, is either "fts3" or "fts4". The virtual table module arguments may
296 be left empty, in which case an FTS table with a single user-defined
297 column named "content" is created. Alternatively, the module arguments
298 may be passed a list of comma separated column names.
299
300 </p><p>
301 If column names are explicitly provided for the FTS table as part of
302 the CREATE VIRTUAL TABLE statement, then a datatype name may be optionally
303 specified for each column. This is pure syntactic sugar, the
304 supplied typenames are not used by FTS or the SQLite core for any
305 purpose. The same applies to any constraints specified along with an
306 FTS column name - they are parsed but not used or recorded by the system
307 in any way.
308
309 </p><div class="codeblock"><pre><i>-- Create an FTS table named "data" with one column - "content":</i>
310 CREATE VIRTUAL TABLE data USING fts3();
311
312 <i>-- Create an FTS table named "pages" with three columns:</i>
313 CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);
314
315 <i>-- Create an FTS table named "mail" with two columns. Datatypes
316 -- and column constraints are specified along with each column. These
317 -- are completely ignored by FTS and SQLite. </i>
318 CREATE VIRTUAL TABLE mail USING fts3(
319 subject VARCHAR(256) NOT NULL,
320 body TEXT CHECK(length(body)&lt;10240)
321 );
322 </pre></div>
323
324 <p>
325 As well as a list of columns, the module arguments passed to a CREATE
326 VIRTUAL TABLE statement used to create an FTS table may be used to specify
327 a <a href="fts3.html#tokenizer">tokenizer</a>. This is done by specifying a string of the form
328 "tokenize=&lt;tokenizer name&gt; &lt;tokenizer args&gt;" in place of a column
329 name, where &lt;tokenizer name&gt; is the name of the tokenizer to use and
330 &lt;tokenizer args&gt; is an optional list of whitespace separated qualifiers
331 to pass to the tokenizer implementation. A tokenizer specification may be
332 placed anywhere in the column list, but at most one tokenizer declaration is
333 allowed for each CREATE VIRTUAL TABLE statement. <a href="fts3.html#tokenizer">See below</a> for a
334 detailed description of using (and, if necessary, implementing) a tokenizer.
335
336 </p><div class="codeblock"><pre><i>-- Create an FTS table named "papers" with two columns that uses</i>
337 <i>-- the tokenizer "porter".</i>
338 CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);
339
340 <i>-- Create an FTS table with a single column - "content" - that uses</i>
341 <i>-- the "simple" tokenizer.</i>
342 CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);
343
344 <i>-- Create an FTS table with two columns that uses the "icu" tokenizer.</i>
345 <i>-- The qualifier "en_AU" is passed to the tokenizer implementation</i>
346 CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);
347 </pre></div>
348
349 <p>
350 FTS tables may be dropped from the database using an ordinary <a href="lang_droptable.html">DROP TABLE</a>
351 statement. For example:
352
353 </p><div class="codeblock"><pre><i>-- Create, then immediately drop, an FTS4 table.</i>
354 CREATE VIRTUAL TABLE data USING fts4();
355 DROP TABLE data;
356 </pre></div>
357
358 <h2 id="populating_fts_tables"><span>1.3. </span>Populating FTS Tables</h2>
359
360 <p>
361 FTS tables are populated using <a href="lang_insert.html">INSERT</a>, <a href="lang_update.html">UPDATE</a> and <a href="lang_delete.html">DELETE</a>
362 statements in the same way as ordinary SQLite tables are.
363
364 </p><p>
365 As well as the columns named by the user (or the "content" column if no
366 module arguments were specified as part of the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>
367 statement), each FTS table has a "rowid" column. The rowid of an FTS
368 table behaves in the same way as the rowid column of an ordinary SQLite
369 table, except that the values stored in the rowid column of an FTS table
370 remain unchanged if the database is rebuilt using the <a href="lang_vacuum.html">VACUUM</a> command.
371 For FTS tables, "docid" is allowed as an alias along with the usual "rowid",
372 "oid" and "_oid_" identifiers. Attempting to insert or update a row with a
373 docid value that already exists in the table is an error, just as it would
374 be with an ordinary SQLite table.
375
376 </p><p>
377 There is one other subtle difference between "docid" and the normal SQLite
378 aliases for the rowid column. Normally, if an INSERT or UPDATE statement
379 assigns discrete values to two or more aliases of the rowid column, SQLite
380 writes the rightmost of such values specified in the INSERT or UPDATE
381 statement to the database. However, assigning a non-NULL value to both
382 the "docid" and one or more of the SQLite rowid aliases when inserting or
383 updating an FTS table is considered an error. See below for an example.
384
385 </p><div class="codeblock"><pre><i>-- Create an FTS table</i>
386 CREATE VIRTUAL TABLE pages USING fts4(title, body);
387
388 <i>-- Insert a row with a specific docid value.</i>
389 INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...');
390
391 <i>-- Insert a row and allow FTS to assign a docid value using the same algorithm as</i>
392 <i>-- SQLite uses for ordinary tables. In this case the new docid will be 54,</i>
393 <i>-- one greater than the largest docid currently present in the table.</i>
394 INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');
395
396 <i>-- Change the title of the row just inserted.</i>
397 UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;
398
399 <i>-- Delete the entire table contents.</i>
400 DELETE FROM pages;
401
402 <i>-- The following is an error. It is not possible to assign non-NULL values to both</i>
403 <i>-- the rowid and docid columns of an FTS table.</i>
404 INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');
405 </pre></div>
406
407 <p>
408 To support full-text queries, FTS maintains an inverted index that maps
409 from each unique term or word that appears in the dataset to the locations
410 in which it appears within the table contents. For the curious, a
411 complete description of the <a href="fts3.html#data_structures">data structure</a> used to store
412 this index within the database file appears below. A feature of
413 this data structure is that at any time the database may contain not
414 one index b-tree, but several different b-trees that are incrementally
415 merged as rows are inserted, updated and deleted. This technique improves
416 performance when writing to an FTS table, but causes some overhead for
417 full-text queries that use the index. Evaluating the special <a href="fts3.html#*fts4optcmd">"optimize" command</a>,
418 an SQL statement of the
419 form "INSERT INTO &lt;fts-table&gt;(&lt;fts-table&gt;) VALUES('optimize')",
420 causes FTS to merge all existing index b-trees into a single large
421 b-tree containing the entire index. This can be an expensive operation,
422 but may speed up future queries.
423
424 </p><p>
425 For example, to optimize the full-text index for an FTS table named
426 "docs":
427
428 </p><div class="codeblock"><pre><i>-- Optimize the internal structure of FTS table "docs".</i>
429 INSERT INTO docs(docs) VALUES('optimize');
430 </pre></div>
431
432 <p>
433 The statement above may appear syntactically incorrect to some. Refer to
434 the section describing the <a href="fts3.html#simple_fts_queries">simple fts queries</a> for an explanation.
435
436 </p><p>
437 There is another, deprecated, method for invoking the optimize
438 operation using a SELECT statement. New code should use statements
439 similar to the INSERT above to optimize FTS structures.
440
441 </p><a name="simple_fts_queries"></a>
442 <h2 tags="simple fts queries" id="simple_fts_queries"><span>1.4. </span>Simple FTS Queries</h2>
443
444 <p>
445 As for all other SQLite tables, virtual or otherwise, data is retrieved
446 from FTS tables using a <a href="lang_select.html">SELECT</a> statement.
447
448 </p><p>
449 FTS tables can be queried efficiently using SELECT statements of two
450 different forms:
451
452 </p><ul>
453 <li><p>
454 <b>Query by rowid</b>. If the WHERE clause of the SELECT statement
455 contains a sub-clause of the form "rowid = ?", where ? is an SQL expression,
456 FTS is able to retrieve the requested row directly using the equivalent
457 of an SQLite <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> index.
458
459 </p></li><li><p>
460 <b>Full-text query</b>. If the WHERE clause of the SELECT statement contains
461 a sub-clause of the form "&lt;column&gt; MATCH ?", FTS is able to use
462 the built-in full-text index to restrict the search to those documents
463 that match the full-text query string specified as the right-hand operand
464 of the MATCH clause.
465 </p></li></ul>
466
467 <p>
468 If neither of these two query strategies can be used, all
469 queries on FTS tables are implemented using a linear scan of the entire
470 table. If the table contains large amounts of data, this may be an
471 impractical approach (the first example on this page shows that a linear
472 scan of 1.5 GB of data takes around 30 seconds using a modern PC).
473
474 </p><div class="codeblock"><pre><i>-- The examples in this block assume the following FTS table:</i>
475 CREATE VIRTUAL TABLE mail USING fts3(subject, body);
476
477 SELECT * FROM mail WHERE rowid = 15; <i>-- Fast. Rowid lookup.</i>
478 SELECT * FROM mail WHERE body MATCH 'sqlite'; <i>-- Fast. Full-text query.</i>
479 SELECT * FROM mail WHERE mail MATCH 'search'; <i>-- Fast. Full-text query.</i>
480 SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20; <i>-- Fast. Rowid lookup.</i>
481 SELECT * FROM mail WHERE subject = 'database'; <i>-- Slow. Linear scan.</i>
482 SELECT * FROM mail WHERE subject MATCH 'database'; <i>-- Fast. Full-text query.</i>
483 </pre></div>
484
485 <p>
486 In all of the full-text queries above, the right-hand operand of the MATCH
487 operator is a string consisting of a single term. In this case, the MATCH
488 expression evaluates to true for all documents that contain one or more
489 instances of the specified word ("sqlite", "search" or "database", depending
490 on which example you look at). Specifying a single term as the right-hand
491 operand of the MATCH operator results in the simplest and most common type
492 of full-text query possible. However more complicated queries are possible,
493 including phrase searches, term-prefix searches and searches for documents
494 containing combinations of terms occurring within a defined proximity of each
495 other. The various ways in which the full-text index may be queried are
496 <a href="fts3.html#full_text_index_queries">described below</a>.
497
498 </p><p>
499 Normally, full-text queries are case-insensitive. However, this
500 is dependent on the specific <a href="fts3.html#tokenizer">tokenizer</a> used by the FTS table
501 being queried. Refer to the section on <a href="fts3.html#tokenizer">tokenizers</a> for details.
502
503 </p><p>
504 The paragraph above notes that a MATCH operator with a simple term as the
505 right-hand operand evaluates to true for all documents that contain the
506 specified term. In this context, the "document" may refer to either the
507 data stored in a single column of a row of an FTS table, or to the contents
508 of all columns in a single row, depending on the identifier used as the
509 left-hand operand to the MATCH operator. If the identifier specified as
510 the left-hand operand of the MATCH operator is an FTS table column name,
511 then the document that the search term must be contained in is the value
512 stored in the specified column. However, if the identifier is the name
513 of the FTS <i>table</i> itself, then the MATCH operator evaluates to true
514 for each row of the FTS table for which any column contains the search
515 term. The following example demonstrates this:
516
517 </p><div class="codeblock"><pre><i>-- Example schema</i>
518 CREATE VIRTUAL TABLE mail USING fts3(subject, body);
519
520 <i>-- Example table population</i>
521 INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
522 INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
523 INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order', 'was a software problem');
524
525 <i>-- Example queries</i>
526 SELECT * FROM mail WHERE subject MATCH 'software'; <i>-- Selects rows 1 and 2</i>
527 SELECT * FROM mail WHERE body MATCH 'feedback'; <i>-- Selects row 2</i>
528 SELECT * FROM mail WHERE mail MATCH 'software'; <i>-- Selects rows 1, 2 and 3</i>
529 SELECT * FROM mail WHERE mail MATCH 'slow'; <i>-- Selects rows 1 and 3</i>
530 </pre></div>
531
532 <p>
533 At first glance, the final two full-text queries in the example above seem
534 to be syntactically incorrect, as there is a table name ("mail") used as
535 an SQL expression. The reason this is acceptable is that each FTS table
536 actually has a <a href="c3ref/declare_vtab.html">HIDDEN</a> column with the same name
537 as the table itself (in this case, "mail"). The value stored in this
538 column is not meaningful to the application, but can be used as the
539 left-hand operand to a MATCH operator. This special column may also be
540 passed as an argument to the <a href="fts3.html#snippet">FTS auxiliary functions</a>.
541
542 </p><p>
543 The following example illustrates the above. The expressions "docs",
544 "docs.docs" and "main.docs.docs" all refer to column "docs". However, the
545 expression "main.docs" does not refer to any column. It could be used to
546 refer to a table, but a table name is not allowed in the context in which
547 it is used below.
548
549 </p><div class="codeblock"><pre><i>-- Example schema</i>
550 CREATE VIRTUAL TABLE docs USING fts4(content);
551
552 <i>-- Example queries</i>
553 SELECT * FROM docs WHERE docs MATCH 'sqlite'; <i>-- OK.</i>
554 SELECT * FROM docs WHERE docs.docs MATCH 'sqlite'; <i>-- OK.</i>
555 SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite'; <i>-- OK.</i>
556 SELECT * FROM docs WHERE main.docs MATCH 'sqlite'; <i>-- Error.</i>
557 </pre></div>
558
559 <h2 id="summary"><span>1.5. </span>Summary</h2>
560
561 <p>
562 From the users point of view, FTS tables are similar to ordinary SQLite
563 tables in many ways. Data may be added to, modified within and removed
564 from FTS tables using the INSERT, UPDATE and DELETE commands just as
565 it may be with ordinary tables. Similarly, the SELECT command may be used
566 to query data. The following list summarizes the differences between FTS
567 and ordinary tables:
568
569 </p><ol>
570 <li><p>
571 As with all virtual table types, it is not possible to create indices or
572 triggers attached to FTS tables. Nor is it possible to use the ALTER TABLE
573 command to add extra columns to FTS tables (although it is possible to use
574 ALTER TABLE to rename an FTS table).
575
576 </p></li><li><p>
577 Data-types specified as part of the "CREATE VIRTUAL TABLE" statement
578 used to create an FTS table are ignored completely. Instead of the
579 normal rules for applying type <a href="datatype3.html#affinity">affinity</a> to inserted values, all
580 values inserted into FTS table columns (except the special rowid
581 column) are converted to type TEXT before being stored.
582
583 </p></li><li><p>
584 FTS tables permit the special alias "docid" to be used to refer to the
585 rowid column supported by all <a href="vtab.html">virtual tables</a>.
586
587 </p></li><li><p>
588 The <a href="fts3.html#full_text_index_queries">FTS MATCH</a> operator is supported for queries based on the built-in
589 full-text index.
590
591 </p></li><li><p>
592 The <a href="fts3.html#snippet">FTS auxiliary functions</a>, <a href="fts3.html#snippet">snippet()</a>, <a href="fts3.html#offsets">offsets()</a>, and <a href="fts3.html#matchinfo">matchinfo()</a> are
593 available to support full-text queries.
594
595 </p></li><li><p>
596 <a name="hiddencol"></a>
597
598 Every FTS table has a <a href="vtab.html#hiddencol">hidden column</a> with the
599 same name as the table itself. The value contained in each row for the
600 hidden column is a blob that is only useful as the left operand of a
601 <a href="fts3.html#full_text_index_queries">MATCH</a> operator, or as the left-most argument to one
602 of the <a href="fts3.html#snippet">FTS auxiliary functions</a>.
603
604
605 </p></li></ol>
606
607
608 <a name="compiling_and_enabling_fts3_and_fts4"></a>
609 <h1 tags="compile fts" id="compiling_and_enabling_fts3_and_fts4"><span>2. </span>Compiling and Enabling FTS3 and FTS4</h1>
610
611 <p>
612 Although FTS3 and FTS4 are included with the SQLite core source code, they are not
613 enabled by default. To build SQLite with FTS functionality enabled, define
614 the preprocessor macro <a href="compile.html#enable_fts3">SQLITE_ENABLE_FTS3</a> when compiling. New applications
615 should also define the <a href="compile.html#enable_fts3_parenthesis">SQLITE_ENABLE_FTS3_PARENTHESIS</a> macro to enable the
616 <a href="fts3.html#_set_operations_using_the_enhanced_query_syntax">enhanced query syntax</a> (see below). Usually, this is done by adding the
617 following two switches to the compiler command line:
618
619 </p><div class="codeblock"><pre>-DSQLITE_ENABLE_FTS3
620 -DSQLITE_ENABLE_FTS3_PARENTHESIS
621 </pre></div>
622
623 <p>
624 Note that enabling FTS3 also makes FTS4 available. There is not a separate
625 SQLITE_ENABLE_FTS4 compile-time option. A build of SQLite either supports
626 both FTS3 and FTS4 or it supports neither.
627
628 </p><p>
629 If using the canonical build system, setting the CPPFLAGS
630 environment variable while running the 'configure' script is an easy
631 way to set these macros. For example, the following command:
632
633 </p><div class="codeblock"><pre>CPPFLAGS="-DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure --enable-fts3 &lt;configure options&gt;
634 </pre></div>
635
636 <p>
637 where <i>&lt;configure options&gt;</i> are those options normally passed to
638 the configure script, if any.
639
640 </p><p>
641 Because FTS3 and FTS4 are virtual tables, The <a href="compile.html#enable_fts3">SQLITE_ENABLE_FTS3</a> compile-time option
642 is incompatible with the <a href="compile.html#omit_virtualtable">SQLITE_OMIT_VIRTUALTABLE</a> option.
643
644 </p><p>
645 If a build of SQLite does not include the FTS modules, then any attempt to prepare an
646 SQL statement to create an FTS3 or FTS4 table or to drop or access an existing
647 FTS table in any way will fail. The error message returned will be similar
648 to "no such module: ftsN" (where N is either 3 or 4).
649
650 </p><p>
651 If the C version of the <a href="https://icu.unicode.org">ICU library</a>
652 is available, then FTS may also be compiled with the SQLITE_ENABLE_ICU
653 pre-processor macro defined. Compiling with this macro enables an FTS
654 <a href="fts3.html#tokenizer">tokenizer</a> that uses the ICU library to split a document into terms
655 (words) using the conventions for a specified language and locale.
656
657 </p><div class="codeblock"><pre>-DSQLITE_ENABLE_ICU
658 </pre></div>
659
660 <p>
661 In the canonical build tree version 3.48 or higher ICU can be enabled
662 using configure script flags: see <code>./configure --help</code> for
663 details.
664
665
666 </p><a name="full_text_index_queries"></a>
667 <h1 tags="FTS MATCH" id="full_text_index_queries"><span>3. </span>Full-text Index Queries</h1>
668
669 <p>
670 The most useful thing about FTS tables is the queries that may be
671 performed using the built-in full-text index. Full-text queries are
672 performed by specifying a clause of the form
673 "&lt;column&gt; MATCH &lt;full-text query expression&gt;" as part of the WHERE
674 clause of a SELECT statement that reads data from an FTS table.
675 <a href="fts3.html#simple_fts_queries">Simple FTS queries</a> that return all documents that
676 contain a given term are described above. In that discussion the right-hand
677 operand of the MATCH operator was assumed to be a string consisting of a
678 single term. This section describes the more complex query types supported
679 by FTS tables, and how they may be utilized by specifying a more
680 complex query expression as the right-hand operand of a MATCH operator.
681
682 </p><p>
683 FTS tables support three basic query types:
684
685 </p><ul>
686 <a name="termprefix"></a>
687
688 <li><p><b>Token or token prefix queries</b>.
689 An FTS table may be queried for all documents that contain a specified
690 term (the <a href="fts3.html#simple_fts_queries">simple case</a> described above), or for
691 all documents that contain a term with a specified prefix. As we have
692 seen, the query expression for a specific term is simply the term itself.
693 The query expression used to search for a term prefix is the prefix
694 itself with a '*' character appended to it. For example:
695 </p></li></ul>
696
697 <div class="codeblock"><pre><i>-- Virtual table declaration</i>
698 CREATE VIRTUAL TABLE docs USING fts3(title, body);
699
700 <i>-- Query for all documents containing the term "linux":</i>
701 SELECT * FROM docs WHERE docs MATCH 'linux';
702
703 <i>-- Query for all documents containing a term with the prefix "lin". This will match</i>
704 <i>-- all documents that contain "linux", but also those that contain terms "linear",</i>
705 <i>--"linker", "linguistic" and so on.</i>
706 SELECT * FROM docs WHERE docs MATCH 'lin*';
707 </pre></div>
708
709 <ul>
710 <li style="list-style:none"><p>
711 Normally, a token or token prefix query is matched against the FTS table
712 column specified as the left-hand side of the MATCH operator. Or, if the
713 special column with the same name as the FTS table itself is specified,
714 against all columns. This may be overridden by specifying a column-name
715 followed by a ":" character before a basic term query. There may be space
716 between the ":" and the term to query for, but not between the column-name
717 and the ":" character. For example:
718 </p></li></ul>
719
720 <div class="codeblock"><pre><i>-- Query the database for documents for which the term "linux" appears in</i>
721 <i>-- the document title, and the term "problems" appears in either the title</i>
722 <i>-- or body of the document.</i>
723 SELECT * FROM docs WHERE docs MATCH 'title:linux problems';
724
725 <i>-- Query the database for documents for which the term "linux" appears in</i>
726 <i>-- the document title, and the term "driver" appears in the body of the document</i>
727 <i>-- ("driver" may also appear in the title, but this alone will not satisfy the</i>
728 <i>-- query criteria).</i>
729 SELECT * FROM docs WHERE body MATCH 'title:linux driver';
730 </pre></div>
731
732 <ul>
733 <li style="list-style:none"><p>
734 If the FTS table is an FTS4 table (not FTS3), a token may also be prefixed
735 with a "&#94;" character. In this case, in order to match the token must
736 appear as the very first token in any column of the matching row. Examples:
737 </p></li></ul>
738
739 <div class="codeblock"><pre><i>-- All documents for which "linux" is the first token of at least one</i>
740 <i>-- column.</i>
741 SELECT * FROM docs WHERE docs MATCH '&#94;linux';
742
743 <i>-- All documents for which the first token in column "title" begins with "lin".</i>
744 SELECT * FROM docs WHERE body MATCH 'title: &#94;lin*';
745 </pre></div>
746
747 <a name="phrase"></a>
748
749 <ul>
750 <li><p><b>Phrase queries</b>.
751 A phrase query is a query that retrieves all documents that contain a
752 nominated set of terms or term prefixes in a specified order with no
753 intervening tokens. Phrase queries are specified by enclosing a space
754 separated sequence of terms or term prefixes in double quotes (").
755 For example:
756 </p></li></ul>
757
758 <div class="codeblock"><pre><i>-- Query for all documents that contain the phrase "linux applications".</i>
759 SELECT * FROM docs WHERE docs MATCH '"linux applications"';
760
761 <i>-- Query for all documents that contain a phrase that matches "lin* app*". As well as</i>
762 <i>-- "linux applications", this will match common phrases such as "linoleum appliances"</i>
763 <i>-- or "link apprentice".</i>
764 SELECT * FROM docs WHERE docs MATCH '"lin* app*"';
765 </pre></div>
766
767 <a name="near"></a>
768
769 <ul>
770 <li><p><b>NEAR queries</b>.
771 A NEAR query is a query that returns documents that contain a two or
772 more nominated terms or phrases within a specified proximity of each
773 other (by default with 10 or less intervening terms). A NEAR query is
774 specified by putting the keyword "NEAR" between two phrase, token or
775 token prefix queries. To specify a proximity other than the default,
776 an operator of the form "NEAR/<i>&lt;N&gt;</i>" may be used, where
777 <i>&lt;N&gt;</i> is the maximum number of intervening terms allowed.
778 For example:
779 </p></li></ul>
780
781 <div class="codeblock"><pre><i>-- Virtual table declaration.</i>
782 CREATE VIRTUAL TABLE docs USING fts4();
783
784 <i>-- Virtual table data.</i>
785 INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system');
786
787 <i>-- Search for a document that contains the terms "sqlite" and "database" with</i>
788 <i>-- not more than 10 intervening terms. This matches the only document in</i>
789 <i>-- table docs (since there are only six terms between "SQLite" and "database"</i>
790 <i>-- in the document)</i>.
791 SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';
792
793 <i>-- Search for a document that contains the terms "sqlite" and "database" with</i>
794 <i>-- not more than 6 intervening terms. This also matches the only document in</i>
795 <i>-- table docs. Note that the order in which the terms appear in the document</i>
796 <i>-- does not have to be the same as the order in which they appear in the query.</i>
797 SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite';
798
799 <i>-- Search for a document that contains the terms "sqlite" and "database" with</i>
800 <i>-- not more than 5 intervening terms. This query matches no documents.</i>
801 SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite';
802
803 <i>-- Search for a document that contains the phrase "ACID compliant" and the term</i>
804 <i>-- "database" with not more than 2 terms separating the two. This matches the</i>
805 <i>-- document stored in table docs.</i>
806 SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"';
807
808 <i>-- Search for a document that contains the phrase "ACID compliant" and the term</i>
809 <i>-- "sqlite" with not more than 2 terms separating the two. This also matches</i>
810 <i>-- the only document stored in table docs.</i>
811 SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite';
812 </pre></div>
813
814 <ul>
815 <li style="list-style: none"><p>
816 More than one NEAR operator may appear in a single query. In this case each
817 pair of terms or phrases separated by a NEAR operator must appear within the
818 specified proximity of each other in the document. Using the same table and
819 data as in the block of examples above:
820 </p></li></ul>
821
822 <div class="codeblock"><pre><i>-- The following query selects documents that contains an instance of the term </i>
823 <i>-- "sqlite" separated by two or fewer terms from an instance of the term "acid",</i>
824 <i>-- which is in turn separated by two or fewer terms from an instance of the term</i>
825 <i>-- "relational".</i>
826 SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';
827
828 <i>-- This query matches no documents. There is an instance of the term "sqlite" with</i>
829 <i>-- sufficient proximity to an instance of "acid" but it is not sufficiently close</i>
830 <i>-- to an instance of the term "relational".</i>
831 SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';
832 </pre></div>
833
834 <p>
835 Phrase and NEAR queries may not span multiple columns within a row.
836
837 </p><p>
838 The three basic query types described above may be used to query the full-text
839 index for the set of documents that match the specified criteria. Using the
840 FTS query expression language it is possible to perform various set
841 operations on the results of basic queries. There are currently three
842 supported operations:
843
844 </p><ul>
845 <li> The AND operator determines the <b>intersection</b> of two sets of documents.
846
847 </li><li> The OR operator calculates the <b>union</b> of two sets of documents.
848
849 </li><li> The NOT operator (or, if using the standard syntax, a unary "-" operator)
850 may be used to compute the <b>relative complement</b> of one set of
851 documents with respect to another.
852 </li></ul>
853
854 <p>
855 The FTS modules may be compiled to use one of two slightly different versions
856 of the full-text query syntax, the "standard" query syntax and the "enhanced"
857 query syntax. The basic term, term-prefix, phrase and NEAR queries described
858 above are the same in both versions of the syntax. The way in which set
859 operations are specified is slightly different. The following two sub-sections
860 describe the part of the two query syntaxes that pertains to set operations.
861 Refer to the description of how to <a href="fts3.html#compiling_and_enabling_fts3_and_fts4">compile fts</a> for compilation notes.
862
863 </p><a name="_set_operations_using_the_enhanced_query_syntax"></a>
864 <h2 tags="enhanced query syntax" id="_set_operations_using_the_enhanced_query_syntax"><span>3.1. </span>
865 Set Operations Using The Enhanced Query Syntax</h2>
866
867 <p>
868 The enhanced query syntax supports the AND, OR and NOT binary set operators.
869 Each of the two operands to an operator may be a basic FTS query, or the
870 result of another AND, OR&nbsp;or NOT set operation. Operators must be entered
871 using capital letters. Otherwise, they are interpreted as basic term queries
872 instead of set operators.
873
874 </p><p>
875 The AND operator may be implicitly specified. If two basic queries appear
876 with no operator separating them in an FTS query string, the results are
877 the same as if the two basic queries were separated by an AND operator.
878 For example, the query expression "implicit operator" is a more succinct
879 version of "implicit AND operator".
880
881 </p><div class="codeblock"><pre><i>-- Virtual table declaration</i>
882 CREATE VIRTUAL TABLE docs USING fts3();
883
884 <i>-- Virtual table data</i>
885 INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system');
886 INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system');
887 INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');
888
889 <i>-- Return the set of documents that contain the term "sqlite", and the</i>
890 <i>-- term "database". This query will return the document with docid 3 only.</i>
891 SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';
892
893 <i>-- Again, return the set of documents that contain both "sqlite" and</i>
894 <i>-- "database". This time, use an implicit AND operator. Again, document</i>
895 <i>-- 3 is the only document matched by this query. </i>
896 SELECT * FROM docs WHERE docs MATCH 'database sqlite';
897
898 <i>-- Query for the set of documents that contains either "sqlite" or "database".</i>
899 <i>-- All three documents in the database are matched by this query.</i>
900 SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';
901
902 <i>-- Query for all documents that contain the term "database", but do not contain</i>
903 <i>-- the term "sqlite". Document 1 is the only document that matches this criteria.</i>
904 SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';
905
906 <i>-- The following query matches no documents. Because "and" is in lowercase letters,</i>
907 <i>-- it is interpreted as a basic term query instead of an operator. Operators must</i>
908 <i>-- be specified using capital letters. In practice, this query will match any documents</i>
909 <i>-- that contain each of the three terms "database", "and" and "sqlite" at least once.</i>
910 <i>-- No documents in the example data above match this criteria.</i>
911 SELECT * FROM docs WHERE docs MATCH 'database and sqlite';
912 </pre></div>
913
914 <p>
915 The examples above all use basic full-text term queries as both operands of
916 the set operations demonstrated. Phrase and NEAR queries may also be used,
917 as may the results of other set operations. When more than one set operation
918 is present in an FTS query, the precedence of operators is as follows:
919
920 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
921 <tr style="text-align:left"><th>Operator</th><th>Enhanced Query Syntax Precedence
922 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>NOT </td><td> Highest precedence (tightest grouping).
923 </td></tr><tr style="text-align:left"><td>AND </td><td>
924 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>OR </td><td> Lowest precedence (loosest grouping).
925 </td></tr></table>
926
927 <p>
928 When using the enhanced query syntax, parenthesis may be used to override
929 the default precedence of the various operators. For example:
930
931 </p><div class="codeblock"><pre><i>-- Return the docid values associated with all documents that contain the</i>
932 <i>-- two terms "sqlite" and "database", and/or contain the term "library".</i>
933 SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library';
934
935 <i>-- This query is equivalent to the above.</i>
936 SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
937 UNION
938 SELECT docid FROM docs WHERE docs MATCH 'library';
939
940 <i>-- Query for the set of documents that contains the term "linux", and at least</i>
941 <i>-- one of the phrases "sqlite database" and "sqlite library".</i>
942 SELECT docid FROM docs WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';
943
944 <i>-- This query is equivalent to the above.</i>
945 SELECT docid FROM docs WHERE docs MATCH 'linux'
946 INTERSECT
947 SELECT docid FROM (
948 SELECT docid FROM docs WHERE docs MATCH '"sqlite library"'
949 UNION
950 SELECT docid FROM docs WHERE docs MATCH '"sqlite database"'
951 );
952 </pre></div>
953
954
955 <h2 id="set_operations_using_the_standard_query_syntax"><span>3.2. </span>Set Operations Using The Standard Query Syntax</h2>
956
957 <p>
958 FTS query set operations using the standard query syntax are similar, but
959 not identical, to set operations with the enhanced query syntax. There
960 are four differences, as follows:
961
962 </p><ol>
963 <li value="1"><p> Only the implicit version of the AND operator is supported.
964 Specifying the string "AND" as part of a standard query syntax query is
965 interpreted as a term query for the set of documents containing the term
966 "and".
967 </p></li></ol>
968
969 <ol>
970 <li value="2"><p> Parentheses are not supported.
971 </p></li></ol>
972
973 <ol>
974 <li value="3"><p> The NOT operator is not supported. Instead of the NOT
975 operator, the standard query syntax supports a unary "-" operator that
976 may be applied to basic term and term-prefix queries (but not to phrase
977 or NEAR queries). A term or term-prefix that has a unary "-" operator
978 attached to it may not appear as an operand to an OR operator. An FTS
979 query may not consist entirely of terms or term-prefix queries with unary
980 "-" operators attached to them.
981 </p></li></ol>
982
983 <div class="codeblock"><pre><i>-- Search for the set of documents that contain the term "sqlite" but do</i>
984 <i>-- not contain the term "database".</i>
985 SELECT * FROM docs WHERE docs MATCH 'sqlite -database';
986 </pre></div>
987
988 <ol>
989 <li value="4"><p> The relative precedence of the set operations is different.
990 In particular, using the standard query syntax the "OR" operator has a
991 higher precedence than "AND". The precedence of operators when using the
992 standard query syntax is:
993 </p></li></ol>
994
995 <table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
996 <tr style="text-align:left"><th>Operator</th><th>Standard Query Syntax Precedence
997 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>Unary "-" </td><td> Highest precedence (tightest grouping).
998 </td></tr><tr style="text-align:left"><td>OR </td><td>
999 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>AND </td><td> Lowest precedence (loosest grouping).
1000 </td></tr></table>
1001
1002 <ol><li style="list-style:none">
1003 The following example illustrates precedence of operators using the standard
1004 query syntax:
1005 </li></ol>
1006
1007 <div class="codeblock"><pre><i>-- Search for documents that contain at least one of the terms "database"</i>
1008 <i>-- and "sqlite", and also contain the term "library". Because of the differences</i>
1009 <i>-- in operator precedences, this query would have a different interpretation using</i>
1010 <i>-- the enhanced query syntax.</i>
1011 SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';
1012 </pre></div>
1013
1014 <a name="snippet"></a>
1015
1016 <h1 id="auxiliary_functions_snippet_offsets_and_matchinfo"><span>4. </span>Auxiliary Functions - Snippet, Offsets and Matchinfo</h1>
1017
1018 <p>
1019 The FTS3 and FTS4 modules provide three special SQL scalar functions that may be useful
1020 to the developers of full-text query systems: "snippet", "offsets" and
1021 "matchinfo". The purpose of the "snippet" and "offsets" functions is to allow
1022 the user to identify the location of queried terms in the returned documents.
1023 The "matchinfo" function provides the user with metrics that may be useful
1024 for filtering or sorting query results according to relevance.
1025
1026 </p><p>
1027 The first argument to all three special SQL scalar functions
1028 must be the <a href="fts3.html#hiddencol">FTS hidden column</a> of the FTS table that the function is
1029 applied to. The <a href="fts3.html#hiddencol">FTS hidden column</a> is an automatically-generated column found on
1030 all FTS tables that has the same name as the FTS table itself.
1031 For example, given an FTS table named "mail":
1032
1033 </p><div class="codeblock"><pre>SELECT offsets(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
1034 SELECT snippet(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
1035 SELECT matchinfo(mail) FROM mail WHERE mail MATCH &lt;full-text query expression&gt;;
1036 </pre></div>
1037
1038 <p>
1039 The three auxiliary functions are only useful within a SELECT statement that
1040 uses the FTS table's full-text index. If used within a SELECT that uses
1041 the "query by rowid" or "linear scan" strategies, then the snippet and
1042 offsets both return an empty string, and the matchinfo function returns
1043 a blob value zero bytes in size.
1044
1045 <a name="matchable"></a>
1046
1047 </p><p id="matchable">
1048 All three auxiliary functions extract a set of "matchable phrases" from
1049 the FTS query expression to work with. The set of matchable phrases for
1050 a given query consists of all phrases (including unquoted tokens and
1051 token prefixes) in the expression except those that are prefixed with
1052 a unary "-" operator (standard syntax) or are part of a sub-expression
1053 that is used as the right-hand operand of a NOT operator.
1054
1055 </p><p>
1056 With the following provisos, each series of tokens in the FTS table that
1057 matches one of the matchable phrases in the query expression is known as a
1058 "phrase match":
1059
1060 </p><ol>
1061 <li> If a matchable phrase is part of a series of phrases connected by
1062 NEAR operators in the FTS query expression, then each phrase match
1063 must be sufficiently close to other phrase matches of the relevant
1064 types to satisfy the NEAR condition.
1065
1066 </li><li> If the matchable phrase in the FTS query is restricted to matching
1067 data in a specified FTS table column, then only phrase matches that
1068 occur within that column are considered.
1069 </li></ol>
1070
1071 <a name="offsets"></a>
1072
1073 <h2 id="the_offsets_function"><span>4.1. </span>The Offsets Function</h2>
1074
1075 <p>
1076 For a SELECT query that uses the full-text index, the offsets() function
1077 returns a text value containing a series of space-separated integers. For
1078 each term in each <a href="#matchable">phrase match</a> of the current row,
1079 there are four integers in the returned list. Each set of four integers is
1080 interpreted as follows:
1081
1082 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
1083 <tr style="text-align:left"><th>Integer </th><th>Interpretation
1084 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>0
1085 </td><td>The column number that the term instance occurs in (0 for the
1086 leftmost column of the FTS table, 1 for the next leftmost, etc.).
1087 </td></tr><tr style="text-align:left"><td>1
1088 </td><td>The term number of the matching term within the full-text query
1089 expression. Terms within a query expression are numbered starting
1090 from 0 in the order that they occur.
1091 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>2
1092 </td><td>The byte offset of the matching term within the column.
1093 </td></tr><tr style="text-align:left"><td>3
1094 </td><td>The size of the matching term in bytes.
1095 </td></tr></table>
1096
1097 <p>
1098 The following block contains examples that use the offsets function.
1099
1100 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE mail USING fts3(subject, body);
1101 INSERT INTO mail VALUES('hello world', 'This message is a hello world message.');
1102 INSERT INTO mail VALUES('urgent: serious', 'This mail is seen as a more serious mail');
1103
1104 <i>-- The following query returns a single row (as it matches only the first</i>
1105 <i>-- entry in table "mail". The text returned by the offsets function is</i>
1106 <i>-- "0 0 6 5 1 0 24 5".</i>
1107 <i>--</i>
1108 <i>-- The first set of four integers in the result indicate that column 0</i>
1109 <i>-- contains an instance of term 0 ("world") at byte offset 6. The term instance</i>
1110 <i>-- is 5 bytes in size. The second set of four integers shows that column 1</i>
1111 <i>-- of the matched row contains an instance of term 0 ("world") at byte offset</i>
1112 <i>-- 24. Again, the term instance is 5 bytes in size.</i>
1113 SELECT offsets(mail) FROM mail WHERE mail MATCH 'world';
1114
1115 <i>-- The following query returns also matches only the first row in table "mail".</i>
1116 <i>-- In this case the returned text is "1 0 5 7 1 0 30 7".</i>
1117 SELECT offsets(mail) FROM mail WHERE mail MATCH 'message';
1118
1119 <i>-- The following query matches the second row in table "mail". It returns the</i>
1120 <i>-- text "1 0 28 7 1 1 36 4". Only those occurrences of terms "serious" and "mail"</i>
1121 <i>-- that are part of an instance of the phrase "serious mail" are identified; the</i>
1122 <i>-- other occurrences of "serious" and "mail" are ignored.</i>
1123 SELECT offsets(mail) FROM mail WHERE mail MATCH '"serious mail"';
1124 </pre></div>
1125
1126 <a name="snippet"></a>
1127
1128 <h2 id="the_snippet_function"><span>4.2. </span>The Snippet Function</h2>
1129
1130 <p>
1131 The snippet function is used to create formatted fragments of document text
1132 for display as part of a full-text query results report. The snippet function
1133 may be passed between one and six arguments, as follows:
1134
1135 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
1136 <tr style="text-align:left"><th>Argument </th><th>Default Value </th><th>Description
1137 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>0 </td><td>N/A
1138 </td><td> The first argument to the snippet function must always be the <a href="fts3.html#hiddencol">FTS hidden column</a>
1139 of the FTS table being queried and from which the snippet is to be taken. The
1140 <a href="fts3.html#hiddencol">FTS hidden column</a> is an automatically generated column with the same name as the
1141 FTS table itself.
1142 </td></tr><tr style="text-align:left"><td>1 </td><td>"&lt;b&gt;"
1143 </td><td> The "start match" text.
1144 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>2 </td><td>"&lt;/b&gt;"
1145 </td><td> The "end match" text.
1146 </td></tr><tr style="text-align:left"><td>3 </td><td>"&lt;b&gt;...&lt;/b&gt;"
1147 </td><td> The "ellipses" text.
1148 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>4 </td><td>-1
1149 </td><td> The FTS table column number to extract the returned fragments of
1150 text from. Columns are numbered from left to right starting with
1151 zero. A negative value indicates that the text may be extracted
1152 from any column.
1153 </td></tr><tr style="text-align:left"><td>5 </td><td>-15
1154 </td><td> The absolute value of this integer argument is used as the
1155 (approximate) number of tokens to include in the returned text
1156 value. The maximum allowable absolute value is 64. The value of
1157 this argument is referred to as <i>N</i> in the discussion below.
1158 </td></tr></table>
1159
1160 <p>
1161 The snippet function first attempts to find a fragment of text consisting
1162 of <i>|N|</i> tokens within the current row that contains at least one phrase
1163 match for each matchable phrase matched somewhere in the current row,
1164 where <i>|N|</i> is the absolute value of the sixth argument passed to the
1165 snippet function. If the text stored in a single column contains less than
1166 <i>|N|</i> tokens, then the entire column value is considered. Text fragments
1167 may not span multiple columns.
1168
1169 </p><p>
1170 If such a text fragment can be found, it is returned with the following
1171 modifications:
1172
1173 </p><ul>
1174 <li> If the text fragment does not begin at the start of a column value,
1175 the "ellipses" text is prepended to it.
1176 </li><li> If the text fragment does not finish at the end of a column value,
1177 the "ellipses" text is appended to it.
1178 </li><li> For each token in the text fragment that is part of a phrase match,
1179 the "start match" text is inserted into the fragment before the token,
1180 and the "end match" text is inserted immediately after it.
1181 </li></ul>
1182
1183 <p>
1184 If more than one such fragment can be found, then fragments that contain
1185 a larger number of "extra" phrase matches are favored. The start of
1186 the selected text fragment may be moved a few tokens forward or backward
1187 to attempt to concentrate the phrase matches toward the center of the
1188 fragment.
1189
1190 </p><p>
1191 Assuming <i>N</i> is a positive value, if no fragments can be found that
1192 contain a phrase match corresponding to each matchable phrase, the snippet
1193 function attempts to find two fragments of approximately <i>N</i>/2 tokens
1194 that between them contain at least one phrase match for each matchable phrase
1195 matched by the current row. If this fails, attempts are made to find three
1196 fragments of <i>N</i>/3 tokens each and finally four <i>N</i>/4 token
1197 fragments. If a set of four fragments cannot be found that encompasses the
1198 required phrase matches, the four fragments of <i>N</i>/4 tokens that provide
1199 the best coverage are selected.
1200
1201 </p><p>
1202 If <i>N</i> is a negative value, and no single fragment can be found
1203 containing the required phrase matches, the snippet function searches
1204 for two fragments of <i>|N|</i> tokens each, then three, then four. In
1205 other words, if the specified value of <i>N</i> is negative, the sizes
1206 of the fragments is not decreased if more than one fragment is required
1207 to provide the desired phrase match coverage.
1208
1209 </p><p>
1210 After the <i>M</i> fragments have been located, where <i>M</i> is between
1211 two and four as described in the paragraphs above, they are joined together
1212 in sorted order with the "ellipses" text separating them. The three
1213 modifications enumerated earlier are performed on the text before it is
1214 returned.
1215
1216 </p><div class="codeblock"><pre><b>Note: In this block of examples, newlines and whitespace characters have
1217 been inserted into the document inserted into the FTS table, and the expected
1218 results described in SQL comments. This is done to enhance readability only,
1219 they would not be present in actual SQLite commands or output.</b>
1220
1221 <i>-- Create and populate an FTS table.</i>
1222 CREATE VIRTUAL TABLE text USING fts4();
1223 INSERT INTO text VALUES('
1224 During 30 Nov-1 Dec, 2-3oC drops. Cool in the upper portion, minimum temperature 14-16oC
1225 and cool elsewhere, minimum temperature 17-20oC. Cold to very cold on mountaintops,
1226 minimum temperature 6-12oC. Northeasterly winds 15-30 km/hr. After that, temperature
1227 increases. Northeasterly winds 15-30 km/hr.
1228 ');
1229
1230 <i>-- The following query returns the text value:</i>
1231 <i>--</i>
1232 <i>-- "&lt;b&gt;...&lt;/b&gt;cool elsewhere, minimum temperature 17-20oC. &lt;b&gt;Cold&lt;/b&gt; to very </i>
1233 <i>-- &lt;b&gt;cold&lt;/b&gt; on mountaintops, minimum temperature 6&lt;b&gt;...&lt;/b&gt;".</i>
1234 <i>--</i>
1235 SELECT snippet(text) FROM text WHERE text MATCH 'cold';
1236
1237 <i>-- The following query returns the text value:</i>
1238 <i>--</i>
1239 <i>-- "...the upper portion, &#91;minimum&#93; &#91;temperature&#93; 14-16oC and cool elsewhere,</i>
1240 <i>-- &#91;minimum&#93; &#91;temperature&#93; 17-20oC. Cold..."</i>
1241 <i>--</i>
1242 SELECT snippet(text, '&#91;', '&#93;', '...') FROM text WHERE text MATCH '"min* tem*"'
1243 </pre></div>
1244
1245 <a name="matchinfo"></a>
1246 <h2 id="matchinfo" tags="matchinfo"><span>4.3. </span>The Matchinfo Function</h2>
1247
1248 <p>
1249 The matchinfo function returns a blob value. If it is used within a query
1250 that does not use the full-text index (a "query by rowid" or "linear scan"),
1251 then the blob is zero bytes in size. Otherwise, the blob consists of zero
1252 or more 32-bit unsigned integers in machine byte-order. The exact number
1253 of integers in the returned array depends on both the query and the value
1254 of the second argument (if any) passed to the matchinfo function.
1255
1256 </p><p>
1257 The matchinfo function is called with either one or two arguments. As for
1258 all auxiliary functions, the first argument must be the special
1259 <a href="fts3.html#hiddencol">FTS hidden column</a>. The second argument, if it is specified, must be a text value
1260 comprised only of the characters 'p', 'c', 'n', 'a', 'l', 's', 'x', 'y' and 'b'.
1261 If no second argument is explicitly supplied, it defaults to "pcx". The
1262 second argument is referred to as the "format string" below.
1263
1264 </p><p>
1265 Characters in the matchinfo format string are processed from left to right.
1266 Each character in the format string causes one or more 32-bit unsigned
1267 integer values to be added to the returned array. The "values" column in
1268 the following table contains the number of integer values appended to the
1269 output buffer for each supported format string character. In the formula
1270 given, <i>cols</i> is the number of columns in the FTS table, and
1271 <i>phrases</i> is the number of <a href="#matchable">matchable phrases</a> in
1272 the query.
1273
1274 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
1275 <tr style="text-align:left"><th>Character</th><th>Values</th><th>Description
1276 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>p </td><td>1 </td><td>The number of matchable phrases in the query.
1277 </td></tr><tr style="text-align:left"><td>c </td><td>1 </td><td>The number of user defined columns in the FTS
1278 table (i.e. not including the docid or the <a href="fts3.html#hiddencol">FTS hidden column</a>).
1279 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>x </td><td style="white-space:nowrap">3 * <i>cols</i> * <i>phrases</i>
1280 </td><td><a name="matchinfo-x"></a>
1281
1282 For each distinct combination of a phrase and table column, the
1283 following three values:
1284 <ul>
1285 <li> In the current row, the number of times the phrase appears in
1286 the column.
1287 </li><li> The total number of times the phrase appears in the column in
1288 all rows in the FTS table.
1289 </li><li> The total number of rows in the FTS table for which the
1290 column contains at least one instance of the phrase.
1291 </li></ul>
1292 The first set of three values corresponds to the left-most column
1293 of the table (column 0) and the left-most matchable phrase in the
1294 query (phrase 0). If the table has more than one column, the second
1295 set of three values in the output array correspond to phrase 0 and
1296 column 1. Followed by phrase 0, column 2 and so on for all columns of
1297 the table. And so on for phrase 1, column 0, then phrase 1, column 1
1298 etc. In other words, the data for occurrences of phrase <i>p</i> in
1299 column <i>c</i> may be found using the following formula:
1300 <pre>
1301 hits_this_row = array&#91;3 * (c + p*cols) + 0&#93;
1302 hits_all_rows = array&#91;3 * (c + p*cols) + 1&#93;
1303 docs_with_hits = array&#91;3 * (c + p*cols) + 2&#93;
1304 </pre>
1305 </td></tr><tr style="text-align:left"><td>y</td><td style="white-space:nowrap"><i>cols</i> * <i>phrases</i>
1306 </td><td><a name="matchinfo-y"></a>
1307
1308 For each distinct combination of a phrase and table column, the
1309 number of usable phrase matches that appear in the column. This is
1310 usually identical to the first value in each set of three returned by the
1311 <a href="fts3.html#matchinfo-x">matchinfo 'x' flag</a>. However, the number of hits reported by the
1312 'y' flag is zero for any phrase that is part of a sub-expression
1313 that does not match the current row. This makes a difference for
1314 expressions that contain AND operators that are descendants of OR
1315 operators. For example, consider the expression:
1316 <pre>
1317 a OR (b AND c)
1318 </pre>
1319 and the document:
1320 <pre>
1321 "a c d"
1322 </pre>
1323 The <a href="fts3.html#matchinfo-x">matchinfo 'x' flag</a> would report a single hit for the phrases "a" and "c".
1324 However, the 'y' directive reports the number of hits for "c" as zero, as
1325 it is part of a sub-expression that does not match the document - (b AND c).
1326 For queries that do not contain AND operators descended from OR
1327 operators, the result values returned by 'y' are always the same as
1328 those returned by 'x'.
1329
1330 <p style="margin-left:0;margin-right:0">
1331 The first value in the array of integer values corresponds to the
1332 leftmost column of the table (column 0) and the first phrase in the query
1333 (phrase 0). The values corresponding to other column/phrase combinations
1334 may be located using the following formula:
1335
1336 </p><pre>
1337 hits_for_phrase_p_column_c = array&#91;c + p*cols&#93;
1338 </pre>
1339 For queries that use OR expressions, or those that use LIMIT or return
1340 many rows, the 'y' matchinfo option may be faster than 'x'.
1341
1342 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>b</td><td style="white-space:nowrap"><i>((cols+31)/32)</i> * <i>phrases</i>
1343 </td><td><a name="matchinfo-b"></a>
1344
1345
1346 The matchinfo 'b' flag provides similar information to the
1347 <a href="fts3.html#matchinfo-y">matchinfo 'y' flag</a>, but in a more
1348 compact form. Instead of the precise number of hits, 'b' provides a single
1349 boolean flag for each phrase/column combination. If the phrase is present in
1350 the column at least once (i.e. if the corresponding integer output of 'y' would
1351 be non-zero), the corresponding flag is set. Otherwise cleared.
1352
1353 <p style="margin-left:0;margin-right:0">
1354 If the table has 32 or fewer columns, a single unsigned integer is output for
1355 each phrase in the query. The least significant bit of the integer is set if the
1356 phrase appears at least once in column 0. The second least significant bit is
1357 set if the phrase appears once or more in column 1. And so on.
1358
1359 </p><p style="margin-left:0;margin-right:0">
1360 If the table has more than 32 columns, an extra integer is added to the output
1361 of each phrase for each extra 32 columns or part thereof. Integers
1362 corresponding to the same phrase are clumped together. For example, if a table
1363 with 45 columns is queried for two phrases, 4 integers are output. The first
1364 corresponds to phrase 0 and columns 0-31 of the table. The second integer
1365 contains data for phrase 0 and columns 32-44, and so on.
1366
1367 </p><p style="margin-left:0;margin-right:0">
1368 For example, if nCol is the number of columns in the table, to determine if
1369 phrase p is present in column c:
1370
1371 </p><pre>
1372 p_is_in_c = array&#91;p * ((nCol+31)/32)&#93; & (1 &lt;&lt; (c % 32))
1373 </pre>
1374
1375 </td></tr><tr style="text-align:left"><td>n </td><td>1 </td><td>The number of rows in the FTS4 table. This value is
1376 only available when querying FTS4 tables, not FTS3.
1377 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>a </td><td><i>cols</i> </td><td>For each column, the average number of
1378 tokens in the text values stored in the column (considering all rows in
1379 the FTS4 table). This value is only available when querying FTS4 tables,
1380 not FTS3.
1381 </td></tr><tr style="text-align:left"><td>l </td><td><i>cols</i> </td><td>
1382 For each column, the length of the value stored in the current row of the
1383 FTS4 table, in tokens. This value is only available when querying
1384 FTS4 tables, not FTS3. And only if the "matchinfo=fts3" directive was not
1385 specified as part of the "CREATE VIRTUAL TABLE" statement used to create
1386 the FTS4 table.
1387 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>s </td><td><i>cols</i> </td><td>For each column, the length of the longest
1388 subsequence of phrase matches that the column value has in common
1389 with the query text. For example, if a table column contains the text
1390 'a b c d e' and the query is 'a c "d e"', then the length of the longest
1391 common subsequence is 2 (phrase "c" followed by phrase "d e").
1392
1393
1394 </td></tr></table>
1395
1396 <p>
1397 For example:
1398
1399 </p><div class="codeblock"><pre><i>-- Create and populate an FTS4 table with two columns:</i>
1400 CREATE VIRTUAL TABLE t1 USING fts4(a, b);
1401 INSERT INTO t1 VALUES('transaction default models default', 'Non transaction reads');
1402 INSERT INTO t1 VALUES('the default transaction', 'these semantics present');
1403 INSERT INTO t1 VALUES('single request', 'default data');
1404
1405 <i>-- In the following query, no format string is specified and so it defaults</i>
1406 <i>-- to "pcx". It therefore returns a single row consisting of a single blob</i>
1407 <i>-- value 80 bytes in size (20 32-bit integers - 1 for "p", 1 for "c" and</i>
1408 <i>-- 3*2*3 for "x"). If each block of 4 bytes in</i> the blob is interpreted
1409 <i>-- as an unsigned integer in machine byte-order, the values will be:</i>
1410 <i>--</i>
1411 <i>-- 3 2 1 3 2 0 1 1 1 2 2 0 1 1 0 0 0 1 1 1</i>
1412 <i>--</i>
1413 <i>-- The row returned corresponds to the second entry inserted into table t1.</i>
1414 <i>-- The first two integers in the blob show that the query contained three</i>
1415 <i>-- phrases and the table being queried has two columns. The next block of</i>
1416 <i>-- three integers describes column 0 (in this case column "a") and phrase</i>
1417 <i>-- 0 (in this case "default"). The current row contains 1 hit for "default"</i>
1418 <i>-- in column 0, of a total of 3 hits for "default" that occur in column</i>
1419 <i>-- 0 of any table row. The 3 hits are spread across 2 different rows.</i>
1420 <i>--</i>
1421 <i>-- The next set of three integers (0 1 1) pertain to the hits for "default"</i>
1422 <i>-- in column 1 of the table (0 in this row, 1 in all rows, spread across </i>
1423 <i>-- 1 rows).</i>
1424 <i>--</i>
1425 SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'default transaction "these semantics"';
1426
1427 <i>-- The format string for this query is "ns". The output array will therefore</i>
1428 <i>-- contain 3 integer values - 1 for "n" and 2 for "s". The query returns</i>
1429 <i>-- two rows (the first two rows in the table match). The values returned are:</i>
1430 <i>--</i>
1431 <i>-- 3 1 1</i>
1432 <i>-- 3 2 0</i>
1433 <i>--</i>
1434 <i>-- The first value in the matchinfo array returned for both rows is 3 (the </i>
1435 <i>-- number of rows in the table). The following two values are the lengths </i>
1436 <i>-- of the longest common subsequence of phrase matches in each column.</i>
1437 SELECT matchinfo(t1, 'ns') FROM t1 WHERE t1 MATCH 'default transaction';
1438 </pre></div>
1439
1440 <p>
1441 The matchinfo function is much faster than either the snippet or offsets
1442 functions. This is because the implementation of both snippet and offsets
1443 is required to retrieve the documents being analyzed from disk, whereas
1444 all data required by matchinfo is available as part of the same portions
1445 of the full-text index that are required to implement the full-text query
1446 itself. This means that of the following two queries, the first may be
1447 an order of magnitude faster than the second:
1448
1449 </p><div class="codeblock"><pre>SELECT docid, matchinfo(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
1450 SELECT docid, offsets(tbl) FROM tbl WHERE tbl MATCH &lt;query expression&gt;;
1451 </pre></div>
1452
1453 <p>
1454 The matchinfo function provides all the information required to calculate
1455 probabilistic "bag-of-words" relevancy scores such as
1456 <a href="http://en.wikipedia.org/wiki/Okapi_BM25">Okapi BM25/BM25F</a> that may
1457 be used to order results in a full-text search application. Appendix A of this
1458 document, "<a href="fts3.html#appendix_a">search application tips</a>", contains an example of using the
1459 matchinfo() function efficiently.
1460
1461 </p><a name="fts4aux"></a>
1462 <h1 id="fts4aux" tags="fts4aux"><span>5. </span>Fts4aux - Direct Access to the Full-Text Index</h1>
1463
1464 <p>
1465 As of <a href="releaselog/3_7_6.html">version 3.7.6</a> (2011-04-12),
1466 SQLite includes a new virtual table module called
1467 "fts4aux", which can be used to inspect the full-text index of an existing
1468 FTS table directly. Despite its name, fts4aux works just as well with FTS3
1469 tables as it does with FTS4 tables. Fts4aux tables are read-only. The only
1470 way to modify the contents of an fts4aux table is by modifying the
1471 contents of the associated FTS table. The fts4aux module is automatically
1472 included in all <a href="fts3.html#compiling_and_enabling_fts3_and_fts4">builds that include FTS</a>.
1473
1474 </p><p>
1475 An fts4aux virtual table is constructed with one or two arguments. When
1476 used with a single argument, that argument is the unqualified name of the
1477 FTS table that it will be used to access. To access a table in a different
1478 database (for example, to create a TEMP fts4aux table that will access an
1479 FTS3 table in the MAIN database) use the two-argument form and give the
1480 name of the target database (ex: "main") in the first argument and the name
1481 of the FTS3/4 table as the second argument. (The two-argument form of
1482 fts4aux was added for SQLite <a href="releaselog/3_7_17.html">version 3.7.17</a> (2013-05-20)
1483 and will throw an error in prior releases.)
1484 For example:
1485
1486 </p><div class="codeblock"><pre><i>-- Create an FTS4 table</i>
1487 CREATE VIRTUAL TABLE ft USING fts4(x, y);
1488
1489 <i>-- Create an fts4aux table to access the full-text index for table "ft"</i>
1490 CREATE VIRTUAL TABLE ft_terms USING fts4aux(ft);
1491
1492 <i>-- Create a TEMP fts4aux table accessing the "ft" table in "main"</i>
1493 CREATE VIRTUAL TABLE temp.ft_terms_2 USING fts4aux(main,ft);
1494 </pre></div>
1495
1496 <p>
1497 For each term present in the FTS table, there are between 2 and N+1 rows
1498 in the fts4aux table, where N is the number of user-defined columns in
1499 the associated FTS table. An fts4aux table always has the same four columns,
1500 as follows, from left to right:
1501
1502 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
1503 <tr style="text-align:left"><th>Column Name</th><th>Column Contents
1504 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>term</td><td>
1505 Contains the text of the term for this row.
1506 </td></tr><tr style="text-align:left"><td>col</td><td>
1507 This column may contain either the text value '*' (i.e. a single
1508 character, U+002a) or an integer between 0 and N-1, where N is
1509 again the number of user-defined columns in the corresponding FTS table.
1510
1511 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>documents</td><td>
1512 This column always contains an integer value greater than zero.
1513 <br><br>
1514 If the "col" column contains the value '*', then this column
1515 contains the number of rows of the FTS table that contain at least one
1516 instance of the term (in any column). If col contains an integer
1517 value, then this column contains the number of rows of the FTS table that
1518 contain at least one instance of the term in the column identified by
1519 the col value. As usual, the columns of the FTS table are numbered
1520 from left to right, starting with zero.
1521
1522 </td></tr><tr style="text-align:left"><td>occurrences</td><td>
1523 This column also always contains an integer value greater than zero.
1524 <br><br>
1525 If the "col" column contains the value '*', then this column
1526 contains the total number of instances of the term in all rows of the
1527 FTS table (in any column). Otherwise, if col contains an integer
1528 value, then this column contains the total number of instances of the
1529 term that appear in the FTS table column identified by the col
1530 value.
1531
1532 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>languageid <i>(hidden)</i></td><td>
1533 <a name="f4alid"></a>
1534
1535 This column determines which <a href="fts3.html#*fts4languageid">languageid</a> is used to
1536 extract vocabulary from the FTS3/4 table.
1537 <br><br>
1538 The default value for languageid is 0. If an alternative language
1539 is specified in WHERE clause constraints, then that alternative is
1540 used instead of 0. There can only be a single languageid per query.
1541 In other words, the WHERE clause cannot contain a range constraint
1542 or IN operator on the languageid.
1543 </td></tr></table>
1544
1545 <p>
1546 For example, using the tables created above:
1547
1548 </p><div class="codeblock"><pre>INSERT INTO ft(x, y) VALUES('Apple banana', 'Cherry');
1549 INSERT INTO ft(x, y) VALUES('Banana Date Date', 'cherry');
1550 INSERT INTO ft(x, y) VALUES('Cherry Elderberry', 'Elderberry');
1551
1552 <i>-- The following query returns this data:</i>
1553 <i>--</i>
1554 <i>-- apple | * | 1 | 1</i>
1555 <i>-- apple | 0 | 1 | 1</i>
1556 <i>-- banana | * | 2 | 2</i>
1557 <i>-- banana | 0 | 2 | 2</i>
1558 <i>-- cherry | * | 3 | 3</i>
1559 <i>-- cherry | 0 | 1 | 1</i>
1560 <i>-- cherry | 1 | 2 | 2</i>
1561 <i>-- date | * | 1 | 2</i>
1562 <i>-- date | 0 | 1 | 2</i>
1563 <i>-- elderberry | * | 1 | 2</i>
1564 <i>-- elderberry | 0 | 1 | 1</i>
1565 <i>-- elderberry | 1 | 1 | 1</i>
1566 <i>--</i>
1567 SELECT term, col, documents, occurrences FROM ft_terms;
1568 </pre></div>
1569
1570 <p>
1571 In the example, the values in the "term" column are all lower case,
1572 even though they were inserted into table "ft" in mixed case. This is because
1573 an fts4aux table contains the terms as extracted from the document text
1574 by the <a href="fts3.html#tokenizer">tokenizer</a>. In this case, since table "ft" uses the
1575 <a href="fts3.html#tokenizer">simple tokenizer</a>, this means all terms have been folded to
1576 lower case. Also, there is (for example) no row with column "term"
1577 set to "apple" and column "col" set to 1. Since there are no instances
1578 of the term "apple" in column 1, no row is present in the fts4aux table.
1579
1580 </p><p>
1581 During a transaction, some of the data written to an FTS table may be
1582 cached in memory and written to the database only when the transaction is
1583 committed. However the implementation of the fts4aux module is only able
1584 to read data from the database. In practice this means that if an fts4aux
1585 table is queried from within a transaction in which the associated
1586 FTS table has been modified, the results of the query are likely to reflect
1587 only a (possibly empty) subset of the changes made.
1588
1589 </p><a name="fts4_options"></a>
1590 <h1 id="fts4_options" tags="FTS4 options"><span>6. </span>FTS4 Options</h1>
1591
1592 <p>
1593 If the "CREATE VIRTUAL TABLE" statement specifies module FTS4 (not FTS3),
1594 then special directives - FTS4 options - similar to the "tokenize=*" option
1595 may also appear in place of column names. An FTS4 option consists of the
1596 option name, followed by an "=" character, followed by the option value.
1597 The option value may optionally be enclosed in single or double quotes, with
1598 embedded quote characters escaped in the same way as for SQL literals. There
1599 may not be whitespace on either side of the "=" character. For example,
1600 to create an FTS4 table with the value of option "matchinfo" set to "fts3":
1601
1602 </p><div class="codeblock"><pre><i>-- Create a reduced-footprint FTS4 table.</i>
1603 CREATE VIRTUAL TABLE papers USING fts4(author, document, matchinfo=fts3);
1604 </pre></div>
1605
1606 <p>
1607 FTS4 currently supports the following options:
1608
1609 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
1610 <tr style="text-align:left"><th>Option</th><th>Interpretation
1611 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>compress</td><td>
1612 The compress option is used to specify the compress function. It is an error to
1613 specify a compress function without also specifying an uncompress
1614 function. <a href="fts3.html#the_compress_and_uncompress_options">See below</a> for details.
1615
1616 </td></tr><tr style="text-align:left"><td>content</td><td>
1617 The content allows the text being indexed to be
1618 stored in a separate table distinct from the FTS4 table,
1619 or even outside of SQLite.
1620
1621 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>languageid</td><td>
1622 The languageid option causes the FTS4 table to have an additional hidden
1623 integer column that identifies the language of the text contained in
1624 each row. The use of the languageid option allows the same FTS4 table
1625 to hold text in multiple languages or scripts, each with different tokenizer
1626 rules, and to query each language independently of the others.
1627
1628 </td></tr><tr style="text-align:left"><td>matchinfo</td><td>
1629 When set to the value "fts3", the matchinfo option reduces the amount of
1630 information stored by FTS4 with the consequence that the "l" option of
1631 <a href="fts3.html#matchinfo">matchinfo()</a> is no longer available.
1632
1633 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>notindexed</td><td>
1634 This option is used to specify the name of a column for which data is
1635 not indexed. Values stored in columns that are not indexed are not
1636 matched by MATCH queries. Nor are they recognized by auxiliary functions.
1637 A single CREATE VIRTUAL TABLE statement may have any number of notindexed
1638 options.
1639
1640 </td></tr><tr style="text-align:left"><td>order</td><td>
1641 <a name="fts4order"></a>
1642
1643 The "order" option may be set to either "DESC" or "ASC" (in upper or
1644 lower case). If it is set to "DESC", then FTS4 stores its data in such
1645 a way as to optimize returning results in descending order by docid.
1646 If it is set to "ASC" (the default), then the data structures are
1647 optimized for returning results in ascending order by docid. In other
1648 words, if many of the queries run against the FTS4 table use "ORDER BY
1649 docid DESC", then it may improve performance to add the "order=desc"
1650 option to the CREATE VIRTUAL TABLE statement.
1651
1652 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>prefix</td><td>
1653 This option may be set to a comma-separated list of positive non-zero
1654 integers. For each integer N in the list, a separate index is created
1655 in the database file to optimize <a href="fts3.html#termprefix">prefix queries</a> where
1656 the query term is N bytes in length, not including the '*' character,
1657 when encoded using UTF-8. <a href="fts3.html#the_prefix_option">See below</a> for details.
1658
1659 </td></tr><tr style="text-align:left"><td>uncompress</td><td>
1660 This option is used to specify the uncompress function. It is an error to
1661 specify an uncompress function without also specifying a compress
1662 function. <a href="fts3.html#the_compress_and_uncompress_options">See below</a> for details.
1663 </td></tr></table>
1664
1665 <p>
1666 When using FTS4, specifying a column name that contains an "=" character
1667 and is not either a "tokenize=*" specification or a recognized FTS4 option
1668 is an error. With FTS3, the first token in the unrecognized directive is
1669 interpreted as a column name. Similarly, specifying multiple "tokenize=*"
1670 directives in a single table declaration is an error when using FTS4, whereas
1671 the second and subsequent "tokenize=*" directives are interpreted as column
1672 names by FTS3. For example:
1673
1674 </p><div class="codeblock"><pre><i>-- An error. FTS4 does not recognize the directive "xyz=abc".</i>
1675 CREATE VIRTUAL TABLE papers USING fts4(author, document, xyz=abc);
1676
1677 <i>-- Create an FTS3 table with three columns - "author", "document"</i>
1678 <i>-- and "xyz".</i>
1679 CREATE VIRTUAL TABLE papers USING fts3(author, document, xyz=abc);
1680
1681 <i>-- An error. FTS4 does not allow multiple tokenize=* directives</i>
1682 CREATE VIRTUAL TABLE papers USING fts4(tokenize=porter, tokenize=simple);
1683
1684 <i>-- Create an FTS3 table with a single column named "tokenize". The</i>
1685 <i>-- table uses the "porter" tokenizer.</i>
1686 CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple);
1687
1688 <i>-- An error. Cannot create a table with two columns named "tokenize".</i>
1689 CREATE VIRTUAL TABLE papers USING fts3(tokenize=porter, tokenize=simple, tokenize=icu);
1690 </pre></div>
1691
1692 <a name="*fts4compression"></a>
1693
1694 <a name="the_compress_and_uncompress_options"></a>
1695 <h2 tags="fts4 compress option" id="the_compress_and_uncompress_options"><span>6.1. </span>The compress= and uncompress= options</h2>
1696
1697 <p>
1698 The compress and uncompress options allow FTS4 content to be stored in
1699 the database in a compressed form. Both options should be set to the name
1700 of an SQL scalar function registered using <a href="c3ref/create_function.html">sqlite3_create_function()</a>
1701 that accepts a single argument.
1702
1703 </p><p>
1704 The compress function should return a compressed version of the value
1705 passed to it as an argument. Each time data is written to the FTS4 table,
1706 each column value is passed to the compress function and the result value
1707 stored in the database. The compress function may return any type of SQLite
1708 value (blob, text, real, integer or null).
1709
1710 </p><p>
1711 The uncompress function should uncompress data previously compressed by
1712 the compress function. In other words, for all SQLite values X, it should
1713 be true that uncompress(compress(X)) equals X. When data that has been
1714 compressed by the compress function is read from the database by FTS4, it
1715 is passed to the uncompress function before it is used.
1716
1717 </p><p>
1718 If the specified compress or uncompress functions do not exist, the table
1719 may still be created. An error is not returned until the FTS4 table is
1720 read (if the uncompress function does not exist) or written (if it is the
1721 compress function that does not exist).
1722
1723 </p><div class="codeblock"><pre><i>-- Create an FTS4 table that stores data in compressed form. This</i>
1724 <i>-- assumes that the scalar functions zip() and unzip() have been (or</i>
1725 <i>-- will be) added to the database handle.</i>
1726 CREATE VIRTUAL TABLE papers USING fts4(author, document, compress=zip, uncompress=unzip);
1727 </pre></div>
1728
1729 <p>
1730 When implementing the compress and uncompress functions it is important to
1731 pay attention to data types. Specifically, when a user reads a value from
1732 a compressed FTS table, the value returned by FTS is exactly the same
1733 as the value returned by the uncompress function, including the data type.
1734 If that data type is not the same as the data type of the original value as
1735 passed to the compress function (for example if the uncompress function is
1736 returning BLOB when compress was originally passed TEXT), then the users
1737 query may not function as expected.
1738
1739 <a name="*fts4content"></a>
1740
1741 </p><a name="the_content_option_"></a>
1742 <h2 tags="fts4 content option" id="the_content_option_"><span>6.2. </span>The content= option </h2>
1743
1744 <p>
1745 The content option allows FTS4 to forego storing the text being indexed.
1746 The content option can be used in two ways:
1747
1748 </p><ul>
1749 <li><p> The indexed documents are not stored within the SQLite database
1750 at all (a "contentless" FTS4 table), or
1751
1752 </p></li><li><p> The indexed documents are stored in a database table created and
1753 managed by the user (an "external content" FTS4 table).
1754 </p></li></ul>
1755
1756 <p>
1757 Because the indexed documents themselves are usually much larger than
1758 the full-text index, the content option can be used to achieve
1759 significant space savings.
1760
1761 </p><a name="_contentless_fts4_tables_"></a>
1762 <h3 tags="contentless fts4 tables" id="_contentless_fts4_tables_"><span>6.2.1. </span> Contentless FTS4 Tables </h3>
1763
1764 <p>
1765 In order to create an FTS4 table that does not store a copy of the indexed
1766 documents at all, the content option should be set to an empty string.
1767 For example, the following SQL creates such an FTS4 table with three
1768 columns - "a", "b", and "c":
1769
1770 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c);
1771 </pre></div>
1772
1773 <p>
1774 Data can be inserted into such an FTS4 table using an INSERT statements.
1775 However, unlike ordinary FTS4 tables, the user must supply an explicit
1776 integer docid value. For example:
1777
1778 </p><div class="codeblock"><pre><i>-- This statement is Ok:</i>
1779 INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i');
1780
1781 <i>-- This statement causes an error, as no docid value has been provided:</i>
1782 INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r');
1783 </pre></div>
1784
1785 <p>
1786 It is not possible to UPDATE or DELETE a row stored in a contentless FTS4
1787 table. Attempting to do so is an error.
1788
1789 </p><p>
1790 Contentless FTS4 tables also support SELECT statements. However, it is
1791 an error to attempt to retrieve the value of any table column other than
1792 the docid column. The auxiliary function matchinfo() may be used, but
1793 snippet() and offsets() may not. For example:
1794
1795 </p><div class="codeblock"><pre><i>-- The following statements are Ok:</i>
1796 SELECT docid FROM t1 WHERE t1 MATCH 'xxx';
1797 SELECT docid FROM t1 WHERE a MATCH 'xxx';
1798 SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx';
1799
1800 <i>-- The following statements all cause errors, as the value of columns</i>
1801 <i>-- other than docid are required to evaluate them.</i>
1802 SELECT * FROM t1;
1803 SELECT a, b FROM t1 WHERE t1 MATCH 'xxx';
1804 SELECT docid FROM t1 WHERE a LIKE 'xxx%';
1805 SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx';
1806 </pre></div>
1807
1808 <p>
1809 Errors related to attempting to retrieve column values other than docid
1810 are runtime errors that occur within sqlite3_step(). In some cases, for
1811 example if the MATCH expression in a SELECT query matches zero rows, there
1812 may be no error at all even if a statement does refer to column values
1813 other than docid.
1814
1815 </p><a name="_external_content_fts4_tables_"></a>
1816 <h3 tags="external content fts4 tables" id="_external_content_fts4_tables_"><span>6.2.2. </span> External Content FTS4 Tables </h3>
1817
1818 <p>
1819 An "external content" FTS4 table is similar to a contentless table, except
1820 that if evaluation of a query requires the value of a column other than
1821 docid, FTS4 attempts to retrieve that value from a table (or view, or
1822 virtual table) nominated by the user (hereafter referred to as the "content
1823 table"). The FTS4 module never writes to the content table, and writing
1824 to the content table does not affect the full-text index. It is the
1825 responsibility of the user to ensure that the content table and the
1826 full-text index are consistent.
1827
1828 </p><p>
1829 An external content FTS4 table is created by setting the content option
1830 to the name of a table (or view, or virtual table) that may be queried by
1831 FTS4 to retrieve column values when required. If the nominated table does
1832 not exist, then an external content table behaves in the same way as
1833 a contentless table. For example:
1834
1835 </p><div class="codeblock"><pre>CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
1836 CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c);
1837 </pre></div>
1838
1839 <p>
1840 Assuming the nominated table does exist, then its columns must be the same
1841 as or a superset of those defined for the FTS table. The external table
1842 must also be in the same database file as the FTS table. In other words,
1843 The external table cannot be in a different database file connected using
1844 <a href="lang_attach.html">ATTACH</a> nor may one of the FTS table and the external content be in the
1845 TEMP database when the other is in a persistent database file such as MAIN.
1846
1847 </p><p>
1848 When a users query on the FTS table requires a column value other than
1849 docid, FTS attempts to read the requested value from the corresponding column of
1850 the row in the content table with a rowid value equal to the current FTS
1851 docid. Only the subset of content-table columns duplicated in the FTS/34
1852 table declaration can be queried for - to retrieve values from any other
1853 columns the content table must be queried directly. Or, if such a row cannot
1854 be found in the content table, a NULL value is used instead. For example:
1855
1856 </p><div class="codeblock"><pre>CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c);
1857 CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c);
1858
1859 INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f');
1860 INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l');
1861 INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2;
1862 <i>-- The following query returns a single row with two columns containing
1863 -- the text values "i j" and "k l".
1864 --
1865 -- The query uses the full-text index to discover that the MATCH
1866 -- term matches the row with docid=3. It then retrieves the values
1867 -- of columns b and c from the row with rowid=3 in the content table
1868 -- to return.
1869 --</i>
1870 SELECT * FROM t3 WHERE t3 MATCH 'k';
1871
1872 <i>-- Following the UPDATE, the query still returns a single row, this
1873 -- time containing the text values "xxx" and "yyy". This is because the
1874 -- full-text index still indicates that the row with docid=3 matches
1875 -- the FTS4 query 'k', even though the documents stored in the content
1876 -- table have been modified.
1877 --</i>
1878 UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3;
1879 SELECT * FROM t3 WHERE t3 MATCH 'k';
1880
1881 <i>-- Following the DELETE below, the query returns one row containing two
1882 -- NULL values. NULL values are returned because FTS is unable to find
1883 -- a row with rowid=3 within the content table.
1884 --</i>
1885 DELETE FROM t2;
1886 SELECT * FROM t3 WHERE t3 MATCH 'k';
1887 </pre></div>
1888
1889 <p>
1890 When a row is deleted from an external content FTS4 table, FTS4 needs to
1891 retrieve the column values of the row being deleted from the content table.
1892 This is so that FTS4 can update the full-text index entries for each token
1893 that occurs within the deleted row to indicate that row has been
1894 deleted. If the content table row cannot be found, or if it contains values
1895 inconsistent with the contents of the FTS index, the results can be difficult
1896 to predict. The FTS index may be left containing entries corresponding to the
1897 deleted row, which can lead to seemingly nonsensical results being returned
1898 by subsequent SELECT queries. The same applies when a row is updated, as
1899 internally an UPDATE is the same as a DELETE followed by an INSERT.
1900
1901 </p><p>
1902 This means that in order to keep an FTS in sync with an external content
1903 table, any UPDATE or DELETE operations must be applied first to the FTS
1904 table, and then to the external content table. For example:
1905
1906 </p><div class="codeblock"><pre>CREATE TABLE t1_real(id INTEGER PRIMARY KEY, a, b, c, d);
1907 CREATE VIRTUAL TABLE t1_fts USING fts4(content="t1_real", b, c);
1908
1909 <i>-- This works. When the row is removed from the FTS table, FTS retrieves
1910 -- the row with rowid=123 and tokenizes it in order to determine the entries
1911 -- that must be removed from the full-text index.
1912 --</i>
1913 DELETE FROM t1_fts WHERE rowid = 123;
1914 DELETE FROM t1_real WHERE rowid = 123;
1915
1916 --<i> This <b>does not work</b>. By the time the FTS table is updated, the row
1917 -- has already been deleted from the underlying content table. As a result
1918 -- FTS is unable to determine the entries to remove from the FTS index and
1919 -- so the index and content table are left out of sync.
1920 --</i>
1921 DELETE FROM t1_real WHERE rowid = 123;
1922 DELETE FROM t1_fts WHERE rowid = 123;
1923 </pre></div>
1924
1925 <p>
1926 Instead of writing separately to the full-text index and the content table,
1927 some users may wish to use database triggers to keep the full-text index
1928 up to date with respect to the set of documents stored in the content table.
1929 For example, using the tables from earlier examples:
1930
1931 </p><div class="codeblock"><pre>CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN
1932 DELETE FROM t3 WHERE docid=old.rowid;
1933 END;
1934 CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN
1935 DELETE FROM t3 WHERE docid=old.rowid;
1936 END;
1937
1938 CREATE TRIGGER t2_au AFTER UPDATE ON t2 BEGIN
1939 INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
1940 END;
1941 CREATE TRIGGER t2_ai AFTER INSERT ON t2 BEGIN
1942 INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c);
1943 END;
1944 </pre></div>
1945
1946 <p>
1947 The DELETE trigger must be fired before the actual delete takes place
1948 on the content table. This is so that FTS4 can still retrieve the original
1949 values in order to update the full-text index. And the INSERT trigger must
1950 be fired after the new row is inserted, so as to handle the case where the
1951 rowid is assigned automatically within the system. The UPDATE trigger must
1952 be split into two parts, one fired before and one after the update of the
1953 content table, for the same reasons.
1954
1955 </p><p>
1956 The <a href="fts3.html#*fts4rebuidcmd">FTS4 "rebuild" command</a>
1957 deletes the entire full-text index and rebuilds it based on the current
1958 set of documents in the content table. Assuming again that "t3" is the
1959 name of the external content FTS4 table, the rebuild command looks like this:
1960
1961 </p><div class="codeblock"><pre>INSERT INTO t3(t3) VALUES('rebuild');
1962 </pre></div>
1963
1964 <p>
1965 This command may also be used with ordinary FTS4 tables, for example if
1966 the implementation of the tokenizer changes. It is an
1967 error to attempt to rebuild the full-text index maintained by a contentless
1968 FTS4 table, since no content will be available to do the rebuilding.
1969
1970
1971 <a name="*fts4languageid"></a>
1972
1973 </p><a name="the_languageid_option"></a>
1974 <h2 tags="fts4 languageid option" id="the_languageid_option"><span>6.3. </span>The languageid= option</h2>
1975
1976 <p>
1977 When the languageid option is present, it specifies the name of
1978 another <a href="vtab.html#hiddencol">hidden column</a> that is added to the FTS4
1979 table and which is used to specify the language stored in each row
1980 of the FTS4 table. The name of the languageid hidden column must
1981 be distinct from all other column names in the FTS4 table. Example:
1982
1983 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE t1 USING fts4(x, y, languageid="lid")
1984 </pre></div>
1985
1986 <p>
1987 The default value of a languageid column is 0. Any value inserted
1988 into a languageid column is converted to a 32-bit (not 64) signed
1989 integer.
1990
1991 </p><p>
1992 By default, FTS queries (those that use the MATCH operator)
1993 consider only those rows with the languageid column set to 0. To
1994 query for rows with other languageid values, a constraint of the
1995 form "&lt;language-id&gt;=&lt;integer&gt;" must be added to the queries
1996 WHERE clause. For example:
1997
1998 </p><div class="codeblock"><pre>SELECT * FROM t1 WHERE t1 MATCH 'abc' AND lid=5;
1999 </pre></div>
2000
2001 <p>
2002 It is not possible for a single FTS query to return rows with
2003 different languageid values. The results of adding WHERE clauses
2004 that use other operators (e.g. lid!=5, or lid&lt;=5) are undefined.
2005
2006 </p><p>
2007 If the content option is used along with the languageid option,
2008 then the named languageid column must exist in the content= table
2009 (subject to the usual rules - if a query never needs to read the
2010 content table then this restriction does not apply).
2011
2012 </p><p>
2013 When the languageid option is used, SQLite invokes the xLanguageid()
2014 on the sqlite3_tokenizer_module object immediately after the object
2015 is created in order to pass in the language id that the
2016 tokenizer should use. The xLanguageid() method will never be called
2017 more than once for any single tokenizer object. The fact that different
2018 languages might be tokenized differently is one reason why no single
2019 FTS query can return rows with different languageid values.
2020
2021
2022
2023 <a name="fts4matchinfo"></a>
2024
2025 </p><a name="the_matchinfo_option"></a>
2026 <h2 tags="fts4 matchinfo option" id="the_matchinfo_option"><span>6.4. </span>The matchinfo= option</h2>
2027
2028 <p>
2029 The matchinfo option may only be set to the value "fts3".
2030 Attempting to set matchinfo to anything other than "fts3" is an error.
2031 If this option is specified, then some of the extra information stored by
2032 FTS4 is omitted. This reduces the amount of disk space consumed by
2033 an FTS4 table until it is almost the same as the amount that would
2034 be used by the equivalent FTS3 table, but also means that the data
2035 accessed by passing the 'l' flag to the <a href="fts3.html#matchinfo">matchinfo()</a> function is
2036 not available.
2037
2038 <a name="fts4notindexed"></a>
2039
2040 </p><a name="the_notindexed_option"></a>
2041 <h2 tags="fts4 notindexed option" id="the_notindexed_option"><span>6.5. </span>The notindexed= option</h2>
2042
2043 <p>
2044 Normally, the FTS module maintains an inverted index of all terms in
2045 all columns of the table. This option is used to specify the name of
2046 a column for which entries should not be added to the index. Multiple
2047 "notindexed" options may be used to specify that multiple columns should
2048 be omitted from the index. For example:
2049
2050 </p><div class="codeblock"><pre><i>-- Create an FTS4 table for which only the contents of columns c2 and c4</i>
2051 <i>-- are tokenized and added to the inverted index.</i>
2052 CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, c3, c4, notindexed=c1, notindexed=c3);
2053 </pre></div>
2054
2055 <p>
2056 Values stored in unindexed columns are not eligible to match MATCH
2057 operators. They do not influence the results of the offsets() or matchinfo()
2058 auxiliary functions. Nor will the snippet() function ever return a
2059 snippet based on a value stored in an unindexed column.
2060
2061 <a name="fts4prefix"></a>
2062
2063 </p><a name="the_prefix_option"></a>
2064 <h2 tags="fts4 prefix option" id="the_prefix_option"><span>6.6. </span>The prefix= option</h2>
2065
2066 <p>
2067 The FTS4 prefix option causes FTS to index term prefixes of specified lengths
2068 in the same way that it always indexes complete terms. The prefix option
2069 must be set to a comma separated list of positive non-zero integers.
2070 For each value N in the list, prefixes of length N bytes (when encoded
2071 using UTF-8) are indexed. FTS4 uses term prefix indexes to speed up
2072 <a href="fts3.html#termprefix">prefix queries</a>. The cost, of course, is that indexing term prefixes as
2073 well as complete terms increases the database size and slows down write
2074 operations on the FTS4 table.
2075
2076 </p><p>
2077 Prefix indexes may be used to optimize <a href="fts3.html#termprefix">prefix queries</a> in two cases.
2078 If the query is for a prefix of N bytes, then a prefix index created
2079 with "prefix=N" provides the best optimization. Or, if no "prefix=N"
2080 index is available, a "prefix=N+1" index may be used instead.
2081 Using a "prefix=N+1" index is less
2082 efficient than a "prefix=N" index, but is better than no prefix index at all.
2083
2084 </p><div class="codeblock"><pre><i>-- Create an FTS4 table with indexes to optimize 2 and 4 byte prefix queries.</i>
2085 CREATE VIRTUAL TABLE t1 USING fts4(c1, c2, prefix="2,4");
2086
2087 <i>-- The following two queries are both optimized using the prefix indexes.</i>
2088 SELECT * FROM t1 WHERE t1 MATCH 'ab*';
2089 SELECT * FROM t1 WHERE t1 MATCH 'abcd*';
2090
2091 <i>-- The following two queries are both partially optimized using the prefix</i>
2092 <i>-- indexes. The optimization is not as pronounced as it is for the queries</i>
2093 <i>-- above, but still an improvement over no prefix indexes at all.</i>
2094 SELECT * FROM t1 WHERE t1 MATCH 'a*';
2095 SELECT * FROM t1 WHERE t1 MATCH 'abc*';
2096 </pre></div>
2097
2098 <a name="*cmds"></a>
2099
2100 <a name="commands"></a>
2101 <h1 id="commands" tags="commands"><span>7. </span>Special Commands For FTS3 and FTS4</h1>
2102
2103 <p>
2104 Special INSERT operates can be used to issue commands to FTS3 and FTS4 tables.
2105 Every FTS3 and FTS4 has a hidden, read-only column which is the same name as
2106 the table itself. INSERTs into this hidden column are interpreted as commands
2107 to the FTS3/4 table. For a table with the name "xyz" the following commands
2108 are supported:
2109
2110 </p><ul>
2111 <li><p>INSERT INTO xyz(xyz) VALUES('optimize');</p>
2112 </li><li><p>INSERT INTO xyz(xyz) VALUES('rebuild');</p>
2113 </li><li><p>INSERT INTO xyz(xyz) VALUES('integrity-check');</p>
2114 </li><li><p>INSERT INTO xyz(xyz) VALUES('merge=X,Y');</p>
2115 </li><li><p>INSERT INTO xyz(xyz) VALUES('automerge=N');</p>
2116 </li></ul>
2117
2118 <a name="*fts4optcmd"></a>
2119
2120 <h2 id="optimize"><span>7.1. </span>The "optimize" command</h2>
2121
2122 <p>
2123 The "optimize" command causes FTS3/4 to merge together all of its
2124 inverted index b-trees into one large and complete b-tree. Doing
2125 an optimize will make subsequent queries run faster since there are
2126 fewer b-trees to search, and it may reduce disk usage by coalescing
2127 redundant entries. However, for a large FTS table, running optimize
2128 can be as expensive as running <a href="lang_vacuum.html">VACUUM</a>. The optimize command
2129 essentially has to read and write the entire FTS table, resulting
2130 in a large transaction.
2131
2132 </p><p>
2133 In batch-mode operation, where an FTS table is initially built up
2134 using a large number of INSERT operations, then queried repeatedly
2135 without further changes, it is often a good idea
2136 to run "optimize" after the last INSERT and before the first query.
2137
2138 <a name="*fts4rebuidcmd"></a>
2139
2140 </p><h2 id="rebuild"><span>7.2. </span>The "rebuild" command</h2>
2141
2142 <p>
2143 The "rebuild" command causes SQLite to discard the entire FTS3/4
2144 table and then rebuild it again from original text. The concept
2145 is similar to <a href="lang_reindex.html">REINDEX</a>, only that it applies to an
2146 FTS3/4 table instead of an ordinary index.
2147
2148 </p><p>
2149 The "rebuild" command should be run whenever the implementation
2150 of a custom tokenizer changes, so that all content can be retokenized.
2151 The "rebuild" command is also useful when using the
2152 <a href="fts3.html#*fts4content">FTS4 content option</a> after changes have been made to the original
2153 content table.
2154
2155 <a name="*fts4ickcmd"></a>
2156
2157 </p><h2 id="integcheck"><span>7.3. </span>The "integrity-check" command</h2>
2158
2159 <p>
2160 The "integrity-check" command causes SQLite to read and verify
2161 the accuracy of all inverted indices in an FTS3/4 table by comparing
2162 those inverted indices against the original content. The
2163 "integrity-check" command silently succeeds if the inverted
2164 indices are all ok, but will fail with an SQLITE_CORRUPT error
2165 if any problems are found.
2166
2167 </p><p>
2168 The "integrity-check" command is similar in concept to
2169 <a href="pragma.html#pragma_integrity_check">PRAGMA integrity_check</a>. In a working system, the "integrity-command"
2170 should always be successful. Possible causes of integrity-check
2171 failures include:
2172 </p><ul>
2173 <li> The application has made changes to the <a href="fts3.html#*shadowtab">FTS shadow tables</a>
2174 directly, without using the FTS3/4 virtual table, causing
2175 the shadow tables to become out of sync with each other.
2176 </li><li> Using the <a href="fts3.html#*fts4content">FTS4 content option</a> and failing to manually keep
2177 the content in sync with the FTS4 inverted indices.
2178 </li><li> Bugs in the FTS3/4 virtual table. (The "integrity-check"
2179 command was originally conceived as part of the test suite
2180 for FTS3/4.)
2181 </li><li> Corruption to the underlying SQLite database file. (See
2182 documentation on <a href="howtocorrupt.html">how to corrupt</a> an SQLite database for
2183 additional information.)
2184 </li></ul>
2185
2186 <a name="*fts4mergecmd"></a>
2187
2188 <h2 id="mergecmd"><span>7.4. </span>The "merge=X,Y" command</h2>
2189
2190 <p>
2191 The "merge=X,Y" command (where X and Y are integers) causes SQLite
2192 to do a limited amount of work toward merging the various inverted
2193 index b-trees of an FTS3/4 table together into one large b-tree.
2194 The X value is the target number of "blocks" to be merged, and Y is
2195 the minimum number of b-tree segments on a level required before
2196 merging will be applied to that level. The value of Y should
2197 be between 2 and 16 with a recommended value of 8. The value of X
2198 can be any positive integer but values on the order of 100 to 300
2199 are recommended.
2200
2201 </p><p>
2202 When an FTS table accumulates 16 b-tree segments at the same level,
2203 the next INSERT into that table will cause all 16 segments to be
2204 merged into a single b-tree segment at the next higher level. The
2205 effect of these level merges is that most INSERTs into an FTS table
2206 are very fast and take minimal memory, but an occasional INSERT is
2207 slow and generates a large transaction because of the need to
2208 do merging. This results in "spiky" performance of INSERTs.
2209
2210 </p><p>
2211 To avoid spiky INSERT performance, an application can run the
2212 "merge=X,Y" command periodically, possibly in an idle thread or
2213 idle process, to ensure that the FTS table never accumulates
2214 too many b-tree segments at the same level. INSERT performance
2215 spikes can generally be avoided, and performance of FTS3/4 can be
2216 maximized, by running "merge=X,Y" after every few thousand
2217 document inserts. Each "merge=X,Y" command will run in a separate
2218 transaction (unless they are grouped together using <a href="lang_transaction.html">BEGIN</a>...<a href="lang_transaction.html">COMMIT</a>,
2219 of course). The transactions can be kept small by choosing a value
2220 for X in the range of 100 to 300. The idle thread that is running
2221 the merge commands can know when it is done by checking the difference
2222 in <a href="c3ref/total_changes.html">sqlite3_total_changes()</a> before and after each "merge=X,Y"
2223 command and stopping the loop when the difference drops below two.
2224
2225 <a name="*fts4automergecmd"></a>
2226
2227 </p><h2 id="automerge""><span>7.5. </span>The "automerge=N" command</h2>
2228
2229 <p>
2230 The "automerge=N" command (where N is an integer between 0 and 15,
2231 inclusive) is used to configure an FTS3/4 tables "automerge" parameter,
2232 which controls automatic incremental inverted index merging. The default
2233 automerge value for new tables is 0, meaning that automatic incremental
2234 merging is completely disabled. If the value of the automerge parameter
2235 is modified using the "automerge=N" command, the new parameter value is
2236 stored persistently in the database and is used by all subsequently
2237 established database connections.
2238
2239 </p><p>
2240 Setting the automerge parameter to a non-zero value enables automatic
2241 incremental merging. This causes SQLite to do a small amount of inverted
2242 index merging after every INSERT operation. The amount of merging
2243 performed is designed so that the FTS3/4 table never reaches a point
2244 where it has 16 segments at the same level and hence has to do a large
2245 merge in order to complete an insert. In other words, automatic
2246 incremental merging is designed to prevent spiky INSERT performance.
2247
2248 </p><p>
2249 The downside of automatic incremental merging is that it makes
2250 every INSERT, UPDATE, and DELETE operation on an FTS3/4 table run
2251 a little slower, since extra time must be used to do the incremental
2252 merge. For maximum performance, it is recommended that applications
2253 disable automatic incremental merge and instead use the
2254 <a href="fts3.html#*fts4mergecmd">"merge" command</a> in an idle process to keep the inverted indices
2255 well merged. But if the structure of an application does not easily
2256 allow for idle processes, the use of automatic incremental merge is
2257 a very reasonable fallback solution.
2258
2259 </p><p>
2260 The actual value of the automerge parameter determines the number of
2261 index segments merged simultaneously by an automatic inverted index
2262 merge. If the value is set to N, the system waits until there are at
2263 least N segments on a single level before beginning to incrementally
2264 merge them. Setting a lower value of N causes segments to be merged more
2265 quickly, which may speed up full-text queries and, if the workload
2266 contains UPDATE or DELETE operations as well as INSERTs, reduce the space
2267 on disk consumed by the full-text index. However, it also increases the
2268 amount of data written to disk.
2269
2270 </p><p>
2271 For general use in cases where the workload contains few UPDATE or DELETE
2272 operations, a good choice for automerge is 8. If the workload contains
2273 many UPDATE or DELETE commands,
2274 or if query speed is a concern, it may be advantageous to reduce automerge
2275 to 2.
2276
2277 </p><p>
2278 For reasons of backwards compatibility, the "automerge=1" command sets
2279 the automerge parameter to 8, not 1 (a value of 1 would make no sense
2280 anyway, as merging data from a single segment is a no-op).
2281
2282
2283 </p><a name="tokenizer"></a>
2284 <h1 id="tokenizer" tags="tokenizer"><span>8. </span>Tokenizers</h1>
2285
2286 <p>
2287 An FTS tokenizer is a set of rules for extracting terms from a document
2288 or basic FTS full-text query.
2289
2290 </p><p>
2291 Unless a specific tokenizer is specified as part of the CREATE
2292 VIRTUAL TABLE statement used to create the FTS table, the default
2293 tokenizer, "simple", is used. The simple tokenizer extracts tokens from
2294 a document or basic FTS full-text query according to the following
2295 rules:
2296
2297 </p><ul>
2298 <li><p> A term is a contiguous sequence of eligible characters, where
2299 eligible characters are all alphanumeric characters and all characters with
2300 Unicode codepoint values greater than or equal to 128.
2301 All other characters are
2302 discarded when splitting a document into terms. Their only contribution is
2303 to separate adjacent terms.
2304
2305 </p></li><li><p> All uppercase characters within the ASCII range (Unicode codepoints
2306 less than 128), are transformed to their lowercase equivalents as part
2307 of the tokenization process. Thus, full-text queries are
2308 case-insensitive when using the simple tokenizer.
2309 </p></li></ul>
2310
2311 <p>
2312 For example, when a document containing the text "Right now, they're very
2313 frustrated.", the terms extracted from the document and added to the
2314 full-text index are, in order, "right now they re very frustrated". Such
2315 a document would match a full-text query such as "MATCH 'Frustrated'",
2316 as the simple tokenizer transforms the term in the query to lowercase
2317 before searching the full-text index.
2318
2319 </p><p>
2320 As well as the "simple" tokenizer, the FTS source code features a tokenizer
2321 that uses the <a href="http://tartarus.org/~martin/PorterStemmer/">Porter
2322 Stemming algorithm</a>. This tokenizer uses the same rules to separate
2323 the input document into terms including folding all terms into lower case,
2324 but also uses the Porter Stemming algorithm to reduce related English language
2325 words to a common root. For example, using the same input document as in the
2326 paragraph above, the porter tokenizer extracts the following tokens:
2327 "right now thei veri frustrat". Even though some of these terms are not even
2328 English words, in some cases using them to build the full-text index is more
2329 useful than the more intelligible output produced by the simple tokenizer.
2330 Using the porter tokenizer, the document not only matches full-text queries
2331 such as "MATCH 'Frustrated'", but also queries such as "MATCH 'Frustration'",
2332 as the term "Frustration" is reduced by the Porter stemmer algorithm to
2333 "frustrat" - just as "Frustrated" is. So, when using the porter tokenizer,
2334 FTS is able to find not just exact matches for queried terms, but matches
2335 against similar English language terms. For more information on the
2336 Porter Stemmer algorithm, please refer to the page linked above.
2337
2338 </p><p>
2339 Example illustrating the difference between the "simple" and "porter"
2340 tokenizers:
2341
2342 </p><div class="codeblock"><pre><i>-- Create a table using the simple tokenizer. Insert a document into it.</i>
2343 CREATE VIRTUAL TABLE simple USING fts3(tokenize=simple);
2344 INSERT INTO simple VALUES('Right now they''re very frustrated');
2345
2346 <i>-- The first of the following two queries matches the document stored in</i>
2347 <i>-- table "simple". The second does not.</i>
2348 SELECT * FROM simple WHERE simple MATCH 'Frustrated';
2349 SELECT * FROM simple WHERE simple MATCH 'Frustration';
2350
2351 <i>-- Create a table using the porter tokenizer. Insert the same document into it</i>
2352 CREATE VIRTUAL TABLE porter USING fts3(tokenize=porter);
2353 INSERT INTO porter VALUES('Right now they''re very frustrated');
2354
2355 <i>-- Both of the following queries match the document stored in table "porter".</i>
2356 SELECT * FROM porter WHERE porter MATCH 'Frustrated';
2357 SELECT * FROM porter WHERE porter MATCH 'Frustration';
2358 </pre></div>
2359
2360 <p>
2361 If this extension is compiled with the SQLITE_ENABLE_ICU pre-processor
2362 symbol defined, then there exists a built-in tokenizer named "icu"
2363 implemented using the ICU library. The first argument passed to the
2364 xCreate() method (see fts3_tokenizer.h) of this tokenizer may be
2365 an ICU locale identifier. For example "tr_TR" for Turkish as used
2366 in Turkey, or "en_AU" for English as used in Australia. For example:
2367
2368 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE thai_text USING fts3(text, tokenize=icu th_TH)
2369 </pre></div>
2370
2371 <p>
2372 The ICU tokenizer implementation is very simple. It splits the input
2373 text according to the ICU rules for finding word boundaries and discards
2374 any tokens that consist entirely of white-space. This may be suitable
2375 for some applications in some locales, but not all. If more complex
2376 processing is required, for example to implement stemming or
2377 discard punctuation, this can be done by creating a tokenizer
2378 implementation that uses the ICU tokenizer as part of its implementation.
2379
2380 <a name="unicode61"></a>
2381
2382 </p><p>
2383 The "unicode61" tokenizer is available beginning with SQLite <a href="releaselog/3_7_13.html">version 3.7.13</a>
2384 (2012-06-11).
2385 Unicode61 works very much like "simple" except that it does simple unicode
2386 case folding according to rules in Unicode Version 6.1 and it recognizes
2387 unicode space and punctuation characters and uses those to separate tokens.
2388 The simple tokenizer only does case folding of ASCII characters and only
2389 recognizes ASCII space and punctuation characters as token separators.
2390
2391 </p><p>
2392 By default, "unicode61" attempts to remove diacritics from Latin script
2393 characters. This behaviour can be overridden by adding the tokenizer argument
2394 "remove_diacritics=0". For example:
2395
2396 </p><div class="codeblock"><pre><i>-- Create tables that remove <b>all</b>diacritics from Latin script characters</i>
2397 <i>-- as part of tokenization.</i>
2398 CREATE VIRTUAL TABLE txt1 USING fts4(tokenize=unicode61);
2399 CREATE VIRTUAL TABLE txt2 USING fts4(tokenize=unicode61 "remove_diacritics=2");
2400
2401 <i>-- Create a table that does not remove diacritics from Latin script</i>
2402 <i>-- characters as part of tokenization.</i>
2403 CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "remove_diacritics=0");
2404 </pre></div>
2405
2406 <p>The remove_diacritics option may be set to "0", "1" or "2". The default
2407 value is "1". If it is set to "1" or "2", then diacritics are removed from
2408 Latin script characters as described above. However, if it is set to "1",
2409 then diacritics are not removed in the fairly uncommon case where a single
2410 unicode codepoint is used to represent a character with more that one
2411 diacritic. For example, diacritics are not removed from codepoint 0x1ED9
2412 ("LATIN SMALL LETTER O WITH CIRCUMFLEX AND DOT BELOW"). This is technically
2413 a bug, but cannot be fixed without creating backwards compatibility
2414 problems. If this option is set to "2", then diacritics are correctly
2415 removed from all Latin characters.
2416
2417 </p><p>
2418 It is also possible to customize the set of codepoints that unicode61 treats
2419 as separator characters. The "separators=" option may be used to specify one
2420 or more extra characters that should be treated as separator characters, and
2421 the "tokenchars=" option may be used to specify one or more extra characters
2422 that should be treated as part of tokens instead of as separator characters.
2423 For example:
2424
2425 </p><div class="codeblock"><pre><i>-- Create a table that uses the unicode61 tokenizer, but considers "."</i>
2426 <i>-- and "=" characters to be part of tokens, and capital "X" characters to</i>
2427 <i>-- function as separators.</i>
2428 CREATE VIRTUAL TABLE txt3 USING fts4(tokenize=unicode61 "tokenchars=.=" "separators=X");
2429
2430 <i>-- Create a table that considers space characters (codepoint 32) to be</i>
2431 <i>-- a token character</i>
2432 CREATE VIRTUAL TABLE txt4 USING fts4(tokenize=unicode61 "tokenchars= ");
2433 </pre></div>
2434
2435 <p>
2436 If a character specified as part of the argument to "tokenchars=" is considered
2437 to be a token character by default, it is ignored. This is true even if it has
2438 been marked as a separator by an earlier "separators=" option. Similarly, if
2439 a character specified as part of a "separators=" option is treated as a separator
2440 character by default, it is ignored. If multiple "tokenchars=" or "separators="
2441 options are specified, all are processed. For example:
2442
2443 </p><div class="codeblock"><pre><i>-- Create a table that uses the unicode61 tokenizer, but considers "."</i>
2444 <i>-- and "=" characters to be part of tokens, and capital "X" characters to</i>
2445 <i>-- function as separators. Both of the "tokenchars=" options are processed</i>
2446 <i>-- The "separators=" option ignores the "." passed to it, as "." is by</i>
2447 <i>-- default a separator character, even though it has been marked as a token</i>
2448 <i>-- character by an earlier "tokenchars=" option.</i>
2449 CREATE VIRTUAL TABLE txt5 USING fts4(
2450 tokenize=unicode61 "tokenchars=." "separators=X." "tokenchars=="
2451 );
2452 </pre></div>
2453
2454 <p>
2455 The arguments passed to the "tokenchars=" or "separators=" options are
2456 case-sensitive. In the example above, specifying that "X" is a separator
2457 character does not affect the way "x" is handled.
2458
2459 <a name="f3tknzr"></a>
2460
2461 </p><h2 id="custom_application_defined_tokenizers"><span>8.1. </span>Custom (Application Defined) Tokenizers</h2>
2462
2463 <p>
2464 In addition to providing built-in "simple", "porter" and (possibly) "icu" and
2465 "unicode61" tokenizers,
2466 FTS provides an interface for applications to implement and register custom
2467 tokenizers written in C. The interface used to create a new tokenizer is defined
2468 and described in the fts3_tokenizer.h source file.
2469
2470 </p><p>
2471 Registering a new FTS tokenizer is similar to registering a new
2472 virtual table module with SQLite. The user passes a pointer to a
2473 structure containing pointers to various callback functions that
2474 make up the implementation of the new tokenizer type. For tokenizers,
2475 the structure (defined in fts3_tokenizer.h) is called
2476 "sqlite3_tokenizer_module".
2477
2478 </p><p>
2479 FTS does not expose a C-function that users call to register new
2480 tokenizer types with a database handle. Instead, the pointer must
2481 be encoded as an SQL blob value and passed to FTS through the SQL
2482 engine by evaluating a special scalar function, "fts3_tokenizer()".
2483 The fts3_tokenizer() function may be called with one or two arguments,
2484 as follows:
2485
2486 </p><div class="codeblock"><pre>SELECT fts3_tokenizer(&lt;tokenizer-name&gt;);
2487 SELECT fts3_tokenizer(&lt;tokenizer-name&gt;, &lt;sqlite3_tokenizer_module ptr&gt;);
2488 </pre></div>
2489
2490 <p>
2491 Where &lt;tokenizer-name&gt; is a <a href="lang_expr.html#varparam">parameter</a> to which a string is bound
2492 using <a href="c3ref/bind_blob.html">sqlite3_bind_text()</a> where the string identifies the tokenizer and
2493 &lt;sqlite3_tokenizer_module ptr&gt; is a <a href="lang_expr.html#varparam">parameter</a> to which a BLOB is
2494 bound using <a href="c3ref/bind_blob.html">sqlite3_bind_blob()</a> where the value of the BLOB is a
2495 pointer to an sqlite3_tokenizer_module structure.
2496 If the second argument is present,
2497 it is registered as tokenizer &lt;tokenizer-name&gt; and a copy of it
2498 returned. If only one argument is passed, a pointer to the tokenizer
2499 implementation currently registered as &lt;tokenizer-name&gt; is returned,
2500 encoded as a blob. Or, if no such tokenizer exists, an SQL exception
2501 (error) is raised.
2502
2503 </p><p>
2504 Prior to SQLite <a href="releaselog/3_11_0.html">version 3.11.0</a> (2016-02-15), the arguments to
2505 fts3_tokenizer() could be literal strings or BLOBs. They did not have to
2506 be <a href="lang_expr.html#varparam">bound parameters</a>. But that could lead to security problems in the
2507 event of an SQL injection. Hence, the legacy behavior is now disabled
2508 by default. But the old legacy behavior can be enabled, for backwards
2509 compatibility in applications that really need it,
2510 by calling
2511 <a href="c3ref/db_config.html">sqlite3_db_config</a>(db,<a href="c3ref/c_dbconfig_defensive.html#sqlitedbconfigenablefts3tokenizer">SQLITE_DBCONFIG_ENABLE_FTS3_TOKENIZER</a>,1,0).
2512
2513 </p><p>
2514 The following block contains an example of calling the fts3_tokenizer()
2515 function from C code:
2516
2517 </p><div class="codeblock"><pre><i>/*
2518 ** Register a tokenizer implementation with FTS3 or FTS4.
2519 */</i>
2520 int registerTokenizer(
2521 sqlite3 *db,
2522 char *zName,
2523 const sqlite3_tokenizer_module *p
2524 ){
2525 int rc;
2526 sqlite3_stmt *pStmt;
2527 const char *zSql = "SELECT fts3_tokenizer(?1, ?2)";
2528
2529 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
2530 if( rc!=SQLITE_OK ){
2531 return rc;
2532 }
2533
2534 sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
2535 sqlite3_bind_blob(pStmt, 2, &p, sizeof(p), SQLITE_STATIC);
2536 sqlite3_step(pStmt);
2537
2538 return sqlite3_finalize(pStmt);
2539 }
2540
2541 <i>/*
2542 ** Query FTS for the tokenizer implementation named zName.
2543 */</i>
2544 int queryTokenizer(
2545 sqlite3 *db,
2546 char *zName,
2547 const sqlite3_tokenizer_module **pp
2548 ){
2549 int rc;
2550 sqlite3_stmt *pStmt;
2551 const char *zSql = "SELECT fts3_tokenizer(?)";
2552
2553 *pp = 0;
2554 rc = sqlite3_prepare_v2(db, zSql, -1, &pStmt, 0);
2555 if( rc!=SQLITE_OK ){
2556 return rc;
2557 }
2558
2559 sqlite3_bind_text(pStmt, 1, zName, -1, SQLITE_STATIC);
2560 if( SQLITE_ROW==sqlite3_step(pStmt) ){
2561 if( sqlite3_column_type(pStmt, 0)==SQLITE_BLOB ){
2562 memcpy(pp, sqlite3_column_blob(pStmt, 0), sizeof(*pp));
2563 }
2564 }
2565
2566 return sqlite3_finalize(pStmt);
2567 }
2568 </pre></div>
2569
2570
2571 <a name="fts3tok"></a>
2572
2573 <h2 id="querying_tokenizers"><span>8.2. </span>Querying Tokenizers</h2>
2574
2575 <p>The "fts3tokenize" virtual table can be used to directly access any
2576 tokenizer. The following SQL demonstrates how to create an instance
2577 of the fts3tokenize virtual table:
2578
2579 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE tok1 USING fts3tokenize('porter');
2580 </pre></div>
2581
2582 <p>The name of the desired tokenizer should be substituted in place of
2583 'porter' in the example, of course. If the tokenizer requires one or
2584 more arguments, they should be separated by commas in the fts3tokenize
2585 declaration (even though they are separated by spaces in declarations
2586 of regular fts4 tables). The following creates fts4 and fts3tokenize
2587 tables that use the same tokenizer:
2588 </p><div class="codeblock"><pre>CREATE VIRTUAL TABLE text1 USING fts4(tokenize=icu en_AU);
2589 CREATE VIRTUAL TABLE tokens1 USING fts3tokenize(icu, en_AU);
2590
2591 CREATE VIRTUAL TABLE text2 USING fts4(tokenize=unicode61 "tokenchars=@." "separators=123");
2592 CREATE VIRTUAL TABLE tokens2 USING fts3tokenize(unicode61, "tokenchars=@.", "separators=123");
2593 </pre></div>
2594
2595 <p>
2596 Once the virtual table is created, it can be queried as follows:
2597
2598 </p><div class="codeblock"><pre>SELECT token, start, end, position
2599 FROM tok1
2600 WHERE input='This is a test sentence.';
2601 </pre></div>
2602
2603 <p>The virtual table will return one row of output for each token in the
2604 input string. The "token" column is the text of the token. The "start"
2605 and "end" columns are the byte offset to the beginning and end of the
2606 token in the original input string.
2607 The "position" column is the sequence number
2608 of the token in the original input string. There is also an "input"
2609 column which is simply a copy of the input string that is specified in
2610 the WHERE clause. Note that a constraint of the form "input=?" must
2611 appear in the WHERE clause or else the virtual table will have no input
2612 to tokenize and will return no rows. The example above generates
2613 the following output:
2614
2615 </p><div class="codeblock"><pre>thi|0|4|0
2616 is|5|7|1
2617 a|8|9|2
2618 test|10|14|3
2619 sentenc|15|23|4
2620 </pre></div>
2621
2622 <p>Notice that the tokens in the result set from the fts3tokenize virtual
2623 table have been transformed according to the rules of the tokenizer.
2624 Since this example used the "porter" tokenizer, the "This" token was
2625 converted into "thi". If the original text of the token is desired,
2626 it can be retrieved using the "start" and "end" columns with the
2627 <a href="lang_corefunc.html#substr">substr()</a> function. For example:
2628
2629 </p><div class="codeblock"><pre>SELECT substr(input, start+1, end-start), token, position
2630 FROM tok1
2631 WHERE input='This is a test sentence.';
2632 </pre></div>
2633
2634 <p>The fts3tokenize virtual table can be used on any tokenizer, regardless
2635 of whether or not there exists an FTS3 or FTS4 table that actually uses
2636 that tokenizer.
2637
2638
2639 </p><a name="data_structures"></a>
2640 <h1 tags="segment btree" id="data_structures"><span>9. </span>Data Structures</h1>
2641
2642 <p>
2643 This overview describes how the FTS module stores its
2644 index and content in the database. It is <b>not necessary to read or
2645 understand the material in this section in order to use FTS</b> in an
2646 application. However, it may be useful to application developers attempting
2647 to analyze and understand FTS performance characteristics, or to developers
2648 contemplating enhancements to the existing FTS feature set.
2649 </p>
2650
2651 <a name="*shadowtab"></a>
2652
2653 <a name="shadow_tables"></a>
2654 <h2 tags="shadowtabs" id="shadow_tables"><span>9.1. </span>Shadow Tables</h2>
2655 <p>
2656 For each FTS virtual table in a database, three to five real (non-virtual) tables
2657 are created to store the underlying data. These real tables are called "shadow tables".
2658 The real tables are named "%_content",
2659 "%_segdir", "%_segments", "%_stat", and "%_docsize", where "%" is replaced by the name
2660 of the FTS virtual table.
2661
2662 </p><p>
2663 The leftmost column of the "%_content" table is an INTEGER PRIMARY KEY field
2664 named "docid". Following this is one column for each column of the FTS
2665 virtual table as declared by the user, named by prepending the column name
2666 supplied by the user with "c<i>N</i>", where <i>N</i> is the index of the
2667 column within the table, numbered from left to right starting with 0. Data
2668 types supplied as part of the virtual table declaration are not used as
2669 part of the %_content table declaration. For example:
2670
2671 </p><div class="codeblock"><pre><i>-- Virtual table declaration</i>
2672 CREATE VIRTUAL TABLE abc USING fts4(a NUMBER, b TEXT, c);
2673
2674 <i>-- Corresponding %_content table declaration</i>
2675 CREATE TABLE abc_content(docid INTEGER PRIMARY KEY, c0a, c1b, c2c);
2676 </pre></div>
2677
2678 <p>
2679 The %_content table contains the unadulterated data inserted by the user
2680 into the FTS virtual table by the user. If the user does not explicitly
2681 supply a "docid" value when inserting records, one is selected automatically
2682 by the system.
2683
2684 </p><p>
2685 The %_stat and %_docsize tables are only created if the FTS table uses the
2686 FTS4 module, not FTS3. Furthermore, the %_docsize table is omitted if the
2687 FTS4 table is created with the <a href="fts3.html#fts4matchinfo">"matchinfo=fts3"</a> directive
2688 specified as part of the CREATE VIRTUAL TABLE statement. If they are created,
2689 the schema of the two tables is as follows:
2690 </p><div class="codeblock"><pre>CREATE TABLE %_stat(
2691 id INTEGER PRIMARY KEY,
2692 value BLOB
2693 );
2694
2695 CREATE TABLE %_docsize(
2696 docid INTEGER PRIMARY KEY,
2697 size BLOB
2698 );
2699 </pre></div>
2700
2701 <p>
2702 For each row in the FTS table, the %_docsize table contains a corresponding
2703 row with the same "docid" value. The "size" field contains a blob consisting
2704 of <i>N</i> FTS varints, where <i>N</i> is the number of user-defined columns
2705 in the table. Each varint in the "size" blob is the number of tokens in the
2706 corresponding column of the associated row in the FTS table. The %_stat table
2707 always contains a single row with the "id" column set to 0. The "value"
2708 column contains a blob consisting of <i>N+1</i> FTS varints, where <i>N</i>
2709 is again the number of user-defined columns in the FTS table. The first
2710 varint in the blob is set to the total number of rows in the FTS table. The
2711 second and subsequent varints contain the total number of tokens stored in
2712 the corresponding column for all rows of the FTS table.
2713
2714 </p><p>
2715 The two remaining tables, %_segments and %_segdir, are used to store the
2716 full-text index. Conceptually, this index is a lookup table that maps each
2717 term (word) to the set of docid values corresponding to records in the
2718 %_content table that contain one or more occurrences of the term. To
2719 retrieve all documents that contain a specified term, the FTS module
2720 queries this index to determine the set of docid values for records that
2721 contain the term, then retrieves the required documents from the %_content
2722 table. Regardless of the schema of the FTS virtual table, the %_segments
2723 and %_segdir tables are always created as follows:
2724
2725 </p><div class="codeblock"><pre>CREATE TABLE %_segments(
2726 blockid INTEGER PRIMARY KEY, <i>-- B-tree node id</i>
2727 block blob <i>-- B-tree node data</i>
2728 );
2729
2730 CREATE TABLE %_segdir(
2731 level INTEGER,
2732 idx INTEGER,
2733 start_block INTEGER, <i>-- Blockid of first node in %_segments</i>
2734 leaves_end_block INTEGER, <i>-- Blockid of last leaf node in %_segments</i>
2735 end_block INTEGER, <i>-- Blockid of last node in %_segments</i>
2736 root BLOB, <i>-- B-tree root node</i>
2737 PRIMARY KEY(level, idx)
2738 );
2739 </pre></div>
2740
2741 <p>
2742 The schema depicted above is not designed to store the full-text index
2743 directly. Instead, it is used to store one or more b-tree structures. There
2744 is one b-tree for each row in the %_segdir table. The %_segdir table
2745 row contains the root node and various meta-data associated with the
2746 b-tree structure, and the %_segments table contains all other (non-root)
2747 b-tree nodes. Each b-tree is referred to as a "segment". Once it has
2748 been created, a segment b-tree is never updated (although it may be
2749 deleted altogether).
2750
2751 </p><p>
2752 The keys used by each segment b-tree are terms (words). As well as the
2753 key, each segment b-tree entry has an associated "doclist" (document list).
2754 A doclist consists of zero or more entries, where each entry consists of:
2755
2756 </p><ul>
2757 <li> A docid (document id), and
2758 </li><li> A list of term offsets, one for each occurrence of the term within
2759 the document. A term offset indicates the number of tokens (words)
2760 that occur before the term in question, not the number of characters
2761 or bytes. For example, the term offset of the term "war" in the
2762 phrase "Ancestral voices prophesying war!" is 3.
2763 </li></ul>
2764
2765 <p>
2766 Entries within a doclist are sorted by docid. Positions within a doclist
2767 entry are stored in ascending order.
2768
2769 </p><p>
2770 The contents of the logical full-text index is found by merging the
2771 contents of all segment b-trees. If a term is present in more than one
2772 segment b-tree, then it maps to the union of each individual doclist. If,
2773 for a single term, the same docid occurs in more than one doclist, then only
2774 the doclist that is part of the most recently created segment b-tree is
2775 considered valid.
2776
2777 </p><p>
2778 Multiple b-tree structures are used instead of a single b-tree to reduce
2779 the cost of inserting records into FTS tables. When a new record is
2780 inserted into an FTS table that already contains a lot of data, it is
2781 likely that many of the terms in the new record are already present in
2782 a large number of existing records. If a single b-tree were used, then
2783 large doclist structures would have to be loaded from the database,
2784 amended to include the new docid and term-offset list, then written back
2785 to the database. Using multiple b-tree tables allows this to be avoided
2786 by creating a new b-tree which can be merged with the existing b-tree
2787 (or b-trees) later on. Merging of b-tree structures can be performed as
2788 a background task, or once a certain number of separate b-tree structures
2789 have been accumulated. Of course, this scheme makes queries more expensive
2790 (as the FTS code may have to look up individual terms in more than one
2791 b-tree and merge the results), but it has been found that in practice this
2792 overhead is often negligible.
2793
2794 </p><h2 id="variable_length_integer_varint_format"><span>9.2. </span>Variable Length Integer (varint) Format</h2>
2795
2796 <p>
2797 Integer values stored as part of segment b-tree nodes are encoded using the
2798 FTS varint format. This encoding is similar, but <b>not identical</b>, to
2799 the <a href="fileformat2.html#varint">SQLite varint format</a>.
2800
2801 </p><p>
2802 An encoded FTS varint consumes between one and ten bytes of space. The
2803 number of bytes required is determined by the sign and magnitude of the
2804 integer value encoded. More accurately, the number of bytes used to store
2805 the encoded integer depends on the position of the most significant set bit
2806 in the 64-bit twos-complement representation of the integer value. Negative
2807 values always have the most significant bit set (the sign bit), and so are
2808 always stored using the full ten bytes. Positive integer values may be
2809 stored using less space.
2810
2811 </p><p>
2812 The final byte of an encoded FTS varint has its most significant bit
2813 cleared. All preceding bytes have the most significant bit set. Data
2814 is stored in the remaining seven least significant bits of each byte.
2815 The first byte of the encoded representation contains the least significant
2816 seven bits of the encoded integer value. The second byte of the encoded
2817 representation, if it is present, contains the seven next least significant
2818 bits of the integer value, and so on. The following table contains examples
2819 of encoded integer values:
2820
2821 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
2822 <tr style="text-align:left"><th>Decimal</th><th>Hexadecimal</th><th width="100%">Encoded Representation
2823 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>43</td><td>0x000000000000002B</td><td>0x2B
2824 </td></tr><tr style="text-align:left"><td>200815</td><td>0x000000000003106F</td><td>0xEF 0xA0 0x0C
2825 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>-1</td><td>0xFFFFFFFFFFFFFFFF</td><td>0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0xFF 0x01
2826 </td></tr></table>
2827
2828
2829 <h2 id="segment_b_tree_format"><span>9.3. </span>Segment B-Tree Format</h2>
2830
2831 <p>
2832 Segment b-trees are prefix-compressed b+-trees. There is one segment b-tree
2833 for each row in the %_segdir table (see above). The root node of the segment
2834 b-tree is stored as a blob in the "root" field of the corresponding row
2835 of the %_segdir table. All other nodes (if any exist) are stored in the
2836 "blob" column of the %_segments table. Nodes within the %_segments table are
2837 identified by the integer value in the blockid field of the corresponding
2838 row. The following table describes the fields of the %_segdir table:
2839
2840 </p><table striped="1" style="margin:1em auto; width:80%; border-spacing:0">
2841 <tr style="text-align:left"><th>Column </th><th width="100%">Interpretation
2842 </th></tr><tr style="text-align:left;background-color:#DDDDDD"><td>level </td><td>
2843 Between them, the contents of the "level" and "idx" fields define the
2844 relative age of the segment b-tree. The smaller the value stored in the
2845 "level" field, the more recently the segment b-tree was created. If two
2846 segment b-trees are of the same "level", the segment with the larger
2847 value stored in the "idx" column is more recent. The PRIMARY KEY constraint
2848 on the %_segdir table prevents any two segments from having the same value
2849 for both the "level" and "idx" fields.
2850 </td></tr><tr style="text-align:left"><td>idx </td><td> See above.
2851 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>start_block </td><td>
2852 The blockid that corresponds to the node with the smallest blockid that
2853 belongs to this segment b-tree. Or zero if the entire segment b-tree
2854 fits on the root node. If it exists, this node is always a leaf node.
2855 </td></tr><tr style="text-align:left"><td>leaves_end_block </td><td>
2856 The blockid that corresponds to the leaf node with the largest blockid
2857 that belongs to this segment b-tree. Or zero if the entire segment b-tree
2858 fits on the root node.
2859 </td></tr><tr style="text-align:left;background-color:#DDDDDD"><td>end_block </td><td>
2860 This field may contain either an integer or a text field consisting of
2861 two integers separated by a space character (unicode codepoint 0x20).
2862 <p style="margin-left:0;margin-right:0">
2863 The first, or only, integer is the blockid that corresponds to the interior
2864 node with the largest blockid that belongs to this segment b-tree. Or zero
2865 if the entire segment b-tree fits on the root node. If it exists, this node
2866 is always an interior node.
2867 </p><p style="margin-left:0;margin-right:0;margin-bottom:0">
2868 The second integer, if it is present, is the aggregate size of all data
2869 stored on leaf pages in bytes. If the value is negative, then the segment
2870 is the output of an unfinished incremental-merge operation, and the
2871 absolute value is current size in bytes.
2872
2873 </p></td></tr><tr style="text-align:left"><td>root </td><td>
2874 Blob containing the root node of the segment b-tree.
2875 </td></tr></table>
2876
2877 <p>
2878 Apart from the root node, the nodes that make up a single segment b-tree are
2879 always stored using a contiguous sequence of blockids. Furthermore, the
2880 nodes that make up a single level of the b-tree are themselves stored as
2881 a contiguous block, in b-tree order. The contiguous sequence of blockids
2882 used to store the b-tree leaves are allocated starting with the blockid
2883 value stored in the "start_block" column of the corresponding %_segdir row,
2884 and finishing at the blockid value stored in the "leaves_end_block"
2885 field of the same row. It is therefore possible to iterate through all the
2886 leaves of a segment b-tree, in key order, by traversing the %_segments
2887 table in blockid order from "start_block" to "leaves_end_block".
2888
2889 </p><h3 id="segment_b_tree_leaf_nodes"><span>9.3.1. </span>Segment B-Tree Leaf Nodes</h3>
2890
2891 <p>
2892 The following diagram depicts the format of a segment b-tree leaf node.
2893
2894 </p><center>
2895 <img src="images/fts3_leaf_node.png">
2896 <p> Segment B-Tree Leaf Node Format
2897 </p></center>
2898
2899 <p>
2900 The first term stored on each node ("Term 1" in the figure above) is
2901 stored verbatim. Each subsequent term is prefix-compressed with respect
2902 to its predecessor. Terms are stored within a page in sorted (memcmp)
2903 order.
2904
2905 </p><h3 id="segment_b_tree_interior_nodes"><span>9.3.2. </span>Segment B-Tree Interior Nodes</h3>
2906
2907 <p>
2908 The following diagram depicts the format of a segment b-tree interior
2909 (non-leaf) node.
2910
2911 </p><center>
2912 <img src="images/fts3_interior_node.png">
2913 <p> Segment B-Tree Interior Node Format
2914 </p></center>
2915
2916
2917 <h2 id="doclist_format"><span>9.4. </span>Doclist Format</h2>
2918
2919 <p>
2920 A doclist consists of an array of 64-bit signed integers, serialized using
2921 the FTS varint format. Each doclist entry is made up of a series of two
2922 or more integers, as follows:
2923
2924 </p><ol>
2925 <li> The docid value. The first entry in a doclist contains the literal docid
2926 value. The first field of each subsequent doclist entry contains the
2927 difference between the new docid and the previous one (always a positive
2928 number).
2929 </li><li> Zero or more term-offset lists. A term-offset list is present for each
2930 column of the FTS virtual table that contains the term. A term-offset
2931 list consists of the following:
2932 <ol>
2933 <li> Constant value 1. This field is omitted for any term-offset list
2934 associated with column 0.
2935 </li><li> The column number (1 for the second leftmost column, etc.). This
2936 field is omitted for any term-offset list associated with column 0.
2937 </li><li> A list of term-offsets, sorted from smallest to largest. Instead
2938 of storing the term-offset value literally, each integer stored
2939 is the difference between the current term-offset and the previous
2940 one (or zero if the current term-offset is the first), plus 2.
2941 </li></ol>
2942 </li><li> Constant value 0.
2943 </li></ol>
2944
2945 <center>
2946 <img src="images/fts3_doclist2.png">
2947 <p> FTS3 Doclist Format
2948 </p></center>
2949
2950 <center>
2951 <img src="images/fts3_doclist.png">
2952 <p> FTS Doclist Entry Format
2953 </p></center>
2954
2955 <p>
2956 For doclists for which the term appears in more than one column of the FTS
2957 virtual table, term-offset lists within the doclist are stored in column
2958 number order. This ensures that the term-offset list associated with
2959 column 0 (if any) is always first, allowing the first two fields of the
2960 term-offset list to be omitted in this case.
2961
2962 </p><a name="limitations"></a>
2963 <h1 tags="bugs" id="limitations"><span>10. </span>Limitations</h1>
2964
2965 <h2 id="_utf_16_byte_order_mark_problem_"><span>10.1. </span> UTF-16 byte-order-mark problem </h2>
2966
2967 For UTF-16 databases, when using the "simple" tokenizer, it is possible to use
2968 malformed unicode strings to cause the
2969 <a href="#integcheck">integrity-check special command</a> to falsely report
2970 corruption, or for <a href="#snippet">auxiliary functions</a> to return
2971 incorrect results. More specifically, the bug can be triggered by any of the following:
2972
2973 <ul>
2974 <li><p>A UTF-16 byte-order-mark (BOM) is embedded at the beginning of an SQL string
2975 literal value inserted into an FTS3 table. For example:
2976
2977 </p><div class="codeblock"><pre>INSERT INTO fts_table(col) VALUES(char(0xfeff)||'text...');
2978 </pre></div>
2979
2980 </li><li><p>Malformed UTF-8 that SQLite converts to a UTF-16 byte-order-mark is
2981 embedded at the beginning of an SQL string literal value inserted
2982 into an FTS3 table.
2983
2984 </p></li><li><p>A text value created by casting a blob that begins with the two
2985 bytes 0xFF and 0xFE, in either possible order, is inserted into an
2986 FTS3 table. For example:
2987
2988 </p><div class="codeblock"><pre>INSERT INTO fts_table(col) VALUES(CAST(X'FEFF' AS TEXT));
2989 </pre></div>
2990 </li></ul>
2991
2992 Everything works correctly if any of the following are true:
2993 <ul>
2994 <li> The <a href="pragma.html#pragma_encoding">database encoding</a> is UTF-8.
2995 </li><li> All text strings are inserted using one of the
2996 <a href="c3ref/bind_blob.html">sqlite3_bind_text()</a> family of functions.
2997 </li><li> Literal strings contain no byte-order-marks.
2998 </li><li> A tokenizer is used that recognizes byte-order-marks
2999 as whitespace. (The default "simple" tokenizer for
3000 FTS3/4 does not think that BOMs are whitespace, but
3001 the unicode tokenizer does.)
3002 </li></ul>
3003 All of the above conditions must be false in order for problems
3004 to occur. And even if all of the conditions above are false,
3005 most things will still operate correctly. Only the
3006 <a href="#integcheck">integrity-check</a> command and the
3007 <a href="#snippet">auxiliary functions</a> might give
3008 unexpected results.
3009
3010 <a name="appendix_a"></a>
3011 <h1 id="appendix_a" nonumber="1" tags="search application tips">
3012 Appendix A: Search Application Tips
3013 </h1>
3014
3015 <p>
3016 FTS is primarily designed to support Boolean full-text queries - queries
3017 to find the set of documents that match a specified criteria. However, many
3018 (most?) search applications require that results are somehow ranked in order
3019 of "relevance", where "relevance" is defined as the likelihood that the user
3020 who performed the search is interested in a specific element of the returned
3021 set of documents. When using a search engine to find documents on the world
3022 wide web, the user expects that the most useful, or "relevant", documents
3023 will be returned as the first page of results, and that each subsequent page
3024 contains progressively less relevant results. Exactly how a machine can
3025 determine document relevance based on a users query is a complicated problem
3026 and the subject of much ongoing research.
3027
3028 </p><p>
3029 One very simple scheme might be to count the number of instances of the
3030 users search terms in each result document. Those documents that contain
3031 many instances of the terms are considered more relevant than those with
3032 a small number of instances of each term. In an FTS application, the
3033 number of term instances in each result could be determined by counting
3034 the number of integers in the return value of the <a href="fts3.html#offsets">offsets</a> function.
3035 The following example shows a query that could be used to obtain the
3036 ten most relevant results for a query entered by the user:
3037
3038 </p><div class="codeblock"><pre><i>-- This example (and all others in this section) assumes the following schema</i>
3039 CREATE VIRTUAL TABLE documents USING fts3(title, content);
3040
3041 <i>-- Assuming the application has supplied an SQLite user function named "countintegers"</i>
3042 <i>-- that returns the number of space-separated integers contained in its only argument,</i>
3043 <i>-- the following query could be used to return the titles of the 10 documents that contain</i>
3044 <i>-- the greatest number of instances of the users query terms. Hopefully, these 10</i>
3045 <i>-- documents will be those that the users considers more or less the most "relevant".</i>
3046 SELECT title FROM documents
3047 WHERE documents MATCH &lt;query&gt;
3048 ORDER BY countintegers(offsets(documents)) DESC
3049 LIMIT 10 OFFSET 0
3050 </pre></div>
3051
3052 <p>
3053 The query above could be made to run faster by using the FTS <a href="fts3.html#matchinfo">matchinfo</a>
3054 function to determine the number of query term instances that appear in each
3055 result. The matchinfo function is much more efficient than the offsets
3056 function. Furthermore, the matchinfo function provides extra information
3057 regarding the overall number of occurrences of each query term in the entire
3058 document set (not just the current row) and the number of documents in which
3059 each query term appears. This may be used (for example) to attach a higher
3060 weight to less common terms which may increase the overall computed relevancy
3061 of those results the user considers more interesting.
3062
3063 </p><div class="codeblock"><pre><i>-- If the application supplies an SQLite user function called "rank" that</i>
3064 <i>-- interprets the blob of data returned by matchinfo and returns a numeric</i>
3065 <i>-- relevancy based on it, then the following SQL may be used to return the</i>
3066 <i>-- titles of the 10 most relevant documents in the dataset for a users query.</i>
3067 SELECT title FROM documents
3068 WHERE documents MATCH &lt;query&gt;
3069 ORDER BY rank(matchinfo(documents)) DESC
3070 LIMIT 10 OFFSET 0
3071 </pre></div>
3072
3073 <p>
3074 The SQL query in the example above uses less CPU than the first example
3075 in this section, but still has a non-obvious performance problem. SQLite
3076 satisfies this query by retrieving the value of the "title" column and
3077 matchinfo data from the FTS module for every row matched by the users
3078 query before it sorts and limits the results. Because of the way SQLite's
3079 virtual table interface works, retrieving the value of the "title" column
3080 requires loading the entire row from disk (including the "content" field,
3081 which may be quite large). This means that if the users query matches
3082 several thousand documents, many megabytes of "title" and "content" data
3083 may be loaded from disk into memory even though they will never be used
3084 for any purpose.
3085
3086 </p><p>
3087 The SQL query in the following example block is one solution to this
3088 problem. In SQLite, when a <a href="optoverview.html#flattening">sub-query
3089 used in a join contains a LIMIT clause</a>, the results of the sub-query are
3090 calculated and stored in a temporary table before the main query is executed.
3091 This means that SQLite will load only the docid and matchinfo data for each
3092 row matching the users query into memory, determine the docid values
3093 corresponding to the ten most relevant documents, then load only the title
3094 and content information for those 10 documents only. Because both the matchinfo
3095 and docid values are gleaned entirely from the full-text index, this results
3096 in dramatically less data being loaded from the database into memory.
3097
3098 </p><div class="codeblock"><pre>SELECT title FROM documents JOIN (
3099 SELECT docid, rank(matchinfo(documents)) AS rank
3100 FROM documents
3101 WHERE documents MATCH &lt;query&gt;
3102 ORDER BY rank DESC
3103 LIMIT 10 OFFSET 0
3104 ) AS ranktable USING(docid)
3105 ORDER BY ranktable.rank DESC
3106 </pre></div>
3107
3108 <p>
3109 The next block of SQL enhances the query with solutions to two other problems
3110 that may arise in developing search applications using FTS:
3111
3112 </p><ol>
3113 <li> <p>
3114 The <a href="fts3.html#snippet">snippet</a> function cannot be used with the above query. Because
3115 the outer query does not include a "WHERE ... MATCH" clause, the snippet
3116 function may not be used with it. One solution is to duplicate the WHERE
3117 clause used by the sub-query in the outer query. The overhead associated
3118 with this is usually negligible.
3119 </p></li><li> <p>
3120 The relevancy of a document may depend on something other than just
3121 the data available in the return value of matchinfo. For example
3122 each document in the database may be assigned a static weight based
3123 on factors unrelated to its content (origin, author, age, number
3124 of references etc.). These values can be stored by the application
3125 in a separate table that can be joined against the documents table
3126 in the sub-query so that the rank function may access them.
3127 </p></li></ol>
3128
3129 <p>
3130 This version of the query is very similar to that used by the
3131 <a href="https://sqlite.org/search?q=fts3">sqlite.org documentation search</a>
3132 application.
3133
3134 </p><div class="codeblock"><pre><i>-- This table stores the static weight assigned to each document in FTS table</i>
3135 <i>-- "documents". For each row in the documents table there is a corresponding row</i>
3136 <i>-- with the same docid value in this table.</i>
3137 CREATE TABLE documents_data(docid INTEGER PRIMARY KEY, weight);
3138
3139 <i>-- This query is similar to the one in the block above, except that:</i>
3140 <i>--</i>
3141 <i>-- 1. It returns a "snippet" of text along with the document title for display. So</i>
3142 <i>-- that the snippet function may be used, the "WHERE ... MATCH ..." clause from</i>
3143 <i>-- the sub-query is duplicated in the outer query.</i>
3144 <i>--</i>
3145 <i>-- 2. The sub-query joins the documents table with the document_data table, so that</i>
3146 <i>-- implementation of the rank function has access to the static weight assigned</i>
3147 <i>-- to each document.</i>
3148 SELECT title, snippet(documents) FROM documents JOIN (
3149 SELECT docid, rank(matchinfo(documents), documents_data.weight) AS rank
3150 FROM documents JOIN documents_data USING(docid)
3151 WHERE documents MATCH &lt;query&gt;
3152 ORDER BY rank DESC
3153 LIMIT 10 OFFSET 0
3154 ) AS ranktable USING(docid)
3155 WHERE documents MATCH &lt;query&gt;
3156 ORDER BY ranktable.rank DESC
3157 </pre></div>
3158
3159 <p>
3160 All the example queries above return the ten most relevant query results.
3161 By modifying the values used with the OFFSET and LIMIT clauses, a query
3162 to return (say) the next ten most relevant results is easy to construct.
3163 This may be used to obtain the data required for a search applications second
3164 and subsequent pages of results.
3165
3166 </p><p>
3167 The next block contains an example rank function that uses matchinfo data
3168 implemented in C. Instead of a single weight, it allows a weight to be
3169 externally assigned to each column of each document. It may be registered
3170 with SQLite like any other user function using <a href="c3ref/create_function.html">sqlite3_create_function</a>.
3171
3172 </p><p><b> Security Warning:</b> Because it is just an ordinary SQL function,
3173 rank() may be invoked as part of any SQL query in any context. This means
3174 that the first argument passed may not be a valid matchinfo blob.
3175 Implementors should take care to handle this case without causing buffer
3176 overruns or other potential security problems.
3177
3178 </p><div class="codeblock"><pre><i>/*</i>
3179 <i>** SQLite user defined function to use with matchinfo() to calculate the</i>
3180 <i>** relevancy of an FTS match. The value returned is the relevancy score</i>
3181 <i>** (a real value greater than or equal to zero). A larger value indicates </i>
3182 <i>** a more relevant document.</i>
3183 <i>**</i>
3184 <i>** The overall relevancy returned is the sum of the relevancies of each </i>
3185 <i>** column value in the FTS table. The relevancy of a column value is the</i>
3186 <i>** sum of the following for each reportable phrase in the FTS query:</i>
3187 <i>**</i>
3188 <i>** (&lt;hit count&gt; / &lt;global hit count&gt;) * &lt;column weight&gt;</i>
3189 <i>**</i>
3190 <i>** where &lt;hit count&gt; is the number of instances of the phrase in the</i>
3191 <i>** column value of the current row and &lt;global hit count&gt; is the number</i>
3192 <i>** of instances of the phrase in the same column of all rows in the FTS</i>
3193 <i>** table. The &lt;column weight&gt; is a weighting factor assigned to each</i>
3194 <i>** column by the caller (see below).</i>
3195 <i>**</i>
3196 <i>** The first argument to this function must be the return value of the FTS </i>
3197 <i>** matchinfo() function. Following this must be one argument for each column </i>
3198 <i>** of the FTS table containing a numeric weight factor for the corresponding </i>
3199 <i>** column. Example:</i>
3200 <i>**</i>
3201 <i>** CREATE VIRTUAL TABLE documents USING fts3(title, content)</i>
3202 <i>**</i>
3203 <i>** The following query returns the docids of documents that match the full-text</i>
3204 <i>** query &lt;query&gt; sorted from most to least relevant. When calculating</i>
3205 <i>** relevance, query term instances in the 'title' column are given twice the</i>
3206 <i>** weighting of those in the 'content' column.</i>
3207 <i>**</i>
3208 <i>** SELECT docid FROM documents </i>
3209 <i>** WHERE documents MATCH &lt;query&gt; </i>
3210 <i>** ORDER BY rank(matchinfo(documents), 1.0, 0.5) DESC</i>
3211 <i>*/</i>
3212 static void rankfunc(sqlite3_context *pCtx, int nVal, sqlite3_value **apVal){
3213 int *aMatchinfo; <i>/* Return value of matchinfo() */</i>
3214 int nMatchinfo; <i>/* Number of elements in aMatchinfo&#x5B;&#x5D; */</i>
3215 int nCol = 0; <i>/* Number of columns in the table */</i>
3216 int nPhrase = 0; <i>/* Number of phrases in the query */</i>
3217 int iPhrase; <i>/* Current phrase */</i>
3218 double score = 0.0; <i>/* Value to return */</i>
3219
3220 assert( sizeof(int)==4 );
3221
3222 <i> /* Check that the number of arguments passed to this function is correct.</i>
3223 <i> ** If not, jump to wrong_number_args. Set aMatchinfo to point to the array</i>
3224 <i> ** of unsigned integer values returned by FTS function matchinfo. Set</i>
3225 <i> ** nPhrase to contain the number of reportable phrases in the users full-text</i>
3226 <i> ** query, and nCol to the number of columns in the table. Then check that the</i>
3227 <i> ** size of the matchinfo blob is as expected. Return an error if it is not.</i>
3228 <i> */</i>
3229 if( nVal&lt;1 ) goto wrong_number_args;
3230 aMatchinfo = (unsigned int *)sqlite3_value_blob(apVal&#x5B;0&#x5D;);
3231 nMatchinfo = sqlite3_value_bytes(apVal&#x5B;0&#x5D;) / sizeof(int);
3232 if( nMatchinfo&gt;=2 ){
3233 nPhrase = aMatchinfo&#x5B;0&#x5D;;
3234 nCol = aMatchinfo&#x5B;1&#x5D;;
3235 }
3236 if( nMatchinfo!=(2+3*nCol*nPhrase) ){
3237 sqlite3_result_error(pCtx,
3238 "invalid matchinfo blob passed to function rank()", -1);
3239 return;
3240 }
3241 if( nVal!=(1+nCol) ) goto wrong_number_args;
3242
3243 <i> /* Iterate through each phrase in the users query. */</i>
3244 for(iPhrase=0; iPhrase&lt;nPhrase; iPhrase++){
3245 int iCol; <i>/* Current column */</i>
3246
3247 <i> /* Now iterate through each column in the users query. For each column,</i>
3248 <i> ** increment the relevancy score by:</i>
3249 <i> **</i>
3250 <i> ** (&lt;hit count&gt; / &lt;global hit count&gt;) * &lt;column weight&gt;</i>
3251 <i> **</i>
3252 <i> ** aPhraseinfo&#x5B;&#x5D; points to the start of the data for phrase iPhrase. So</i>
3253 <i> ** the hit count and global hit counts for each column are found in </i>
3254 <i> ** aPhraseinfo&#x5B;iCol*3&#x5D; and aPhraseinfo&#x5B;iCol*3+1&#x5D;, respectively.</i>
3255 <i> */</i>
3256 int *aPhraseinfo = &aMatchinfo&#x5B;2 + iPhrase*nCol*3&#x5D;;
3257 for(iCol=0; iCol&lt;nCol; iCol++){
3258 int nHitCount = aPhraseinfo&#x5B;3*iCol&#x5D;;
3259 int nGlobalHitCount = aPhraseinfo&#x5B;3*iCol+1&#x5D;;
3260 double weight = sqlite3_value_double(apVal&#x5B;iCol+1&#x5D;);
3261 if( nHitCount>0 ){
3262 score += ((double)nHitCount / (double)nGlobalHitCount) * weight;
3263 }
3264 }
3265 }
3266
3267 sqlite3_result_double(pCtx, score);
3268 return;
3269
3270 <i> /* Jump here if the wrong number of arguments are passed to this function */</i>
3271 wrong_number_args:
3272 sqlite3_result_error(pCtx, "wrong number of arguments to function rank()", -1);
3273 }
3274 </pre></div>
3275 <p align="center"><small><i>This page was last updated on 2025-06-12 09:10:23Z </small></i></p>
3276