Constraint foreign key exception when having conflicting operations inside one transaction about kabin HOT 5 CLOSED

According to this answer and many others, we need to make these changes:

1. Make all constraints DEFERRED inside one transaction
2. Adjust insert order for compounds and lists and call notifyQuery only after all SQL operations
3. Wrap database operations such as create, migrate, clear and drop inside a transaction and turn off foreign keys using PRAGMA

from kabin.

1. Make all constraints DEFERRED inside one transaction

In order to make the first point work correctly, all foreign keys should be created as DEFERRABLE INITIALLY IMMEDIATE by default, and DEFERRABLE INITIALLY DEFERRED

Changed here: 022636f

This means that projects adopting these changes, should update their database version

from kabin.

1. Make all constraints DEFERRED inside one transaction

Some deeper research showed that room uses PRAGMA syntax to make foreign keys DEFERRED inside a transaction, which seems like a better option than SET CONSTRAINTS ALL DEFERRED since it's rolled back once the transaction ends, and you don't have to do it manually.

More info: https://sqlite.org/pragma.html#pragma_defer_foreign_keys

from kabin.

1- Make all constraints DEFERRED inside one transaction
3- Wrap database operations such as create, migrate, clear and drop inside a transaction and turn off foreign keys using PRAGMA

In order to achieve this type of control over foreign keys, KabinDatabaseConfiguration had to be changed to include more flags. configuration is now passed to Database, Scheme and Dao, which means this commit might introduce breaking changes for some projects.

Changed here: 132af15

from kabin.

2. Adjust insert order for compounds and lists and call notifyQuery only after all SQL operations

Further research showed that notifyQuery inside one transaction delays triggers until the transaction is successful, therefore, these changes were postponed until future refactoring of the compiler and processor modules.

from kabin.