-- 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_39_40_a
#
# title: rationalise key columns
#;
# description:
# rationalise all the primary and foreign key columns to be INT(10) UNSIGNED
# Note that this is not a definitive list of all primary/foreign keys, just
# the ones that were not defined as INT(10) UNSIGNED in schema 39.

ALTER TABLE oligo_feature CHANGE COLUMN oligo_feature_id oligo_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE oligo_feature CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE oligo_feature CHANGE COLUMN oligo_probe_id oligo_probe_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE oligo_probe CHANGE COLUMN oligo_probe_id oligo_probe_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE oligo_probe CHANGE COLUMN oligo_array_id oligo_array_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE oligo_array CHANGE COLUMN oligo_array_id oligo_array_id INT(10) UNSIGNED NOT NULL auto_increment;
ALTER TABLE oligo_array CHANGE COLUMN parent_array_id parent_array_id INT(10) UNSIGNED;

ALTER TABLE alt_allele CHANGE COLUMN alt_allele_id alt_allele_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE alt_allele CHANGE COLUMN gene_id gene_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE dna CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE dnac CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE exon CHANGE COLUMN exon_id exon_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE exon_stable_id CHANGE COLUMN exon_id exon_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE exon_transcript CHANGE COLUMN exon_id exon_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE exon_transcript CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE simple_feature CHANGE COLUMN simple_feature_id simple_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE protein_align_feature CHANGE COLUMN protein_align_feature_id protein_align_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE dna_align_feature CHANGE COLUMN dna_align_feature_id dna_align_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE repeat_consensus CHANGE COLUMN repeat_consensus_id repeat_consensus_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE repeat_feature CHANGE COLUMN repeat_feature_id repeat_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE gene CHANGE COLUMN gene_id gene_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE gene CHANGE COLUMN display_xref_id display_xref_id INT(10) UNSIGNED;

ALTER TABLE gene_stable_id CHANGE COLUMN gene_id gene_id INT UNSIGNED NOT NULL;

ALTER TABLE supporting_feature CHANGE COLUMN exon_id exon_id INT(10) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE supporting_feature CHANGE COLUMN feature_id feature_id INT(10) UNSIGNED DEFAULT '0' NOT NULL;

ALTER TABLE transcript_supporting_feature CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED DEFAULT '0' NOT NULL;
ALTER TABLE transcript_supporting_feature CHANGE COLUMN feature_id feature_id INT(10) UNSIGNED DEFAULT '0' NOT NULL;

ALTER TABLE transcript CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE transcript CHANGE COLUMN gene_id gene_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE transcript CHANGE COLUMN display_xref_id display_xref_id INT(10) UNSIGNED;

ALTER TABLE transcript_stable_id CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE translation CHANGE COLUMN translation_id translation_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ;
ALTER TABLE translation CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE translation CHANGE COLUMN start_exon_id start_exon_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE translation CHANGE COLUMN end_exon_id end_exon_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE translation_stable_id CHANGE COLUMN translation_id translation_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE assembly CHANGE COLUMN asm_seq_region_id asm_seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE protein_feature CHANGE COLUMN translation_id translation_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE karyotype CHANGE COLUMN karyotype_id karyotype_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE karyotype CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE object_xref CHANGE COLUMN object_xref_id object_xref_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE object_xref CHANGE COLUMN ensembl_id ensembl_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE identity_xref CHANGE COLUMN object_xref_id object_xref_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE xref CHANGE COLUMN xref_id xref_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE xref CHANGE COLUMN external_db_id external_db_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE external_synonym CHANGE COLUMN xref_id xref_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE external_db CHANGE COLUMN external_db_id external_db_id	 INT(10) UNSIGNED NOT NULL;

ALTER TABLE prediction_exon CHANGE COLUMN prediction_exon_id prediction_exon_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE prediction_exon CHANGE COLUMN prediction_transcript_id prediction_transcript_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE prediction_exon CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE prediction_transcript CHANGE COLUMN prediction_transcript_id prediction_transcript_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE prediction_transcript CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE marker_synonym CHANGE COLUMN marker_synonym_id marker_synonym_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE marker_synonym CHANGE COLUMN marker_id marker_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE marker CHANGE COLUMN marker_id marker_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE marker CHANGE COLUMN display_marker_synonym_id display_marker_synonym_id INT(10) UNSIGNED;

ALTER TABLE marker_feature CHANGE COLUMN marker_feature_id marker_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE marker_feature CHANGE COLUMN marker_id marker_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE marker_map_location CHANGE COLUMN marker_id marker_id INT UNSIGNED NOT NULL;
ALTER TABLE marker_map_location CHANGE COLUMN map_id map_id INT UNSIGNED NOT NULL;

