[BioSQL-l] Re: Biosql-ora

[Jansen E Lim]

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_.

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.
See debug output below.

<snip>

preparing SELECT ASSOC query: SELECT t2.oid, t2.name,
BS_seqfeature_qualifier_assoc.value, t2.ont_oid FROM BS_seqfeature t1,
BS_ontology_term t2, BS_seqfeature_qualifier_assoc WHERE t1.oid =
BS_seqfeature_qualifier_assoc.fea_oid AND t2.oid =
BS_seqfeature_qualifier_assoc.ont_oid AND (t1.oid = ? AND t2.ont_oid = ?)
SimpleValueAdaptor: binding ASSOC column 1 to "53" (FK to
Bio::SeqFeature::Generic)
SimpleValueAdaptor: binding ASSOC column 2 to "11" (constraint
Bio::Annotation::SimpleValue::category)
preparing query: SELECT t1.oid, t1.comment_text, t1.rank, t1.ent_oid FROM
BS_comment t1 WHERE t1.fea_oid = ?
DBD::Oracle::db prepare failed: ORA-00904: "T1"."FEA_OID": invalid
identifier (DBD ERROR: OCIStmtExecute/Describe) at
/stf/biocgi/limje/Bio/DB/BioSQL/BasePersistenceAdaptor.pm line 1050, <GEN1>
line 116.
TermAdaptor: binding PK column to "21"
Query FIND LOCATION BY FEATURE: binding column 1 to "54"
no adaptor found for class Bio::DB::Persistent::PersistentObjectFactory

<snip>

-Jansen

Hilmar Lapp wrote:

> The Oracle version of the schema in biosql is out-dated. I need to
> update it. bioperl-db works well with the (current :) version of the
> Oracle schema, that's what we use here internally.
>
> I promise to update this by tonight (PST :). Sorry about that. You're
> the first one asking for the Oracle version. Let me know if you are
> serious about using it, and I'll consider moving the native repository
> into biosql-schema (as opposed to an import, which it is now). Also, I
> have constrained the Oracle version pretty rigorously, which wasn't
> appropriate in all places. I.e., sometimes I have to relax column
> widths etc to accommodate input data, and you will want to be made
> aware of such changes if they happen and if you run it seriously. That
> is, I'd post that to the list then too.
>
> On another note, the Oracle version adds some considerable
> sophistication on the database server end. It comes with a complete
> PL/SQL API, materialized views for fast name lookup and genome mapping,
> and with context indexes. Even though it may all run on Oracle 8i, I
> can't guarantee. I develop on 9i. For utilizing the additional
> features, it will help if you're somewhat familiar with Oracle.
>
>         -hilmar
>
> On Tuesday, January 28, 2003, at 11:44  AM, Jansen E Lim wrote:
>
> > Hi Hilmar,
> >
> > [...]
> > I tried using the Oracle verion of biosql and I wasn't sure it I could
> > use it as is.  There seems to be
> > quite a difference in the schema (table names are prefixed with SG_,
> > new column names, etc) between the PostgreSQL version.  Would it be
> > possible to get a list of what needs changing to make it work?
> > [...]
> >
> > Again, thank you for taking time to explain.
> >
> > Regards,
> > -Jansen
> >
> >
> >
> >
> --
> -------------------------------------------------------------
> Hilmar Lapp                            email: lapp at gnf.org
> GNF, San Diego, Ca. 92121              phone: +1-858-812-1757
> -------------------------------------------------------------