[BioSQL-l] Re: Biosql-ora

[Hilmar Lapp]

On Wednesday, January 29, 2003, at 12:05  PM, Jansen E Lim wrote:

> Hilmar,
>
> Thank you for updating the Oracle version.  I tried it today and got it
> working.
> I had to do some minor fixing on the Chr_Map_Assoc package - spec and 
> body
> files.  Specifically,
> the get_oid function refers to the SG_CHR_MAP_ASSOCS view to define the
> parameters datatype. However,
> I believe the view is prefixed with SGLD_.

It's not. The one prefixed by SGLD_ is only for loading. My convention 
is to have separate APIs for ro and rw, as far as views are concerned. 
You will find insert triggers on the SGLD_ views, but never on SG_ 
views. Conversely, SGLD_ views may not do very well on SELECTs, unlike 
the SG_ views.

Unfortunately I forgot to reset the overall build script to its 
default. I also failed to produce an INSTALL document ... please bear 
with me.

Here's a rough walk-through.

1) Check BS-defs.sql and make sure the settings reflect what you want.

2) Check BS-create-all.sql:
	- add AS SYSDBA to the connect commands to sys (needed for 9i)
	- make sure only those commands are commented out that you know you 
don't want to happen (e.g., you may have the tablespaces already, so 
skip that)

3) Creation of the warehouse components and the context indexes is not 
yet included in the overall build script. In order to enable that, you 
need to
	SQL> @BS-create-warehouse
	SQL> @BS-create-ctx  -- read the comments to check your settings

After issuing the first command SG_Chr_Map_Assocs should be present. 
Also, there should be no objects anymore which don't compile. Let me 
know if that's not the case.

4) To create the Biosql API used by the Oracle driver in bioperl-db, 
run BS-create-Biosql-API.sql:
	SQL> @BS-create-Biosql-API

I guess you did that because otherwise you would have run into trouble 
connecting from bioperl-db.

5) I also have a separate user biosql that I load as, not the schema 
owner (I generally don't use the schema owner from applications - too 
much damage possible). Because the aforementioned Biosql-API is not 
public (I chose that to limit cluttering), every user who wants to use 
the API needs to establish synonyms. There is a script 
BS-create-Biosql-usersyns.sql which creates a script usersyns.sql, 
which is then to be run under every user who wants to use the API, for 
me for instance the user biosql. I.e.,

	SQL> @BS-create-Biosql-usersyns
	SQL> connect biosql/biosql -- or whatever your usr/pwd is
	SQL> @usersyns

6) I also create a bare-bones test schema under a different schema 
owner specifically for the bioperl-db test suite. For bioperl-db to 
work you don't need the warehouse, nor the context indexes, nor the 
PL/SQL API. All that is needed is the schema (in BS-DDL) and the biosql 
API (in BS-create-Biosql-API.sql). Be careful to comment out the 
creation of roles, grants, and specifically synonyms in BS-create-all 
when instantiating the test schema.

You don't need to do that, but for tracking problems it is going to be 
convenient once you have your main schema populated with content you 
don't want to jeopardize.

After those steps you should be set. If you want to check whether all 
objects compile and you don't have a script for that, let me know.


>
> Also, I get an error when I use load_seqdatabase to re-enter a record 
> that
> exists in the db using -lookup -remove
> options. It was referencing a non-existing column --- 
> bs_comment.fea_oid.
>

This is expected (and handled). According to the bioperl object model, 
SeqFeatureI's can have annotation and hence associated comments, just 
like SeqI's (bioentry) can. It just so happens that the biosql schema 
doesn't support that (yet). BTW the same column is referenced by all 
other drivers as well, only the exception in MySQL and Pg happens at 
bind time instead of at prepare time (but is caught and handled as 
well).

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