kotlin-orm · vincentlauvlwj · May 26, 2022 · Jan 15, 2022 · Jan 17, 2022 · Jan 17, 2022
diff --git a/build.gradle b/build.gradle
@@ -168,6 +168,11 @@ subprojects { project ->
                             name = "Eric Fenderbosch"
                             email = "eric@fender.net"
                         }
+                        developer {
+                            id = "kocproz"
+                            name = "Kacper Stasiuk"
+                            email = "kocproz@pm.me"
+                        }
                     }
                 }
             }

diff --git a/ktorm-support-postgresql/ktorm-support-postgresql.gradle b/ktorm-support-postgresql/ktorm-support-postgresql.gradle
@@ -4,7 +4,7 @@ dependencies {
 
     testImplementation project(path: ":ktorm-core", configuration: "testOutput")
     testImplementation project(":ktorm-jackson")
-    testImplementation "org.postgresql:postgresql:42.2.5"
+    implementation "org.postgresql:postgresql:42.2.5"
     testImplementation "org.testcontainers:postgresql:1.15.1"
     testImplementation "com.zaxxer:HikariCP:4.0.3"
     testImplementation "com.mchange:c3p0:0.9.5.5"

diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/EarthDistance.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/EarthDistance.kt
@@ -0,0 +1,269 @@
+/*
+ * Copyright 2018-2021 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.expression.ArgumentExpression
+import org.ktorm.expression.FunctionExpression
+import org.ktorm.expression.ScalarExpression
+import org.ktorm.schema.BooleanSqlType
+import org.ktorm.schema.ColumnDeclaring
+import org.ktorm.schema.DoubleSqlType
+import org.ktorm.schema.SqlType
+
+/**
+ * Enum for 'cube' and 'earthdistance' binary operators.
+ */
+public enum class CubeExpressionType(private val value: String) {
+    /**
+     * Cube overlaps operator, translated to the && operator in PostgreSQL.
+     */
+    OVERLAP("&&"),
+
+    /**
+     * Cube contains operator, translated to the @> operator in PostgreSQL.
+     */
+    CONTAINS("@>"),
+
+    /**
+     * Cube contained in operator, translated to the <@ operator in PostgreSQL.
+     */
+    CONTAINED_IN("<@");
+
+    override fun toString(): String {
+        return value
+    }
+}
+
+/**
+ * Expression class represents PostgreSQL's `Cube` operations.
+ *
+ * @property type the expression's type.
+ * @property left the expression's left operand.
+ * @property right the expression's right operand.
+ */
+public data class CubeExpression<T : Any>(
+    val type: CubeExpressionType,
+    val left: ScalarExpression<*>,
+    val right: ScalarExpression<*>,
+    override val sqlType: SqlType<T>,
+    override val isLeafNode: Boolean = false,
+    override val extraProperties: Map<String, Any> = emptyMap()
+) : ScalarExpression<T>()
+
+/**
+ * Cube contains operator, translated to the @> operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.contains(argument: ColumnDeclaring<Cube>): CubeExpression<Boolean> {
+    return CubeExpression(CubeExpressionType.CONTAINS, asExpression(), argument.asExpression(), BooleanSqlType)
+}
+
+/**
+ * Cube contains operator, translated to the @> operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.contains(argument: Cube): CubeExpression<Boolean> {
+    return this.contains(wrapArgument(argument))
+}
+
+/**
+ * Cube contains operator, translated to the @> operator in PostgreSQL.
+ */
+@JvmName("containsEarth")
+public infix fun ColumnDeclaring<Cube>.contains(argument: ColumnDeclaring<Earth>): CubeExpression<Boolean> {
+    return CubeExpression(CubeExpressionType.CONTAINS, asExpression(), argument.asExpression(), BooleanSqlType)
+}
+
+/**
+ * Cube contains operator, translated to the @> operator in PostgreSQL.
+ */
+@JvmName("containsEarth")
+public infix fun ColumnDeclaring<Cube>.contains(argument: Earth): CubeExpression<Boolean> {
+    return this.contains(ArgumentExpression(argument, PGEarthType))
+}
+
+/**
+ * Cube contained in operator, translated to the <@ operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.containedIn(argument: ColumnDeclaring<Cube>): CubeExpression<Boolean> {
+    return CubeExpression(CubeExpressionType.CONTAINED_IN, asExpression(), argument.asExpression(), BooleanSqlType)
+}
+
+/**
+ * Cube contained in operator, translated to the <@ operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.containedIn(argument: Cube): CubeExpression<Boolean> {
+    return this.containedIn(wrapArgument(argument))
+}
+
+/**
+ * Cube contained in operator, translated to the <@ operator in PostgreSQL.
+ */
+@JvmName("earthContainedInCube")
+public infix fun ColumnDeclaring<Earth>.containedIn(argument: ColumnDeclaring<Cube>): CubeExpression<Boolean> {
+    return CubeExpression(CubeExpressionType.CONTAINED_IN, asExpression(), argument.asExpression(), BooleanSqlType)
+}
+
+/**
+ * Cube contained in operator, translated to the <@ operator in PostgreSQL.
+ */
+@JvmName("earthContainedInCube")
+public infix fun ColumnDeclaring<Earth>.containedIn(argument: Cube): CubeExpression<Boolean> {
+    return this.containedIn(ArgumentExpression(argument, PGCubeType))
+}
+
+/**
+ * Cube overlap operator, translated to the && operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.overlaps(argument: ColumnDeclaring<Cube>): CubeExpression<Boolean> {
+    return CubeExpression(CubeExpressionType.OVERLAP, asExpression(), argument.asExpression(), BooleanSqlType)
+}
+
+/**
+ * Cube overlap operator, translated to the && operator in PostgreSQL.
+ */
+public infix fun ColumnDeclaring<Cube>.overlaps(argument: Cube): CubeExpression<Boolean> {
+    return this.overlaps(wrapArgument(argument))
+}
+
+/**
+ * Returns the location of a point on the surface of the Earth
+ * given its latitude (argument 1) and longitude (argument 2) in degrees.
+ *
+ * Function from earthdistance extension
+ */
+public fun llToEarth(
+    lat: ColumnDeclaring<Double>,
+    lng: ColumnDeclaring<Double>
+): FunctionExpression<Earth> =
+    FunctionExpression(
+        functionName = "ll_to_earth",
+        arguments = listOf(lat.asExpression(), lng.asExpression()),
+        sqlType = PGEarthType
+    )
+
+/**
+ * Returns the location of a point on the surface of the Earth
+ * given its latitude (argument 1) and longitude (argument 2) in degrees.
+ */
+public fun llToEarth(
+    lat: ColumnDeclaring<Double>,
+    lng: Double
+): FunctionExpression<Earth> =
+    llToEarth(lat, ArgumentExpression(lng, DoubleSqlType))
+
+/**
+ * Returns the location of a point on the surface of the Earth
+ * given its latitude (argument 1) and longitude (argument 2) in degrees.
+ */
+public fun llToEarth(
+    lat: Double,
+    lng: ColumnDeclaring<Double>
+): FunctionExpression<Earth> =
+    llToEarth(ArgumentExpression(lat, DoubleSqlType), lng)
+
+/**
+ * Returns the location of a point on the surface of the Earth
+ * given its latitude (argument 1) and longitude (argument 2) in degrees.
+ */
+public fun llToEarth(
+    lat: Double,
+    lng: Double
+): FunctionExpression<Earth> =
+    llToEarth(ArgumentExpression(lat, DoubleSqlType), ArgumentExpression(lng, DoubleSqlType))
+
+/**
+ * Get distance between 2 points on earth.
+ *
+ * Function from earthdistance extension, `earth_distance(point1, point2)` in SQL.
+ */
+public fun earthDistance(
+    point1: ColumnDeclaring<Earth>,
+    point2: ColumnDeclaring<Earth>
+): FunctionExpression<Double> =
+    FunctionExpression(
+        functionName = "earth_distance",
+        arguments = listOf(point1.asExpression(), point2.asExpression()),
+        sqlType = DoubleSqlType
+    )
+
+/**
+ * Get distance between 2 points on earth.
+ */
+public fun earthDistance(
+    point1: ColumnDeclaring<Earth>,
+    point2: Earth
+): FunctionExpression<Double> =
+    earthDistance(point1, ArgumentExpression(point2, PGEarthType))
+
+/**
+ * Get distance between 2 points on earth.
+ */
+public fun earthDistance(
+    point1: Earth,
+    point2: ColumnDeclaring<Earth>
+): FunctionExpression<Double> =
+    earthDistance(ArgumentExpression(point1, PGEarthType), point2)
+
+/**
+ * Get distance between 2 points on earth.
+ */
+public fun earthDistance(
+    point1: Earth,
+    point2: Earth
+): FunctionExpression<Double> =
+    earthDistance(ArgumentExpression(point1, PGEarthType), ArgumentExpression(point2, PGEarthType))
+
+/**
+ * Creates a bounding cube, sized to contain all the points that are not farther than radius meters from a given point.
+ *
+ * Function from earthdistance extension, `earth_box(point, radius)` in SQL.
+ */
+public fun earthBox(
+    point: ColumnDeclaring<Earth>,
+    radius: ColumnDeclaring<Double>
+): FunctionExpression<Cube> =
+    FunctionExpression(
+        functionName = "earth_box",
+        arguments = listOf(point.asExpression(), radius.asExpression()),
+        sqlType = PGCubeType
+    )
+
+/**
+ * Creates a bounding cube, sized to contain all the points that are not farther than radius meters from a given point.
+ */
+public fun earthBox(
+    point: ColumnDeclaring<Earth>,
+    radius: Double
+): FunctionExpression<Cube> =
+    earthBox(point, ArgumentExpression(radius, DoubleSqlType))
+
+/**
+ * Creates a bounding cube, sized to contain all the points that are not farther than radius meters from a given point.
+ */
+public fun earthBox(
+    point: Earth,
+    radius: ColumnDeclaring<Double>
+): FunctionExpression<Cube> =
+    earthBox(ArgumentExpression(point, PGEarthType), radius)
+
+/**
+ * Creates a bounding cube, sized to contain all the points that are not farther than radius meters from a given point.
+ */
+public fun earthBox(
+    point: Earth,
+    radius: Double
+): FunctionExpression<Cube> =
+    earthBox(ArgumentExpression(point, PGEarthType), ArgumentExpression(radius, DoubleSqlType))
diff --git a/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt b/ktorm-support-postgresql/src/main/kotlin/org/ktorm/support/postgresql/PostgreSqlDialect.kt
@@ -18,7 +18,15 @@ package org.ktorm.support.postgresql
 
 import org.ktorm.database.Database
 import org.ktorm.database.SqlDialect
-import org.ktorm.expression.*
+import org.ktorm.expression.ArgumentExpression
+import org.ktorm.expression.ColumnAssignmentExpression
+import org.ktorm.expression.QueryExpression
+import org.ktorm.expression.ScalarExpression
+import org.ktorm.expression.SelectExpression
+import org.ktorm.expression.SqlExpression
+import org.ktorm.expression.SqlExpressionVisitor
+import org.ktorm.expression.SqlFormatter
+import org.ktorm.expression.TableExpression
 import org.ktorm.schema.IntSqlType
 
 /**
@@ -60,6 +68,7 @@ public open class PostgreSqlFormatter(
         val result = when (expr) {
             is ILikeExpression -> visitILike(expr)
             is HStoreExpression -> visitHStore(expr)
+            is CubeExpression -> visitCube(expr)
             else -> super.visitScalar(expr)
         }
 
@@ -185,6 +194,30 @@ public open class PostgreSqlFormatter(
         return expr
     }
 
+    protected open fun <T : Any> visitCube(expr: CubeExpression<T>): CubeExpression<T> {
+        if (expr.left.removeBrackets) {
+            visit(expr.left)
+        } else {
+            write("(")
+            visit(expr.left)
+            removeLastBlank()
+            write(") ")
+        }
+
+        writeKeyword("${expr.type} ")
+
+        if (expr.right.removeBrackets) {
+            visit(expr.right)
+        } else {
+            write("(")
+            visit(expr.right)
+            removeLastBlank()
+            write(") ")
+        }
+
+        return expr
+    }
+
     protected open fun visitInsertOrUpdate(expr: InsertOrUpdateExpression): InsertOrUpdateExpression {
         writeKeyword("insert into ")
         visitTable(expr.table)
@@ -276,6 +309,7 @@ public open class PostgreSqlExpressionVisitor : SqlExpressionVisitor() {
         val result = when (expr) {
             is ILikeExpression -> visitILike(expr)
             is HStoreExpression -> visitHStore(expr)
+            is CubeExpression -> visitCube(expr)
             else -> super.visitScalar(expr)
         }
 
@@ -305,6 +339,17 @@ public open class PostgreSqlExpressionVisitor : SqlExpressionVisitor() {
         }
     }
 
+    protected open fun <T : Any> visitCube(expr: CubeExpression<T>): CubeExpression<T> {
+        val left = visitScalar(expr.left)
+        val right = visitScalar(expr.right)
+
+        if (left === expr.left && right === expr.right) {
+            return expr
+        } else {
+            return expr.copy(left = left, right = right)
+        }
+    }
+
     protected open fun visitInsertOrUpdate(expr: InsertOrUpdateExpression): InsertOrUpdateExpression {
         val table = visitTable(expr.table)
         val assignments = visitColumnAssignments(expr.assignments)