SONAR-4941 Create WS to search users by permission template

7 files changed, 240 insertions, 7 deletions

diff --git a/sonar-core/src/main/java/org/sonar/core/permission/PermissionDao.java b/sonar-core/src/main/java/org/sonar/core/permission/PermissionDao.java
index 7e038608a5d..0429d8096cc 100644
--- a/sonar-core/src/main/java/org/sonar/core/permission/PermissionDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/permission/PermissionDao.java
@@ -36,24 +36,24 @@ import static com.google.common.collect.Maps.newHashMap;
 
 public class PermissionDao implements ServerComponent {
 
-  private final MyBatis mybatis;
+  private final MyBatis myBatis;
 
-  public PermissionDao(MyBatis mybatis) {
-    this.mybatis = mybatis;
+  public PermissionDao(MyBatis myBatis) {
+    this.myBatis = myBatis;
   }
 
   /**
    * @return a paginated list of users.
    */
   public List<UserWithPermissionDto> selectUsers(WithPermissionQuery query, @Nullable Long componentId, int offset, int limit) {
-    SqlSession session = mybatis.openSession();
+    SqlSession session = myBatis.openSession();
     try {
       Map<String, Object> params = newHashMap();
       params.put("query", query);
       params.put("componentId", componentId);
       return session.selectList("org.sonar.core.permission.PermissionMapper.selectUsers", params, new RowBounds(offset, limit));
     } finally {
-      MyBatis.closeQuietly(session);
+      myBatis.closeQuietly(session);
     }
   }
 
@@ -67,7 +67,7 @@ public class PermissionDao implements ServerComponent {
    * Membership parameter from query is not taking into account in order to deal more easily deal the 'Anyone' group
    */
   public List<GroupWithPermissionDto> selectGroups(WithPermissionQuery query, @Nullable Long componentId) {
-    SqlSession session = mybatis.openSession();
+    SqlSession session = myBatis.openSession();
     try {
       Map<String, Object> params = newHashMap();
       params.put("query", query);
@@ -75,7 +75,7 @@ public class PermissionDao implements ServerComponent {
       params.put("anyoneGroup", DefaultGroups.ANYONE);
       return session.selectList("org.sonar.core.permission.PermissionMapper.selectGroups", params);
     } finally {
-      MyBatis.closeQuietly(session);
+      myBatis.closeQuietly(session);
     }
   }
 
diff --git a/sonar-core/src/main/java/org/sonar/core/permission/PermissionTemplateDao.java b/sonar-core/src/main/java/org/sonar/core/permission/PermissionTemplateDao.java
index 27fb8b84caa..72262d4052b 100644
--- a/sonar-core/src/main/java/org/sonar/core/permission/PermissionTemplateDao.java
+++ b/sonar-core/src/main/java/org/sonar/core/permission/PermissionTemplateDao.java
@@ -20,7 +20,9 @@
 
 package org.sonar.core.permission;
 
+import com.google.common.annotations.VisibleForTesting;
 import org.apache.commons.lang.time.DateFormatUtils;
+import org.apache.ibatis.session.RowBounds;
 import org.apache.ibatis.session.SqlSession;
 import org.sonar.api.ServerComponent;
 import org.sonar.api.task.TaskComponent;
@@ -34,6 +36,9 @@ import javax.annotation.Nullable;
 import java.text.Normalizer;
 import java.util.Date;
 import java.util.List;
+import java.util.Map;
+
+import static com.google.common.collect.Maps.newHashMap;
 
 public class PermissionTemplateDao implements TaskComponent, ServerComponent {
 
@@ -49,6 +54,26 @@ public class PermissionTemplateDao implements TaskComponent, ServerComponent {
     this(myBatis, new DefaultDateProvider());
   }
 
+  /**
+   * @return a paginated list of users.
+   */
+  public List<UserWithPermissionDto> selectUsers(WithPermissionQuery query, Long templateId, int offset, int limit) {
+    SqlSession session = myBatis.openSession();
+    try {
+      Map<String, Object> params = newHashMap();
+      params.put("query", query);
+      params.put("templateId", templateId);
+      return session.selectList("org.sonar.core.permission.PermissionTemplateMapper.selectUsers", params, new RowBounds(offset, limit));
+    } finally {
+      MyBatis.closeQuietly(session);
+    }
+  }
+
+  @VisibleForTesting
+  List<UserWithPermissionDto> selectUsers(WithPermissionQuery query, Long templateId) {
+    return selectUsers(query, templateId, 0, Integer.MAX_VALUE);
+  }
+
   @CheckForNull
   public PermissionTemplateDto selectTemplateByName(String templateName) {
     SqlSession session = myBatis.openSession();
diff --git a/sonar-core/src/main/java/org/sonar/core/permission/WithPermissionQuery.java b/sonar-core/src/main/java/org/sonar/core/permission/WithPermissionQuery.java
index 3faf1dfd8c5..8f691a13570 100644
--- a/sonar-core/src/main/java/org/sonar/core/permission/WithPermissionQuery.java
+++ b/sonar-core/src/main/java/org/sonar/core/permission/WithPermissionQuery.java
@@ -44,6 +44,7 @@ public class WithPermissionQuery {
 
   private final String permission;
   private final String component;
+  private final String template;
   private final String membership;
   private final String search;
 
@@ -60,6 +61,7 @@ public class WithPermissionQuery {
   private WithPermissionQuery(Builder builder) {
     this.permission = builder.permission;
     this.component = builder.component;
+    this.template = builder.template;
     this.membership = builder.membership;
     this.search = builder.search;
     this.searchSql = searchToSql(search);
@@ -82,6 +84,16 @@ public class WithPermissionQuery {
     return permission;
   }
 
+  /**
+   * Used only for permission template
+   */
+  public String template() {
+    return template;
+  }
+
+  /**
+   * Used on project permission
+   */
   @CheckForNull
   public String component() {
     return component;
@@ -112,6 +124,7 @@ public class WithPermissionQuery {
   public static class Builder {
     private String permission;
     private String component;
+    private String template;
     private String membership;
     private String search;
 
@@ -126,6 +139,11 @@ public class WithPermissionQuery {
       return this;
     }
 
+    public Builder template(String template) {
+      this.template = template;
+      return this;
+    }
+
     public Builder component(@Nullable String component) {
       this.component = component;
       return this;
diff --git a/sonar-core/src/main/resources/org/sonar/core/permission/PermissionTemplateMapper.xml b/sonar-core/src/main/resources/org/sonar/core/permission/PermissionTemplateMapper.xml
index 29af09a54c0..7cf029510c1 100644
--- a/sonar-core/src/main/resources/org/sonar/core/permission/PermissionTemplateMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/permission/PermissionTemplateMapper.xml
@@ -61,6 +61,28 @@
     </choose>
   </delete>
 
+  <select id="selectUsers" parameterType="map" resultType="UserWithPermission">
+    SELECT u.login as login, u.name as name, ptu.permission_reference as permission
+    FROM users u
+    LEFT JOIN perm_templates_users ptu ON ptu.user_id=u.id
+      AND ptu.permission_reference=#{query.permission}
+      AND ptu.template_id=#{templateId}
+    <where>
+      <choose>
+        <when test="query.membership() == 'IN'">
+          AND ptu.permission_reference IS NOT NULL
+        </when>
+        <when test="query.membership() == 'OUT'">
+          AND ptu.permission_reference IS NULL
+        </when>
+      </choose>
+      <if test="query.search() != null">
+        AND (UPPER(u.name) LIKE #{query.searchSql} ESCAPE '/')
+      </if>
+    </where>
+    ORDER BY u.name
+  </select>
+
   <select id="selectByKey" parameterType="String" resultType="PermissionTemplate">
     SELECT id, name, kee, description, key_pattern AS keyPattern, created_at AS createdAt, updated_at AS updatedAt
     FROM permission_templates
diff --git a/sonar-core/src/test/java/org/sonar/core/permission/UserWithPermissionTemplateDaoTest.java b/sonar-core/src/test/java/org/sonar/core/permission/UserWithPermissionTemplateDaoTest.java
new file mode 100644
index 00000000000..adc1563ff60
--- /dev/null
+++ b/sonar-core/src/test/java/org/sonar/core/permission/UserWithPermissionTemplateDaoTest.java
@@ -0,0 +1,142 @@
+/*
+ * SonarQube, open source software quality management tool.
+ * Copyright (C) 2008-2013 SonarSource
+ * mailto:contact AT sonarsource DOT com
+ *
+ * SonarQube 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.
+ *
+ * SonarQube 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.core.permission;
+
+import org.junit.Before;
+import org.junit.Test;
+import org.sonar.core.persistence.AbstractDaoTestCase;
+
+import java.util.List;
+
+import static org.fest.assertions.Assertions.assertThat;
+
+public class UserWithPermissionTemplateDaoTest extends AbstractDaoTestCase {
+
+  private static final Long TEMPLATE_ID = 50L;
+
+  private PermissionTemplateDao dao;
+
+  @Before
+  public void setUp() {
+    dao = new PermissionTemplateDao(getMyBatis());
+  }
+
+  @Test
+  public void select_all_users() throws Exception {
+    setupData("users_with_permissions");
+
+    WithPermissionQuery query = WithPermissionQuery.builder().permission("user").build();
+    List<UserWithPermissionDto> result = dao.selectUsers(query, TEMPLATE_ID);
+    assertThat(result).hasSize(3);
+
+    UserWithPermissionDto user1 = result.get(0);
+    assertThat(user1.getLogin()).isEqualTo("user1");
+    assertThat(user1.getName()).isEqualTo("User1");
+    assertThat(user1.getPermission()).isNotNull();
+
+    UserWithPermissionDto user2 = result.get(1);
+    assertThat(user2.getLogin()).isEqualTo("user2");
+    assertThat(user2.getName()).isEqualTo("User2");
+    assertThat(user2.getPermission()).isNotNull();
+
+    UserWithPermissionDto user3 = result.get(2);
+    assertThat(user3.getLogin()).isEqualTo("user3");
+    assertThat(user3.getName()).isEqualTo("User3");
+    assertThat(user3.getPermission()).isNull();
+  }
+
+  @Test
+  public void return_nothing_on_unknown_template_key() throws Exception {
+    setupData("users_with_permissions");
+
+    WithPermissionQuery query = WithPermissionQuery.builder().permission("user").build();
+    List<UserWithPermissionDto> result = dao.selectUsers(query, 999L);
+    assertThat(result).hasSize(3);
+
+    UserWithPermissionDto user1 = result.get(0);
+    assertThat(user1.getPermission()).isNull();
+
+    UserWithPermissionDto user2 = result.get(1);
+    assertThat(user2.getPermission()).isNull();
+
+    UserWithPermissionDto user3 = result.get(2);
+    assertThat(user3.getPermission()).isNull();
+  }
+
+  @Test
+  public void select_only_user_with_permission() throws Exception {
+    setupData("users_with_permissions");
+
+    // user1 and user2 have permission user
+    assertThat(dao.selectUsers(WithPermissionQuery.builder().permission("user").membership(WithPermissionQuery.IN).build(), TEMPLATE_ID)).hasSize(2);
+  }
+
+  @Test
+  public void select_only_user_without_permission() throws Exception {
+    setupData("users_with_permissions");
+
+    // Only user3 has not the user permission
+    assertThat(dao.selectUsers(WithPermissionQuery.builder().permission("user").membership(WithPermissionQuery.OUT).build(), TEMPLATE_ID)).hasSize(1);
+  }
+
+  @Test
+  public void search_by_user_name() throws Exception {
+    setupData("users_with_permissions");
+
+    List<UserWithPermissionDto> result = dao.selectUsers(WithPermissionQuery.builder().permission("user").search("SEr1").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(1);
+    assertThat(result.get(0).getName()).isEqualTo("User1");
+
+    result = dao.selectUsers(WithPermissionQuery.builder().permission("user").search("user").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(3);
+  }
+
+  @Test
+  public void should_be_sorted_by_user_name() throws Exception {
+    setupData("users_with_permissions_should_be_sorted_by_user_name");
+
+    List<UserWithPermissionDto> result = dao.selectUsers(WithPermissionQuery.builder().permission("user").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(3);
+    assertThat(result.get(0).getName()).isEqualTo("User1");
+    assertThat(result.get(1).getName()).isEqualTo("User2");
+    assertThat(result.get(2).getName()).isEqualTo("User3");
+  }
+
+  @Test
+  public void should_be_paginated() throws Exception {
+    setupData("users_with_permissions");
+
+    List<UserWithPermissionDto> result = dao.selectUsers(WithPermissionQuery.builder().permission("user").build(), TEMPLATE_ID, 0, 2);
+    assertThat(result).hasSize(2);
+    assertThat(result.get(0).getName()).isEqualTo("User1");
+    assertThat(result.get(1).getName()).isEqualTo("User2");
+
+    result = dao.selectUsers(WithPermissionQuery.builder().permission("user").build(), TEMPLATE_ID, 1, 2);
+    assertThat(result).hasSize(2);
+    assertThat(result.get(0).getName()).isEqualTo("User2");
+    assertThat(result.get(1).getName()).isEqualTo("User3");
+
+    result = dao.selectUsers(WithPermissionQuery.builder().permission("user").build(), TEMPLATE_ID, 2, 1);
+    assertThat(result).hasSize(1);
+    assertThat(result.get(0).getName()).isEqualTo("User3");
+  }
+
+}
diff --git a/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions.xml b/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions.xml
new file mode 100644
index 00000000000..a53bed650d2
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions.xml
@@ -0,0 +1,15 @@
+<dataset>
+
+  <users id="200" login="user1" name="User1"/>
+  <users id="201" login="user2" name="User2"/>
+  <users id="202" login="user3" name="User3"/>
+
+  <perm_templates_users id="1" user_id="200" permission_reference="user" template_id="50"/>
+  <perm_templates_users id="2" user_id="200" permission_reference="admin" template_id="50"/>
+  <perm_templates_users id="3" user_id="200" permission_reference="codeviewer" template_id="50"/>
+
+  <perm_templates_users id="4" user_id="201" permission_reference="user" template_id="50"/>
+
+  <permission_templates id="50" name="Default template for Projects" kee="default_template_for_projects"/>
+
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions_should_be_sorted_by_user_name.xml b/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions_should_be_sorted_by_user_name.xml
new file mode 100644
index 00000000000..3bb7487923c
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/permission/UserWithPermissionTemplateDaoTest/users_with_permissions_should_be_sorted_by_user_name.xml
@@ -0,0 +1,11 @@
+<dataset>
+
+  <users id="200" login="user3" name="User3"/>
+  <users id="201" login="user1" name="User1"/>
+  <users id="202" login="user2" name="User2"/>
+
+  <user_roles id="1" user_id="200" resource_id="100" role="user"/>
+  <user_roles id="2" user_id="200" resource_id="100" role="admin"/>
+  <user_roles id="3" user_id="200" resource_id="100" role="codeviewer"/>
+
+</dataset>