[BioSQL-l] Updates to biosql Oracle schema

[Richard HOLLAND]

I have recently attempted to install BioSQL on Oracle using the most
up-to-date version of the scripts in the CVS repository (downloaded
three weeks ago, approx. 15th October), only to find that there were a
number of bugs in them. My CVS access to pub.open-bio.org seems to have
stopped working (although I can still ssh to it) so I am forced to email
them to the list instead in the hope that someone else can make the
changes for me...:

 

* biosql-ora/BS-create-tablespaces.sql :

            -           change 

@BS-defs

to 

@BS-defs-local

* biosql-ora/BS-create-schema-user.sql

            -           change 

@BS-defs

to 

@BS-defs-local

* biosql-ora/BS-create-Biosql-usersyns.sql

            -           add another 'set' line to prevent wrapping
mid-word (and thus breaking) of SQL statements in the generated
usersyns.sql script (the number is arbitrary, it works for me, and is
better than the 80 char default):

set lines 200

* biosql-ora/BS-defs.sql

            -           add another definition allowing us to specify
the instance name instead of relying on the environment variables to do
this for us:

define biosql_instance=biosql

* biosql-ora/BS-create-all.sql

            -           change all 

connect &sysdba/&dbapwd as sysdba

                        to reference the instance variable like this:

connect &sysdba@&biosql_instance/&dbapwd as sysdba

-                      change all 

connect &biosql_owner/&biosql_pwd

to reference the instance variable like this:

                                    connect
&biosql_owner@&biosql_instance/&biosql_pwd

* biosql-ora/BS-grants.sql

            -           allow other users to see the primary keys, else
they cannot insert or update even if they are in the admin/loader roles.
Do this by removing a line from the last grant statement:

AND   object_name NOT LIKE '%_PK_SEQ'

 

Also could someone add a note saying which privileges the
BIOSQL_BASE_USER and BIOSQL_SCHEMA_CREATOR roles must have granted to
them, because it's not that obvious at first. These ones work for me:

 

            @BS-defs-local

            create role &base_user;

            grant 

            CREATE SESSION,

            CREATE SYNONYM,

            CREATE VIEW

            to &base_user;

            create role &schema_creator;

            grant 

            CREATE PROCEDURE,

            CREATE ROLE,

            CREATE SEQUENCE,

            CREATE SESSION,

            CREATE SYNONYM,

            CREATE TRIGGER,

            CREATE TYPE,

            CREATE VIEW,

            CREATE TABLE

            to &schema_creator;

 

It appears that the other bug I found, namely the definition for the
view Term_Relationship_Term in the BS-create-Biosql-API2.sql referring
to a non-existent column SG_Term_Assoc.Trm_Oid appears to have been
fixed by someone on the 17th October by adding that column to the
underlying table, so I won't post what I did about that one (I basically
altered the view so that it pointed to obj_trm_oid instead, maybe not
the best way to do things but never mind, it doesn't matter now...!).

 

cheers,

Richard

 

PS. I am away now until Tuesday next week, if any questions need
answering. I am aware that some changes were made on the 17th but I do
not 

 

Richard Holland

Bioinformatics Specialist

Genome Institute of Singapore

60 Biopolis Street, #02-01 Genome, Singapore 138672

Tel: (65) 6478 8000   DID: (65) 6478 8199

Email: hollandr at gis.a-star.edu.sg

 

---------------------------------------------

This email is confidential and may be privileged. If you are not the
intended recipient, please delete it and notify us immediately. Please
do not copy or use it for any purpose, or disclose its content to any
other person. Thank you.

---------------------------------------------