Node-Red SQLite Database

 Node-Red  & SQLite Database

源自於http://stevesnoderedguide.com/storing-iot-data-sql-database

inject	Msg.topic	remark
create database table	CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)
delete records	DELETE FROM DATA WHERE TIMESTAMP>=0
Drop Table	drop table data
show RECORDS	SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100
Function name	Function
Insert using variable	var sensor="sensor5"; msg="( timestamp,sensor,temperature,humidity) values(4,"+"\'"+sensor +"\'" + ",20,66)"; var topic="INSERT INTO DATA " +msg; var msg1={}; msg1.topic=topic;   return msg1;
Insert	var newMsg = {  "topic": "INSERT INTO data ( timestamp,sensor,temperature,humidity) values(0,'sensor',20,66)" };   return newMsg;
test data	var count=context.get('count'); var timestamp=context.get('timestamp');   if (count==undefined) {     count=1;     timestamp=1; } var data1={"sensor":"sensor1","temperature":21,"humidity":61}; //var data2={"sensor":"sensor2","temperature":22,"humidity":62}; //var data3={"sensor":"sensor3","temperature":23,"humidity":63}; if (count%20==0)     count=-count; data1.temperature+=(count*.25); //data2.temperature+=(count*.5); //data3.temperature+=(count*.75); data1.humidity+=(count*.25); //data2.humidity+=(count*.5); //data3.humidity+=(count*.75); msg.payload=[data1]; var msg1={}; var msg2={}; var msg3={};   data1.timestamp=timestamp; //timestamp+=1; //data2.timestamp=timestamp; //timestamp+=1; //data3.timestamp=timestamp;   msg1.payload=data1; //msg2.payload=data2; //msg3.payload=data3; timestamp+=1; count+=1; context.set('count',count); context.set('timestamp',timestamp); return msg1;
write query	var timestamp=msg.payload.timestamp; var sensor=msg.payload.sensor; var temperature=msg.payload.temperature; var humidity=msg.payload.humidity; //put data into json string   msg="( timestamp,sensor,temperature,humidity) values("+timestamp +",\'"+sensor +"\'," + temperature + "," + humidity +")"; var topic="INSERT INTO DATA " +msg; var msg1={}; msg1.topic=topic; msg1.payload=timestamp; return msg1;
	var payload=msg.error.message; msg.payload = payload +" timestamp "+msg.payload; return msg;

 

