[Biopython-dev] zxJDBC support for BioSQL

[Peter]

On Tue, Jan 12, 2010 at 10:06 PM, Kyle Ellrott <kellrott at gmail.com> wrote:
> I haven't played with Postgre yet (don't even have it installed).
> Sqlite as a python package hasn't been standardized to Jython yet  (
> http://bugs.jython.org/issue1682864 )
>
> One option is to call SQLite JDBC (
> http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC ) rather then reusing the
> existing SQLite code.
> But like zxJDBC, the jar would need to be in the CLASSPATH variable for the
> code to work.

I'm not 100% convinced that the details of your current approach
are the best way forward: Specifically taking a user script that works
on (C) Python using MySQL with MySQLdb as the driver, and when
run on Jython automatically interpreting this to use the Java MySQL
Connector/J with the org.gjt.mm.mysql.Driver (and so on for the
PostgreSQL and SQLite drivers?)

It might be clearer if we just treat the different Jython/Java drivers
as top level alternatives:

* MySQLdb (Python only, at least for now)
* psycopg, psycopg2, pgdb (Python only, at least for now)
* sqlite3 (currently Python only, maybe available on Jython later)
* org.gjt.mm.mysql.Driver (Jython only)
* Some JAVA PostreSQL driver (Jython only)
* Some JAVA SQLite driver (Jython only)

This way we have a clean separation of all the different driver
or database specific changes - although the user is required
to make some minor changes to take an existing BioSQL on
MySQL script to explicitly change the driver from MySQLdb
to org.gjt.mm.mysql.Driver if they want to run it on Jython.
We also won't have lots of "if jython" statements everywhere.

What are your thoughts on this?

Note there will be some similarities between all the MySQL
adaptors, all the PostgreSQL adaptors, etc. I've just made
a small improvement to file BioSQL/DBUtils.py to reduce
the code duplication for the existing (C) Python PostgreSQL
adaptors.

Peter