[BioSQL-l] Updates to biosql Oracle schema

[Hilmar Lapp]

Thanks for your suggestions, Richard. I'll include the schema instance, 
but without the literal '@' so that you may define it as empty too.

The change from including BS-defs.sql to including BS-defs-local.sql is 
intentional in all scripts. You're supposed to copy BS-defs.sql, which 
serves as a template, to BS-defs-local.sql and edit it to suit your 
setup.

As for the additional column and term_relationship_term view, Biojava 
needs this. Sorry for having been a day late with adding the column to 
the schema. I will also add all schema changes as DDL ALTER etc 
commands to the script sql/biosql-ora/migrate/1.0/migrate-all.sql. So, 
whenever you see a change in this script upon cvs update, just execute 
the added command and your schema is supposed to be up-to-date.

	-hilmar

On Wednesday, November 10, 2004, at 01:50  AM, Richard HOLLAND wrote:

> 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.
>
> ---------------------------------------------
>
>
>
> _______________________________________________
> BioSQL-l mailing list
> BioSQL-l at open-bio.org
> http://open-bio.org/mailman/listinfo/biosql-l
>
-- 
-------------------------------------------------------------
Hilmar Lapp                            email: lapp at gnf.org
GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
-------------------------------------------------------------