AndroidX Paging¶
To use SQLDelight with Android's Paging 3 Library add a dependency on the paging extension artifact.
dependencies {
implementation("app.cash.sqldelight:androidx-paging3-extensions:2.0.0-rc02")
}
dependencies {
implementation "app.cash.sqldelight:androidx-paging3-extensions:2.0.0-rc02"
}
SQLDelight offers two methods for paging data — offset based paging and keyset paging.
Offset Based Paging¶
Offset paging achieves paged results using OFFSET
and LIMIT
clauses. Creating a PagingSource
that performs offset based paging requires a count query as well as the paged query.
countPlayers:
SELECT count(*) FROM hockeyPlayer;
players:
SELECT *
FROM hockeyPlayer
LIMIT :limit OFFSET :offset;
import app.cash.sqldelight.android.paging3.QueryPagingSource
val pagingSource: PagingSource = QueryPagingSource(
countQuery = playerQueries.countPlayers(),
transacter = playerQueries,
context = Dispatchers.IO,
queryProvider = playerQueries::players,
)
By default, queries are performed on Dispatchers.IO
if no context is specified. Consumers expecting to use RxJava's Scheduler
to perform queries should use the Scheduler.asCoroutineDispatcher
extension function.
Keyset Paging¶
Offset paging is simple and easy to maintain. Unfortunately it performs poorly on large datasets. The OFFSET
clause of a SQL statement really just drops already executed rows in a SQL query. Therefore, as the number to OFFSET
grows, so does the amount of time it takes to execute your query. To overcome this, SQLDelight offers a "keyset paging" implementation of PagingSource
. Rather than querying an entire dataset and inefficiently dropping the first OFFSET
elements, keyset paging operates using a unique column to restrict the bounds of your queries. This performs better at the expense of higher developer maintenance.
The queryProvider
callback that this paging source accepts has two parameters — a beginInclusive
non-null unique Key
as well as an endExclusive
nullable unique Key?
. An example of the core paging query is shown below.
keyedQuery:
SELECT * FROM hockeyPlayer
WHERE id >= :beginInclusive AND (id < :endExclusive OR :endExclusive IS NULL)
ORDER BY id ASC;
Queries used in keyset paging must have a unique ordering like shown above.
Both beginInclusive
and endExclusive
are pre-calculated keys that act as page boundaries. Page sizes are established when pre-calculating page boundaries. The pageBoundariesProvider
callback takes an anchor: Key?
parameter as well as a limit: Int?
parameter. An example query that pre-calculates page boundaries is shown below.
pageBoundaries:
SELECT id
FROM (
SELECT
id,
CASE
WHEN ((row_number() OVER(ORDER BY id ASC) - 0) % :limit) = 0 THEN 1
WHEN id = :anchor THEN 1
ELSE 0
END page_boundary;
FROM hockeyPlayer
ORDER BY id ASC
)
WHERE page_boundary = 1;
Pre-calculating page boundaries of a SQL query will likely require SQLite Window Functions. Window functions were introduced in SQLite version 3.25.0, and therefore are not available by default until Android API 30. To use keyset paging SQLDelight recommends either setting minApi 30
or bundling your own SQLite version. The Requery organization offers an up-to-date distribution of SQLite as a standalone library.
The AndroidX paging library allows for the first page fetch to differ in size from the subsequent page fetches with PagingConfig.initialLoadSize
. This functionality should be avoided, as the pageBoundariesProvider
callback is invoked a single time on the first page fetch. Failing to have matching PagingConifg.initialLoadSize
and PagingConfig.pageSize
will result in unexpected page boundary generation.
This paging source does not support jumping.
To create this paging source, use the QueryPagingSource
factory function.
import app.cash.sqldelight.android.paging3.QueryPagingSource
val keyedSource = QueryPagingSource(
transacter = playerQueries,
context = Dispatchers.IO,
pageBoundariesProvider = playerQueries::pageBoundaries,
queryProvider = playerQueries::keyedQuery,
)
By default, queries are performed on Dispatchers.IO
if no context is specified. Consumers expecting to use RxJava's Scheduler
to perform queries should use the Scheduler.asCoroutineDispatcher
extension function.