Polymorphic Room Relations

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 involved in 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 them, with an eye towards how they can be implemented with Room.

Polymorphism With Separate Tables

One approach has each concrete type be stored in its own table. So, for example if we have a Comment class and a Link class, and they both implement a common Note interface, we wind up with dedicated tables for Comment and Link. 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.

The Trips/RoomPoly sample project employs this strategy. This is another riff on the trip-tracking sample app shown elsewhere in this book.

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

package com.commonsware.android.room;

public interface Note {
  String tripId();
}

We also have Comment and Link classes that implement that interface and have slightly different contents:

package com.commonsware.android.room;

import android.arch.persistence.room.Entity;
import android.arch.persistence.room.ForeignKey;
import android.arch.persistence.room.Ignore;
import android.arch.persistence.room.Index;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;
import java.util.UUID;
import static android.arch.persistence.room.ForeignKey.CASCADE;

@Entity(
  tableName="comments",
  foreignKeys=@ForeignKey(
    entity=Trip.class,
    parentColumns="id",
    childColumns="tripId",
    onDelete=CASCADE),
  indices=@Index("tripId"))
public class Comment implements Note {
  @PrimaryKey
  @NonNull
  public final String id;

  public final String text;

  @NonNull public final String tripId;

  public Comment(@NonNull String id, String text, @NonNull String tripId) {
    this.id=id;
    this.text=text;
    this.tripId=tripId;
  }

  @Ignore
  public Comment(String text, @NonNull Trip trip) {
    this(UUID.randomUUID().toString(), text, trip.id);
  }

  @Override
  public String tripId() {
    return tripId;
  }
}

package com.commonsware.android.room;

import android.arch.persistence.room.Entity;
import android.arch.persistence.room.ForeignKey;
import android.arch.persistence.room.Ignore;
import android.arch.persistence.room.Index;
import android.arch.persistence.room.PrimaryKey;
import android.support.annotation.NonNull;
import java.util.UUID;
import static android.arch.persistence.room.ForeignKey.CASCADE;

@Entity(
  tableName="links",
  foreignKeys=@ForeignKey(
    entity=Trip.class,
    parentColumns="id",
    childColumns="tripId",
    onDelete=CASCADE),
  indices=@Index("tripId"))
public class Link implements Note {
  @PrimaryKey
  @NonNull
  public final String id;

  public final String title;

  @NonNull public final String url;
  @NonNull public final String tripId;

  public Link(@NonNull String id, String title, @NonNull String url, @NonNull
    String tripId) {
    this.id=id;
    this.title=title;
    this.url=url;
    this.tripId=tripId;
  }

  @Ignore
  public Link(String title, @NonNull String url, @NonNull Trip trip) {
    this(UUID.randomUUID().toString(), title, url, trip.id);
  }

  @Override
  public String tripId() {
    return tripId;
  }
}

So, from a database schema standpoint, we have a 1:N relation between Trip and Comment, and we have a separate 1:N relation between Trip and Link. However, some of the rest of our Java code might want to think of that as a unified 1:N relation between Trip and Note, not worrying about the differences between Comment and Link. After all, we implemented that interface for some good reason (though in this sample, the reason is “to have a common interface to use for illustration purposes”).

The project still has a TripStore DAO, with a concrete implementation code-generated by Room. In this project, though, TripStore is an abstract class, not an interface, the way most versions of the “trips” sample are set up.

We can have the same sort of basic CRUD methods for Comment and Link as we would with any other 1:N Room relation:

  /*
    Comment
   */

  @Query("SELECT * FROM comments WHERE tripId=:tripId")
  abstract List<Comment> findCommentsForTrip(String tripId);

  @Insert
  abstract void insert(Comment... comments);

  @Update
  abstract void update(Comment... comments);

  @Delete
  abstract void delete(Comment... comments);

  /*
    Link
   */

  @Query("SELECT * FROM links WHERE tripId=:tripId")
  abstract List<Link> findLinksForTrip(String tripId);

  @Override
  @Query("SELECT * FROM links WHERE tripId=:tripId")
  public abstract DataSource.Factory<Integer, Link> pagedStuffForTrip(String tripId);

  @Insert
  abstract void insert(Link... comments);

  @Update
  abstract void update(Link... comments);

  @Delete
  abstract void delete(Link... comments);

But… what if we want to work with Note, ignoring the differences between Comment and Link?

For that, we need to write some custom DAO methods, using @Transaction to ensure that they are transactional:

  @Transaction
  List<Note> findNotesForTrip(String tripId) {
    ArrayList<Note> result=new ArrayList<>();

    result.addAll(findCommentsForTrip(tripId));
    result.addAll(findLinksForTrip(tripId));

    return result;
  }

  @Transaction
  void insert(Note... notes) {
    for (Note note : notes) {
      if (note instanceof Comment) {
        insert((Comment)note);
      }
      else if (note instanceof Link) {
        insert((Link)note);
      }
      else {
        throw new IllegalArgumentException("Um, wut dis? "+note.getClass().getCanonicalName());
      }
    }
  }

  @Transaction
  void update(Note... notes) {
    for (Note note : notes) {
      if (note instanceof Comment) {
        update((Comment)note);
      }
      else if (note instanceof Link) {
        update((Link)note);
      }
      else {
        throw new IllegalArgumentException("Um, wut dis? "+note.getClass().getCanonicalName());
      }
    }
  }

  @Transaction
  void delete(Note... notes) {
    for (Note note : notes) {
      if (note instanceof Comment) {
        delete((Comment)note);
      }
      else if (note instanceof Link) {
        delete((Link)note);
      }
      else {
        throw new IllegalArgumentException("Um, wut dis? "+note.getClass().getCanonicalName());
      }
    }
  }

Retrieval — in the form of findNotesForTrip() — simply calls findCommentsForTrip() and findLinksForTrip() and concatenates their results.

Mutation — insert(), update(), and delete() — check each Note to see what type it is and casts to perform the “real” insert(), update(), or delete() for the concrete type.

So, this approach optimizes for the per-concrete-type behaviors, with wrappers to try to homogenize access when dealing with the Note abstraction.

Can I Join a UNION?

You might think that we could create findNotesForTrip() 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 WHERE tripId=:tripId UNION ALL SELECT * FROM comments WHERE tripId=:tripId")
List<Note> findNotesForTrip(String tripId);

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 Link and Comment 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 Link objects and that rows that are comments should be turned into Comment 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.