FILO database to support no network scenarios

Hi community,

I need a DB that when the Internet connection goes out, the flow automatically stores information in the DB.
When the internet recovers the flow should dump the information in a First in Last out order.
Any ideas or welcome or paths to take :smiley:

PS: This implementation is going to be used to plot data in Thingsboard, therefore i need to figure out how timestamping my data works to have graphs that the plots the data in the "real-time" when received dumped data received of the flow.

Do you really mean first in last out, which is the opposite order that they were added in?
Why do you want a database rather than a persistant array, for example?

1 Like

You are right a FIFO would meet, although the timestamp is crucial.
An array would do also...i would need at least 3 days of data, but i guess it wouldn´t be a problem

I have a flow for that, will sort it out a bit later.

Firstly, for the timestamp, you can feed the message through a Function node containing something like

msg.payload.timestamp = (new Date()).getTime()
return msg

Which will add a milliseconds timestamp into the payload. Or if you don't want it in the payload use msg.timestamp = (new Date()).getTime()

Coincidentally I was earlier today tidying up this flow ready to put it on the flows site. This solves the very similar problem of queuing emails for sending. If the internet is up they are sent, but if there is a problem they are queued until they can be sent. The flow needs node-red-contrib-simple-gate and node-red-contrib-queue-gate so you will need to install those if you aren't using them already.

