sqlite fts 4 trigger didn't update index

Question

I face an index issue with a virtual table from sqlite using FTS 4. I try to optimise the index speed of my virtual table.

This virtual table take its content from a real table. Previously, to refresh the index of the virtual table, we used the following command which did well the job.

INSERT INTO virtual_table(virtual_table) VALUES('rebuild');

But this command took some times and we need to call it after each edit/insert/delete so it's a big problem.

If I well understand the documentation (https://www.sqlite.org/fts3.html#external_content_fts4_tables) I can put some trigger to automatically refresh the index of the virtual table.

We saw the virtual table is well filled, but the index is not refreshed and all SQL queries return nothing... Except if I call the query talk before.

The virtual table is not empty because, SELECT * FROM virtual_table return well many datas. That mean, the virtual table well bind the real table.

The index didn't work because queries like SELECT * FROM virtual_table WHERE virtual_table MATCH 'e' return nothing. So the triggers seems to not work.

I well used trigger as documentation said :

CREATE VIRTUAL TABLE virtual_table USING fts4(content='real_table', tokenize=unicode61, b, c);

CREATE TRIGGER real_table_bu BEFORE UPDATE ON real_table BEGIN
  DELETE FROM virtual_table WHERE docid=old.rowid;
END;
CREATE TRIGGER real_table_bd BEFORE DELETE ON real_table BEGIN
  DELETE FROM virtual_table WHERE docid=old.rowid;
END;
CREATE TRIGGER real_table_au AFTER UPDATE ON real_table BEGIN
  INSERT INTO virtual_table(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;
CREATE TRIGGER real_table_ai AFTER INSERT ON real_table BEGIN
  INSERT INTO virtual_table(docid, b, c) VALUES(new.rowid, new.b, new.c);
END;

I need some help to fix this index issue :) !

I'm using sqlite 3.32.2 under Android 13 API 33 through Flutter & sqlflite.

Answer 1

I found the problem. In fact, inside my query which set theses triggers, I also set a DROP TRIGER to be sure that the following ones were up to date.

I saw that the query did the drop and stop after that, so the trigger creation was not called...

Ty of this post i discover that my triggers were not created : How can I list all the triggers of a database in sqlite?