Rails - Oct 2005 - Fixtures, PostgreSQL, foreign keys, transactionality, oh my!

I''ve just spent a bunch of hours creating fixtures that load
properly into my DB. Not so bad.

Now, when running the tests (default) I''m getting error messages
like this:

ActiveRecord::StatementInvalid: ERROR:  update or delete on "users"  
violates foreign key constraint "users_credit_cards" on
"credit_cards"
DETAIL:  Key (id)=(3) is still referenced from table "credit_cards".
: DELETE FROM users

I imagine this is happening as Rails tries to clear the DB between  
tests.

So, I had read somewhere that transactional fixtures would avoid this,
as the deletes do not happen.

As per Pragmatic AWD w/ R page 177, I added this before the fixtures  
line
in each test:

   self.use_transactional_fixtures = true

And this did not help.

Also, test_helper.rb has these lines:

   # Turn these on to use transactional fixtures with table_name 
(:fixture_name) instantiation of fixtures
   # self.use_transactional_fixtures = true
   # self.use_instantiated_fixtures  = false

I uncommented these, and even tried them in the individual unit tests  
(not very DRY) and I *still* get
these same errors.

It''s late, I''m tired, and I just *know* I''m missing
something
obvious, but I cannot fix this darn thing.

Any help would be appreciated.

-- 
-- Tom Mornini

Tom Mornini wrote:
> I''ve just spent a bunch of hours creating fixtures that load
> properly into my DB. Not so bad.
> 
> Now, when running the tests (default) I''m getting error messages
> like this:
> 
> ActiveRecord::StatementInvalid: ERROR:  update or delete on
"users"
> violates foreign key constraint "users_credit_cards" on
"credit_cards"
> DETAIL:  Key (id)=(3) is still referenced from table
"credit_cards".
> : DELETE FROM users
I had the same trouble with Oracle. I tried two ways of fixing this,
neither of which was perfect, but you might like to try them:

1/
Load and unload every fixture before and after each test, by listing all
your fixtures in test/helper.rb''s class Test::Unit::TestCase -- use an
order which will allow them to be removed cleanly when Rails deletes
them in reverse order. You need to have:

  self.use_transactional_fixtures = true
  self.use_instantiated_fixtures  = false

in test/helper.rb too.

2/
Don''t use fixtures in your tests at all, but arrange a Rake task to
clear down the test database and reload it from another database or from
fixtures, or wherever, before running the tests.

I had trouble getting the latter option to work with the Fixtures class,
and cloning the Dev database to Test worked but isn''t really correct,
so
I went with the former option and waited.



Chris.

On Oct 10, 2005, at 2:09 AM, Chris Andrews wrote:
> Tom Mornini wrote:
>
>> I''ve just spent a bunch of hours creating fixtures that load
>> properly into my DB. Not so bad.
>>
>> Now, when running the tests (default) I''m getting error
messages
>> like this:
>>
>> ActiveRecord::StatementInvalid: ERROR:  update or delete on
"users"
>> violates foreign key constraint "users_credit_cards" on  
>> "credit_cards"
>> DETAIL:  Key (id)=(3) is still referenced from table
"credit_cards".
>> : DELETE FROM users
>>
>
> I had the same trouble with Oracle. I tried two ways of fixing this,
> neither of which was perfect, but you might like to try them:
>
> 1/
> Load and unload every fixture before and after each test, by  
> listing all
> your fixtures in test/helper.rb''s class Test::Unit::TestCase --
use an
> order which will allow them to be removed cleanly when Rails deletes
> them in reverse order. You need to have:
>
>   self.use_transactional_fixtures = true
>   self.use_instantiated_fixtures  = false
>
> in test/helper.rb too.
Thanks, Chris.

Doesn''t transactional fixtures mean it uses DB transactions to restore
state rather than delete/insert?

If so, why are deletes being issued?

-- 
-- Tom Mornini

Tom Mornini wrote:
> 
> Doesn''t transactional fixtures mean it uses DB transactions to
restore
> state rather than delete/insert?
> 
> If so, why are deletes being issued?
> 
As I understand it, it''s using transactions to preserve the state of
the
database between individual tests, but it''ll still try to clear down
the
whole thing at the end of each script.


Chris.

On Monday 10 October 2005 10:46, Tom Mornini wrote:
> I''ve just spent a bunch of hours creating fixtures that load
> properly into my DB. Not so bad.
Then you''re lucky. It won''t work when you have circular
foreign key
dependencies among tables. I have no idea how to overcome this. I just 
circumvent the problem by generating my test database without FK 
constraints.

Michael

-- 
Michael Schuerig                           The more it stays the same,
mailto:michael-q5aiKMLteq4b1SvskN2V4Q@public.gmane.org                       
The less it changes!
http://www.schuerig.de/michael/      --Spinal Tap, The Majesty of Rock

