From 40cb473bb34fa98a67cfef279e6906b6fc5ee1ed Mon Sep 17 00:00:00 2001
From: Andreas Baumann <mail@andreasbaumann.cc>
Date: Sat, 9 Dec 2023 16:39:16 +0100
Subject: gave up on fts5 sql with wasm for now, far too convoluted build
 systems

---
 search/fts5/README  | 32 +++++++++++++++++++++++++++++++-
 search/fts5/main.js | 10 ++++++++++
 2 files changed, 41 insertions(+), 1 deletion(-)
 create mode 100644 search/fts5/main.js

(limited to 'search')

diff --git a/search/fts5/README b/search/fts5/README
index a9ce834..5ab27ed 100644
--- a/search/fts5/README
+++ b/search/fts5/README
@@ -19,16 +19,46 @@ EOF
 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
+cp posts.db ../../static/index/.
 
 # 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);
+select uri,highlight(posts, 1, '<b>', '</b>'),snippet(posts, 2, '<b>', '</b>', '...', 50) from posts where posts MATCH 'OpenBSD' ORDER BY bm25(posts) limit 5 offset 0;
 
 # TODO: make a server-side search (search window and result page)
+# this would mean we have to render the hugo and the ranklist part on the server?
+
+# typical "modern" web development craziness.. :-)
+pacman --needed -S sha3sum
+trizen -G emsdk
+cd emdsk && makepkg -sif
+sudo emsdk install latest
+sudo emsdk activate latest
+sudo chown -R abaumann:users /usr/lib/emsdk/
+source /usr/lib/emsdk/emsdk_env.sh
+git clone --recursive https://github.com/jlongster/sql.js
+# add -DSQLITE_ENABLE_FTS5 to CFLAGS in Makefile
+# disable 'sha3sum -c cache/check.txt' (is broken)
+# emcc: error: setting `INLINING_LIMIT` expects `bool` but got `int`
+# set -sINLINING_LIMIT as booelean flag, modern emcc doesn't allow a cost integer here anymore it seems
+make
+# tons of errors, the containersized version uses VCode and WSL, so this things is
+# hairy to build
+# let's try a precompiled one
+# https://verdicts.listen.dev/npm/sql.js-fts5
+npm install sql.js-fts5
+sudo pacman -S browserify
+browserify main.js -o sql-fts5.js
+cp sql-fts5.js ../../themes/new_theme/static/js/.
+cp node_modules/sql.js-fts5/dist/sql-wasm.js ../../themes/new_theme/static/js/.
 
 # 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://www.skypack.dev/view/sql.js-fts5
+# https://github.com/phiresky/sql.js-httpvfs
+# https://github.com/psanford/sqlite3vfshttp
 # https://phiresky.github.io/blog/2021/hosting-sqlite-databases-on-github-pages/
+# https://github.com/kbumsik/sqlite-wasm
diff --git a/search/fts5/main.js b/search/fts5/main.js
new file mode 100644
index 0000000..70479aa
--- /dev/null
+++ b/search/fts5/main.js
@@ -0,0 +1,10 @@
+const initSqlJs = require( 'sql.js-fts5' );
+
+const SQL = initSqlJs({
+  locateFile: file => `http://localhost:1313/js/${file}`
+});
+
+const db = new SQL.Database();
+
+let sqlstr = "SELECT * FROM posts";
+db.run(sqlstr);
-- 
cgit v1.2.3-54-g00ecf