Overview

1. Introduction to FTS3 and FTS4

CREATE VIRTUAL TABLE enrondata1 USING fts3(content TEXT);     /* FTS3 table */
CREATE TABLE enrondata2(content TEXT);                        /* Ordinary table */

SELECT count(*) FROM enrondata1 WHERE content MATCH 'linux';  /* 0.03 seconds */
SELECT count(*) FROM enrondata2 WHERE content LIKE '%linux%'; /* 22.5 seconds */

1.1. Differences between FTS3 and FTS4

• FTS4 contains query performance optimizations that may significantly improve the performance of full-text queries that contain terms that are very common (present in a large percentage of table rows).
• FTS4 supports some additional options that may used with the matchinfo() function.
• Because it stores extra information on disk in two new shadow tables in order to support the performance optimizations and extra matchinfo() options, FTS4 tables may consume more disk space than the equivalent table created using FTS3. Usually the overhead is 1-2% or less, but may be as high as 10% if the documents stored in the FTS table are very small. The overhead may be reduced by specifying the directive "matchinfo=fts3" as part of the FTS4 table declaration, but this comes at the expense of sacrificing some of the extra supported matchinfo() options.
• FTS4 provides hooks (the compress and uncompress options) allowing data to be stored in a compressed form, reducing disk usage and IO.

1.2. Creating and Destroying FTS Tables

CREATE VIRTUAL TABLE data USING fts3();


CREATE VIRTUAL TABLE pages USING fts4(title, keywords, body);


CREATE VIRTUAL TABLE mail USING fts3(
  subject VARCHAR(256) NOT NULL,
  body TEXT CHECK(length(body)<10240)
);

CREATE VIRTUAL TABLE papers USING fts3(author, document, tokenize=porter);



CREATE VIRTUAL TABLE data USING fts4(tokenize=simple);



CREATE VIRTUAL TABLE names USING fts3(a, b, tokenize=icu en_AU);

CREATE VIRTUAL TABLE data USING fts4();
DROP TABLE data;

1.3. Populating FTS Tables

CREATE VIRTUAL TABLE pages USING fts4(title, body);


INSERT INTO pages(docid, title, body) VALUES(53, 'Home Page', 'SQLite is a software...');




INSERT INTO pages(title, body) VALUES('Download', 'All SQLite source code...');


UPDATE pages SET title = 'Download SQLite' WHERE rowid = 54;


DELETE FROM pages;



INSERT INTO pages(rowid, docid, title, body) VALUES(1, 2, 'A title', 'A document body');

INSERT INTO docs(docs) VALUES('optimize');

1.4. Simple FTS Queries

• Query by rowid. If the WHERE clause of the SELECT statement contains a sub-clause of the form "rowid = ?", where ? is an SQL expression, FTS is able to retrieve the requested row directly using the equivalent of an SQLite INTEGER PRIMARY KEY index.
• Full-text query. If the WHERE clause of the SELECT statement contains a sub-clause of the form "<column> MATCH ?", FTS is able to use the built-in full-text index to restrict the search to those documents that match the full-text query string specified as the right-hand operand of the MATCH clause.

CREATE VIRTUAL TABLE mail USING fts3(subject, body);

SELECT * FROM mail WHERE rowid = 15;                
SELECT * FROM mail WHERE body MATCH 'sqlite';       
SELECT * FROM mail WHERE mail MATCH 'search';       
SELECT * FROM mail WHERE rowid BETWEEN 15 AND 20;   
SELECT * FROM mail WHERE subject = 'database';      
SELECT * FROM mail WHERE subject MATCH 'database';  

CREATE VIRTUAL TABLE mail USING fts3(subject, body);


INSERT INTO mail(docid, subject, body) VALUES(1, 'software feedback', 'found it too slow');
INSERT INTO mail(docid, subject, body) VALUES(2, 'software feedback', 'no feedback');
INSERT INTO mail(docid, subject, body) VALUES(3, 'slow lunch order',  'was a software problem');


