teaching machines

SENG 440: Lecture 13 – Object-Relational Mapping via Room

April 1, 2019 by . Filed under lectures, semester1-2019, seng440.

Dear students,

Today we examine persisting data with databases. We will add a song database to our Rattler application. Initially we’ll just maintain an in-memory database, but then we’ll switch to long-term storage using Room, an object-relational mapping framework. Since ORM tools let us maintain our schema and forge our queries in our host language, we will write hardly any SQL.

Before we forget, here’s your TODO for next time:

Let’s start creating our app, which we’ll call Rattler.

Database Operations

When we left on last time, we had an app that allowed the user to edit and play a single RTTTL file. Today we want to extend this to allow the player to manage a whole library of ringtones. For a start, I’ve got the app set up with a fixed collection of Song instances to serve as our “database.” What sort of operations do we generally want to perform on a database? What do we want to do in this app? Let’s walk through some of the most common answers.

Insert

For one, we want to be able to create new songs. We’ll add a clause to our activity’s onOptionsItemSelected to trigger our adapter’s insert method that grows our database:

override fun onOptionsItemSelected(item: MenuItem) = when (item.itemId) {
  ...
  R.id.newSongButton -> {
    adapter.insert()
    true
  }
  ...
}

Our adapter will generate a placeholder song and trigger view updates:

fun insert() {
  val song = Song("Untitled", 100, "")
  songs.add(song)
  selectIndex(songs.size - 1)
  notifyItemInserted(songs.size - 1)
}

Note that we call notifyItemInserted. Fine-grained notices like this are the raison d’etre for RecyclerView.

Delete

We also delete records from a database. We’ll start with a new clause in the activity’s onOptionsItemSelected:

override fun onOptionsItemSelected(item: MenuItem) = when (item.itemId) {
  ...
  R.id.deleteButton -> {
    adapter.delete()
    true
  }
  ...
}

In the adapter’s delete method, we yank the song out of the list and announce the vacancy. But deleting only makes sense when the adapter has a selected item. Let’s add a helper predicate to simplify our code.

val isSongSelected: Boolean
  get() = selectedIndex != RecyclerView.NO_POSITION

Then we’ll write delete as follows:

fun delete() {
  if (isSongSelected) {
    val song = songs.removeAt(selectedIndex)
    selectIndex(RecyclerView.NO_POSITION)
    notifyItemRemoved(selectedIndex)
  }
}

It’s worth noting that the first time I wrote this app, I simply reloaded all the songs when a new song was inserted or deleted. If you can avoid doing that, the RecyclerView can animate the changes to the list much more gracefully.

Clear

Let’s add an action to wipe the database. We’ll start with a new clause in our activity’s onOptionsItemSelected:

override fun onOptionsItemSelected(item: MenuItem) = when (item.itemId) {
  ...
  R.id.clearDatabaseButton -> {
    adapter.clear()
    true
  }
  ...
}

In our adapter’s clear method, we wipe the list and trigger whatever non-selection callback the client has registered:

fun clear() {
  songs.clear()
  notifyDataSetChanged()
  onNothingSelected()
}

Since every item is changed in the RecyclerView, we don’t try to make a fine-grained announcement.

Update

Finally, we want to edit items in the database. We already have hooks for edit events: our three TextWatchers in our main activity. We have them trigger our adapter:

titleBox.addTextChangedListener(object: TextWatcher {
  ...
  override fun onTextChanged(text: CharSequence?, p1: Int, p2: Int, p3: Int) {
    song.title = titleBox.text.toString()
    adapter.update()
  }
})

notesBox.addTextChangedListener(object: TextWatcher {
  ...
  override fun onTextChanged(text: CharSequence?, p1: Int, p2: Int, p3: Int) {
    song.notes = notesBox.text.toString()
    adapter.update()
  }
})

beatsPerMinuteBox.addTextChangedListener(object: TextWatcher {
  ...
  override fun onTextChanged(text: CharSequence?, p1: Int, p2: Int, p3: Int) {
    try {
      song.beatsPerMinute = beatsPerMinuteBox.text.toString().toInt()
      adapter.update()
    } catch (e: NumberFormatException) {
    }
  }
})

Our adapter then just requests a redraw:

fun update() {
  if (isSongSelected) {
    notifyItemChanged(selectedIndex)
  }
}

Technically, only the title of a song is shown in the RecyclerView, so we would only need to trigger a redraw if the title had changed. But that’s a lot more work than we have time for.

Object-Relational Mapping

