2019年12月8日星期日

Node-RED中的 SQLite 資料庫瀏覽器（DB Browser for SQLite）

SQLite 資料庫瀏覽器

DB Browser for SQLite

https://sqlitebrowser.org/

依照作業系統選擇32bit 或64 bit

我是下載

DB Browser for SQLite - .zip (no installer) for 64-bit Windows

放D碟中解壓縮然後執行 DB Browser for SQLite.exe

Join the chat at https://gitter.im/sqlitebrowser/sqlitebrowser

What it is

DB Browser for SQLite (DB4S) is a high quality, visual, open source tool to create, design, and edit database files compatible with SQLite.

DB4S is for users and developers who want to create, search, and edit databases. DB4S uses a familiar spreadsheet-like interface, and complicated SQL commands do not have to be learned.

Controls and wizards are available for users to:

Create and compact database files
Create, define, modify and delete tables
Create, define, and delete indexes
Browse, edit, add, and delete records
Search records
Import and export records as text
Import and export tables from/to CSV files
Import and export databases from/to SQL dump files
Issue SQL queries and inspect the results
Examine a log of all SQL commands issued by the application
Plot simple graphs based on table or query data

Downloads

Windows

Our latest release (3.11.2) for Windows:

Note - If for any reason the standard Windows release does not work (e.g. gives an error), try a nightly build (below).

Nightly builds often fix bugs reported after the last release. 😄

註冊 FRED 學習Node-RED https://fred.sensetecnic.com/

註冊 FRED 學習Node-RED https://fred.sensetecnic.com/

參考 http://developers.sensetecnic.com/article/how-to-import-a-node-red-flow/

Node-RED 的前端（FRED）為雲端中的多個用戶管理Node-RED的實例。

註冊 https://fred.sensetecnic.com/ 選擇Free

輸入相關資料

選擇最下面的 FRED Short Free 50 node limit, 24 hour run time

進入mail中驗證確認你的email地址

進入 https://fred.sensetecnic.com

Menu > Import > Clipboard

將程式import進入FRED中

需要添加node節點時

WHAT IS FRED

Front End for Node-RED (FRED) manages instances of Node-RED for multiple users in the cloud. We manage and optimize your instance of Node RED so you worry about accomplishing your project, not setting up and maintaining your Node-RED instance.

CREATE A FRED ACCOUNT

To begin our tutorial create your own Node-RED instance in the cloud. Register for a free account at http://fred.sensetecnic.com.

After registering make sure to activate your account via your email. You will not be able to login until you validate your account.

IMPORTING A FLOW USING THE CLIPBOARD.

Importing a flow in FRED (Node-RED) is really simple. When you log in you can access the import from clipboard function under the

Menu > Import > Clipboard

menu.

For this guide we will be copying the this simple flow:

[cc]
[{“id”:”f1019291.0efe7″,”type”:”debug”,”name”:””,”active”:true,”console”:”false”,”complete”:”false”,”x”:350,”y”:260,”z”:”65e4be7c.9a1b4″,”wires”:[]},{“id”:”e45ccc34.1ba33″,”type”:”inject”,”name”:””,”topic”:””,”payload”:””,”payloadType”:”date”,”repeat”:””,”crontab”:””,”once”:false,”x”:192,”y”:172,”z”:”65e4be7c.9a1b4″,”wires”:[[“f1019291.0efe7”]]}]
[/cc]

Select and copy to your clipboard. Paste it into the modal window and click “Ok”.

You can then position your flow by clicking anywhere on the canvas.

That’s it, you’re done.

Node-RED insert and select using binding

Database examples - insert and select using binding

源自於 https://flows.nodered.org/flow/2c48fc395772e22ecc64

A selection of database examples. How you can manipulate the message topic and payload in various ways to get or insert data into a database. Includes bind columns, getting data directly from an input, converting automatically, templates etc. Examples are in SQLite but can be in MySQL or Postgres. Database table = "CREATE TABLE test (id INTEGER, text VARCHAR);".

