You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Cory Nelson <ph...@gmail.com> on 2009/11/12 02:00:34 UTC
Ensuring unique attributes across documents?
Hello,
I'm trying to implement a typical user signup operation: when you
finally add a user to the database, you want it to have both a unique
user name, and a unique email. In SQL I'd do something like:
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
if(count == 0)
{
INSERT INTO t_users VALUES(...)
COMMIT
}
else
{
ROLLBACK
}
Can anyone give an example of how to achieve the same thing in CouchDB?
--
Cory Nelson
Re: Ensuring unique attributes across documents?
Posted by Jan Lehnardt <ja...@apache.org>.
Hi Cory,
On 12 Nov 2009, at 04:59, Cory Nelson wrote:
> Indeed, that would be great if there were only one piece of data to
> keep unique. In fact, I'm already doing that for the user name. But
> what about the email?
>
> I guess this could really be made into a more generic question: how do
> you create or alter one document only if pre-conditions are met in
> other documents, in a way that doesn't risk permanent inconsistencies
> if the database or client crash, or with partitioning?
You don't. A single document is the largest unit you can work with in
isolation. if requests would be allowed to work on multiple documents
at the same time and create dependencies, we'd end up implementing
locking and other nasty things that we work hard on to avoid.
A layer on top of one more more CouchDB could enforce your
guarantees, but CouchDB does not. Sorry :)
Cheers
Jan
--
>
> Or alternately: is this merely a design that worked in transactional
> dbs but doesn't translate to something like CouchDB? If so, what's
> the recommended way to handle things like this?
>
> Thanks!
>
> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
> <za...@gmail.com> wrote:
>> You COULD use the User document's ID to ensure uniqueness. The second
>> PUT to the same database URL would result in an HTTP 409 status code.
>>
>> Here it is on StackOverflow:
>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>>
>>
>> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>>> Hello,
>>>
>>> I'm trying to implement a typical user signup operation: when you
>>> finally add a user to the database, you want it to have both a unique
>>> user name, and a unique email. In SQL I'd do something like:
>>>
>>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>>
>>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>>
>>> if(count == 0)
>>> {
>>> INSERT INTO t_users VALUES(...)
>>> COMMIT
>>> }
>>> else
>>> {
>>> ROLLBACK
>>> }
>>>
>>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>>
>>> --
>>> Cory Nelson
>>>
>>
>
Re: Ensuring unique attributes across documents?
Posted by Zachary Zolton <za...@gmail.com>.
@janl, was thinking of mentioning Redis as well, but I thought my
email was getting long enough already. LOL
On Thu, Nov 12, 2009 at 5:18 PM, Chris Anderson <jc...@apache.org> wrote:
> On Thu, Nov 12, 2009 at 3:06 PM, Jan Lehnardt <ja...@apache.org> wrote:
>>
>> On 12 Nov 2009, at 23:43, Zachary Zolton wrote:
>>
>>> Sure, you could even maintain a database just for uniqueness, with
>>> strings like "attr_name-attr_value" as the doc IDs.
>>>
>>> BUT, you still have problems:
>>> * Writing multiple docs leaves an open window for race conditions
>>> (not so bad really)
>>> * Any other server you replicate from may have already accepted a
>>> doc with that ID
>>>
>>> You can avoid this by only writing to a single master database, but
>>> you should be aware that you're introducing a Single Point of Failure.
>>> At that point you could even consider using a transactional RDBMS to
>>> allocate the unique values.
>>
>> or a lightweight redis store. If I'd go for the SPOF route, I'd build the
>> most lightweight solution possible.
>>
>> Cheers
>> Jan
>> --
>>
>
> Let's remember that document ids are a great way to enforce
> uniqueness. You just have to remember when you use them, that you
> should namespace yourself so you aren't incompatible with data-merges
> (replication) with other systems. So something like
>
> com.example.user:jchris
>
> instead of just
>
> user:jchris
>
> Using docids will mean collisions are eventually detected on clusters,
> which is probably better than using uuids + secondary attributes in a
> view query.
>
> I know this doesn't solve the 2-values problem, but before people get
> carried away with uniqueness servers etc, do try to think about if you
> can model it with a single uniqueness constraint.
>
> Chris
>
>>
>>
>>>
>>> --Zach
>>>
>>> On Thu, Nov 12, 2009 at 3:46 PM, Adam Wolff <aw...@gmail.com> wrote:
>>>> Put a separate document under the email address that refers to the username.
>>>> Use view collation to build the user record.
>>>>
>>>> A
>>>>
>>>> On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>
>>>>> Indeed, that would be great if there were only one piece of data to
>>>>> keep unique. In fact, I'm already doing that for the user name. But
>>>>> what about the email?
>>>>>
>>>>> I guess this could really be made into a more generic question: how do
>>>>> you create or alter one document only if pre-conditions are met in
>>>>> other documents, in a way that doesn't risk permanent inconsistencies
>>>>> if the database or client crash, or with partitioning?
>>>>>
>>>>> Or alternately: is this merely a design that worked in transactional
>>>>> dbs but doesn't translate to something like CouchDB? If so, what's
>>>>> the recommended way to handle things like this?
>>>>>
>>>>> Thanks!
>>>>>
>>>>> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
>>>>> <za...@gmail.com> wrote:
>>>>>> You COULD use the User document's ID to ensure uniqueness. The second
>>>>>> PUT to the same database URL would result in an HTTP 409 status code.
>>>>>>
>>>>>> Here it is on StackOverflow:
>>>>>>
>>>>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>>>>>>
>>>>>>
>>>>>> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> I'm trying to implement a typical user signup operation: when you
>>>>>>> finally add a user to the database, you want it to have both a unique
>>>>>>> user name, and a unique email. In SQL I'd do something like:
>>>>>>>
>>>>>>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>>>>>>
>>>>>>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>>>>>>
>>>>>>> if(count == 0)
>>>>>>> {
>>>>>>> INSERT INTO t_users VALUES(...)
>>>>>>> COMMIT
>>>>>>> }
>>>>>>> else
>>>>>>> {
>>>>>>> ROLLBACK
>>>>>>> }
>>>>>>>
>>>>>>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>>>>>>
>>>>>>> --
>>>>>>> Cory Nelson
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>
>
>
> --
> Chris Anderson
> http://jchrisa.net
> http://couch.io
>
Re: Ensuring unique attributes across documents?
Posted by Brian Candler <B....@pobox.com>.
On Sun, Nov 15, 2009 at 08:15:10AM +0000, Brian Candler wrote:
> But in this case there are more relaxed solutions.
Also, if you've read the following at
http://couchdb.apache.org/docs/intro.html
"Most applications require no special planning to take advantage of
distributed updates and replication."
You should note that relying on doc_id to enforce uniqueness of a particular
value will prevent your application from working properly in a distributed
multi-master setup, since you'll no longer have this uniqueness guarantee.
Similarly, relying on 409 PUT responses to prevent revision conflicts within
the database will no longer work with multiple masters.
This is just a laws-of-physics thing: if you are doing independent updates
on disconnected nodes, you cannot enforce uniqueness of a value. You either
need some central locking service, or else you just deal with the non-unique
values afterwards.
The latter (relaxed) approach could be quite straightforward. e.g. when you
fetch a document by attribute foo using view_docs_by_foo, invoke it with
:limit=>2. If you get one document then there's no problem. If you get 2
then there is, and you can trigger a procedure by which either the user or
the system administrator has to sort it out.
If you are using the doc_id rather than an attribute, what you'll actually
find is two revisions of the document under the same doc_id, rather than two
different docs sharing the same attribute value. In principle you deal with
this in the same way.
Regards,
Brian.
Re: Ensuring unique attributes across documents?
Posted by Brian Candler <B....@pobox.com>.
On Fri, Nov 13, 2009 at 08:35:43AM -0800, Adam Wolff wrote:
> I know it's uptight (not relaxed) to worry about multiple users
> claiming the same email address, but for some apps, it's important to
> disallow it.
But in this case there are more relaxed solutions.
For example - suppose on signup you send a user an E-mail containing an
activation link to confirm that the person who claimed that E-mail really is
that person. Then if by some fluke or race condition you get two accounts
which claim the same E-mail, then you know it must actually be the same
person twice. Therefore you can just ignore the subsequent signups.
When you find a user by their E-mail address, you just take the first row
returned (use :limit=>1). With time-based UUID generation this will be the
first time they signed up.
You can do a periodic sweep looking for multiple occurrences of the same
username if you want to tidy up the database.
B.
Re: Ensuring unique attributes across documents?
Posted by Adam Wolff <aw...@gmail.com>.
Wait a sec. I know a lot of people on this list are building
distributed apps served directly out of couchdb, but not everyone is.
Couchdb is a great source of global uniqueness, as long as you just
declare "this database does
not accept merges" which you can do within your application.
I know it's uptight (not relaxed) to worry about multiple users
claiming the same email address, but for some apps, it's important to
disallow it.
A
On Thursday, November 12, 2009, Chris Anderson <jc...@apache.org> wrote:
> On Thu, Nov 12, 2009 at 3:06 PM, Jan Lehnardt <ja...@apache.org> wrote:
>>
>> On 12 Nov 2009, at 23:43, Zachary Zolton wrote:
>>
>>> Sure, you could even maintain a database just for uniqueness, with
>>> strings like "attr_name-attr_value" as the doc IDs.
>>>
>>> BUT, you still have problems:
>>> * Writing multiple docs leaves an open window for race conditions
>>> (not so bad really)
>>> * Any other server you replicate from may have already accepted a
>>> doc with that ID
>>>
>>> You can avoid this by only writing to a single master database, but
>>> you should be aware that you're introducing a Single Point of Failure.
>>> At that point you could even consider using a transactional RDBMS to
>>> allocate the unique values.
>>
>> or a lightweight redis store. If I'd go for the SPOF route, I'd build the
>> most lightweight solution possible.
>>
>> Cheers
>> Jan
>> --
>>
>
> Let's remember that document ids are a great way to enforce
> uniqueness. You just have to remember when you use them, that you
> should namespace yourself so you aren't incompatible with data-merges
> (replication) with other systems. So something like
>
> com.example.user:jchris
>
> instead of just
>
> user:jchris
>
> Using docids will mean collisions are eventually detected on clusters,
> which is probably better than using uuids + secondary attributes in a
> view query.
>
> I know this doesn't solve the 2-values problem, but before people get
> carried away with uniqueness servers etc, do try to think about if you
> can model it with a single uniqueness constraint.
>
> Chris
>
>>
>>
>>>
>>> --Zach
>>>
>>> On Thu, Nov 12, 2009 at 3:46 PM, Adam Wolff <aw...@gmail.com> wrote:
>>>> Put a separate document under the email address that refers to the username.
>>>> Use view collation to build the user record.
>>>>
>>>> A
>>>>
>>>> On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>
>>>>> Indeed, that would be great if there were only one piece of data to
>>>>> keep unique. In fact, I'm already doing that for the user name. But
>>>>> what about the email?
>>>>>
>>>>> I guess this could really be made into a more generic question: how do
>>>>> you create or alter one document only if pre-conditions are met in
>>>>> other documents, in a way that doesn't risk permanent inconsistencies
>>>>> if the database or client crash, or with partitioning?
>>>>>
>>>>> Or alternately: is this merely a design that worked in transactional
>>>>> dbs but doesn't translate to something like CouchDB? If so, what's
>>>>> the recommended way to handle things like this?
>>>>>
>>>>> Thanks!
>>>>>
>>>>> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
>>>>> <za...@gmail.com> wrote:
>>>>>> You COULD use the User document's ID to ensure uniqueness. The second
>>>>>> PUT to the same database URL would result in an HTTP 409 status code.
>>>>>>
>>>>>> Here it is on StackOverflow:
>>>>>>
>>>>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>>>>>>
>>>>>>
>>>>>> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>>>> Hello,
>>>>>>>
>>>>>>> I'm trying to implement a typical user signup operation: when you
>>>>>>> finally add a user to the database, you want it to have both a unique
>>>>>>> user name, and a unique email. In SQL I'd do something like:
>>>>>>>
>>>>>>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>>>>>>
>>>>>>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>>>>>>
>>>>>>> if(count == 0)
>>>>>>> {
>>>>>>> INSERT INTO t_users VALUES(...)
>>>>>>> COMMIT
>>>>>>> }
>>>>>>> else
>>>>>>> {
>>>>>>> ROLLBACK
>>>>>>> }
>>>>>>>
>>>>>>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>>>>>>
>>>>>>> --
>>>>>>> Cory Nelson
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>>
>
>
>
> --
> Chris Anderson
> http://jchrisa.net
> http://couch.io
>
Re: Ensuring unique attributes across documents?
Posted by Chris Anderson <jc...@apache.org>.
On Thu, Nov 12, 2009 at 3:06 PM, Jan Lehnardt <ja...@apache.org> wrote:
>
> On 12 Nov 2009, at 23:43, Zachary Zolton wrote:
>
>> Sure, you could even maintain a database just for uniqueness, with
>> strings like "attr_name-attr_value" as the doc IDs.
>>
>> BUT, you still have problems:
>> * Writing multiple docs leaves an open window for race conditions
>> (not so bad really)
>> * Any other server you replicate from may have already accepted a
>> doc with that ID
>>
>> You can avoid this by only writing to a single master database, but
>> you should be aware that you're introducing a Single Point of Failure.
>> At that point you could even consider using a transactional RDBMS to
>> allocate the unique values.
>
> or a lightweight redis store. If I'd go for the SPOF route, I'd build the
> most lightweight solution possible.
>
> Cheers
> Jan
> --
>
Let's remember that document ids are a great way to enforce
uniqueness. You just have to remember when you use them, that you
should namespace yourself so you aren't incompatible with data-merges
(replication) with other systems. So something like
com.example.user:jchris
instead of just
user:jchris
Using docids will mean collisions are eventually detected on clusters,
which is probably better than using uuids + secondary attributes in a
view query.
I know this doesn't solve the 2-values problem, but before people get
carried away with uniqueness servers etc, do try to think about if you
can model it with a single uniqueness constraint.
Chris
>
>
>>
>> --Zach
>>
>> On Thu, Nov 12, 2009 at 3:46 PM, Adam Wolff <aw...@gmail.com> wrote:
>>> Put a separate document under the email address that refers to the username.
>>> Use view collation to build the user record.
>>>
>>> A
>>>
>>> On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>
>>>> Indeed, that would be great if there were only one piece of data to
>>>> keep unique. In fact, I'm already doing that for the user name. But
>>>> what about the email?
>>>>
>>>> I guess this could really be made into a more generic question: how do
>>>> you create or alter one document only if pre-conditions are met in
>>>> other documents, in a way that doesn't risk permanent inconsistencies
>>>> if the database or client crash, or with partitioning?
>>>>
>>>> Or alternately: is this merely a design that worked in transactional
>>>> dbs but doesn't translate to something like CouchDB? If so, what's
>>>> the recommended way to handle things like this?
>>>>
>>>> Thanks!
>>>>
>>>> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
>>>> <za...@gmail.com> wrote:
>>>>> You COULD use the User document's ID to ensure uniqueness. The second
>>>>> PUT to the same database URL would result in an HTTP 409 status code.
>>>>>
>>>>> Here it is on StackOverflow:
>>>>>
>>>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>>>>>
>>>>>
>>>>> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>>> Hello,
>>>>>>
>>>>>> I'm trying to implement a typical user signup operation: when you
>>>>>> finally add a user to the database, you want it to have both a unique
>>>>>> user name, and a unique email. In SQL I'd do something like:
>>>>>>
>>>>>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>>>>>
>>>>>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>>>>>
>>>>>> if(count == 0)
>>>>>> {
>>>>>> INSERT INTO t_users VALUES(...)
>>>>>> COMMIT
>>>>>> }
>>>>>> else
>>>>>> {
>>>>>> ROLLBACK
>>>>>> }
>>>>>>
>>>>>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>>>>>
>>>>>> --
>>>>>> Cory Nelson
>>>>>>
>>>>>
>>>>
>>>
>>
>
>
--
Chris Anderson
http://jchrisa.net
http://couch.io
Re: Ensuring unique attributes across documents?
Posted by Jan Lehnardt <ja...@apache.org>.
On 12 Nov 2009, at 23:43, Zachary Zolton wrote:
> Sure, you could even maintain a database just for uniqueness, with
> strings like "attr_name-attr_value" as the doc IDs.
>
> BUT, you still have problems:
> * Writing multiple docs leaves an open window for race conditions
> (not so bad really)
> * Any other server you replicate from may have already accepted a
> doc with that ID
>
> You can avoid this by only writing to a single master database, but
> you should be aware that you're introducing a Single Point of Failure.
> At that point you could even consider using a transactional RDBMS to
> allocate the unique values.
or a lightweight redis store. If I'd go for the SPOF route, I'd build the
most lightweight solution possible.
Cheers
Jan
--
>
> --Zach
>
> On Thu, Nov 12, 2009 at 3:46 PM, Adam Wolff <aw...@gmail.com> wrote:
>> Put a separate document under the email address that refers to the username.
>> Use view collation to build the user record.
>>
>> A
>>
>> On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
>>
>>> Indeed, that would be great if there were only one piece of data to
>>> keep unique. In fact, I'm already doing that for the user name. But
>>> what about the email?
>>>
>>> I guess this could really be made into a more generic question: how do
>>> you create or alter one document only if pre-conditions are met in
>>> other documents, in a way that doesn't risk permanent inconsistencies
>>> if the database or client crash, or with partitioning?
>>>
>>> Or alternately: is this merely a design that worked in transactional
>>> dbs but doesn't translate to something like CouchDB? If so, what's
>>> the recommended way to handle things like this?
>>>
>>> Thanks!
>>>
>>> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
>>> <za...@gmail.com> wrote:
>>>> You COULD use the User document's ID to ensure uniqueness. The second
>>>> PUT to the same database URL would result in an HTTP 409 status code.
>>>>
>>>> Here it is on StackOverflow:
>>>>
>>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>>>>
>>>>
>>>> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>>>>> Hello,
>>>>>
>>>>> I'm trying to implement a typical user signup operation: when you
>>>>> finally add a user to the database, you want it to have both a unique
>>>>> user name, and a unique email. In SQL I'd do something like:
>>>>>
>>>>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>>>>
>>>>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>>>>
>>>>> if(count == 0)
>>>>> {
>>>>> INSERT INTO t_users VALUES(...)
>>>>> COMMIT
>>>>> }
>>>>> else
>>>>> {
>>>>> ROLLBACK
>>>>> }
>>>>>
>>>>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>>>>
>>>>> --
>>>>> Cory Nelson
>>>>>
>>>>
>>>
>>
>
Re: Ensuring unique attributes across documents?
Posted by Zachary Zolton <za...@gmail.com>.
Sure, you could even maintain a database just for uniqueness, with
strings like "attr_name-attr_value" as the doc IDs.
BUT, you still have problems:
* Writing multiple docs leaves an open window for race conditions
(not so bad really)
* Any other server you replicate from may have already accepted a
doc with that ID
You can avoid this by only writing to a single master database, but
you should be aware that you're introducing a Single Point of Failure.
At that point you could even consider using a transactional RDBMS to
allocate the unique values.
--Zach
On Thu, Nov 12, 2009 at 3:46 PM, Adam Wolff <aw...@gmail.com> wrote:
> Put a separate document under the email address that refers to the username.
> Use view collation to build the user record.
>
> A
>
> On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
>
>> Indeed, that would be great if there were only one piece of data to
>> keep unique. In fact, I'm already doing that for the user name. But
>> what about the email?
>>
>> I guess this could really be made into a more generic question: how do
>> you create or alter one document only if pre-conditions are met in
>> other documents, in a way that doesn't risk permanent inconsistencies
>> if the database or client crash, or with partitioning?
>>
>> Or alternately: is this merely a design that worked in transactional
>> dbs but doesn't translate to something like CouchDB? If so, what's
>> the recommended way to handle things like this?
>>
>> Thanks!
>>
>> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
>> <za...@gmail.com> wrote:
>> > You COULD use the User document's ID to ensure uniqueness. The second
>> > PUT to the same database URL would result in an HTTP 409 status code.
>> >
>> > Here it is on StackOverflow:
>> >
>> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>> >
>> >
>> > On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>> >> Hello,
>> >>
>> >> I'm trying to implement a typical user signup operation: when you
>> >> finally add a user to the database, you want it to have both a unique
>> >> user name, and a unique email. In SQL I'd do something like:
>> >>
>> >> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> >>
>> >> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>> >>
>> >> if(count == 0)
>> >> {
>> >> INSERT INTO t_users VALUES(...)
>> >> COMMIT
>> >> }
>> >> else
>> >> {
>> >> ROLLBACK
>> >> }
>> >>
>> >> Can anyone give an example of how to achieve the same thing in CouchDB?
>> >>
>> >> --
>> >> Cory Nelson
>> >>
>> >
>>
>
Re: Ensuring unique attributes across documents?
Posted by Adam Wolff <aw...@gmail.com>.
Put a separate document under the email address that refers to the username.
Use view collation to build the user record.
A
On Wed, Nov 11, 2009 at 7:59 PM, Cory Nelson <ph...@gmail.com> wrote:
> Indeed, that would be great if there were only one piece of data to
> keep unique. In fact, I'm already doing that for the user name. But
> what about the email?
>
> I guess this could really be made into a more generic question: how do
> you create or alter one document only if pre-conditions are met in
> other documents, in a way that doesn't risk permanent inconsistencies
> if the database or client crash, or with partitioning?
>
> Or alternately: is this merely a design that worked in transactional
> dbs but doesn't translate to something like CouchDB? If so, what's
> the recommended way to handle things like this?
>
> Thanks!
>
> On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
> <za...@gmail.com> wrote:
> > You COULD use the User document's ID to ensure uniqueness. The second
> > PUT to the same database URL would result in an HTTP 409 status code.
> >
> > Here it is on StackOverflow:
> >
> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
> >
> >
> > On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
> >> Hello,
> >>
> >> I'm trying to implement a typical user signup operation: when you
> >> finally add a user to the database, you want it to have both a unique
> >> user name, and a unique email. In SQL I'd do something like:
> >>
> >> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
> >>
> >> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
> >>
> >> if(count == 0)
> >> {
> >> INSERT INTO t_users VALUES(...)
> >> COMMIT
> >> }
> >> else
> >> {
> >> ROLLBACK
> >> }
> >>
> >> Can anyone give an example of how to achieve the same thing in CouchDB?
> >>
> >> --
> >> Cory Nelson
> >>
> >
>
Re: Ensuring unique attributes across documents?
Posted by Paul Davis <pa...@gmail.com>.
On Wed, Nov 11, 2009 at 10:59 PM, Cory Nelson <ph...@gmail.com> wrote:
> Indeed, that would be great if there were only one piece of data to
> keep unique. In fact, I'm already doing that for the user name. But
> what about the email?
>
> I guess this could really be made into a more generic question: how do
> you create or alter one document only if pre-conditions are met in
> other documents, in a way that doesn't risk permanent inconsistencies
> if the database or client crash, or with partitioning?
>
> Or alternately: is this merely a design that worked in transactional
> dbs but doesn't translate to something like CouchDB? If so, what's
> the recommended way to handle things like this?
>
> Thanks!
Another thing to think about is what permanent inconsistencies means
to you. For a user signup workflow, you could use autocomplete
suggestions from a view to help the user pick a unique name and check
if an entered user id exists. Then just submit the form and check a
view if the new account is kosher.
That's obviously racey, but generally it doesn't matter. How often are
you going to have two people trying to signup with the same email
address? And you can resolve race conditions by using the update seq.
Who ever saved first wins. The loser just gets a friendly, "Oops,
someone just registered that email address."
Granted that doesn't prevent replication from ruining things, but how
often do you want to replicate unknown data into the database holding
user info?
And another thought process is, do you really want globally unique
email addresses? Or do you just want to prevent a spammer signing up
thousands of accounts?
Paul Davis
Re: Ensuring unique attributes across documents?
Posted by Cory Nelson <ph...@gmail.com>.
Indeed, that would be great if there were only one piece of data to
keep unique. In fact, I'm already doing that for the user name. But
what about the email?
I guess this could really be made into a more generic question: how do
you create or alter one document only if pre-conditions are met in
other documents, in a way that doesn't risk permanent inconsistencies
if the database or client crash, or with partitioning?
Or alternately: is this merely a design that worked in transactional
dbs but doesn't translate to something like CouchDB? If so, what's
the recommended way to handle things like this?
Thanks!
On Wed, Nov 11, 2009 at 6:14 PM, Zachary Zolton
<za...@gmail.com> wrote:
> You COULD use the User document's ID to ensure uniqueness. The second
> PUT to the same database URL would result in an HTTP 409 status code.
>
> Here it is on StackOverflow:
> http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
>
>
> On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
>> Hello,
>>
>> I'm trying to implement a typical user signup operation: when you
>> finally add a user to the database, you want it to have both a unique
>> user name, and a unique email. In SQL I'd do something like:
>>
>> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>>
>> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>>
>> if(count == 0)
>> {
>> INSERT INTO t_users VALUES(...)
>> COMMIT
>> }
>> else
>> {
>> ROLLBACK
>> }
>>
>> Can anyone give an example of how to achieve the same thing in CouchDB?
>>
>> --
>> Cory Nelson
>>
>
Re: Ensuring unique attributes across documents?
Posted by Zachary Zolton <za...@gmail.com>.
You COULD use the User document's ID to ensure uniqueness. The second
PUT to the same database URL would result in an HTTP 409 status code.
Here it is on StackOverflow:
http://stackoverflow.com/questions/1058258/does-couchdb-support-unqiue-key-constraint
On Wed, Nov 11, 2009 at 7:00 PM, Cory Nelson <ph...@gmail.com> wrote:
> Hello,
>
> I'm trying to implement a typical user signup operation: when you
> finally add a user to the database, you want it to have both a unique
> user name, and a unique email. In SQL I'd do something like:
>
> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE
>
> count = SELECT COUNT(*) FROM t_users WHERE name=? OR email=?
>
> if(count == 0)
> {
> INSERT INTO t_users VALUES(...)
> COMMIT
> }
> else
> {
> ROLLBACK
> }
>
> Can anyone give an example of how to achieve the same thing in CouchDB?
>
> --
> Cory Nelson
>