SQLite should properly check if foreign keys are supported and/or omit foreign key statements when a table diff is analyzed

[BigMichi1]

Bug Report

Q	A
Version	3.3.5

Summary

while investigating some differences reported by the doctrine:migration:diff command I figured out that there is every time a diff is reported for tables containing foreign keys because of ManyToOne relations. when inspecting the generated migration file I've seen that the up method contains foreign keys while the down method does not. as far as I could see the code sometimes checks if foreign keys are supported before generating the relevant statements and sometimes not.

as foreign keys are a feature that can be turned on/off for an SQLite database it probably makes sense to replace the hardcoded false in SqlitePlatform in method supportsForeignKeyConstraints with a call to PRAGMA foreign_keys; where the result of 0 means it is disabled so the method should return false while the result 1 should return true.

additionally, the getAlterTableSQL in SqlitePlatform should probably check if foreign keys are supported to generate the correct statements not containing the foreign keys. currently, they are generated regardless if SQLite supports it or not.

Current behaviour

table diff migration generates a migration containing foreign keys for SQLite even if the method supportsForeignKeyConstraints returns every time false.

How to reproduce

1. create two entities with a ManyToOne relation
2. run symfony console doc:mig:diff --from-empty-schema
3. run symfony console doc:mig:mig
4. run symfony console doc:mig:diff

step 4 now generates a diff containing foreign key statements where I actually would expect that nothing is created

Expected behaviour

omit the foreign key statements in the create table statement for a diff
OR
determine if SQLite supports foreign keys so that the check is database feature dependent
OR
add checks if foreign keys are supported in more places which are currently assume they are supported (mainly in the area for the TableDiff handling

[morozov]

In my understanding, SQLite reporting that it doesn't support foreign keys is a workaround for #4243. Somebody needs to figure out how to address the FK introspection issue without introducing a BC break.

[morozov]

This may be addressed in 3.4.x by #5427. @BigMichi1 could you check if the issue is still reproducible?

[BigMichi1]

tried with the latest code on 3.4.x and my diffs are gone

[github-actions]

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.