SONAR-1330 Search for user allowed to edit single quality profile

6 files changed, 401 insertions, 1 deletions

diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersDao.java
index b173b067332..2cab9a929aa 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersDao.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersDao.java
@@ -19,9 +19,11 @@
  */
 package org.sonar.db.qualityprofile;
 
+import java.util.List;
 import org.sonar.api.utils.System2;
 import org.sonar.db.Dao;
 import org.sonar.db.DbSession;
+import org.sonar.db.Pagination;
 import org.sonar.db.user.UserDto;
 
 public class QProfileEditUsersDao implements Dao {
@@ -36,6 +38,14 @@ public class QProfileEditUsersDao implements Dao {
     return mapper(dbSession).selectByQProfileAndUser(profile.getKee(), user.getId()) != null;
   }
 
+  public int countByQuery(DbSession dbSession, SearchUsersQuery query){
+    return mapper(dbSession).countByQuery(query);
+  }
+
+  public List<UserMembershipDto> selectByQuery(DbSession dbSession, SearchUsersQuery query, Pagination pagination){
+    return mapper(dbSession).selectByQuery(query, pagination);
+  }
+
   public void insert(DbSession dbSession, QProfileEditUsersDto dto) {
     mapper(dbSession).insert(dto, system2.now());
   }
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersMapper.java
index 601b03791f8..70694c3307f 100644
--- a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersMapper.java
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/QProfileEditUsersMapper.java
@@ -19,12 +19,18 @@
  */
 package org.sonar.db.qualityprofile;
 
+import java.util.List;
 import org.apache.ibatis.annotations.Param;
+import org.sonar.db.Pagination;
 
 public interface QProfileEditUsersMapper {
 
   QProfileEditUsersDto selectByQProfileAndUser(@Param("qProfileUuid") String qProfileUuid, @Param("userId") int userId);
 
+  int countByQuery(@Param("query") SearchUsersQuery query);
+
+  List<UserMembershipDto> selectByQuery(@Param("query") SearchUsersQuery query, @Param("pagination") Pagination pagination);
+
   void insert(@Param("dto") QProfileEditUsersDto dto, @Param("now") long now);
 
   void delete(@Param("qProfileUuid") String qProfileUuid, @Param("userId") int userId);
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/SearchUsersQuery.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/SearchUsersQuery.java
new file mode 100644
index 00000000000..cdb6077c600
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/SearchUsersQuery.java
@@ -0,0 +1,124 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 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.qualityprofile;
+
+import com.google.common.collect.ImmutableSet;
+import java.util.Locale;
+import java.util.Set;
+import javax.annotation.CheckForNull;
+import javax.annotation.Nullable;
+import org.apache.commons.lang.StringUtils;
+import org.sonar.db.organization.OrganizationDto;
+
+import static com.google.common.base.MoreObjects.firstNonNull;
+import static com.google.common.base.Preconditions.checkArgument;
+import static java.util.Objects.requireNonNull;
+import static org.sonar.db.DaoDatabaseUtils.buildLikeValue;
+import static org.sonar.db.WildcardPosition.BEFORE_AND_AFTER;
+
+public class SearchUsersQuery {
+
+  public static final String ANY = "ANY";
+  public static final String IN = "IN";
+  public static final String OUT = "OUT";
+  public static final Set<String> AVAILABLE_MEMBERSHIPS = ImmutableSet.of(ANY, IN, OUT);
+
+  private final String organizationUuid;
+  private final String qProfileUuid;
+  private final String query;
+  private final String membership;
+
+  // for internal use in MyBatis
+  final String querySql;
+  final String querySqlLowercase;
+
+  private SearchUsersQuery(Builder builder) {
+    this.organizationUuid = builder.organization.getUuid();
+    this.qProfileUuid = builder.profile.getKee();
+    this.query = builder.query;
+    this.membership = builder.membership;
+    this.querySql = query == null ? null : buildLikeValue(query, BEFORE_AND_AFTER);
+    this.querySqlLowercase = querySql == null ? null : querySql.toLowerCase(Locale.ENGLISH);
+  }
+
+  public String getOrganizationUuid() {
+    return organizationUuid;
+  }
+
+  public String getQProfileUuid() {
+    return qProfileUuid;
+  }
+
+  public String getMembership() {
+    return membership;
+  }
+
+  @CheckForNull
+  public String getQuery() {
+    return query;
+  }
+
+  public static Builder builder() {
+    return new Builder();
+  }
+
+  public static class Builder {
+    private OrganizationDto organization;
+    private QProfileDto profile;
+    private String query;
+    private String membership;
+
+    private Builder() {
+    }
+
+    public Builder setOrganization(OrganizationDto organization) {
+      this.organization = organization;
+      return this;
+    }
+
+    public Builder setProfile(QProfileDto profile) {
+      this.profile = profile;
+      return this;
+    }
+
+    public Builder setMembership(@Nullable String membership) {
+      this.membership = membership;
+      return this;
+    }
+
+    public Builder setQuery(@Nullable String s) {
+      this.query = StringUtils.defaultIfBlank(s, null);
+      return this;
+    }
+
+    private void initMembership() {
+      membership = firstNonNull(membership, ANY);
+      checkArgument(AVAILABLE_MEMBERSHIPS.contains(membership),
+        "Membership is not valid (got " + membership + "). Availables values are " + AVAILABLE_MEMBERSHIPS);
+    }
+
+    public SearchUsersQuery build() {
+      requireNonNull(organization, "Organization cannot be null");
+      requireNonNull(profile, "Quality profile cant be null.");
+      initMembership();
+      return new SearchUsersQuery(this);
+    }
+  }
+}
diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java
new file mode 100644
index 00000000000..7c301457135
--- /dev/null
+++ b/server/sonar-db-dao/src/main/java/org/sonar/db/qualityprofile/UserMembershipDto.java
@@ -0,0 +1,41 @@
+/*
+ * SonarQube
+ * Copyright (C) 2009-2017 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.qualityprofile;
+
+public class UserMembershipDto {
+
+  private int userId;
+  // Set by MyBatis
+  private String uuid;
+
+  public int getUserId() {
+    return userId;
+  }
+
+  public UserMembershipDto setUserId(int userId) {
+    this.userId = userId;
+    return this;
+  }
+
+  public boolean isSelected() {
+    return uuid != null;
+  }
+
+}
diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QProfileEditUsersMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QProfileEditUsersMapper.xml
index a56278ea9d8..8bf84485bc4 100644
--- a/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QProfileEditUsersMapper.xml
+++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/qualityprofile/QProfileEditUsersMapper.xml
@@ -18,12 +18,70 @@
     and qeu.qprofile_uuid = #{qProfileUuid, jdbcType=VARCHAR}
   </select>
 
+  <select id="countByQuery" resultType="int">
+    select count(u.id)
+    <include refid="sqlSelectByQuery" />
+  </select>
+
+  <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.qualityprofile.UserMembershipDto">
+    SELECT u.id as userId, u.name as name, qeu.uuid as uuid
+    <include refid="sqlSelectByQuery"/>
+    ORDER BY u.name ASC
+    LIMIT #{pagination.pageSize,jdbcType=INTEGER}
+    OFFSET #{pagination.offset,jdbcType=INTEGER}
+  </select>
+
+  <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.qualityprofile.UserMembershipDto" databaseId="mssql">
+    select * from (
+    select row_number() over(order by u.name asc) as number,
+      u.id as userId, u.name as name, qeu.uuid as uuid
+      <include refid="sqlSelectByQuery" />
+    ) as query
+    where
+    query.number between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+    order by query.name asc
+  </select>
+
+  <select id="selectByQuery" parameterType="map" resultType="org.sonar.db.qualityprofile.UserMembershipDto" databaseId="oracle">
+    select * from (
+      select rownum as rn, t.* from (
+        select u.id as userId, u.name as name, qeu.uuid as uuid
+        <include refid="sqlSelectByQuery"/>
+        order by u.name ASC
+      ) t
+    ) t
+    where
+    t.rn between #{pagination.startRowNumber,jdbcType=INTEGER} and #{pagination.endRowNumber,jdbcType=INTEGER}
+  </select>
+
+  <sql id="sqlSelectByQuery">
+    FROM users u
+    LEFT JOIN qprofile_edit_users qeu ON qeu.user_id=u.id AND qeu.qprofile_uuid=#{query.qProfileUuid, jdbcType=VARCHAR}
+    INNER JOIN organization_members om ON u.id=om.user_id AND om.organization_uuid=#{query.organizationUuid, jdbcType=VARCHAR}
+    <where>
+      <choose>
+        <when test="query.getMembership() == 'IN'">
+          AND qeu.uuid IS NOT NULL
+        </when>
+        <when test="query.getMembership() == 'OUT'">
+          AND qeu.uuid IS NULL
+        </when>
+      </choose>
+      <if test="query.getQuery() != null">
+        AND (
+        lower(u.name) like #{query.querySqlLowercase} ESCAPE '/'
+        or u.login like #{query.querySql} ESCAPE '/')
+      </if>
+      AND u.active=${_true}
+    </where>
+  </sql>
+
   <insert id="insert" useGeneratedKeys="false" parameterType="map">
     insert into qprofile_edit_users(
     uuid,
     user_id,
     qprofile_uuid,
-    created_at,
+    created_at
     ) values (
     #{dto.uuid, jdbcType=VARCHAR},
     #{dto.userId, jdbcType=INTEGER},
diff --git a/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QProfileEditUsersDaoTest.java b/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QProfileEditUsersDaoTest.java
index 5f0992e9a8e..e0a90e7cbb4 100644
--- a/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QProfileEditUsersDaoTest.java
+++ b/server/sonar-db-dao/src/test/java/org/sonar/db/qualityprofile/QProfileEditUsersDaoTest.java
@@ -24,11 +24,17 @@ import org.junit.Test;
 import org.sonar.api.utils.System2;
 import org.sonar.api.utils.internal.TestSystem2;
 import org.sonar.db.DbTester;
+import org.sonar.db.Pagination;
 import org.sonar.db.organization.OrganizationDto;
 import org.sonar.db.user.UserDto;
 
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.core.api.Assertions.entry;
+import static org.assertj.core.api.Assertions.tuple;
+import static org.sonar.db.qualityprofile.SearchUsersQuery.ANY;
+import static org.sonar.db.qualityprofile.SearchUsersQuery.IN;
+import static org.sonar.db.qualityprofile.SearchUsersQuery.OUT;
+import static org.sonar.db.qualityprofile.SearchUsersQuery.builder;
 
 public class QProfileEditUsersDaoTest {
 
@@ -57,6 +63,161 @@ public class QProfileEditUsersDaoTest {
   }
 
   @Test
+  public void countByQuery() {
+    OrganizationDto organization = db.organizations().insert();
+    QProfileDto profile = db.qualityProfiles().insert(organization);
+    UserDto user1 = db.users().insertUser();
+    UserDto user2 = db.users().insertUser();
+    UserDto user3 = db.users().insertUser();
+    db.organizations().addMember(organization, user1);
+    db.organizations().addMember(organization, user2);
+    db.organizations().addMember(organization, user3);
+    db.qualityProfiles().addUserPermission(profile, user1);
+    db.qualityProfiles().addUserPermission(profile, user2);
+
+    assertThat(underTest.countByQuery(db.getSession(), builder()
+      .setOrganization(organization)
+      .setProfile(profile)
+      .setMembership(ANY).build()))
+      .isEqualTo(3);
+
+    assertThat(underTest.countByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(IN).build()))
+      .isEqualTo(2);
+
+    assertThat(underTest.countByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(OUT).build()))
+      .isEqualTo(1);
+  }
+
+  @Test
+  public void selectByQuery() {
+    OrganizationDto organization = db.organizations().insert();
+    QProfileDto profile = db.qualityProfiles().insert(organization);
+    UserDto user1 = db.users().insertUser();
+    UserDto user2 = db.users().insertUser();
+    UserDto user3 = db.users().insertUser();
+    db.organizations().addMember(organization, user1);
+    db.organizations().addMember(organization, user2);
+    db.organizations().addMember(organization, user3);
+    db.qualityProfiles().addUserPermission(profile, user1);
+    db.qualityProfiles().addUserPermission(profile, user2);
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+      .setOrganization(organization)
+      .setProfile(profile)
+      .setMembership(ANY).build(), Pagination.all()))
+      .extracting(UserMembershipDto::getUserId, UserMembershipDto::isSelected)
+      .containsExactlyInAnyOrder(
+        tuple(user1.getId(), true),
+        tuple(user2.getId(), true),
+        tuple(user3.getId(), false));
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(IN).build(),
+      Pagination.all()))
+      .extracting(UserMembershipDto::getUserId, UserMembershipDto::isSelected)
+      .containsExactlyInAnyOrder(tuple(user1.getId(), true), tuple(user2.getId(), true));
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(OUT).build(),
+      Pagination.all()))
+      .extracting(UserMembershipDto::getUserId, UserMembershipDto::isSelected)
+      .containsExactlyInAnyOrder(tuple(user3.getId(), false));
+  }
+
+  @Test
+  public void selectByQuery_search_by_name_or_login() {
+    OrganizationDto organization = db.organizations().insert();
+    QProfileDto profile = db.qualityProfiles().insert(organization);
+    UserDto user1 = db.users().insertUser(u -> u.setLogin("user1").setName("John Doe"));
+    UserDto user2 = db.users().insertUser(u -> u.setLogin("user2").setName("John Smith"));
+    UserDto user3 = db.users().insertUser(u -> u.setLogin("user3").setName("Jane Doe"));
+    db.organizations().addMember(organization, user1);
+    db.organizations().addMember(organization, user2);
+    db.organizations().addMember(organization, user3);
+    db.qualityProfiles().addUserPermission(profile, user1);
+    db.qualityProfiles().addUserPermission(profile, user2);
+    db.qualityProfiles().addUserPermission(profile, user3);
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(IN)
+        .setQuery("user2").build(),
+      Pagination.all()))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactlyInAnyOrder(user2.getId());
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(IN)
+        .setQuery("joh").build(),
+      Pagination.all()))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactlyInAnyOrder(user1.getId(), user2.getId());
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(IN)
+        .setQuery("Doe").build(),
+      Pagination.all()))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactlyInAnyOrder(user1.getId(), user3.getId());
+  }
+
+  @Test
+  public void selectByQuery_with_paging() {
+    OrganizationDto organization = db.organizations().insert();
+    QProfileDto profile = db.qualityProfiles().insert(organization);
+    UserDto user1 = db.users().insertUser(u -> u.setName("user1"));
+    UserDto user2 = db.users().insertUser(u -> u.setName("user2"));
+    UserDto user3 = db.users().insertUser(u -> u.setName("user3"));
+    db.organizations().addMember(organization, user1);
+    db.organizations().addMember(organization, user2);
+    db.organizations().addMember(organization, user3);
+    db.qualityProfiles().addUserPermission(profile, user1);
+    db.qualityProfiles().addUserPermission(profile, user2);
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(ANY)
+        .build(),
+      Pagination.forPage(1).andSize(1)))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactly(user1.getId());
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(ANY)
+        .build(),
+      Pagination.forPage(3).andSize(1)))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactly(user3.getId());
+
+    assertThat(underTest.selectByQuery(db.getSession(), builder()
+        .setOrganization(organization)
+        .setProfile(profile)
+        .setMembership(ANY)
+        .build(),
+      Pagination.forPage(1).andSize(10)))
+      .extracting(UserMembershipDto::getUserId)
+      .containsExactly(user1.getId(), user2.getId(), user3.getId());
+  }
+
+  @Test
   public void insert() {
     underTest.insert(db.getSession(), new QProfileEditUsersDto()
       .setUuid("ABCD")