SQLite foreign keys disabled by default & circular schema design #6

Open
opened 2026-10-20 19:33:47 +00:00 by gemini · 0 comments
Collaborator

Description

Review of Database.kt and repository classes:

  1. Foreign Key Enforcement:
    In Android's SQLiteOpenHelper, foreign key constraints are disabled by default. Database.kt toggles PRAGMA foreign_keys = ON in onUpgrade, but does not override onConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) }. Consequently, none of the FOREIGN KEY constraints are enforced during regular app usage.

  2. Circular Foreign Key Dependency:

    • Record references Participant (wave1_id, wave2_id added via ALTER TABLE in onCreate).
    • Participant references Record (record_id).
      Because of this, RecordRepository only implements insert(), while updateRecordWaves was placed into ParticipantRepository.
  3. Missing UNIQUE Constraints on City and Dialect:
    City and Dialect tables do not have UNIQUE constraints on the name column. Although getOrInsertCity and getOrInsertDialect perform 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

  • Add override fun onConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) } to Database.kt.
  • Add UNIQUE constraints to City(name) and Dialect(name) and trim input before querying/inserting.
  • Consolidate record updates into RecordRepository.
### Description Review of `Database.kt` and repository classes: 1. **Foreign Key Enforcement**: In Android's `SQLiteOpenHelper`, foreign key constraints are disabled by default. `Database.kt` toggles `PRAGMA foreign_keys = ON` in `onUpgrade`, but does not override `onConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) }`. Consequently, none of the `FOREIGN KEY` constraints are enforced during regular app usage. 2. **Circular Foreign Key Dependency**: - `Record` references `Participant` (`wave1_id`, `wave2_id` added via `ALTER TABLE` in `onCreate`). - `Participant` references `Record` (`record_id`). Because of this, `RecordRepository` only implements `insert()`, while `updateRecordWaves` was placed into `ParticipantRepository`. 3. **Missing UNIQUE Constraints on City and Dialect**: `City` and `Dialect` tables do not have `UNIQUE` constraints on the `name` column. Although `getOrInsertCity` and `getOrInsertDialect` perform 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 - Add `override fun onConfigure(db: SQLiteDatabase) { db.setForeignKeyConstraintsEnabled(true) }` to `Database.kt`. - Add `UNIQUE` constraints to `City(name)` and `Dialect(name)` and trim input before querying/inserting. - Consolidate record updates into `RecordRepository`.
Sign in to join this conversation.
No labels
No milestone
No project
No assignees
1 participant
Notifications
Due date
The due date is invalid or out of range. Please use the format "yyyy-mm-dd".

No due date set.

Dependencies

No dependencies set

Reference
charlie/Tanit#6
No description provided.