How about managing fts_table using triggers attached to the master table? That should take care of synchronization issues IMHO.
Mike -----Ursprüngliche Nachricht----- Von: Paul Quinn [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 7. April 2007 09:08 An: sqlite-users@sqlite.org Betreff: [sqlite] Master table with child FTS table I have a database setup that creates a master table, and a child 'Full Text Search' (FTS) table that matches it by rowid. My problem is with keeping the FTS table in sync with the master. Let me explain in SQL: setup like so: CREATE TABLE master (a, b); CREATE VIRTUAL TABLE fts_table USING fts2(words); inserts like so: INSERT INTO master (a, b) VALUES (blah, blah); INSERT INTO fts_table (rowid, words) VALUES (last_insert_rowid(), 'some words'); searches like so: SELECT a, b, words FROM master JOIN fts_table ON master.rowid == fts_table.rowid WHERE words MATCH 'word'; In my scenario, I need to do a number of updates and deletes to the master table and the fts_table needs to follow suit. However a number of problems have surfaced: FTS does not work for INSERT OR REPLACE. At least that is what I have found, didn't find any docs on this. So I resort to using delete/insert like so: DELETE FROM fts_table WHERE rowid = <rowid deleted from master table>; INSERT INTO fts_table (rowid, words) VALUES (<rowid for master insert>, 'some words'); However, I am finding that this is not reliable. The FTS table will still go out of sync. Row counts will not match after some indeterminant amount of updates. It seems either some DELETE's are failing, or the INSERTs into existing rowid's are failing (silently - the return error is always ok). And after the out of sync problems start occuring, sometimes the FTS goes to pot and the SELECT sql's start failing with SQLITE_ERROR. So, is there a better way to do what I am trying to do? I'm using SQLite 3.3.13, and I've tried both FTS1 and FTS2 (using FTS2 right now). Thanks in advance for any help. -PQ ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
