[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 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,  
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".



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