tamimattafi/kabin

A Kotlin Multiplatform library for database storage inspired by Room

kabin's Issues

Bug: db connection on android doesn't support ForeignKey

As said in documentation, connection should enable foreign key support in runtime.
Please, add configuration for this or make this behavior enabled by default

Primary keys are ignored, if the columns are inside an embedded class

Problem:

PRIMARY KEY declaration is missing from table creation query, if the primary key column is inside an @Embedded class, and it was assigned through primaryKeys inside @Entity annotation

Example

Here's an Entity that has an embedded identity class, which contains the column used as primary key:

@Entity(
    primaryKeys = ["identity_id"]
)
data class FileEntity(
    @Embedded("identity_")
    val identity: FileIdentityLocal,
    val fileReferenceBase64: String? = null,
    val date: Int? = null,
    val size: Int? = null
)

The generated query looks like this:

CREATE TABLE IF NOT EXISTS documententity
(
     identity_id               TEXT NOT NULL,
     identity_type             TEXT NOT NULL,
     identity_mimetype         TEXT,
     identity_fallbackmimetype TEXT,
     identity_volumeid         INTEGER,
     identity_accesshash       INTEGER,
     identity_datacenterid     INTEGER,
     filereferencebase64       TEXT,
     date                      INTEGER,
     size                      INTEGER
) 

As we can see, PRIMARY KEY is nowhere to be seen

iOS: Creating scheme throws SQLiteExceptionErrorCode: transaction within a transaction

Problem

After updating to 0.1.0-alpha06, iOS apps started failing during scheme creation:

SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction

Stack Trace:

