You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@couchdb.apache.org by Pete Hodgson <pu...@thepete.net> on 2009/11/30 22:21:31 UTC
representing many-to-many relations with concurrent inserts
Hi list,
I'm a newbie with couchDB, so please forgive any transgressions. Also, I'm
cross-posting this question to Stack Overflow,
http://stackoverflow.com/questions/1822444/representing-a-many-to-many-relationship-in-couchdbI
hope that's not considered rude.
Let's say I'm writing a log analysis application. The main domain object
would be a LogEntry. In addition. users of the application define a LogTopic
which describes what log entries they are interested in. As the application
receives log entries it adds them to couchDB, and also checks them against
all the LogTopics in the system to see if they match the criteria in the
topic. If it does then the system should record that the entry matches the
topic. Thus, there is a many-to-many relationship between LogEntries and
LogTopics.
If I were storing this in a RDBMS I would do something like:
CREATE TABLE Entry (
id int,
...
)
CREATE TABLE Topic (
id int,
...
)
CREATE TABLE TopicEntryMap (
entry_id int,
topic_id int
)
Using CouchDB I first tried having just two document types. I'd have a
LogEntry type, looking something like this:
{
'type': 'LogEntry',
'severity': 'DEBUG',
...
}
and I'd have a LogTopic type, looking something like this:
{
'type': 'LogTopic',
'matching_entries': ['log_entry_1','log_entry_12','log_entry_34',....],
...
}
You can see that I represent the relationship by using a
matching_entriesfield in each LogTopic documents to store a list of
LogEntry document ids.
This works fine up to a point, but I have issues when multiple clients are
both attempting to add a matching entry to a topic. Both attempt optimistic
updates, and one fails. The solution I'm using now is to essentially
reproduce the RDBMS approach, and add a third document type, something like:
{
'type':'LogTopicToLogEntryMap',
'topic_id':'topic_12',
'entry_id':'entry_15'
}
This works, and gets past the concurrent update issues, but I have two
reservations:
1. I worry that I'm just using this approach because it's what I'd do in
a relational DB. I wonder if there's a more couchDB-like (relaxful?)
solution.
2. My views can no longer retrieve all the entries for a specific topic
in one call. My previous solution allowed that (if I used the include_docs
parameter).
Anyone have a better solution for me? Would it help if I also posted the
views I'm using?
Cheers,
Pete
Re: representing many-to-many relations with concurrent inserts
Posted by Mark Hammond <sk...@gmail.com>.
On 1/12/2009 8:21 AM, Pete Hodgson wrote:
> This works fine up to a point, but I have issues when multiple clients are
> both attempting to add a matching entry to a topic. Both attempt optimistic
> updates, and one fails.
Depending on the rate of expected conflicts, one solution is to simply
retry a few times.
Mark
Re: representing many-to-many relations with concurrent inserts
Posted by Nathan Stott <nr...@gmail.com>.
You may find this blog post helpful:
http://www.cmlenz.net/archives/2007/10/couchdb-joins
On Mon, Nov 30, 2009 at 3:21 PM, Pete Hodgson <pu...@thepete.net> wrote:
> Hi list,
>
> I'm a newbie with couchDB, so please forgive any transgressions. Also, I'm
> cross-posting this question to Stack Overflow,
>
> http://stackoverflow.com/questions/1822444/representing-a-many-to-many-relationship-in-couchdbI
> hope that's not considered rude.
>
> Let's say I'm writing a log analysis application. The main domain object
> would be a LogEntry. In addition. users of the application define a
> LogTopic
> which describes what log entries they are interested in. As the application
> receives log entries it adds them to couchDB, and also checks them against
> all the LogTopics in the system to see if they match the criteria in the
> topic. If it does then the system should record that the entry matches the
> topic. Thus, there is a many-to-many relationship between LogEntries and
> LogTopics.
>
> If I were storing this in a RDBMS I would do something like:
>
> CREATE TABLE Entry (
> id int,
>
> ...
> )
>
> CREATE TABLE Topic (
>
> id int,
> ...
> )
>
> CREATE TABLE TopicEntryMap (
>
> entry_id int,
> topic_id int
> )
>
> Using CouchDB I first tried having just two document types. I'd have a
> LogEntry type, looking something like this:
>
> {
> 'type': 'LogEntry',
>
> 'severity': 'DEBUG',
> ...
> }
>
> and I'd have a LogTopic type, looking something like this:
>
> {
> 'type': 'LogTopic',
>
> 'matching_entries': ['log_entry_1','log_entry_12','log_entry_34',....],
>
> ...
> }
>
> You can see that I represent the relationship by using a
> matching_entriesfield in each LogTopic documents to store a list of
> LogEntry document ids.
> This works fine up to a point, but I have issues when multiple clients are
> both attempting to add a matching entry to a topic. Both attempt optimistic
> updates, and one fails. The solution I'm using now is to essentially
> reproduce the RDBMS approach, and add a third document type, something
> like:
>
> {
> 'type':'LogTopicToLogEntryMap',
>
> 'topic_id':'topic_12',
> 'entry_id':'entry_15'
> }
>
> This works, and gets past the concurrent update issues, but I have two
> reservations:
>
> 1. I worry that I'm just using this approach because it's what I'd do in
> a relational DB. I wonder if there's a more couchDB-like (relaxful?)
> solution.
> 2. My views can no longer retrieve all the entries for a specific topic
> in one call. My previous solution allowed that (if I used the
> include_docs
> parameter).
>
> Anyone have a better solution for me? Would it help if I also posted the
> views I'm using?
> Cheers,
> Pete
>
Re: representing many-to-many relations with concurrent inserts
Posted by Mark Hammond <sk...@gmail.com>.
On 15/12/2009 6:51 PM, Troy Kruthoff wrote:
> When I first read about this feature I had to take my sql pills to calm
> me down, but then I discovered you do not get the emitting doc as well
> (bummer).
...
> Better yet would be to allow an array: {'_id',['id1','id2']...}
+1 - that is what would make this feature useful in practice.
Cheers,
Mark
Re: representing many-to-many relations with concurrent inserts
Posted by Troy Kruthoff <tk...@gmail.com>.
When I first read about this feature I had to take my sql pills to
calm me down, but then I discovered you do not get the emitting doc as
well (bummer).
For example, accounts and users as separate docs, a view listing users
with include_docs would return the user (emitter) and the account (_id
included in value) doc. But if I set the view value to the '_id' of
the account, I get the account. I know I could store the user in the
view as well, to get both, but that just fattens up the view.
Better yet would be to allow an array: {'_id',['id1','id2']...}
For now, I've decided to just emit multiple values, example:
function(doc) {
if (doc.type=='User') {
emit([doc.email,doc.password],{'_id':doc.account._id});
emit([doc.email,doc.password],null);
}
}
Troy
On Dec 12, 2009, at 9:55 AM, Jan Lehnardt wrote:
> Hi,
>
> I don't think this has been documented yet. It'd be great if you could
> update the wiki :)
>
> Cheers
> Jan
> --
>
> On 1 Dec 2009, at 11:51, christian schilling wrote:
>
>> thats sounds like a feature i have been waiting for. sadly i can't
>> find any
>> documentation about this, am i looking in the wrong places (couchdb
>> wiki) or
>> does is not exist?
>>
>> 2009/12/1 Chris Anderson <jc...@apache.org>
>>
>>> On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net>
>>> wrote:
>>>>> Have you looked at the include_docs=true view query parameter?
>>>>> With
>>>>> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that
>>>>> aren't
>>>>> the one that did the emiting.
>>>>>
>>>>> This technique is used in http://aimpl.org/
>>>>>
>>>>>
>>>>>
>>>> Hi Chris,
>>>>
>>>> Thanks, I have tried include_docs. As I recall, when I did that
>>>> couchDB
>>>> included the /mapping document/, not the log entry document. Does
>>>> that
>>> seem
>>>> feasible? I can go back and confirm the exact results if needed
>>>> (not at
>>> the
>>>> machine in question ATM).
>>>>
>>>
>>> Yes, the ability to link to non-mapping docs is new in trunk.
>>>
>>>
>>> --
>>> Chris Anderson
>>> http://jchrisa.net
>>> http://couch.io
>>>
>
Re: representing many-to-many relations with concurrent inserts
Posted by Alexander Uvarov <al...@gmail.com>.
+1. This sounds like a killer-feature.
On Dec 12, 2009, at 10:55 PM, Jan Lehnardt wrote:
> Hi,
>
> I don't think this has been documented yet. It'd be great if you could
> update the wiki :)
>
> Cheers
> Jan
> --
>
> On 1 Dec 2009, at 11:51, christian schilling wrote:
>
>> thats sounds like a feature i have been waiting for. sadly i can't
>> find any
>> documentation about this, am i looking in the wrong places (couchdb
>> wiki) or
>> does is not exist?
>>
>> 2009/12/1 Chris Anderson <jc...@apache.org>
>>
>>> On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net>
>>> wrote:
>>>>> Have you looked at the include_docs=true view query parameter?
>>>>> With
>>>>> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that
>>>>> aren't
>>>>> the one that did the emiting.
>>>>>
>>>>> This technique is used in http://aimpl.org/
>>>>>
>>>>>
>>>>>
>>>> Hi Chris,
>>>>
>>>> Thanks, I have tried include_docs. As I recall, when I did that
>>>> couchDB
>>>> included the /mapping document/, not the log entry document. Does
>>>> that
>>> seem
>>>> feasible? I can go back and confirm the exact results if needed
>>>> (not at
>>> the
>>>> machine in question ATM).
>>>>
>>>
>>> Yes, the ability to link to non-mapping docs is new in trunk.
>>>
>>>
>>> --
>>> Chris Anderson
>>> http://jchrisa.net
>>> http://couch.io
>>>
>
Re: representing many-to-many relations with concurrent inserts
Posted by Jan Lehnardt <ja...@apache.org>.
Hi,
I don't think this has been documented yet. It'd be great if you could
update the wiki :)
Cheers
Jan
--
On 1 Dec 2009, at 11:51, christian schilling wrote:
> thats sounds like a feature i have been waiting for. sadly i can't find any
> documentation about this, am i looking in the wrong places (couchdb wiki) or
> does is not exist?
>
> 2009/12/1 Chris Anderson <jc...@apache.org>
>
>> On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net> wrote:
>>>> Have you looked at the include_docs=true view query parameter? With
>>>> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
>>>> the one that did the emiting.
>>>>
>>>> This technique is used in http://aimpl.org/
>>>>
>>>>
>>>>
>>> Hi Chris,
>>>
>>> Thanks, I have tried include_docs. As I recall, when I did that couchDB
>>> included the /mapping document/, not the log entry document. Does that
>> seem
>>> feasible? I can go back and confirm the exact results if needed (not at
>> the
>>> machine in question ATM).
>>>
>>
>> Yes, the ability to link to non-mapping docs is new in trunk.
>>
>>
>> --
>> Chris Anderson
>> http://jchrisa.net
>> http://couch.io
>>
Re: representing many-to-many relations with concurrent inserts
Posted by christian schilling <in...@googlemail.com>.
thats sounds like a feature i have been waiting for. sadly i can't find any
documentation about this, am i looking in the wrong places (couchdb wiki) or
does is not exist?
2009/12/1 Chris Anderson <jc...@apache.org>
> On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net> wrote:
> >> Have you looked at the include_docs=true view query parameter? With
> >> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
> >> the one that did the emiting.
> >>
> >> This technique is used in http://aimpl.org/
> >>
> >>
> >>
> > Hi Chris,
> >
> > Thanks, I have tried include_docs. As I recall, when I did that couchDB
> > included the /mapping document/, not the log entry document. Does that
> seem
> > feasible? I can go back and confirm the exact results if needed (not at
> the
> > machine in question ATM).
> >
>
> Yes, the ability to link to non-mapping docs is new in trunk.
>
>
> --
> Chris Anderson
> http://jchrisa.net
> http://couch.io
>
Re: representing many-to-many relations with concurrent inserts
Posted by Pete Hodgson <pu...@thepete.net>.
On Tue, Dec 1, 2009 at 8:32 AM, Chris Anderson <jc...@apache.org> wrote:
> On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net> wrote:
> >> Have you looked at the include_docs=true view query parameter? With
> >> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
> >> the one that did the emiting.
> >>
> >> This technique is used in http://aimpl.org/
> >>
> >>
> >>
> > Hi Chris,
> >
> > Thanks, I have tried include_docs. As I recall, when I did that couchDB
> > included the /mapping document/, not the log entry document. Does that
> seem
> > feasible? I can go back and confirm the exact results if needed (not at
> the
> > machine in question ATM).
> >
>
> Yes, the ability to link to non-mapping docs is new in trunk.
>
>
Ah ok. Thanks a lot of the info. The inability to include_docs was one of my
main reservations with the schema I was using. I guess I'll stick with what
I have then.
Re: representing many-to-many relations with concurrent inserts
Posted by Chris Anderson <jc...@apache.org>.
On Tue, Dec 1, 2009 at 8:30 AM, Pete Hodgson <pu...@thepete.net> wrote:
>> Have you looked at the include_docs=true view query parameter? With
>> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
>> the one that did the emiting.
>>
>> This technique is used in http://aimpl.org/
>>
>>
>>
> Hi Chris,
>
> Thanks, I have tried include_docs. As I recall, when I did that couchDB
> included the /mapping document/, not the log entry document. Does that seem
> feasible? I can go back and confirm the exact results if needed (not at the
> machine in question ATM).
>
Yes, the ability to link to non-mapping docs is new in trunk.
--
Chris Anderson
http://jchrisa.net
http://couch.io
Re: representing many-to-many relations with concurrent inserts
Posted by Pete Hodgson <pu...@thepete.net>.
> Have you looked at the include_docs=true view query parameter? With
> CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
> the one that did the emiting.
>
> This technique is used in http://aimpl.org/
>
>
>
Hi Chris,
Thanks, I have tried include_docs. As I recall, when I did that couchDB
included the /mapping document/, not the log entry document. Does that seem
feasible? I can go back and confirm the exact results if needed (not at the
machine in question ATM).
Re: representing many-to-many relations with concurrent inserts
Posted by Chris Anderson <jc...@apache.org>.
On Mon, Nov 30, 2009 at 3:05 PM, Pete Hodgson <pu...@thepete.net> wrote:
> Roger, please accept my apologies. I'll be sure to not cross-post on this
> list again. Nathan, thanks for the link. That's a very helpful post.
>
> So do I take it from that post that creating a mapping document like I
> described below is the Right Way to deal with concurrent update concerns?
> Also, is there any way to formulate a view into a many-to-many relationship
> which allows include_docs to work correctly? The view I have to retrieve all
> log entries matching a log topic currently looks something like:
>
> function(doc) {
> if( doc['couchrest-type'] != 'LogTopicToLogEntryMap' )
> return;
>
> emit(doc['topic_id'], doc['entry_id']);
> }
>
> This works, but I have to manually pull out the entry_id array in the client
> and then use it to do a second view query to get the actual log entries.
Have you looked at the include_docs=true view query parameter? With
CouchDB 0.11 you can emit {"_id":"foo"} to include docs that aren't
the one that did the emiting.
This technique is used in http://aimpl.org/
> I
> could create a view which contains the log entry document itself, but I'm
> concerned about the amount of data duplication this would cause, given that
> log entries will be the most common document by far in the system.
>
> Cheers,
> Pete
>
--
Chris Anderson
http://jchrisa.net
http://couch.io
Re: representing many-to-many relations with concurrent inserts
Posted by Nathan Stott <nr...@gmail.com>.
Pete,
You need to write a view that returns more than just LogTopicToLogEntryMap.
Have it return the topic and entries too and then you can use include_docs
to get it all in one query.
On Mon, Nov 30, 2009 at 5:05 PM, Pete Hodgson <pu...@thepete.net> wrote:
> Roger, please accept my apologies. I'll be sure to not cross-post on this
> list again. Nathan, thanks for the link. That's a very helpful post.
>
> So do I take it from that post that creating a mapping document like I
> described below is the Right Way to deal with concurrent update concerns?
> Also, is there any way to formulate a view into a many-to-many relationship
> which allows include_docs to work correctly? The view I have to retrieve
> all
> log entries matching a log topic currently looks something like:
>
> function(doc) {
> if( doc['couchrest-type'] != 'LogTopicToLogEntryMap' )
> return;
>
> emit(doc['topic_id'], doc['entry_id']);
> }
>
> This works, but I have to manually pull out the entry_id array in the
> client
> and then use it to do a second view query to get the actual log entries. I
> could create a view which contains the log entry document itself, but I'm
> concerned about the amount of data duplication this would cause, given that
> log entries will be the most common document by far in the system.
>
> Cheers,
> Pete
>
Re: representing many-to-many relations with concurrent inserts
Posted by Pete Hodgson <pu...@thepete.net>.
Roger, please accept my apologies. I'll be sure to not cross-post on this
list again. Nathan, thanks for the link. That's a very helpful post.
So do I take it from that post that creating a mapping document like I
described below is the Right Way to deal with concurrent update concerns?
Also, is there any way to formulate a view into a many-to-many relationship
which allows include_docs to work correctly? The view I have to retrieve all
log entries matching a log topic currently looks something like:
function(doc) {
if( doc['couchrest-type'] != 'LogTopicToLogEntryMap' )
return;
emit(doc['topic_id'], doc['entry_id']);
}
This works, but I have to manually pull out the entry_id array in the client
and then use it to do a second view query to get the actual log entries. I
could create a view which contains the log entry document itself, but I'm
concerned about the amount of data duplication this would cause, given that
log entries will be the most common document by far in the system.
Cheers,
Pete
Re: representing many-to-many relations with concurrent inserts
Posted by Roger Binns <ro...@rogerbinns.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Pete Hodgson wrote:
> Also, I'm
> cross-posting this question to Stack Overflow,
> hope that's not considered rude.
Posting the same question in multiple places is indeed rude! Do you expect
people responding to have to check every location? No one wants to spend
the time posting an answer when someone else has posted the same answer
elsewhere. And then what about followups?
http://www.catb.org/~esr/faqs/smart-questions.html#forum
"Don't shotgun-blast all the available help channels at once, that's like
yelling and irritates people. Step through them softly."
Roger
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iEYEARECAAYFAksUQzUACgkQmOOfHg372QQcdACdEXazNkEpjoHMV/wAexUG+hGg
SHoAni6mob8cBwh08NxTmXfIsu5QVD5Y
=waJ9
-----END PGP SIGNATURE-----