(The SQLite docs spend quite a lot of time talking about external content and contentless mode but do not name or even seem to define this mode of operation, I just call it "normal" mode.) CREATE TABLE IF NOT EXISTS pages (ĬREATE VIRTUAL TABLE pages_fts USING FTS5(ĭELETE FROM pages_fts WHERE title = new. ![]() This allows you to build the search index however you like, using regular SQL statements. To do this, just omit the content and content_rowid columns from the FTS5 table definitions. With an external content index, you must always keep the index and the external table in sync, or the result is a corrupted database. It turns out that when linking an index to external content (with content=), you cannot pick and choose which rows of the content table you want to index. SO still thinks it is a duplicate, so I have to add this text in order to post the question. I had previously posted this question under the wrong account and have since deleted it. CREATE TABLE IF NOT EXISTS pages ( revision TEXT NOT NULL, title TEXT NOT NULL, body TEXT ) CREATE VIRTUAL TABLE pagesfts USING FTS5 ( title, body ) CREATE TRIGGER pagesafterinsert AFTER INSERT ON pages BEGIN DELETE FROM pagesfts WHERE title new.title INSERT INTO pagesfts (title, body) VALUES (new.title, new. SELECT title, snippet(pages_fts, 1, '>', '', 'homehomehomeversionversionversion< one INSERT INTO pages_fts(pages_fts, rank) VALUES ('integrity-check', 1) INSERT INTO pages (revision, title, body) VALUES ('T22:48:46.205782', 'home', 'body version one') INSERT INTO pages (revision, title, body) VALUES ('T22:48:40.250981', 'home', 'body version two') INSERT INTO pages (revision, title, body) VALUES ('T22:48:35.582797', 'home', 'body version one') VALUES ('delete', old.id, old.title, old.body) delete the matching entry from the FTS tableĬREATE TRIGGER pages_after_delete AFTER DELETE ON pages After a page is deleted from the pages table, insert a matching entry into the FTS tableĬREATE TRIGGER pages_after_insert AFTER INSERT ON pages After a page is inserted into the pages table, INSERT INTO pages_fts (pages_fts, id, title, body) delete the old matching entry in the FTS tableĬREATE TRIGGER pages_before_insert BEFORE INSERT ON pages Before a page is inserted into the pages table, And yet, I'm getting results for all revisions, or previous revisions.ĬREATE virtual TABLE pages_fts USING FTS5( I'm attempting to use TRIGGERs to manage the index. I only care about indexing and searching the most recent revision of each page, so only the most recent revision of each page should be in the FTS5 index. (It will be very rare that a page is deleted, but I'd like to allow for it, if possible.) The idea is that there is one big pages table where every revision of every page gets stored. NoMutex: The connection is opened in multi-threading mode. FullMutex: The connection is opened in serialized threading mode. My goal is a sort of wiki-like notebook using SQLite as the data store. The SQLiteOpenFlagenum supports these values: Create: The connection will automatically create the database file if it doesn't exist.
0 Comments
Leave a Reply. |