[Opensim-dev] Uniqueness in MySQL useraccounts ('Name' field)

[Melanie]

Actually, it's the combination FirstName, LastName and ScopeID that
needs to be kept unique as different scopes may well contain users
with identical names.

Such a change would break some existing code (web interfaces
mostly). Opensim allows you to modify the database, so if you
add/change this on your system opensim will still work. This may be
a better and faster solution for you.

Melanie

On 07/03/2012 07:16, Kevin Buckley wrote:
> I have a question/request which was being discussed on the Opensim Users
> list and Justin suggested it might be better handled here?
> 
>  
> 
> As a developer of an Opensim member/user system I need to maintain the
> attribute that the combination of FirstName and LastName is unique within an
> individual Opensim implementation.
> 
>  
> 
> To achieve that, on a server which supports multiple, concurrent signups,
> the best way is to have a unique, composite key in the useraccounts table
> which includes FirstName and LastName and then trap MySQL error 1062
> (duplicate entry) when attempting to create a new user record.  This avoids
> the situation where the code checks for uniqueness in step-1 and then, if it
> satisfies that, writes the new user record in step-2; with the risk that
> another process writes a duplicate record between those steps.
> 
>  
> 
> There are other ways to deal with this but they introduce their own issues
> and they're not as efficient as the method above.
> 
>  
> 
> The Opensim setup process already goes to the trouble of creating such a
> composite key, called 'Name'.  However, it doesn't give it the UNIQUE
> attribute.
> 
>  
> 
> So, currently (as an easy fix and to avoid having to remember to manually
> edit the database index every time I upgrade Opensim) my code forces 'Name'
> to be UNIQUE every time a new user subscribes.  But clearly this is a large
> and mostly pointless overhead as it actually only needs to do it once.
> 
>  
> 
> So my question/request is: is it possible to modify subsequent Opensim
> releases so that 'Name' is given the UNIQUE attribute by default (probably a
> one line code change I imagine)?
> 
>  
> 
> I can't see any downside to this and, unless I'm missing something, it seems
> a logical thing to do?
> 
>  
> 
> Kevin
> 
> 
> 
> 
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev