Node-Red & SQLite

 Node-Red & SQLite

[{"id":"1707fa8f0973393a","type":"ui_template","z":"bbd4d284a5440a6b","group":"d44d4f44.3219e8","name":"UI Table JSON","order":1,"width":"","height":"","format":"<style>\n.table\n{\n    height:400px;\n    width:700px;\n    background:lightblue;\n}\n</style>\n<div class=\"table\">\n<table style=\"width:100%\">\n  <tr>\n    <th>TIMESTAMP</th> \n    <th>SENSOR</th>\n    <th>RAW DATA (JSON)</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:10\">\n    <td>{{msg.payload[$index].TIMESTAMP}}</td>\n    <td>{{msg.payload[$index].SENSOR}}</td>\n    <td>{{msg.payload[$index].RAW_DATA}}</td>\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":700,"y":60,"wires":[[]]},{"id":"4ec7e5693f057524","type":"inject","z":"bbd4d284a5440a6b","name":"Test Data","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":280,"wires":[["e083570ada620338"]]},{"id":"e083570ada620338","type":"function","z":"bbd4d284a5440a6b","name":"test data","func":"var count=context.get('count');\nvar timestamp=context.get('timestamp');\n\nif (count==undefined)\n{\n    count=1;\n    timestamp=1;\n}\nvar data1={\"sensor\":\"sensor1\",\"temperature\":21,\"humidity\":61};\nvar data2={\"sensor\":\"sensor2\",\"temperature\":22,\"humidity\":62};\nvar data3={\"sensor\":\"sensor3\",\"temperature\":23,\"humidity\":63};\nif (count%20==0)\n    count=-count;\ndata1.temperature+=(count*.25);\n//data2.temperature+=(count*.5);\n//data3.temperature+=(count*.75);\ndata1.humidity+=(count*.25);\n//data2.humidity+=(count*.5);\n//data3.humidity+=(count*.75);\nmsg.payload=[data1];\nvar msg1={};\nvar msg2={};\nvar msg3={};\n\ndata1.timestamp=timestamp;\ntimestamp+=1;\ndata2.timestamp=timestamp;\ntimestamp+=1;\ndata3.timestamp=timestamp;\n\nmsg1.payload=JSON.stringify(data1);\nmsg2.payload=JSON.stringify(data2);\nmsg3.payload=JSON.stringify(data3);\ntimestamp+=1;\ncount+=1;\ncontext.set('count',count);\ncontext.set('timestamp',timestamp);\nreturn [[msg1,msg2,msg3]];","outputs":1,"noerr":0,"x":340,"y":280,"wires":[["ee4dabd53e20d016","dd7bfb54fe166e93"]]},{"id":"dd7bfb54fe166e93","type":"function","z":"bbd4d284a5440a6b","name":"write query","func":"var raw_data=msg.payload;\nvar temp=JSON.parse(msg.payload);\nvar timestamp=temp.timestamp;\nvar sensor=temp.sensor;\nmsg=\"(timestamp,sensor,raw_data) values(\"+timestamp +\",\\'\"+sensor +\"\\'\"+ \",\\'\"+raw_data+\"\\'\"  +\")\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\nmsg1.payload=timestamp;\nreturn msg1;","outputs":1,"noerr":0,"x":490,"y":280,"wires":[["48f120389cbaf6ca","504c1d52fb2428d9"]]},{"id":"48f120389cbaf6ca","type":"debug","z":"bbd4d284a5440a6b","name":"sql out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":630,"y":340,"wires":[]},{"id":"ee4dabd53e20d016","type":"debug","z":"bbd4d284a5440a6b","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":450,"y":360,"wires":[]},{"id":"504c1d52fb2428d9","type":"delay","z":"bbd4d284a5440a6b","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":640,"y":280,"wires":[["55bf70b8486fa2a0"]]},{"id":"55bf70b8486fa2a0","type":"sqlite","z":"bbd4d284a5440a6b","mydb":"f5a33dc2.f6f08","sqlquery":"msg.topic","sql":"","name":"Test0.db","x":720,"y":200,"wires":[[]]},{"id":"c4888864a83658f3","type":"inject","z":"bbd4d284a5440a6b","name":"show RECORDS","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"1","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","payloadType":"date","x":230,"y":60,"wires":[["e8aaa414c2e71503"]]},{"id":"e8aaa414c2e71503","type":"sqlite","z":"bbd4d284a5440a6b","mydb":"f5a33dc2.f6f08","sqlquery":"msg.topic","sql":"","name":"Test0.db","x":460,"y":60,"wires":[["1707fa8f0973393a"]]},{"id":"70ee452ad2aff839","type":"inject","z":"bbd4d284a5440a6b","name":"create database table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL,RAW_DATA TEXT NOT NULL)","payload":"","payloadType":"date","x":240,"y":140,"wires":[["55bf70b8486fa2a0"]]},{"id":"2eef220a7ded9603","type":"inject","z":"bbd4d284a5440a6b","name":"delete records","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":210,"y":180,"wires":[["55bf70b8486fa2a0"]]},{"id":"d145d20bb67723e6","type":"inject","z":"bbd4d284a5440a6b","name":"Drop Table","repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":200,"y":220,"wires":[["55bf70b8486fa2a0"]]},{"id":"d44d4f44.3219e8","type":"ui_group","name":"Sensors","tab":"34054411.bf9404","order":1,"disp":true,"width":"12","collapse":false},{"id":"f5a33dc2.f6f08","type":"sqlitedb","db":"D:\\SQlite\\test0.db","mode":"RWC"},{"id":"34054411.bf9404","type":"ui_tab","name":"Home","icon":"dashboard"}]