[Biojava-l] BioSQL, Oracle, & isSPASupported?

[Tim Burcham]

Thomas,

OK, I think I understand the logic behind the biosql accelerators.

I think I am suggesting something different. I am working through the
code right now, and would like to have it become part of the main
branch, assuming you all approve!

What we are doing is hiding the logic of which RDBMS you use behind an
object. This abstraction is a subclass of DBHelper, although the new
DBHelper class has much more functionality than the current DBHelper
interface. Most of the functionality is contained in an implementation
of DBHelper, named SimpleDBHelper. This greatly simplifies the code in
BioSQLSequenceDB.

My suggesting regarding isSPASupported could be completely contained
within either a subclass of PostgreSQLDBHelper, or even within the
PostgreSQLDBHelper itself. This would also clean up the code in
BioSQLSequenceDB, I think.

What do you think? Is this something you, and other people might be
interested in? I need help testing the new code for PostgreSQL.

Thanks - Tim

-----Original Message-----
From: Thomas Down [mailto:td2@sanger.ac.uk] 
Sent: Tuesday, August 13, 2002 4:57 AM
To: Tim Burcham
Cc: biojava-l@biojava.org
Subject: Re: [Biojava-l] BioSQL, Oracle, & isSPASupported?


On Mon, Aug 12, 2002 at 10:24:53AM -0700, Tim Burcham wrote:
> I am sure that I missed this somewhere in the archives, but... We are 
> supporting an Oracle instance of BioSQL, and we keep running across 
> this isSPASupported(), which seems to be some sort of BioSQL
> accelerator:
> 
> PreparedStatement ps = conn.prepareStatement( "select 
> biosql_accelerators_level()" );
> 
> Are spaChecked & spaSupported ever true? In which case, against which 
> DBMS?

Yes, it can be.  Currently the only implementation is for PostgreSQL.
See:

     http://www.biojava.org/download/biosql/biosql-accelerators-pg.sql

As you've probably guessed, the biosql_accelerators_level stored
procedure is just called as a check to see if the other stored
procedures are available (and that a matching version is in use).

I'm hoping it should be reasonably easy to create a compatible
implementation of the accelerators in Oracle PL/SQL.  However, I've
heard suggestions that the syntax I've been using for calling stored
procedures in PostgreSQL won't work with Oracle.  I'd like to find some
way to handle this while minimizing the amount of database-dependant
code.

> We are moving a lot of functionality to a subclass of DBHelper to 
> isolate BioSQLSequenceDB from the actual DBMS and SQL used. I would be

> curious how others are handing this for Oracle. It seems that the
> isSPASupported() method could also be isoloated this way, by just 
> instantiating a different DBHelper. This would make the code much 
> easier to read, and would not have the very similar duplication of 
> code in BioSQLSequenceDB class.

Are you suggesting that isSPASupported() just return true or false
depending on which database is in use?  I think we should be cautious
about this, since the SPA procedures are just an extension, rather than
a core part of BioSQL.  The idea is that end users can load the
appropriate SPA implementation into the database if they want it.

Does this make sense?

     Thomas.