kotlin-orm · vincentlauvlwj · Oct 22, 2020 · Oct 14, 2020 · Oct 14, 2020 · Oct 15, 2020
diff --git a/build.gradle b/build.gradle
@@ -142,6 +142,11 @@ subprojects { project ->
                             name = "Antony Denyer"
                             email = "git@antonydenyer.co.uk"
                         }
+                        developer {
+                            id = "mik629"
+                            name = "Mikhail Erkhov"
+                            email = "mikhail.erkhov@gmail.com"
+                        }
                     }
                     scm {
                         url = "https://github.com/kotlin-orm/ktorm.git"

diff --git a/ktorm-core/src/main/kotlin/org/ktorm/dsl/Query.kt b/ktorm-core/src/main/kotlin/org/ktorm/dsl/Query.kt
@@ -18,11 +18,18 @@ package org.ktorm.dsl
 
 import org.ktorm.database.Database
 import org.ktorm.database.iterator
-import org.ktorm.expression.*
+import org.ktorm.expression.ArgumentExpression
+import org.ktorm.expression.ColumnDeclaringExpression
+import org.ktorm.expression.ColumnExpression
+import org.ktorm.expression.OrderByExpression
+import org.ktorm.expression.OrderType
+import org.ktorm.expression.QueryExpression
+import org.ktorm.expression.SelectExpression
+import org.ktorm.expression.SqlExpressionVisitor
+import org.ktorm.expression.UnionExpression
 import org.ktorm.schema.BooleanSqlType
 import org.ktorm.schema.Column
 import org.ktorm.schema.ColumnDeclaring
-import java.lang.Appendable
 import java.sql.ResultSet
 
 /**
@@ -330,23 +337,52 @@ public fun ColumnDeclaring<*>.desc(): OrderByExpression {
     return OrderByExpression(asExpression(), OrderType.DESCENDING)
 }
 
+/**
+ * Specify the pagination limit parameter of this query.
+ *
+ * This function requires a dialect enabled, different SQLs will be generated with different dialects.
+ *
+ * Note that if [limit] is zero then it will be ignored.
+ */
+public fun Query.limit(limit: Int): Query {
+    return limit(null, limit)
+}
+
+/**
+ * Specify the pagination offset parameter of this query.
+ *
+ * This function requires a dialect enabled, different SQLs will be generated with different dialects.
+ *
+ * Note that if [offset] is zero then it will be ignored.
+ */
+public fun Query.offset(offset: Int): Query {
+    return limit(offset, null)
+}
+
 /**
  * Specify the pagination parameters of this query.
  *
  * This function requires a dialect enabled, different SQLs will be generated with different dialects. For example,
  * `limit ?, ?` by MySQL, `limit m offset n` by PostgreSQL.
  *
- * Note that if both [offset] and [limit] are zero, they will be ignored.
+ * Note that if both [offset] and [limit] aren't positive, they will be ignored.
  */
-public fun Query.limit(offset: Int, limit: Int): Query {
-    if (offset == 0 && limit == 0) {
+public fun Query.limit(offset: Int?, limit: Int?): Query {
+    val isOffsetInvalid = offset == null || offset <= 0
+    val isLimitInvalid = limit == null || limit <= 0
+    if (isOffsetInvalid && isLimitInvalid) {
         return this
     }
-
     return this.withExpression(
         when (expression) {
-            is SelectExpression -> expression.copy(offset = offset, limit = limit)
-            is UnionExpression -> expression.copy(offset = offset, limit = limit)
+            is SelectExpression -> expression.copy(
+                offset = if (isOffsetInvalid) expression.offset else offset,
+                limit = if (isLimitInvalid) expression.limit else limit
+            )
+            is UnionExpression -> expression.copy(
+                    offset = if (isOffsetInvalid) expression.offset else offset,
+                    limit = if (isLimitInvalid) expression.limit else limit
+            )
         }
     )
 }

diff --git a/ktorm-global/src/test/kotlin/org/ktorm/global/GlobalQueryTest.kt b/ktorm-global/src/test/kotlin/org/ktorm/global/GlobalQueryTest.kt
@@ -1,13 +1,17 @@
 package org.ktorm.global
 
 import org.junit.Test
+import org.ktorm.database.DialectFeatureNotSupportedException
 import org.ktorm.dsl.*
 import org.ktorm.expression.ScalarExpression
 
 /**
  * Created by vince at Apr 05, 2020.
  */
 class GlobalQueryTest : BaseGlobalTest() {
+    companion object {
+        const val TWO = 2
+    }
 
     @Test
     fun testSelect() {
@@ -163,6 +167,22 @@ class GlobalQueryTest : BaseGlobalTest() {
         }
     }
 
+    /**
+     * An exception is thrown because pagination should be provided by dialects.
+     */
+    @Test(expected = DialectFeatureNotSupportedException::class)
+    fun testLimitWithoutOffset() {
+        Employees.select().orderBy(Employees.id.desc()).limit(TWO).iterator()
+    }
+
+    /**
+     * An exception is thrown because pagination should be provided by dialects.
+     */
+    @Test(expected = DialectFeatureNotSupportedException::class)
+    fun testOffsetWithoutLimit() {
+        Employees.select().orderBy(Employees.id.desc()).offset(TWO).iterator()
+    }
+
     @Test
     fun testBetween() {
         val names = Employees

diff --git a/ktorm-support-mysql/src/test/kotlin/org/ktorm/support/mysql/MySqlTest.kt b/ktorm-support-mysql/src/test/kotlin/org/ktorm/support/mysql/MySqlTest.kt
@@ -26,6 +26,16 @@ import java.util.concurrent.TimeoutException
 class MySqlTest : BaseTest() {
 
     companion object {
+        const val TOTAL_RECORDS = 4
+        const val MINUS_ONE = -1
+        const val ZERO = 0
+        const val ONE = 1
+        const val TWO = 2
+        const val ID_1 = 1
+        const val ID_2 = 2
+        const val ID_3 = 3
+        const val ID_4 = 4
+
         class KMySqlContainer : MySQLContainer<KMySqlContainer>()
 
         @ClassRule
@@ -79,10 +89,59 @@ class MySqlTest : BaseTest() {
         assert(query.totalRecords == 4)
 
         val ids = query.map { it[Employees.id] }
+        assert(ids.size == 2)
         assert(ids[0] == 4)
         assert(ids[1] == 3)
     }
 
+    /**
+     * Verifies that invalid pagination parameters are ignored.
+     */
+    @Test
+    fun testBothLimitAndOffsetAreNotPositive() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(ZERO, MINUS_ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3, ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit parameter works as expected.
+     */
+    @Test
+    fun testLimitWithoutOffset() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3))
+    }
+
+    /**
+     * Verifies that offset parameter works as expected.
+     */
+    @Test
+    fun testOffsetWithoutLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit and offset parameters work together as expected.
+     */
+    @Test
+    fun testOffsetWithLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO).limit(ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2))
+    }
+
     @Test
     fun testBulkInsert() {
         database.bulkInsert(Employees) {

diff --git a/ktorm-support-oracle/src/test/kotlin/org/ktorm/support/oracle/OracleTest.kt b/ktorm-support-oracle/src/test/kotlin/org/ktorm/support/oracle/OracleTest.kt
@@ -25,6 +25,16 @@ import java.util.concurrent.TimeoutException
 class OracleTest : BaseTest() {
 
     companion object {
+        const val TOTAL_RECORDS = 4
+        const val MINUS_ONE = -1
+        const val ZERO = 0
+        const val ONE = 1
+        const val TWO = 2
+        const val ID_1 = 1
+        const val ID_2 = 2
+        const val ID_3 = 3
+        const val ID_4 = 4
+
         @ClassRule
         @JvmField
         val oracle: OracleContainer = OracleContainer("zerda/oracle-database:11.2.0.2-xe")
@@ -82,6 +92,54 @@ class OracleTest : BaseTest() {
         assert(ids[1] == 3)
     }
 
+    /**
+     * Verifies that invalid pagination parameters are ignored.
+     */
+    @Test
+    fun testBothLimitAndOffsetAreNotPositive() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(ZERO, MINUS_ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3, ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit parameter works as expected.
+     */
+    @Test
+    fun testLimitWithoutOffset() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3))
+    }
+
+    /**
+     * Verifies that offset parameter works as expected.
+     */
+    @Test
+    fun testOffsetWithoutLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit and offset parameters work together as expected.
+     */
+    @Test
+    fun testOffsetWithLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO).limit(ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2))
+    }
+
     @Test
     fun testSequence() {
         for (employee in database.employees) {

diff --git a/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt b/ktorm-support-sqlite/src/test/kotlin/org/ktorm/support/sqlite/SQLiteTest.kt
@@ -21,6 +21,18 @@ import java.time.LocalDate
  */
 class SQLiteTest : BaseTest() {
 
+    companion object {
+        const val TOTAL_RECORDS = 4
+        const val MINUS_ONE = -1
+        const val ZERO = 0
+        const val ONE = 1
+        const val TWO = 2
+        const val ID_1 = 1
+        const val ID_2 = 2
+        const val ID_3 = 3
+        const val ID_4 = 4
+    }
+
     lateinit var connection: Connection
 
     override fun init() {
@@ -72,10 +84,59 @@ class SQLiteTest : BaseTest() {
         assert(query.totalRecords == 4)
 
         val ids = query.map { it[Employees.id] }
+        assert(ids.size == 2)
         assert(ids[0] == 4)
         assert(ids[1] == 3)
     }
 
+    /**
+     * Verifies that invalid pagination parameters are ignored.
+     */
+    @Test
+    fun testBothLimitAndOffsetAreNotPositive() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(ZERO, MINUS_ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3, ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit parameter works as expected.
+     */
+    @Test
+    fun testLimitWithoutOffset() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).limit(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_4, ID_3))
+    }
+
+    /**
+     * Verifies that offset parameter works as expected.
+     */
+    @Test
+    fun testOffsetWithoutLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2, ID_1))
+    }
+
+    /**
+     * Verifies that limit and offset parameters work together as expected.
+     */
+    @Test
+    fun testOffsetWithLimit() {
+        val query = database.from(Employees).select().orderBy(Employees.id.desc()).offset(TWO).limit(ONE)
+        assert(query.totalRecords == TOTAL_RECORDS)
+
+        val ids = query.map { it[Employees.id] }
+        assert(ids == listOf(ID_2))
+    }
+
     @Test
     fun testPagingSql() {
         var query = database