[BioSQL-l] Oracle support...

[Hilmar Lapp]

On Tuesday, July 29, 2003, at 03:15  PM, Len Trigg wrote:

> 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

This is old and does not apply anymore. There are too many differences 
between the RDBMSs, mysql is far too little expressive, and the 
transform script just gave too much grief. We concluded that the rate 
of change wouldn't be that high anymore so that it is easily affordable 
to have RDBMS versions decoupled.


> -----------------------
>
> 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?
>

No it's hand-crafted. (Well, with some help by ERwin ...)

>
> 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.

It won't be auto-generated though, and doesn't have to be.


>
>
>> 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?

Everything that breaks existing code is a major change. If you change 
table column names (as opposed to adding columns or changing their 
precision), you inevitably break existing code based on Mysql, 
especially language bindings that aren't flexible. (In Pg and Oracle 
you could write backward compatibility-preserving views but in mysql 
there's really not much you can do.)

Note that we're sort-of post-1.0 already since I wanted to get out 1.0 
for almost 2 months already. I.e., presently literally any change 
except bug-fixes are post-1.0.

>
> 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.
>

I have them aliased to provide virtual table-specific sequences. Check 
out BS-create-Biosql-API.sql.
	
	-hilmar

-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------