2023年11月24日星期五

SQLite and Node-RED

名稱	內容
Create Table inject	msg.topic = CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT , BOOL INT )
Create Table button	msg.topic = "CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT , BOOL INT)"; return msg;
INSERT inject & INSERT function	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 var_date = yyyy+'/'+MM+'/'+dd+' '+h+':'+m+':'+s; var randomNum = Math.round(Math.random()*100); var largeBool = (randomNum > 50)?1:0; msg.topic = "INSERT INTO RANDOMNUM (TIMESTAMP , VALUE , BOOL ) VALUES ($var_date , $randomNum , $largeBool ) " ; msg.payload = [var_date, randomNum , largeBool ] return msg; //CREATE TABLE RANDOMNUM( //ID INTEGER PRIMARY KEY AUTOINCREMENT , //TIMESTAMP TEXT NOT NULL , //VALUE INT NOT NULL, //BOOL INT NOT NULL //)
View Data inject	msg.topic = SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100;
SELECT * button	msg.topic = "SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100"; return msg;
DELETE inject	msg.topic = DELETE from RANDOMNUM
DELETE function	msg.topic = "DELETE from RANDOMNUM"; return msg;
DROP TABLE inject	msg.topic = DROP TABLE RANDOMNUM
DROP TABLE function	msg.topic = "DROP TABLE RANDOMNUM"; return msg;