SELECT * FROM mail WHERE subject MATCH 'software';    
SELECT * FROM mail WHERE body    MATCH 'feedback';    
SELECT * FROM mail WHERE mail    MATCH 'software';    
SELECT * FROM mail WHERE mail    MATCH 'slow';        

CREATE VIRTUAL TABLE docs USING fts4(content);


SELECT * FROM docs WHERE docs MATCH 'sqlite';              
SELECT * FROM docs WHERE docs.docs MATCH 'sqlite';         
SELECT * FROM docs WHERE main.docs.docs MATCH 'sqlite';    
SELECT * FROM docs WHERE main.docs MATCH 'sqlite';         

1.5. Summary

1. As with all virtual table types, it is not possible to create indices or triggers attached to FTS tables. Nor is it possible to use the ALTER TABLE command to add extra columns to FTS tables (although it is possible to use ALTER TABLE to rename an FTS table).
2. Data-types specified as part of the "CREATE VIRTUAL TABLE" statement used to create an FTS table are ignored completely. Instead of the normal rules for applying type affinity to inserted values, all values inserted into FTS table columns (except the special rowid column) are converted to type TEXT before being stored.
3. FTS tables permit the special alias "docid" to be used to refer to the rowid column supported by all virtual tables.
4. The FTS MATCH operator is supported for queries based on the built-in full-text index.
5. The FTS auxiliary functions, snippet(), offsets(), and matchinfo() are available to support full-text queries.
6. Every FTS table has a hidden column with the same name as the table itself. The value contained in each row for the hidden column is a blob that is only useful as the left operand of a MATCH operator, or as the left-most argument to one of the FTS auxiliary functions.

2. Compiling and Enabling FTS3 and FTS4

-DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS

CPPFLAGS="-DSQLITE_ENABLE_FTS3 -DSQLITE_ENABLE_FTS3_PARENTHESIS" ./configure <configure options>

-DSQLITE_ENABLE_ICU

3. Full-text Index Queries

CREATE VIRTUAL TABLE docs USING fts3(title, body);


SELECT * FROM docs WHERE docs MATCH 'linux';




SELECT * FROM docs WHERE docs MATCH 'lin*';

• Normally, a token or token prefix query is matched against the FTS table column specified as the right-hand side of the MATCH operator. Or, if the special column with the same name as the FTS table itself is specified, against all columns. This may be overridden by specifying a column-name followed by a ":" character before a basic term query. There may be space between the ":" and the term to query for, but not between the column-name and the ":" character. For example:

SELECT * FROM docs WHERE docs MATCH 'title:linux problems';



.

SELECT * FROM docs WHERE body MATCH 'title:linux driver';

