*** 1,4 ****
! This page documents the SQL-level interface to the full-text-search module. The version documented is fts3, though fts2 and fts1 usage is similar.
Status: *DRAFT*. I consider this information correct, but haven't done a final review.
--- 1,4 ----
! This page documents the SQL-level interface to the full-text-search module. =fts3= usage is documented, =fts2= and =fts1= are mostly identical.
Status: *DRAFT*. I consider this information correct, but haven't done a final review.
***************
*** 10,16 ****
_: _tokenizer_ ::= *SIMPLE* | *PORTER* | _user-defined_
! If no tokenizer is requested, then the simple tokenizer is used. Additional user-defined tokenizers may be built into a particular SQLite library, *SIMPLE* and *PORTER* are the only tokenizers provided by default. If no column names are requested, then the single column *content* is used. Thus, the following statements all create a table with a single column *content* using tokenizer *simple*:
CREATE VIRTUAL TABLE t USING fts3;
CREATE VIRTUAL TABLE t USING fts3();
--- 10,16 ----
_: _tokenizer_ ::= *SIMPLE* | *PORTER* | _user-defined_
! If no tokenizer is requested, then the *SIMPLE* tokenizer is used. Additional user-defined tokenizers may be built into a particular SQLite library, *SIMPLE* and *PORTER* are the only tokenizers provided by default. If no column names are requested, then the single column *content* is used. Thus, the following statements all create a table with a single column *content* using tokenizer *simple*:
CREATE VIRTUAL TABLE t USING fts3;
CREATE VIRTUAL TABLE t USING fts3();
***************
*** 18,24 ****
CREATE VIRTUAL TABLE t USING fts3(TOKENIZE simple);
CREATE VIRTUAL TABLE t USING fts3(content, TOKENIZE simple);
! The column names 'docid' and 'tokenize' are reserved and should not be used in fts create statements. Multiple column names can be provided:
CREATE VIRTUAL TABLE t USING fts3(name, address);
--- 18,24 ----
CREATE VIRTUAL TABLE t USING fts3(TOKENIZE simple);
CREATE VIRTUAL TABLE t USING fts3(content, TOKENIZE simple);
! The column names *docid* and *tokenize* are reserved and should not be used in =fts3= create statements. Multiple column names can be provided:
CREATE VIRTUAL TABLE t USING fts3(name, address);
***************
*** 27,58 ****
CREATE VIRTUAL TABLE t USING fts3(name);
CREATE VIRTUAL TABLE t USING fts3(name NOT NULL UNIQUE);
! In other words, any additional information, including constraints and type information, is ignored.
! Destroying an fts table uses the standard SQLite {link: http://www.sqlite.org/lang_droptable.html DROP TABLE} syntax.
! **Accessing fts tables**
! Fts tables are accessed using *SELECT*, *INSERT*, *UPDATE*, and *DELETE* statements exactly as regular SQLite tables. The set of columns which can be referenced are exactly those enumerated in the create statement, all of those columns will appear to be of type TEXT. The table has the implicit unique index on rowid, plus the full-text index (see next section), but otherwise allows for no indices. A query such as:
SELECT rowid FROM t WHERE content LIKE 'This%';
will work by running a full table scan over all rows.
! In addition to the explicitely enumerated columns, the following implicit columns are present:
rowid - a unique 64-bit integer per row, as for a regular table.
docid - the preferred alias for rowid.
_table-name_ - a "magic" column used for snippet generation.
! These columns should not be referenced in the create statement, and will not appear to exist except when explicitely referenced by name in SELECT or INSERT statements.
! When inserting into an fts table, if no docid is provided, then a docid is generated in the same way a rowid is generated for all SQLite tables. This docid can be accessed using LAST_INSERT_ROWID():
INSERT INTO t VALUES ('This is a test');
SELECT LAST_INSERT_ROWID();
! The rowid or docid can be provided to the INSERT statement, but providing both rowid and docid is an error:
INSERT INTO t (rowid, content) VALUES (10, 'This is a test');
INSERT INTO t (docid, content) VALUES (11, 'This is a test');
--- 27,58 ----
CREATE VIRTUAL TABLE t USING fts3(name);
CREATE VIRTUAL TABLE t USING fts3(name NOT NULL UNIQUE);
! Any additional information, including constraints and type information, is ignored.
! Destroying an =fts= table uses the standard SQLite {link: http://www.sqlite.org/lang_droptable.html DROP TABLE} syntax.
! **Accessing =fts= tables**
! =fts= tables are accessed using *SELECT*, *INSERT*, *UPDATE*, and *DELETE* statements exactly as regular SQLite tables. The set of columns which can be referenced are exactly those enumerated in the create statement, all of which will appear to be of type TEXT. The table has the implicit unique index on =rowid=, plus the full-text index (see next section), but otherwise allows for no indices. A query such as:
SELECT rowid FROM t WHERE content LIKE 'This%';
will work by running a full table scan over all rows.
! In addition to the explicitly enumerated columns, the following implicit columns are present:
rowid - a unique 64-bit integer per row, as for a regular table.
docid - the preferred alias for rowid.
_table-name_ - a "magic" column used for snippet generation.
! These columns should not be referenced in the create statement, and will not appear to exist except when explicitly referenced by name in *SELECT* or *INSERT* statements.
! When inserting into an =fts= table, if no =docid= is provided, then a =docid= is generated in the same way a =rowid= is generated for regular SQLite tables. This =docid= can be accessed using *LAST_INSERT_ROWID()*:
INSERT INTO t VALUES ('This is a test');
SELECT LAST_INSERT_ROWID();
! The =rowid= or =docid= can be provided to the *INSERT* statement, but providing both =rowid= and =docid= is an error:
INSERT INTO t (rowid, content) VALUES (10, 'This is a test');
INSERT INTO t (docid, content) VALUES (11, 'This is a test');
***************
*** 61,74 ****
-- Throws an error
INSERT INTO t (docid, rowid, content) VALUES (10, 11, 'This is a test');
! Fts tables do not allow *UPDATE* on the docid. So the following two statements throw an error:
UPDATE t SET docid = docid + 1 WHERE content = 'This is a test';
UPDATE t SET rowid = rowid + 1 WHERE content = 'This is a test';
**Using the Fulltext Index**
! Fts tokenizes its input data and builds an index over the resulting terms. To do queries using this index, a new *MATCH* operator was introduced. An example fts table:
CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients);
INSERT INTO recipe VALUES (
--- 61,74 ----
-- Throws an error
INSERT INTO t (docid, rowid, content) VALUES (10, 11, 'This is a test');
! =fts= tables do not allow *UPDATE* on the =docid=. So the following two statements throw an error:
UPDATE t SET docid = docid + 1 WHERE content = 'This is a test';
UPDATE t SET rowid = rowid + 1 WHERE content = 'This is a test';
**Using the Fulltext Index**
! =fts= tokenizes its input data and builds an index over the resulting terms. To do queries using this index, a new *MATCH* operator is introduced. An example =fts= table:
CREATE VIRTUAL TABLE recipe USING fts3(name, ingredients);
INSERT INTO recipe VALUES (
***************
*** 88,98 ****
SELECT docid FROM recipe WHERE ingredients MATCH 'onions cheese';
! The string to the right of the MATCH is tokenized, each token is found in the index, and the set of documents where *both* tokens are present is returned. Since the MATCH has 'ingredients' on the left, this only finds recipes where the tokens are present in the ingredients column. A search over all columns uses the _table-name_ column:
SELECT docid FROM recipe WHERE recipe MATCH 'stew';
! The query language for the right-hand-side of the MATCH operator has a few variants, here described by example:
-- Recipes with 'onions' and 'cheese' each in any column of the row.
SELECT * FROM recipe WHERE recipe MATCH 'onions cheese';
--- 88,98 ----
SELECT docid FROM recipe WHERE ingredients MATCH 'onions cheese';
! The string to the right of the *MATCH* is tokenized, each token is found in the index, and the set of documents where both tokens are present is returned. Since the *MATCH* has 'ingredients' on the left, this only finds recipes where the tokens are present in the ingredients column. A search over all columns uses the _table-name_ column:
SELECT docid FROM recipe WHERE recipe MATCH 'stew';
! The query language for the right-hand-side of the *MATCH* operator has a few variants, here described by example:
-- Recipes with 'onions' and 'cheese' each in any column of the row.
SELECT * FROM recipe WHERE recipe MATCH 'onions cheese';
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|