Adding support for the `mode` query parameter in Sqlite (PHP 8.1)

[swiffer]

PHP Sqlite allows setting mode and cache parameters. While adding support for cache was straight forward I struggled a little bit with the mode parameter and the options that are currently possible.

Currently for a sqlite connection you can set name and memory options. If memory has been set to anything name is overwritten to :memory:.

https://github.com/cakephp/phinx/blob/master/tests/Phinx/Config/ConfigTest.php#L344

What is the benefit of having the memory option and not specifying :memory: in the name directly?

PHP 8.1+ adds support for using mode query parameter when creating a connection. Wouldn't it be better to keep the name or use the name specified in memory if not true as name for the database?

https://github.com/cakephp/phinx/blob/master/src/Phinx/Db/Adapter/SQLiteAdapter.php#L159

                if($options['memory'] !== true && PHP_VERSION_ID >= 80100) {
                    $options['name'] = $options['memory'];
                    $params[] = 'mode=memory';
                } else {
                    $options['name'] = static::MEMORY;
                }

[MasterOdin]

Looking at the PHP docs for PDO SQLite, I do not see either of these options mentioned. Could you provide a link to somewhere that talks about them, and what their meaning and values could be?

[othercorey]

It's not part of PDO specifically, they just added support for URI filenames. That is required to use filenames with options when opening the db.

https://www.sqlite.org/sharedcache.html

[MasterOdin]

From the above example, what does sqlite:foo.db?mode=memory do? Is it to allow opening multiple different shared caches for in-memory databases?

[othercorey]

That is creating a unique in-memory db using the name foo.db so you can open and share caches with multiple dbs.

[othercorey]

The more common use case will simply be file::memory:?cache=shared

[MasterOdin]

For cache=shared for a non inline memory DB, do we need to use file: there as well? When should file: be prefixed automatically?

Just want to make sure I fully understand these settings before reviewing #2053.

[othercorey]

So, all URI filenames have to be prefixed with file:. Support for this is disabled by default, but PDO enables it as long as open_basedir isn't configured.

In order to avoid conflicts, cakephp only adds the file: prefix when cache or mode config option is set which is the only time a URI filename is needed for us.

[MasterOdin]

Alright, so through this, we'll need to also document the open_basedir limitation along with cache and mode options.

Implementation idea:

1. If memory and mode are set, throw an error unless mode === "memory"
2. If memory is set, then set mode = "memory"
3. If is_empty(name) && mode === "memory", then unset mode and set name = ":memory:"
4. If name === ":memory:" && mode === "memory", then unset mode
5. If !is_empty(mode) || !is_empty(cache), then prepend file: to name if name is not already prefixed by file:
6. Append mode and cache to name as query parameters

We could then further deprecate memory altogether and have users use either name == ":memory:" or mode === "memory" to achieve current behavior, while being inline with the recommendation for sqlite settings.

This would be a BC break where both name and memory are set as now name would be honored with file:${name}?mode=memory, instead of name being ignored, but I think that shouldn't be too large an issue for users.

[swiffer]

Thanks for your feedback

As the URI scheme is only supported with PHP 8.1+ and usage only is needed when specifing mode or cache (for which users need to modify their config anyway if they opt in) why should we add support for mode + memory.

1. If PHP Version is lower than 8.1 and mode or cache are set - throw an error
2. If mode or cache and memory are set, throw an error
3. If mode or cache are set and PHP greater than or equal 8.1 - use URI scheme
4. Else, keep current behavior

This also makes deprecating memory easier as we don't need to mess around with the conditions again.

What do you think (see updated PR #2053)