From 872fcf28337c727cf4c56ee1d609833cc0031071 Mon Sep 17 00:00:00 2001 From: zuisong Date: Thu, 14 Jul 2022 19:03:40 +0800 Subject: [PATCH 1/6] case when --- .../ktorm/expression/SqlExpressionVisitor.kt | 5 ++ .../org/ktorm/expression/SqlExpressions.kt | 62 ++++++++++------ .../org/ktorm/expression/SqlFormatter.kt | 17 +++++ .../test/kotlin/org/ktorm/dsl/QueryTest.kt | 70 +++++++++++++++++++ 4 files changed, 133 insertions(+), 21 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt index f22ebbdb..74edf2ee 100644 --- 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,10 @@ public open class SqlExpressionVisitor { } } + protected open fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { + return expr + } + protected open fun visitColumnAssignment( expr: ColumnAssignmentExpression ): ColumnAssignmentExpression { diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt index 2f06186f..01d7d261 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt @@ -82,7 +82,7 @@ public data class CastingExpression( val expression: SqlExpression, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -128,7 +128,7 @@ public data class SelectExpression( override val offset: Int? = null, override val limit: Int? = null, override val tableAlias: String? = null, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : QueryExpression() /** @@ -146,7 +146,7 @@ public data class UnionExpression( override val offset: Int? = null, override val limit: Int? = null, override val tableAlias: String? = null, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : QueryExpression() /** @@ -195,7 +195,7 @@ public data class UnaryExpression( val operand: ScalarExpression<*>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -301,7 +301,7 @@ public data class BinaryExpression( val right: ScalarExpression<*>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -318,7 +318,7 @@ public data class TableExpression( val catalog: String? = null, val schema: String? = null, override val isLeafNode: Boolean = true, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : QuerySourceExpression() /** @@ -332,7 +332,7 @@ public data class ColumnExpression( val name: String, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -348,7 +348,7 @@ public data class ColumnDeclaringExpression( val declaredName: String? = null, override val sqlType: SqlType = expression.sqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() { override fun aliased(label: String?): ColumnDeclaringExpression { @@ -386,7 +386,7 @@ public data class OrderByExpression( val expression: ScalarExpression<*>, val orderType: OrderType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() /** @@ -433,7 +433,7 @@ public data class JoinExpression( val right: QuerySourceExpression, val condition: ScalarExpression? = null, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : QuerySourceExpression() /** @@ -451,7 +451,7 @@ public data class InListExpression( val notInList: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -465,7 +465,7 @@ public data class ExistsExpression( val notExists: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -516,7 +516,7 @@ public data class AggregateExpression( val isDistinct: Boolean, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -534,9 +534,29 @@ public data class BetweenExpression( val notBetween: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() + +/** + * 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 sqlType the argument's [SqlType]. + */ +public data class CaseWhenExpression( + val whenThenConditions: List, ScalarExpression>>, + override val sqlType: SqlType, + val elseExpr: ScalarExpression? = null, + override val isLeafNode: Boolean = true, + override val extraProperties: Map = emptyMap(), +) : ScalarExpression() + /** * Argument expression, wraps an argument passed to the executed SQL. * @@ -547,7 +567,7 @@ public data class ArgumentExpression( val value: T?, override val sqlType: SqlType, override val isLeafNode: Boolean = true, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -561,7 +581,7 @@ public data class FunctionExpression( val arguments: List>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : ScalarExpression() /** @@ -574,7 +594,7 @@ public data class ColumnAssignmentExpression( val column: ColumnExpression, val expression: ScalarExpression, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() /** @@ -587,7 +607,7 @@ public data class InsertExpression( val table: TableExpression, val assignments: List>, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() /** @@ -602,7 +622,7 @@ public data class InsertFromQueryExpression( val columns: List>, val query: QueryExpression, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() /** @@ -617,7 +637,7 @@ public data class UpdateExpression( val assignments: List>, val where: ScalarExpression? = null, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() /** @@ -630,5 +650,5 @@ public data class DeleteExpression( val table: TableExpression, val where: ScalarExpression?, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap() + override val extraProperties: Map = emptyMap(), ) : SqlExpression() diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt index 2bab5c9d..b71d01f1 100644 --- 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,23 @@ public abstract class SqlFormatter( return expr } + + override fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { + writeKeyword("case ") + expr.whenThenConditions.forEach { (condition, result) -> + 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> ): List> { diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index b7af2f0d..dce41331 100644 --- 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,10 @@ package org.ktorm.dsl import org.junit.Test import org.ktorm.BaseTest +import org.ktorm.expression.ArgumentExpression +import org.ktorm.expression.CaseWhenExpression import org.ktorm.expression.ScalarExpression +import org.ktorm.schema.VarcharSqlType import kotlin.random.Random /** @@ -283,4 +286,71 @@ class QueryTest : BaseTest() { assert(names[0] == "0:vince") assert(names[1] == "1:marry") } + + + @Test + fun `test case when`() { + val caseWhen = CaseWhenExpression( + whenThenConditions = listOf( + ("vince" eq Employees.name) to ArgumentExpression("hello vince", VarcharSqlType), + ), + elseExpr = ArgumentExpression("hello world", VarcharSqlType), + sqlType = VarcharSqlType + ).aliased("c") + 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:hello vince") + assert(names[1] == "1:hello world") + } + + @Test + fun `test case when without else`() { + val casWhen = CaseWhenExpression( + whenThenConditions = listOf( + ("vince" eq Employees.name) to ArgumentExpression("hello vince", VarcharSqlType), + ), + sqlType = VarcharSqlType + ).aliased("c") + val names = database + .from(Employees) + .select( + casWhen + ) + .where { Employees.departmentId eq 1 } + .orderBy(Employees.salary.desc()) + .flatMapIndexed { index, row -> listOf("$index:${row[casWhen]}") } + + assert(names.size == 2) + assert(names[0] == "0:hello vince") + assert(names[1] == "1:null") + } + + @Test + fun `test case when without when and else`() { + val casWhen = CaseWhenExpression( + whenThenConditions = listOf( + ), + sqlType = VarcharSqlType + ).aliased("c") + val names = database + .from(Employees) + .select( + casWhen + ) + .where { Employees.departmentId eq 1 } + .orderBy(Employees.salary.desc()) + .flatMapIndexed { index, row -> listOf("$index:${row[casWhen]}") } + + assert(names.size == 2) + assert(names[0] == "0:null") + assert(names[1] == "1:null") + } } From b73f4df145d3acb97868239792a90bd3fdba4067 Mon Sep 17 00:00:00 2001 From: zuisong Date: Fri, 15 Jul 2022 19:16:10 +0800 Subject: [PATCH 2/6] case when enhancement --- .../org/ktorm/dsl/CaseWhenExpression.kt | 98 ++++++++++++++ .../ktorm/expression/SqlExpressionVisitor.kt | 4 +- .../org/ktorm/expression/SqlExpressions.kt | 48 +++---- .../org/ktorm/expression/SqlFormatter.kt | 7 +- .../test/kotlin/org/ktorm/dsl/QueryTest.kt | 121 ++++++++++++++---- 5 files changed, 229 insertions(+), 49 deletions(-) create mode 100644 ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt new file mode 100644 index 00000000..dfe3cfb5 --- /dev/null +++ b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt @@ -0,0 +1,98 @@ +/* + * 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 +import java.util.* + +public fun case( + resultType: SqlType, + builder: CaseWhenBuilder.() -> Unit, +): CaseWhenExpression { + + val caseWhenBuilder = CaseWhenBuilder(BooleanSqlType, resultType) + builder(caseWhenBuilder) + + return CaseWhenExpression( + whenThenConditions = caseWhenBuilder.whenThenExprList, + elseExpr = caseWhenBuilder.elseExpr, + sqlType = resultType + ) +} + +public fun case( + resultType: SqlType, + value: ColumnDeclaring, + builder: CaseWhenBuilder.() -> Unit, +): CaseWhenExpression { + + + val caseWhenBuilder = CaseWhenBuilder(value.sqlType, resultType) + builder(caseWhenBuilder) + + return CaseWhenExpression( + caseExpr = value.asExpression(), + whenThenConditions = caseWhenBuilder.whenThenExprList, + elseExpr = caseWhenBuilder.elseExpr, + sqlType = resultType + ) +} + + +public class CaseWhenBuilder( + private val caseSqlType: SqlType, + private val resultType: SqlType, +) { + + internal val whenThenExprList: LinkedList, ScalarExpression>> = LinkedList() + internal var elseExpr: ScalarExpression? = null + + public fun whenThen(`when`: T, then: ScalarExpression) { + whenThenExprList.add( + ArgumentExpression(`when`, caseSqlType) to then + ) + } + + public fun whenThen(`when`: T, then: V?) { + whenThenExprList.add( + ArgumentExpression(`when`, caseSqlType) to ArgumentExpression(then, resultType) + ) + } + + public fun whenThen(`when`: ScalarExpression, then: ScalarExpression) { + whenThenExprList.add(`when` to then) + } + + public fun whenThen(`when`: ScalarExpression, then: V?) { + whenThenExprList.add(`when` to ArgumentExpression(then, resultType)) + } + + public fun `else`(`else`: ScalarExpression) { + this.elseExpr = `else` + } + + public fun `else`(`else`: V?) { + this.elseExpr = ArgumentExpression(`else`, resultType) + } + + +} diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt index 74edf2ee..3ea932aa 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt @@ -68,7 +68,7 @@ public open class SqlExpressionVisitor { is BetweenExpression<*> -> visitBetween(expr) is ArgumentExpression -> visitArgument(expr) is FunctionExpression -> visitFunction(expr) - is CaseWhenExpression -> visitCaseWhen(expr) + is CaseWhenExpression<*, *> -> visitCaseWhen(expr) else -> visitUnknown(expr) } @@ -304,7 +304,7 @@ public open class SqlExpressionVisitor { } } - protected open fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { + protected open fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { return expr } diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt index 01d7d261..c4c41f4a 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt @@ -82,7 +82,7 @@ public data class CastingExpression( val expression: SqlExpression, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -128,7 +128,7 @@ public data class SelectExpression( override val offset: Int? = null, override val limit: Int? = null, override val tableAlias: String? = null, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : QueryExpression() /** @@ -146,7 +146,7 @@ public data class UnionExpression( override val offset: Int? = null, override val limit: Int? = null, override val tableAlias: String? = null, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : QueryExpression() /** @@ -195,7 +195,7 @@ public data class UnaryExpression( val operand: ScalarExpression<*>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -301,7 +301,7 @@ public data class BinaryExpression( val right: ScalarExpression<*>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -318,7 +318,7 @@ public data class TableExpression( val catalog: String? = null, val schema: String? = null, override val isLeafNode: Boolean = true, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : QuerySourceExpression() /** @@ -332,7 +332,7 @@ public data class ColumnExpression( val name: String, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -348,7 +348,7 @@ public data class ColumnDeclaringExpression( val declaredName: String? = null, override val sqlType: SqlType = expression.sqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() { override fun aliased(label: String?): ColumnDeclaringExpression { @@ -386,7 +386,7 @@ public data class OrderByExpression( val expression: ScalarExpression<*>, val orderType: OrderType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() /** @@ -433,7 +433,7 @@ public data class JoinExpression( val right: QuerySourceExpression, val condition: ScalarExpression? = null, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : QuerySourceExpression() /** @@ -451,7 +451,7 @@ public data class InListExpression( val notInList: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -465,7 +465,7 @@ public data class ExistsExpression( val notExists: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -516,7 +516,7 @@ public data class AggregateExpression( val isDistinct: Boolean, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -534,7 +534,7 @@ public data class BetweenExpression( val notBetween: Boolean = false, override val sqlType: SqlType = BooleanSqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() @@ -547,10 +547,12 @@ public data class BetweenExpression( * * @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( - val whenThenConditions: List, ScalarExpression>>, +public data class CaseWhenExpression( + val caseExpr: ScalarExpression? = null, + val whenThenConditions: List, ScalarExpression>>, override val sqlType: SqlType, val elseExpr: ScalarExpression? = null, override val isLeafNode: Boolean = true, @@ -567,7 +569,7 @@ public data class ArgumentExpression( val value: T?, override val sqlType: SqlType, override val isLeafNode: Boolean = true, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -581,7 +583,7 @@ public data class FunctionExpression( val arguments: List>, override val sqlType: SqlType, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : ScalarExpression() /** @@ -594,7 +596,7 @@ public data class ColumnAssignmentExpression( val column: ColumnExpression, val expression: ScalarExpression, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() /** @@ -607,7 +609,7 @@ public data class InsertExpression( val table: TableExpression, val assignments: List>, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() /** @@ -622,7 +624,7 @@ public data class InsertFromQueryExpression( val columns: List>, val query: QueryExpression, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() /** @@ -637,7 +639,7 @@ public data class UpdateExpression( val assignments: List>, val where: ScalarExpression? = null, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() /** @@ -650,5 +652,5 @@ public data class DeleteExpression( val table: TableExpression, val where: ScalarExpression?, override val isLeafNode: Boolean = false, - override val extraProperties: Map = emptyMap(), + override val extraProperties: Map = emptyMap() ) : SqlExpression() diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt index b71d01f1..2febb19e 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt @@ -519,8 +519,13 @@ public abstract class SqlFormatter( } - override fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { + override fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { writeKeyword("case ") + if (expr.caseExpr != null) { + visit(expr.caseExpr) + write(" ") + + } expr.whenThenConditions.forEach { (condition, result) -> writeKeyword("when ") visit(condition) diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index dce41331..49dc1c12 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt @@ -3,7 +3,6 @@ package org.ktorm.dsl import org.junit.Test import org.ktorm.BaseTest import org.ktorm.expression.ArgumentExpression -import org.ktorm.expression.CaseWhenExpression import org.ktorm.expression.ScalarExpression import org.ktorm.schema.VarcharSqlType import kotlin.random.Random @@ -290,13 +289,14 @@ class QueryTest : BaseTest() { @Test fun `test case when`() { - val caseWhen = CaseWhenExpression( - whenThenConditions = listOf( - ("vince" eq Employees.name) to ArgumentExpression("hello vince", VarcharSqlType), - ), - elseExpr = ArgumentExpression("hello world", VarcharSqlType), - sqlType = VarcharSqlType - ).aliased("c") + val caseWhen = + case(VarcharSqlType) { + whenThen( + `when` = ("vince" eq Employees.name), + then = ArgumentExpression("hello vince", VarcharSqlType) + ) + `else`(ArgumentExpression("hello world", VarcharSqlType)) + }.aliased("c") val names = database .from(Employees) .select( @@ -313,20 +313,22 @@ class QueryTest : BaseTest() { @Test fun `test case when without else`() { - val casWhen = CaseWhenExpression( - whenThenConditions = listOf( - ("vince" eq Employees.name) to ArgumentExpression("hello vince", VarcharSqlType), - ), - sqlType = VarcharSqlType - ).aliased("c") + val caseWhen = + case(VarcharSqlType) { + whenThen( + `when` = ("vince" eq Employees.name), + then = ArgumentExpression("hello vince", VarcharSqlType) + ) + } + .aliased("c") val names = database .from(Employees) .select( - casWhen + caseWhen ) .where { Employees.departmentId eq 1 } .orderBy(Employees.salary.desc()) - .flatMapIndexed { index, row -> listOf("$index:${row[casWhen]}") } + .flatMapIndexed { index, row -> listOf("$index:${row[caseWhen]}") } assert(names.size == 2) assert(names[0] == "0:hello vince") @@ -335,22 +337,95 @@ class QueryTest : BaseTest() { @Test fun `test case when without when and else`() { - val casWhen = CaseWhenExpression( - whenThenConditions = listOf( - ), - sqlType = VarcharSqlType - ).aliased("c") + val caseWhen = + case(VarcharSqlType) { + }.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:null") + assert(names[1] == "1:null") + } + + + @Test + fun `test case when with caseValue`() { + val caseWhen = + case(VarcharSqlType, Employees.name) { + whenThen( + `when` = "vince", + then = "hello vince" + ) + whenThen( + `when` = ArgumentExpression("vince1", VarcharSqlType), + then = "hello vince1" + ) + `else`("hello world") + }.aliased("c") + 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:hello vince") + assert(names[1] == "1:hello world") + } + + @Test + fun `test case when with caseValue without else`() { + val caseWhen = + case(VarcharSqlType, Employees.name) { + whenThen( + `when` = "vince", + then = "hello vince" + ) + } + .aliased("c") + 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:hello vince") + assert(names[1] == "1:null") + } + + @Test + fun `test case when with caseValue without when and else`() { + val caseWhen = + case(VarcharSqlType, Employees.name) { + + }.aliased("c") val names = database .from(Employees) .select( - casWhen + caseWhen ) .where { Employees.departmentId eq 1 } .orderBy(Employees.salary.desc()) - .flatMapIndexed { index, row -> listOf("$index:${row[casWhen]}") } + .flatMapIndexed { index, row -> listOf("$index:${row[caseWhen]}") } assert(names.size == 2) assert(names[0] == "0:null") assert(names[1] == "1:null") } + + } From 900c01f074faa3eb0c7680da95aa3da132710bc6 Mon Sep 17 00:00:00 2001 From: zuisong Date: Fri, 15 Jul 2022 22:41:41 +0800 Subject: [PATCH 3/6] case when infix dsl --- .../org/ktorm/dsl/CaseWhenExpression.kt | 101 ++++++++++++++++++ .../org/ktorm/expression/SqlExpressions.kt | 3 +- .../test/kotlin/org/ktorm/dsl/QueryTest.kt | 68 ++++++++++++ 3 files changed, 171 insertions(+), 1 deletion(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt index dfe3cfb5..6d843061 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt @@ -14,6 +14,8 @@ * limitations under the License. */ + + package org.ktorm.dsl import org.ktorm.expression.ArgumentExpression @@ -33,6 +35,7 @@ public fun case( builder(caseWhenBuilder) return CaseWhenExpression( + whenSqlType = BooleanSqlType, whenThenConditions = caseWhenBuilder.whenThenExprList, elseExpr = caseWhenBuilder.elseExpr, sqlType = resultType @@ -50,6 +53,7 @@ public fun case( builder(caseWhenBuilder) return CaseWhenExpression( + whenSqlType = value.sqlType, caseExpr = value.asExpression(), whenThenConditions = caseWhenBuilder.whenThenExprList, elseExpr = caseWhenBuilder.elseExpr, @@ -96,3 +100,100 @@ public class CaseWhenBuilder( } + +public data class CaseExpression internal constructor( + internal val caseValue: ScalarExpression? = null, + internal val caseSqlType: SqlType, +) + +@Suppress("FunctionName") +public fun CASE(): CaseExpression { + return CaseExpression(null, BooleanSqlType) +} + +@Suppress("FunctionName") +public fun CASE(caseValue: ColumnDeclaring): CaseExpression { + return CaseExpression(caseValue.asExpression(), caseValue.sqlType) +} + +public data class WhenExpression internal constructor( + internal val caseValue: ScalarExpression?, + internal val condition: ScalarExpression, +) + +@Suppress("FunctionName") +public infix fun CaseExpression.WHEN(condition: ColumnDeclaring): WhenExpression { + return WhenExpression( + this.caseValue, + condition.asExpression(), + ) +} + +@Suppress("FunctionName") +public infix fun CaseExpression.WHEN(condition: T): WhenExpression { + return WhenExpression( + this.caseValue, + ArgumentExpression(condition, this.caseSqlType), + ) +} + + +@Suppress("FunctionName") +public infix fun WhenExpression.THEN(value: ColumnDeclaring): CaseWhenExpression { + return CaseWhenExpression( + caseExpr = this.caseValue, + whenThenConditions = listOf(this.condition to value.asExpression()), + whenSqlType = this.condition.sqlType, + sqlType = value.sqlType, + ) +} + +@Suppress("FunctionName") +public infix fun CaseWhenExpression.ELSE(value: ColumnDeclaring): CaseWhenExpression { + return this.copy(elseExpr = value.asExpression()) +} + +@Suppress("FunctionName") +public infix fun CaseWhenExpression.ELSE(value: V): CaseWhenExpression { + return this.copy(elseExpr = ArgumentExpression(value, this.sqlType)) +} + +public data class When2Expression internal constructor( + internal val caseWhenExpression: CaseWhenExpression, + internal val condition: ScalarExpression, +) + +@Suppress("FunctionName") +public infix fun CaseWhenExpression.WHEN(condition: ColumnDeclaring): When2Expression { + return When2Expression( + caseWhenExpression = this, + condition = condition.asExpression(), + ) +} + +@Suppress("FunctionName") +public infix fun CaseWhenExpression.WHEN(condition: T): When2Expression { + return When2Expression( + caseWhenExpression = this, + condition = ArgumentExpression(condition, this.whenSqlType), + ) +} + +@Suppress("FunctionName") +public infix fun When2Expression.THEN(value: ColumnDeclaring): CaseWhenExpression { + return this.caseWhenExpression.copy( + whenThenConditions = this.caseWhenExpression.whenThenConditions + (this.condition to value.asExpression()) + ) +} + +@Suppress("FunctionName") +public infix fun When2Expression.THEN(value: V): CaseWhenExpression { + return this.caseWhenExpression.copy( + whenThenConditions = this.caseWhenExpression.whenThenConditions + (this.condition to ArgumentExpression( + value, + this.caseWhenExpression.sqlType + )) + ) +} + + diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt index c4c41f4a..7079080e 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt @@ -550,9 +550,10 @@ public data class BetweenExpression( * @property caseExpr case statements, optional, may be null. * @property sqlType the argument's [SqlType]. */ -public data class CaseWhenExpression( +public data class CaseWhenExpression internal constructor( val caseExpr: ScalarExpression? = null, val whenThenConditions: List, ScalarExpression>>, + internal val whenSqlType: SqlType, override val sqlType: SqlType, val elseExpr: ScalarExpression? = null, override val isLeafNode: Boolean = true, diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index 49dc1c12..a63a4cce 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt @@ -4,6 +4,7 @@ import org.junit.Test import org.ktorm.BaseTest import org.ktorm.expression.ArgumentExpression import org.ktorm.expression.ScalarExpression +import org.ktorm.schema.IntSqlType import org.ktorm.schema.VarcharSqlType import kotlin.random.Random @@ -427,5 +428,72 @@ class QueryTest : BaseTest() { assert(names[1] == "1:null") } + @Test + fun `test case when with dsl`() { + val caseWhen = + CASE() + .WHEN(Employees.name eq "vince").THEN(ArgumentExpression(1, IntSqlType)) + .WHEN(Employees.name eq "mary").THEN(ArgumentExpression(2, IntSqlType)) + .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 + 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") + } } + From 747e2012c67146d35573f74bea681626e58eacfd Mon Sep 17 00:00:00 2001 From: zuisong Date: Sat, 16 Jul 2022 15:56:25 +0800 Subject: [PATCH 4/6] remove old dsl --- .../org/ktorm/dsl/CaseWhenExpression.kt | 76 --------- .../test/kotlin/org/ktorm/dsl/QueryTest.kt | 144 +----------------- 2 files changed, 1 insertion(+), 219 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt index 6d843061..65bc47cf 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt @@ -24,82 +24,6 @@ import org.ktorm.expression.ScalarExpression import org.ktorm.schema.BooleanSqlType import org.ktorm.schema.ColumnDeclaring import org.ktorm.schema.SqlType -import java.util.* - -public fun case( - resultType: SqlType, - builder: CaseWhenBuilder.() -> Unit, -): CaseWhenExpression { - - val caseWhenBuilder = CaseWhenBuilder(BooleanSqlType, resultType) - builder(caseWhenBuilder) - - return CaseWhenExpression( - whenSqlType = BooleanSqlType, - whenThenConditions = caseWhenBuilder.whenThenExprList, - elseExpr = caseWhenBuilder.elseExpr, - sqlType = resultType - ) -} - -public fun case( - resultType: SqlType, - value: ColumnDeclaring, - builder: CaseWhenBuilder.() -> Unit, -): CaseWhenExpression { - - - val caseWhenBuilder = CaseWhenBuilder(value.sqlType, resultType) - builder(caseWhenBuilder) - - return CaseWhenExpression( - whenSqlType = value.sqlType, - caseExpr = value.asExpression(), - whenThenConditions = caseWhenBuilder.whenThenExprList, - elseExpr = caseWhenBuilder.elseExpr, - sqlType = resultType - ) -} - - -public class CaseWhenBuilder( - private val caseSqlType: SqlType, - private val resultType: SqlType, -) { - - internal val whenThenExprList: LinkedList, ScalarExpression>> = LinkedList() - internal var elseExpr: ScalarExpression? = null - - public fun whenThen(`when`: T, then: ScalarExpression) { - whenThenExprList.add( - ArgumentExpression(`when`, caseSqlType) to then - ) - } - - public fun whenThen(`when`: T, then: V?) { - whenThenExprList.add( - ArgumentExpression(`when`, caseSqlType) to ArgumentExpression(then, resultType) - ) - } - - public fun whenThen(`when`: ScalarExpression, then: ScalarExpression) { - whenThenExprList.add(`when` to then) - } - - public fun whenThen(`when`: ScalarExpression, then: V?) { - whenThenExprList.add(`when` to ArgumentExpression(then, resultType)) - } - - public fun `else`(`else`: ScalarExpression) { - this.elseExpr = `else` - } - - public fun `else`(`else`: V?) { - this.elseExpr = ArgumentExpression(`else`, resultType) - } - - -} public data class CaseExpression internal constructor( internal val caseValue: ScalarExpression? = null, diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index a63a4cce..811c1848 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt @@ -5,7 +5,6 @@ import org.ktorm.BaseTest import org.ktorm.expression.ArgumentExpression import org.ktorm.expression.ScalarExpression import org.ktorm.schema.IntSqlType -import org.ktorm.schema.VarcharSqlType import kotlin.random.Random /** @@ -287,149 +286,8 @@ class QueryTest : BaseTest() { assert(names[1] == "1:marry") } - - @Test - fun `test case when`() { - val caseWhen = - case(VarcharSqlType) { - whenThen( - `when` = ("vince" eq Employees.name), - then = ArgumentExpression("hello vince", VarcharSqlType) - ) - `else`(ArgumentExpression("hello world", VarcharSqlType)) - }.aliased("c") - 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:hello vince") - assert(names[1] == "1:hello world") - } - - @Test - fun `test case when without else`() { - val caseWhen = - case(VarcharSqlType) { - whenThen( - `when` = ("vince" eq Employees.name), - then = ArgumentExpression("hello vince", VarcharSqlType) - ) - } - .aliased("c") - 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:hello vince") - assert(names[1] == "1:null") - } - - @Test - fun `test case when without when and else`() { - val caseWhen = - case(VarcharSqlType) { - }.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:null") - assert(names[1] == "1:null") - } - - - @Test - fun `test case when with caseValue`() { - val caseWhen = - case(VarcharSqlType, Employees.name) { - whenThen( - `when` = "vince", - then = "hello vince" - ) - whenThen( - `when` = ArgumentExpression("vince1", VarcharSqlType), - then = "hello vince1" - ) - `else`("hello world") - }.aliased("c") - 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:hello vince") - assert(names[1] == "1:hello world") - } - - @Test - fun `test case when with caseValue without else`() { - val caseWhen = - case(VarcharSqlType, Employees.name) { - whenThen( - `when` = "vince", - then = "hello vince" - ) - } - .aliased("c") - 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:hello vince") - assert(names[1] == "1:null") - } - - @Test - fun `test case when with caseValue without when and else`() { - val caseWhen = - case(VarcharSqlType, Employees.name) { - - }.aliased("c") - 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:null") - assert(names[1] == "1:null") - } - @Test - fun `test case when with dsl`() { + fun `test case when with function chain`() { val caseWhen = CASE() .WHEN(Employees.name eq "vince").THEN(ArgumentExpression(1, IntSqlType)) From 2ab88fdf710cc0be4995ffc8e876b74d3545a195 Mon Sep 17 00:00:00 2001 From: zuisong Date: Sat, 16 Jul 2022 16:39:11 +0800 Subject: [PATCH 5/6] change dsl, add docs --- .../org/ktorm/dsl/CaseWhenExpression.kt | 143 +++++++++++++----- .../ktorm/expression/SqlExpressionVisitor.kt | 23 ++- .../org/ktorm/expression/SqlExpressions.kt | 3 +- .../org/ktorm/expression/SqlFormatter.kt | 2 - .../test/kotlin/org/ktorm/dsl/QueryTest.kt | 2 +- 5 files changed, 127 insertions(+), 46 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt index 65bc47cf..78f53bb4 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt @@ -14,8 +14,6 @@ * limitations under the License. */ - - package org.ktorm.dsl import org.ktorm.expression.ArgumentExpression @@ -25,45 +23,113 @@ 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 internal constructor( internal val caseValue: ScalarExpression? = null, internal val caseSqlType: SqlType, ) +/** + * 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 internal constructor( + internal val caseValue: ScalarExpression?, + internal val condition: ScalarExpression, + internal val caseWhenExpression: CaseWhenExpression? = null, +) + +/** + * DSL to build [CaseExpression] without [CaseExpression.caseValue]. + */ @Suppress("FunctionName") public fun CASE(): CaseExpression { return CaseExpression(null, BooleanSqlType) } +/** + * DSL to build [CaseExpression] with [CaseExpression.caseValue]. + */ @Suppress("FunctionName") public fun CASE(caseValue: ColumnDeclaring): CaseExpression { return CaseExpression(caseValue.asExpression(), caseValue.sqlType) } -public data class WhenExpression internal constructor( - internal val caseValue: ScalarExpression?, - internal val condition: ScalarExpression, -) - +/** + * DSL to build [WhenExpression] from [CaseExpression]. + */ @Suppress("FunctionName") -public infix fun CaseExpression.WHEN(condition: ColumnDeclaring): WhenExpression { +public infix fun CaseExpression.WHEN(condition: ColumnDeclaring): WhenExpression { return WhenExpression( - this.caseValue, - condition.asExpression(), + caseValue = this.caseValue, + condition = condition.asExpression(), + caseWhenExpression = null ) } +/** + * DSL to build [WhenExpression] from [CaseExpression]. + */ @Suppress("FunctionName") -public infix fun CaseExpression.WHEN(condition: T): WhenExpression { +public infix fun CaseExpression.WHEN(condition: T): WhenExpression { 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 WhenExpression.THEN(value: V): CaseWhenExpression { + 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 WhenExpression.THEN(value: ColumnDeclaring): CaseWhenExpression { +public infix fun WhenExpression.THEN(value: ColumnDeclaring): CaseWhenExpression { + 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 WhenExpression.THEN( + value: ColumnDeclaring, +): CaseWhenExpression { return CaseWhenExpression( caseExpr = this.caseValue, whenThenConditions = listOf(this.condition to value.asExpression()), @@ -72,52 +138,49 @@ public infix fun WhenExpression.THEN(value: ColumnDeclarin ) } +/** + * DSL to build [CaseWhenExpression] from [CaseWhenExpression] with else branch. + * [value] the value of else branch + */ @Suppress("FunctionName") public infix fun CaseWhenExpression.ELSE(value: ColumnDeclaring): CaseWhenExpression { 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 CaseWhenExpression.ELSE(value: V): CaseWhenExpression { return this.copy(elseExpr = ArgumentExpression(value, this.sqlType)) } -public data class When2Expression internal constructor( - internal val caseWhenExpression: CaseWhenExpression, - internal val condition: ScalarExpression, -) - +/** + * DSL to build [WhenExpression] from [CaseWhenExpression]. + * + * [condition] the when condition + */ @Suppress("FunctionName") -public infix fun CaseWhenExpression.WHEN(condition: ColumnDeclaring): When2Expression { - return When2Expression( +public infix fun CaseWhenExpression.WHEN(condition: ColumnDeclaring): WhenExpression { + return WhenExpression( caseWhenExpression = this, condition = condition.asExpression(), + caseValue = null ) } +/** + * DSL to build [WhenExpression] from [CaseWhenExpression]. + * + * [condition] the when condition + */ @Suppress("FunctionName") -public infix fun CaseWhenExpression.WHEN(condition: T): When2Expression { - return When2Expression( +public infix fun CaseWhenExpression.WHEN(condition: T): WhenExpression { + return WhenExpression( caseWhenExpression = this, condition = ArgumentExpression(condition, this.whenSqlType), + caseValue = null, ) } - -@Suppress("FunctionName") -public infix fun When2Expression.THEN(value: ColumnDeclaring): CaseWhenExpression { - return this.caseWhenExpression.copy( - whenThenConditions = this.caseWhenExpression.whenThenConditions + (this.condition to value.asExpression()) - ) -} - -@Suppress("FunctionName") -public infix fun When2Expression.THEN(value: V): CaseWhenExpression { - return this.caseWhenExpression.copy( - whenThenConditions = this.caseWhenExpression.whenThenConditions + (this.condition to ArgumentExpression( - value, - this.caseWhenExpression.sqlType - )) - ) -} - - diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt index 3ea932aa..e4352649 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt @@ -305,7 +305,28 @@ public open class SqlExpressionVisitor { } protected open fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { - return expr + 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) + } + + return expr.copy( + caseExpr = caseExpr, + whenThenConditions = whenThenConditions, + elseExpr = elseExpr, + ) } protected open fun visitColumnAssignment( diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt index 7079080e..23f45886 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt @@ -537,7 +537,6 @@ public data class BetweenExpression( override val extraProperties: Map = emptyMap() ) : ScalarExpression() - /** * 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 @@ -553,9 +552,9 @@ public data class BetweenExpression( public data class CaseWhenExpression internal constructor( val caseExpr: ScalarExpression? = null, val whenThenConditions: List, ScalarExpression>>, + val elseExpr: ScalarExpression? = null, internal val whenSqlType: SqlType, override val sqlType: SqlType, - val elseExpr: ScalarExpression? = null, override val isLeafNode: Boolean = true, override val extraProperties: Map = emptyMap(), ) : ScalarExpression() diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt index 2febb19e..c4f1eaca 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt @@ -518,13 +518,11 @@ public abstract class SqlFormatter( return expr } - override fun visitCaseWhen(expr: CaseWhenExpression): CaseWhenExpression { writeKeyword("case ") if (expr.caseExpr != null) { visit(expr.caseExpr) write(" ") - } expr.whenThenConditions.forEach { (condition, result) -> writeKeyword("when ") diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index 811c1848..f0c123b5 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt @@ -291,7 +291,7 @@ class QueryTest : BaseTest() { val caseWhen = CASE() .WHEN(Employees.name eq "vince").THEN(ArgumentExpression(1, IntSqlType)) - .WHEN(Employees.name eq "mary").THEN(ArgumentExpression(2, IntSqlType)) + .WHEN(Employees.name eq "mary").THEN(2) .ELSE(ArgumentExpression(3, IntSqlType)) .aliased("caseWhen") val names = database From 46111fe2c951e98149ea90bb4ec494663443f327 Mon Sep 17 00:00:00 2001 From: zuisong Date: Mon, 18 Jul 2022 23:32:59 +0800 Subject: [PATCH 6/6] fix bad code smell --- .../main/kotlin/ktorm.maven-publish.gradle.kts | 5 +++++ .../dsl/{CaseWhenExpression.kt => CaseWhen.kt} | 0 .../ktorm/expression/SqlExpressionVisitor.kt | 17 ++++++++++++----- .../org/ktorm/expression/SqlExpressions.kt | 2 +- .../kotlin/org/ktorm/expression/SqlFormatter.kt | 2 +- .../src/test/kotlin/org/ktorm/dsl/QueryTest.kt | 1 + 6 files changed, 20 insertions(+), 7 deletions(-) rename ktorm-core/src/main/kotlin/org/ktorm/dsl/{CaseWhenExpression.kt => CaseWhen.kt} (100%) diff --git a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts index 7241ebfd..a6926602 100644 --- 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/CaseWhenExpression.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhen.kt similarity index 100% rename from ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhenExpression.kt rename to ktorm-core/src/main/kotlin/org/ktorm/dsl/CaseWhen.kt diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt index e4352649..42e63b5a 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressionVisitor.kt @@ -322,11 +322,18 @@ public open class SqlExpressionVisitor { visitScalar(condition) to visitScalar(value) } - return expr.copy( - caseExpr = caseExpr, - whenThenConditions = whenThenConditions, - elseExpr = elseExpr, - ) + 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 visitColumnAssignment( diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt index 23f45886..92e9b161 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlExpressions.kt @@ -549,7 +549,7 @@ public data class BetweenExpression( * @property caseExpr case statements, optional, may be null. * @property sqlType the argument's [SqlType]. */ -public data class CaseWhenExpression internal constructor( +public data class CaseWhenExpression constructor( val caseExpr: ScalarExpression? = null, val whenThenConditions: List, ScalarExpression>>, val elseExpr: ScalarExpression? = null, diff --git a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt index c4f1eaca..b3f7e38a 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/expression/SqlFormatter.kt @@ -524,7 +524,7 @@ public abstract class SqlFormatter( visit(expr.caseExpr) write(" ") } - expr.whenThenConditions.forEach { (condition, result) -> + for ((condition, result) in expr.whenThenConditions) { writeKeyword("when ") visit(condition) writeKeyword("then ") diff --git a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt index f0c123b5..36fde4ac 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/dsl/QueryTest.kt @@ -337,6 +337,7 @@ class QueryTest : BaseTest() { CASE(Employees.name) WHEN "vince" THEN Employees.id WHEN "mary" THEN 2 + WHEN "mary1" THEN Employees.departmentId ELSE 3 ).aliased("caseWhen") val names = database