Store IOT Data in SQLite Database Node-Red
儲存物聯網資料使用SQLite資料庫節點元件
參考來源 http://www.steves-internet-guide.com/storing-iot-data-sql-database/
var n1 = Math.round( Math.random() *100)
var n2 = Math.round( Math.random() *100)
var n3 = Math.round( Math.random() *100)
var sensor="sensor"+n1;
msg="( timestamp,sensor,temperature,humidity) values(" + "\'"+ n1 +"\'"+",\'"+sensor +"\'" +",\'"+ n2 +"\'"+",\'" +n3 + "\'"+")";
var topic="INSERT INTO DATA " +msg;
var msg1={};
msg1.topic=topic;
return msg1;
JSON encoded data is also a text string and so can also be stored directly in the database, but more commonly you will want to extract parts of the data to use as database keys (column names).
The easiest way to see this is by doing an example. Let’s assume our incoming data payload looked like this:
sensor1,temperature,20,humidity,71
So what we have is a device called sensor1 and two key/value pairs:
temperature=20 and humidity=71
To store this data in a data base we first need a database and a table.
So lets create a database called test.db and a table called sensor_data.
Now a table consists of rows and columns. To create a row we need to add elements to the columns. To do that we need to assign names to the columns.
In our example we would need three columns called:
- sensor
- temperature
- humidity
We also need to assign a data type to the values, in our case sensor would be TEXT and temperature and humidity would be Integers (INT)
When we add our data to the table the table would look like this:
| Sensor | Temperature | Humidity |
| sensor1 | 20 | 71 |
Note: Column names are not case sensitive.
The following code snippet shows how we do this:
var data="sensor1,temperature,20,humidity,71";
//extract elements from data
var sensor=data[0];
var temperature=data[3];
var humidity=data[5];
//create SQL command
msg="(sensor,temperature,humidity) values("+",\'"+sensor +"\'," + temperature + "," + humidity +")";
var topic="INSERT INTO DATA " +msg;
var msg1={}; //create new msg object
//The SQlite node expects the command in the topic field
msg1.topic=topic;
return msg1;
The interesting and most important point when constructing the command is that text values need to be in quotes, and because we are constructing a string we need to escape them. So we have:
var n1 = Math.round( Math.random() *100)
var n2 = Math.round( Math.random() *100)
var n3 = Math.round( Math.random() *100)
var sensor="sensor"+n1;
msg="( timestamp,sensor,temperature,humidity) values(" + "\'"+ n1 +"\'"+",\'"+sensor +"\'" +",\'"+ n2 +"\'"+",\'" +n3 + "\'"+")";
var topic="INSERT INTO DATA " +msg;
var msg1={};
msg1.topic=topic;
return msg1;
[{"id":"64defb82.112a54","type":"inject","z":"4388b775.bc0d98","name":"新增資料表","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, TEMPERATURE INT NOT NULL, HUMIDITY INT NOT NULL)","payload":"","payloadType":"date","x":100,"y":160,"wires":[["4e4d5e15.d9b4e"]]},{"id":"61312ba3.11be74","type":"debug","z":"4388b775.bc0d98","name":"database_out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":720,"y":160,"wires":[]},{"id":"f264f7ef.23e548","type":"inject","z":"4388b775.bc0d98","name":"新增資料(insert data)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"6","crontab":"","once":false,"onceDelay":"","topic":"","payload":"","payloadType":"date","x":140,"y":420,"wires":[["3e0c60b7.f7233"]]},{"id":"1ccfef20.182591","type":"function","z":"4388b775.bc0d98","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,"initialize":"","finalize":"","x":270,"y":360,"wires":[["4e4d5e15.d9b4e","bad7234c.4b05a"]]},{"id":"6903e50f.e8116c","type":"inject","z":"4388b775.bc0d98","name":"刪除記錄(Record)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"DELETE FROM DATA WHERE TIMESTAMP>=0","payload":"","payloadType":"date","x":120,"y":220,"wires":[["4e4d5e15.d9b4e"]]},{"id":"e070472b.987f38","type":"inject","z":"4388b775.bc0d98","name":"檢視資料庫Records","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"30","crontab":"","once":true,"onceDelay":"","topic":"SELECT * FROM DATA ORDER BY TIMESTAMP DESC LIMIT 100","payload":"","payloadType":"date","x":140,"y":100,"wires":[["bab4327d.23b35"]]},{"id":"a4a93a5c.1110b8","type":"ui_template","z":"4388b775.bc0d98","group":"50aecc4e.588bc4","name":"UI Table","order":1,"width":"","height":"","format":"<style>\n.table\n{\n height:400px;\n width:550px;\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>TEMPERATURE</th> \n <th>HUMIDITY</th>\n \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].TEMPERATURE}}</td> \n <td>{{msg.payload[$index].HUMIDITY}}</td>\n\n </tr>\n</table>\n</div>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":560,"y":100,"wires":[[]]},{"id":"4e4d5e15.d9b4e","type":"sqlite","z":"4388b775.bc0d98","mydb":"70b352c7.b98d9c","sqlquery":"msg.topic","sql":"","name":"","x":490,"y":160,"wires":[["61312ba3.11be74"]]},{"id":"93f32cf2.9a888","type":"inject","z":"4388b775.bc0d98","name":"測試資料","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":460,"wires":[["fcd54242.d9e77"]]},{"id":"fcd54242.d9e77","type":"function","z":"4388b775.bc0d98","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,"initialize":"","finalize":"","x":260,"y":460,"wires":[["98dd95f0.5ebc48","474f6647.47b828"]]},{"id":"98dd95f0.5ebc48","type":"debug","z":"4388b775.bc0d98","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":410,"y":500,"wires":[]},{"id":"474f6647.47b828","type":"function","z":"4388b775.bc0d98","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,"initialize":"","finalize":"","x":430,"y":460,"wires":[["22d9a3fb.73a56c","e2c9013c.09a4e"]]},{"id":"22d9a3fb.73a56c","type":"debug","z":"4388b775.bc0d98","name":"sql out","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":590,"y":500,"wires":[]},{"id":"3e0c60b7.f7233","type":"function","z":"4388b775.bc0d98","name":"Insert using variable","func":"\nvar n1 = Math.round( Math.random() *100)\nvar n2 = Math.round( Math.random() *100)\nvar n3 = Math.round( Math.random() *100)\nvar sensor=\"sensor\"+n1;\n\nmsg=\"( timestamp,sensor,temperature,humidity) values(\" + \"\\'\"+ n1 +\"\\'\"+\",\\'\"+sensor +\"\\'\" +\",\\'\"+ n2 +\"\\'\"+\",\\'\" +n3 + \"\\'\"+\")\";\nvar topic=\"INSERT INTO DATA \" +msg;\nvar msg1={};\nmsg1.topic=topic;\n\nreturn msg1;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":360,"y":420,"wires":[["4e4d5e15.d9b4e","bad7234c.4b05a"]]},{"id":"bad7234c.4b05a","type":"debug","z":"4388b775.bc0d98","name":"insert","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":410,"y":360,"wires":[]},{"id":"bab4327d.23b35","type":"sqlite","z":"4388b775.bc0d98","mydb":"70b352c7.b98d9c","sqlquery":"msg.topic","sql":"","name":"","x":400,"y":100,"wires":[["a4a93a5c.1110b8"]]},{"id":"584b13cc.c4fd7c","type":"catch","z":"4388b775.bc0d98","name":"","scope":null,"uncaught":false,"x":110,"y":580,"wires":[["77d3ce1d.e3501","662fbf18.adeac"]]},{"id":"77d3ce1d.e3501","type":"debug","z":"4388b775.bc0d98","name":"error","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","x":270,"y":620,"wires":[]},{"id":"8ecc64f.df2bc98","type":"ui_text","z":"4388b775.bc0d98","group":"50aecc4e.588bc4","order":7,"width":0,"height":0,"name":"","label":"text","format":"{{msg.payload}}","layout":"row-spread","x":410,"y":580,"wires":[]},{"id":"e2c9013c.09a4e","type":"delay","z":"4388b775.bc0d98","name":"","pauseType":"delay","timeout":"1","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":600,"y":460,"wires":[["4e4d5e15.d9b4e"]]},{"id":"662fbf18.adeac","type":"function","z":"4388b775.bc0d98","name":"sql errors","func":"var payload=msg.error.message;\nmsg.payload = payload +\" timestamp \"+msg.payload;\nreturn msg;","outputs":1,"noerr":0,"x":280,"y":580,"wires":[["8ecc64f.df2bc98"]]},{"id":"a914a25c.3ba78","type":"inject","z":"4388b775.bc0d98","name":"刪除資料表(Drop Table)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":"","topic":"drop table data","payload":"","payloadType":"date","x":140,"y":280,"wires":[["4e4d5e15.d9b4e"]]},{"id":"bb56c0e9.201d7","type":"inject","z":"4388b775.bc0d98","name":"新增資料(Insert)","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":360,"wires":[["1ccfef20.182591"]]},{"id":"5e49b898.b95c88","type":"function","z":"4388b775.bc0d98","name":"Clear errors","func":"node.error(\"\",msg);//clear error\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":270,"y":520,"wires":[[]]},{"id":"9f876ef5.9140c","type":"inject","z":"4388b775.bc0d98","name":"清除錯誤","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":true,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":100,"y":520,"wires":[["5e49b898.b95c88"]]},{"id":"577e872e.08e478","type":"inject","z":"4388b775.bc0d98","name":"","repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":1360,"wires":[["853a649c.0297e8"]]},{"id":"853a649c.0297e8","type":"function","z":"4388b775.bc0d98","name":"","func":"var data={};\ndata[\"temp\"]=20;\ndata[\"humidity\"]=50;\njson_out=JSON.stringify(data);\nreturn msg;","outputs":1,"noerr":0,"x":270,"y":1400,"wires":[[]]},{"id":"643da8f4.1170c8","type":"comment","z":"4388b775.bc0d98","name":"","info":"關係資料庫的層次結構可以分為四級：資料庫(Database)、表(Table)與視圖、記錄(Record)和欄位(Field)","x":70,"y":60,"wires":[]},{"id":"21f95e2a.98ba32","type":"comment","z":"4388b775.bc0d98","name":"","info":"Drop Table 刪除表定義及其所有的數據，索引，觸發器，約束和權限規範","x":70,"y":320,"wires":[]},{"id":"50aecc4e.588bc4","type":"ui_group","z":"","name":"Sensors","tab":"d4d6ffe9.c20be","order":1,"disp":true,"width":"12","collapse":false},{"id":"70b352c7.b98d9c","type":"sqlitedb","z":"","db":"test20201130.db","mode":"RWC"},{"id":"d4d6ffe9.c20be","type":"ui_tab","z":"","name":"Home","icon":"dashboard"}]
