0

We have come to a condition where we want to use the "lead" and "lag" functions in android but they don't function unless your sqlite version is at least 3.2. Is it possible for our project to use the newer sqllite versions through Jetpack, and if so, how?

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
John Lord
  • 1,941
  • 12
  • 27
  • The [official documentation](https://developer.android.com/reference/android/database/sqlite/package-summary.html) says that android uses a superior version from the start. – Ricardo A. May 30 '19 at 20:49
  • 1
    BTW, it appears that `lead()` and `lag()` were [added in 3.25](https://stackoverflow.com/questions/56385084/what-version-of-sqlite-added-support-for-the-lead-and-lag-functions#comment99370476_56385084). – CommonsWare May 30 '19 at 20:59
  • we are forced to target android 6.0. We lease tablets to truck drivers and some of them are still running 5.1 on them. – John Lord May 30 '19 at 21:00

1 Answers1

2

Is it possible for our project to use the newer sqllite versions through Jetpack

Jetpack does not directly give you a new SQLite version.

However, if you are using Room, you can use a custom implementation of the SupportSQLiteDatabase family of interfaces. By default, Room uses its own Framework... versions, which use the built-in SQLite. But, on your Room.DatabaseBuilder, you can call openHelperFactory() and supply a SupportSQLiteOpenHelper.Factory that uses another implementation.

There are at least two of these that you could use, that are on fairly current versions of SQLite:

Both require you to package binaries of SQLite with your app (and cryptography libraries in the case of SQLCipher for Android). This will significantly increase the size of your APK.

CommonsWare
  • 986,068
  • 189
  • 2,389
  • 2,491
  • thank you. This was good and needed information. Our database has hundreds of tables so converting to room is off the table. We'll just work around not having lead/lag for now. – John Lord May 30 '19 at 20:59
  • 1
    @JohnLord: Another possibility is to use the `Support...` classes directly. `SupportSQLiteDatabase` has an API that is similar to, though not identical to, the framework `SQLiteDatabase`. – CommonsWare May 30 '19 at 21:00
  • thanks. We discovered though that the functions listed that we wanted haven't actually made it into the newest android releases yet anyway so the point is moot. – John Lord May 31 '19 at 15:37
  • @JohnLord: I think you misunderstood. I am saying to use the Requery or SQLCipher for Android libraries directly, rather than via Room. They provide APIs that are similar to `SQLiteDatabase`. Both are based on newer SQLite versions that should have your listed functions. This would be a less tedious conversion than going all the way to Room, though it would still require some conversion, and you still wind up with much larger APKs. All that being said, if you can skip those functions, that's probably the simpler solution overall. – CommonsWare May 31 '19 at 15:43
  • no I understood. The version of sqlite that includes those functions isn't released yet on ANY android devices. It's still in development. In any case, if it makes our app larger, i couldn't use it anyway. They are pretty anal about it since our clients pay for data. – John Lord May 31 '19 at 20:56