1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
# Search index with Sqlite3 FTS5 Full Text Search
# generate JSON dynamically with a JSON output generator,
# see https://halfelf.org/2017/hugos-making-json/
#curl http://localhost:1313/index.json > posts.json
curl http://www.andreasbaumann.cc/index.json > posts.json
# we need sqlite and FTS5
# https://www.legendu.net/misc/blog/hands-on-full-text-search-in-sqlite3/
pacman -S sqlite3
# use posts.json to create entries in the virtual FTS5 table
rm posts.db
cat <<EOF | sqlite3 posts.db
CREATE VIRTUAL TABLE posts USING fts5(uri,title,content);
EOF
# https://jqlang.github.io/jq/manual/
jq -j '.[] | "INSERT INTO posts(uri, title, content) VALUES (", ( [ .uri, .title, .content // empty ] | map(.|gsub("'"'"'";"`")|gsub("\n";" ")|@sh) | join(",")), ");\n"' \
posts.json > posts.sql
sqlite3 posts.db < posts.sql
# some test queries
# https://www.legendu.net/misc/blog/hands-on-full-text-search-in-sqlite3/
# https://sqlite.org/fts5.html
select uri from posts where posts MATCH 'OpenBSD' ORDER BY bm25(posts);
select uri,highlight(posts, 1, '<b>', '</b>'),snippet(posts, 2, '<b>', '</b>', '...', 10) from posts where posts MATCH 'OpenBSD' ORDER BY bm25(posts);
# TODO: make a server-side search (search window and result page)
# TODO: add as sqlite.js to page with data (local sqlite fts search)
# https://blog.ouseful.info/2022/04/06/compiling-full-text-search-fts5-into-sqlite-wasm-build/
# https://jlongster.com/future-sql-web
# https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/
|
