[Day 23] 自定義 ColumnType, Operator, Expression 擴展 Exposed Query DSL API

2021 iThome 鐵人賽

DAY 23

Modern Web

基於 Kotlin Ktor 建構支援模組化開發的 Web 框架系列第 23 篇

13th鐵人賽 kotlin ktor exposed

林俊宏

團隊Kotlin 愛台灣 2021

2021-09-28 21:28:23

1391 瀏覽

ORM 框架可以讓開發者專注於物件的 CRUD 操作，不必直接思考 SQL 要怎麼寫。如果是新增、修改、刪除操作的話，通常只要寫一行程式碼，呼叫 save() 之類的方法就完成了，所以 ORM 的 Query API 才是開發者關注的重點。Exposed API 的使用方式非常直覺簡單，官方文件也都有範例可以參考，就不在此多做介紹了，所以今天的主題是分享進階的實作技巧，如何透過自定義 ColumnType, Operator, Expression，寫出更簡潔的查詢程式碼。

Exposed DSL API 與 DAO API 之間的選擇

Exposed 提供2種 API 操作資料庫

DAO API => lightweight Data Access Objects
DSL API => typesafe SQL wrapping DSL

目前我只使用 DSL API 操作，原因是

我個人習慣有必要才使用 ORM 的進階功能，甚至 Lazy Loading 都很少用，所以 DSL API 就夠用了。
Exposed 是用 Kotlin 寫的，而且 DSL API 的設計很棒，所以即使是比較複雜的查詢需求，寫出來的 DSL 程式碼也非常簡潔易讀
目前我的專案實作重點是底層架構設計及實現基礎設施功能，所以還沒有商業邏輯需要處理，可以先省略 Model 的 Entity 物件，只定義 DTO 物件即可。未來可以根據 CQRS 的原則，Command 使用 DAO API 操作 Entity 物件，Query 使用 DSL API 應付各種查詢情境
DAO API 需要多定義一個 Entity 類別，而且 Entity 類別的屬性必須是 delegated property，綁定對應的 Table 物件的 Column 屬性。然而我使用的 json library kotlinx.serialization 無法 serialize delegated property，所以即使是最簡單查詢 Entity 資料的需求，我也無法把 Entity 物件直接轉為 json 輸出至前端，必須要定義 DTO 類別用來轉換資料。既然如此，我就直接使用 DSL API 把查詢出來的資料填入 DTO 物件即可。相關文件及討論可參考以下連結
- [kotlinx.serialization] backing-fields-are-serialized
- [Exposed] Proper way to serialize/deserialize DAOs entities. #497
```
// DSL Table
object StarWarsFilms : IntIdTable() {
  val sequelId = integer("sequel_id").uniqueIndex()
  val name = varchar("name", 50)
  val director = varchar("director", 50)
}

// DAO Entity
class StarWarsFilm(id: EntityID<Int>) : IntEntity(id) {
 companion object : IntEntityClass<StarWarsFilm>(StarWarsFilms)
 var sequelId by StarWarsFilms.sequelId 
 var name     by StarWarsFilms.name
 var director by StarWarsFilms.director
}
```

自定義 ColumnType

以 club_user 資料表為例，lang,contact1, address1,contact2, address2 在資料庫的 columnType 都是字串。如果我們也在 ClubUserTable 的欄位變數型態宣告為字串的話，那麼每次從查詢結果 ResultRow 取出欄位的字串值後，就要再自行轉換為物件填入到 UserDTO。為了避免每次查詢後都要自己轉換，我們可以利用 kotlin extension function 的特性，在 Exposed 的 Table 類別增加 columnType。

object ClubUserTable : UUIDTable(name = "club_user") {
    // string of varchar column
    val name = varchar("name", USER_NAME_LENGTH)
    
    // lang object of varchar(20) column 
    val lang = lang("lang").nullable()
    
    // json of text column
    val contact1 = jsonObject("contact1")
    val addresses1 = jsonArray("addresses1")
    
    // dto of text column
    val contact2 = dto<Contact>("contact2")
    val addresses2 = dtoList<Address>("addresses2")
}

@Serializable
data class UserDTO(@JvmField @Serializable(with = UUIDSerializer::class) val id: UUID) : EntityDTO<UUID> {

    var name: String? = null

    var contact1: JsonObject? = null
    var addresses1: JsonArray? = null

    var contact2: Contact? = null
    var addresses2: List<Address>? = null
    
    override fun getId(): UUID = id
}

Object Type Column

以 lang 欄位為例，儲存在資料庫的值是字串 languageCode，我們可以實作 valueFromDB
及 notNullValueToDB 方法，告訴 Exposed 要如何與 Lang 物件做雙向轉換，然後 ResultRow 呼叫 get(lang) 方法取出的物件型態就會是 Lang 了。

