Circular foreign key references

[rjgildea]

Attempting to generate sqlalchemy models via sqlacodegen leads to the following warning/error:

sqlacodegen mysql+pymysql://user:password@host:port/ispyb_build --noinflect > models.py
/Users/rjgildea/software/cctbx_py3/conda_base/lib/python3.8/site-packages/sqlalchemy/sql/ddl.py:1213: SAWarning: Cannot correctly sort tables; there are unresolvable cycles between tables "BLSample, BLSampleImage, BLSubSample, DataCollection, Screening, ScreeningOutput, ScreeningStrategy, ScreeningStrategySubWedge, ScreeningStrategyWedge", which is usually caused by mutually dependent foreign key constraints.  Foreign key constraints involving these tables will not be considered; this warning may raise an error in a future release.
  util.warn(

This appears to be the result of two circular foreign key references:
BLSample -> BLSubSample -> BLSampleImage -> BLSample (via BLSample.blSubSampleId)
and
DataCollection -> screeningStrategySubWedge -> ScreeningStrategyWedge -> ScreeningStrategy -> Screening -> DataCollection (via DataCollection.strategySubWedgeOrigId).

As far as I can tell, both BLSample.blSubSampleId and DataCollection.strategySubWedgeOrigId appear to be unused at DLS. I don't understand what purpose either column serves. Can these columns be removed from the table?

[stufisher]

BLSample.blSubSampleId was to mark crystals as fished from a marked position on a plate
otherwise unused so ignore it
(needs a sensible comment)
for plates sample = drop, blsubsample = marked position, now you can fish that into a new blsample => puck (edited)

[KarlLevik]

I think you mean sample = drop?
Is there any code actually using BLSample.blSubSampleId?
Is this how it works?

• You have a plate with wells/drops (BLSample) which have marked positions (BLSubSample).
• You then fish out something from a marked position subsample1 within a well/drop sample1 and place it inside a new sample2 which is not in the plate.
• You set sample2.blSubSampleId = ID of subsample1.
• subsample1.blSampleId still points to the well/drop (BLSample) of the plate.

[stufisher]

*fixed :)

Yes, exactly its just for traceability.
Synchweb populates blsample.blsubsampleid even if its null, if you remove from the db you will break creating samples