[Biojava-l] BioSQL implementation suggestions

[Keith James]

I'm looking seriously at BioSQL as a basis for storing information
about orthologous genes in various bacterial genomes. As we use
Oracle, I scanned the exisiting implementation to see what pits I
could be falling into ;) and have a few points that I'd like to get
feedback on. 

I spotted some MySQL/Postgres-specific SQL hard-coded (use of 'limit
x' where Oracle uses 'where rownum <= x', I think). I guess from the
SQL sequence helper classes we're going the route of polymorphism to
support different databases, rather than something like loading SQL
from ResourceBundles?

Also, I'm using a DataSource via JNDI (and will probably be using a
3rd party Connection pool). Currently there is no way of accessing the
BioJava implementation this way and no way to bypass the pool class it
uses internally. The pool is linked in many places and isn't an
interface, so it looks like a big job to do this. Is there already a
plan to address this, or do we need one?

I'd like to tighten up the closing of resources (Statements,
ResultSets) as there is a Connection pool in use (and Connections go
back to the pool, rather than being closed.) Currently, no ResultSets
are closed (I know they should close when their Statement is closed,
but some implementations are buggy and I prefer belt & braces). I
think that Statements should be closed in finally blocks as there are
cases where they will be left open if an Exception is thrown (and then
the Connection may then be returned to the pool with it still open.)

I'd like to log all the underlying SQL. Logging appears in Java 1.4
(which is still a beta version for us on Tru64) or log4j (which I like
better anyway). But the issue of dragging in more dependencies
arises. Opinions?

This is fairly urgent, but I'll hold off making any changes for
now. Once there has been some discussion, I'll come back with a
proposal (if nobody else gets one in first).

Keith

-- 

-= Keith James - kdj@sanger.ac.uk - http://www.sanger.ac.uk/Users/kdj =-
Pathogen Sequencing Unit, Wellcome Trust Sanger Institute, Cambridge, UK