reader full text search prototype; https://github.com/lemon24/reader/issues/122

search.sql

CREATE TABLE entries (
    id TEXT NOT NULL,
    feed TEXT NOT NULL,
    title TEXT,
    summary TEXT,
    content TEXT,
    PRIMARY KEY (id, feed)
);

CREATE VIRTUAL TABLE entries_search USING fts5(
    id UNINDEXED,
    feed UNINDEXED,
    key UNINDEXED,
    title,
    summary,
    content
);


-- TODO: The values should probably be stripped of HTML here.

CREATE TRIGGER entries_ai AFTER INSERT ON entries BEGIN
    INSERT INTO entries_search
    SELECT
        new.id,
        new.feed,
        json_each.key,
        new.title,
        new.summary,
        json_extract(json_each.value, '$.value')
    FROM json_each(new.content);
END;

CREATE TRIGGER entries_ad AFTER DELETE ON entries BEGIN
    DELETE FROM entries_search
    WHERE
        entries_search.id = old.id AND
        entries_search.feed = old.feed;
END;

CREATE TRIGGER entries_au AFTER UPDATE ON entries BEGIN
    DELETE FROM entries_search
    WHERE (old.id, old.feed) = (entries_search.id, entries_search.feed);

    INSERT INTO entries_search
    SELECT
        new.id,
        new.feed,
        json_each.key,
        new.title,
        new.summary,
        json_extract(json_each.value, '$.value')
    FROM json_each(new.content);

END;


INSERT INTO entries VALUES (
    'one',
    'feed',
    'one title',
    'summary of one',
    '[{"value": "first post"}]'
);
INSERT INTO entries VALUES (
    'two',
    'feed',
    'second title',
    'summary of two',
    '[{"value": "first content"}, {"value": "another one of the contents"}]'
);
INSERT INTO entries VALUES (
    'three',
    'feed',
    'third',
    NULL,
    NULL
);

-- DELETE FROM entries WHERE id = 'three';
UPDATE entries SET summary = 'some summary' WHERE id = 'three';


-- TODO: How would pagination work here? Scrolling window on rank?

.mode line

.print one row per content
.print -------------------
.print

SELECT
    entries.id,
    entries.feed,
    entries_search.key,
    entries_search.rank,
    highlight(entries_search, 3, '>>>', '<<<') as title,
    highlight(entries_search, 4, '>>>', '<<<') as summary,
    highlight(entries_search, 5, '>>>', '<<<') as content
FROM entries, entries_search
WHERE
    (entries.id, entries.feed) = (entries_search.id, entries_search.feed) AND
    entries_search MATCH 't*'
ORDER BY
    entries_search.rank;

.print


.print one row per entry
.print -----------------
.print

WITH search AS (
    SELECT
        id,
        feed,
        rank,
        highlight(entries_search, 3, '>>>', '<<<') as title,
        highlight(entries_search, 4, '>>>', '<<<') as summary,
        json_object(
            'key', key,
            'rank', rank,
            'content', highlight(entries_search, 5, '>>>', '<<<')
        ) as content
    FROM entries_search
    WHERE entries_search MATCH 't*'

    -- https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg115821.html
    -- rule 14 of https://www.sqlite.org/optoverview.html#subquery_flattening
    LIMIT -1 OFFSET 0
)
SELECT
    entries.id,
    entries.feed,
    min(search.rank) as rank,
    search.title,
    search.summary,
    json_group_array(json(search.content)) as content
FROM entries, search
WHERE
    (entries.id, entries.feed) = (search.id, search.feed)
GROUP BY entries.id, entries.feed
ORDER BY
    rank;

.print

~output.txt

one row per content
-------------------

     id = two
   feed = feed
    key = 1
   rank = -1.49152542372881e-06
  title = second >>>title<<<
summary = summary of >>>two<<<
content = another one of >>>the<<< contents

     id = two
   feed = feed
    key = 0
   rank = -1.4251012145749e-06
  title = second >>>title<<<
summary = summary of >>>two<<<
content = first content

     id = one
   feed = feed
    key = 0
   rank = -1.05389221556886e-06
  title = one >>>title<<<
summary = summary of one
content = first post

one row per entry
-----------------

     id = two
   feed = feed
   rank = -1.49152542372881e-06
  title = second >>>title<<<
summary = summary of >>>two<<<
content = [{"key":0,"rank":-1.4251012145749e-06,"content":"first content"},{"key":1,"rank":-1.49152542372881e-06,"content":"another one of >>>the<<< contents"}]

     id = one
   feed = feed
   rank = -1.05389221556886e-06
  title = one >>>title<<<
summary = summary of one
content = [{"key":0,"rank":-1.05389221556886e-06,"content":"first post"}]