SQLite foreign keys disabled by default & circular schema design #6
Loading…
Reference in a new issue
No description provided.
Delete branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Description
Review of
Database.ktand repository classes:Foreign Key Enforcement:
In Android's
SQLiteOpenHelper, foreign key constraints are disabled by default.Database.kttogglesPRAGMA foreign_keys = ONinonUpgrade, but does not overrideonConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) }. Consequently, none of theFOREIGN KEYconstraints are enforced during regular app usage.Circular Foreign Key Dependency:
RecordreferencesParticipant(wave1_id,wave2_idadded viaALTER TABLEinonCreate).ParticipantreferencesRecord(record_id).Because of this,
RecordRepositoryonly implementsinsert(), whileupdateRecordWaveswas placed intoParticipantRepository.Missing UNIQUE Constraints on City and Dialect:
CityandDialecttables do not haveUNIQUEconstraints on thenamecolumn. AlthoughgetOrInsertCityandgetOrInsertDialectperform a check before inserting, without a unique constraint or input trimming (name.trim()), leading/trailing whitespaces or race conditions will create duplicate cities and dialects.Suggested Fix
override fun onConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) }toDatabase.kt.UNIQUEconstraints toCity(name)andDialect(name)and trim input before querying/inserting.RecordRepository.