OMERO5.0DEV__5 to OMERO5.0DEV__6 database upgrade script

OMERO5.0DEV__5.sql

-- Copyright (C) 2013 Glencoe Software, Inc. All rights reserved.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; either version 2 of the License, or
-- (at your option) any later version.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License along
-- with this program; if not, write to the Free Software Foundation, Inc.,
-- 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
--
-- Copyright 2012 Glencoe Software, Inc. All rights reserved.
-- Use is subject to license terms supplied in LICENSE.txt
--

BEGIN;

--
-- Assert we are upgrading from patch 5 to patch 6
-- References:
--  * https://github.com/openmicroscopy/openmicroscopy/pull/1168
--  * http://trac.openmicroscopy.org.uk/ome/ticket/10811
--

CREATE OR REPLACE FUNCTION omero_assert_db_version(version varchar, patch int) RETURNS void AS '
DECLARE
    rec RECORD;
BEGIN

    SELECT INTO rec *
           FROM dbpatch
          WHERE id = ( SELECT id FROM dbpatch ORDER BY id DESC LIMIT 1 )
            AND currentversion = version
            AND currentpatch = patch;

    IF NOT FOUND THEN
        RAISE EXCEPTION ''ASSERTION ERROR: Wrong database version'';
    END IF;

END;' LANGUAGE plpgsql;

SELECT omero_assert_db_version('OMERO5.0DEV', 5);
DROP FUNCTION omero_assert_db_version(varchar, int);

--
-- Prepare database version
--

INSERT INTO dbpatch (currentVersion, currentPatch, previousVersion, previousPatch)
    VALUES ('OMERO5.0DEV', 6, 'OMERO5.0DEV', 5);

--
--  Fileset new field templatePrefix (not null)
--

ALTER TABLE fileset ADD COLUMN templatePrefix text;
-- NOTE: Template prefix population performed in accordance with the details
--       available on the OME Trac ticket #10811.
UPDATE fileset SET templatePrefix = (
    SELECT substring(path, '.*?/.*?/.*?/.*?/') FROM originalfile WHERE id IN (
        SELECT originalfile FROM filesetentry WHERE fileset = fileset.id
    ) LIMIT 1
);
ALTER TABLE fileset ALTER COLUMN templatePrefix SET NOT NULL;

--
-- Update the database version to patch 6.
--

UPDATE dbpatch set message = 'Database updated.', finished = clock_timestamp()
    WHERE currentVersion  = 'OMERO5.0DEV' AND currentPatch = 6 AND
          previousVersion = 'OMERO5.0DEV' AND previousPatch = 5;

COMMIT;