[ome-users] upgrading screen-plate-well tables from 3.1 to 4.0.1

[Susan Wilson]

Hi,

Is there an established procedure for migrating the database from  
omero 3 to omero 4?

Thanks.
Susan

On May 5, 2009, at 3:22 AM, Bernhard Holländer wrote:

> Hi Josh!
>
> Thanks for the patch, that worked! I would appreciate if this patch
> will be included in the standard ugrade scripts. I think the well
> index based on the grouped counter you have in the function is good
> enough.
>
> Best wishes! Bernhard
>
> On Mon, May 4, 2009 at 8:22 PM,  <josh.moore at gmx.de> wrote:
>>
>> Bernhard Holländer writes:
>>  > Hi Josh!
>>
>> Hey Bernhard,
>>
>> so the problem is that you started creating Wells with SPW before the
>> Well.wellSamples relationship was ordered. As we discussed recently,
>> ordering the wellSamples uses the well_index column with 0-based
>> offsets.
>>
>> I'm not sure the following function is acceptable for everyone, since
>> there is a user decision to be made on the well sample order, but in
>> your case you can use this to get your wellsample ducks in a row:
>>
>> CREATE OR REPLACE FUNCTION assert_well_index() RETURNS VOID AS '
>> DECLARE
>>    count int4;
>>    w     int8;
>>    ws    int8;
>> BEGIN
>>
>>  FOR w IN SELECT well FROM wellsample GROUP BY well LOOP
>>    RAISE NOTICE ''Indexing well % '', w;
>>    count := 0;
>>    FOR ws IN SELECT id FROM wellsample WHERE well = w LOOP
>>       RAISE NOTICE ''Indexing well sample % '', ws;
>>       UPDATE wellsample SET well_index = count WHERE id = ws;
>>       count := count + 1;
>>    END LOOP;
>>  END LOOP;
>>
>> END;' LANGUAGE plpgsql;
>>
>> SELECT assert_well_index();
>>
>> Hope that helps,
>> ~Josh
>>
>> P.S. attached is a patch against sql/psql/OMERO4__0/OMERO3A__11.sql
>>
>>
>>
>>
>>
>>  > I'll send the files off-list!
>>  >
>>  > Thanks! Bernhard
>>  >
>>  > On Mon, May 4, 2009 at 4:54 PM,  <josh.moore at gmx.de> wrote:
>>  > >
>>  > > Bernhard Holländer writes:
>>  > >  > Hi all!
>>  > >
>>  > > Hi Bernhard,
>>  > >
>>  > >  > I've tried to migrate a database from omero 3.1 to 4.0.1  
>> which
>>  > >  > contains entries in the screen plate well model tables.  
>> Unfortunately
>>  > >  > I get an error that there are null values in the well_index  
>> table:
>>  > >  > ERROR:  column "well_index" contains null values
>>  > >  >
>>  > >  > The upgrade is not complete at that point and the resulting  
>> database
>>  > >  > state cannot be used :-(
>>  > >
>>  > > Frown indeed. Could you send the results of "select * from  
>> dbpatch",
>>  > > and possibly a console log of the upgrade (maybe from using  
>> "script"),
>>  > > and we'll see what we can do.
>>  > >
>>  > > ~J.
>>  > >
>>  > >  > It would be great if there's a work around for that problem.
>>  > >  >
>>  > >  > Thanks! Bernhard
>>
>> _______________________________________________
>> ome-users mailing list
>> ome-users at lists.openmicroscopy.org.uk
>> http://lists.openmicroscopy.org.uk/mailman/listinfo/ome-users
>>
>>
> _______________________________________________
> ome-users mailing list
> ome-users at lists.openmicroscopy.org.uk
> http://lists.openmicroscopy.org.uk/mailman/listinfo/ome-users