Better Documentation on how to integrate Requery w/ SqlDelight on Android

[dptsolutions]

Description

Pretty much the title. Ultimately, I figured it out, and you just have to do a couple of things to use Requery w/ SqlDelight, and get the benefits of the newer SQLite features in the version packaged by Requery:

1. Update your AndroidSqliteDriver instantiation to add the factory parameter and set it to an instance of RequerySQLiteOpenHelperFactory
2. In your Gradle script, add a dialect entry to your database's config block, and set that line to a newer version of the SQLite dialect that is closest to the version of SQLite packaged up by Requery.

The current documentation provides some hints to guide you to the solution, but they are spread out across multiple pages in the docs, and some pages:

• The Keyset Paging section in the AndroidX Paging mentions bundling your own SQLite version using Requery. But it doesn't mention anything about the dialect setting needed to use a dialect version higher than the one set by minSDK, nor how to configure Requery to be used with SQLDelight.
• The SQLite Versions section in the Getting Started page mentions how dialect is tied to minSdkVersion, but makes no mention that you can bundle your own SQLite version and get the newer dialect features in SQLite.
• The Dialect property section of the Gradle page reiterates that the dialect is selected by default by minSdkVersion, and similarly makes no mention of Requery or how it can be overridden if you package your own newer version of SQLite.

So I propose that the documentation can be updated as follows:

1. In the SQLite Versions section in the Getting Started page, update the existing text to say something like "By default for Android projects, the SQLDelight Gradle Plugin..." and keep the existing paragraph as is.
2. Add a second paragraph to the section saying something like "If you wish to use newer SQLite dialect features on older versions of Android, such as Keyset Paging, you can bundle your own SQLite version. The Requery organization offers an up-to-date distribution of SQLite as a standalone library." And then below this paragraph, show an example of how to configure SQLDelight to use Requery:
   1. Follow Requery's instructions to add it to your project.
   2. Add an example of configuring the AndroidSqliteDriver (expanding on the example earlier in the Getting Started page) by adding the factory parameter and setting it to an instance of RequerySQLiteOpenHelperFactory.
   3. Add an example of configuring the SQLDelight Gradle plugin to use the SQLite dialect version supported by SQLDelight that is closest to the version brought in by Requery, and linking out to the dialect section of the docs for the details of all supported dialect versions.
   4. Update the dialect Gradle property docs to mention that the automatic selection is default, but if you bundle your own SQLite you can use this property to override the default and use a newer dialect, and link out to the updated SQLite Versions section of the Android Getting Started page.
   5. In the Keyset Paging section of the AndroidX Paging extension page, Remove the "The Requery organization offers an up-to-date distribution..." line, and change the "bundling your own SQLite version" text in the previous sentence to be a link to the new paragraph in the Getting Started page mentioned above.

[dptsolutions]

Totally willing to do this and submit a PR!

[mainrs]

Hey! I thank you for opening this issue! This has given me so many headaches. It never worked to configure the project in such a way that I can use the keyedQuery feature.

I enabled the 3.35 dialect in a KMP project, added requery to my Android dependencies, but the code this does not compile. It's always complaining about syntax issues regarding the keyedQuery from the official documentation:

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 News
  ORDER BY id ASC
)
WHERE page_boundary = 1;

Compiling with dialect app.cash.sqldelight.dialects.sqlite_3_38.SqliteDialect

<project>/shared/data-local/src/commonMain/sqldelight/shared/data/local/News.sq: (12, 31): ')' or <expr> expected, got 'ORDER'
07    SELECT id
08    FROM (
09      SELECT
10        id,
11        CASE
12          WHEN ((row_number() OVER(ORDER BY id ASC) - 0) % :limit) = 0 THEN 1
                                     ^^^^^
13          WHEN id = :anchor THEN 1
14          ELSE 0
15        END page_boundary

I've seen other people voice the same issue on the GitHub issue tracker here. But none of them provided a solution that worked for me.

[brinsche]

I think this is tracked in https://github.com/cashapp/sqldelight/issues/2799 and just broken at the moment :/