2020年8月24日星期一

Node-Red & MySQL & Line Notify

注意

S =msg.payload

msg.topic = "INSERT INTO led ( `LED_Status`) VALUES ( '" + S + "');"

return msg;

Node-Red 程式

=======================================================================

[{"id":"f869fa31.2f2508","type":"tab","label":"流程5","disabled":false,"info":""},{"id":"ae7468f4.24aea8","type":"mqtt in","z":"f869fa31.2f2508","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":120,"y":80,"wires":[["d90adb1e.55bb18","267de439.cf353c","814976c.686a388"]]},{"id":"d90adb1e.55bb18","type":"ui_text","z":"f869fa31.2f2508","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data","format":"{{msg.payload}}","layout":"col-center","x":380,"y":80,"wires":[]},{"id":"d11d794c.f6c4d8","type":"ui_button","z":"f869fa31.2f2508","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"On led","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"LED開","payloadType":"str","topic":"","x":90,"y":320,"wires":[["ad1c7f22.8f16b","971b5fae.98e77"]]},{"id":"ed1f01ca.24304","type":"ui_button","z":"f869fa31.2f2508","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"Off led","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"LED關","payloadType":"str","topic":"","x":90,"y":380,"wires":[["ad1c7f22.8f16b","971b5fae.98e77"]]},{"id":"ad1c7f22.8f16b","type":"mqtt out","z":"f869fa31.2f2508","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":360,"y":320,"wires":[]},{"id":"971b5fae.98e77","type":"ui_audio","z":"f869fa31.2f2508","name":"","group":"6c9116b.b62d4e8","voice":"zh-TW","always":"","x":320,"y":380,"wires":[]},{"id":"267de439.cf353c","type":"function","z":"f869fa31.2f2508","name":"Format timestamp","func":"var date = new Date();\nvar h = date.getHours();\nvar m = date.getMinutes();\nvar s = date.getSeconds();\nif(h<10){\n h = '0'+h;\n}\nif(m<10){\n m = '0' + m;\n}\nif(s<10){\n s = '0' + s;\n}\nmsg.payload = msg.payload + ' --> Time:(' + h + ':' + m + ':' + s + ')' ;\n\nreturn msg;","outputs":1,"noerr":0,"x":190,"y":220,"wires":[["9ebd8c60.18215"]]},{"id":"9ebd8c60.18215","type":"function","z":"f869fa31.2f2508","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A4wwPNh2WqB7dlfeQyyIAwtggn1kfZSI5LkkCdia1gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":370,"y":220,"wires":[["31dbb78.b800e48"]]},{"id":"31dbb78.b800e48","type":"http request","z":"f869fa31.2f2508","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":540,"y":220,"wires":[["da02d08f.6b501"]]},{"id":"da02d08f.6b501","type":"debug","z":"f869fa31.2f2508","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":690,"y":220,"wires":[]},{"id":"814976c.686a388","type":"function","z":"f869fa31.2f2508","name":"MySQL Function","func":"S =msg.payload\nmsg.topic = \"INSERT INTO led ( `LED_Status`) VALUES ( '\" + S + \"');\"\nreturn msg;\n\n//INSERT INTO `led` (`id`, `time_led`, `LED_Status`) VALUES ('1', current_timestamp(), 'LED開');","outputs":1,"noerr":0,"x":370,"y":120,"wires":[["97a0fefe.2112d","a08402e0.5d7e9"]]},{"id":"a08402e0.5d7e9","type":"mysql","z":"f869fa31.2f2508","mydb":"3ec1eff7.3a59c","name":"MySQLDatabase","x":590,"y":160,"wires":[[]]},{"id":"97a0fefe.2112d","type":"debug","z":"f869fa31.2f2508","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":120,"wires":[]},{"id":"841df58d.ee5e98","type":"mqtt-broker","z":"","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Test","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"3ec1eff7.3a59c","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"mysql_led","tz":"GMT +8"},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]

Node-Red & MySQL random 2 number Store To MySQL Database

