Movies Database

Entities, DAO and Database

Let's define the Entities, DAO and Database using Room.

Here's our schema

erDiagram
    RATING ||--o{ MOVIE: movies
    MOVIE ||--o{ ROLE : cast
    ROLE }o--|| ACTOR : appearance

What does this mean?

• RATING <-> MOVIE is a one-to-many relationship

  • A RATING can be associated with zero or more MOVIEs
  • A MOVIE has exactly one RATING

• MOVIE <-> ACTOR is a many-to-many relationship

  • An ACTOR can appear in zero or more MOVIEs
  • A MOVIE can cast zero or more ACTORs
  • To implement this, we introduce an associative entity ROLE with a one-to-many relationship on each side
    • A MOVIE can cast zero or more ROLEs
    • An ACTOR can appear in zero or more ROLEs
    • A ROLE can appear in exactly one MOVIE
    • A ROLE is played by exactly one ACTOR

Let's look at the attributes of each entity. All entities are represented as Kotlin data classes.

Naming

There will be a different types of classes used in this application:

• Database entities: the objects that represent rows in a database. We'll use the suffix "Entity" for these.
• Data-layer Plain-Old-Kotlin-Objects (POKOs): helper objects for retrieving data (or partial data) from entities. For example, a POKO may retrieve a Rating with a list of all Movies taggged with that Rating. We won't use a suffix for these.
• Data transfer objects: representations of the data from the data module exposed by the repository module. This hides the fact that the data is coming from a database, as the repository could be returning it from anywhere. We're creating a abstraction that doesn't leak the underlying implementation details to the user-interface layer. We'll use the suffix "Dto" for these.

RatingEntity

Represents an MPAA rating (G, PG, PG-13, R, NR)

Attribute	Type	Key
id	String	PK
name	String
description	String

We define RatingEntity and a POKO to gather a rating and all of its movies at the same time. Note that this POKO represents a ONE-TO-MANY relation.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/RatingEntity.kt

// ...
import java.util.UUID

@Entity
data class RatingEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var name: String,
    var description: String,
)
// ONE-TO-MANY relationship (Rating -> Movies)
// NOTE: THIS IS NOT AN ENTITY!
data class RatingWithMovies(
    @Embedded
    val rating: RatingEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "ratingId",
    )
    val movies: List<MovieEntity>
)

ActorEntity

An actor that can appear in movies

Attribute	Type	Key	Comment
id	String	PK
name	String

We define an ActorEntity and two POKOs to gather an actor and all associated movies at the same time. Note that these POKOs represent a MANY-TO-MANY relation, gathering the association data in the RoleEntity by treating it as a ONE-TO-MANY relation and a MANY-TO-ONE relation. The RoleEntity is the MANY part in the middle.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/ActorEntity.kt

// ...
import java.util.UUID

@Entity
data class ActorEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var name: String,
)
// POKO that groups an Actor with the movies they appear in, and the
//   role played in each movie. This is a ONE-TO-MANY relation from the
//   ActorEntity to the RoleEntity, where we resolve the RoleEntity's movieId
//   to fetch the actual movie (a MANY-TO-ONE relation). The end result is turning
//   a MANY-TO-MANY relation into two ONE-TO-MANY relationships, where the MANY part
//   is the RoleEntity.
data class ActorWithFilmography(
    @Embedded
    val actor: ActorEntity,
    @Relation(
        entity = RoleEntity::class,
        parentColumn = "id",
        entityColumn = "actorId",
    )
    val rolesWithMovies: List<RoleWithMovie>,
)
data class RoleWithMovie(
    @Embedded
    val role: RoleEntity,
    @Relation(
        parentColumn = "movieId",
        entityColumn = "id"
    )
    val movie: MovieEntity,
)

MovieEntity

A movie

Attribute	Type	Key
id	String	PK
title	String
description	String
ratingId	String	FK

We define MovieEntity and two POKOs to gather a movie and all associated actors at the same time. Note that these POKOs represent a MANY-TO-MANY relation, gathering the association data in the RoleEntity by treating it as a ONE-TO-MANY relation and a MANY-TO-ONE relation. The RoleEntity is the MANY part in the middle.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/MovieEntity.kt

// ...
//   Rating key, we update that key in all associated movies.

@Entity(
    indices = [
        Index(value = ["ratingId"])
    ],
    foreignKeys = [
        ForeignKey(
            entity = RatingEntity::class,
            parentColumns = ["id"],
            childColumns = ["ratingId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class MovieEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var title: String,
    var description: String,
    var ratingId: String,
)
// POKO that groups a Movie with the actors that appear in it, and the
//   role played in the movie. This is a ONE-TO-MANY relation from the
//   MovieEntity to the RoleEntity, where we resolve the RoleEntity's actorId
//   to fetch the actual actor (a MANY-TO-ONE relation). The end result is turning
//   a MANY-TO-MANY relation into two ONE-TO-MANY relationships, where the MANY part
//   is the RoleEntity.
data class MovieWithCast(
    @Embedded
    val movie: MovieEntity,
    @Relation(
        entity = RoleEntity::class,
        parentColumn = "id",
        entityColumn = "movieId",
    )
    val rolesWithActors: List<RoleWithActor>,
)
data class RoleWithActor(
    @Embedded
    val role: RoleEntity,
    @Relation(
        parentColumn = "actorId",
        entityColumn = "id"
    )
    val actor: ActorEntity,
)

RoleEntity

Association entity that casts actors into movies

Attribute	Type	Key
movieId	String	FK
actorId	String	FK
character	String
orderInCredits	int

We define RoleEntity to hold the MANY-TO-MANY relation data between MovieEntity and ActorEntity. It holds additional association data describing how the movies and actors are related. Gathering the many-to-many data would be simpler if we weren't interested in the association data. However, to access the association data we'll have to use it at the center to two MANY-TO-ONE relations. See MovieEntity and ActorEntity above for how this works.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/RoleEntity.kt

// ...
//    either parent's key is updated, we update this entity.

@Entity(
    primaryKeys = ["actorId", "movieId"],
    indices = [
        Index("movieId"),
        Index("actorId")
    ],
    foreignKeys = [
        ForeignKey(
            entity = MovieEntity::class,
            parentColumns = ["id"],
            childColumns = ["movieId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = ActorEntity::class,
            parentColumns = ["id"],
            childColumns = ["actorId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class RoleEntity(
    var movieId: String,
    var actorId: String,
    var character: String,
    var orderInCredits: Int,
)

MovieDao

We start with a DAO that has basic create and read operations. We'll make it an abstract class so we can define a concrete implementation for resetDatabase().

All basic query functions immediately return a Flow that we'll collect inside a coroutine. When the data is fetched, it will emit the result to the Flow. If the data changes, the queries will re-execute and emit the new data to the previously-returned Flow.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/MovieDao.kt

// ...
import kotlinx.coroutines.flow.Flow

@Dao
abstract class MovieDao {
    @Query("SELECT * FROM RatingEntity")
    abstract fun getRatingsFlow(): Flow<List<RatingEntity>>
    @Query("SELECT * FROM MovieEntity")
    abstract fun getMoviesFlow(): Flow<List<MovieEntity>>
    @Query("SELECT * FROM ActorEntity")
    abstract fun getActorsFlow(): Flow<List<ActorEntity>>

    @Transaction
    @Query("SELECT * FROM RatingEntity WHERE id = :id")
    abstract suspend fun getRatingWithMovies(id: String): RatingWithMovies
    @Transaction
    @Query("SELECT * FROM ActorEntity WHERE id = :id")
    abstract suspend fun getActorWithFilmography(id: String): ActorWithFilmography
    @Transaction
    @Query("SELECT * FROM MovieEntity WHERE id = :id")
    abstract suspend fun getMovieWithCast(id: String): MovieWithCast

    @Insert
    abstract suspend fun insert(vararg ratings: RatingEntity)
    @Insert
    abstract suspend fun insert(vararg movies: MovieEntity)
    @Insert
    abstract suspend fun insert(vararg actors: ActorEntity)
    @Insert
    abstract suspend fun insert(vararg roles: RoleEntity)
    @Query("DELETE FROM MovieEntity")
    abstract suspend fun clearMovies()
    @Query("DELETE FROM ActorEntity")
    abstract suspend fun clearActors()
    @Query("DELETE FROM RatingEntity")
    abstract suspend fun clearRatings()
    @Query("DELETE FROM RoleEntity")
    abstract suspend fun clearRoles()
    @Transaction
    open suspend fun resetDatabase() {
        clearMovies()
        clearActors()
        clearRoles()
        clearRatings()
        insert(
            RatingEntity(id = "r0", name = "Not Rated", description = "Not yet rated"),
            RatingEntity(id = "r1", name = "G", description = "General Audiences"),
            RatingEntity(id = "r2", name = "PG", description = "Parental Guidance Suggested"),
            RatingEntity(id = "r3", name = "PG-13", description = "Unsuitable for those under 13"),
            RatingEntity(id = "r4", name = "R", description = "Restricted - 17 and older"),
        )
        insert(
            MovieEntity("m1", "The Transporter", "Jason Statham kicks a guy in the face", "r3"),
            MovieEntity("m2", "Transporter 2", "Jason Statham kicks a bunch of guys in the face", "r4"),
            MovieEntity("m3", "Hobbs and Shaw", "Cars, Explosions and Stuff", "r3"),
            MovieEntity("m4", "Jumanji - Welcome to the Jungle", "The Rock smolders", "r3"),
        )
        insert(
            ActorEntity("a1", "Jason Statham"),
            ActorEntity("a2", "The Rock"),
            ActorEntity("a3", "Shu Qi"),
            ActorEntity("a4", "Amber Valletta"),
            ActorEntity("a5", "Kevin Hart"),
        )
        insert(
            RoleEntity("m1", "a1", "Frank Martin", 1),
            RoleEntity("m1", "a3", "Lai", 2),
            RoleEntity("m2", "a1", "Frank Martin", 1),
            RoleEntity("m2", "a4", "Audrey Billings", 2),
            RoleEntity("m3", "a2", "Hobbs", 1),
            RoleEntity("m3", "a1", "Shaw", 2),
            RoleEntity("m4", "a2", "Spencer", 1),
            RoleEntity("m4", "a5", "Fridge", 2),
        )
    }
}

Note that functions getRatingWithMovies(), getActorWithFilmography(), andgetMovieWithCast() are declared as one-shot functions. For now, this is fine, but it will become an issue later when we allow data updates from the UI.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/MovieDao.kt

// ...

@Dao
abstract class MovieDao {
    // ...
    abstract fun getActorsFlow(): Flow<List<ActorEntity>>

    @Transaction
    @Query("SELECT * FROM RatingEntity WHERE id = :id")
    abstract suspend fun getRatingWithMovies(id: String): RatingWithMovies
    @Transaction
    @Query("SELECT * FROM ActorEntity WHERE id = :id")
    abstract suspend fun getActorWithFilmography(id: String): ActorWithFilmography
    @Transaction
    @Query("SELECT * FROM MovieEntity WHERE id = :id")
    abstract suspend fun getMovieWithCast(id: String): MovieWithCast

    @Insert
    // ...
}
// ...

The problem is that because we're not fetching these data using a Flow, there's no way for the database to send us updated data when the data behind the scenes changes. This will lead to stale data on the screen. For example, if we display a movie with its cast, and delete a cast entry, the entry will be removed from the database, but still be visible on the screen unless we do something manual (ewwwww) to update it. Later we'll switch to using Flows for these queries, but for now, I want to demonstrate how you can use a controlled-side effect inside a composable function to fetch data (and demonstrate why this can lead to stale data).

Functions like these that return POKOs that use @Relation will run multiple queries and must be annotated with @Transaction.

Later we'll add update and delete functionality. For now, the only updating we'll do is insert initial data.

MovieDatabase

A very typical Room database declaration.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/MovieDatabase.kt

// ...
import androidx.room.RoomDatabase

@Database(
    version = 1,
    entities = [
        MovieEntity::class,
        ActorEntity::class,
        RoleEntity::class,
        RatingEntity::class,
    ],
    exportSchema = false
)
abstract class MovieDatabase: RoomDatabase() {
    abstract val dao: MovieDao
}

The database will contain Movie, Actor, Role and Rating entities and expose a MovieDao.

DatabaseBuilder.kt

This Kotlin file hosts a single function that we'll use to create an instance of the database.

show in full file data/src/main/java/com/androidbyexample/compose/movies/data/DatabaseBuilder.kt

// ...
import androidx.room.Room

fun createDao(context: Context) =
    Room.databaseBuilder(
        context,
        MovieDatabase::class.java,
        "MOVIES"
    )
// uncomment the following to see the SQL queries that are run
//        .setQueryCallback(
//            { sqlQuery, bindArgs ->
//                Log.d("!!!SQL", "SQL Query: $sqlQuery SQL Args: $bindArgs")
//            }, Executors.newSingleThreadExecutor()
//        )
        .build()
        .dao

Defining this function here, in the data module, avoids the need to make the repository module depend on Room just to create the database instance.

Note that if you want to see the SQL queries that are being run, you can uncomment the setQueryCallback call and look at the Logcat view at the bottom of Android Studio.

---

All code changes

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/ActorEntity.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Embedded
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.Relation
import java.util.UUID

@Entity
data class ActorEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var name: String,
)
// POKO that groups an Actor with the movies they appear in, and the
//   role played in each movie. This is a ONE-TO-MANY relation from the
//   ActorEntity to the RoleEntity, where we resolve the RoleEntity's movieId
//   to fetch the actual movie (a MANY-TO-ONE relation). The end result is turning
//   a MANY-TO-MANY relation into two ONE-TO-MANY relationships, where the MANY part
//   is the RoleEntity.
data class ActorWithFilmography(
    @Embedded
    val actor: ActorEntity,
    @Relation(
        entity = RoleEntity::class,
        parentColumn = "id",
        entityColumn = "actorId",
    )
    val rolesWithMovies: List<RoleWithMovie>,
)
data class RoleWithMovie(
    @Embedded
    val role: RoleEntity,
    @Relation(
        parentColumn = "movieId",
        entityColumn = "id"
    )
    val movie: MovieEntity,
)

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/DatabaseBuilder.kt

package com.androidbyexample.compose.movies.data

import android.content.Context
import androidx.room.Room

fun createDao(context: Context) =
    Room.databaseBuilder(
        context,
        MovieDatabase::class.java,
        "MOVIES"
    )
// uncomment the following to see the SQL queries that are run
//        .setQueryCallback(
//            { sqlQuery, bindArgs ->
//                Log.d("!!!SQL", "SQL Query: $sqlQuery SQL Args: $bindArgs")
//            }, Executors.newSingleThreadExecutor()
//        )
        .build()
        .dao

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/MovieDao.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Dao
import androidx.room.Insert
import androidx.room.Query
import androidx.room.Transaction
import kotlinx.coroutines.flow.Flow

@Dao
abstract class MovieDao {
    @Query("SELECT * FROM RatingEntity")
    abstract fun getRatingsFlow(): Flow<List<RatingEntity>>
    @Query("SELECT * FROM MovieEntity")
    abstract fun getMoviesFlow(): Flow<List<MovieEntity>>
    @Query("SELECT * FROM ActorEntity")
    abstract fun getActorsFlow(): Flow<List<ActorEntity>>

    @Transaction
    @Query("SELECT * FROM RatingEntity WHERE id = :id")
    abstract suspend fun getRatingWithMovies(id: String): RatingWithMovies
    @Transaction
    @Query("SELECT * FROM ActorEntity WHERE id = :id")
    abstract suspend fun getActorWithFilmography(id: String): ActorWithFilmography
    @Transaction
    @Query("SELECT * FROM MovieEntity WHERE id = :id")
    abstract suspend fun getMovieWithCast(id: String): MovieWithCast

    @Insert
    abstract suspend fun insert(vararg ratings: RatingEntity)
    @Insert
    abstract suspend fun insert(vararg movies: MovieEntity)
    @Insert
    abstract suspend fun insert(vararg actors: ActorEntity)
    @Insert
    abstract suspend fun insert(vararg roles: RoleEntity)
    @Query("DELETE FROM MovieEntity")
    abstract suspend fun clearMovies()
    @Query("DELETE FROM ActorEntity")
    abstract suspend fun clearActors()
    @Query("DELETE FROM RatingEntity")
    abstract suspend fun clearRatings()
    @Query("DELETE FROM RoleEntity")
    abstract suspend fun clearRoles()
    @Transaction
    open suspend fun resetDatabase() {
        clearMovies()
        clearActors()
        clearRoles()
        clearRatings()
        insert(
            RatingEntity(id = "r0", name = "Not Rated", description = "Not yet rated"),
            RatingEntity(id = "r1", name = "G", description = "General Audiences"),
            RatingEntity(id = "r2", name = "PG", description = "Parental Guidance Suggested"),
            RatingEntity(id = "r3", name = "PG-13", description = "Unsuitable for those under 13"),
            RatingEntity(id = "r4", name = "R", description = "Restricted - 17 and older"),
        )
        insert(
            MovieEntity("m1", "The Transporter", "Jason Statham kicks a guy in the face", "r3"),
            MovieEntity("m2", "Transporter 2", "Jason Statham kicks a bunch of guys in the face", "r4"),
            MovieEntity("m3", "Hobbs and Shaw", "Cars, Explosions and Stuff", "r3"),
            MovieEntity("m4", "Jumanji - Welcome to the Jungle", "The Rock smolders", "r3"),
        )
        insert(
            ActorEntity("a1", "Jason Statham"),
            ActorEntity("a2", "The Rock"),
            ActorEntity("a3", "Shu Qi"),
            ActorEntity("a4", "Amber Valletta"),
            ActorEntity("a5", "Kevin Hart"),
        )
        insert(
            RoleEntity("m1", "a1", "Frank Martin", 1),
            RoleEntity("m1", "a3", "Lai", 2),
            RoleEntity("m2", "a1", "Frank Martin", 1),
            RoleEntity("m2", "a4", "Audrey Billings", 2),
            RoleEntity("m3", "a2", "Hobbs", 1),
            RoleEntity("m3", "a1", "Shaw", 2),
            RoleEntity("m4", "a2", "Spencer", 1),
            RoleEntity("m4", "a5", "Fridge", 2),
        )
    }
}

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/MovieDatabase.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Database
import androidx.room.RoomDatabase

@Database(
    version = 1,
    entities = [
        MovieEntity::class,
        ActorEntity::class,
        RoleEntity::class,
        RatingEntity::class,
    ],
    exportSchema = false
)
abstract class MovieDatabase: RoomDatabase() {
    abstract val dao: MovieDao
}

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/MovieEntity.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Embedded
import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.Index
import androidx.room.PrimaryKey
import androidx.room.Relation
import java.util.UUID

// Movie Entity - note that we set up a foreign key relationship with RatingEntity,
//   which is considered the owner of movies with that rating. (Probably not the best
//   way to represent this relationship, but I wanted to demonstrate a one-to-many
//   relationship with cascading deletion.
//   If we delete a Rating, we delete all associated movies. If we update the
//   Rating key, we update that key in all associated movies.

@Entity(
    indices = [
        Index(value = ["ratingId"])
    ],
    foreignKeys = [
        ForeignKey(
            entity = RatingEntity::class,
            parentColumns = ["id"],
            childColumns = ["ratingId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class MovieEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var title: String,
    var description: String,
    var ratingId: String,
)
// POKO that groups a Movie with the actors that appear in it, and the
//   role played in the movie. This is a ONE-TO-MANY relation from the
//   MovieEntity to the RoleEntity, where we resolve the RoleEntity's actorId
//   to fetch the actual actor (a MANY-TO-ONE relation). The end result is turning
//   a MANY-TO-MANY relation into two ONE-TO-MANY relationships, where the MANY part
//   is the RoleEntity.
data class MovieWithCast(
    @Embedded
    val movie: MovieEntity,
    @Relation(
        entity = RoleEntity::class,
        parentColumn = "id",
        entityColumn = "movieId",
    )
    val rolesWithActors: List<RoleWithActor>,
)
data class RoleWithActor(
    @Embedded
    val role: RoleEntity,
    @Relation(
        parentColumn = "actorId",
        entityColumn = "id"
    )
    val actor: ActorEntity,
)

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/RatingEntity.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Embedded
import androidx.room.Entity
import androidx.room.PrimaryKey
import androidx.room.Relation
import java.util.UUID

@Entity
data class RatingEntity(
    @PrimaryKey var id: String = UUID.randomUUID().toString(),
    var name: String,
    var description: String,
)
// ONE-TO-MANY relationship (Rating -> Movies)
// NOTE: THIS IS NOT AN ENTITY!
data class RatingWithMovies(
    @Embedded
    val rating: RatingEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "ratingId",
    )
    val movies: List<MovieEntity>
)

ADDED: data/src/main/java/com/androidbyexample/compose/movies/data/RoleEntity.kt

package com.androidbyexample.compose.movies.data

import androidx.room.Entity
import androidx.room.ForeignKey
import androidx.room.Index

// RoleEntity - note that it's considered a child of both MovieEntity and
//    ActorEntity. When either parent is deleted, we delete this entity. If
//    either parent's key is updated, we update this entity.

@Entity(
    primaryKeys = ["actorId", "movieId"],
    indices = [
        Index("movieId"),
        Index("actorId")
    ],
    foreignKeys = [
        ForeignKey(
            entity = MovieEntity::class,
            parentColumns = ["id"],
            childColumns = ["movieId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = ActorEntity::class,
            parentColumns = ["id"],
            childColumns = ["actorId"],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class RoleEntity(
    var movieId: String,
    var actorId: String,
    var character: String,
    var orderInCredits: Int,
)