CREATE TABLE RFIDtable( id INT PRIMARY KEY NOT NULL, currenttime TEXT , uidname TEXT)

INSERT Function

-----------------------------------------------------------

msg.topic = "INSERT INTO RFIDtable (id,currenttime, uidname) VALUES (?,?,?)";

var date = new Date();

var h = date.getHours();

var m = date.getMinutes();

var s = date.getSeconds();

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var hms= h + ':' + m + ':' + s ;

var id= Date.now() ;

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

return msg;

-----------------------------------------------------------

View Data

SELECT * FROM RFIDtable ORDER BY id DESC LIMIT 100;

Node-RED SQLite DataBase

Create database db_004.db in C:\Users\alex\AppData\Roaming\npm directory

CREATE TABLE RANDOMNUM( id INT PRIMARY KEY NOT NULL, uidname TEXT NOT NULL)

Insert Function

msg.topic = "INSERT INTO RANDOMNUM (id,uidname) VALUES (?,?)";
var id= Date.now() ;
msg.payload = [id , msg.payload];
return msg;

先將Node-red 程式停止用 DB Browser for SQLite 程式瀏覽資料庫的資料

Flow JSON Source code

[{"id":"e20757bf.7a8bd8","type":"comment","z":"e589c8e1.a6c6e8","name":"sqlite.org","info":"https://fred.sensetecnic.com/\nhttp://noderedguide.com/tutorial-sqlite-and-node-red/\nhttps://www.sqlite.org/lang.html\nhttps://www.sqlite.org/datatype3.html#expraff\n\nCREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)\n\n//INSERT\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;\n\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000\n\nSELECT COUNT(*) FROM RANDOMNUM\n\nUPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0\n\nSELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;\n<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>\n\n","x":120,"y":20,"wires":[]},{"id":"3d894a61.e39596","type":"inject","z":"e589c8e1.a6c6e8","name":"CREATE DB","topic":"CREATE TABLE RANDOMNUM( id INT PRIMARY KEY NOT NULL, uidname TEXT NOT NULL) ","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":80,"wires":[["5555653.e543f9c"]]},{"id":"c36fd839.ffe478","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":570,"y":80,"wires":[]},{"id":"34673595.cf49da","type":"inject","z":"e589c8e1.a6c6e8","name":"INSERT","topic":"","payload":"Alex_test","payloadType":"str","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":220,"wires":[["5e024b52.b7b894"]]},{"id":"5e024b52.b7b894","type":"function","z":"e589c8e1.a6c6e8","name":"INSERT","func":"\nmsg.topic = \"INSERT INTO RANDOMNUM (id,uidname) VALUES (?,?)\";\nvar id= Date.now() ;\nmsg.payload = [id , msg.payload];\nreturn msg;\n","outputs":1,"noerr":0,"x":460,"y":220,"wires":[["e896b4d1.a91068"]]},{"id":"94d53ffd.cc12f","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":220,"wires":[]},{"id":"4892e705.435ad8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 30s","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":280,"wires":[["1b74bd9.e807b42"]]},{"id":"b7961ad7.ebc218","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 24h","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":320,"wires":[["1b74bd9.e807b42"]]},{"id":"80c34500.0ec9b8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 7d","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":360,"wires":[["1b74bd9.e807b42"]]},{"id":"b0726f0e.9de2e","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":530,"y":340,"wires":[]},{"id":"9344f59.05f8e08","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":540,"wires":[]},{"id":"e11209f0.f0b0d8","type":"inject","z":"e589c8e1.a6c6e8","name":"COUNT","topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":560,"wires":[["a802c882.9e8368"]]},{"id":"b0d564b2.35d6f8","type":"inject","z":"e589c8e1.a6c6e8","name":"SELECT","topic":"SELECT * FROM RANDOMNUM ORDER BY id DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":620,"wires":[["f1cdb8b0.ff1078"]]},{"id":"a92b7df2.2f457","type":"ui_template","z":"e589c8e1.a6c6e8","group":"320899a7.844536","name":"","order":0,"width":"6","height":"7","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>id</th>\n <th>uidname</th> \n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].id}}</td>\n <td>{{msg.payload[$index].uidname}}</td> \n \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":600,"y":580,"wires":[[]]},{"id":"850b713a.65bde","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":620,"wires":[]},{"id":"5555653.e543f9c","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":340,"y":80,"wires":[["c36fd839.ffe478"]]},{"id":"e896b4d1.a91068","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":600,"y":220,"wires":[["94d53ffd.cc12f"]]},{"id":"1b74bd9.e807b42","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":340,"wires":[["b0726f0e.9de2e"]]},{"id":"a802c882.9e8368","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":560,"wires":[["9344f59.05f8e08"]]},{"id":"f1cdb8b0.ff1078","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":380,"y":620,"wires":[["a92b7df2.2f457","850b713a.65bde"]]},{"id":"74154e1b.94744","type":"mqtt out","z":"e589c8e1.a6c6e8","name":"","topic":"ale9ufo/sqlite4","qos":"","retain":"","broker":"e4d9b72d.d14398","x":440,"y":400,"wires":[]},{"id":"c0e847f1.f45d78","type":"mqtt in","z":"e589c8e1.a6c6e8","name":"","topic":"ale9ufo/sqlite4","qos":"2","datatype":"auto","broker":"e4d9b72d.d14398","x":130,"y":160,"wires":[["e3ee04a6.c3c8b8","62720a94.0617f4"]]},{"id":"bb882ae1.a38818","type":"inject","z":"e589c8e1.a6c6e8","name":"","topic":"","payload":"{\"RFIDNo\":\"Alex9ufo\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":400,"wires":[["74154e1b.94744"]]},{"id":"e3ee04a6.c3c8b8","type":"json","z":"e589c8e1.a6c6e8","name":"","property":"payload","action":"str","pretty":false,"x":290,"y":160,"wires":[["5e024b52.b7b894"]]},{"id":"6eb6cf26.f828d","type":"ui_text","z":"e589c8e1.a6c6e8","group":"320899a7.844536","order":5,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":510,"y":120,"wires":[]},{"id":"62720a94.0617f4","type":"function","z":"e589c8e1.a6c6e8","name":"json+時分秒","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 = 'Time:(' + h + ':' + m + ':' + s + ')'+ msg.payload ;\nreturn msg;\n","outputs":1,"noerr":0,"x":330,"y":120,"wires":[["6eb6cf26.f828d"]]},{"id":"17d785ed.5ff65a","type":"inject","z":"e589c8e1.a6c6e8","name":"","topic":"","payload":"{\"RFIDNo\":\"RaserryPI\"}","payloadType":"json","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":440,"wires":[["74154e1b.94744"]]},{"id":"c10a901d.fd08e","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Alex9ufo","group":"320899a7.844536","order":2,"width":0,"height":0,"passthru":false,"label":"insert1","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{\"RFIDNo\":\"Alex9ufo\"}","payloadType":"json","topic":"","x":120,"y":480,"wires":[["74154e1b.94744"]]},{"id":"10169ee9.450341","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Select","group":"320899a7.844536","order":9,"width":0,"height":0,"passthru":false,"label":"select","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM RANDOMNUM ORDER BY id DESC LIMIT 100;","x":130,"y":660,"wires":[["f1cdb8b0.ff1078"]]},{"id":"8304c0d6.ffefa","type":"ui_button","z":"e589c8e1.a6c6e8","name":"RaserryPI","group":"320899a7.844536","order":3,"width":0,"height":0,"passthru":false,"label":"insert2","tooltip":"","color":"","bgcolor":"","icon":"","payload":"{\"RFIDNo\":\"RaserryPI\"}","payloadType":"json","topic":"","x":120,"y":520,"wires":[["74154e1b.94744"]]},{"id":"15c90a8c.4be805","type":"ui_button","z":"e589c8e1.a6c6e8","name":"Alex9ufo","group":"320899a7.844536","order":2,"width":0,"height":0,"passthru":false,"label":"insert3","tooltip":"","color":"","bgcolor":"","icon":"","payload":"Alex_test","payloadType":"str","topic":"","x":300,"y":260,"wires":[["5e024b52.b7b894","6eb6cf26.f828d"]]},{"id":"320899a7.844536","type":"ui_group","z":"","name":"SQLITE4","tab":"ef1e941f.54b368","order":1,"disp":true,"width":"6","collapse":false},{"id":"d2b794d0.abbc68","type":"sqlitedb","z":"","db":"db_004.db"},{"id":"e4d9b72d.d14398","type":"mqtt-broker","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":15,"cleansession":true,"birthQos":"0","willQos":"0"},{"id":"ef1e941f.54b368","type":"ui_tab","z":"","name":"SQLite","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

2019年12月2日星期一

Node-RED導入SQLite提交SQL Server

新建table

CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)

1) TIMESTAMP INT PRIMARY KEY NOT NULL,
2) FIRSTNAME TEXT NOT NULL,
3) LASTNAME TEXT NOT NULL,
4) COUNTRY TEXT NOT NULL)

