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

[Chris Allan]

On Sun, May 29, 2005 at 09:08:57PM +0200, Joshua Moore wrote:
> 
> One and all,

Heya.

> 
> 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,
> ...
> 

First off I'll point you to the following:

http://lists.openmicroscopy.org.uk/pipermail/ome-devel/2005-May/000397.html

> 
> 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).

image_annotation has been fixed in HEAD, "timestamp" and "experimenter"
fields are gone.

> 
> 
> II. FOREIGN KEYS
> 
> There are several other fields which at first glance seem to be missing FK
> constraints:
> 
>  * images_annotations.experimenter

As above, this no longer exists.

>  * images.pixels_id  (I assume this should be the id of the default pixels)

A good question. I'd suggest that it be the ID of the default pixels as
well.

>  * 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.

I'd suggest you checkout Ilya's module from CVS, mentioned above, and
check these 3 issues are still present.

> 
> 
> 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).

Hrm, have to think on this one.

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

Eh? What convention? Float is != to Int quite obviously.

> 
>  * Should the maps have a UNIQUE constraint on both 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?
> 
> 
> 
> 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.

Ciao.

-Chris