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

[Christopher H. Laco]

Karl.Moens at marsh.com wrote:
> Matt wrote:
> 
>> 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?
> 
> At least MySQL does not allow you to have colums with names which only 
> differ because of their case.
> 
> ***QUOTE FROM THE MANUAL 5.0***
> 
> 9.2.2. Identifier Case Sensitivity
> In MySQL, databases correspond to directories within the data directory. 
> Each table within a database corresponds to at least one file within the 
> database directory (and possibly more, depending on the storage engine). 
> Consequently, the case sensitivity of the underlying operating system 
> determines the case sensitivity of database and table names. This means 
> database and table names are case sensitive in most varieties of Unix, and 
> not case sensitive in Windows. One notable exception is Mac OS X, which is 
> Unix-based but uses a default filesystem type (HFS+) that is not case 
> sensitive. However, Mac OS X also supports UFS volumes, which are case 
> sensitive just as on any Unix. See Section 1.9.4, ?MySQL Extensions to 
> Standard SQL?. The lower_case_table_names system variable also affects how 
> the server handles identifier case sensitivity, as described later in this 
> section. 
> Note: Although database and table names are not case sensitive on some 
> platforms, you should not refer to a given database or table using 
> different cases within the same statement. The following statement would 
> not work because it refers to a table both as my_table and as MY_TABLE: 
> mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;
> 
> Column, index, stored routine, and trigger names are not case sensitive on 
> any platform, nor are column aliases. 
> ***UNQUOTE FROM THE MANUAL 5.0***
> 
> 
> Karl
> aka CountZero on PerlMonks

There are other cases as well. For example, the default MSSQL install
uses a non case sensitive code page (ok, most US installs), but one can
[and some 3rd party reporting software] requires using a database usings
a case sensitive code page.

My general rule of thumb, which is OCD compliant, is always make your
queries exactly match your structure...even if the system is case
insensitive.

I've gotten bitten in the butt before when moving scripts from one
server to another, where one had a case-sensitive tempdb, and one did not.

In a perfect world, table/columns/relationships should completely honor
what they're fed:

->table('foo')
SELECT * FROM foo

->table('Foo')
SELECT * FROM Foo


->columns(qw/Foo bar BAZ/)
SELECT Foo bar BAZ From Foo


$foo->Foo
$foo->bar
$foo->BAZ


But, I wouldn't expect that to be the default...maybe add a:

__PACKAGE__->case_sensitive(1);


With that said, I'm betting most of this is SQL Abstracts issues, not
DBICs issue...

-=Chris

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 187 bytes
Desc: OpenPGP digital signature
Url : http://lists.rawmode.org/pipermail/dbix-class/attachments/20060214/de12746a/signature.pgp