| 1 |
|
| 2 |
FTS4 CONTENT OPTION |
| 3 |
|
| 4 |
Normally, in order to create a full-text index on a dataset, the FTS4 |
| 5 |
module stores a copy of all indexed documents in a specially created |
| 6 |
database table. |
| 7 |
|
| 8 |
As of SQLite version 3.7.9, FTS4 supports a new option - "content" - |
| 9 |
designed to extend FTS4 to support the creation of full-text indexes where: |
| 10 |
|
| 11 |
* The indexed documents are not stored within the SQLite database |
| 12 |
at all (a "contentless" FTS4 table), or |
| 13 |
|
| 14 |
* The indexed documents are stored in a database table created and |
| 15 |
managed by the user (an "external content" FTS4 table). |
| 16 |
|
| 17 |
Because the indexed documents themselves are usually much larger than |
| 18 |
the full-text index, the content option can sometimes be used to achieve |
| 19 |
significant space savings. |
| 20 |
|
| 21 |
CONTENTLESS FTS4 TABLES |
| 22 |
|
| 23 |
In order to create an FTS4 table that does not store a copy of the indexed |
| 24 |
documents at all, the content option should be set to an empty string. |
| 25 |
For example, the following SQL creates such an FTS4 table with three |
| 26 |
columns - "a", "b", and "c": |
| 27 |
|
| 28 |
CREATE VIRTUAL TABLE t1 USING fts4(content="", a, b, c); |
| 29 |
|
| 30 |
Data can be inserted into such an FTS4 table using an INSERT statements. |
| 31 |
However, unlike ordinary FTS4 tables, the user must supply an explicit |
| 32 |
integer docid value. For example: |
| 33 |
|
| 34 |
-- This statement is Ok: |
| 35 |
INSERT INTO t1(docid, a, b, c) VALUES(1, 'a b c', 'd e f', 'g h i'); |
| 36 |
|
| 37 |
-- This statement causes an error, as no docid value has been provided: |
| 38 |
INSERT INTO t1(a, b, c) VALUES('j k l', 'm n o', 'p q r'); |
| 39 |
|
| 40 |
It is not possible to UPDATE or DELETE a row stored in a contentless FTS4 |
| 41 |
table. Attempting to do so is an error. |
| 42 |
|
| 43 |
Contentless FTS4 tables also support SELECT statements. However, it is |
| 44 |
an error to attempt to retrieve the value of any table column other than |
| 45 |
the docid column. The auxiliary function matchinfo() may be used, but |
| 46 |
snippet() and offsets() may not. For example: |
| 47 |
|
| 48 |
-- The following statements are Ok: |
| 49 |
SELECT docid FROM t1 WHERE t1 MATCH 'xxx'; |
| 50 |
SELECT docid FROM t1 WHERE a MATCH 'xxx'; |
| 51 |
SELECT matchinfo(t1) FROM t1 WHERE t1 MATCH 'xxx'; |
| 52 |
|
| 53 |
-- The following statements all cause errors, as the value of columns |
| 54 |
-- other than docid are required to evaluate them. |
| 55 |
SELECT * FROM t1; |
| 56 |
SELECT a, b FROM t1 WHERE t1 MATCH 'xxx'; |
| 57 |
SELECT docid FROM t1 WHERE a LIKE 'xxx%'; |
| 58 |
SELECT snippet(t1) FROM t1 WHERE t1 MATCH 'xxx'; |
| 59 |
|
| 60 |
Errors related to attempting to retrieve column values other than docid |
| 61 |
are runtime errors that occur within sqlite3_step(). In some cases, for |
| 62 |
example if the MATCH expression in a SELECT query matches zero rows, there |
| 63 |
may be no error at all even if a statement does refer to column values |
| 64 |
other than docid. |
| 65 |
|
| 66 |
EXTERNAL CONTENT FTS4 TABLES |
| 67 |
|
| 68 |
An "external content" FTS4 table is similar to a contentless table, except |
| 69 |
that if evaluation of a query requires the value of a column other than |
| 70 |
docid, FTS4 attempts to retrieve that value from a table (or view, or |
| 71 |
virtual table) nominated by the user (hereafter referred to as the "content |
| 72 |
table"). The FTS4 module never writes to the content table, and writing |
| 73 |
to the content table does not affect the full-text index. It is the |
| 74 |
responsibility of the user to ensure that the content table and the |
| 75 |
full-text index are consistent. |
| 76 |
|
| 77 |
An external content FTS4 table is created by setting the content option |
| 78 |
to the name of a table (or view, or virtual table) that may be queried by |
| 79 |
FTS4 to retrieve column values when required. If the nominated table does |
| 80 |
not exist, then an external content table behaves in the same way as |
| 81 |
a contentless table. For example: |
| 82 |
|
| 83 |
CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c); |
| 84 |
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", a, c); |
| 85 |
|
| 86 |
Assuming the nominated table does exist, then its columns must be the same |
| 87 |
as or a superset of those defined for the FTS table. |
| 88 |
|
| 89 |
When a users query on the FTS table requires a column value other than |
| 90 |
docid, FTS attempts to read this value from the corresponding column of |
| 91 |
the row in the content table with a rowid value equal to the current FTS |
| 92 |
docid. Or, if such a row cannot be found in the content table, a NULL |
| 93 |
value is used instead. For example: |
| 94 |
|
| 95 |
CREATE TABLE t2(id INTEGER PRIMARY KEY, a, b, c, d); |
| 96 |
CREATE VIRTUAL TABLE t3 USING fts4(content="t2", b, c); |
| 97 |
|
| 98 |
INSERT INTO t2 VALUES(2, 'a b', 'c d', 'e f'); |
| 99 |
INSERT INTO t2 VALUES(3, 'g h', 'i j', 'k l'); |
| 100 |
INSERT INTO t3(docid, b, c) SELECT id, b, c FROM t2; |
| 101 |
|
| 102 |
-- The following query returns a single row with two columns containing |
| 103 |
-- the text values "i j" and "k l". |
| 104 |
-- |
| 105 |
-- The query uses the full-text index to discover that the MATCH |
| 106 |
-- term matches the row with docid=3. It then retrieves the values |
| 107 |
-- of columns b and c from the row with rowid=3 in the content table |
| 108 |
-- to return. |
| 109 |
-- |
| 110 |
SELECT * FROM t3 WHERE t3 MATCH 'k'; |
| 111 |
|
| 112 |
-- Following the UPDATE, the query still returns a single row, this |
| 113 |
-- time containing the text values "xxx" and "yyy". This is because the |
| 114 |
-- full-text index still indicates that the row with docid=3 matches |
| 115 |
-- the FTS4 query 'k', even though the documents stored in the content |
| 116 |
-- table have been modified. |
| 117 |
-- |
| 118 |
UPDATE t2 SET b = 'xxx', c = 'yyy' WHERE rowid = 3; |
| 119 |
SELECT * FROM t3 WHERE t3 MATCH 'k'; |
| 120 |
|
| 121 |
-- Following the DELETE below, the query returns one row containing two |
| 122 |
-- NULL values. NULL values are returned because FTS is unable to find |
| 123 |
-- a row with rowid=3 within the content table. |
| 124 |
-- |
| 125 |
DELETE FROM t2; |
| 126 |
SELECT * FROM t3 WHERE t3 MATCH 'k'; |
| 127 |
|
| 128 |
When a row is deleted from an external content FTS4 table, FTS4 needs to |
| 129 |
retrieve the column values of the row being deleted from the content table. |
| 130 |
This is so that FTS4 can update the full-text index entries for each token |
| 131 |
that occurs within the deleted row to indicate that that row has been |
| 132 |
deleted. If the content table row cannot be found, or if it contains values |
| 133 |
inconsistent with the contents of the FTS index, the results can be difficult |
| 134 |
to predict. The FTS index may be left containing entries corresponding to the |
| 135 |
deleted row, which can lead to seemingly nonsensical results being returned |
| 136 |
by subsequent SELECT queries. The same applies when a row is updated, as |
| 137 |
internally an UPDATE is the same as a DELETE followed by an INSERT. |
| 138 |
|
| 139 |
Instead of writing separately to the full-text index and the content table, |
| 140 |
some users may wish to use database triggers to keep the full-text index |
| 141 |
up to date with respect to the set of documents stored in the content table. |
| 142 |
For example, using the tables from earlier examples: |
| 143 |
|
| 144 |
CREATE TRIGGER t2_bu BEFORE UPDATE ON t2 BEGIN |
| 145 |
DELETE FROM t3 WHERE docid=old.rowid; |
| 146 |
END; |
| 147 |
CREATE TRIGGER t2_bd BEFORE DELETE ON t2 BEGIN |
| 148 |
DELETE FROM t3 WHERE docid=old.rowid; |
| 149 |
END; |
| 150 |
|
| 151 |
CREATE TRIGGER t2_bu AFTER UPDATE ON t2 BEGIN |
| 152 |
INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); |
| 153 |
END; |
| 154 |
CREATE TRIGGER t2_bd AFTER INSERT ON t2 BEGIN |
| 155 |
INSERT INTO t3(docid, b, c) VALUES(new.rowid, new.b, new.c); |
| 156 |
END; |
| 157 |
|
| 158 |
The DELETE trigger must be fired before the actual delete takes place |
| 159 |
on the content table. This is so that FTS4 can still retrieve the original |
| 160 |
values in order to update the full-text index. And the INSERT trigger must |
| 161 |
be fired after the new row is inserted, so as to handle the case where the |
| 162 |
rowid is assigned automatically within the system. The UPDATE trigger must |
| 163 |
be split into two parts, one fired before and one after the update of the |
| 164 |
content table, for the same reasons. |
| 165 |
|
| 166 |
FTS4 features a special command similar to the 'optimize' command that |
| 167 |
deletes the entire full-text index and rebuilds it based on the current |
| 168 |
set of documents in the content table. Assuming again that "t3" is the |
| 169 |
name of the external content FTS4 table, the command is: |
| 170 |
|
| 171 |
INSERT INTO t3(t3) VALUES('rebuild'); |
| 172 |
|
| 173 |
This command may also be used with ordinary FTS4 tables, although it may |
| 174 |
only be useful if the full-text index has somehow become corrupt. It is an |
| 175 |
error to attempt to rebuild the full-text index maintained by a contentless |
| 176 |
FTS4 table. |