Engineering question: Maintaining foreign-key/foreign-key-like relationship between mappings/annotations and variations?

[jsstevenson]

Intuitively, we would like to have foreign keys from annotations/mappings back to variations. However, we expect to provide a single table for each type of variation (alleles, CNVs, etc), and it's not particularly straightforward or non-janky to do a foreign key like this. if we don't have this kind of a relationship, then it's possible to add annotations/mappings for variations that aren't contained in the DB, which could be bad.

• most promising solution: CHECK constraints https://docs.sqlalchemy.org/en/20/core/constraints.html#check-constraint
• uglier solutions:
  • manually run separate check queries
  • crazy join tables??
  • one big variations table with a lot of null columns?

[theferrit32]

I think the check constraint is probably the best way to enforce id existence at the database level.

I am thinking we could do a query per table when it is needed to search annotations and then go back to what they point to and the type cannot be disambiguated by the id prefix, or if that seems overly brittle. If you query for annotations and then get some object ids, then doing an indexed query against each other object table should be pretty quick, especially compared to the non-indexed query against annotation values you would have just done.

e.g. you have annotations like

{
  "object_id": "ga4gh:VA:123",
  "annotation_type": "gnomad-af",
  "annotation_value": {
    "af": 0.05,
    "grpmax-faf": 0.1
  }
}

If you do a query for annotation_value->'grpmax-faf' >= 0.05 and get this object_id: "ga4gh:VA:123" (and others) you could then do a series of joins against Allele, CopyNumberCount, and CopyNumberChange (and others?) which would each give the matching records for those object_ids. The return values have different structure for each so I think it'd be easier to handle them separately in application code once you get the result set anyways. Instead of a getting a result set with the superset of all the columns.

[jsstevenson]

I think we're going with check constraints -- #286

[jennifer-bowser]

Unfortunately it looks like you can't write check constraints for one table that reference other tables' columns.

Some alternative options:

• We could combine a check constraint with a custom function or trigger

  • Cons:
    • Personal preference, but if we're going to need to write custom logic for this anyways, then I'd rather do it in Python alongside the rest of our code
    • We'd need additional logic to handle attempted deletes of related entities - i.e., a check constraint + function that runs on the insert of a new Mapping entry, for instance, won't stop somebody from deleting the Allele that Mapping relies on in the future

• We could have an additional table that houses all vrs object IDs and their type, then use a compound foreign key in our Mappings/Annotations table that references this instead of trying to reference one of our variant tables directly. Example:

  vrs_objects

  id	object_type
  ga4gh:VA.QiKnpRR8S7SPoUE-RMUJJbT-RS1akuHA	Allele
  ga4gh:CX.eVlFPLW5eFChqk1GX5QBpHent0UchVhG	CopyNumber

  Then the Annotations and Mappings table could be foreign keyed to this instead of to the individual variant tables

  • Cons:
    • We'd need to double add/alter/delete vrs objects from both their individual tables and this one every time we want to change anything
  • Pros:
    • I believe we need to add a generic vrs_objects table anyways to support Eugene's usecase:

    TODO: read table names from env vars and default to the current names
    TODO: default to also writing the full VRS object blobs into vrs_objects like before, in addition to the new tables.

    Both of these changes are useful when AnyVar is just one tool that sits on top of a larger data ecosystem. For MGB's use of AnyVar, I would say they are requirements.

    Being able to configure the table names allows us to have AnyVar read/write directly to our core variant database.

    Writing the full VRS objects to the database allows bulk operations at the database level, enabling performance at a scale that would simply not be possible if those VRS objects had to extracted through a REST API. Our VRS object table currently contains about 1 billion rows (SL+VA).

[jsstevenson]

This is deeply unsatisfying and I'd really like to return to a better idea later -- but for Jan 15th maybe we just do the hacky thing (separate query beforehand to verify existence of dependent objects) and solve later

[theferrit32]

I would lean towards, for now, having no constraints for these values, because we are putting values in we know are good and we can trust ourselves at the moment, for the most part.

Also in any case, table constraints will slow down write performance so may be an inconvenience in the short term anyways.

[ehclark]

The way this problem is typically solved with ORMs is to use inheritance. I would probably opt for joined table inheritance. VrsObject would be the base class. Location and Allele and other variant types would be subclasses. Then you can define Annotation as having a relationship to VrsObject which could be created as a simple foreign key in the database.

https://docs.sqlalchemy.org/en/20/orm/inheritance.html