val langObject: Lang = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(lang)

@Serializable
class Lang(val locale: Locale) {
    constructor(code: String) : this(Locale.Builder().setLanguageTag(code).build())
    val code: String = locale.toLanguageTag()
}

fun Table.lang(name: String): Column<Lang> = registerColumn(name, object : VarCharColumnType(20) {
    override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
    override fun valueFromDB(value: Any): Lang = Lang(value as String)
    override fun notNullValueToDB(value: Any): Any = (value as Lang).code
})

JSON Type Column

資料庫除了儲存單純的字串之外，儲存 Json 字串也是非常常見的需求，在這裡使用 kotlinx.serialization 的 parseToJsonElement 與 toString 進行轉換

val contact1: JsonObject? = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(contact1)

fun Table.jsonObject(name: String): Column<JsonObject> = registerColumn(name, object : StringColumnType() {
    override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
    override fun valueFromDB(value: Any): JsonElement = json.parseToJsonElement(value as String).jsonObject
    override fun notNullValueToDB(value: Any): Any = value.toString()
})

fun Table.jsonArray(name: String): Column<JsonArray> = registerColumn(name, object : StringColumnType() {
    override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
    override fun valueFromDB(value: Any): JsonElement = json.parseToJsonElement(value as String).jsonArray
    override fun notNullValueToDB(value: Any): Any = value.toString()
})

Mapping JSON Type Column to DTO Object

如果能再從 JSON 物件轉換為自定義的 DTO 物件，那就更簡潔易讀且 typesafe 了

val addresses2: List<Address>? = ClubUserTable.select { account eq "john" }.singleOrNull()?.get(addresses2)

@OptIn(InternalSerializationApi::class)
inline fun <reified T : Any> Table.dto(name: String): Column<T> = registerColumn(name, object : StringColumnType() {
    override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
    override fun valueFromDB(value: Any): T = json.decodeFromString(T::class.serializer(), value as String)
    override fun notNullValueToDB(value: Any): Any = json.encodeToString(T::class.serializer(), value as T)
})

@OptIn(InternalSerializationApi::class)
inline fun <reified T : Any> Table.dtoList(name: String): Column<List<T>> = registerColumn(name, object : StringColumnType() {
    override fun sqlType(): String = currentDialect.dataTypeProvider.textType()
    override fun valueFromDB(value: Any): List<T> = json.decodeFromString(ListSerializer(T::class.serializer()), value as String)
    override fun notNullValueToDB(value: Any): Any = json.encodeToString(ListSerializer(T::class.serializer()), value as List<T>)
})

自定義 Operator

Temporal Range Operator

SQL 的 Beween Operator 的 begin 與 end 都是 inclusive，然而我們還是會有 endExclusive 的需求，與其每次都要寫 columnA >= begin and columnB < end，倒不如自己定義 endExclusiveRange operator 比較容易閱讀且不會出錯

ClubUserTable.select { createdAt endExclusiveRange (beginInstant to endInstant) }.toList()

infix fun ExpressionWithColumnType<Instant>.endExclusiveRange(pair: Pair<Instant, Instant>): AndOp = inTemporalRange(this, pair)

private fun <T, R> endExclusiveRange(column: ExpressionWithColumnType<T>, pair: Pair<R, R>): AndOp = AndOp(
    listOf(
        GreaterEqOp(column, QueryParameter(pair.first, column.columnType)),
        LessOp(column, QueryParameter(pair.second, column.columnType))
    )
)

Overlap Operator

另一個常見需求是檢查兩個 X, Y 的時間區間有沒有重疊，也就是 Y.startInclusive <= X.endInclusive && Y.endInclusive >= X.startInclusive

ClubUserTable.select { overlap(createdAt, updatedAt, beginInstant, endInstant) }.toList()

fun <T, R> SqlExpressionBuilder.overlap(
    startColumn: ExpressionWithColumnType<T>,
    endColumn: ExpressionWithColumnType<T>,
    rangeStart: R,
    rangeEndInclusive: R
): AndOp = AndOp(
    listOf(
        LessEqOp(startColumn, QueryParameter(rangeEndInclusive, startColumn.columnType)),
        GreaterEqOp(endColumn, QueryParameter(rangeStart, endColumn.columnType))
    )
)

Multiple Columns in IN Clause

MySQL 支援多個欄位的 IN 查詢語法 => SELECT * FROM table WHERE (x,y) IN ((1, 2), (3, 4), (5, 6))。Java 的解決方式不外乎就是寫個 SQL 字串處理的 utility method，但是 kotlin 可以在 SqlExpressionBuilder 加上 multiIn extension function，覆寫 toQueryBuilder 方法將組好的字串 append 到 SQL statement

