The best way to implement device database

aargollo · 19 January 2019 20:05

Hi Guys, I´m very happy to post here!
I working to building a IoT gateway, using Rpi3 and Zigbee serial interface. I Already have important parameters got from Zigbee end devices, like nwkshort address, IEEE address, EP, and ModelID, all those got from specific Zigbee messages and storage in the global variables.

To implement persistence, I believe that need to working with data base and after It I´ll working to implement another components, like to JSon API.

I would like to receive advise what the best data base to working, and If is possible I would like to receive links to study it?

Best Regards,
Alex

ukmoose · 20 January 2019 09:15

With v0.19 you can store global variables so they are available after restarts see https://nodered.org/docs/user-guide/context
especially the section on context stores

aargollo · 20 January 2019 11:08

Hi Ukmoose, I´m already working with global variable.
My IoT gateway will have some devices that have dynamic association in two stages:

1st - attaching procedure, in this moment, gateway and devices will discovery the important parameter (ShortAdd, IEEEadd, EP, ModelID and etc.)
2st - status stage, the gateway will send command to devices. For example if the exchange device is an actuator (like Light switch), the gateway, will use the parameters got in attaching stage, to send a command Command (ON or OFF).

The parameters will be stored in the database table or in the file for use in the status stage.
Do you can advise the bast database to do it?

Colin · 20 January 2019 11:57

I think that @ukmoose was pointing out that global vars can automatically be saved to persistent storage (a file for example) so all you need to do is to enable that and they will automatically be saved. There may therefore be no need for a database.

aargollo · 20 January 2019 14:39

Hi @Colin and @ukmoose , tks!
I have doubts how can I need to make a structured file to storage the parameters to use after it in the command structure, for example:

Device File Example
node_name, nwk_shortAdd, nwk_ieeeAdd, EP
RWL020, 3340, 00178801021a83e8, 02

All those parameter above I'll available to send the command when necessary.

Please, they can share w/ me more information how can I structure the device file to persist the parameters?

Best Regards,
Alex

Colin · 20 January 2019 14:58

Why not save them in persistent context as suggested? Why do you want the extra work of saving a file yourself?
If you really do want a file then convert the data to json and save a json file.

aargollo · 20 January 2019 15:54

Colin,
I build a parser to get parameters during 1st stage (attaching procedure) and than I didn´t construct a code structure to support multiples devices during attaching in the same time, for each attached device I have the same global variable. To get around this, I have in my mind before persist in file check if I have device storage in the file, to do it I´ll use the unique address (IEEE address). Before storage the my code need to have a structure to check if device have in the file and than if do not have include it.

Would you can share a best article to explain well how to build JSON structure?

Colin · 20 January 2019 16:10

The Json node will convert a javascript object to a json string.
An alternative would be an sqlite database.

aargollo · 20 January 2019 16:39

