From a247cdab18befbdbf4cc3ebf692053a845d24dc4 Mon Sep 17 00:00:00 2001
From: Sébastien Lesaint <sebastien.lesaint@sonarsource.com>
Date: Wed, 20 Jan 2016 14:45:13 +0100
Subject: SONAR-7130 faster purge: single SQL query for all PRJ children

---
 .../src/main/java/org/sonar/db/component/ResourceDao.java | 15 ++-------------
 .../main/java/org/sonar/db/component/ResourceMapper.java  |  2 +-
 sonar-db/src/main/java/org/sonar/db/purge/PurgeDao.java   |  8 +++-----
 .../resources/org/sonar/db/component/ResourceMapper.xml   | 10 ++++++++--
 .../PurgeDaoTest/shouldDeleteAbortedBuilds-result.xml     |  1 +
 .../db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds.xml   |  1 +
 6 files changed, 16 insertions(+), 21 deletions(-)

(limited to 'sonar-db')

diff --git a/sonar-db/src/main/java/org/sonar/db/component/ResourceDao.java b/sonar-db/src/main/java/org/sonar/db/component/ResourceDao.java
index 22fd72a83c8..e05678a6d3c 100644
--- a/sonar-db/src/main/java/org/sonar/db/component/ResourceDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/component/ResourceDao.java
@@ -87,19 +87,8 @@ public class ResourceDao extends AbstractDao {
     return session.getMapper(ResourceMapper.class).selectLastSnapshotByResourceKey(resourceKey);
   }
 
-  public List<ResourceDto> getDescendantProjects(long projectId, SqlSession session) {
-    ResourceMapper mapper = session.getMapper(ResourceMapper.class);
-    List<ResourceDto> resources = newArrayList();
-    appendChildProjects(projectId, mapper, resources);
-    return resources;
-  }
-
-  private void appendChildProjects(long projectId, ResourceMapper mapper, List<ResourceDto> resources) {
-    List<ResourceDto> subProjects = mapper.selectDescendantProjects(projectId);
-    for (ResourceDto subProject : subProjects) {
-      resources.add(subProject);
-      appendChildProjects(subProject.getId(), mapper, resources);
-    }
+  public List<ResourceDto> selectWholeTreeForRootId(SqlSession session, long rootId, String scope) {
+    return session.getMapper(ResourceMapper.class).selectWholeTreeForRootId(rootId, scope);
   }
 
   public void updateAuthorizationDate(Long projectId, SqlSession session) {
diff --git a/sonar-db/src/main/java/org/sonar/db/component/ResourceMapper.java b/sonar-db/src/main/java/org/sonar/db/component/ResourceMapper.java
index f48ba74b757..32ed246eada 100644
--- a/sonar-db/src/main/java/org/sonar/db/component/ResourceMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/component/ResourceMapper.java
@@ -35,7 +35,7 @@ public interface ResourceMapper {
 
   ResourceDto selectResourceByUuid(String uuid);
 
-  List<ResourceDto> selectDescendantProjects(long rootProjectId);
+  List<ResourceDto> selectWholeTreeForRootId(@Param("rootId") long rootId, @Param("scope") String scope);
 
   /**
    * @since 3.0
diff --git a/sonar-db/src/main/java/org/sonar/db/purge/PurgeDao.java b/sonar-db/src/main/java/org/sonar/db/purge/PurgeDao.java
index d3b005e3b7c..88af87c4a3f 100644
--- a/sonar-db/src/main/java/org/sonar/db/purge/PurgeDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/purge/PurgeDao.java
@@ -29,6 +29,7 @@ import org.apache.commons.lang.ArrayUtils;
 import org.apache.ibatis.session.ResultContext;
 import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.SqlSession;
+import org.sonar.api.resources.Scopes;
 import org.sonar.api.utils.System2;
 import org.sonar.api.utils.log.Logger;
 import org.sonar.api.utils.log.Loggers;
@@ -210,11 +211,8 @@ public class PurgeDao implements Dao {
   /**
    * Load the whole tree of projects, including the project given in parameter.
    */
-  private List<ResourceDto> getProjects(long rootProjectId, SqlSession session) {
-    List<ResourceDto> projects = Lists.newArrayList();
-    projects.add(resourceDao.selectResource(rootProjectId, session));
-    projects.addAll(resourceDao.getDescendantProjects(rootProjectId, session));
-    return projects;
+  private List<ResourceDto> getProjects(long rootId, SqlSession session) {
+    return resourceDao.selectWholeTreeForRootId(session, rootId, Scopes.PROJECT);
   }
 
   private PurgeMapper mapper(DbSession session) {
diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml
index 9c86d2c4e7f..0816902334c 100644
--- a/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/component/ResourceMapper.xml
@@ -127,8 +127,14 @@
     </where>
   </select>
 
-  <select id="selectDescendantProjects" parameterType="long" resultMap="resourceResultMap">
-    select * from projects where scope='PRJ' and root_id=#{id}
+  <select id="selectWholeTreeForRootId" parameterType="long" resultMap="resourceResultMap">
+    select
+    pAll.*
+    from projects pAll
+    inner join projects pRoot on pAll.project_uuid = pRoot.uuid and pRoot.id = #{rootId}
+    <where>
+      pAll.scope = #{scope}
+    </where>
   </select>
 
   <select id="selectRootProjectByComponentKey" parameterType="string" resultMap="resourceResultMap">
diff --git a/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds-result.xml b/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds-result.xml
index 041565fc171..0a9e001e8d2 100644
--- a/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds-result.xml
+++ b/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds-result.xml
@@ -7,6 +7,7 @@ Snapshot 2 has been deleted
 
   <!-- the project -->
   <projects id="1" enabled="[true]" root_id="[null]"
+            uuid="projectUUID" project_uuid="projectUUID"
             long_name="[null]" scope="PRJ" qualifier="TRK" kee="project" name="project"
             description="[null]" language="java" copy_resource_id="[null]" person_id="[null]"
             authorization_updated_at="[null]"/>
diff --git a/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds.xml b/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds.xml
index 0c821011796..91881d8c22f 100644
--- a/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds.xml
+++ b/sonar-db/src/test/resources/org/sonar/db/purge/PurgeDaoTest/shouldDeleteAbortedBuilds.xml
@@ -2,6 +2,7 @@
 
   <!-- the project -->
   <projects id="1" enabled="[true]" root_id="[null]"
+            uuid="projectUUID" project_uuid="projectUUID"
             long_name="[null]" scope="PRJ" qualifier="TRK" kee="project" name="project"
             description="[null]" language="java" copy_resource_id="[null]" person_id="[null]"
             authorization_updated_at="[null]"/>
-- 
cgit v1.2.3