my $self = shift;
my $dbc = shift;
$dbc = $self->xref->dbc unless defined $dbc;
# ALL are on the Gene level now. This may change but for now it is okay.
my ($alt_to_ref, $ref_to_alts) = $self->get_alt_allele_hashes();
#
# Move the xrefs on to the reference Gene.
# NOTE: Igonore used as the xref might already be on this Gene already and we do not want it to crash
#
my $move_sql =(<<'MOVE');
UPDATE IGNORE object_xref ox,
xref x, source s
SET ox.ensembl_id = ?
WHERE x.source_id = s.source_id AND
ox.xref_id = x.xref_id AND
ox.ensembl_id = ? AND
ox.ensembl_object_type = 'Gene' AND
ox.ox_status = 'DUMP_OUT' AND
s.name in (
MOVE
$move_sql .= "'".join("', '",$self->get_gene_specific_list()) . "')";
print "MOVE SQL\n$move_sql\n";
#
# Now where it was already on the Gene the ignore will have stopped the move
# so we now want to just remove those ones as they already exist.
#
my $del_ix_sql =(<<'DIX');
DELETE ix
FROM identity_xref ix, object_xref ox,
xref x, source s
WHERE x.source_id = s.source_id AND
ox.object_xref_id = ix.object_xref_id AND
ox.xref_id = x.xref_id AND
ox.ensembl_id = ? AND
ox.ensembl_object_type = 'Gene' AND
ox.ox_status = 'DUMP_OUT' AND
s.name in (
DIX
$del_ix_sql .= "'".join("', '",$self->get_gene_specific_list()) . "')";
my $del_sql =(<<'DEL');
DELETE ox
FROM object_xref ox,
xref x, source s
WHERE x.source_id = s.source_id AND
ox.xref_id = x.xref_id AND
ox.ensembl_id = ? AND
ox.ensembl_object_type = 'Gene' AND
ox.ox_status = 'DUMP_OUT' AND
s.name in (
DEL
$del_sql .= "'".join("', '",$self->get_gene_specific_list()) . "')";
my $move_sth = $dbc->prepare($move_sql) || croak "$move_sql cannot be prepared";
my $del_ix_sth = $dbc->prepare($del_ix_sql) || croak "$del_ix_sql cannot be prepared";
my $del_sth = $dbc->prepare($del_sql) || croak "$del_sql cannot be prepared";
my $move_count = 0;
my $del_ix_count = 0;
my $del_ox_count = 0;
foreach my $key (keys %$alt_to_ref){
$move_sth->execute($alt_to_ref->{$key}, $key);
$move_count += $move_sth->rows;
$del_ix_sth->execute($key);
$del_ix_count += $del_ix_sth->rows;
$del_sth->execute($key);
$del_ox_count += $del_sth->rows;
}
$move_sth->finish;
$del_sth->finish;
$del_ix_sth->finish;
print "Number of rows:- moved = $move_count, identitys deleted = $del_ix_count, object_xrefs deleted = $del_ox_count\n";
#
# Now we have all the data on the reference Gene we want to copy all the data
# onto the alt alleles.
#
my $get_data_sql=(<<'GET');
SELECT ox.object_xref_id, ox.ensembl_object_type, ox.xref_id, ox.linkage_annotation,
ox.linkage_type, ox.ox_status, ox.unused_priority, ox.master_xref_id,
ix.query_identity, ix.target_identity, ix.hit_start, ix.hit_end,
ix.translation_start, ix.translation_end, ix.cigar_line, ix.score, ix.evalue
FROM
xref x, source s, object_xref ox
LEFT JOIN identity_xref ix ON ox.object_xref_id =ix.object_xref_id
WHERE x.source_id = s.source_id AND
ox.xref_id = x.xref_id AND
ox.ensembl_id = ? AND
ox.ox_status = 'DUMP_OUT' AND
ox.ensembl_object_type = 'Gene' AND
s.name in (
GET
$get_data_sql .= "'".join("', '",$self->get_gene_specific_list()) . "')";
my $get_data_sth = $self->xref->dbc->prepare($get_data_sql) || croak "Could not prepare $get_data_sql";
my $insert_object_xref_sql =(<<'INO');
INSERT INTO object_xref (object_xref_id, ensembl_id, ensembl_object_type, xref_id, linkage_annotation,
linkage_type, ox_status, unused_priority, master_xref_id)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
INO
my $insert_ox_sth = $self->xref->dbc->prepare($insert_object_xref_sql) || croak "Could not prepare $insert_object_xref_sql";
my $insert_identity_xref_sql = (<<'INI');
INSERT INTO identity_xref (object_xref_id, query_identity, target_identity, hit_start, hit_end,
translation_start, translation_end, cigar_line, score, evalue )
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
INI
my $insert_ix_sth = $self->xref->dbc->prepare($insert_identity_xref_sql) || croak "Could not prepare $insert_identity_xref_sql";
my $max_object_xref_id;
my $sth = $self->xref->dbc->prepare("SELECT MAX(object_xref_id) FROM object_xref");
$sth->execute();
$sth->bind_columns(\$max_object_xref_id);
$sth->fetch;
if((!defined($max_object_xref_id)) or (!$max_object_xref_id)){
croak "Problem getting max object_xref_id";
}
$max_object_xref_id++;
my $added_count = 0;
my $ignored = 0;
foreach my $key (keys %$ref_to_alts){
$get_data_sth->execute($key);
my ($object_xref_id, $ensembl_object_type, $xref_id, $linkage_annotation,
$linkage_type, $ox_status, $unused_priority, $master_xref_id,
$query_identity, $target_identity, $hit_start, $hit_end,
$translation_start, $translation_end, $cigar_line, $score, $evalue);
$get_data_sth->bind_columns(\$object_xref_id, \$ensembl_object_type, \$xref_id, \$linkage_annotation,
\$linkage_type, \$ox_status, \$unused_priority, \$master_xref_id,
\$query_identity, \$target_identity, \$hit_start, \$hit_end,
\$translation_start, \$translation_end, \$cigar_line, \$score, \$evalue);
while( $get_data_sth->fetch()){
foreach my $alt (@{$ref_to_alts->{$key}}){
$max_object_xref_id++;
$insert_ox_sth->execute($max_object_xref_id, $alt, $ensembl_object_type, $xref_id, $linkage_annotation,
$linkage_type, $ox_status, $unused_priority, $master_xref_id) || croak "Could not insert object_xref data";
#ONLY add identity xref if object_xref was added successfully.
if( $insert_ox_sth->rows){
$added_count++;
$insert_ix_sth->execute($max_object_xref_id, $query_identity, $target_identity, $hit_start, $hit_end,
$translation_start, $translation_end, $cigar_line, $score, $evalue) || croak "Could not insert identity_xref data";
}
else{
$ignored++;
}
}
}
}
print "Added $added_count new mapping but ignored $ignored\n";
if($tester->unlinked_entries){
croak "Problems found after process_alt_alleles\n";
}
$self->update_process_status('alt_alleles_processed');
return;
}