[opencms-dev] RV: Beta 2 - case-sensitiveness in MySQL 4

[Claus Priisholm]

I think the issue is that ideally these things shouldn't matter - table 
names should be case insensitive. And they are on the databases I am 
familiar with except for MySQL. MySQL provides means to rectify this 
issue by setting the lower_case_table_names=1 parameter but it is not 
the default setting on unix-systems (apart from Mac OS X).
I still think this is a deficiency in MySQL because it exposes 
differences between the underlying OS', but since people expect things 
to work anyway it seems that using the same case in both scripts and 
code should solve the immediate problem.

The next question is whether it should be upper or lower case. 
Primarily a matter of taste, since there are still some issues whether 
you choose the one or the other. I really haven't spend a lot of time 
studying this but it is my understanding that this is how three common 
database systems works:

MySQL default behaviour: on unix systems (except Mac OS X) keeps the 
case of table names and name comparisons are case-sensitive, on 
Windows/Mac OS X it stores names as lower case and name comparisons are 
case-insensitive. All in all it favours lower case names to the extend 
that SHOW TABLES then always shows the table names in the case that you 
would except them to do.

PostgreSQL converts names to lower case (unless you quote them), name 
comparisons are also lower case (unless you use the quote them).

Oracle internally stores the table names as upper case but it is  case 
insensitive during comparison (with one exception, if you start 
fiddling with the meta data you will see that they are stored as upper 
case).

So choosing to go all lower case or all upper case helps solving the 
lack of cross-platform consistency in MySQL, but once you start relying 
on the meta-data then there are potentiel problems lurking.

My own preference is to use lower case names based on the following 
very scientific argument: it saves me from having to use the shift-key 
on systems that are case-sensitive :-)


On 22/4-2005, at 8.01, Alexander Kandzior wrote:

>> As you state it is not difficult (though it may be tedious)
>> to change the source code to use lower case table names.
>> Check with Alkacon first, but I doubt that they are working
>> on that particular issue at this point in time so they
>> probably won't mind a helping hand.
>
> So table names should always be lower case? For OpenCms 6 we put all 
> table
> names (and SQL Queries) to upper case, so there should be no more 
> mixing of
> upper / lower case. Will this be enough or why should table names be 
> always
> lower case?
>
> Best Regards,
> Alex.
>
> Alexander Kandzior
> Alkacon Software - The OpenCms Experts
> http://www.alkacon.com
>
>
>
>
>
> _______________________________________________
> This mail is send to you from the opencms-dev mailing list
> To change your list options, or to unsubscribe from the list, please 
> visit
> http://mail.opencms.org/mailman/listinfo/opencms-dev
>
>
--
Claus Priisholm, CodeDroids ApS
+45 48 22 46 46
email: cpr (you-know-what) interlet.dk - cpr (you-know-what) 
codedroids.com
skype: claus_priisholm
http://www.interlet.dk - http://www.codedroids.com