CODEWITHSUNDEEP
sqlitefull-text-searchpopulate
Avrob
Avrob

Reputation: 545

SQLite FTS table is not being populated

DROP TABLE IF EXISTS TEXTS;
CREATE TABLE TEXTS (text_id int PRIMARY KEY, text ntext,last_update datetime DEFAULT 
CURRENT_TIMESTAMP);
DROP TABLE IF EXISTS FTSTEXTS;
CREATE VIRTUAL TABLE FTSTEXTS USING fts5(text,content="TEXTS",content_rowid="text_id",tokenize=unicode61);
Insert into texts (text_id,text) values (1,'first'),(2,'second'),(3,'third'),(4,'fourth');
Insert into ftstexts (rowid,text) values (1,'first'),(2,'second'),(3,'third'),(4,'fourth');
update texts set text='5555' where text_id=2;
update ftstexts set text='5555' where rowid=2;
select text from texts where text_id=(select rowid from ftstexts where text match('second') limit 1);

Result is "5555"

After creating table and updating the value of row 2 to "5555" the query

select rowid from ftstexts where text match('second') limit 1

Returns 2:

How can the FTS table be populated to exclude the term "second" from FTS search?

The query was run on SQLITEStudio 3.2.1

Upvotes: 0

Views: 326

Answers (1)

Shawn
Shawn

Reputation: 52326

External content FTS5 tables don't support updates. You have to delete the old contents of a row and insert the new contents as separate steps.

The documentation includes some sample triggers to automate keeping such a table in sync with the table that holds the actual data.

You might make an update trigger something along the lines of

CREATE TRIGGER texts_au AFTER UPDATE ON texts BEGIN
  INSERT INTO ftstexts(ftstexts, rowid, text) VALUES('delete', old.text_id, old.text);
  INSERT INTO ftstexts(rowid, text) VALUES (new.text_id, new.text);
END;

Upvotes: 1

Related Questions