[BioSQL-l] Oracle support...

[Hilmar Lapp]

On Thursday, July 31, 2003, at 02:58  PM, Len Trigg wrote:

> Hilmar Lapp wrote:
>> An optional complete map to the biosql names (where possible) would
>> minimize naming problems then, except for those that cannot be
>> eliminated.
>
> I have committed my Oracle support to BioJava CVS. This corresponds to
> the mysql-port schema I posted, (where I altered the sequence names
> altered to be compatible with the synonyms created by the Symgene
> schema). As far as I can see, the only thing now preventing BioJava
> from also working with the Symgene schema is the difference in naming
> convention for primary and foreign keys, and the two reserved words
> mentioned earlier.

Where these would would be the 'comment' table and 
term_synonym.synonym, right?

If you look at BS-create-Biosql-API you'll see that I aliased the 
sg_comment table to anncomment. Also, in the Oracle version the column 
term_synonym.synonym becomes term_synonym.name.

The latter I think should also be it's name in the mysql and Pg 
versions because it would be consistent with the naming standard 
introduced at the Singapore hackathon - which is funny because there we 
also introduced the term_synonym table. Everybody makes mistakes, and 
some are going to come down on you. At any rate, this would be a 
post-1.0 change unless there are strong votes to change it now.

Same applies to the comment table.

Is your Biojava/Oracle support for biosql able to handle names 
different from the otherwise expected Pg schema?

>
> Do you have suggestions for how to address the key naming? I guess the
> obvious options are to either standardize on the same naming
> convention, special case the symgene naming in the BioJava code, or
> maybe oracle has some method to provide an alias for them.
>

Oracle helps (as would any other RDBMS with views). My proposal is to 
create 2 APIs to achieve interoperability with client code:

	1) An API based on views for tables and synonyms for sequences that 
achieves a complete mapping to the names used in the Pg version of 
biosql. Client code should be able to use that API as if it were biosql 
native tables, except for creating indexes and similar operations 
(which you're not going to do in Biojava nor from Bioperl).

	This API doesn't exist yet and needs to be written, one view per table.

	2) An API (mostly) based on synonyms that aliases table names and 
sequence names to the ones used in Pg, but may result in some columns 
named differently. The advantage of this API is that the tables are 
actual tables for all possible purposes(*).

	This API exists already in BS-create-Biosql-API (and is the one that 
bioperl-db works off of for Oracle).

Adding 1) isn't a big job, but I'm to tied up here to be able to 
instantly start writing it. Maybe this could be a feet-wetting exercise 
for Jim, who volunteered to contribute to biosql?

Jim, what do you think? (BTW if you aren't subscribed to biosql, you'll 
want to subscribe. Here is where the discussions about the schema 
happen.)

Len, you'd be welcome too of course. Otherwise I'll get to it tomorrow 
or over the weekend.

	-hilmar

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