SQLite FTS5: how to stay in sync virtual table with the database?

Getting Started
1

Hi, there’s a way to setup some triggers / some other stuff to make stay in sync the database with the virtual table created wit FTS5 (to use the full search text capabilities)?
Or i’ve to manually modify data in virtual table every time i modify my database ?

2

How do u connect the fts table to the real table??

3

Frankly … don’t know … :flushed:
I create a table using FTS5 and import all my data from my real DB into virtual table (see my other looong post).
Don’t know how they are … connected.
Any help (tutorial, i’ve seen the one at SQLite) will be highly appreciated.

4

I try earlier with my real table and the fts table that have lots if text and simply using JOIN From the real table To the virtual table and then using MATCH to find the word.

Make sure not to use LEFT JOIN

5

May you please show me a fragment of your code ?
JOIN should be used when? at every changes you made to real table (add/modify/delete data)? or once for all when you setup the virtual table and import for the first time data from real table to the virtual one ?

6

this is the NOT xojo code. just SQL Statement that i run on the SQLiteManager

to create the virtual table call tmpJob

CREATE VIRTUAL TABLE tmpJob
USING FTS5(JobID, Description,EstimateLine);

insert into the tmpJob, all the records from tblJob but with just Description and JobDetails

INSERT INTO tmpJob(JobID, Description,EstimateLine)
SELECT JobID, Description, JobDetails
FROM tblJob;

select records from tblJob with some information from tblJob and Description and JobDetails from tmpJob

SELECT tblJob.JobID, JobNo, JobDateIN, Client, ClientReference, tblJob.Description, Location, Status
FROM tblJob
JOIN  tmpJob ON tblJob.JobID = tmpJob.JobID
WHERE tmpJob MATCH 'mahogany table'

if you want to look for the word ‘mahogany table’ use MATCH '“mahogany table”'

for both mohagany and table anywhere in tmpJob, then use MATCH ‘mahogany AND table’ or simply MATCH 'mahogany table’

if either one or both word, use MATCH 'mahogany OR table’

sorry to mention you can use JOIN or INNER JOIN BUT not OUTER JOIN or LEFT JOIN or RIGHT JOIN

7

Thanks Richard,
but when you delete a record from tblJob (assume the record with JobID = 1) how this change is propagated to tmpJob ?

8

I am trying to figure out how to use trigger to add, update and delete the tmpJob when Description and JobDetails is add or modify

9

got it working for the whole create and triggers

CREATE VIRTUAL TABLE tmpJob USING fts5(content="tblJob",  JobID, Description, EstimateLine);

DROP TRIGGER tmpJob_before_update;
CREATE TRIGGER tmpJob_before_update 
BEFORE UPDATE ON tblJob BEGIN
    DELETE FROM tmpJob WHERE JobID=old.JobID;
END;

DROP TRIGGER tmpJob_before_delete;
CREATE TRIGGER tmpJob_before_delete 
BEFORE DELETE ON tblJob BEGIN
    DELETE FROM tmpJob WHERE JobID=old.JobID;
END;

DROP TRIGGER tblJob_after_update;
CREATE TRIGGER tblJob_after_update 
AFTER UPDATE ON tblJob BEGIN
    INSERT INTO tmpJob( JobID, Description,EstimateLine) 
    SELECT  JobID, Description,EstimateLine 
    FROM tblJob 
    WHERE new.JobID = tblJob.JobID;
END;

DROP TRIGGER tblJob_after_insert;
CREATE TRIGGER tblJob_after_insert 
AFTER INSERT ON tblJob BEGIN
    INSERT INTO tmpJob(JobID, Description,EstimateLine ) 
    SELECT JobID, Description,EstimateLine  
    FROM tblJob 
    WHERE new.JobID = tblJob.JobID;
END;
1 Like
10

after more research, i wasn’t able to find any record at all using MATCH using the above code for CREATE VIRTUAL TABLE so i change to the following code to create table and add in record and i was able to MATCH the records

CREATE VIRTUAL TABLE tmpJob
USING FTS5(JobID, Description,EstimateLine);

INSERT INTO tmpJob(JobID, Description,EstimateLine)
SELECT JobID, Description, EstimateLine
FROM tblJob;
11

@Richard_Duke your help is really invaluable !
thank you so much now all works properly and my virtual table is perfectly synced :hugs:

12

you are welcome

13

I see that automagically was inserted the DROP at the beginning (of the 4 triggers) but with this syntax:

DROP TRIGGER “main”.“cNotifiche_AFTER_DELETE”;
CREATE TRIGGER cNotifiche_AFTER_DELETE AFTER
…

Is it the same of

DROP TRIGGER cNotifiche_AFTER_DELETE
CREATE TRIGGER cNotifiche_AFTER_DELETE AFTER

of the second one is ‘better’ ?

14

I use the second one since i had to make change

15

Sorry Richard, may you elaborate a little bit ?
In the other post you just said that DROP is not necessary and i’ve created the triggers without DROP.
But opening the database i see that DROP is automagically added, tough with a little syntax difference:

DROP TRIGGER “main”.“cNotifiche_AFTER_DELETE”;

So it’s necessary ? And what the difference between the 2 syntaxes ?

DROP TRIGGER cNotifiche_AFTER_DELETE

16

strange… i only run the trigger once for the database and if make changes like adding in new fields into the insert trigger, then i do a drop and then create again.

i don’t get the main.triiger_name at all. so no idea why