[BioSQL-l] Updates to biosql Oracle schema
Richard HOLLAND
hollandr at gis.a-star.edu.sg
Mon Nov 15 20:08:16 EST 2004
Thanks Hilmar. I already understood the BS-defs/BS-defs-local situation,
that was why I was surprised to find two scripts still referring to
BS-defs to load constants when all the others were referring to
BS-defs-local instead.
Mark found one other potential problem - we're not sure if this should
be fixed or not. When loading Genbank records, we hit a record with a
very large open reading frame translation stored as one peptide, which
was too big to fit in the appropriate column. The column failing was
bioentry_qualifer_value.value, which when expanded to 300 characters in
length was able to store it. Not sure if this should be changed in
BioSQL or not?
Richard Holland
Bioinformatics Specialist
GIS extension 8199
---------------------------------------------
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.
---------------------------------------------
> -----Original Message-----
> From: Hilmar Lapp [mailto:hlapp at gnf.org]
> Sent: Thursday, November 11, 2004 1:09 AM
> To: Richard HOLLAND
> Cc: biosql-l at open-bio.org
> Subject: Re: [BioSQL-l] Updates to biosql Oracle schema
>
> 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
> -------------------------------------------------------------
>
More information about the BioSQL-l
mailing list