Alright... riddle me this. I'm using SQLDelight and I suppose I'm missing something fundamental here...

# squarelibraries

[Colton Idle]

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

[Colton Idle]

Query:

suspend fun doesItemExist(id: String): Boolean {
    var result = false
    withContext(ioDispatcher) {
      val item = myQueries.selectItemById(id).executeAsOneOrNull()
      if (card != null) {
        result = true
      }
    }
    return result
  }

[Colton Idle]

Large streaming insert:

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!!)
        }
      }
    }

[Colton Idle]

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.

[Derek Ellis]

Have you enabled write-ahead logging?

[Colton Idle]

Nope.

[Derek Ellis]

That would be it
By default SQLite doesn't allow concurrent reads/writes, but with WAL enabled you can read while you write

[Colton Idle]

Oh really? Hot damn. I thought it did.

[Colton Idle]

I wonder if there are any other side effects to doing this, or if this is a pretty standard route.

[Colton Idle]

I swear whenever I try to google anything + sqldelight I just get terrible answers. lol

[Derek Ellis]

My understanding is that it increases memory usage, but that's generally not a concern in most environments

[Colton Idle]

Okay, will give this answer a shot

https://stackoverflow.com/a/65426659

[saket]

huh I was under the assumption that sqldelight enables WAL by default

[saket]

should we do it @Derek Ellis?

[Colton Idle]

apparently it does on iOS?

[Colton Idle]

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

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()
          }
        }

[Derek Ellis]

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.

[Derek Ellis]

With Android, you should be able to just call enableWriteAheadLogging on the SQLiteDatabase object, right?
https://source.android.com/docs/core/perf/compatibility-wal

[saket]

if WAL offers significant benefits then why not?

[Colton Idle]

Alright, hopefully this is the right way

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

[Colton Idle]

Oh. Can I just do

MyDb.Schema.version.toInt() 👀

[Colton Idle]

OH! SQLDelight ships with it's own callback!

object : AndroidSqliteDriver.Callback(schema = MagnarDb.Schema) {
            override fun onOpen(db: SupportSQLiteDatabase) {
              db.enableWriteAheadLogging()
            }
          }

idk how i missed that. wow