4.2 Database upgrade

[jamoore]

The first big task of the DB upgrade will be making sure permissions are sensible, but other DB changes are being placed here as an umbrella e.g. model evolution (SPW and Filter mainly)

Previous ideas.

• Make all groups private (currently rw-r-r)
• Make all group data private (enums?)
• movie ns to file.mimetype??
  • "openmicroscopy.org/omero/movie/mpeg";
  • "openmicroscopy.org/omero/movie/qt";
  • "openmicroscopy.org/omero/movie/wmv";

[jmoore]

Note: all DB modifications will be happening in a single commit tied to this ticket as opposed to the individual tasks because of the interruptions caused by small DB changes.

[jmoore]

(In [6737]) see #1750 - Initial support for arrays in Hibernate and blitz (cF NDIM)

[jmoore]

(In [6738]) see #1750 - Adding slightly modified sql array support from Hibernate forum

[jmoore]

(In [6739]) see #1750 - 4.2 Database mapping changes

[jmoore]

(In [6741]) see #1750 - RC1 SQL for 4.2 DB, code and tests compile. Needs review!

[jmoore]

(In [6744]) see #1750 - Build fixes for simplifying model creation / testing

NB: This allows the following usage of bin/omero db script

   FILE=`omero db script "" "" ome | perl -pe 's/^Saving to (.*?)$/$1/'`
   psql omero -f $FILE

[jmoore]

(In [6749]) see #1750 - More test fixes

[jmoore]

(In [6751]) see #1750 - Making SqlArray Java 6 compatible

[jmoore]

(In [6752]) see #1750 - But for Java5 we have to remove the @Override annotation on SqlArray.free()

[jmoore]

(In [6756]) see #1750 - Fixing misspelling of emissionSFilterLinks?

[jmoore]

(In [6758]) see #1750, #2287 - Fixing FilterSet links and the naming of linked objects

[jmoore]

(In [6759]) see #1750 - Re-adding PlateAcquisition start and end time

[cxallan]