write query function
var d = new Date();
var timestamp = d.getTime();

var newMsg = {
"payload":{
"time":timestamp,
"firstname": msg.payload.firstname,
"lastname": msg.payload.lastname,
"country" : msg.payload.country
},
"topic": "INSERT INTO MEMBER VALUES ( " + timestamp + ", '" + msg.payload.firstname + "', '" + msg.payload.lastname + "', '" + msg.payload.country + "')"
}
return newMsg;

msg function

msg.topic = "SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;";
return msg;

Template HTML樣板

<table style="width:100%">
<tr>
<th>Index</th>
<th>Timestamp</th>
<th>Firstname</th>
<th>Lastname</th>
<th>Country</th>
</tr>
<tr ng-repeat="x in msg.payload | limitTo:20">
<td>{{$index}}</td>
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>{{msg.payload[$index].FIRSTNAME}}</td>
<td>{{msg.payload[$index].LASTNAME}}</td>
<td>{{msg.payload[$index].COUNTRY}}</td>
</tr>
</table>

View records
SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;

Flow JSON Sourcecode

[{"id":"cf55a763.b5ff38","type":"ui_form","z":"fea04978.fe55d8","name":"","label":"","group":"31745d9a.412202","order":0,"width":0,"height":0,"options":[{"label":"First Name","value":"firstname","type":"text","required":true},{"label":"Last Name","value":"lastname","type":"text","required":true},{"label":"Country","value":"country","type":"text","required":true}],"formValue":{"firstname":"","lastname":"","country":""},"payload":"","topic":"","x":110,"y":80,"wires":[["3f5f4dda.fc2022","24308878.5823e8"]]},{"id":"3f5f4dda.fc2022","type":"http request","z":"fea04978.fe55d8","name":"","method":"POST","ret":"txt","url":"localhost:1880/mysitepost","tls":"","x":310,"y":80,"wires":[[]]},{"id":"f71bc20e.2b85b","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":520,"y":140,"wires":[["e5d3a3d1.44fea","ebe341d5.18eec"]]},{"id":"fa5d0e64.396e4","type":"inject","z":"fea04978.fe55d8","name":"create database table","topic":"CREATE TABLE MEMBER( TIMESTAMP INT PRIMARY KEY NOT NULL, FIRSTNAME TEXT NOT NULL, LASTNAME TEXT NOT NULL, COUNTRY TEXT NOT NULL)","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":260,"y":200,"wires":[["f71bc20e.2b85b"]]},{"id":"e5d3a3d1.44fea","type":"debug","z":"fea04978.fe55d8","name":"","active":true,"console":"false","complete":"false","x":670,"y":140,"wires":[]},{"id":"24308878.5823e8","type":"function","z":"fea04978.fe55d8","name":"write query","func":"var d = new Date();\nvar timestamp = d.getTime();\n\nvar newMsg = {\n \"payload\":{\n \"time\":timestamp,\n \"firstname\": msg.payload.firstname,\n \"lastname\": msg.payload.lastname,\n \"country\" : msg.payload.country\n },\n \"topic\": \"INSERT INTO MEMBER VALUES ( \" + timestamp + \", '\" + msg.payload.firstname + \"', '\" + msg.payload.lastname + \"', '\" + msg.payload.country + \"')\"\n}\nreturn newMsg;","outputs":1,"noerr":0,"x":310,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"bb4ecef5.9fbc8","type":"inject","z":"fea04978.fe55d8","name":"View records","topic":"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"x":270,"y":260,"wires":[["f71bc20e.2b85b"]]},{"id":"4c2d7ba7.5ab7e4","type":"inject","z":"fea04978.fe55d8","name":"delete","topic":"DELETE FROM MEMBER WHERE TIMESTAMP <= strftime('%s','now', '-1 hours')*1000","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"x":120,"y":140,"wires":[["f71bc20e.2b85b"]]},{"id":"98c12adf.ad0728","type":"ui_template","z":"fea04978.fe55d8","group":"63800e4a.d5e23","name":"","order":0,"width":"10","height":"10","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Firstname</th> \n <th>Lastname</th>\n <th>Country</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].FIRSTNAME}}</td> \n <td>{{msg.payload[$index].LASTNAME}}</td>\n <td>{{msg.payload[$index].COUNTRY}}</td>\n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":940,"y":180,"wires":[[]]},{"id":"99a0029a.fed36","type":"sqlite","z":"fea04978.fe55d8","mydb":"77e8c1a4.6e881","sqlquery":"msg.topic","sql":"","name":"","x":780,"y":180,"wires":[["98c12adf.ad0728"]]},{"id":"ebe341d5.18eec","type":"function","z":"fea04978.fe55d8","name":"msg","func":"msg.topic = \"SELECT * FROM MEMBER ORDER BY TIMESTAMP DESC LIMIT 100;\";\nreturn msg;","outputs":1,"noerr":0,"x":650,"y":180,"wires":[["99a0029a.fed36"]]},{"id":"31745d9a.412202","type":"ui_group","z":"","name":"Default","tab":"1d173c13.e2dc64","disp":true,"width":"6"},{"id":"77e8c1a4.6e881","type":"sqlitedb","z":"","db":"sqlite.db","mode":"RWC"},{"id":"63800e4a.d5e23","type":"ui_group","z":"","name":"Database","tab":"1d173c13.e2dc64","disp":true,"width":"10"},{"id":"1d173c13.e2dc64","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]

使用nodered SQLite（存儲在database數據庫中）

使用nodered SQLite（存儲在database數據庫中）
使用database前必須在編輯窗口中
Create Database創建數據庫。
1)單擊創建數據庫-> 2)插入（多次單擊）-> 3)選擇

