SONAR-11560 fix api/permissions/users sorting

6 files changed, 94 insertions, 1 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/PermissionQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/PermissionQuery.java
index 4e346ffef52..8eaab6b2b18 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/PermissionQuery.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/PermissionQuery.java
@@ -48,6 +48,7 @@ public class PermissionQuery {
   private final String permission;
   // filter on project, else filter org permissions
   private final String componentUuid;
+  private final Long componentId;
   private final String template;
 
   // filter on login, email or name of users or groups
@@ -67,6 +68,7 @@ public class PermissionQuery {
     this.permission = builder.permission;
     this.withAtLeastOnePermission = builder.withAtLeastOnePermission;
     this.componentUuid = builder.componentUuid;
+    this.componentId = builder.componentId;
     this.template = builder.template;
     this.searchQuery = builder.searchQuery;
     this.searchQueryToSql = builder.searchQuery == null ? null : buildLikeValue(builder.searchQuery, WildcardPosition.BEFORE_AND_AFTER);
@@ -100,6 +102,11 @@ public class PermissionQuery {
   }
 
   @CheckForNull
+  public Long getComponentId() {
+    return componentId;
+  }
+
+  @CheckForNull
   public String getSearchQuery() {
     return searchQuery;
   }
@@ -130,6 +137,7 @@ public class PermissionQuery {
     private String permission;
     private String organizationUuid;
     private String componentUuid;
+    private Long componentId;
     private String template;
     private String searchQuery;
     private boolean withAtLeastOnePermission;
@@ -157,6 +165,11 @@ public class PermissionQuery {
       return this;
     }
 
+    public Builder setComponentId(Long componentId) {
+      this.componentId = componentId;
+      return this;
+    }
+
     public Builder setOrganizationUuid(String organizationUuid) {
       this.organizationUuid = organizationUuid;
       return this;
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
index c4556579a95..f29f4ba3f97 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionDao.java
@@ -51,7 +51,16 @@ public class UserPermissionDao implements Dao {
   }
 
   public List<Integer> selectUserIdsByQuery(DbSession dbSession, PermissionQuery query) {
-    return mapper(dbSession).selectUserIdsByQuery(query)
+    return paginate(mapper(dbSession).selectUserIdsByQuery(query), query);
+  }
+
+  public List<Integer> selectUserIdsByQueryAndScope(DbSession dbSession, PermissionQuery query) {
+    return paginate(mapper(dbSession).selectUserIdsByQueryAndScope(query), query);
+  }
+
+
+  private static List<Integer> paginate(List<Integer> results, PermissionQuery query) {
+    return results
       .stream()
       // Pagination is done in Java because it's too complex to use SQL pagination in Oracle and MsSQL with the distinct
       .skip(query.getPageOffset())
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
index 9156aeb6a5a..164673412d9 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/permission/UserPermissionMapper.java
@@ -31,6 +31,11 @@ public interface UserPermissionMapper {
   List<Integer> selectUserIdsByQuery(@Param("query") PermissionQuery query);
 
   /**
+   * Fetch user ids based on permission query and only in a specific scope (global permissions only, organization permissions only or project permissions only)
+   */
+  List<Integer> selectUserIdsByQueryAndScope(@Param("query") PermissionQuery query);
+
+  /**
    * Count the number of distinct users returned by {@link #selectUserIdsByQuery(PermissionQuery)}
    * {@link PermissionQuery#getPageOffset()} and {@link PermissionQuery#getPageSize()} are ignored.
    */
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
index 62453effbc5..71d6b929116 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/permission/UserPermissionMapper.xml
@@ -27,6 +27,28 @@
     order by case when (count(ur.role) > 0) then 1 else 2 end asc, lower(u.name) asc
   </select>
 
+  <select id="selectUserIdsByQueryAndScope" parameterType="map" resultType="int">
+    select
+    u.id, lower(u.name)
+    from users u
+    left join user_roles ur on ur.user_id = u.id and ur.organization_uuid=#{query.organizationUuid,jdbcType=VARCHAR}
+    <choose>
+      <when test="query.componentId == null">
+        and ur.resource_id is null
+      </when>
+      <otherwise>
+        and ur.resource_id = #{query.componentId,jdbcType=BIGINT}
+      </otherwise>
+    </choose>
+    left join projects p on ur.resource_id = p.id
+    inner join organization_members om on u.id=om.user_id and om.organization_uuid=#{query.organizationUuid,jdbcType=VARCHAR}
+    <where>
+      <include refid="sqlQueryFilters" />
+    </where>
+    group by u.id, lower(u.name)
+    order by case when (count(distinct ur.role) > 0) then 1 else 2 end asc, lower(u.name) asc
+  </select>
+
   <select id="countUsersByQuery" parameterType="map" resultType="int">
     select count(distinct(u.id))
     <include refid="sqlQueryJoins" />
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/PermissionQueryTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/PermissionQueryTest.java
index 56f574d34a7..e0c15477d03 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/PermissionQueryTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/PermissionQueryTest.java
@@ -34,12 +34,14 @@ public class PermissionQueryTest {
   public void create_query() {
     PermissionQuery quey = PermissionQuery.builder()
       .setComponentUuid("COMPONENT_UUID")
+      .setComponentId(1234L)
       .setOrganizationUuid("ORGANIZATION_UUID")
       .setPermission("user")
       .setSearchQuery("sonar")
       .build();
 
     assertThat(quey.getComponentUuid()).isEqualTo("COMPONENT_UUID");
+    assertThat(quey.getComponentId()).isEqualTo(1234L);
     assertThat(quey.getOrganizationUuid()).isEqualTo("ORGANIZATION_UUID");
     assertThat(quey.getPermission()).isEqualTo("user");
     assertThat(quey.getSearchQuery()).isEqualTo("sonar");
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
index 9e20261b533..21538a36575 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/permission/UserPermissionDaoTest.java
@@ -261,6 +261,48 @@ public class UserPermissionDaoTest {
   }
 
   @Test
+  public void selectUserIdsByQueryAndScope_with_organization_scope() {
+    OrganizationDto org1 = db.organizations().insert();
+    OrganizationDto org2 = db.organizations().insert();
+    UserDto user1 = insertUser(u -> u.setLogin("login1").setName("Marius").setEmail("email1@email.com"), org1, org2);
+    UserDto user2 = insertUser(u -> u.setLogin("login2").setName("Marie").setEmail("email2@email.com"), org1, org2);
+    ComponentDto project1 = db.components().insertPrivateProject(org1);
+    ComponentDto project2 = db.components().insertPrivateProject(org2);
+    addProjectPermission(org1, USER, user1, project1);
+    addGlobalPermission(org1, PROVISIONING, user1);
+    addProjectPermission(org2, ISSUE_ADMIN, user2, project2);
+    PermissionQuery query = PermissionQuery.builder().setOrganizationUuid(org1.getUuid()).build();
+
+    List<Integer> result = underTest.selectUserIdsByQueryAndScope(dbSession, query);
+
+    // users with any kind of global permissions are first on the list and then sorted by name
+    assertThat(result).containsExactly(user1.getId(), user2.getId());
+  }
+
+  @Test
+  public void selectUserIdsByQueryAndScope_with_project_scope() {
+    OrganizationDto org1 = db.organizations().insert();
+    OrganizationDto org2 = db.organizations().insert();
+    UserDto user1 = insertUser(u -> u.setLogin("login1").setName("Marius").setEmail("email1@email.com"), org1, org2);
+    UserDto user2 = insertUser(u -> u.setLogin("login2").setName("Marie").setEmail("email2@email.com"), org1, org2);
+    ComponentDto project1 = db.components().insertPrivateProject(org1);
+    ComponentDto project2 = db.components().insertPrivateProject(org2);
+    addProjectPermission(org1, USER, user1, project1);
+    addGlobalPermission(org1, PROVISIONING, user1);
+    addProjectPermission(org2, ISSUE_ADMIN, user2, project2);
+    PermissionQuery query = PermissionQuery.builder()
+      .setOrganizationUuid(org1.getUuid())
+      .setComponentUuid(project1.uuid())
+      .setComponentId(project1.getId())
+      .build();
+
+    List<Integer> result = underTest.selectUserIdsByQueryAndScope(dbSession, query);
+
+    // users with any this projects permissions are first on the list and then sorted by name
+    assertThat(result).containsExactly(user1.getId(), user2.getId());
+  }
+
+  @Test
   public void selectUserIdsByQuery_is_paginated() {
     OrganizationDto organization = db.organizations().insert();
     List<Integer> userIds = new ArrayList<>();