SQLite Forum

Unable to search an FTS5 trigram table when using external content
Login

Unable to search an FTS5 trigram table when using external content

(1) By anonymous on 2021-09-28 08:29:20 [source]

Hello. I'm attempting to create an FTS5 trigram table to text search a separate table. I used the external content option and I know it at least sees the data because when I select all with no where clause it does show data. Below is a minimal example to see it not working. SQLite version 3.34.1

sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
sqlite> INSERT INTO test(data) VALUES('testing foo bar');
sqlite> SELECT * FROM test;
1|testing foo bar
sqlite> CREATE VIRTUAL TABLE fts USING fts5(data, content=test, content_rowid=id, tokenize'trigram');
sqlite> SELECT * FROM fts;
testing foo bar
sqlite> SELECT * FROM fts('foo');
sqlite> SELECT * FROM fts WHERE fts = 'foo';
sqlite> SELECT * FROM fts WHERE fts MATCH 'foo';
sqlite> SELECT * FROM fts WHERE data MATCH 'foo';
sqlite> SELECT * FROM fts WHERE data = 'foo';

You can see none of the 5 queries I tried with searches returned anything. I tested non external content and it worked fine. Am I doing something wrong?

(2) By Dan Kennedy (dan) on 2021-09-28 11:24:30 in reply to 1 [link] [source]

An external content table is like a contentless table in that the user has to arrange to update the FTS index using INSERT statements to keep it in sync with the external table.

    sqlite> CREATE TABLE test(id INTEGER PRIMARY KEY AUTOINCREMENT, data TEXT);
    sqlite> INSERT INTO test(data) VALUES('testing foo bar');
    sqlite> CREATE VIRTUAL TABLE fts USING fts5(data, content=test, content_rowid=id, tokenize='trigram');
    sqlite> INSERT INTO fts(rowid, data) SELECT id, data FROM test;
    sqlite> SELECT * FROM fts('foo');
    testing foo bar

One way to do this is with triggers, another is to have your app update both the external content table and the fts index at the same time. See documentation here:

https://sqlite.org/fts5.html#external_content_and_contentless_tables

For small tables, the 'rebuild' command can be useful:

https://sqlite.org/fts5.html#the_rebuild_command

(3) By anonymous on 2021-09-29 01:15:57 in reply to 2 [link] [source]

Thank you so much. I set up and trigger and it's working now. I'm assuming the act of inserting data into the table is what actually causes the index to be built for searching.