The top group box simulates where messages are generated ready for processing, for this test it is just a couple of Inject nodes passing messages to the link to the main flows.
The second box is the processing section. You should replace that with your flow that attempts to send the data off to wherever it has to go. If that succeeds then send a message to the Success link (it doesn't matter what is in the message) if it fails then send a message to the Fail link. On success then the flow will automatically be passed the next item in the queue if there is one. On fail the message will be left in the queue and it will try again later.
The third box is the queueing logic. Don't touch that, except for two things:

  1. Setup the retry interval in the Retry Inject node. If configured for, say, 1 minute then if there is anything in the queue then every minute it will try processing the next one in the queue.
  2. In the q-gate node you can configure it to persist the queue over a node-red restart or a power down. To do that you will need to configure persistent context storage as described here.

Let me know if it is of any use and, if you try it, about any issues you find so that I can sort those before publishing it.

[{"id":"89da67b3.6f5df8","type":"link out","z":"d7a63188.caf3f8","name":"","links":["7b82d42f.790bc4"],"x":655,"y":400,"wires":[]},{"id":"714e3fa4.977eb8","type":"link in","z":"d7a63188.caf3f8","name":"","links":["d40f6199.ca6928"],"x":95,"y":320,"wires":[["8dfe0985.65fe38"]]},{"id":"277f80ea.c70558","type":"link out","z":"d7a63188.caf3f8","name":"","links":["26da4505.fbcb1a"],"x":655,"y":500,"wires":[]},{"id":"76f96e3e.445448","type":"comment","z":"d7a63188.caf3f8","name":"Success","info":"","x":740,"y":400,"wires":[]},{"id":"b3a20653.5090f","type":"comment","z":"d7a63188.caf3f8","name":"Fail","info":"","x":730,"y":500,"wires":[]},{"id":"d587ee1b.29f8c","type":"link out","z":"d7a63188.caf3f8","name":"Email In","links":["bc690602.401968"],"x":595,"y":240,"wires":[]},{"id":"114812dc.83d695","type":"comment","z":"d7a63188.caf3f8","name":"Pass messages to be sent to this link, Email In","info":"","x":190,"y":240,"wires":[]},{"id":"385944c.7d9ff3c","type":"link in","z":"d7a63188.caf3f8","name":"Email In","links":["f7fd6e04.79cd7"],"x":415,"y":240,"wires":[["d587ee1b.29f8c"]]},{"id":"8ecf963e.8177e","type":"group","z":"d7a63188.caf3f8","name":"Queueing Logic","style":{"label":true},"nodes":["e2c4363f.8d9858","7b82d42f.790bc4","17e5b203.2e05a6","a956ee68.132138","a0ff8bf9.07b3c","d40f6199.ca6928","26da4505.fbcb1a","adb41c42.5ebb58","a9d7fb1c.90e5d8","9cecbdb5.a12018","bc690602.401968","342c4f70.587fd","781040cd.92e308","d520fb3.f05bc88","a95d58b9.e5eaa"],"x":54,"y":539,"w":732,"h":362},{"id":"e2c4363f.8d9858","type":"inject","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Retry 1 minute","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"60","crontab":"","once":false,"onceDelay":0.1,"topic":"control","payload":"peek","payloadType":"str","x":180,"y":760,"wires":[["a95d58b9.e5eaa"]]},{"id":"7b82d42f.790bc4","type":"link in","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"","links":["89da67b3.6f5df8"],"x":135,"y":640,"wires":[["17e5b203.2e05a6"]]},{"id":"17e5b203.2e05a6","type":"function","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"get next","func":"node.send({topic: \"control\", payload: \"drop\"})\nmsg.topic = \"control\"\nmsg.payload = \"peek\"\nreturn msg;","outputs":1,"noerr":0,"x":280,"y":640,"wires":[["781040cd.92e308"]]},{"id":"a956ee68.132138","type":"gate","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"","controlTopic":"gate_control","defaultState":"open","openCmd":"open","closeCmd":"close","toggleCmd":"toggle","defaultCmd":"default","persist":false,"x":710,"y":760,"wires":[["781040cd.92e308"]]},{"id":"a0ff8bf9.07b3c","type":"change","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Open gate","rules":[{"t":"set","p":"topic","pt":"msg","to":"gate_control","tot":"str"},{"t":"set","p":"payload","pt":"msg","to":"open","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":440,"y":860,"wires":[["a956ee68.132138"]]},{"id":"d40f6199.ca6928","type":"link out","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"","links":["714e3fa4.977eb8"],"x":675,"y":620,"wires":[]},{"id":"26da4505.fbcb1a","type":"link in","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"","links":["277f80ea.c70558"],"x":135,"y":860,"wires":[["a0ff8bf9.07b3c"]]},{"id":"adb41c42.5ebb58","type":"status","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"q-gate status","scope":["781040cd.92e308"],"x":190,"y":800,"wires":[["a9d7fb1c.90e5d8"]]},{"id":"a9d7fb1c.90e5d8","type":"function","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Queue empty?","func":"// expects msg.status.text in form \"queuing: <number>\"\n// sends a message if queue is empty\nif (parseInt(msg.status.text.split(\":\")[1]) !== 0) {\n    msg = null\n}\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":400,"y":800,"wires":[["a0ff8bf9.07b3c"]]},{"id":"9cecbdb5.a12018","type":"comment","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Set this to appropriate retry interval","info":"","x":230,"y":720,"wires":[]},{"id":"bc690602.401968","type":"link in","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"","links":["d587ee1b.29f8c"],"x":335,"y":680,"wires":[["a95d58b9.e5eaa","781040cd.92e308"]]},{"id":"342c4f70.587fd","type":"comment","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Leave this alone except for setting retry interval and Persistence in email q-gate","info":"","x":350,"y":580,"wires":[]},{"id":"781040cd.92e308","type":"q-gate","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"q-gate","controlTopic":"control","defaultState":"queueing","openCmd":"open","closeCmd":"close","toggleCmd":"toggle","queueCmd":"queue","defaultCmd":"default","triggerCmd":"trigger","flushCmd":"flush","resetCmd":"reset","peekCmd":"peek","dropCmd":"drop","statusCmd":"status","maxQueueLength":"100","keepNewest":false,"qToggle":false,"persist":false,"storeName":"memoryOnly","x":510,"y":620,"wires":[["d520fb3.f05bc88","d40f6199.ca6928"]]},{"id":"d520fb3.f05bc88","type":"function","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Close gate","func":"//node.send(msg)      // this clones the message\n// Close gate with new message so as not to corrupt the original in the queue\nreturn({topic: \"gate_control\", payload: \"close\"})","outputs":1,"noerr":0,"initialize":"","finalize":"","x":510,"y":700,"wires":[["a956ee68.132138"]]},{"id":"a95d58b9.e5eaa","type":"function","z":"d7a63188.caf3f8","g":"8ecf963e.8177e","name":"Peek","func":"// Send peek command with new message so as not to corrupt the original in the queue\nreturn({topic: \"control\", payload: \"peek\"})","outputs":1,"noerr":0,"initialize":"","finalize":"","x":450,"y":760,"wires":[["a956ee68.132138"]]},{"id":"ad7bc74.9606538","type":"group","z":"d7a63188.caf3f8","name":"This is probably off in another tab somewhere, linking to this tab","style":{"label":true},"nodes":["c168d86.dc52c28","d8e840f0.feaa6","f7fd6e04.79cd7"],"x":74,"y":79,"w":386,"h":122},{"id":"c168d86.dc52c28","type":"inject","z":"d7a63188.caf3f8","g":"ad7bc74.9606538","name":"test email 1","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"Test message from tigger","payload":"Test message 1","payloadType":"str","x":180,"y":120,"wires":[["f7fd6e04.79cd7"]]},{"id":"d8e840f0.feaa6","type":"inject","z":"d7a63188.caf3f8","g":"ad7bc74.9606538","name":"test email 2","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"Test message from tigger","payload":"Test message 2","payloadType":"str","x":180,"y":160,"wires":[["f7fd6e04.79cd7"]]},{"id":"f7fd6e04.79cd7","type":"link out","z":"d7a63188.caf3f8","g":"ad7bc74.9606538","name":"","links":["385944c.7d9ff3c"],"x":395,"y":140,"wires":[]},{"id":"c9ace4ce.f68ff8","type":"group","z":"d7a63188.caf3f8","name":"Send email, pass message to success or fail outputs","style":{"label":true},"nodes":["8dfe0985.65fe38","1f3a9acd.76111d","5195765a.402ca8","ebe672e8.9507b8","5c5a8e5f.9496e","89253195.c7a538","b3fbfbec.98eca"],"x":134,"y":279,"w":452,"h":242},{"id":"8dfe0985.65fe38","type":"e-mail","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","server":"some server","port":"465","secure":true,"tls":false,"name":"someone@somewhere.com","dname":"email","x":270,"y":360,"wires":[]},{"id":"1f3a9acd.76111d","type":"complete","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"","scope":["8dfe0985.65fe38"],"uncaught":false,"x":220,"y":440,"wires":[["ebe672e8.9507b8","89253195.c7a538"]]},{"id":"5195765a.402ca8","type":"catch","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"","scope":["8dfe0985.65fe38"],"uncaught":false,"x":210,"y":480,"wires":[["5c5a8e5f.9496e","277f80ea.c70558"]]},{"id":"ebe672e8.9507b8","type":"debug","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"OK","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":440,"wires":[]},{"id":"5c5a8e5f.9496e","type":"debug","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"FAIL","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":490,"y":480,"wires":[]},{"id":"89253195.c7a538","type":"delay","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"Limit send rate 1 every 20 secs","pauseType":"delay","timeout":"20","timeoutUnits":"seconds","rate":"1","nbRateUnits":"1","rateUnits":"second","randomFirst":"1","randomLast":"5","randomUnits":"seconds","drop":false,"x":430,"y":400,"wires":[["89da67b3.6f5df8"]]},{"id":"b3fbfbec.98eca","type":"comment","z":"d7a63188.caf3f8","g":"c9ace4ce.f68ff8","name":"Messages to be sent come in here","info":"","x":290,"y":320,"wires":[]}]
1 Like

This seems wonderfully built and weirdly exactly what I was looking for :smiley:
I will defenitly explore it and report back, maximum in 2 days.
Great work!

I took a look on your flow and cannot figure out how does the email node triggers the q-gate to open or buffer the messages.
My application will send messages via HTTP post, therefore i need to send them if the post goes or buffer them if it fails.
My node-red skills keep improving but i havent figured out your flow hahahaha

A message sent to the Success link goes the 'get next' function which removes the previous message (which has just been processed) from the queue and releases the next one, if there is one.
A message sent to the Fail link goes to the 'Open Gate' node that opens the 'gate' node which then allows the next trigger from the Retry Inject node to release the same message from the queue to try again.
All you need to do to handle your http post is to send a message to the OK or Fail link dependent on whether the post succeeded or failed. You don't need to buffer it if it fails as it is already in the q-gate.
Note that you should be sending your messages to this link
image
from where they go into the q-gate waiting to be handled (which will be immediately if there is nothing in the queue already).
The q-gate operates in a mode where messages are 'peeked' which means they are sent for processing but are not removed from the queue, until the Success event occurs, at which time the processed message is dropped from the queue and the next one peeked.

Thank you for the explanation!
I got it, although with the HTTP request it needed some kind of filter in the "complete" node since the HTPP request would always send there something, triggering the gate to open.
I put a json converver that would parse anything in the error mode. Although i don´t really like it...need something more sexy than not parsing beacuse of an error.
Anyway thank you very much!

Can you show us what you mean, there may be a better way of solving the problem. Do you mean that when there is an error it generates an output in the Catch and Complete nodes? If so then if you export and paste just the flows for the catch and complete I will take a look.

I see you are correct, when there is an error the Complete node still fires, which seems inconsistent with the e-mail node. Would a solution be to just check the status code from the http node or is that not sufficient for what you want? If so then you can just do this

image

The Switch node being configured with:

image

Exactly, the HTTP makes those nodes send in both occasions to send a message from the complete node triggering the gate to be open at all times.

I tried this you just posted and it is just perfect. configuring as in the picture makes it to work for when it doesn't find the address specifed(no internet)
rule
The rest of the flow is the same as you posted.

Checking for empty payload should be ok provided you are sure you never validly get an empty payload from your requests. If you think that might happen then the solution of not using the Catch node but just using Complete and checking the status code might work better.

1 Like

I´ve checked and it doesn´t in my case!

@Colin
I´ve been testing the flow.
Just to put in context this flow sens POST http to a server.
I´ve been testing the scenario where the server goes offline and after 2 h it goes online, testing if the gate stores and then sends when it goes online.
My issue is that it detects the server OFFLINE but when I goes online the HTTP / complete node doesn´t react to the change.
I´ve been fidling around but had no luck

Do you mean you have not worked out how to signal the OK message when it is successful?

That was sorted out.
The thing is that the HTTP node when there is no network sends an ERROR (which is great) also when the domain cant be reached it sends an error message that triggers the gate.
The problem is that the gates when the domain(my server) is back online, don't trigger the gate to open again, because no message is outputted by the HTTP node.
It´s a weird behavior.

Connect debug nodes showing what is going into the http node, and what comes out on the Complete and Catch nodes and what comes out from the http node itself.

Edit: and show us how you have linked up the the OK and FAIL links

Certainly this is how i have the block.

and this is the error when it can´t connect to the server.

After this message is shown, even with the server up the gate never opens without a flow restart.

What are you sending to OK and FAIL when there is a failure and when it succeeds?