alex9ufo 聰明人求知心切: 11月 2020

2020年11月30日星期一

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程式

Node-Red Storing IOT data In a SQL Database Part2

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程式

Store IOT Data in SQLite Database in Node-Red

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"}]

Node-Red SQLite 範例

產生資料庫Create

CREATE TABLE RFID ( id INT PRIMARY KEY NOT NULL, current_time TEXT , uid_name TEXT)

插入資料庫的資料

msg.topic = "INSERT INTO RFID (id,current_time, uid_name) VALUES (?,?,?)";

var Today = new Date();

var yyyy = Today.getFullYear(); //年

var MM = Today.getMonth()+1; //月

var dd = Today.getDate(); //日

var h = Today.getHours(); //時

var m = Today.getMinutes(); //分

var s = Today.getSeconds(); //秒

if(MM<10)

{

MM = '0'+MM;

}

if(dd<10)

{

dd = '0'+dd;

}

if(h<10)

{

h = '0'+h;

}

if(m<10)

{

m = '0' + m;

}

if(s<10)

{

s = '0' + s;

}

var hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;

var id= Date.now() ;

msg.payload = [id ,hms, msg.payload];

return msg;

檢視資料庫

SELECT * FROM RFID ORDER BY id DESC LIMIT 100;

顯示樣板

<tr>

</tr>

<td>{{$index}}</td>

<td>{{msg.payload[$index].current_time}}</td>

<td>{{msg.payload[$index].uid_name}}</td>

</tr>

</table>

檔案目錄 C:\Users\alex\AppData\Roaming\npm

SQLite檔案名稱 RFID2020_1130.db

// N0de-Red程式

[{"id":"1909ec67.e04a64","type":"debug","z":"18a9e5f7.6c481a","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":710,"y":120,"wires":[]},{"id":"e202150a.adf688","type":"sqlite","z":"18a9e5f7.6c481a","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_Example","x":510,"y":120,"wires":[["1909ec67.e04a64"]]},{"id":"d853a446.d7ed78","type":"sqlite","z":"18a9e5f7.6c481a","mydb":"e77b6c54.4f02","sqlquery":"msg.topic","sql":"","name":"SQLITE_Example","x":450,"y":300,"wires":[["e8a8b129.c8ad6"]]},{"id":"697d90a6.eefc5","type":"ui_button","z":"18a9e5f7.6c481a","name":"","group":"b165cbf1.03d608","order":50,"width":"2","height":"2","passthru":false,"label":"新增卡號1","tooltip":"","color":"","bgcolor":"","icon":"","payload":"Alex_test1","payloadType":"str","topic":"","x":150,"y":80,"wires":[["73137d5.4a9ce84"]]},{"id":"c36997b.25b3868","type":"ui_button","z":"18a9e5f7.6c481a","name":"","group":"b165cbf1.03d608","order":51,"width":"2","height":"2","passthru":false,"label":"新增卡號2","tooltip":"","color":"","bgcolor":"blue","icon":"","payload":"RFIDAlex_test2","payloadType":"str","topic":"","x":150,"y":120,"wires":[["73137d5.4a9ce84"]]},{"id":"e12f3f08.e6d9a","type":"ui_button","z":"18a9e5f7.6c481a","name":"","group":"b165cbf1.03d608","order":8,"width":0,"height":0,"passthru":false,"label":"刪除所有的資料","tooltip":"","color":"","bgcolor":"","icon":"","payload":"","payloadType":"str","topic":"DELETE from RFID","x":160,"y":180,"wires":[["e202150a.adf688"]]},{"id":"73137d5.4a9ce84","type":"function","z":"18a9e5f7.6c481a","name":"INSERT","func":"msg.topic = \"INSERT INTO RFID (id,current_time, uid_name) VALUES (?,?,?)\";\n\nvar Today = new Date();\nvar yyyy = Today.getFullYear(); //年\nvar MM = Today.getMonth()+1; //月\nvar dd = Today.getDate(); //日\nvar h = Today.getHours(); //時\nvar m = Today.getMinutes(); //分\nvar s = Today.getSeconds(); //秒\n\nif(MM<10)\n{\n MM = '0'+MM;\n}\n\nif(dd<10)\n{\n dd = '0'+dd;\n}\n\nif(h<10)\n{\n h = '0'+h;\n}\n\nif(m<10)\n{\n m = '0' + m;\n}\n\nif(s<10)\n{\n s = '0' + s;\n}\nvar hms= yyyy + '/'+ MM + '/'+ dd + ' ' + h + ':' + m + ':' + s ;\nvar id= Date.now() ;\nmsg.payload = [id ,hms, msg.payload];\nreturn msg;\n","outputs":1,"noerr":0,"initialize":"","finalize":"","x":320,"y":100,"wires":[["e202150a.adf688","8f7e0ad3.3163f8"]]},{"id":"8f7e0ad3.3163f8","type":"ui_text","z":"18a9e5f7.6c481a","group":"b165cbf1.03d608","order":10,"width":0,"height":0,"name":"","label":"資料表","format":"{{msg.payload}}","layout":"row-left","x":470,"y":60,"wires":[]},{"id":"f30e2ef6.8fa1b","type":"ui_button","z":"18a9e5f7.6c481a","name":"","group":"b165cbf1.03d608","order":10,"width":"2","height":"2","passthru":false,"label":"檢視資料庫","tooltip":"","color":"","bgcolor":"orange","icon":"","payload":"","payloadType":"str","topic":"SELECT * FROM RFID ORDER BY id DESC LIMIT 100;","x":150,"y":300,"wires":[["d853a446.d7ed78"]]},{"id":"e8a8b129.c8ad6","type":"ui_template","z":"18a9e5f7.6c481a","group":"b165cbf1.03d608","name":"","order":0,"width":"12","height":"4","format":"<table style=\"width:100%\">\n <tr>\n <th>ID </th> \n <th>時間 </th> \n <th>卡號 </th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{$index}}</td>\n <td>{{msg.payload[$index].current_time}}</td>\n <td>{{msg.payload[$index].uid_name}}</td> \n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":false,"templateScope":"local","x":640,"y":300,"wires":[[]]},{"id":"ab89d10b.421d4","type":"ui_button","z":"18a9e5f7.6c481a","name":"","group":"b165cbf1.03d608","order":11,"width":"6","height":"2","passthru":false,"label":"建立資料庫(只能執行一次)","tooltip":"","color":"orange","bgcolor":" purple","icon":"","payload":"","payloadType":"str","topic":"CREATE TABLE RFID ( id INT PRIMARY KEY NOT NULL, current_time TEXT , uid_name TEXT)","x":190,"y":240,"wires":[["e202150a.adf688"]]},{"id":"e77b6c54.4f02","type":"sqlitedb","z":"","db":"RFID2020_1130.db","mode":"RWC"},{"id":"b165cbf1.03d608","type":"ui_group","z":"","name":"Data","tab":"7a15eb23.c05f34","order":3,"disp":true,"width":"6","collapse":false},{"id":"7a15eb23.c05f34","type":"ui_tab","z":"","name":"SQlite_2020_0720","icon":"dashboard","disabled":false,"hidden":false}]

