Architecture help for store and forward deployment

[Jeffrey Osborne]

Hi all, am looking for some suggestions in creating an architecture on the conduit. To summarize, the gateways will aggregate data from nearby sensors (or mDots), and then push this so a remote server only after a predetermined time. As an example, we collect data every 10 minutes but transmit it to a remote server only every 60 minutes. What I’m thinking of doing is as follows:

1) Hosting either a local file or mySQL database on the conduit (unsure how to do the later though) where data is stored

2) After a predetermined time, read the file or database and push that data to a remote server

3) Delete information in the locally stored file/database.

Anyone have suggestions on this architecture? Specifically, I’m unsure if I can host a mySQL database on the conduit (looking into that now). I also don’t quite understand MQTT and if that has relevance for what I’m doing right now. Thanks for any help!

[Jeff Hatch]

Jeffrey,

There is a bitbake recipe for MySQL, so it is probably possible to compile MySQL for the Conduit and install the resulting .ipk file. I am not sure how well MySQL will perform on the Conduit hardware.

I have used a flat file composed of JSON collections in the past as a database for custom applications and other things on the Conduit. That might be a path to use. You could use Python to implement the JSON parsing.

Jeff

[Peter Ferland]

You can also try using python with sqlite. You would just need the package python-sqlite3 to get started (ie, opkg install python-sqlite3 )

[Jeffrey Osborne]

I was able to get something working using a locally hosted node-red application (i.e. on my PC, not the multitech). I was using mySQL and some flow.* variables to save variables while the flow is deployed. I have included my flow below, you would need to update the mysql nodes to include your own database though.

I don’t know anything yet about hosting the sqlite on the conduit. I will take a look at this and report back if I get anywhere with it.

