Detector update query exception

[cxallan]

When updating a Detector or related objects linking to a Detector we get the following exception in the Blitz-0.log:

Caused by: org.postgresql.util.PSQLException: ERROR: column detectors0_.group_id does not exist
 Position: 1008
       at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
       at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
       at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
       at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:273)
       at sun.reflect.GeneratedMethodAccessor38.invoke(Unknown Source)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:597)
       at bitronix.tm.resource.jdbc.BaseProxyHandlerClass.invoke(BaseProxyHandlerClass.java:63)
       at $Proxy60.executeQuery(Unknown Source)
       at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
       at org.hibernate.loader.Loader.getResultSet(Loader.java:1869)
       at org.hibernate.loader.Loader.doQuery(Loader.java:718)
       at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
       at org.hibernate.loader.Loader.loadCollection(Loader.java:2082)
       ... 112 more

This appears to be due to database schema issues:

omero44=# \d detector
             Table "public.detector"
      Column        |       Type       | Modifiers 
---------------------+------------------+-----------
amplificationgain   | double precision | 
gain                | double precision | 
offset              | double precision | 
voltage             | double precision | 
zoom                | double precision | 
manufacturerspec_id | bigint           | not null
instrument          | bigint           | not null
type                | bigint           | 
Indexes:
   "detector_pkey" PRIMARY KEY, btree (manufacturerspec_id)
   "i_detector_instrument" btree (instrument)
   "i_detector_type" btree (type)
Foreign-key constraints:
   "fkdetector_instrument_instrument" FOREIGN KEY (instrument) REFERENCES instrument(id)
   "fkdetector_manufacturerspec_id_manufacturerspec" FOREIGN KEY (manufacturerspec_id) REFERENCES manufacturerspec(id)
   "fkdetector_type_detectortype" FOREIGN KEY (type) REFERENCES detectortype(id)
Referenced by:
   TABLE "detectorsettings" CONSTRAINT "fkdetectorsettings_detector_detector" FOREIGN KEY (detector) REFERENCES detector(manufacturerspec_id)

[jmoore]

See: ​https://hibernate.onjira.com/browse/HHH-1901 (Opened 2006; unresolved)

[jmoore]

Doesn't look there's a "correct" way to fix this issue in the Hibernate world, so queries on joined-subclasses (as opposed to on the superclass) will continue to fail in this manner. But, that problem existed before "settings" and "reference" were added, so I'm assuming we can get away with that for 4.4.0.

Instead, I'm going to add a workaround in object.vm so that if the superclass in question is "...Settings" or "...Reference" that a TABLE_PER_CLASS strategy will be used, so that each of the tables will have a group_id, owner_id, etc.

The trade-offs for the TABLE_PER_CLASS strategy are the what columns can be present on the superclass and which identity strategy we can use (see: ​https://docs.jboss.org/hibernate/annotations/3.5/reference/en/html_single/) which shouldn't be an issue. It also makes sense because "Settings" and "Reference" are essentially dummy classes, and we do not plan on adding columns to them.

[jmoore]

After fixing the Reference/Settings/DectorSettings... hierarchy, I found issues with all of the ManufacturerSpec/Microscope... hierarchy.

Since ManufacturerSpec has fields itself, this may be a more complicated issue.

In either case, we are likely going to need tests that try to retrieve all settings/manu.specs or specific subclasses to see if any exceptions are thrown.

[jmoore]

Fixed and pushed to schema-2012-06. Again, lookout for weird queries involving ManufacturerSpec subclasses (especially LightSource).