[Dbix-class] Case sensitivity in column names using Oracle

[Matt S Trout]

On Mon, Feb 13, 2006 at 03:02:33PM +0000, Michael Gray wrote:
> Hello all, and thanks for doing a great job with DBI::Class.  
> 
> I'm just starting in with DBIC, and I am trying to port an existing 
> application which was written to use DBIx::Recordset.  Smooth progress 
> with tables which are largely read-only, but I've hit a big bump with 
> inserting into tables with auto-incrementing keys.  
> 
> This first manifested itself as appauling performance on multiple 
> inserts, whereas everything else had thus far been significantly faster. 
> Closer investigation revealed that PK::Auto::insert() was calling 
> ResultSource::column_info() and thus Storage::DBI::columns_info_for() 
> and DBD::Oracle::column_info() on *every* insert.
> 
> Further digging revealed that my database is set up with upper-case 
> table and column names, but that the application uses lower-case 
> everywhere.  This mostly works, except that columns_info_for() gets the 
> upper-case information back, and column_info fails to cache anything as 
> it is churning through the returned info looking for entries using 
> lower-case column names.

That's ... annoying. It should probably fire once only and leave a flag
to say it's done so to avoid re-checking.

> It's possible to work around this by specifying upper-case names to 
> add_columns() in my schema class, but this results in upper-case 
> accessors and is going to be a right pain (not to mention ugly) in my 
> application.

__PACKAGE__->add_columns(Foo => { accessor => 'foo' }, ...);

should provide a workaround.
 
> I'd like to do something more systematic, possibly doing case conversion 
> in ResultSource::column_info() or Storage::DBI::columns_info_for(), but 
> I'm not sure where would be best, nor how to toggle the behaviour in a 
> DBIx::Class approved fashion.

I guess $source->column_info should handle the DWIMery. As to how to do this
in the most portable possible fashion, I'm not sure - does anybody know if
there are any databases that'll let you have foo, Foo and FOO all as column
names on the same table? (I used to believe this sort of insanity never
happened. then I released DBIC :)

-- 
     Matt S Trout       Offering custom development, consultancy and support
  Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

 + Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +