When I use SQLDelight (1.5.3) I do `database.Schem...

# squarelibraries

[Humphrey]

When I use SQLDelight (1.5.3) I do database.Schema.create(driver) . I'm using a file based database. Second time I start my application the database already exists. I don't want it to recreate the tables. What is a good approach for that?

[jw]

https://github.com/JakeWharton/dodo/blob/e64a6d12afcadba674a726cce37a1dd20414bf06/src/main/kotlin/com/jakewharton/dodo/db.kt#L13-L36

[Humphrey]

I've looked at the code but don't understand what your doing precisely. Maybe you can elaborate and explain what you're doing?

[mbonnin]

Uses the user_version PRAGMA to store the version in the DB itself and handle the migrations manually

[mbonnin]

Basically, the second time, you see there's an user_version already and instead of creating the DB, you update it

[Humphrey]

Thanks that little information helped me through.. Found one more easier to understand https://github.com/paug/fraugdroid/blob/master/src/main/kotlin/fraug/droid/features/DatabaseHolder.kt#L13

[jw]

That's basically the same exact code but with slightly more abstraction. And I'm not sure their means of accessing the version is safe when the pragma is absent.

[Humphrey]

Well I tried this code
sqlCursor.getLong(0)!!.toInt()
And it always gave me a 0 whether the database is absent or not.

[jw]

you're probably just getting lucky because you're only running on one platform

[Humphrey]

Oh okay, I'll do the null check. Now I'm running it on Linux and the idea is to be run on Windows and Mac later on.

[jw]

i mean you don't have to if it doesn't seem like you need it. i just distinctly remember a crash because of this.

[Humphrey]

Is this a good implementation of the DatabaseHelper? Or am I missing something?

import com.squareup.sqldelight.sqlite.driver.JdbcSqliteDriver

const val VERSION_PRAGMA = "user_version"

class DbHelper(path: String = "") {

    private val driver by lazy { JdbcSqliteDriver(JdbcSqliteDriver.IN_MEMORY + path) }
    private var version: Int
        get() {
            driver.executeQuery(null, "PRAGMA $VERSION_PRAGMA", 0).use {
                if (it.next()) {
                    return it.getLong(0)?.toInt() ?: 0
                }
                return 0
            }
        }
        set(version) {
            driver.execute(null, "PRAGMA $VERSION_PRAGMA = $version;)", 0)
        }

    val database by lazy {
        val currentVersion = version
        if (currentVersion == 0) {
            Database.Schema.create(driver)
            version = 1
            println("init: created tables, setVersion to 1")
        } else {
            val schemaVersion = Database.Schema.version
            if (currentVersion < schemaVersion) {
                Database.Schema.migrate(driver, currentVersion, schemaVersion)
                version = schemaVersion
                println("init: migrated from $currentVersion to $schemaVersion")
            }
        }
        Database(driver)
    }

}

[Humphrey]

Instead of setting the version to 1 I need to set it to the schemaVersion. I just saw that mistake now. But for the rest it should be good now?