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

[Ronan SALMON]

Chuck Hagenbuch <chuck at horde.org> a écrit :

> 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.

This could be a Horde configuration option, and by default set to  
insensitive username.

Ronan.