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

[Bernhard Holländer]

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