Polymorphic Entities

Java and Kotlin programmers are used to polymorphism, where you can treat objects as being of the same type, when in truth their concrete types differ. This could be based on a common interface or a common base class (abstract or otherwise).

Those used to putting data into SQL databases are used to the fact that polymorphism and a relational database do not work together naturally. This is just “one of those things” that developers have to deal with, as part of “object-relational impedance mismatch”.

There are a few strategies for dealing with polymorphic relations in relational databases. This chapter outlines two of them, with an eye towards how they can be implemented with Room.

Polymorphism With Separate Tables

One approach uses a separate table for instances of each concrete type. So, for example if we have a CommentEntity class and a LinkEntity class, and they both implement a common Note interface, we wind up with dedicated tables for CommentEntity and LinkEntity. This keeps the database structure simple, as we still have a 1:1 relationship between concrete class and table. However, it means that any persistence code that deals with Note objects needs to handle the fact that a Note is stored differently for different Note implementations, and that can add complexity.

The the MiscSamples module of the book’s primary sample project has a poly sub-package with classes that implement this strategy.

As depicted in the preceding paragraph, we have a Note interface:

package com.commonsware.room.misc.poly

interface Note {
  val displayText: CharSequence
}

We also have CommentEntity and LinkEntity classes that implement that interface and have slightly different contents:

package com.commonsware.room.misc.poly

import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "comments")
data class CommentEntity(
  @PrimaryKey
  val id: Long,
  val text: String
) : Note {
  override val displayText: CharSequence
    get() = text
}

package com.commonsware.room.misc.poly

import androidx.core.text.HtmlCompat
import androidx.room.Entity
import androidx.room.PrimaryKey

@Entity(tableName = "links")
data class LinkEntity(
  @PrimaryKey(autoGenerate = true)
  val id: Long,
  val title: String,
  val url: String
) : Note {
  override val displayText: CharSequence
    get() = HtmlCompat.fromHtml(
      """<a href="$url">$title</a>""",
      HtmlCompat.FROM_HTML_MODE_COMPACT
    )
}

All Note wants is a displayName CharSequence with a display representation of the note. Comment just holds some text (text), so the displayName is just the text. Link has a URL and a title, so the displayName is a clickable rendition of that link, here generated by a snippet of HTML and HtmlCompat. A more efficient, but less readable, approach would be to use a ClickableSpan with a SpannableStringBuilder to create this clickable link.

PolyStore is our DAO for manipulating these objects. That needs to have functions for working with our two entity classes:

  @Query("SELECT * FROM comments")
  fun allComments(): List<CommentEntity>

  @Insert
  fun insert(vararg comments: CommentEntity)

  @Query("SELECT * FROM links")
  fun allLinks(): List<LinkEntity>

  @Insert
  fun insert(vararg links: LinkEntity)

However, consumers of PolyStore might prefer to work with Note objects, ignoring the details of whether those notes are comments or links. For that, we have to write our own concrete functions:

  @Transaction
  fun allNotes() = allComments() + allLinks()

  @Transaction
  fun insert(vararg notes: Note) {
    insert(*notes.filterIsInstance(CommentEntity::class.java).toTypedArray())
    insert(*notes.filterIsInstance(LinkEntity::class.java).toTypedArray())
  }

allNotes() simply combines the results of the allComments() and allLinks() functions. The Note variant of insert() turns around and calls the CommentEntity and LinkEntity variants of insert(), finding the CommentEntity and LinkEntity instances in our vararg of Note objects. The fact that we are using vararg makes insert() a bit complicated, with spread operators (*) and toTypedArray() calls. If we were using List instead of vararg, we would have somewhat simpler code:

@Transaction
fun insert(notes: List<Note>) {
  insert(notes.filterIsInstance(CommentEntity::class.java))
  insert(notes.filterIsInstance(LinkEntity::class.java))
}

Having the Note functions on PolyStore allows us to work with the concrete types or the Note interface, as we see fit:

package com.commonsware.room.misc.poly

