alex9ufo 聰明人求知心切: Node-Red UI control LED ON / LED Off 2 (Publish --> Subscribe delay 2 sec ) 含SQLite

Node-Red UI control LED ON / LED Off 2 (Publish --> Subscribe delay 2 sec ) 含SQLite

1.建立資料庫

a. Filename(檔名) :LEDONOFF.db

b. Table (資料表) : LEDtable

c. Filed(欄位) : 三個 id , currenttime , Status

CREATE TABLE LEDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , Status TEXT)

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

2.檢視資料庫的檢視結果

<tr>

<th>Index</th>

</tr>

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

<td>{{msg.payload[$index].currenttime}}</td>

<td>{{msg.payload[$index].Status}}</td>

</tr>

</table>

3..檢視資料庫

SELECT * FROM LEDtable ORDER BY id DESC LIMIT 100;

4. 插入資料

msg.topic = "INSERT INTO LEDtable (id,currenttime, Status) VALUES (?,?,?)";

var id= Date.now() ;

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;

//msg.payload=MQTT的訂閱資料

msg.payload = [id,hms, msg.payload];

return msg;

[{"id":"124d8a23.1285f6","type":"mqtt in","z":"681fb23e.69453c","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":116,"y":114,"wires":[["42005f54.d3e6c"]]},{"id":"a9d36e8d.37634","type":"ui_text","z":"681fb23e.69453c","group":"57fb2f24.9f1f1","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data (MQTT 訂閱的資訊)","format":"{{msg.payload}}","layout":"col-center","x":756,"y":74,"wires":[]},{"id":"c40534ff.731628","type":"ui_button","z":"681fb23e.69453c","name":"","group":"57fb2f24.9f1f1","order":0,"width":0,"height":0,"passthru":false,"label":"On led","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"LED on","payloadType":"str","topic":"","x":110,"y":400,"wires":[["1bc1f3c3.64d7ac","3a41798c.237d76","c4a49581.6bbe78"]]},{"id":"3e84f6a7.66af1a","type":"ui_button","z":"681fb23e.69453c","name":"","group":"57fb2f24.9f1f1","order":0,"width":0,"height":0,"passthru":false,"label":"Off led","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"LED off","payloadType":"str","topic":"","x":110,"y":520,"wires":[["1bc1f3c3.64d7ac","3a41798c.237d76","c4a49581.6bbe78"]]},{"id":"1bc1f3c3.64d7ac","type":"mqtt out","z":"681fb23e.69453c","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":340,"y":520,"wires":[]},{"id":"9f100f75.0b215","type":"function","z":"681fb23e.69453c","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":690,"y":180,"wires":[["1975153c.2fe26b"]]},{"id":"1975153c.2fe26b","type":"function","z":"681fb23e.69453c","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer A41wwPNh2WqB7d1lfeQyyIAwtggn11kfZSI51LkkCdia11gB'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":870,"y":180,"wires":[["b14d166e.9f5108"]]},{"id":"b14d166e.9f5108","type":"http request","z":"681fb23e.69453c","name":"","method":"POST","ret":"txt","paytoqs":false,"url":"https://notify-api.line.me/api/notify","tls":"","persist":false,"proxy":"","authType":"","x":696,"y":254,"wires":[["bbd74828.226738"]]},{"id":"bbd74828.226738","type":"debug","z":"681fb23e.69453c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":886,"y":254,"wires":[]},{"id":"7ceea8ed.6cf8d8","type":"debug","z":"681fb23e.69453c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":666,"y":34,"wires":[]},{"id":"e48d485c.accce8","type":"ui_audio","z":"681fb23e.69453c","name":"","group":"57fb2f24.9f1f1","voice":"zh-TW","always":true,"x":656,"y":134,"wires":[]},{"id":"3a41798c.237d76","type":"ui_text","z":"681fb23e.69453c","group":"57fb2f24.9f1f1","order":7,"width":0,"height":0,"name":"","label":"MQTT Publish Data (MQTT發行的資訊)","format":"{{msg.payload}}","layout":"col-center","x":400,"y":400,"wires":[]},{"id":"5ea60fcf.51db3","type":"function","z":"681fb23e.69453c","name":"","func":"var pub=msg.payload;\nif (pub=='LED on')\n msg.payload='LED 開';\nelse if (pub=='LED off')\n msg.payload='LED 關';\nelse msg.payload=''; \n \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":446,"y":114,"wires":[["7ceea8ed.6cf8d8","9f100f75.0b215","e48d485c.accce8","a9d36e8d.37634","7d3ae53.43d311c"]]},{"id":"c4a49581.6bbe78","type":"debug","z":"681fb23e.69453c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":310,"y":460,"wires":[]},{"id":"42005f54.d3e6c","type":"delay","z":"681fb23e.69453c","name":"","pauseType":"delay","timeout":"2","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":316,"y":114,"wires":[["5ea60fcf.51db3"]]},{"id":"a2184f87.2c262","type":"ui_button","z":"681fb23e.69453c","name":"","group":"57fb2f24.9f1f1","order":11,"width":"4","height":"1","passthru":false,"label":"建立資料庫(只能執行一次)","tooltip":"","color":"","bgcolor":" purple","icon":"","payload":"","payloadType":"str","topic":"CREATE TABLE LEDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , Status TEXT)","x":206,"y":294,"wires":[["da4fd42f.ac1118"]]},{"id":"6f94cd0b.273054","type":"ui_button","z":"681fb23e.69453c","name":"","group":"57fb2f24.9f1f1","order":10,"width":"2","height":"1","passthru":false,"label":"檢視資料庫","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM LEDtable ORDER BY id DESC LIMIT 100;","x":250,"y":340,"wires":[["da4fd42f.ac1118"]]},{"id":"da4fd42f.ac1118","type":"sqlite","z":"681fb23e.69453c","mydb":"61a261a8.68f6a","sqlquery":"msg.topic","sql":"","name":"LED","x":446,"y":314,"wires":[["b9705419.9cbf68"]]},{"id":"b9705419.9cbf68","type":"ui_template","z":"681fb23e.69453c","group":"57fb2f24.9f1f1","name":"檢視結果","order":0,"width":"12","height":"4","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>時間</th> \n <th>狀態</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].currenttime}}</td>\n <td>{{msg.payload[$index].Status}}</td> \n </tr>\n</table>\n","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":596,"y":314,"wires":[["e61f8bea.cbc2a8"]]},{"id":"e61f8bea.cbc2a8","type":"debug","z":"681fb23e.69453c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":766,"y":314,"wires":[]},{"id":"7d3ae53.43d311c","type":"function","z":"681fb23e.69453c","name":"插入資料","func":"msg.topic = \"INSERT INTO LEDtable (id,currenttime, Status) VALUES (?,?,?)\";\nvar id= Date.now() ;\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\n\nvar hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;\n//var id= Date.now() ;\n//msg.payload = [id ,hms, msg.payload];\nmsg.payload = [id,hms, msg.payload];\n\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":380,"y":220,"wires":[["da4fd42f.ac1118"]]},{"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":"57fb2f24.9f1f1","type":"ui_group","z":"","name":"ON OFF 1","tab":"6f605bb4.60a174","order":1,"disp":true,"width":"6","collapse":false},{"id":"61a261a8.68f6a","type":"sqlitedb","z":"","db":"LEDONOFF.db","mode":"RWC"},{"id":"6f605bb4.60a174","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

SQlite簡介

SQLite是一般常用在嵌入式系統的資料庫，特色為支援精簡「SQL」指令、效能好、相對其它資料庫使用更少的記憶體，因為所有資料都儲存在一個檔案當中，所以在移轉資料時也非常便利。

SQLite是一個輕量級的資料庫，像是Android手機的內建資料庫就是使用它。

建立資料表（table）

create table指令的語法為：

create table table_name(field1, field2, field3, ...);

table_name 是資料表的名稱，fieldx 則是欄位的名字。sqlite 的欄位不會檢查是屬於哪一種資料型態：sqlite的欄位可以儲存任何東西：文字、數字、大量文字（blob），它會在適時自動轉換。

加入一筆資料

接下來我們要加入資料了，語法為：

insert into table_name values(data1, data2, data3, ...);

例如

insert into tbl values ('hello!', 10);

insert into tbl values ('goodbye’, 20);

查詢資料

利用 SQL 語法裡的 select ：

select columns from table_name where expression;

最常見的用法，當然是倒出所有資料庫的內容：

select * from tbl;

如果資料太多了，我們或許會想限制筆數：

select * from tbl limit 10;

有時候我們只想知道資料庫一共有多少筆資料：

select count(*) from film;

alex9ufo 聰明人求知心切

2020年11月27日星期五

Node-Red UI control LED ON / LED Off 2 (Publish --> Subscribe delay 2 sec ) 含SQLite

沒有留言:

張貼留言

Messaging API作為替代方案

2020年11月27日 星期五

Node-Red UI control LED ON / LED Off 2 (Publish --> Subscribe delay 2 sec ) 含SQLite

沒有留言:

張貼留言

Messaging API作為替代方案

2020年11月27日星期五