db:fixtures:load order

I was trying to deal with foreign key issues related to order of
fixture loading when I came across this:

http://techpolesen.blogspot.com/2007/04/rails-fixture-tips.html

This got me looking deeper into rails and I noticed that
db:fixtures:load calls Fixtures.create_fixtures once for each fixture
file. However, Fixtures.create_fixtures is capable of taking multiple
files and also handle the correct order for both deletes and inserts,
so I created a new rake task that handles deletes/inserts in the
correct order based on ENV['FIXTURES']. I'm just wondering why the
rake task doesn't work this way:

namespace :db do
  namespace :fixtures do
    desc "Load fixtures into the current environment's database. Load
specific fixtures using FIXTURES=x,y"
    task :load => :environment do
      require 'active_record/fixtures'
      ActiveRecord::Base.establish_connection(RAILS_ENV.to_sym)
      fixtures = (ENV['FIXTURES'] ? ENV['FIXTURES'].split(/,/) :
Dir.glob(File.join(RAILS_ROOT, 'test', 'fixtures', '*.{yml,csv}')))
      Fixtures.create_fixtures('test/fixtures', fixtures)
    end
  end
end

You can hack it (and speed up fixture loading somewhat) by monkey
patching the rails code to wrap all the fixture loading in a
transaction. This will probably solve your dependency issue

You can hack it (and speed up fixture loading somewhat) by monkey
patching the rails code to wrap all the fixture loading in a
transaction. This will probably solve your dependency issue

That's basically what I did.

This got me looking deeper into rails and I noticed that
db:fixtures:load calls Fixtures.create_fixtures once for each fixture
file.

In a project that had "circular" foreign key constraints (table A.b_id must refer to B.id, and B.a_id must refer back to A.id), I monkeypatched the create_fixtures to just add them to the array/hash of loaded fixtures, and load them all in in one big transaction when needed (I also used fixture-scenarios). For this postgres install, I also had to set all constraints to deferrable in the test database schema, and then do "set constraints all deferred" in the transaction that loads the fixtures to defer constraint checking until the transaction is committed.

I don't think fixtures scales well enough to bigger projects, they're workable for smaller-ish (in terms of domain) projects, but I've often seem them fall down on bigger projects, esp. when there's a lot of (admittedly bad) inter-dependencies between models.

Right now, the fixture-scenarios plugin is a step in the right direction, but I wouldn't mid joining in in an effort to replace the current Fixture system in rails completely, hell, I wouldn't mind it breaking backwards-incompatibility if thats what it took.

JS

this is all well and good, but I still don't get why db:fixtures:load
passes in one fixture at a time to Fixtures.create_fixtures when it
will work unchanged to pass them all in at once. It actually
simplifies the rake task and fixes foreign key issues as long as they
aren't circular. I believe it's also transparent.

this is all well and good, but I still don't get why db:fixtures:load
passes in one fixture at a time to Fixtures.create_fixtures when it
will work unchanged to pass them all in at once. It actually
simplifies the rake task and fixes foreign key issues as long as they
aren't circular. I believe it's also transparent.

Odds are the person who wrote it initially doesn't use database
constraints. Send a patch in for review and I can't see the harm in
applying it.

Right now, the fixture-scenarios plugin is a step in the right
direction, but I wouldn't mid joining in in an effort to replace the
current Fixture system in rails completely, hell, I wouldn't mind it
breaking backwards-incompatibility if thats what it took.

Increasing the overhead required for editing fixtures never seemed
like a good idea to me. I've always used fixtures as a way to store a
'representative' snapshots of my production system, and made my tests
work accordingly. In this kind of usage the per-testcase fixtures
overhead isn't needed as you can simply load the test database right
at the beginning, and rollback the transaction after each testcase.

The fixtures code is currently ripe for some tidying, but I'm not
necessarily sold that they're more harm than good. The ability to
get a named reference to a particular chunk of a known object graph,
is completely killer.

What I think could be useful is a middle-road between pre-loaded
fixtures and transactional fixtures, so the fixtures are loaded at the
beginning of tests and not reloaded for each testcase which is wasted
work if the fixtures are not specified per-testcase and transactional
fixtures are used.

I'm currently using something like this, I specify all fixtures in
test_helper.rb and I monkeypatched the fixtures loading code so that
it never reloads the same fixtures when transactional fixtures are used.
(@@already_loaded_fixtures in my hack is shared by all test classes)

This gets increasingly more useful as the amount of fixtures grows
and the amount of testcases (TestCase classes) grows. Sure, it may
not be the best idea to use the database for unit tests but I find
it quite comfortable to be able to use the same fixtures for both
development and testing.