Decades ago our technological ancestors factored out the responsibility of storing and retrieving data to databases, which lived independently of our software. Because these two worlds were independent, our data types generally had two representations: the database representation and the host language representation. We effectively wrote two schemas, one explicit and written in the database language, and the other implicit as a collection of type declarations in our host language. It was our responsibility to write bridge code that mapped the two representations to each other.

To map the host language representation to the database, we often wrote SQL insert or update statements and dropped our string and numeric primitives into its value fields, hopefully using prepared statements to prevent injections. To map the database representation to the host language, we issued SQL select statements, extracted the results, and instantiated our models manually.

After we wrote this bridge code a couple of hundred times, we realized that mapping between representations could be automated. We added reflection mechanisms to the host language that would let a system interrogate our type declarations and generate a database schema and SQL statements. These object-relational mapping tools are present in many systems: ActiveRecord in Ruby on Rails, Hibernate for Java, Core Data in iOS, and so on. In Android, we have Room.

Room

There are a few steps to getting Room to automatically create a database for us. First we have some configuring to do:

Once the project is configured, we must dive write a wee bit of code to enable Room to automatically map. We’ll follow these steps:

Let’s walk through these one by one.

Song Entity

Each model that needs its own table in the database must be annotated with @Entity. In Rattler, the only model we have is Song. By default, Room will use the name of the class as the table name. Database folks tend to freak out about naming conventions. Many folks say table names should be plural and lowercase. We can override the default using a parameter. We mark up Song as follows:

@Entity(tableName = "songs")
class Song(...) ...
}

Room will examine each @Entity and create a column for each field—public or private. Again, the name of each field will be used by default. The convention in SQL is to use snake case rather than camel case. We can override the default using the @ColumnInfo annotation:

@Entity(tableName = "songs")
class Song(var title: String,
           @ColumnInfo(name = "beats_per_minute") var beatsPerMinute: Int,
           var notes: String) {
  ...
}

Finally, what does almost every table in a database need? An ID to serve as a primary key. Probably we don’t need this as a constructor parameter. Let’s just declare it as a field:

class Song(...) {
  @PrimaryKey(autoGenerate = true) var id: Long = 0
  ...
}

I want the database to automatically increment the serial value that backs this primary key, which I accomplish via the autoGenerate parameter.

That’s it for the model. Now let’s add a DAO for each model.

Song DAO

Most of our database operations fall into four buckets: create, read, update, and delete (CRUD). Our DAO declares methods that will support these operations. But it doesn’t implement them. The whole point of an ORM is to automatically define these implementations based on our entities. So, our song’s DAO is only an interface:

@Dao
interface SongDao {
  ...
}

We’ll need a method to create or insert a new song. We get to pick the name, but we must annotate it with @Insert to get insertion semantics:

@Insert
fun insert(song: Song): Long

This method returns the ID of the newly inserted song.

Similarly, we’ll need methods to update or delete existing songs:

@Update
fun update(song: Song)

@Delete
fun delete(song: Song)

Finally we mark a read/query operation with @Query. This operation comes in many possible forms, and Room doesn’t try to guess which form you are thinking of. Instead we must write some SQL to communicate exactly what we want. In our case, we want to get a list of all songs in the database:

@Query("SELECT * FROM songs")
fun getAll(): List<Song>

If we had a search feature, we could add a query that selected by some criteria. But we don’t have such a feature, and that means our DAO is complete for the time being. On to the database.

Database

To create and access a database, we must first create an abstract class that binds our entities to it:

@Database(entities = [Song::class], version = 1)
abstract class SongDatabase : RoomDatabase() {
  abstract fun songDao(): SongDao
}

Then we’ll try adding this code to our main activity:

val database = Room.databaseBuilder(applicationContext, SongDatabase::class.java, "songs").build()

Unfortunately, the code fails. When we use raw SQLite calls, we can get away with running on the main thread. Room, however, fails if run on the main thread. Since we’ll sometimes need both a background thread to perform the query and the main thread to update the UI, we’ll use a number of AsyncTasks to handle our database interactions.

LoadDatabaseTask

Since the adapter and database are bound up together in our app, let’s have the adapter own the database. This might not be a great design, but I’m not convinced there is a great design. We’ll need that database reference before we can do anything else, so let’s fire off our AsyncTask to grab that reference in the adapter’s constructor:

class SongAdapter(val context: Context) : RecyclerView.Adapter<SongViewHolder>() {
  ...

  var database: SongDatabase? = null

  init {
    LoadDatabaseTask(this).execute()
  }

  ...
}

The task itself is a lot of code for not a lot of work. We build the database on the background thread, and set the adapter’s field on the main thread:

