AutoProcProgramAttachment: new column 'primary' + add enum option 'Debug'

[KarlLevik]

We'd like to propose the following change to the AutoProcProgramAttachment table:

ALTER TABLE AutoProcProgramAttachment
    MODIFY `fileType` enum('Log','Result','Graph', 'Debug') DEFAULT NULL COMMENT 'Type of file Attachment',
    ADD `primary` tinyint(1) COMMENT 'Indicate whether the attachment is the primary one for the particular autoProcProgramId and fileType'; 

The idea is to have only one single 'primary' attachment for each autoProcProgramId and fileType.

The purpose of these changes is to make it possible to indicate to the user which attachments are the most important ones as some softwares now apparently produce up to 75 attachments per run.

[CV-GPhL]

Could one enhance that from a binary flag (as far as I understand it) to an enumerator? I.e. instead of having multiple "Log" attachments with one being flagged as "primary", just give each of them a ranking (1,2,...,N). The user can then decide to only look at rank 1 (would be similar to the primary flag proposed above) or maybe at the top 5 ranking attachments. For Log attachments it is probably quite easy to provide a single (primary, rank 1) attachement - but for "Result" I would already struggle and for "Graph" it seems impossible.

[KarlLevik]

This was discussed at the VC meeting today, and we agreed that Clemen's (@CV-GPhL ) improvement idea was sound - i.e. instead of a 'primary' boolean use a 'rank' integer. It will be up to the processing software and ingester to make sure we only have one single entry with rank 1 for each autoProcProgramId and fileType.

I'll create a PR for this at some point.

[KarlLevik]

Updated SQL:

ALTER TABLE AutoProcProgramAttachment
    MODIFY `fileType` enum('Log','Result','Graph', 'Debug') DEFAULT NULL COMMENT 'Type of file Attachment',
    ADD `importanceRank` tinyint unsigned COMMENT 'For the particular autoProcProgramId and fileType, indicate the importance of the attachment. 1 is most important, higher numbers are less important';

I've renamed the new column to importanceRank as that seems a bit clearer than just rank.

[drnasmith]

I think it would be more flexible to have importance increasing?

So 2 more important than 1?

Then you can always decide later that a new result is “better” by providing an incremented value.

From: Karl Levik notifications@github.com Sent: 26 June 2019 16:47 To: ispyb/ispyb-database-modeling ispyb-database-modeling@noreply.github.com Cc: Subscribed subscribed@noreply.github.com Subject: Re: [ispyb/ispyb-database-modeling] AutoProcProgramAttachment: new column 'primary' + add enum option 'Debug' (#43)

Updated SQL:

ALTER TABLE AutoProcProgramAttachment

MODIFY `fileType` enum('Log','Result','Graph', 'Debug') DEFAULT NULL COMMENT 'Type of file Attachment',

ADD `importanceRank` tinyint unsigned COMMENT 'For the particular autoProcProgramId and fileType, indicate the importance of the attachment. 1 is most important, higher numbers are less important';

I've renamed the new column to importanceRank as that seems a bit clearer than just rank.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/ispyb/ispyb-database-modeling/issues/43?email_source=notifications&email_token=AH4ATGEDX33SCK4CTPXEQN3P4OFPLA5CNFSM4HB75DHKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODYT64FA#issuecomment-505933332, or mute the threadhttps://github.com/notifications/unsubscribe-auth/AH4ATGDBVMB36DPSUFZFXHLP4OFPLANCNFSM4HB75DHA.

-- This e-mail and any attachments may contain confidential, copyright and or privileged material, and are for the use of the intended addressee only. If you are not the intended addressee or an authorised recipient of the addressee please notify us of receipt by returning the e-mail and do not use, copy, retain, distribute or disclose the information in or attached to the e-mail. Any opinions expressed within this e-mail are those of the individual and not necessarily of Diamond Light Source Ltd. Diamond Light Source Ltd. cannot guarantee that this e-mail or any attachments are free from viruses and we cannot accept liability for any damage which you may sustain as a result of software viruses which may be transmitted in or with the message. Diamond Light Source Limited (company no. 4375679). Registered in England and Wales with its registered office at Diamond House, Harwell Science and Innovation Campus, Didcot, Oxfordshire, OX11 0DE, United Kingdom

[KarlLevik]

Updated SQL - specifying in the column comment that higher numbers are more important:

ALTER TABLE AutoProcProgramAttachment
    MODIFY `fileType` enum('Log','Result','Graph', 'Debug') DEFAULT NULL COMMENT 'Type of file Attachment',
    ADD `importanceRank` tinyint unsigned COMMENT 'For the particular autoProcProgramId and fileType, indicate the importance of the attachment. Higher numbers are more important';