2022 年度 OSS リテラシ3 : WWW サーバとデータベースサーバの連動
はじめに
WWW サーバの受け取った情報を，PHP 言語で書かれたプログラムを介して， データベースに登録できるようにする．
以下の設定方法は Debian wiki に従っている. <URL:https://wiki.debian.org/PHP>.
準備
パッケージインストール
php から mysql を利用するためのライブラリをインストールする.
$ sudo apt update $ sudo apt install php-mysql php-mdb2-driver-mysql ...(略)...
WWW サーバを再起動する
$ sudo systemctl restart apache2
PHP のサンプルファイルの取得
GitHub からサンプルを取得する.
$ cd (ホームディレクトリに移動) $ sudo apt install git $ git clone https://github.com/sugiymki/iotex-server.git
演習用のテーブルの作成
データベース iotex 内に必要となるテーブル (表) を複数作成する. GitHub から clone したソフトウェア内にテーブルを作るためのコードが 含まれているので, それを利用する．
まず，中身を覗いてみる．データベース操作言語 (SQL) で テーブル monitoring と monitoring_hosts が定義されていることがわかる．
$ lv ~/iotex-server/sample/schema.sql
CREATE TABLE `monitoring` (
`hostname` varchar(20) CHARACTER SET utf8 NOT NULL,
`time` datetime NOT NULL,
`temp` double DEFAULT NULL,
`temp2` double DEFAULT NULL,
`temp3` double DEFAULT NULL,
`humi` double DEFAULT NULL,
`humi2` double DEFAULT NULL,
`humi3` double DEFAULT NULL,
`dp` double DEFAULT NULL,
`dp2` double DEFAULT NULL,
`dp3` double DEFAULT NULL,
`bmptemp` double DEFAULT NULL,
`dietemp` double DEFAULT NULL,
`lux` double DEFAULT NULL,
`objtemp` double DEFAULT NULL,
`pres` double DEFAULT NULL,
`sitemp` double DEFAULT NULL,
`sihumi` double DEFAULT NULL,
`eco2` double DEFAULT NULL,
`tvoc` double DEFAULT NULL,
`winddir` double DEFAULT NULL,
`windvel` double DEFAULT NULL
PRIMARY KEY (`hostname`, `time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `monitoring_hosts` (
`hostname` varchar(20) NOT NULL,
`ip` varchar(15) DEFAULT NULL,
`time` datetime DEFAULT NULL,
PRIMARY KEY (`hostname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
確認したら，MySQL のユーザ iotex で，schema.sql を用いてテーブルを作る． パスワードを聞かれるが，これは MariaDB の iotex ユーザを作成する際に指定したパスワードを入力すること．
$ mysql -u iotex -p iotex < ~/iotex-server/sample/schema.sql Enter password: <設定した iotex のパスワードを入力>
データベース iotex の中に， monitoring と monitoring_hosts の 2 つのテーブルが作成できたことが確認する． ターミナル上から以下のようにコマンドを打てば良い (-e の後ろにデータベース操作言語で命令を書く)
$ mysql -u iotex -p -h localhost iotex -e "show tables" Enter password: +------------------+ | Tables_in_iotex | +------------------+ | monitoring | | monitoring_hosts | +------------------+
PHP スクリプトの利用
GitHub から clone したソフトウェアに含まれる PHP ファイルを ~/public_html/php 以下にリンクして利用する．
$ mkdir ~/public_html/php $ cd ~/public_html/php $ ln -s ~/iotex-server/php/monitoring.php .
データベースに接続するための情報を用意し， それを PHP スクリプトで使うことにする． ファイルは自分自身と www-data グループ (Web サーバで使われるグループ) 以外からは閲覧できないようにしておく. HTTP からアクセスするためには, www-data ユーザもしくは www-data グループに閲覧許可をつけないといけない.
$ cd $ chgrp www-data ~/iotex-server/conf/db_info.php $ chmod 640 ~/iotex-server/conf/db_info.php $ vi ~/iotex-server/conf/db_info.php <?php $SERV="localhost"; $USER="iotex"; $PASS="<password>"; (パスワードを書く. "<" と ">" は削除すること) $DBNM="iotex"; ?>
さらに，サンプルスクリプト内の設定ファイルのパスを修正する． /home/user の user 部分を自分のアカウント名に変更する．
$ vi ~/public_html/php/monitoring.php
// 設定ファイル
require_once("/home/user/iotex-server/conf/db_info.php");
PHP 経由でのデータベースへのデータ入力.
GitHub で配布した php スクリプトは http の GET メソッドを使って 送られてきたデータをデータベースに格納してる.
例えば以下のように, monitoring.php の ? 以降に "変数名=値" を並べて ブラウザにコピペすれば, それを確かめることができる. 以下は jxxxx という名前のデバイスから，時刻 2022/09/01 00:00:00 に， 温度 10.0, 湿度 80 が送られてきたことに相当する．
なお，IP と ユーザ名 (hogehoge) は自分のものに変更すること！
http://10.176.0.XX/~hogehoge/php/monitoring.php?hostname=jxxxx&time=2022-10-01T00:00:00&temp=10.0&humi=80
うまくいくと以下のような表示が現れるはずである．これは実行されたデータベース操作言語の命令である．
insert into monitoring (hostname,time,temp,temp2,temp3,humi,humi2,humi3, dp,dp2,dp3,bmptemp,dietemp,lux,objtemp,pres,sitemp,sihumi,eco2,tvoc) values( "jxxxx","2022-10-01T00:00:00",10.00,null,null,80.00,null,null,null,null,null,null,null,null,null,null,null,null,null,null ) ON DUPLICATE KEY UPDATE temp=10.00,temp2=null,temp3=null,humi=80.00,humi2=null,humi3=null,dp=null,dp2=null,dp3=null,bmptemp=null,dietemp=null,lux=null,objtemp=null,pres=null,sitemp=null,sihumi=null,eco2=null,tvoc=null
INSERT INTO monitoring_hosts VALUES("jxxxx","192.168.0.19","2022-09-01T00:00:00") ON DUPLICATE KEY UPDATE ip="192.168.0.19",time="2022-09-01T00:00:00"
なお，ブラウザ上に以下のように ERROR が出た場合は，db_info.php に書いたパスワードが 間違っていた可能性が高い．
ERROR: SQLSTATE[HY000] [1045] Access denied for user 'iotex'@'localhost' (using password: YES)INSERT INTO monitoring_hosts VALUES("jxxxx","192.168.0.19","2018-01-01T00:00:00") ON DUPLICATE KEY UPDATE ip="192.168.0.19",time="2018-01-01T00:00:00"
動作確認
$ mysql -u iotex -p iotex -e "select * from monitoring" Enter password: +----------+---------------------+------+-------+-------+------+-------+-------+------+------+------+---------+---------+------+---------+------+--------+--------+------+------+---------+---------+ | hostname | time | temp | temp2 | temp3 | humi | humi2 | humi3 | dp | dp2 | dp3 | bmptemp | dietemp | lux | objtemp | pres | sitemp | sihumi | eco2 | tvoc | winddir | windvel | +----------+---------------------+------+-------+-------+------+-------+-------+------+------+------+---------+---------+------+---------+------+--------+--------+------+------+---------+---------+ | jxxxx | 2022-10-01 00:00:00 | 10 | NULL | NULL | 80 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | +----------+---------------------+------+-------+-------+------+-------+-------+------+------+------+---------+---------+------+---------+------+--------+--------+------+------+---------+---------+ $ mysql -u iotex -p iotex -e "select * from monitoring_hosts" Enter password: +----------+--------------+---------------------+ | hostname | ip | time | +----------+--------------+---------------------+ | jxxxx | 192.168.0.19 | 2022-10-01 00:00:00 | +----------+--------------+---------------------+
[参考] データベースのテーブル
テーブル monitoring +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | hostname | varchar(20) | YES | | NULL | |ホスト名 | time | datetime | YES | | NULL | |時刻 | temp | double | YES | | NULL | |温度 (SHT75, 1 or Si7021) | temp2 | double | YES | | NULL | |温度 (SHT75, 2) | temp3 | double | YES | | NULL | |温度 (SHT75, 3) | humi | double | YES | | NULL | |湿度 (SHT75, 1 or Si7021) | humi2 | double | YES | | NULL | |湿度 (SHT75, 2) | humi3 | double | YES | | NULL | |湿度 (SHT75, 3) | dp | double | YES | | NULL | |露点温度 (SHT75, 1) | dp2 | double | YES | | NULL | |露点温度 (SHT75, 2) | dp3 | double | YES | | NULL | |露点温度 (SHT75, 3) | bmptemp | double | YES | | NULL | |温度 (BMP180) | dietemp | double | YES | | NULL | |基板温度 (TMP007) | lux | double | YES | | NULL | |照度 (TSL2561) | objtemp | double | YES | | NULL | |放射温度 (TM007) | pres | double | YES | | NULL | |圧力 (BMP180) | sitemp | double | YES | | NULL | |温度 (Si7021) | sihumi | double | YES | | NULL | |湿度 (Si7021) | eco2 | double | YES | | NULL | |CO2 濃度 (SGP30) | tvoc | double | YES | | NULL | |総揮発性有機化合物量 (SGP30) | winddir | double | YES | | NULL | |風向 | windvel | double | YES | | NULL | |風速 +---------------+-------------+------+-----+---------+-------+ テーブル monitoring_hosts +----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | hostname | varchar(20) | NO | PRI | NULL | | ホスト名 | ip | varchar(15) | YES | | NULL | | IP アドレス | time | datetime | YES | | NULL | | 時刻 +----------+-------------+------+-----+---------+-------+
課題
monitoring.php を用いてテーブルに値を入れる操作を数回行なった後, MySQL モニタでテーブルの値を一覧表示せよ. 実行した SQL 文とその戻り値を wbt のオンラインテキストにコピペせよ.