SONAR-7480 optimize SQL request in QP Search WS

4 files changed, 130 insertions, 8 deletions

diff --git a/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java b/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
index ca2ec555f1c..fbe2d1913c2 100644
--- a/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
@@ -19,16 +19,20 @@
  */
 package org.sonar.db.qualityprofile;
 
+import com.google.common.base.Function;
 import com.google.common.base.Preconditions;
 import com.google.common.collect.Lists;
 import com.google.common.collect.Maps;
+import java.util.Collection;
 import java.util.Date;
 import java.util.List;
 import java.util.Map;
 import javax.annotation.CheckForNull;
+import javax.annotation.Nonnull;
 import javax.annotation.Nullable;
 import org.sonar.api.utils.System2;
 import org.sonar.db.Dao;
+import org.sonar.db.DatabaseUtils;
 import org.sonar.db.DbSession;
 import org.sonar.db.MyBatis;
 import org.sonar.db.RowNotFoundException;
@@ -155,8 +159,7 @@ public class QualityProfileDao implements Dao {
   }
 
   /**
-   * @deprecated Replaced by
-   *    {@link #selectAll(DbSession)}
+   * @deprecated Replaced by {@link #selectAll(DbSession)}
    */
   @Deprecated
   public List<QualityProfileDto> selectAll() {
@@ -169,7 +172,18 @@ public class QualityProfileDao implements Dao {
   }
 
   @CheckForNull
-  public QualityProfileDto selectDefaultProfile(DbSession session, String language) {
+  public List<QualityProfileDto> selectDefaultProfiles(final DbSession session, Collection<String> languageKeys) {
+    return DatabaseUtils.executeLargeInputs(languageKeys, new Function<List<String>, List<QualityProfileDto>>() {
+      @Override
+      @Nonnull
+      public List<QualityProfileDto> apply(@Nonnull List<String> input) {
+        return mapper(session).selectDefaultProfiles(input);
+      }
+    });
+  }
+
+  @CheckForNull
+  public QualityProfileDto selectDefaultProfile(final DbSession session, String language) {
     return mapper(session).selectDefaultProfile(language);
   }
 
@@ -198,6 +212,16 @@ public class QualityProfileDao implements Dao {
     return mapper(session).selectByProjectAndLanguage(projectKey, language);
   }
 
+  public List<QualityProfileDto> selectByProjectAndLanguages(final DbSession session, final String projectKey, Collection<String> languageKeys) {
+    return DatabaseUtils.executeLargeInputs(languageKeys, new Function<List<String>, List<QualityProfileDto>>() {
+      @Override
+      @Nonnull
+      public List<QualityProfileDto> apply(@Nonnull List<String> input) {
+        return mapper(session).selectByProjectAndLanguages(projectKey, input);
+      }
+    });
+  }
+
   public List<QualityProfileDto> selectByLanguage(String language) {
     DbSession session = mybatis.openSession(false);
     try {
@@ -273,6 +297,16 @@ public class QualityProfileDao implements Dao {
     return mapper(session).selectByNameAndLanguage(name, language);
   }
 
+  public List<QualityProfileDto> selectByNameAndLanguages(final String name, Collection<String> languageKeys, final DbSession session) {
+    return DatabaseUtils.executeLargeInputs(languageKeys, new Function<List<String>, List<QualityProfileDto>>() {
+      @Override
+      @Nonnull
+      public List<QualityProfileDto> apply(@Nonnull List<String> input) {
+        return mapper(session).selectByNameAndLanguages(name, input);
+      }
+    });
+  }
+
   public List<ComponentDto> selectProjects(String profileName, String language) {
     DbSession session = mybatis.openSession(false);
     try {
@@ -346,5 +380,4 @@ public class QualityProfileDao implements Dao {
   private QualityProfileMapper mapper(DbSession session) {
     return session.getMapper(QualityProfileMapper.class);
   }
-
 }
diff --git a/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java b/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
index d2f9e73a959..b8a6ef79c09 100644
--- a/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
@@ -37,9 +37,13 @@ public interface QualityProfileMapper {
   @CheckForNull
   QualityProfileDto selectDefaultProfile(@Param("language") String language);
 
+  List<QualityProfileDto> selectDefaultProfiles(@Param("languages") List<String> languages);
+
   @CheckForNull
   QualityProfileDto selectByNameAndLanguage(@Param("name") String name, @Param("language") String language);
 
+  List<QualityProfileDto> selectByNameAndLanguages(@Param("name") String name, @Param("languages") List<String> languages);
+
   @CheckForNull
   QualityProfileDto selectById(@Param("id") Integer id);
 
@@ -70,6 +74,8 @@ public interface QualityProfileMapper {
 
   QualityProfileDto selectByProjectAndLanguage(@Param("projectKey") String projectKey, @Param("language") String language);
 
+  List<QualityProfileDto> selectByProjectAndLanguages(@Param("projectKey") String projectKey, @Param("languages") List<String> input);
+
   void insertProjectProfileAssociation(@Param("projectUuid") String projectUuid, @Param("profileKey") String profileKey);
 
   void updateProjectProfileAssociation(@Param("projectUuid") String projectUuid, @Param("profileKey") String profileKey);
diff --git a/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
index 873a983c4cb..edc2563f8e2 100644
--- a/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
@@ -49,6 +49,19 @@
     WHERE p.name=#{name} AND p.language=#{language}
   </select>
 
+  <select id="selectByNameAndLanguages" parameterType="map" resultType="QualityProfile">
+    SELECT
+    <include refid="profilesColumns"/>
+    FROM rules_profiles p
+    <where>
+      p.name=#{name}
+      AND p.language in
+      <foreach collection="languages" open="(" close=")" item="language" separator=",">
+        #{language}
+      </foreach>
+    </where>
+  </select>
+
   <select id="selectByKey" parameterType="string" resultType="QualityProfile">
     SELECT
     <include refid="profilesColumns"/>
@@ -97,8 +110,23 @@
     SELECT
     <include refid="profilesColumns"/>
     FROM rules_profiles p
-    WHERE p.is_default=${_true}
-    AND p.language=#{language}
+    <where>
+      p.is_default=${_true}
+      AND p.language=#{language}
+    </where>
+  </select>
+
+  <select id="selectDefaultProfiles" parameterType="map" resultType="QualityProfile">
+    SELECT
+    <include refid="profilesColumns"/>
+    FROM rules_profiles p
+    <where>
+      p.is_default=${_true}
+      AND p.language in
+      <foreach collection="languages" open="(" close=")" item="language" separator=",">
+        #{language}
+      </foreach>
+    </where>
   </select>
 
   <select id="selectProjects" resultType="Component">
@@ -184,11 +212,24 @@
     <include refid="profilesColumns"/>
     FROM rules_profiles p
     JOIN project_qprofiles pp ON pp.profile_key=p.kee
-    JOIN projects project ON pp.project_uuid=project.uuid
-    AND project.kee=#{projectKey}
+    JOIN projects project ON pp.project_uuid=project.uuid AND project.kee=#{projectKey}
     WHERE p.language=#{language}
   </select>
 
+  <select id="selectByProjectAndLanguages" parameterType="map" resultType="QualityProfile">
+    SELECT
+    <include refid="profilesColumns"/>
+    FROM rules_profiles p
+    JOIN project_qprofiles pp ON pp.profile_key=p.kee
+    JOIN projects project ON pp.project_uuid=project.uuid AND project.kee=#{projectKey}
+    <where>
+      p.language in
+      <foreach collection="languages" open="(" close=")" item="language" separator=",">
+        #{language}
+      </foreach>
+    </where>
+  </select>
+
   <insert id="insertProjectProfileAssociation" keyColumn="id" useGeneratedKeys="true">
     INSERT INTO project_qprofiles (project_uuid, profile_key) VALUES (#{projectUuid}, #{profileKey})
   </insert>
diff --git a/sonar-db/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java b/sonar-db/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
index f53a1edd2b1..d47a6e19467 100644
--- a/sonar-db/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
@@ -28,6 +28,8 @@ import org.sonar.api.utils.System2;
 import org.sonar.core.util.UtcDateUtils;
 import org.sonar.db.DbTester;
 
+import static com.google.common.collect.ImmutableList.of;
+import static java.util.Collections.singletonList;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.mockito.Mockito.mock;
 import static org.mockito.Mockito.when;
@@ -130,6 +132,18 @@ public class QualityProfileDaoTest {
   }
 
   @Test
+  public void get_default_profiles() {
+    dbTester.prepareDbUnit(getClass(), "shared.xml");
+
+    List<QualityProfileDto> java = dao.selectDefaultProfiles(dbTester.getSession(), singletonList("java"));
+    assertThat(java).extracting("key").containsOnly("java_sonar_way");
+
+    assertThat(dao.selectDefaultProfiles(dbTester.getSession(), singletonList("js"))).isEmpty();
+    assertThat(dao.selectDefaultProfiles(dbTester.getSession(), of("java", "js"))).extracting("key").containsOnly("java_sonar_way");
+    assertThat(dao.selectDefaultProfiles(dbTester.getSession(), of("js", "java"))).extracting("key").containsOnly("java_sonar_way");
+  }
+
+  @Test
   public void get_by_name_and_language() {
     dbTester.prepareDbUnit(getClass(), "shared.xml");
 
@@ -144,6 +158,22 @@ public class QualityProfileDaoTest {
   }
 
   @Test
+  public void get_by_name_and_languages() {
+    dbTester.prepareDbUnit(getClass(), "shared.xml");
+
+    List<QualityProfileDto> dtos = dao.selectByNameAndLanguages("Sonar Way", singletonList("java"), dbTester.getSession());
+    assertThat(dtos).hasSize(1);
+    QualityProfileDto dto = dtos.iterator().next();
+    assertThat(dto.getId()).isEqualTo(1);
+    assertThat(dto.getName()).isEqualTo("Sonar Way");
+    assertThat(dto.getLanguage()).isEqualTo("java");
+    assertThat(dto.getParentKee()).isNull();
+
+    assertThat(dao.selectByNameAndLanguages("Sonar Way", singletonList("unknown"), dbTester.getSession())).isEmpty();
+    assertThat(dao.selectByNameAndLanguages("Sonar Way", of("java", "unknown"), dbTester.getSession())).extracting("id").containsOnly(1);
+  }
+
+  @Test
   public void find_by_language() {
     dbTester.prepareDbUnit(getClass(), "select_by_language.xml");
 
@@ -236,4 +266,16 @@ public class QualityProfileDaoTest {
     assertThat(dao.selectByProjectAndLanguage(dbTester.getSession(), "org.codehaus.sonar:sonar", "unkown")).isNull();
     assertThat(dao.selectByProjectAndLanguage(dbTester.getSession(), "unknown", "java")).isNull();
   }
+
+  @Test
+  public void select_by_project_key_and_languages() {
+    dbTester.prepareDbUnit(getClass(), "projects.xml");
+
+    List<QualityProfileDto> dto = dao.selectByProjectAndLanguages(dbTester.getSession(), "org.codehaus.sonar:sonar", singletonList("java"));
+    assertThat(dto).extracting("id").containsOnly(1);
+
+    assertThat(dao.selectByProjectAndLanguages(dbTester.getSession(), "org.codehaus.sonar:sonar", singletonList("unkown"))).isEmpty();
+    assertThat(dao.selectByProjectAndLanguages(dbTester.getSession(), "org.codehaus.sonar:sonar", of("java", "unkown"))).extracting("id").containsOnly(1);
+    assertThat(dao.selectByProjectAndLanguages(dbTester.getSession(), "unknown", singletonList("java"))).isEmpty();
+  }
 }