SQLite in-memory option useless without shared cache option

[bitwombat]

I've tried to make this as short and as clear as possible. Please bear with me.

SQLite's in-memory option would make my tests run 33x faster (!) I know because they run that fast with a RAM disk. But, a RAM disk doesn't work within Docker, and I'm trying to be a Big Boy and get my tests to work inside a Docker container.

The problem isn't that the in-memory db isn't persistent - I understand that. The problem is that every SQLite connection to an in-memory database has a different cache. Apparently this means the different connections don't see the same stuff.

The way the DBAL works is that it opens a new connection on every repository request.

SQLite has an option: cache=shared which "allows separate database connections to share the same in-memory database." sqlite docs

This option can only be passed in if the database is opened using a URI filename.

If I subclass DBAL\Driver\PDOSqlite\Driver and make the _constructPdoDSN as follows:

   protected function _constructPdoDsn(array $params)
    {
        $dsn = 'sqlite:';
        if (isset($params['path'])) {
            $dsn .= $params['path'];
        } elseif (isset($params['memory'])) {
            $dsn .= ':memory:?cache=shared';   // <------ THIS
        }

        return $dsn;
    }

Then I get a file on disk called :memory:?cache=shared.

If I make the DSN start with file:, then I get a driver not found error from the DBAL.

Any ideas?

This issue is similar but different to #1518

[bitwombat]

Here's PDO using a shared in-memory database.

// Basic usage
$dsn = 'sqlite::memory:';
$pdo = new PDO($dsn);
$pdo->query('CREATE TABLE t1(first, last)');

$result = $pdo->query('INSERT INTO t1 VALUES ("bit", "wombat")');

var_dump($result);  // PDOStatement object

// Getting a new connection between operations
$dsn = 'sqlite::memory:';
$pdo = new PDO($dsn);
$pdo->query('CREATE TABLE t1(first, last)');

$pdo = new PDO($dsn);  // new connection
$result = $pdo->query('INSERT INTO t1 VALUES ("bit", "wombat")');  // this fails, as expected

var_dump($result);  // false

// Getting a new connection between operations, but shared
$dsn = 'sqlite::memory:?cache=shared';  // Tell SQLite to share
$pdo = new PDO($dsn);
$pdo->query('CREATE TABLE t1(first, last)');

$pdo = new PDO($dsn);  // new connection
$result = $pdo->query('INSERT INTO t1 VALUES ("bit", "wombat")');  // this works

var_dump($result);  // PDOStatement object

[morozov]

@bitwombat could you provide the code you use to instantiate your PDO connection using the DBAL? Additionally, it may help if you look at values of the parameters of parent::__construct() in PDOConnection::__construct() and see if there are any anomalies:

https://github.com/doctrine/dbal/blob/cd3bebc0c366646ae60d645e3f67daf2fddf698d/lib/Doctrine/DBAL/Driver/PDOConnection.php#L43

I cannot see any modifications made to the DSN on the way from the driver to the connection constructor, so it should be passed to the PDO constructor as is.

[bitwombat]

Oops - my example above was "working" because a file named ":memory:?cache=shared" was being opened on disk! So, something in PHP's PDO stuff isn't interpreting that URI.

SQLite3 has a sqlite3_enable_shared_cache function, so I'm currently looking in the PHP source for sqlite3.c to see if it even calls this. Yak shaving!

[bitwombat]

I think this needs to be a documentation issue. I'll close and make a new issue.

[github-actions[bot]]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.