[Webservice] Restructure database to give more meaningful success/failure information

Description

At present, the success/failure information we send to myMdC is unreliable in various important ways - see calibration/planning#104 . In addition, the correction_complete events sent via Kafka are sent per detector, but they don't actually tell you which detector they relate to (they give a detector type, such as JUNGFRAU, but not the Karabo ID of a specific detector). Replacing the webservice still looks like a big enough job that it seemed worth spending a bit of time improving the old one. I hope this might also ease the transition to the new one when it's ready.

The jobs database is completely redesigned, with three tables instead of one: each request from myMdC can have several executions, each of which can have several slurm_jobs. I think these terms line up with what Robert is using in Orca. I haven't tried to do any migration of existing data - we're only using it to track active jobs, so I'm assuming we'll just start with an empty database when deploying this change.

The jobs monitor code is also largely refactored, with the loop broken up into several methods in a JobsMonitor class.

How Has This Been Tested?

Deployed on max-exfl017, and submitted correction & dark requests from the test myMdC instance for the CALLAB proposal.

This revealed a number of previously hidden issues which are (hopefully) specific to CALLAB. The presence of JNGFR01 in the filenames triggers an execution for both FXE_XAD_JF1M and SPB_IRDA_JF4M, at least one of which will fail on any given run (as it can't find its data). Previously, the webservice would hide this, because the failing jobs would fail quickly, and the status would only come from the last jobs running, but with these changes the failures are visible.

Relevant Documents (optional)

https://in.xfel.eu/test_metadata/proposals/259/runs/63612

https://in.xfel.eu/test_metadata/proposals/259/runs/86193

https://in.xfel.eu/test_metadata/proposals/259#proposal-calibration

Types of changes

• Bug fix (non-breaking change which fixes an issue)
• New feature (non-breaking change which adds functionality)
• Refactor (refactoring code with no functionality changes)

Checklist:

• My code follows the code style of this project.

Reviewers

@ahmedk @roscar @schmidtp

webservice/job_monitor.py

@@ -111,9 +111,6 @@ class JobsMonitor:
 
     def do_updates(self):
         ongoing_jobs_by_exn = self.get_updates_by_exec_id()
-        # ^ dict grouping statuses of unfinished jobs by execution ID:
-        # {12345: ['R-5:41', 'PD-0:00', ...]}
-        # Newly completed executions are present with an empty list.
 
         # For executions still running, regroup the jobs by request
         # (by run, for correction requests):
@@ -144,7 +141,13 @@ class JobsMonitor:
         for req_id in reqs_finished:
             self.process_request_finished(req_id)
 
-    def get_updates_by_exec_id(self):
+    def get_updates_by_exec_id(self) -> dict:
+        """Get statuses of unfinished jobs, grouped by execution ID
+
+        E.g. {12345: ['R-5:41', 'PD-0:00', ...]}
+
+        Newly completed executions are present with an empty list.
+        """
         c = self.job_db.cursor()
         c.execute("SELECT job_id, exec_id FROM slurm_jobs WHERE finished = 0")
 
@@ -202,7 +205,7 @@ class JobsMonitor:
             self.mymdc_update_dark(mymdc_id, msg)
 
     def process_execution_finished(self, exec_id):
-        """Send notification that one execution has finished"""
+        """Send notification & record that one execution has finished"""
         statuses = [r[0] for r in self.job_db.execute(
             "SELECT status FROM slurm_jobs WHERE exec_id = ?", (exec_id,)
         ).fetchall()]
@@ -238,7 +241,7 @@ class JobsMonitor:
         return r['req_id']
 
     def process_request_finished(self, req_id):
-        """Send notifications that a request has finished"""
+        """Send Kafka notifications and update myMDC that a request has finished."""
         krb_id_successes = {r[0]: r[1] for r in self.job_db.execute(
             "SELECT karabo_id, success FROM executions WHERE req_id = ?",
             (req_id,)
@@ -283,6 +286,7 @@ class JobsMonitor:
             self.mymdc_update_dark(r['mymdc_id'], msg, status)
 
     def mymdc_update_run(self, run_id, msg, status='R'):
+        """Update correction status in MyMdC"""
         data = {'flg_cal_data_status': status,
                 'cal_pipeline_reply': msg}
         if status != 'R':
@@ -293,6 +297,7 @@ class JobsMonitor:
             log.error(Errors.MDC_RESPONSE.format(response))
 
     def mymdc_update_dark(self, dark_run_id, msg, status='IP'):
+        """Update dark run status in MyMdC"""
         data = {'dark_run': {'flg_status': status,
                              'calcat_feedback': msg}}
         response = self.mdc.update_dark_run_api(dark_run_id, data)