[dev] Case insensitive unique key, was: [horde] Error when updating DB scheme for kronolith

[Chuck Hagenbuch]

Quoting Jan Schneider <jan at horde.org>:

> Zitat von MarkatOSI <buy at mark.net>:
>
>> Problem solved. I found a similar situation described in another posting
>>
>> "I'm having the same issue :
>> SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry
>> 'Nmaurice' for key 'rampage_users_user_name'
>>
>> But over here nmaurice is a user and in our case, I'm guessing that
>> this user usually logs in as 'nmaurice', and logged in at least once
>> as 'Nmaurice'.
>>
>> After running the following query, the migrate script was happy.
>> mysql> update kronolith_events set event_creator_id=lower(event_creator_id);
>
> The core problem is that we have a unique key on  
> rampage_users.user_name. Whether this key is case sensitive or not  
> depends on the table's/database's collation in MySQL. By default any  
> indexes are case insensitive. This breaks because Horde itself is  
> case sensitive.
> The options that I see are:
> - normalize usernames in Content (not good because it would make  
> Horde case insenstive in a single place)
> - enforce collation during table creation (not portable, not sure if  
> possible, you can't just pick cs vs ci but also have to pick a  
> locale for the collation)
> - make this a regular key an ensure uniqueness in userland code (not  
> as elegant)
> - ???

What about making Horde usernames case insensitive everywhere?

I know that we have made a policy of not mucking with usernames, but  
does anyone actually use case sensitivity to make joe and Joe  
different accounts? It seems to cause a lot of confusion for people,  
as well as posing problems like this one.

-chuck