Ⅴ-2. ESP32によるIoTゲートウェイの開発②

 BLE通信の実験も最終回になりました。今回はサーバーサイドの作業が中心になります。
 まずMySQL(MariaDB)でデータベースを作成します。続いて、受信データからデータベースを更新する仕組みを、サーバーサイドスクリプトで作成します。これで、センサーデバイスからゲートウェイを介してサーバーに至る、一連のシステムが完成します。最後に動作を検証して今後の課題を考えます。

 ※サーバーサイド・スクリプト等のダウンロードは右側の[Download]ボタンをクリックしてください。  


1.データベースの作成

(1)データベースの作成

 ホスト側のデータベースはMySQLから発展したMariaDBを使用します。参考までに、ここではRaspberry PiにWi-Fi接続したパソコン上で、Windowsターミナル・エミュレータのPoderosaを使用しています。それぞれの環境で使えるSSHクライアントを準備してください。
 まず、「mysql -u root -p」と入力してMariaDBを起動しパスワードを入力します。
 次にデータベースを作成しますが、ここではdatastackという名前をつけています。「create database datastack;」と指示して、作成されたら「use datastack;」でdatastackを操作できる状態に移動します。
$ mysql -u root -p Enter password: ######## Welcome to the MariaDB monitor. Commands end with ; or \g.     :    (省略)     : MariaDB [(none)]> create database datastack; MariaDB [(none)]> use datastack; MariaDB [datastack]>

(2)テーブルの作成

 datastackに作成したいテーブルを、もう一度確認しておきましょう。

 次のコードをコピペして一気にテーブルを作ってしまいましょう。
CREATE TABLE datatable ( id_no INT NOT NULL AUTO_INCREMENT, gtw_id INT NOT NULL, dev_id INT NOT NULL, seq_no INT NOT NULL, state INT NOT NULL, temp FLOAT(7,2) NULL, humd FLOAT(7,2) NULL, daytime TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id_no) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

 これで計測データの記録準備が整いました。


2.伝送方式とプログラム

