Skip to content

Instantly share code, notes, and snippets.

@Jeremywu0109

Jeremywu0109/SQLite-Node-RED.json

Created October 25, 2021 15:22
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 Jeremywu0109/c79f8fc420808397004af15791b08ce6 to your computer and use it in GitHub Desktop.
Save Jeremywu0109/c79f8fc420808397004af15791b08ce6 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQLite-Node-RED.json
[
{
"id": "b6b5f313a70d78c2",
"type": "tab",
"label": "流程3",
"disabled": false,
"info": ""
},
{
"id": "b630ef5a75522127",
"type": "sqlite",
"z": "b6b5f313a70d78c2",
"mydb": "3656b1cad8af426d",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 360,
"y": 160,
"wires": [
[
"bfa060c037d3467d"
]
]
},
{
"id": "988cce207f735e27",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "Create DB",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "CREATE TABLE RANDOMNUM( TIMESTAMP INT PRIMARY KEY NOT NULL, VALUE INT NOT NULL, BOOL INT NOT NULL)",
"payloadType": "date",
"x": 130,
"y": 160,
"wires": [
[
"b630ef5a75522127"
]
]
},
{
"id": "bfa060c037d3467d",
"type": "debug",
"z": "b6b5f313a70d78c2",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 670,
"y": 160,
"wires": []
},
{
"id": "8d8bcdbe315da1a4",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "INSERT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "",
"payloadType": "date",
"x": 140,
"y": 280,
"wires": [
[
"f693e20eca66f073"
]
]
},
{
"id": "f693e20eca66f073",
"type": "function",
"z": "b6b5f313a70d78c2",
"name": "INSERT",
"func": "var randonNum = Math.round(Math.random()*100);\nvar largeBool = (randonNum>50)?1:0;\nvar newMsg={\n \"topic\":\"INSERT INTO RANDOMNUM VALUES (\"+msg.payload+\", \"+randonNum+\", \"+largeBool+\")\"\n}\nreturn newMsg;",
"outputs": 1,
"noerr": 0,
"initialize": "",
"finalize": "",
"libs": [],
"x": 350,
"y": 280,
"wires": [
[
"4b0ce2c050f84823"
]
]
},
{
"id": "4b0ce2c050f84823",
"type": "sqlite",
"z": "b6b5f313a70d78c2",
"mydb": "3656b1cad8af426d",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 490,
"y": 280,
"wires": [
[
"09d9c194f97452e9"
]
]
},
{
"id": "09d9c194f97452e9",
"type": "debug",
"z": "b6b5f313a70d78c2",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 670,
"y": 280,
"wires": []
},
{
"id": "5eb07c09aafbfbb3",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "Delete 30s",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('Xs',''now, '-30 seconds')*1000",
"payloadType": "date",
"x": 130,
"y": 380,
"wires": [
[
"823e612a8b706fcf"
]
]
},
{
"id": "c5bb9027a4ec63e7",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "Delete 24h",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('Xs',''now, '-24 hours')*1000",
"payloadType": "date",
"x": 130,
"y": 440,
"wires": [
[
"823e612a8b706fcf"
]
]
},
{
"id": "0214170f04de658c",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "Delete 7d",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "DELETE FROM RANDOMNUM WHERE TIMESTAMP <= strftime('Xs',''now, '-7 days')*1000",
"payloadType": "date",
"x": 130,
"y": 500,
"wires": [
[
"823e612a8b706fcf"
]
]
},
{
"id": "823e612a8b706fcf",
"type": "sqlite",
"z": "b6b5f313a70d78c2",
"mydb": "3656b1cad8af426d",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 360,
"y": 440,
"wires": [
[
"8469e806745b7fba"
]
]
},
{
"id": "8469e806745b7fba",
"type": "debug",
"z": "b6b5f313a70d78c2",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 670,
"y": 440,
"wires": []
},
{
"id": "ad476fd2655985eb",
"type": "sqlite",
"z": "b6b5f313a70d78c2",
"mydb": "3656b1cad8af426d",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 370,
"y": 580,
"wires": [
[
"9b05aa809bc9a06f"
]
]
},
{
"id": "ab62e3a2bfe8b5fe",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "Update DB",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "UPDATES RANDOMNUM SET BOOL = 1 WHERE VALUE > 80 AND BOOL = 0",
"payloadType": "date",
"x": 140,
"y": 580,
"wires": [
[
"ad476fd2655985eb"
]
]
},
{
"id": "9b05aa809bc9a06f",
"type": "debug",
"z": "b6b5f313a70d78c2",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 680,
"y": 580,
"wires": []
},
{
"id": "d2bade4a2412f240",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "COUNT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "SELECT COUNT(*) FROM RANDOMNUM",
"payloadType": "date",
"x": 120,
"y": 640,
"wires": [
[
"ad476fd2655985eb"
]
]
},
{
"id": "d022fee029398c11",
"type": "inject",
"z": "b6b5f313a70d78c2",
"name": "SELECT",
"props": [
{
"p": "payload"
},
{
"p": "topic",
"vt": "str"
}
],
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"topic": "SELECT * FROM RANDOMNUM ORDER BY TIMESTAMP DESC LIMIT 100",
"payloadType": "date",
"x": 130,
"y": 740,
"wires": [
[
"c4adf23516aa77e6"
]
]
},
{
"id": "c4adf23516aa77e6",
"type": "sqlite",
"z": "b6b5f313a70d78c2",
"mydb": "3656b1cad8af426d",
"sqlquery": "msg.topic",
"sql": "",
"name": "",
"x": 370,
"y": 740,
"wires": [
[
"d7e1496c028874a0"
]
]
},
{
"id": "d7e1496c028874a0",
"type": "debug",
"z": "b6b5f313a70d78c2",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"statusVal": "",
"statusType": "auto",
"x": 720,
"y": 740,
"wires": []
},
{
"id": "3656b1cad8af426d",
"type": "sqlitedb",
"db": "hbh",
"mode": "RWC"
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment