Node-Red + SQLite
[
{
"id": "6d54f1c90f2b268b",
"type": "inject",
"z": "f7dcba167cab72f5",
"name": "CREATE",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 60,
"wires": [
[
"aa61a72c40fc35e3"
]
]
},
{
"id": "aa61a72c40fc35e3",
"type": "sqlite",
"z": "f7dcba167cab72f5",
"mydb": "af927aba.0dc108",
"sqlquery": "fixed",
"sql": "create table temps (thetime Timestamp , thetemp integer)",
"name": "CREATE TABLE",
"x": 360,
"y": 60,
"wires": [
[
"ec9a92a690df8642"
]
]
},
{
"id": "ec9a92a690df8642",
"type": "debug",
"z": "f7dcba167cab72f5",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 530,
"y": 60,
"wires": []
},
{
"id": "a227dba4e7c0230b",
"type": "sqlite",
"z": "f7dcba167cab72f5",
"mydb": "af927aba.0dc108",
"sqlquery": "prepared",
"sql": "insert into temps (thetime , thetemp) values ($thetime , $thevalue)",
"name": "INSERT INTO SQLITE",
"x": 620,
"y": 160,
"wires": [
[
"963af8cead0dbed2"
]
]
},
{
"id": "9cd4ddb7a9e48fb8",
"type": "inject",
"z": "f7dcba167cab72f5",
"name": "INSERT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 160,
"wires": [
[
"5c7545b34b108347"
]
]
},
{
"id": "5c7545b34b108347",
"type": "random",
"z": "f7dcba167cab72f5",
"name": "",
"low": 1,
"high": "100",
"inte": "true",
"property": "payload",
"x": 260,
"y": 160,
"wires": [
[
"ac5d165902f18eb8"
]
]
},
{
"id": "ac5d165902f18eb8",
"type": "function",
"z": "f7dcba167cab72f5",
"name": "msg.params",
"func": "// Create a Params variable\n// with a time and value component\n//\nmsg.params = { $thetime:Date.now(), $thevalue:msg.payload }\nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 430,
"y": 160,
"wires": [
[
"a227dba4e7c0230b",
"8e011b76938da008"
]
]
},
{
"id": "963af8cead0dbed2",
"type": "debug",
"z": "f7dcba167cab72f5",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 810,
"y": 160,
"wires": []
},
{
"id": "8e011b76938da008",
"type": "debug",
"z": "f7dcba167cab72f5",
"name": "debug 72",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "params",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 580,
"y": 120,
"wires": []
},
{
"id": "59fb557ebcbab510",
"type": "inject",
"z": "f7dcba167cab72f5",
"name": "SELECT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 240,
"wires": [
[
"6d345b74f17c79ad"
]
]
},
{
"id": "6d345b74f17c79ad",
"type": "sqlite",
"z": "f7dcba167cab72f5",
"mydb": "af927aba.0dc108",
"sqlquery": "fixed",
"sql": "select * from temps;",
"name": "select",
"x": 370,
"y": 240,
"wires": [
[
"c1808cd1325c2dd6"
]
]
},
{
"id": "c1808cd1325c2dd6",
"type": "debug",
"z": "f7dcba167cab72f5",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 570,
"y": 240,
"wires": []
},
{
"id": "8bcaac3a0ed08136",
"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": 670,
"y": 300,
"wires": [
[]
]
},
{
"id": "33d777e29323a151",
"type": "function",
"z": "f7dcba167cab72f5",
"name": "Formatted Data - 3 times/values",
"func": "msg.payload = [{\n\"series\": [\"A\"],\n\"data\": [\n[{ \"x\": 1577229315152, \"y\": 5 },\n{ \"x\": 1577229487133, \"y\": 4 },\n{ \"x\": 1577232484872, \"y\": 6 }\n]\n],\n\"labels\": [\"Data Values\"]\n}];\n \nreturn msg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 410,
"y": 300,
"wires": [
[
"8bcaac3a0ed08136"
]
]
},
{
"id": "fb8044f38fbceb6a",
"type": "inject",
"z": "f7dcba167cab72f5",
"name": "Line Chart",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 120,
"y": 300,
"wires": [
[
"33d777e29323a151"
]
]
},
{
"id": "f2f5cfcd7742e5f4",
"type": "function",
"z": "f7dcba167cab72f5",
"name": "Formatted Chart Data ",
"func": " // \n // Create a data variable \n // \n var series = [\"temp DegC\"]; \n var labels = [\"Data Values\"]; \n var data = \"[[\"; \n \n for (var i=0; i < msg.payload.length; i++) { \n data += '{ \"x\":' + msg.payload[i].thetime + ', \"y\":' + msg.payload[i].thetemp + '}'; \n if (i < (msg.payload.length - 1)) { \n data += \",\" \n } else { \n data += \"]]\" \n } \n } \n var jsondata = JSON.parse(data); \n msg.payload = [{\"series\": series, \"data\": jsondata, \"labels\": labels}]; \n \n \n return msg; ",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 500,
"y": 380,
"wires": [
[
"d251faf76f285f5c",
"56ceb28b1d7350af"
]
]
},
{
"id": "8f71cf747905f57b",
"type": "inject",
"z": "f7dcba167cab72f5",
"name": "Sqlite Data in a Line Chart",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payload": "",
"payloadType": "date",
"x": 150,
"y": 380,
"wires": [
[
"e7b510e90114d162"
]
]
},
{
"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": 690,
"y": 380,
"wires": [
[]
]
},
{
"id": "56ceb28b1d7350af",
"type": "debug",
"z": "f7dcba167cab72f5",
"name": "debug ",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "payload",
"targetType": "msg",
"statusVal": "",
"statusType": "auto",
"x": 690,
"y": 420,
"wires": []
},
{
"id": "e7b510e90114d162",
"type": "sqlite",
"z": "f7dcba167cab72f5",
"mydb": "af927aba.0dc108",
"sqlquery": "fixed",
"sql": "select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;",
"name": "select",
"x": 330,
"y": 380,
"wires": [
[
"f2f5cfcd7742e5f4"
]
]
},
{
"id": "af927aba.0dc108",
"type": "sqlitedb",
"db": "C:\\Users\\User\\.node-red\\sensor_0611.db",
"mode": "RWC"
},
{
"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
}
]
[{"id":"6d54f1c90f2b268b","type":"inject","z":"f7dcba167cab72f5","name":"CREATE","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":60,"wires":[["aa61a72c40fc35e3"]]},{"id":"aa61a72c40fc35e3","type":"sqlite","z":"f7dcba167cab72f5","mydb":"af927aba.0dc108","sqlquery":"fixed","sql":"create table temps (thetime Timestamp , thetemp integer)","name":"CREATE TABLE","x":360,"y":60,"wires":[["ec9a92a690df8642"]]},{"id":"ec9a92a690df8642","type":"debug","z":"f7dcba167cab72f5","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":530,"y":60,"wires":[]},{"id":"a227dba4e7c0230b","type":"sqlite","z":"f7dcba167cab72f5","mydb":"af927aba.0dc108","sqlquery":"prepared","sql":"insert into temps (thetime , thetemp) values ($thetime , $thevalue)","name":"INSERT INTO SQLITE","x":620,"y":160,"wires":[["963af8cead0dbed2"]]},{"id":"9cd4ddb7a9e48fb8","type":"inject","z":"f7dcba167cab72f5","name":"INSERT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":160,"wires":[["5c7545b34b108347"]]},{"id":"5c7545b34b108347","type":"random","z":"f7dcba167cab72f5","name":"","low":1,"high":"100","inte":"true","property":"payload","x":260,"y":160,"wires":[["ac5d165902f18eb8"]]},{"id":"ac5d165902f18eb8","type":"function","z":"f7dcba167cab72f5","name":"msg.params","func":"// Create a Params variable\n// with a time and value component\n//\nmsg.params = { $thetime:Date.now(), $thevalue:msg.payload }\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":430,"y":160,"wires":[["a227dba4e7c0230b","8e011b76938da008"]]},{"id":"963af8cead0dbed2","type":"debug","z":"f7dcba167cab72f5","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":810,"y":160,"wires":[]},{"id":"8e011b76938da008","type":"debug","z":"f7dcba167cab72f5","name":"debug 72","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"params","targetType":"msg","statusVal":"","statusType":"auto","x":580,"y":120,"wires":[]},{"id":"59fb557ebcbab510","type":"inject","z":"f7dcba167cab72f5","name":"SELECT","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":240,"wires":[["6d345b74f17c79ad"]]},{"id":"6d345b74f17c79ad","type":"sqlite","z":"f7dcba167cab72f5","mydb":"af927aba.0dc108","sqlquery":"fixed","sql":"select * from temps;","name":"select","x":370,"y":240,"wires":[["c1808cd1325c2dd6"]]},{"id":"c1808cd1325c2dd6","type":"debug","z":"f7dcba167cab72f5","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":570,"y":240,"wires":[]},{"id":"8bcaac3a0ed08136","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":670,"y":300,"wires":[[]]},{"id":"33d777e29323a151","type":"function","z":"f7dcba167cab72f5","name":"Formatted Data - 3 times/values","func":"msg.payload = [{\n\"series\": [\"A\"],\n\"data\": [\n[{ \"x\": 1577229315152, \"y\": 5 },\n{ \"x\": 1577229487133, \"y\": 4 },\n{ \"x\": 1577232484872, \"y\": 6 }\n]\n],\n\"labels\": [\"Data Values\"]\n}];\n \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":410,"y":300,"wires":[["8bcaac3a0ed08136"]]},{"id":"fb8044f38fbceb6a","type":"inject","z":"f7dcba167cab72f5","name":"Line Chart","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":120,"y":300,"wires":[["33d777e29323a151"]]},{"id":"f2f5cfcd7742e5f4","type":"function","z":"f7dcba167cab72f5","name":"Formatted Chart Data ","func":" // \n // Create a data variable \n // \n var series = [\"temp DegC\"]; \n var labels = [\"Data Values\"]; \n var data = \"[[\"; \n \n for (var i=0; i < msg.payload.length; i++) { \n data += '{ \"x\":' + msg.payload[i].thetime + ', \"y\":' + msg.payload[i].thetemp + '}'; \n if (i < (msg.payload.length - 1)) { \n data += \",\" \n } else { \n data += \"]]\" \n } \n } \n var jsondata = JSON.parse(data); \n msg.payload = [{\"series\": series, \"data\": jsondata, \"labels\": labels}]; \n \n \n return msg; ","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":500,"y":380,"wires":[["d251faf76f285f5c","56ceb28b1d7350af"]]},{"id":"8f71cf747905f57b","type":"inject","z":"f7dcba167cab72f5","name":"Sqlite Data in a Line Chart","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":380,"wires":[["e7b510e90114d162"]]},{"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":690,"y":380,"wires":[[]]},{"id":"56ceb28b1d7350af","type":"debug","z":"f7dcba167cab72f5","name":"debug ","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":690,"y":420,"wires":[]},{"id":"e7b510e90114d162","type":"sqlite","z":"f7dcba167cab72f5","mydb":"af927aba.0dc108","sqlquery":"fixed","sql":"select * from temps LIMIT 8 OFFSET (SELECT COUNT(*) FROM temps)-8;","name":"select","x":330,"y":380,"wires":[["f2f5cfcd7742e5f4"]]},{"id":"af927aba.0dc108","type":"sqlitedb","db":"C:\\Users\\User\\.node-red\\sensor_0611.db","mode":"RWC"},{"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}]
沒有留言:
張貼留言