-- Copyright (C) 2012-4 Glencoe Software, Inc. All rights reserved.
-- Use is subject to license terms supplied in LICENSE.txt
--
-- 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.
--

---
--- OMERO5 readiness check for upgrade from OMERO5.3__0 to OMERO5.4__0.
---

BEGIN;

--
-- check OMERO database version
--

CREATE OR REPLACE FUNCTION omero_assert_db_version(expected_version VARCHAR, expected_patch INTEGER) RETURNS void AS $$

DECLARE
    current_version VARCHAR;
    current_patch INTEGER;

BEGIN
    SELECT currentversion, currentpatch INTO STRICT current_version, current_patch
        FROM dbpatch ORDER BY id DESC LIMIT 1;

    IF current_version <> expected_version OR current_patch <> expected_patch THEN
        RAISE EXCEPTION 'wrong OMERO database version for this upgrade script';
    END IF;

END;$$ LANGUAGE plpgsql;

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


--
-- check PostgreSQL server version and database encoding
--

CREATE OR REPLACE FUNCTION db_pretty_version(version INTEGER) RETURNS TEXT AS $$

BEGIN
    RETURN (version/10000)::TEXT || '.' || ((version/100)%100)::TEXT || '.' || (version%100)::TEXT;

END;$$ LANGUAGE plpgsql;


CREATE FUNCTION assert_db_server_prerequisites(version_prereq INTEGER) RETURNS void AS $$

DECLARE
    version_num INTEGER;
    char_encoding TEXT;

BEGIN
    SELECT CAST(setting AS INTEGER) INTO STRICT version_num
        FROM pg_settings WHERE name = 'server_version_num';
    SELECT pg_encoding_to_char(encoding) INTO STRICT char_encoding
        FROM pg_database WHERE datname = current_database();

    IF version_num < version_prereq THEN
        RAISE EXCEPTION 'PostgreSQL database server version % is less than OMERO prerequisite %',
            db_pretty_version(version_num), db_pretty_version(version_prereq);
    END IF;

    IF char_encoding != 'UTF8' THEN
        RAISE EXCEPTION 'OMERO database character encoding must be UTF8, not %', char_encoding;
    ELSE
        SET client_encoding = 'UTF8';
    END IF;

END;$$ LANGUAGE plpgsql;

SELECT assert_db_server_prerequisites(90300);

DROP FUNCTION assert_db_server_prerequisites(INTEGER);
DROP FUNCTION db_pretty_version(INTEGER);


--
-- Actual upgrade check
--

-- check for exploitation of 2017-SV5

CREATE MATERIALIZED VIEW filepath_arrays(id, repo, filepath) AS
    SELECT id, repo, regexp_split_to_array('/' || path || name || '/', '/+')
    FROM originalfile
    WHERE repo IS NOT NULL;

CREATE INDEX filepath_arrays_repo_filepath ON filepath_arrays(repo, filepath);

CREATE FUNCTION assert_no_filepath_array_duplicates() RETURNS void AS $$

DECLARE
    filepath_curr filepath_arrays%ROWTYPE;
    ids BIGINT[];

BEGIN
    FOR filepath_curr IN SELECT * FROM filepath_arrays LOOP
        ids := ARRAY(SELECT id FROM filepath_arrays
            WHERE repo = filepath_curr.repo AND filepath = filepath_curr.filepath);
        IF ARRAY_LENGTH(ids, 1) > 1 THEN
            RAISE EXCEPTION 'duplicate original file paths with IDs %', ids;
        END IF;
    END LOOP;

END;$$ LANGUAGE plpgsql;

SELECT assert_no_filepath_array_duplicates();
DROP FUNCTION assert_no_filepath_array_duplicates();

DROP MATERIALIZED VIEW filepath_arrays;


--
-- FINISHED
--

SELECT E'\n\n\nYOUR DATABASE IS READY FOR UPGRADE TO VERSION OMERO5.4__0\n\n\n' AS Status;

ROLLBACK;
