Unique constraints in couchdb

user_152326 0
Viewed 9770

Are there any techniques/proposals to enforce unique constraints? Yes, we can create key that's unique, but we cannot change key and keys and also this approach is not suitable for complicated validation (separate unique login, separate unique email, etc...)

For example, an Account should have unique login and email. Deriving a key from this fields will result in inconsistency:

key1: "Account-john@example.net-john", { email: "john@example.net", login: "john"}
key2: "Account-mary@example.net-mary", { email: "mary@example.net", login: "mary"}

Looking good, but:

key1: "Account-john@example.net-mary", { email: "john@example.net", login: "mary"}
key2: "Account-mary@example.net-mary", { email: "mary@example.net", login: "mary"}

Oops, now we have 2 accounts with login: "mary"

couchdb
4 Answers

This is one of the less fun bits of CouchDB. The best way I've found for handling unique fields that can change (like in your user example) is to create "pointer" documents with the unique values as a component of the key, and then use those to let you claim unique values. The important part of doing this is having a predictable key for the primary document, then saving the unique field claim documents prior to saving the primary (and letting key conflicts prevent the primary document from saving).

Given a user with a unique username and unique email, your primary documents might look like this:

user-1234: { username: "kurt", email: "kurt@localhost" }
user-9876: { username: "petunia", email: "pie@crust.com" }

The unique field pointers would look something like this:

user-username-kurt: { primary_doc: "user-1234" }
user-email-kurt@localhost: { primary_doc: "user-1234" }
user-username-petunia: { primary_doc: "user-9876" }
user-email-pie@crust.com: { primary_doc: "user-9876" }

Creating or updating a user would take these steps:

  1. Prep your user document, generate a key for it if necessary
  2. Save a "pointer" document for each changed unique field
  3. If saving any of those fails, stop and fix errors
  4. Save primary user document

Step 3 will take some thought. For instance, you won't want to try claim unique values for fields that haven't changed. You could, but then you'd have to put some additional logic in to handle a case where you're claiming a value for a user that already owns that value.

Step 3 would be a good place to let people take old claimed values as well. If one user has "released" the username kurt, for instance, I can just update that particular document to point to my new key after verifying that it's no longer in use. The alternative is to clear out claimed unique values when they change. I'm not sure which would be less work, really. Leaving stale claimed values in makes the most sense to me.

The interesting thing about this solution is that you don't need to use those pointer documents for anything once they're created. You can create views as normal on your user docs, and use them to query by email or username.

This setup also allows you to do relationships without having to worry about cascading user keys. I don't know about you, but my user documents are referenced by just about every other document in the system. Changing a user key would be a massive pain in the ass.

MrKurt_352960
user_46593660
Related