text: -- Copyright [1999-2015] Wellcome Trust Sanger Institute and the EMBL-European Bioinformatics Institute
  -- Copyright [2016-2019] EMBL-European Bioinformatics Institute
  -- 
  -- Licensed under the Apache License, Version 2.0 (the "License");
  -- you may not use this file except in compliance with the License.
  -- You may obtain a copy of the License at
  -- 
  --      http://www.apache.org/licenses/LICENSE-2.0
  -- 
  -- Unless required by applicable law or agreed to in writing, software
  -- distributed under the License is distributed on an "AS IS" BASIS,
  -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  -- See the License for the specific language governing permissions and
  -- limitations under the License.
  
  # patch_56_57_d.sql
  #
  # title: CompareSchema tidy up
  #
  # description:
  # Re-apply some patches which have been identified by the CompareSchema 
  # as absent.
  
  #Some array tables had wrong index name
  ALTER IGNORE table array drop KEY `vendor`;
  ALTER IGNORE table array drop KEY `vendor_name_idx`;
  ALTER IGNORE table array add UNIQUE KEY `vendor_name_idx` (`vendor`, `name`);
  
  #Lots of very minor column definition changes
  ALTER table experimental_set modify `name` varchar(100) NOT NULL;
  alter table result_set modify `analysis_id` smallint(5) unsigned NOT NULL;
  ALTER table unmapped_object modify `ensembl_object_type` enum('RegulatoryFeature','ExternalFeature','AnnotatedFeature','FeatureType', 'Probe', 'ProbeSet', 'ProbeFeature') NOT NULL;
  ALTER table xref modify `description` VARCHAR(255);
  alter table probe_design modify `analysis_id` smallint(5) unsigned NOT NULL;
  alter table regulatory_feature modify `bound_seq_region_start` int(10) unsigned NOT NULL AFTER stable_id;
  alter table regulatory_feature modify `bound_seq_region_end` int(10) unsigned NOT NULL AFTER bound_seq_region_start;
  alter table coord_system modify `is_current` boolean default True AFTER species_id;
  alter table probe_set modify `name` varchar(100) NOT NULL;
  ALTER table cell_type change column description description varchar(80);
  ALTER table external_db modify `type` ENUM('ARRAY', 'ALT_TRANS', 'MISC', 'LIT', 'PRIMARY_DB_SYNONYM', 'ENSEMBL') default NULL;
  
  #From patch_50_51_b.sql
  ALTER IGNORE TABLE meta DROP INDEX key_value;
  ALTER IGNORE TABLE meta DROP INDEX meta_key_index;
  ALTER IGNORE TABLE meta DROP INDEX meta_value_index;
  ALTER IGNORE TABLE meta DROP INDEX species_key_value_idx;
  ALTER IGNORE TABLE meta DROP INDEX species_value_idx;
  ALTER TABLE meta ADD UNIQUE INDEX species_key_value_idx (species_id, meta_key, meta_value);
  ALTER TABLE meta ADD INDEX species_value_idx (species_id, meta_value);
  
  #Dros only?
  ALTER IGNORE TABLE meta_coord DROP INDEX cs_table_name_idx;
  ALTER IGNORE TABLE meta_coord DROP INDEX table_name;
  ALTER TABLE meta_coord ADD UNIQUE KEY `table_name` (`table_name`,`coord_system_id`);
  ALTER IGNORE table probe_set ADD INDEX `name` (`name`);
  
  
  #Correct result_feature index to remove PRIMARY KEY
  #as it does not match the partition key
  ALTER IGNORE table result_feature ADD KEY `result_feature_idx` (`result_feature_id`);
  #remove PRIMARY KEY if present
  ALTER IGNORE table result_feature DROP PRIMARY KEY; 
  ALTER IGNORE table result_feature DROP INDEX result_feature_id; 
  #Now need to drop other key and re instate as this will have dropped to the bottom?
  #Is this really an issue, as it seems CompareSchema does not complain about
  #the order of keys 
  ##ALTER table result_feature DROP INDEX set_window_seq_region_idx; 
  ##ALTER table result_feature ADD INDEX `set_window_seq_region_idx` (`result_set_id`, `window_size`,`seq_region_id`,`seq_region_start`);
  
  
  # patch identifier
  INSERT INTO meta (species_id, meta_key, meta_value) VALUES (NULL, 'patch', 'patch_56_57_d.sql|CompareSchema_tidyup');
  
  
  