SQLite3 にフィルタ(条件)を渡す

bash で SQLite3 を実行するには

sqlite3 DB名 "sqlite文"

でいい。where を使い条件を与え select するなら

sqlite3 DB名 "select 抜き出すデータ from テーブル名 where 条件

になる。この where に基本、

where (kind in (1,6,7,9735) and authors in ("ab12","cd34") (タグaの値1 in (tag1,tag2,tag3,tag4) or タグaの値2 in (tag1,tag2,tag3,tag4) ) and (タグbの値1 in (tag1,tag2,tag3,tag4) or タグaの値b in (tag1,tag2,tag3,tag4) ))

という形で条件を渡すことにする。
kinds 、ids 、authors などは json 配列の [] 角括弧括りで渡されるので、これを () 丸括弧に変えてやればそのまま in 以降に使える。例えば kinds なら

echo kind in $(echo 対象のjson | jq -c .kinds | tr "[]" "()")

で、
{"kinds":[1,2,3]} => kind in (1,2,3)
と変換できる。
また、あるタイプのタグ(例えば e)の値を一つずつ
"eタグの値" in (tag1,tag2,tag3,tag4) or ...
と tag1〜tag4 まで全てと照らし合わせながら or で繋ぎつつ、別のタイプのタグ(例えば p)の値と and で繋ぎ、それも同様に tag1〜tag4 の全てと照らし合わせながら or で繋ぐ、という力技でなんとかやってみる。
例)
{"#e":["a","b"],"#p":["c","d"]}
=>
( ("a" in (tag1,tag2,tag3,tag4) or "b" in (tag1,tag2,tag3,tag4)) or ("c" in (tag1,tag2,tag3,tag4) or "d" in (tag1,tag2,tag3,tag4)) )

面倒いわ。

ただこれでは json のタグ(Nostr のタグではなく json のタグ)そのものが無かった場合に null が返ってきて色々とバグりそうなので、その場合はまったく何も返ってこないようにしたい。また、where 以降に各条件を and でつないで渡すようにもしたい。そのために add_and_in / add_and_or という関数を作る。またフィルタに until/since タグを渡されたときに created_at と比較するために add_and_compa (and を付けて比較)という関数も作る。そして limit 指定に対しては make_limit という関数で SQLite3 の where に適した形に変える。

function add_and_in(){
	read -r line
	if [ $line == "null" ]; then
		echo ""
	else
		echo "and $1 in $line"
	fi
}

function add_and_or(){
read -r line
tmp=""
if [ "$line" = "null" ]; then
	echo "$tmp"
else
	while read -r element; do
		tmp="${tmp}${element} in (tag1,tag2,tag3,tag4) or "
	done < <(echo $line | jq .[])
	echo "and ($(head -c -4 <<< "${tmp}"))"
fi
}

function add_and_compa(){
	read -r line
	if [ $line == "null" ]; then
		echo ""
	else
		echo "and $1 $2 $line"
	fi
}
function make_limit(){
	read -r line
	if [ $line == "null" ]; then
		echo ""
	else
		echo " limit $line"
	fi
}

これらを使って、フィルタjson を where に適した形にしてしまう make_dbs という関数を作る。

function make_dbs(){

	created_at=$(echo $json | jq -c .created_at | add_and_in created_at)
	ids=$(echo $json | jq -c .ids  | sed 's/"/'\''/g' | add_and_in id | tr "[]" "()")
	authors=$(echo $json | jq -c .authors | sed 's/"/'\''/g'  | add_and_in author | tr "[]" "()")
	kinds=$(echo $json | jq -c .kinds | add_and_in kind | tr "[]" "()") 
	hash_e=$(echo $json | jq -c '."#e"' | add_and_or)
	hash_p=$(echo $json | jq -c '."#p"' | add_and_or) 
	since=$(echo $json | jq -c .since | add_and_compa 'created_at' '>')
	_until=$(echo $json | jq -c .until | add_and_compa 'created_at' '<')
	limit=$(echo $json | jq -c .limit | make_limit)

	condition=$(echo \(1=1 $kinds $authors $ids $hash_e $hash_p $since $_until\))
}

これで例えば　{"kinds":[1,3],"authors":["abc"],"#e":["def","ghi"],"#p":["jkl"],"since":123,"until":456} というフィルタなら

make_dbs '{"kinds":[1,3],"authors":["abc"],"#e":["def","ghi"],"#p":["jkl"],"since":123,"until":456}'

で
(1=1 and kind in (1,3) and author in ('abc') and ("def" in (tag1,tag2,tag3,tag4) or "ghi" in (tag1,tag2,tag3,tag4) ) and ("jkl" in (tag1,tag2,tag3,tag4) ) and created_at > 123 and created_at < 456)
という形に変換できる。これを where に渡せば良い。

フィルタに掛けて返ってきた値をどうするか

1つの REQ に複数のフィルタが配列で渡されることがあるので

この make_dbs 関数にそれぞれを順に渡し、
返ってきた値(created_at)を sort し並べ、
それぞれと同じ名前のファイル jsons ディレクトリから探して開き(cat)、
["EVENT","セッションID",(ファイルの内容)] としてクライアントに渡し、
最後に ["EOSE","セッションID"] を渡す

で、取り敢えず REQ が来た時点での フィルタに適うデータは返せる。

DB_arr_tmp=() # フィルタを created_at に変換したものを1つずつ要素とする一時的な配列
for json in $jsons; do
	make_dbs $json # フィルタを condition に変換
	DB_arr_tmp+=( $(sqlite3 test.db "select created_at from forRelay where ${condition} order by created_at desc$limit") )
done

IFS=$'\n'; DB_arr=( $(sort <<<"${DB_arr_tmp[*]}") ); unset IFS # 一時的な配列を sort で並べ直す
unset DB_arr_tmp

# DB_arr 配列の値(created_at)に応じて順にファイルを取ってきて整形し、クライアントに返す
for line in ${DB_arr[@]}; do
	json=$(cat ./jsons/$line)
	echo "[\"EVENT\",\"$SID\",$json]"
done
unset DB_arr

一旦休憩。