From 410a07681d5cc51dfcfd1afee0c5f06df30a2d12 Mon Sep 17 00:00:00 2001
From: Eric Hartmann <112486+ehartmann@users.noreply.github.com>
Date: Tue, 11 Dec 2018 16:31:27 +0100
Subject: [PATCH] SONARCLOUD-278 Reduce SQL requests on RegisterQualityProfiles

---
 .../db/qualityprofile/QualityProfileDao.java  | 12 +++-
 .../qualityprofile/QualityProfileMapper.java  |  9 ++-
 .../qualityprofile/QualityProfileMapper.xml   | 52 +++++++++-------
 .../qualityprofile/QualityProfileDaoTest.java | 60 ++++++++++++++++++-
 .../RegisterQualityProfiles.java              | 19 +++---
 .../RegisterQualityProfilesTest.java          | 11 +++-
 6 files changed, 124 insertions(+), 39 deletions(-)

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
index db8095b83cc..00269c40bb2 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileDao.java
@@ -25,6 +25,7 @@ import java.util.Date;
 import java.util.List;
 import java.util.Locale;
 import java.util.Map;
+import java.util.Set;
 import javax.annotation.CheckForNull;
 import javax.annotation.Nullable;
 import org.sonar.api.utils.System2;
@@ -75,7 +76,7 @@ public class QualityProfileDao implements Dao {
     return mapper(dbSession).selectBuiltInRuleProfiles();
   }
 
