[BioSQL-l] Oracle support...

[Susie Stephens]

Len,

Oracle would like to help people in life sciences share Oracle related code
and schemas. Recently we worked with UCSD to set up a SourceForce project for
this purpose (http://sourceforge.net/projects/oraclelifesci/).

It would be great if you could post the schema file that you ported to the Web
site, so that other people could use it too.

Regards,

Susie



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.
>
>   ------------------------------------------------------------------------
>                           Name: biosqldb-oracle.sql
>    biosqldb-oracle.sql    Type: Plain Text (text/plain)
>                       Encoding: 7bit
>
>   ------------------------------------------------------------------------
>
>    dropsqldb-oracle.sqlName: dropsqldb-oracle.sql
>                        Type: Plain Text (text/plain)
>
>   ------------------------------------------------------------------------
>
>   ------------------------------------------------------------------------
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
-------------- next part --------------
A non-text attachment was scrubbed...
Name: Susie.Stephens.vcf
Type: text/x-vcard
Size: 245 bytes
Desc: Card for Susie Stephens
Url : http://pw600a.bioperl.org/pipermail/biosql-l/attachments/20030729/330ef875/Susie.Stephens.vcf