• Phrase queries. A phrase query is a query that retrieves all documents that contain a nominated set of terms or term prefixes in a specified order with no intervening tokens. Phrase queries are specified by enclosing a space separated sequence of terms or term prefixes in double quotes ("). For example:

SELECT * FROM docs WHERE docs MATCH '"linux applications"';




SELECT * FROM docs WHERE docs MATCH '"lin* app*"';

• NEAR queries. A NEAR query is a query that returns documents that contain a two or more nominated terms or phrases within a specified proximity of each other (by default with 10 or less intervening terms). A NEAR query is specified by putting the keyword "NEAR" between two phrase, term or prefix queries. To specify a proximity other than the default, an operator of the form "NEAR/<N>" may be used, where <N> is the maximum number of intervening terms allowed. For example:

CREATE VIRTUAL TABLE docs USING fts4();


INSERT INTO docs VALUES('SQLite is an ACID compliant embedded relational database management system');



 
.
SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR database';





SELECT * FROM docs WHERE docs MATCH 'database NEAR/6 sqlite';



SELECT * FROM docs WHERE docs MATCH 'database NEAR/5 sqlite';




SELECT * FROM docs WHERE docs MATCH 'database NEAR/2 "ACID compliant"';




SELECT * FROM docs WHERE docs MATCH '"ACID compliant" NEAR/2 sqlite';

• More than one NEAR operator may appear in a single query. In this case each pair of terms or phrases separated by a NEAR operator must appear within the specified proximity of each other in the document. Using the same table and data as in the block of examples above:

 
 
 
 SELECT * FROM docs WHERE docs MATCH 'sqlite NEAR/2 acid NEAR/2 relational';

 
 
 
 SELECT * FROM docs WHERE docs MATCH 'acid NEAR/2 sqlite NEAR/2 relational';

• The AND operator determines the intersection of two sets of documents.
• The OR operator calculates the union of two sets of documents.
• The NOT operator (or, if using the standard syntax, a unary "-" operator) may be used to compute the relative complement of one set of documents with respect to another.

3.1. Set Operations Using The Enhanced Query Syntax

CREATE VIRTUAL TABLE docs USING fts3();


INSERT INTO docs(docid, content) VALUES(1, 'a database is a software system');
INSERT INTO docs(docid, content) VALUES(2, 'sqlite is a software system');
INSERT INTO docs(docid, content) VALUES(3, 'sqlite is a database');



SELECT * FROM docs WHERE docs MATCH 'sqlite AND database';




SELECT * FROM docs WHERE docs MATCH 'database sqlite';



SELECT * FROM docs WHERE docs MATCH 'sqlite OR database';



SELECT * FROM docs WHERE docs MATCH 'database NOT sqlite';






SELECT * FROM docs WHERE docs MATCH 'database and sqlite';

SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database OR library';


SELECT docid FROM docs WHERE docs MATCH 'sqlite AND database'
  UNION
SELECT docid FROM docs WHERE docs MATCH 'library';



SELECT docid FROM docs WHERE docs MATCH '("sqlite database" OR "sqlite library") AND linux';


SELECT docid FROM docs WHERE docs MATCH 'linux'
  INTERSECT
SELECT docid FROM (
  SELECT docid FROM docs WHERE docs MATCH '"sqlite library"'
    UNION
  SELECT docid FROM docs WHERE docs MATCH '"sqlite database"'
);

3.2. Set Operations Using The Standard Query Syntax

1. Only the implicit version of the AND operator is supported. Specifying the string "AND" as part of a standard query syntax query is interpreted as a term query for the set of documents containing the term "and".

1. Parenthesis are not supported.

1. The NOT operator is not supported. Instead of the NOT operator, the standard query syntax supports a unary "-" operator that may be applied to basic term and term-prefix queries (but not to phrase or NEAR queries). A term or term-prefix that has a unary "-" operator attached to it may not appear as an operand to an OR operator. An FTS query may not consist entirely of terms or term-prefix queries with unary "-" operators attached to them.

SELECT * FROM docs WHERE docs MATCH 'sqlite -database';

1. The relative precedence of the set operations is different. In particular, using the standard query syntax the "OR" operator has a higher precedence than "AND". The precedence of operators when using the standard query syntax is:

1. The following example illustrates precedence of operators using the standard query syntax:

SELECT * FROM docs WHERE docs MATCH 'sqlite OR database library';

4. Auxiliary Functions - Snippet, Offsets and Matchinfo

SELECT offsets(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT snippet(mail) FROM mail WHERE mail MATCH <full-text query expression>;
SELECT matchinfo(mail) FROM mail WHERE mail MATCH <full-text query expression>;

1. If a matchable phrase is part of a series of phrases connected by NEAR operators in the FTS query expression, then each phrase match must be sufficiently close to other phrase matches of the relevant types to satisfy the NEAR condition.
2. If the matchable phrase in the FTS query is restricted to matching data in a specified FTS table column, then only phrase matches that occur within that column are considered.

4.1. The Offsets Function