On Monday, July 16, 2012 4:32:26 PM UTC-7, mjackson wrote:
>
> I'm using an in-memory SQLite database for testing. Before running each
> test I'd like to clear all records from the database so that it is in a
> pristine state for running my test. However, I'm using several foreign key
> constraints and the sqlite driver throws an error if I try and delete
> records that cause that constraint to be violated. So I need to be very
> careful in the method that clears the database to make sure that I don't
> violate any of these constraints and delete data from referenced tables
> last. As the system grows, this approach becomes a bit unwieldy.
>
> So instead of using that approach I'm exploring alternatives. So far, the
> best solution that I have goes something like this:
>
> module Descendants
> def inherited(subclass)
> super
> subclasses << subclass
> end
>
> def subclasses
> @subclasses ||= []
> end
>
> def descendants
> subclasses.inject(subclasses) do |all, subclass|
> all + subclass.descendants
> end
> end
> end
>
> class Sequel::Model
> extend Descendants
> end
>
> def clear_db(db)
> db.foreign_keys = false
> Sequel::Model.descendants.each do |model|
> db.run("DELETE FROM #{model.table_name}")
> end
> db.foreign_keys = true
> end
>
> db = Sequel.sqlite
> # Create a bunch of records, some that have foreign key constraints...
> clear_db(db)
>
> This works pretty well, and is pretty fast. All data gets dropped from the
> database without complaints and SQLite is able to do a TRUNCATE on the
> tables since we leave out the WHERE clause on the DELETE statement.
>
> Just thought I'd share this with this list and ask if anyone else is
> running into the same problem. If so, what strategy are you using?
>
Your strategy looks good, but if possible I would recommend using
transactions if you can:
DB = Sequel.sqlite
# create tables
DB.transaction(:rollback=>:always) do
# create a bunch of records
end
This should be even faster than deleting, and is generally simpler.
However, if you have code that depends on transactions rolling back, it
won't work properly.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To view this discussion on the web visit
https://groups.google.com/d/msg/sequel-talk/-/6d7nQjdB7TsJ.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sequel-talk?hl=en.