I also had to wrap the load_fixtures method into a transaction that
deffers constraints so the fixtures can be loaded in whatever order
without getting FK errors as long as all the FK constraints are
satisfied when all the fixtures are loaded.

Besides that I hacked the transaction code (I'm using the nested
transactions plugin) so that transactions inside a transactional test
work exactly like they do in a normal test. (I'll reopen #5457 for
a discussion on this)

Any comments, suggestions on this? Could some of this get into core?

Tarmo's proposal of having two classes of fixtures (pre-loaded before
all tests, and per-test fixtures) seems very smart. It would also
ease the transition from the current per-test approach.

For performance reasons, wouldn't such an approach require nested
transactions? Not that nested transaction support is a bad thing...

As for dailer's and other's proposal of reworking the guts of fixture
loading into a single transaction -PLEASE! I could really use the
foreign key help that provides.

Actually there is currently pre-loaded fixtures support but if you
use it you lose the benefit of table_name(:fixture_name) accessors.

Basically the pre-loaded fixtures currently mean that rails does
not load anything into the database it runs each test inside a
transaction and then rolls the transaction back after the test has
finished, this way the preloaded data stays the same in the database.

I'm not actually proposing a mixed system, I'd just like a way to
load the fixtures like they are currently loaded but do it once
and only once for all tests.

btw. Nested transactions would not necessarily be required for a mixed
system, but you would have to reload the per-test fixtures after each
transaction rollback.

Yes...but.

The current pre-loaded fixtures support depends on manual database
loading. I was hoping you were proposing a more active role for
Rails: before every test run, load a named set of fixtures. The
selection of those fixtures needs to be in a separate file (something
like test/fixtures.rb or such). Perhaps one file per test type (unit,
functional, integration) would be appropriate.

In any case, such a mechanism would allow one to factor out some of
the common fixtures used across all test scenarios. Hopefully there
would be a performance gain -In my case, many fixtures would be loaded
one time only (and restored by rolling back the DB). And by making
the definition of these global fixtures a ruby script, you could
perhaps open the door to some more programmatic and sophisticated
fixture management scenarios including foreign key management, dynamic
fixtures (beyond ERB) and fixtures copied from production.

You all should check out Tom Werner's excellent fixture_scenarios plugin. It's great for managing disjoint sets of fixture data, and includes the ability to share common fixtures. I'd love to see that functionality in core, but for now it works fine as a plugin.

Tarmo Tänav wrote:

I'm not actually proposing a mixed system, I'd just like a way to
load the fixtures like they are currently loaded but do it once
and only once for all tests.

I seriously agree with Tarmo.

Especially painful if trying to use Rails test fixtures and DB integrity
at the same time. In the end I usually let Rails "win", and let Rails
tests & validations do its jobs rather than trying to RI everything into DB.

It'd be great to be able to specify loading order for the fixtures
globally :slight_smile:

