[yunqa.de] Re: Adding Full Text Search to my app
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sun, 06 Jan 2013 12:49:32 +0100
On 06.01.2013 07:56, email@xxxxxxxxxx wrote:
> I read the full text search example, but I'm afraid I don't get it.
Full Text Search is explained in great detail in the DISQLite3 help:
DISQLite3.chm -> Extensions -> Full Text Search (FTS3, FTS4)
> I'm most familiar with the "Object Layer" syntax rather than the native
> api, that probably explains why I don't understand the example.
FTS works just the same with both layers, you just need to initialize
the FTS module by calling this function:
function sqlite3Fts3Init(
DB: sqlite3_ptr): Integer;
With the object layer, the call looks like this:
sqlite3Fts3Init(DB.Handle);
> Anyway, I have a table called Files that indexes files & folders. I need
> to provide fast search on file names (full with paths) rather than content.
>
> I read the documentation, if I'm not mistaken, to create a full text
> search table, the syntax would be:
>
> CREATE VIRTUAL TABLE FTS USING fts4 (FileID Integer NOT NULL UNIQUE,
> FileName TEXT UNIQUE COLLATE USERNOCASE);
FTS tables do not store integers but text only. Also, FTS does not
support the UNIQUE and COLLATE keywords. The documentation referenced
above explains that and why they are ignored.
> FWIW, "Files" table definition is:
>
> --------------------------------------------------
> PRAGMA journal_mode=WAL;
>
> CREATE TABLE Files (
> Parent Integer,
> ID Integer NOT NULL UNIQUE,
> GUID Text NOT NULL,
> ParentGUID Text,
> FileName Text NOT NULL UNIQUE,
> Name Text NOT NULL,
> MaskOrBackupDate Text DEFAULT '',
> ModifDateOrInSync Integer DEFAULT 0,
> BackupCount Integer DEFAULT 0,
> Type Integer NOT NULL DEFAULT 0,
> Flag Integer DEFAULT 0,
> AccessGroup Integer DEFAULT 0,
> PRIMARY KEY ('ID')
> );
> --------------------------------------------------
>
> Given this, I did the following:
>
> 1. To populate the FTS table, I created a trigger (files are never
> deleted, so this should be enough):
>
> CREATE TRIGGER files_ins AFTER INSERT ON Files
> BEGIN
> INSERT INTO FTS (FileID, FileName) VALUES(new.ID, new.FileName);
> END;
Instead of triggers, the content= option seems more appropriate.
Explained in chapter 6.2 of the FTS help page.
> However, I see 6 other tables created (FTS_content, FTS_docsize,
> etc...), it seems that I'm missing something. Are these tables needed?
> Can I delete them?
Yes, these table are needed, don't delete them manually. They are FTS
helper tables which SQLite maintains for you. It also deletes them if
you drop the original FTS table.
> In anycase, what sql should I use in order to search for myfile* (for
> example)?
DISQLite3 supports the standard query syntax. The full syntax is
explained in the help.
> Also, does the FTS support unicode?
Absolutely. Unicode is supported by the "unicode61" tokenizer:
CREATE VIRTUAL TABLE Unicode USING fts4 (text, tokenize=unicode61);
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
