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

使用nodered SQLite（存儲在數據庫中）
複製和使用時，必須在編輯窗口中創建數據庫。
單擊創建數據庫->插入（多次單擊）->選擇
您可以看到保存為儀表板模板節點的值。

[{"id":"9d6d4a84.d1aff8","type":"comment","z":"8484646.833e198","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":100,"y":80,"wires":[]},{"id":"f2e4a07e.d74cd","type":"inject","z":"8484646.833e198","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":150,"y":140,"wires":[["aec9239d.cc376"]]},{"id":"bbe65fa9.231ef","type":"debug","z":"8484646.833e198","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":140,"wires":[]},{"id":"340954fa.e315ac","type":"inject","z":"8484646.833e198","name":"INSERT","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":200,"wires":[["d268106.8674cf"]]},{"id":"d268106.8674cf","type":"function","z":"8484646.833e198","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":300,"y":200,"wires":[["3d041ff8.99772"]]},{"id":"eee4c1af.a72cc","type":"debug","z":"8484646.833e198","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":200,"wires":[]},{"id":"e1493005.34b9b","type":"inject","z":"8484646.833e198","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":260,"wires":[["34a62b4f.a21764"]]},{"id":"70414900.693c28","type":"inject","z":"8484646.833e198","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":300,"wires":[["34a62b4f.a21764"]]},{"id":"9054be03.28c8","type":"inject","z":"8484646.833e198","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":340,"wires":[["34a62b4f.a21764"]]},{"id":"72e6d526.73c10c","type":"debug","z":"8484646.833e198","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":260,"wires":[]},{"id":"17e945eb.9ed5ca","type":"inject","z":"8484646.833e198","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":140,"y":400,"wires":[["9d5e0f2a.5b208"]]},{"id":"8d7215d1.6dfd18","type":"debug","z":"8484646.833e198","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":400,"wires":[]},{"id":"f4ca62dc.ae34b","type":"inject","z":"8484646.833e198","name":"COUNT","topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":130,"y":440,"wires":[["9d5e0f2a.5b208"]]},{"id":"29558e2e.545582","type":"inject","z":"8484646.833e198","name":"SELECT","topic":"SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":140,"y":500,"wires":[["4dc03fc9.22dea"]]},{"id":"852f16e.709a3e8","type":"ui_template","z":"8484646.833e198","group":"8fb8647f.605e88","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":600,"y":500,"wires":[[]]},{"id":"68266292.d9f43c","type":"debug","z":"8484646.833e198","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":590,"y":540,"wires":[]},{"id":"aec9239d.cc376","type":"sqlite","z":"8484646.833e198","mydb":"8fb63f39.b200c","sqlquery":"msg.topic","sql":"","name":"db_003","x":440,"y":140,"wires":[["bbe65fa9.231ef"]]},{"id":"3d041ff8.99772","type":"sqlite","z":"8484646.833e198","mydb":"8fb63f39.b200c","sqlquery":"msg.topic","sql":"","name":"db_003","x":440,"y":200,"wires":[["eee4c1af.a72cc"]]},{"id":"34a62b4f.a21764","type":"sqlite","z":"8484646.833e198","mydb":"8fb63f39.b200c","sqlquery":"msg.topic","sql":"","name":"db_003","x":440,"y":260,"wires":[["72e6d526.73c10c"]]},{"id":"9d5e0f2a.5b208","type":"sqlite","z":"8484646.833e198","mydb":"8fb63f39.b200c","sqlquery":"msg.topic","sql":"","name":"db_003","x":440,"y":400,"wires":[["8d7215d1.6dfd18"]]},{"id":"4dc03fc9.22dea","type":"sqlite","z":"8484646.833e198","mydb":"8fb63f39.b200c","sqlquery":"msg.topic","sql":"","name":"db_003","x":440,"y":500,"wires":[["852f16e.709a3e8","68266292.d9f43c"]]},{"id":"8fb8647f.605e88","type":"ui_group","name":"Group 1","tab":"50ec94a2.74b2ec","order":1,"disp":true,"width":6},{"id":"8fb63f39.b200c","type":"sqlitedb","z":"","db":"db_003.db","mode":"RWC"},{"id":"50ec94a2.74b2ec","type":"ui_tab","z":"","name":"Main Tab","icon":"dashboard","order":1}]