Hi Guys, I have progress to working with JSON structure in a file, using JSON node and File Node as they can see in the 2st part of my code below :

	if (debug==1){
         let data={"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
         node.log(typeof data);
         msg.payload=JSON.stringify(data);
         return msg;
     }else{
											    
         let d = new Date();
         let t =d.getTime();
         payload={"Time":t,"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
         msg.payload=payload;
         //msg.filename='home/pi/HomeGateway/nwkFilestorage.txt'
        return msg;
    }

The next step: need to check before to write on file if Device exists, and than if exist the device must not be written to the file!

I'll post here my improvements!
best regards,
Alex

drmibell · 21 January 2019 03:18

As @ukmoose and @Colin suggest, you probably don't really NEED a file. Where you may have caused yourself a problem is

If each device is described by an object containing its parameters (properties), you can just create an array of those objects. Then it is easy to add or delete devices, search the array to see whether a particular device has already been attached, and so on. The array can be saved in a persistent global variable each time it is modified and retrieved when needed. No file needed.

aargollo · 21 January 2019 10:14

Hi drmibell, thanks a lot.
Thanks for your answer, I really want to do as you, Colin and ukmoose said!
I can´t understanding how to do it without a file, my code got the parameter (properties) from each device in the same variable, during attaching procedure. I using different nodes as you can see bellow:

For example, you can see in part of the code bellow how can i got Nwk Short Address:

 if (msg.payload[9]!=2){
                  if (msg.payload[10]!=2) {
                              let nwkaddrs1 = msg.payload[9].toString(16);
                              let nwkaddrs2 = msg.payload[10].toString(16);
                              global.set('NWKAd1',nwkaddrs1);
                              global.set('NWKAd2',nwkaddrs2);

Please, do you can advise me how to add JSON objects in the memory without persist it in the file?

ukmoose · 21 January 2019 10:48

Have you checked what version of Node-RED you are using?
If it is v0.19.x have you edited your settings.js fie change contextStorage to localfilesystem?

(see my first reply The best way to implement device database)

If you have have you tried running your flow (in order to write the global context ) and then restarting Node-RED and seeing if you can access it?

drmibell · 21 January 2019 15:51

The code fragment and partial flow you have posted pretty much confirm my suspicion that you are struggling with a self-inflicted problem. If the incoming msg.payload contains all or most of the information related to the device being registered, then a single function node should extract those parameters and create an object describing the device. That same function can get the array of devices from context storage, check to see if the new device is already registered and so on. I can see why you might have wanted to break the process apart and check that you are extracting the parameters correctly, but now I think you need to put the pieces back together.

aargollo · 21 January 2019 16:10

Hi @ukmoose sorry for that! I´ll study more about context!
I wrote your answer to below divided in two parts.

a) My Node-Red is v0.19.4. and I´m not changed nothing in my settings.js, the default original settings is:
//contextStorage: {
// default: {
// module:"localfilesystem"
// },
I need to change it to localfilesystem?
b) I restarted my node-red and than I got success to access global variables.

My challenge is to study in global context and understanding how to handling multiples devices in global variables, without to persist it in file.

Best Regards,
Alex

JayDickson · 21 January 2019 16:16

It looks like you're storing each piece of data as its own object in context. It may be of some benefit to encode it in a manner in in such that each node on your zigbee mesh gets its own object and each piece of data exists as a property of the object.

aargollo · 21 January 2019 16:21

Hi @drmibell,
Thanks.
I have put the pieces back in the last node (JSON Parameters Persistence) in my flow, as you can see part of my code inside this node bellow:

        }else if (msg.payload[1]==129){
            
            switch (msg.payload[2]){
                 case 2:{
  
                        /* Análise do xxxx */
                        if (msg.sliced.length >=1){
                            let nwkaddrs1 = global.get('NWKAdS1');
                            let nwkaddrs2 = global.get('NWKAdS2');
                            let nwkaddr1 = global.get('NWKAd1');
                            let nwkaddr2 = global.get('NWKAd2');
                            let ep1s = global.get('EP1s');
                            let ep2s = global.get('EP2s');
                            let ep1 = global.get('EP1');
                            let ep2 = global.get('EP2');
                            let I3E1=global.get('IEEE1');
							let I3E2=global.get('IEEE2');
							let I3E3=global.get('IEEE3');
							let I3E4=global.get('IEEE4');
							let I3E5=global.get('IEEE5');
							let I3E6=global.get('IEEE6');
							let I3E7=global.get('IEEE7');
							let I3E8=global.get('IEEE8');
							let I3EAdd = +I3E1+I3E2+I3E3+I3E4+I3E5+I3E6+I3E7+I3E8;
							let I3Eb1=global.get('IEEEb1');
							let I3Eb2=global.get('IEEEb2');
							let I3Eb3=global.get('IEEEb3');
							let I3Eb4=global.get('IEEEb4');
							let I3Eb5=global.get('IEEEb5');
							let I3Eb6=global.get('IEEEb6');
							let I3Eb7=global.get('IEEEb7');
							let I3Eb8=global.get('IEEEb8');
							let I3EAdds = +I3Eb1+I3Eb2+I3Eb3+I3Eb4+I3Eb5+I3Eb6+I3Eb7+I3Eb8;
                            let ModelID = msg.sliced;
                            global.set('ZCLModelID',ModelID);
                            	if (debug==1){
                                                let data={"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
                                                node.log(typeof data);
                                                msg.payload=JSON.stringify(data);
                                                return msg;
											}else{
											    
											    let d = new Date();
											    let t =d.getTime();
											    payload={"Time":d,"ModelID":ModelID,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1":ep1,"IEEEAddS":I3EAdds,"NetworkAdd1S":nwkaddrs1,"NetworkAdd2S":nwkaddrs2,"EP1S":ep1s};
                                                msg.payload=payload;
                                                //msg.filename='home/pi/HomeGateway/nwkFilestorage.txt'
                                                return msg;
											}
                            
                            
                        
                        } else{
                            msg.payload="ERROR: Network elements ";
                        }

My challenge is storage multiple devices, as object, using global context in memory using my code. I have in my mind that my JSON array, in memory, something like:

"{ModelID":**DEVICE1**,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1",ModelID":**DEVICE2**,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1,ModelID":**DEVICEn**,"IEEEAdd":I3EAdd,"NetworkAdd":nwkaddr1+nwkaddr2,"EP1}

JayDickson · 21 January 2019 17:16

Here's something to play around with for getting stuff formatted into a context variable:

[{"id":"84c463eb.44b728","type":"inject","z":"460ed46c.d01004","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":380,"y":260,"wires":[["c946e83b.64cec8"]]},{"id":"6842bf84.7986c8","type":"debug","z":"460ed46c.d01004","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":830,"y":260,"wires":[]},{"id":"c946e83b.64cec8","type":"change","z":"460ed46c.d01004","name":"","rules":[{"t":"move","p":"payload","pt":"msg","to":"payload.name","tot":"msg"},{"t":"set","p":"payload.prop1","pt":"msg","to":"property1","tot":"str"},{"t":"set","p":"payload.prop2","pt":"msg","to":"prop2","tot":"str"},{"t":"set","p":"payload.prop3","pt":"msg","to":"property 3","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":540,"y":260,"wires":[["ac15f9ab.5163e8"]]},{"id":"ac15f9ab.5163e8","type":"function","z":"460ed46c.d01004","name":"","func":"var unique = String(msg.payload.name);\nvar prop1 = msg.payload.prop1;\nvar prop2 = msg.payload.prop2;\nvar prop3 = msg.payload.prop3;\n\n\nvar properties = {\n \"prop1\":prop1, \n \"prop2\":prop2, \n \"prop3\":prop3,\n};\n\nnode.send({\n \"uniqueIdentifier\":unique,\n \"properties\":properties});\nflow.set(unique,\n {\"uniqueIdentifier\":unique,\n \"properties\":properties});","outputs":1,"noerr":0,"x":710,"y":260,"wires":[["6842bf84.7986c8"]]}]

It will obviously need tailoring to your particular use case, but as it stands it can be made to generate a new variable every time a new unique identifier is encountered, but update the properties every time an already seen UID is seen again.

16%20PM 04%20PM

Note, that it could be a hell of a lot more elegant. This was bashed together between taking service calls at the office.

aargollo · 22 January 2019 09:30

Tks JayDikson,
I starting to working with context variable, as you can see bellow:
I have change my node to get IEEEaddr, that will be working as uniqueIdentifier.

Part of my code to create the UniqueID:

payload={"uniqueIdentifier":I3EAdd};
msg.payload=payload;
return msg;

My change node config.:

My debug log:

But, when I changed the function before change node to construct UniqueID with IEEEaddr and dont get another parameters, as I did before, I cant see anymore the variables in Context Data.
I believe that I need to change config parameters in settings.js. I´m right? Do you can help me?

Best Regards,
Alex

JayDickson · 22 January 2019 14:55

payload={"uniqueIdentifier":I3EAdd};
msg.payload=payload;
return msg;

It looks like your entire payload is just an object with "uniqueIdentifier" as the key and whatever the variable "I3Add" is defined as. We need to see more of your function to be able to tell you much more. Also, can you expand that object in your context store so we can see the properties and any sub objects?

JayDickson · 22 January 2019 17:16

Here, I did some cleanup on that function. Hopefully this makes a little more sense as I was taking lots of unnecessary steps. (Including having a redundant unique identifier within the variable itself.) I've also tweaked it to use msg.topic as the variable identifier to be a little more in line with other conventions.

//*********************************************************
//this section defines what will be stored in the variable 
//"out". This is an object (defined with the curly brackets)
out=
{
    "properties":{
        "prop1":msg.payload.prop1, 
        "prop2":msg.payload.prop2, 
        "prop3":msg.payload.prop3,
    }
};
//*********************************************************


//*********************************************************
//here we send the entire message, preserving any inbound data
node.send(msg);
//*********************************************************


//*********************************************************
//this bit actually stores the data in the "out" object to a flow variable, ignoring the rest of the message
//the inbound msg.topic determines the flow variable name used
flow.set(String(msg.topic),out)
//*********************************************************

Topic		Replies	Views
Store data on a database General	17	10574	13 March 2020
Persistent variables? General	13	776	22 February 2021
Storing energy data General	52	3535	5 October 2019
Reload global variables after deploy/restart Core Development	10	8917	28 January 2019
0.19 Node Red roadmap General	30	3150	13 August 2018

The best way to implement device database

Related Topics