Node-Red : LiteDB資料庫
參考來源https://flows.nodered.org/flow/ec03f2483384351bae669da6bcfe40b0
[{"id":"eefd2e0d.1afec","type":"comment","z":"39ec815688599ede","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":40,"wires":[]},{"id":"ce813a38.486e28","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":360,"y":100,"wires":[["4f3f3858.c6ea58"]]},{"id":"adb5271b.e49118","type":"inject","z":"39ec815688599ede","name":"CREATE DB","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)","payload":"","payloadType":"date","x":150,"y":100,"wires":[["ce813a38.486e28"]]},{"id":"4f3f3858.c6ea58","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":100,"wires":[]},{"id":"36c37ba9.5b67f4","type":"inject","z":"39ec815688599ede","name":"INSERT","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":160,"wires":[["9818d1c4.3144b"]]},{"id":"9818d1c4.3144b","type":"function","z":"39ec815688599ede","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":160,"wires":[["fba3bbd7.c848f8"]]},{"id":"885a944d.7a7838","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":650,"y":160,"wires":[]},{"id":"fba3bbd7.c848f8","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":480,"y":160,"wires":[["885a944d.7a7838"]]},{"id":"6d47683e.0fb558","type":"inject","z":"39ec815688599ede","name":"DELETE 30s","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-30 seconds')*1000","payload":"","payloadType":"date","x":150,"y":220,"wires":[["3f405d54.0a4b42"]]},{"id":"3f405d54.0a4b42","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":400,"y":220,"wires":[["4f233c28.ad61a4"]]},{"id":"21f6b4e8.c15a4c","type":"inject","z":"39ec815688599ede","name":"DELETE 24h","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-24 hours')*1000","payload":"","payloadType":"date","x":150,"y":260,"wires":[["3f405d54.0a4b42"]]},{"id":"7d2be4c7.bcd85c","type":"inject","z":"39ec815688599ede","name":"DELETE 7d","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('%s','now', '-7 days')*1000","payload":"","payloadType":"date","x":150,"y":300,"wires":[["3f405d54.0a4b42"]]},{"id":"4f233c28.ad61a4","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":220,"wires":[]},{"id":"cd31dbfe.a50238","type":"inject","z":"39ec815688599ede","name":"UPDATE","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"UPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL = 0","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"UPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL = 0","payload":"","payloadType":"date","x":140,"y":360,"wires":[["1edfd08.fcc9c3"]]},{"id":"1edfd08.fcc9c3","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":400,"y":360,"wires":[["5b075b30.5b3014"]]},{"id":"5b075b30.5b3014","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":360,"wires":[]},{"id":"1a6eb281.ee04dd","type":"inject","z":"39ec815688599ede","name":"COUNT","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"SELECT COUNT(*) FROM RANDOMNUM","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","x":130,"y":400,"wires":[["1edfd08.fcc9c3"]]},{"id":"4667ba0e.d67e64","type":"inject","z":"39ec815688599ede","name":"SELECT","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","x":140,"y":460,"wires":[["9e2dbd84.ab087"]]},{"id":"9e2dbd84.ab087","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":400,"y":460,"wires":[["df51560e.5729a8","95eec7a9.8e2a88"]]},{"id":"df51560e.5729a8","type":"ui_template","z":"39ec815688599ede","group":"d40ead3e.334ab","name":"","order":0,"width":"12","height":"10","format":"<table style=\"width:100%\">\n <tr>\n \n <th>Index</th> \n <th>---</th>\n <th>Timestamp</th>\n <th>---</th> \n <th>Value</th> \n <th>---</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>}{</td> \n <td>{{msg.payload[$index].VALUE}}</td> \n <td>}{</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n <td>}</td> \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","className":"","x":620,"y":460,"wires":[[]]},{"id":"95eec7a9.8e2a88","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":500,"wires":[]},{"id":"97158aa1.026308","type":"sqlitedb","db":"D:\\Node-RED\\litedb\\db_001.db","mode":"RWC"},{"id":"d40ead3e.334ab","type":"ui_group","name":"Group 1","tab":"3c770aff.9ba2a6","order":1,"disp":true,"width":"12","collapse":false,"className":""},{"id":"3c770aff.9ba2a6","type":"ui_tab","name":"Main Tab","icon":"dashboard","order":1}]
1) 新增一資料庫
|
功能
|
程式暨說明
|
備註
|
|
CREATE DB 產生新資料庫 D:\Node-RED\litedb\db_001.db
|
|
Inject
(CREATE DB)
|
產生資料庫
msg.topic內容
資料表 (Table) 名稱 RANDOMNUM
資料欄位TIMESTAMP, VALUE , BOOL 三項
主索引欄
TIMESTAMP
CREATE TABLE
RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT
NOT NULL)
|
|
|
Litedb
(db_001.db)
|
</> SQL Query
Via msg.topic
|
|
|
[{"id":"adb5271b.e49118","type":"inject","z":"39ec815688599ede","name":"CREATE
DB","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"CREATE
TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL
INT NOT
NULL)","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE
TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL
INT NOT
NULL)","payload":"","payloadType":"date","x":150,"y":100,"wires":[["ce813a38.486e28"]]},{"id":"ce813a38.486e28","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":360,"y":100,"wires":[["4f3f3858.c6ea58"]]},{"id":"4f3f3858.c6ea58","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":100,"wires":[]},{"id":"97158aa1.026308","type":"sqlitedb","db":"D:\\Node-RED\\litedb\\db_001.db","mode":"RWC"}]
|
2) 插入一筆資料
|
功能
|
程式暨說明
|
備註
|
|
插入一筆資料
|
|
Inject (INSERT)
|
msg.payload à 資料欄位TIMESTAMP
|
|
|
functiomn (INSERT)
|
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;
資料欄位TIMESTAMP, VALUE
, BOOL 三項
TIMESTAMP à msg.payload
VALUE à randomNum
BOOL à largeBool
|
|
|
Litedb (db_001.db)
|
</> SQL Query
Via msg.topic
|
|
|
[{"id":"36c37ba9.5b67f4","type":"inject","z":"39ec815688599ede","name":"INSERT","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":160,"wires":[["9818d1c4.3144b"]]},{"id":"9818d1c4.3144b","type":"function","z":"39ec815688599ede","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":160,"wires":[["fba3bbd7.c848f8"]]},{"id":"fba3bbd7.c848f8","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":480,"y":160,"wires":[["885a944d.7a7838"]]},{"id":"885a944d.7a7838","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":650,"y":160,"wires":[]},{"id":"97158aa1.026308","type":"sqlitedb","db":"D:\\Node-RED\\litedb\\db_001.db","mode":"RWC"}]
|
3) 刪除資料
|
功能
|
程式暨說明
|
備註
|
|
刪除資料
|
|
Inject (DELETE 30s)
|
刪除30秒內的資料
msg.topic設定
DELETE FROM RANDOMNUM WHERE TIMESTAMP <=
strftime('%s','now', '-30 seconds')*1000
|
|
|
Inject (DELETE 24H)
|
刪除24小時內的資料
msg.topic設定
DELETE FROM RANDOMNUM WHERE TIMESTAMP <=
strftime('%s','now', '-24 hours')*1000
|
|
|
Inject (DELETE 7d)
|
刪除7 天內的資料
Msg.topic設定
DELETE FROM RANDOMNUM WHERE TIMESTAMP <=
strftime('%s','now', '-7 days')*1000
|
|
|
Litedb (db_001.db)
|
</> SQL Query
Via msg.topic
|
|
4) 更新資料庫暨計算資料筆數
|
功能
|
程式暨說明
|
備註
|
|
更新資料庫暨計算資料筆數
|
|
|
|
Inject (UPDATE)
|
Msg.topic設定
UPDATE RANDOMNUM SET BOOL = 1 WHERE VALUE
> 20 AND BOOL = 0
|
|
|
Inject (COUNT)
|
Msg.topic設定
SELECT COUNT(*) FROM RANDOMNUM
|
|
|
Litedb (db_001.db)
|
</> SQL Query
Via msg.topic
|
|
|
[{"id":"cd31dbfe.a50238","type":"inject","z":"39ec815688599ede","name":"UPDATE","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"UPDATE
RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL =
0","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"UPDATE
RANDOMNUM SET BOOL = 1 WHERE VALUE > 20 AND BOOL =
0","payload":"","payloadType":"date","x":140,"y":360,"wires":[["1edfd08.fcc9c3"]]},{"id":"1a6eb281.ee04dd","type":"inject","z":"39ec815688599ede","name":"COUNT","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"SELECT
COUNT(*) FROM RANDOMNUM","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT
COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","x":130,"y":400,"wires":[["1edfd08.fcc9c3"]]},{"id":"5b075b30.5b3014","type":"debug","z":"39ec815688599ede","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":630,"y":360,"wires":[]},{"id":"1edfd08.fcc9c3","type":"sqlite","z":"39ec815688599ede","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_001","x":400,"y":360,"wires":[["5b075b30.5b3014"]]},{"id":"97158aa1.026308","type":"sqlitedb","db":"D:\\Node-RED\\litedb\\db_001.db","mode":"RWC"}]
|
5) 顯示資料庫內容
|
功能
|
程式暨說明
|
備註
|
|
顯示資料庫內容
|
|
|
|
Inject (SELECT)
|
Msg.topic設定
SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;
|
|
|
Litedb (db_001.db)
|
</> SQL Query
Via msg.topic
|
|
|
</> template
|
<table style="width:100%">
<tr>
<th>Index</th>
<th>---</th>
<th>Timestamp</th>
<th>---</th>
<th>Value</th>
<th>---</th>
<th>Bool</th>
</tr>
<tr
ng-repeat="x in msg.payload | limitTo:20">
<td>{{$index}}</td>
<td>{</td>
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>}{</td>
<td>{{msg.payload[$index].VALUE}}</td>
<td>}{</td>
<td>{{msg.payload[$index].BOOL}}</td>
<td>}</td>
</tr>
</table>
|
|
沒有留言:
張貼留言