diff options
Diffstat (limited to 'search/fts5/README')
| -rw-r--r-- | search/fts5/README | 87 |
1 files changed, 87 insertions, 0 deletions
diff --git a/search/fts5/README b/search/fts5/README new file mode 100644 index 0000000..681b425 --- /dev/null +++ b/search/fts5/README @@ -0,0 +1,87 @@ +# 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 + +# create post SQL statements from JSON with JQ (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 +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 +sqlite3 posts.db +sqlite> select uri from posts where posts MATCH 'OpenBSD' ORDER BY bm25(posts); +sqlite> 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 +#https://github.com/sql-js/sql.js/issues/546 + +building emscripten2 +/data/INSTALL/emscripten2/src/llvm-project/llvm/include/llvm/Support/Signals.h:119:8: error: variable or field ‘CleanupOnSignal’ declared void + 119 | void CleanupOnSignal(uintptr_t Context); + | ^~~~~~~~~~~~~~~ +In file included from /data/INSTALL/emscripten2/src/llvm-project/llvm/lib/Support/Signals.cpp:251: +/data/INSTALL/emscripten2/src/llvm-project/llvm/lib/Support/Unix/Signals.inc:348:44: error: ‘void llvm::sys::CleanupOnSignal(uintptr_t)’ should have been declared inside ‘llvm::sys’ + 348 | void sys::CleanupOnSignal(uintptr_t Context) { + + +# 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 +# https://www.skypack.dev/view/sql.js-fts5 +npm install sql.js-fts5 +cp node_modules/sql.js-fts5/dist/sql-wasm.js ../../themes/new_theme/static/js/. +cp node_modules/sql.js-fts5/dist/sql-wasm.wasm ../../themes/new_theme/static/js/. + +# WASM is not read with self-hosted hugo server because of a charset-utf-8 added +# to application/wasm: https://github.com/gohugoio/hugo/issues/10734 +# => rebuild Hugo with hugo-0.120.1-charset.patch + +# https://jlongster.com/future-sql-web +# https://github.com/jlongster/absurd-sql +npm install absurd-sql +cp node_modules/absurd-sql/src/sqlite-fs.js ../../themes/new_theme/static/js/. +cp node_modules/absurd-sql/dist/indexeddb-backend.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://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 +sudo pacman -S typescript +# typescript compile errors +# 362 this.wasm._free(blobPtr); |