SONAR-4301 Create queries to select all authorized projects for a user and to select issues id and project ids from issue query

10 files changed, 263 insertions, 0 deletions

diff --git a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
index fe8ba6b950e..fc3d571fb48 100644
--- a/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/issue/db/IssueDao.java
@@ -22,6 +22,8 @@ package org.sonar.core.issue.db;
 
 import com.google.common.annotations.VisibleForTesting;
 import com.google.common.collect.Lists;
+import org.apache.ibatis.session.ResultContext;
+import org.apache.ibatis.session.ResultHandler;
 import org.apache.ibatis.session.SqlSession;
 import org.sonar.api.BatchComponent;
 import org.sonar.api.ServerComponent;
@@ -43,6 +45,8 @@ import static com.google.common.collect.Maps.newHashMap;
  */
 public class IssueDao implements BatchComponent, ServerComponent {
 
+  private final static Integer MAX_RESULT = 10000;
+
   private final MyBatis mybatis;
 
   public IssueDao(MyBatis mybatis) {
@@ -99,6 +103,34 @@ public class IssueDao implements BatchComponent, ServerComponent {
   }
 
   @VisibleForTesting
+  List<IssueDto> selectIssueAndProjectIds(IssueQuery query, Integer maxResults) {
+    SqlSession session = mybatis.openSession();
+    try {
+      return selectIssueAndProjectIds(query, maxResults, session);
+    } finally {
+      MyBatis.closeQuietly(session);
+    }
+  }
+
+  /**
+   * The returned IssueDto list contains only the issue id and the project id
+   */
+  public List<IssueDto> selectIssueAndProjectIds(IssueQuery query, final Integer maxResults, SqlSession session) {
+    final List<IssueDto> issues = newArrayList();
+    ResultHandler resultHandler = new ResultHandler(){
+      @Override
+      public void handleResult(ResultContext context) {
+        issues.add((IssueDto) context.getResultObject());
+        if (issues.size() >= maxResults) {
+          context.stop();
+        }
+      }
+    };
+    session.select("selectIssueAndProjectIds", query, resultHandler);
+    return issues;
+  }
+
+  @VisibleForTesting
   Collection<IssueDto> selectByIds(Collection<Long> ids, IssueQuery.Sort sort, Boolean asc) {
     SqlSession session = mybatis.openSession();
     try {
diff --git a/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java b/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
index ca29c79c7b2..df3addad3ed 100644
--- a/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/user/AuthorizationDao.java
@@ -34,6 +34,7 @@ import java.util.Map;
 import java.util.Set;
 
 import static com.google.common.collect.Lists.newArrayList;
+import static com.google.common.collect.Maps.newHashMap;
 
 public class AuthorizationDao implements ServerComponent {
 
@@ -74,4 +75,29 @@ public class AuthorizationDao implements ServerComponent {
   public boolean isAuthorizedComponentId(int componentId, @Nullable Integer userId, String role) {
     return keepAuthorizedComponentIds(Sets.newHashSet(componentId), userId, role).size() == 1;
   }
+
+  public Set<Integer> selectAuthorizedRootProjectsIds(@Nullable Integer userId, String role) {
+    SqlSession session = mybatis.openSession();
+    try {
+      return selectAuthorizedRootProjectsIds(userId, role, session);
+
+    } finally {
+      MyBatis.closeQuietly(session);
+    }
+  }
+
+  public Set<Integer> selectAuthorizedRootProjectsIds(@Nullable Integer userId, String role, SqlSession session) {
+    String sql;
+    Map<String, Object> params = newHashMap();
+    if (userId == null) {
+      sql = "selectAuthorizedRootProjectsIdsForAnonymous";
+      params.put("role", role);
+    } else {
+      sql = "selectAuthorizedRootProjectsIdsForUser";
+      params.put("userId", userId);
+      params.put("role", role);
+    }
+
+    return Sets.newHashSet(session.<Integer>selectList(sql, params));
+  }
 }
diff --git a/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml b/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
index 4edb2f7a599..3023ba9b728 100644
--- a/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/issue/db/IssueMapper.xml
@@ -157,6 +157,11 @@
     <include refid="selectQueryConditions"/>
   </select>
 
+  <select id="selectIssueAndProjectIds" parameterType="map" resultType="Issue">
+    select i.id, i.resource_id as resourceId
+    <include refid="selectQueryConditions"/>
+  </select>
+
   <select id="select" parameterType="map" resultType="Issue">
     select
     <include refid="issueColumns"/>
diff --git a/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml b/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
index dd07daabd72..24bad8b195c 100644
--- a/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/user/AuthorizationMapper.xml
@@ -45,4 +45,30 @@
         </foreach>
   </select>
 
+  <select id="selectAuthorizedRootProjectsIdsForUser" parameterType="map" resultType="int">
+    SELECT s.root_project_id
+    FROM group_roles gr, snapshots s
+    WHERE
+    gr.role=#{role}
+    and (gr.group_id is null or gr.group_id in (select gu.group_id from groups_users gu where gu.user_id=#{userId}))
+    and gr.resource_id = s.root_project_id
+    and s.islast = ${_true}
+    UNION
+    SELECT s.root_project_id
+    FROM user_roles ur, snapshots s
+    WHERE
+    ur.role=#{role}
+    and ur.user_id=#{userId} and s.project_id=ur.resource_id
+    and s.islast = ${_true}
+  </select>
+
+  <select id="selectAuthorizedRootProjectsIdsForAnonymous" parameterType="map" resultType="int">
+    SELECT s.root_project_id
+    FROM group_roles gr, snapshots s
+    WHERE
+    gr.role=#{role}
+    and gr.group_id is null
+    and gr.resource_id = s.root_project_id
+  </select>
+
 </mapper>
diff --git a/sonar-core/src/test/java/org/sonar/core/issue/db/IssueDaoTest.java b/sonar-core/src/test/java/org/sonar/core/issue/db/IssueDaoTest.java
index fe83068bf2e..8ed5fdc2384 100644
--- a/sonar-core/src/test/java/org/sonar/core/issue/db/IssueDaoTest.java
+++ b/sonar-core/src/test/java/org/sonar/core/issue/db/IssueDaoTest.java
@@ -302,6 +302,18 @@ public class IssueDaoTest extends AbstractDaoTestCase {
   }
 
   @Test
+  public void should_select_issue_and_project_ids() {
+    setupData("shared", "should_select_issue_and_project_ids");
+
+    IssueQuery query = IssueQuery.builder().build();
+    List<IssueDto> results = dao.selectIssueAndProjectIds(query, 5);
+    assertThat(results).hasSize(3);
+
+    results = dao.selectIssueAndProjectIds(query, 2);
+    assertThat(results).hasSize(2);
+  }
+
+  @Test
   public void should_select_open_issues() {
     setupData("shared", "should_select_open_issues");
 
diff --git a/sonar-core/src/test/java/org/sonar/core/user/AuthorizationDaoTest.java b/sonar-core/src/test/java/org/sonar/core/user/AuthorizationDaoTest.java
index ce9c8e2d224..6f9465d4b47 100644
--- a/sonar-core/src/test/java/org/sonar/core/user/AuthorizationDaoTest.java
+++ b/sonar-core/src/test/java/org/sonar/core/user/AuthorizationDaoTest.java
@@ -106,4 +106,47 @@ public class AuthorizationDaoTest extends AbstractDaoTestCase {
       null, "admin");
     assertThat(componentIds).isEmpty();
   }
+
+  @Test
+  public void should_return_root_project_ids_for_user() {
+    setupData("should_return_root_project_ids_for_user");
+
+    AuthorizationDao authorization = new AuthorizationDao(getMyBatis());
+    Set<Integer> rootProjectIds = authorization.selectAuthorizedRootProjectsIds(USER, "user");
+
+    assertThat(rootProjectIds).containsOnly(PROJECT);
+
+    // user does not have the role "admin"
+    rootProjectIds = authorization.selectAuthorizedRootProjectsIds(USER, "admin");
+    assertThat(rootProjectIds).isEmpty();
+  }
+
+  @Test
+  public void should_return_root_project_ids_for_group() {
+    // but user is not in an authorized group
+    setupData("should_return_root_project_ids_for_group");
+
+    AuthorizationDao authorization = new AuthorizationDao(getMyBatis());
+    Set<Integer> rootProjectIds = authorization.selectAuthorizedRootProjectsIds(USER, "user");
+
+    assertThat(rootProjectIds).containsOnly(PROJECT);
+
+    // user does not have the role "admin"
+    rootProjectIds = authorization.selectAuthorizedRootProjectsIds(USER, "admin");
+    assertThat(rootProjectIds).isEmpty();
+  }
+
+  @Test
+  public void should_return_root_project_ids_for_anonymous() {
+    setupData("should_return_root_project_ids_for_anonymous");
+
+    AuthorizationDao authorization = new AuthorizationDao(getMyBatis());
+    Set<Integer> rootProjectIds = authorization.selectAuthorizedRootProjectsIds(null, "user");
+
+    assertThat(rootProjectIds).containsOnly(PROJECT);
+
+    // group does not have the role "admin"
+    rootProjectIds = authorization.selectAuthorizedRootProjectsIds(null, "admin");
+    assertThat(rootProjectIds).isEmpty();
+  }
 }
diff --git a/sonar-core/src/test/resources/org/sonar/core/issue/db/IssueDaoTest/should_select_issue_and_project_ids.xml b/sonar-core/src/test/resources/org/sonar/core/issue/db/IssueDaoTest/should_select_issue_and_project_ids.xml
new file mode 100644
index 00000000000..3d7bd96af21
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/issue/db/IssueDaoTest/should_select_issue_and_project_ids.xml
@@ -0,0 +1,77 @@
+<dataset>
+
+  <!-- rule 500 -->
+  <issues
+      id="100"
+      kee="ABCDE-1"
+      resource_id="401"
+      rule_id="500"
+      severity="BLOCKER"
+      manual_severity="[false]"
+      message="[null]"
+      line="200"
+      effort_to_fix="4.2"
+      status="OPEN"
+      resolution="FIXED"
+      checksum="XXX"
+      reporter="arthur"
+      assignee="perceval"
+      author_login="[null]"
+      attributes="JIRA=FOO-1234"
+      issue_creation_date="2013-04-16"
+      issue_update_date="2013-04-16"
+      issue_close_date="2013-04-16"
+      created_at="2013-04-16"
+      updated_at="2013-04-16"
+      />
+
+  <issues
+      id="101"
+      kee="ABCDE-2"
+      resource_id="401"
+      rule_id="500"
+      severity="BLOCKER"
+      manual_severity="[false]"
+      message="[null]"
+      line="200"
+      effort_to_fix="4.2"
+      status="OPEN"
+      resolution="FIXED"
+      checksum="XXX"
+      reporter="arthur"
+      assignee="perceval"
+      author_login="[null]"
+      attributes="JIRA=FOO-1234"
+      issue_creation_date="2013-04-16"
+      issue_update_date="2013-04-16"
+      issue_close_date="2013-04-16"
+      created_at="2013-04-16"
+      updated_at="2013-04-16"
+      />
+
+
+  <!-- rule 501 -->
+  <issues
+      id="102"
+      kee="ABCDE-3"
+      resource_id="401"
+      rule_id="501"
+      severity="BLOCKER"
+      manual_severity="[false]"
+      message="[null]"
+      line="200"
+      effort_to_fix="4.2"
+      status="OPEN"
+      resolution="FIXED"
+      checksum="XXX"
+      reporter="arthur"
+      assignee="perceval"
+      author_login="[null]"
+      attributes="JIRA=FOO-1234"
+      issue_creation_date="2013-04-16"
+      issue_update_date="2013-04-16"
+      issue_close_date="2013-04-16"
+      created_at="2013-04-16"
+      updated_at="2013-04-16"
+      />
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_anonymous.xml b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_anonymous.xml
new file mode 100644
index 00000000000..a474a8ad168
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_anonymous.xml
@@ -0,0 +1,13 @@
+<dataset>
+
+  <user_roles id="1" user_id="100" resource_id="999" role="user"/>
+  <groups_users user_id="100" group_id="200"/>
+  <group_roles id="1" group_id="[null]" resource_id="300" role="user"/>
+
+  <snapshots id="1" project_id="300" root_project_id="300" islast="[true]"/>
+  <snapshots id="2" project_id="301" root_project_id="300" islast="[true]"/>
+  <snapshots id="3" project_id="302" root_project_id="300" islast="[true]"/>
+
+  <snapshots id="4" project_id="303" root_project_id="301" islast="[true]"/>
+
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_group.xml b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_group.xml
new file mode 100644
index 00000000000..73a034687d7
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_group.xml
@@ -0,0 +1,15 @@
+<dataset>
+
+  <!-- user 100 has no direct grant access, but is in the group 200 that has the role "user"
+  on the project 300  -->
+  <user_roles id="1" user_id="100" resource_id="999" role="user"/>
+  <groups_users user_id="100" group_id="200"/>
+  <group_roles id="1" group_id="200" resource_id="300" role="user"/>
+
+  <snapshots id="1" project_id="300" root_project_id="300" islast="[true]"/>
+  <snapshots id="2" project_id="301" root_project_id="300" islast="[true]"/>
+  <snapshots id="3" project_id="302" root_project_id="300" islast="[true]"/>
+
+  <snapshots id="4" project_id="303" root_project_id="301" islast="[true]"/>
+
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_user.xml b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_user.xml
new file mode 100644
index 00000000000..036563f4f9d
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/user/AuthorizationDaoTest/should_return_root_project_ids_for_user.xml
@@ -0,0 +1,14 @@
+<dataset>
+
+  <!-- user 100 has the role "user" on the project 300 and in group 200 -->
+  <user_roles id="1" user_id="100" resource_id="300" role="user"/>
+  <groups_users user_id="100" group_id="200"/>
+  <group_roles id="1" group_id="200" resource_id="999" role="user"/>
+
+  <snapshots id="1" project_id="300" root_project_id="300" islast="[true]"/>
+  <snapshots id="2" project_id="301" root_project_id="300" islast="[true]"/>
+  <snapshots id="3" project_id="302" root_project_id="300" islast="[true]"/>
+
+  <snapshots id="4" project_id="303" root_project_id="301" islast="[true]"/>
+
+</dataset>