2020年11月29日星期日

Using SQLite with Node-RED on Windows

源自於 https://github.com/mpolinowski/nodered-sqlite

Using SQLite with Node-RED on Windows

Installing SQLite

First scroll down to the newest pre-compiled version for your Operating System here and choose the bundle of command-line tools for managing SQLite database files, including the command-line shell program, the sqldiff.exe program, and the sqlite3_analyzer.exe program.

Unzip the folder, rename it something short - e.g. sqlite3 - and copy it to your C:\ partition. Now add the installation path to your system variables and run the sqlite3 command to see that it is working:

To exit the SQLite Shell again, type .quit

Creating your first Database

Navigate to the directory you want to store your data and type sqlite3 mydb.db and .databases:

To add a table, type in the following create table cameras (id INT primary key);. This will create a table named cameras with a single (primary) column with the name of id that expects an integer value.

Installing SQLite in Node-RED

Open the Manage Palette menu inside Node-RED, switch to the Install tab and search for node-red-node-sqlite. More information can be found here. A new node called sqlite should appear on the left under the storage tab. In this flow, you’re going to send 5 SQL queries (CREATE, INSERT, SELECT, DELETE and DROP) to your SQLite database:

Doubleclick the SQLite Node press the Add new sqlitedb button and type in the absolute path to the SQLite Database we created earlier

Configure your CREATE Inject Node as follows:

CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)

Repeat this step to create the following :

INSERT inject node

INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), "manual")

SELECT inject node with

SELECT * FROM dhtreadings

DELETE inject node as follows

DELETE from dhtreadings

DROP inject node

DROP TABLE dhtreadings

To save your application, you need to click the Deploy button on the top right corner (You might have to restart Node-RED) and your application is saved and ready. Open the debug window and press the first inject node to trigger the CREATE SQL query:

Visualizing Data from SQLite in Node-RED Dashboard

We are able to visualize the data by using the dashboard nodes.

Injecting SELECT * FROM dhtreadings into our SQLite node gives us an array of records in the response, we can simply use the ui template node to parse the information we want.

For example, we would like to have a table showing the first 2 records, just simply connect the template UI node to the SQLite node:

In the template node, enter the following code:

