From a25e08baa0a47b506353482d3af1a0cd4bdd6c54 Mon Sep 17 00:00:00 2001 From: Sven Allers Date: Thu, 9 Feb 2023 18:50:52 +0100 Subject: [PATCH] PostgresSQL: array_position() for String and Enum (via casting) --- .../src/test/kotlin/org/ktorm/BaseTest.kt | 2 +- .../org/ktorm/support/postgresql/Functions.kt | 62 +++++++++++++++++++ .../support/postgresql/BasePostgreSqlTest.kt | 17 ++++- .../ktorm/support/postgresql/CommonTest.kt | 13 +--- .../ktorm/support/postgresql/FunctionsTest.kt | 60 ++++++++++++++++++ 5 files changed, 139 insertions(+), 15 deletions(-) create mode 100644 ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/Functions.kt create mode 100644 ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/FunctionsTest.kt diff --git a/ktorm-core/src/test/kotlin/org/ktorm/BaseTest.kt b/ktorm-core/src/test/kotlin/org/ktorm/BaseTest.kt index 745d31b39..fdda2bd8f 100644 --- a/ktorm-core/src/test/kotlin/org/ktorm/BaseTest.kt +++ b/ktorm-core/src/test/kotlin/org/ktorm/BaseTest.kt @@ -116,4 +116,4 @@ abstract class BaseTest { val Database.employees get() = this.sequenceOf(Employees) val Database.customers get() = this.sequenceOf(Customers) -} \ No newline at end of file +} diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/Functions.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/Functions.kt new file mode 100644 index 000000000..5c953b744 --- /dev/null +++ b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/Functions.kt @@ -0,0 +1,62 @@ +/* + * Copyright 2018-2023 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.support.postgresql + +import org.ktorm.dsl.cast +import org.ktorm.expression.ArgumentExpression +import org.ktorm.expression.FunctionExpression +import org.ktorm.schema.ColumnDeclaring +import org.ktorm.schema.IntSqlType +import org.ktorm.schema.SqlType +import org.ktorm.schema.TextSqlType + +/** + * PostgreSQL array_position function for enums, translated to `array_position(value, cast(column as text))`. + * Uses the `name` attribute of the enums as actual value for the query. + */ +public fun > arrayPosition(value: Array, column: ColumnDeclaring): FunctionExpression = + arrayPosition(value.map { it?.name }.toTypedArray(), column.cast(TextSqlType)) + +/** + * PostgreSQL array_position function for enums, translated to `array_position(value, cast(column as text))`. + * Uses the `name` attribute of the enums as actual value for the query. + */ +public inline fun > arrayPosition( + value: Collection, + column: ColumnDeclaring +): FunctionExpression = + arrayPosition(value.map { it?.name }.toTypedArray(), column.cast(TextSqlType)) + +/** + * PostgreSQL array_position function, translated to `array_position(value, column)`. + */ +public fun arrayPosition(value: TextArray, column: ColumnDeclaring): FunctionExpression = + arrayPosition(value, column, TextArraySqlType) + +/** + * PostgreSQL array_position function, translated to `array_position(value, column)`. + */ +public fun arrayPosition( + value: Array, + column: ColumnDeclaring, + arraySqlType: SqlType> +): FunctionExpression = + FunctionExpression( + functionName = "array_position", + arguments = listOf(ArgumentExpression(value, arraySqlType), column.asExpression()), + sqlType = IntSqlType + ) diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/BasePostgreSqlTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/BasePostgreSqlTest.kt index 675a55525..68d3178d8 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/BasePostgreSqlTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/BasePostgreSqlTest.kt @@ -2,6 +2,9 @@ package org.ktorm.support.postgresql import org.ktorm.BaseTest import org.ktorm.database.Database +import org.ktorm.schema.Table +import org.ktorm.schema.enum +import org.ktorm.schema.int import org.testcontainers.containers.PostgreSQLContainer import kotlin.concurrent.thread @@ -16,7 +19,7 @@ abstract class BasePostgreSqlTest : BaseTest() { execSqlScript("drop-postgresql-data.sql") } - companion object : PostgreSQLContainer("postgres:13-alpine") { + companion object : PostgreSQLContainer("postgres:14-alpine") { init { // Start the container when it's first used. start() @@ -24,4 +27,14 @@ abstract class BasePostgreSqlTest : BaseTest() { Runtime.getRuntime().addShutdownHook(thread(start = false) { stop() }) } } -} \ No newline at end of file + + enum class Mood { + HAPPY, + SAD + } + + object TableWithEnum : Table("t_enum") { + val id = int("id").primaryKey() + val current_mood = enum("current_mood") + } +} diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/CommonTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/CommonTest.kt index 018c650c4..1edd2a6df 100644 --- a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/CommonTest.kt +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/CommonTest.kt @@ -7,7 +7,6 @@ import org.ktorm.database.use import org.ktorm.dsl.* import org.ktorm.entity.* import org.ktorm.schema.Table -import org.ktorm.schema.enum import org.ktorm.schema.int import org.ktorm.schema.varchar import java.util.concurrent.ExecutionException @@ -151,16 +150,6 @@ class CommonTest : BasePostgreSqlTest() { } - enum class Mood { - HAPPY, - SAD - } - - object TableWithEnum : Table("t_enum") { - val id = int("id").primaryKey() - val current_mood = enum("current_mood") - } - @Test fun testEnum() { database.insert(TableWithEnum) { @@ -220,4 +209,4 @@ class CommonTest : BasePostgreSqlTest() { assertEquals("test~~", e1.v) assert(e1.k.isNotEmpty()) } -} \ No newline at end of file +} diff --git a/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/FunctionsTest.kt b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/FunctionsTest.kt new file mode 100644 index 000000000..4f3619f79 --- /dev/null +++ b/ktorm-support-postgresql/src/test/kotlin/org/ktorm/support/postgresql/FunctionsTest.kt @@ -0,0 +1,60 @@ +package org.ktorm.support.postgresql + +import org.junit.Test +import org.ktorm.dsl.* +import kotlin.test.assertEquals + +class FunctionsTest : BasePostgreSqlTest() { + @Test + fun testArrayPositionEnumCollection() { + database.insert(TableWithEnum) { + set(it.current_mood, Mood.SAD) + } + database.insert(TableWithEnum) { + set(it.current_mood, Mood.HAPPY) + } + + val moodsSorted = database + .from(TableWithEnum) + .select() + .orderBy(arrayPosition(listOf(Mood.SAD, Mood.HAPPY), TableWithEnum.current_mood).asc()) + .map { row -> + row[TableWithEnum.current_mood] + } + + assertEquals(listOf(Mood.SAD, Mood.HAPPY, Mood.HAPPY), moodsSorted) + } + + @Test + fun testArrayPositionEnumArray() { + database.insert(TableWithEnum) { + set(it.current_mood, Mood.SAD) + } + database.insert(TableWithEnum) { + set(it.current_mood, Mood.HAPPY) + } + + val moodsSorted = database + .from(TableWithEnum) + .select() + .orderBy(arrayPosition(arrayOf(Mood.SAD, Mood.HAPPY), TableWithEnum.current_mood).asc()) + .map { row -> + row[TableWithEnum.current_mood] + } + + assertEquals(listOf(Mood.SAD, Mood.HAPPY, Mood.HAPPY), moodsSorted) + } + + @Test + fun testArrayPositionTextArray() { + val namesSorted = database + .from(Employees) + .select() + .orderBy(arrayPosition(arrayOf("tom", "vince", "marry"), Employees.name).asc()) + .map { row -> + row[Employees.name] + } + + assertEquals(listOf("tom", "vince", "marry", "penny"), namesSorted) + } +}