Logic Delete

This chapter describes logical delete behavior in mutation APIs.

Declare the logical delete column

Use @LogicDelete on the KPojo field that marks deleted rows. The annotation is enabled by default.

import com.kotlinorm.annotations.Default
import com.kotlinorm.annotations.LogicDelete
import com.kotlinorm.annotations.PrimaryKey
import com.kotlinorm.interfaces.KPojo

data class User(
    @PrimaryKey(identity = true)
    var id: Int? = null,
    var name: String? = null,
    @LogicDelete
    @Default("0") // @Default("false") for Postgres
    var deleted: Boolean? = null
) : KPojo

If your application uses the same logical delete field across tables, configure the global strategy and keep the field on each KPojo that should use it.

import com.kotlinorm.Kronos
import com.kotlinorm.beans.config.KronosCommonStrategy
import com.kotlinorm.beans.dsl.Field

Kronos.logicDeleteStrategy = KronosCommonStrategy(enabled = true, field = Field("deleted"))

Delete writes the delete marker

When logical delete is enabled, delete().execute() generates an UPDATE that writes the deleted value. The generated condition also keeps already deleted rows out of the target set.

User(id = 1)
    .delete()
    .by { it.id }
    .execute()

UPDATE `user`
SET `deleted` = :deletedNew
WHERE `id` = :id
  AND `deleted` = 0

id = 1
deletedNew = 1

Use .logic(false) for the current delete when the operation must generate a physical DELETE.

User(id = 1)
    .delete()
    .logic(false)
    .by { it.id }
    .execute()

DELETE
FROM `user`
WHERE `id` = :id

Select and update ignore deleted rows

Select and update statements add the active-row condition for a KPojo with logical delete enabled.

User()
    .select { [it.id, it.name] }
    .toMapList()

User(id = 1)
    .update()
    .set { it.name = "Kronos" }
    .by { it.id }
    .execute()

SELECT `id`, `name`
FROM `user`
WHERE `deleted` = 0

UPDATE `user`
SET `name` = :nameNew
WHERE `id` = :id
  AND `deleted` = 0

Insert and upsert maintain the marker

Insert initializes the logical delete field with the active value. When match-field upsert finds a logically deleted row, Kronos updates that row and restores the logical delete field to the active value. onConflict() upsert also includes that field in the inserted columns when the strategy is active, and writes the active value during conflict update.

User(name = "Kronos")
    .insert()
    .execute()

INSERT INTO `user` (`name`, `deleted`)
VALUES (:name, :deleted)

name = "Kronos"
deleted = 0

User(id = 1, name = "Kronos")
    .upsert { it.name }
    .on { it.id }
    .execute()

UPDATE `user`
SET `name` = :nameNew,
    `deleted` = :deletedNew
WHERE `id` = :id

DataGuard boundary

Logical delete changes a delete into an update statement. If no user condition is provided, the generated statement can still target every active row.

User()
    .delete()
    .execute()

UPDATE `user`
SET `deleted` = :deletedNew
WHERE `deleted` = 0

Enable 📚DataGuard when the application should reject full-table writes. See 📚Delete and 📚Update for condition APIs.