From c5d6ef5f209111463580bc9bfe7013af30c9ad4b Mon Sep 17 00:00:00 2001
From: Belen Pruvost <belen.pruvost@sonarsource.com>
Date: Wed, 28 Jul 2021 17:55:06 +0200
Subject: [PATCH] SONAR-15148 - Fix Audit paginated query for Oracle

---
 .../org/sonar/db/audit/AuditMapper.xml        | 36 ++++++++++++++++++-
 1 file changed, 35 insertions(+), 1 deletion(-)

diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml
index 469560c01a5..6b8a9fc5646 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/audit/AuditMapper.xml
@@ -13,10 +13,30 @@
     a.created_at as "createdAt"
   </sql>
 
+  <sql id="sqlColumnsInInnerQuery">
+    uuid,
+    user_uuid,
+    user_login,
+    category,
+    operation,
+    new_value,
+    created_at
+  </sql>
+
   <select id="selectByPeriodPaginated" parameterType="map" resultType="org.sonar.db.audit.AuditDto">
+    select
+      <include refid="sqlColumns"/>
+    from audits a
+    where
+      a.created_at &gt;= #{start} and a.created_at &lt; #{end}
+    limit #{pagination.pageSize,jdbcType=INTEGER} offset #{pagination.offset,jdbcType=INTEGER}
+  </select>
+
+  <select id="selectByPeriodPaginated" parameterType="map" resultType="org.sonar.db.audit.AuditDto" databaseId="mssql">
     select <include refid="sqlColumns"/> from (
       select
-        row_number() over(order by created_at, uuid) as row_number, *
+        row_number() over(order by created_at, uuid) as row_number,
+          <include refid="sqlColumnsInInnerQuery" />
         from audits
         where created_at &gt;= #{start} and created_at &lt; #{end}
     ) as a
@@ -25,6 +45,20 @@
     order by a.row_number asc
   </select>
 
+  <select id="selectByPeriodPaginated" parameterType="map" resultType="org.sonar.db.audit.AuditDto" databaseId="oracle">
+    select <include refid="sqlColumns"/> from (
+      select rownum as rn, t.* from (
+        select
+        <include refid="sqlColumnsInInnerQuery"/>
+        from audits
+        where created_at &gt;= #{start} and created_at &lt; #{end}
+      ) t
+    ) a
+    where
+      a.rn between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+    order by a.rn asc
+  </select>
+
   <insert id="insert" parameterType="Map" useGeneratedKeys="false">
     INSERT INTO audits
     (
-- 
2.39.5