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

OMERO5.0DEV__3.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 3 to patch 4
--

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

--
-- Prepare database version
--

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

--
-- OriginalFile sha1 --> hash, hasher
--

ALTER TABLE originalfile RENAME COLUMN sha1 TO hash;
ALTER TABLE originalfile ADD COLUMN hasher int8;

--
-- ChecksumAlgorithm data type
--

CREATE TABLE checksumalgorithm (
    id int8 not null,
    permissions int8 not null,
    value varchar(255) not null unique,
    external_id int8 unique,
    primary key (id)
);

ALTER TABLE checksumalgorithm
    ADD CONSTRAINT FKchecksumalgorithm_external_id_externalinfo
    FOREIGN KEY (external_id)
    REFERENCES externalinfo;

CREATE SEQUENCE seq_checksumalgorithm;
INSERT INTO _lock_ids (name, id)
    SELECT 'seq_checksumalgorithm', nextval('_lock_seq');

INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'Adler-32';
INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'CRC-32';
INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'MD5-128';
INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'Murmur3-32';
INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'Murmur3-128';
INSERT INTO checksumalgorithm (id,permissions,value)
    SELECT ome_nextval('seq_checksumalgorithm'),-52,'SHA1-160';

--
-- Update OriginalFile table
--

UPDATE originalfile SET hasher =
    (SELECT id FROM checksumalgorithm WHERE value = 'SHA1-160');

--
-- Constraints, etc.
--

ALTER TABLE originalfile
    ADD CONSTRAINT FKoriginalfile_hasher_checksumalgorithm
    FOREIGN KEY (hasher)
    REFERENCES checksumalgorithm;

CREATE INDEX i_OriginalFile_hasher ON originalfile(hasher);

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

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

COMMIT;