SONAR-23512 Project report telemetry

6 files changed, 128 insertions, 3 deletions

diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java
index 2fbee908d06..ae521749cb7 100644
--- a/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java
+++ b/server/sonar-db-dao/src/it/java/org/sonar/db/report/ReportSubscriptionDaoIT.java
@@ -178,6 +178,30 @@ class ReportSubscriptionDaoIT {
     assertThat(underTest.countByQualifier(db.getSession(), "TRK")).isEqualTo(2);
   }
 
+  @Test
+  void countPerProject_shouldReturnCorrectValue() {
+    ProjectData projectData1 = db.components().insertPrivateProject(p -> p.setQualifier("TRK"));
+    ComponentDto mainBranch1 = projectData1.getMainBranchComponent();
+    ProjectData projectData2 = db.components().insertPrivateProject(p -> p.setQualifier("TRK"));
+    ComponentDto mainBranch2 = projectData2.getMainBranchComponent();
+
+    ComponentDto branch1 = db.components().insertProjectBranch(mainBranch1);
+    ComponentDto branch2 = db.components().insertProjectBranch(mainBranch2);
+
+    ReportSubscriptionDto subscriptionBranch1 = createSubscriptionDto("uuid2").setBranchUuid(branch1.branchUuid()).setUserUuid("userUuid2");
+    ReportSubscriptionDto subscriptionBranch2 = createSubscriptionDto("uuid3").setBranchUuid(branch2.branchUuid()).setUserUuid("userUuid3");
+    ReportSubscriptionDto subscriptionBranch3 = createSubscriptionDto("uuid4").setBranchUuid(branch2.branchUuid()).setUserUuid("userUuid4");
+    ReportSubscriptionDto subscriptionBranch4 = createSubscriptionDto("uuid").setPortfolioUuid("pf_uuid").setUserUuid("userUuid");
+
+    underTest.insert(db.getSession(), subscriptionBranch1);
+    underTest.insert(db.getSession(), subscriptionBranch2);
+    underTest.insert(db.getSession(), subscriptionBranch3);
+    underTest.insert(db.getSession(), subscriptionBranch4);
+    assertThat(underTest.countPerProject(db.getSession())).hasSize(2)
+      .containsEntry(projectData1.projectUuid(), 1)
+      .containsEntry(projectData2.projectUuid(), 2);
+  }
+
   @NotNull
   private static ReportSubscriptionDto createSubscriptionDto(String uuid) {
     return new ReportSubscriptionDto().setUuid(uuid);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java
index e55b2c24f9f..eb74eb6bc7c 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionDao.java
@@ -20,8 +20,10 @@
 package org.sonar.db.report;
 
 import java.util.List;
+import java.util.Map;
 import java.util.Optional;
 import java.util.Set;
+import java.util.stream.Collectors;
 import org.apache.ibatis.annotations.Param;
 import org.sonar.db.Dao;
 import org.sonar.db.DbSession;
@@ -65,4 +67,8 @@ public class ReportSubscriptionDao implements Dao {
   public int countByQualifier(DbSession dbSession, String qualifier) {
     return mapper(dbSession).countByQualifier(qualifier);
   }
+
+  public Map<String, Integer> countPerProject(DbSession dbSession) {
+    return mapper(dbSession).countPerProject().stream().collect(Collectors.toMap(SubscriptionCount::getProjectUuid, SubscriptionCount::getCount));
+  }
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java
index 15e79038d4f..d786764da01 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/ReportSubscriptionMapper.java
@@ -40,4 +40,6 @@ public interface ReportSubscriptionMapper {
   void delete(ReportSubscriptionDto subscriptionDto);
 
   int countByQualifier(String qualifier);
+
+  List<SubscriptionCount> countPerProject();
 }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/report/SubscriptionCount.java b/server/sonar-db-dao/src/main/java/org/sonar/db/report/SubscriptionCount.java
new file mode 100644
index 00000000000..2c5b74b766b
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/report/SubscriptionCount.java
@@ -0,0 +1,42 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2024 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.report;
+
+public class SubscriptionCount {
+  private String projectUuid;
+  private int count;
+
+  public SubscriptionCount() {
+    // used by MyBatis
+  }
+
+  public SubscriptionCount(String projectUuid, int count) {
+    this.projectUuid = projectUuid;
+    this.count = count;
+  }
+
+  public String getProjectUuid() {
+    return projectUuid;
+  }
+
+  public int getCount() {
+    return count;
+  }
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml
index 6afd40d7a04..e1a7081d178 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/report/ReportSubscriptionMapper.xml
@@ -65,10 +65,23 @@
     WHERE p.qualifier = #{qualifier,jdbcType=VARCHAR}
   </select>
 
-    <delete id="delete" parameterType="map">
+  <select id="countPerProject" resultType="org.sonar.db.report.SubscriptionCount">
+    SELECT
+      p.uuid   as projectUuid,
+      count(1) as count
+    FROM report_subscriptions rs
+    INNER JOIN project_branches pb
+      ON rs.branch_uuid = pb.uuid
+    INNER JOIN projects p
+      ON pb.project_uuid = p.uuid
+    WHERE p.qualifier = 'TRK'
+    GROUP BY p.uuid
+  </select>
+
+  <delete id="delete" parameterType="map">
     delete from report_subscriptions
-      where <include refid="filterConditions"/>
-    </delete>
+    where <include refid="filterConditions"/>
+  </delete>
 
   <sql id="filterConditions">
     user_uuid=#{userUuid,jdbcType=VARCHAR} and
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/report/SubscriptionCountTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/report/SubscriptionCountTest.java
new file mode 100644
index 00000000000..76e52c223fb
--- /dev/null
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/report/SubscriptionCountTest.java
@@ -0,0 +1,38 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2024 SonarSource SA
+ * mailto:info AT sonarsource DOT com
+ *
+ * This program is free software; you can redistribute it and/or
+ * modify it under the terms of the GNU Lesser General Public
+ * License as published by the Free Software Foundation; either
+ * version 3 of the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Lesser General Public License for more details.
+ *
+ * You should have received a copy of the GNU Lesser General Public License
+ * along with this program; if not, write to the Free Software Foundation,
+ * Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
+ */
+package org.sonar.db.report;
+
+import org.junit.jupiter.api.Test;
+
+import static org.assertj.core.api.Assertions.assertThat;
+
+class SubscriptionCountTest {
+
+  @Test
+  void testGetters() {
+    SubscriptionCount underTest = new SubscriptionCount("uuid", 42);
+    assertThat(underTest.getProjectUuid()).isEqualTo("uuid");
+    assertThat(underTest.getCount()).isEqualTo(42);
+    underTest = new SubscriptionCount();
+    assertThat(underTest.getProjectUuid()).isNull();
+    assertThat(underTest.getCount()).isZero();
+  }
+
+}