From f9de635da60912f8a84f77f6e36af9534115c125 Mon Sep 17 00:00:00 2001
From: =?utf8?q?L=C3=A9o=20Geoffroy?= <leo.geoffroy@sonarsource.com>
Date: Thu, 2 Feb 2023 13:43:04 +0100
Subject: [PATCH] SONAR-18386 Refactor audit delete query

---
 .../java/org/sonar/db/audit/AuditDao.java     | 10 ++++-
 .../java/org/sonar/db/audit/AuditMapper.java  |  5 ++-
 .../org/sonar/db/audit/AuditMapper.xml        | 38 ++++++++++++-------
 3 files changed, 36 insertions(+), 17 deletions(-)

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java
index dc138d6e78f..bf1fb96cc5a 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditDao.java
@@ -23,6 +23,7 @@ import java.util.List;
 import org.sonar.api.utils.System2;
 import org.sonar.core.util.UuidFactory;
 import org.sonar.db.Dao;
+import org.sonar.db.DatabaseUtils;
 import org.sonar.db.DbSession;
 import org.sonar.db.Pagination;
 
@@ -57,7 +58,7 @@ public class AuditDao implements Dao {
       long now = system2.now();
       auditDto.setCreatedAt(now);
     }
-    if(auditDto.getNewValue().length() > MAX_SIZE) {
+    if (auditDto.getNewValue().length() > MAX_SIZE) {
       auditDto.setNewValue(EXCEEDED_LENGTH);
     }
     getMapper(dbSession).insert(auditDto);
@@ -68,7 +69,12 @@ public class AuditDao implements Dao {
   }
 
   public long deleteBefore(DbSession dbSession, long threshold) {
-    return getMapper(dbSession).purge(threshold);
+    List<String> uuids = getMapper(dbSession).selectUuidsOlderThan(threshold);
+    DatabaseUtils.executeLargeInputsWithoutOutput(uuids, list -> {
+      getMapper(dbSession).purgeUuids(list);
+      dbSession.commit();
+    });
+    return uuids.size();
   }
 
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java
index 88c08866db6..929f12e76ac 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/audit/AuditMapper.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.audit;
 
+import java.util.Collection;
 import java.util.List;
 import org.apache.ibatis.annotations.Param;
 import org.sonar.db.Pagination;
@@ -31,5 +32,7 @@ public interface AuditMapper {
 
   List<AuditDto> selectOlderThan(@Param("beforeTimestamp") long beforeTimestamp);
 
-  long purge(long threshold);
+  List<String> selectUuidsOlderThan(@Param("beforeTimestamp") long beforeTimestamp);
+
+  void purgeUuids(@Param("uuids") Collection<String> uuids);
 }
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 edf3aaa7329..a532172fc60 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
@@ -93,21 +93,31 @@
       a.created_at &lt; #{beforeTimestamp,jdbcType=BIGINT}
   </select>
 
-  <delete id="purge" parameterType="long">
-    delete from audits
-    where uuid in (select a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
-    order by a.created_at limit 100000)
-  </delete>
+  <select id="selectUuidsOlderThan" parameterType="long" resultType="string">
+    select a.uuid
+    from audits a
+    where a.created_at &lt; #{beforeTimestamp,jdbcType=BIGINT}
+    order by a.created_at
+    limit 100000
+  </select>
 
-  <delete id="purge" parameterType="long" databaseId="mssql">
-    delete from audits
-    where uuid in (select top 100000 a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
-      order by a.created_at)
-  </delete>
+    <select id="selectUuidsOlderThan" parameterType="long" resultType="string" databaseId="mssql">
+    select top 100000 a.uuid
+    from audits a
+    where a.created_at &lt; #{beforeTimestamp,jdbcType=BIGINT}
+    order by a.created_at
+  </select>
+
+    <select id="selectUuidsOlderThan" parameterType="long" resultType="string" databaseId="oracle">
+    select a.uuid
+    from audits a
+    where a.created_at &lt; #{beforeTimestamp,jdbcType=BIGINT}
+    order by a.created_at
+    fetch first 100000 rows only
+  </select>
 
-    <delete id="purge" parameterType="long" databaseId="oracle">
-    delete from audits
-    where uuid in (select a.uuid from audits a where a.created_at &lt; #{threshold,jdbcType=BIGINT}
-      order by a.created_at fetch first 100000 rows only)
+  <delete id="purgeUuids" parameterType="string">
+    delete from audits where uuid in
+    <foreach collection="uuids" open="(" close=")" item="uuid" separator=",">#{uuid,jdbcType=VARCHAR}</foreach>
   </delete>
 </mapper>
-- 
2.39.5