SubFlow to Select data from SQL DB and reformat for ui-chart
This Subflow will select data from a SQL compatible database based on a search string (msg.topic) and number of days History (msg.days). First rev now allows msg.days can include part days to the resolution of a minute (0.000695 days) Optionally it can also modify the Series values using a Regex provided in (msg.match).
Output is provided in msg.payload and can be fed directly to a ui-chart node (ie to initiate with historical data on startup.
Database table (called Parameters) must have at least these three columns (can be relabelled).
TimeStamp : As a Unix timestamp or ISO 8601 string (ie native to Node-red, Javascript) Will be returned as x: values in the output from Flow.
Parameter : A string that represents the series in the Database Will be modifed by msg.match returned as series: values in the output from Flow.
Value : a number Will be returned as y: values in the output from Flow
Database name, Table name and Column names can be change by editing the Flow to match your database
[{"id":"c43b871795b4ab82","type":"subflow","name":"Extract SQL History","info":"This is a subflow that will extract an array of data from a table in s SQL Compatible \r\ndatabase and reformats into a format compatible with preloading into\r\na node-red-dashboard ui-chart.\r\nAn example inject node (disabled in flow) shows a typical message format when entering the\r\nsubflow. They are\r\n 1. msg.topic - This is SQL filter string used to select records based on Parameter column., ie \"House/%/Temp\" where % is SQL wildcard\r\n 2. msg.days - a positive number of days history to be retrieved. ie 1.5 will retrieve the last 36 hours\r\n 3. msg.match - a string with a regex expression including Regex delimiters and any modifiers. ie /\\w+\\/(\\w+)\\/\\w+/i\r\n this will modify the index values to $1 (content of first brackets in Regex)\r\n\r\nDatabase table has at least 3 columns, in this flow these are\r\n The Table is called Parameters\r\n The Columns are\r\n 1. Timestamp, TIme of record in universal format. THis is the x value of output\r\n 2. Parameter, A string with parameter name. This could be the MQTT format like \"House/Dining/Temp\" but can others provided a SQL filter will work. it will be there series values in the output\r\n 3. Value, a Number. this is th Y value of output \r\n\r\nUsers can edit the Flow to match there database, table and Column needs but you need three columns as above for this to work.","category":"","in":[{"x":40,"y":140,"wires":[{"id":"3b920ec478af876b"}]}],"out":[{"x":1040,"y":140,"wires":[{"id":"a81a991345c4f024","port":0}]}],"env":[],"meta":{"module":"Extract SQL for Chart","version":"0.0.1","author":"Ian Harrison","desc":"Passed a msg.topic with the Paramter required from Parameters table and msg.days with number of days history required. Returns msg.payload[]","keywords":"SQL Node-red-dashboard ui-chart"},"color":"#DDAA99"},{"id":"3b920ec478af876b","type":"change","z":"c43b871795b4ab82","name":"Build Select Query for Parameter Table","rules":[{"t":"set","p":"minutes","pt":"msg","to":"msg.days*60*24","tot":"jsonata"},{"t":"set","p":"topic","pt":"msg","to":"\"SELECT Parameter, TimeStamp,Value FROM Parameters WHERE (Parameter LIKE '\"& msg.topic & \"') AND (TimeStamp BETWEEN DATE_ADD( NOW(), INTERVAL -\"& msg.minutes &\" MINUTE) AND NOW()) ORDER BY Parameter, TimeStamp ASC;\"","tot":"jsonata"},{"t":"delete","p":"payload","pt":"msg"},{"t":"delete","p":"qos","pt":"msg"},{"t":"delete","p":"retain","pt":"msg"}],"action":"","property":"","from":"","to":"","reg":false,"x":230,"y":140,"wires":[["67de8c3529aa660f"]]},{"id":"dd0b8805e50b4e23","type":"debug","z":"c43b871795b4ab82","d":true,"name":"SQL request","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":650,"y":240,"wires":[]},{"id":"67de8c3529aa660f","type":"delay","z":"c43b871795b4ab82","name":"","pauseType":"rate","timeout":"5","timeoutUnits":"seconds","rate":"10","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"allowrate":true,"outputs":1,"x":480,"y":140,"wires":[["dd0b8805e50b4e23","ba839e6904e64d57"]]},{"id":"ba839e6904e64d57","type":"mysql","z":"c43b871795b4ab82","mydb":"aa025dd27a4e9d87","name":"","x":700,"y":140,"wires":[["41acb6e50afa21fc","a81a991345c4f024"]]},{"id":"41acb6e50afa21fc","type":"debug","z":"c43b871795b4ab82","d":true,"name":"SQL response","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":860,"y":240,"wires":[]},{"id":"a81a991345c4f024","type":"function","z":"c43b871795b4ab82","name":"function 2","func":"var SQLArray = msg.payload // Array from DB of obects each like \n/* {\n \"Parameter\": \"House/Dining/Temp\",\n \"TimeStamp\": \"2022-09-08T07:45:35.220Z\",\n \"Value\": 22.7\n },*/\nvar data = [];\nvar series = [];\nvar LastParameter = \"\";\nfor (var j in SQLArray){\n var Parameter = SQLArray[j].Parameter ;\n if (Parameter != LastParameter) {\n if (!LastParameter.includes(Parameter)){\n series.push(Parameter);\n data.push([]);\n };\n LastParameter = Parameter \n };\n var i = series.indexOf(Parameter);\n var r = {};\n r.x = SQLArray[j].TimeStamp;\n r.y = SQLArray[j].Value;\n data[i].push(r)\n}\n// if there is a Regex in msg.match then modify each element \n// of series array IAW Regex)\nif ('match' in msg) {\n //msg.match = msg.match.replaceAll(\"\\\\\" ,'\\\\\\\\');\n var modifier = msg.match.replace(/.+\\/(.*)$/, '$1');\n var pattern = msg.match.replace(/\\/(.+)\\/.*$/, '$1');\n var re = new RegExp(pattern, modifier);\n // re = /\\w+\\/(\\w+)\\/\\w+/;\n for (var k in series) series[k] = series[k].replace(re, '$1');\n msg.re = re;\n};\nvar output = {}\noutput.series=series ; \noutput.data = data ;\nmsg.topic = \"\"\nmsg.payload = [] ;\nmsg.payload[0] = output ; \nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":900,"y":140,"wires":[["cdfb5f9e18098e34"]]},{"id":"3e4d278142f43aca","type":"inject","z":"c43b871795b4ab82","d":true,"name":"Example inject","props":[{"p":"topic","vt":"str"},{"p":"days","v":"0.5","vt":"num"},{"p":"match","v":"/\\w+\\/(\\w+)\\/\\w+/i","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"House/%/Temp","x":140,"y":240,"wires":[["3b920ec478af876b"]]},{"id":"cdfb5f9e18098e34","type":"debug","z":"c43b871795b4ab82","d":true,"name":"Result Array","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1110,"y":240,"wires":[]},{"id":"aa025dd27a4e9d87","type":"MySQLdatabase","name":"WombatHollow@ NAS","host":"10.0.0.118","port":"3306","db":"WombatControl","tz":"+10:00","charset":"UTF8"}]