import androidx.room.Room
import androidx.test.ext.junit.runners.AndroidJUnit4
import androidx.test.platform.app.InstrumentationRegistry
import com.commonsware.room.misc.MiscDatabase
import com.natpryce.hamkrest.*
import com.natpryce.hamkrest.assertion.assertThat
import org.junit.Test
import org.junit.runner.RunWith

@RunWith(AndroidJUnit4::class)
class PolyStoreTest {
  private val db = Room.inMemoryDatabaseBuilder(
    InstrumentationRegistry.getInstrumentation().targetContext,
    MiscDatabase::class.java
  )
    .build()
  private val underTest = db.polyStore()

  @Test
  fun comments() {
    assertThat(underTest.allComments(), isEmpty)

    val firstComment = CommentEntity(1, "This is a comment")
    val secondComment = CommentEntity(2, "This is another comment")

    underTest.insert(firstComment, secondComment)

    assertThat(
      underTest.allComments(),
      allOf(
        hasSize(equalTo(2)),
        hasElement(firstComment),
        hasElement(secondComment)
      )
    )

    assertThat(
      underTest.allNotes(),
      allOf(
        hasSize(equalTo(2)),
        hasElement(firstComment as Note),
        hasElement(secondComment as Note)
      )
    )
  }

  @Test
  fun links() {
    assertThat(underTest.allLinks(), isEmpty)

    val firstLink = LinkEntity(1, "CommonsWare", "https://commonsware.com")
    val secondLink = LinkEntity(
      2,
      "Room Release Notes",
      "https://developer.android.com/jetpack/androidx/releases/room"
    )

    underTest.insert(firstLink, secondLink)

    assertThat(
      underTest.allLinks(),
      allOf(
        hasSize(equalTo(2)),
        hasElement(firstLink),
        hasElement(secondLink)
      )
    )

    assertThat(
      underTest.allNotes(),
      allOf(
        hasSize(equalTo(2)),
        hasElement(firstLink as Note),
        hasElement(secondLink as Note)
      )
    )
  }

  @Test
  fun notes() {
    assertThat(underTest.allNotes(), isEmpty)

    val firstComment = CommentEntity(1, "This is a comment")
    val secondComment = CommentEntity(2, "This is another comment")
    val firstLink = LinkEntity(1, "CommonsWare", "https://commonsware.com")
    val secondLink = LinkEntity(
      2,
      "Room Release Notes",
      "https://developer.android.com/jetpack/androidx/releases/room"
    )

    underTest.insert(firstComment, secondComment, firstLink, secondLink)

    assertThat(
      underTest.allNotes(), allOf(
        hasSize(equalTo(4)),
        hasElement(firstLink as Note),
        hasElement(secondLink as Note),
        hasElement(firstComment as Note),
        hasElement(secondComment as Note)
      )
    )
  }
}

Can I JOIN a UNION?

You might think that we could create allNotes() using the UNION support in SQLite. This basically allows you to concatenate two queries and combine their results.

The theory would be that you could do something like this:

@Query("SELECT * FROM links UNION ALL SELECT * FROM comments")
fun allNotes(): List<Note>

However, this will not work.

In this specific case, links and comments do not have the same columns, as our entities have different fields. This runs afoul of UNION regulations, as at minimum, both halves of the UNION have to return the same number of columns.

Beyond that, Room has no way to know which rows are links and which rows are comments, as there is nothing to distinguish them in the result set.

Finally, Room cannot create instances of Note, as that is an interface, and we want LinkEntity and CommentEntity objects anyway. That would require Room to not only know which rows are links and which are comments, but that rows that are links should be turned into LinkEntity objects and that rows that are comments should be turned into CommentEntity objects.

From a practical standpoint, both entities would need to have the same properties and resulting schema. The result set (embodied in a Cursor) has only one set of column names, based on the first query in the UNION. Room would need to be able to determine how to populate entities from the second query using the first query’s column names. In all likelihood, that would require the names to be the same in both queries and in both entities.

Due to these limitations, it is unlikely that Room will get this capability, though it is not impossible.