Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

[Sqlite] Add option to support PRAGMA for all connections #2531

Closed
moroine opened this issue Oct 11, 2016 · 2 comments
Closed

[Sqlite] Add option to support PRAGMA for all connections #2531

moroine opened this issue Oct 11, 2016 · 2 comments
Assignees
Labels

Comments

@moroine
Copy link

@moroine moroine commented Oct 11, 2016

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 ?

@kimhemsoe

This comment has been minimized.

Copy link
Member

@kimhemsoe kimhemsoe commented Oct 11, 2016

Events is the tool you are looking for.

You can follow the trail for MysqlSessionInit in the doctrine bundle for an example of this.

@moroine

This comment has been minimized.

Copy link
Author

@moroine moroine commented Oct 11, 2016

Awesome ! Exactly what I was looking for !

Thanks !

@kimhemsoe kimhemsoe self-assigned this Oct 11, 2016
@kimhemsoe kimhemsoe added the Question label Oct 11, 2016
@kimhemsoe kimhemsoe closed this Oct 11, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.