您可以看到保存為dashboard儀表板的值。

1) 新建database table
CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)

2) 插入 table
var randomNum = Math.round(Math.random()*100);
var largeBool = (randomNum > 50)?1:0;
var newMsg = {
"topic": "INSERT INTO RANDOMNUM VALUES ( " + msg.payload + ", " + randomNum + ", " + largeBool + ")"
}
return newMsg;

3) 刪除30秒內的資料
DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000

4)刪除24小時內的資料
DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000

5)刪除7日內的資料
DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000

6)更新資料
UPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL = 0

7)計數資料
SELECT COUNT(*) FROM RANDOMNUM

7)選擇資料
SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;

8) Dash Board template的Template 板模設定20筆資料

<table style="width:100%">
<tr>
<th>Index</th>
<th>Timestamp</th>
<th>Value</th>
<th>Bool</th>
</tr>
<tr ng-repeat="x in msg.payload | limitTo:20">
<td>{{$index}}</td>
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>{{msg.payload[$index].VALUE}}</td>
<td>{{msg.payload[$index].BOOL}}</td>
</tr>
</table>

[{"id":"e20757bf.7a8bd8","type":"comment","z":"e589c8e1.a6c6e8","name":"sqlite.org","info":"https://fred.sensetecnic.com/\nhttp://noderedguide.com/tutorial-sqlite-and-node-red/\nhttps://www.sqlite.org/lang.html\nhttps://www.sqlite.org/datatype3.html#expraff\n\nCREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)\n\n//INSERT\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;\n\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000\nDELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000\n\nSELECT COUNT(*) FROM RANDOMNUM\n\nUPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0\n\nSELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;\n<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>\n\n","x":120,"y":60,"wires":[]},{"id":"3d894a61.e39596","type":"inject","z":"e589c8e1.a6c6e8","name":"CREATE DB","topic":"CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":120,"wires":[["5555653.e543f9c"]]},{"id":"c36fd839.ffe478","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":120,"wires":[]},{"id":"34673595.cf49da","type":"inject","z":"e589c8e1.a6c6e8","name":"INSERT","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":180,"wires":[["5e024b52.b7b894"]]},{"id":"5e024b52.b7b894","type":"function","z":"e589c8e1.a6c6e8","name":"INSERT","func":"var randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\nvar newMsg = {\n \"topic\": \"INSERT INTO RANDOMNUM VALUES ( \" + msg.payload + \", \" + randomNum + \", \" + largeBool + \")\"\n}\nreturn newMsg;","outputs":1,"noerr":0,"x":320,"y":180,"wires":[["e896b4d1.a91068"]]},{"id":"94d53ffd.cc12f","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":180,"wires":[]},{"id":"4892e705.435ad8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 30s","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":240,"wires":[["1b74bd9.e807b42"]]},{"id":"b7961ad7.ebc218","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 24h","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":280,"wires":[["1b74bd9.e807b42"]]},{"id":"80c34500.0ec9b8","type":"inject","z":"e589c8e1.a6c6e8","name":"DELETE 7d","topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":170,"y":320,"wires":[["1b74bd9.e807b42"]]},{"id":"b0726f0e.9de2e","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":240,"wires":[]},{"id":"104eab65.e9ef35","type":"inject","z":"e589c8e1.a6c6e8","name":"UPDATE","topic":"UPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL = 0","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":380,"wires":[["a802c882.9e8368"]]},{"id":"9344f59.05f8e08","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":380,"wires":[]},{"id":"e11209f0.f0b0d8","type":"inject","z":"e589c8e1.a6c6e8","name":"COUNT","topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":150,"y":420,"wires":[["a802c882.9e8368"]]},{"id":"b0d564b2.35d6f8","type":"inject","z":"e589c8e1.a6c6e8","name":"SELECT","topic":"SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":160,"y":480,"wires":[["f1cdb8b0.ff1078"]]},{"id":"a92b7df2.2f457","type":"ui_template","z":"e589c8e1.a6c6e8","group":"320899a7.844536","name":"","order":0,"width":"6","height":"7","format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":620,"y":480,"wires":[[]]},{"id":"850b713a.65bde","type":"debug","z":"e589c8e1.a6c6e8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":610,"y":520,"wires":[]},{"id":"5555653.e543f9c","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":460,"y":120,"wires":[["c36fd839.ffe478"]]},{"id":"e896b4d1.a91068","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":460,"y":180,"wires":[["94d53ffd.cc12f"]]},{"id":"1b74bd9.e807b42","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":460,"y":240,"wires":[["b0726f0e.9de2e"]]},{"id":"a802c882.9e8368","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":460,"y":380,"wires":[["9344f59.05f8e08"]]},{"id":"f1cdb8b0.ff1078","type":"sqlite","z":"e589c8e1.a6c6e8","mydb":"d2b794d0.abbc68","sqlquery":"msg.topic","sql":"","name":"db_004","x":460,"y":480,"wires":[["a92b7df2.2f457","850b713a.65bde"]]},{"id":"320899a7.844536","type":"ui_group","name":"Group 1","tab":"ef1e941f.54b368","order":1,"disp":true,"width":6},{"id":"d2b794d0.abbc68","type":"sqlitedb","z":"","db":"db_004"},{"id":"ef1e941f.54b368","type":"ui_tab","z":"","name":"Main Tab","icon":"dashboard","order":1}]

