Database Software Implementation

[hamishmb]

Good point. The status table is supposed to be for "right now" so not an issue. We could stick a message in the event log when status changes.

[hamishmb]

NB: The *Control tables are all also meant to be what's happening right now, but a message will be put in the event log when anything changes in those tables.

[hamishmb]

I'm in the process of amending the database spec to allow for extra features, so the NAS box and GUIs will be able to request:

• Updates to be installed to the river system software to be installed (perhaps from a pre-defined location on the NAS box, accessible over the network).
• Individual Pis to reboot.
• Individual pis to shut down.
• Manual control (which will mean the Pi ignores all requests except from the Staff GUI).

Implementation will be a separate issue, but can anyone think of more requests we may want to handle? I'll upload the database table design v4 shortly.

EDIT: Uploaded to https://wmtprojectsforum.altervista.org ... 0Box/Specs

Hamish

[TerryJC]

Hamish.

As mentioned last night, I think we might need a get-together of some kind for this, because I think I need to bounce ideas off people. My biggest concern with this Table is how we deal with times. Some time ago we agreed to adopt my idea of using a System Tick instead of a full Date Stamp to define the point at which a reading is taken. We subsequently agreed that the System Tick should be incremented by the NAS Box rather than SumpPi.

Whichever device generates the Tick, it has to be related to a point in time in some way. My original idea was that if the shortest interval between readings was 15s (which I believe it currently is), then the NAS Box would increment the System Tick every 15s. This number would be stored in the database as a Read-only Record along with the true date-stamp at the time. Then as the Readings come in the measured values would be stored together with the current System Tick value.

What this does is to group all readings into segments. We know that none of the readings can possibly arrive at the exact same time, due to differences in Pi system clock values, network lags and database queuing, but when the results are displayed in the form of a chart, it will be possible to tie the measurements together with a time that corresponds to when the System Tick was generated.

For example, if the current System Tick is 23646927 and the associated date-stamp is 2020-05-06 10:01:05, then all readings that arrive between 10:01:05 and 10:01:20 will have that Tick value and any that arrive at 10:01:21 will have the next value; 23646928. When the graph is drawn, all the readings that came in between 10:01:05 and 10:01:20 will appear at the same point on the X Axis and the later one at the next point and so on. This will allow Staff and Visitors to get an understanding of the state of the River in it's entirety for any time with a resolution of 15s.

Does this make sense? Should we call an online Meeting to discuss it? I may have other comments later, but at the moment I'd like to get this sorted.

[hamishmb]

That does make sense, I remember the discussion. I then proposed that we use the sockets code to send the system tick to the pis, in order to propagate it faster and reduce load on the database from the pis having to query it rapidly. Either approach should work, but I feel using the sockets code may produce better results.

[TerryJC]

As you say, either way should work, but the System Tick Value that appears on the NAS Box should have the associated date-stamp. Would you envisage that the individual Pis would then read the current System Tick Value and send their results to the database, or that the individual Pis would send their results to the database and the NAS Box would include the System tick value when it writes the records?

[hamishmb]

Good idea, hadn't thought of that. Perhaps the NAS box should add the system tick, as you suggest. This would mean it'd have to keep track of which tick was for which time window, though, so the pi's reading the value themselves and then putting it in the database is probably simpler.

[hamishmb]

Maybe a video chat with you, me, and other interested people would be a better way to go. Then one of us could write our discussion up and put in the meetings forum.

[TerryJC]

Yes. I'll email Penri after lunch to make sure that he's seen this.

[Penri]

Hello

A video chat at 16:30 this afternoon I could do. Would that be OK for everyone else interested?


Penri