[{"id":"e4921ea0.5ee538","type":"inject","z":"7f84d0fc.49d6f","name":"create database table","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)","payload":"","payloadType":"date","x":160,"y":220,"wires":[["41be04d2.d76fc4"]]},{"id":"df3ec565.41cf","type":"debug","z":"7f84d0fc.49d6f","name":"database_out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":600,"y":280,"wires":[]},{"id":"6c8d4c99.7532ac","type":"inject","z":"7f84d0fc.49d6f","name":"insert data","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":120,"y":160,"wires":[["3ad7c728.b01598"]]},{"id":"c0c0246e.0f5ed","type":"function","z":"7f84d0fc.49d6f","name":"Insert","func":"var newMsg = {\n \"topic\": \"INSERT INTO data ( timestamp,sensor,temperature,humidity) values(0,'sensor',20,66)\"\n};\n\nreturn newMsg;","outputs":1,"noerr":0,"x":310,"y":100,"wires":[["41be04d2.d76fc4","aef24493.aeb0c8"]]},{"id":"8a2750f3.e4eaa","type":"inject","z":"7f84d0fc.49d6f","name":"delete records","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"DELETE FROM DATA WHERE TIMESTAMP>=0","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":130,"y":280,"wires":[["41be04d2.d76fc4"]]},{"id":"c885f7e0.416228","type":"inject","z":"7f84d0fc.49d6f","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":150,"y":40,"wires":[["41186c79.d2be6c"]]},{"id":"d4d337ca.8077b8","type":"ui_template","z":"7f84d0fc.49d6f","group":"d44d4f44.3219e8","name":"UI Table","order":1,"width":"","height":"","format":"<style>\n.table\n{\n    height:400px;\n    width:550px;\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>TEMPERATURE</th> \n    <th>HUMIDITY</th>\n  \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].TEMPERATURE}}</td> \n    <td>{{msg.payload[$index].HUMIDITY}}</td>\n\n  </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":520,"y":40,"wires":[[]]},{"id":"41be04d2.d76fc4","type":"sqlite","z":"7f84d0fc.49d6f","mydb":"f5a33dc2.f6f08","sqlquery":"msg.topic","sql":"","name":"Test.db","x":440,"y":280,"wires":[["df3ec565.41cf"]]},{"id":"d073f7fa.a7d388","type":"inject","z":"7f84d0fc.49d6f","name":"Test Data","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":400,"wires":[["8bc74a92.321318"]]},{"id":"8bc74a92.321318","type":"function","z":"7f84d0fc.49d6f","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};\n//var data2={\"sensor\":\"sensor2\",\"temperature\":22,\"humidity\":62};\n//var 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;\n//timestamp+=1;\n//data2.timestamp=timestamp;\n//timestamp+=1;\n//data3.timestamp=timestamp;\n\nmsg1.payload=data1;\n//msg2.payload=data2;\n//msg3.payload=data3;\ntimestamp+=1;\ncount+=1;\ncontext.set('count',count);\ncontext.set('timestamp',timestamp);\nreturn msg1;","outputs":1,"noerr":0,"x":260,"y":400,"wires":[["357d9b0a.e10d2c","e16707b5.494488"]]},{"id":"357d9b0a.e10d2c","type":"debug","z":"7f84d0fc.49d6f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":410,"y":440,"wires":[]},{"id":"e16707b5.494488","type":"function","z":"7f84d0fc.49d6f","name":"write query","func":"var timestamp=msg.payload.timestamp;\nvar sensor=msg.payload.sensor;\nvar temperature=msg.payload.temperature;\nvar humidity=msg.payload.humidity;\n//put data into json string\n\nmsg=\"( timestamp,sensor,temperature,humidity) values(\"+timestamp +\",\\'\"+sensor +\"\\',\" + temperature + \",\" + humidity +\")\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\nmsg1.payload=timestamp;\nreturn msg1;","outputs":1,"noerr":0,"x":430,"y":400,"wires":[["76a1ebe4.7141cc","f01afa9c.2efe7"]]},{"id":"76a1ebe4.7141cc","type":"debug","z":"7f84d0fc.49d6f","name":"sql out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":590,"y":440,"wires":[]},{"id":"3ad7c728.b01598","type":"function","z":"7f84d0fc.49d6f","name":"Insert using variable","func":"var sensor=\"sensor5\";\nmsg=\"( timestamp,sensor,temperature,humidity) values(4,\"+\"\\'\"+sensor +\"\\'\" + \",20,66)\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\n\nreturn msg1;","outputs":1,"noerr":0,"x":320,"y":160,"wires":[["41be04d2.d76fc4","615ef7c72b804fa3"]]},{"id":"aef24493.aeb0c8","type":"debug","z":"7f84d0fc.49d6f","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":510,"y":100,"wires":[]},{"id":"41186c79.d2be6c","type":"sqlite","z":"7f84d0fc.49d6f","mydb":"f5a33dc2.f6f08","sqlquery":"msg.topic","sql":"","name":"Test.db","x":340,"y":40,"wires":[["d4d337ca.8077b8"]]},{"id":"f64d795d.fb6ae","type":"catch","z":"7f84d0fc.49d6f","name":"","scope":null,"uncaught":false,"x":110,"y":500,"wires":[["32c27692.03eeaa","36ed3df.88509c2"]]},{"id":"32c27692.03eeaa","type":"debug","z":"7f84d0fc.49d6f","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":270,"y":560,"wires":[]},{"id":"b922566a.497af8","type":"ui_text","z":"7f84d0fc.49d6f","group":"d44d4f44.3219e8","order":7,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":430,"y":500,"wires":[]},{"id":"f01afa9c.2efe7","type":"delay","z":"7f84d0fc.49d6f","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":600,"y":400,"wires":[["41be04d2.d76fc4"]]},{"id":"36ed3df.88509c2","type":"function","z":"7f84d0fc.49d6f","name":"sql errors","func":"var payload=msg.error.message;\nmsg.payload = payload +\" timestamp \"+msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":280,"y":500,"wires":[["b922566a.497af8"]]},{"id":"f1424658.30cf38","type":"inject","z":"7f84d0fc.49d6f","name":"Drop Table","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"drop table data","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":120,"y":340,"wires":[["41be04d2.d76fc4"]]},{"id":"2bc9495f.1b60de","type":"inject","z":"7f84d0fc.49d6f","name":"insert","props":[{"p":"payload","v":"","vt":"date"},{"p":"topic","v":"","vt":"string"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":110,"y":100,"wires":[["c0c0246e.0f5ed"]]},{"id":"e33e7ff6.a8878","type":"function","z":"7f84d0fc.49d6f","name":"Clear errors","func":"node.error(\"\",msg);//clear error\nreturn msg;","outputs":1,"noerr":0,"x":310,"y":980,"wires":[[]]},{"id":"5a600e15.2c12a8","type":"inject","z":"7f84d0fc.49d6f","name":"clear errors","repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":110,"y":940,"wires":[["e33e7ff6.a8878"]]},{"id":"e3d941d6.71e9f8","type":"inject","z":"7f84d0fc.49d6f","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":1380,"wires":[["64ab6c84.0329dc"]]},{"id":"64ab6c84.0329dc","type":"function","z":"7f84d0fc.49d6f","name":"","func":"var data={};\ndata[\"temp\"]=20;\ndata[\"humidity\"]=50;\njson_out=JSON.stringify(data);\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":1420,"wires":[[]]},{"id":"615ef7c72b804fa3","type":"debug","z":"7f84d0fc.49d6f","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":510,"y":160,"wires":[]},{"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\\test1.db","mode":"RWC"},{"id":"34054411.bf9404","type":"ui_tab","name":"Home","icon":"dashboard"}]