Michael Schuerig wrote:
> On Monday 10 October 2005 10:46, Tom Mornini wrote:
> 
>>I''ve just spent a bunch of hours creating fixtures that load
>>properly into my DB. Not so bad.
> 
> 
> Then you''re lucky. It won''t work when you have circular
foreign key
> dependencies among tables. I have no idea how to overcome this. I just 
> circumvent the problem by generating my test database without FK 
> constraints.
Presumably one could generate and run a ''disable all FKs''
script from
the data dictionary, load fixtures, then run a corresponding ''enable
all
FKs'' script. Perhaps the relevant DB adaptors could be taught to do
this?

I''d be concerned to be testing against a database without the
constraints that are in place on the production database. Despite DHH''s
quite reasonable comments on not repeating model constraints in the
database, I''m not ready to give up using FKs just yet :)


Chris.

Tom Mornini wrote:
> I''ve just spent a bunch of hours creating fixtures that load
> properly into my DB. Not so bad.
> 
> Now, when running the tests (default) I''m getting error messages
> like this:
> 
> ActiveRecord::StatementInvalid: ERROR:  update or delete on
"users"
> violates foreign key constraint "users_credit_cards" on
"credit_cards"
> DETAIL:  Key (id)=(3) is still referenced from table
"credit_cards".
> : DELETE FROM users
> 
> I imagine this is happening as Rails tries to clear the DB between  tests.
> 
> So, I had read somewhere that transactional fixtures would avoid this,
> as the deletes do not happen.
> 
> As per Pragmatic AWD w/ R page 177, I added this before the fixtures  line
> in each test:
> 
>   self.use_transactional_fixtures = true
I added this code to my Test::Unit::TestCase class in test/test_helper.rb class 
to get this working.

   # Delete the fixtures associated with this test suite in the reverse
   # order that they are loaded.  Take care to use the database
   # connection associated with the fixture, not with the model being
   # tested.
   def teardown
     self.class.fixture_table_names.reverse.each do |table_name|
       klass_name = Inflector.classify(table_name.to_s)
       if Object.const_defined?(klass_name)
         klass = Object.const_get(klass_name)
         klass.connection.delete("DELETE FROM #{table_name}",
''Fixture Delete'')
       else
         flunk("Cannot find class for table
''#{table_name}'' to delete fixtures")
       end
     end
   end

Regards,
Blair

On Oct 10, 2005, at 3:50 PM, Blair Zajac wrote:
> Tom Mornini wrote:
>
>> I''ve just spent a bunch of hours creating fixtures that load
>> properly into my DB. Not so bad.
>> Now, when running the tests (default) I''m getting error
messages
>> like this:
>> ActiveRecord::StatementInvalid: ERROR:  update or delete on  
>> "users"  violates foreign key constraint
"users_credit_cards" on
>> "credit_cards"
>> DETAIL:  Key (id)=(3) is still referenced from table
"credit_cards".
>> : DELETE FROM users
>> I imagine this is happening as Rails tries to clear the DB  
>> between  tests.
>> So, I had read somewhere that transactional fixtures would avoid  
>> this,
>> as the deletes do not happen.
>> As per Pragmatic AWD w/ R page 177, I added this before the  
>> fixtures  line
>> in each test:
>>   self.use_transactional_fixtures = true
>>
>
> I added this code to my Test::Unit::TestCase class in test/ 
> test_helper.rb class to get this working.
>
>   # Delete the fixtures associated with this test suite in the reverse
>   # order that they are loaded.  Take care to use the database
>   # connection associated with the fixture, not with the model being
>   # tested.
>   def teardown
>     self.class.fixture_table_names.reverse.each do |table_name|
>       klass_name = Inflector.classify(table_name.to_s)
>       if Object.const_defined?(klass_name)
>         klass = Object.const_get(klass_name)
>         klass.connection.delete("DELETE FROM #{table_name}",  
> ''Fixture Delete'')
>       else
>         flunk("Cannot find class for table
''#{table_name}'' to
> delete fixtures")
>       end
>     end
>   end
Thanks, Blair!

Interestingly, this works great WITHOUT transactional fixtures, but  
fails with.

In a twist, I have several HABTM tables with fixtures, but no models.

Your code flunked them, so I changed it to this:

   def teardown
     self.class.fixture_table_names.reverse.each do |table_name|
       klass_name = Inflector.classify(table_name.to_s)
       if Object.const_defined?(klass_name)
         klass = Object.const_get(klass_name)
         klass.connection.delete("DELETE FROM #{table_name}",  
''Fixture Delete'')
       else
         ActiveRecord::Base.connection.execute("DELETE FROM # 
{table_name}")
       end
     end
   end

and that seems to work fine.

Thanks so much!

-- 
-- Tom Mornini