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

[Richard Holland]

I'm the maintainer for BJX so you can post patches to me.

However, I agree that a consensus needs to be reached on how to store
this data amongst the various projects. There's no point one fixing it
one way and another fixing it the other. This is, from my point of view,
the main problem with having something like BioSQL defined as a schema
rather than as an API that can include defined behaviour (business
logic) as well as database structure (the Oracle version for instance
would expose a public set of PL/SQL stored procedures and projects would
then interact solely via those).

Also, BioJava itself may not be case-insensitive (can't remember how I
coded it now...) so would need changes throughout to things like
equals() methods as well (this has implications going back into the
original code as well as the BJX extensions which build on that).

cheers,
Richard


James Procter wrote:
> Thanks for replying...
> 
> Richard Holland wrote:
>> Hibernate reflects the case-sensitivity of the database it is connected
>> to. There's no options in it for changing that, and so you have to use
>> it the way the database underneath expects you to.
> agreed.
> 
>> However, you can modify your queries so that you convert your query
>> terms to a specific case in advance of the search using the toUpper() or
>> toLower() functions of the String class, then when performing the
>> search, use HQL's lower() or upper() functions inside the query HQL to
>> convert values to the same case when making the comparisons.
> agreed (again).
>> Someone will need to search through the BJX code to find the spots where
>> explicit queries are made against accessions or any other
>> case-insensitive data, then modify it to use the above technique. This
>> would possibly involve introducing HQL queries where currently only
>> direct Hibernate object references are being made.
> ok - this isn't too hard to do. Do you know who currently maintains the
> BiojavaX bindings to BJX ?  I can send them patches - or I guess submit
> them directly... but.. it wont fix the issue.
> 
> In a situation where the backend DB is case sensitive, new accessions
> inserted into the DB must be forced to be the same case in the same way
> as the accession query string will be forced to be. This modification is
> again straightforward, but the case change would also have to be
> propagated to existing entries in the BioSQL. Furthermore, modifying the
> Biojava-x bindings would only ensure case-insensitivity for Biojava
> queries, the same modification would also be necessary for the other
> bio-* bindings.
> 
> This isn't actually a burning issue for me at the moment even though it
> might sound like it from the way I'm posting about it - I had already
> hacked biojava-x to accomodate the case for my specific BioSQL database.
> However, it is important to clarify this situation in order to ensure
> interoperability between biosql databases and off the shelf middleware.
> 
> all the best!
> Jim
> 
> 

-- 
Richard Holland, BSc MBCS
Finance Director, Eagle Genomics Ltd
M: +44 7500 438846 | E: holland at eaglegenomics.com
http://www.eaglegenomics.com/