Ensembl/ensembl-hive

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

SET @expected_version = 75;

    -- 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> -------------------------------------------------

-- in order to drop the UNIQUE KEY you needed to drop all participating foreign keys first:
ALTER TABLE dataflow_rule DROP FOREIGN KEY dataflow_rule_ibfk_1;
ALTER TABLE dataflow_rule DROP FOREIGN KEY dataflow_rule_ibfk_2;
ALTER TABLE dataflow_rule DROP INDEX from_analysis_id;

-- re-creating both foreign keys:
ALTER TABLE dataflow_rule           ADD FOREIGN KEY (from_analysis_id)          REFERENCES analysis_base(analysis_id);
ALTER TABLE dataflow_rule           ADD FOREIGN KEY (funnel_dataflow_rule_id)   REFERENCES dataflow_rule(dataflow_rule_id);

-- creating new table to hold the condition + dataflow target parameters:
CREATE TABLE dataflow_target (
    source_dataflow_rule_id INTEGER     NOT NULL,
    on_condition            VARCHAR(255)          DEFAULT NULL,
    input_id_template       MEDIUMTEXT            DEFAULT NULL,
    to_analysis_url         VARCHAR(255) NOT NULL DEFAULT '',       -- to be renamed 'target_url'

    UNIQUE  KEY (source_dataflow_rule_id, on_condition, input_id_template(512), to_analysis_url)
) COLLATE=latin1_swedish_ci ENGINE=InnoDB;

-- adding a foreign key between targets and rules:
ALTER TABLE dataflow_target         ADD FOREIGN KEY (source_dataflow_rule_id)   REFERENCES dataflow_rule(dataflow_rule_id);

-- transferring the data:
INSERT INTO dataflow_target (source_dataflow_rule_id, on_condition, input_id_template, to_analysis_url)
    SELECT dataflow_rule_id, NULL, input_id_template, to_analysis_url FROM dataflow_rule;

-- removing the duplicated columns:
ALTER TABLE dataflow_rule DROP COLUMN to_analysis_url;
ALTER TABLE dataflow_rule DROP COLUMN input_id_template;

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


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