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

[Kevin Buckley]

What does ScopeID do (if that can be answered in one sentence)?

-----Original Message-----
From: opensim-dev-bounces at lists.berlios.de
[mailto:opensim-dev-bounces at lists.berlios.de] On Behalf Of Melanie
Sent: 07 March 2012 10:25
To: opensim-dev at lists.berlios.de
Subject: Re: [Opensim-dev] Uniqueness in MySQL useraccounts ('Name' field)

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
_______________________________________________
Opensim-dev mailing list
Opensim-dev at lists.berlios.de
https://lists.berlios.de/mailman/listinfo/opensim-dev