Hello, I''m working on developing an application using Rails, and I would like to use InnoDB tables in MySQL to enforce refferential integrity on the DB level (since the DB may get used by other apps, and I want to make sure none of them can put it in an unstable state). The last release of Rails fixed a problem with running rake by turning off foreign key checking while copying the production database to the test database, but I''m still having a related problem. When the unit tests are executed and they run the create_fixtures code, foreign key checks are not disabled, and I''m getting a lot of key errors due to the order that the fixtures are created. Is there a way to turn off foreign key checking during the call to create_fixtures? Also, I''d appreciate any advice on the best way to handle foreign key errors in the model classes. Is there any good way to keep from having to duplicate the checking in the model itself? I.e, If I have a User model and a Comment model, and I want to prevent a User from being destroyed if there are any associated Comments, and I have a foreign key defined on the comments table as "FOREIGN KEY comments_user_id_fk (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE RESTRICT", is there anyway to simply let the User class attempt the deletion and then provide a meaningful error message only in the case that the command fails on the MySQL side, or am I stuck with first checking that the User object has no associated Comment objects within the User class itself? If I do have to do the checking in the model class, is it possible to have the before_destroy callback method abort the call to destroy, or do I have to override the destroy method directly? Answers to those questions and any other advice on Rails with InnoDB (or other databases that support refferential integrity checking) are greatly appreciated. -- Regards, John Wilger ----------- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don''t know," Alice answered. "Then," said the cat, "it doesn''t matter." - Lewis Carrol, Alice in Wonderland
John Wilger said:> [...] When the unit > tests are executed and they run the create_fixtures code, foreign key > checks are not disabled, and I''m getting a lot of key errors due to > the order that the fixtures are created. Is there a way to turn off > foreign key checking during the call to create_fixtures?I know that postgresql can be told to defer foreign key constraints until the end of transaction. So within a transaction, you can enter the data in any order as long as by commit time everything is consistent. The options to enable this are "DEFERRABLE" and "INITIALLY DEFERRED" (see http://www.faqs.org/docs/ppbook/x13546.htm for details). There may be something similar for mySql. Than all you have do to is make sure the fixtures are created in a single transaction. -- -- Jim Weirich jim-Fxty1mrVU9GlFc2d6oM/ew@public.gmane.org http://onestepback.org ----------------------------------------------------------------- "Beware of bugs in the above code; I have only proved it correct, not tried it." -- Donald Knuth (in a memo to Peter van Emde Boas)
On Mon, 1 Nov 2004 20:29:13 -0000 (UTC), Jim Weirich <jim-Fxty1mrVU9GlFc2d6oM/ew@public.gmane.org> wrote:> > Than all you have do to is make sure the fixtures are created in a single > transaction.While I understand that that is what needs to happen, I''m just not sure how to go about doing so. Is there an option to the create_fixtures method that would enable this? If not, it seems like there should be. It looks like you can send a block to the Fixtures::create_fixtures method that defines a connection to use (which defaults to ActiveRecord::Base.connection if no block is supplied). Does the answer lie in there? -- Regards, John Wilger ----------- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don''t know," Alice answered. "Then," said the cat, "it doesn''t matter." - Lewis Carrol, Alice in Wonderland
Well, I did a little poking around and seem to have solved the problem
with the fixtures. I modified the create_fixtures method in
test_helper.rb to read:
def create_fixtures(*table_names)
ActiveRecord::Base.connection.begin_db_transaction
ActiveRecord::Base.connection.execute("SET foreign_key_checks = 0")
fixtures = Fixtures.create_fixtures(File.dirname(__FILE__) +
"/fixtures", table_names)
ActiveRecord::Base.connection.commit_db_transaction
ActiveRecord::Base.connection.execute("SET foreign_key_checks = 1")
return fixtures
end
This put the whole thing inside one transaction and turns off the
foreign key checking. Unless anybody can see how this would cause a
problem, I guess that''s what I''ll go with.
On Mon, 1 Nov 2004 15:54:16 -0500, John Wilger
<johnwilger-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> On Mon, 1 Nov 2004 20:29:13 -0000 (UTC), Jim Weirich
> <jim-Fxty1mrVU9GlFc2d6oM/ew@public.gmane.org> wrote:
>
> >
> > Than all you have do to is make sure the fixtures are created in a
single
> > transaction.
>
> While I understand that that is what needs to happen, I''m just not
> sure how to go about doing so. Is there an option to the
> create_fixtures method that would enable this? If not, it seems like
> there should be.
>
> It looks like you can send a block to the Fixtures::create_fixtures
> method that defines a connection to use (which defaults to
> ActiveRecord::Base.connection if no block is supplied). Does the
> answer lie in there?
--
Regards,
John Wilger
-----------
Alice came to a fork in the road. "Which road do I take?" she asked.
"Where do you want to go?" responded the Cheshire cat.
"I don''t know," Alice answered.
"Then," said the cat, "it doesn''t matter."
- Lewis Carrol, Alice in Wonderland