[Gmod-help] MySQL Chado Schema

[Scott Cain]

Hi Kevin,

I'm going to cc my response to the schema mailing list in case anyone
else would like to chime in; we can trim the help email address from
future responses.  My answers/comments are below.

Scott

On Mon, 2008-01-28 at 12:37 -0700, kevin greer wrote:
> Hi Dave,
> 
> I have been working with the Chado schema for a little while now and I
> have a couple of questions.  Basically, we are trying to decide if we
> should use Chado in PostgreSQL or try to port it to MySQL. 
> 
> 1. Do you know when version 1.0 is going to be released?

If I had a dollar for every time I was asked that, I'd be a pretty rich
man :-)  I anticipate some time in the first half of this year.  I am
working on community annotation tools and a few other items that I want
to get shaped up before I release it.

> 2. How stable is the schema, (i.e. how often is it changed)?  By
> schema I am referring to everything in the database (tables, views,
> SPs, triggers).

Here is the cvs history for the main schema (tables, views, and
functions):

  http://gmod.cvs.sourceforge.net/gmod/schema/chado/modules/default_schema.sql?view=log

The tables themselves change very infrequently, and the changes are
virtually never destructive.  The last time there was a destructive
change (ie, removing tables or columns) was almost 3 years ago when the
phenotype module was reworked to make it functional (the module that was
there before was really a placeholder for continued development).  The
last major addition was the stock module about a year ago contributed by
the folks at ParameciumDB.  

There are no triggers included in the core schema.  I've written
triggers for use with Apollo, but they are not required.

> 3. How are the sequences for multiple strains of the same organism
> denoted.  I assume that people are using dbxrefs (feature_dbxref), but
> I wanted to check if you know how most people are handling this?

I don't know off hand.  Perhaps someone at FlyBase could answer this for
how they are doing it.

> 4. I did a test port of some of the schema into MySQL and found a
> couple of things that MySQL couldn't handle, and which seem a little
> odd to me.  In particular there are a couple of tables that have
> fields that are type 'text' (for example uniquename) that is then used
> in a unique constraint.  I am not sure exactly how PostrgeSQL handles
> text fields but usually they are stored separately from the rest of
> the table so I would expect that having a unique constraint on this
> column might impact performance (and MySQL doesn't allow it).  Also,
> since it is a uniquename that humans will need to use, I am wondering
> why it needs to be such a large field.  Anyway, for MySQL it would be
> necessary change the datatype to something like varchar(255) so I just
> wanted to make sure that I wouldn't be shortening a field that needed
> to be that long for some reason.

I don't remember why uniquename was made text instead of varchar(8000)
which seems like it would be enough, though postgres handles text pretty
efficiently, so I don't there there is any problem with a unique index
including it.  However, I should point out that uniquename is not
necessarily human readable (as per the comment on the column included in
the DDL): it is there primarily to support specific needs for FlyBase.
Perhaps someone at FlyBase could expand more on this. 

> 5. Is there any plan to support MySQL in the future?  I am pretty sure
> that the answer is no, but I wanted to make sure.  Also, has there
> been much demand for supporting MySQL?

Not really, though if you want to give back code you've written, we will
happily include it in the code repository.  There has not be a huge
outcry for MySQL, but it does come up occasionally.

Good luck!
Scott

> 
> Thanks,
>    Kevin
> 
> Dave Clements, GMOD Help Desk wrote: 
> > Hi Kevin,
> > 
> > MySQL is not officially supported by GMOD for Chado.  Chado takes
> > advantage of many DBMS features, such as foreign keys and other
> > integrity constraints, that were not available in MySQL when Chado
> > was created.  We strongly encourage people to use PostgreSQL. 
> > 
> > If you use MySQL you will have to do some porting of both the schema
> > definition (the DDL), and of the the tools that use Chado.  You also
> > still may not be able to get the same level of data constraints in
> > MySQL as you can with Postgres.  I believe there are a few sites out
> > there that run Chado on MySQL, but I don't know who they are. 
> > 
> > I will:
> > 1. Fix the website to more clearly state the above.
> > 2. See if I can find out who, if anyone, runs Chado on MySQL.  I'll
> > add that list of users to the web site.  I'll also let you know what
> > I find out. 
> > 3. Post this email to the Gmod-schema mailing list.  That list
> > discusses all things Chado.  I recommend subscribing to it if you
> > haven't already done so.
> > 
> > Thanks, 
> > 
> > Dave C.
> > GMOD Help Desk 
> > 
> > 
> > 
> > On Jan 15, 2008 4:59 PM, kevin greer <kevin at agcol.arizona.edu>
> > wrote:
> >         Hello,
> >         
> >         Is there a MySQL version of the Chado schema available.  Do
> >         you know of
> >         any issues with deploying the Chado database in using MySQL?
> >         
> >         Thanks,
> >           Kevin
> > 
> > 
> > 
> 
-- 
------------------------------------------------------------------------
Scott Cain, Ph. D.                                         cain at cshl.edu
GMOD Coordinator (http://www.gmod.org/)                     216-392-3087
Cold Spring Harbor Laboratory