This is especially true because the greatest blocker I've come through
when using DB RI with Rails is when doing 'rake test' (with fixtures).
The app itself work fine with or without RI. Although using RI gives me
some sort of added "safety".... (and... redundancy :slight_smile:

Load order by itself is not always enough, there can be circular
dependencies between tables and even self-referential dependencies
in one table.

In those cases the only way to mass-load the data (other than
manually inserting all the rows in the correct order) is to
disable foreign keys completely until all the data has been
loaded.

This (last I checked) can be done with one simple command in mysql
and with a bit of hacking in postgresql (when creating foreign
key constraints mark them as deferrable and when loading the
fixtures do it inside a transaction and "SET CONSTRAINTS ALL DEFERRED;".

So indeed foreign keys can be a pain to use with Rails but the problem
can be solved without having to maintain a correct loading order
for all the fixtures.

Tarmo Tänav wrote:

Tarmo Tänav wrote:
I'm not actually proposing a mixed system, I'd just like a way to
load the fixtures like they are currently loaded but do it once
and only once for all tests.

I seriously agree with Tarmo.
Especially painful if trying to use Rails test fixtures and DB integrity
at the same time. In the end I usually let Rails "win", and let Rails
tests & validations do its jobs rather than trying to RI everything into DB.
It'd be great to be able to specify loading order for the fixtures
globally :)

Load order by itself is not always enough, there can be circular
dependencies between tables and even self-referential dependencies
in one table.
In those cases the only way to mass-load the data (other than
manually inserting all the rows in the correct order) is to
disable foreign keys completely until all the data has been
loaded.
This (last I checked) can be done with one simple command in mysql
and with a bit of hacking in postgresql (when creating foreign
key constraints mark them as deferrable and when loading the
fixtures do it inside a transaction and "SET CONSTRAINTS ALL DEFERRED;".
So indeed foreign keys can be a pain to use with Rails but the problem
can be solved without having to maintain a correct loading order
for all the fixtures.

Thank you so much Tarmo.

Yep I’m using PostgreSQL mostly but I’m not aware of that specific
PostgreSQL feature (or trick)

Thank you very much, it surely helps me a lot. :slight_smile:

So, did you submit this as a patch? It seems to solve my issue and it
solves a lot of other issues as well.

Haven't submitted a patch for the fixture thing but I have for
nested transactions. I'll provide the hotpatch but I don't
know how it would behave without my nested transactions patch.

This should go into config/initializers/
so it's like a plugin but only the ruby code.
http://tarmo.itech.ee/fixtures_improvement.rb

If you're using the arnesttransacts [1] plugin then you should
probably overwrite it's lib/nested_transactions.rb file with this:
http://tarmo.itech.ee/nested_transactions.rb

If you're interested I've also changed the redhillonrails_core plugin
so all foreign keys created by it are deferrable by default, to make
this happen you just have to add 'sql << " DEFERRABLE"' to the
to_sql method in the plugins foreign_key_definition.rb file.

Oh, and for specifying the fixtures, it's enough to just do it in
test_helper.rb in whatever order. I'm not sure what happens exactly
if some tests add some fixtures that test_helper.rb does not.

As a warning, one minor downside with this approach is that if the
fixtures contain FK violations then the tests are still run but all
of them fail with an error and the error is not shown until all
testcases are ran.

[1] http://rubyforge.org/projects/arnesttransacts/

Tarmo Tänav wrote:

Tarmo Tänav wrote:
I'm not actually proposing a mixed system, I'd just like a way to
load the fixtures like they are currently loaded but do it once
and only once for all tests.

I seriously agree with Tarmo.
Especially painful if trying to use Rails test fixtures and DB integrity
at the same time. In the end I usually let Rails "win", and let Rails
tests & validations do its jobs rather than trying to RI everything into DB.
It'd be great to be able to specify loading order for the fixtures
globally :)

Load order by itself is not always enough, there can be circular
dependencies between tables and even self-referential dependencies
in one table.
In those cases the only way to mass-load the data (other than
manually inserting all the rows in the correct order) is to
disable foreign keys completely until all the data has been
loaded.
This (last I checked) can be done with one simple command in mysql
and with a bit of hacking in postgresql (when creating foreign
key constraints mark them as deferrable and when loading the
fixtures do it inside a transaction and "SET CONSTRAINTS ALL DEFERRED;".
So indeed foreign keys can be a pain to use with Rails but the problem
can be solved without having to maintain a correct loading order
for all the fixtures.

Do you know any plugin which provide this behavior (i.e. disable
constraints in Mysql/postgres/etc before fixture load, and enable them
after fixture load successful, for every test case?)

Or you have one?

It’d be deadly useful!!!

thanks

(BTW I posted your solution on Ruby Indonesia Wiki at
, with credits of course. once we have enough content we’re planning to
release these as a community [CC] licensed book )

Do you know any plugin which provide this behavior (i.e. disable
constraints in Mysql/postgres/etc before fixture load, and enable them
after fixture load successful, for every test case?)

Or you have one?

It'd be deadly useful!!!

As I said in the other mail, I currently only have a hotpatch:
http://groups.google.com/group/rubyonrails-core/tree/browse_frm/thread/e010a554e3eb7971/b3af060589805d20?rnum=11&hl=id&_done=%2Fgroup%2Frubyonrails-core%2Fbrowse_frm%2Fthread%2Fe010a554e3eb7971%3Fhl%3Did%26#doc_15fa682aa603d5e5

I am planning to make this into a plugin but haven't done so yet.

thanks

(BTW I posted your solution on Ruby Indonesia Wiki at
http://wiki.ruby-id.web.id/wiki/Rails_test_fixtures_dengan_referential_integrity , with credits of course. once we have enough content we're planning to release these as a community [CC] licensed book )

Thanks

Sorry for the confusion. I wasn't asking about your patch. I was
asking about the original question posted by dailer. His changes,
seems to me, makes most (all) of this other discussion pointless.
Just add all your records in one transaction and be done with it -- at
least with my current constraints in PostgreSQL, it fixes my problem
(and I have a circular set of constraints).

Making the constraints deferred only works for foreign key constraints
(and triggers -- I think?).