co.touchlab.sqliter.interop.SQLiteExceptionErrorCode: Sqlite operation failure cannot start a transaction within a transaction
    at 0   ComposeApp                          0x108a31cef        kfun:kotlin.Throwable#<init>(kotlin.String?){} + 119 
    at 1   ComposeApp                          0x108a2b05f        kfun:kotlin.Exception#<init>(kotlin.String?){} + 115 
    at 2   ComposeApp                          0x1089e933b        kfun:co.touchlab.sqliter.interop.SQLiteException#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig){} + 115 
    at 3   ComposeApp                          0x1089e942f        kfun:co.touchlab.sqliter.interop.SQLiteExceptionErrorCode#<init>(kotlin.String;co.touchlab.sqliter.interop.SqliteDatabaseConfig;kotlin.Int){} + 199 
    at 4   ComposeApp                          0x1089e54a3        kfun:co.touchlab.sqliter.interop.ActualSqliteStatement#resetStatement(){} + 1127 
    at 5   ComposeApp                          0x1089fecd3        kfun:co.touchlab.sqliter.interop.SqliteStatement#resetStatement(){}-trampoline + 91 
    at 6   ComposeApp                          0x1089fb333        kfun:co.touchlab.sqliter.native.NativeStatement#resetStatement(){} + 531 
    at 7   ComposeApp                          0x1089fa623        kfun:co.touchlab.sqliter.native.NativeStatement#execute(){} + 679 
    at 8   ComposeApp                          0x1089fd38b        kfun:co.touchlab.sqliter.Statement#execute(){}-trampoline + 91 
    at 9   ComposeApp                          0x1089f838f        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.beginTransaction$lambda$0#internal + 67 
    at 10  ComposeApp                          0x1089f876b        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 79 
    at 11  ComposeApp                          0x1089f87e3        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection.$beginTransaction$lambda$0$FUNCTION_REFERENCE$0.$<bridge-UNNN>invoke(co.touchlab.sqliter.Statement){}#internal + 99 
    at 12  ComposeApp                          0x108b7ce8f        kfun:kotlin.Function1#invoke(1:0){}1:1-trampoline + 107 
    at 13  ComposeApp                          0x1089dc3a3        kfun:co.touchlab.sqliter#withStatement__at__co.touchlab.sqliter.DatabaseConnection(kotlin.String;kotlin.Function1<co.touchlab.sqliter.Statement,0:0>){0§<kotlin.Any?>}0:0 + 279 
    at 14  ComposeApp                          0x1089f76bb        kfun:co.touchlab.sqliter.native.NativeDatabaseConnection#beginTransaction(){} + 307 
    at 15  ComposeApp                          0x1089fce7b        kfun:co.touchlab.sqliter.DatabaseConnection#beginTransaction(){}-trampoline + 91 
    at 16  ComposeApp                          0x108a074cf        kfun:app.cash.sqldelight.driver.native.ThreadConnection#newTransaction(){}app.cash.sqldelight.Transacter.Transaction + 275 
    at 17  ComposeApp                          0x108a0691f        kfun:app.cash.sqldelight.driver.native.SqliterWrappedConnection#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction> + 159 
    at 18  ComposeApp                          0x10a534977        kfun:app.cash.sqldelight.db.SqlDriver#newTransaction(){}app.cash.sqldelight.db.QueryResult<app.cash.sqldelight.Transacter.Transaction>-trampoline + 99 
    at 19  ComposeApp                          0x10a53150f        kfun:app.cash.sqldelight.SuspendingTransacterImpl.$transactionWithWrapperCOROUTINE$0.invokeSuspend#internal + 767 
    at 20  ComposeApp                          0x10a531f23        kfun:app.cash.sqldelight.SuspendingTransacterImpl.transactionWithWrapper#internal + 323 
    at 21  ComposeApp                          0x10a5310ff        kfun:app.cash.sqldelight.SuspendingTransacterImpl#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.
    SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 151 
    at 22  ComposeApp                          0x10a534703        kfun:app.cash.sqldelight.SuspendingTransacter#transaction#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any-trampoline + 127 
    at 23  ComposeApp                          0x10a52eb8f        kfun:app.cash.sqldelight.SuspendingTransacter#transaction$default#suspend(kotlin.Boolean;kotlin.coroutines.SuspendFunction1<app.cash.sqldelight.SuspendingTransactionWithoutReturn,kotlin.Unit>;kotlin.Int;kotlin.coroutines.Continuation<kotlin.Unit>){}kotlin.Any + 211 
    at 24  ComposeApp                          0x10a54246b        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2COROUTINE$3.invokeSuspend#internal + 1319 
    at 25  ComposeApp                          0x10a542787        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.create$lambda$2#internal + 331 
    at 26  ComposeApp                          0x10a543afb        kfun:com.attafitamim.kabin.core.database.KabinSqlSchema.$create$lambda$2$FUNCTION_REFERENCE$1.invoke#internal + 115 
    at 27  ComposeApp                          0x108b826eb        kfun:kotlin.coroutines.SuspendFunction0#invoke#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any?-trampoline + 107 
    at 28  ComposeApp                          0x10a532b77        kfun:app.cash.sqldelight.db.QueryResult.AsyncValue#await#suspend(kotlin.coroutines.Continuation<1:0>){}kotlin.Any? + 175 
    at 29  ComposeApp                          0x1089ced57        kfun:app.cash.sqldelight.async.coroutines.object-1.create$lambda$0#internal + 243 
    at 30  ComposeApp                          0x1089cf0b7        kfun:app.cash.sqldelight.async.coroutines.object-1.$create$lambda$0$FUNCTION_REFERENCE$0.invoke#internal + 139 
    at 31  ComposeApp                          0x108b8242b        kfun:kotlin.Function2#invoke(1:0;1:1){}1:2-trampoline + 115 
    at 32  ComposeApp                          0x108a3a3b7        kfun:kotlin.coroutines.intrinsics.object-4.invokeSuspend#internal + 731 
    at 33  ComposeApp                          0x108b81d47     

It seems like scheme's create method is already wrapped inside a transaction by sqldelight, and kabin tries to wrap it again which leads to the exception above. Further investigation should include the migrate method.

Bug: Flow doesn't emit new value when compound have relation to list of entities

Problem

When we have complex compound hierarchy with relation to a list of entities (or compounds) - reactive subscription via flow doesn't emit new values on changed entities.

Environment: kabin: 0.1.0-alpha05; android target.
SQLite version (according to documentation): 3.39 (API 34).

Example

data class Compound1(
    val entity: Entity1,
    
    @Relation(
        entity = Entity2::class,
        parentColumn = "id",
        entityColumn = "id"
    )
    val compounds2: List<Compound2>
)

@Dao
interface IEntity2Dao {

    @Query("DELETE FROM Entity2 WHERE id = :id")
    suspend fun removeEntity(id: String)
}

@Dao
interface IEntity1Dao {

    @Query("SELECT * FROM Entity1")
    suspend fun getCompound(id: String): Flow<Compound1>
}

to reproduce:

