Home › API › SIMPLE_SQL_FTS5

SIMPLE_SQL_FTS5

Full-text search using SQLite's FTS5 extension. Create virtual tables, index content, and search with BM25 ranking.

View Source

FTS5 Requirements

FTS5 must be enabled in the SQLite build. SIMPLE_SQL's bundled SQLite includes FTS5 support.

Creation

make (a_db: SIMPLE_SQL_DATABASE)

Create FTS5 helper attached to database.

Table Management

create_table (a_name: READABLE_STRING_8; a_columns: ARRAY [READABLE_STRING_8])

Create FTS5 virtual table with specified columns.

create_content_table (a_name, a_content_table: READABLE_STRING_8; a_columns: ARRAY [READABLE_STRING_8])

Create external content FTS5 table linked to existing table.

drop_table (a_name: READABLE_STRING_8)

Drop FTS5 virtual table.

Indexing

insert (a_table: READABLE_STRING_8; a_values: ARRAY [READABLE_STRING_GENERAL])

Insert content into FTS5 table.

delete_by_rowid (a_table: READABLE_STRING_8; a_rowid: INTEGER_64)

Remove content from index by rowid.

rebuild (a_table: READABLE_STRING_8)

Rebuild entire FTS5 index (for content tables).

Searching

search (a_table: READABLE_STRING_8; a_query: READABLE_STRING_8): SIMPLE_SQL_RESULT

Search FTS5 table. Returns matches with BM25 ranking.

search_with_highlights (a_table, a_query: READABLE_STRING_8; a_column: INTEGER; a_before, a_after: READABLE_STRING_8): SIMPLE_SQL_RESULT

Search with highlighted snippets (e.g., <b>match</b>).

search_with_snippets (a_table, a_query: READABLE_STRING_8; a_column: INTEGER; a_before, a_after: READABLE_STRING_8; a_max_tokens: INTEGER): SIMPLE_SQL_RESULT

Search with contextual snippets around matches.

Query Syntax

FTS5 supports various query operators:

word - Match documents containing "word"
word1 word2 - Match documents containing both words (AND)
word1 OR word2 - Match documents containing either word
"exact phrase" - Match exact phrase
word* - Prefix matching
column:word - Search specific column
NOT word - Exclude documents containing word

Examples

Create and Populate FTS5 Table

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

    -- Create FTS5 table
    fts.create_table ("docs_fts", <<"title", "content">>)

    -- Index documents
    fts.insert ("docs_fts", <<"Getting Started Guide", "This guide covers installation and basic usage...">>)
    fts.insert ("docs_fts", <<"API Reference", "Complete reference for all classes and features...">>)
end

Search with Ranking

result := fts.search ("docs_fts", "installation guide")

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

Search with Highlights

result := fts.search_with_highlights ("docs_fts", "installation", 1, "<b>", "</b>")

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

External Content Table

-- Index content from existing 'documents' table
fts.create_content_table ("documents_fts", "documents", <<"title", "body">>)

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

Special Characters

Apostrophes and special characters in queries need proper escaping. See the FTS5 Tutorial for details.