ALTER TABLE marker_map_location CHANGE COLUMN marker_id marker_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE marker_map_location CHANGE COLUMN map_id map_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE marker_map_location CHANGE COLUMN marker_synonym_id marker_synonym_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE map CHANGE COLUMN map_id map_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE qtl CHANGE COLUMN qtl_id qtl_id INT(10) UNSIGNED AUTO_INCREMENT NOT NULL;
ALTER TABLE qtl CHANGE COLUMN flank_marker_id_1 flank_marker_id_1 INT(10) UNSIGNED;
ALTER TABLE qtl CHANGE COLUMN flank_marker_id_2 flank_marker_id_2 INT(10) UNSIGNED;
ALTER TABLE qtl CHANGE COLUMN peak_marker_id peak_marker_id INT(10) UNSIGNED;

ALTER TABLE qtl_synonym CHANGE COLUMN qtl_synonym_id qtl_synonym_id 	 INT(10) UNSIGNED AUTO_INCREMENT NOT NULL;
ALTER TABLE qtl_synonym CHANGE COLUMN qtl_id qtl_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE qtl_feature CHANGE COLUMN seq_region_id seq_region_id 	INT(10)	UNSIGNED NOT NULL;
ALTER TABLE qtl_feature CHANGE COLUMN qtl_id qtl_id INT(10)	UNSIGNED NOT NULL;

ALTER TABLE mapping_session CHANGE COLUMN mapping_session_id mapping_session_id 	 INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE stable_id_event CHANGE COLUMN mapping_session_id mapping_session_id INT(10) UNSIGNED NOT NULL DEFAULT '0';

ALTER TABLE gene_archive CHANGE COLUMN peptide_archive_id peptide_archive_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE gene_archive CHANGE COLUMN mapping_session_id mapping_session_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE peptide_archive CHANGE COLUMN peptide_archive_id peptide_archive_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE seq_region CHANGE COLUMN coord_system_id coord_system_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE assembly_exception CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE assembly_exception CHANGE COLUMN exc_seq_region_id exc_seq_region_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE coord_system CHANGE COLUMN coord_system_id coord_system_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE meta_coord CHANGE COLUMN coord_system_id coord_system_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE density_feature CHANGE COLUMN density_feature_id density_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE density_feature CHANGE COLUMN density_type_id density_type_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE density_feature CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE density_feature CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE density_feature CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE density_type CHANGE COLUMN density_type_id density_type_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE regulatory_feature CHANGE COLUMN regulatory_feature_id regulatory_feature_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE regulatory_feature CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_feature CHANGE COLUMN regulatory_factor_id regulatory_factor_id INT(10) UNSIGNED;

ALTER TABLE regulatory_factor CHANGE COLUMN regulatory_factor_id regulatory_factor_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ;

ALTER TABLE regulatory_feature_object CHANGE COLUMN regulatory_feature_id regulatory_feature_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_feature_object CHANGE COLUMN ensembl_object_id ensembl_object_id INT(10) UNSIGNED NOT NULL;

ALTER TABLE regulatory_factor_coding CHANGE COLUMN regulatory_factor_id regulatory_factor_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_factor_coding CHANGE COLUMN transcript_id transcript_id INT(10) UNSIGNED;
ALTER TABLE regulatory_factor_coding CHANGE COLUMN gene_id gene_id 	INT(10) UNSIGNED;

ALTER TABLE regulatory_search_region CHANGE COLUMN regulatory_search_region_id regulatory_search_region_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE regulatory_search_region CHANGE COLUMN seq_region_id seq_region_id INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_search_region CHANGE COLUMN ensembl_object_id ensembl_object_id INT(10) UNSIGNED;

ALTER TABLE unmapped_object CHANGE COLUMN unmapped_object_id unmapped_object_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
ALTER TABLE unmapped_object CHANGE COLUMN external_db_id external_db_id INT(10) UNSIGNED NOT NULL;

# Similarly make all seq_region_start & seq_region_end columns INT(10) UNSIGNED
# Although these are not used as keys, they are used in many joins and having
# the same column type should make joins faster as MySQL will not have to do
# any type casting.

ALTER TABLE oligo_feature CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE oligo_feature CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE prediction_exon CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE prediction_exon CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE qtl_feature CHANGE COLUMN seq_region_start seq_region_start INT(10)	UNSIGNED NOT NULL;
ALTER TABLE qtl_feature CHANGE COLUMN seq_region_end seq_region_end INT(10)	UNSIGNED NOT NULL;

ALTER TABLE assembly_exception CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE assembly_exception CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;
ALTER TABLE assembly_exception CHANGE COLUMN exc_seq_region_start exc_seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE assembly_exception CHANGE COLUMN exc_seq_region_end exc_seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE regulatory_feature CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_feature CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE regulatory_search_region CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE regulatory_search_region CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

ALTER TABLE prediction_transcript CHANGE COLUMN seq_region_start seq_region_start INT(10) UNSIGNED NOT NULL;
ALTER TABLE prediction_transcript CHANGE COLUMN seq_region_end seq_region_end INT(10) UNSIGNED NOT NULL;

# patch identifier
INSERT INTO meta (meta_key, meta_value) VALUES ('patch', 'patch_39_40_a.sql|rationalise_key_columns');

