Andras/BoundlessFlowCampus2K
3
6
Fork 0
Code Issues 143 Pull requests 14 Projects Releases Packages Wiki Activity Actions 2

#19-dynamic-table-database #460

Merged
b24johka merged 24 commits from #19-dynamic-table-database into team_1_week_2 2026-04-22 06:53:17 +00:00
Conversation 9 Commits 24 Files changed 10 +386 -133
a24antej commented 2026-04-21 07:45:10 +00:00
Collaborator
Copy link
  • Updated middlewae to dynamicaly create tables with the structure of the data given
  • Updated roombooking to use this new middleware
  • all other services does currently not work, need to create new issues to fix this
  • Guide on how to use this new middleware is in the README file of middleware PLEASE READ THIS
  • No error handling and test is currently implementded, (readroombookingmockup has outcomented code that i used for testing)
  • to properly test everything we need to do it by using the services/mockup
- Updated middlewae to dynamicaly create tables with the structure of the data given - Updated roombooking to use this new middleware - all other services does currently not work, need to create new issues to fix this - Guide on how to use this new middleware is in the README file of middleware PLEASE READ THIS - No error handling and test is currently implementded, (readroombookingmockup has outcomented code that i used for testing) - to properly test everything we need to do it by using the services/mockup
Trying to control how an SQL-table is created/structured based on the data from the objects it will store inside it by having the SQL-table data types be specified in the object itself and from there having the middleware automatically figure out how to create the associated tables.

If this doesn't prove to be a good solution, I have also made the payloads easier to read in the middleware container's log, as well as display the name+datatype of each of the payload's properties. This should make it easier to get a better grasp of what's actually being sent.
(I also fixed some spelling errors that were irking me)
Managed to solve the issue of sending an array in the payload, so now an array of strings defining the SQL-datatypes(char, varchar, DATETIME, etc.) on each object can be used to structure the SQL-query for our database inserts. Only implemented in the Room Service so far.

It doesn't do anything yet except send an additional piece of data in the room service payload, but we can use this piece of data to "dynamically" construct our SQL-queries in the middleware.

(See the test SQL-query string print-out on line 47-58 in roomBookingApiMockup/Program.cs for an example of how the query string could be assembled in the middleware.)
Create table and insert works as intended
can also get data from database, with some modification in the service
added commented out tests to make shure that delete and update function works in middleware
a24sakma reviewed 2026-04-21 09:36:14 +00:00
a24sakma left a comment
Collaborator
Copy link

Looks good with the dynamic table, it makes life easy for re usability later.

However I added a comment about the better error handling and we will have a meeting for clarifying the side effects of this pr and what needs to be done later.

Don't merge this pr until the update after the meeting.

Looks good with the dynamic table, it makes life easy for re usability later. However I added a comment about the better error handling and we will have a meeting for clarifying the side effects of this pr and what needs to be done later. Don't merge this pr until the update after the meeting.
middleware/middleware/Program.cs Outdated
@ -123,1 +169,4 @@
string tablename = Sanitize(topic);
var jpayload = JsonObject.Parse(payload);
string dirtySqlDataTypes = jpayload[0].ToString();
a24sakma commented 2026-04-21 09:32:00 +00:00
Collaborator
Copy link

I would add try catch for InsertMessage, DeleteMessage and updateMessage for better error handling with relevent message like
`
try{
your logic here
}catch(){

Console.WriteLine($"Operation InserMessage from middleware, it failed with error message : {e.Message}");
throw;
}
`

`
try{

your logic here

}catch(){

Console.WriteLine($"Operation DeleteMessage from middleware, it failed with error message : {e.Message}");
throw;
}

`
try{
your logic here

}catch(){

Console.WriteLine($"Operation Update Message from middleware, it failed with error message : {e.Message}");
throw;
}

