CODEWITHSUNDEEP
sqlsqlitefull-text-searchfts5
handle
handle

Reputation: 6299

Populate virtual SQLite FTS5 (full text search) table from content table

I've followed https://kimsereylam.com/sqlite/2020/03/06/full-text-search-with-sqlite.html to set up SQLite's virtual table extension FTS5 for full text search on an external content table. While the blog shows how to set up triggers to keep the virtual FTS table updated with the data:

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL UNIQUE,
    short_description TEXT
)

CREATE VIRTUAL TABLE user_fts USING fts5(
    username, 
    short_description, 
    email UNINDEXED, 
    content='user', 
    content_rowid='id' 
)

CREATE TRIGGER user_ai AFTER INSERT ON user
    BEGIN
        INSERT INTO user_fts (rowid, username, short_description)
        VALUES (new.id, new.username, new.short_description);
    END;
...

I am failing to populate the FTS table from all previous data in an analogous fashion. I'll stick to the example from the blog:

INSERT INTO user_fts (rowid, username, short_description) SELECT (id, username, short_description) FROM user;

However, sqlite (3.37.2) fails with row value misused.

Please explain how id, content_rowid, rowid and new.id are related and how to modify the query to update the FTS table properly.

Upvotes: 3

Views: 4919

Answers (1)

handle
handle

Reputation: 6299

INSERT INTO user_fts (rowid, username, short_description) SELECT id, username, short_description FROM user;

(no parentheses) works.

rowid is a unique 64 bit unsigned integer row id. If the table contains an integer primary key (as id in user), they are the same (alias). I.e. user.rowid == user.id = user_fts.rowid.
Doc: https://www.sqlite.org/lang_createtable.html#rowid

The new refers to the element being inserted.
Doc: https://www.sqlite.org/lang_createtrigger.html

content_rowid links the virtual FTS table to the external data table row id column (it defaults to rowid).
Doc: https://www.sqlite.org/fts5.html#external_content_tables

Upvotes: 2

Related Questions