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

[Michael Rubinsky]

Quoting Jan Schneider <jan at horde.org>:

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

Ah. Silly me didn't account for the fact that joe and Joe may be  
different users. Makes sense.


>>
>>
>>> 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 :)

Heh, yeah... started replying here and must have forgot to delete it :)



> Jan.
>
> -- 
> Do you need professional PHP or Horde consulting?
> http://horde.org/consulting/
>
> -- 
> Horde developers mailing list
> Frequently Asked Questions: http://horde.org/faq/
> To unsubscribe, mail: dev-unsubscribe at lists.horde.org


mike

The Horde Project (www.horde.org)
mrubinsk at horde.org