I would add try catch for InsertMessage, DeleteMessage and updateMessage for better error handling with relevent message like ` try{ your logic here }catch(){ Console.WriteLine($"Operation InserMessage from middleware, it failed with error message : {e.Message}"); throw; } ` ` try{ your logic here }catch(){ Console.WriteLine($"Operation DeleteMessage from middleware, it failed with error message : {e.Message}"); throw; } ` try{ your logic here }catch(){ Console.WriteLine($"Operation Update Message from middleware, it failed with error message : {e.Message}"); throw; }
a24sakma approved these changes 2026-04-21 11:44:03 +00:00
a24sakma left a comment
Collaborator
Copy link

Done review from me! 👍

Done review from me! 👍
a24timsv commented 2026-04-21 12:57:31 +00:00
Collaborator
Copy link

Just a questions for my own curiosity: What is the intent of the UpdateMessage and DeleteMessage? As far as I know, all current mockups only insert into their tables to keep history of values. But what purpose would an update and delete function serve in such a database?

Just a questions for my own curiosity: What is the intent of the UpdateMessage and DeleteMessage? As far as I know, all current mockups only insert into their tables to keep history of values. But what purpose would an update and delete function serve in such a database?
a24antej commented 2026-04-21 13:00:16 +00:00
Author
Collaborator
Copy link

like in roombooking you may want to change the date on an existing booking or delete some bookings.

like in roombooking you may want to change the date on an existing booking or delete some bookings.
a24timsv commented 2026-04-21 13:18:56 +00:00
Collaborator
Copy link

So the idea is to simulate the booking process entirely. Got it. Just wondering if this is related to #43, as it only asks for visualization of booked rooms. Now it might be that Andras is a bit ambiguous in his wording and also means to add the ability to add, change and delete bookings. I assume that all mockup data is to be replaced with real data in the future (hopefully) and if that is the case we might put a lot of time on something that essentially will be replaced with the TimeEdit API.

So the idea is to simulate the booking process entirely. Got it. Just wondering if this is related to #43, as it only asks for visualization of booked rooms. Now it might be that Andras is a bit ambiguous in his wording and also means to add the ability to add, change and delete bookings. I assume that all mockup data is to be replaced with real data in the future (hopefully) and if that is the case we might put a lot of time on something that essentially will be replaced with the TimeEdit API.
Removed some old commented-out code and made the middleware log more readable in the console by segmenting each message. (Just prints a "-----------------------" to make each message distinct)
c24danli commented 2026-04-21 15:52:51 +00:00
Collaborator
Copy link

I have spent some time trying to get the tempsensor to work with the new middleware took some time but got it to work.
The problem i had was i tried to use DUBLE(5,2) as a datatype but in the middleware it became an index problem as it saw the comma as the start of a new datatype.
I havent checked it completly as i was only working on getting the tempSensor to work but will try to look through the rest after a break.

I have spent some time trying to get the tempsensor to work with the new middleware took some time but got it to work. The problem i had was i tried to use DUBLE(5,2) as a datatype but in the middleware it became an index problem as it saw the comma as the start of a new datatype. I havent checked it completly as i was only working on getting the tempSensor to work but will try to look through the rest after a break.
a23timgu commented 2026-04-21 18:19:37 +00:00
Collaborator
Copy link

@c24danli wrote in #460 (comment):

I have spent some time trying to get the tempsensor to work with the new middleware took some time but got it to work. The problem i had was i tried to use DUBLE(5,2) as a datatype but in the middleware it became an index problem as it saw the comma as the start of a new datatype. I havent checked it completly as i was only working on getting the tempSensor to work but will try to look through the rest after a break.

We had some work earlier that used an array to store the different SQL datatypes, but there are issues with trying to send an array through MQTT as the result is still just a string arriving at the destination.
I suppose changing the split function to use another character than ',' to split the datatype string could solve this issue, but we'll have to discuss which character would work best for this. I suspect a character not used by SQL-queries would be best.

