Hi @kpgalligan Good morning.. Found the root cause. issue is "SELECT last_insert_rowid();" is returning 0 when called to insert a record on parent table and then trying to insert record in child with 0 as FK.
But, this was not the case for the last one year so far with the sqldelight version:1.3.0 ( which is pretty old now) but never got a requirement to upgrade it unless the recent request of encrypting DB now.
(app is not live yet :)
just to reproduce the issue in latest "KaMPKitSQLCipher", i made some dummy relation in Table.sq
CREATE TABLE Breed (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
favorite INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE BeedDetails (
id INTEGER PRIMARY KEY AUTOINCREMENT ,
momentId INTEGER ,
FOREIGN KEY (momentId) REFERENCES Breed(id) ON DELETE CASCADE
);
and below is the change i made, which is working FINE.
dbRef.transactionWithContext(backgroundDispatcher) {
breedNames.forEach { name ->
val idd=dbRef.tableQueries.insertBreed(null, name, 0)
val insertedBreedId = dbRef.tableQueries.rowid().executeAsOne()
dbRef.tableQueries.insertBreedDetails(insertedBreedId)
}
}
Just to reproduce the issue ( Single threaded model in ios) i just commented transactionWithContext changes and saw that it last_insert_rowid is returning 0.
//dbRef.transactionWithContext(backgroundDispatcher) {
breedNames.forEach { name ->
val idd=dbRef.tableQueries.insertBreed(null, name, 0)
val insertedBreedId = dbRef.tableQueries.rowid().executeAsOne()
dbRef.tableQueries.insertBreedDetails(insertedBreedId)
}
// }
So, then i digged a bit, and saw that below two connections types.
override fun createMultiThreadedConnection(): DatabaseConnection {
return ConcurrentDatabaseConnection(createConnection()).freeze()
}
override fun createSingleThreadedConnection(): DatabaseConnection {
return SingleThreadDatabaseConnection(createConnection())
}
looks like "createSingleThreadedConnection" is not in use?.
So it seems like now each query runs in its own context and not one-by-one on th same thread.
but not sure how to enfore to use createSingleThreadedConnection.
just thought of sharing! 🙂