Dbal: [Sqlite] Add option to support PRAGMA for all connections

Created on 11 Oct 2016 · 2Comments · Source: doctrine/dbal

Hi,

I've experience a really bad issue using sqlite compare to mysql due to Sqlite foreign keys.

I'm running my unit test using sqlite, for performance reason, with LiipFunctionalTestBundle.

I'm using unidirectional association with @JoinColumn combined with onDelete="CASCADE". The problem is that when I delete a parent foreign key using sqlite, the onDelete is not executed due to restrictions about sqlite foreign keys.

After some researches, I found in the documentation the answer

Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection.

This is why I would like to add support of predefined PRAGMA (or any query) to execute on each new connection. When I update the Doctrine\DBAL\Driver\PDOSqlite\Driver#connect as the following code it works like a charm.

public function connect(array $params, $username = null, $password = null, array $driverOptions = array())
{
    if (isset($driverOptions['userDefinedFunctions'])) {
        $this->_userDefinedFunctions = array_merge(
            $this->_userDefinedFunctions, $driverOptions['userDefinedFunctions']);
        unset($driverOptions['userDefinedFunctions']);
    }

    try {
        $pdo = new PDOConnection(
            $this->_constructPdoDsn($params),
            $username,
            $password,
            $driverOptions
        );
    } catch (PDOException $ex) {
        throw DBALException::driverException($this, $ex);
    }

    // Force PRAGMA here
    $pdo->exec("PRAGMA foreign_keys = ON;");

    foreach ($this->_userDefinedFunctions as $fn => $data) {
        $pdo->sqliteCreateFunction($fn, $data['callback'], $data['numArgs']);
    }

    return $pdo;
}

So my question is there is any existing way to execute predefined query on each connection ? Or if not do you think this would be a nice option to include ?

Question

Source