[ome-devel] Database Issues: FK, UNIQUE, and such

[Ilya Goldberg]

On May 29, 2005, at 3:08 PM, Joshua Moore wrote:

>
> One and all,

Hi Josh

> while trying to combine hibernate* with the existing OME DB (as 
> discussed at
> the developer meeting in Baltimore) I've run into several mapping 
> issues.
> I've summarized them below with specific questions. Each one may be 
> one of
> 1) errors on my part 2) errors in the db or 3) simply conventions that 
> more
> indoctrinated ome'ers already know. To start with an example of type 
> three,
> ...
>
>
> I. ANNOTATIONS
>    WRONG: imageAnotation.getExperimenter()
>    RIGHT: imageAnnotation.getModuleExecution()
>                          .getExperimenter()
>
> ImageAnnotation (i.e. image_annotations) does have an experimenter 
> field,
> but it's apparently not used and doesn't have a FK constraint (should 
> it be
> removed?).  Dataset_annotation on the other hand doesn't have the
> experimenter field, nor does it have the timestamp field that
> image_annotation field has (which by the way is the only timestamp 
> with type
> BIGINT and not TIMESTAMP WITHOUT TIME ZONE).

You're working with an old DB.  The current release does not have an 
experimenter reference in imageAnnotation.  Same for timestamp.  Both 
of these come from the ModuleExecution that creates these attributes.

> II. FOREIGN KEYS
>
> There are several other fields which at first glance seem to be 
> missing FK
> constraints:
>
>  * images_annotations.experimenter
>  * images.pixels_id  (I assume this should be the id of the default 
> pixels)
>  * experimenter.group_id
>  * image_pixels.repository (Doesn't look like any table has a FK 
> constraint
> on repositories)
>  * repositories.module_execution_id (This seems significant)
>
>   As Chris and Ilya explained, semantic types don't get foreign key
> constraints. Perhaps this explains some of these, but I don't think 
> all of
> them.

Personally, I don't like this loosey-goosey usage of foreign key 
constraints.  The data model has to be reflected in the DB schema.
I'm experimenting with adding foreign key constraints for all declared 
references in the OME_2_4_1_DB_UPDATES branch.
There are a number of chicken-egg issues with doing this on a fresh DB 
install, so this isn't in this branch yet.  My current thinking is to 
do this in a separate call to the DB delegate 
($delegate->addFKconstraints ($dbh,$class), which would be called 
sometime after $delegate->addClassToDatabase()).
Since I'm trying to do this at a very low level, this will add FK 
declarations to all DBObjects (wether or not they are semantic types).  
This should cover all the missing FKs above.

> III. One-to-One
>
> Relatedly, Hibernate assumes that the relationship between Image and 
> Image
> Dimension is *-1, but not that multiple images could share the same
> dimension (which is conceivable), but that an image has multiple 
> dimensions.
> Is this what's meant? If not, a solution would be a UNIQUE constraint 
> on the
> FK image_dimensions.image_id (a one-one relationship).

We don't currently support explicit "has-one" relationships using 
references.  So for now at least, an image can in fact have multiple 
dimensions.

> VI. Miscellany
>
>  * What exactly is the reasoning for String descriptions of pixel 
> types? At
> least in the java, it seems the convention is int.

The pixel types have to be described somehow, no?  If Java assumes that 
all pixels are ints, then it is incorrect.  Pixels can be floats as 
well.  Actually, there are 7 different pixel types in all.  A Java int 
primitive will also lack the numerical range to deal with unsigned long 
ints.

>  * Should the maps have a UNIQUE constraint on both fields.

Explicit many-to-many relationships (maps) are not supported in STs.  
This could in-fact be done transparently by saying that an ST 
consisting of only references gets a UNIQUE constraint on all its 
fields.

>  * Projects and datasets are many-many. CGs and Cats are 1-Many. 
> Screens and
> plates as well? What is/will be the rule-of-thumb for these (and 
> future)
> containers?

I don't think we want a rule-of-thumb for containers.  Some things are 
naturally a hierarchy, some things are a pseudo-hierarchy, and others 
are graphs.  What did you have in mind?

-Ilya

> Finally, it occurs to me that some of these issues may have been 
> addressed
> in one of the newer releases. If so, ignore me and move on.
>
>
> Best wishes,
>   Josh
>
>
> --------------------------
> * On Hibernate: for anyone not familiar with Hibernate it's an
> object-relational mapping tool for Java. I've taken 21 tables from the 
> DB
> and generated Java objects for them. The code samples included are 
> from this
> generated code. Eg. in the object ImageAnnotation rather than an 
> Integer
> field "experimenter", there should be a field of type Experimenter. 
> There
> isn't, however, because of no foreign key constraint on the 
> experimenter
> column of image_annotations. Most of my questions come from this type 
> of
> issue in the DB.
>
> -- 
> Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
> Ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl
> _______________________________________________
> ome-devel mailing list
> ome-devel at lists.openmicroscopy.org.uk
> http://lists.openmicroscopy.org.uk/mailman/listinfo/ome-devel
>