Error when using phpunit/dbunit with SQL Server

Question

I am using phpunit/dbunit to test sql server database, but it shows the following error

1) Tests\Integration\BlocksDaoIntegrationTest::test_insert_block PHPUnit_Extensions_Database_Operation_Exception: COMPOSITE[TRUNCATE] operation failed on query: TRUNCATE TABLE DIGITAL_DOCUMENTS_IMAGES using args: Array ( ) [SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot truncate table 'DIGITAL_DOCUMENTS_IMAGES' because it is being referenced by a FOREIGN KEY constraint.]

Could someone please help me to solve it? Thank you
(sorry for the english I'm Brazilian)

Answer 1

I have solved the problem extending the class PHPUnit_Extensions_Database_Operation_Truncate and then returning it:

The operation class:

    class SQLServerTruncateOperation extends \PHPUnit_Extensions_Database_Operation_Truncate {

    private $reseed = "DECLARE @sql NVARCHAR(MAX);

        SET @sql = N'SET NOCOUNT ON;';

        ;WITH s(t) AS
         (
           SELECT 
             QUOTENAME(OBJECT_SCHEMA_NAME(referenced_object_id)) 
             + '.' + QUOTENAME(OBJECT_NAME(referenced_object_id))
          FROM sys.foreign_keys AS k
          WHERE EXISTS 
          (
            SELECT 1 FROM sys.identity_columns 
            WHERE [object_id] = k.referenced_object_id
          )
          GROUP BY referenced_object_id
        )
        SELECT @sql = @sql + N'DBCC CHECKIDENT(''' + t + ''', RESEED, 0) WITH NO_INFOMSGS;'
        FROM s;
        EXEC sp_executesql @sql;";

    public function execute(\PHPUnit_Extensions_Database_DB_IDatabaseConnection $connection, \PHPUnit_Extensions_Database_DataSet_IDataSet $dataSet) {
        $connection->getConnection()->query('EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"');
        $connection->getConnection()->query('EXEC sp_MSForEachTable "DELETE FROM ?"');
        $connection->getConnection()->query($this->reseed);
        $connection->getConnection()->query('EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"');

    }
}

My test base class:

 abstract class DBUnitAbstractTestCase extends \PHPUnit_Extensions_Database_TestCase{

    static private $pdo = null;

    private $conn = null;

    protected function getConnection() {
        if ($this->conn === null) {
            if (self::$pdo == null) {
                self::$pdo = new \PDO("sqlsrv:Server=server;Database=db", "user", 'password');
            }
            $this->conn = $this->createDefaultDBConnection(self::$pdo, 'database');
        }

        return $this->conn;
    }

    public function getSetUpOperation() {
        $cascadeTruncates = TRUE; 
        return new \PHPUnit_Extensions_Database_Operation_Composite(array(
            new SQLServerTruncateOperation($cascadeTruncates),
            \PHPUnit_Extensions_Database_Operation_Factory::INSERT()
        ));
    }
}

Answer 2

The problem is you have foreign keys in the table DIGITAL_DOCUMENTS_IMAGES.
Try replacing the command

TRUNCATE TABLE DIGITAL_DOCUMENTS_IMAGES

with

DELETE FROM DIGITAL_DOCUMENTS_IMAGES

and you will get the same result Maybe it can take some additional time but at the end you will have you table empty.

There are some restrictions when you use TRUNCATE`. You cannot use TRUNCATE TABLE on tables that:

• Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

• Participate in an indexed view.

• Are published by using transactional replication or merge replication.

Here you have additional information about the difference between TRUNCATE and DELETE

Hope this helps.