Thanks for the suggestion Jeremy. Is it a bad idea to use Database#begin_transaction and Database#rollback_transaction independently? Since I'm using this feature for testing, it would be nice to call begin_transaction in my setup and rollback_transaction in my teardown instead of using the Database#transaction method with a block.
-- Michael Jackson @mjackson On Mon, Jul 16, 2012 at 10:25 PM, Jeremy Evans <[email protected]>wrote: > 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. > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. 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.
