Hi, Newbie on SQLite here.
I also have the same problem on how to do a ranking on FTS3 but no clue.
And I found that you use a function call score() that do sorting. But it is
not found in SQLite doc.
So, can you please tell me how can I get this function or, can you please
tell me what is the correct way of doing ranking in FTS3, given that only
use the Virtual Table?
Thank you for all your help in advance.
Scott Hess wrote:
>
> On 6/10/07, Mark Gilbert <m...@gallery.co.uk> 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 sqlite-users-unsubscr...@sqlite.org
> -----------------------------------------------------------------------------
>
>
>
--
View this message in context:
http://www.nabble.com/Ranking-in-fts.-tp11034641p23534144.html
Sent from the SQLite mailing list archive at Nabble.com.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
