[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-users
Subject:    Re: Question on FetchHashKeyName
From:       Alexander Foken <alexander () foken ! de>
Date:       2017-03-01 8:21:06
Message-ID: 0bd8338a-3936-457a-3210-2ad16491e9da () foken ! de
[Download RAW message or body]

Hi!

As far as I understand generic SQL, all identifiers are case insensitive 
by default. A simple-minded database could simply force lower or upper 
case on all identifiers while processing queries, and it would not 
violate SQL. It could switch from upper to lower case or vice versa with 
every minor patch and still would not violate SQL.

That's why I would never rely on any database to preserve case in 
identifiers.

Yes, there are ways to force a database to preserve case on identifiers 
(quoting them everywhere should work most of the times), but as I said: 
Don't rely on that, it may be an implementation artefact that might 
change at any time.

FetchHashKeyName is the way to go, force either lower (NAME_lc) or upper 
(NAME_uc) case on any identifier returned from the database, and compare 
accordingly. If you want to keep your camelcased names, wrap them in 
lc() or uc() before comparing them with DBI result hash keys. Perl 
should be smart enough to optimize ($key eq 
uc("myPrettyCamelCasedName")) to ($key eq 'MYPRETTYCAMELCASEDNAME') at 
compile time.

As an alternative to using lc/uc, consider tieing the DBI result hash to 
Tie::CPHash. It adds some overhead, your keys won't be camelcased, but 
at least you can access the values in the hash using camelcased keys.

Alexander


On 28.02.2017 19:37, Jonas B. Nielsen wrote:
> Hello All,
> 
> I have been using DBI for some years with MySQL, PostgreSQL, SQLite and Oracle. \
> When using DBI with Oracle I always set FetchHashKeyName to NAME_lc. 
> Over the weekend I was prototyping a new system and I wanted to use SQLite since I \
> was somewhat offline. All of the hash keys were lower-case by default, but the \
> protocol I was trying to adhere to was using camelCase. 
> So my question is (and is has possibly been responded to before):
> 
> - Would it be possible to have FetchHashKeyName preserve case? so if a database was \
> using camelCase this would be preserved. 
> Our basis datamodel is encapsulated in views for separate \
> service/application/protocol implementations, so if I would get the views to adhere \
> to the protocol, I would not have to translated the hash keys (and maintain the \
> translation dictionary afterwards. 
> I checked some FAQ without luck \
> (https://www.fnal.gov/docs/products/perl/pod/site_perl/DBI/FAQ.html), which however \
> seem a bit obsolete (last update 1997) and I cannot find any trace of the FAQ on \
> MetaCPAN. 
> jonasbn
> —
> pauseid: JONASBN
> email: jonasbn@cpan.org
> twitter: @jonasbn
> blog: https://lastmover.wordpress.com/
> 
> 
> 
> 
> 


-- 
Alexander Foken
mailto:alexander@foken.de http://www.foken.de/alexander/


[prev in list] [next in list] [prev in thread] [next in thread]