On 6/10/07, Mark Gilbert <[EMAIL PROTECTED]> wrote:
I have just started learning about Full Text search in SQlite, and I
have some questions
Sorry for the delayed response, I just noticed this email waiting for
an answer, and nobody has ...
1) With the Amalgamated Sqlite, I guess to enable FTS I just #define
SQLITE_ENABLE_FTS2 at the start of sqlite3.c
Is there anything else I have to do to switch it on ?
I don't think FTS2 is in the amalgamation. You can either build it
separately and manually hook it in (after sqlite3_open(), call
sqlite3Fts2Init(), like is done in main.c), or you could perhaps
define SQLITE_ENABLE_FTS2, and compile sqlite3.c plus the .c files in
ext/fts2.
2) If I want to do a full text (google style) search across 6 columns
in my database, what is the procedure? Currently I conjor up a
long SQL query with lots of ands and ors. What is to be gained
using FTS ?
CREATE TABLE x USING fts2(a, b, c, d, e, f);
INSERT INTO x (a, b, c, d, e, f) VALUES ('hello', 'world', 'this',
'is', 'a', 'test');
SELECT rowid FROM x WHERE a MATCH 'this'; -- Look for 'this' in column a.
SELECT rowid FROM x WHERE x MATCH 'this'; -- Look for 'this' in any column.
SELECT rowid FROM x WHERE x MATCH 'a:this'; -- Again, only column a.
SELECT rowid FROM x WHERE x MATCH 'this b:that'; -- require 'that'
in column b.
Note the use of a table-named column in the second and later MATCH
calls. This sets the default column specifier for words in the query
to "all".
3) It seems that FTS is basically a special table. Do I have to fill
this table in parallel with my regular columns ? (so its in sync) or
do I recreate the FTS table on every search ? Or do I just fill the
FTS table on its own, and stop using my regular tables altogether ?
Depends entirely on what you're trying to accomplish. FTS is a
separate table which stores TEXT columns, and provides a word-level
index on them. If your current data is just a table of TEXT columns,
you can probably just store it directly in FTS and be done with it.
If it's a mixture of TEXT and non-TEXT, or has other constraints you
might want to have a pair of tables joined on rowid. Sometimes you
want both. Say you want a table where a msgid should be unique, and
other columns should be searchable. You might have something like:
CREATE TABLE a (msgid TEXT PRIMARY KEY);
CREATE TABLE a_fts (subject, body);
You select things using a join on the implicit rowid column (an SQLite
concept which fts abides by):
SELECT msgid, subject, body FROM a JOIN a_fts USING(rowid) WHERE
subject MATCH ?;
To update, make sure you use a transaction to keep things consistent:
BEGIN;
INSERT INTO a (msgid) VALUES (?);
INSERT INTO a_fts (rowid, subject, body) VALUES (last_insert_rowid(), ?, ?);
COMMIT;
BEGIN;
DELETE FROM a_fts WHERE rowid IN (SELECT rowid FROM a WHERE msgid = ?);
DELETE FROM a WHERE msgid = ?;
END;
BEGIN;
UPDATE a_fts SET subject = ? WHERE rowid IN (SELECT rowid FROM a
WHERE msgid = ?);
UPDATE a SET msgid = ? WHERE msgid = ?;
END;
If you wanted msgid to also be searchable, just duplicate it in a_fts.
-scott
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
