<commit_before>/**
Use MyISAM storage engine
*/
SET storage_engine=MYISAM;

/**
@header  Variation tables
@desc    These tables define the central variation data.
@colour  #000
*/


/**
@table variation

@desc This is the schema's generic representation of a variation, defined as a genetic feature that varies between individuals of the same species. 
      The most common type is the single nucleotide variation (SNP) though the schema also accommodates copy number variations (CNVs) and structural variations (SVs).<br />
			In Ensembl, a variation is defined by its flanking sequence rather than its mapped location on a chromosome; a variation may in fact have multiple mappings across a genome, 
			although this fails our <a href="data_description.html#quality_control">Quality Control</a>.<br /> 
      This table stores a variation's name (commonly an ID of the form e.g. rs123456, assigned by dbSNP), along with a validation status and ancestral (or reference) allele.

@column variation_id		    Primary key, internal identifier.
@column source_id			      Foreign key references to the @link source table.
@column name				        Name of the variation. e.g. "rs1333049".
@column validation_status	  Variant discovery method and validation from dbSNP. See the status descriptions <a href="data_description.html#validation_status">here</a>.
@column ancestral_allele	  Taken from dbSNP to show ancestral allele for the variation.
@column flipped				      This is set to 1 if the variant is flipped from the negative to the positive strand during import.
@column class_attrib_id		  Class of the variation, key into the @link attrib table.<br /> The list of variation classes is available <a href="data_description.html#classes">here</a>.
@column somatic             flags whether this variation is known to be somatic or not
@column minor_allele        The minor allele of this variant, as reported by dbSNP
@column minor_allele_freq   The 'global' frequency of the minor allele of this variant, as reported by dbSNP
@column minor_allele_count  The number of samples the minor allele of this variant is found in, as reported by dbSNP
@column clinical_significance  A set of clinical significance classes assigned to the variant.<br /> 
                                         The list of clinical significances is available <a href="data_description.html#clin_significance">here</a>.
@column evidence            A summary of the evidence supporting a variant as a guide to its potential reliability.

@see variation_synonym
@see failed_variation
@see variation_feature
@see allele
@see individual_genotype_multiple_bp
@see compressed_genotype_var
@see attrib
*/

create table variation (
  variation_id int(10) unsigned not null auto_increment, # PK
  source_id int(10) unsigned not null, 
  name varchar(255),
  validation_status SET('cluster','freq',
								 'submitter','doublehit',
								 'hapmap','1000Genome',
								 'failed','precious'),
  ancestral_allele varchar(255) DEFAULT NULL,
  flipped tinyint(1) unsigned NULL DEFAULT NULL,
  class_attrib_id int(10) unsigned default 0,
  somatic tinyint(1) DEFAULT 0 NOT NULL,
  minor_allele varchar(50) DEFAULT NULL,
  minor_allele_freq float DEFAULT NULL,
  minor_allele_count int(10) unsigned DEFAULT NULL,
  clinical_significance SET('drug-response','histocompatibility','non-pathogenic','other','pathogenic','probable-non-pathogenic','probable-pathogenic''unknown','untested'),
  evidence SET('Multiple_observations', 'Frequency','HapMap','1000Genomes', 'Cited'),

	primary key( variation_id ),
	unique ( name ),
	key source_idx (source_id)
);


/**
@table variation_feature

@desc This table represents mappings of variations to genomic locations. It stores an allele string representing the different possible alleles that are found at that locus e.g. "A/T" for a SNP, as well as a "worst case" consequence of the mutation. It also acts as part of the relationship between variations and transcripts.

@column variation_feature_id	Primary key, internal identifier.
@column seq_region_id			    Foreign key references @link seq_region in core db. Refers to the seq_region which this variant is on, which may be a chromosome, a clone, etc...
@column seq_region_start		  The start position of the variation on the @link seq_region.
@column seq_region_end			  The end position of the variation on the @link seq_region.
@column seq_region_strand		  The orientation of the variation on the @link seq_region.
@column variation_id				  Foreign key references to the @link variation table.
@column allele_string			    This is a denormalised string taken from the alleles in the allele table associated with this variation. The reference allele (i.e. one on the reference genome comes first).
@column variation_name			  A denormalisation taken from the variation table. This is the name or identifier that is used for displaying the feature.
@column map_weight				    The number of times that this variation has mapped to the genome. This is a denormalisation as this particular feature is one example of a mapped location. This can be used to limit the the features that come back from a query.
@column flags						      Flag to filter the selection of variations.
@column source_id					    Foreign key references to the source table.
@column validation_status		  Variant discovery method and validation from dbSNP. See the status descriptions <a href="data_description.html#validation_status">here</a>.
@column consequence_types		  The SO term(s) of all unique observed consequence types of this variation feature.<br /> The list of consequence descriptions is available <a href="predicted_data.html#consequences">here</a>.
@column variation_set_id		  The variation feature can belong to a @link variation_set.
@column class_attrib_id			  Class of the variation, key in the @link attrib table.<br /> The list of variation classes is available <a href="data_description.html#classes">here</a>.
@column somatic               Flags whether this variation_feature is somatic or germline
@column minor_allele          The minor allele of this variant, as reported by dbSNP
@column minor_allele_freq     The 'global' frequency of the minor allele of this variant, as reported by dbSNP
@column minor_allele_count    The number of samples the minor allele of this variant is found in, as reported by dbSNP
@column alignment_quality     Quality of alignment for variants mapped by flanks rather than position justified.
@column evidence              A summary of the evidence supporting a variant as a guide to its potential reliability.

@see variation
@see tagged_variation_feature
@see transcript_variation
@see seq_region
@see attrib
*/

