Dynamic typing converter from the incoming sensory data to the database

Andras commented

2026-03-27 16:03:08 +00:00

Owner

Right now, a table is created in the sensors database for each sensor that comes in via MQTT.

The created database table includes a timestamp and a payload.

We need a solution to create the table with all the necessary fields for the incoming data: i.e., determine what kind of data it is and create columns with the correct data types.

May we need some form of a Strategy design pattern to handle this?

Right now, a table is created in the sensors database for each sensor that comes in via MQTT. The created database table includes a timestamp and a payload. We need a solution to create the table with all the necessary fields for the incoming data: i.e., determine what kind of data it is and create columns with the correct data types. May we need some form of a Strategy design pattern to handle this?

Andras added the

feature request

label

2026-03-27 16:03:08 +00:00

f21marfo referenced this issue

2026-04-16 13:57:09 +00:00

#298-update-delete-middleware #349

a24antej referenced this issue

2026-04-16 14:06:54 +00:00

#298-update-delete-middleware #349

a24antej was assigned by b24johka

2026-04-17 07:35:03 +00:00

a23timgu commented

2026-04-17 14:23:51 +00:00

Collaborator

We've done some research on this today and we've come up with some ideas that we're gonna experiment with over the weekend.

a23timgu commented

2026-04-20 00:27:22 +00:00

Collaborator

I've made some progress(or experiments rather, if you prefer to call it that), but I'm not entirely sure how we want to proceed with this.

I spent some time trying to have the data objects themselves control the SQL query by storing an array of strings that would declare what data type each property of the objects would have in the database.
The same thing could also be done by just using a single string and then having the middleware split it up and combine that string with the properties into an SQL-query.

Or, if we're not too picky about the datatypes we want to use in the database, we could just have each datatype on the objects (bool, string, int) correspond to a reasonably fitting SQL datatype. There could be some minor issues with this like how to handle DATETIME, but that could possibly be handled in the middleware.

I've also updated the middleware to output more information about the payload(s), which could prove useful when working on this area. (see picture)

Maybe I've completely misunderstood the issue. If so, please enlighten me.

I've made some progress(or experiments rather, if you prefer to call it that), but I'm not entirely sure how we want to proceed with this. I spent some time trying to have the data objects themselves control the SQL query by storing an array of strings that would declare what data type each property of the objects would have in the database. The same thing could also be done by just using a single string and then having the middleware split it up and combine that string with the properties into an SQL-query. Or, if we're not too picky about the datatypes we want to use in the database, we could just have each datatype on the objects (bool, string, int) correspond to a reasonably fitting SQL datatype. There could be some minor issues with this like how to handle DATETIME, but that could possibly be handled in the middleware. I've also updated the middleware to output more information about the payload(s), which could prove useful when working on this area. (see picture) Maybe I've completely misunderstood the issue. If so, please enlighten me.

payload.JPG

92 KiB