[Biojava-l] Ontology and BioSQL
Thomas Down
td2 at sanger.ac.uk
Thu Dec 2 05:03:10 EST 2004
Hi Richard,
We've had a problem very much like this when using BioSQL with a MySQL
database -- the issue occurs when you have two "ontology terms" (which
are used for a number of different purposes -- feature types,
annotation keys, etc) which differ only by name. For example
"organism" and "ORGANISM". There's was a discussion of this issue in
MySQL here:
http://www.biojava.org/pipermail/biojava-dev/2004-August/001857.html
From your stacktraces, it looks like you're actually using Oracle -- I
don't use Oracle myself so can't confirm that this is the same problem,
but it looks very similar.
Is there a way to enforce uniqueness in a case-sensitive way in Oracle?
Thomas.
On 2 Dec 2004, at 08:52, Richard HOLLAND wrote:
> Yup, this problem happened since the very first time I ran it on an
> empty database. The terms get created in term, the
> term representing the triple gets created in term, but it does not
> create the relationship in term_relationship.
>
> The exception is being thrown by the persistTerm call on the very first
> line of persistTriple.
>
> I'm thinking that this is something to do with the OntologyMonitor?
> That
> it is seeing the Triple get created, but
> because the Triple is an instanceof Term it is firing two change events
> - one for the Term representation, the
> other for the Triple representation? That's the only way I can see the
> Triple being created in the term table but
> not in the term_relationship table, as otherwise we'd be getting
> exceptions halfway through persistTriple way after that call to
> persistTerm at the beginning, which we're not.
>
> In other words, the Triple already exists in the term table _before_
> persistTriple is getting called. Why? How? Hmm.
>
> cheers,
> Richard
>
> Richard Holland
> Bioinformatics Specialist
> GIS extension 8199
>
> ---------------------------------------------
> This email is confidential and may be privileged. If you are not the
> intended recipient, please delete it and notify us immediately. Please
> do not copy or use it for any purpose, or disclose its content to any
> other person. Thank you.
> ---------------------------------------------
>
>
>> -----Original Message-----
>> From: mark.schreiber at group.novartis.com
>> [mailto:mark.schreiber at group.novartis.com]
>> Sent: Thursday, December 02, 2004 4:18 PM
>> To: Richard HOLLAND
>> Cc: biojava-l at biojava.org; biojava-l-bounces at portal.open-bio.org
>> Subject: Re: [Biojava-l] Ontology and BioSQL
>>
>>
>> Hi -
>>
>> The code responsible for the insert is:
>>
>> private void persistTriple(Connection conn, Ontology ont,
>> Triple triple)
>> throws SQLException
>> {
>> persistTerm(conn, triple);
>>
>> PreparedStatement import_trip = conn.prepareStatement(
>> "insert into term_relationship " +
>> " (subject_term_id, predicate_term_id,
>> object_term_id,
>> ontology_id) " +
>> "values (?, ?, ?, ?)"
>> );
>> import_trip.setInt(1, termID(triple.getSubject()));
>> import_trip.setInt(2, termID(triple.getPredicate()));
>> import_trip.setInt(3, termID(triple.getObject()));
>> import_trip.setInt(4, ontologyID(ont));
>> import_trip.executeUpdate();
>> import_trip.close();
>> int tripID = dbHelper.getInsertID(conn, "term_relationship",
>> "term_relationship_id");
>>
>> PreparedStatement link_trip_to_term = conn.prepareStatement(
>> "insert into term_relationship_term " +
>> " (term_relationship_id, term_id) " +
>> "values (?, ?)" );
>> link_trip_to_term.setInt(1, tripID);
>> link_trip_to_term.setInt(2, termID(triple));
>> link_trip_to_term.executeUpdate();
>> link_trip_to_term.close();
>>
>> //System.err.println("Persisted triple: " + triple);
>> }
>>
>> I can't immediately see any attempt to write it twice. Are
>> you removing
>> the old triples before running the program again?
>>
>> - Mark
>>
>>
>>
>>
>>
>> "Richard HOLLAND" <hollandr at gis.a-star.edu.sg>
>> Sent by: biojava-l-bounces at portal.open-bio.org
>> 12/02/2004 02:16 PM
>>
>>
>> To: <biojava-l at biojava.org>
>> cc: (bcc: Mark Schreiber/GP/Novartis)
>> Subject: [Biojava-l] Ontology and BioSQL
>>
>>
>> Hi all,
>>
>> I am trying to use the Ontology class in Biojava 1.4rc1 to
>> create terms and triples, which are automatically persisted
>> using the BioSQL links in BioJava. Here is my code:
>>
>> BioSQLSequenceDB db = new BioSQLSequenceDB(dbURL,
>> dbUser,
>> dbPass,
>> biodatabase,
>> createIfMissing);
>>
>> Ontology o = db.createOntology("MyOntology","An
>> ontology"); // Use getOntology to update
>> Term MyGroup = o.createTerm("MyGroup","My favourite terms.");
>> Term Boo = o.createTerm("Boo","A fright");
>> Term ISA = o.createTerm("ISA","Is a");
>> Term Joke = o.createTerm("Joke","Something funny");
>> o.createTriple(Boo,Joke,ISA,null,null);
>>
>> I get the following exception (NB. the ontology does not
>> exist yet but the database connection is just fine):
>>
>> Exception in thread "main"
>> org.biojava.bio.BioRuntimeException: Error removing from
>> BioSQL tables (rolled back successfully) at
>> org.biojava.bio.seq.db.biosql.OntologySQL.persistTriple(Ontolo
>> gySQL.java
>> :588)
>> at
>> org.biojava.bio.seq.db.biosql.OntologySQL.access$300(OntologyS
>> QL.java:61
>> )
>> at
>> org.biojava.bio.seq.db.biosql.OntologySQL$OntologyMonitor.post
>> Change(Ont
>> ologySQL.java:512)
>> at
>> org.biojava.utils.ChangeSupport.firePostChangeEvent(ChangeSupp
>> ort.java:3
>> 38)
>> at org.biojava.ontology.Ontology$Impl.createTriple(Ontology.java:497)
>> at testapp.LoadOntology.main(LoadOntology.java:61)
>> Caused by: java.sql.SQLException: Failed to persist term:
>> ISA(Boo, Joke) from ontology: ontology: MyOntology with
>> error: 1 : 23000 at
>> org.biojava.bio.seq.db.biosql.OntologySQL.persistTerm(Ontology
>> SQL.java:5
>> 62)
>> at
>> org.biojava.bio.seq.db.biosql.OntologySQL.persistTriple(Ontolo
>> gySQL.java
>> :595)
>> at
>> org.biojava.bio.seq.db.biosql.OntologySQL.persistTriple(Ontolo
>> gySQL.java
>> :576)
>> ... 5 more
>> Caused by: java.sql.SQLException: ORA-00001: unique constraint
>> (BIOSQL_OWNER.XAK1TERM) violated
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseErr
>> or.java:12
>> 5)
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:305)
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:272)
>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:623)
>> at
>> oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedSta
>> tement.jav
>> a:181)
>> at
>> oracle.jdbc.driver.T4CPreparedStatement.execute_for_rows(T4CPr
>> eparedStat
>> ement.java:543)
>> at
>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(Oracle
>> Statement.
>> java:1028)
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.executeInternal(Ora
>> clePrepare
>> dStatement.java:2888)
>> at
>> oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(Oracl
>> ePreparedS
>> tatement.java:2960)
>> at
>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpd
>> ate(Delega
>> tingPreparedStatement.java:101)
>> at
>> org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpd
>> ate(Delega
>> tingPreparedStatement.java:101)
>> at
>> org.biojava.bio.seq.db.biosql.OntologySQL.persistTerm(Ontology
>> SQL.java:5
>> 54)
>> ... 7 more
>> Java Result: 1
>>
>> When I check in the database, I find that the ontology has
>> been created, and entries made in term for each of the terms,
>> and also an entry has been made in term for the triple
>> itself. So why the exception? Is it trying to insert the triple twice?
>>
>> cheers,
>> Richard
>>
>> Richard Holland
>> Bioinformatics Specialist
>> Genome Institute of Singapore
>> 60 Biopolis Street, #02-01 Genome, Singapore 138672
>> Tel: (65) 6478 8000 DID: (65) 6478 8199
>> Email: hollandr at gis.a-star.edu.sg
>>
>> ---------------------------------------------
>> This email is confidential and may be privileged. If you are
>> not the intended recipient, please delete it and notify us
>> immediately. Please do not copy or use it for any purpose, or
>> disclose its content to any other person. Thank you.
>> ---------------------------------------------
>>
>> _______________________________________________
>> Biojava-l mailing list - Biojava-l at biojava.org
>> http://biojava.org/mailman/listinfo/biojava-l
>>
>>
>>
>>
>
> _______________________________________________
> Biojava-l mailing list - Biojava-l at biojava.org
> http://biojava.org/mailman/listinfo/biojava-l
>
More information about the Biojava-l
mailing list