SQLDelight in Android: Getting Started

Transcript: 14. Integrate with Android Paging

The Android Paging library helps developers deal with segmented access to long lists of data. Assuming that the power users of our bug collector app could aggregate hundreds and thousands of collections over time, the performance of the app may start to degrade over time when all data is pulled from the database at the same time.

And eventually, they could face the dreaded OutOfMemory erorr.

With Android Paging, these large tables are split into chunks to minimize the memory consumption at any given time. SQLDelight has an integration point with Android Paging, so let’s explore how it works.

To get started, open the app module’s build script and find the dependency declaration for the Android driver. Below it, add a new dependency on the Android Paging extension for SQLDelight.

Furthermore, we need to add a dependency on the Android Paging library itself, so add its artifact coordinates below as well, then sync the project.

dependencies {
    implementation "com.squareup.sqldelight:android-paging-extensions:$sqldelightVersion"

    // Android Paging
    implementation "androidx.paging:paging-runtime-ktx:2.1.2"
}

Once again, we will refactor the collection list screen of the app. In order to do this with Paging, a few changes need to be made to the functions available through the collection table.

Let’s open its script file. SQLDelight needs two functions for the connection to Paging: a query that counts the number of rows in the table, and a query that selects a list of rows based on a given limit and offset. Above the existing all() function, add a new one called ‘count’ and write out an SQL query that counts all the rows of the collection table.

Afterwards, modify the all() function below and add a LIMIT and OFFSET statement to it, passing in two new parameters as the values. Again, this setup is required by the SQLDelight extension function - without it, the connection to Android Paging doesn’t work.

+count:
+SELECT count(*) FROM collection;

all:
-SELECT * FROM collection;
+SELECT * FROM collection
+LIMIT :limit OFFSET :offset;

Next up, we will connect these functions in the DatabaseRepository. Open it up and find the compilation error near the top.

The listCollections() method will no longer return a Query object, but instead we will create a DataSource Factory for it.

This is an API of the Android Paging library and has two type parameters: an identifier for elements in the source, which can be an Int, and the element type itself - in our case, Collection.