[{"id":"8b281012.0959f","type":"inject","z":"4b270a81.bcaf04","name":"Initialize","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":true,"x":123,"y":97,"wires":[["d4e0a72c.869818"]]},{"id":"d4e0a72c.869818","type":"function","z":"4b270a81.bcaf04","name":"Initialize Inject Counter","func":"var injects = 0; \nflow.set('injects', injects); \n\nvar DeviceID = flow.get('DeviceID')||[]; DeviceID = []; flow.set('DeviceID', DeviceID); \nvar currentDate = flow.get('currentDate')||[]; currentDate = []; flow.set('currentDate', currentDate);\nvar temperature = flow.get('temperature')||[]; temperature = []; flow.set('temperature', temperature); ","outputs":1,"noerr":0,"x":345,"y":97,"wires":[[]]},{"id":"2a322e48.cc4862","type":"function","z":"4b270a81.bcaf04","name":"Remove Arrays","func":"var injects = 0; \nflow.set('injects', injects); \n\nvar DeviceID = flow.get('DeviceID')||[]; DeviceID = []; flow.set('DeviceID', DeviceID); \nvar currentDate = flow.get('currentDate')||[]; currentDate = []; flow.set('currentDate', currentDate);\nvar temperature = flow.get('temperature')||[]; temperature = []; flow.set('temperature', temperature); \n\nmsg.DeviceID = DeviceID; \nmsg.currentDate = currentDate; \nmsg.temperature = temperature; \nmsg.output = \"Data sent\"; \n\nreturn msg; \n","outputs":1,"noerr":0,"x":1036,"y":392,"wires":[["1ea9446f.aa50dc","e4f0e7a7.eb2228","e2633138.fa57d","1934a010.f1b6b"]]},{"id":"e1f4b6bd.cb4098","type":"function","z":"4b270a81.bcaf04","name":"Create Readings","func":"function getRandomArbitrary(min, max) {\n return Math.random() * (max - min) + min;\n}\n\n//DEFINE VARIABLES//\nvar NumDevices = 2; \ninjects = flow.get('injects'); \nDeviceID = flow.get('DeviceID'); \ncurrentDate = flow.get('currentDate'); \ntemperature = flow.get('temperature'); \n\n//CREATE NEW DATABASE ENTIRES//\nfor (i = (injects-1)*NumDevices; i < (injects*NumDevices); i++){\n DeviceID[i] = i - (injects - 1)*NumDevices; \n currentDate[i] = new Date().toISOString().slice(0, 19).replace('T', ' '); \n temperature[i] = getRandomArbitrary(20, 30); \n}\n\nflow.set('DeviceID', DeviceID);\nflow.set('currentDate', currentDate); \nflow.set('temperature', temperature); \n\nmsg.injects = injects; \nmsg.DeviceID = DeviceID; \nmsg.currentDate = currentDate; \nmsg.temperature = temperature; \n\nmsg.output = \"Measurement Taken\"; \n\nreturn msg; ","outputs":1,"noerr":0,"x":600,"y":156,"wires":[["1062d6a8.56e869","3c001f50.b38bf","c16c3b08.d90fc8","e22ccd12.d980a","c6b000.edba6"]]},{"id":"e6d5ba6b.0cdbf8","type":"inject","z":"4b270a81.bcaf04","name":"Inject Readings","topic":"","payload":"","payloadType":"date","repeat":"10","crontab":"","once":false,"x":149,"y":155,"wires":[["f19ae38b.b7faa"]]},{"id":"f19ae38b.b7faa","type":"function","z":"4b270a81.bcaf04","name":"Create Variables","func":"injects = flow.get('injects'); injects += 1;\nflow.set('injects', injects); \nmsg.injects = injects; \n\n\nreturn msg; ","outputs":1,"noerr":0,"x":367,"y":156,"wires":[["e1f4b6bd.cb4098"]]},{"id":"3029d034.10ee1","type":"inject","z":"4b270a81.bcaf04","name":"TX Injection","topic":"","payload":"","payloadType":"date","repeat":"60","crontab":"","once":false,"x":132,"y":295,"wires":[["29f93eb9.019da2"]]},{"id":"1062d6a8.56e869","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"injects","x":829,"y":74,"wires":[]},{"id":"521ad365.803dbc","type":"function","z":"4b270a81.bcaf04","name":"Query Last ID","func":"msg.topic = \"SELECT ID FROM sensorsimulation.SensorReadings ORDER BY id DESC limit 1\";\n\nreturn msg; \n\n\n ","outputs":1,"noerr":0,"x":563,"y":292,"wires":[["2c3ecc94.e5e634"]]},{"id":"d937690f.938518","type":"function","z":"4b270a81.bcaf04","name":"Prepare Payload","func":"var ID = msg.payload[0][\"ID\"] + 1; \n\n//SET VARIABLES//\nvar DeviceID = flow.get('DeviceID'); \nvar currentDate = flow.get('currentDate'); \nvar temperature = flow.get('temperature'); \nvar i = flow.get('i'); \n\n//CREATE PAYLOAD//\nmsg.topic = \"INSERT INTO SensorReadings (ID, DeviceID, TimeStamp, Reading) VALUES (?,?,?,?)\";\nmsg.payload = [ID, DeviceID[i], currentDate[i], temperature[i]];\n\nmsg.i = i;\n\nif (i == DeviceID.length - 1){\n var stop = flow.get('stop'); stop = 1; flow.set('stop', stop); //Sets stop = 1 if reach end of array\n}else{\n i += 1; flow.set('i', i); //Else, increase i\n}\n\nmsg.stop = flow.get('stop'); \n\nreturn msg; ","outputs":1,"noerr":0,"x":954,"y":291,"wires":[["6b27963b.116a78","78331081.e7d21","507726d7.057028","135c0b89.087044"]]},{"id":"6b27963b.116a78","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"payload","x":1190,"y":174,"wires":[]},{"id":"3c001f50.b38bf","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"temperature","x":847,"y":123,"wires":[]},{"id":"29f93eb9.019da2","type":"function","z":"4b270a81.bcaf04","name":"Initialize Loop Counter","func":"var i = 0; flow.set('i', i); \n\nvar stop = 0; flow.set('stop', stop); //Stop the loop indicator\n\nmsg.i = i; \n\nreturn msg; \n","outputs":1,"noerr":0,"x":332,"y":295,"wires":[["521ad365.803dbc","57b8aece.1987d"]]},{"id":"57b8aece.1987d","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"i","x":539,"y":236,"wires":[]},{"id":"78331081.e7d21","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"i","x":1168,"y":129,"wires":[]},{"id":"dbb58db.43f537","type":"switch","z":"4b270a81.bcaf04","name":"Exit Loop Check","property":"stop","propertyType":"flow","rules":[{"t":"eq","v":"0","vt":"num"},{"t":"eq","v":"1","vt":"str"}],"checkall":"true","outputs":2,"x":822,"y":387,"wires":[["521ad365.803dbc"],["2a322e48.cc4862"]]},{"id":"507726d7.057028","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"stop","x":1180,"y":225,"wires":[]},{"id":"c16c3b08.d90fc8","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"DeviceID","x":838,"y":169,"wires":[]},{"id":"e22ccd12.d980a","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"currentDate","x":846,"y":213,"wires":[]},{"id":"1ea9446f.aa50dc","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"temperature","x":1242,"y":392,"wires":[]},{"id":"e4f0e7a7.eb2228","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"DeviceID","x":1235,"y":437,"wires":[]},{"id":"e2633138.fa57d","type":"debug","z":"4b270a81.bcaf04","name":"","active":false,"console":"false","complete":"currentDate","x":1243,"y":481,"wires":[]},{"id":"c6b000.edba6","type":"debug","z":"4b270a81.bcaf04","name":"","active":true,"console":"false","complete":"output","x":834,"y":256,"wires":[]},{"id":"1934a010.f1b6b","type":"debug","z":"4b270a81.bcaf04","name":"","active":true,"console":"false","complete":"output","x":1223,"y":525,"wires":[]},{"id":"2c3ecc94.e5e634","type":"mysql","z":"4b270a81.bcaf04","mydb":"","name":"","x":747,"y":301,"wires":[["d937690f.938518"]]},{"id":"135c0b89.087044","type":"mysql","z":"4b270a81.bcaf04","mydb":"","name":"","x":1171,"y":281,"wires":[["dbb58db.43f537"]]}]

[Author]

Posts