Use SQL DOMAINs in model

[mtbcarroll]

In defining the DB schema we should do things like,

CREATE DOMAIN positive_float AS FLOAT8 CHECK (VALUE >= 0);
CREATE DOMAIN strictly_positive_float AS FLOAT8 CHECK (VALUE > 0);

(or name them whatever they are in the model, maybe it's "non-negative" or something)

and then change the table columns to use these datatypes. This should make the table definitions clearer and constraint updates easier.

[mtbcarroll]

grep -n m....clusive ome.xsd reveals the location of some good stuff but, while ome.dsl.Property.DBTYPES and ome.dsl.SemanticType.getCheck() look strikingly relevant, I don't see what, if anything, is using or calling them, or how else the SQL schema is getting generated.

[jamoore]

dsl is used to generate the model/target/generated/src Java files which are used by Hibernate to generate the schema.

[mtbcarroll]

I am thinking that perhaps the client code is the $type.check code in the strange file object.vm. (Maybe getDbType() isn't used in generation.)

[jamoore]

Glancing quickly, I think you're right; dbtype doesn't seem to be used any longer.

[mtbcarroll]

Thank you. It looks like changes to Property.getDef() (with reference to type) and to SemanticType.getCheck() are what's indicated.

[mtbcarroll]

Strangely, Property.getDef() turns 4-byte XML / Java floats not into 4-byte SQL REALs but 8-byte SQL DOUBLE PRECISION instead. Is this a feature or a bug? If the latter, I wonder how big a breaking change it is to fix.

[jamoore]

See #803: basically, the SQL float was slightly too small for the Java float, so we have Java double everywhere.

[mtbcarroll]

Aha, thank you: for the same reason, it looks like the PostgreSQL JDBC driver made a similar change in 8.1 so that Java floats going through it also become double precision. The REAL range is indeed a bit inadequate.

[mtbcarroll]

Fixed by ​http://github.com/openmicroscopy/openmicroscopy/pull/2191.

[Josh Moore <josh@…>]

(In [09cd2fb81dd1cd70653358a6d16c32b25ac25596/ome.git] on branch develop) Merge pull request #2191 from mtbc/model-changes

fix #11663, #11664, #11877: SQL DOMAIN; import logs (rebased); FS delete log trigger