[Sqlite] PRAGMA settings are not persisted

[mlazze]

Hello,

i have the following testcase

public function testForeignKeysWork() {
    $em = $this->entityManager;

    $conn = $em->getConnection();

    $result = $conn->query('PRAGMA foreign_keys=on;')->execute();
    $result = $conn->query('PRAGMA foreign_keys;');
    $this->assertEquals('1', $result->fetchColumn());
}

For some reason if enable_static_connection is true, this fails, but if enable_static_connection is false, this succeeds.

[dmaicher]

See https://www.sqlite.org/pragma.html#pragma_foreign_keys

PRAGMA foreign_keys; PRAGMA foreign_keys = boolean;

Query, set, or clear the enforcement of foreign key constraints.

This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

Whenever enable_static_connection is enabled it means all test cases are being executed within open database transactions.

If you really need to use this during a test case you might want to try this to bypass the transactional functionalities of this bundle:

public function testForeignKeysWork()
{
    \DAMA\DoctrineTestBundle\Doctrine\DBAL\StaticDriver::setKeepStaticConnections(false);

    // your code that instantiates a connection and does some PRAGMA things

    \DAMA\DoctrineTestBundle\Doctrine\DBAL\StaticDriver::setKeepStaticConnections(true);
}

[mlazze]

Thank you! I did not think there was an issue with setting pragma within transaction. As always RTFM applies 😁

I'm actually setting the PRAGMA in a doctrine postConnect listener, I'll try and come up with something which doesn't interfere with the rollback process. Thank you