ClubUserTable.select { multiIn(listOf(name, enabled), listOf(listOf("john", true), listOf("mary", false))) }.toList()

fun SqlExpressionBuilder.multiIn(columns: List<Column<*>>, values: List<List<Any>>) = object : Op<Boolean>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder {
        append(columns.joinToString(",", prefix = "(", postfix = ")") {
            it.name
        })
        append(" in ")
        append(values.joinToString(",", prefix = "(", postfix = ")") { it1 ->
            it1.joinToString(",", prefix = "(", postfix = ")") { it2 ->
                when (it2) {
                    is String -> "'$it2'"
                    else -> it2.toString()
                }
            }
        })
    }
}

自定義 Expression

如果需要寫比較複雜的 Raw SQL，那麼我們可以定義 RawExpression ，然後在裡面 append 任何字串。這個方式的好處是不必為了一個 Exposed 未內建支援的 expression，就放棄使用 DSL API 走回字串相加的老方法

class RawExpression(private val raw: String) : Expression<String>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder { +raw }
}

class CountExpression : Expression<Long>() {
    override fun toQueryBuilder(queryBuilder: QueryBuilder) = queryBuilder { +"COUNT(*)" }
}

ResultRowDTOMapper

Exposed DSL API 的查詢結果是 ResultRow 物件，需要自己轉換為各種 DTO 物件，所以我在 ResultRow 加上 toDTO(dtoClass: KClass<T>): T …等 extension function，可以寫出 fluent-style 的程式碼 query.toList().toDTO(UserDTO::class).map {...}。另一方面，內部實作會遞迴處理每一個 DTO 物件屬性，所以支援巢狀結構的 DTO。

點我連結至完整 ResultRowDTOMapper 程式碼

fun <T : EntityDTO<*>> ResultRow.toDTO(dtoClass: KClass<T>): T {
    logger.debug { "========== map ResultRow to ${dtoClass.qualifiedName} begin ==========" }
    val mapper = ResultRowDTOMapper.getMapper(dtoClass)
    val value = mapper.mapDTO(this)!!
    logger.debug { "========== map ResultRow to ${dtoClass.qualifiedName} end ==========" }
    return value
}

fun <T : EntityDTO<*>> List<ResultRow>.toSingleDTO(dtoClass: KClass<T>): T? {
    return takeIf { it.isNotEmpty() }?.toDTO(dtoClass)?.get(0)
}

fun <T : EntityDTO<*>> List<ResultRow>.toDTO(dtoClass: KClass<T>): List<T> {
    logger.debug { "=============== map List<ResultRow> to ${dtoClass.qualifiedName} begin ===============" }
    val mapper = ResultRowDTOMapper.getMapper(dtoClass)
    val dtoList = mutableListOf<T>()
    val dtoMap = mutableMapOf<List<*>, T>()

    this.forEach { resultRow ->
        val dtoIdValues = resultRow.getPKValuesOfTable(mapper.table)!!
        var dto = dtoMap[dtoIdValues]
        if (dto == null) {
            dto = dtoClass.primaryConstructor!!.call(*dtoIdValues.toTypedArray())
            mapper.mapDTO(dto, resultRow)
            dtoMap[dtoIdValues] = dto
            dtoList.add(dto)
        } else {
            // use single to avoid cross join
            val nestedDTOListProperty =
                dtoClass.memberProperties.single { it.returnType.isSubtypeOf(ResultRowDTOMapper.listType) } as KProperty<MutableList<T>>
            val nestedDTOClass = nestedDTOListProperty.returnType.arguments[0].type!!.classifier as KClass<T>
            val nestedDTO = resultRow.toDTO(nestedDTOClass)
            nestedDTOListProperty.getter.call(dto).add(nestedDTO)
        }
    }
    logger.debug { "=============== map List<ResultRow> to ${dtoClass.qualifiedName} end ===============" }
    return dtoList
}

Transaction

為了知道每一個 Exposed transaction block 耗時多久，還有想 catch Exposed ExposedSQLException 轉換為自定義的 Exception，所以定義自己的 transaction function，內部再呼叫 Exposed transaction block

private val profilingLogger = KotlinLogging.logger("fanpoll.infra.database.sql.Profiling")

fun <T> transaction(db: Database? = null, statement: Transaction.() -> T): T {
    return transaction(db) {
        val begin = Instant.now()
        profilingLogger.debug { "===== Transaction Profiling Begin ($id) ===== " }
        try {
            statement()
        } catch (e: ExposedSQLException) {
            throw InternalServerException(InfraResponseCode.DB_SQL_ERROR, e.toString(), e) // include caused SQL
        } finally {
            profilingLogger.debug {
                "===== Transaction execution time: ${Duration.between(begin, Instant.now()).toMillis()} millis ($id) ====="
            }
        }
    }
}