https://kotlinlang.org logo
#squarelibraries
Title
# squarelibraries
c

Colton Idle

11/16/2023, 10:15 PM
Alright... riddle me this. I'm using SQLDelight and I suppose I'm missing something fundamental here. Querying my db (to see if a value exists) while I have a large transaction ongoing prevents the query. Am I doing something blatantly wrong here? Code in thread
Query:
Copy code
suspend fun doesItemExist(id: String): Boolean {
    var result = false
    withContext(ioDispatcher) {
      val item = myQueries.selectItemById(id).executeAsOneOrNull()
      if (card != null) {
        result = true
      }
    }
    return result
  }
Large streaming insert:
Copy code
suspend fun deleteAllThenInsertAllItems(bufferedReader: BufferedReader) =
    withContext(isDispatcher) {
      myQueries.transaction {
        myQueries.deleteAll()
        var currentLine: String? = null
        while (bufferedReader.readLine().also { currentLine = it } != null) {
          myQueries.insert(currentLine!!)
        }
      }
    }
If I was making two writes then I'd expect the transaction to finish first. But I'm really not sure why trying to read from the db is an issue.
d

Derek Ellis

11/16/2023, 10:22 PM
Have you enabled write-ahead logging?
🤔 1
c

Colton Idle

11/16/2023, 10:22 PM
Nope.
d

Derek Ellis

11/16/2023, 10:23 PM
That would be it By default SQLite doesn't allow concurrent reads/writes, but with WAL enabled you can read while you write
c

Colton Idle

11/16/2023, 10:25 PM
Oh really? Hot damn. I thought it did.
I wonder if there are any other side effects to doing this, or if this is a pretty standard route.
I swear whenever I try to google anything + sqldelight I just get terrible answers. lol
d

Derek Ellis

11/16/2023, 10:33 PM
My understanding is that it increases memory usage, but that's generally not a concern in most environments
c

Colton Idle

11/16/2023, 10:34 PM
Okay, will give this answer a shot https://stackoverflow.com/a/65426659
s

saket

11/16/2023, 10:44 PM
huh I was under the assumption that sqldelight enables WAL by default
should we do it @Derek Ellis?
c

Colton Idle

11/17/2023, 12:24 AM
apparently it does on iOS?
So I seem to have no idea what im doing. and google + sqldelight docs + sqldelight issue tracker is leaving me empty handed here. Im still iterating. but ive gotten this so far
Copy code
callback = object : SupportSQLiteOpenHelper.Callback() {
          override fun onCreate(db: SupportSQLiteDatabase) {
            super.onConfigure(db)
            setPragma(db, "JOURNAL_MODE = WAL")
            setPragma(db, "SYNCHRONOUS = 2")
          }

          override fun onUpgrade(db: SupportSQLiteDatabase, oldVersion: Int, newVersion: Int) {
          }

          private fun setPragma(db: SupportSQLiteDatabase, pragma: String) {
            val cursor = db.query("PRAGMA $pragma")
            cursor.moveToFirst()
            cursor.close()
          }
        }
d

Derek Ellis

11/17/2023, 3:01 AM
I'm not sure if SQLDelight should enable this by default since it usually defers to whatever underlying driver/database you're using with it.
plus1 1
With Android, you should be able to just call
enableWriteAheadLogging
on the
SQLiteDatabase
object, right? https://source.android.com/docs/core/perf/compatibility-wal
s

saket

11/17/2023, 4:43 AM
if WAL offers significant benefits then why not?
c

Colton Idle

11/17/2023, 6:36 AM
Alright, hopefully this is the right way
Copy code
callback = object : SupportSQLiteOpenHelper.Callback(1) {
            override fun onCreate(db: SupportSQLiteDatabase) {
              db.enableWriteAheadLogging()
            }
            override fun onUpgrade(db: SupportSQLiteDatabase, oldVersion: Int, newVersion: Int) {
              TODO("Not yet implemented")
            }
          }
I am a bit confused on why i need to specify a version number as a constructor arg to SupportSQLiteOpenHelper.Callback
Oh. Can I just do
MyDb.Schema.version.toInt()
👀
OH! SQLDelight ships with it's own callback!
Copy code
object : AndroidSqliteDriver.Callback(schema = MagnarDb.Schema) {
            override fun onOpen(db: SupportSQLiteDatabase) {
              db.enableWriteAheadLogging()
            }
          }
idk how i missed that. wow
3 Views