dkforest

CREATE VIRTUAL TABLE fts5forummessages USING fts5(message, content='forummessages', contentrowid='id');

DROP TRIGGER forummessagesbefore_update; CREATE TRIGGER forummessagesbefore_update BEFORE UPDATE ON forum_messages BEGIN DELETE FROM fts5forummessages WHERE id=old.id; END;

DROP TRIGGER forummessagesbefore_delete; CREATE TRIGGER forummessagesbefore_delete BEFORE DELETE ON forum_messages BEGIN DELETE FROM fts5forummessages WHERE id=old.id; END;

DROP TRIGGER forummessagesafter_update; CREATE TRIGGER forummessagesafter_update AFTER UPDATE ON forum_messages BEGIN INSERT INTO fts5forummessages(message) SELECT message FROM forum_messages WHERE new.id = forum_messages.id; END;

DROP TRIGGER forummessagesafter_insert; CREATE TRIGGER forummessagesafter_insert AFTER INSERT ON forum_messages BEGIN INSERT INTO fts5forummessages(message) SELECT message FROM forum_messages WHERE new.id = forum_messages.id; END;

INSERT INTO fts5forummessages SELECT message FROM forum_messages;

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

select highlight(fts5forummessages, 0, '<b>', '</b>') from fts5forummessages where fts5forummessages match 'spell' order by rank;