class LoadDatabaseTask(adapter: SongAdapter) : AsyncTask<Unit, Unit, SongDatabase?>() {
  private val adapter = WeakReference(adapter)

  override fun doInBackground(vararg p0: Unit?): SongDatabase? {
    var database: SongDatabase? = null
    adapter.get()?.let {
      database = Room.databaseBuilder(it.context.applicationContext, SongDatabase::class.java, "songs").build()
    }
    return database
  }

  override fun onPostExecute(database: SongDatabase?) {
    adapter.get()?.let {
      it.database = database
    }
  }
}

Note that we use a WeakReference to guard against an expired activity.

LoadSongsTask

Once we’ve got a reference to our database, let’s pull out all the songs from it and populate our RecyclerView. That query also needs to be happen on a background thread. We’ll fire off another AsyncTask when our database property is set:

class SongAdapter(val context: Context) : RecyclerView.Adapter<SongViewHolder>() {
  ...

  var database: SongDatabase? = null
    set(value) {
      field = value
      value?.let {
        LoadSongsTask(it, this).execute()
      }
    }

  ...
}

The task itself invokes the song DAO to do all the work. It simply schedules the tasks on the appropriate threads:

class LoadSongsTask(private val database: SongDatabase,
                    private val adapter: SongAdapter) : AsyncTask<Unit, Unit, List<Song>>() {
  override fun doInBackground(vararg p0: Unit?): List<Song> {
    val songDao = database.songDao()
    return songDao.getAll()
  }

  override fun onPostExecute(songs: List<Song>) {
    adapter.songs = songs
  }
}

NewSongTask

We tweak our adapter’s insert method to fire off a database insertion:

fun insert() {
  if (database != null) {
    val song = Song("Untitled", 100, "")
    songs.add(song)
    selectIndex(songs.size - 1)
    notifyItemInserted(songs.size - 1)

    NewSongTask(database!!, song).execute()
  }
}

The accompanying task again farms out the work to the DAO to do the inserting, but we must also update the song’s ID, which the database assigns and the DAO reports back:

class NewSongTask(private val database: SongDatabase,
                  private val song: Song) : AsyncTask<Unit, Unit, Unit>() {
  override fun doInBackground(vararg p0: Unit?) {
    song.id = database.songDao().insert(song)
  }
}

There’s probably a race condition in here somewhere. That’s something to investigate another day.

DeleteSongTask

And then we add delete to the adapter.

fun delete() {
  if (isSongSelected) {
    val song = songs.removeAt(selectedIndex)
    selectIndex(RecyclerView.NO_POSITION)
    notifyItemRemoved(selectedIndex)
    DeleteSongTask(database!!, song).execute()
  }
}

Once again, the DAO does all the work for us in our task:

class DeleteSongTask(private val database: SongDatabase,
                     private val song: Song) : AsyncTask<Unit, Unit, Unit>() {
  override fun doInBackground(vararg p0: Unit?) {
    database.songDao().delete(song)
  }
}

ClearDatabaseTask

We adjust our adapter’s clear method to wipe its list, clear the database, and trigger whatever non-selection callback the client has registered:

fun clear() {
  if (database != null) {
    songs.clear()
    notifyDataSetChanged()
    ClearDatabaseTask(database!!).execute()
    onNothingSelected()
  }
}

The task calls upon the database to wipe all its tables:

class ClearDatabaseTask(private val database: SongDatabase) : AsyncTask<Unit, Unit, Unit>() {
  override fun doInBackground(vararg p0: Unit?) {
    database.clearAllTables()
  }
}

We’re getting close!

UpdateSongTask

The only task that remains is to handle edits to songs. The adapter’s update method now triggers a redraw and issues a database update command:

fun update() {
  if (isSongSelected) {
    notifyItemChanged(selectedIndex)
    UpdateSongTask(database!!, songs[selectedIndex]).execute()
  }
}

The task once again calls upon the DAO to do all the work:

class UpdateSongTask(private val database: SongDatabase,
                     private val song: Song) : AsyncTask<Unit, Unit, Unit>() {
  override fun doInBackground(vararg p0: Unit?) {
    database.songDao().update(song)
  }
}

And there we have it. Because we are pushing database transactions as we go, we don’t need to do anything more for our data to persist. The database lives in our app’s internal storage and will therefore ride out any configuration changes. We didn’t write a lot of code to achieve this—a few annotations but a bit more asynchronous code.

That’s enough for today. See you next time!

Sincerely,

P.S. It’s time for a haiku!

Database debates
Test betas, see stats, state bets
Baddest sad, best dabs