A Room With a View

SQLite, like many SQL databases, not only supports CREATE TABLE for creating tables, but CREATE VIEW for creating views. In this case, a database view has nothing much to do with an Android View. Rather, a database view is a “view” onto other data in the database. It amounts to a SQL query that you can in turn query against as if it were a table.

For example, earlier in the book, we saw AppEntity:

package com.commonsware.room.misc

import androidx.room.*

@Entity(tableName = "apps")
data class AppEntity(
  @PrimaryKey
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val fullDescription: String,
  val latestVersionName: String,
  val lastUpdated: Long,
  val iconUrl: String,
  val packageUrl: String,
  val donationUrl: String
) {
  @Dao
  interface Store {
    @Query("SELECT * FROM apps")
    fun loadAll(): List<AppEntity>

    @Query("SELECT applicationId, displayName, shortDescription, iconUrl FROM apps")
    fun loadListModels(): List<AppListModel>

    @Insert
    fun insert(entity: AppEntity)
  }
}

data class AppListModel(
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val iconUrl: String
)

We used that to demonstrate having a DAO function return a type other than the entity itself, where loadListModels() returns a subset of the columns of the table, and those get mapped to an AppListModel instead of an AppEntity.

Another way to have implemented that would be to define a database view on the apps table that happens to return those four columns. We can then query that view as if it were an actual table, with separate @Query functions.

Defining a View

Just as an entity is declared via an @Entity annotation, a database view is declared via a @DatabaseView annotation. It takes two parameters:

• value, which is a SQL query like you might have in a @Query annotation, though no parameters are allowed; and
• viewName, which is the equivalent of tableName on @Entity, supplying the name to use for this view

viewName is optional; if you skip it, the view is named the same as the class on which you are applying the @DatabaseView annotation.

AppView is a view on the AppEntity table (apps) that returns the four columns that we were using with loadListModels() and AppListModel before:

@DatabaseView(
  viewName = "appListView",
  value = "SELECT applicationId, displayName, shortDescription, iconUrl FROM apps"
)
data class AppView(
  val applicationId: String,
  val displayName: String,
  val shortDescription: String,
  val iconUrl: String
) {