Add status for MR and SAD phasing

[olofsvensson]

The AutoProcStatus table is used for tracking the status of the automatic data processing pipelines. We would now need a similar table / mechanism for tracking the status of MR and SAD phasing pipelines.

[rhfogh]

Without knowing anything about the details, I wonder if it might be possible/sensible to use something like a single, shared ProcessingPipelineTable instead of one for each type of processing?

[olofsvensson]

I think you are right Rasmus, however, it would mean quite some refactoring. I guess it depends on how much manpower is available for refactoring given the developments of the new backend.

[KarlLevik]

At Diamond we now use the AutoProcProgram columns processingStatus tinyint(1) (NULL (not started/unknown), 0 (failure), 1 (success)) and processingMessage varchar(255) to indicate the current status for all processing pipelines (including MR and phasing), although this doesn't give us the "status history" for a particular processing job.

Solange's comment on #32 makes it sound like you're already using AutoProcProgram instead of PhasingProgramRun, so I guess the ESRF are sort of also doing what we're doing (just with the addition of AutoProcStatus).

There's an interesting feature (SQL Standard) in MariaDB 10.3+ which allows you to add history to tables such as AutoProcProgram, see: https://mariadb.com/kb/en/temporal-data-tables/

[stufisher]

Yes, please use AutoProcProgram, we already have one duplicate of this table (PhasingProgram), lets not add another.

We need to add the fk from AutoProcProgram to datacollection to make it more useful, the link via AutoProcIntegration is not useful for other types of processing

See https://github.com/ispyb/ispyb-database-modeling/issues/32

[olofsvensson]

Hi @rhfogh , @KarlLevik and @stufisher , I just had a zoom meeting with Solange, Max and Gianluca (@antolinos couldn't participate), here are our conclusions:

• We agree to move from PhasingProgram/Attachment to AutoProcProgram/Attachment. However, as we don't want to make any major data base changes before the startup (early May?), we suggest to start using AutoProcProgram/Attachment for phasing by adding a new column 'autoProcProgramId' to the PhasingStep table. Some extra logic then will be needed on the EXI/EXI2 side.

• The 'processingMessage' column in AutoProcProgram is not sufficient as we would like to keep a history of the status messages. Therefore we suggest to detach AutoProcStatus from AutoProcIntegration and attach it to AutoProcProgram by changing the AutoProcStatus column 'autoProcIntegrationId' to 'autoProcProgramId'. We will then update the content of this column by writing a script that for each autoProcIntegrationId translates it into a autoProcProgramId. @KarlLevik , your idea to add history to a table sounds interesting but we feel that the suggestion above is simpler to implement.