# Unit testing and the schema

**URL:** <https://www.ruby-forum.com/t/unit-testing-and-the-schema/49463>\
**Category:** Rails\
**Created:** [December 4, 2005, 11:47pm UTC](https://www.ruby-forum.com/t/unit-testing-and-the-schema/49463 "2005-12-04T23:47:22Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Forrest\_L\_Norvell](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e68b1a/32.png) [@Forrest\_L\_Norvell](https://www.ruby-forum.com/u/Forrest_L_Norvell)\
**Post date:** [December 4, 2005, 11:47pm UTC](https://www.ruby-forum.com/t/unit-testing-and-the-schema/49463/1 "2005-12-04T23:47:22Z")

</div>

Hi! I’m a newbie wrapping my head around the Rails way of designing apps  
at the database layer, and I’ve hit a minor conceptual logjam and was  
hoping some of you might be able to help work me through it.

I’m (re)designing a PHP audio jukebox in RoR (I’m going to be migrating  
a big pile of data from a system called Netjuke, which has been folded  
into the Jinzora project). I have some very definite ideas about how the  
data ought to be structured and how relationships ought to work, and the  
metadata schema I’ve designed generates lots of complicated dependencies  
and relationships which I feel are best specified and enforced at the  
database level. For now I’m working in PostgreSQL, but I might move this  
all over to Oracle at some point.

So my toy version of the app has three classes of entities: artists,  
albums and tracks. Albums and tracks belong to artists (think  
compilations or soundtracks for why that notion of ownership needs to be  
at both levels) and tracks also belong to albums. I’m building the  
schema via migrations, but have slapped in migrations that look like:

class AddConstraints \< ActiveRecord::Migration  
def self.up  
execute ‘ALTER TABLE tracks ADD FOREIGN KEY(album\_id) REFERENCES  
albums ON DELETE RESTRICT’  
execute ‘ALTER TABLE tracks ADD FOREIGN KEY(artist\_id) REFERENCES  
artists ON DELETE RESTRICT’  
execute ‘ALTER TABLE albums ADD FOREIGN KEY(artist\_id) REFERENCES  
artists ON DELETE RESTRICT’  
end

def self.down  
execute ‘ALTER TABLE tracks DROP CONSTRAINT tracks\_artist\_id\_fkey’  
execute ‘ALTER TABLE tracks DROP CONSTRAINT tracks\_album\_id\_fkey’  
execute ‘ALTER TABLE albums DROP CONSTRAINT albums\_artist\_id\_fkey’  
end  
end

My problem is that using fixtures and transactional unit tests, I have  
to incorporate all the fixtures for the classes in the foreign key  
restrictions to ensure that fixtures get set up and torn down correctly.  
I.e. unless I have a line that says “fixtures :artists, :albums,  
:tracks” in test/unit/(album|artist|track)\_test.rb, the test database  
isn’t left in a sane state across unit tests and unit tests start  
failing due to integrity constraint errors when the fixture code tries  
to empty out the table during the fixture setup invoked by the fixtures  
method.

It was a bit of an effort to figure all that out, and my solution feels  
hacky. Why should I have to set up fixtures for tracks and albums when  
I’m only testing artists, who sit at the top of this hierarchy? And  
how’s that going to play when I add in many-to-many relationships  
between groups (artists) and their members? And glom genres onto this?  
It seems like this is going to make my test cases increasingly complex  
and unwieldy.

Is it already time for me to abandon using “fixtures” in my test cases  
and start doing explicit fixture setup and teardown? Or is there a  
simple way I can modify the framework, e.g. to automatically clear out  
all of the fixtures used by an individual test case? I can’t imagine I’m  
the only person who’s run into this.

Thanks in advance to any insight you might be able to provide. RoR is  
totally making it fun for me to work on this project again, and for that  
alone I’m very thankful.

Forrest

---

<div class="post-metadata">

**Author:** ![Forrest\_L\_Norvell](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e68b1a/32.png) [@Forrest\_L\_Norvell](https://www.ruby-forum.com/u/Forrest_L_Norvell)\
**Post date:** [December 5, 2005, 3:34am UTC](https://www.ruby-forum.com/t/unit-testing-and-the-schema/49463/2 "2005-12-05T03:34:46Z")

</div>

Yeah, that’s basically the deal, though it’s not obvious from the  
RDocs. You can pre-populate the database before your tests run and  
use transactional\_fixtures. Transactional fixtures are perhaps  
misnamed, as any database changes made by your tests roll back, but  
the fixtures do NOT. Fixtures are still deleted, inserted and  
committed for each test.

I admit that I didn’t like the approach either – in previous Java  
projects, the team introduced nasty sequence problems in tests when  
we relied on pre-populated data. But after I fiddled with the order  
of my fixtures to account for foreign key constraints, it works fine  
and the transactions keep the test fast.

Scott

On Dec 4, 2005, at 2:47 PM, Forrest L Norvell wrote:

> Hi! I’m a newbie wrapping my head around the Rails way of designing  
> apps  
> at the database layer, and I’ve hit a minor conceptual logjam and was  
> hoping some of you might be able to help work me through it.
> 
> …

---

<div class="post-metadata">

**Author:** ![Forrest\_L\_Norvell](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/e68b1a/32.png) [@Forrest\_L\_Norvell](https://www.ruby-forum.com/u/Forrest_L_Norvell)\
**Post date:** [December 5, 2005, 3:34pm UTC](https://www.ruby-forum.com/t/unit-testing-and-the-schema/49463/3 "2005-12-05T15:34:18Z")

</div>

— Scott W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> committed for each test.
> 
> I admit that I didn’t like the approach either – in  
> previous Java  
> projects, the team introduced nasty sequence  
> problems in tests when  
> we relied on pre-populated data. But after I fiddled  
> with the order  
> of my fixtures to account for foreign key  
> constraints, it works fine  
> and the transactions keep the test fast.

The approach also causes problems if you have triggers  
that disallow deletes from tables. We have data that  
we are required by law to only delete after 7,  
sometimes 20 years. So I created a trigger to prevent  
that from occuring. We ended up rewriting how the  
tests work from rake to building the schema from  
scratch every time. It fortunately only takes about 20  
seconds.

Shawn G.

* * *

Yahoo! DSL ? Something to write home about.  
Just $16.99/mo. or less.  
[dsl.yahoo.com](http://dsl.yahoo.com)