*** 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 provided. 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 =fts3= 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);
***************
*** 33,58 ****
**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');
--- 33,58 ----
**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 perform a full table scan over all rows.
! In addition to the explicitly-enumerated columns, the following columns are implicitely 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 with the same name as the table, 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 one is generated in the same way as values are generated for =INTEGER PRIMARY KEY= columns in 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 =INSERT= statements, but providing both 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 is 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 =docid=, the following 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 column =ingredients= on the left, this only finds recipes where the tokens are present in that column. A search over all columns can be done using the column with the same name as the table:
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';
***************
*** 120,126 ****
SELECT * FROM recipe WHERE recipe MATCH 'bu*';
! *NOTE* that the _table-name_ column will not participate if you alias a table in a statement. So this statement will not work:
SELECT docid FROM recipe AS ra WHERE ra MATCH 'stew';
--- 120,126 ----
SELECT * FROM recipe WHERE recipe MATCH 'bu*';
! *NOTE* that the table-named column will not participate if you alias a table in a statement. So this statement will not work:
SELECT docid FROM recipe AS ra WHERE ra MATCH 'stew';
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|