Further changes for XPDF

[KarlLevik]

As discussed at the ISPyB meeting at Soliel earlier this week, Diamond has made some recent database schema developments to support our XPDF (pair distribution function) beamline.

While these schema changes have been "live" at Diamond for a little while now, we are happy to make changes to this if anyone finds a problem with it.

A few words about the schema changes

In the tables and columns you'll see the words DataCollectionPlan and Component. These are what we would like to rename the current DiffractionPlan and Protein tables to, respectively, in the future. This is to make the schema less MX specific.

packingFraction was previously erroneously added to Crystal, so we've now moved it to BLSample.

The table _DataCollectionPlan_hasDetector allows us to have multiple Detectors for each DC plan, and to use the same plan for multiple detectors.

The table _BLSample_hasDataCollectionPlan allows us to have multiple DC plans for each BLSample and also to use the same plan on multiple samples.

The enums DiffractionPlan.experimentKind and DataCollectionGroup.experimentType have to be extended to allow for XPDF data collection types.

Each DiffractionPlan needs to have a name to make them more easily recognizable by humans.

_DataCollection_hasScanParametersModel allows us to say that a DC was done using a set of scan parameters.

_DataCollectionPlan_hasDetector allows each DC to be associated with more than one Detector.

I would have attached a diagram to show the changes, but alas, it's not possible.

The SQL statements

ALTER TABLE DiffractionPlan drop FOREIGN KEY DataCollectionPlan_ibfk2;
ALTER TABLE DiffractionPlan drop dataCollectionPlanGroupId;
DROP TABLE DataCollectionPlanGroup;

ALTER TABLE Crystal DROP packingFraction;
ALTER TABLE Crystal ADD theoreticalDensity float;
ALTER TABLE BLSample ADD packingFraction float;
ALTER TABLE Protein CHANGE theoreticalDensity density float;

DROP TABLE IF EXISTS DiffractionPlan_has_Detector;
DROP TABLE IF EXISTS BLSample_has_DiffractionPlan;
DROP TABLE IF EXISTS DataCollectionPlan_has_Detector;
DROP TABLE IF EXISTS BLSample_has_DataCollectionPlan;

CREATE TABLE DataCollectionPlan_has_Detector (
    dataCollectionPlanId int(11) unsigned NOT NULL,
    detectorId int(11) NOT NULL,
    exposureTime double,
    distance double,
    orientation double,
    PRIMARY KEY (`dataCollectionPlanId`, `detectorId`),
    CONSTRAINT DataCollectionPlan_has_Detector_ibfk1 FOREIGN KEY (dataCollectionPlanId) REFERENCES DiffractionPlan (diffractionPlanId),
    CONSTRAINT DataCollectionPlan_has_Detector_ibfk2 FOREIGN KEY (detectorId) REFERENCES Detector (detectorId)
);

CREATE TABLE BLSample_has_DataCollectionPlan (
    blSampleId int(11) unsigned NOT NULL,
    dataCollectionPlanId int(11) unsigned NOT NULL,
    PRIMARY KEY (`blSampleId`, `dataCollectionPlanId`),    
    CONSTRAINT BLSample_has_DataCollectionPlan_ibfk1 FOREIGN KEY (blSampleId) REFERENCES BLSample (blSampleId),
    CONSTRAINT BLSample_has_DataCollectionPlan_ibfk2 FOREIGN KEY (dataCollectionPlanId) REFERENCES DiffractionPlan (diffractionPlanId)
);

ALTER TABLE DiffractionPlan
  MODIFY experimentKind enum('Default','MXPressE','MXPressO','MXPressE_SAD','MXScore','MXPressM','MAD','SAD','Fixed','Ligand binding','Refinement',
    'OSC','MAD - Inverse Beam','SAD - Inverse Beam','MESH','XFE', 'Bragg', 'PDF', 'PDF+Bragg');

DROP TABLE IF EXISTS Protein_has_Lattice;
DROP TABLE IF EXISTS ComponentLattice;

CREATE TABLE ComponentLattice (
    componentLatticeId int(11) unsigned auto_increment PRIMARY KEY,
    componentId int(10) unsigned,
    spaceGroup varchar(20),
    cell_a double,
    cell_b double,
    cell_c double,
    cell_alpha double,
    cell_beta double,
    cell_gamma double,
    CONSTRAINT ComponentLattice_ibfk1 FOREIGN KEY (componentId) REFERENCES Protein (proteinId)
);

ALTER TABLE DiffractionPlan 
  ADD `name` varchar(20) AFTER `diffractionPlanId`; 

ALTER TABLE DataCollectionGroup 
  MODIFY experimentType enum('SAD','SAD - Inverse Beam','OSC','Collect - Multiwedge','MAD','Helical','Multi-positional','Mesh','Burn','MAD - Inverse Beam','Characterization','Dehydration','tomo','experiment','EM','PDF', 'PDF+Bragg', 'Bragg');

CREATE TABLE DataCollection_has_ScanParametersModel (
    dataCollectionId int(11) unsigned NOT NULL,
    scanParametersModelId int(11) unsigned NOT NULL,
    PRIMARY KEY (dataCollectionId, scanParametersModelId),
    CONSTRAINT DataCollection_has_ScanParametersModel_ibfk1
      FOREIGN KEY (dataCollectionId) REFERENCES DataCollection(dataCollectionId) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT DataCollection_has_ScanParametersModel_ibfk2
      FOREIGN KEY (scanParametersModelId) REFERENCES ScanParametersModel(scanParametersModelId) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE DataCollectionPlan_has_Detector CHANGE `orientation` `roll` double;

ALTER TABLE ScanParametersModel CHANGE modelNumber sequenceNumber tinyint(3) unsigned;

CREATE UNIQUE INDEX Detector_ibuk1 ON Detector (detectorSerialNumber);

DROP TABLE IF EXISTS DataCollectionPlan_has_Detector;

CREATE TABLE DataCollectionPlan_has_Detector (
    dataCollectionPlanHasDetectorId int(11) unsigned auto_increment PRIMARY KEY,
    dataCollectionPlanId int(11) unsigned NOT NULL,
    detectorId int(11) NOT NULL,
    exposureTime double,
    distance double,
    roll double,
    UNIQUE KEY (`dataCollectionPlanId`, `detectorId`),
    CONSTRAINT DataCollectionPlan_has_Detector_ibfk1 FOREIGN KEY (dataCollectionPlanId) REFERENCES DiffractionPlan (diffractionPlanId),
    CONSTRAINT DataCollectionPlan_has_Detector_ibfk2 FOREIGN KEY (detectorId) REFERENCES Detector (detectorId)
);

Can these changes be merged into the official database schema?

[KarlLevik]

Some further additions to this:

ALTER TABLE BLSample_has_DataCollectionPlan
  DROP `order`;

ALTER TABLE ScanParametersModel
  DROP duration;

ALTER TABLE BLSample_has_DataCollectionPlan
  ADD `order` tinyint unsigned;

ALTER TABLE ScanParametersModel
  ADD duration mediumint unsigned COMMENT 'Duration for parameter change in seconds';