54.sql (2223B)
1 -- +migrate Up 2 CREATE VIRTUAL TABLE fts5_forum_messages USING fts5(id UNINDEXED, uuid UNINDEXED, thread_id UNINDEXED, message, content='forum_messages', content_rowid='id'); 3 4 CREATE VIRTUAL TABLE fts5_forum_threads USING fts5(id UNINDEXED, uuid UNINDEXED, name, content='forum_threads', content_rowid='id'); 5 6 -- +migrate StatementBegin 7 CREATE TRIGGER forum_messages_before_update 8 BEFORE UPDATE ON forum_messages BEGIN 9 DELETE FROM fts5_forum_messages WHERE id=old.id; 10 END; 11 12 CREATE TRIGGER forum_messages_before_delete 13 BEFORE DELETE ON forum_messages BEGIN 14 DELETE FROM fts5_forum_messages WHERE id=old.id; 15 END; 16 17 CREATE TRIGGER forum_messages_after_update 18 AFTER UPDATE ON forum_messages BEGIN 19 INSERT INTO fts5_forum_messages(id, uuid, thread_id, message) 20 SELECT id, uuid, thread_id, message 21 FROM forum_messages 22 WHERE new.id = forum_messages.id; 23 END; 24 25 CREATE TRIGGER forum_messages_after_insert 26 AFTER INSERT ON forum_messages BEGIN 27 INSERT INTO fts5_forum_messages(id, uuid, thread_id, message) 28 SELECT id, uuid, thread_id, message 29 FROM forum_messages 30 WHERE new.id = forum_messages.id; 31 END; 32 33 CREATE TRIGGER forum_threads_before_update 34 BEFORE UPDATE ON forum_threads BEGIN 35 DELETE FROM fts5_forum_threads WHERE id=old.id; 36 END; 37 38 CREATE TRIGGER forum_threads_before_delete 39 BEFORE DELETE ON forum_threads BEGIN 40 DELETE FROM fts5_forum_threads WHERE id=old.id; 41 END; 42 43 CREATE TRIGGER forum_threads_after_update 44 AFTER UPDATE ON forum_threads BEGIN 45 INSERT INTO fts5_forum_threads(id, uuid, name) 46 SELECT id, uuid, name 47 FROM forum_threads 48 WHERE new.id = forum_threads.id; 49 END; 50 51 CREATE TRIGGER forum_threads_after_insert 52 AFTER INSERT ON forum_threads BEGIN 53 INSERT INTO fts5_forum_threads(id, uuid, name) 54 SELECT id, uuid, name 55 FROM forum_threads 56 WHERE new.id = forum_threads.id; 57 END; 58 -- +migrate StatementEnd 59 60 INSERT INTO fts5_forum_threads SELECT id, uuid, name FROM forum_threads; 61 INSERT INTO fts5_forum_threads(fts5_forum_threads) VALUES('rebuild'); 62 INSERT INTO fts5_forum_messages SELECT id, uuid, thread_id, message FROM forum_messages; 63 INSERT INTO fts5_forum_messages(fts5_forum_messages) VALUES('rebuild'); 64 65 -- +migrate Down