Full-Text Search with FTS5

Implement powerful text search using SQLite's FTS5 extension with BM25 ranking.

What is FTS5?

FTS5 (Full-Text Search 5) is SQLite's built-in full-text search engine. It provides:

• Fast text searching across millions of documents
• BM25 relevance ranking
• Phrase matching and boolean queries
• Highlighting and snippets

Setting Up FTS5

local
    fts: SIMPLE_SQL_FTS5
do
    create fts.make (db)

    -- Create FTS5 virtual table
    fts.create_table ("articles_fts", <<"title", "content">>)
end

Indexing Content

-- Add documents to the index
fts.insert ("articles_fts", <<"Getting Started with Eiffel", "Eiffel is a programming language...">>)
fts.insert ("articles_fts", <<"Advanced Eiffel Patterns", "This article covers advanced topics...">>)
fts.insert ("articles_fts", <<"Database Access in Eiffel", "Learn how to use SIMPLE_SQL...">>)

Basic Search

local
    result: SIMPLE_SQL_RESULT
do
    -- Simple search
    result := fts.search ("articles_fts", "eiffel")

    across result as row loop
        print (row.string_value ("title") + "%N")
    end
end

Query Syntax

Example Queries

-- Phrase search
result := fts.search ("articles_fts", "%"getting started%"")

-- Boolean OR
result := fts.search ("articles_fts", "eiffel OR database")

-- Prefix search
result := fts.search ("articles_fts", "prog*")  -- programming, program, etc.

-- Exclude term
result := fts.search ("articles_fts", "eiffel NOT advanced")

-- Search specific column
result := fts.search ("articles_fts", "title:database")

Highlighted Results

-- Get results with highlighted matches
result := fts.search_with_highlights ("articles_fts", "eiffel", 1, "<mark>", "</mark>")

across result as row loop
    -- Content will have <mark>Eiffel</mark> around matches
    print (row.string_value ("content") + "%N")
end

Snippets (Context Around Matches)

-- Get snippets with context
result := fts.search_with_snippets ("articles_fts", "eiffel", 1, "<b>", "</b>", 20)

-- Result: "...programming language. <b>Eiffel</b> is known for..."

External Content Tables

Index an existing table without duplicating data:

-- Create FTS index linked to existing 'documents' table
fts.create_content_table ("documents_fts", "documents", <<"title", "body">>)

-- Rebuild index after data changes
fts.rebuild ("documents_fts")

Handling Special Characters

-- WRONG: apostrophe breaks query
result := fts.search ("articles_fts", "O'Brien")  -- Error!

-- CORRECT: wrap in double quotes
result := fts.search ("articles_fts", "%"O'Brien%"")

Complete Search Feature

class DOCUMENT_SEARCH

feature

    db: SIMPLE_SQL_DATABASE
    fts: SIMPLE_SQL_FTS5

    setup
        do
            create fts.make (db)
            fts.create_content_table ("docs_fts", "documents", <<"title", "content">>)
        end

    search (a_query: STRING): ARRAYED_LIST [TUPLE [id: INTEGER_64; title: STRING_32; snippet: STRING_32]]
        local
            result: SIMPLE_SQL_RESULT
            escaped_query: STRING
        do
            create Result.make (10)

            -- Escape special characters
            escaped_query := escape_query (a_query)

            -- Search with snippets
            result := fts.search_with_snippets ("docs_fts", escaped_query, 1, "<mark>", "</mark>", 50)

            across result as row loop
                Result.extend ([
                    row.integer_value ("rowid"),
                    row.string_value ("title"),
                    row.string_value ("snippet")
                ])
            end
        end

    escape_query (a_query: STRING): STRING
        do
            Result := a_query.twin
            -- Wrap in quotes if contains special chars
            if Result.has (''') then
                Result := "%"" + Result + "%""
            end
        end

end

Next Steps

• API Reference - Full FTS5 documentation
• DMS Mock App - See FTS5 in action