實驗 Sqlite and Node-Red

 實驗 Sqlite and Node-Red

參考來源 https://funprojects.blog/2019/12/26/sqlite-and-node-red/

CREATE TABLE temps (thetime Timestamp , thetemp integer)

[{"id":"d251faf76f285f5c","type":"ui_chart","z":"f7dcba167cab72f5","name":"","group":"92b4e639.d05558","order":35,"width":0,"height":0,"label":"chart","chartType":"line","legend":"false","xformat":"HH:mm:ss","interpolate":"linear","nodata":"","dot":false,"ymin":"","ymax":"","removeOlder":1,"removeOlderPoints":"","removeOlderUnit":"3600","cutout":0,"useOneColor":false,"useUTC":false,"colors":["#1f77b4","#aec7e8","#ff7f0e","#2ca02c","#98df8a","#d62728","#ff9896","#9467bd","#c5b0d5"],"outputs":1,"useDifferentColor":false,"className":"","x":730,"y":380,"wires":[[]]},{"id":"92b4e639.d05558","type":"ui_group","name":"溫度","tab":"a769a2ac.25aff","order":1,"disp":true,"width":"15","collapse":false,"className":""},{"id":"a769a2ac.25aff","type":"ui_tab","name":"Python","icon":"dashboard","order":1,"disabled":false,"hidden":false}]

SQLite Data Base

名稱	功能	備註
CREATE	inject
CREATE TABLE (sqlite)	C:\Users\User\.node-red\sqlite.db   READ-WRITE-CREATE   < SQL Query >Fixed Statement   create table temps (thetime Timestamp , thetemp integer)
debug	msg.payload
INSERT	inject
random	msg.payload  a whole number –integer  froma 1 to 100
Function (msg.params)	// Create a Params variable // with a time and value component // msg.params = { $thetime:Date.now(), $thevalue:msg.payload } return msg;
INSERT INTO SQLITE (sqlite)	C:\Users\User\.node-red\sqlite.db   < SQL Query >Prepared Statement   insert into temps (thetime , thetemp) values ($thetime , $thevalue)
select (sqlite)	C:\Users\User\.node-red\sqlite.db   < SQL Query >Fixed Statement   select * from temps;
Formatted Data - 3 times/values (function)	msg.payload = [{ "series": ["A"], "data": [ [{ "x": 1577229315152, "y": 5 }, { "x": 1577229487133, "y": 4 }, { "x": 1577232484872, "y": 6 } ] ], "labels": ["Data Values"] }];   return msg;
select2 (sqlite)	C:\Users\User\.node-red\sqlite.db   < SQL Query >Fixed Statement     select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;
Formatted Chart Data  (function)	//   // 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;