<table style="width:100%">
  <tr>
    <th>Time</th> 
    <th>Temp</th> 
    <th>Hum</th>
  </tr>
  <tr ng-repeat="x in msg.payload | limitTo:2">
    <td>{{msg.payload[$index].currenttime}}</td>
    <td>{{msg.payload[$index].temperature}}</td> 
    <td>{{msg.payload[$index].humidity}}</td>
  </tr>
</table>

This code simply a table in HTML, and places the index, currenttime, temperature and humidity fields from msg.payload into the appropriate cells of the table using ng-repeat & limitTo. Once you deploy the nodes, the UI template node will update the table whenever SELECT * is sent to the SQLite node, and an array of records are returned from the SQLite node to the UI template node. The resulting table would look like this when you hit the URL http://192.168.1.112:1880/ui/:

Database Administration

For the basic database administrative activities you can use web (php) based tools like phpLiteAdmin or go with an installable tool like SQLiteBrowser:

Flow Export

[{"id":"af8b3131.1b188","type":"tab","label":"sqlite","disabled":false,"info":""},{"id":"d7f0c96f.0d0588","type":"inject","z":"af8b3131.1b188","name":"CREATE","topic":"CREATE TABLE dhtreadings(id INTEGER PRIMARY KEY AUTOINCREMENT, temperature NUMERIC, humidity NUMERIC, currentdate DATE, currenttime TIME, device TEXT)","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":40,"wires":[["7f9d303d.adb45"]]},{"id":"522e8f98.ac3de","type":"inject","z":"af8b3131.1b188","name":"INSERT","topic":"INSERT INTO dhtreadings(temperature, humidity, currentdate, currenttime, device) values(22.4, 48, date('now'), time('now'), \"manual\")","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":100,"wires":[["7f9d303d.adb45"]]},{"id":"40fe756f.d7cd5c","type":"inject","z":"af8b3131.1b188","name":"SELECT","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":160,"wires":[["7f9d303d.adb45"]]},{"id":"3b1c34a.1639acc","type":"inject","z":"af8b3131.1b188","name":"DELETE","topic":"DELETE from dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":90,"y":220,"wires":[["7f9d303d.adb45"]]},{"id":"57370135.7daa1","type":"inject","z":"af8b3131.1b188","name":"DROP TABLE","topic":"DROP TABLE dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":100,"y":280,"wires":[["7f9d303d.adb45"]]},{"id":"7f9d303d.adb45","type":"sqlite","z":"af8b3131.1b188","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite","x":270,"y":160,"wires":[["593ed62b.495198"]]},{"id":"593ed62b.495198","type":"debug","z":"af8b3131.1b188","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":430,"y":160,"wires":[]},{"id":"3735b0c8.22ff","type":"sqlite","z":"af8b3131.1b188","mydb":"4e71ffcc.32ba8","sqlquery":"msg.topic","sql":"","name":"SQLite","x":370,"y":300,"wires":[["2f6afcf0.58e874"]]},{"id":"c8e55090.582db","type":"inject","z":"af8b3131.1b188","name":"View Records","topic":"SELECT * FROM dhtreadings","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":300,"y":240,"wires":[["3735b0c8.22ff"]]},{"id":"2f6afcf0.58e874","type":"ui_template","z":"af8b3131.1b188","group":"5be80bfc.629394","name":"UI Table","order":2,"width":"6","height":"3","format":"<table style=\"width:100%\">\n  <tr>\n    <th>Time</th> \n    <th>Temp</th> \n    <th>Hum</th>\n  </tr>\n  <tr ng-repeat=\"x in msg.payload | limitTo:2\">\n    <td>{{msg.payload[$index].currenttime}}</td>\n    <td>{{msg.payload[$index].temperature}}</td> \n    <td>{{msg.payload[$index].humidity}}</td>\n  </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"templateScope":"local","x":430,"y":360,"wires":[[]]},{"id":"4e71ffcc.32ba8","type":"sqlitedb","z":"","db":"C:\\Users\\INSTAR\\.node-red\\db\\mydb.db"},{"id":"5be80bfc.629394","type":"ui_group","z":"","name":"Data","tab":"da0180c5.64fa9","order":3,"disp":true,"width":"6","collapse":false},{"id":"da0180c5.64fa9","type":"ui_tab","z":"","name":"IP Camera","icon":"photo_camera"}]

訂閱：文章 (Atom)

2020年11月30日 星期一

2020年11月29日 星期日

Installing SQLite

Creating your first Database

Installing SQLite in Node-RED

INSERT inject node

SELECT inject node with

DELETE inject node as follows

DROP inject node

Visualizing Data from SQLite in Node-RED Dashboard

Database Administration

Flow Export

2020年11月30日星期一

2020年11月29日星期日