Reading data from a SQL Database Using Node-Red (讀取SQLite資料庫內容)
源自於 http://www.steves-internet-guide.com/storing-iot-data-sql-database/
1.Convert time
//var payload=msg.payload;
for (var i=0;i<msg.payload.length;i++)
{
dateObj = new Date(msg.payload[i].TIMESTAMP);
var date=dateObj.getDate();
var month=dateObj.getMonth()+1;
var year=dateObj.getFullYear();
var hours=dateObj.getHours();
var minutes=dateObj.getMinutes();
var seconds=dateObj.getSeconds();
var newtime=date+"/"+month+"/"+year;
newtime=newtime+"-"+hours+":"+minutes+":"+seconds;
msg.payload[i].TIMESTAMP=newtime
}
return msg;
2. 函數
var data=context.get('data')|| {value:"",condition:">="};
var topic=msg.topic;
var payload=msg.payload;
if (topic=="site")
data.site=msg.payload;
if (topic=="value")
data.value=msg.payload;
if (topic=="condition")
data.condition=msg.payload;
if (topic=="sdate")
data.sdate=msg.payload;
if (topic=="edate")
data.edate=msg.payload;
//
var d=new Date();
now=d.getTime();
if (data.edate==undefined || data.edate=="")
edate=now;
else
edate=data.edate;
if (data.sdate==undefined || data.sdate=="")
sdate=0;
else
sdate=data.sdate;
context.set('data',data)
if (sdate==0)
{
var part_msg="";
}
else
{
var part_msg=" AND TIMESTAMP >"+sdate+ " AND TIMESTAMP<"+edate+ " ";
}
if(topic=="submit")
{
var query="";
if(data.value==undefined ||data.value=="" )
{
if(data.site!=undefined)
query ="SELECT * FROM SITES WHERE SITE="+"\""+data.site+"\""+part_msg+" ORDER BY TIMESTAMP DESC LIMIT 100";
}
else if(data.value!=undefined && data.value!="" )
{
if(data.site!=undefined && data.condition !=undefined)
{
query ="SELECT * FROM SITES WHERE SITE="+"\""+data.site+"\""+" AND PAGE_SPEED ";
query=query+data.condition + data.value +part_msg;
query=query+" ORDER BY TIMESTAMP DESC LIMIT 100";
}
}
node.log("query "+query);
msg.topic=query;
context.set('data',data)
if (query!="")
return msg;
}
return;
3. Node-Red程式
[{"id":"a40cd66e.d9f398","type":"sqlite","z":"579c5a8c.871a84","mydb":"b30b3534.bde2f8","sqlquery":"msg.topic","sql":"","name":"sites database","x":340,"y":80,"wires":[["f7328f55.20ab3"]]},{"id":"4aa0c162.e6b9b","type":"inject","z":"579c5a8c.871a84","name":"show results","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM SITES ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":110,"y":60,"wires":[["a40cd66e.d9f398"]]},{"id":"475a4754.a4f3e8","type":"ui_template","z":"579c5a8c.871a84","group":"725a612e.289f8","name":"","order":0,"width":0,"height":0,"format":"<style>\ntable\n{\n background:blue;\n}\n.main\n{\n\n\n height:500px;\n}\n</style>\n<div class=\"main\">\n<table style=\"width:100%\">\n <tr>\n <th>TIMESTAMP</th> \n <th>SITE</th>\n <th>PAGE LOAD SPEED</th> \n\n \n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:100\">\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].SITE}}</td>\n <td>{{msg.payload[$index].PAGE_SPEED}}</td> \n \n\n </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":660,"y":80,"wires":[[]]},{"id":"af55f545.af9538","type":"ui_dropdown","z":"579c5a8c.871a84","name":"","label":"Site","tooltip":"","place":"Select option","group":"cc87581b.e05208","order":2,"width":"6","height":"1","passthru":false,"multiple":true,"options":[{"label":"www.build-your-website.co.u","value":"www.build-your-website.co.uk","type":"str"},{"label":"www.steves-internet-guide.com","value":"www.steves-internet-guide.com","type":"str"},{"label":"stevessmarthomeguide.com","value":"stevessmarthomeguide.com","type":"str"}],"payload":"","topic":"site","x":70,"y":120,"wires":[["edaeabe5.ebbc88"]]},{"id":"b017e4b0.802d28","type":"debug","z":"579c5a8c.871a84","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":270,"y":420,"wires":[]},{"id":"edaeabe5.ebbc88","type":"function","z":"579c5a8c.871a84","name":"","func":"var data=context.get('data')|| {value:\"\",condition:\">=\"};\nvar topic=msg.topic;\nvar payload=msg.payload;\nif (topic==\"site\")\ndata.site=msg.payload;\nif (topic==\"value\")\ndata.value=msg.payload;\nif (topic==\"condition\")\ndata.condition=msg.payload;\nif (topic==\"sdate\")\ndata.sdate=msg.payload;\nif (topic==\"edate\")\ndata.edate=msg.payload;\n\n\n//\nvar d=new Date();\nnow=d.getTime();\nif (data.edate==undefined || data.edate==\"\")\n edate=now;\nelse\nedate=data.edate;\nif (data.sdate==undefined || data.sdate==\"\")\n sdate=0;\nelse\n sdate=data.sdate;\ncontext.set('data',data)\nif (sdate==0)\n{\n var part_msg=\"\"; \n \n}\nelse\n{\n var part_msg=\" AND TIMESTAMP >\"+sdate+ \" AND TIMESTAMP<\"+edate+ \" \"; \n}\nif(topic==\"submit\")\n{\n var query=\"\";\n if(data.value==undefined ||data.value==\"\" )\n {\n if(data.site!=undefined)\n query =\"SELECT * FROM SITES WHERE SITE=\"+\"\\\"\"+data.site+\"\\\"\"+part_msg+\" ORDER BY TIMESTAMP DESC LIMIT 100\";\n }\n else if(data.value!=undefined && data.value!=\"\" ) \n {\n if(data.site!=undefined && data.condition !=undefined)\n {\n query =\"SELECT * FROM SITES WHERE SITE=\"+\"\\\"\"+data.site+\"\\\"\"+\" AND PAGE_SPEED \";\n query=query+data.condition + data.value +part_msg;\n query=query+\" ORDER BY TIMESTAMP DESC LIMIT 100\";\n }\n }\nnode.log(\"query \"+query);\nmsg.topic=query;\n\ncontext.set('data',data)\nif (query!=\"\")\n return msg;\n}\nreturn;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":290,"y":260,"wires":[["a40cd66e.d9f398"]]},{"id":"f7328f55.20ab3","type":"function","z":"579c5a8c.871a84","name":"Convert time","func":"//var payload=msg.payload;\nfor (var i=0;i<msg.payload.length;i++)\n{\n dateObj = new Date(msg.payload[i].TIMESTAMP); \n var date=dateObj.getDate();\n var month=dateObj.getMonth()+1;\n var year=dateObj.getFullYear();\n var hours=dateObj.getHours();\n var minutes=dateObj.getMinutes(); \n var seconds=dateObj.getSeconds(); \n var newtime=date+\"/\"+month+\"/\"+year;\n newtime=newtime+\"-\"+hours+\":\"+minutes+\":\"+seconds;\n msg.payload[i].TIMESTAMP=newtime\n}\n\nreturn msg;","outputs":1,"noerr":0,"x":510,"y":80,"wires":[["475a4754.a4f3e8"]]},{"id":"d1d4d396.c01fd","type":"ui_dropdown","z":"579c5a8c.871a84","name":"Page Load Speed","label":"Page Load Speed","tooltip":"","place":"Select option","group":"cc87581b.e05208","order":2,"width":"5","height":"1","passthru":true,"options":[{"label":">=","value":">=","type":"str"},{"label":"<=","value":"<=","type":"str"},{"label":"=","value":"=","type":"str"}],"payload":"","topic":"condition","x":110,"y":180,"wires":[["edaeabe5.ebbc88"]]},{"id":"1e2fa072.75b04","type":"ui_text_input","z":"579c5a8c.871a84","name":"value","label":"value","tooltip":"","group":"cc87581b.e05208","order":2,"width":"4","height":"1","passthru":false,"mode":"text","delay":"100","topic":"value","x":70,"y":240,"wires":[["edaeabe5.ebbc88"]]},{"id":"b67a72f3.4c536","type":"ui_button","z":"579c5a8c.871a84","name":"submit","group":"cc87581b.e05208","order":0,"width":"4","height":"1","passthru":false,"label":"Submit","tooltip":"","color":"","bgcolor":"","icon":"","payload":"1","payloadType":"str","topic":"submit","x":70,"y":300,"wires":[["edaeabe5.ebbc88"]]},{"id":"f1b960b3.e813","type":"ui_date_picker","z":"579c5a8c.871a84","name":"start date","label":"Start date","group":"cc87581b.e05208","order":4,"width":0,"height":0,"passthru":true,"topic":"sdate","x":80,"y":420,"wires":[["edaeabe5.ebbc88","b017e4b0.802d28"]]},{"id":"4bd0ba18.db4644","type":"ui_date_picker","z":"579c5a8c.871a84","name":"End Date","label":"End Date","group":"cc87581b.e05208","order":4,"width":0,"height":0,"passthru":true,"topic":"edate","x":80,"y":360,"wires":[["edaeabe5.ebbc88"]]},{"id":"b30b3534.bde2f8","type":"sqlitedb","z":"","db":"sites.db","mode":"RWC"},{"id":"725a612e.289f8","type":"ui_group","z":"","name":"Results","tab":"5fb4c7c3.738e88","order":2,"disp":true,"width":"14","collapse":false},{"id":"cc87581b.e05208","type":"ui_group","z":"","name":"Default","tab":"5fb4c7c3.738e88","order":1,"disp":true,"width":"12","collapse":false},{"id":"5fb4c7c3.738e88","type":"ui_tab","z":"","name":"SITE-RESULTS","icon":"dashboard"}]
沒有留言:
張貼留言