Enable foreign keys with LazyDatabase.

[bartvanhoutte]

Small support request/possible feature request. I would like to use foreign keys in my SQLite database and according to the SQLite documentation I have to enable this per connection via PRAGMA foreign_keys = ON.
Is there a way to execute this command everytime LazyDatabase creates a connection to the database?

[bartvanhoutte]

I've currently worked around the issue by enabling FKs every time when selecting/updating/deleting, but this obviously isn't ideal ...

[clue]

@bartvanhoutte Thanks for reporting!

This is indeed not currently supported and your feature request makes perfect sense.

It's my understanding we should probably provide some API to pass arbitrary commands that are executed once the underlying connection is ready or do you think it makes more sense to provide some predefined ones?

[bartvanhoutte]

Looking at the amount of options available to be configured this way I think it makes more sense to somehow provide an array of queries to be executed when a connection is made.

Something like this would suffice I guess:

$options['onConnect'][] = 'PRAGMA foreign_keys = on';
$options['onConnect'][] = 'PRAGMA threads = 4';
$db = (new DatabaseFactory($loop))->openLazy($path, $flags, $options);

What do you think?

[clue]

Here's my concept for a future SqliteClient API as discussed in #47:

$db = new Clue\React\Sqlite\SqliteClient('users.db');

$db->on('open', fn () => $db->exec('PRAGMA foreign_keys = on'));

$db->query('SELECT 1')->then(fn (QueryResult $result) => var_dump($result));

The idea is to fire the open event immediately after the database is ready to accept commands, right before the query passed to query() would actually be executed.

What do you think about this?

[Stevemoretz]

Sounds good to me!

[bartvanhoutte]

@clue excellent.