前回の反省にもとづき、今回実施の実証実験（もどき）は記録という点だけは充実しました。無事？実験期間が終わったのでその記録の処理にかかりました。記録は充実したもののJavaScriptもSQLも良く知らない事を露呈、翻弄されてしまいました。まあね、改良点がハッキリしたので、成果はあったということにしておきますが。
※「ブロックを積みながら」投稿順 index はこちら
BBC micro:bit v2をBluetoothセンサ端末とし、ラズパイ3をデータ格納とWeb表示のためのnodeREDサーバとして「夜間頻尿モニタリング」の「セルフ介護」を狙っております本プロジェクトです。対象自分、作るのも自分。ようやくフル１週間分のデータが取得できました。ここから「応用」面の考察にすすめるかと思いきや、まだまだソフトウエア的にボロボロであることが判明。修正すべき点が多いです。
BBC micro:bit v2、健気に１週間の連続動作達成
前回は１週間働いたつもりが、最初の１日でサーバへの接続が切れていて無駄骨だったmicro:bitです。今回も健気に単三電池(百均アルカリ）２本の寿命をまっとうするまで動作しつづけていました。連続動作記録 7日と11時間39分27秒であります。電池投入から電池切れまでの期間を実証実験もどきの期間としておりますが、１週間ちょいというのは、ほぼ目論見どおり。頑張ったな micro:bit v2。
さてこの期間に11097回のBLEパケットをラズパイに向けて送信し、データがラズパイ３に接続のHDDに記録されました。そのうちターゲット・イベント（トイレ検出）はBLEからMQTTへの橋渡しをするPython3スクリプトレベルで80回を記録。7.5日に80回か、昼間は居ない時間帯もあることも考えると相当多いな、それともソフトのバグありか？今回はいろいろ記録が残っているのでデータを処理しながらソフトの動作を追及していきたいと思います。
アカラサマなバグあり
「イベント」間の時間間隔のMAX/MINをnodeREDダッシュボードに表示するようになっておるのです。MAXは問題なかったのですが、MINには0秒という表示が現れます。0秒はないだろう。0秒は。バグです。も少し言えば、時間間隔は内部で秒で記録しているのですが、そのまま秒で表示だととっさに実感がわかないデス。何時間何分とか表示したい。要改良。
SQLiteで集計のイベント数とPython3のイベント数が合わない。
SQL側で記録のイベント数を数えてみまたところ、Python3のスクリプト側で記録している数と不一致です。先ほど述べたとおりMQTT送っているPython3スクリプト側は80回、SQL側は79回と言ってます。しかたがないので、両方のタイムスタンプを突き合わせてみたところ、確かに１回差がありました。多かったPython3側の記録をみると、これは前回イベントとの時間間隔が７秒という「多めにカウント」しているケース。トイレの扉をバタバタしたのか、中で暴れた？のか。イベントとしては記録しないで良いケース。生のパケットそのままのPython3側では記録されていますが、SQLへの書き込みを制御しているNodeREDの方は、処理内部のJavaScriptで時間間隔が短かすぎるケースをはじいていたのでした。忘れていたですが、予定通りの機能。
ただ足切り基準は60秒。２分以内のケースが他にもあり、これらはすべて記録しない方が良い「連続」のイベントだと思うので足切り基準を変更（設定可能と）するべきかもしれません。これを取り除くともう少し回数減るか。
NodeREDダッシュボード上に特定期間のイベント回数集計機能追加
折角、データベースに書き込んでいるのだし、特定期間の「イベント」回数を集計する機能くらいつけたろう、どうせSQL１文だし簡単だろ、ということで始めたところがハマリました。まったくもって知識不足。
一番痛かったのは、DBに記録しているタイムスタンプでした。NodeREDのJavaScript関数のデフォで得た日時文字列をそのままSQLiteのTEXT型列に記録していたのです。SQL側で日付の比較のために UnixEpoch に変換しようとして気づきました。
- JavaScript側、年月日の間は / (スラッシュ）、月、日、時は1桁のこともある
- SQLite側、年月日の間は – (マイナス）、月、日、時は0づめ2桁
単なるフォーマット違いですが、SQLiteで即座に数値に変換できないフォーマットでDBに書き込んでいました。次回からはSQLiteに合わせるようにDBへの記録を改めることにいたしました。しかし、記録してしまった今回までのレコードについてはupdateとreplaceで直すしかありませんでした（時間が1桁というケースは後になって気づいたので、現状まだ直しきれてないです。）無知でした。
ようやく比較できると思ったら、まだうまく行きません。SQL側では、以下の関数でUnixEpochに変換しています。
strftime('%s', timestamp)
これって、文字列じゃん！ NodeRED側から流れてくる数値と比較すると文字列と数値の大小比較になって妙な挙動を起こしていました。対策は以下です（こちらのブログ記事のおかげ。ありがとうございました。）
round(strftime('%s', timestamp))
さて、それでもまだ日時の比較がうまく行きませぬ。何が悪いの？数値をダンプして調べてみると、
- SQL側秒単位
- NodeRED側 ms単位
桁があってなかったです。SQL側で%s.sss みたいにしても桁合わせできますが、意味無いのでNodeREDのJavaScriptの側で1000で割って桁を合わせました。
ようやく動作するSQL文を吐き出すNodeRED側のJavaScriptコードがこちら。
var endDate = msg.payload / 1000;
var startDate = flow.get("startDate") / 1000;
const evntKID = flow.get("EVNT").toFixed();
const deviceID = flow.get("deviceID").toFixed();
var sql = 'select count(timestamp) from kvdata ' +
'where deviceid = ' + deviceID + " and " +
'keyid = ' + evntKID + ' and ' +
'data = 1 and ' +
"round(strftime('%s', timestamp)) > " + startDate + ' and ' +
"round(strftime('%s', timestamp)) < " + endDate + ';'
var sqlMsg = {
payload: sql,
topic: sql
}
return sqlMsg;
こんだけ書くのに何時間もかかってしまったです。勉強不足はなはだし。
NodeREDのダッシュボードに追加した回数サマリ出力がこちら。Start Date選んで、End Date選ぶとその間の「イベント」回数を集計するだけのもの。Countの結果表示が内部表現のままだし。。。直さないと。なお、集計数は、時間の0ズメレコードが抜けているデータなので、実数より少ないです。
まあ、いろいろフィードバックはあったということで、あちこち直すんだな！