Database Schema (SQLDelight)

Relevant source files

The following files were used as context for generating this wiki page:

• data/src/main/sqldelight/com/vocabri/data/word/Word.sq
• data/src/main/sqldelight/com/vocabri/data/word/Translation.sq
• data/src/main/sqldelight/com/vocabri/data/word/Example.sq
• data/src/main/java/com/vocabri/data/datasource/word/WordLocalDataSourceImpl.kt
• data/src/main/java/com/vocabri/data/di/DataModule.kt
• README.md

The Vocabri application utilizes an offline-first architecture with a local database managed by SQLDelight. This approach ensures data persistence and reactivity, providing a seamless user experience even without a network connection. The database, named vocabri.db, is responsible for storing all user-generated vocabulary, including words, their translations, usage examples, and part-of-speech specific metadata.

SQLDelight generates type-safe Kotlin APIs from SQL statements, which are defined in .sq files located in the data/src/main/sqldelight directory. This ensures compile-time verification of all database queries and migrations, reducing runtime errors. The schema is designed to be extensible, supporting various parts of speech through a base entity and specialized tables.

Sources: README.md, data/src/main/java/com/vocabri/data/di/DataModule.kt:42-46

Database Initialization

The database is instantiated within the Koin dependency injection graph. The dataModule provides a singleton instance of VocabriDatabase. It uses the AndroidSqliteDriver to connect to the local SQLite database file named vocabri.db.

// data/src/main/java/com/vocabri/data/di/DataModule.kt:37-46
@ExcludeFromCoverage
val dataModule = module {
    single {
        VocabriDatabase(
            driver = AndroidSqliteDriver(
                schema = VocabriDatabase.Schema.synchronous(),
                context = androidContext(),
                name = "vocabri.db",
            ),
        )
    }
//...

Sources: data/src/main/java/com/vocabri/data/di/DataModule.kt:37-46

Entity-Relationship Diagram

The following diagram illustrates the relationships between the tables in the vocabri.db schema. A central WordBaseEntity table holds common word data, while specialized tables store part-of-speech specific attributes. TranslationEntity and ExampleEntity are linked via a foreign key to WordBaseEntity.

Table Schemas

The database schema is composed of a base table for words and several related tables for translations, examples, and part-of-speech details.

WordBaseEntity

This is the central table, storing the primary identifier and text for each word.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY, NOT NULL	The unique identifier for the word.
text	TEXT	NOT NULL	The word itself.

Sources: data/src/main/sqldelight/com/vocabri/data/word/Word.sq:24-27

TranslationEntity

Stores one or more translations for a word.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY, NOT NULL	The unique identifier for the translation.
wordId	TEXT	NOT NULL, FOREIGN KEY (WordBaseEntity.id) ON DELETE CASCADE	The ID of the word this translation belongs to.
translation	TEXT	NOT NULL	The translated text.

Sources: data/src/main/sqldelight/com/vocabri/data/word/Translation.sq:24-28

ExampleEntity

Stores one or more usage examples for a word.

Column	Type	Constraints	Description
id	TEXT	PRIMARY KEY, NOT NULL	The unique identifier for the example.
wordId	TEXT	NOT NULL, FOREIGN KEY (WordBaseEntity.id) ON DELETE CASCADE	The ID of the word this example belongs to.
example	TEXT	NOT NULL	The example sentence or phrase.

Sources: data/src/main/sqldelight/com/vocabri/data/word/Example.sq:24-28

Part-of-Speech Tables

These tables store attributes specific to each part of speech. Each has a one-to-one relationship with WordBaseEntity, using the word's id as both a primary and foreign key. The ON DELETE CASCADE constraint ensures that when a WordBaseEntity record is deleted, the corresponding part-of-speech record is also deleted.

Table	Column	Type	Description
NounEntity	gender	TEXT	The grammatical gender of the noun.
	pluralForm	TEXT	The plural form of the noun.
VerbEntity	conjugation	TEXT	Conjugation details.
	management	TEXT	Verb management (e.g., prepositions used).
AdjectiveEntity	comparative	TEXT	The comparative form.
	superlative	TEXT	The superlative form.
AdverbEntity	comparative	TEXT	The comparative form.
	superlative	TEXT	The superlative form.

Sources: data/src/main/sqldelight/com/vocabri/data/word/Word.sq:30-67

Data Access Layer

The WordLocalDataSourceImpl class serves as the primary interface to the database for word-related operations. It uses the type-safe queries generated by SQLDelight to perform CRUD operations.

Sources: data/src/main/java/com/vocabri/data/datasource/word/WordLocalDataSourceImpl.kt

Word Insertion Flow

The insertWord function handles the insertion of a new word and all its related data. It's a multi-step process that writes to several tables.

The following sequence diagram shows the steps involved when insertWord is called.

Word Observation Flow

The observeWordsByPartOfSpeech function provides a reactive Flow of words. It first queries all base words and then, for each word, fetches its related translations, examples, and part-of-speech-specific data. This composition happens in the data source layer, which then assembles the complete Word domain models.

Word Deletion

Deletion is simplified by the ON DELETE CASCADE constraints in the schema. The deleteWord function only needs to delete the record from the WordBaseEntity table. The database automatically handles the deletion of all associated records in TranslationEntity, ExampleEntity, and the specific part-of-speech tables.

// data/src/main/java/com/vocabri/data/datasource/word/WordLocalDataSourceImpl.kt:270-274
override suspend fun deleteWord(wordId: String) {
    log.i { "Deleting word by ID = $wordId" }
    database.wordQueries.deleteWordById(wordId)
    log.i { "Word with ID = $wordId deleted successfully" }
}

The corresponding SQLDelight query is defined as:

-- data/src/main/sqldelight/com/vocabri/data/word/Word.sq:75
deleteWordById:
DELETE FROM WordBaseEntity WHERE id = ?;

Sources: data/src/main/java/com/vocabri/data/datasource/word/WordLocalDataSourceImpl.kt:270-274, data/src/main/sqldelight/com/vocabri/data/word/Word.sq:75

Summary

The database schema for Vocabri is a well-structured system built with SQLDelight, providing a robust foundation for the app's offline-first capabilities. By separating base word data from part-of-speech specifics and related entities like translations and examples, the schema remains clean and extensible. The use of foreign key constraints with ON DELETE CASCADE simplifies data management logic within the application. The data access layer effectively orchestrates queries to construct rich domain models, delivering them reactively to the upper layers of the application.