[Database] Support foreign keys with sqlite dialect

[guvra]

Description

Context: SQLite database with the following configuration:

return new SQLiteConfig(
    path: root_path('database.sqlite'),
);

I created an entity with a belongsTo statement:

    public function up(): QueryStatement
    {
        return new CreateTableStatement('xxxxx')
            // ...
            ->belongsTo(...);
    }

When running migrations with vendor/bin/tempest migrate:up, the table is created without foreign keys.

After inspecting the tempest source code, I found out that the sqlite dialect is not compatible with this feature.
In CreateTableStatement:

// Remove BelongsTo for sqlLite as it does not support those queries
->filter(fn (QueryStatement $queryStatement) => ! ($dialect === DatabaseDialect::SQLITE && $queryStatement instanceof BelongsToStatement))

Is it planned to add foreign key support for sqlite?
Foreign keys are implemented in sqlite (https://sqlite.org/foreignkeys.html).

Benefits

Foreign keys are mandatory to ensure data consistency.

[innocenzi]

Sounds like something we should support, yes!

[guvra]

FYI foreign keys are disabled by default in SQLIte (for backwards compatibility), so at the start of each database connection, it is necessary to run PRAGMA foreign_keys = ON;.

It looks like it's already enabled in tempest by the SetForeignKeyChecksStatement class.