Database Software Implementation

[hamishmb]

As I'm going to be starting work on this soon, I thought I would make a thread dedicated to it.

I have attached an initial version of the tables I was thinking could be used to achieve what we want. We'll also have some form of access control with users, so that, for example:

• Sumppi can edit the records for its own devices.
• The visitor GUI can't be used to control anything because its use only has read permissions.
• The NAS box can have total control so it can eg free locks when needed.
• Pis cannot take control of devices they aren't supposed to control - for example stagepi can't open gate valve V4 to let water into the sump.

That should be documented somewhere, but I'm not sure where or how.

How does this seem as an initial approach?

[TerryJC]

Your tables seem to cover all the bases (sorry), but time will tell and we may need to add a few things as we go along. Apart from anything else there will obviously be more tables as we add more devices to the system.

The place to document this is the 'WMT River System Software Design Document'. A rather early version of this is in the files server (https://wmtprojectsforum.altervista.org ... les/Design) and a slightly later version may be found in the relevant forum Topic at viewtopic.php?f=5&t=51&start=30#p815. I have a more up to date version on my hard drive, but even that hasn't been changed since December so it really needs updating. I've been holding off publishing it because of all the changes to the software that have been going on.

Once I've completed the current device manufacturing marathon, I'll look towards updating it with details of the database in particular and also the other software that you have been installing on the NAS Box. I will also have to update the algorithms in the Annexes and the associated descriptions in the text and I'll need a bit of help from you to make sure I've fully understood the code and not left anything out.

Good work by the way

[hamishmb]

Ah I see.

I did think that we could probably do with an event log, so I've updated the spreadsheet to include a table for that as well.

Thanks! There's still quite a lot of work ahead, but hopefully the database server will be installed and working in the near future.

[hamishmb]

I've made a start on this today, and have immediately made very good progress. I still have a fair bit to figure out, but all being well we may be able to deploy the NAS box fairly soon. The work isn't pushed to Gitlab yet though, because it's incomplete, not super robust, and has no tests yet.

[hamishmb]

Work on my OU modules is going well so far, and the workload is low this week, so I will have some time to try and finish the database stuff soon hopefully. One question is whether we want to wait until we have the control algorithms (eg the wendy butts and NAS box ones) in place before we deploy the NAS box. After it's deployed, it will be a bit harder for me to test stuff beforehand, though I do have a VM that hosts a database to help with that.

[Penri]

I think we need a little chat about this:
a) to make sure the necessary infrastructure is in place
b) to understand how things can be sorted in the event of an anomaly/ failure
c) ... and I wouldn’t want to deploy it unless we were going to use it

[hamishmb]

Agreed, we should do that soon. We might as well do that at the same time as the other software meeting - it all ties together, and I need to know what we want the control algorithms to be before I can implement them anyway.

[hamishmb]

I have made several more improvements to the database software, including improved argument validation and automated unit tests.

Terry, I'd appreciate it if you could run these at your end for some independent verification that things are working. You can run the following to do this:

Code: Select all

python3 ./unittests.py -c

I'd like to meet soon to discuss what to do next and how to do it. Can you two do Tuesday afternoon?

[hamishmb]

Attached is an updated version of the database design spreadsheet. I quickly realised it would be better from a software and usability standpoint to use Pi IDs than Pi names.

[Penri]

I note that some of the tables don't have either a Tick or Time column, is that deliberate? Surely we'll need one or the other to establish system status as any particular point.