Building an RSS Reader for Android #5: Data access objects and SQL queries, continued

In my last post, I wrote about one of the data access objects within our app, called FeedsDao, which contains all methods that have to do with accessing Feed data in our database. The operations defined within range from simple, needing only a simple annotation recognizable by Room, to complex, requiring reference to multiple tables and lengthy SQL queries. Our second DAO, which has to do with Entry data, is quite similar:

interface EntriesDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun addEntries(entries: List<Entry>)

    @Query("SELECT * FROM Entry WHERE url = :entryId")
    fun getEntry(entryId: String): LiveData<Entry?>

    @Query(
        "SELECT url, title, website, date, image, isStarred, isRead " +
            "FROM Entry WHERE isRead = 0 ORDER BY date DESC LIMIT :max"
    )
    // Warning is for unspecified fields, which we want null
    @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
    fun getNewEntries(max: Int): LiveData<List<Entry>>

    @Query(
        "SELECT url, title, website, date, image, isStarred, isRead " +
            "FROM Entry WHERE isStarred = 1"
    )
    @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
    fun getStarredEntries(): LiveData<List<Entry>>

    @Query(
        "SELECT Entry.url, title, website, author, date, content, image, isStarred, isRead " +
            "FROM FeedEntryCrossRef AS _junction " +
            "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
            "WHERE _junction.feedUrl = :feedId"
    )
    fun getEntriesByFeed(feedId: String): LiveData<List<Entry>>

    @Query(
        "SELECT Entry.url, isStarred, isRead " +
                "FROM FeedEntryCrossRef AS _junction " +
                "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
                "WHERE _junction.feedUrl = :feedId"
    )
    fun getEntriesToggleableByFeedSynchronously(feedId: String): List<EntryToggleable>

    @Update
    fun updateEntries(entries: List<Entry>)

    @Query("UPDATE Entry SET isStarred = :isStarred WHERE url IN (:entryId)")
    fun updateEntryIsStarred(vararg entryId: String, isStarred: Boolean)

    @Query("UPDATE Entry SET isRead = :isRead WHERE url IN (:entryId)")
    fun updateEntryIsRead(vararg entryId: String, isRead: Boolean)

    @Delete
    fun deleteEntries(entries: List<Entry>)

    @Query(
        "DELETE FROM Entry WHERE url IN " +
                "(SELECT url FROM FeedEntryCrossRef AS _junction " +
                "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
                "WHERE _junction.feedUrl IN (:feedId))"
    )
    fun deleteEntriesByFeed(vararg feedId: String)

    @Query("DELETE FROM Entry WHERE url IN (:entryIds)")
    fun deleteEntriesById(entryIds: List<String>)

    @Query("DELETE FROM Entry WHERE url NOT IN (SELECT entryUrl FROM FeedEntryCrossRef)")
    fun deleteLeftoverEntries()
}

I'll spend time here only on the more complex operations.

Fetching specific fields

You'll notice a pair of operations, getNewEntries and getStarredEntries, that call for specific fields from the Entry table and return a list of Entries. Usually, when not all data is required from any given row, it makes sense to define a separate model or data class with only the needed properties. In this case, I simply allow Room to return null values on those fields in which I'm not interested—the @SuppressWarnings annotation tells Room that this is on purpose.

@Query(
    "SELECT url, title, website, date, image, isStarred, isRead " +
        "FROM Entry WHERE isRead = 0 ORDER BY date DESC LIMIT :max"
)
// Warning is for unspecified fields, which we want null
@SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
fun getNewEntries(max: Int): LiveData<List<Entry>>

@Query(
    "SELECT url, title, website, date, image, isStarred, isRead " +
        "FROM Entry WHERE isStarred = 1"
)
@SuppressWarnings(RoomWarnings.CURSOR_MISMATCH)
fun getStarredEntries(): LiveData<List<Entry>>

The SQL query accompanying getStarredEntries is simple enough—i.e., return the specified data from all rows in the Entry table whose isStarred property is 1, or true. In getNewEntries it's more specific, but not overly complicated either: fetch all rows from the Entry table whose isRead property is 0 (or false), sorting all fetched rows by date in descending order (that is, from newest to oldest). Furthermore the method requires an argument named 'max,' representing the maximum number of entries it should return. That number is passed on the query as a limit.

In most other cases though, I've found it better to define new data classes depending on what specific fields or properties I need. Like Feed, there are also multiple variations of the Entry data class, depending on what is needed in a given situation:

// Light version of Entry – no content and author
data class EntryLight(
    val url: String,
    val title: String,
    val website: String,
    val date: Date?,
    val image: String?,
    var isStarred: Boolean = false,
    var isRead: Boolean = false
)

// Minimal version of Entry – no url, website, image, isStarred, isRead
data class EntryMinimal (
    val title: String,
    val date: Date?,
    val author: String?,
    val content: String
)

// Entry data with only fields toggleable by user
data class EntryToggleable(
    val url: String,
    val isStarred: Boolean,
    val isRead: Boolean,
)

More inner joins

Another pair of methods, getEntriesByFeed and getEntriesToggleableByFeedSynchronously, use almost the exact same query using the INNER JOIN clause: SELECT ... FROM FeedEntryCrossRef AS _junction INNER JOIN Entry ON (_junction.entryUrl = Entry.url) WHERE _junction.feedUrl = :feedId.

