Foreign Key support is broken during migration

[TWiStErRob]

ISSUE_TEMPLATE

DBFlow Version: 3.1.1-5.x

Bug or Feature Request: both?

Description:
Consider this simple database:

@Database(
        name = "Test",
        version = 1,
        foreignKeysSupported = true
)
interface DB

When I open this DB during normal operation, the following gives me 1, i.e. foreign keys are enabled

FlowManager
    .getDatabase(DB.class)
    .getWritableDatabase()
    .rawQuery("PRAGMA foreign_keys;")
    .use { cursor ->
        cursor.moveToFirst()
        println(cursor.getInt(0))
    }

but if I do this in a migration, I get 0:

@Migration(
    database = DB::class,
    version = 2
)
class SomeMigration : BaseMigration() {
    override fun migrate(database: DatabaseWrapper) {
        database
            .rawQuery("PRAGMA foreign_keys;")
            .use { cursor ->
                cursor.moveToFirst()
                println(cursor.getInt(0))
            }
    }
}

the reason?

BaseDatabaseHelper.checkForeignKeySupport gets called during onUpgrade, but doesn't enable foreign keys. That call is a red herring for us to think that foreign key will ever work during onUpgrade.

I can confidently say it'll never work, because of two reasons:
(simplified)

class android.database.sqlite.SQLiteOpenHelper {
    SQLiteDatabase getDatabaseLocked() {
        onConfigure(db);
        db.beginTransaction();
        try {
            if (version == 0) {
                onCreate(db);
            } else {
                if (version > mNewVersion) {
                    onDowngrade(db, version, mNewVersion);
                } else {
                    onUpgrade(db, version, mNewVersion);
                }
            }
            db.setVersion(mNewVersion);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        onOpen(db);
        return db;
    }
}

https://www.sqlite.org/pragma.html#pragma_foreign_keys
This pragma is a no-op within a transaction; foreign key constraint enforcement may only be enabled or disabled when there is no pending BEGIN or SAVEPOINT.

Notice that onUpgrade is within a transaction, and there's no way around not having a transaction there.

The solution? configure the DB connection with foreign keys in onConfigure, before any operations happen. This would remove the need to call checkForeignKeySupport 3 times in BaseDatabaseHelper.

Background: we discovered this, because we wanted to rely on CASCADE during a migration, which simply didn't work, because it needs foreign keys to be enabled in order to do anything.

[agrosner]

its in 5.0.0-alpha2 develop now. good find!