Skip to content

Add a proposalId column to BLSampleGroup #69

Description

@KarlLevik

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;

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions