[Biojava-dev] BioSQL postgre BioEntryRelationship

[Christian Köberle]

Hi,
I found a bug in the postgre mapping file for BioEntryRelationship.
line:
<many-to-one name="object" class="Feature" column="object_bioentry_id"
not-null="true" cascade="persist,merge,save-update" node="@objectFeatureId"
embed-xml="false"/>
The value for the attribute class has to be "BioEntry"

For the BioEntry I miss methodes to have access to subject_bioentry
BioEntryRelationship. I think the BioEntryRelationship. is a parent child
relationship. So it will be nice to have access to both.

Furthermore the hibernate mapping strategies for the BioSQL is quite slow
and produces a lot of queries to the database. Because for all lists and set
the lazy fetch mode is disable. In this mode hibernate will execute one
query for each element in a list or set. The faster way is to enable the
lazy fetch mode an use methods to load the list. Each of these methods
executes only one query.
For excample:

public List<BioEntry> getParents(BioEntry bioEntry){

String stmt = "SLECT r.object FROM BioEntryEelationship r WHERE r.object
=:subject";
Query query = session.createQuery(stmt);
query.setParameter("subject", bioEntry);
return query.list();

}


This is factor 2 to 4 faster than the methode BioEntry..getRelationships()
In case of all dependences of an BioEntry-Object an select with lazy
fetching can be 500 times faster than a select with eager fetching (in case
of unigene cluster Hs.4 for example).
Here a example for the relationship unigene cluster Hs.2 and the gene
BC067218 (we use BioSQL to store Unigene)

getParents():
runtime: 14 msec
SQL: Hibernate: select bioentry1_.bioentry_id as bioentry1_89_,
bioentry1_.name as name89_, bioentry1_.identifier as identifier89_,
bioentry1_.accession as accession89_, bioentry1_.description as
descript5_89_, bioentry1_.version as version89_, bioentry1_.division as
division89_, bioentry1_.taxon_id as taxon8_89_, bioentry1_.biodatabase_id as
biodatab9_89_, bioentry1_1_.version as version93_, bioentry1_1_.length as
length93_, bioentry1_1_.alphabet as alphabet93_, bioentry1_1_.seq as seq93_,
case when bioentry1_1_.bioentry_id is not null then 2 when
bioentry1_.bioentry_id is not null then 0 end as clazz_ from
unigene.bioentry_relationship bioentryre0_ inner join unigene.bioentry
bioentry1_ on bioentryre0_.subject_bioentry_id=bioentry1_.bioentry_id left
outer join unigene.biosequence bioentry1_1_ on
bioentry1_.bioentry_id=bioentry1_1_.bioentry_id left outer
joinunigene.biosequence bioentry1_2_ on
bioentry1_.bioentry_id=bioentry1_2_.bioentry_id where
bioentryre0_.object_bioentry_id=?


bioEntry.getRelationships():
runtime: 36 msec
SQL:Hibernate: select bioentry0_.bioentry_id as bioentry1_89_,
bioentry0_.name as name89_, bioentry0_.identifier as identifier89_,
bioentry0_.accession as accession89_, bioentry0_.description as
descript5_89_, bioentry0_.version as version89_, bioentry0_.division as
division89_, bioentry0_.taxon_id as taxon8_89_, bioentry0_.biodatabase_id as
biodatab9_89_, bioentry0_1_.version as version93_, bioentry0_1_.length as
length93_, bioentry0_1_.alphabet as alphabet93_, bioentry0_1_.seq as seq93_,
case when bioentry0_1_.bioentry_id is not null then 2 when
bioentry0_.bioentry_id is not null then 0 end as clazz_ from
unigene.bioentry bioentry0_ left outer join unigene.biosequence bioentry0_1_
on bioentry0_.bioentry_id=bioentry0_1_.bioentry_id left outer join
unigene.biosequence bioentry0_2_ on
bioentry0_.bioentry_id=bioentry0_2_.bioentry_id where bioentry0_.name=?
Hibernate: select relationsh0_.object_bioentry_id as object3_1_,
relationsh0_.bioentry_relationship_id as bioentry1_1_,
relationsh0_.bioentry_relationship_id as bioentry1_95_0_,
relationsh0_.term_id as term2_95_0_, relationsh0_.object_bioentry_id as
object3_95_0_, relationsh0_.subject_bioentry_id as subject4_95_0_,
relationsh0_.rank as rank95_0_ from unigene.bioentry_relationship
relationsh0_ where relationsh0_.object_bioentry_id=?
Hibernate: select namespace0_.biodatabase_id as biodatab1_80_0_,
namespace0_.name as name80_0_, namespace0_.authority as authority80_0_,
namespace0_.description as descript4_80_0_ from unigene.biodatabase
namespace0_ where namespace0_.biodatabase_id=?
Hibernate: select bioentry0_.bioentry_id as bioentry1_89_0_, bioentry0_.name
as name89_0_, bioentry0_.identifier as identifier89_0_, bioentry0_.accession
as accession89_0_, bioentry0_.description as descript5_89_0_,
bioentry0_.version as version89_0_, bioentry0_.division as division89_0_,
bioentry0_.taxon_id as taxon8_89_0_, bioentry0_.biodatabase_id as
biodatab9_89_0_, bioentry0_1_.version as version93_0_, bioentry0_1_.length
as length93_0_, bioentry0_1_.alphabet as alphabet93_0_, bioentry0_1_.seq as
seq93_0_, case when bioentry0_1_.bioentry_id is not null then 2 when
bioentry0_.bioentry_id is not null then 0 end as clazz_0_ from
unigene.bioentry bioentry0_ left outer join unigene.biosequence bioentry0_1_
on bioentry0_.bioentry_id=bioentry0_1_.bioentry_id left outer join
unigene.biosequence bioentry0_2_ on
bioentry0_.bioentry_id=bioentry0_2_.bioentry_id where
bioentry0_.bioentry_id=?
Hibernate: select namespace0_.biodatabase_id as biodatab1_80_0_,
namespace0_.name as name80_0_, namespace0_.authority as authority80_0_,
namespace0_.description as descript4_80_0_ from unigene.biodatabase
namespace0_ where namespace0_.biodatabase_id=?
Hibernate: select term0_.term_id as term1_84_0_, term0_.name as name84_0_,
term0_.identifier as identifier84_0_, term0_.definition as definition84_0_,
term0_.is_obsolete as is5_84_0_, term0_.ontology_id as ontology6_84_0_ from
unigene.term term0_ where term0_.term_id=?
Hibernate: select ontology0_.ontology_id as ontology1_83_0_, ontology0_.name
as name83_0_, ontology0_.definition as definition83_0_ from unigene.ontology
ontology0_ where ontology0_.ontology_id=?
Hibernate: select termset0_.ontology_id as ontology6_1_, termset0_.term_id
as term1_1_, termset0_.term_id as term1_84_0_, termset0_.name as name84_0_,
termset0_.identifier as identifier84_0_, termset0_.definition as
definition84_0_, termset0_.is_obsolete as is5_84_0_, termset0_.ontology_id
as ontology6_84_0_ from unigene.term termset0_ where termset0_.ontology_id=?
Hibernate: select tripleset0_.ontology_id as ontology5_1_,
tripleset0_.term_relationship_id as term1_1_,
tripleset0_.term_relationship_id as term1_87_0_, tripleset0_.subject_term_id
as subject2_87_0_, tripleset0_.object_term_id as object3_87_0_,
tripleset0_.predicate_term_id as predicate4_87_0_, tripleset0_.ontology_id
as ontology5_87_0_ from unigene.term_relationship tripleset0_ where
tripleset0_.ontology_id=?
Hibernate: select rankedcros0_.term_id as term1_0_, rankedcros0_.dbxref_id
as dbxref2_0_, rankedcros0_.rank as rank0_ from unigene.term_dbxref
rankedcros0_ where rankedcros0_.term_id=?
Hibernate: select synonymset0_.term_id as term1_0_, synonymset0_.synonym as
synonym0_ from unigene.term_synonym synonymset0_ where
synonymset0_.term_id=?

-- 
Christian Köberle