[horde] Fwd: [dev] Case insensitive unique key

[Rafael Varela Pet]

El mar, 03-05-2011 a las 17:20 +0200, Jan Schneider escribió:

> Admins,
> 
> please take a look at this conversation on the developer mailing list.  
> This is the context for the question if we should normalize all user  
> names to lower case in Horde 5.

+1 for normalized user names



> ----- Weitergeleitete Nachricht von Chuck Hagenbuch <chuck at horde.org> -----
>    Datum: Sun, 24 Apr 2011 00:06:36 -0400
>      Von: Chuck Hagenbuch <chuck at horde.org>
> Betreff: Re: [dev] Case insensitive unique key, was: [horde] Error  
> when updating DB scheme for kronolith
>       An: dev at lists.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)
> > - ???
> 
> 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.


Regards,
-- 
Rafael Varela Pet
Area de Tecnoloxías da Información e Comunicacións

Universidade de Santiago de Compostela
15782 Santiago de Compostela
http://www.usc.es/atic/sistemas