Why not using Sqlite for testing

Counter argument for testing in Sqlite: disadvantages of using it against a real database because of referential integrity.

Very Bad Things
Last week I blogged about using sqlite for testing and talk about the speed gain of this approach. But there'are also bad sides in using only sqlite as a mean to test your application.
Here's a simple list of problems I encountered during my experience in last months of using sqlite databases:

• it's not the production database. It might seem a trivial point, but the other issues all comes out from this one. No matter what database abstraction layer you have, or what powerful Orm you are delegating, the cpu will not execute the same code that the production machine will. Period.
• for instance, sqlite currently does not implement referential integrity. So I set up my forms and run integration tests and saw my domain objects gracefully saved in sqlite, but when I ran my browser on the staging machine exceptions were thrown by Doctrine being unable to save (or delete) objects due to referential integrity. This cause me a lot of headaches to reproduce the bugs in test environment, and if I haven't added additional infrastructure to test on a mysql database, I would never been able to replicate the behaviour and find a fix.
• for the same reason, sqlite silently ignores every constraint in CREATE TABLE queries (or similar Ddl instructions, such as ALTER TABLE). This can cause very bad things happen, like a regeneration script that duplicates constraint of foreign keys that fails in production but not in test: the nightmare of a Tdder.
• moreover, all your code that deletes or updates data flawlessly in test, can fail in production because breaks referential integrity.

So, what's the solution?
I have setup two phing targets (two phpunit bootstrap files will be the same, and are used respectively by my two phing targets) to launch the full test suite against the dummy sqlite database or against a full featured mysql database. This imply that after a bit of changes on classes that use the database, I run the slow test_mysql target and make sure that everything is correct. For what needs a database, the sqlite testing has become a smoke test.
The ideal setup would be a dedicated machine that implements continuos integration. Since it is dedicated it can waste time and cpu cycles to test all the day against every dbms I need to support. As the proverb says, databases are like women: can't live with them, can't live without.
Sqlite is a powerful tool that lets you do amazing things like testing database interaction from a netbook with no connection, but be careful: because, for your information, real database abstraction does not exist. :)