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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, December 02, 2004 4:18 PM To: Richard HOLLAND Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 12/02/2004 02:16 PM
To: <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
--------------------------------------------- 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 - [EMAIL PROTECTED] http://biojava.org/mailman/listinfo/biojava-l
_______________________________________________ Biojava-l mailing list - [EMAIL PROTECTED] http://biojava.org/mailman/listinfo/biojava-l
_______________________________________________ Biojava-l mailing list - [EMAIL PROTECTED] http://biojava.org/mailman/listinfo/biojava-l
