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

[Jan Schneider]

Zitat von Michael Rubinsky <mrubinsk at horde.org>:

> 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)
>> - ???
>
> This is only an issue when the authentication backend is case  
> insensitive, right? Wouldn't ensuring the username is case sensitive,

No, it doesn't have anything to do with the authencation backend.  
Whether Joe and joe are different users in the backend or not doesn't  
matter, they are always different users in Horde.

> when using an auth backend that is not case sensitive, be the job of  
> one of the authentication hooks? This is how we always recommended  
> fixing this issue in Horde 3. This would ensure that the same  
> username is *always* used, and would alleviate the need for any of  
> the above "fixes".

Exactly, but this only applies if joe and Joe are the same in the  
authentication backend. The admins are responsible for normalizing  
user names if necessary. But *if* Joe and joe are indeed different  
users, we have to support that. At the moment this is not possible in  
these few places.

>
>
>> Other places that might be affected:
>> - group names
>> - tag names (we already normalize them in php code)
>> - vilma domain and user names
>> - wiki page names
>>
>> Jan.
>
> Shouldn't this be handled by
>
>
>
>> Do you need professional PHP or Horde consulting?

By what? Professional consulting? Sure :)

Jan.

-- 
Do you need professional PHP or Horde consulting?
http://horde.org/consulting/