@c24danli wrote in https://git.webug.se/Andras/BoundlessFlowCampus2K/pulls/460#issuecomment-4844: > I have spent some time trying to get the tempsensor to work with the new middleware took some time but got it to work. The problem i had was i tried to use DUBLE(5,2) as a datatype but in the middleware it became an index problem as it saw the comma as the start of a new datatype. I havent checked it completly as i was only working on getting the tempSensor to work but will try to look through the rest after a break. We had some work earlier that used an array to store the different SQL datatypes, but there are issues with trying to send an array through MQTT as the result is still just a string arriving at the destination. I suppose changing the split function to use another character than ',' to split the datatype string could solve this issue, but we'll have to discuss which character would work best for this. I suspect a character not used by SQL-queries would be best.
c24danli commented 2026-04-21 21:21:42 +00:00
Collaborator
Copy link

A bit tierd so might be some mistakes but its more of list to talk about tomorrow

Rewiew

Strong points

  • Makes it a lot easier to querry the db
  • Really good Read me file(was needed)
  • More options for later in terms of delete,uppdate

Fixes/uncertainties

middleware\Program.cs

  • don’t mind it but wasn’t switch statements one of the nono’s that we had in software engenering?
    switch (action)
    {
    case "insert":
    await CreateTable(topic, dataBase, payload);
    await InsertMessage(topic, dataBase, payload);
    break;
    case "update":
    await UpdateMessage(topic, dataBase, payload);
    break;
    case "delete":
    await DeleteMessage(topic, dataBase, payload);
    break;
    }

  • Old code left

    • line 206
      // Old sql string
      string sql = $"INSERT INTO {tablename} (payload) VALUES (@payload)";

    • line 212
      cmd.Parameters.AddWithValue("@payload", payload);

    • line 243
      cmd.Parameters.AddWithValue("@payload", payload);

    • line 292

      cmd.Parameters.AddWithValue("@payload", payload);

  • Safety
    not safe for sql injections not a big deal for the data from mockup as we control it, not sure how it will work in parking/bookingroom but if the user can input values say regnumber for car harmful sql injections could be a thing.
    example code:
    secondhalfsql += $"'{jpayload[i + 2]}'";
    if example regnr is inserted by the user ABC123'; DROP TABLE t_sensors_... could be a thing. Probably could be handled in frontend but should it?

  • Uppdate
    Later problem but updates becomes a bit more complex as we will need more data then normally, normally primarykey, tablename and column to chage with value is needed but now we will need sqlDataTypes with matching amount to the columns that are changed or send a complete row.

  • Redability/
    because it uses index/positions instead of names it can become hard to understand intuativly ex:
    string secondhalfsql = $" WHERE {jpayload[1].GetPropertyName()} = '{jpayload[1]}';"
    Or when looping through “Jpayload” could not

