Annotation of embedaddon/sqlite3/ext/fts3/README.content, revision 1.1.1.1
1.1 misho 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.
177:
178:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to README.content CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / ext / fts3