Node-RED 控制Dashboard UI 介面驅動MQTT Server 與 Line Notify

說明下載 http://www.mediafire.com/file/8axa2qq7bgekyzp/Node-red-MQTT.pdf/file

[{"id":"9a9808d2.b904e8","type":"mqtt in","z":"7377d363.84128c","name":"","topic":"alex9ufo/led/led_event","qos":"1","datatype":"auto","broker":"841df58d.ee5e98","x":140,"y":100,"wires":[["ff64ce35.d54d6","cc43c13c.49236","bd66ced7.e745b"]]},{"id":"ff64ce35.d54d6","type":"ui_text","z":"7377d363.84128c","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"name":"","label":"MQTT Suscribe Data","format":"{{msg.payload}}","layout":"col-center","x":400,"y":100,"wires":[]},{"id":"e10b330a.0e832","type":"ui_button","z":"7377d363.84128c","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"On led","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"Light-emitting diode on","payloadType":"str","topic":"","x":110,"y":300,"wires":[["a4db0e32.936f8","6d69def.f1acb2"]]},{"id":"5ccc4685.ea9d18","type":"ui_button","z":"7377d363.84128c","name":"","group":"6c9116b.b62d4e8","order":0,"width":0,"height":0,"passthru":false,"label":"Off led","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"Light-emitting diode off","payloadType":"str","topic":"","x":110,"y":360,"wires":[["a4db0e32.936f8","6d69def.f1acb2"]]},{"id":"a4db0e32.936f8","type":"mqtt out","z":"7377d363.84128c","name":"","topic":"alex9ufo/led/led_event","qos":"1","retain":"false","broker":"841df58d.ee5e98","x":360,"y":300,"wires":[]},{"id":"6d69def.f1acb2","type":"ui_audio","z":"7377d363.84128c","name":"","group":"6c9116b.b62d4e8","voice":"en-US","always":"","x":320,"y":360,"wires":[]},{"id":"cc43c13c.49236","type":"function","z":"7377d363.84128c","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":270,"y":160,"wires":[["d3901e4c.e507d"]]},{"id":"d3901e4c.e507d","type":"function","z":"7377d363.84128c","name":"Set Line API ","func":"msg.headers = {'content-type':'application/x-www-form-urlencoded','Authorization':'Bearer oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu'};\nmsg.payload = {\"message\":msg.payload};\nreturn msg;\n\n//oR7KdXvK1eobRr2sRRgsl4PMq23DjDlhfUs96SyUBZu","outputs":1,"noerr":0,"x":370,"y":240,"wires":[["2d2f8066.cb721"]]},{"id":"2d2f8066.cb721","type":"http request","z":"7377d363.84128c","name":"","method":"POST","ret":"txt","url":"https://notify-api.line.me/api/notify","tls":"","x":490,"y":160,"wires":[["6085953d.1462ec"]]},{"id":"6085953d.1462ec","type":"debug","z":"7377d363.84128c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":240,"wires":[]},{"id":"bd66ced7.e745b","type":"debug","z":"7377d363.84128c","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":370,"y":60,"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":"LED","tab":"28920cc.c23e2f4","order":1,"disp":true,"width":"6","collapse":false},{"id":"28920cc.c23e2f4","type":"ui_tab","z":"","name":"MQTT_LED","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

訂閱：文章 (Atom)

2019年12月8日 星期日