[LRUG] Enforcing uniqueness

[Matthew Rudy Jacobs]

I had to do this exact thing a couple of years ago.

In postgres you can add a partial unique index

Something like "alter table loans add unique index (client_id) where status
= "active"

On Friday, 11 November 2011, Roland Swingler <roland.swingler at gmail.com>
wrote:
> Could you split Loans into current loans and past loans, and have a
> table for each? Current Loans can then have a unique index on the
> book_id column.
>
> Roland
>
> On Fri, Nov 11, 2011 at 2:22 PM, Andrew Stewart
> <boss at airbladesoftware.com> wrote:
>> Hola El Rug,
>>
>> Let's say I am modelling book libraries in ActiveRecord.  A library has
many books and a book has many loans.  A loan is "current" while the book
is out of the library; and, er, not current when the book is (back) in the
library.
>>
>>    class Library < ActiveRecord::Base
>>      has_many :books
>>    end
>>
>>    class Book < ActiveRecord::Base
>>      belongs_to :library
>>      has_many :loans
>>      has_one  :current_loan, :class_name => 'Loan', :conditions =>
'loans.returned_at is null'
>>    end
>>
>>    class Loan < ActiveRecord::Base
>>      belongs_to :book
>>    end
>>
>> I cannot figure out how to ensure a book can't end up with two current
loans in the database.  I am seeing this at the moment due to, I believe,
double-click form submissions in the GUI.
>>
>> Rails' uniqueness validator is vulnerable to race conditions, as my
database attests.  The usual answer is to apply a unique index in the
database.  But I want an index on the loans table like "unique(book_id)
where returned_at is null" -- which isn't possible, as far as I know.
>>
>> I think there are four layers where this could be tackled:
>>
>> View: add client-side behaviour to prevent duplicate form submissions.
>> => Mitigates the problem but doesn't really solve it.
>>
>> Controller: serialise access to the action where loans are created,
perhaps per library (to reduce contention).
>> => Mitigates the problem but doesn't really solve it.  Also I'm not sure
how to implement.
>>
>> Model: use a validator
>> => This is what I'm already doing.  It's not bullet proof.
>>
>> Database: use an index
>> => I don't think it's possible given my data model.
>>
>> Given all of the above, I think the controller is my best bet.  But I'm
not sure how to do it.
>>
>> Any ideas?
>>
>> Cheers,
>> Andy Stewart
>>
>> -------
>> http://airbladesoftware.com
>> _______________________________________________
>> Chat mailing list
>> Chat at lists.lrug.org
>> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20111111/e4c5690c/attachment.html>