suspend fun reproducer(
    dao1: IEntity1Dao,
    dao2: IEntity2Dao
) {
    coroutineScope.launch {
        dao1.getCompound("id1").collect {
            // only first emit
        }

        dao2.removeEntity("id2")
    }
}

Mapper: issue with the order of mapper constructor arguments in the generated database class

Problem

The generated database class has Type mismatch errors when creating mappers using the constructor. This due to incorrect order of arguments.

The error looks like this:

Type mismatch.
Required:
ColumnAdapter<Int, Long>
Found:
ColumnAdapter<Long, String>

And doesn't allow a successful compilation

Solution

Use the same order to generate Mapper class and Mapper constructor calls inside database class

Insert: Incorrect parameters count

Problem

The generated code uses an incorrect parameters count for @Insert @Update and other similar annotations, it can be reproduced inside the sample, here's an extract from UserDao:

  public suspend fun insertOrReplace(entity: BankEntity) {
    driver.execute(
            -1230324746,
            """INSERT OR REPLACE INTO BankEntity VALUES (?, ?, ?, ?)""",
            1
        )
     {
      bindLong(0, entity.number)
      bindString(1, entity.country)
      bindString(2, entity.region)
      bindString(3, kotlinCollectionsListKotlinStringKotlinStringAdapter.encode(entity.supportedCards))
    }
    notifyQueries(-1230324746) { emit ->
      emit("BankEntity")
    }
  }

Solution

Parameters count must be equal to columns count of the entity, in our example, it must be 4

Constraint foreign key exception when having conflicting operations inside one transaction

Problem

If we have several tables with foreign key relations, then inside one transaction do some operations influences on each other and get exception android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY).

Unlikely, in Room library the same tables and relations such transaction is considered valid.

Environment: kabin: 0.1.0-alpha04;
SQLite version (according to documentation): 3.39 (API 34).

Example

We have 2 tables and a dao.

@Entity
data class UserEntity(
    @PrimaryKey
    val id: Int,
    val name: String
)

@Entity(
    primaryKeys = ["id", "userId"],
    foreignKeys = [
        ForeignKey(
            entity = UserEntity::class,
            parentColumns = ["id"],
            childColumns = ["userId"],
            onDelete = ForeignKey.Action.CASCADE,
            onUpdate = ForeignKey.Action.CASCADE
        )
    ],
)
data class UserAvatarEntity(
    val userId: Int,
    val id: String,
    val url: String
)

@Dao
interface IUserDaoSpecial {

    @Transaction
    suspend fun doSomeOperations() {
        val userEntity = UserEntitySpecial(
            id = 0,
            name = "John"
        )
        val userAvatarEntity = UserAvatarEntitySpec(
            userId = 0,
            id = "d64c2cd8-fd5f-4f06-844e-b4006b9464bb",
            url = "https://icon.jpg"
        )

        insertAvatar(userAvatarEntity)
        insertUser(userEntity)
    }

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertUser(user: UserEntitySpecial)

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAvatar(avatarEntitySpec: UserAvatarEntitySpec)
}

If we will invoke dao method, we will catch an exception.

android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)

Expected behavior: successful operations inside one transaction.
I guess this error is connected with deferred flag in ForeignKey.

Flow of a single element doesn't throw error when the element doesn't exist

Problem

Querying a reactive entity doesn't throw an exception, if the entity doesn't exist, since Dao is using mapToOneNotNull instead of mapToOne

However, Room uses a different behavior, it emits an error instead of suspending the Flow<T> forever while waiting for insert

Example

Here's a query that returns a Flow of a single element:

@Query("SELECT * FROM UserEntity WHERE name = :name AND sampleAge = :age")
suspend fun getEntityReactive(age: Int, name: String): Flow<UserEntity>

This will generate the following code:

override suspend fun getEntityReactive(age: Int, name: String): Flow<UserEntity> =
    queries.queryUserEntityByNameAge(name, age).asFlowIONotNull()

As we see, converting Query<T> to Flow<T> is done using asFlowIONotNull, but it will be more correct to use asFlowIO, which uses mapToOne under the hood

ForeignKey: Inconsistent behavior across different KMP targets, especially during migration

Problem

Enabling and disabling foreign keys currently works through the API provided by sqldelight, however, this leads to inconsistent behavior between android and iOS.

Explanation

