JDBC Device Registry - DeviceRegistrations Table: Add mapping table for gateways to allow search & filter mechanisms

[julian-sotec]

Background Information: Hono supports gateways that are very useful in scenarios where the device itself cannot communicate through honos standard adapters.
However: As the device registrations SQL table schema using JSON operators might be sufficient for basic use cases and SQL queries - more advanced queries and filters to device properties stored in its data field are very complex and therefore also inefficient.
Example: Finding out a device is a gateway currently involves going into the data field of a device and checking a json structure for the "via" attribute.

That being said our suggestion would be introducing a mapping table containing the information if a device is a gateway - allowing easier queries and access to the gateway information. The mapping table schema will look like this:

ID	TenantID	GatewayID	DeviceID
1	1	1	2
2	1	1	3

In addition this kind of schema improvements will be also applied to the viaGroups and memberOf attributes.

Since this change will also involve migration work for existing users of the gateway feature in jdbc use cases we also plan to provide a migration script that would typically run either automatically or manual.

[harism]

Bear in mind that via attribute for devices is not the only way to use certain devices as gateway devices. There is also gateway groups marked with device attributes memberOf and viaGroup which makes certain devices act similarly as gateway devices. If there is plans to create separate table for via information maybe the two other gateway group attributes could be addressed the same time with a bit more flexible table(s) structure and not make a solution for this one attribute only?

[julian-sotec]

Bear in mind that via attribute for devices is not the only way to use certain devices as gateway devices. There is also gateway groups marked with device attributes memberOf and viaGroup which makes certain devices act similarly as gateway devices. If there is plans to create separate table for via information maybe the two other gateway group attributes could be addressed the same time with a bit more flexible table(s) structure and not make a solution for this one attribute only?

Thank you for your Input on this. I briefly mentioned what you described in the Issue above. We will defenetly take this into consideration. If you have any suggestion on a table schema on viaGroup / memberOf - feel free to share it with us!