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

[Melanie]

It allows to keep data from more than one grid in the same set of
tables (for hosting multiple grids on shared systems)

Melanie

On 07/03/2012 11:42, Kevin Buckley wrote:
> 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
> 
> _______________________________________________
> Opensim-dev mailing list
> Opensim-dev at lists.berlios.de
> https://lists.berlios.de/mailman/listinfo/opensim-dev
> 
>