SONAR-4940 Create WS to search groups by permission template

9 files changed, 203 insertions, 14 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 0429d8096cc..7bbcaf60ad8 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
@@ -28,7 +28,6 @@ import org.sonar.api.security.DefaultGroups;
 import org.sonar.core.persistence.MyBatis;
 
 import javax.annotation.Nullable;
-
 import java.util.List;
 import java.util.Map;
 
@@ -53,7 +52,7 @@ public class PermissionDao implements ServerComponent {
       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);
     }
   }
 
@@ -64,7 +63,7 @@ public class PermissionDao implements ServerComponent {
 
   /**
    * @return a non paginated list of groups.
-   * Membership parameter from query is not taking into account in order to deal more easily deal the 'Anyone' group
+   * Membership parameter from query is not taking into account in order to deal more easily with the 'Anyone' group
    */
   public List<GroupWithPermissionDto> selectGroups(WithPermissionQuery query, @Nullable Long componentId) {
     SqlSession session = myBatis.openSession();
@@ -75,7 +74,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 300d7579fe7..e8d11287bc4 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
@@ -25,6 +25,7 @@ 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.security.DefaultGroups;
 import org.sonar.api.task.TaskComponent;
 import org.sonar.core.date.DateProvider;
 import org.sonar.core.date.DefaultDateProvider;
@@ -32,7 +33,6 @@ import org.sonar.core.persistence.MyBatis;
 
 import javax.annotation.CheckForNull;
 import javax.annotation.Nullable;
-
 import java.text.Normalizer;
 import java.util.Date;
 import java.util.List;
@@ -74,6 +74,23 @@ public class PermissionTemplateDao implements TaskComponent, ServerComponent {
     return selectUsers(query, templateId, 0, Integer.MAX_VALUE);
   }
 
+  /**
+   * @return a non paginated list of groups.
+   * Membership parameter from query is not taking into account in order to deal more easily with the 'Anyone' group
+   */
+  public List<GroupWithPermissionDto> selectGroups(WithPermissionQuery query, Long templateId) {
+    SqlSession session = myBatis.openSession();
+    try {
+      Map<String, Object> params = newHashMap();
+      params.put("query", query);
+      params.put("templateId", templateId);
+      params.put("anyoneGroup", DefaultGroups.ANYONE);
+      return session.selectList("org.sonar.core.permission.PermissionTemplateMapper.selectGroups", params);
+    } finally {
+      MyBatis.closeQuietly(session);
+    }
+  }
+
   @CheckForNull
   public PermissionTemplateDto selectTemplateByKey(String templateKey) {
     SqlSession session = myBatis.openSession();
diff --git a/sonar-core/src/main/resources/org/sonar/core/permission/PermissionMapper.xml b/sonar-core/src/main/resources/org/sonar/core/permission/PermissionMapper.xml
index bf4a7ec568b..4b95b2be527 100644
--- a/sonar-core/src/main/resources/org/sonar/core/permission/PermissionMapper.xml
+++ b/sonar-core/src/main/resources/org/sonar/core/permission/PermissionMapper.xml
@@ -6,7 +6,8 @@
   <select id="selectUsers" parameterType="map" resultType="UserWithPermission">
     SELECT u.login as login, u.name as name, user_role.role as permission
     FROM users u
-    LEFT JOIN user_roles user_role ON user_role.user_id=u.id AND user_role.role=#{query.permission}
+    LEFT JOIN user_roles user_role ON user_role.user_id=u.id
+      AND user_role.role=#{query.permission}
     <if test="componentId != null">
       AND user_role.resource_id=#{componentId}
     </if>
@@ -33,7 +34,8 @@
     SELECT name, permission FROM
     (SELECT g.name as name, group_role.role as permission
     FROM groups g
-    LEFT JOIN group_roles group_role ON group_role.group_id=g.id AND group_role.role=#{query.permission}
+    LEFT JOIN group_roles group_role ON group_role.group_id=g.id
+      AND group_role.role=#{query.permission}
     <if test="componentId != null">
       AND group_role.resource_id=#{componentId}
     </if>
@@ -41,12 +43,12 @@
       AND group_role.resource_id IS NULL
     </if>
     UNION
-    -- Add Anyone group
+    -- Add Anyone group permission
     SELECT #{anyoneGroup} as name, group_role.role as permission
     FROM group_roles group_role
     <where>
-      AND group_role.group_id IS NULL
       AND group_role.role=#{query.permission}
+      AND group_role.group_id IS NULL
       <if test="componentId != null">
         AND group_role.resource_id=#{componentId}
       </if>
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 f6461371b30..f472ec10ae2 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
@@ -83,6 +83,31 @@
     ORDER BY u.name
   </select>
 
+  <select id="selectGroups" parameterType="map" resultType="GroupWithPermission">
+    SELECT name, permission FROM
+    (SELECT g.name as name, ptg.permission_reference as permission
+    FROM groups g
+    LEFT JOIN perm_templates_groups ptg ON ptg.group_id=g.id
+      AND ptg.permission_reference=#{query.permission}
+      AND ptg.template_id=#{templateId}
+    UNION
+    -- Add Anyone group permission
+    SELECT #{anyoneGroup} as name, ptg.permission_reference as permission
+    FROM perm_templates_groups ptg
+    <where>
+      AND ptg.permission_reference=#{query.permission}
+      AND ptg.template_id=#{templateId}
+      AND ptg.group_id IS NULL
+    </where>
+    ) groups
+    <where>
+      <if test="query.search() != null">
+        AND (UPPER(groups.name) LIKE #{query.searchSql} ESCAPE '/')
+      </if>
+    </where>
+    ORDER BY groups.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/GroupWithPermissionTemplateDaoTest.java b/sonar-core/src/test/java/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest.java
new file mode 100644
index 00000000000..cbaa225a0a0
--- /dev/null
+++ b/sonar-core/src/test/java/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest.java
@@ -0,0 +1,114 @@
+/*
+ * 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 GroupWithPermissionTemplateDaoTest extends AbstractDaoTestCase {
+
+  private static final long TEMPLATE_ID = 50L;
+
+  private PermissionTemplateDao dao;
+
+  @Before
+  public void setUp() {
+    dao = new PermissionTemplateDao(getMyBatis());
+  }
+
+  @Test
+  public void select_groups() throws Exception {
+    setupData("groups_with_permissions");
+
+    WithPermissionQuery query = WithPermissionQuery.builder().permission("user").build();
+    List<GroupWithPermissionDto> result = dao.selectGroups(query, TEMPLATE_ID);
+
+    assertThat(result).hasSize(4);
+
+    GroupWithPermissionDto anyone = result.get(0);
+    assertThat(anyone.getName()).isEqualTo("Anyone");
+    assertThat(anyone.getPermission()).isNotNull();
+
+    GroupWithPermissionDto group1 = result.get(1);
+    assertThat(group1.getName()).isEqualTo("sonar-administrators");
+    assertThat(group1.getPermission()).isNotNull();
+
+    GroupWithPermissionDto group2 = result.get(2);
+    assertThat(group2.getName()).isEqualTo("sonar-reviewers");
+    assertThat(group2.getPermission()).isNull();
+
+    GroupWithPermissionDto group3 = result.get(3);
+    assertThat(group3.getName()).isEqualTo("sonar-users");
+    assertThat(group3.getPermission()).isNotNull();
+  }
+
+  @Test
+  public void anyone_group_is_not_returned_when_it_has_no_permission() throws Exception {
+    setupData("groups_with_permissions");
+
+    // Anyone group has not the permission 'admin', so it's not returned
+    WithPermissionQuery query = WithPermissionQuery.builder().permission("admin").build();
+    List<GroupWithPermissionDto> result = dao.selectGroups(query, TEMPLATE_ID);
+    assertThat(result).hasSize(3);
+
+    GroupWithPermissionDto group1 = result.get(0);
+    assertThat(group1.getName()).isEqualTo("sonar-administrators");
+    assertThat(group1.getPermission()).isNotNull();
+
+    GroupWithPermissionDto group2 = result.get(1);
+    assertThat(group2.getName()).isEqualTo("sonar-reviewers");
+    assertThat(group2.getPermission()).isNull();
+
+    GroupWithPermissionDto group3 = result.get(2);
+    assertThat(group3.getName()).isEqualTo("sonar-users");
+    assertThat(group3.getPermission()).isNull();
+  }
+
+  @Test
+  public void search_by_groups_name() throws Exception {
+    setupData("groups_with_permissions");
+
+    List<GroupWithPermissionDto> result = dao.selectGroups(WithPermissionQuery.builder().permission("user").search("aDMini").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(1);
+    assertThat(result.get(0).getName()).isEqualTo("sonar-administrators");
+
+    result = dao.selectGroups(WithPermissionQuery.builder().permission("user").search("sonar").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(3);
+  }
+
+  @Test
+  public void search_groups_should_be_sorted_by_group_name() throws Exception {
+    setupData("groups_with_permissions_should_be_sorted_by_group_name");
+
+    List<GroupWithPermissionDto> result = dao.selectGroups(WithPermissionQuery.builder().permission("user").build(), TEMPLATE_ID);
+    assertThat(result).hasSize(4);
+    assertThat(result.get(0).getName()).isEqualTo("Anyone");
+    assertThat(result.get(1).getName()).isEqualTo("sonar-administrators");
+    assertThat(result.get(2).getName()).isEqualTo("sonar-reviewers");
+    assertThat(result.get(3).getName()).isEqualTo("sonar-users");
+  }
+
+}
diff --git a/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions.xml b/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions.xml
new file mode 100644
index 00000000000..38240ab4255
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions.xml
@@ -0,0 +1,16 @@
+<dataset>
+
+  <groups id="200" name="sonar-administrators"/>
+  <groups id="201" name="sonar-users" />
+  <groups id="202" name="sonar-reviewers" />
+
+  <perm_templates_groups id="1" group_id="200" permission_reference="user" template_id="50"/>
+  <perm_templates_groups id="2" group_id="200" permission_reference="admin" template_id="50"/>
+  <perm_templates_groups id="3" group_id="200" permission_reference="codeviewer" template_id="50"/>
+
+  <perm_templates_groups id="4" group_id="201" permission_reference="user" template_id="50"/>
+
+  <!-- Permission 'user' for Anyone group -->
+  <perm_templates_groups id="5" group_id="[null]" permission_reference="user" template_id="50"/>
+
+</dataset>
diff --git a/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions_should_be_sorted_by_group_name.xml b/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions_should_be_sorted_by_group_name.xml
new file mode 100644
index 00000000000..c98d45aece0
--- /dev/null
+++ b/sonar-core/src/test/resources/org/sonar/core/permission/GroupWithPermissionTemplateDaoTest/groups_with_permissions_should_be_sorted_by_group_name.xml
@@ -0,0 +1,16 @@
+<dataset>
+
+  <groups id="200" name="sonar-reviewers" />
+  <groups id="201" name="sonar-users" />
+  <groups id="202" name="sonar-administrators"/>
+
+  <perm_templates_groups id="1" group_id="200" permission_reference="user" template_id="50"/>
+  <perm_templates_groups id="2" group_id="200" permission_reference="admin" template_id="50"/>
+  <perm_templates_groups id="3" group_id="200" permission_reference="codeviewer" template_id="50"/>
+
+  <perm_templates_groups id="4" group_id="201" permission_reference="user" template_id="50"/>
+
+  <!-- Permission 'user' for Anyone group -->
+  <perm_templates_groups id="5" group_id="[null]" permission_reference="user" template_id="50"/>
+
+</dataset>
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
index a53bed650d2..9c9f018aa2b 100644
--- 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
@@ -10,6 +10,4 @@
 
   <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
index 3bb7487923c..54acdf839b2 100644
--- 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
@@ -4,8 +4,10 @@
   <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"/>
+  <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"/>
 
 </dataset>