create table variation_feature(
	variation_feature_id int(10) unsigned not null auto_increment,
	seq_region_id int(10) unsigned not null,
	seq_region_start int not null,
	seq_region_end int not null,
	seq_region_strand tinyint not null,
	variation_id int(10) unsigned not null,
	allele_string varchar(50000),
    variation_name varchar(255),
	map_weight int not null,
	flags SET('genotyped'),
	source_id int(10) unsigned not null, 
	validation_status SET(
        'cluster',
        'freq',
		'submitter',
        'doublehit',
		'hapmap',
        '1000Genome',
		'precious'
    ),
    consequence_types SET (
        'intergenic_variant',
        'splice_acceptor_variant',
        'splice_donor_variant',
        'stop_lost',
        'coding_sequence_variant',
        'missense_variant',
        'stop_gained',
        'synonymous_variant',
        'frameshift_variant',
        'nc_transcript_variant',
        'non_coding_exon_variant',
        'mature_miRNA_variant',
        'NMD_transcript_variant',
        '5_prime_UTR_variant',
        '3_prime_UTR_variant',
        'incomplete_terminal_codon_variant',
        'intron_variant',
        'splice_region_variant',
        'downstream_gene_variant',
        'upstream_gene_variant',
        'initiator_codon_variant',
        'stop_retained_variant',
        'inframe_insertion',
        'inframe_deletion',
        'transcript_ablation',
        'transcript_fusion',
        'transcript_amplification',
        'transcript_translocation',
        'TFBS_ablation',
        'TFBS_fusion',
        'TFBS_amplification',
        'TFBS_translocation',
        'regulatory_region_ablation',
        'regulatory_region_fusion',
        'regulatory_region_amplification',
        'regulatory_region_translocation',
        'feature_elongation',
        'feature_truncation',
        'regulatory_region_variant',
        'TF_binding_site_variant'
    ) DEFAULT 'intergenic_variant' NOT NULL,
    variation_set_id SET (
            '1','2','3','4','5','6','7','8',
            '9','10','11','12','13','14','15','16',
            '17','18','19','20','21','22','23','24',
            '25','26','27','28','29','30','31','32',
            '33','34','35','36','37','38','39','40',
            '41','42','43','44','45','46','47','48',
            '49','50','51','52','53','54','55','56',
            '57','58','59','60','61','62','63','64'
    ) NOT NULL DEFAULT '',
    class_attrib_id int(10) unsigned default 0,
    somatic tinyint(1) DEFAULT 0 NOT NULL,
    minor_allele varchar(50) DEFAULT NULL,
    minor_allele_freq float DEFAULT NULL,
    minor_allele_count int(10) unsigned DEFAULT NULL,
    alignment_quality double  DEFAULT NULL,
    evidence SET('Multiple_observations', 'Frequency','HapMap','1000Genomes', 'Cited'),

   	primary key( variation_feature_id ),
	  key pos_idx( seq_region_id, seq_region_start, seq_region_end ),
	  key variation_idx( variation_id ),
    key variation_set_idx ( variation_set_id ),
    key consequence_type_idx (consequence_types)
);

<commit_msg>Add the evidence "ESP" in variation and variation_feature<commit_after>/**
Use MyISAM storage engine
*/
SET storage_engine=MYISAM;

/**
@header  Variation tables
@desc    These tables define the central variation data.
@colour  #000
*/


