[ ] sqlite-utils command-line tool - sqlite-utils

sqlite-utils command-line tool - sqlite-utils

Configuring full-text search

You can enable SQLite full-text search on a table and a set of columns like this:

sqlite-utils enable-fts mydb.db documents title summary

This will use SQLite's FTS5 module by default. Use --fts4 if you want to use FTS4:

sqlite-utils enable-fts mydb.db documents title summary --fts4

The enable-fts command will populate the new index with all existing documents. If you later add more documents you will need to use populate-fts to cause them to be indexed as well:

sqlite-utils populate-fts mydb.db documents title summary

A better solution here is to use database triggers. You can set up database triggers to automatically update the full-text index using the --create-triggers option when you first run enable-fts:

sqlite-utils enable-fts mydb.db documents title summary --create-triggers

To set a custom FTS tokenizer, e.g. to enable Porter stemming, use --tokenize=:

sqlite-utils populate-fts mydb.db documents title summary --tokenize=porter

To remove the FTS tables and triggers you created, use disable-fts:

sqlite-utils disable-fts mydb.db documents

To rebuild one or more FTS tables (see Rebuilding a full-text search table), use rebuild-fts:

sqlite-utils rebuild-fts mydb.db documents

You can rebuild every FTS table by running rebuild-fts without passing any table names:

sqlite-utils rebuild-fts mydb.db

Executing searches

Once you have configured full-text search for a table, you can search it using sqlite-utils search:

sqlite-utils search mydb.db documents searchterm

This command accepts the same output options as sqlite-utils query: --table, --csv, --tsv, --nl etc.

By default it shows the most relevant matches first. You can specify a different sort order using the -o option, which can take a column or a column followed by desc:

# Sort by rowid
sqlite-utils search mydb.db documents searchterm -o rowid
# Sort by created in descending order
sqlite-utils search mydb.db documents searchterm -o 'created desc'

SQLite advanced search syntax is enabled by default. To run a search with automatic quoting applied to the terms to avoid them being potentially interpreted as advanced search syntax use the --quote option.

You can specify a subset of columns to be returned using the -c option one or more times:

sqlite-utils search mydb.db documents searchterm -c title -c created

By default all search results will be returned. You can use --limit 20 to return just the first 20 results.

Use the --sql option to output the SQL that would be executed, rather than running the query:

sqlite-utils search mydb.db documents searchterm --sql

with original as (
    select
        rowid,
        *
    from [documents]
)
select
    [original].*
from
    [original]
    join [documents_fts] on [original].rowid = [documents_fts].rowid
where
    [documents_fts] match :query
order by
    [documents_fts].rank

irthomasthomas / undecidability