Skip to content

Documentation explaining how to use SQLite FTS with Datasette #253

Closed
@simonw

Description

@simonw

In particular how to work with https://www.sqlite.org/fts3.html#_external_content_fts4_tables_ - which Datasette can automatically detect and use to add a search UI to your page.

Examples of basic search setup like this:

CREATE VIRTUAL TABLE "interests_fts"
    USING FTS4 (name, content="interests");
INSERT INTO "interests_fts" (rowid, name)
    SELECT rowid, name FROM interests;

And complex join-based search setup like this:

CREATE VIRTUAL TABLE "interests_fts"
    USING FTS4 (name, category, member, content="interests");
INSERT INTO "interests_fts" (rowid, name, category, member)
    SELECT interests.rowid, interests.name, interest_categories.name, members.name
    FROM interests
        JOIN interest_categories ON interests.category_id = interest_categories.id
        JOIN members ON interests.member_id = members.id;

Also mention how csvs-to-sqlite can be used to do this easily.

This will benefit from #252

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions