Skip to content

Instantly share code, notes, and snippets.

@sonntam
Created June 4, 2022 21:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save sonntam/7ea3226d2fc0b078c59130af7eb4b494 to your computer and use it in GitHub Desktop.
Save sonntam/7ea3226d2fc0b078c59130af7eb4b494 to your computer and use it in GitHub Desktop.
Node-Red sqlite node using custom exec node
[
{
"id": "6ea632aca2282da9",
"type": "subflow",
"name": "exec stdin",
"info": "## Inputs \r\n\r\nSimilar to the exec node this starts a process.\r\nThe input message format is as follows:\r\n\r\n```js\r\nmsg = {\r\n topic: \"<command to be executed>\",\r\n timeout: <timeout in milliseconds before process is killed>,\r\n payload: <passed to stdin:optional>\r\n}\r\n```\r\n\r\n## Outputs\r\n\r\nThe first output contains error message from the underlying\r\nnode.js functionality (if any).\r\n\r\nThe second output contains stdout output.\r\n\r\nThe third output contains stderr output.",
"category": "function",
"in": [
{
"x": 60,
"y": 80,
"wires": [
{
"id": "0c0c99c7af618215"
}
]
}
],
"out": [
{
"x": 760,
"y": 80,
"wires": [
{
"id": "0c0c99c7af618215",
"port": 0
}
]
},
{
"x": 770,
"y": 140,
"wires": [
{
"id": "0c0c99c7af618215",
"port": 1
}
]
},
{
"x": 770,
"y": 200,
"wires": [
{
"id": "0c0c99c7af618215",
"port": 2
}
]
}
],
"env": [],
"meta": {},
"color": "#dc8e6b",
"inputLabels": [
"cmd stdin"
],
"outputLabels": [
"err",
"stdout",
"stderr"
],
"icon": "node-red/cog.svg"
},
{
"id": "0c0c99c7af618215",
"type": "function",
"z": "6ea632aca2282da9",
"name": "exec",
"func": "var exec = childProcess.exec;\n\nvar child = exec(msg.topic, \n {\n timeout: msg.timeout\n },\n function (err,stdout,stderr) {\n node.send({ payload: err });\n node.send([null, Object.assign({}, msg, { \n payload: stdout \n })]);\n node.send([null, null, Object.assign({}, msg, {\n payload: stderr\n })]);\n });\n\nvar stdinStream = new stream.Readable();\nif( msg.hasOwnProperty(\"payload\") && msg.payload != null)\n{\n stdinStream.push(msg.payload.toString()); // Add data to the internal queue for users of the stream to consume\n stdinStream.push(null); // Signals the end of the stream (EOF)\n stdinStream.pipe(child.stdin);\n}",
"outputs": 3,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [
{
"var": "childProcess",
"module": "child_process"
},
{
"var": "stream",
"module": "stream"
}
],
"x": 530,
"y": 80,
"wires": [
[],
[],
[]
]
},
{
"id": "e158b68ab8e2e6fa",
"type": "subflow",
"name": "sqlite read async",
"info": "# Read from sqlite3 database asynchronically\r\n\r\n## Inputs\r\n\r\n```js\r\nmsg = {\r\n payload: \"<SQL statement here>\"\r\n}\r\n```\r\n\r\nThe database is opened with the readonly flag.\r\n\r\n## Outputs\r\n\r\nResults are returned as javascript objects in from\r\nof an array of rows.",
"category": "",
"in": [
{
"x": 40,
"y": 80,
"wires": [
{
"id": "7da38f0bc305b155"
}
]
}
],
"out": [
{
"x": 940,
"y": 80,
"wires": [
{
"id": "cf324d62c6b1f36d",
"port": 0
}
]
}
],
"env": [
{
"name": "dbFile",
"type": "str",
"value": "",
"ui": {
"icon": "font-awesome/fa-file-o",
"label": {
"en-US": "SQLite Database path"
},
"type": "input",
"opts": {
"types": [
"str"
]
}
}
}
],
"meta": {
"author": "Marcus Sonntag",
"keywords": "sqlite"
},
"color": "#E27A00",
"inputLabels": [
"sql statement"
],
"icon": "node-red-node-sqlite/sqlite.png"
},
{
"id": "053bcbfa1ecf271f",
"type": "subflow:6ea632aca2282da9",
"z": "e158b68ab8e2e6fa",
"name": "",
"x": 400,
"y": 80,
"wires": [
[],
[
"1c65ad932133fe02"
],
[]
]
},
{
"id": "7da38f0bc305b155",
"type": "change",
"z": "e158b68ab8e2e6fa",
"name": "",
"rules": [
{
"t": "set",
"p": "_original_topic",
"pt": "msg",
"to": "topic",
"tot": "msg"
},
{
"t": "set",
"p": "payload",
"pt": "msg",
"to": "topic",
"tot": "msg"
},
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "\"nice sqlite3 -json -readonly \\\"\" & $env(\"dbFile\") & \"\\\"\"",
"tot": "jsonata"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 200,
"y": 80,
"wires": [
[
"053bcbfa1ecf271f"
]
]
},
{
"id": "cf324d62c6b1f36d",
"type": "json",
"z": "e158b68ab8e2e6fa",
"name": "",
"property": "payload",
"action": "obj",
"pretty": false,
"x": 790,
"y": 80,
"wires": [
[]
]
},
{
"id": "1c65ad932133fe02",
"type": "change",
"z": "e158b68ab8e2e6fa",
"name": "",
"rules": [
{
"t": "set",
"p": "topic",
"pt": "msg",
"to": "_original_topic",
"tot": "msg"
},
{
"t": "delete",
"p": "_original_topic",
"pt": "msg"
}
],
"action": "",
"property": "",
"from": "",
"to": "",
"reg": false,
"x": 580,
"y": 80,
"wires": [
[
"cf324d62c6b1f36d"
]
]
},
{
"id": "cefeeea5dd77adb6",
"type": "subflow:e158b68ab8e2e6fa",
"z": "e1108061.bbef",
"name": "",
"env": [
{
"name": "dbFile",
"value": "$HOME/.node-red/sqlite/sensor_data.db",
"type": "str"
}
],
"x": 530,
"y": 740,
"wires": [
[
"d322ccd149700396"
]
]
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment