Entitat

We will start to explore the rest of the configuration for entities and DAO

• Introducció
• Primary Keys
• Indexes
• Custom Column
• Name

Introducció

The only absolute requirements for a Room entity class is that it be annotated with the @Entity annotation and have a field identified as the primary key, typically by way of a @PrimaryKey annotation. Anything above and beyond that is optional.

However, there is a fair bit that is “above and beyond that”. Some — though probably not all — of these features will be of interest in larger apps.

Crea un projecte room-entity.

Primary Keys

If you have a single field that is the primary key for your entity, using the @PrimaryKey annotation is simple and helps you clearly identify that primary key at a later point.

However, you do have some other options.

• Universally Unique IDs as a Primary Key in a Room Database

Auto-Generated Primary Keys

In SQLite, if you have an INTEGER column identified as the PRIMARY KEY, you can optionally have SQLite assign unique values for that column, by way of the AUTOINCREMENT keyword.

In Room, if you have a Long property that is your @PrimaryKey, you can optionally apply AUTOINCREMENT to the corresponding column by adding autoGenerate=true to the annotation:

import androidx.room.*

@Database(entities = [AutoGenerate::class], version = 1)
abstract class NoteDatabase : RoomDatabase() {
    abstract fun autoGenerate(): AutoGenerate.SQL
}


@Entity(tableName = "AutoGenerate")
data class AutoGenerate(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    val text: String,
) {
    @Dao
    interface SQL {
        @Query("SELECT * FROM AutoGenerate")
        suspend fun select(): List<AutoGenerate>

        @Query("SELECT * FROM AutoGenerate WHERE id = :id")
        suspend fun selectWhereId(id: Long): AutoGenerate?

        @Insert
        suspend fun insert(autoGenerate: AutoGenerate): Long
    }
}

By default, autoGenerate is false. Setting that property to true gives you AUTOINCREMENT in the generated CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS AutoGenerate (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, text TEXT NOT NULL)

Insert methods treat 0 (or null) as not-set while inserting the item.

You do not know your primary key until you insert the entity into a database. Your @Insert-annotated functions can return a Long result, and that will be the primary key for that inserted entity.

class DatabaseTest {

    val db = Room.inMemoryDatabaseBuilder<DaoDatabase>().setDriver(BundledSQLiteDriver()).build()

    @Test
    fun autoGenerate() = runTest {

        db.autoGenerate().select() shouldBe emptyList()

        val id = db.autoGenerate().insert(AutoGenerate(text = "Hello World"))
        id shouldBe 1L

        db.autoGenerate().selectWhereId(1L)!!.let{
            it.id.shouldBe(1L);
            it.text shouldBe "Hello World"
        }
    }
}

UUIDs as Primary Keys

While UUIDs these take up much more room than a simple Long, they can be uniquely generated outside of the database.

Universally Unique Identifiers are 128-bit numbers which are often used as database or session IDs. The great thing about them is that they can be generated independently anywhere, with an extremely low chance of generating the same ID twice.

This means you can create these IDs and assign them to things on the client side on many different devices, without having to constantly synchronise with a server that would tell you what the next ID should be.

Modifica la secció settings del fitxer module.yaml:

settings:
  kotlin:
    ksp:
      processors:
        - androidx.room:room-compiler:2.8.0
    optIns: [ kotlin.uuid.ExperimentalUuidApi ]

TODO. Revisar!!

@Entity(tableName = "User")
data class User(
  @PrimaryKey val id: UUID = UUID.fromString(DEFAULT_UUID),
  val firstName: String,
  val lastName: String
)

UUID in Kotlin Multiplatform

Composite Primary Keys

In some cases, you may have a composite primary key, made up of two or more columns in the database. This is particularly true if you are trying to design your entities around an existing database structure, one that used a composite primary key for one of its tables (for whatever reason).

If, logically, those are all part of a single object, you could combine them into a single property, as we will see in Custom Type. However, it may be that they should be individual properties in your entity, but they happen to combine to create the primary key. In that case, you can skip the @PrimaryKey annotation and use the primaryKeys property of the @Entity annotation.

One scenario for this is data versioning, where we are tracking changes to data over time, the way a version control system tracks changes to source code and other files over time. There are several ways of implementing data versioning. One approach has all versions of the same entity in the same table, with a version code attached to the “natural” primary key to identify a specific version of that content.

In that case, you could have something like:

