Store logs and frames to database

[jorpic]

This is a preliminary implementation of storing various stuff to database.

• [x] log to database
• [x] test logging to db
• [x] store TMFrames
• [ ] store TMFrames with correct types
• [ ] replay TMFrames from DB
• [ ] test TMFrames storing and replaying
• [ ] store and replay other types of records
• [ ] update README.md

esa-db package now exports two functions:

• withDatabaseLogger :: FilePath -> LogLevel -> (LogFunc -> m b) -> m b
  • Opens database at the specified path.
  • Wraps an IO action in the same way as RIO's withLogFunc.
  • Provides LogFunc that saves logs to the database.
• databaseSink :: (MonadResource m, Table' a) => FilePath -> ConduitT a o m ()
  • Polymorphic conduit that just stores incoming records to database.
  • Table' is a wrapper class for selda-storable types.

The code relies on SQLite to handle multithreaded interactions with DB. So there is no single shared DB connection but both functions create new connections (and close them carefully afterwards).

Storing TMFrames uses incorrect datatypes for now. I'm working on pull-requests to sqilte-direct and selda to add support for Word-types.

Questions

1. What is the correct way to store SunTime in database? Is it ok to use timeToWord64 and store it as Word64?

2. I am not sure if my changes to config are appropriate (see https://github.com/jorpic/AURIS/commit/e55090c5fc598a0755c7afdd6576488c18d91542):

   • Database file name is stored in AurisConfig.
   • It is then prepended with configPath and stored to GlobalStorage so it can be accessed from anywhere in esa-space-protocols to create new databaseSink.
   • I suppose there will be a bunch of flags like cfgStoreTMFrames in Data.PUS.Config to configure which datatypes should be persisted (TMFrames, PUS packets, Parameter Values).

3. How TFrame replaying is supposed to work?

• Should it be some kind of config params or command line switches that disable listening to TCP sockets and enable fetching from DB? or maybe it should be initiated from UI?

• Should replaying be parametrized by something? E.g. replay only records with earth reception time in specified range.

• Should the relative order of records be preserved when replaying different kinds of data? E.g. if TFrame f1 received through TCP connection before PUS packet p1 should they be replayed from DB in the same order or it does not matter?

[oswald2]

This is great news!

If you think, it would be easier to work with a different library than selda (because of the missing data types), we can also change that. It was at the first implementation, that Paolo and Matthias discovered the missing types, that's why I neglected the db interface a bit. Also, Paolo raised a request to selda (at least if I remember correctly). If they are still not there, we may reconsider the choice of the db library. It just should be possible to use both SQLite and Postgres (SQLite more for testing sessions when using the systems in assembly, integration and testing of a satellite and Postgres for more operational use in flying a mission).

Here for your questions:

What is the correct way to store SunTime in database? Is it ok to use timeToWord64 and store it as Word64?

Yes.

I am not sure if my changes to config are appropriate (see jorpic@e55090c):

Database file name is stored in AurisConfig.

For the moment this is ok. I would propose to create a new config structure in esa-db itself (which could possibly be a sum type in the case other databases (e.g. postgres) need different configuration parameters, and put it as a single field into AurisConfig like it is done with PUSConfig

It is then prepended with configPath and stored to GlobalStorage so it can be accessed from anywhere in esa-space-protocols to create new databaseSink.

Correct.

I suppose there will be a bunch of flags like cfgStoreTMFrames in Data.PUS.Config to configure which datatypes should be persisted (TMFrames, PUS packets, Parameter Values).

Actually, this is depending on the MIB. Generally, all TM Frames are stored all the time. The reason is, this is done before parameter extraction with data from the MIB, so in case the MIB is changed and the data needs to be reprocessed, especially the TM Frames are the raw source data. But a config for storing PUS Packets makes absolute sense, as they cost additional storage and time and mostly only TM Packets and TC Packets (next higher protocol layer) are interesting.

But there are config files present in the MIB, which specify some filtering options, but currently these files are not read in and therefore not used. Anyway, this will also be changeable via the GUI (there is an own Settings/Control page for TM, TC and also a page for DB admin tasks (import, export, delete old data etc). So these filters and information are not static configuration, but dynamic. Therefore, they will end up in the AppState data structure, as this is intended for dynamic settings.

How TFrame replaying is supposed to work? Should it be some kind of config params or command line switches that disable listening to TCP sockets and enable fetching from DB? or maybe it should be initiated from UI? Should replaying be parametrized by something? E.g. replay only records with earth reception time in specified range.

It is initiated from the UI. Basically, we need a possibility for general retrieval which can then be used for multiple tasks (e.g. initiating a retrieval of historical data for analysis from a TM display, which is the most common task). A replay is then just a retrieval of historical data which is fed into the processing pipeline again instead of being sent to the UI. Currently, only replay of TM Frames makes sense, for PUS Packets only the retrieval for a historical display is foreseen. For the replay, the UI will provide the possibility to first delete the time range of PUS Packets selected for replay (as the packets would otherwise be stored as duplicates), so there needs to be also a delete request with the same selection criteria as for the retrieval.

For TM Frames there are the following possibilities:

• Time range: will be present every time. Time range can be: [from,to] which means ERT of the TM Frame >= from and <= to, in case from is not specified it means from the beginning in the database and in case to is not specified it means until latest in database
• Virtual Channel (optional): a list of virtual channel IDs (VCID), or all if list is empty
• Frame Count (optional): a range of the virtual channel frame count (VCFC), again a [from,to] interval similar to the time range
• Master Channel Frame Count (optional): the master channel frame count is increased for all frames (though still 8 bit and therefore frequently overruns) while each virtual channel have their own count. Same as for VCFC, but for the MCFC header field.

For PUS Packets it is quite similar, but there are subtle differences:

• Time range: this can be either for the source time of the packet (timestamp) or the ERT, analogous to the TM Frames. The ERT is the earth reception time, which is the timestamp of the TM Frame that contained the PUS Packet. The timestamp of the PUS packet is the on-board timestamp of the clock on board of the satellite when the packet was created (values were sampled). Naturally, these differ by the so called propagation delay and clock drift of the spacecraft, so for the PUS Packet, this needs also to be stored. Generally, in the code you will often see that the PUSPacket is wrapped in a ExtractedDU, which provides the metadata from the lower protocol level (in this case information from the TM Frame like ERT). I don't remember if I have written that last time, but basically, for PUS Packets in reality the data of an ExtractedDU PUSPacket needs to be stored.
• APID (optional): like the VC ID for TM Frame, a list of APIDs or all APIDs when empty
• Type (optional): the service type of the packet, again a list of values (8 bit unsigned), or all when empty
• SubType (optional): the service sub-type of the packet, again a list of values (8 bit unsigned), or all when the list is empty
• Source Sequence Count (optional): a range of [from,to] for the 16 bit unsigned sequence number. If present, both from and to needs to be specified.

A PUS Packet can be identified by the tuple (APID, Type, SubType, SSC) and sometimes the PI1 and PI2 values are needed, but on the PUS Packet level we don't have the information of PI1 and PI2 values (these are only present one level higher on TM Packet level).

I think we should get this working first, before thinking about TM Packets.

Should the relative order of records be preserved when replaying different kinds of data? E.g. if TFrame f1 received through TCP connection before PUS packet p1 should they be replayed from DB in the same order or it does not matter?

It is very hard to determine an order between different protocol layers as they have their own timestamp which are all based on different clocks. Therefore, normally only TM Frames are replayed as they are the lowest protocol level. I currently would not foresee a replay for PUS Packets from the DB. What is probably needed (but this is rather easy to implement) is additional replay from a file. Ground stations record the raw TM Frames in files which are then transferred per SFTP to the mission control center and a replay of this file is possible. But this is simply then using a file conduit and injecting the frames into the processing queue. This could be also done for raw packet files and in this case, can only be done exclusively for either TM Frames or PUS Packets.

Later then, for TM Packets (next higher protocol layer) it will get more involved as they already contain extracted parameters, which need to be stored separately. There are some issues with storing these, as the parameters are basically sum types across several datatypes with several possibilities to store them, but this is probably for a different discussion. Also, there needs to be a reference from the parameter values to the TM Packet which contained them. The thing is, I am not sure how to implement this in a performant way (this is a critical path).

• Normally you would store the TM Packet, get the (automatic) ID of the record and put it as a foreign key reference into the parameter values extracted from this packet when storing in the DB. You then have foreign key integrity, but you need to wait for the first store of the packet and then pass the values back up to the application layer so that the application also has a possibility to reference the packet where the parameters came from.
• Another possibility would be to to keep the references on the application layer in the data as e.g. an UUID generated for the TM Packet and then just put the UUID also into the extracted TM parameters as well. Then, they can be immediately used on the application layer without having to wait on the DB, but most probably the foreign key integrity is lost, as the stores could be asynchronous.

The store performance was always a major point in the systems and can never be fast enough. Therefore, currently, I am more leaning towards the second solution, but I am not sure. Probably it is best to measure both and then decide.

[oswald2]

Merged into jorpic-esa-db, starting to integrate...