rails gotchas: nested transactions

So I've been adding some new plugins for testing that I've never tried before, in this case: shoulda and stump, and found that stump just isn't playing nice with SQLite :P

The problem only occurred when I tried to declare a proxy! function on an existing model. ok, I didn't get around to trying everything, so maybe it occurs on other things too, but it seemed to be happy with stub and mock.

The code would simply fail with the following error:

SQLite3::SQLException: cannot start a transaction within a transaction
<insert useless backtrace here>

It's been driving me crazy for the last few days trying all sorts of ways to get it to work. I almost abandoned transactional fixtures entirely before I finally found this snippet at the very *bottom* of the ActiveRecord::Transactions page in APIdock

"Most databases don’t support true nested transactions. At the time of writing, the only database that we’re aware of that supports true nested transactions, is MS-SQL."

So the problem is not transactions... but SQLite's lack of support of proper nested transactions.

The solution: Install MySQL.

It's annoying to have to go back to creating a MySQL db/user and granting rights before I can run my tests - but if it makes the test code work, then it's worth that extra step.

SQLite is nice, but it's ability to blow away the db by just doing a quick rm isn't worth not being able to properly test my code. Besides - the real db is on MySQL anyway, so I figure I might as well.