Re: need to implement full text search

These are some of the advantages of SQLite's full text search (FTS) over your
approach:

1. You can simplify your database structure:

You can put all keywords into a single record. You will no longer have to take
care of referential integrity. The FTS index is updated automatically each time
you insert or change records in the FTS table, you do not have to worry about
this at all.

2. FTS supports advanced search queries:

You can OR terms (apples OR pears), exclude terms (apples -pears), perform
phrase searches ("apples mixed with pears"), and prefix searches (apple*), which
will find apple as well as apples.

3. Combine FTS with other SQL

SQLite FTS allows to integrate full text search queries into ordinary SQL. This
allows to filter objects by date, or other properties, just by writing a new SQL
query or creating a new view.

4. You can create your own tokenizer:

If you require WideString or StoppWord support, or need to exclude certain
characters from the full text index, you can completely customize the FTS
tokenizer. A porter stemmer tokenizer is part of the standard distribution.
Searching for "run" it fins "run", "runs", "running", etc.

Delphi Implementation

The free DISQLite3 Personal edition contains the SQLite3 database engine as well
as all FTS modules: FTS1, FTS2, and FTS3. Caution: Only use FTS3 or later. Do
NOT use FTS1 or FTS2, they contain a design flaw which can lead to database
corruption.

Compile the full text search demo project for a first impression of the
performance and usage benefits which your application will gain from using
DISQLite3 and full text search!

Ralf

"Edwin" <edwinyeah@(nospam)gmail.com> wrote:

Really? good!

But I still have one thing not clear, I want to creat a table in SQLite with
structure below:
keyword, file_path, object_id

and when searching we use a SQL like:
select file_path, object_id from mytable where keyword like '%akeyword%'

what's the advantage of using the full-text API instead of the
aforementioned approach? Thanks.

---
The Delphi Inspiration
http://www.yunqa.de/delphi/
.

Relevant Pages

  • Combining FTS and Index Server
    ... indexing, so I'm not sure exactly what I'm looking for. ... I'm familiar w/ FTS, as I have used this for a message board on another ... both files and database content at the same time. ... and PDF files for storing in the database? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Misterious disabling of table FTS
    ... Just to add this was on SQL 2005 sp1 ... Today two tables in the same database (which are the only ones with FTS ... of them were enabled and working and nothing is showing in the logs about ...
    (microsoft.public.sqlserver.fulltext)
  • Misterious disabling of table FTS
    ... that database) had FTS disabled. ... them were enabled and working and nothing is showing in the logs about any ... "Changing the status to MERGE for the full-text catalog ". ...
    (microsoft.public.sqlserver.fulltext)
  • FTS capabilities + questions
    ... The database that I am going to hit up against ... What is the best method to use FTS against so many tables? ... Could I build a massive query that dumps the results into a single ... I know this problem isnt unique to our company but just wondering how ...
    (microsoft.public.sqlserver.fulltext)
  • Full Text Search question
    ... The database that I am going to hit up against ... What is the best method to use FTS against so many tables? ... Could I build a massive query that dumps the results into a single ... I know this problem isnt unique to our company but just wondering how ...
    (comp.databases.sybase)