*** 1,18 ****
<html>
<h1 align="center">
Full-text Search for SQLite<br>
! (as of 2006-08-23)<br>
</h1>
</html>
! This is preliminary documentation for a full-text indexing module for SQLite
! **Getting the full-text module**
! The full-text module is in the SQLite CVS tree in the ext/fts1 directory.
! Building
! The module can be built either as a standalone shared library, or statically linked into the SQLite library.
*As a shared library*
--- 1,17 ----
<html>
<h1 align="center">
Full-text Search for SQLite<br>
! (as of 2006-09-19)<br>
</h1>
</html>
! **Introduction**
! The module fts1 adds full-text indexing capabilities to SQLite. It is not yet included in the SQLite binary distrubution; you can find it in the SQLite CVS tree in the ext/fts1 directory.
! **Building fts1**
! fts1 can be built either as a standalone shared library, or statically linked into the SQLite library.
*As a shared library*
***************
*** 20,100 ****
*Statically linked*
! To statically link the module into SQLite, add all .c files from the fts1 directory to the Makefile you use to compile SQLite so that they will be linked into the SQLite image. You must define the preprocessor symbol SQLITE_CORE when compiling these files.
! **Initializing**
! *As a shared library*
!
! When the module is built as a shared library, you can load it into SQLite using the ".load" shell command.
! sqlite> .load fulltext
Or you can load it using a SELECT statement:
! SELECT load_extension('fulltext');
Note that you may need to call sqlite3_enable_load_extension before loading the extension; see the SQLite LoadableExtensions documentation.
! *Statically linked*
! In a static build, you must call the fulltext_init function to be able to use full-text indexing on any given database connection. This function is defined in fulltext.h and has the following prototype:
! int fulltext_init(sqlite3 *db);
! fulltext_init returns SQLITE_OK on success or an SQLite error code otherwise.
! **Using**
! Full-text tables store fully indexed text. You can create a full-text table using the CREATE VIRTUAL TABLE statement:
! sqlite>create virtual table foo using fulltext;
! A full-text table has a single column content of type text:
! sqlite>insert into foo (content) values ('to sleep perchance to dream');
! sqlite>insert into foo (content) values ('to be or not to be');
! Every row has a unique rowid, just as in any other SQLite table:
! sqlite>select rowid, * from foo;
! 1|to sleep perchance to dream
! 2|to be or not to be
sqlite>
! The MATCH operator performs a full-text match on the content column in a full-text table:
! sqlite>select * from foo where content match 'dream';
! to sleep perchance to dream
sqlite>
! *Query language*
! A query may contain multiple terms, in which case it will return only documents containing all of the terms:
! sqlite>select * from foo where content match 'dream sleep';
! to sleep perchance to dream
sqlite>
Phrases may be enclosed in double quotes:
! sqlite>select * from foo where content match '"to dream" "to sleep"';
! to sleep perchance to dream
sqlite>
! *Joining full-text fields*
! A full-text table stores only one text column. To store records which conceptually have several full-text fields, create several full-text tables and join them together using record IDs. For example, suppose that we'd like to store a set of email messages; each message has a sender, recipient, subject and body. We'd like the subject and body to be full-text indexed. We can use the following schema:
! create table email(sender text, recipient text, subject_id int, body_id int);
! create virtual table email_subject using fulltext;
! create virtual table email_body using fulltext;
! To find the IDs of all messages containing the word "jam" we can issue a query joining the email and email_body tables:
! select email.rowid
! from email join email_body on email.body_id = email_body.rowid
! where content match 'jam';
*Tokenization*
--- 19,184 ----
*Statically linked*
! To statically link fts1 into SQLite, add all .c files from the fts1 directory to the Makefile you use to compile SQLite so that they will be linked into the SQLite image. You must define the preprocessor symbols SQLITE_CORE and SQLITE_ENABLE_FTS1 when compiling these files.
! **Initializing fts1**
! When fts1 is built as a shared library, you can load it into SQLite using the ".load" shell command.
! sqlite> .load fts1
Or you can load it using a SELECT statement:
! SELECT load_extension('fts1');
Note that you may need to call sqlite3_enable_load_extension before loading the extension; see the SQLite LoadableExtensions documentation.
! In a static build, fts1 is always available; there's no need to load or otherwise initialize it.
! **Using fts1**
! Full-text tables store one or more columns of fully indexed text. You can create a full-text table using the CREATE VIRTUAL TABLE statement. For example, the following creates a table with columns *name* and *ingredients*:
! sqlite>create virtual table recipe using fts1(name, ingredients);
! You can insert rows into a full-text table in the same way as into an ordinary table with columns of type TEXT:
! sqlite>insert into recipe (name, ingredients) values ('broccoli stew', 'broccoli peppers cheese tomatoes');
! sqlite>insert into recipe (name, ingredients) values ('pumpkin stew', 'pumpkin onions garlic celery');
! sqlite>insert into recipe (name, ingredients) values ('broccoli pie', 'broccoli cheese onions flour');
! sqlite>insert into recipe (name, ingredients) values ('pumpkin pie', 'pumpkin sugar flour butter');
! The MATCH operator performs a full-text match on a column in a full-text table:
! sqlite> select rowid, name, ingredients from recipe where name match 'pie';
! 3|broccoli pie|broccoli cheese onions flour
! 4|pumpkin pie|pumpkin sugar flour butter
! sqlite>
! As can be seen in the preceding output, every row in a full-text table has a unique rowid, just as in any other SQLite table.
! A query may contain multiple terms, in which case it will return only documents containing all of the terms:
! sqlite> select name, ingredients from recipe where ingredients match 'onions cheese';
! broccoli pie|broccoli cheese onions flour
sqlite>
! *OR queries*
!
! Inside a query, the OR operator may be used to retrieve documents containing either of two terms:
! sqlite> select name, ingredients from recipe where ingredients match 'onions OR cheese';
! broccoli stew|broccoli peppers cheese tomatoes
! pumpkin stew|pumpkin onions garlic celery
! broccoli pie|broccoli cheese onions flour
sqlite>
! Note that the OR in this query must be capitalized.
! The OR operator binds more tightly than the implicit AND between two adjacent terms. Thus, the query 'onions OR cheese pumpkin' matches text which contains either "onions" or "cheese", and also contains "pumpkin":
!
! sqlite> select name, ingredients from recipe where ingredients match 'onions OR cheese pumpkin';
! pumpkin stew|pumpkin onions garlic celery
! sqlite>
!
! fts1 does not currently provide any grouping operator (i.e. parentheses) for overriding this default precedence.
!
! *Excluding terms*
!
! The - operator excludes any documents containing the term which follows it:
!
! sqlite> select name, ingredients from recipe where ingredients match 'onions -celery';
! broccoli pie|broccoli cheese onions flour
! sqlite>
!
! Note that a query must contain at least one non-excluded term:
! sqlite> select name, ingredients from recipe where ingredients match '-celery'; /* invalid! */
! SQL error: SQL logic error or missing database
sqlite>
+ *Phrase searches*
+
Phrases may be enclosed in double quotes:
! sqlite> select name, ingredients from recipe where ingredients match '"broccoli cheese"';
! broccoli pie|broccoli cheese onions flour
! sqlite>
!
! *Querying multiple columns*
!
! To query all columns in a full-text table simultaneously, use the table's name on the left-hand side of the MATCH operator:
!
! sqlite> select name, ingredients from recipe where recipe match 'pie';
! broccoli pie|broccoli cheese onions flour
! pumpkin pie|pumpkin sugar flour butter
! sqlite>
!
! When an all-column query contains multiple terms, a row will match even if the terms appear in different columns in the row:
!
! sqlite> select name, ingredients from recipe where recipe match 'sugar pie';
! pumpkin pie|pumpkin sugar flour butter
! sqlite>
!
! Any term in a query string may be preceded by the name of a particular column to use for matching that term:
!
! sqlite> select name, ingredients from recipe where recipe match 'name:pie ingredients:onions';
! broccoli pie|broccoli cheese onions flour
sqlite>
! The following are entirely equivalent:
! sqlite> select name from recipe where ingredients match 'sugar';
! sqlite> select name from recipe where recipe match 'ingredients:sugar';
! When a specific column name appears to the left of the MATCH operator, that column is used for matching any term without an explicit column qualifier. Thus, the following are equivalent:
! sqlite> select name from recipe where recipe match 'name:pie ingredients:onions';
! sqlite> select name from recipe where name match 'pie ingredients:onions';
! Note that fts1 currently requires a query to contain at most one instance of the MATCH operator. This means that to match multiple specific columns you must use field specifiers as described above; the following won't work:
!
! sqlite> select name, ingredients from recipe
! ...> where name match 'pie' and ingredients match 'onions'; /* invalid! */
! SQL error: unable to use function MATCH in the requested context
! sqlite>
!
! *Retrieving offset information*
!
! When returning full-text matches, fts1 can return the character offsets of individual term matches. To retrieve offset information in a query, call the offsets function and pass it the name of your full-text table:
!
! sqlite> select name, ingredients, offsets(recipe) from recipe where recipe match 'sugar pie';
! pumpkin pie|pumpkin sugar flour butter|0 1 8 3 1 0 8 5
! sqlite>
!
! offsets returns a string containing a series of integers separated by spaces. Each match is represented by a series of four consecutive integers:
!
! 1:The index of the column containing the match. Columns are numbered starting from 0.
! 1:The term in the query expression which was matched. Terms are numbered starting from 0.
! 1:The byte offset of the first character of the matching phrase, measured from the beginning of the column's text.
! 1:Number of bytes in the match.
!
! For example, in the query above two matches are returned, which we can decode as follows:
!
! *:In column 0 (name), term 1 ("pie") matched at byte offset 8; the match is 3 bytes long (the length of "pie").
! *:In column 1 (ingredients), term 0 ("sugar") matched at byte offset 8; the match is 5 bytes long (the length of "sugar").
!
! *Joining full-text data*
!
! A full-text table stores only full-text-indexed strings. To store full-text-indexed values along with other values, it's convenient to use both a full-text table and an ordinary SQLite table, joined by rowid. For example, suppose that you'd like to store a set of messages; each message has a sender and priority, which are not full-text indexed, and a subject and body, which are full-text indexed. You can use the following schema:
!
! create table email(sender text, priority integer);
! create virtual table email_text using fts1(subject, body);
!
! You can insert a new message as follows:
!
! insert into email (sender, priority) values ('ashley@foo.com', 4);
! insert into email_text (rowid, subject, body) values (last_insert_rowid(), 'update', 'coming home now');
!
! To find the sender of all messages containing the word "jam" you can issue a query joining the email and email_text tables:
!
! select sender, subject from email join email_text on email.rowid = email_text.rowid
! where body match 'jam';
*Tokenization*
***************
*** 116,130 ****
We're still in the process of assessing and improving performance.
**Caveats**
! Please note that the full-text database format is subject to change at any time. We are not planning to implement backward compatibility in updates in the near future, so new code releases may fail spectacularly with old databases. Of course this will change at some future point once our data structures become more stable. If backward compatibility is important for your application, let us know and we'll see what we can work out.
**Missing features**
The full-text module is still in an early development phase. The following features are missing but hopefully coming soon:
! *: We plan to support NOT queries (using the syntax "-foo"), OR queries (e.g. "cat OR dog") and prefix queries (e.g. "foo*").
*: Applications will be able to specify custom tokenizers.
*: It will be possible to update text in full-text tables (at the moment only inserts and deletes work).
- *: Full-text queries will return the character offset where each match occurred; this will allow callers to generate snippets conveniently.
--- 200,235 ----
We're still in the process of assessing and improving performance.
+ *Internals*
+
+ Every full-text table contains a column with the same name as the table itself. This self-named column is used in multi-column queries as described above, and will appear in queries for * :
+
+ sqlite> create virtual table foo using fts1(name, address);
+ sqlite> insert into foo (name, address) values ('amanda', '43 elm avenue');
+ sqlite> .header on
+ sqlite> select * from foo;
+ name|address|foo
+ amanda|43 elm avenue|8è5
+ sqlite>
+
+ This column's value is private to fts1. When querying a full-text table, you probably want to specify column names explicitly rather than using *, to avoid seeing this self-named column.
+
+ When you create a full-text table, fts1 creates two backing tables which hold the table's contents and full-text index:
+
+ sqlite> .tables
+ foo foo_content foo_term
+ sqlite>
+
+ You should not access the backing tables directly; their format is internal to fts1 and is subject to change.
+
**Caveats**
! Please note that the full-text database format is subject to change at any time. We are not planning to implement backward compatibility in updates in the near future, so new code releases may fail spectacularly with old databases. Of course this will change at some future point once our data structures become more stable.
**Missing features**
The full-text module is still in an early development phase. The following features are missing but hopefully coming soon:
! *: We plan to support prefix queries (e.g. "foo*").
*: Applications will be able to specify custom tokenizers.
*: It will be possible to update text in full-text tables (at the moment only inserts and deletes work).
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|