[{"id":"6a6300bd.5b3a6","type":"tab","label":"Flow 4","disabled":false,"info":""},{"id":"e85555cf.03b198","type":"debug","z":"6a6300bd.5b3a6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","x":570,"y":40,"wires":[]},{"id":"7fa0d45f.9028ec","type":"function","z":"6a6300bd.5b3a6","name":"MySQL Function","func":"temperature = msg.payload[0]\nhumidity = msg.payload[1]\n\n//humidity = msg.payload.humidity\n//temperature = msg.payload.temperature\nmsg.topic = \"INSERT INTO temp_humi ( `temperature`, `humidity`) VALUES ( \"+ temperature+\", \"+ humidity +\");\"\nreturn msg;\n\n","outputs":1,"noerr":0,"x":450,"y":200,"wires":[["bb82ea59.32bd68","db671226.0ba2f"]]},{"id":"bb82ea59.32bd68","type":"mysql","z":"6a6300bd.5b3a6","mydb":"3ec1eff7.3a59c","name":"MySQLDatabase","x":690,"y":200,"wires":[[]]},{"id":"8a986dfc.3816c","type":"inject","z":"6a6300bd.5b3a6","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":80,"y":140,"wires":[["9ed27499.0d9ca8","7740e351.41719c"]]},{"id":"9ed27499.0d9ca8","type":"random","z":"6a6300bd.5b3a6","name":"","low":"-50","high":"50","inte":"true","property":"payload","x":240,"y":120,"wires":[["fd7b9e51.97bab"]]},{"id":"7740e351.41719c","type":"random","z":"6a6300bd.5b3a6","name":"","low":"1","high":"100","inte":"true","property":"payload","x":240,"y":160,"wires":[["fd7b9e51.97bab"]]},{"id":"fd7b9e51.97bab","type":"join","z":"6a6300bd.5b3a6","name":"","mode":"custom","build":"array","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"2","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":390,"y":120,"wires":[["e85555cf.03b198","7fa0d45f.9028ec"]]},{"id":"db671226.0ba2f","type":"debug","z":"6a6300bd.5b3a6","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":660,"y":300,"wires":[]},{"id":"3ec1eff7.3a59c","type":"MySQLdatabase","z":"","name":"","host":"127.0.0.1","port":"3306","db":"mysql_test1","tz":"GMT +8"}]

2020年8月23日星期日

Node-RED & MySQL (Insert , Delete , Update , Select )

Setting MySQL DataBase

/Template

<tr>

<th>Index</th>

<th>Sensor</th>

<th>Temperature</th>

<th>Humidity</th>

</tr>

<td>{{$index}}</td>

<td align="center">{{msg.payload[$index].SensorName}}</td>

<td align="center">{{msg.payload[$index].Temperature}}</td>

<td align="center">{{msg.payload[$index].Humidity}}</td>

</tr>

</table>

/Insert

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor1', '25', '78');

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor2', '45', '56');

INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor3', '38', '88');

SELECT * FROM sensor

DELETE FROM sensor WHERE Sensor_Id = 6

UPDATE sensor SET Temperature = 0 WHERE Sensor_Id = 7

/ PhpMyAdmin Setting

[{"id":"5a02d23f.8784ec","type":"tab","label":"流程4","disabled":false,"info":""},{"id":"e8de394c.aad598","type":"mysql","z":"5a02d23f.8784ec","mydb":"73a92d28.0c3eb4","name":"Test","x":370,"y":300,"wires":[["3e0abcb2.4bb474","4edf2cfa.0ee964"]]},{"id":"e3ca7c3f.1e92f","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data1","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor1', '25', '78');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":180,"wires":[["e8de394c.aad598"]]},{"id":"3e0abcb2.4bb474","type":"debug","z":"5a02d23f.8784ec","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":300,"wires":[]},{"id":"15998a8.733e176","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data2","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor2', '45', '56');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":240,"wires":[["e8de394c.aad598"]]},{"id":"e5c239a7.944e78","type":"inject","z":"5a02d23f.8784ec","name":"Insert Data3","topic":"INSERT INTO `sensor` (`SensorName`, `Temperature`, `Humidity`) VALUES ('Sensor3', '38', '88');","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":300,"wires":[["e8de394c.aad598"]]},{"id":"595c5780.0c9f98","type":"inject","z":"5a02d23f.8784ec","name":"Select","topic":"SELECT * FROM sensor","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":340,"wires":[["e8de394c.aad598"]]},{"id":"d2cc1836.ca4e98","type":"inject","z":"5a02d23f.8784ec","name":"Delete","topic":"DELETE FROM sensor WHERE Sensor_Id = 6","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":380,"wires":[["e8de394c.aad598"]]},{"id":"a64a4ae2.71f728","type":"inject","z":"5a02d23f.8784ec","name":"UpDate Record 7","topic":"UPDATE sensor SET Temperature = 0 WHERE Sensor_Id = 7","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":420,"wires":[["e8de394c.aad598"]]},{"id":"4edf2cfa.0ee964","type":"ui_template","z":"5a02d23f.8784ec","group":"6c9116b.b62d4e8","name":"","order":9,"width":0,"height":0,"format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Sensor</th>\n <th>Temperature</th> \n <th>Humidity</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td align=\"center\">{{msg.payload[$index].SensorName}}</td>\n <td align=\"center\">{{msg.payload[$index].Temperature}}</td> \n <td align=\"center\">{{msg.payload[$index].Humidity}}</td>\n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","x":560,"y":360,"wires":[[]]},{"id":"73a92d28.0c3eb4","type":"MySQLdatabase","z":"","name":"MySQL_Test2","host":"localhost","port":"3306","db":"mysql_test2","tz":""},{"id":"6c9116b.b62d4e8","type":"ui_group","z":"","name":"Test","tab":"eeb8e179.a47a4","order":1,"disp":true,"width":"6","collapse":false},{"id":"eeb8e179.a47a4","type":"ui_tab","z":"","name":"MySQL","icon":"dashboard","disabled":false,"hidden":false}]

安裝MySQL

XAMPP Apache + MariaDB + PHP + Perl

什麼是XAMPP？

XAMPP是最流行的PHP開發環境

XAMPP是完全免費且易於安裝的Apache發行版本，其中包含MariaDB、PHP和Perl。XAMPP開放源碼套件的設置讓安裝和使用出奇容易。

下載點

https://www.apachefriends.org/zh_tw/index.html

參考來源

https://a091234765.pixnet.net/blog/post/403781468-%5B%E7%B6%B2%E9%A0%81%E6%8A%80%E5%B7%A7%E5%AD%B8%E7%BF%92%E7%AD%86%E8%A8%98%5Dxampp-mysql%E7%99%BB%E5%85%A5%E8%A8%AD%E7%BD%AE

安裝後需要修改MySQL 設定才能啟動 Apache 與MySQL

Apache + MariaDB (MySQL)

2020年8月15日星期六

MQTT 架構 mosquitto 安裝開發工具 mqttlens 安裝

源自於 https://yazelin.github.io/usc2017nsp/week1.html

MQTT 架構

發佈者Publisher、代理人Broker和訂閱者Subscriber

發佈者Publisher ( Arduino )
代理人Broker (筆電/PC)
訂閱者Subscriber (筆電/PC/手機)

mosquitto 安裝

mosquitto 安裝
注意：「不勾選」Service
Win32OpenSSL_Light-1_0_2k 安裝
注意：安裝時將DLL檔安裝至Bin資料夾內

從預設路徑(:c:/OpenSSL-Win32/bin)中複製dll檔

到mosquitto資料夾中
pthreadVC2
下載完後直接移動至mosquitto資料夾內
參考影片

開發工具 mqttlens 安裝

mqttlens安裝
(若案裝失敗以系統管理員身份執行Chrome後重新安裝)
參考影片

備註

Win32OpenSSL_Light安裝時勾選將Dll檔安裝於bin資料夾中，完後需將libeay32.dll、ssleay32.dll複製到mosquitto資料夾內
pthreadVC2.dll下載後也需移動至mosquitto資料夾內
mqttlens為Chrome的擴充功能
參考連結MQTT教學（一）：認識MQTT
參考連結MQTT教學（二）：安裝MQTT伺服器Mosquitto
參考連結MQTT教學（四）：使用MQTTLens訂閱與發布MQTT訊息

訂閱：文章 (Atom)