-fun listCollections(): Query<Collection> {
+fun listCollections(): DataSource.Factory<Int, Collection> {
    return database.collectionQueries.all()
}

Clear out the method body and replace the return statement with this: A newly created QueryDataSourceFactory object. This class is an implementation of the DataSource Factory and comes from the SQLDelight extension library - this right here is the handoff point between these two worlds!

fun listCollections(): DataSource.Factory<Int, Collection> {
-    return database.collectionQueries.all()
+    return QueryDataSourceFactory(
+
+    )    
}

This factory accepts three parameters in the constructor, let’s fill them out one by one. First, the queryProvider. This is a lambda that needs to return the SELECT query which pulls the actual objects from the table.

This lambda has two numerical parameters: limit and offset. These can be piped straight to the all() method, which now accepts the same two parameters due to our recent change! In fact, this structure lends itself to further simplification, so hit Option+Enter on it and convert the lambda to a method reference.

The second parameter is the countQuery - a reference to the Query function which allows the data source to check the number of items in the table. Connect it to the new count() function of the CollectionQueries object.

Finally, the transacter object. This is the workhorse object which will execute the SQL transactions needed under the hood. Again, this can be the CollectionQueries object directly. With these three parameters out of the way, the repository changes are complete.

fun listCollections(): DataSource.Factory<Int, Collection> {
    return QueryDataSourceFactory(
+        queryProvider = database.collectionQueries::all,
+        countQuery = database.collectionQueries.count(),
+        transacter = database.collectionQueries
    )
}

Next up, you guessed it: The ViewModel. Open the CollectionListViewModel and watch some code turn red. Similar to the other integration points we have investigated, the listener field does not need to be here anymore, since the repository doesn’t return the raw Query type. any longer: get rid of it.

-private val collectionQueryListener = object : Query.Listener {
-    override fun queryResultsChanged() {
-        refreshState()
-    }
-}

Furthermore, we will transform the DataSource passed in from the repository into a LiveData object, as per the architecture recommendations by Google. To do this, find the collectionQuery field up here and add a call to the extension function ‘toLiveData()’ at the end.

If your IDE cannot resolve this function, make sure that the dependency declaration in the build script uses the “ktx” version of the Android Paging library - it’s only available in there. The required parameter of the LiveData conversion method expresses the number of rows per “page” to load from the database.

This can be any positive integer value and I will arbitrarily choose 10 here.

-private val collectionQuery = repository.listCollections()
+private val collectionQuery = repository.listCollections().toLiveData(pageSize = 3)

Next, clear out the contents of the constructor and replace them with a call to observe the LiveData field from above. This LiveData will be observed until its onCleared() method is called.

Therefore, we need to declare the observer for this LiveData as a private field, such that it can be referenced from multiple methods. The Observer’s type parameter will be PagedList, which is the underlying type contained in that LiveData field.

Inside its block, the UI state of the screen will be updated with whatever the new PagedList is, so copy the contents of the refreshState() method from below and paste them in here.

Update the value to use the parameter and finally, use this observer as the argument to observeForever() in the constructor.

+private val observer = Observer<PagedList<Collection>> { collections ->
+    _state.value = State.Result(
+        collections = collections
+    )
+}

init {
-    refreshState()
-
-    collectionQuery.addListener(collectionQueryListener)
+    collectionQuery.observeForever(observer)
}

Finally, to clear this observer again, remove everything from onCleared() and remove the observer from the LiveData like so.

override fun onCleared() {
-    collectionQuery.removeListener(collectionQueryListener)
+    collectionQuery.removeObserver(observer)
}

The old refreshState() method is no longer needed, so delete it as well to complete this step.

-private fun refreshState() {
-    _state.value = State.Result(
-        collections = collectionQuery.executeAsList()
-    )
-}

There is one final adjustment that needs to be made inside the ViewModel and it has to do with the UI State class up here. Up until this point, the Result class has expressed the list of collections as an actual Kotlin List.

However with Android Paging, there is an important distinction between a generic list and a PagedList. Update the type of the collections field from List to PagedList. This will be necessary once we update the RecyclerView’s adapter in the UI layer in a second.

sealed class State {
    object Loading : State()
-    data class Result(val collections: List<Collection>): State()
+    data class Result(val collections: PagedList<Collection>): State()
}

Speaking of which, let’s open the CollectionListAdapter class. In Android Paging, there is a specialized parent class for paginated lists, and its surprising name is PagedListAdapter. Update the type signature so that our CollectionListAdapter no longer inherits from ListAdapter, but PagedListAdapter instead.

With this change in place, scroll down to the onBindViewHolder() method and check out the error here. With a paged list, it’s possible to get a null value whenever an item hasn’t been loaded yet, so the value of the item variable down here is now nullable, where it wasn’t before.

To fix this, wrap these assignments in a conditional null check and execute them only if item is not null. Then, add an else block and reset these views to some placeholder values. I’m using three dots for the ID view and null for the rest.

-class CollectionListAdapter : ListAdapter<Collection, CollectionHolder>(diffCallback) {
+class CollectionListAdapter : PagedListAdapter<Collection, CollectionHolder>(diffCallback) {

  override fun onBindViewHolder(holder: CollectionHolder, position: Int) {
    // Ensure a formatter is available to format the collection's timestamp.
    // Create the object when the first ViewHolder is being bound
    val formatter = this.formatter ?: holder.itemView.resources
      .getDateTimeFormatter(R.string.timestamp_format)
      .also { this.formatter = it }

    // Style
    val item = getItem(position)
-    holder.idTextView.text = "#${item.collectionId}"
-    holder.nameTextView.text = item.name
-    holder.creationTextView.text = item.creationTime.format(formatter)
-
-    // Listeners
-    holder.layout.setOnClickListener { _clickEvents.offer(item.collectionId) }
+
+    if (item != null) {
+        holder.idTextView.text = "#${item.collectionId}"
+        holder.nameTextView.text = item.name
+        holder.creationTextView.text = item.creationTime.format(formatter)
+        holder.layout.setOnClickListener { _clickEvents.offer(item.collectionId) }
+    } else {
+        holder.idTextView.text = "..."
+        holder.nameTextView.text = null
+        holder.creationTextView.text = null
+        holder.layout.setOnClickListener(null)
+    }
}

This completes the connection of SQLDelight to the world of Android Paging. Hit the Run button to launch the app and observe once more that the screen still works correctly. With Paging as the backbone here, the loading of this particular screen has been optimized by a huge amount. Well done, us!