• SQLite Full-text Search module

--
-- File information
--
CREATE TABLE File (
    id          INTEGER PRIMARY KEY,
    filepath    TEXT NOT NULL,
    filename    TEXT NOT NULL
);
 
 
--
-- File tags
--
CREATE TABLE FileTag (
    file_id     INTEGER NOT NULL,
    tag         TEXT NOT NULL
);
 
CREATE UNIQUE INDEX FileTag_unique ON FileTag(file_id, tag);
 
 
--
-- Tags
--
CREATE VIEW Tag AS 
    SELECT DISTINCT tag FROM FileTag ORDER BY tag ASC;
 
 
--
-- Full-text search index
--
CREATE VIRTUAL TABLE SearchIndex USING fts3(file, tag);
 
 
--
-- Triggers to update the full-text search index
--
CREATE TRIGGER insert_File_update_fts AFTER INSERT ON File
BEGIN
    INSERT INTO SearchIndex(docid, file) 
                VALUES(new.id, new.filename);
END;
 
CREATE TRIGGER update_File_update_fts AFTER UPDATE ON File
BEGIN
    UPDATE SearchIndex 
           SET file = new.filename 
           WHERE docid = new.id;
END;
 
CREATE TRIGGER delete_File_update_fts AFTER DELETE ON File
BEGIN
    DELETE FROM SearchIndex 
                WHERE docid = old.id;
END;
 
CREATE TRIGGER insert_FileTag_update_fts AFTER INSERT ON FileTag
BEGIN
    UPDATE SearchIndex 
           SET tag = (SELECT group_concat(tag, ' ') 
                             FROM FileTag
                             WHERE file_id = new.file_id)
           WHERE docid = new.file_id;
END;
 
CREATE TRIGGER update_FileTag_update_fts AFTER UPDATE ON FileTag
BEGIN
    UPDATE SearchIndex 
           SET tag = (SELECT group_concat(tag, ' ') 
                             FROM FileTag
                             WHERE file_id = new.file_id)
           WHERE docid = new.file_id;
END;
 
CREATE TRIGGER delete_FileTag_update_fts AFTER DELETE ON FileTag
BEGIN
    UPDATE SearchIndex 
           SET tag = (SELECT group_concat(tag, ' ') 
                             FROM FileTag
                             WHERE file_id = old.file_id)
           WHERE docid = old.file_id;
END;