$Bio::EnsEMBL::DBSQL::DBEntryAdaptor::VERSION
=
'112.0.0_57'
;
$Bio::EnsEMBL::DBSQL::DBEntryAdaptor::VERSION
=
'112.0.057'
;
@ISA
=
qw( Bio::EnsEMBL::DBSQL::BaseAdaptor )
;
sub
fetch_by_dbID {
my
(
$self
,
$dbID
) =
@_
;
my
$sth
=
$self
->prepare(
"SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.xref_id = ?
AND xref.external_db_id = exDB.external_db_id" );
$sth
->bind_param( 1,
$dbID
, SQL_INTEGER );
$sth
->execute();
my
$exDB
;
my
$max_rows
= 1000;
while
(
my
$rowcache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$refID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
if
( !
defined
(
$exDB
) ) {
$exDB
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
}
if
(
defined
(
$synonym
) ) {
$exDB
->add_synonym(
$synonym
) }
}
}
$sth
->finish();
return
$exDB
;
}
sub
_get_all_dm_loc_sth {
my
(
$self
,
$constraint
,
$ensembl_object
) =
@_
;
my
$object_type
;
if
(
$ensembl_object
->isa(
"Bio::EnsEMBL::Gene"
)){
$object_type
=
"Gene"
;
}
elsif
(
$ensembl_object
->isa(
"Bio::EnsEMBL::Transcript"
)){
$object_type
=
"Transcript"
;
}
elsif
(
$ensembl_object
->isa(
"Bio::EnsEMBL::Translation"
)){
$object_type
=
"Translation"
;
}
elsif
(
$ensembl_object
->isa(
"Bio::EnsEMBL::Operon"
)){
$object_type
=
"Operon"
;
}
elsif
(
$ensembl_object
->isa(
"Bio::EnsEMBL::OperonTranscript"
)){
$object_type
=
"OperonTranscript"
;
}
else
{
warn
(
ref
(
$ensembl_object
).
" is not a Gene Transcript or Translation object??\n"
);
return
undef
;
}
my
$sql
= "SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB, dependent_xref dx, object_xref ox)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.external_db_id = exDB.external_db_id AND
ox.xref_id = xref.xref_id AND
ox.ensembl_object_type = \'
$object_type
\' AND
ox.ensembl_id = ".
$ensembl_object
->dbID();
if
(
$constraint
){
$sql
.=
" AND $constraint"
;
}
else
{
die
"NO constraint???\n"
;
}
my
$sth
=
$self
->prepare(
$sql
) ||
die
"Could not prepare $sql"
;
return
$self
->_get_all_dm(
$sth
);
}
sub
_get_all_dm_sth {
my
(
$self
,
$constraint
) =
@_
;
my
$sql
= "SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB, dependent_xref dx)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.external_db_id = exDB.external_db_id ";
if
(
$constraint
){
$sql
.=
"AND $constraint"
;
}
else
{
die
"NO constraint???\n"
;
}
my
$sth
=
$self
->prepare(
$sql
) ||
die
"Could not prepare $sql"
;
return
$self
->_get_all_dm(
$sth
);
}
sub
_get_all_dm{
my
(
$self
,
$sth
) =
@_
;
$sth
->execute() ||
die
"Not able to execute statement handle"
;
my
@list
=();
my
%seen
;
my
$max_rows
= 1000;
while
(
my
$rowcache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$dbID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
if
( !
defined
(
$seen
{
$dbID
}) ) {
my
$exDB
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
if
(
$synonym
) {
$exDB
->add_synonym(
$synonym
) };
$seen
{
$dbID
} = 1;
push
@list
,
$exDB
;
}
}
}
$sth
->finish();
return
\
@list
;
}
sub
get_all_dependents {
my
(
$self
,
$dbid
,
$ensembl_object
) =
@_
;
if
(
defined
(
$ensembl_object
) and !(
$ensembl_object
->isa(
"Bio::EnsEMBL::Feature"
) or
$ensembl_object
->isa(
"Bio::EnsEMBL::Translation"
))){
die
ref
(
$ensembl_object
).
" is not an Gene Transcript or Translation"
;
}
my
$constraint
=
" dx.master_xref_id = $dbid AND dx.dependent_xref_id = xref.xref_id"
;
if
(
defined
(
$ensembl_object
)){
return
$self
->_get_all_dm_loc_sth(
$constraint
,
$ensembl_object
);
}
else
{
return
$self
->_get_all_dm_sth(
$constraint
,
$ensembl_object
);
}
}
sub
get_all_masters {
my
(
$self
,
$dbid
,
$ensembl_object
) =
@_
;
if
(
defined
(
$ensembl_object
) and !(
$ensembl_object
->isa(
"Bio::EnsEMBL::Feature"
) or
$ensembl_object
->isa(
"Bio::EnsEMBL::Translation"
))){
die
ref
(
$ensembl_object
).
" is not an Gene Transcript or Translation"
;
}
my
$constraint
=
"dx.dependent_xref_id = $dbid AND dx.master_xref_id = xref.xref_id"
;
if
(
defined
(
$ensembl_object
)){
return
$self
->_get_all_dm_loc_sth(
$constraint
,
$ensembl_object
);
}
else
{
return
$self
->_get_all_dm_sth(
$constraint
,
$ensembl_object
);
}
}
sub
fetch_all_by_name {
my
(
$self
,
$name
,
$dbname
) =
@_
;
my
$sql
= (
<<SQL);
SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
exDB.priority, exDB.db_name, exDB.db_display_name, exDB.db_release,
es.synonym, xref.info_type, xref.info_text,
exDB.type, exDB.secondary_db_name, exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE (xref.dbprimary_acc = ? or xref.display_label = ?)
AND xref.external_db_id = exDB.external_db_id
SQL
if
(
defined
$dbname
){
$sql
.=
" AND exDB.db_name = ?"
;
}
my
$sth
=
$self
->prepare(
$sql
);
$sth
->bind_param( 1,
$name
, SQL_VARCHAR );
$sth
->bind_param( 2,
$name
, SQL_VARCHAR );
if
(
defined
$dbname
){
$sth
->bind_param( 3 ,
$dbname
, SQL_VARCHAR );
}
$sth
->execute();
my
$max_rows
= 1000;
my
$precache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
);
if
( !
$sth
->rows() &&
lc
(
$dbname
) eq
'interpro'
) {
$precache
=
undef
;
$sth
->finish();
$sth
=
$self
->prepare(
"SELECT NULL,
i.interpro_ac,
i.id,
NULL,
NULL,
'Interpro'
,
NULL,
NULL
FROM interpro i
WHERE i.interpro_ac = ?" );
$sth
->bind_param( 1,
$name
, SQL_VARCHAR );
$sth
->execute();
}
my
%exDB
;
my
@exDBlist
;
while
(
my
$rowcache
=
$precache
||
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
$precache
=
undef
;
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$dbID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
if
( !
defined
$exDB
{
$dbID
} ) {
my
$entrie
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
$exDB
{
$dbID
} =
$entrie
;
push
@exDBlist
,
$entrie
;
}
if
(
$synonym
) {
$exDB
{
$dbID
}->add_synonym(
$synonym
) }
}
}
$sth
->finish();
return
\
@exDBlist
;
}
sub
fetch_by_db_accession {
my
(
$self
,
$dbname
,
$accession
) =
@_
;
my
$sth
=
$self
->prepare(
"SELECT xref.xref_id,
xref.dbprimary_acc,
xref.display_label,
xref.version,
exDB.priority,
exDB.db_name,
exDB.db_display_name,
exDB.db_release,
es.synonym,
xref.info_type,
xref.info_text,
exDB.type,
exDB.secondary_db_name,
exDB.secondary_db_table,
xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es ON
es.xref_id = xref.xref_id
WHERE xref.dbprimary_acc = ?
AND exDB.db_name like ?
AND xref.external_db_id = exDB.external_db_id" );
$sth
->bind_param( 1,
$accession
, SQL_VARCHAR );
$sth
->bind_param( 2,
$dbname
, SQL_VARCHAR );
$sth
->execute();
my
$max_rows
= 1000;
my
$precache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
);
if
( !
$sth
->rows() &&
lc
(
$dbname
) eq
'interpro'
) {
$precache
=
undef
;
$sth
->finish();
$sth
=
$self
->prepare(
"SELECT NULL,
i.interpro_ac,
i.id,
NULL,
NULL,
'Interpro'
,
NULL,
NULL
FROM interpro i
WHERE i.interpro_ac = ?" );
$sth
->bind_param( 1,
$accession
, SQL_VARCHAR );
$sth
->execute();
}
my
$exDB
;
while
(
my
$rowcache
=
$precache
||
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
$precache
=
undef
;
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$dbID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
if
( !
defined
(
$exDB
) ) {
$exDB
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
}
if
(
$synonym
) {
$exDB
->add_synonym(
$synonym
) }
}
}
$sth
->finish();
return
$exDB
;
}
sub
store {
my
(
$self
,
$dbEntry
,
$ensID
,
$ensType
,
$ignore_release
,
$master_xref
) =
@_
;
my
$dbJustInserted
;
my
$ensembl_id
;
if
(
defined
(
$ensID
) ) {
if
(
$ensID
=~ /^\d+$/ ) {
$ensembl_id
=
$ensID
;
}
elsif
(
ref
(
$ensID
) eq
'Bio::EnsEMBL::Gene'
or
ref
(
$ensID
) eq
'Bio::EnsEMBL::Transcript'
or
ref
(
$ensID
) eq
'Bio::EnsEMBL::Translation'
or
ref
(
$ensID
) eq
'Bio::EnsEMBL::OperonTranscript'
or
ref
(
$ensID
) eq
'Bio::EnsEMBL::Operon'
)
{
warning(
"You should pass DBEntryAdaptor->store() "
.
"a dbID rather than an ensembl object "
.
"to store the xref on"
);
if
(
defined
(
$ensID
->dbID() ) ) {
$ensembl_id
=
$ensID
->dbID();
}
else
{
throw(
sprintf
(
"%s %s doesn't have a dbID, can't store xref"
,
$ensType
,
$ensID
->display_id() ) );
}
}
else
{
throw(
"Invalid dbID passed to DBEntryAdaptor->store()"
);
}
}
my
$dbRef
;
$dbRef
=
$self
->_check_external_db(
$dbEntry
,
$ignore_release
);
my
$xref_id
=
$self
->_store_or_fetch_xref(
$dbEntry
,
$dbRef
);
$dbEntry
->dbID(
$xref_id
);
my
$object_xref_id
;
if
(
$ensembl_id
) {
$object_xref_id
=
$self
->_store_object_xref_mapping(
$ensembl_id
,
$dbEntry
,
$ensType
,
$ignore_release
)};
if
(
defined
$master_xref
&&
defined
$object_xref_id
) {
$self
->_store_dependent_xref_mapping(
$object_xref_id
,
$dbEntry
,
$master_xref
); }
return
$xref_id
;
}
sub
update {
my
(
$self
,
$dbEntry
) =
@_
;
if
(!
defined
$dbEntry
|| !
ref
$dbEntry
|| !
$dbEntry
->isa(
'Bio::EnsEMBL::DBEntry'
)) {
throw(
"Must update a dbentry object, not a $dbEntry"
);
}
my
$update_dbentry_sql
=
qq(
UPDATE xref
SET dbprimary_acc = ?,
display_label = ?,
version = ?,
description = ?,
external_db_id = ?,
info_type = ?,
info_text = ?
WHERE xref_id = ?
)
;
my
$dbRef
=
$self
->_check_external_db(
$dbEntry
);
my
$display_id
=
$dbEntry
->display_id;
$display_id
=
''
unless
defined
$display_id
;
my
$sth
=
$self
->prepare(
$update_dbentry_sql
);
$sth
->bind_param(1,
$dbEntry
->primary_id,SQL_VARCHAR);
$sth
->bind_param(2,
$display_id
,SQL_VARCHAR);
$sth
->bind_param(3, (
$dbEntry
->version ||
q{0}
),SQL_VARCHAR);
$sth
->bind_param(4,
$dbEntry
->description,SQL_VARCHAR);
$sth
->bind_param(5,
$dbRef
,SQL_INTEGER);
$sth
->bind_param(6, (
$dbEntry
->info_type ||
'NONE'
), SQL_VARCHAR);
$sth
->bind_param(7, (
$dbEntry
->info_text ||
''
), SQL_VARCHAR);
$sth
->bind_param(8,
$dbEntry
->dbID(), SQL_INTEGER);
$sth
->execute();
}
sub
_store_object_xref_mapping {
my
$self
=
shift
;
my
$ensembl_id
=
shift
;
my
$dbEntry
=
shift
;
my
$ensembl_type
=
shift
;
my
$ignore_release
=
shift
;
my
$dbc
=
$self
->dbc();
my
$sql_helper
=
$dbc
->sql_helper();
if
(not
defined
(
$ensembl_type
)) { warning(
"No Ensembl data type provided for new xref"
);}
my
$analysis_id
;
if
(
$dbEntry
->analysis() ) {
$analysis_id
=
$self
->db()->get_AnalysisAdaptor->store(
$dbEntry
->analysis() );
}
my
$insert_ignore
=
$self
->insert_ignore_clause();
my
$insert_params
= [
[
$dbEntry
->dbID(), SQL_INTEGER],
[
$ensembl_type
, SQL_VARCHAR],
[
$ensembl_id
, SQL_INTEGER],
[
$dbEntry
->linkage_annotation(),SQL_VARCHAR],
[
$analysis_id
, SQL_INTEGER],
];
my
$base_object_xref_insert_sql
=
qq(
${insert_ignore} INTO object_xref
( xref_id,
ensembl_object_type,
ensembl_id,
linkage_annotation,
analysis_id )
VALUES ( ?, ?, ?, ?, ? )
);
my
$object_xref_id
;
if
(
$dbc
->driver() eq
'mysql'
) {
my
$sql
=
$base_object_xref_insert_sql
.
'ON DUPLICATE KEY UPDATE object_xref_id=LAST_INSERT_ID(object_xref_id)'
;
$sql_helper
->execute_update(
-SQL
=>
$sql
,
-PARAMS
=>
$insert_params
);
$object_xref_id
=
$self
->last_insert_id(
'object_xref_id'
,
undef
,
'object_xref'
);
}
else
{
my
$updated_rows
=
$sql_helper
->execute_update(
-SQL
=>
$base_object_xref_insert_sql
,
-PARAMS
=>
$insert_params
);
if
(
$updated_rows
== 1) {
$object_xref_id
=
$self
->last_insert_id(
'object_xref_id'
,
undef
,
'object_xref'
);
}
else
{
my
$sql
=
'select object_xref_id from object_xref where xref_id =? and ensembl_object_type =? and ensembl_id =?'
;
my
$params
= [
[
$dbEntry
->dbID(), SQL_INTEGER],
[
$ensembl_type
, SQL_VARCHAR],
[
$ensembl_id
, SQL_INTEGER],
];
$object_xref_id
=
$sql_helper
->execute_single_result(
-SQL
=>
$sql
,
-PARAMS
=>
$params
);
}
}
$dbEntry
->adaptor(
$self
);
my
$sth
;
if
(
$dbEntry
->isa(
'Bio::EnsEMBL::IdentityXref'
) ) {
$sth
=
$self
->prepare( "
${insert_ignore} INTO identity_xref
( object_xref_id,
xref_identity,
ensembl_identity,
xref_start,
xref_end ,
ensembl_start,
ensembl_end,
cigar_line,
score,
evalue )
VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
$sth
->bind_param( 1,
$object_xref_id
, SQL_INTEGER );
$sth
->bind_param( 2,
$dbEntry
->xref_identity, SQL_INTEGER );
$sth
->bind_param( 3,
$dbEntry
->ensembl_identity, SQL_INTEGER );
$sth
->bind_param( 4,
$dbEntry
->xref_start, SQL_INTEGER );
$sth
->bind_param( 5,
$dbEntry
->xref_end, SQL_INTEGER );
$sth
->bind_param( 6,
$dbEntry
->ensembl_start, SQL_INTEGER );
$sth
->bind_param( 7,
$dbEntry
->ensembl_end, SQL_INTEGER );
$sth
->bind_param( 8,
$dbEntry
->cigar_line, SQL_LONGVARCHAR );
$sth
->bind_param( 9,
$dbEntry
->score, SQL_DOUBLE );
$sth
->bind_param( 10,
$dbEntry
->evalue, SQL_DOUBLE );
$sth
->execute();
}
elsif
(
$dbEntry
->isa(
'Bio::EnsEMBL::OntologyXref'
) ) {
$sth
=
$self
->prepare( "
${insert_ignore} INTO ontology_xref
( object_xref_id,
source_xref_id,
linkage_type )
VALUES ( ?, ?, ? )" );
foreach
my
$info
( @{
$dbEntry
->get_all_linkage_info() } ) {
my
(
$linkage_type
,
$sourceXref
) = @{
$info
};
my
$sourceXid
=
undef
;
if
(
$sourceXref
) {
$sourceXref
->is_stored(
$self
->dbc ) ||
$self
->store(
$sourceXref
,
undef
,
undef
,
$ignore_release
);
$sourceXid
=
$sourceXref
->dbID;
}
$sth
->bind_param( 1,
$object_xref_id
, SQL_INTEGER );
$sth
->bind_param( 2,
$sourceXid
, SQL_INTEGER );
$sth
->bind_param( 3,
$linkage_type
, SQL_VARCHAR );
$sth
->execute();
}
$sth
=
$self
->prepare( "
${insert_ignore} INTO associated_xref
( object_xref_id,
xref_id,
source_xref_id,
condition_type,
associated_group_id,
`rank` )
VALUES ( ?, ?, ?, ?, ?, ? ) " );
my
$annotext
=
$dbEntry
->get_all_associated_xrefs();
foreach
my
$ax_group
(
sort
keys
%{
$annotext
}) {
my
$group
=
$annotext
->{
$ax_group
};
my
$gsth
=
$self
->prepare( "
INSERT INTO associated_group
( description )
VALUES ( ? )" );
$gsth
->bind_param( 1,
$ax_group
, SQL_VARCHAR );
$gsth
->execute();
my
$associatedGid
=
$self
->last_insert_id(
'associated_group_id'
,
undef
,
'associated_group'
);
foreach
my
$ax_rank
(
sort
keys
%{
$group
}) {
my
@ax
= @{
$group
->{
$ax_rank
} };
my
$associatedXid
=
undef
;
my
$sourceXid
=
undef
;
if
(!
$ax
[0]->dbID) {
$self
->store(
$ax
[0]);
}
$associatedXid
=
$ax
[0]->dbID;
if
(!
$ax
[1]->dbID) {
$self
->store(
$ax
[1]);
}
$sourceXid
=
$ax
[1]->dbID;
if
(!
defined
$associatedXid
|| !
defined
$sourceXid
) {
next
;
}
$sth
->bind_param( 1,
$object_xref_id
, SQL_INTEGER );
$sth
->bind_param( 2,
$associatedXid
, SQL_INTEGER );
$sth
->bind_param( 3,
$sourceXid
, SQL_INTEGER );
$sth
->bind_param( 4,
$ax
[2], SQL_VARCHAR );
$sth
->bind_param( 5,
$associatedGid
, SQL_VARCHAR );
$sth
->bind_param( 6,
$ax_rank
, SQL_INTEGER );
$sth
->execute();
}
}
}
return
$object_xref_id
;
}
sub
_store_dependent_xref_mapping {
my
$self
=
shift
;
my
$object_xref_id
=
shift
;
my
$dbEntry
=
shift
;
my
$master_xref
=
shift
;
my
$insert_ignore
=
$self
->insert_ignore_clause();
my
$sth
=
$self
->prepare( "
${insert_ignore} INTO dependent_xref
( object_xref_id,
master_xref_id,
dependent_xref_id )
VALUES ( ?, ?, ?)" );
$sth
->bind_param( 1,
$object_xref_id
, SQL_INTEGER );
$sth
->bind_param( 2,
$master_xref
->dbID, SQL_INTEGER );
$sth
->bind_param( 3,
$dbEntry
->dbID, SQL_INTEGER );
$sth
->execute();
}
sub
get_external_db_id {
my
$self
=
shift
;
my
$db_ids
=
$self
->get_external_db_ids(
@_
);
return
shift
@$db_ids
;
}
sub
get_external_db_ids {
my
(
$self
,
$db_name
,
$db_release
,
$ignore_release
) =
@_
;
my
$sql_helper
=
$self
->dbc->sql_helper;
my
$sql
=
'SELECT external_db_id FROM external_db WHERE db_name'
;
if
(
$db_name
=~ /%|_/) {
$sql
.=
' LIKE ?'
;
}
else
{
$sql
.=
' = ?'
;
}
my
@bound_params
;
push
@bound_params
,
$db_name
;
unless
(
$ignore_release
) {
if
(
$db_release
) {
$sql
.=
' AND db_release = ?'
;
push
@bound_params
,
$db_release
;
}
else
{
$sql
.=
' AND db_release is NULL'
;
}
}
my
@db_ids
= @{
$sql_helper
->execute_simple(
-SQL
=>
$sql
,
-PARAMS
=> \
@bound_params
) };
return
\
@db_ids
;
}
sub
get_distinct_external_dbs {
my
(
$self
) =
@_
;
my
$sql
=
'SELECT DISTINCT db_name FROM external_db'
;
my
$names
=
$self
->dbc->sql_helper->execute_simple(
-SQL
=>
$sql
);
return
[
sort
@{
$names
} ];
}
sub
_check_external_db {
my
(
$self
,
$db_entry
,
$ignore
) =
@_
;
my
(
$db_name
,
$db_release
);
$db_name
=
$db_entry
->dbname();
$db_release
=
$db_entry
->release();
my
$db_id
=
$self
->get_external_db_id(
$db_name
,
$db_release
,
$ignore
);
if
(
$db_id
) {
return
$db_id
;
}
else
{
throw(
sprintf
(
"external_db [%s] release [%s] does not exist"
,
$db_name
,
$db_release
)
);
}
}
sub
_store_or_fetch_xref {
my
$self
=
shift
;
my
$dbEntry
=
shift
;
my
$dbRef
=
shift
;
my
$xref_id
;
my
$display_id
=
$dbEntry
->display_id;
$display_id
=
''
unless
defined
$display_id
;
my
$insert_ignore
=
$self
->insert_ignore_clause();
my
$sth
=
$self
->prepare( "
${insert_ignore} INTO xref
( dbprimary_acc,
display_label,
version,
description,
external_db_id,
info_type,
info_text )
VALUES ( ?, ?, ?, ?, ?, ?, ? ) ");
$sth
->bind_param(1,
$dbEntry
->primary_id,SQL_VARCHAR);
$sth
->bind_param(2,
$display_id
,SQL_VARCHAR);
$sth
->bind_param(3, (
$dbEntry
->version ||
q{0}
),SQL_VARCHAR);
$sth
->bind_param(4,
$dbEntry
->description,SQL_VARCHAR);
$sth
->bind_param(5,
$dbRef
,SQL_INTEGER);
$sth
->bind_param(6, (
$dbEntry
->info_type ||
'NONE'
), SQL_VARCHAR);
$sth
->bind_param(7, (
$dbEntry
->info_text ||
''
), SQL_VARCHAR);
my
$count
=
$sth
->execute();
$xref_id
=
$self
->last_insert_id(
'xref_id'
,
undef
,
'xref'
)
if
$count
> 0;
$sth
->finish();
if
(
$xref_id
) {
my
$synonym_check_sth
=
$self
->prepare(
"SELECT xref_id, synonym
FROM external_synonym
WHERE xref_id = ?
AND synonym = ?");
my
$synonym_store_sth
=
$self
->prepare(
"${insert_ignore} INTO external_synonym
( xref_id, synonym ) VALUES ( ?, ? ) ");
my
$synonyms
=
$dbEntry
->get_all_synonyms();
foreach
my
$syn
(
@$synonyms
) {
$synonym_check_sth
->bind_param(1,
$xref_id
,SQL_INTEGER);
$synonym_check_sth
->bind_param(2,
$syn
,SQL_VARCHAR);
$synonym_check_sth
->execute();
my
(
$dbSyn
) =
$synonym_check_sth
->fetchrow_array();
$synonym_store_sth
->bind_param(1,
$xref_id
,SQL_INTEGER);
$synonym_store_sth
->bind_param(2,
$syn
,SQL_VARCHAR);
$synonym_store_sth
->execute()
if
(!
$dbSyn
);
}
$synonym_check_sth
->finish();
$synonym_store_sth
->finish();
}
else
{
my
$sql
= 'SELECT xref_id FROM xref
WHERE dbprimary_acc = ?
AND version =?
AND external_db_id = ?
AND info_type = ?
AND info_text = ?';
my
$info_type
=
$dbEntry
->info_type() ||
'NONE'
;
my
$info_text
=
$dbEntry
->info_text() ||
q{}
;
my
$version
=
$dbEntry
->version() ||
q{0}
;
$sth
=
$self
->prepare(
$sql
);
$sth
->bind_param(1,
$dbEntry
->primary_id,SQL_VARCHAR);
$sth
->bind_param(2,
$version
, SQL_VARCHAR);
$sth
->bind_param(3,
$dbRef
, SQL_INTEGER);
$sth
->bind_param(4,
$info_type
, SQL_VARCHAR);
$sth
->bind_param(5,
$info_text
, SQL_VARCHAR);
$sth
->execute();
(
$xref_id
) =
$sth
->fetchrow_array();
$sth
->finish;
if
(!
$xref_id
) {
my
$msg
=
'Cannot find an xref id for %s (version=%d) with external db id %d.'
;
throw(
sprintf
(
$msg
,
$dbEntry
->primary_id(),
$version
,
$dbRef
))
}
}
return
$xref_id
;
}
sub
exists
{
my
(
$self
,
$dbe
) =
@_
;
unless
(
$dbe
&&
ref
$dbe
&&
$dbe
->isa(
'Bio::EnsEMBL::DBEntry'
)) {
throw(
"arg must be a Bio::EnsEMBL::DBEntry not [$dbe]"
);
}
my
$sth
=
$self
->prepare('SELECT x.xref_id
FROM xref x, external_db xdb
WHERE x.external_db_id = xdb.external_db_id
AND x.display_label = ?
AND xdb.db_name = ?
AND x.dbprimary_acc = ?');
$sth
->bind_param(1,
$dbe
->display_id,SQL_VARCHAR);
$sth
->bind_param(2,
$dbe
->dbname,SQL_VARCHAR);
$sth
->bind_param(3,
$dbe
->primary_id,SQL_VARCHAR);
$sth
->execute();
my
(
$dbID
) =
$sth
->fetchrow_array;
$sth
->finish;
return
$dbID
;
}
sub
fetch_all_by_Gene {
my
(
$self
,
$gene
,
$ex_db_reg
,
$exdb_type
) =
@_
;
if
(!
ref
(
$gene
) || !
$gene
->isa(
'Bio::EnsEMBL::Gene'
)) {
throw(
"Bio::EnsEMBL::Gene argument expected."
);
}
return
$self
->_fetch_by_object_type(
$gene
->dbID(),
'Gene'
,
$ex_db_reg
,
$exdb_type
);
}
sub
fetch_all_by_Operon {
my
(
$self
,
$gene
,
$ex_db_reg
,
$exdb_type
) =
@_
;
if
(!
ref
(
$gene
) || !
$gene
->isa(
'Bio::EnsEMBL::Operon'
)) {
throw(
"Bio::EnsEMBL::Operon argument expected."
);
}
return
$self
->_fetch_by_object_type(
$gene
->dbID(),
'Operon'
,
$ex_db_reg
,
$exdb_type
);
}
sub
fetch_all_by_Transcript {
my
(
$self
,
$trans
,
$ex_db_reg
,
$exdb_type
) =
@_
;
if
(!
ref
(
$trans
) || !
$trans
->isa(
'Bio::EnsEMBL::Transcript'
)) {
throw(
"Bio::EnsEMBL::Transcript argument expected."
);
}
return
$self
->_fetch_by_object_type(
$trans
->dbID(),
'Transcript'
,
$ex_db_reg
,
$exdb_type
);
}
sub
fetch_all_by_Translation {
my
(
$self
,
$trans
,
$ex_db_reg
,
$exdb_type
) =
@_
;
if
(!
ref
(
$trans
) || !
$trans
->isa(
'Bio::EnsEMBL::Translation'
)) {
throw(
'Bio::EnsEMBL::Translation argument expected.'
);
}
if
( !
$trans
->dbID ){
warning(
"Cannot fetch_all_by_Translation without a dbID"
);
return
[];
}
return
$self
->_fetch_by_object_type(
$trans
->dbID(),
'Translation'
,
$ex_db_reg
,
$exdb_type
);
}
sub
fetch_all_by_RNAProduct {
my
(
$self
,
$rp
,
$ex_db_reg
,
$ex_db_type
) =
@_
;
if
(!
ref
(
$rp
) || !
$rp
->isa(
'Bio::EnsEMBL::RNAProduct'
)) {
throw(
'Bio::EnsEMBL::RNAProduct argument expected.'
);
}
if
(!
$rp
->dbID()){
warning(
"Cannot fetch_all_by_RNAProduct without a dbID"
);
return
[];
}
return
$self
->_fetch_by_object_type(
$rp
->dbID(),
'RNAProduct'
,
$ex_db_reg
,
$ex_db_type
);
}
sub
remove_from_object {
my
$self
=
shift
;
my
$dbe
=
shift
;
my
$object
=
shift
;
my
$object_type
=
shift
;
if
(!
ref
(
$dbe
) || !
$dbe
->isa(
'Bio::EnsEMBL::DBEntry'
)) {
throw(
"Bio::EnsEMBL::DBEntry argument expected."
);
}
if
(!
ref
(
$object
) || !
$dbe
->isa(
'Bio::EnsEMBL::Storable'
)) {
throw(
"Bio::EnsEMBL::Storable argument expected."
);
}
if
(!
$object_type
) {
throw(
"object_type string argument expected."
);
}
if
(!
$object
->is_stored(
$self
->db())) {
warning(
"Cannot remove DBEntries for $object_type "
.
$object
->dbID() .
". Object is not stored in this database."
);
return
;
}
if
(!
$dbe
->is_stored(
$self
->db())) {
warning(
"Cannot remove DBEntry "
.
$dbe
->dbID() .
". Is not stored "
.
"in this database."
);
return
;
}
my
$sth
=
$self
->prepare
(
"SELECT ox.object_xref_id "
.
"FROM object_xref ox "
.
"WHERE ox.xref_id = ? "
.
"AND ox.ensembl_id = ? "
.
"AND ox.ensembl_object_type = ?"
);
$sth
->bind_param(1,
$dbe
->dbID,SQL_INTEGER);
$sth
->bind_param(2,
$object
->dbID,SQL_INTEGER);
$sth
->bind_param(3,
$object_type
,SQL_VARCHAR);
$sth
->execute();
my
(
$ox_id
) =
$sth
->fetchrow_array();
if
(!
$sth
->rows() == 1) {
$sth
->finish();
return
;
}
$sth
->finish();
$sth
=
$self
->prepare(
"DELETE FROM ontology_xref WHERE object_xref_id = ?"
);
$sth
->bind_param(1,
$ox_id
,SQL_INTEGER);
$sth
->execute();
$sth
->finish();
$sth
=
$self
->prepare(
"DELETE FROM associated_xref WHERE object_xref_id = ?"
);
$sth
->bind_param(1,
$ox_id
,SQL_INTEGER);
$sth
->execute();
$sth
->finish();
$sth
=
$self
->prepare(
"DELETE FROM identity_xref WHERE object_xref_id = ?"
);
$sth
->bind_param(1,
$ox_id
,SQL_INTEGER);
$sth
->execute();
$sth
->finish();
$sth
=
$self
->prepare(
"DELETE FROM object_xref WHERE object_xref_id = ?"
);
$sth
->bind_param(1,
$ox_id
,SQL_INTEGER);
$sth
->execute();
$sth
->finish();
return
;
}
sub
_fetch_by_object_type {
my
(
$self
,
$ensID
,
$ensType
,
$exdbname
,
$exdb_type
) =
@_
;
my
@out
;
if
( !
defined
(
$ensID
) ) {
throw(
"Can't fetch_by_EnsObject_type without an object"
);
}
if
( !
defined
(
$ensType
) ) {
throw(
"Can't fetch_by_EnsObject_type without a type"
);
}
my
$schema_version
=
$self
->schema_version();
my
$columns_sql
= (
<<COLUMNS_SQL);
SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label, xref.version,
exDB.priority,
exDB.db_name, exDB.db_release, exDB.status, exDB.db_display_name,
exDB.secondary_db_name, exDB.secondary_db_table,
oxr.object_xref_id,
es.synonym,
idt.xref_identity, idt.ensembl_identity, idt.xref_start,
idt.xref_end, idt.ensembl_start, idt.ensembl_end,
idt.cigar_line, idt.score, idt.evalue, oxr.analysis_id,
gx.linkage_type,
xref.info_type, xref.info_text, exDB.type, gx.source_xref_id,
oxr.linkage_annotation, xref.description
COLUMNS_SQL
my
$tables_sql
=
<<'TABLES_SQL';
FROM (xref xref, external_db exDB, object_xref oxr)
LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
LEFT JOIN identity_xref idt on idt.object_xref_id = oxr.object_xref_id
LEFT JOIN ontology_xref gx on gx.object_xref_id = oxr.object_xref_id
TABLES_SQL
my
$where_sql
=
<<'WHERE_SQL';
WHERE xref.xref_id = oxr.xref_id
AND xref.external_db_id = exDB.external_db_id
AND oxr.ensembl_id = ?
AND oxr.ensembl_object_type = ?
WHERE_SQL
if
(
$schema_version
>= 72) {
$columns_sql
.=
','
;
$columns_sql
.=
<<'EXTRA';
ax.xref_id, ax.source_xref_id, ax.condition_type, ax.associated_group_id, ax.rank
EXTRA
$tables_sql
.=
<<'EXTRA';
LEFT JOIN associated_xref ax ON ax.object_xref_id = oxr.object_xref_id
LEFT JOIN associated_group ag ON ax.associated_group_id = ag.associated_group_id
EXTRA
}
my
$sql
= "
$columns_sql
$tables_sql
$where_sql
";
if
(
defined
(
$exdbname
) ) {
if
(
index
(
$exdbname
,
'%'
) != -1 ) {
$sql
.=
" AND exDB.db_name LIKE "
.
$self
->dbc()->db_handle()->quote(
$exdbname
, SQL_VARCHAR );
}
else
{
$sql
.=
" AND exDB.db_name = "
.
$self
->dbc()->db_handle()->quote(
$exdbname
, SQL_VARCHAR );
}
}
if
(
defined
(
$exdb_type
) ) {
if
(
index
(
$exdb_type
,
'%'
) != -1 ) {
$sql
.=
" AND exDB.type LIKE "
.
$self
->dbc()->db_handle()->quote(
$exdb_type
, SQL_VARCHAR );
}
else
{
$sql
.=
" AND exDB.type = "
.
$self
->dbc()->db_handle()->quote(
$exdb_type
, SQL_VARCHAR );
}
}
my
$sth
=
$self
->prepare(
$sql
);
$sth
->bind_param( 1,
$ensID
, SQL_INTEGER );
$sth
->bind_param( 2,
$ensType
, SQL_VARCHAR );
$sth
->execute();
my
(
%seen
,
%linkage_types
,
%synonyms
,
%associated_xrefs
);
my
$max_rows
= 1000;
while
(
my
$rowcache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
while
(
my
$arrRef
=
shift
( @{
$rowcache
} ) ) {
my
(
$refID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$release
,
$exDB_status
,
$exDB_db_display_name
,
$exDB_secondary_db_name
,
$exDB_secondary_db_table
,
$objid
,
$synonym
,
$xrefid
,
$ensemblid
,
$xref_start
,
$xref_end
,
$ensembl_start
,
$ensembl_end
,
$cigar_line
,
$score
,
$evalue
,
$analysis_id
,
$linkage_type
,
$info_type
,
$info_text
,
$type
,
$source_xref_id
,
$link_annotation
,
$description
,
$associated_xref_id
,
$source_associated_xref_id
,
$condition_type
,
$associate_group_id
,
$associate_group_rank
) =
@$arrRef
;
my
$linkage_key
=
(
$linkage_type
||
''
) . (
$source_xref_id
||
''
);
my
$associated_key
=
(
$condition_type
||
''
)
. (
$source_associated_xref_id
||
''
)
. (
$associated_xref_id
||
''
);
my
$analysis
=
undef
;
if
(
defined
(
$analysis_id
) ) {
$analysis
=
$self
->db()->get_AnalysisAdaptor()->fetch_by_dbID(
$analysis_id
);
}
my
%obj_hash
= (
'adaptor'
=>
$self
,
'dbID'
=>
$refID
,
'primary_id'
=>
$dbprimaryId
,
'display_id'
=>
$displayid
,
'version'
=>
$version
,
'release'
=>
$release
,
'info_type'
=>
$info_type
,
'info_text'
=>
$info_text
,
'type'
=>
$type
,
'secondary_db_name'
=>
$exDB_secondary_db_name
,
'secondary_db_table'
=>
$exDB_secondary_db_table
,
'dbname'
=>
$dbname
,
'description'
=>
$description
,
'linkage_annotation'
=>
$link_annotation
,
'analysis'
=>
$analysis
,
'ensembl_object_type'
=>
$ensType
,
'ensembl_id'
=>
$ensID
);
my
$source_xref
;
my
$associated_xref
;
my
$source_associated_xref
;
if
( !
$seen
{
$refID
} ) {
my
$exDB
;
if
( (
defined
(
$xrefid
) ) ) {
$exDB
= Bio::EnsEMBL::IdentityXref->new_fast( \
%obj_hash
);
$exDB
->xref_identity(
$xrefid
);
$exDB
->ensembl_identity(
$ensemblid
);
$exDB
->cigar_line(
$cigar_line
);
$exDB
->xref_start(
$xref_start
);
$exDB
->xref_end(
$xref_end
);
$exDB
->ensembl_start(
$ensembl_start
);
$exDB
->ensembl_end(
$ensembl_end
);
$exDB
->score(
$score
);
$exDB
->evalue(
$evalue
);
}
elsif
(
defined
$linkage_type
&&
$linkage_type
ne
""
) {
$exDB
= Bio::EnsEMBL::OntologyXref->new_fast( \
%obj_hash
);
$source_xref
= (
defined
(
$source_xref_id
)
?
$self
->fetch_by_dbID(
$source_xref_id
)
:
undef
);
$exDB
->add_linkage_type(
$linkage_type
,
$source_xref
|| () );
$linkage_types
{
$refID
}->{
$linkage_key
} = 1;
if
(
defined
$associated_xref_id
&&
$associated_xref_id
ne
""
) {
$exDB
= Bio::EnsEMBL::OntologyXref->new_fast( \
%obj_hash
);
$associated_xref
= (
defined
(
$associated_xref_id
)
?
$self
->fetch_by_dbID(
$associated_xref_id
)
:
undef
);
$source_associated_xref
= (
defined
(
$source_associated_xref_id
)
?
$self
->fetch_by_dbID(
$source_associated_xref_id
)
:
undef
);
if
(
defined
(
$associated_xref
) ) {
my
$ct
=
''
;
if
(
defined
$condition_type
) {
$ct
=
$condition_type
;
}
$exDB
->add_linked_associated_xref(
$associated_xref
,
$source_associated_xref
,
$ct
,
$associate_group_id
,
$associate_group_rank
);
}
}
}
else
{
$exDB
= Bio::EnsEMBL::DBEntry->new_fast( \
%obj_hash
);
}
if
(
defined
(
$exDB_status
) ) {
$exDB
->status(
$exDB_status
) }
$exDB
->priority(
$priority
);
$exDB
->db_display_name(
$exDB_db_display_name
);
push
(
@out
,
$exDB
);
$seen
{
$refID
} =
$exDB
;
}
if
(
defined
(
$synonym
) && !
$synonyms
{
$refID
}->{
$synonym
} ) {
if
(
defined
(
$synonym
) ) {
$seen
{
$refID
}->add_synonym(
$synonym
);
}
$synonyms
{
$refID
}->{
$synonym
} = 1;
}
if
(
defined
(
$linkage_type
)
&&
$linkage_type
ne
""
&& !
$linkage_types
{
$refID
}->{
$linkage_key
}
&& check_ref(
$seen
{
$refID
},
'Bio::EnsEMBL::OntologyXref'
) )
{
$source_xref
= (
defined
(
$source_xref_id
)
?
$self
->fetch_by_dbID(
$source_xref_id
)
:
undef
);
$seen
{
$refID
}
->add_linkage_type(
$linkage_type
,
$source_xref
|| () );
$linkage_types
{
$refID
}->{
$linkage_key
} = 1;
}
if
(
defined
(
$associated_xref_id
)
&&
$associated_xref_id
ne
""
&& !
$associated_xrefs
{
$refID
}->{
$associated_key
} )
{
$associated_xref
= (
defined
(
$associated_xref_id
)
?
$self
->fetch_by_dbID(
$associated_xref_id
)
:
undef
);
$source_associated_xref
= (
defined
(
$source_associated_xref_id
)
?
$self
->fetch_by_dbID(
$source_associated_xref_id
)
:
undef
);
if
(
defined
(
$associated_xref
) ) {
my
$ct
=
''
;
if
(
defined
$condition_type
) {
$ct
=
$condition_type
;
}
$seen
{
$refID
}->add_linked_associated_xref(
$associated_xref
,
$source_associated_xref
,
$ct
,
$associate_group_id
,
$associate_group_rank
);
}
$linkage_types
{
$refID
}->{
$linkage_key
} = 1;
}
}
}
return
\
@out
;
}
sub
list_gene_ids_by_external_db_id {
my
(
$self
,
$external_db_id
,
$linkage_type
) =
@_
;
my
%T
=
map
{ (
$_
, 1) }
$self
->_type_by_external_db_id(
$external_db_id
,
'Translation'
,
'gene'
,
$linkage_type
),
$self
->_type_by_external_db_id(
$external_db_id
,
'Transcript'
,
'gene'
,
$linkage_type
),
$self
->_type_by_external_db_id(
$external_db_id
,
'Gene'
,
undef
,
$linkage_type
);
return
keys
%T
;
}
sub
list_gene_ids_by_extids {
my
(
$self
,
$external_name
,
$external_db_name
,
$override
) =
@_
;
my
%T
=
map
{ (
$_
, 1 ) }
$self
->_type_by_external_id(
$external_name
,
'Translation'
,
'gene'
,
$external_db_name
,
$override
),
$self
->_type_by_external_id(
$external_name
,
'Transcript'
,
'gene'
,
$external_db_name
,
$override
),
$self
->_type_by_external_id(
$external_name
,
'Gene'
,
undef
,
$external_db_name
,
$override
);
return
keys
%T
;
}
sub
list_transcript_ids_by_extids {
my
(
$self
,
$external_name
,
$external_db_name
,
$override
) =
@_
;
my
%T
=
map
{ (
$_
, 1 ) }
$self
->_type_by_external_id(
$external_name
,
'Translation'
,
'transcript'
,
$external_db_name
,
$override
),
$self
->_type_by_external_id(
$external_name
,
'Transcript'
,
undef
,
$external_db_name
,
$override
);
return
keys
%T
;
}
sub
list_transcript_ids_by_external_db_id {
my
(
$self
,
$external_db_id
,
$linkage_type
) =
@_
;
my
%T
=
map
{ (
$_
, 1 ) }
$self
->_type_by_external_db_id(
$external_db_id
,
'Translation'
,
'transcript'
,
$linkage_type
),
$self
->_type_by_external_db_id(
$external_db_id
,
'Transcript'
,
'transcript'
,
$linkage_type
);
return
keys
%T
;
}
sub
list_translation_ids_by_extids {
my
(
$self
,
$external_name
,
$external_db_name
,
$override
) =
@_
;
return
$self
->_type_by_external_id(
$external_name
,
'Translation'
,
undef
,
$external_db_name
,
$override
);
}
sub
list_translation_ids_by_external_db_id {
my
(
$self
,
$external_db_id
,
$linkage_type
) =
@_
;
return
$self
->_type_by_external_db_id(
$external_db_id
,
'Translation'
,
undef
,
$linkage_type
),
}
sub
list_rnaproduct_ids_by_extids {
my
(
$self
,
$external_name
,
$external_db_name
,
$override
) =
@_
;
return
$self
->_type_by_external_id(
$external_name
,
'RNAProduct'
,
undef
,
$external_db_name
,
$override
);
}
sub
_type_by_external_id {
my
(
$self
,
$name
,
$ensType
,
$extraType
,
$external_db_name
,
$override
) =
@_
;
my
$comparison_operator
;
if
(
$name
=~ /[_%\[]/ ) {
$comparison_operator
=
"LIKE"
;
if
(
$name
=~ /^.?%/ && !
$override
) {
warn
"External $ensType name $name is too vague and will monopolise database resources. Please use a more specific $ensType name.\n"
;
return
;
}
elsif
(
$name
=~ /^\w\w_/ && !
$override
) {
$name
=~ s/(?<=\w\w)(?=_)/\\/;
}
}
else
{
$comparison_operator
=
"="
;
}
my
$from_sql
=
''
;
my
$where_sql
=
''
;
my
$ID_sql
=
'oxr.ensembl_id'
;
if
(
defined
(
$extraType
) ) {
if
(
lc
(
$extraType
) eq
'translation'
) {
$ID_sql
=
'tl.translation_id'
;
}
else
{
$ID_sql
=
"t.${extraType}_id"
;
}
if
(
lc
(
$ensType
) eq
'translation'
) {
$from_sql
=
'transcript t, translation tl, '
;
$where_sql
=
qq(
t.transcript_id = tl.transcript_id AND
tl.translation_id = oxr.ensembl_id AND
t.is_current = 1 AND
)
;
}
else
{
$from_sql
=
'transcript t, '
;
$where_sql
=
't.'
.
lc
(
$ensType
)
.
'_id = oxr.ensembl_id AND '
.
't.is_current = 1 AND '
;
}
}
my
$multispecies
=
$self
->db()->is_multispecies();
if
(
lc
(
$ensType
) eq
'gene'
) {
$from_sql
=
'gene g, '
;
$from_sql
.=
'seq_region s, coord_system cs, '
if
$multispecies
;
$where_sql
=
'g.gene_id = oxr.ensembl_id AND g.is_current = 1 AND '
;
if
(
$multispecies
) {
$where_sql
.=
<<'SQL';
g.seq_region_id = s.seq_region_id AND
s.coord_system_id = cs.coord_system_id AND
cs.species_id = ? AND
SQL
}
}
elsif
(
lc
(
$ensType
) eq
'transcript'
) {
$from_sql
=
'transcript t, '
;
$from_sql
.=
'seq_region s, coord_system cs, '
if
$multispecies
;
$where_sql
=
't.transcript_id = oxr.ensembl_id AND t.is_current = 1 AND '
;
if
(
$multispecies
) {
$where_sql
.=
<<'SQL';
t.seq_region_id = s.seq_region_id AND
s.coord_system_id = cs.coord_system_id AND
cs.species_id = ? AND
SQL
}
}
elsif
(
lc
(
$ensType
) eq
'translation'
) {
$from_sql
=
'translation tl, transcript t, '
;
$from_sql
.=
'seq_region s, coord_system cs, '
if
$multispecies
;
$where_sql
=
't.transcript_id = tl.transcript_id AND tl.translation_id = oxr.ensembl_id AND t.is_current = 1 AND '
;
if
(
$multispecies
) {
$where_sql
.=
<<'SQL';
t.seq_region_id = s.seq_region_id AND
s.coord_system_id = cs.coord_system_id AND
cs.species_id = ? AND
SQL
}
}
if
(
defined
(
$external_db_name
) ) {
$from_sql
.=
'external_db xdb, '
;
$where_sql
.=
'xdb.db_name LIKE '
.
$self
->dbc()->db_handle()->quote(
$external_db_name
.
'%'
)
.
' AND xdb.external_db_id = x.external_db_id AND'
;
}
my
@queries
;
push
(
@queries
,
qq(
SELECT $ID_sql
FROM $from_sql
xref x,
object_xref oxr
WHERE $where_sql
x.dbprimary_acc $comparison_operator ?
AND x.xref_id = oxr.xref_id
AND oxr.ensembl_object_type = ?
)
);
push
(
@queries
,
qq(
SELECT $ID_sql
FROM $from_sql
xref x,
object_xref oxr
WHERE $where_sql
x.display_label $comparison_operator ?
AND x.xref_id = oxr.xref_id
AND oxr.ensembl_object_type = ?
)
);
if
(
defined
(
$external_db_name
) ) {
push
(
@queries
,
qq(
SELECT $ID_sql
FROM $from_sql
external_synonym syn,
object_xref oxr,
xref x
WHERE $where_sql
syn.synonym $comparison_operator ?
AND syn.xref_id = oxr.xref_id
AND oxr.ensembl_object_type = ?
AND x.xref_id = oxr.xref_id)
);
}
else
{
push
(
@queries
,
qq(
SELECT $ID_sql
FROM $from_sql
external_synonym syn,
object_xref oxr
WHERE $where_sql
syn.synonym $comparison_operator ?
AND syn.xref_id = oxr.xref_id
AND oxr.ensembl_object_type = ?)
);
}
my
%result
;
my
$h
=
$self
->dbc()->sql_helper();
my
@params
= ([
$name
, SQL_VARCHAR], [
$ensType
, SQL_VARCHAR]);
unshift
(
@params
, [
$self
->species_id(), SQL_INTEGER] )
if
$multispecies
;
foreach
my
$query
(
@queries
) {
$h
->execute_no_return(
-SQL
=>
$query
,
-PARAMS
=> \
@params
,
-CALLBACK
=>
sub
{
my
(
$row
) =
@_
;
my
(
$id
) = @{
$row
};
$result
{
$id
} = 1;
});
}
return
keys
(
%result
);
}
sub
_type_by_external_db_id {
my
(
$self
,
$external_db_id
,
$ensType
,
$extraType
,
$linkage_type
) =
@_
;
throw
"No external DB identifer given"
unless
defined
$external_db_id
;
throw
"No Ensembl type given"
unless
defined
$ensType
;
my
$from_sql
=
''
;
my
$where_sql
=
''
;
my
$ID_sql
=
"oxr.ensembl_id"
;
my
$lcEnsType
=
lc
(
$ensType
);
if
(
defined
$extraType
) {
if
(
lc
(
$extraType
) eq
'translation'
) {
$ID_sql
=
"tl.translation_id"
;
}
else
{
$ID_sql
=
"t.${extraType}_id"
;
}
if
(
$lcEnsType
eq
'translation'
) {
$from_sql
=
'transcript t, translation tl, '
;
$where_sql
=
qq(
t.transcript_id = tl.transcript_id AND
tl.translation_id = oxr.ensembl_id AND
t.is_current = 1 AND
)
;
}
else
{
$from_sql
=
'transcript t, '
;
$where_sql
=
't.'
.
$lcEnsType
.
'_id = oxr.ensembl_id AND '
.
't.is_current = 1 AND '
;
}
}
if
(
$lcEnsType
eq
'gene'
) {
$from_sql
=
'gene g, '
;
$where_sql
=
'g.gene_id = oxr.ensembl_id AND g.is_current = 1 AND '
;
}
elsif
(
$lcEnsType
eq
'transcript'
) {
$from_sql
=
'transcript t, '
;
$where_sql
=
't.transcript_id = oxr.ensembl_id AND t.is_current = 1 AND '
;
}
elsif
(
$lcEnsType
eq
'translation'
) {
$from_sql
=
'transcript t, translation tl, '
;
$where_sql
=
qq(
t.transcript_id = tl.transcript_id AND
tl.translation_id = oxr.ensembl_id AND
t.is_current = 1 AND
)
;
}
if
(
$linkage_type
) {
$from_sql
.=
'ontology_xref ontx, '
;
$where_sql
.=
'oxr.object_xref_id = ontx.object_xref_id AND '
;
$where_sql
.=
'ontx.linkage_type =? AND'
;
}
my
$query
=
"SELECT
$ID_sql
FROM
$from_sql
xref x, object_xref oxr
WHERE
$where_sql
x.external_db_id = ? AND
x.xref_id = oxr.xref_id AND oxr.ensembl_object_type= ?";
my
@params
;
push
(
@params
, [
$linkage_type
, SQL_VARCHAR])
if
$linkage_type
;
push
(
@params
,
[
$external_db_id
, SQL_INTEGER],
[
$ensType
, SQL_VARCHAR],
);
my
%result
;
$self
->dbc()->sql_helper()->execute_no_return(
-SQL
=>
$query
,
-PARAMS
=> \
@params
,
-CALLBACK
=>
sub
{
my
(
$row
) =
@_
;
my
(
$id
) = @{
$row
};
$result
{
$id
} = 1;
});
return
keys
(
%result
);
}
sub
fetch_all_by_description {
my
(
$self
,
$description
,
$dbname
) =
@_
;
my
@results
= ();
my
$sql
=
"SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
xref.version,
exDB.priority,
exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
exDB.secondary_db_table, xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
WHERE xref.description like ?
AND xref.external_db_id = exDB.external_db_id";
if
(
defined
(
$dbname
) ) {
$sql
.=
" AND exDB.db_name = ? "
}
my
$sth
=
$self
->prepare(
$sql
);
$sth
->bind_param( 1,
$description
, SQL_VARCHAR );
if
(
defined
(
$dbname
) ) {
$sth
->bind_param( 2,
$dbname
, SQL_VARCHAR );
}
$sth
->execute();
my
$max_rows
= 1000;
while
(
my
$rowcache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$dbID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$ex_dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
my
$exDB
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$ex_dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
if
(
$synonym
) {
$exDB
->add_synonym(
$synonym
) }
push
@results
,
$exDB
;
}
}
$sth
->finish();
return
\
@results
;
}
sub
fetch_all_by_source {
my
(
$self
,
$source
) =
@_
;
my
@results
= ();
my
$sql
=
"SELECT xref.xref_id, xref.dbprimary_acc, xref.display_label,
xref.version,
exDB.priority,
exDB.db_name, exDB.db_display_name, exDB.db_release, es.synonym,
xref.info_type, xref.info_text, exDB.type, exDB.secondary_db_name,
exDB.secondary_db_table, xref.description
FROM (xref, external_db exDB)
LEFT JOIN external_synonym es on es.xref_id = xref.xref_id
WHERE exDB.db_name like ?
AND xref.external_db_id = exDB.external_db_id";
my
$sth
=
$self
->prepare(
$sql
);
$sth
->bind_param( 1,
$source
, SQL_VARCHAR );
$sth
->execute();
my
$max_rows
= 1000;
while
(
my
$rowcache
=
$sth
->fetchall_arrayref(
undef
,
$max_rows
) ) {
while
(
my
$arrayref
=
shift
( @{
$rowcache
} ) ) {
my
(
$dbID
,
$dbprimaryId
,
$displayid
,
$version
,
$priority
,
$dbname
,
$db_display_name
,
$release
,
$synonym
,
$info_type
,
$info_text
,
$type
,
$secondary_db_name
,
$secondary_db_table
,
$description
) =
@$arrayref
;
my
$exDB
=
Bio::EnsEMBL::DBEntry->new(
-adaptor
=>
$self
,
-dbID
=>
$dbID
,
-primary_id
=>
$dbprimaryId
,
-display_id
=>
$displayid
,
-version
=>
$version
,
-release
=>
$release
,
-dbname
=>
$dbname
,
-priority
=>
$priority
,
-db_display_name
=>
$db_display_name
,
-info_type
=>
$info_type
,
-info_text
=>
$info_text
,
-type
=>
$type
,
-secondary_db_name
=>
$secondary_db_name
,
-secondary_db_table
=>
$secondary_db_table
,
-description
=>
$description
);
if
(
$synonym
) {
$exDB
->add_synonym(
$synonym
) }
push
@results
,
$exDB
;
}
}
$sth
->finish();
return
\
@results
;
}
sub
fetch_all_synonyms {
my
(
$self
,
$dbID
) =
@_
;
my
@synonyms
= ();
my
$sth
=
$self
->prepare(
"SELECT synonym "
.
"FROM external_synonym "
.
"WHERE xref_id = ?"
);
$sth
->bind_param( 1,
$dbID
, SQL_INTEGER );
$sth
->execute();
my
$synonym
;
$sth
->bind_col(1, \
$synonym
);
while
(
$sth
->fetch() ) {
push
(
@synonyms
,
$synonym
);
}
return
\
@synonyms
;
}
sub
get_db_name_from_external_db_id{
my
$self
=
shift
;
my
$external_db_id
=
shift
;
my
$sth
=
$self
->prepare(
"SELECT db_name FROM external_db WHERE external_db_id = ?"
);
$sth
->bind_param(1,
$external_db_id
, SQL_INTEGER);
$sth
->execute();
my
(
$db_name
) =
$sth
->fetchrow_array();
$sth
->finish();
return
$db_name
;
}
1;