Ensembl/ensembl-hive

-- ---------------------------------------------------------------------------------------------------
-- improved time_analysis() function for timing individual analyses or groups (takes into account running roles)
-- ---------------------------------------------------------------------------------------------------

SET @expected_version = 67;

    -- make MySQL stop immediately after it encounters division by zero:
SET SESSION sql_mode='TRADITIONAL';

    -- warn that we detected the schema version mismatch:
SELECT CONCAT(  'The patch only applies to schema version ',
                @expected_version,
                ', but the current schema version is ',
                meta_value,
                ', so skipping the rest.') AS ''
    FROM hive_meta WHERE meta_key='hive_sql_schema_version' AND meta_value<>@expected_version;

    -- cause division by zero only if current version differs from the expected one:
INSERT INTO hive_meta (meta_key, meta_value)
    SELECT 'this_should_never_be_inserted', 1 FROM hive_meta WHERE NOT 1/(meta_key<>'hive_sql_schema_version' OR meta_value=@expected_version);

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

    -- Now undo the change so that we could patch potentially non-TRADITIONAL schema:
SET SESSION sql_mode='';

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

DROP PROCEDURE IF EXISTS time_analysis;
CREATE PROCEDURE time_analysis(IN analyses_pattern CHAR(64))
READS SQL DATA
    SELECT
        COUNT(*)-COUNT(when_finished) AS still_running,
        (UNIX_TIMESTAMP(CASE WHEN COUNT(*)>COUNT(when_finished) THEN CURRENT_TIMESTAMP ELSE max(when_finished) END) - UNIX_TIMESTAMP(min(when_started)))/60 AS measured_in_minutes,
        (UNIX_TIMESTAMP(CASE WHEN COUNT(*)>COUNT(when_finished) THEN CURRENT_TIMESTAMP ELSE max(when_finished) END) - UNIX_TIMESTAMP(min(when_started)))/3600 AS measured_in_hours,
        (UNIX_TIMESTAMP(CASE WHEN COUNT(*)>COUNT(when_finished) THEN CURRENT_TIMESTAMP ELSE max(when_finished) END) - UNIX_TIMESTAMP(min(when_started)))/3600/24 AS measured_in_days
        FROM role JOIN analysis_base USING (analysis_id)
        WHERE logic_name LIKE analyses_pattern;

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


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