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

[Joshua Moore]

One and all,

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


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.


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


VI. Miscellany

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

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

-- 
Weitersagen: GMX DSL-Flatrates mit Tempo-Garantie!
Ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl