This flow is SQLite Node example.
Youtube link
https://youtu.be/4YfnjOcnq5s
This flow is SQLite Node example.
Youtube link
https://youtu.be/4YfnjOcnq5s
[{"id":"eefd2e0d.1afec","type":"comment","z":"5b33d6eb.97a4a8","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":200,"y":680,"wires":[]},{"id":"ce813a38.486e28","type":"sqlite","z":"5b33d6eb.97a4a8","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_002","x":440,"y":740,"wires":[["4f3f3858.c6ea58"]]},{"id":"adb5271b.e49118","type":"inject","z":"5b33d6eb.97a4a8","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":230,"y":740,"wires":[["ce813a38.486e28"]]},{"id":"4f3f3858.c6ea58","type":"debug","z":"5b33d6eb.97a4a8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":670,"y":740,"wires":[]},{"id":"36c37ba9.5b67f4","type":"inject","z":"5b33d6eb.97a4a8","name":"INSERT","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":800,"wires":[["9818d1c4.3144b"]]},{"id":"9818d1c4.3144b","type":"function","z":"5b33d6eb.97a4a8","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":400,"y":800,"wires":[["fba3bbd7.c848f8"]]},{"id":"885a944d.7a7838","type":"debug","z":"5b33d6eb.97a4a8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":730,"y":800,"wires":[]},{"id":"fba3bbd7.c848f8","type":"sqlite","z":"5b33d6eb.97a4a8","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_002","x":560,"y":800,"wires":[["885a944d.7a7838"]]},{"id":"6d47683e.0fb558","type":"inject","z":"5b33d6eb.97a4a8","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":230,"y":860,"wires":[["3f405d54.0a4b42"]]},{"id":"3f405d54.0a4b42","type":"sqlite","z":"5b33d6eb.97a4a8","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_002","x":480,"y":860,"wires":[["4f233c28.ad61a4"]]},{"id":"21f6b4e8.c15a4c","type":"inject","z":"5b33d6eb.97a4a8","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":230,"y":900,"wires":[["3f405d54.0a4b42"]]},{"id":"7d2be4c7.bcd85c","type":"inject","z":"5b33d6eb.97a4a8","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":230,"y":940,"wires":[["3f405d54.0a4b42"]]},{"id":"4f233c28.ad61a4","type":"debug","z":"5b33d6eb.97a4a8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":860,"wires":[]},{"id":"cd31dbfe.a50238","type":"inject","z":"5b33d6eb.97a4a8","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":220,"y":1000,"wires":[["1edfd08.fcc9c3"]]},{"id":"1edfd08.fcc9c3","type":"sqlite","z":"5b33d6eb.97a4a8","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_002","x":480,"y":1000,"wires":[["5b075b30.5b3014"]]},{"id":"5b075b30.5b3014","type":"debug","z":"5b33d6eb.97a4a8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":1000,"wires":[]},{"id":"1a6eb281.ee04dd","type":"inject","z":"5b33d6eb.97a4a8","name":"COUNT","topic":"SELECT COUNT(*) FROM RANDOMNUM","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":210,"y":1040,"wires":[["1edfd08.fcc9c3"]]},{"id":"4667ba0e.d67e64","type":"inject","z":"5b33d6eb.97a4a8","name":"SELECT","topic":"SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100;","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":220,"y":1100,"wires":[["9e2dbd84.ab087"]]},{"id":"9e2dbd84.ab087","type":"sqlite","z":"5b33d6eb.97a4a8","mydb":"97158aa1.026308","sqlquery":"msg.topic","sql":"","name":"db_002","x":480,"y":1100,"wires":[["df51560e.5729a8","95eec7a9.8e2a88"]]},{"id":"df51560e.5729a8","type":"ui_template","z":"5b33d6eb.97a4a8","group":"d40ead3e.334ab","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":700,"y":1100,"wires":[[]]},{"id":"95eec7a9.8e2a88","type":"debug","z":"5b33d6eb.97a4a8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":1140,"wires":[]},{"id":"97158aa1.026308","type":"sqlitedb","z":"","db":"db_002"},{"id":"d40ead3e.334ab","type":"ui_group","name":"Group 1","tab":"3c770aff.9ba2a6","order":1,"disp":true,"width":6},{"id":"3c770aff.9ba2a6","type":"ui_tab","z":"","name":"Main Tab","icon":"dashboard","order":1}] |
Video in the readme link is no longer available.