ispyb / ispyb-database-modeling

4 stars 3 forks source link

Add a proposalId column to BLSampleGroup #69

Open KarlLevik opened 3 years ago

KarlLevik commented 3 years ago

Currently, we can't really create a sample group without simultaneously adding samples to it because the samples are needed to link it to a proposal.

This SQL adds a proposalId column to BLSampleGroup + a FK constraint, and populates old rows based on the BLSample -> Crystal -> Protein.proposalId route:

ALTER TABLE BLSampleGroup
  ADD proposalId int(10) unsigned,
  ADD CONSTRAINT BLSampleGroup_fk_proposalId
    FOREIGN KEY (`proposalId`) 
      REFERENCES `Proposal` (`proposalId`) 
        ON DELETE SET NULL ON UPDATE CASCADE;

UPDATE BLSampleGroup sg
  JOIN BLSampleGroup_has_BLSample sghs ON sghs.blSampleGroupId = sg.blSampleGroupId
  JOIN BLSample s ON s.blSampleId = sghs.blSampleId
  JOIN Crystal c ON c.crystalId = s.crystalId
  JOIN Protein p ON p.proteinId = c.proteinId
SET sg.proposalId = p.proposalId;