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

[josh.moore at gmx.de]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: wellsample.patch
Type: application/octet-stream
Size: 827 bytes
Desc: not available
URL: <http://lists.openmicroscopy.org.uk/pipermail/ome-users/attachments/20090504/9d3bfad0/attachment.obj>
-------------- next part --------------



 > 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