-- sqlite3 FTS5: https://www.sqlite.org/fts5.html
-- e.g.
-- select rank, uri, snippet(fts, 2, '<B>','</B>', '...', 5)
-- from fts where content match 'ogre' order by rank limit 10;
-- noting that it can return partial <TAG> blocks and therefore
-- needs some massaging before being displayed on the site.
drop table if exists page;
create table page(
id integer primary KEY autoincrement DEFAULT NULL,
-- mtime of the underlying content file
mtime integer NOT NULL,
-- URI of the page
uri text NOT NULL UNIQUE ON CONFLICT REPLACE,
-- full contents of the underlying content file
content text NOT NULL
);
drop table if exists fts;
CREATE VIRTUAL TABLE fts USING fts5(mtime, uri, content, content='page', content_rowid='id');
-- i am continually getting corruption errors when updating the db via triggers, so...
-- after updating the [page] table:
-- delete from page where uri='/notyet';
-- INSERT INTO fts(fts) VALUES('delete-all');
-- insert into fts(rowid, mtime, uri, content) select id,mtime,uri,content from page;
create table reindex(
x integer UNIQUE ON CONFLICT REPLACE
);
create trigger tr_reindex after insert on reindex BEGIN
delete from page where uri='/notyet';
INSERT INTO fts(fts) VALUES('delete-all');
insert into fts(rowid, mtime, uri, content) select id,mtime,uri,content from page;
END;
-- Triggers to keep the FTS index up to date.
-- CREATE TRIGGER tr_ai AFTER INSERT ON page BEGIN
-- INSERT INTO fts(rowid, mtime, uri, content) VALUES (new.id, new.mtime, new.uri, new.content);
-- END;
-- CREATE TRIGGER tr_ad AFTER DELETE ON page BEGIN
-- INSERT INTO fts(fts, rowid, mtime, uri, content) VALUES('delete', old.id, old.mtime, old.uri, old.content);
-- END;
-- CREATE TRIGGER tr_au AFTER UPDATE ON page BEGIN
-- INSERT INTO fts(fts, rowid, mtime, uri, content) VALUES('delete', old.id, old.mtime, old.uri, old.content);
-- INSERT INTO fts(rowid, mtime, uri, content) VALUES (new.id, new.mtime, new.uri, new.content);
-- END;