https://kotlinlang.org logo
Title
k

kpgalligan

10/16/2020, 7:50 PM
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.
v

Venkat , Bangalore , India

10/19/2020, 12:47 PM
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! 🙂