kotlin-orm · vincentlauvlwj · Sep 21, 2022 · Jul 14, 2022 · Jul 15, 2022 · Jul 15, 2022
diff --git a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts
@@ -131,6 +131,11 @@ publishing {
                         name.set("ccr")
                         email.set("2938137849@qq.com")
                     }
+                    developer {
+                        id.set("zuisong")
+                        name.set("zuisong")
+                        email.set("com.me@foxmail.com")
+                    }
                 }
             }
         }

diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhen.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhen.kt
@@ -0,0 +1,186 @@
+/*
+ * Copyright 2018-2022 the original author or authors.
+ *
+ * Licensed under the Apache License, Version 2.0 (the "License");
+ * you may not use this file except in compliance with the License.
+ * You may obtain a copy of the License at
+ *
+ *      http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.ktorm.dsl
+
+import org.ktorm.expression.ArgumentExpression
+import org.ktorm.expression.CaseWhenExpression
+import org.ktorm.expression.ScalarExpression
+import org.ktorm.schema.BooleanSqlType
+import org.ktorm.schema.ColumnDeclaring
+import org.ktorm.schema.SqlType
+
+/**
+ * case when of case Expression.
+ * [caseValue] case value, may be null
+ * [caseSqlType] case value sqlType
+ *
+ * if [caseValue] is null, then [caseSqlType] is [BooleanSqlType]
+ */
+public data class CaseExpression<T : Any> internal constructor(
+    internal val caseValue: ScalarExpression<T>? = null,
+    internal val caseSqlType: SqlType<T>,
+)
+
+/**
+ * case when of when Expression.
+ *
+ * [caseValue] case value, may be null
+ * [condition] when condition
+ * [caseWhenExpression], the [CaseWhenExpression] when build [WhenExpression]
+ * from [CaseWhenExpression], may be bull
+ */
+public data class WhenExpression<T : Any, V : Any> internal constructor(
+    internal val caseValue: ScalarExpression<T>?,
+    internal val condition: ScalarExpression<T>,
+    internal val caseWhenExpression: CaseWhenExpression<T, V>? = null,
+)
+
+/**
+ * DSL to build [CaseExpression] without [CaseExpression.caseValue].
+ */
+@Suppress("FunctionName")
+public fun CASE(): CaseExpression<Boolean> {
+    return CaseExpression(null, BooleanSqlType)
+}
+
+/**
+ * DSL to build [CaseExpression] with [CaseExpression.caseValue].
+ */
+@Suppress("FunctionName")
+public fun <T : Any> CASE(caseValue: ColumnDeclaring<T>): CaseExpression<T> {
+    return CaseExpression(caseValue.asExpression(), caseValue.sqlType)
+}
+
+/**
+ * DSL to build [WhenExpression] from [CaseExpression].
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any> CaseExpression<T>.WHEN(condition: ColumnDeclaring<T>): WhenExpression<T, Nothing> {
+    return WhenExpression(
+        caseValue = this.caseValue,
+        condition = condition.asExpression(),
+        caseWhenExpression = null
+    )
+}
+
+/**
+ * DSL to build [WhenExpression] from [CaseExpression].
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any> CaseExpression<T>.WHEN(condition: T): WhenExpression<T, Nothing> {
+    return WhenExpression(
+        this.caseValue,
+        ArgumentExpression(condition, this.caseSqlType),
+        caseWhenExpression = null
+    )
+}
+
+/**
+ * DSL to build [CaseWhenExpression] from [WhenExpression].
+ *
+ * [value] the value of then branch
+ */
+@JvmName("_THEN")
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> WhenExpression<T, V>.THEN(value: V): CaseWhenExpression<T, V> {
+    return this.caseWhenExpression!!.copy(
+        whenThenConditions = this.caseWhenExpression.whenThenConditions +
+            (this.condition to
+                ArgumentExpression(
+                    value,
+                    this.caseWhenExpression.sqlType
+                ))
+    )
+}
+
+/**
+ * DSL to build [CaseWhenExpression] from [WhenExpression].
+ *
+ * [value] the value of then branch
+ */
+@JvmName("_THEN")
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> WhenExpression<T, V>.THEN(value: ColumnDeclaring<V>): CaseWhenExpression<T, V> {
+    return this.caseWhenExpression!!.copy(
+        whenThenConditions = this.caseWhenExpression.whenThenConditions +
+            (this.condition to value.asExpression())
+    )
+}
+
+/**
+ * DSL to build [CaseWhenExpression] from [WhenExpression].
+ *
+ * [value] the value of then branch
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> WhenExpression<T, Nothing>.THEN(
+    value: ColumnDeclaring<V>,
+): CaseWhenExpression<T, V> {
+    return CaseWhenExpression(
+        caseExpr = this.caseValue,
+        whenThenConditions = listOf(this.condition to value.asExpression()),
+        whenSqlType = this.condition.sqlType,
+        sqlType = value.sqlType,
+    )
+}
+
+/**
+ * DSL to build [CaseWhenExpression] from [CaseWhenExpression] with else branch.
+ * [value] the value of else branch
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> CaseWhenExpression<T, V>.ELSE(value: ColumnDeclaring<V>): CaseWhenExpression<T, V> {
+    return this.copy(elseExpr = value.asExpression())
+}
+
+/**
+ * DSL to build [CaseWhenExpression] from [CaseWhenExpression] with else branch.
+ *
+ * [value] the value of else branch
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> CaseWhenExpression<T, V>.ELSE(value: V): CaseWhenExpression<T, V> {
+    return this.copy(elseExpr = ArgumentExpression(value, this.sqlType))
+}
+
+/**
+ * DSL to build [WhenExpression] from [CaseWhenExpression].
+ *
+ * [condition] the when condition
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> CaseWhenExpression<T, V>.WHEN(condition: ColumnDeclaring<T>): WhenExpression<T, V> {
+    return WhenExpression(
+        caseWhenExpression = this,
+        condition = condition.asExpression(),
+        caseValue = null
+    )
+}
+
+/**
+ * DSL to build [WhenExpression] from [CaseWhenExpression].
+ *
+ * [condition] the when condition
+ */
+@Suppress("FunctionName")
+public infix fun <T : Any, V : Any> CaseWhenExpression<T, V>.WHEN(condition: T): WhenExpression<T, V> {
+    return WhenExpression(
+        caseWhenExpression = this,
+        condition = ArgumentExpression(condition, this.whenSqlType),
+        caseValue = null,
+    )
+}
diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt
@@ -68,6 +68,7 @@ public open class SqlExpressionVisitor {
             is BetweenExpression<*> -> visitBetween(expr)
             is ArgumentExpression -> visitArgument(expr)
             is FunctionExpression -> visitFunction(expr)
+            is CaseWhenExpression<*, *> -> visitCaseWhen(expr)
             else -> visitUnknown(expr)
         }
 
