This flow is SQLite Node example.
Youtube link
https://youtu.be/4YfnjOcnq5s
Last active
May 24, 2020 08:52
-
-
Save leedrch/ec03f2483384351bae669da6bcfe40b0 to your computer and use it in GitHub Desktop.
Flow example - SQLite Node
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| [{"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}] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Video in the readme link is no longer available.