Node-Red Storing IOT data In a SQL Database Part2
(存入 Json 格式)
參考來源 http://www.steves-internet-guide.com/storing-iot-data-sql-database/
1. 建立資料表 TABLE data
CREATE TABLE data(TIMESTAMP INT PRIMARY KEY NOT NULL, SENSOR TEXT NOT NULL,RAW_DATA TEXT NOT NULL)
2. 刪除資料表 TABLE data
drop table data
3.刪除紀錄
DELETE FROM data WHERE TIMESTAMP>=0
4.檢視資料庫內容
SELECT * FROM data ORDER BY TIMESTAMP DESC LIMIT 100
5. function 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=JSON.stringify(data1);
msg2.payload=JSON.stringify(data2);
msg3.payload=JSON.stringify(data3);
timestamp+=1;
count+=1;
context.set('count',count);
context.set('timestamp',timestamp);
return [[msg1,msg2,msg3]];
6.Write Query
var raw_data=msg.payload;
var temp=JSON.parse(msg.payload);
var timestamp=temp.timestamp;
var sensor=temp.sensor;
msg="(timestamp,sensor,raw_data) values("+timestamp +",\'"+sensor +"\'"+ ",\'"+raw_data+"\'" +")";
var topic="INSERT INTO DATA " +msg;
var msg1={};
msg1.topic=topic;
msg1.payload=timestamp;
return msg1;
7. UI 樣板
<style>
.table
{
height:400px;
width:700px;
background:blue;
}
</style>
<div class="table">
<table style="width:100%">
<tr>
<th>TIMESTAMP</th>
<th>SENSOR</th>
<th>RAW DATA (JSON)</th>
</tr>
<tr ng-repeat="x in msg.payload | limitTo:20">
<td>{{msg.payload[$index].TIMESTAMP}}</td>
<td>{{msg.payload[$index].SENSOR}}</td>
<td>{{msg.payload[$index].RAW_DATA}}</td>
</tr>
</table>
</div>
8. Node-Red程式
[{"id":"b02620d.8a494e","type":"ui_template","z":"84ff1570.906d18","group":"cfc9bd1b.be5ed","name":"UI Table JSON","order":1,"width":"","height":"","format":"<style>\n.table\n{\n height:400px;\n width:700px;\n background:blue;\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:20\">\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,"resendOnRefresh":false,"templateScope":"local","x":760,"y":60,"wires":[[]]},{"id":"d8c3cfd5.23763","type":"inject","z":"84ff1570.906d18","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":160,"y":260,"wires":[["6e6167a8.9ca048"]]},{"id":"6e6167a8.9ca048","type":"function","z":"84ff1570.906d18","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);\ndata2.temperature+=(count*.5);\ndata3.temperature+=(count*.75);\ndata1.humidity+=(count*.25);\ndata2.humidity+=(count*.5);\ndata3.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,"initialize":"","finalize":"","x":300,"y":260,"wires":[["f1d1209.b27a8e","e6593493.09e348"]]},{"id":"e6593493.09e348","type":"function","z":"84ff1570.906d18","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,"initialize":"","finalize":"","x":470,"y":260,"wires":[["ed24e8ca.4cdab8","9369eae3.0d5888"]]},{"id":"ed24e8ca.4cdab8","type":"debug","z":"84ff1570.906d18","name":"sql out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":630,"y":300,"wires":[]},{"id":"f1d1209.b27a8e","type":"debug","z":"84ff1570.906d18","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":450,"y":300,"wires":[]},{"id":"9369eae3.0d5888","type":"delay","z":"84ff1570.906d18","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":640,"y":260,"wires":[["a15fdc7a.b5753"]]},{"id":"a15fdc7a.b5753","type":"sqlite","z":"84ff1570.906d18","mydb":"34f76900.4ed308","sqlquery":"msg.topic","sql":"","name":"","x":450,"y":140,"wires":[["f87ff1a7.6ae62"]]},{"id":"8e78b579.0ebce8","type":"inject","z":"84ff1570.906d18","name":"show RECORDS","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM data ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":190,"y":60,"wires":[["5247b4fb.6f4cdc"]]},{"id":"5247b4fb.6f4cdc","type":"sqlite","z":"84ff1570.906d18","mydb":"34f76900.4ed308","sqlquery":"msg.topic","sql":"","name":"","x":450,"y":60,"wires":[["b02620d.8a494e"]]},{"id":"35ff2bd4.4a8984","type":"inject","z":"84ff1570.906d18","name":"create database table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"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":200,"y":110,"wires":[["a15fdc7a.b5753"]]},{"id":"a2c9398b.4061a8","type":"inject","z":"84ff1570.906d18","name":"delete records","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM data WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":170,"y":160,"wires":[["a15fdc7a.b5753"]]},{"id":"317d154d.651c2a","type":"inject","z":"84ff1570.906d18","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":160,"y":209,"wires":[["a15fdc7a.b5753"]]},{"id":"f87ff1a7.6ae62","type":"debug","z":"84ff1570.906d18","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":750,"y":140,"wires":[]},{"id":"cfc9bd1b.be5ed","type":"ui_group","z":"","name":"Sensors","tab":"39bfa985.c84436","order":1,"disp":true,"width":"14","collapse":false},{"id":"34f76900.4ed308","type":"sqlitedb","z":"","db":"test20201130-2.db","mode":"RWC"},{"id":"39bfa985.c84436","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
沒有留言:
張貼留言