[BioSQL-l] Oracle support...

[Hilmar Lapp]

I guess we need somebody to help better documenting the Oracle version.

There is an API that gets instantiated which gives you identical table  
names to the MySQL/Pg schema except for where this is not possible due  
to the table names being reserved words. The column names are meanwhile  
also identical, again except where this is not possible due to reserved  
words. The primary key / foreign key naming convention is different  
though.

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.

I'll maybe just provide a second API that maps the Oracle version to  
identical column names using views (the current API uses synonyms), but  
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  
new RDBMS we may hit the same problem again if the language bindings  
don't let you specify or modify a mapping.

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

	-hilmar

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). my_sequence.currval is connection-isolated - so if, and  
only if, you have the connection handle you are guaranteed to obtain  
the last generated ID value.

On Monday, July 28, 2003, at 09:49  PM, Len Trigg wrote:

> Hi all,
>
> I have been using BioJava for some bioinformatics problems, and use
> BioSQL for storing sequences in mysql. All in all, it's very nice.
>
> I then had to use Oracle as the database, and ran into a whole pile of
> headaches. I am definitely no expert in Oracle, so that may account
> for a large part of my problems, but I guess it also says something
> when the procedure for creating the biosql database in mysql pretty
> much involves executing one sql file, but the oracle setup consists of
> around 140 files, and the table and field names you end up with don't
> match those in the BioSQL relational model described in
> biosql-ERD.pdf. Trying to fathom what is going on is a nightmare for
> those who have only had experience with the mysql installation.
>
> In desperation, I actually just ported the mysql schema file to Oracle
> directly and have happily been using that. There were a couple of
> minor niggles that I thought I would mention because either they might
> warrant changes to the BioSQL schema, or someone might know of
> workarounds.
>
> First, I discovered that "comment" and "synonym" are reserved words in
> Oracle, so I had to rename the comment table, and the synonym field of
> term_synonym table. Perhaps the main schema could be changed to avoid
> these reserved words.
>
> Second, to support autoincrement fields, I created an oracle sequence
> and trigger for each table. Having one per table makes it easy to
> access the last id assigned when doing an insert (which BioJava makes
> use of).
>
> I have attached my ported schema file (and one to drop the schema) for
> those interested. It should be possible to autogenerate this file, as
> I gather that is how the postgres one is created. I think that a
> "vanilla" alternative to the existing oracle schema would make a nice
> addition for those who don't need extra bells and whistles. These
> files correspond to current BioSQL CVS.
>
> Cheers,
> Len.
>
> <biosqldb-oracle.sql><mime-attachment><dropsqldb-oracle.sql><mime- 
> attachment><mime-attachment>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------