Both methods are meant to fetch the required Entries using only a feedId (or URL) as a reference. Apart from the different fields and return objects needed, the only difference is that the former is asynchronous (and therefore returns a LiveData object), while the latter is synchronous, meant to run as a background task.

@Query(
    "SELECT Entry.url, title, website, author, date, content, image, isStarred, isRead " +
        "FROM FeedEntryCrossRef AS _junction " +
        "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
        "WHERE _junction.feedUrl = :feedId"
)
fun getEntriesByFeed(feedId: String): LiveData<List<Entry>>

@Query(
    "SELECT Entry.url, isStarred, isRead " +
        "FROM FeedEntryCrossRef AS _junction " +
        "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
        "WHERE _junction.feedUrl = :feedId"
)
fun getEntriesToggleableByFeedSynchronously(feedId: String): List<EntryToggleable>

In both cases, the query points first to the FeedEntryCrossRef table (since the only reference data is a Feed URL), using it as a junction between said table and the Entry table by declaring an equivalence between the former's entryUrl column and the latter's url column. The query therefore fetches all resulting rows where the value of feedUrl matches the given feedId.

More nested queries

One method, deleteEntriesByFeed, requires a much more involved query. The method accepts a variable number of feedId as an argument; we want the operation to delete all entries associated with each given feedId, which may be one or more. In this case we require not only looking across tables, but a nested query: DELETE FROM Entry WHERE url IN (SELECT url FROM FeedEntryCrossRef AS _junction INNER JOIN Entry ON (_junction.entryUrl = Entry.url) WHERE _junction.feedUrl IN (:feedId)).

@Query(
    "DELETE FROM Entry WHERE url IN " +
            "(SELECT url FROM FeedEntryCrossRef AS _junction " +
            "INNER JOIN Entry ON (_junction.entryUrl = Entry.url) " +
            "WHERE _junction.feedUrl IN (:feedId))"
)
fun deleteEntriesByFeed(vararg feedId: String)

First, the inner query, which uses an INNER JOIN to once again find all Entries associated with the given feedId through our FeedEntryCrossRef table: we end up with a collection of URLs each belonging to an Entry in our database. Then the outer query simply uses that collection, deleting every Entry whose url does appears in it.

Another method, named deleteLeftoverEntries, requires a nested query as well but is much simpler than the previous example. The idea is to delete all Entries in our database which are, at the time of querying, unassociated with any Feeds. This means, in the inner query, first collecting all entryUrls from our FeedEntryCrossRef table; then the outer query simply refers to the resulting collection, and deleting every Entry it finds whose url not in it.

@Query("DELETE FROM Entry WHERE url NOT IN (SELECT entryUrl FROM FeedEntryCrossRef)")
fun deleteLeftoverEntries()

A third DAO: FeedEntryCrossRefs

The last example is meant only as a safety method: if all is working as it should, we don't actually expect our app to have any Entries that aren't associated with any Feed. A similar method appears in our third DAO, which is much smaller than the previous two, and contains only operations specific to the FeedEntryCrossRef entity:

interface FeedEntryCrossRefsDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun addFeedEntryCrossRefs(crossRefs: List<FeedEntryCrossRef>)

    @Transaction
    fun addFeedEntryCrossRefs(feedId: String, entries: List<Entry>) {
        addFeedEntryCrossRefs(entries.map { FeedEntryCrossRef(feedId, it.url) })
    }

    @Query("DELETE FROM FeedEntryCrossRef WHERE feedUrl = :feedId AND entryUrl IN (:entryIds)")
    fun deleteFeedEntryCrossRefs(feedId: String, entryIds: List<String>)

    @Query("DELETE FROM FeedEntryCrossRef WHERE feedUrl IN (:feedId)")
    fun deleteCrossRefsByFeed(vararg feedId: String)

    @Query("DELETE FROM FeedEntryCrossRef WHERE feedUrl NOT IN (SELECT url FROM Feed)")
    fun deleteLeftoverCrossRefs()
}

Note the final method contained herein, deleteLeftOverCrossRefs, which is accompanied by a nested query as well. Similar to the previous example, the query first collects all URLs from our Feed table, then deletes all rows from FeedEntryCrossRef where the respective feedUrl values do not appear in the collection. This means we get rid of cross references in our database that point to Entries that no longer exist.

One final thing to note here is the pair of methods with the same name, addFeedEntryCrossRefs. This is a case of function overloading, meaning the ability to write multiple functions of the same name with different implementations.

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addFeedEntryCrossRefs(crossRefs: List<FeedEntryCrossRef>)

@Transaction
fun addFeedEntryCrossRefs(feedId: String, entries: List<Entry>) {
    addFeedEntryCrossRefs(entries.map { FeedEntryCrossRef(feedId, it.url) })
}

The first function takes a list of FeedEntryCrossRef as an argument, and tells Room to add each element in the list directly to our database, while ignoring any duplicate entries. The second function is practically an intermediary step: it takes instead a feedId and an Entry list as arguments. Each element in the Entry list is mapped to a new FeedEntryCrossRef object along with the given feedId; this results in a new list of FeedEntryCrossRef objects, ready to be saved to our database by passing it into the first function, of the same name. The @Transaction annotation on the second function tells Room that whatever is defined in the function body is meant to be a performed as a single database transaction.