[{"id":"fdc70b54b6b1a6ad","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":770,"y":220,"wires":[["c03f66e948805298"]]},{"id":"25a9609806d69b88","type":"inject","z":"e5871d216d80bff1","name":"Create Table","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT , BOOL INT )","payload":"","payloadType":"date","x":550,"y":240,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"9aacd9f70c7b5080","type":"comment","z":"e5871d216d80bff1","name":"","info":"CREATE TABLE RANDOMNUM(\nID INTEGER PRIMARY KEY AUTOINCREMENT,\nTIMESTAMP TEXT ,\nVALUE INT , \nBOOL INT \n,PRIMARY KEY (id)\n)","x":90,"y":180,"wires":[]},{"id":"c03f66e948805298","type":"debug","z":"e5871d216d80bff1","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":930,"y":220,"wires":[]},{"id":"d0cfc81736e68c9a","type":"function","z":"e5871d216d80bff1","name":"INSERT data into database","func":"var 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(); //秒\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 var_date = yyyy+'/'+MM+'/'+dd+' '+h+':'+m+':'+s;\n\nvar randomNum = Math.round(Math.random()*100);\nvar largeBool = (randomNum > 50)?1:0;\n\nmsg.topic = \"INSERT INTO RANDOMNUM (TIMESTAMP , VALUE , BOOL ) VALUES ($var_date , $randomNum , $largeBool ) \" ;\nmsg.payload = [var_date, randomNum , largeBool ]\nreturn msg;\n\n//CREATE TABLE RANDOMNUM(\n//ID INT PRIMARY KEY ,\n//TIMESTAMP TEXT NOT NULL ,\n//VALUE INT NOT NULL, \n//BOOL INT NOT NULL\n//)","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":400,"y":360,"wires":[["fc701fe3114ba8a9"]]},{"id":"89f60a44a343e629","type":"inject","z":"e5871d216d80bff1","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":360,"wires":[["d0cfc81736e68c9a"]]},{"id":"f150897647cf6b4c","type":"inject","z":"e5871d216d80bff1","name":"SELECT *","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM RANDOMNUM ORDER BY id DESC LIMIT 100;","payload":"","payloadType":"date","x":560,"y":200,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"cc8462dbcfe365b9","type":"inject","z":"e5871d216d80bff1","name":"DELETE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DELETE from RANDOMNUM","payload":"","payloadType":"date","x":620,"y":80,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"86478f76dcf68e1c","type":"inject","z":"e5871d216d80bff1","name":"DROP TABLE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"DROP TABLE RANDOMNUM","payload":"","payloadType":"date","x":570,"y":160,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"39288d1b0e9a36f4","type":"comment","z":"e5871d216d80bff1","name":"","info":"","x":90,"y":400,"wires":[]},{"id":"37580c3214ea7c6c","type":"ui_template","z":"e5871d216d80bff1","group":"3d571d5edb075d0c","name":"","order":1,"width":6,"height":8,"format":"<table style=\"width:100%\">\n <tr>\n <th>Index</th> \n <th>Timestamp</th>\n <th>Value</th> \n <th>Bool</th>\n </tr>\n <tr ng-repeat=\"x in msg.payload | limitTo:20\">\n <td>{{msg.payload[$index].ID}}</td>\n <td>{{msg.payload[$index].TIMESTAMP}}</td>\n <td>{{msg.payload[$index].VALUE}}</td> \n <td>{{msg.payload[$index].BOOL}}</td>\n </tr>\n</table>","storeOutMessages":true,"fwdInMessages":true,"resendOnRefresh":true,"templateScope":"local","className":"","x":540,"y":480,"wires":[["05c2f8ddb9f90cf6"]]},{"id":"2cd3919d33042f98","type":"inject","z":"e5871d216d80bff1","name":"View Data","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100;","payload":"","payloadType":"date","x":100,"y":500,"wires":[["72e262dfee532416"]]},{"id":"72e262dfee532416","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":350,"y":500,"wires":[["37580c3214ea7c6c","3ef3da019561f4db"]]},{"id":"05c2f8ddb9f90cf6","type":"debug","z":"e5871d216d80bff1","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":670,"y":480,"wires":[]},{"id":"3ef3da019561f4db","type":"ui_table","z":"e5871d216d80bff1","group":"866f959e6470a02c","name":"","order":1,"width":"9","height":8,"columns":[{"field":"ID","title":"序號","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"TIMESTAMP","title":"日期時間","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"VALUE","title":"數值","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}},{"field":"BOOL","title":"布林","width":"","align":"left","formatter":"plaintext","formatterParams":{"target":"_blank"}}],"outputs":0,"cts":false,"x":530,"y":520,"wires":[]},{"id":"52fb1b72dc427336","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":1,"width":0,"height":0,"passthru":false,"label":"DELETE ALL ","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"DELETE ALL data","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":40,"wires":[["98ee6738ca53e25d"]]},{"id":"98ee6738ca53e25d","type":"ui_toast","z":"e5871d216d80bff1","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":270,"y":40,"wires":[["11aa9c122e9a4341"]]},{"id":"11aa9c122e9a4341","type":"function","z":"e5871d216d80bff1","name":"ok ?","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;\n","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":40,"wires":[["0907c0c3ee331722"],[]]},{"id":"0907c0c3ee331722","type":"function","z":"e5871d216d80bff1","name":"DELETE","func":"msg.topic = \"DELETE from RANDOMNUM\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":680,"y":40,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"fea43c8882f7c611","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":2,"width":0,"height":0,"passthru":false,"label":"DROP TABLE","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"DROP TABLE","payloadType":"str","topic":"topic","topicType":"msg","x":120,"y":120,"wires":[["280a24efdfb869ec"]]},{"id":"280a24efdfb869ec","type":"ui_toast","z":"e5871d216d80bff1","position":"prompt","displayTime":"3","highlight":"","sendall":true,"outputs":1,"ok":"OK","cancel":"Cancel","raw":true,"className":"","topic":"","name":"","x":290,"y":120,"wires":[["0c1789304c8cbdc4"]]},{"id":"0c1789304c8cbdc4","type":"function","z":"e5871d216d80bff1","name":"ok ?","func":"var topic=msg.payload;\nif (topic==\"\"){\n return [msg,null];\n \n}\nif (topic==\"Cancel\"){\n return [null,msg];\n \n}\nreturn msg;\n","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":120,"wires":[["0ea24c2427871d8d"],[]]},{"id":"0ea24c2427871d8d","type":"function","z":"e5871d216d80bff1","name":"DROP TABLE ","func":"msg.topic = \"DROP TABLE RANDOMNUM\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":580,"y":120,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"17ba4ecdebd82736","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":3,"width":0,"height":0,"passthru":false,"label":"SELECT *","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":90,"y":440,"wires":[["dee563ebbe341ad2"]]},{"id":"dee563ebbe341ad2","type":"function","z":"e5871d216d80bff1","name":"SELECT *","func":"msg.topic = \"SELECT * FROM RANDOMNUM ORDER BY ID DESC LIMIT 100\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":290,"y":440,"wires":[["72e262dfee532416"]]},{"id":"51d6e19fc98acb03","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":4,"width":0,"height":0,"passthru":false,"label":"Create Table","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":110,"y":220,"wires":[["7de36e59aaa57793"]]},{"id":"7de36e59aaa57793","type":"function","z":"e5871d216d80bff1","name":"CREATE TABLE","func":"\nmsg.topic = \"CREATE TABLE RANDOMNUM( ID INTEGER PRIMARY KEY AUTOINCREMENT, TIMESTAMP TEXT , VALUE INT , BOOL INT)\";\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":320,"y":220,"wires":[["fdc70b54b6b1a6ad"]]},{"id":"6b44741d7a4c8885","type":"ui_button","z":"e5871d216d80bff1","name":"","group":"ac7347ff53d34ce3","order":5,"width":0,"height":0,"passthru":false,"label":"INSERT","tooltip":"","color":"","bgcolor":"","className":"","icon":"","payload":"","payloadType":"str","topic":"topic","topicType":"msg","x":100,"y":300,"wires":[["d0cfc81736e68c9a"]]},{"id":"fc701fe3114ba8a9","type":"sqlite","z":"e5871d216d80bff1","mydb":"a87c732dba25794c","sqlquery":"msg.topic","sql":"","name":"testDB","x":650,"y":360,"wires":[["dee563ebbe341ad2"]]},{"id":"a87c732dba25794c","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\testDB.db","mode":"RWC"},{"id":"3d571d5edb075d0c","type":"ui_group","name":"View_Database","tab":"2f449046556f64a6","order":2,"disp":true,"width":6,"collapse":false,"className":""},{"id":"866f959e6470a02c","type":"ui_group","name":"Database Table","tab":"2f449046556f64a6","order":3,"disp":true,"width":"9","collapse":false,"className":""},{"id":"ac7347ff53d34ce3","type":"ui_group","name":"COMMAND","tab":"2f449046556f64a6","order":1,"disp":true,"width":"6","collapse":false,"className":""},{"id":"2f449046556f64a6","type":"ui_tab","name":"testDB","icon":"dashboard","order":92,"disabled":false,"hidden":false}]

SQLite and Node-RED

源自於 https://funprojects.blog/2019/12/26/sqlite-and-node-red/

DECEMBER 26, 2019Posted in NODE-REDTagged CHART, NODE-RED, NODERED, RASPBERRY PI, SQLITE

Sqlite is an extremely light weight database that does not run a server component.

In this blog I wanted to document how I used Node-Red to create, insert and view SQL data on a Raspberry Pi. I also wanted to show how to reformat the SQL output so that it could be viewed in a Node-Red Dashboard line chart.

Installation

Node-Red is pre-installed on the Pi Raspian image. I wasn’t able to install the Sqlite node using the Node-Red palette manager. Instead I did a manual install as per the directions at: https://flows.nodered.org/node/node-red-node-sqlite .

cd ~/.node-red npm i --unsafe-perm node-red-node-sqlite npm rebuild

Create a Database and Table

It is possible to create a database and table structures totally in Node-Red.

I connected a manual inject node to a sqlite node.

sqlite_create_table

In the sqlite node an SQL create table command is used to make a new table. Note: the database file is automatically created.

For my example I used a 2 column table with a timestamp and a value

sqlite_db_config

Insert Data into Sqlite

Data can be inserted into Sqlite a number of different ways. A good approach for a Rasp Pi is to pass some parameters into an SQL statement.

sqlite_insert_flow

The sqlite node can use a “Prepared Statement” with a msg.params item to pass in data. For my example I created two variable $thetime and $thevalue.

sqlite_insert_conf

A function node can be used to format a msg.params item.

// Create a Params variable
// with a time and value component
//
msg.params = { $thetime:Date.now(), $thevalue:msg.payload }
return msg;

Viewing Sqlite Data

A “select” statement is used in an sqlite node to view the data.

A simple SQL statement to get all the data for all the rows in this example would be:

select * from temps;

A debug node can used to view the output.
sqlite_select

Custom Line Chart

Node-Red has a nice dashboard component that is well formatted for web pages on mobile devices.

To add the dashboard components use the Node-Red palette manager and search for: node-red-dashboard.

By default the chart node will create its own data vs. time storage. For many applications this is fine however if you want long term storage or customized historical plots then you will need to pass all the trend data to the chart node.

For some details on passing data into charts see: https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#stored-data

Below is an example flow for creating a custom chart with 3 values with times. custom_chart_data

The JavaScript code needs to create a structure with: series, data and labels definitions

msg.payload = [{
"series": ["A"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 6 }
]
],
"labels": ["Data Values"]
}];
 
return msg;

This will create a simple chart:

custom_chart_image

For reference, below is an example of the data structure for three I/O points with timestamps:

// Data Structure for: Three data points with timestamps
 
msg.payload = [{
"series": ["A", "B", "C"],
"data": [
[{ "x": 1577229315152, "y": 5 },
{ "x": 1577229487133, "y": 4 },
{ "x": 1577232484872, "y": 2 }
],
[{ "x": 1577229315152, "y": 8 },
{ "x": 1577229487133, "y": 2 },
{ "x": 1577232484872, "y": 11 }
],
[{ "x": 1577229315152, "y": 15 },
{ "x": 1577229487133, "y": 14 },
{ "x": 1577232484872, "y": 12 }
]
],
"labels": ["Data Values"]
}];

Sqlite Data in a Line Chart

To manually update a line chart with some Sqlite data I used the following nodes:

sqlite_2_chart The SQL select statement will vary based on which time period or aggregate data is required. For the last 8 values I used:

select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;

The challenging part is to format the SQL output to match the required format for the Line Chart. You will need to iterate over each data row (payload object) and format a JSON string.

 //  
 // Create a data variable   
 //  
 var series = ["temp DegC"];  
 var labels = ["Data Values"];  
 var data = "[[";  
   
 for (var i=0; i < msg.payload.length; i++) {  
   data += '{ "x":' + msg.payload[i].thetime + ', "y":' + msg.payload[i].thetemp + '}';  
   if (i < (msg.payload.length - 1)) {  
     data += ","  
   } else {  
     data += "]]"  
   }  
 }  
 var jsondata = JSON.parse(data);  
 msg.payload = [{"series": series, "data": jsondata, "labels": labels}];  
   
   
 return msg;

To view the Node-Red Dashboard enter: http://pi_address:1880/ui

Screen_chart_sqlite

Final Comments

For a small standalone Raspberry Pi project using sqlite as a database is an excellent option. Because a Pi is limited in data storage I would need to include a function to limit the amount of data stored.

訂閱：文章 (Atom)

alex9ufo 聰明人求知心切

2023年11月24日星期五