Channels
#squarelibraries
Title
# squarelibraries
c
Colton Idle
11/16/2023, 10:15 PMAlright... 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 PMHave you enabled write-ahead logging?
🤔 1
c
Colton Idle
11/16/2023, 10:22 PMNope.
d
Derek Ellis
11/16/2023, 10:23 PMThat 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 PMOh 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 PMMy 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 PMOkay, will give this answer a shot
https://stackoverflow.com/a/65426659
s
saket
11/16/2023, 10:44 PMhuh 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 AMapparently 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 AMI'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
enableWriteAheadLoggingSQLiteDatabases
saket
11/17/2023, 4:43 AMif WAL offers significant benefits then why not?
c
Colton Idle
11/17/2023, 6:36 AMAlright, 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")
}
}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()
}
}3 Views