started a FTS5/sqlite3 search

1 files changed, 34 insertions, 0 deletions

diff --git a/search/fts5/README b/search/fts5/README
new file mode 100644
index 0000000..a9ce834
--- /dev/null
+++ b/search/fts5/README
@@ -0,0 +1,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/