I use SQLDelight 1.5.4 on JVM and I want to avoid ...

# touchlab-tools

[Stefan Oltmann]

I use SQLDelight 1.5.4 on JVM and I want to avoid primary key constraint violations in my geolocation cache.

First I tried to append a WHERE NOT EXISTS to my INSERT INTO LocationDb VALUES ?; , but that seems to be not supported.

Then I tried to put that into an transaction like this:

queries.transaction {

    val locationDb = queries.find(
        latitude = gpsCoordinates.latitude,
        longitude = gpsCoordinates.longitude
    ).executeAsOneOrNull()

    if (locationDb == null) {

        queries.add(
            LocationDb(
                latitude = gpsCoordinates.latitude,
                longitude = gpsCoordinates.longitude,
                locationName = location.name,
                city = location.city,
                state = location.state,
                country = location.country
            )
        )
    }
}

This results in [SQLITE_BUSY] The database file is locked (database is locked)

I found online that this can be due to multiple connections and the recommendation is to have just one. I don't know how many I have using the SQLiteJDBCDriver.

I create the drive like this:

actual fun createDriver(): SqlDriver {

    val filePath = getFilePath()

    val driver = JdbcSqliteDriver(
        "jdbc:sqlite:$filePath",
        Properties().apply {

            /* Use foreign keys and cascade deletions. */
            put("foreign_keys", "true")
        }
    )

    PhotosDatabase.Schema.create(driver)

    return driver
}

I only have one instance of the Database proxy...

How can I do my insert without primary key violation?
How can I set the max connections?

[eygraber]

I think touchlab deals with the native drivers. For JVM you're probably better off in #squarelibraries

[eygraber]

In any case, what does queries.find / queries.add look like?

[Stefan Oltmann]

CREATE TABLE IF NOT EXISTS LocationDb (

  latitude REAL NOT NULL,
  longitude REAL NOT NULL,
  locationName TEXT,
  city TEXT,
  state TEXT,
  country TEXT NOT NULL,

  -- This combination makes up the id
  PRIMARY KEY (latitude, longitude)
);

find:
SELECT locationName, city, state, country
FROM LocationDb
WHERE latitude = ? AND longitude = ?;

add:
INSERT INTO LocationDb VALUES ?;

[eygraber]

I don't know why you're getting that error, but I believe the reason WHERE NOT EXISTS isn't working is because SQLite doesn't support that syntax for an INSERT.

Try INSERT OR IGNORE INTO LocationDb...

[Stefan Oltmann]

That sounds like the syntax I was looking for 🙂 Thank you. 🙏