0

I already read from here FTS4 sqlite MATCH not working and here Full text search example in Android but It can't solve my problem.

I have a external database and I use sqlite 3 version 3.13.0 with 2 table like this.

enter image description here

And I create a virtual table:

CREATE VIRTUAL TABLE tb_bank_fts USING fts4 (content="tb_bank",address, typename)

Virtual table create successfull and I can use select.

enter image description here

But I can't use "match" query with fts4 table, alway return null with no error.

enter image description here

Half month ago I can query fts4 table with "match" but now i can't do that.I don't know why. I try using SQlite Manager addon in Firefox but same problem.

Community
  • 1
  • 1
Ken Kem
  • 635
  • 1
  • 6
  • 13
  • How did you insert data into the FTS table? – CL. Jul 11 '16 at 09:20
  • @CL. I use this code `CREATE VIRTUAL TABLE fts_table_name USING fts4 (content="original_table_name", colum1, colum2 ...)`. All record from original table will insert to virtual table with columns you choose. Sorry for my bad english! – Ken Kem Jul 11 '16 at 10:08

1 Answers1

4

The documentation says:

The FTS4 module never writes to the content table, and writing to the content table does not affect the full-text index. It is the responsibility of the user to ensure that the content table and the full-text index are consistent.

So when you've inserted data into the base table, you also have to insert it into the FTS table:

INSERT INTO tb_bank_fts(docid, address, typename)
SELECT rowid, address, typename FROM tb_bank;

or simply:

INSERT INTO tb_bank_fts(tb_bank_fts) VALUES('rebuild');
CL.
  • 173,858
  • 17
  • 217
  • 259
  • OMG, you save my 5 hours. thank you very much. I solved that. I already understand – Ken Kem Jul 11 '16 at 11:35
  • This solution is not complete : 'rebuild' will index the entire FTS table and this is not good. It could take many second if your FTS table is large. If you insert data often, you will see the problem. FTS 4 docs talk about triggers to fix this issue. But on my side it didn't work. Trigger well add data but didn't index the table... https://stackoverflow.com/questions/76229493/sqlite-fts-4-trigger-didnt-update-index – Eng May 12 '23 at 07:23
  • This solution made my day – Swastik Jun 13 '23 at 03:48