SONAR-11370 Add new SQL request to retrieve rules by languages and type

4 files changed, 109 insertions, 0 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleDao.java
index ebf033b8316..7b8fe189c2b 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleDao.java
@@ -141,6 +141,11 @@ public class RuleDao implements Dao {
     return mapper(session).selectAllDefinitions();
   }
 
+  public List<RuleDto> selectByTypeAndLanguages(DbSession session, String organizationUuid, List<Integer> types, List<String> languages) {
+    return ensureOrganizationIsSet(organizationUuid,
+      executeLargeInputs(languages, chunk -> mapper(session).selectByTypeAndLanguages(organizationUuid, types, chunk)));
+  }
+
   public List<RuleDto> selectByQuery(DbSession session, String organizationUuid, RuleQuery ruleQuery) {
     return ensureOrganizationIsSet(organizationUuid, mapper(session).selectByQuery(organizationUuid, ruleQuery));
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleMapper.java
index f6271b0e723..e3754a7d2a1 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/rule/RuleMapper.java
@@ -63,6 +63,8 @@ public interface RuleMapper {
 
   List<RuleDto> selectByQuery(@Param("organizationUuid") String organizationUuid, @Param("query") RuleQuery ruleQuery);
 
+  List<RuleDto> selectByTypeAndLanguages(@Param("organizationUuid") String organizationUuid, @Param("types") List<Integer> types, @Param("languages") List<String> languages);
+
   void insertDefinition(RuleDefinitionDto ruleDefinitionDto);
 
   void updateDefinition(RuleDefinitionDto ruleDefinitionDto);
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/rule/RuleMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/rule/RuleMapper.xml
index b0ed27b6d1e..60d92465953 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/rule/RuleMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/rule/RuleMapper.xml
@@ -296,6 +296,18 @@
       r.updated_at desc
   </select>
 
+  <select id="selectByTypeAndLanguages" parameterType="map" resultType="Rule">
+    select
+    <include refid="selectJoinedTablesColumns"/>
+    from
+    rules r
+    <include refid="outerJoinRulesMetadata"/>
+    where
+      r.status != 'REMOVED'
+      and r.rule_type in <foreach collection="types" item="type" separator="," open="(" close=")">#{type, jdbcType=INTEGER}</foreach>
+      and r.language in <foreach collection="languages" item="language" separator="," open="(" close=")">#{language, jdbcType=VARCHAR}</foreach>
+  </select>
+
   <insert id="insertDefinition" parameterType="org.sonar.db.rule.RuleDefinitionDto" keyColumn="id" useGeneratedKeys="true" keyProperty="id">
     insert into rules (
       plugin_key,
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/rule/RuleDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/rule/RuleDaoTest.java
index 399dda82f17..946ca37331f 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/rule/RuleDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/rule/RuleDaoTest.java
@@ -436,6 +436,96 @@ public class RuleDaoTest {
   }
 
   @Test
+  public void selectByTypeAndLanguages() {
+    OrganizationDto organization = db.organizations().insert();
+    OrganizationDto organization2 = db.organizations().insert();
+
+    RuleDefinitionDto rule1 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("java", "S001"))
+            .setConfigKey("S1")
+            .setType(RuleType.VULNERABILITY)
+            .setLanguage("java"));
+    db.rules().insertOrUpdateMetadata(rule1, organization);
+
+    RuleDefinitionDto rule2 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("js", "S002"))
+            .setType(RuleType.SECURITY_HOTSPOT)
+            .setLanguage("js"));
+    db.rules().insertOrUpdateMetadata(rule2, organization);
+
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.VULNERABILITY.getDbConstant()), singletonList("java")))
+      .extracting(RuleDto::getOrganizationUuid, RuleDto::getId, RuleDto::getLanguage, RuleDto::getType)
+      .containsExactly(tuple(organization.getUuid(), rule1.getId(), "java", RuleType.VULNERABILITY.getDbConstant()));
+
+    // Rule available also on organization2
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization2.getUuid(), singletonList(RuleType.VULNERABILITY.getDbConstant()), singletonList("java")))
+      .extracting(RuleDto::getOrganizationUuid, RuleDto::getId, RuleDto::getLanguage, RuleDto::getType)
+      .containsExactly(tuple(organization2.getUuid(), rule1.getId(), "java", RuleType.VULNERABILITY.getDbConstant()));
+
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.SECURITY_HOTSPOT.getDbConstant()), singletonList("js")))
+      .extracting(RuleDto::getOrganizationUuid, RuleDto::getId, RuleDto::getLanguage, RuleDto::getType)
+      .containsExactly(tuple(organization.getUuid(), rule2.getId(), "js", RuleType.SECURITY_HOTSPOT.getDbConstant()));
+
+    // Rule available also on organization2
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization2.getUuid(), singletonList(RuleType.SECURITY_HOTSPOT.getDbConstant()), singletonList("js")))
+      .extracting(RuleDto::getOrganizationUuid, RuleDto::getId, RuleDto::getLanguage, RuleDto::getType)
+      .containsExactly(tuple(organization2.getUuid(), rule2.getId(), "js", RuleType.SECURITY_HOTSPOT.getDbConstant()));
+
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.SECURITY_HOTSPOT.getDbConstant()), singletonList("java")))
+      .isEmpty();
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.VULNERABILITY.getDbConstant()), singletonList("js")))
+      .isEmpty();
+  }
+
+  @Test
+  public void selectByTypeAndLanguages_return_nothing_when_no_rule_on_languages() {
+    OrganizationDto organization = db.organizations().insert();
+
+    RuleDefinitionDto rule1 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("java", "S001"))
+        .setConfigKey("S1")
+        .setType(RuleType.VULNERABILITY)
+        .setLanguage("java"));
+    db.rules().insertOrUpdateMetadata(rule1, organization);
+
+    RuleDefinitionDto rule2 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("js", "S002"))
+        .setType(RuleType.VULNERABILITY)
+        .setLanguage("js"));
+    db.rules().insertOrUpdateMetadata(rule2, organization);
+
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.VULNERABILITY.getDbConstant()), singletonList("cpp")))
+      .isEmpty();
+  }
+
+  @Test
+  public void selectByTypeAndLanguages_return_nothing_when_no_rule_with_type() {
+    OrganizationDto organization = db.organizations().insert();
+
+    RuleDefinitionDto rule1 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("java", "S001"))
+        .setConfigKey("S1")
+        .setType(RuleType.VULNERABILITY)
+        .setLanguage("java"));
+    db.rules().insertOrUpdateMetadata(rule1, organization);
+
+    RuleDefinitionDto rule2 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("java", "S002"))
+        .setType(RuleType.SECURITY_HOTSPOT)
+        .setLanguage("java"));
+    db.rules().insertOrUpdateMetadata(rule2, organization);
+
+    RuleDefinitionDto rule3 = db.rules().insert(
+      r -> r.setKey(RuleKey.of("java", "S003"))
+        .setType(RuleType.CODE_SMELL)
+        .setLanguage("java"));
+    db.rules().insertOrUpdateMetadata(rule3, organization);
+
+    assertThat(underTest.selectByTypeAndLanguages(db.getSession(), organization.getUuid(), singletonList(RuleType.BUG.getDbConstant()), singletonList("java")))
+      .isEmpty();
+  }
+
+  @Test
   public void select_by_query() {
     OrganizationDto organization = db.organizations().insert();
     RuleDefinitionDto rule1 = db.rules().insert(r -> r.setKey(RuleKey.of("java", "S001")).setConfigKey("S1"));