使用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}]