/**
@table variation

@desc This is the schema's generic representation of a variation, defined as a genetic feature that varies between individuals of the same species. 
      The most common type is the single nucleotide variation (SNP) though the schema also accommodates copy number variations (CNVs) and structural variations (SVs).<br />
			In Ensembl, a variation is defined by its flanking sequence rather than its mapped location on a chromosome; a variation may in fact have multiple mappings across a genome, 
			although this fails our <a href="data_description.html#quality_control">Quality Control</a>.<br /> 
      This table stores a variation's name (commonly an ID of the form e.g. rs123456, assigned by dbSNP), along with a validation status and ancestral (or reference) allele.

@column variation_id		    Primary key, internal identifier.
@column source_id			      Foreign key references to the @link source table.
@column name				        Name of the variation. e.g. "rs1333049".
@column validation_status	  Variant discovery method and validation from dbSNP. See the status descriptions <a href="data_description.html#validation_status">here</a>.
@column ancestral_allele	  Taken from dbSNP to show ancestral allele for the variation.
@column flipped				      This is set to 1 if the variant is flipped from the negative to the positive strand during import.
@column class_attrib_id		  Class of the variation, key into the @link attrib table.<br /> The list of variation classes is available <a href="data_description.html#classes">here</a>.
@column somatic             flags whether this variation is known to be somatic or not
@column minor_allele        The minor allele of this variant, as reported by dbSNP
@column minor_allele_freq   The 'global' frequency of the minor allele of this variant, as reported by dbSNP
@column minor_allele_count  The number of samples the minor allele of this variant is found in, as reported by dbSNP
@column clinical_significance  A set of clinical significance classes assigned to the variant.<br /> 
                                         The list of clinical significances is available <a href="data_description.html#clin_significance">here</a>.
@column evidence            A summary of the evidence supporting a variant as a guide to its potential reliability.

@see variation_synonym
@see failed_variation
@see variation_feature
@see allele
@see individual_genotype_multiple_bp
@see compressed_genotype_var
@see attrib
*/

create table variation (
  variation_id int(10) unsigned not null auto_increment, # PK
  source_id int(10) unsigned not null, 
  name varchar(255),
  validation_status SET('cluster','freq',
								 'submitter','doublehit',
								 'hapmap','1000Genome',
								 'failed','precious'),
  ancestral_allele varchar(255) DEFAULT NULL,
  flipped tinyint(1) unsigned NULL DEFAULT NULL,
  class_attrib_id int(10) unsigned default 0,
  somatic tinyint(1) DEFAULT 0 NOT NULL,
  minor_allele varchar(50) DEFAULT NULL,
  minor_allele_freq float DEFAULT NULL,
  minor_allele_count int(10) unsigned DEFAULT NULL,
  clinical_significance SET('drug-response','histocompatibility','non-pathogenic','other','pathogenic','probable-non-pathogenic','probable-pathogenic''unknown','untested'),
  evidence SET('Multiple_observations','Frequency','HapMap','1000Genomes','Cited','ESP'),

	primary key( variation_id ),
	unique ( name ),
	key source_idx (source_id)
);


/**
@table variation_feature

@desc This table represents mappings of variations to genomic locations. It stores an allele string representing the different possible alleles that are found at that locus e.g. "A/T" for a SNP, as well as a "worst case" consequence of the mutation. It also acts as part of the relationship between variations and transcripts.

@column variation_feature_id	Primary key, internal identifier.
@column seq_region_id			    Foreign key references @link seq_region in core db. Refers to the seq_region which this variant is on, which may be a chromosome, a clone, etc...
@column seq_region_start		  The start position of the variation on the @link seq_region.
@column seq_region_end			  The end position of the variation on the @link seq_region.
@column seq_region_strand		  The orientation of the variation on the @link seq_region.
@column variation_id				  Foreign key references to the @link variation table.
@column allele_string			    This is a denormalised string taken from the alleles in the allele table associated with this variation. The reference allele (i.e. one on the reference genome comes first).
@column variation_name			  A denormalisation taken from the variation table. This is the name or identifier that is used for displaying the feature.
@column map_weight				    The number of times that this variation has mapped to the genome. This is a denormalisation as this particular feature is one example of a mapped location. This can be used to limit the the features that come back from a query.
@column flags						      Flag to filter the selection of variations.
@column source_id					    Foreign key references to the source table.
@column validation_status		  Variant discovery method and validation from dbSNP. See the status descriptions <a href="data_description.html#validation_status">here</a>.
@column consequence_types		  The SO term(s) of all unique observed consequence types of this variation feature.<br /> The list of consequence descriptions is available <a href="predicted_data.html#consequences">here</a>.
@column variation_set_id		  The variation feature can belong to a @link variation_set.
@column class_attrib_id			  Class of the variation, key in the @link attrib table.<br /> The list of variation classes is available <a href="data_description.html#classes">here</a>.
@column somatic               Flags whether this variation_feature is somatic or germline
@column minor_allele          The minor allele of this variant, as reported by dbSNP
@column minor_allele_freq     The 'global' frequency of the minor allele of this variant, as reported by dbSNP
@column minor_allele_count    The number of samples the minor allele of this variant is found in, as reported by dbSNP
@column alignment_quality     Quality of alignment for variants mapped by flanks rather than position justified.
@column evidence              A summary of the evidence supporting a variant as a guide to its potential reliability.

@see variation
@see tagged_variation_feature
@see transcript_variation
@see seq_region
@see attrib
*/