Inside NativeSqliteDriver, and exactly during the creation of a connection through DatabaseManager, PRAGMA foreign_keys = ON; is called before migrate.

migrate is executed inside a transaction, therefore, our PRAGMA foreign_keys = OFF; call inside KabinSqlSchema.migrate function has no effect, because according to the official docs, it should be called before BEGIN TRANSACTION or after COMMIT https://www.sqlite.org/pragma.html#pragma_foreign_keys

Even tho we have defer_foreign_keys calls inside our transactions, some errors triggered by foreign keys during migration are not handled, since they are not directly related to constraints, for example, having table not found errors when dropping related tables. This throws an error immediately and doesn't wait for COMMIT to recheck the integrity of the keys https://www.sqlite.org/pragma.html#pragma_defer_foreign_keys

On android, foreign keys are enabled inside the callback onOpen, here's the official documentation of the method:

Called when the database has been opened. The implementation should check SupportSQLiteDatabase.isReadOnly before updating the database.
This method is called after the database connection has been configured and after the database schema has been created, upgraded or downgraded as necessary. If the database connection must be configured in some way before the schema is created, upgraded, or downgraded, do it in onConfigure instead.
Params:
db - The database.

As we can see, onOpen is called after all the work with the scheme is done, including migration, which means during migration, foreign keys are disabled, and enabled only after everything is set.

Solution

Make a unified logic to enabled and disable foreign keys during driver creation, using raw SQL calls, rather than using platform specific config. This will help to increase the consistency and similarity.

Multiple and nullable list arguments break code-gen

Problem

1. Using lists multiple times inside a query breaks code-gen
2. Nullable lists are not handled when creating query arguments using createArguments

Example

Here's an example of a query having lists as arguments, which are used many times in different places, and are nullable

@Query("""
        SELECT * FROM SimpleEntity 
                WHERE(:types IS NULL OR type IN :types)
                AND (:statuses IS NULL OR status IN :statuses)
                AND (:startAt is NULL OR createdAt >= :startAt)
                AND (:endAt IS NULL OR createdAt <= :endAt)
                AND id IN (
                        SELECT parentId FROM SampleParticipantEntity
                        WHERE (:roles IS NULL OR type IN :roles) AND self = 1
                 )
        ORDER BY createdAt DESC LIMIT :limit
""")
suspend fun getSampleCompoundsReactive(
        statuses: List<String>?,
        types: List<String>?,
        roles: List<String>?,
        startAt: Int?,
        endAt: Int?,
        limit: Int
): Flow<List<SampleCompound>>

This will generate the following code:

val types = types.orEmpty()
val typesIndexes = createArguments(types.size)
val types = types.orEmpty()
val typesIndexes = createArguments(types.size)
val statuses = statuses.orEmpty()
val statusesIndexes = createArguments(statuses.size)
val statuses = statuses.orEmpty()
val statusesIndexes = createArguments(statuses.size)
val roles = roles.orEmpty()
val rolesIndexes = createArguments(roles.size)
val roles = roles.orEmpty()
val rolesIndexes = createArguments(roles.size)

Here we see two issues:

1. types and typesIndexes are doubled
2. createArguments will never return NULL, which is important for the correctness of queries like :types IS NULL OR type IN :types

Bug: type mismatch for complex compounds having nullable and non-nullable types

Problem

If we will have complex compounds hierarchy, and in one place compound is considered nullable, an in another - not null, nullable query is used, and compiler throws "type mismatch" error.

Environment: kabin: 0.1.0-alpha05; android target.
SQLite version (according to documentation): 3.39 (API 34).

Example

data class ACompound(
    @Embedded
    val entity: AEntity,

    @Relation(
        entity = BEntity::class,
        parentColumn = "bId",
        entityColumn = "id"
    )
    // query1 returning nullable compound is generated
    val bCompound: bCompound?,
    
        @Relation(
        entity = CEntity::class,
        parentColumn = "cId",
        entityColumn = "id"
    )
    val cCompound: cCompound,
)

data class CCompound(
    @Embedded
    val entity: CEntity,

    @Relation(
        entity = BEntity::class,
        parentColumn = "bId",
        entityColumn = "id"
    )
    // query1 will be still used in dao, though we have non-nullable returning type
    val bCompound: bCompound,

Maybe we should add postfix "Optional" or "Nullable" to queries returning nullable type to avoid such problems.