@Entity(tableName = "CompositeKey", primaryKeys = ["id", "version"])
data class CompositeKey(
    val id: String = Uuid.random().toString(),
    val version: Int = 1,
    val title: String,
    val text: String,
) {
    @Dao
    interface SQL {
        @Query("SELECT * FROM CompositeKey")
        suspend fun select(): List<CompositeKey>

        @Query("SELECT * FROM CompositeKey WHERE id = :id AND version = :version")
        suspend fun selectWherePrimaryKey(id: String, version: Int): CompositeKey?

        @Insert
        suspend fun insert(compositeKey: CompositeKey)
    }
}

Room will then use the PRIMARY KEY keyword in the CREATE TABLE statement to set up the composite primary key:

CREATE TABLE IF NOT EXISTS CompositeKey (id TEXT NOT NULL, version INTEGER NOT NULL, title TEXT NOT NULL, text TEXT NOT NULL, PRIMARY KEYKEY(id, version))

In our case, we set version to have a default value of 1, so we can create a CompositeKey with just a string identifier, at least for its initial version:

@Test
fun compositeKey() = runTest {

    val entity = CompositeKey(title = "Hello World", text = "- A tiny step to begin something big.")
    db.compositeKey().insert(entity)

    db.compositeKey().selectWherePrimaryKey(entity.id, 1)!!.let {
        it.id shouldBe entity.id
        it.version shouldBe entity.version
    }
}

@Test
fun compositeKeyDuplicated() = runTest {
    val entity = CompositeKey(title = "Tips & Tricks", text = "Short insights to help you work smarter.")
    db.compositeKey().insert(entity)

    val copy = entity.copy(text = "This is different!")

    val ex = shouldThrow<SQLiteException> {
        db.compositeKey().insert(copy)
    }
    ex.message?.contains("UNIQUE constraint failed")
}

If we try to insert entities with the same key twice, our @Insert-annotated function will throw a SQLiteException.

Indexes

Your primary key is indexed automatically by SQLite. However, you may wish to set up other indexes for other columns or collections of columns, to speed up queries.

To do that, you have two choices:

1. Use the indices property on @Entity. This property takes a list of nested Index annotations, each of which declares an index.
2. Use the index property on @ColumnInfo, to add an index on a single property.

The latter is simpler; the former handles more complex scenarios (e.g., an index involving multiple properties).

Here, we have an entity with an index on a category property:

@Entity(tableName = "Indexed")
data class Indexed(
    @PrimaryKey val id: String = Uuid.random().toString(),
    val title: String,
    @ColumnInfo(index = true) val category: String,
    val text: String? = null,
    val version: Int = 1,
) {
    @Dao
    interface SQL {

        @Query("SELECT * FROM Indexed")
        suspend fun select(): List<Indexed>

        @Query("SELECT * FROM Indexed WHERE category = :category")
        suspend fun selectWhereCategory(category: String): List<Indexed>

        @Insert
        suspend fun insert(vararg indexed: Indexed)
    }
}

Room will add the requested index:

CREATE TABLE IF NOT EXISTS Indexed (id TEXT NOT NULL, title TEXT NOT NULL, category TEXT NOT NULL, text TEXT, version INTEGER NOT NULL, PRIMARY KEY(id))
CREATE INDEX IF NOT EXISTS index_indexed_category ON indexed (category)

Alternatively, we could have used indices on the @Entity annotation:

@Entity(tableName = "Indexed", indices = [Index("category")])
data class Indexed(
    @PrimaryKey val id: String = Uuid.random().toString(),
    val title: String,
    val category: String,
    val text: String? = null,
    val version: Int = 1,
)

If you have a composite index, consisting of two or more fields, the Index nested annotation takes a comma-delimited list of column names and will generate the composite index.

The index will be used by SQLite automatically if you execute queries that involve the index. The selectWhereCategory() function queries on the indexed category property, and so our index should be used when we call that function:

@Test
fun indexed() = runTest {

    db.indexed().insert(
        Indexed(
            title = "What’s New This Week",
            category = "news",
            text = "Highlights, fixes, and small delights you might've missed."
        )
    )

    db.indexed().selectWhereCategory("news").let {
        it.size shouldBe 1
        it.first().title shouldBe "What’s New This Week"
    }
}

If the index should also enforce uniqueness — only one entity can have the indexed value — add unique = true to the Index annotation.

This requires you to assign the column(s) for the index to the value property, due to the way annotations work in Kotlin:

@Entity(tableName = "Indexed", indices = [Index(value = ["title"], unique = true)])
data class Indexed(
    @PrimaryKey val id: String = Uuid.random().toString(),
    val title: String,
    @ColumnInfo(index = true) val category: String,
    val text: String? = null,
    val version: Int = 1,
)

This causes Room to add the UNIQUE keyword to the CREATE INDEX statement:

CREATE UNIQUE INDEX IF NOT EXISTS index_indexed_title ON indexed (title)

While a regular index supports multiple values, a unique index does not, leading once again to a SQLiteConstraintException if we try inserting a duplicate:

@Test
fun indexedDuplicated() = runTest {
    db.indexed().insert(
        Indexed(
            title = "Kotlin",
            category = "news",
            text = "Kotlin 2.20 is out!"
        )
    )



    val ex = shouldThrow<SQLiteException> {
        db.indexed().insert(
            Indexed(
                title = "Kotlin",
                category = "news",
                text = "Kotlin 2.21 is out!"
            )
        )
    }
    ex.message!!.contains("UNIQUE constraint failed")
}

Custom Column

Beyond specifying index = true, you can configure other options on a @ColumnInfo annotation.

Name

By default, Room will generate names for your tables and columns based off of the entity class names and property names. In general, it does a respectable job of this, and so you may just leave them alone. However, you may find that you need to control these names, particularly if you are trying to match an existing database schema (e.g., you are migrating an existing Android app to use Room instead of using SQLite directly). And for table names in particular, setting your own name can simplify some of the SQL that you have to write for @Query-annotated functions.

As we have seen, to control the table name, use the tableName property on the @Entity attribute, and give it a valid SQLite table name. To rename a column, add the @ColumnInfo annotation to the property, with a name property that provides your desired name for the column:

@Entity(tableName = "CustomColumn")
data class CustomColumn(
    @PrimaryKey(autoGenerate = true) val id: Long = 0,
    @ColumnInfo(name = "firstName") val givenName: String,
    @ColumnInfo(name = "lastName") val surname: String,
) {
    @Dao
    interface SQL {
        @Query("SELECT * FROM CustomColumn")
        suspend fun select(): List<CustomColumn>

        @Query("SELECT * FROM CustomColumn WHERE firstName = :name")
        suspend fun selectWhereGiveName(name: String): List<CustomColumn>

        @Insert
        suspend fun insert(customColumn: CustomColumn)
    }
}

Here, we changed the givenName property’s column to firstName, along with specifying the table name.

The SQL will reflect that change:

CREATE TABLE IF NOT EXISTS CustomColumn (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, firstName TEXT NOT NULL, lastName TEXT, PRIMARY KEY(id))

…even though we still refer to the property by its regular Kotlin name:

  @Test
    fun customColumn() = runTest {
        db.customColumn().insert(CustomColumn(givenName = "David", surname = "de Mingo"))
        db.customColumn().select().first().let {
            it.givenName shouldBe "David"
        }
    }

Note, though, that many of the annotation attributes that Room uses refer to column names, not property names.

Fixa’t que en el @Query de la consulta selectWhereGiveName() no se usa el nombre de la propiedad givenName, sino el nombre de la columna firstName:

@Query("SELECT * FROM CustomColumn WHERE firstName = :name")
suspend fun selectWhereGiveName(name: String): List<CustomColumn>

Collate

You can specify a collate property to indicate the collation sequence to apply to this column. Here, “collation sequence” is a fancy way of saying “comparison function for comparing two strings”.

There are four options:

• BINARY and UNDEFINED, which are equivalent, the default value, and indicate that case is sensitive
• NOCASE, which indicates that case is not sensitive (more accurately, that the 26 English letters are converted to uppercase)
• RTRIM, which indicates that trailing spaces should be ignored on a case-sensitive collation

There is no full-UTF equivalent of NOCASE in SQLite.

Type Affinity

Normally, Room will determine the type to use on the column in SQLite based upon the type of the property (e.g., Int properties create INTEGER columns). If, for some reason, you wish to try to override this behavior, you can use the typeAffinity property on @ColumnInfo to specify some other type to use.

Default Values

@ColumnInfo also has a defaultValue property. As you might guess from the name, it provides a default value for the column in the table definition.

However, “out of the box”, it may be less useful than you think. If you @Insert an entity, the value for this column from the entity will be used, not the default value.

We will explore defaultValue, and scenarios where it is useful, later [Default values and partial entities]