[Open-bio-l] char(n) vs. varchar(n) in BioSQL

[Thomas Down]

On Fri, Mar 08, 2002 at 01:06:40PM -0800, Chris Mungall wrote:
> > 
> > During the last round of schema-changes, some columns
> > were changed from varchar(n) to char(n) data types.  When
> > I questioned this, I was told that it's still possible to
> > store variable-length strings in char(n) columns.  This
> > doesn't turn out to be entirely true.  MySQL includes some
> > special-case code to strip trailing spaces when retrieving
> > char(n) attributes, which gives the illusion of supporting
> > variable length strings.  Other databases don't even do
> > this.
> 
> Actually, it's not MySQL special case code, it's perl - specifically the
> ChopBlanks option in DBI

The MySQL documentation mentions that char(n) attributes
get trimmed.  It works from Java and the SQL monitor, as well,
so I think it must be happening server side.  Presumably, DBI
offers this functionality in a database-independant way.

> > Anyway, just to give other implementors a heads-up: strings
> > from the ontology_term.term_name and seqfeature_source.source_name
> > columns will need to be trimmed client-side, if you want to
> > maintain database portability.  I still think these should
> > probably have stayed varchar(n).  On the other hand, it's
> > not a big enough issue to warrant another schema change.
>
> I'm willing to go with the vote. I thought that fixed with records were
> faster in most DBMSs but I could be imagining that. If this isn't true we
> should switch back to varchar.

I doubt it'll be an issue either way unless the ontology_term
table got huge.

That said, unless this issue's going to cause someone a really
big headache, or unless there's some other reason why the schema
needs to change,in the next few days, my vote would actually be to let sleeping
(Bio)dogs lie.  A nice, stable, schema will make it much easier
to sell BioSQL to the world.  My point in posting was mainly
to make sure this didn't bite other implementations.


Does this sound reasonable,

    Thomas.