StreetComplete Migrate to multiplatform SQLite library

Attempt to fix #5417.

As mentioned in #6119, the multiplatform Jetpack SQLite library is quite bare-bones, so a lot of stuff that was previously handled by the Android SQLite library now has to be implemented on our side.

Apr 08 '25 20:04 FloEdelmann

Wow, awesome! You did it!

Unfortunately I won't be able to review this until sometime next week probably.

One comment from scrolling through it vertically: The API of the old wrapper interface was basically a copy of the Android one, because that's what was exposed on Android. The new interface doesn't need to copy that as long as it remains well readable.

Don't know regarding transactions. Did you check the syntax for it in the sqlite docs?

El 8 de abril de 2025 22:28:50 CEST, Flo Edelmann @.***> escribió:

@FloEdelmann commented on this pull request.

override fun <T> transaction(block: () -> T): T {

   databaseConnection.execSQL("BEGIN IMMEDIATE TRANSACTION")

```
   try {
```
```
       val result = block()
```

       databaseConnection.execSQL("END TRANSACTION")

```
       return result
```
```
   } catch (t: Throwable) {
```

       databaseConnection.execSQL("ROLLBACK TRANSACTION")

```
       throw t
```
```
   }
```
}

This doesn't work correctly yet: I consistently get an error when downloading data:

Unable to download
android.database.SQLException: Error code: 5, message: cannot commit transaction - SQL statements in progress
  at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.nativeStep(Native Method)
  at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.access$nativeStep(BundledSQLiteStatement.jvmAndroid.kt:1)
  at androidx.sqlite.driver.bundled.BundledSQLiteStatement.step(BundledSQLiteStatement.jvmAndroid.kt:100)
  at androidx.sqlite.SQLite.execSQL(SQLite.kt:56)
  at de.westnordost.streetcomplete.data.StreetCompleteDatabase.transaction(StreetCompleteDatabase.kt:130)
  at de.westnordost.streetcomplete.data.osm.mapdata.WayDao.getAll(WayDao.kt:67)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController$getWays$1.invoke(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController$getWays$1.invoke(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getWays$lambda$26(MapDataCache.kt:265)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.$r8$lambda$ZWQirpDjfvRnK1EiAAaEtzZT8dc(Unknown Source:0)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache$$ExternalSyntheticLambda8.invoke(D8$$SyntheticClass:0)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getElements(MapDataCache.kt:235)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getWays(MapDataCache.kt:265)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.getWays(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.completeMapData(MapDataController.kt:151)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.putAllForBBox(MapDataController.kt:65)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataDownloader$download$2.invokeSuspend(MapDataDownloader.kt:29)

The Android SQLite library seems to have done much more than this function currently does: https://android.googlesource.com/platform/frameworks/base/+/refs/heads/main/core/java/android/database/sqlite/SQLiteSession.java

But I have no idea how much of that is needed. If it's everything, then it's a lot, and I think we should then wait until the multiplatform Jetpack SQLite library has some convenience functions around transactions.

-- Reply to this email directly or view it on GitHub: https://github.com/streetcomplete/StreetComplete/pull/6202#pullrequestreview-2751307760 You are receiving this because your review was requested.

Message ID: @.***>

Apr 09 '25 18:04 westnordost

Ah, of course it doesnt work! Well, at least, this is what I am thinking right now:

A transaction, as any SQL statement is written as one string. When you send off "begin transaction" as one sql statement, that statement in itself is already one transaction (containing that statement). If it doesn't already fail there (because within the statement, you didn't specify the transaction's end, which might be a SQL syntax error), then it will fail at "end transaction" because it's like, "what transaction?" (the two sql statements are not connected in any way).

El 8 de abril de 2025 22:28:50 CEST, Flo Edelmann @.***> escribió:

@FloEdelmann commented on this pull request.

override fun <T> transaction(block: () -> T): T {

   databaseConnection.execSQL("BEGIN IMMEDIATE TRANSACTION")

```
   try {
```
```
       val result = block()
```

       databaseConnection.execSQL("END TRANSACTION")

```
       return result
```
```
   } catch (t: Throwable) {
```

       databaseConnection.execSQL("ROLLBACK TRANSACTION")

```
       throw t
```
```
   }
```
}

This doesn't work correctly yet: I consistently get an error when downloading data:

Unable to download
android.database.SQLException: Error code: 5, message: cannot commit transaction - SQL statements in progress
  at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.nativeStep(Native Method)
  at androidx.sqlite.driver.bundled.BundledSQLiteStatementKt.access$nativeStep(BundledSQLiteStatement.jvmAndroid.kt:1)
  at androidx.sqlite.driver.bundled.BundledSQLiteStatement.step(BundledSQLiteStatement.jvmAndroid.kt:100)
  at androidx.sqlite.SQLite.execSQL(SQLite.kt:56)
  at de.westnordost.streetcomplete.data.StreetCompleteDatabase.transaction(StreetCompleteDatabase.kt:130)
  at de.westnordost.streetcomplete.data.osm.mapdata.WayDao.getAll(WayDao.kt:67)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController$getWays$1.invoke(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController$getWays$1.invoke(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getWays$lambda$26(MapDataCache.kt:265)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.$r8$lambda$ZWQirpDjfvRnK1EiAAaEtzZT8dc(Unknown Source:0)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache$$ExternalSyntheticLambda8.invoke(D8$$SyntheticClass:0)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getElements(MapDataCache.kt:235)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataCache.getWays(MapDataCache.kt:265)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.getWays(MapDataController.kt:193)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.completeMapData(MapDataController.kt:151)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataController.putAllForBBox(MapDataController.kt:65)
  at de.westnordost.streetcomplete.data.osm.mapdata.MapDataDownloader$download$2.invokeSuspend(MapDataDownloader.kt:29)

The Android SQLite library seems to have done much more than this function currently does: https://android.googlesource.com/platform/frameworks/base/+/refs/heads/main/core/java/android/database/sqlite/SQLiteSession.java

But I have no idea how much of that is needed. If it's everything, then it's a lot, and I think we should then wait until the multiplatform Jetpack SQLite library has some convenience functions around transactions.

-- Reply to this email directly or view it on GitHub: https://github.com/streetcomplete/StreetComplete/pull/6202#pullrequestreview-2751307760 You are receiving this because your review was requested.

Message ID: @.***>

Apr 10 '25 15:04 westnordost