How to ensure sqlite isn't caching specific select queries?

Question

I'm in the situation that I'm using sqlite with ActiveRecord and Rails (also, this is JRuby and so I'm actually using the jdbcsqlite adapter, in case that matters). Now, I'm trying to insert a row into the table attention_seekers, but only if there is no other existing similar row. Accordingly,

unless AttentionSeeker.find(:first, :conditions => {:key_id => key.id, :locale_id => l.id})
  item = AttentionSeeker.new(:key_id => key.id, :locale_id => l.id)
  item.save
end

This is the generated output in the log:

CACHE (0.0ms)   SELECT * FROM attention_seekers WHERE (attention_seekers.key_id = 318 AND attention_seekers.locale_id = 20) 
AttentionSeeker Create (1.0ms)   INSERT INTO attention_seekers (key_id, locale_id) VALUES(318, 20)
CACHE (0.0ms)   SELECT * FROM attention_seekers WHERE (attention_seekers.key_id = 318 AND attention_seekers.locale_id = 20) 
AttentionSeeker Create (2.0ms)   INSERT INTO attention_seekers (key_id, locale_id) VALUES(318, 20)

As you can see, for some reason the find is being cached, even though I'm inserting elements which affect it. What am I doing wrong/how can I stop this behaviour?

Answer 1

Instead of putting this code in your controller (which is where I'm guessing it is), you may want to consider using a validation instead, which I think would solve the problem:

class AttentionSeeker < ActiveRecord::Base
    validates_uniqueness_of :key_id, :scope => :locale_id
end

Please note the "scope" option in the validation rule.

Failing that you could try wrapping the query in a Transaction

Failing that, and this seems incredibly janky you could add a cachebuster to the query itself. Something like

buster = rand(Time.now)
attention_seeker = AttentionSeeker.find(:first, :conditions => ["#{buster} = #{buster}"])

Which should give you a unique query every time through your loop.

Answer 2

I did some digging and came across this helpful blog post, with more information available here. My solution (using the validation that Mike Buckbee suggested - thanks!):

AttentionSeeker.uncached do
  item = AttentionSeeker.new(:key_id => key.id, :locale_id => l.id)
  item.save
end

Answer 3

Unique indices on the schema-level are safer than validates_uniqueness_of.

See http://railswarts.blogspot.com/2007/11/validatesuniquenessof-is-broken-and.html

Stephan