From 3a6d71993dfcde510c7852a0291961d0aa89ad74 Mon Sep 17 00:00:00 2001
From: =?utf8?q?S=C3=A9bastien=20Lesaint?=
 <sebastien.lesaint@sonarsource.com>
Date: Tue, 28 Mar 2017 16:06:03 +0200
Subject: [PATCH] SONAR-9040 fix pagination in CeQueueDao#selectEligibleForPeek

---
 .../main/java/org/sonar/db/ce/CeQueueDao.java |  5 +-
 .../java/org/sonar/db/ce/CeQueueMapper.java   |  3 +-
 .../org/sonar/db/ce/CeQueueMapper.xml         | 50 +++++++++++++++++--
 3 files changed, 51 insertions(+), 7 deletions(-)

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueDao.java
index 73e90cfcfda..dc94239d882 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueDao.java
@@ -26,6 +26,7 @@ import org.apache.ibatis.session.RowBounds;
 import org.sonar.api.utils.System2;
 import org.sonar.db.Dao;
 import org.sonar.db.DbSession;
+import org.sonar.db.Pagination;
 
 import static java.util.Collections.emptyList;
 import static org.sonar.db.ce.CeQueueDto.Status.IN_PROGRESS;
@@ -33,7 +34,7 @@ import static org.sonar.db.ce.CeQueueDto.Status.PENDING;
 
 public class CeQueueDao implements Dao {
 
-  private static final RowBounds ONE_ROW_LIMIT = new RowBounds(0, 1);
+  private static final Pagination ONE_RESULT_PAGINATION = Pagination.forPage(1).andSize(1);
 
   private final System2 system2;
 
@@ -110,7 +111,7 @@ public class CeQueueDao implements Dao {
   }
 
   public Optional<CeQueueDto> peek(DbSession session, String workerUuid) {
-    List<EligibleTaskDto> eligibles = mapper(session).selectEligibleForPeek(ONE_ROW_LIMIT);
+    List<EligibleTaskDto> eligibles = mapper(session).selectEligibleForPeek(ONE_RESULT_PAGINATION);
     if (eligibles.isEmpty()) {
       return Optional.absent();
     }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueMapper.java
index b89569f01f4..d605e2b8be1 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/ce/CeQueueMapper.java
@@ -24,6 +24,7 @@ import javax.annotation.CheckForNull;
 import javax.annotation.Nullable;
 import org.apache.ibatis.annotations.Param;
 import org.apache.ibatis.session.RowBounds;
+import org.sonar.db.Pagination;
 
 public interface CeQueueMapper {
 
@@ -35,7 +36,7 @@ public interface CeQueueMapper {
 
   int countByQuery(@Param("query") CeTaskQuery query);
 
-  List<EligibleTaskDto> selectEligibleForPeek(RowBounds rowBounds);
+  List<EligibleTaskDto> selectEligibleForPeek(@Param("pagination") Pagination pagination);
 
   @CheckForNull
   CeQueueDto selectByUuid(@Param("uuid") String uuid);
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/ce/CeQueueMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/ce/CeQueueMapper.xml
index 657c8a9d526..2d1f28c90c9 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/ce/CeQueueMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/ce/CeQueueMapper.xml
@@ -114,8 +114,45 @@
 
   <select id="selectEligibleForPeek" resultType="org.sonar.db.ce.EligibleTaskDto">
     select
-      cq.uuid as "uuid",
-      cq.execution_count as "executionCount"
+      <include refid="columnsSelectEligibleForPeek"/>
+    <include refid="sqlSelectEligibleForPeek"/>
+    <include refid="orderBySelectEligibleForPeek"/>
+    limit #{pagination.pageSize,jdbcType=INTEGER} offset #{pagination.offset,jdbcType=INTEGER}
+  </select>
+
+  <select id="selectEligibleForPeek" parameterType="map" resultType="org.sonar.db.ce.EligibleTaskDto" databaseId="mssql">
+    select * from (
+      select
+        row_number() over(<include refid="orderBySelectEligibleForPeek"/>) as number,
+        <include refid="columnsSelectEligibleForPeek"/>
+      <include refid="sqlSelectEligibleForPeek"/>
+    ) as query
+    where
+      query.number between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+    <include refid="orderBySelectEligibleForPeek"/>
+  </select>
+
+  <select id="selectEligibleForPeek" parameterType="map" resultType="org.sonar.db.ce.EligibleTaskDto" databaseId="oracle">
+    select * from (
+      select rownum as rn, t.* from (
+        select
+          <include refid="columnsSelectEligibleForPeek"/>
+        <include refid="sqlSelectEligibleForPeek" />
+        <include refid="orderBySelectEligibleForPeek"/>
+      ) t
+    ) t
+    where
+      t.rn between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+  </select>
+
+  <sql id="columnsSelectEligibleForPeek">
+    cq.uuid as "uuid",
+    cq.execution_count as "executionCount",
+    cq.created_at as "created_at",
+    cq.id as "id"
+  </sql>
+
+  <sql id="sqlSelectEligibleForPeek">
     from
       ce_queue cq
     where
@@ -129,8 +166,13 @@
           cq.component_uuid=cq2.component_uuid
           and cq2.status &lt;&gt; 'PENDING'
       )
-    <include refid="orderByDateAndId"/>
-  </select>
+  </sql>
+
+  <sql id="orderBySelectEligibleForPeek">
+    order by
+      created_at asc,
+      id asc
+  </sql>
 
   <insert id="insert" parameterType="org.sonar.db.ce.CeQueueDto" useGeneratedKeys="false">
     insert into ce_queue
-- 
2.39.5