<commit_before># Adds a display_label column to the prediction_transcript table and
# populates it.
# This is done by creating a temporary table and renaming it rather than just
# using an alter table statement followed by an update statement.  This
# is because in mysql 3 it is not possible to use a join in an update 
# statement.


# create the temporary table

CREATE TABLE tmp_prediction_transcript (
    prediction_transcript_id int unsigned not null auto_increment,
    seq_region_id int unsigned not null,
    seq_region_start int unsigned not null,
    seq_region_end int unsigned not null,
    seq_region_strand tinyint not null,
    analysis_id int,
    display_label varchar(255),
    
    PRIMARY KEY( prediction_transcript_id ),
    KEY ( seq_region_id, seq_region_start ),
    KEY analysis_idx( analysis_id )
);


# populate the tmp table from the original table, and generate display_labels
# at the same time.  The display label generated looks like GENSCAN00000018082
# or SNAP00000018082, etc.

INSERT INTO tmp_prediction_transcript 
SELECT pt.prediction_transcript_id, pt.seq_region_id, pt.seq_region_start, 
       pt.seq_region_end, pt.seq_region_strand, pt.analysis_id,
       CONCAT(UPPER(a.logic_name), LPAD(pt.prediction_transcript_id, 11, '0'))
FROM   prediction_transcript pt, analysis a
WHERE  a.analysis_id = pt.analysis_id;


# drop the original table and rename the temp table

DROP TABLE prediction_transcript;
ALTER TABLE tmp_prediction_transcript RENAME prediction_transcript;


################################################################################

# change the indexes on the align feature tables so that range queries
# can use the indexes properly

ALTER TABLE dna_align_feature DROP INDEX seq_region_idx;
ALTER TABLE protein_align_feature DROP INDEX seq_region_idx;
ALTER TABLE dna_align_feature ADD INDEX seq_region_idx(analysis_id, seq_region_id, seq_region_start, score);
ALTER TABLE protein_align_feature ADD INDEX seq_region_idx(analysis_id, seq_region_id, seq_region_start, score);


# reconstruct the meta coord table with the addition of a 
# max feature length column

DELETE FROM meta_coord;
ALTER TABLE meta_coord add column max_length int;

INSERT INTO meta_coord
SELECT 'assembly_exception', sr.coord_system_id, 
       MAX(IF(ae.seq_region_end - ae.seq_region_start > ae.exc_seq_region_end - ae.exc_seq_region_start, ae.seq_region_end - ae.seq_region_start + 1, ae.exc_seq_region_end - ae.exc_seq_region_start + 1))
FROM   assembly_exception ae, seq_region sr
WHERE  sr.seq_region_id = ae.seq_region_id
GROUP BY sr.coord_system_id;

INSERT INTO meta_coord
SELECT 'density_feature', sr.coord_system_id,
       MAX(df.seq_region_end - df.seq_region_start + 1)
FROM   density_feature df, seq_region sr
WHERE  sr.seq_region_id = df.seq_region_id
GROUP BY sr.coord_system_id;


INSERT INTO meta_coord
SELECT 'dna_align_feature', sr.coord_system_id,
       MAX(daf.seq_region_end - daf.seq_region_start + 1)<commit_msg>Fixed the seq_region_idx index in dna_align_feature/protein_align_feature to be compliant with table.sql<commit_after># Adds a display_label column to the prediction_transcript table and
# populates it.
# This is done by creating a temporary table and renaming it rather than just
# using an alter table statement followed by an update statement.  This
# is because in mysql 3 it is not possible to use a join in an update 
# statement.


# create the temporary table

CREATE TABLE tmp_prediction_transcript (
    prediction_transcript_id int unsigned not null auto_increment,
    seq_region_id int unsigned not null,
    seq_region_start int unsigned not null,
    seq_region_end int unsigned not null,
    seq_region_strand tinyint not null,
    analysis_id int,
    display_label varchar(255),
    
    PRIMARY KEY( prediction_transcript_id ),
    KEY ( seq_region_id, seq_region_start ),
    KEY analysis_idx( analysis_id )
);


# populate the tmp table from the original table, and generate display_labels
# at the same time.  The display label generated looks like GENSCAN00000018082
# or SNAP00000018082, etc.

INSERT INTO tmp_prediction_transcript 
SELECT pt.prediction_transcript_id, pt.seq_region_id, pt.seq_region_start, 
       pt.seq_region_end, pt.seq_region_strand, pt.analysis_id,
       CONCAT(UPPER(a.logic_name), LPAD(pt.prediction_transcript_id, 11, '0'))
FROM   prediction_transcript pt, analysis a
WHERE  a.analysis_id = pt.analysis_id;


# drop the original table and rename the temp table

DROP TABLE prediction_transcript;
ALTER TABLE tmp_prediction_transcript RENAME prediction_transcript;


################################################################################

# change the indexes on the align feature tables so that range queries
# can use the indexes properly

ALTER TABLE dna_align_feature DROP INDEX seq_region_idx;
ALTER TABLE protein_align_feature DROP INDEX seq_region_idx;
ALTER TABLE dna_align_feature ADD INDEX seq_region_idx( seq_region_id, analysis_id, seq_region_start, score);
ALTER TABLE protein_align_feature ADD INDEX seq_region_idx( seq_region_id, analysis_id, seq_region_start, score);


# reconstruct the meta coord table with the addition of a 
# max feature length column

DELETE FROM meta_coord;
ALTER TABLE meta_coord add column max_length int;

INSERT INTO meta_coord
SELECT 'assembly_exception', sr.coord_system_id, 
       MAX(IF(ae.seq_region_end - ae.seq_region_start > ae.exc_seq_region_end - ae.exc_seq_region_start, ae.seq_region_end - ae.seq_region_start + 1, ae.exc_seq_region_end - ae.exc_seq_region_start + 1))
FROM   assembly_exception ae, seq_region sr
WHERE  sr.seq_region_id = ae.seq_region_id
GROUP BY sr.coord_system_id;

INSERT INTO meta_coord
SELECT 'density_feature', sr.coord_system_id,
       MAX(df.seq_region_end - df.seq_region_start + 1)
FROM   density_feature df, seq_region sr
WHERE  sr.seq_region_id = df.seq_region_id
GROUP BY sr.coord_system_id;


INSERT INTO meta_coord
SELECT 'dna_align_feature', sr.coord_system_id,
       MAX(daf.seq_region_end - daf.seq_region_start + 1)
{'fraction_non_alphanumeric': 0.0776731118823335, 'fraction_numerical': 0.010907288051561725, 'mean_word_length': 5.181818181818182, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1833191', 'n_tokens_mistral': 1972, 'n_tokens_neox': 1828, 'n_words': 591}