[Bioperl-l] bioperl-db and postgres8.3 - status query

[Chris Fields]

On Fri, 2010-08-20 at 10:59 -0500, Siddhartha Basu wrote:
> Hi, 
> 
> On Thu, 19 Aug 2010, Robert Buels wrote:
> 
> > Chris Fields wrote:
> > > I think it's worth exploring having a DBIx::Class-based middle-ware 
> > > approach similar to what Rob Buels has done for Chado.  That would be 
> > > fairly easy to get started using DBIx::Class::Schema::Loader.
> > > After that it would require optimization and tweaking, which is 
> > > potentially more complex than Rob's setup as Chado is very Pg-specific, 
> > > but maybe Rob can elaborate...
> >
> > Elaborating on how Bio::Chado::Schema is developed:
> >
> > The vast majority of the code and POD in BCS is autogenerated by 
> > DBIx::Class::Schema::Loader.  DBICSL gives you a baseline set of 
> > DBIx::Class classes that covers all the tables, views, columns, unique 
> > constraints, and foreign key relationships.
> >
> > Beyond that, you have to add on yourself.  In BCS, we have mostly done 
> > things like:
> >
> >   * make better-named aliases for some of the autogenerated
> >     relationships (though DBICSL does a surprisingly good job of naming
> >     relationships automatically most of the time)
> >   * add a tiny bit of bioperl compatibility (this needs a lot more work
> >     by somebody, volunteers needed!)
> >   * add convenience methods for using some of the Chado property tables
> >   * use DBIx::Class::Tree::NestedSet to add some powerful ways of
> >     traversing phylogenetic tree relationships
> >
> > Regarding DB backend specificity, BCS isn't Pg-specific at all, because 
> > DBIx::Class itself goes to great lengths to be compatible (and performant!) 
> > with just about every relational database out there.  
> I would vouch for that at least as far as chado in oracle is concerned.
> So,  far BCS works out flawlessly with our oracle chado instance at
> dictybase. Quite a chunk of BCS based code is also active in couple of
> our Mojo based webapps. The part which i still couldn't use directly is
> the 'synonym' table as it clashes with oracle specific reserved keywords. 
> However,  overall it seems to quite cross-RDMS compatible and highly
> recommended.
> 
> -siddhartha

Just to point out, I didn't say BCS is Pg-specific, but that Chado is
(that was the DBMS it was designed for).  Maybe that should be amended
to 'was' now :)

I recall seeing a page on this somewhere on the GMOD website along the
lines of "MySQL has problems so we chose Pg", and that Chado support
would focus on Pg.  I'm guessing that's no longer the case?  Or is only
the server-side stuff Pg-specific.

> >In fact, the BCS test 
> > suite deploys a Chado schema into a temporary SQLite database using 
> > DBIC::Schema's deploy() method, and runs all of its tests on that.  Very 
> > handy.
> >
> > Chado's Pg-specific server-side functions can of course be called through 
> > BCS if they are present, but it's perfectly possible to use Chado without 
> > any of the server-side functions, and mostly the way I use it.
> >
> > Rob

I think this opens up the possibility of starting a DBIx::Class-based
middleware solution.  Hilmar, did you want to take that on?

chris