○PHPスクリプトの検討

 前章の「ホスト間インターフェイス」で定めたように、BLEGatewayはホスト宛に、HTTPのGETメソッドでリクエストを送信してきます。リクエスト中の"GET /"の次のrequest_URIが、リクエストによって起動されるファイル名になるので、スクリプトはinsert_db.phpという名前のファイルに記述します。
 その後方のクエリー・ストリング、つまり`?'に続いて'&'で区切られた変数名と値の関係を示す文字列は、PHPのスーパーグローバル変数(単に「定義済み変数」とも)で取り出すことができます。この$_GETは、対象を連想配列として扱うのが特徴です。この特徴をうまく利用すると、クエリー・ストリングから、SQLのデータ挿入を指示するINSERT文を柔軟に生成することが可能になります。
 例えば、BLEGatewayから次のようなリクエストが送信された場合を考えてみましょう。
GET /insert_db.php?&gtw_id=1&dev_id=2&seq_no=34&state=0&temp=25.80&humd=56.00        HTTP/1.1\r\nHost: xx\r\nConnection: close\r\n\r\n"

これに対して、値がゼロの次のような連想配列をテンプレートとして設定しておきます。
<?php $ar = array('gtw_id' => 0, 'dev_id' => 0, 'seq_no' => 0, 'state' => 0, 'temp' => 0.0, 'humd' => 0.0); ?>

 すると、次のような方法でテンプレートの変数名とそれに対応する値を、それぞれカンマ区切りの変数名並びと値並びに変換することができます(ただしその前に、データベースに接続しておく必要があります)。
<?php foreach($ar as $kwd => $val) { $ar[$kwd] = $_GET[$kwd]; } $cols = "";    // 変数名並び用 $vals = "";    // 値並び用 foreach($ar as $kwd => $val) { if (strlen($cols) > 0) { $cols .= ","; $vals .= ","; } $cols .= $kwd; $vals .= $val; } ?>

 上記の結果として、$colsには変数名の並び
   gtw_id,dev_id,seq_no,state,temp,humd
が、また$valsには値並び
   1,2,34,0,25.80,56.00
が現れ、これを次の式に代入すると、
<?php $sql = "INSERT INTO datatable" . "(" . $cols . ") VALUES (" . $vals . ")"; ?>

テーブルへの行挿入SQL文   

INSERT INTO datatable(gtw_id,dev_id,seq_no,state,temp,humd) VALUES (1,2,34,0,25.80,56.00)

が生成されます。このSQL文を実行させることで、受信したデータをデータベース・テーブルに追加記録することができます。


3.サーバーサイド・スクリプト

(1)insert_db.phpの作成

 以上で、PHPの特徴を利用してクエリー・ストリングからSQL文を生成する、巧みな方法を知ることができました。PHPスクリプトinsert_db.phpでは、ケースによって変化するテンプレートを記述することから、できるだけシンプルなコードとします。データフィールドの追加などが発生した場合は、データベーステーブルの変更に合わせて、テンプレート部分だけを変更すればただちに新しい様式に対応できるわけです。そして、SQL文の生成や実行を行うためには、別途、class MySqlDBを作成することにします。
 ついでながら、insert_db.phpと次のMySqlDB.phpの2つのファイルは、ドキュメント・ルート(Raspbian Stretchの場合は/var/www/html)配下に作成します。
    require_once('MySqlDB.php');
    $objDb = new MySqlDB();
    $ar = array('gtw_id' => 0, 'dev_id' => 0, 'seq_no' => 0, 'state' => 0, 'temp' => 0.0, 'humd' => 0.0);
    $stat = $objDb->insertArray("datatable", $ar);
    if (!$stat)
        echo "Failed!\n";
    else
        echo "Completed!\n";

(2)MySqlDBクラスの作成

 class MySqlDBはMySqlDB.phpファイルに記述し、SQL文の生成ロジックの他に、データベース接続メソッドや、実行したSQL文や実行結果の通知メソッドなどのコードも含めます。
 以下に、SQL文の生成部分を示します。全体のコードはダウンロードしてご覧ください。
    /* テーブルに配列要素を挿入する
         $table: テーブル名
         $ar: 挿入列指定のテンプレートになる連想配列 */
    public function insertArray($table, $ar) {
        $stat = $this->connect();
        if (!$stat)
            return false;

        /* <Template > $ar = array('gtw_id' => 0, 'dev_id' => 0, 'seq_no' => 0,
					'state' => 0, 'temp' => 0.0, 'humd' => 0.0); */
        foreach($ar as $kwd => $val) {
            $ar[$kwd] = $_GET[$kwd];
        }

        $cols = "";
        $vals = "";
        foreach($ar as $kwd => $val) {
            if (strlen($cols) > 0) {
                $cols .= ",";
                $vals .= ",";
            }
            $cols .= $kwd;
            $vals .= $val;
        }
        $this->sql = "INSERT INTO " . $table . "(" . $cols . ") VALUES (" . $vals . ")";

        try {
            $stmt = $this->objDb->prepare($this->sql);
            $res = $stmt->execute();
            $db = NULL;
            $this->status = "";
        } catch(PDOException $e) {
            $this->status = $e->getMessage();
            return false;
        }
        return true;
    }


4.動作検証

○問題の発生① 〔計測データ漏れ〕

 これはすでに説明した問題ですが、計測データの授受状況をチェックし始めるとすぐ、計測データが脱落していることに気付きました。原因はBLEBroadcasterでの「TG1WDT_SYS_RESET」の発生で、その意味は「Timer Group1 Watch dog reset digital core」ということ、つまりウォッチドッグタイマー1による割り込みでリセットされていることが分かりました。900回中に5回(0.56%)の頻度で発生し、これでは使い物になりません。
 これに対応するために、timerWrite(timer, 0)をディープスリープ直前に挿入するなど試しましたが、好ましい結果が得られません。最終的に、割り込み発生の可能性があるGPIOのハンドリング(異常発生用プッシュボタン関連の)をすべて除去し、ESPRESSIFのESP-IDF Programming GuideのInterrupt Watchdog API Referenceを参考にして、esp_int_wdt_init()を使用することで大幅に改善されました。
 次の表がその結果です。
 テストは13時49分から19時4分まで行いました。データ脱落の原因を把握してはいませんが、14時30分前後に近辺で大昔の電子レンジを使っていたので、そのノイズが原因かも知れません。なお、後日の22時から翌日10時までのテストでは、合計11,610データで脱落はゼロでした。


○問題の発生② 〔VSCの不審な挙動〕

 Visual Studio Codeは使い始めたばかりでまったく未熟なのですが、BLEGatewayのアップロード時に、
    #include <Adafruit_SSD1306.h>
    #include <Adafruit_GFX.h>
の2行の下に赤い波線が現れてエラーになってしまいました。またその後、BLEBroadcasterでもDHTライブラリーのインクルードで同様の問題が発生しました。
 これが正しい方法かどうか分かりませんが、Explorerのそれぞれのプロジェクトにあるc_cpp_properties.jsonを開いて、次のようなパスを追加しました。当面、これで解消しています。
   <BLEGateway>
   <BLEBroadcastre>


○動作確認

 以下に一連の結果を掲げます。黒の背景色に白文字の図はVSCodeのTERMINAL画面です。
 BLEBroadcasterが起動すると、次のように1秒間のアドバータイジングと10秒間のディープスリープを繰り返します。送信確認のために、その時点で送信したシーケンス番号を表示しています(「*** Seq-No. n ***」のように)。

 BLEGatewayのデバッグモードをfalseにした状態では、BLEBroadcasterからの受信データをモニター表示します。

 デバッグモードをtrueにすると、GETメソッドによる送信内容とそのレスポンスを観察することができます。insert_db.phpの最後にecho文で"Completed!"を出力していますが、その内容も表示されているのがわかります。そのecho文に続けて、MySqlDBで生成された結果をgetSqlCode()で取得してechoすれば、変換結果のSQL文をこの画面で確認することもできます。お試しください。

 以上の経過は、ホストのデータベースに記録・蓄積されます。早速Poderosa(任意のSSHクライアント)からMySQL(MariaDB)にログインして、どのように記録されているか確認してみましょう。
$ mysql -u root -p Enter password: ######## Welcome to the MariaDB monitor. Commands end with ; or \g. MariaDB [(none)]> use datastack; MariaDB [datastack]> SELECT * FROM datatable;


 phpMyAdminをインストールしていれば、ブラウザのアドレスバーに「192.168.~.~/phpmyadmin/」のようにホストのIPアドレスを指定して入力すると、ログイン画面が表示されます。

 画面左に表示されたデータベースツリーからデータベース名「datastack」を選択します。

 右画面上部に表示されたテーブル一覧の「datatable」の「表示」アイコンをクリックすると、記録内容を一覧表示できます。

 一覧表示の内容はCSVやJSON、PDF、必要ならSQLなどにも変換してエキスポートできます。


5.今後の課題

 IoTゲートウェイと謳ったにしては、最後まで温度と湿度の伝送記録かとがっかりされたかも知れません。しかし、BLEについての基礎知識と実験を通じて、その理解と応用方法はかなり見えてきたのではないでしょうか。とりわけWi-Fiと組み合わせた通信の用途は広く、今回行ったサーバーサイド・スクリプトなどは、拡張性と柔軟性を備えたシステム構築に役に立つことと思います。今回のシリーズを通して、BLEを身近に引き寄せていただき、様々な応用システムに発展させていただければ嬉しいです。
 最後に、今後の課題あるいは留意事項などを簡単にまとめおくことにしましょう。

①計測間隔の問題

 データベースに記録された計測時刻を見ると、12~13秒毎にデータを受け付けていることが分かります。BLEBroadcasterでは10秒のスリープを区切りとして計測・送信する仕組みになっているのですが、目覚めや計測待ちや送受信に付随する動作などで増加しているわけです。
 気温や湿度を秒単位で計測する必要は少ないかも知れませんが、時間間隔で計測する場合には、正確な間隔をどう設定するかという問題から逃れられません。十分長い時間単位で考えるか、試行して満足できる間隔精度を確保することが必要になります。


②デバイスの接続可能台数など

 準備できるESP32の数に制限があることから、この点はほとんど検討できていません。アドバタイジング時間やスキャニング時間の最適値がいくらかも、まだ検討できていません。多数のデバイスを必要とする場合は、これらを見積もりながら、最終的にネットワーク構築を通しての確認は欠かせません。


③正しい計測時刻の保持

 最も基本的な必須条件です。計測日時をホストのシステムクロックから取得するため、ホスト側で標準時刻による時刻補正を行うなど正確な時刻をキープする必要があります。


④多要素計測と省電力化

 温度と湿度だけでなく、さらに気圧や照度、風速、水温、・・・など計測要素が増加する場合、毎回、すべての計測値を送信すると、センサーデバイス側の消費電力が増加してしまいます。この対策として少なくとも2つの方法が考えられます。
 そのひとつは、すべての計測値が前回計測値と変化していなければ、送信を抑制する方法です。計測値に変化が少ない場合には有効です。
 もうひとつは、単一要素からすべての要素数までを網羅する伝送フォーマットを用意して(例えば要素種別と計測値の配列のようなフォーマット)、変化があった要素の計測値のみを詰め込んでアドバタイズする方法です。ロジックは複雑になりますが、省電力効果が期待できます。


⑤異常発生への対応

 ブロードキャスト方式を踏襲する限り、データの確実な到達を保証することはできません。きわめて優れた省電力効果とトレードオフの関係と割り切らなければならないでしょう。実際、実験で見る限り欠落の発生は無視しうるほど少ないのですが、ただ、何らかの原因でデバイスが通信を中断してしまうケースがあり、これは無視できません。
 ゲートウェイ側で受信の時間監視を行って、一定時間以上経過しても受信データがないデバイスに対してアテンションをあげることが必要と思われます。BLEGateway側にはメモリーに十分な余裕があるので、メール通報とリンクさせることも考えられます。


⑥記録データの活用

 言うまでもなく、収集・蓄積した計測データは十分に活用したいものです。そしてMariaDBはその要求に十分応えてくれます。選択・抽出してグラフ化ツールで見やすく加工したり、統計処理ソフトと連携して分析したりと、期待が膨らみます。


⑦新技術の動向

 Bluetoothは日々進歩しています。最新のBluetooth 5.0(2016年12月策定)で更新されたBLE仕様は、旧規格と比較して、4倍の通信距離、2倍の通信速度、8倍のデータ容量という特徴があり、これに加えて、ペリフェラルが自立したネットワークを形成できるメッシュ・ネットワークが登場して、今後の展開が期待されます。これらを含め、新技術情報のキャッチにはたえず注意を払っておきたいですね。



 長らくお付き合いいただきまして、ありがとうございました。
 それでは、ご健闘を!


 
Copyright (C) 2011-2024 Marchan, All rights reserved.