foreach (var property in jpayload) 
{ string columnName = property.Key; 
var value = property.Value;

be used instead of
for (int i = 0; i < sqlDataTypes.Length; i++)
As its an Json object with keys and values?
If it works then the problem I had with inserting DECIMAL(5,2) would not be a problem as there would be no need for the string.Split(",").

A bit tierd so might be some mistakes but its more of list to talk about tomorrow ### Rewiew ### Strong points - Makes it a lot easier to querry the db - Really good Read me file(was needed) - More options for later in terms of delete,uppdate ### Fixes/uncertainties _middleware\Program.cs_ - don’t mind it but wasn’t switch statements one of the nono’s that we had in software engenering? switch (action) { case "insert": await CreateTable(topic, dataBase, payload); await InsertMessage(topic, dataBase, payload); break; case "update": await UpdateMessage(topic, dataBase, payload); break; case "delete": await DeleteMessage(topic, dataBase, payload); break; } - Old code left - line 206 ` // Old sql string` `string sql = $"INSERT INTO {tablename} (payload) VALUES (@payload)";` - line 212 ` cmd.Parameters.AddWithValue("@payload", payload);` - line 243 `cmd.Parameters.AddWithValue("@payload", payload);` - line 292 `cmd.Parameters.AddWithValue("@payload", payload);` - Safety not safe for sql injections not a big deal for the data from mockup as we control it, not sure how it will work in parking/bookingroom but if the user can input values say regnumber for car harmful sql injections could be a thing. example code: `secondhalfsql += $"'{jpayload[i + 2]}'";` if example regnr is inserted by the user `ABC123'; DROP TABLE t_sensors_...` could be a thing. Probably could be handled in frontend but should it? - Uppdate Later problem but updates becomes a bit more complex as we will need more data then normally, normally primarykey, tablename and column to chage with value is needed but now we will need sqlDataTypes with matching amount to the columns that are changed or send a complete row. - Redability/ because it uses index/positions instead of names it can become hard to understand intuativly ex: ` string secondhalfsql = $" WHERE {jpayload[1].GetPropertyName()} = '{jpayload[1]}';"` Or when looping through “Jpayload” could not ``` foreach (var property in jpayload) { string columnName = property.Key; var value = property.Value; ``` be used instead of `for (int i = 0; i < sqlDataTypes.Length; i++)` As its an Json object with keys and values? If it works then the problem I had with inserting DECIMAL(5,2) would not be a problem as there would be no need for the `string.Split(",").` 
added sql_datatypers and id to fit middleware
b24johka commented 2026-04-22 06:53:09 +00:00
Collaborator
Copy link

Very detailed review, this will be merged into the team branch.

Very detailed review, this will be merged into the team branch.
b24johka merged commit 98a307ff2c into team_1_week_2 2026-04-22 06:53:17 +00:00
Sign in to join this conversation.
Reviewers
No reviewers
a24sakma
c24danli
Labels
Clear labels   
_CRITICAL_

Only use this label for critical tasks that are project-scope,and must be addressed ASAP.

  
API

   
app

   
backEnd

   
Blocked-waiting-for-further-changes

   
bug

Something is not working

  
bug-only-on-server-for-mobile-not-webpage

   
Bug-Report-After-Merge

   
cleanup

Issues to cleanup the codebase (restructure folders, remove deprecated or smelly code)

  
close

Issue to close

  
design

   
duplicate

This issue or pull request already exists

  
enhancement

New feature

  
feature request

   
frontEnd

   
further-changes-needed

   
future-problem-not-fixint-this-period

   
help wanted

Need some help

  
invalid

Something is wrong

  
last-week-issue-to-fix

   
library

   
low-priority

   
needs input

Issue needs input from product owner or customer

  
needs review

issue is in need of review

  
not-implemented.

   
project documentation

   
question

More information is needed

  
research

   
reviewed

This Issue has been reviewed

  
Script

   
security

use this for security-related issues

  
SQL

  
style

   
Team 1

Team 1 is responsible for this issue

  
Team 2

Team 2 is responsible for this issue

  
team leaders

   
test-creation

   
testing

   
topLevel

Reserved for top-level, project-wide issues.

  
unassigned

   
Under-review

   
wontfix

This won't be fixed

No labels
_CRITICAL_
API
app
backEnd
Blocked-waiting-for-further-changes
bug
bug-only-on-server-for-mobile-not-webpage
Bug-Report-After-Merge
cleanup
close
design
duplicate
enhancement
feature request
frontEnd
further-changes-needed
future-problem-not-fixint-this-period
help wanted
invalid
last-week-issue-to-fix
library
low-priority
needs input
needs review
not-implemented.
project documentation
question
research
reviewed
Script
security
SQL
style
Team 1
Team 2
team leaders
test-creation
testing
topLevel
unassigned
Under-review
wontfix
Milestone
Clear milestone
No items
No milestone
Projects
Clear projects
No items
No project
Assignees
Clear assignees
No assignees
6 participants
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set.

Reference
Andras/BoundlessFlowCampus2K!460
No description provided.