[LRUG] Enforcing uniqueness

[Jocke Selin]

On Fri, Nov 11, 2011 at 2:46 PM, Matthew Rudy Jacobs
<matthewrudyjacobs at gmail.com> wrote:
> 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"

An index like that is very valuable for data integrity.

If Books has got a flag, is_current, you can act upon that flag.

In pseudo-SQL and pseudo-Ruby:
BEGIN;
SELECT is_current FROM books where id = 123;
unless Book.is_current
  INSERT INTO loans...;
  UPDATE books SET is_current = true;
else
  # Double loan - baaad!
end
COMMIT;

/JS


> 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
>>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
>



-- 
jocke at selincite.com - Jocke Selin - http://jocke.selincite.com