[yunqa.de] Adding Full Text Search to my app
- From: "email@xxxxxxxxxx" <email@xxxxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Sun, 06 Jan 2013 07:56:57 +0100
Hi,
I read the full text search example, but I'm afraid I don't get it.
I'm most familiar with the "Object Layer" syntax rather than the native
api, that probably explains why I don't understand the example.
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);
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;
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?
In anycase, what sql should I use in order to search for myfile* (for
example)? Also, does the FTS support unicode?
Thanks!
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
