Skip to content

Instantly share code, notes, and snippets.

@bburky
Last active May 17, 2021
Embed
What would you like to do?
EPUB full text search using SQLite FTS5
#!/bin/sh
# Update index:
# epub-fts5.sh
# FTS5 query (avoid single quotes, there's no escaping): https://www.sqlite.org/fts5.html#full_text_query_syntax
# epub-fts5.sh foo OR bar NOT baz
# epub-fts5.sh '"foo bar"'
# epub-fts5.sh 'NEAR(foo bar)'
EPUB_PATH=$HOME/path/to/your/epub/library
if [ "$#" -eq 0 ]; then
sqlite3 "$EPUB_PATH/epub_index.db" <<EOF && echo Index updated
CREATE TABLE IF NOT EXISTS chapters(
epub_path TEXT,
chapter_path TEXT,
chapter_text TEXT,
PRIMARY KEY (epub_path, chapter_path)
);
CREATE VIRTUAL TABLE IF NOT EXISTS chapters_idx USING fts5(chapter_text, content='chapters', tokenize='porter unicode61');
CREATE TRIGGER IF NOT EXISTS chapters_ai AFTER INSERT ON chapters BEGIN
INSERT INTO chapters_idx(rowid, chapter_text) VALUES (new.rowid, new.chapter_text);
END;
-- Add more triggers if desired https://www.sqlite.org/fts5.html#external_content_tables
INSERT OR IGNORE INTO chapters
SELECT
fsdir.name,
zipfile.name,
zipfile.data
FROM fsdir("", "$EPUB_PATH")
JOIN zipfile(fsdir.data)
WHERE
fsdir.name LIKE "%.epub"
AND (
zipfile.name LIKE "%.html" OR
zipfile.name LIKE "%.xhtml"
);
EOF
else
sqlite3 -separator ": " "$EPUB_PATH/epub_index.db" <<EOF
SELECT epub_path, snippet(chapters_idx, -1, "", "", "", 20)
FROM chapters_idx
JOIN chapters ON chapters.rowid = chapters_idx.rowid
WHERE chapters_idx match '$@';
EOF
fi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment