Today I wanted to test a database-handling class using PHPUnits PHPUnit_Extensions_Database_TestCase and an sqlite in-memory database.
On running the tests I always got the following error-messages:
MyDatabaseTest::testDatabaseConnection with data set #0 ()
PHPUnit_Extensions_Database_Operation_Exception: COMPOSITE[TRUNCATE] operation failed on query:
DELETE FROM "[tablename]"
using args: Array
(
)
[SQLSTATE[HY000]: General error: 1 no such table: [tablename]]
WTF…
After some checking what might help I got different solutions:
- Use MySQL as test-backend – No, I want to use an sqlite with in-memory storage as it’s fast and doesn’t require too much setup on a CI-Server
- Use a pre-configured sqlite-file and therefore a file-based sqlite. That might have been possible, but I just wanted to use in-memory….
The solution was rather simple. It seems that PHPUnit deletes the content of the table in question before setting up the table to execute a test. That only works, if the table already exists. Therefore I’ve added a create table xyz statement right after setting up the in-memory-table. So the test-class now looks like that:
class MyDatabaseTest extends PHPUnit_Extensions_Database_TestCase
{
protected $pdo = null;
/**
* @return PHPUnit_Extensions_Database_DB_IDatabaseConnection
*/
public function getConnection()
{
if (null === $this->pdo) {
$this->pdo = new PDO('sqlite::memory:');
$this->pdo->exec('create table [tablename]([table-definition])');
}
return $this->createDefaultDBConnection($this->pdo, ':memory:');
}
/**
* @return PHPUnit_Extensions_Database_DataSet_IDataSet
*/
public function getDataSet()
{
return $this->createXMLDataSet('[path/to/xml-seed-file]');
}
public function testDatabaseConnection()
{
$pdo = $this->getConnection()->getConnection();
// Do your database-tests here using the required pdo-object
}
}
Perhaps this can help someone to find a solution earlier đŸ˜‰
Thanks for writing this up, the PHPUnit/DBUnit official documentation still doesn’t mention you need to do this. I was hoping with SQLite’s weak type enforcement it would just have created the tables for you based on your dataset.
You should create tables in $this->setUp(), remove – in $this->tearDown()
Still actual for PHPUnit 5.7
https://gist.github.com/jeremyharris/4484784
The thing is that – as far as I remember – the tables needed to exist on the call to
createDefaultDBConnection– which is called before thesetUp-method. Therefore it didn’t work to create the tables in the setup method…