[FR]: Queries - support full-text search

[martinrotter]

Brief description of the feature request

Add new Query mode -> FTS (full-text search):

SQLite

This will use https://www.sqlite.org/fts5.html

There is a special table

CREATE VIRTUAL TABLE Messages_fts USING fts5(
    title,
    contents,
    content='Messages',
    content_rowid='id'
);

This to rebuild the database

INSERT INTO Messages_fts(Messages_fts)
VALUES ('rebuild');

This to keep DB uptodate

CREATE TRIGGER messages_ai AFTER INSERT ON Messages BEGIN
  INSERT INTO Messages_fts(rowid, title, contents)
  VALUES (new.id, new.title, new.contents);
END;

CREATE TRIGGER messages_ad AFTER DELETE ON Messages BEGIN
  INSERT INTO Messages_fts(Messages_fts, rowid, title, contents)
  VALUES('delete', old.id, old.title, old.contents);
END;

CREATE TRIGGER messages_au AFTER UPDATE ON Messages BEGIN
  INSERT INTO Messages_fts(Messages_fts, rowid, title, contents)
  VALUES('delete', old.id, old.title, old.contents);

  INSERT INTO Messages_fts(rowid, title, contents)
  VALUES (new.id, new.title, new.contents);
END;

Query

SELECT *
FROM Messages_fts
WHERE Messages_fts MATCH 'carrot AND beetroot AND cabbage';

Full query as this

SELECT
    m.id,
    m.is_read,
    m.is_important,
    m.is_deleted,
    m.is_pdeleted,
    m.feed,
    m.title,
    m.url,
    m.author,
    m.date_created,
    m.contents,
    m.enclosures,
    m.score,
    m.account_id,
    m.custom_id,
    m.custom_data,
    GROUP_CONCAT(l.custom_id) AS msg_labels
FROM Messages_fts f
JOIN Messages m ON m.id = f.rowid
LEFT JOIN LabelsInMessages lim ON lim.message = m.id
LEFT JOIN Labels l ON lim.label = l.id
WHERE f MATCH 'carrot AND beetroot AND cabbage'
  AND m.account_id = 1
  AND m.is_deleted = 0
  AND m.is_pdeleted = 0
GROUP BY m.id
ORDER BY m.date_created DESC, m.title ASC;

MariaDB

Special index

ALTER TABLE Messages
ADD FULLTEXT INDEX ft_title_contents (title, contents);

Query as this

SELECT *
FROM Messages
WHERE MATCH(title, contents)
      AGAINST('carrot beetroot cabbage');

Full query

SELECT
    m.*,
    GROUP_CONCAT(l.custom_id) AS msg_labels
FROM Messages m
LEFT JOIN LabelsInMessages lim ON lim.message = m.id
LEFT JOIN Labels l ON lim.label = l.id
WHERE m.account_id = 1
  AND m.is_deleted = 0
  AND m.is_pdeleted = 0
  AND MATCH(m.title, m.contents)
      AGAINST('+carrot +beetroot +cabbage' IN BOOLEAN MODE)
GROUP BY m.id
ORDER BY m.date_created DESC, m.title ASC;

This will be much much much faster than regexps and raw sql stuff.