[BioSQL-l] Oracle support...

[Len Trigg]

Hilmar Lapp wrote:
> Bioperl-db deals with those differences seamlessly. I thought Thomas  
> and Matt wanted to make the Biojava implementation more flexible, too,  
> but I don't know where this went.

Currently BioJava assumes that the names of the tables and fields
correspond to those used by mysql and pg. I'm not sure where the
'canonical' definition of the schema lives, but the main README
suggests that the mysql one is definitive:

-----------------------
1. Generally, the MySQL schema sources function as the basis from
which schemas for other RDBMSs are auto-generated using, as an
example,

        $ scripts/transform_sql -target pg biosqldb-mysql.sql >biosqldb-pg.sql
-----------------------

The mentioned transformation script does not exist in CVS though --
where is it?. Is doc/biosql-ERD.pdf also automatically generated
from the mysql schema?


What exactly does the oracle schema do that is not satisfied by the
mysql-port schema? There must be a lot of extra functionality,
surely. I definitely think that a simple autogenerated oracle schema
would be a great addition for those who don't want the extra
complexity.


> there's still going to be exceptions due to reserved words. We can  
> change those column names post-1.0, but once support is extended to a  

Renaming one table and one column sounds pretty simple, maybe it
should be before a 1.0 release is made? What constitutes a minor
versus major change for a project like this?


> BTW you're welcome to post and ask before you go through the exercise  
> and despair :-)

Yeah :-). Unfortunately, with the problems I was having, I could see
it taking several iterations (i.e. several days across timezones) to
sort out, whereas porting and using the mysql schema was trivial.



> BTW also note that for retrieval of the last generated primary key it  
> doesn't help to have one sequence per table (it actually makes it  
> slightly more complicated because you need one more piece of  
> information).

Having one sequence per table made getting the last insert id simple
within the context of the BioJava implementation, where the relevant
method to implement is:

public int getInsertID(Connection conn, String table, String columnName);

So I just perform a "select SE_" + table + ".CURRVAL from dual" query.

An alternative is to have a single sequence that is shared between all
tables, it's no big deal. The oracle schema had several sequences, and
I couldn't see how to determine which sequence was the correct one to
query.


Cheers,
Len.