I currently work on a project supporting both Oracle and Postgres. One thing that was suggested to me when I arrived was that we continue to leverage foreign keys like they had in the past. I found a "foreign key plugin":http://wiki.rubyonrails.org/rails/pages/Foreign+Key+Schema+Dumper+Plugin on the "rails wiki":http://wiki.rubyonrails.org and had to hack it to support Oracle. This did a decent job of representing our foreign keys and it even allowed us to continue using the :ruby schema format. However you face a big problem with respect to tests and the order that fixtures are loaded into your tests db. Foreign keys are cool and all, but I won't be using them any time soon.
Basically activerecord purges your fixtures in reverse order from how you define them in your tests. For example if your test looks sorta like
1
2
|
class AddressTest < Test::Unit::TestCase
fixtures :parties, :addresses |
You can rest assured that the addresses fixtures will be purged before the parties fixture. Likewise during loading, parties will be first and addresses will be second. For simple tests, just 'ruby test/unit/address_test.rb' the above declaration will work if you include all of the fixtures that are going to be related to the tables you'll be testing again. However between executing tests all of your tests data doesn't seem to be purged and when the time comes for some of the stale data to be purged, you'll get exceptions galore(and failing tests as a result). Here's a little hack I whipped up to get around the foreign key nightmare, I threw it in lib/foreign_key_helper.rb.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
|
class Test::Unit::TestCase
# EWWT
class_inheritable_accessor :foreign_keys
def setup_with_fixtures_and_foreign_key_support
self.foreign_keys ||= ActiveRecord::Base.connection.tables.inject({}) { |sum,tbl| sum[tbl] = ActiveRecord::Base.connection.foreign_key_constraints(tbl); sum }
self.foreign_keys.each do |tbl,fks|
fks.each { |fk| ActiveRecord::Base.connection.remove_foreign_key_constraint(tbl, fk.name) }
end
# puts ActiveRecord::Base.connection.tables.inject({}) { |sum,tbl| sum[tbl] = ActiveRecord::Base.connection.foreign_key_constraints(tbl); sum }.inspect
setup_with_fixtures
# fk's are all going to be wrapped in a transaction, i think... so they're readded on teardown
self.fixture_table_names.each do |tbl|
self.foreign_keys[tbl].each { |fk| ActiveRecord::Base.connection.add_foreign_key_constraint(tbl,fk.foreign_key, fk.reference_table, fk.reference_column, {:on_update => fk.on_update, :on_delete => fk.on_delete}) }
end
# puts ActiveRecord::Base.connection.tables.inject({}) { |sum,tbl| sum[tbl] = ActiveRecord::Base.connection.foreign_key_constraints(tbl); sum }.inspect
end
alias_method :setup_without_fixtures, :setup_with_fixtures_and_foreign_key_support
alias_method :setup, :setup_with_fixtures_and_foreign_key_support
def teardown_with_fixtures_and_foreign_key_support
teardown_with_fixtures
self.foreign_keys.each do |tbl,fks|
fks.each { |fk| ActiveRecord::Base.connection.add_foreign_key_constraint(tbl,fk.foreign_key, fk.reference_table, fk.reference_column, {:on_update => fk.on_update, :on_delete => fk.on_delete}) }
end
end
alias_method :teardown_without_fixtures, :teardown_with_fixtures_and_foreign_key_support
alias_method :teardown, :teardown_with_fixtures_and_foreign_key_support
def self.method_added(method)
case method.to_s
when 'setup'
unless method_defined?(:setup_without_fixtures)
alias_method :setup_without_fixtures, :setup
define_method(:setup) do
setup_with_fixtures_and_foreign_key_support
setup_without_fixtures
end
end
when 'teardown'
unless method_defined?(:teardown_without_fixtures)
alias_method :teardown_without_fixtures, :teardown
define_method(:teardown) do
teardown_without_fixtures
teardown_with_fixtures_and_foreign_key_support
end
end
end
end
end
|
Due to how setup and teardown are implemented in rails it took a lot more code than I'd originally thought it would take. It's pretty simple how it runs
In setup
* Remove all foreign keys on the db, but keep track of them in a hash
* load all of the fixture data(stuff that would barf in certain cases)
* Put foreign keys back in place for test execution, but *ONLY* the tables which are being leveraged in this test
In teardown
* tear down all of the fixture data(fks have been rolled back due to transactional fixtures being used)
* put system-wide foreign key constraints back in place.
By removing foreign keys before the tests and only adding the foreign keys to tables whose fixtures have been loaded a lot of the headaches are avoided. It's fucking slow though. I didn't have a large test dataset, but w/o the foreign keys my unit tests took about 45 seconds to run. With foreign keys it took around 220 seconds to run. The time thing sold me on not using foreign keys in development.
I'm sure there are a number of arguments that can be made for using foreign keys etc etc, but it's just stuff that gets in the way in a framework like rails. Do we test w/ foreign keys in place before milestones(what I'm leaning towards)? Do we test w/o the fks in place(obviously not :])? Maybe somebody can come up with a better way to handle them, but I don't really think you need fks if you're using rails associations correctly. If you are forced to use them though, the above approach might be a good place to start.
May 1st, 2006 at 09:37 AM Foreign keys have saved my ass on several occasions. Having said that, I probably won't use them in future. You're right that if you use associations properly, FKs don't help. Where they're useful is for when you make a mistake. Simply forgetting a :dependent => true on an association can leave your database full of stale data. On the other hand, you're probably better having comprehensive tests for all your associations. That's what I'll be doing from now on, but until I get there, the FKs stay. Here's what I use to cope with having foreign keys in testing (slap it into test_helper.rb). Obviously this is MySQL specific, but maybe there's a similar variable for Oracle and Postgres:
class Fixtures # Oh for alias_method_chain alias :original_delete_existing_fixtures :delete_existing_fixtures alias :original_insert_fixtures :insert_fixtures def delete_existing_fixtures @connection.update "SET FOREIGN_KEY_CHECKS = 0", 'Fixtures deactivate foreign key checks.'; original_delete_existing_fixtures @connection.update "SET FOREIGN_KEY_CHECKS = 1", 'Fixtures activate foreign key checks.'; end def insert_fixtures @connection.update "SET FOREIGN_KEY_CHECKS = 0", 'Fixtures deactivate foreign key checks.'; original_insert_fixtures @connection.update "SET FOREIGN_KEY_CHECKS = 1", 'Fixtures activate foreign key checks.'; end endMay 1st, 2006 at 09:45 AM Oh pimp, I do something like that for delete fixtures w/ certain triggers now, but I have it delegate down to the adapter. hmm.
May 2nd, 2006 at 03:25 PM Yep, if you're using MySQL, it's as simple as wrapping your tear-up/down code with SET FOREIGN_KEY_CHECKS = 0 SET FOREIGN_KEY_CHECKS = 1 Although incase you're of the impression that this is some kind of design flaw in the whole concept of foreign keys, other more fully-featured DBMSes (and maybe later versions of MySQL now) will allow you to specify that checking for certain foreign keys be deferred until the transaction is committed - so it checks referential integrity for the entire transaction taken together, not for each individual insert/delete. This is the more standard and elegant way of dealing with these situations (inserting and deleting multiple rows where you have cyclic references of foreign keys)
May 10th, 2006 at 09:11 PM Yup... http://dev.rubyonrails.org/ticket/2404
May 20th, 2006 at 02:39 AM What about using "on delete cascade" for your foreign keys? At least for Postgres that seems to be a way to make foreign keys co-exist with Rails test fixtures.
March 2nd, 2007 at 08:36 AM
I found that the methodadded method in the snippet above wasn’t correctly intercepting test cases that define their own setup/teardown method. I think it’s something to do with fixtures.rb having already nabbed the name :setupwithoutfixtures, causing the ‘unless methoddefined?(:setupwithoutfixtures)’ to fail every time.
I haven’t yet followed that all the way through to figure out exactly what’s going on, but replacing ‘withoutfixtures’ with ‘withoutforeignkeysupport’ throughout method_added has fixed it for me.