wanderinghorse.net website

fts-pages.sql at tip
Login

File site-tools/fts-pages.sql from the latest check-in


-- 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;