create table variation_feature(
	variation_feature_id int(10) unsigned not null auto_increment,
	seq_region_id int(10) unsigned not null,
	seq_region_start int not null,
	seq_region_end int not null,
	seq_region_strand tinyint not null,
	variation_id int(10) unsigned not null,
	allele_string varchar(50000),
    variation_name varchar(255),
	map_weight int not null,
	flags SET('genotyped'),
	source_id int(10) unsigned not null, 
	validation_status SET(
        'cluster',
        'freq',
		'submitter',
        'doublehit',
		'hapmap',
        '1000Genome',
		'precious'
    ),
    consequence_types SET (
        'intergenic_variant',
        'splice_acceptor_variant',
        'splice_donor_variant',
        'stop_lost',
        'coding_sequence_variant',
        'missense_variant',
        'stop_gained',
        'synonymous_variant',
        'frameshift_variant',
        'nc_transcript_variant',
        'non_coding_exon_variant',
        'mature_miRNA_variant',
        'NMD_transcript_variant',
        '5_prime_UTR_variant',
        '3_prime_UTR_variant',
        'incomplete_terminal_codon_variant',
        'intron_variant',
        'splice_region_variant',
        'downstream_gene_variant',
        'upstream_gene_variant',
        'initiator_codon_variant',
        'stop_retained_variant',
        'inframe_insertion',
        'inframe_deletion',
        'transcript_ablation',
        'transcript_fusion',
        'transcript_amplification',
        'transcript_translocation',
        'TFBS_ablation',
        'TFBS_fusion',
        'TFBS_amplification',
        'TFBS_translocation',
        'regulatory_region_ablation',
        'regulatory_region_fusion',
        'regulatory_region_amplification',
        'regulatory_region_translocation',
        'feature_elongation',
        'feature_truncation',
        'regulatory_region_variant',
        'TF_binding_site_variant'
    ) DEFAULT 'intergenic_variant' NOT NULL,
    variation_set_id SET (
            '1','2','3','4','5','6','7','8',
            '9','10','11','12','13','14','15','16',
            '17','18','19','20','21','22','23','24',
            '25','26','27','28','29','30','31','32',
            '33','34','35','36','37','38','39','40',
            '41','42','43','44','45','46','47','48',
            '49','50','51','52','53','54','55','56',
            '57','58','59','60','61','62','63','64'
    ) NOT NULL DEFAULT '',
    class_attrib_id int(10) unsigned default 0,
    somatic tinyint(1) DEFAULT 0 NOT NULL,
    minor_allele varchar(50) DEFAULT NULL,
    minor_allele_freq float DEFAULT NULL,
    minor_allele_count int(10) unsigned DEFAULT NULL,
    alignment_quality double  DEFAULT NULL,
    evidence SET('Multiple_observations','Frequency','HapMap','1000Genomes','Cited','ESP'),

   	primary key( variation_feature_id ),
	  key pos_idx( seq_region_id, seq_region_start, seq_region_end ),
	  key variation_idx( variation_id ),
    key variation_set_idx ( variation_set_id ),
    key consequence_type_idx (consequence_types)
);


{'fraction_non_alphanumeric': 0.08399138549892318, 'fraction_numerical': 0.02040816326530612, 'mean_word_length': 3.9324734446130503, 'pattern_counts': {'":': 0, '<': 43, '<?xml version=': 0, '>': 43, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 4, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '7078268', 'n_tokens_mistral': 5823, 'n_tokens_neox': 5212, 'n_words': 1973}