(In [6762]) Fixed server side metadata store issues that arose from data model updates. (See #1750)

[cxallan]

(In [6763]) Fixed reference tests that arose from data model updates; tests all pass. (See #1750)

[wmoore]

Rendering Engine:

Traceback (most recent call last):
  File "test/integration/emanScripts.py", line 189, in testImagesFromRois
    iId = importImage(session, imagePath, imageName)
  File "test/integration/emanScripts.py", line 627, in importImage
    image = scriptUtil.createNewImage(pixelsService, rawPixelStore, renderingEngine, pixelsType, gateway, [plane2D], imageName, "description", dataset=None)
  File "/Users/will/Documents/workspace/Omero/dist/lib/python/omero/util/script_utils.py", line 666, in createNewImage
    resetRenderingSettings(renderingEngine, pixelsId, theC, minValue, maxValue)
  File "/Users/will/Documents/workspace/Omero/dist/lib/python/omero/util/script_utils.py", line 609, in resetRenderingSettings
    renderingEngine.resetDefaults()
  File "/Users/will/Documents/workspace/Omero/dist/lib/python/omero_api_RenderingEngine_ice.py", line 387, in resetDefaults
    return _M_omero.api.RenderingEngine._op_resetDefaults.invoke(self, ((), _ctx))
QueryException: exception ::omero::QueryException
{
    serverStackTrace = ome.services.query.QueryException: Illegal query:select channel from LogicalChannel as channel left outer join fetch channel.filterSet as filter left outer join fetch filter.emFilter as ef left outer join fetch filter.exFilter as exf left outer join fetch ef.transmittanceRange as efTrans left outer join fetch exf.transmittanceRange as exfTrans left outer join fetch channel.secondaryEmissionFilter as emfilter left outer join fetch emfilter.transmittanceRange as emTrans left outer join fetch channel.secondaryExcitationFilter as exfilter left outer join fetch exfilter.transmittanceRange as exTrans left outer join fetch channel.lightSourceSettings as lss left outer join fetch lss.lightSource as ls where channel.id = :id
could not resolve property: emFilter of: ome.model.acquisition.FilterSet [select channel from ome.model.core.LogicalChannel as channel left outer join fetch channel.filterSet as filter left outer join fetch filter.emFilter as ef left outer join fetch filter.exFilter as exf left outer join fetch ef.transmittanceRange as efTrans left outer join fetch exf.transmittanceRange as exfTrans left outer join fetch channel.secondaryEmissionFilter as emfilter left outer join fetch emfilter.transmittanceRange as emTrans left outer join fetch channel.secondaryExcitationFilter as exfilter left outer join fetch exfilter.transmittanceRange as exTrans left outer join fetch channel.lightSourceSettings as lss left outer join fetch lss.lightSource as ls where channel.id = :id]
	at ome.services.query.StringQuery.buildQuery(StringQuery.java:60)
	at ome.services.query.Query.doInHibernate(Query.java:222)
	at org.springframework.orm.hibernate3.HibernateTemplate.doExecute(HibernateTemplate.java:406)
	at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:339)
	at ome.logic.QueryImpl.execute(QueryImpl.java:141)
	at ome.logic.QueryImpl.findByQuery(QueryImpl.java:337)
	at ome.logic.RenderingSettingsImpl.loadLogicalChannel(RenderingSettingsImpl.java:367)
	at ome.logic.RenderingSettingsImpl.resetChannelBindings(RenderingSettingsImpl.java:744)
	at ome.logic.RenderingSettingsImpl.resetDefaults(RenderingSettingsImpl.java:566)
	at ome.logic.RenderingSettingsImpl.resetDefaults(RenderingSettingsImpl.java:485)
	at ome.logic.RenderingSettingsImpl.resetDefaults(RenderingSettingsImpl.java:1248)
	at ome.services.RenderingBean$13.doWork(RenderingBean.java:1540)
	at sun.reflect.GeneratedMethodAccessor193.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at ome.services.util.Executor$Impl$Interceptor.invoke(Executor.java:393)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at ome.security.basic.EventHandler.invoke(EventHandler.java:144)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.orm.hibernate3.HibernateInterceptor.invoke(HibernateInterceptor.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:108)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at ome.tools.hibernate.ProxyCleanupFilter$Interceptor.invoke(ProxyCleanupFilter.java:175)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy54.doWork(Unknown Source)
	at ome.services.util.Executor$Impl.execute(Executor.java:323)
	at ome.services.RenderingBean._resetDefaults(RenderingBean.java:1537)
	at ome.services.RenderingBean.resetDefaults(RenderingBean.java:635)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at ome.services.util.ServiceHandler.invoke(ServiceHandler.java:111)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy70.resetDefaults(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:307)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
	at ome.security.basic.BasicSecurityWiring.invoke(BasicSecurityWiring.java:83)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at ome.services.blitz.fire.AopContextInitializer.invoke(AopContextInitializer.java:40)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
	at $Proxy70.resetDefaults(Unknown Source)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
	at java.lang.reflect.Method.invoke(Method.java:592)
	at ome.services.blitz.util.IceMethodInvoker.invoke(IceMethodInvoker.java:179)
	at ome.services.throttling.Callback.run(Callback.java:56)
	at ome.services.throttling.InThreadThrottlingStrategy.callInvokerOnRawArgs(InThreadThrottlingStrategy.java:56)
	at ome.services.blitz.impl.AbstractAmdServant.callInvokerOnRawArgs(AbstractAmdServant.java:132)
	at ome.services.blitz.impl.RenderingEngineI.resetDefaults_async(RenderingEngineI.java:406)
	at omero.api._RenderingEngineTie.resetDefaults_async(_RenderingEngineTie.java:281)
	at omero.api._RenderingEngineDisp.___resetDefaults(_RenderingEngineDisp.java:1266)
	at omero.api._RenderingEngineDisp.__dispatch(_RenderingEngineDisp.java:1616)
	at IceInternal.Incoming.invoke(Incoming.java:159)
	at Ice.ConnectionI.invokeAll(ConnectionI.java:2037)
	at Ice.ConnectionI.message(ConnectionI.java:972)
	at IceInternal.ThreadPool.run(ThreadPool.java:577)
	at IceInternal.ThreadPool.access$100(ThreadPool.java:12)
	at IceInternal.ThreadPool$EventHandlerThread.run(ThreadPool.java:971)

[jmoore]

see r6766 for enumerations.

[jmoore]

(In [6776]) see #1750 - Fixing TextI reference in OmeroPopo.py

[jmoore]

(In [6783]) see #1750 - 4.2-RC1 DB including fix for #2293 (ScreenPlateWell)

[jmoore]

(In [6785]) see #1750, #2293 - Commenting out sections of OmeroMetadata? for Chris

[cxallan]

(In [6788]) Fixed FilterSet? query used during resetDefaults(). (See #1750).

[jmoore]

(In [6793]) see #1750 - Reversing plate acquisition / well sample relationship as per ​xml:ticket:121

[jmoore]

(In [6795]) see #1750 - Changing timepoint to timestamp (​xml:ticket:120)wq

[jmoore]

(In [6796]) see #1750 - Compile fix r6795

[jmoore]

(In [6799]) see #1750 - more compile fixes

[jmoore]

(In [6807]) see #1750 - Fixing RegionsOfInterest example's use of TextI

[jmoore]

(In [7000]) see #1750, #2353 - Modifying annotations (+term;-uri/query/tb)

[jmoore]

(In [7001]) see #1750 - Making WellSample.plateAcquisition nullable

[jmoore]

(In [7320]) DB 4.2-RC10. See #1750

[jmoore]

(In [7325]) Initial version of OMERO4.2__0 upgrade script (See #1750)

[jmoore]

(In [7327]) Possible unit-test system for DB upgrades. (See #1750)

[jmoore]

(In [7328]) Minor fixes to mappings and renaming to OMERO4.20. (See #1750)

[jmoore]

(In [7329]) OMERO4.2__0 scripts with working upgrade. (See #1750)

[jmoore]

(In [7330]) Updating internal use of original_metadata.txt to match OMERO4.2__0. (See #1750, #1753)

[jmoore]

(In [7338]) Fixing OMERO4.2__0 for PostgreSQL 8.2+ (See #1750)

[jmoore]

(In [7371]) Moving 4.1 scripts to 'user' group (See #1750)

[jmoore]

(In [7394]) Working omero report before refactoring (See #1750)

[jmoore]

(In [7395]) Report now embedded into OMERO4.1__0.sql with \i and using ACTION variable (See #1750)

[jmoore]

(In [7406]) Removing now unneeded ome_nextval(unknown) functions (See #1750, r7338)

[jmoore]

(In [7428]) Adding user photo handling to db upgrade script (See #1750)

[jmoore]

(In [7486]) Ignoring all non-system permissions on update (See #1750, #1434)

[jmoore]

(In [7492]) DB 4.2__0 improvements (See #1750 & #2204, Fix #2565 & #2573)

[jmoore]

(In [7502]) Adding example SQL cleanup script (See #1750)

[jmoore]

(In [7504]) Replacing missing exception in report (See #1750)

[jmoore]

(In [7505]) Scripts updated for PG 8.1 with working upgrade (See #1750 & #2204)

[jmoore]

(In [7508]) Storing seq_table values in new _lock_ids (See #1750)

[jmoore]

(In [7512]) Lowering all group permissions on upgrade (See #1750, #2204)

[jmoore]

(In [7515]) Inserting all of the permissions report file in to OMERO4.1__0.sql to prevent errors (See #1750)