CouchDB – How to replace an SQL Auto-Value, Auto-Increment or DB-Sequence with CouchDB Views?
Learning CouchDB for a SQL educated person is sometimes a bit tricky because things are solved so different. So happened to me.
Currently I am trying to create a simple invoicing app based on CouchDB. When creating invoices you might want to have a straight integer sequence to acts as invoice numbers. In a RDBM this is a great job to use an auto increment, auto value or DB sequence.
The CouchDB FAQ says that using sequences to identify records is not necessary because CouchDB has its own way to create document uuids and optimistically postulates that you’ll find a different way for all other DB-sequence use cases as well.
And well, I did :-)
Using an integer attribute “nr” for each invoice containing its number its as simple as creating a map-reduce-maximum-search to simulate a sequence:
View function:
function(doc) {
if (doc.ruby_class = "Invoice") {
emit(null, doc.nr);
}
}
The map function simply emits the invoice’s nr resulting into an array of invoice numbers.
Emit function:
function(keys, values, rereduce) {
var max = 0;
for( i in values ) {
log( "\n\n" + values[i] + "\n\n");
if (values[i] > max) {
max = values[i];
}
}
return max;
}
The corresponding reduce method is also simple as pie. Just take the incoming array and look for and return the largest element. A simple maximum search.
My first idea of incrementing the return value by 1 to not only get the maximum invoice nr but also receive the next invoice number turned out to be a bad idea.
This is because the reduce function can be called multiple times. Those rereduce method calls then fold result values of prio reduce-calls.
Example:
[1,4,2,5,6,3] might be split into [1,4,2] and [5,6,3].
[1,4,2] reduces to 4.
[5,6,3] reduces to 6,
[4,6] then finally reduces to 6.
Since we don’t know when we’re in the last map invocation we pass the job to increment the result of the maximum search to the client.
Using couch_potato in a Ruby app the example looks like this:
# CouchPotato.database.view Invoice.max_nr
view :max_nr, :type => :raw,
:map => "function(doc) { if (doc.ruby_class = \"Invoice\") { emit(null, doc.nr); } }",
:reduce => "function(keys, values, rereduce) { var max = 0; for( i in values ) { if (values[i] > max) { max = values[i]; } } return max; }",
:results_filter => lambda { |result| result["rows"].first["value"].to_i }
Feedback to this article is very welcome: http://www.twitter.com/railshoster
Update 06.12.2010
As discussed with @CouchDB and @martijnthe:
- @martijnthe: @railshoster looking at your article on auto-increment with couchdb. I think it will fail when there are concurrent requests…
- @martijnthe: @railshoster because couchdb will keep on serving “older”, available data while newer data is being inserted.
- @CouchDB: @martijnthe @railshoster usually you wouldn’t want auto-increment with me anyway, what are you using it for?
- @martijnthe: @CouchDB @railshoster Need it as invoice nr. Here the tax police requires sequential nrs. Would definitely vote 4 UUIDs as invoice nr though
- @railshoster: @martijnthe @couchdb There’s no way around sequential invoice nrs in my case. I guess there must be some way to do this with couchdb. Right?
- @martijnthe: @CouchDB @railshoster Need it as invoice nr. Here the tax police requires sequential nrs. Would definitely vote 4 UUIDs as invoice nr though
- @benoitc: @railshoster you can use sequential uuids
- @CouchDB: @railshoster since creating invoice numbers is probably not a concurrent thing, you should be fine.