@@ -303,6 +304,38 @@ public open class SqlExpressionVisitor {
         }
     }
 
+    protected open fun <T : Any, V : Any> visitCaseWhen(expr: CaseWhenExpression<T, V>): CaseWhenExpression<T, V> {
+        val caseExpr = if (expr.caseExpr != null) {
+            visitScalar(expr.caseExpr)
+        } else {
+            null
+        }
+
+        val elseExpr = if (expr.elseExpr != null) {
+            visitScalar(expr.elseExpr)
+        } else {
+            null
+        }
+
+        val whenThenConditions =
+            expr.whenThenConditions.map { (condition, value) ->
+                visitScalar(condition) to visitScalar(value)
+            }
+
+        if (caseExpr === expr.caseExpr
+            && elseExpr === expr.elseExpr
+            && whenThenConditions === expr.whenThenConditions
+        ) {
+            return expr
+        } else {
+            return expr.copy(
+                caseExpr = caseExpr,
+                whenThenConditions = whenThenConditions,
+                elseExpr = elseExpr,
+            )
+        }
+    }
+
     protected open fun <T : Any> visitColumnAssignment(
         expr: ColumnAssignmentExpression<T>
     ): ColumnAssignmentExpression<T> {

diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt
@@ -537,6 +537,28 @@ public data class BetweenExpression<T : Any>(
     override val extraProperties: Map<String, Any> = emptyMap()
 ) : ScalarExpression<Boolean>()
 
+/**
+ * The CASE statement goes through conditions and returns a value when the
+ * first condition is met (like an if-then-else statement). So, once a condition
+ * is true, it will stop reading and return the result. If no conditions are true,
+ * it returns the value in the ELSE clause.
+ * If there is no ELSE part and no conditions are true, it returns NULL.
+ *
+ * @property whenThenConditions "when conditions then value" statements.
+ * @property elseExpr else statements.
+ * @property caseExpr case statements, optional, may be null.
+ * @property sqlType the argument's [SqlType].
+ */
+public data class CaseWhenExpression<V : Any, T : Any> constructor(
+    val caseExpr: ScalarExpression<V>? = null,
+    val whenThenConditions: List<Pair<ScalarExpression<V>, ScalarExpression<T>>>,
+    val elseExpr: ScalarExpression<T>? = null,
+    internal val whenSqlType: SqlType<V>,
+    override val sqlType: SqlType<T>,
+    override val isLeafNode: Boolean = true,
+    override val extraProperties: Map<String, Any> = emptyMap(),
+) : ScalarExpression<T>()
+
 /**
  * Argument expression, wraps an argument passed to the executed SQL.
  *

diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt
@@ -518,6 +518,26 @@ public abstract class SqlFormatter(
         return expr
     }
 
+    override fun <T : Any, V : Any> visitCaseWhen(expr: CaseWhenExpression<T, V>): CaseWhenExpression<T, V> {
+        writeKeyword("case ")
+        if (expr.caseExpr != null) {
+            visit(expr.caseExpr)
+            write(" ")
+        }
+        for ((condition, result) in expr.whenThenConditions) {
+            writeKeyword("when ")
+            visit(condition)
+            writeKeyword("then ")
+            visit(result)
+        }
+        if (expr.elseExpr != null) {
+            writeKeyword("else ")
+            visit(expr.elseExpr)
+        }
+        writeKeyword("end ")
+        return expr
+    }
+
     override fun visitColumnAssignments(
         original: List<ColumnAssignmentExpression<*>>
     ): List<ColumnAssignmentExpression<*>> {

diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt
@@ -2,7 +2,9 @@ package org.ktorm.dsl
 
 import org.junit.Test
 import org.ktorm.BaseTest
+import org.ktorm.expression.ArgumentExpression
 import org.ktorm.expression.ScalarExpression
+import org.ktorm.schema.IntSqlType
 import kotlin.random.Random
 
 /**
@@ -283,4 +285,74 @@ class QueryTest : BaseTest() {
         assert(names[0] == "0:vince")
         assert(names[1] == "1:marry")
     }
+
+    @Test
+    fun `test case when with function chain`() {
+        val caseWhen =
+            CASE()
+                .WHEN(Employees.name eq "vince").THEN(ArgumentExpression(1, IntSqlType))
+                .WHEN(Employees.name eq "mary").THEN(2)
+                .ELSE(ArgumentExpression(3, IntSqlType))
+                .aliased("caseWhen")
+        val names = database
+            .from(Employees)
+            .select(
+                caseWhen
+            )
+            .where { Employees.departmentId eq 1 }
+            .orderBy(Employees.salary.desc())
+            .flatMapIndexed { index, row -> listOf("$index:${row[caseWhen]}") }
+
+        assert(names.size == 2)
+        assert(names[0] == "0:1")
+        assert(names[1] == "1:3")
+    }
+
+
+    @Test
+    fun `test  case when with infix dsl`() {
+        val caseWhen =
+            (CASE()
+                    WHEN (Employees.name eq "vince") THEN (Employees.id)
+                    WHEN (Employees.name eq "mary") THEN (2)
+                    ELSE 3
+                    ).aliased("caseWhen")
+        val names = database
+            .from(Employees)
+            .select(
+                caseWhen
+            )
+            .where { Employees.departmentId eq 1 }
+            .orderBy(Employees.salary.desc())
+            .flatMapIndexed { index, row -> listOf("$index:${row[caseWhen]}") }
+
+        assert(names.size == 2)
+        assert(names[0] == "0:1")
+        assert(names[1] == "1:3")
+    }
+
+    @Test
+    fun `test  case when with infix dsl1`() {
+        val caseWhen = (
+                CASE(Employees.name)
+                        WHEN "vince" THEN Employees.id
+                        WHEN "mary" THEN 2
+                        WHEN "mary1" THEN Employees.departmentId
+                        ELSE 3
+                ).aliased("caseWhen")
+        val names = database
+            .from(Employees)
+            .select(
+                caseWhen
+            )
+            .where { Employees.departmentId eq 1 }
+            .orderBy(Employees.salary.desc())
+            .flatMapIndexed { index, row -> listOf("$index:${row[caseWhen]}") }
+
+        assert(names.size == 2)
+        assert(names[0] == "0:1")
+        assert(names[1] == "1:3")
+    }
+
 }
+