Building an RSS reader for Android #4: Data access objects and SQL queries

April 14, 2021

Picking up where I previously left off, a DAO is an abstract class or interface that contains definitions of methods for interacting with our database. Collectively the methods can be described as CRUD—Create, Read, Update, and Delete, which together comprise the standard four operations of a persistent storage system. Furthermore we don't actually implement the methods ourselves; the Room library does this for us, although depending on the complexity of a given operation, some SQL (structured query language) is necessary.

To keep things organized, I created a few separate DAOs, three of them corresponding to the three tables or entities we defined previously (Feed, Entry, and FeedEntryCrossRef). For example, FeedsDao contains all operations that have to do with Feeds:

interface FeedsDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun addFeeds(vararg feed: Feed)

    @Query("SELECT * FROM Feed WHERE url = :feedId")
    fun getFeed(feedId: String): LiveData<Feed?>

    @Query("SELECT url, title, imageUrl, category, unreadCount FROM Feed")
    fun getFeedsLight(): LiveData<List<FeedLight>>

    @Query("SELECT url, title, website, imageUrl, description, category FROM Feed")
    fun getFeedsManageable(): LiveData<List<FeedManageable>>

    @Query("SELECT url FROM Feed")
    fun getFeedIds(): LiveData<List<String>>

    @Query("SELECT url, category FROM Feed")
    fun getFeedIdsWithCategories(): LiveData<List<FeedIdWithCategory>>

    @Query("SELECT url FROM Feed")
    fun getFeedUrlsSynchronously(): List<String>

    @Query("SELECT title FROM Feed WHERE url = :feedId")
    fun getFeedTitleSynchronously(feedId: String): String

    @Update
    fun updateFeed(feed: Feed)

    @Query("UPDATE Feed SET title = :title WHERE url = :feedId")
    fun updateFeedTitle(feedId: String, title: String)

    @Query("UPDATE Feed SET category = :category WHERE url IN (:feedId)")
    fun updateFeedCategory(vararg feedId: String, category: String)

    @Transaction
    fun updateFeedTitleAndCategory(feedId: String, title: String, category: String) {
        updateFeedTitle(feedId, title)
        updateFeedCategory(feedId, category = category)
    }

    @Query("UPDATE Feed SET imageUrl = :feedImage WHERE url = :feedId")
    fun updateFeedImage(feedId: String, feedImage: String)

    @Query("UPDATE Feed SET unreadCount = :count WHERE url = :feedId")
    fun updateFeedUnreadCount(feedId: String, count: Int)

    @Query("UPDATE Feed SET unreadCount = (unreadCount + :addend) WHERE url = :feedId")
    fun incrementFeedUnreadCount(feedId: String, addend: Int)

    @Query(
        "UPDATE Feed SET unreadCount = (unreadCount + :addend) WHERE url IN " +
            "(SELECT url FROM FeedEntryCrossRef AS _junction " +
            "INNER JOIN Feed ON (_junction.feedUrl = Feed.url) " +
            "WHERE _junction.entryUrl = (:entryId))"
    )
    fun incrementFeedUnreadCountByEntry(entryId: String, addend: Int)

    @Query("DELETE FROM Feed WHERE url IN (:feedId)")
    fun deleteFeeds(vararg feedId: String)
}

How an operation gets defined within this interface is determined by a few things: 1) what specific data we want retrieved, if any, 2) what data we have on hand by which to fetch something else (for example, primary keys, or any other property), and 3) whether the data should be fetched synchronously or asynchronously—I'll come back to this point later.

Simple operations

The simplest operations are those that don't return any data. For instance, addFeeds accepts a variable number of arguments (vararg), that is, any number of Feeds that we want added to our database. We simply annotate it with @Insert and Room itself creates the necessary database query. We also include OnConflictStrategy.IGNORE to tell Room to ignore any incoming Feed whose primary key (in this case, its URL) already exists in our database.

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun addFeeds(vararg feed: Feed)

Another simple operation is updateFeed, which takes as an argument a single Feed. By simply annotating it with @Update, Room knows to use the Feed's primary key to query the database and make the necessary changes.

@Update
fun updateFeed(feed: Feed)

SQL queries, and synchronous vs asynchronous operations

In most other cases, we need to write the SQL queries ourselves. SQL is a standard way of communicating with relational databases. For example, the method getFeed, which takes a single feedId (or its URL) as an argument, is annotated with @Query, into which we pass a simple query as a string: SELECT * FROM Feed WHERE url = :feedId. This means we want to select all (*) columns from all rows in our Feed table whose value of url is equal to the feedId that has been passed into the method. Because we've ensured that all Feed URLs in our table are unique, we expect to find only one Feed.

@Query("SELECT * FROM Feed WHERE url = :feedId")
fun getFeed(feedId: String): LiveData<Feed?>

Aside: the above method returns to us a nullable Feed wrapped in a LiveData object. Most of the methods defined in this database are asynchronous; this is because database transactions require a perceivable amount time to complete, much like an HTTP request. So as to not make our user interface wait so much, a LiveData object is returned instantly without its content. This LiveData is observable by our UI; as soon as the transaction completes, its content is updated automatically.

There are indeed cases where we want to fetch data synchronously, such as in background tasks where the user interface is not involved. Here we simply skip using LiveData:

@Query("SELECT url FROM Feed")
fun getFeedUrlsSynchronously(): List<String>