-  public List<QProfileDto> selectBuiltInRuleProfilesWithActiveRules(DbSession dbSession) {
+  public List<RulesProfileDto> selectBuiltInRuleProfilesWithActiveRules(DbSession dbSession) {
     return mapper(dbSession).selectBuiltInRuleProfilesWithActiveRules();
   }
 
@@ -135,8 +136,8 @@ public class QualityProfileDao implements Dao {
     return executeLargeInputs(languages, partition -> mapper(dbSession).selectDefaultProfiles(organization.getUuid(), partition));
   }
 
-  public List<QProfileDto> selectDefaultBuiltInProfilesWithoutActiveRules(DbSession dbSession) {
-    return mapper(dbSession).selectDefaultBuiltInProfilesWithoutActiveRules();
+  public List<QProfileDto> selectDefaultBuiltInProfilesWithoutActiveRules(DbSession dbSession, Set<String> languages) {
+    return executeLargeInputs(languages, partition -> mapper(dbSession).selectDefaultBuiltInProfilesWithoutActiveRules(partition));
   }
 
   @CheckForNull
@@ -180,6 +181,11 @@ public class QualityProfileDao implements Dao {
     return mapper(dbSession).selectByNameAndLanguage(organization.getUuid(), name, language);
   }
 
+  @CheckForNull
+  public QProfileDto selectByRuleProfileUuid(DbSession dbSession, String organizationUuid, String ruleProfileKee) {
+    return mapper(dbSession).selectByRuleProfileUuid(organizationUuid, ruleProfileKee);
+  }
+
   public List<QProfileDto> selectByNameAndLanguages(DbSession dbSession, OrganizationDto organization, String name, Collection<String> languages) {
     return mapper(dbSession).selectByNameAndLanguages(organization.getUuid(), name, languages);
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
index dab4546fd2b..d95a2f7ca33 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QualityProfileMapper.java
@@ -42,7 +42,7 @@ public interface QualityProfileMapper {
 
   List<RulesProfileDto> selectBuiltInRuleProfiles();
 
-  List<QProfileDto> selectBuiltInRuleProfilesWithActiveRules();
+  List<RulesProfileDto> selectBuiltInRuleProfilesWithActiveRules();
 
   @CheckForNull
   RulesProfileDto selectRuleProfile(@Param("uuid") String ruleProfileUuid);
@@ -52,7 +52,7 @@ public interface QualityProfileMapper {
   @CheckForNull
   QProfileDto selectDefaultProfile(@Param("organizationUuid") String organizationUuid, @Param("language") String language);
 
-  List<QProfileDto> selectDefaultBuiltInProfilesWithoutActiveRules();
+  List<QProfileDto> selectDefaultBuiltInProfilesWithoutActiveRules(@Param("languages") List<String> languages);
 
   List<QProfileDto> selectDefaultProfiles(
     @Param("organizationUuid") String organizationUuid,
@@ -64,6 +64,11 @@ public interface QualityProfileMapper {
     @Param("name") String name,
     @Param("language") String language);
 
+  @CheckForNull
+  QProfileDto selectByRuleProfileUuid(
+    @Param("organizationUuid") String organizationUuid,
+    @Param("ruleProfileUuid") String ruleProfileKee);
+
   List<QProfileDto> selectByNameAndLanguages(
     @Param("organizationUuid") String organizationUuid,
     @Param("name") String name,
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
index e6ebcaa4e87..3da11e2c1b9 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QualityProfileMapper.xml
@@ -138,6 +138,22 @@
       and oqp.organization_uuid = dp.organization_uuid
   </select>
 
+  <select id="selectDefaultBuiltInProfilesWithoutActiveRules" parameterType="map" resultType="org.sonar.db.qualityprofile.QProfileDto">
+    SELECT
+    <include refid="qProfileColumns"/>
+    FROM org_qprofiles oqp
+    INNER JOIN rules_profiles rp ON oqp.rules_profile_uuid = rp.kee
+    INNER JOIN default_qprofiles dp ON dp.qprofile_uuid = oqp.uuid
+    WHERE
+      rp.is_built_in = ${_true}
+      AND rp.language IN <foreach collection="languages" open="(" close=")" item="language" separator=",">#{language, jdbcType=VARCHAR}</foreach>
+      AND NOT EXISTS (
+        SELECT 1 FROM active_rules ar
+        INNER JOIN rules r ON r.id = ar.rule_id AND r.status &lt;&gt; 'REMOVED'
+        WHERE profile_id=rp.id
+      )
+  </select>
+
   <select id="selectDefaultProfiles" parameterType="map" resultType="org.sonar.db.qualityprofile.QProfileDto">
     select
     <include refid="qProfileColumns"/>
@@ -151,13 +167,10 @@
       and oqp.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
   </select>
 
-  <select id="selectBuiltInRuleProfilesWithActiveRules" resultType="org.sonar.db.qualityprofile.QProfileDto">
-    SELECT
-      <include refid="qProfileColumns"/>
-    FROM org_qprofiles oqp
-    INNER JOIN rules_profiles rp ON oqp.rules_profile_uuid = rp.kee
-    WHERE
-      rp.is_built_in = ${_true}
+  <select id="selectBuiltInRuleProfilesWithActiveRules" resultType="org.sonar.db.qualityprofile.RulesProfileDto">
+    select <include refid="ruleProfileColumns"/>
+    from rules_profiles rp
+    where rp.is_built_in = ${_true}
       AND EXISTS (
         SELECT 1 FROM active_rules ar
         INNER JOIN rules r ON r.id = ar.rule_id AND r.status &lt;&gt; 'REMOVED'
@@ -165,21 +178,6 @@
       )
   </select>
 
-  <select id="selectDefaultBuiltInProfilesWithoutActiveRules" resultType="org.sonar.db.qualityprofile.QProfileDto">
-    SELECT
-      <include refid="qProfileColumns"/>
-    FROM org_qprofiles oqp
-    INNER JOIN rules_profiles rp ON oqp.rules_profile_uuid = rp.kee
-    INNER JOIN default_qprofiles dp ON dp.qprofile_uuid = oqp.uuid
-    WHERE
-      rp.is_built_in = ${_true}
-      AND NOT EXISTS (
-        SELECT 1 FROM active_rules ar
-        INNER JOIN rules r ON r.id = ar.rule_id AND r.status &lt;&gt; 'REMOVED'
-        WHERE profile_id=rp.id
-      )
-  </select>
-
   <select id="selectByNameAndLanguage" parameterType="map" resultType="org.sonar.db.qualityprofile.QProfileDto">
     select
     <include refid="qProfileColumns"/>
@@ -190,6 +188,16 @@
       and rp.language = #{language, jdbcType=VARCHAR}
       and oqp.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
   </select>
+
+  <select id="selectByRuleProfileUuid" parameterType="map" resultType="org.sonar.db.qualityprofile.QProfileDto">
+    select
+    <include refid="qProfileColumns"/>
+    from org_qprofiles oqp
+    inner join rules_profiles rp on oqp.rules_profile_uuid = rp.kee
+    where
+    rp.kee = #{ruleProfileUuid, jdbcType=VARCHAR}
+    and oqp.organization_uuid = #{organizationUuid, jdbcType=VARCHAR}
+  </select>
     
   <select id="selectByNameAndLanguages" parameterType="map" resultType="org.sonar.db.qualityprofile.QProfileDto">
     select
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
index d654d6b82a6..fe8c90973b0 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QualityProfileDaoTest.java
@@ -19,6 +19,7 @@
  */
 package org.sonar.db.qualityprofile;
 
+import com.google.common.collect.Sets;
 import java.util.Arrays;
 import java.util.Collection;
 import java.util.Collections;
@@ -498,11 +499,66 @@ public class QualityProfileDaoTest {
 
     dbSession.commit();
 
-    List<QProfileDto> qProfileDtos = underTest.selectBuiltInRuleProfilesWithActiveRules(dbSession);
-    assertThat(qProfileDtos).extracting(QProfileDto::getName)
+    List<RulesProfileDto> rulesProfileDtos = underTest.selectBuiltInRuleProfilesWithActiveRules(dbSession);
+    assertThat(rulesProfileDtos).extracting(RulesProfileDto::getName)
       .containsOnly(builtInQPWithActiveRules.getName());
   }
 
+  @Test
+  public void selectByRuleProfileUuid() {
+    db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(false));
+    db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(true));
+    QProfileDto qprofile1 = db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(true));
+
+    dbSession.commit();
+
+    assertThat(underTest.selectByRuleProfileUuid(dbSession, db.getDefaultOrganization().getUuid(), qprofile1.getRulesProfileUuid()))
+      .extracting(QProfileDto::getName)
+      .containsOnly(qprofile1.getName());
+
+    assertThat(underTest.selectByRuleProfileUuid(dbSession, "A", qprofile1.getRulesProfileUuid()))
+      .isNull();
+
+    assertThat(underTest.selectByRuleProfileUuid(dbSession, db.getDefaultOrganization().getUuid(), "A"))
+      .isNull();
+  }
+
+
+  @Test
+  public void selectDefaultBuiltInProfilesWithoutActiveRules() {
+    // a quality profile without active rules but not builtin
+    db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(false).setLanguage("java"));
+
+    // a built-in quality profile without active rules
+    QProfileDto javaQPWithoutActiveRules = db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(true).setLanguage("java"));
+    db.qualityProfiles().setAsDefault(javaQPWithoutActiveRules);
+
+    // a built-in quality profile without active rules
+    QProfileDto cppQPWithoutActiveRules = db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(true).setLanguage("cpp"));
+    db.qualityProfiles().setAsDefault(cppQPWithoutActiveRules);
+
+    // a built-in quality profile with active rules
+    QProfileDto builtInQPWithActiveRules = db.qualityProfiles().insert(db.getDefaultOrganization(), qp -> qp.setIsBuiltIn(true).setLanguage("java"));
+    RuleDefinitionDto ruleDefinitionDto = db.rules().insert();
+    db.qualityProfiles().activateRule(builtInQPWithActiveRules, ruleDefinitionDto);
+
+    dbSession.commit();
+
+    assertThat(underTest.selectDefaultBuiltInProfilesWithoutActiveRules(dbSession, Sets.newHashSet("java", "cpp")))
+      .extracting(QProfileDto::getName)
+      .containsOnly(javaQPWithoutActiveRules.getName(), cppQPWithoutActiveRules.getName());
+
+    assertThat(underTest.selectDefaultBuiltInProfilesWithoutActiveRules(dbSession, Sets.newHashSet("java")))
+      .extracting(QProfileDto::getName)
+      .containsOnly(javaQPWithoutActiveRules.getName());
+
+    assertThat(underTest.selectDefaultBuiltInProfilesWithoutActiveRules(dbSession, Sets.newHashSet("cobol")))
+      .isEmpty();
+
+    assertThat(underTest.selectDefaultBuiltInProfilesWithoutActiveRules(dbSession, Sets.newHashSet()))
+      .isEmpty();
+  }
+
   @Test
   public void selectDescendants_returns_empty_if_no_children() {
     QProfileDto base = db.qualityProfiles().insert(db.getDefaultOrganization());
diff --git a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
index 21c4012c7dd..6e7fb9a046c 100644
--- a/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
+++ b/server/sonar-server/src/main/java/org/sonar/server/qualityprofile/RegisterQualityProfiles.java
@@ -153,20 +153,25 @@ public class RegisterQualityProfiles {
    * @see <a href="https://jira.sonarsource.com/browse/SONAR-10363">SONAR-10363</a>
    */
   private void ensureBuiltInDefaultQPContainsRules(DbSession dbSession) {
-    Map<String, QProfileDto> qProfileByLanguage = dbClient.qualityProfileDao().selectBuiltInRuleProfilesWithActiveRules(dbSession).stream()
-      .collect(toMap(QProfileDto::getLanguage, Function.identity(), (oldValue, newValue) -> oldValue));
+    Map<String, RulesProfileDto> rulesProfilesByLanguage = dbClient.qualityProfileDao().selectBuiltInRuleProfilesWithActiveRules(dbSession).stream()
+      .collect(toMap(RulesProfileDto::getLanguage, Function.identity(), (oldValue, newValue) -> oldValue));
 
-    dbClient.qualityProfileDao().selectDefaultBuiltInProfilesWithoutActiveRules(dbSession)
+    dbClient.qualityProfileDao().selectDefaultBuiltInProfilesWithoutActiveRules(dbSession, rulesProfilesByLanguage.keySet())
       .forEach(qp -> {
-        QProfileDto qProfileDto = qProfileByLanguage.get(qp.getLanguage());
-        if (qProfileDto == null) {
+        RulesProfileDto rulesProfile = rulesProfilesByLanguage.get(qp.getLanguage());
+        if (rulesProfile == null) {
+          return;
+        }
+
+        QProfileDto qualityProfile = dbClient.qualityProfileDao().selectByRuleProfileUuid(dbSession, qp.getOrganizationUuid(), rulesProfile.getKee());
+        if (qualityProfile == null) {
           return;
         }
 
         Set<String> uuids = dbClient.defaultQProfileDao().selectExistingQProfileUuids(dbSession, qp.getOrganizationUuid(), Collections.singleton(qp.getKee()));
         dbClient.defaultQProfileDao().deleteByQProfileUuids(dbSession, uuids);
         dbClient.defaultQProfileDao().insertOrUpdate(dbSession, new DefaultQProfileDto()
-          .setQProfileUuid(qProfileDto.getKee())
+          .setQProfileUuid(qualityProfile.getKee())
           .setLanguage(qp.getLanguage())
           .setOrganizationUuid(qp.getOrganizationUuid())
         );
@@ -174,7 +179,7 @@ public class RegisterQualityProfiles {
         LOGGER.info("Default built-in quality profile for language [{}] has been updated from [{}] to [{}] since previous default does not have active rules.",
           qp.getLanguage(),
           qp.getName(),
-          qProfileDto.getName());
+          rulesProfile.getName());
       });
 
     dbSession.commit();
diff --git a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
index bd1f565991a..995d72d741a 100644
--- a/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
+++ b/server/sonar-server/src/test/java/org/sonar/server/qualityprofile/RegisterQualityProfilesTest.java
@@ -31,6 +31,7 @@ import org.sonar.api.utils.System2;
 import org.sonar.api.utils.internal.AlwaysIncreasingSystem2;
 import org.sonar.api.utils.log.LogTester;
 import org.sonar.api.utils.log.LoggerLevel;
+import org.sonar.core.util.UuidFactoryFast;
 import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
@@ -140,16 +141,20 @@ public class RegisterQualityProfilesTest {
 
   @Test
   public void update_default_built_in_quality_profile() {
+    String orgUuid = UuidFactoryFast.getInstance().create();
+
     RulesProfileDto ruleProfileWithoutRule = newRuleProfileDto(rp -> rp.setIsBuiltIn(true).setName("Sonar way").setLanguage(FOO_LANGUAGE.getKey()));
     RulesProfileDto ruleProfileWithOneRule = newRuleProfileDto(rp -> rp.setIsBuiltIn(true).setName("Sonar way 2").setLanguage(FOO_LANGUAGE.getKey()));
 
     QProfileDto qProfileWithoutRule = newQualityProfileDto()
       .setIsBuiltIn(true)
       .setLanguage(FOO_LANGUAGE.getKey())
+      .setOrganizationUuid(orgUuid)
       .setRulesProfileUuid(ruleProfileWithoutRule.getKee());
     QProfileDto qProfileWithOneRule = newQualityProfileDto()
       .setIsBuiltIn(true)
       .setLanguage(FOO_LANGUAGE.getKey())
+      .setOrganizationUuid(orgUuid)
       .setRulesProfileUuid(ruleProfileWithOneRule.getKee());
 
     db.qualityProfiles().insert(qProfileWithoutRule, qProfileWithOneRule);
@@ -169,15 +174,15 @@ public class RegisterQualityProfilesTest {
       format("Default built-in quality profile for language [foo] has been updated from [%s] to [%s] since previous default does not have active rules.",
         qProfileWithoutRule.getName(), qProfileWithOneRule.getName()));
 
-    assertThat(selectUuidOfDefaultProfile(db.getDefaultOrganization(), FOO_LANGUAGE.getKey()))
+    assertThat(selectUuidOfDefaultProfile(FOO_LANGUAGE.getKey()))
       .isPresent().get()
       .isEqualTo(qProfileWithOneRule.getKee());
   }
 
-  private Optional<String> selectUuidOfDefaultProfile(OrganizationDto org, String language) {
+  private Optional<String> selectUuidOfDefaultProfile(String language) {
     return db.select("select qprofile_uuid as \"profileUuid\" " +
       " from default_qprofiles " +
-      " where language='" + language + "'") // organization_uuid='" + org.getUuid() + "' and
+      " where language='" + language + "'")
       .stream()
       .findFirst()
       .map(m -> (String) m.get("profileUuid"));
-- 
2.39.5