From 8d616fb15e63825738b11d124c0a74e4f050f77d Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 17:40:58 +0800 Subject: [PATCH 01/12] Conditional table implement --- .../org/ktorm/schema/ConditionalTable.kt | 211 ++++++++++++++++++ .../ktorm/conditional/ConditionalTableTest.kt | 105 +++++++++ 2 files changed, 316 insertions(+) create mode 100644 ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt create mode 100644 ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt new file mode 100644 index 00000000..cbbd14c6 --- /dev/null +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -0,0 +1,211 @@ +package org.ktorm.schema + +import org.ktorm.dsl.Query +import org.ktorm.dsl.and +import org.ktorm.dsl.where +import org.ktorm.entity.Entity +import org.ktorm.entity.EntitySequence +import org.ktorm.entity.filter +import kotlin.reflect.KClass + + +/** + * An extended type of [Table], implemented to allow default condition generation for fields by entity query. + * + * Provide default fill condition rules for entity queries via [Column.conditionOn] or [Column.conditionNotNullOn], + * e.g. + * ```kotlin + * interface Department : Entity { + * val id: Int + * var name: String + * var location: String + * } + * + * + * object Departments : Table("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * if (value != null) column eq value else column eq 1 + * } + * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { department, column, value -> + * column like "%$value%" + * } + * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field + * } + * ``` + * + * Then, use [filterBy] or [whereBy] to query by entity objects. + * ```kotlin + * val entity = Department { + * // ... + * } + * // by EntitySequence + * database.departments + * .filterBy(entity) + * // Other operations... + * .forEach { + * println(it) + * } + * + * // by Query + * database.from(Departments) + * .select() + * .whereBy(Departments, entity) + * // Other operations... + * .forEach { + * println(it) + * } + * + * ``` + * + * @see Table + * + * @author ForteScarlet + */ +public open class ConditionalTable>( + tableName: String, + alias: String? = null, + catalog: String? = null, + schema: String? = null, + entityClass: KClass? = null +) : Table(tableName, alias, catalog, schema, entityClass) { + private val columnConditions = mutableMapOf ColumnDeclaring?>() + + /** + * Provides a query condition for the current field(column) to be used when querying by entity. + * e.g. + * ```kotlin + * object Departments : ConditionalTable("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * if (value != null) column eq value else column eq 1 + * } + * } + * ``` + * @see conditionNotNullOn + * + * @param condition the query condition. + */ + public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { + return saveConditionOn { entity, columnValue -> + condition(entity, this, columnValue as? C) + } + } + + /** + * Provides a query condition for the current field(column) to be used when querying by entity. + * e.g. + * ```kotlin + * object Departments : ConditionalTable("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { department, column, value -> + * column eq value + * } + * } + * ``` + * @see conditionOn + * + * @param condition the query condition. + */ + public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { + return saveConditionOn { entity, columnValue -> + val value = columnValue as C? + if (value != null) { + condition(entity, this, columnValue as C) + } else { + null + } + } + } + + + @PublishedApi + internal fun Column.saveConditionOn(condition: (E, Any?) -> ColumnDeclaring?): Column { + this.name + + // merge with 'and' + columnConditions.merge(name, condition) { old, curr -> + { entity, value -> + val condition1 = old(entity, value) + val condition2 = curr(entity, value) + when { + condition1 == null && condition2 == null -> null + condition1 == null -> condition2 + condition2 == null -> condition1 + else -> condition1 and condition2 + } + } + } + return this + } + + + /** + * Translate the provided entity classes into query conditions. + * + * @param entity entity of this table. + * @return Query conditions as [ColumnDeclaring]<Boolean%gt;, May be null if no condition is generated. + */ + public fun asCondition(entity: E): ColumnDeclaring? { + val properties = entity.properties + // TODO + // columnConditions.entries.fold ColumnDeclaring?>, ColumnDeclaring?>( + // null + // ) { left, (key, value) -> + // val property = properties[key] + // + // + // TODO() + // } + + return entity.properties.entries.fold, ColumnDeclaring?>(null) { left, (key, value) -> + val conditionFactory = columnConditions[key] ?: return@fold left + val condition = conditionFactory(entity, value) ?: return@fold left + if (left == null) condition else left and condition + } + } + + +} + + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined by each field in [ConditionalTable]. + * + * ```kotlin + * database.departments + * .filterBy(entity) + * // Other operations... + * .forEach { + * println(it) + * } + * ``` + * + * @see ConditionalTable + */ +public fun , T : ConditionalTable> EntitySequence.filterBy(conditionEntity: E): EntitySequence { + return sourceTable.asCondition(conditionEntity) + ?.let { condition -> filter { condition } } + ?: this + +} + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined in each field of [table] of type [ConditionalTable]. + * + * ```kotlin + * // by Query + * database.from(Departments) + * .select() + * .whereBy(Departments, entity) + * // Other operations... + * .forEach { + * println(it) + * } + * ``` + * + * @see ConditionalTable + */ +public fun , T : ConditionalTable> Query.whereBy(table: T, conditionEntity: E): Query { + return table.asCondition(conditionEntity)?.let { this.where(it) } ?: this +} + diff --git a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt new file mode 100644 index 00000000..e37e816c --- /dev/null +++ b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt @@ -0,0 +1,105 @@ +package org.ktorm.conditional + +import org.junit.Test +import org.ktorm.BaseTest +import org.ktorm.database.Database +import org.ktorm.dsl.* +import org.ktorm.entity.forEach +import org.ktorm.entity.sequenceOf +import org.ktorm.schema.* + +/** + * + * @author ForteScarlet + */ +class ConditionalTableTest : BaseTest() { + + @Test + fun testCondition() { + println("a") + println("a") + println("a") + println("a") + println("a") + println("a") + println("a") + val entity1 = Department { + name = "te" + } + + database.departmentsWithCondition + .filterBy(entity1) + .forEach { + println(it) + assert(it.name.startsWith(entity1.name)) + assert(it.mixedCase != null) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity1) + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity1.name)) + assert(it[DepartmentsWithCondition.mixedCase] != null) + } + + + } + + + open class DepartmentsWithCondition(alias: String?) : ConditionalTable("t_department", alias) { + companion object : DepartmentsWithCondition(null) + + override fun aliased(alias: String) = DepartmentsWithCondition(alias) + + val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { _, c, v -> + c eq v + } + val name = varchar("name").bindTo { it.name }.conditionNotNullOn { _, c, v -> + c like "$v%" + } + + val location = varchar("location").transform({ LocationWrapper(it) }, { it.underlying }).bindTo { it.location } + .conditionOn { _, column, locationWrapper -> + if (locationWrapper == null) { + (column.table as DepartmentsWithCondition).mixedCase.isNotNull() + } else { + column eq locationWrapper + } + + } + val mixedCase = varchar("mixedCase").bindTo { it.mixedCase } + } + + open class EmployeesWithCondition(alias: String?) : ConditionalTable("t_employee", alias) { + companion object : EmployeesWithCondition(null) + + override fun aliased(alias: String) = EmployeesWithCondition(alias) + + val id = int("id").primaryKey().bindTo { it.id } + val name = varchar("name").bindTo { it.name } + val job = varchar("job").bindTo { it.job } + val managerId = int("manager_id").bindTo { it.manager?.id } + val hireDate = date("hire_date").bindTo { it.hireDate } + val salary = long("salary").bindTo { it.salary } + val departmentId = int("department_id").references(Departments) { it.department } + val department = departmentId.referenceTable as Departments + } + + open class CustomersWithC(alias: String?) : ConditionalTable("t_customer", alias, schema = "company") { + companion object : CustomersWithC(null) + + override fun aliased(alias: String) = CustomersWithC(alias) + + val id = int("id").primaryKey().bindTo { it.id } + val name = varchar("name").bindTo { it.name } + val email = varchar("email").bindTo { it.email } + val phoneNumber = varchar("phone_number").bindTo { it.phoneNumber } + } + + val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) + + val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) + + val Database.customersWithC get() = this.sequenceOf(CustomersWithC) +} \ No newline at end of file From 20caa48c69f2e3a27491a4d726e63007d07a498b Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 21:12:21 +0800 Subject: [PATCH 02/12] update developer? --- buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts index 1e003a57..72a47062 100644 --- a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts +++ b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts @@ -121,6 +121,11 @@ publishing { name.set("Eric Fenderbosch") email.set("eric@fender.net") } + developer { + id.set("forte") + name.set("Forte Scarlet") + email.set("ForteScarlet@163.com") + } } } } From 132b5cb4a86b11cca92c40630ac15a373ef79c9a Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 22:35:51 +0800 Subject: [PATCH 03/12] =?UTF-8?q?=E5=AE=9E=E7=8E=B0:=20filterBy,=20whereBy?= =?UTF-8?q?,=20filterByOr,=20whereByOr,=20and=20ConditionalTable.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../org/ktorm/schema/ConditionalTable.kt | 183 ++++++++++++++---- .../ktorm/conditional/ConditionalTableTest.kt | 161 +++++++++++++-- 2 files changed, 284 insertions(+), 60 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index cbbd14c6..7f46eea8 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -3,9 +3,7 @@ package org.ktorm.schema import org.ktorm.dsl.Query import org.ktorm.dsl.and import org.ktorm.dsl.where -import org.ktorm.entity.Entity -import org.ktorm.entity.EntitySequence -import org.ktorm.entity.filter +import org.ktorm.entity.* import kotlin.reflect.KClass @@ -29,7 +27,7 @@ import kotlin.reflect.KClass * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { department, column, value -> * column like "%$value%" * } - * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field + * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field(column) * } * ``` * @@ -38,6 +36,7 @@ import kotlin.reflect.KClass * val entity = Department { * // ... * } + * * // by EntitySequence * database.departments * .filterBy(entity) @@ -58,6 +57,8 @@ import kotlin.reflect.KClass * ``` * * @see Table + * @see filterBy + * @see whereBy * * @author ForteScarlet */ @@ -68,7 +69,7 @@ public open class ConditionalTable>( schema: String? = null, entityClass: KClass? = null ) : Table(tableName, alias, catalog, schema, entityClass) { - private val columnConditions = mutableMapOf ColumnDeclaring?>() + private val columnConditions = mutableMapOf ColumnDeclaring?>() /** * Provides a query condition for the current field(column) to be used when querying by entity. @@ -85,11 +86,13 @@ public open class ConditionalTable>( * @param condition the query condition. */ public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { - return saveConditionOn { entity, columnValue -> - condition(entity, this, columnValue as? C) + return saveColumnCondition { entity, entityImpl -> + val value = entityImpl.getColumnValueOrNull(this) + condition(entity, this, value as C?) } } + /** * Provides a query condition for the current field(column) to be used when querying by entity. * e.g. @@ -105,10 +108,10 @@ public open class ConditionalTable>( * @param condition the query condition. */ public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { - return saveConditionOn { entity, columnValue -> - val value = columnValue as C? + return saveColumnCondition { entity, entityImpl -> + val value = entityImpl.getColumnValueOrThrow(this) if (value != null) { - condition(entity, this, columnValue as C) + condition(entity, this, value as C) } else { null } @@ -117,14 +120,28 @@ public open class ConditionalTable>( @PublishedApi - internal fun Column.saveConditionOn(condition: (E, Any?) -> ColumnDeclaring?): Column { - this.name + internal fun EntityImplementation.getColumnValueOrNull(column: Column<*>): Any? { + return column.binding?.let { b -> getColumnValue(b) } + } + + + @PublishedApi + internal fun EntityImplementation.getColumnValueOrThrow(column: Column<*>): Any? { + val binding = column.binding + if (binding != null) { + return getColumnValue(binding) + } + + error("Column $column has no bindings to any entity field.") + } - // merge with 'and' + @PublishedApi + internal fun Column.saveColumnCondition(condition: (E, EntityImplementation) -> ColumnDeclaring?): Column { + // merge by 'and' columnConditions.merge(name, condition) { old, curr -> - { entity, value -> - val condition1 = old(entity, value) - val condition2 = curr(entity, value) + { entity, entityImplementation -> + val condition1 = old(entity, entityImplementation) + val condition2 = curr(entity, entityImplementation) when { condition1 == null && condition2 == null -> null condition1 == null -> condition2 @@ -144,21 +161,16 @@ public open class ConditionalTable>( * @return Query conditions as [ColumnDeclaring]<Boolean%gt;, May be null if no condition is generated. */ public fun asCondition(entity: E): ColumnDeclaring? { - val properties = entity.properties - // TODO - // columnConditions.entries.fold ColumnDeclaring?>, ColumnDeclaring?>( - // null - // ) { left, (key, value) -> - // val property = properties[key] - // - // - // TODO() - // } - - return entity.properties.entries.fold, ColumnDeclaring?>(null) { left, (key, value) -> - val conditionFactory = columnConditions[key] ?: return@fold left - val condition = conditionFactory(entity, value) ?: return@fold left - if (left == null) condition else left and condition + val entityImplementation = entity.implementation + return columnConditions.values.fold<(E, EntityImplementation) -> ColumnDeclaring?, ColumnDeclaring?>( + null + ) { left, factory -> + val declaring = factory(entity, entityImplementation) + if (left == null) { + declaring + } else { + if (declaring == null) left else left and declaring + } } } @@ -174,16 +186,32 @@ public open class ConditionalTable>( * database.departments * .filterBy(entity) * // Other operations... - * .forEach { - * println(it) + * .forEach { println(it) } + * ``` + * + * or + * + * ```kotlin + * database.departments + * .filterBy(entity) { table, condition -> + * condition and (table.location eq LocationWrapper("GuangZhou")) * } + * // Other operations... + * .forEach { println(it) } * ``` * + * If you want to handle the case when the condition may be null, you can refer to [filterByOr]. + * + * @param andThen When the condition exists, you can operate on it. + * * @see ConditionalTable + * @see filterByOr */ -public fun , T : ConditionalTable> EntitySequence.filterBy(conditionEntity: E): EntitySequence { - return sourceTable.asCondition(conditionEntity) - ?.let { condition -> filter { condition } } +public inline fun , T : ConditionalTable> EntitySequence.filterBy( + conditionEntity: E, + andThen: ((table: T, condition: ColumnDeclaring) -> ColumnDeclaring) = { _, condition -> condition } +): EntitySequence { + return sourceTable.asCondition(conditionEntity)?.let { condition -> filter { table -> andThen(table, condition) } } ?: this } @@ -193,19 +221,92 @@ public fun , T : ConditionalTable> EntitySequence.filterB * on the conditions defined in each field of [table] of type [ConditionalTable]. * * ```kotlin - * // by Query * database.from(Departments) * .select() * .whereBy(Departments, entity) * // Other operations... - * .forEach { - * println(it) + * .forEach { println(it) } + * ``` + * + * or + * + * ```kotlin + * database.from(DepartmentsWithCondition) + * .select() + * .whereBy(DepartmentsWithCondition, entity) { + * it and (Departments.location eq LocationWrapper("GuangZhou")) * } + * .forEach { println(it) } * ``` * + * If you want to handle the case when the condition may be null, you can refer to [whereByOr]. + * + * @param andThen When the condition exists, you can operate on it. + * @see ConditionalTable + * @see whereByOr + */ +public inline fun , T : ConditionalTable> Query.whereBy( + table: T, conditionEntity: E, andThen: ((condition: ColumnDeclaring) -> ColumnDeclaring) = { it } +): Query { + return table.asCondition(conditionEntity)?.let { this.where(andThen(it)) } ?: this +} + + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined by each field in [ConditionalTable]. + * + * + * ```kotlin + * database.departments + * .filterByOr(entity) { table, condition -> + * condition?.and(table.location eq LocationWrapper("GuangZhou")) // nullable. + * } + * // Other operations... + * .forEach { println(it) } + * ``` + * + * If you only care about the presence of conditions, you can refer to [filterBy]. + * + * @param andThen When the condition exists, you can operate on it. + * + * @see ConditionalTable + * @see filterBy + */ +public inline fun , T : ConditionalTable> EntitySequence.filterByOr( + conditionEntity: E, + andThen: ((table: T, condition: ColumnDeclaring?) -> ColumnDeclaring?) = { _, condition -> condition } +): EntitySequence { + return andThen(sourceTable, sourceTable.asCondition(conditionEntity))?.let { condition -> filter { condition } } + ?: this + +} + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined in each field of [table] of type [ConditionalTable]. + * + * ```kotlin + * database.from(DepartmentsWithCondition) + * .select() + * .filterByOr(DepartmentsWithCondition, entity) { + * // nullable. + * it?.and(DepartmentsWithCondition.location eq LocationWrapper("GuangZhou")) + * } + * .forEach { println(it) } + * ``` + * If you only care about the presence of conditions, you can refer to [whereBy]. + * + * @param andThen When the condition exists, you can operate on it. + * * @see ConditionalTable + * @see whereBy */ -public fun , T : ConditionalTable> Query.whereBy(table: T, conditionEntity: E): Query { - return table.asCondition(conditionEntity)?.let { this.where(it) } ?: this +public inline fun , T : ConditionalTable> Query.whereByOr( + table: T, + conditionEntity: E, + andThen: ((condition: ColumnDeclaring?) -> ColumnDeclaring?) = { it } +): Query { + return andThen(table.asCondition(conditionEntity))?.let { this.where(it) } ?: this } diff --git a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt index e37e816c..d3a682f2 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt @@ -15,37 +15,156 @@ import org.ktorm.schema.* class ConditionalTableTest : BaseTest() { @Test - fun testCondition() { - println("a") - println("a") - println("a") - println("a") - println("a") - println("a") - println("a") - val entity1 = Department { + fun testCondition1() { + val entity = Department { name = "te" } database.departmentsWithCondition - .filterBy(entity1) + .filterBy(entity) .forEach { - println(it) - assert(it.name.startsWith(entity1.name)) + assert(it.name.startsWith(entity.name)) assert(it.mixedCase != null) } database.from(DepartmentsWithCondition) .select() - .whereBy(DepartmentsWithCondition, entity1) + .whereBy(DepartmentsWithCondition, entity) .forEach { - assert(it[DepartmentsWithCondition.name]!!.startsWith(entity1.name)) + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) assert(it[DepartmentsWithCondition.mixedCase] != null) } + } + + @Test + fun testCondition2() { + val entity = Department { + name = "te" + location = LocationWrapper("Guangzhou") + } + + database.departmentsWithCondition + .filterBy(entity) + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.location.underlying == entity.location.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity) + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) + } + } + + @Test + fun testCondition3() { + val entityManagerId = 1 + val entity = Employee { + manager = Employee { + id = entityManagerId + } + } + database.employeesWithCondition + .filterBy(entity) + .forEach { + assert(it.manager?.id == entityManagerId) + } + database.from(EmployeesWithCondition) + .select() + .whereBy(EmployeesWithCondition, entity) + .forEach { + assert(it[EmployeesWithCondition.managerId] == entityManagerId) + } } + @Test + fun testCondition4() { + val entity = Employee { + salary = 50 + department = Department { + name = "dep" + } + } + database.employeesWithCondition + .filterBy(entity) + .forEach { + assert(it.salary > entity.salary) + } + + database.from(EmployeesWithCondition) + .select() + .whereBy(EmployeesWithCondition, entity) + .forEach { + assert(it[EmployeesWithCondition.salary]!! > entity.salary) + } + } + + + @Test + fun testConditionAndThen1() { + val locationWrapper = LocationWrapper("Guangzhou") + + val entity = Department { + name = "te" + } + + database.departmentsWithCondition + .filterBy(entity) { table, condition -> + condition and (table.location eq locationWrapper) + } + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.mixedCase != null) + assert(it.location.underlying != locationWrapper.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity) { + it and (DepartmentsWithCondition.location eq locationWrapper) + } + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.mixedCase] != null) + assert(it[DepartmentsWithCondition.location]?.underlying != locationWrapper.underlying) + } + } + + @Test + fun testConditionAndThen2() { + val locationWrapper = LocationWrapper("Guangzhou") + + val entity = Department { + name = "te" + } + + database.departmentsWithCondition + .filterByOr(entity) { table, condition -> + val extraCondition = table.location eq locationWrapper + condition?.and(extraCondition) ?: extraCondition + } + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.location.underlying == entity.location.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereByOr(DepartmentsWithCondition, entity) { + it?.and(DepartmentsWithCondition.location eq locationWrapper) // or null. + } + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) + } + } + + open class DepartmentsWithCondition(alias: String?) : ConditionalTable("t_department", alias) { companion object : DepartmentsWithCondition(null) @@ -61,6 +180,7 @@ class ConditionalTableTest : BaseTest() { val location = varchar("location").transform({ LocationWrapper(it) }, { it.underlying }).bindTo { it.location } .conditionOn { _, column, locationWrapper -> + println("location wrapper: $locationWrapper") if (locationWrapper == null) { (column.table as DepartmentsWithCondition).mixedCase.isNotNull() } else { @@ -79,9 +199,13 @@ class ConditionalTableTest : BaseTest() { val id = int("id").primaryKey().bindTo { it.id } val name = varchar("name").bindTo { it.name } val job = varchar("job").bindTo { it.job } - val managerId = int("manager_id").bindTo { it.manager?.id } + val managerId = int("manager_id").bindTo { it.manager?.id }.conditionNotNullOn { employee, column, i -> + column eq i + } val hireDate = date("hire_date").bindTo { it.hireDate } - val salary = long("salary").bindTo { it.salary } + val salary = long("salary").bindTo { it.salary }.conditionOn { employee, column, value -> + column greater (value ?: 0) + } val departmentId = int("department_id").references(Departments) { it.department } val department = departmentId.referenceTable as Departments } @@ -97,9 +221,8 @@ class ConditionalTableTest : BaseTest() { val phoneNumber = varchar("phone_number").bindTo { it.phoneNumber } } - val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) + private val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) - val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) + private val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) - val Database.customersWithC get() = this.sequenceOf(CustomersWithC) } \ No newline at end of file From 5fcf33db50f1d6b249ea2bf4c52e6adbe3f50814 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 22:44:09 +0800 Subject: [PATCH 04/12] =?UTF-8?q?:bulb:=20=E6=B7=BB=E5=8A=A0=E6=BA=90?= =?UTF-8?q?=E7=A0=81=E6=B3=A8=E9=87=8A?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index 7f46eea8..420657ab 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -20,7 +20,7 @@ import kotlin.reflect.KClass * } * * - * object Departments : Table("t_department") { + * object Departments : ConditionalTable("t_department") { * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> * if (value != null) column eq value else column eq 1 * } From 3c5e5859dbc32648f20b2e80891a4fb2a6c3e340 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 23:25:22 +0800 Subject: [PATCH 05/12] =?UTF-8?q?=E4=B8=BA=E5=87=BD=E6=95=B0=E5=8F=82?= =?UTF-8?q?=E6=95=B0=E5=A2=9E=E5=8A=A0=E5=8F=98=E9=87=8F=E5=90=8D=E7=A7=B0?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../src/main/kotlin/org/ktorm/schema/ConditionalTable.kt | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index 420657ab..17a14fe2 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -85,7 +85,7 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { + public inline fun Column.conditionOn(crossinline condition: (E, column: Column, value: C?) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrNull(this) condition(entity, this, value as C?) @@ -107,7 +107,7 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { + public inline fun Column.conditionNotNullOn(crossinline condition: (E, column: Column, value: C) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrThrow(this) if (value != null) { From 8a66455b0b9a170d1f9ae8e45c0d8b00de003bac Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 17:40:58 +0800 Subject: [PATCH 06/12] Conditional table implement --- .../org/ktorm/schema/ConditionalTable.kt | 211 ++++++++++++++++++ .../ktorm/conditional/ConditionalTableTest.kt | 105 +++++++++ 2 files changed, 316 insertions(+) create mode 100644 ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt create mode 100644 ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt new file mode 100644 index 00000000..cbbd14c6 --- /dev/null +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -0,0 +1,211 @@ +package org.ktorm.schema + +import org.ktorm.dsl.Query +import org.ktorm.dsl.and +import org.ktorm.dsl.where +import org.ktorm.entity.Entity +import org.ktorm.entity.EntitySequence +import org.ktorm.entity.filter +import kotlin.reflect.KClass + + +/** + * An extended type of [Table], implemented to allow default condition generation for fields by entity query. + * + * Provide default fill condition rules for entity queries via [Column.conditionOn] or [Column.conditionNotNullOn], + * e.g. + * ```kotlin + * interface Department : Entity { + * val id: Int + * var name: String + * var location: String + * } + * + * + * object Departments : Table("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * if (value != null) column eq value else column eq 1 + * } + * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { department, column, value -> + * column like "%$value%" + * } + * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field + * } + * ``` + * + * Then, use [filterBy] or [whereBy] to query by entity objects. + * ```kotlin + * val entity = Department { + * // ... + * } + * // by EntitySequence + * database.departments + * .filterBy(entity) + * // Other operations... + * .forEach { + * println(it) + * } + * + * // by Query + * database.from(Departments) + * .select() + * .whereBy(Departments, entity) + * // Other operations... + * .forEach { + * println(it) + * } + * + * ``` + * + * @see Table + * + * @author ForteScarlet + */ +public open class ConditionalTable>( + tableName: String, + alias: String? = null, + catalog: String? = null, + schema: String? = null, + entityClass: KClass? = null +) : Table(tableName, alias, catalog, schema, entityClass) { + private val columnConditions = mutableMapOf ColumnDeclaring?>() + + /** + * Provides a query condition for the current field(column) to be used when querying by entity. + * e.g. + * ```kotlin + * object Departments : ConditionalTable("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * if (value != null) column eq value else column eq 1 + * } + * } + * ``` + * @see conditionNotNullOn + * + * @param condition the query condition. + */ + public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { + return saveConditionOn { entity, columnValue -> + condition(entity, this, columnValue as? C) + } + } + + /** + * Provides a query condition for the current field(column) to be used when querying by entity. + * e.g. + * ```kotlin + * object Departments : ConditionalTable("t_department") { + * val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { department, column, value -> + * column eq value + * } + * } + * ``` + * @see conditionOn + * + * @param condition the query condition. + */ + public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { + return saveConditionOn { entity, columnValue -> + val value = columnValue as C? + if (value != null) { + condition(entity, this, columnValue as C) + } else { + null + } + } + } + + + @PublishedApi + internal fun Column.saveConditionOn(condition: (E, Any?) -> ColumnDeclaring?): Column { + this.name + + // merge with 'and' + columnConditions.merge(name, condition) { old, curr -> + { entity, value -> + val condition1 = old(entity, value) + val condition2 = curr(entity, value) + when { + condition1 == null && condition2 == null -> null + condition1 == null -> condition2 + condition2 == null -> condition1 + else -> condition1 and condition2 + } + } + } + return this + } + + + /** + * Translate the provided entity classes into query conditions. + * + * @param entity entity of this table. + * @return Query conditions as [ColumnDeclaring]<Boolean%gt;, May be null if no condition is generated. + */ + public fun asCondition(entity: E): ColumnDeclaring? { + val properties = entity.properties + // TODO + // columnConditions.entries.fold ColumnDeclaring?>, ColumnDeclaring?>( + // null + // ) { left, (key, value) -> + // val property = properties[key] + // + // + // TODO() + // } + + return entity.properties.entries.fold, ColumnDeclaring?>(null) { left, (key, value) -> + val conditionFactory = columnConditions[key] ?: return@fold left + val condition = conditionFactory(entity, value) ?: return@fold left + if (left == null) condition else left and condition + } + } + + +} + + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined by each field in [ConditionalTable]. + * + * ```kotlin + * database.departments + * .filterBy(entity) + * // Other operations... + * .forEach { + * println(it) + * } + * ``` + * + * @see ConditionalTable + */ +public fun , T : ConditionalTable> EntitySequence.filterBy(conditionEntity: E): EntitySequence { + return sourceTable.asCondition(conditionEntity) + ?.let { condition -> filter { condition } } + ?: this + +} + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined in each field of [table] of type [ConditionalTable]. + * + * ```kotlin + * // by Query + * database.from(Departments) + * .select() + * .whereBy(Departments, entity) + * // Other operations... + * .forEach { + * println(it) + * } + * ``` + * + * @see ConditionalTable + */ +public fun , T : ConditionalTable> Query.whereBy(table: T, conditionEntity: E): Query { + return table.asCondition(conditionEntity)?.let { this.where(it) } ?: this +} + diff --git a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt new file mode 100644 index 00000000..e37e816c --- /dev/null +++ b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt @@ -0,0 +1,105 @@ +package org.ktorm.conditional + +import org.junit.Test +import org.ktorm.BaseTest +import org.ktorm.database.Database +import org.ktorm.dsl.* +import org.ktorm.entity.forEach +import org.ktorm.entity.sequenceOf +import org.ktorm.schema.* + +/** + * + * @author ForteScarlet + */ +class ConditionalTableTest : BaseTest() { + + @Test + fun testCondition() { + println("a") + println("a") + println("a") + println("a") + println("a") + println("a") + println("a") + val entity1 = Department { + name = "te" + } + + database.departmentsWithCondition + .filterBy(entity1) + .forEach { + println(it) + assert(it.name.startsWith(entity1.name)) + assert(it.mixedCase != null) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity1) + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity1.name)) + assert(it[DepartmentsWithCondition.mixedCase] != null) + } + + + } + + + open class DepartmentsWithCondition(alias: String?) : ConditionalTable("t_department", alias) { + companion object : DepartmentsWithCondition(null) + + override fun aliased(alias: String) = DepartmentsWithCondition(alias) + + val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { _, c, v -> + c eq v + } + val name = varchar("name").bindTo { it.name }.conditionNotNullOn { _, c, v -> + c like "$v%" + } + + val location = varchar("location").transform({ LocationWrapper(it) }, { it.underlying }).bindTo { it.location } + .conditionOn { _, column, locationWrapper -> + if (locationWrapper == null) { + (column.table as DepartmentsWithCondition).mixedCase.isNotNull() + } else { + column eq locationWrapper + } + + } + val mixedCase = varchar("mixedCase").bindTo { it.mixedCase } + } + + open class EmployeesWithCondition(alias: String?) : ConditionalTable("t_employee", alias) { + companion object : EmployeesWithCondition(null) + + override fun aliased(alias: String) = EmployeesWithCondition(alias) + + val id = int("id").primaryKey().bindTo { it.id } + val name = varchar("name").bindTo { it.name } + val job = varchar("job").bindTo { it.job } + val managerId = int("manager_id").bindTo { it.manager?.id } + val hireDate = date("hire_date").bindTo { it.hireDate } + val salary = long("salary").bindTo { it.salary } + val departmentId = int("department_id").references(Departments) { it.department } + val department = departmentId.referenceTable as Departments + } + + open class CustomersWithC(alias: String?) : ConditionalTable("t_customer", alias, schema = "company") { + companion object : CustomersWithC(null) + + override fun aliased(alias: String) = CustomersWithC(alias) + + val id = int("id").primaryKey().bindTo { it.id } + val name = varchar("name").bindTo { it.name } + val email = varchar("email").bindTo { it.email } + val phoneNumber = varchar("phone_number").bindTo { it.phoneNumber } + } + + val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) + + val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) + + val Database.customersWithC get() = this.sequenceOf(CustomersWithC) +} \ No newline at end of file From b22905dbe02432c094c920605f69cf92437a542e Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 21:12:21 +0800 Subject: [PATCH 07/12] update developer? --- buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts index 1e003a57..72a47062 100644 --- a/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts +++ b/buildSrc/src/main/kotlin/ktorm.maven-publish.gradle.kts @@ -121,6 +121,11 @@ publishing { name.set("Eric Fenderbosch") email.set("eric@fender.net") } + developer { + id.set("forte") + name.set("Forte Scarlet") + email.set("ForteScarlet@163.com") + } } } } From e177474a809c534cfabb44126335abfd05aa81e8 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 22:35:51 +0800 Subject: [PATCH 08/12] =?UTF-8?q?=E5=AE=9E=E7=8E=B0:=20filterBy,=20whereBy?= =?UTF-8?q?,=20filterByOr,=20whereByOr,=20and=20ConditionalTable.?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../org/ktorm/schema/ConditionalTable.kt | 183 ++++++++++++++---- .../ktorm/conditional/ConditionalTableTest.kt | 161 +++++++++++++-- 2 files changed, 284 insertions(+), 60 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index cbbd14c6..7f46eea8 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -3,9 +3,7 @@ package org.ktorm.schema import org.ktorm.dsl.Query import org.ktorm.dsl.and import org.ktorm.dsl.where -import org.ktorm.entity.Entity -import org.ktorm.entity.EntitySequence -import org.ktorm.entity.filter +import org.ktorm.entity.* import kotlin.reflect.KClass @@ -29,7 +27,7 @@ import kotlin.reflect.KClass * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { department, column, value -> * column like "%$value%" * } - * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field + * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field(column) * } * ``` * @@ -38,6 +36,7 @@ import kotlin.reflect.KClass * val entity = Department { * // ... * } + * * // by EntitySequence * database.departments * .filterBy(entity) @@ -58,6 +57,8 @@ import kotlin.reflect.KClass * ``` * * @see Table + * @see filterBy + * @see whereBy * * @author ForteScarlet */ @@ -68,7 +69,7 @@ public open class ConditionalTable>( schema: String? = null, entityClass: KClass? = null ) : Table(tableName, alias, catalog, schema, entityClass) { - private val columnConditions = mutableMapOf ColumnDeclaring?>() + private val columnConditions = mutableMapOf ColumnDeclaring?>() /** * Provides a query condition for the current field(column) to be used when querying by entity. @@ -85,11 +86,13 @@ public open class ConditionalTable>( * @param condition the query condition. */ public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { - return saveConditionOn { entity, columnValue -> - condition(entity, this, columnValue as? C) + return saveColumnCondition { entity, entityImpl -> + val value = entityImpl.getColumnValueOrNull(this) + condition(entity, this, value as C?) } } + /** * Provides a query condition for the current field(column) to be used when querying by entity. * e.g. @@ -105,10 +108,10 @@ public open class ConditionalTable>( * @param condition the query condition. */ public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { - return saveConditionOn { entity, columnValue -> - val value = columnValue as C? + return saveColumnCondition { entity, entityImpl -> + val value = entityImpl.getColumnValueOrThrow(this) if (value != null) { - condition(entity, this, columnValue as C) + condition(entity, this, value as C) } else { null } @@ -117,14 +120,28 @@ public open class ConditionalTable>( @PublishedApi - internal fun Column.saveConditionOn(condition: (E, Any?) -> ColumnDeclaring?): Column { - this.name + internal fun EntityImplementation.getColumnValueOrNull(column: Column<*>): Any? { + return column.binding?.let { b -> getColumnValue(b) } + } + + + @PublishedApi + internal fun EntityImplementation.getColumnValueOrThrow(column: Column<*>): Any? { + val binding = column.binding + if (binding != null) { + return getColumnValue(binding) + } + + error("Column $column has no bindings to any entity field.") + } - // merge with 'and' + @PublishedApi + internal fun Column.saveColumnCondition(condition: (E, EntityImplementation) -> ColumnDeclaring?): Column { + // merge by 'and' columnConditions.merge(name, condition) { old, curr -> - { entity, value -> - val condition1 = old(entity, value) - val condition2 = curr(entity, value) + { entity, entityImplementation -> + val condition1 = old(entity, entityImplementation) + val condition2 = curr(entity, entityImplementation) when { condition1 == null && condition2 == null -> null condition1 == null -> condition2 @@ -144,21 +161,16 @@ public open class ConditionalTable>( * @return Query conditions as [ColumnDeclaring]<Boolean%gt;, May be null if no condition is generated. */ public fun asCondition(entity: E): ColumnDeclaring? { - val properties = entity.properties - // TODO - // columnConditions.entries.fold ColumnDeclaring?>, ColumnDeclaring?>( - // null - // ) { left, (key, value) -> - // val property = properties[key] - // - // - // TODO() - // } - - return entity.properties.entries.fold, ColumnDeclaring?>(null) { left, (key, value) -> - val conditionFactory = columnConditions[key] ?: return@fold left - val condition = conditionFactory(entity, value) ?: return@fold left - if (left == null) condition else left and condition + val entityImplementation = entity.implementation + return columnConditions.values.fold<(E, EntityImplementation) -> ColumnDeclaring?, ColumnDeclaring?>( + null + ) { left, factory -> + val declaring = factory(entity, entityImplementation) + if (left == null) { + declaring + } else { + if (declaring == null) left else left and declaring + } } } @@ -174,16 +186,32 @@ public open class ConditionalTable>( * database.departments * .filterBy(entity) * // Other operations... - * .forEach { - * println(it) + * .forEach { println(it) } + * ``` + * + * or + * + * ```kotlin + * database.departments + * .filterBy(entity) { table, condition -> + * condition and (table.location eq LocationWrapper("GuangZhou")) * } + * // Other operations... + * .forEach { println(it) } * ``` * + * If you want to handle the case when the condition may be null, you can refer to [filterByOr]. + * + * @param andThen When the condition exists, you can operate on it. + * * @see ConditionalTable + * @see filterByOr */ -public fun , T : ConditionalTable> EntitySequence.filterBy(conditionEntity: E): EntitySequence { - return sourceTable.asCondition(conditionEntity) - ?.let { condition -> filter { condition } } +public inline fun , T : ConditionalTable> EntitySequence.filterBy( + conditionEntity: E, + andThen: ((table: T, condition: ColumnDeclaring) -> ColumnDeclaring) = { _, condition -> condition } +): EntitySequence { + return sourceTable.asCondition(conditionEntity)?.let { condition -> filter { table -> andThen(table, condition) } } ?: this } @@ -193,19 +221,92 @@ public fun , T : ConditionalTable> EntitySequence.filterB * on the conditions defined in each field of [table] of type [ConditionalTable]. * * ```kotlin - * // by Query * database.from(Departments) * .select() * .whereBy(Departments, entity) * // Other operations... - * .forEach { - * println(it) + * .forEach { println(it) } + * ``` + * + * or + * + * ```kotlin + * database.from(DepartmentsWithCondition) + * .select() + * .whereBy(DepartmentsWithCondition, entity) { + * it and (Departments.location eq LocationWrapper("GuangZhou")) * } + * .forEach { println(it) } * ``` * + * If you want to handle the case when the condition may be null, you can refer to [whereByOr]. + * + * @param andThen When the condition exists, you can operate on it. + * @see ConditionalTable + * @see whereByOr + */ +public inline fun , T : ConditionalTable> Query.whereBy( + table: T, conditionEntity: E, andThen: ((condition: ColumnDeclaring) -> ColumnDeclaring) = { it } +): Query { + return table.asCondition(conditionEntity)?.let { this.where(andThen(it)) } ?: this +} + + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined by each field in [ConditionalTable]. + * + * + * ```kotlin + * database.departments + * .filterByOr(entity) { table, condition -> + * condition?.and(table.location eq LocationWrapper("GuangZhou")) // nullable. + * } + * // Other operations... + * .forEach { println(it) } + * ``` + * + * If you only care about the presence of conditions, you can refer to [filterBy]. + * + * @param andThen When the condition exists, you can operate on it. + * + * @see ConditionalTable + * @see filterBy + */ +public inline fun , T : ConditionalTable> EntitySequence.filterByOr( + conditionEntity: E, + andThen: ((table: T, condition: ColumnDeclaring?) -> ColumnDeclaring?) = { _, condition -> condition } +): EntitySequence { + return andThen(sourceTable, sourceTable.asCondition(conditionEntity))?.let { condition -> filter { condition } } + ?: this + +} + +/** + * Conditional filtering is performed by the specified entity class [conditionEntity] based + * on the conditions defined in each field of [table] of type [ConditionalTable]. + * + * ```kotlin + * database.from(DepartmentsWithCondition) + * .select() + * .filterByOr(DepartmentsWithCondition, entity) { + * // nullable. + * it?.and(DepartmentsWithCondition.location eq LocationWrapper("GuangZhou")) + * } + * .forEach { println(it) } + * ``` + * If you only care about the presence of conditions, you can refer to [whereBy]. + * + * @param andThen When the condition exists, you can operate on it. + * * @see ConditionalTable + * @see whereBy */ -public fun , T : ConditionalTable> Query.whereBy(table: T, conditionEntity: E): Query { - return table.asCondition(conditionEntity)?.let { this.where(it) } ?: this +public inline fun , T : ConditionalTable> Query.whereByOr( + table: T, + conditionEntity: E, + andThen: ((condition: ColumnDeclaring?) -> ColumnDeclaring?) = { it } +): Query { + return andThen(table.asCondition(conditionEntity))?.let { this.where(it) } ?: this } diff --git a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt index e37e816c..d3a682f2 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt @@ -15,37 +15,156 @@ import org.ktorm.schema.* class ConditionalTableTest : BaseTest() { @Test - fun testCondition() { - println("a") - println("a") - println("a") - println("a") - println("a") - println("a") - println("a") - val entity1 = Department { + fun testCondition1() { + val entity = Department { name = "te" } database.departmentsWithCondition - .filterBy(entity1) + .filterBy(entity) .forEach { - println(it) - assert(it.name.startsWith(entity1.name)) + assert(it.name.startsWith(entity.name)) assert(it.mixedCase != null) } database.from(DepartmentsWithCondition) .select() - .whereBy(DepartmentsWithCondition, entity1) + .whereBy(DepartmentsWithCondition, entity) .forEach { - assert(it[DepartmentsWithCondition.name]!!.startsWith(entity1.name)) + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) assert(it[DepartmentsWithCondition.mixedCase] != null) } + } + + @Test + fun testCondition2() { + val entity = Department { + name = "te" + location = LocationWrapper("Guangzhou") + } + + database.departmentsWithCondition + .filterBy(entity) + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.location.underlying == entity.location.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity) + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) + } + } + + @Test + fun testCondition3() { + val entityManagerId = 1 + val entity = Employee { + manager = Employee { + id = entityManagerId + } + } + database.employeesWithCondition + .filterBy(entity) + .forEach { + assert(it.manager?.id == entityManagerId) + } + database.from(EmployeesWithCondition) + .select() + .whereBy(EmployeesWithCondition, entity) + .forEach { + assert(it[EmployeesWithCondition.managerId] == entityManagerId) + } } + @Test + fun testCondition4() { + val entity = Employee { + salary = 50 + department = Department { + name = "dep" + } + } + database.employeesWithCondition + .filterBy(entity) + .forEach { + assert(it.salary > entity.salary) + } + + database.from(EmployeesWithCondition) + .select() + .whereBy(EmployeesWithCondition, entity) + .forEach { + assert(it[EmployeesWithCondition.salary]!! > entity.salary) + } + } + + + @Test + fun testConditionAndThen1() { + val locationWrapper = LocationWrapper("Guangzhou") + + val entity = Department { + name = "te" + } + + database.departmentsWithCondition + .filterBy(entity) { table, condition -> + condition and (table.location eq locationWrapper) + } + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.mixedCase != null) + assert(it.location.underlying != locationWrapper.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereBy(DepartmentsWithCondition, entity) { + it and (DepartmentsWithCondition.location eq locationWrapper) + } + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.mixedCase] != null) + assert(it[DepartmentsWithCondition.location]?.underlying != locationWrapper.underlying) + } + } + + @Test + fun testConditionAndThen2() { + val locationWrapper = LocationWrapper("Guangzhou") + + val entity = Department { + name = "te" + } + + database.departmentsWithCondition + .filterByOr(entity) { table, condition -> + val extraCondition = table.location eq locationWrapper + condition?.and(extraCondition) ?: extraCondition + } + .forEach { + assert(it.name.startsWith(entity.name)) + assert(it.location.underlying == entity.location.underlying) + } + + database.from(DepartmentsWithCondition) + .select() + .whereByOr(DepartmentsWithCondition, entity) { + it?.and(DepartmentsWithCondition.location eq locationWrapper) // or null. + } + .forEach { + assert(it[DepartmentsWithCondition.name]!!.startsWith(entity.name)) + assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) + } + } + + open class DepartmentsWithCondition(alias: String?) : ConditionalTable("t_department", alias) { companion object : DepartmentsWithCondition(null) @@ -61,6 +180,7 @@ class ConditionalTableTest : BaseTest() { val location = varchar("location").transform({ LocationWrapper(it) }, { it.underlying }).bindTo { it.location } .conditionOn { _, column, locationWrapper -> + println("location wrapper: $locationWrapper") if (locationWrapper == null) { (column.table as DepartmentsWithCondition).mixedCase.isNotNull() } else { @@ -79,9 +199,13 @@ class ConditionalTableTest : BaseTest() { val id = int("id").primaryKey().bindTo { it.id } val name = varchar("name").bindTo { it.name } val job = varchar("job").bindTo { it.job } - val managerId = int("manager_id").bindTo { it.manager?.id } + val managerId = int("manager_id").bindTo { it.manager?.id }.conditionNotNullOn { employee, column, i -> + column eq i + } val hireDate = date("hire_date").bindTo { it.hireDate } - val salary = long("salary").bindTo { it.salary } + val salary = long("salary").bindTo { it.salary }.conditionOn { employee, column, value -> + column greater (value ?: 0) + } val departmentId = int("department_id").references(Departments) { it.department } val department = departmentId.referenceTable as Departments } @@ -97,9 +221,8 @@ class ConditionalTableTest : BaseTest() { val phoneNumber = varchar("phone_number").bindTo { it.phoneNumber } } - val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) + private val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) - val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) + private val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) - val Database.customersWithC get() = this.sequenceOf(CustomersWithC) } \ No newline at end of file From 073f7c53bdc298169da1d47306f566327e0676c7 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 22:44:09 +0800 Subject: [PATCH 09/12] =?UTF-8?q?:bulb:=20=E6=B7=BB=E5=8A=A0=E6=BA=90?= =?UTF-8?q?=E7=A0=81=E6=B3=A8=E9=87=8A?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index 7f46eea8..420657ab 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -20,7 +20,7 @@ import kotlin.reflect.KClass * } * * - * object Departments : Table("t_department") { + * object Departments : ConditionalTable("t_department") { * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> * if (value != null) column eq value else column eq 1 * } From c90d7b8bdd98c5a32b349eeec10d0e8bc59d62b1 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Mon, 18 Apr 2022 23:25:22 +0800 Subject: [PATCH 10/12] =?UTF-8?q?=E4=B8=BA=E5=87=BD=E6=95=B0=E5=8F=82?= =?UTF-8?q?=E6=95=B0=E5=A2=9E=E5=8A=A0=E5=8F=98=E9=87=8F=E5=90=8D=E7=A7=B0?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../src/main/kotlin/org/ktorm/schema/ConditionalTable.kt | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index 420657ab..17a14fe2 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -85,7 +85,7 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionOn(crossinline condition: (E, Column, C?) -> ColumnDeclaring): Column { + public inline fun Column.conditionOn(crossinline condition: (E, column: Column, value: C?) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrNull(this) condition(entity, this, value as C?) @@ -107,7 +107,7 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionNotNullOn(crossinline condition: (E, Column, C) -> ColumnDeclaring): Column { + public inline fun Column.conditionNotNullOn(crossinline condition: (E, column: Column, value: C) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrThrow(this) if (value != null) { From adbca3240a0ca1a1de4f4646b4ab7e014d6ffcdf Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Fri, 27 May 2022 15:58:27 +0800 Subject: [PATCH 11/12] =?UTF-8?q?=E5=B0=86=20conditionOn=20=E4=B8=AD?= =?UTF-8?q?=E7=9A=84=E5=AE=9E=E4=BD=93=E7=B1=BB=E5=8F=82=E6=95=B0=E7=A7=BB?= =?UTF-8?q?=E5=8A=A8=E5=88=B0receiver=E7=9A=84=E4=BD=8D=E7=BD=AE?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- .../org/ktorm/schema/ConditionalTable.kt | 16 ++-- .../ktorm/conditional/ConditionalTableTest.kt | 83 ++++++++++--------- 2 files changed, 50 insertions(+), 49 deletions(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index 17a14fe2..e3d28a74 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -21,10 +21,10 @@ import kotlin.reflect.KClass * * * object Departments : ConditionalTable("t_department") { - * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { column, value -> // this: Department * if (value != null) column eq value else column eq 1 * } - * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { department, column, value -> + * val name = varchar("name").bindTo { it.name }.conditionNotNullOn { column, value -> // this: Department * column like "%$value%" * } * val location = varchar("location").bindTo { it.location } // No conditions will be generated for this field(column) @@ -76,7 +76,7 @@ public open class ConditionalTable>( * e.g. * ```kotlin * object Departments : ConditionalTable("t_department") { - * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { department, column, value -> + * val id = int("id").primaryKey().bindTo { it.id }.conditionOn { column, value -> // this: Department * if (value != null) column eq value else column eq 1 * } * } @@ -85,10 +85,10 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionOn(crossinline condition: (E, column: Column, value: C?) -> ColumnDeclaring): Column { + public inline fun Column.conditionOn(crossinline condition: E.(column: Column, value: C?) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrNull(this) - condition(entity, this, value as C?) + entity.condition(this, value as C?) } } @@ -98,7 +98,7 @@ public open class ConditionalTable>( * e.g. * ```kotlin * object Departments : ConditionalTable("t_department") { - * val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { department, column, value -> + * val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { column, value -> // this: Department * column eq value * } * } @@ -107,11 +107,11 @@ public open class ConditionalTable>( * * @param condition the query condition. */ - public inline fun Column.conditionNotNullOn(crossinline condition: (E, column: Column, value: C) -> ColumnDeclaring): Column { + public inline fun Column.conditionNotNullOn(crossinline condition: E.(column: Column, value: C) -> ColumnDeclaring): Column { return saveColumnCondition { entity, entityImpl -> val value = entityImpl.getColumnValueOrThrow(this) if (value != null) { - condition(entity, this, value as C) + entity.condition(this, value as C) } else { null } diff --git a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt index d3a682f2..9c01d6be 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/conditional/ConditionalTableTest.kt @@ -13,20 +13,20 @@ import org.ktorm.schema.* * @author ForteScarlet */ class ConditionalTableTest : BaseTest() { - + @Test fun testCondition1() { val entity = Department { name = "te" } - + database.departmentsWithCondition .filterBy(entity) .forEach { assert(it.name.startsWith(entity.name)) assert(it.mixedCase != null) } - + database.from(DepartmentsWithCondition) .select() .whereBy(DepartmentsWithCondition, entity) @@ -35,21 +35,21 @@ class ConditionalTableTest : BaseTest() { assert(it[DepartmentsWithCondition.mixedCase] != null) } } - + @Test fun testCondition2() { val entity = Department { name = "te" location = LocationWrapper("Guangzhou") } - + database.departmentsWithCondition .filterBy(entity) .forEach { assert(it.name.startsWith(entity.name)) assert(it.location.underlying == entity.location.underlying) } - + database.from(DepartmentsWithCondition) .select() .whereBy(DepartmentsWithCondition, entity) @@ -58,7 +58,7 @@ class ConditionalTableTest : BaseTest() { assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) } } - + @Test fun testCondition3() { val entityManagerId = 1 @@ -67,13 +67,13 @@ class ConditionalTableTest : BaseTest() { id = entityManagerId } } - + database.employeesWithCondition .filterBy(entity) .forEach { assert(it.manager?.id == entityManagerId) } - + database.from(EmployeesWithCondition) .select() .whereBy(EmployeesWithCondition, entity) @@ -81,7 +81,7 @@ class ConditionalTableTest : BaseTest() { assert(it[EmployeesWithCondition.managerId] == entityManagerId) } } - + @Test fun testCondition4() { val entity = Employee { @@ -95,7 +95,7 @@ class ConditionalTableTest : BaseTest() { .forEach { assert(it.salary > entity.salary) } - + database.from(EmployeesWithCondition) .select() .whereBy(EmployeesWithCondition, entity) @@ -103,16 +103,16 @@ class ConditionalTableTest : BaseTest() { assert(it[EmployeesWithCondition.salary]!! > entity.salary) } } - - + + @Test fun testConditionAndThen1() { val locationWrapper = LocationWrapper("Guangzhou") - + val entity = Department { name = "te" } - + database.departmentsWithCondition .filterBy(entity) { table, condition -> condition and (table.location eq locationWrapper) @@ -122,7 +122,7 @@ class ConditionalTableTest : BaseTest() { assert(it.mixedCase != null) assert(it.location.underlying != locationWrapper.underlying) } - + database.from(DepartmentsWithCondition) .select() .whereBy(DepartmentsWithCondition, entity) { @@ -134,15 +134,15 @@ class ConditionalTableTest : BaseTest() { assert(it[DepartmentsWithCondition.location]?.underlying != locationWrapper.underlying) } } - + @Test fun testConditionAndThen2() { val locationWrapper = LocationWrapper("Guangzhou") - + val entity = Department { name = "te" } - + database.departmentsWithCondition .filterByOr(entity) { table, condition -> val extraCondition = table.location eq locationWrapper @@ -152,7 +152,7 @@ class ConditionalTableTest : BaseTest() { assert(it.name.startsWith(entity.name)) assert(it.location.underlying == entity.location.underlying) } - + database.from(DepartmentsWithCondition) .select() .whereByOr(DepartmentsWithCondition, entity) { @@ -163,66 +163,67 @@ class ConditionalTableTest : BaseTest() { assert(it[DepartmentsWithCondition.location]?.underlying == entity.location.underlying) } } - - - + + open class DepartmentsWithCondition(alias: String?) : ConditionalTable("t_department", alias) { companion object : DepartmentsWithCondition(null) - + override fun aliased(alias: String) = DepartmentsWithCondition(alias) - - val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { _, c, v -> + + val id = int("id").primaryKey().bindTo { it.id }.conditionNotNullOn { c, v -> c eq v } - val name = varchar("name").bindTo { it.name }.conditionNotNullOn { _, c, v -> + val name = varchar("name").bindTo { it.name }.conditionNotNullOn { c, v -> c like "$v%" } - + val location = varchar("location").transform({ LocationWrapper(it) }, { it.underlying }).bindTo { it.location } - .conditionOn { _, column, locationWrapper -> + .conditionOn { column, locationWrapper -> println("location wrapper: $locationWrapper") if (locationWrapper == null) { (column.table as DepartmentsWithCondition).mixedCase.isNotNull() } else { column eq locationWrapper } - + } val mixedCase = varchar("mixedCase").bindTo { it.mixedCase } } - + open class EmployeesWithCondition(alias: String?) : ConditionalTable("t_employee", alias) { companion object : EmployeesWithCondition(null) - + override fun aliased(alias: String) = EmployeesWithCondition(alias) - + val id = int("id").primaryKey().bindTo { it.id } val name = varchar("name").bindTo { it.name } val job = varchar("job").bindTo { it.job } - val managerId = int("manager_id").bindTo { it.manager?.id }.conditionNotNullOn { employee, column, i -> + val managerId = int("manager_id").bindTo { it.manager?.id }.conditionNotNullOn { column, i -> column eq i } val hireDate = date("hire_date").bindTo { it.hireDate } - val salary = long("salary").bindTo { it.salary }.conditionOn { employee, column, value -> + val salary = long("salary").bindTo { it.salary }.conditionOn { column, value -> column greater (value ?: 0) } val departmentId = int("department_id").references(Departments) { it.department } val department = departmentId.referenceTable as Departments } - + open class CustomersWithC(alias: String?) : ConditionalTable("t_customer", alias, schema = "company") { companion object : CustomersWithC(null) - + override fun aliased(alias: String) = CustomersWithC(alias) - + val id = int("id").primaryKey().bindTo { it.id } val name = varchar("name").bindTo { it.name } + @Suppress("unused") val email = varchar("email").bindTo { it.email } + @Suppress("unused") val phoneNumber = varchar("phone_number").bindTo { it.phoneNumber } } - + private val Database.departmentsWithCondition get() = this.sequenceOf(DepartmentsWithCondition) - + private val Database.employeesWithCondition get() = this.sequenceOf(EmployeesWithCondition) - + } \ No newline at end of file From 8a383289fc7ef12623a4ba26397490515e8b0408 Mon Sep 17 00:00:00 2001 From: ForteScarlet Date: Tue, 31 May 2022 22:16:10 +0800 Subject: [PATCH 12/12] =?UTF-8?q?=E4=BF=AE=E6=94=B9=E6=B3=A8=E9=87=8A?= =?UTF-8?q?=E4=B8=AD=E7=9A=84=E9=94=99=E8=AF=AF=E7=AC=A6=E5=8F=B7?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt index e3d28a74..e026f3be 100644 --- a/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt +++ b/ktorm-core/src/main/kotlin/org/ktorm/schema/ConditionalTable.kt @@ -158,7 +158,7 @@ public open class ConditionalTable>( * Translate the provided entity classes into query conditions. * * @param entity entity of this table. - * @return Query conditions as [ColumnDeclaring]<Boolean%gt;, May be null if no condition is generated. + * @return Query conditions as [ColumnDeclaring]<Boolean>, May be null if no condition is generated. */ public fun asCondition(entity: E): ColumnDeclaring? { val entityImplementation = entity.implementation