Puck database schema changes

[KarlLevik]

As discussed at the ISPyB meeting at Soleil earlier this week, Diamond has made some database schema developments for supporting a "puck database" within ISPyB. We now also have production code for this in Synchweb.

However, we are happy to make changes to the schema if anyone finds a problem with it. I've pasted in the create script below, but sadly there doesn't seem to be an easy way to attach the diagram.

The main table is ContainerRegistry, and the other tables are referencing that. An entry in ContainerRegistry can be associated with multiple Proposals through _ContainerRegistry_hasProposal. ContainerHistory will show the history of a given Container (i.e. the beamlines it's been to). ContainerReport allows a Person to write one or more reports about a Container in the ContainerRegistry.

Statements to remove the tables and columns created

ALTER TABLE Container 
  DROP containerRegistryId,
  DROP FOREIGN KEY  Container_ibfk8;

ALTER TABLE ContainerHistory 
  DROP beamlineName;

DROP TABLE IF EXISTS ContainerReport;
DROP TABLE IF EXISTS ContainerRegistry_has_Proposal;
DROP TABLE IF EXISTS ContainerRegistry;

Create statements

CREATE TABLE ContainerRegistry (
  containerRegistryId int(11) unsigned AUTO_INCREMENT PRIMARY KEY,
  barcode varchar(20),
  comments varchar(255),
  recordTimestamp datetime DEFAULT current_timestamp
);

ALTER TABLE Container 
  ADD containerRegistryId int(11) unsigned NULL DEFAULT NULL,
  ADD CONSTRAINT Container_ibfk8 FOREIGN KEY (containerRegistryId) REFERENCES ContainerRegistry(containerRegistryId);

CREATE TABLE ContainerRegistry_has_Proposal (
  containerRegistryHasProposalId int(11) unsigned AUTO_INCREMENT PRIMARY KEY,
  containerRegistryId int(11) unsigned,
  proposalId int(10) unsigned,
  personId int(10) unsigned COMMENT 'Person registering the container',
  recordTimestamp datetime DEFAULT current_timestamp,
  UNIQUE KEY (containerRegistryId, proposalId),
  CONSTRAINT ContainerRegistry_has_Proposal_ibfk1 FOREIGN KEY (containerRegistryId) REFERENCES ContainerRegistry(containerRegistryId),
  CONSTRAINT ContainerRegistry_has_Proposal_ibfk2 FOREIGN KEY (proposalId) REFERENCES Proposal(proposalId),
  CONSTRAINT ContainerRegistry_has_Proposal_ibfk3 FOREIGN KEY (personId) REFERENCES Person(personId)
);

CREATE TABLE ContainerReport (
  containerReportId int(11) unsigned AUTO_INCREMENT PRIMARY KEY,
  containerRegistryId int(11) unsigned,
  personId int(10) unsigned COMMENT 'Person making report',
  report text,
  attachmentFilePath varchar(255),
  recordTimestamp datetime,
  CONSTRAINT ContainerReport_ibfk1 FOREIGN KEY (containerRegistryId) REFERENCES ContainerRegistry(containerRegistryId),
  CONSTRAINT ContainerReport_ibfk2 FOREIGN KEY (personId) REFERENCES Person(personId)
);

ALTER TABLE ContainerHistory 
  ADD beamlineName varchar(20);

Can this be merged into the official schema?

[antolinos]

Hi @KarlLevik,

We had an internal meeting and we all agreed that we are missing here the big picture of this. Could you explain what is the purpose and use case for Container Registry, Report and History?

Another minor comments are:

• barcode is already in Container table, what is difference between Container.barCode and containerRegistry.barcode?
• In the table called ContainerReport there is the column report that it is a text. How is it used? Could you provide an example of report?
• How does attachementFilePath is supposed to be used?

Thanks, A.

[stufisher]

Hi @antolinos we do have barcode in the container table, however it assumes uniqueness (we use it for plate barcodes where it must be unique). Therefore if you register two containers you cannot use the same barcode, mx users obviously want to reuse their pucks.

Puck barcodes for most people dont exist at the moment, diamond now etches a barcode onto each unipuck, this is what we store in containerRegistry.barcode field. The is the same as say DLS-0001 that people would usually put into the container.code field. However code sometimes doesnt match the actual puck text, so containerRegistry.barcode is filled in by a staff member as they etch the puck, so we know it is consistent. This value is then used by the automated puck barcode scanners to assign pucks in ISPyB.

ContainerReport.report is some text that staff can fill in the explain if there is a problem with the puck, i.e. missing screws etc. This is emailed to the users. attachmentFilePath relates to this, it allows staff to attach a photo to the report.

[stufisher]

Sorry forgot the first question!

DLS is slowly moving towards assigning pucks where ever there is beamtime disassociated from dewars. As part of this staff would like to keep track of where pucks are, usages, reporting, etc. The puck database allows us to do this.

DLS staff etch the DLS-0001 text as a qr code onto each puck as they receive them on site. This value is then registered into containerregistry.barcode and this registered container is linked to one or more proposals via containerregistry_has_proposal (sometimes pucks are shared between proposals). We now have a unique way to identify each puck.

When users create a shipment, now when they registered a container they can link the current "instance" of a puck to a registered "container" from container registry. The reason we do this and not use barcode / code fields in container is because they are inconsistent. Users often make mistakes with the text of code, which means they cannot be automatically matched on scanning.

We now register "history" against each container in the same way we register history against dewars. Puck DLS-1234 was loaded into position 4 on i03. This is recorded into history. We also use this on VMXi to record where containers are on the beamline, there are many different locations "storage", "goniometer", etc. Now we have auditable timestamped data for containers, which means you can search and locate them.

Long term we can now see how many times a puck has been used. Along with reporting we can see if a user has sent us a back a container that staff have already reported as broken, etc.