Ensembl/ensembl-hive

-- ---------------------------------------------------------------------------------------------------

CREATE TEMPORARY VIEW exp_ver AS SELECT "69" AS expected_version;

.bail ON

SELECT ('The patch only applies to schema version '
    || expected_version
    || ', but the current schema version is '
    || meta_value
    || ', skipping the rest.') AS ''
    FROM hive_meta JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

INSERT INTO hive_meta (meta_key, meta_value)
    SELECT hm.* FROM hive_meta AS hm JOIN exp_ver WHERE meta_key='hive_sql_schema_version' AND meta_value<>expected_version;

SELECT ('The patch seems to be compatible with schema version '
    || expected_version
    || ', applying the patch...') AS '' FROM exp_ver;


-- ----------------------------------<actual_patch> -------------------------------------------------

DROP VIEW IF EXISTS live_roles;

CREATE VIEW IF NOT EXISTS live_roles AS
    SELECT w.meadow_user, w.meadow_type, w.resource_class_id, rc.name resource_class_name, r.analysis_id, a.logic_name, count(*)
    FROM worker w
    JOIN role r USING(worker_id)
    LEFT JOIN resource_class rc ON w.resource_class_id=rc.resource_class_id
    LEFT JOIN analysis_base a USING(analysis_id)
    WHERE r.when_finished IS NULL
    GROUP BY w.meadow_user, w.meadow_type, w.resource_class_id, rc.name, r.analysis_id, a.logic_name;

-- ----------------------------------</actual_patch> -------------------------------------------------


    -- increase the schema version by one:
UPDATE hive_meta SET meta_value=meta_value+1 WHERE meta_key='hive_sql_schema_version';