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

OMERO5.0DEV__4.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 4 to patch 5
-- References:
--  * https://github.com/openmicroscopy/openmicroscopy/pull/1133/
--  * https://github.com/openmicroscopy/bioformats/pull/499
--  * http://trac.openmicroscopy.org.uk/ome/ticket/10280
--

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', 4);
DROP FUNCTION omero_assert_db_version(varchar, int);

--
-- Prepare database version
--

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

--
--  FilterType new enumeration value 'Tuneable'
--

INSERT INTO filtertype (id,permissions,value)
    SELECT ome_nextval('seq_filtertype'),-52,'Tuneable';

--
-- DetectorSettings new fields integration (constrained to be > 0) and zoom
--

ALTER TABLE detectorsettings ADD COLUMN integration int4;
ALTER TABLE detectorsettings ADD COLUMN zoom float8;
-- NOTE: The decision on the following constraint name is decided
--       automatically by PostgreSQL and may be different.
ALTER TABLE detectorsettings ADD CONSTRAINT detectorsettings_integration_check
    CHECK (integration > 0);

--
-- Objective nominalMagnification int4 --> float8 and drop > 0 constraint
--

ALTER TABLE objective ALTER COLUMN nominalMagnification SET DATA TYPE float8;
-- NOTE: The decision on the following constraint name is decided
--       automatically by PostgreSQL and may be different.
ALTER TABLE objective DROP CONSTRAINT objective_nominalmagnification_check;

--
-- Pixels new field significantBits (constrained to be > 0)
--

ALTER TABLE pixels ADD COLUMN significantBits int4;
-- NOTE: The decision on the following constraint name is decided
--       automatically by PostgreSQL and may be different.
ALTER TABLE pixels DROP CONSTRAINT pixels_check;
ALTER TABLE pixels ADD CONSTRAINT pixels_check
    CHECK (significantBits > 0 AND sizex > 0 AND sizey > 0
        AND sizez > 0 AND sizec > 0 AND sizet > 0);

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

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

COMMIT;