@Query("SELECT title FROM Feed WHERE url = :feedId")
fun getFeedTitleSynchronously(feedId: String): String

Because database transactions are expensive, in many cases we need to define our database operations so that we only fetch precisely the data that we need and nothing more. In the above two methods, for example, each one returns only one field from the Feed table. For cases where we need multiple fields, I created different variations of our Feed data class, each with a different set of properties depending on our needs.

// Light version of Feed – no website and description
data class FeedLight(
    val url: String,
    var title: String,
    val imageUrl: String?,
    var category: String,
    var unreadCount: Int
)

// Feed without unreadCount
data class FeedManageable(
    val url: String,
    var title: String,
    val website: String,
    val imageUrl: String?,
    val description: String?,
    var category: String
): Serializable

data class FeedIdWithCategory(
    val url: String,
    val category: String
)

The following methods show how the above data classes come into play. In our queries, we specify which fields we want in particular, and Room does the job of mapping them onto our specified return objects. In the case of getFeedIds, we only care about one field, url, which is a String. Note also that each of these methods returns a list of objects (wrapped in LiveData), exactly what we expect from a query that ends with FROM Feed without qualifiers—meaning it will fetch data from all rows of our Feed table.

@Query("SELECT url, title, imageUrl, category, unreadCount FROM Feed")
fun getFeedsLight(): LiveData<List<FeedLight>>

@Query("SELECT url, title, website, imageUrl, description, category FROM Feed")
fun getFeedsManageable(): LiveData<List<FeedManageable>>

@Query("SELECT url FROM Feed")
fun getFeedIds(): LiveData<List<String>>

@Query("SELECT url, category FROM Feed")
fun getFeedIdsWithCategories(): LiveData<List<FeedIdWithCategory>>

Making queries with limited data

Earlier I mentioned updateFeed, which is an operation made simple for us to define and annotate because it accepts an entire Feed as an argument. There's no need to write an explicit SQL query because Room has all the information it needs from a complete Feed object. But there are many cases in which we might want to communicate with our database by passing in only a limited amount of data.

For example, updateFeedTitle accepts only a feedId (or URL) and a new title as arguments, so we include a query: UPDATE Feed SET title = :title WHERE url = :feedId. This means it will update a Feed's title column with the given title, in any row where the Feed's url column is the same as the given feedId.

@Query("UPDATE Feed SET title = :title WHERE url = :feedId")
fun updateFeedTitle(feedId: String, title: String)

The method updateFeedCategory is similar, except it accepts a variable quantity of feedId (URLs) and exactly one category. This means that it will update a Feed's category column with the given category, in each row in which the feedId is found among the given collection of feedId's.

@Query("UPDATE Feed SET category = :category WHERE url IN (:feedId)")
fun updateFeedCategory(vararg feedId: String, category: String)

Combined methods

Methods may also be combined into a single transaction with the @Transaction annotation, in cases where we want them executed at the same time.

@Transaction
fun updateFeedTitleAndCategory(feedId: String, title: String, category: String) {
    updateFeedTitle(feedId, title)
    updateFeedCategory(feedId, category = category)
}

Complex queries

The method incrementFeedUnreadCount, as its name suggests, is meant to increment a Feed's unreadCount property by any given number, which could be positive or negative. It simply takes a feedId as an argument, along with a desired addend, and finds the desired Feed to which the given feedId belongs.

@Query("UPDATE Feed SET unreadCount = (unreadCount + :addend) WHERE url = :feedId")
fun incrementFeedUnreadCount(feedId: String, addend: Int)

But what if we wanted to perform the same operation by passing in an entryId instead of a feedId? Remember that Feed and Entry have a many-to-many relationship; this means we want the operation to apply to all Feeds that are associated with any given Entry. In such a case, we need to consider related data across different tables, which requires a more complex query.

So, we annotate the method incrementFeedUnreadCountByEntry with a nested query: UPDATE Feed SET unreadCount = (unreadCount + :addend) WHERE url IN (SELECT url FROM FeedEntryCrossRef AS _junction INNER JOIN Feed ON (_junction.feedUrl = Feed.url) WHERE _junction.entryUrl = (:entryId)).

First, let's look at the inner query: this time, we select a Feed's url property from our FeedEntryCrossRef table, using it as a junction (and, appropriately, giving it the variable name _junction) between our Feed and Entry entities. The INNER JOIN clause allows us to join the Feed table to our FeedEntryCrossRef table, by establishing an equivalence between Feed.url and _junction.feedUrl. In the resulting joined table, the query selects a Feed's url field from all rows in which the value of entryUrl is equivalent to the given entryId, giving us a collection of one or more Feed URLs.

From here the outer query is made much simpler: it looks for all rows of Feed in which the value of url is present in the above collection, and adds the given addend (which, again, may be positive or negative) to the existing value of unreadCount.

@Query(
        "UPDATE Feed SET unreadCount = (unreadCount + :addend) WHERE url IN " +
            "(SELECT url FROM FeedEntryCrossRef AS _junction " +
            "INNER JOIN Feed ON (_junction.feedUrl = Feed.url) " +
            "WHERE _junction.entryUrl = (:entryId))"
    )
fun incrementFeedUnreadCountByEntry(entryId: String, addend: Int)

All of the above represent database operations that are relevant only to Feeds, though in one instance we have needed to consider our FeedEntryCrossRef table. As I mentioned before, there are other DAOs in the app—I'll write about them next time.


Comments are not enabled (yet?). Please email me if you see anything that interests you.