How to enable foreign key checks with SQLite functional tests under Symfony 3.4

Question

I have set up a one-to-many relationship and expect the children to be deleted when the parent is deleted.

I have forgotten to do that and this created a bug in production with MySQL when it refused to remove the parent because children existed.

I wanted to write a functional test on that, but SQLite has foreign key checks disabled by default.

How can I PRAGMA foreign_keys = ON on the connection the controller uses? I have run this on the test side (fixtures), but this is not the same connection, so foreign key checks are still disabled and the deletion of the parent with existing children doesn't fail (it should).

Answer 1

Override the boot function of your bundle, and execute the request there:

public function boot() {
    parent::boot();

    $env = $this->container->getParameter('kernel.environment');
    if ($env !== "test") {
        return;
    }

    /** @var Connection $connection */
    $connection = $this->container->get('doctrine')->getConnection();

    // Get initial state -- should be disabled
    $statement = $connection->prepare("PRAGMA foreign_keys");
    $statement->execute();
    echo __FILE__ . "@" . __LINE__ . "\n";
    print_r($statement->fetchAll());

    // Actually enable the foreign key checks on this connection
    $connection->exec("PRAGMA foreign_keys = ON;");

    // See if it was enabled
    $statement = $connection->prepare("PRAGMA foreign_keys");
    $statement->execute();
    echo __FILE__ . "@" . __LINE__ . "\n";
    print_r($statement->fetchAll());
}

This produces the following:

./src/AppBundle/DependencyInjection/Compiler/DoctrineCompilerPass.php@23
Array
(
    [0] => Array
        (
            [foreign_keys] => 0
        )

)
./src/AppBundle/DependencyInjection/Compiler/DoctrineCompilerPass.php@30
Array
(
    [0] => Array
        (
            [foreign_keys] => 1
        )

)

Now depending on the fixtures you use, you may need to disable the foreign key checks when wiping the database schema:

public function setUp() {
    $manager = $this->getContainer()
        ->get('doctrine')
        ->getManager();
    if (!isset($metadata)) {
        $metadata = $manager->getMetadataFactory()
            ->getAllMetadata();
    }
    $schemaTool = new SchemaTool($manager);
    // Disable the foreign key checks before the database is wiped
    $manager->getConnection()->exec("PRAGMA foreign_keys = OFF");
    $schemaTool->dropDatabase();
    if (!empty($metadata)) {
        $schemaTool->createSchema($metadata);
    }
    // Re-enable the foreign key checks now that it is created
    $manager->getConnection()->exec("PRAGMA foreign_keys = ON");
    $this->postFixtureSetup();

    $this->referenceRepository = $this->loadFixtures(array(ZZZ::class))->getReferenceRepository();
    $this->loadFixtures(array(ZZZ::class));
    $this->client = $this->makeClient();
}