[BioSQL-l] case sensitivity in biosql accessions under BioJavax/Hibernate

[Hilmar Lapp]

On Dec 3, 2008, at 9:56 AM, James Procter wrote:

> I was wondering - are bioentry accessions intended to be case
> insensitive under the archetypal BioSQL schema ?


Sorry for chiming in a bit late here. Accessions are case sensitive in  
BioSQL at the level of the relational model.

In fact, this is enforced for MySQL (which unilaterally chose to treat  
the SQL VARCHAR datatype as case-insensitive) by making the type  
VARCHAR BINARY.

I'm rather disinclined to change that, I have to say. I realize that  
many (all?) of the databases we typically use treat accessions as case- 
insensitive. But I doubt that that's part of the specs in each case,  
and there is no standard that would oblige future databases to do the  
same.

Rather, I think it's application (or data source) level semantics, and  
should hence be implemented at that level if desired. In full-featured  
RDBMSs that's actually not very difficult. For example, you can build  
a function index on UPPER(accession), which gives you indexed access  
to case-insensitive accessions w/o changing the model itself. As Mark  
mentioned, Hibernate can be taught to use functions like these too.

Does that make sense?

	-hilmar
-- 
===========================================================
: Hilmar Lapp  -:-  Durham, NC  -:-  hlapp at gmx dot net :
===========================================================