While doing some database testing with Zend_Test_PHPUnit_Db_SimpleTester I encountered a problem with the setups of a table that have one or more relations to it. The problem is that during setup I’m trying to TRUNCATE the table but SQL Server does not allow you to TRUNCATE a table that has relationships (http://msdn.microsoft.com/en-us/library/ms177570.aspx)
I submitted this problem to Zend but they said something about the order in my XML file is wrong and that I have to make sure that the related tables are truncated first… They said:
Zend_Test_PHPUnit_Db expects you to sort the tables in the XML files in an order that allows for a non-conflicting FK truncation. Since it cannot know which is the correct order without doing lots of inference.
If you don’t want that you can add your own Set Up Operation using PHPUnit’s APIs.
Here’s my XML file
So users roles gets truncated first and then users. TRUNCATE works on roles, but then the operation fails when trying to do users, why? Because of the explanation above, SQL Server DOES NOT ALLOW IT. I forgot to mention that in my original report, but left a comment (http://framework.zend.com/issues/browse/ZF-9888) They haven’t responded…
Anywho, so for the work around I did the following:
1) Extended the SimpleTester class. You need to do this because the $setUpOperation is a protected property
connection = $connection;
// Use custom truncate operation.
$this->setUpOperation = new PHPUnit_Extensions_Database_Operation_Composite(array(
$this->tearDownOperation = PHPUnit_Extensions_Database_Operation_Factory::NONE();
2) Extended the Truncate class
// This will wipe out all foreign key relations.
$db->query('DELETE FROM '.$tableName);
// Reset the seed (TRUNCATE would do this)
$db->query('DBCC CHECKIDENT('.$tableName.', RESEED, 0)');
3) Call my simple tester from my controller test
$config = Zend_Registry::get('config')->database;
$db = Zend_Db::factory($config->adapter, array(
'host' => $config->mssql->host,
'username' => $config->mssql->user,
'password' => $config->mssql->password,
'dbname' => $config->params->dbname,
'adapterNamespace' => 'Custom_Zend_Db_Adapter'
$connection = new Zend_Test_PHPUnit_Db_Connection($db, 'users');
// Create your custom tester
$dbTester = new Custom_Zend_Test_PHPUnit_Db_SimpleTester($connection);
new PHPUnit_Extensions_Database_DataSet_FlatXmlDataSet(dirname(__FILE__) . '/xml/user/seed.xml');
// Will not work because setUpOperation is a protected property.
//$dbTester->setUpOperation = new PHPUnit_Extensions_Database_Operation_Composite(array(
// new Custom_Zend_Test_PHPUnit_Db_Operation_Truncate_Mssql(),
// new Zend_Test_PHPUnit_Db_Operation_Insert(),