kotlin-orm · vincentlauvlwj · Sep 9, 2023 · Feb 9, 2023
diff --git 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)
-}
+}
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
@@ -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 <T : Enum<T>> arrayPosition(value: Array<T?>, column: ColumnDeclaring<T>): FunctionExpression<Int> =
+    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 <reified T : Enum<T>> arrayPosition(
+    value: Collection<T?>,
+    column: ColumnDeclaring<T>
+): FunctionExpression<Int> =
+    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<String>): FunctionExpression<Int> =
+    arrayPosition(value, column, TextArraySqlType)
+
+/**
+ * PostgreSQL array_position function, translated to `array_position(value, column)`.
+ */
+public fun <T : Any> arrayPosition(
+    value: Array<T?>,
+    column: ColumnDeclaring<T>,
+    arraySqlType: SqlType<Array<T?>>
+): FunctionExpression<Int> =
+    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
@@ -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,12 +19,22 @@ abstract class BasePostgreSqlTest : BaseTest() {
         execSqlScript("drop-postgresql-data.sql")
     }
 
-    companion object : PostgreSQLContainer<Companion>("postgres:13-alpine") {
+    companion object : PostgreSQLContainer<Companion>("postgres:14-alpine") {
         init {
             // Start the container when it's first used.
             start()
             // Stop the container when the process exits.
             Runtime.getRuntime().addShutdownHook(thread(start = false) { stop() })
         }
     }
-}
+
+    enum class Mood {
+        HAPPY,
+        SAD
+    }
+
+    object TableWithEnum : Table<Nothing>("t_enum") {
+        val id = int("id").primaryKey()
+        val current_mood = enum<Mood>("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
@@ -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<Nothing>("t_enum") {
-        val id = int("id").primaryKey()
-        val current_mood = enum<Mood>("current_mood")
-    }
-
     @Test
     fun testEnum() {
         database.insert(TableWithEnum) {
@@ -220,4 +209,4 @@ class CommonTest : BasePostgreSqlTest() {
         assertEquals("test~~", e1.v)
         assert(e1.k.isNotEmpty())
     }
-}
+}
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
@@ -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)
+    }
+}
-Original file line number
+Diff line change
@@ Expand Up / @@ -116,4 +116,4 @@ abstract class BaseTest { @@
         val Database.employees get() = this.sequenceOf(Employees)
         val Database.customers get() = this.sequenceOf(Customers)
-    }
+    }