This is a little out of “kampkit” territory, but I...

# touchlab-tools

[kpgalligan]

This is a little out of “kampkit” territory, but I guess is this on a new db? This error basically says you’re violating foreign key constraints.

[Venkat , Bangalore , India]

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! 🙂