SONAR-7129 WS api/components/tree

10 files changed, 590 insertions, 25 deletions

diff --git a/sonar-db/src/main/java/org/sonar/db/component/ComponentDao.java b/sonar-db/src/main/java/org/sonar/db/component/ComponentDao.java
index 4b8f62b0e06..14371abbcf1 100644
--- a/sonar-db/src/main/java/org/sonar/db/component/ComponentDao.java
+++ b/sonar-db/src/main/java/org/sonar/db/component/ComponentDao.java
@@ -41,6 +41,7 @@ import org.sonar.db.RowNotFoundException;
 
 import static com.google.common.base.Preconditions.checkArgument;
 import static com.google.common.collect.Maps.newHashMapWithExpectedSize;
+import static org.sonar.api.utils.Paging.offset;
 import static org.sonar.db.DatabaseUtils.executeLargeInputs;
 
 public class ComponentDao implements Dao {
@@ -150,6 +151,24 @@ public class ComponentDao implements Dao {
     return mapper(session).selectComponentsHavingSameKeyOrderedById(key);
   }
 
+  public List<ComponentDto> selectDirectChildren(DbSession dbSession, ComponentTreeQuery componentQuery) {
+    RowBounds rowBounds = new RowBounds(offset(componentQuery.getPage(), componentQuery.getPageSize()), componentQuery.getPageSize());
+    return mapper(dbSession).selectDirectChildren(componentQuery, rowBounds);
+  }
+
+  public List<ComponentDto> selectAllChildren(DbSession dbSession, ComponentTreeQuery componentQuery) {
+    RowBounds rowBounds = new RowBounds(offset(componentQuery.getPage(), componentQuery.getPageSize()), componentQuery.getPageSize());
+    return mapper(dbSession).selectAllChildren(componentQuery, rowBounds);
+  }
+
+  public int countDirectChildren(DbSession dbSession, ComponentTreeQuery query) {
+    return mapper(dbSession).countDirectChildren(query);
+  }
+
+  public int countAllChildren(DbSession dbSession, ComponentTreeQuery query) {
+    return mapper(dbSession).countAllChildren(query);
+  }
+
   private static class KeyToDto implements Function<List<String>, List<ComponentDto>> {
     private final ComponentMapper mapper;
 
diff --git a/sonar-db/src/main/java/org/sonar/db/component/ComponentMapper.java b/sonar-db/src/main/java/org/sonar/db/component/ComponentMapper.java
index 42a59729461..47e3191cca9 100644
--- a/sonar-db/src/main/java/org/sonar/db/component/ComponentMapper.java
+++ b/sonar-db/src/main/java/org/sonar/db/component/ComponentMapper.java
@@ -59,9 +59,24 @@ public interface ComponentMapper {
 
   List<ComponentDto> selectComponentsByQualifiers(@Param("qualifiers") Collection<String> qualifiers);
 
-  List<ComponentDto> selectByQuery(ComponentQuery query, RowBounds rowBounds);
+  List<ComponentDto> selectByQuery(@Param("query") ComponentQuery query, RowBounds rowBounds);
 
-  int countByQuery(ComponentQuery query);
+  int countByQuery(@Param("query") ComponentQuery query);
+
+  /**
+   * Return direct children components
+   */
+  List<ComponentDto> selectDirectChildren(@Param("query") ComponentTreeQuery componentTreeQuery, RowBounds rowBounds);
+
+  int countDirectChildren(@Param("query") ComponentTreeQuery componentTreeQuery);
+
+  /**
+   * Return all children components.
+   */
+  List<ComponentDto> selectAllChildren(@Param("query") ComponentTreeQuery componentTreeQuery,
+                                       RowBounds rowBounds);
+
+  int countAllChildren(@Param("query") ComponentTreeQuery componentTreeQuery);
 
   /**
    * Return all project (PRJ/TRK) uuids
@@ -83,7 +98,7 @@ public interface ComponentMapper {
    * Return all descendant modules (including itself) from a given component uuid and scope
    */
   List<ComponentDto> selectDescendantModules(@Param("moduleUuid") String moduleUuid, @Param(value = "scope") String scope,
-                                             @Param(value = "excludeDisabled") boolean excludeDisabled);
+    @Param(value = "excludeDisabled") boolean excludeDisabled);
 
   /**
    * Return all files from a given project uuid and scope
@@ -94,7 +109,7 @@ public interface ComponentMapper {
    * Return all descendant files from a given module uuid and scope
    */
   List<FilePathWithHashDto> selectDescendantFiles(@Param("moduleUuid") String moduleUuid, @Param(value = "scope") String scope,
-                                                  @Param(value = "excludeDisabled") boolean excludeDisabled);
+    @Param(value = "excludeDisabled") boolean excludeDisabled);
 
   /**
    * Return uuids and project uuids from list of qualifiers
@@ -109,7 +124,7 @@ public interface ComponentMapper {
    * @param scope scope of components to return. If null, all components are returned
    */
   List<ComponentDto> selectComponentsFromProjectKeyAndScope(@Param("projectKey") String projectKey, @Nullable @Param("scope") String scope,
-                                                            @Param(value = "excludeDisabled") boolean excludeDisabled);
+    @Param(value = "excludeDisabled") boolean excludeDisabled);
 
   /**
    * Return technical projects from a view or a sub-view
@@ -133,5 +148,4 @@ public interface ComponentMapper {
   void update(ComponentDto componentDto);
 
   void delete(long componentId);
-
 }
diff --git a/sonar-db/src/main/java/org/sonar/db/component/ComponentTreeQuery.java b/sonar-db/src/main/java/org/sonar/db/component/ComponentTreeQuery.java
new file mode 100644
index 00000000000..749c21e3aff
--- /dev/null
+++ b/sonar-db/src/main/java/org/sonar/db/component/ComponentTreeQuery.java
@@ -0,0 +1,188 @@
+/*
+ * SonarQube, open source software quality management tool.
+ * Copyright (C) 2008-2014 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.db.component;
+
+import com.google.common.base.Function;
+import com.google.common.base.Joiner;
+import java.util.Collection;
+import java.util.List;
+import javax.annotation.CheckForNull;
+import javax.annotation.Nonnull;
+import javax.annotation.Nullable;
+import org.sonar.db.WildcardPosition;
+
+import static com.google.common.collect.FluentIterable.from;
+import static java.util.Objects.requireNonNull;
+import static org.sonar.db.DatabaseUtils.buildLikeValue;
+import static org.sonar.db.WildcardPosition.AFTER;
+
+public class ComponentTreeQuery {
+  @CheckForNull
+  private final String nameOrKeyQuery;
+  @CheckForNull
+  private final Collection<String> qualifiers;
+  @CheckForNull
+  private final Integer page;
+  @CheckForNull
+  private final Integer pageSize;
+  private final SnapshotDto baseSnapshot;
+  private final String baseSnapshotPath;
+  private final String sqlSort;
+  private final String direction;
+
+  private ComponentTreeQuery(Builder builder) {
+    this.nameOrKeyQuery = builder.nameOrKeyQuery;
+    this.qualifiers = builder.qualifiers;
+    this.page = builder.page;
+    this.pageSize = builder.pageSize;
+    this.baseSnapshot = builder.baseSnapshot;
+    this.baseSnapshotPath = buildLikeValue(baseSnapshot.getPath() + baseSnapshot.getId() + ".", WildcardPosition.AFTER);
+    this.direction = builder.asc ? "ASC" : "DESC";
+    this.sqlSort = sortFieldsToSqlSort(builder.sortFields, direction);
+  }
+
+  public Collection<String> getQualifiers() {
+    return qualifiers;
+  }
+
+  public String getNameOrKeyQuery() {
+    return nameOrKeyQuery;
+  }
+
+  @CheckForNull
+  public String getNameOrKeyQueryToSqlForResourceIndex() {
+    return nameOrKeyQuery == null ? null : buildLikeValue(nameOrKeyQuery, AFTER).toLowerCase();
+  }
+
+  @CheckForNull
+  public String getNameOrKeyQueryToSqlForProjectKey() {
+    return nameOrKeyQuery == null ? null : buildLikeValue(nameOrKeyQuery, AFTER);
+  }
+
+  public Integer getPage() {
+    return page;
+  }
+
+  public Integer getPageSize() {
+    return pageSize;
+  }
+
+  public SnapshotDto getBaseSnapshot() {
+    return baseSnapshot;
+  }
+
+  public String getBaseSnapshotPath() {
+    return baseSnapshotPath;
+  }
+
+  public String getSqlSort() {
+    return sqlSort;
+  }
+
+  public String getDirection() {
+    return direction;
+  }
+
+  public static Builder builder() {
+    return new Builder();
+  }
+
+  private String sortFieldsToSqlSort(List<String> sortFields, String direction) {
+    List<String> sqlSortFields = from(sortFields)
+      .transform(new SortFieldToSqlSortFieldFunction(direction)).toList();
+
+    return Joiner.on(", ").join(sqlSortFields);
+  }
+
+  public static class Builder {
+    @CheckForNull
+    private String nameOrKeyQuery;
+    @CheckForNull
+    private Collection<String> qualifiers;
+    @CheckForNull
+    private Integer page;
+    @CheckForNull
+    private Integer pageSize;
+    private SnapshotDto baseSnapshot;
+    private List<String> sortFields;
+    private boolean asc = true;
+
+    private Builder() {
+      // private constructor
+    }
+
+    public ComponentTreeQuery build() {
+      requireNonNull(baseSnapshot);
+      return new ComponentTreeQuery(this);
+    }
+
+    public Builder setNameOrKeyQuery(@Nullable String nameOrKeyQuery) {
+      this.nameOrKeyQuery = nameOrKeyQuery;
+      return this;
+    }
+
+    public Builder setQualifiers(Collection<String> qualifiers) {
+      this.qualifiers = qualifiers;
+      return this;
+    }
+
+    public Builder setPage(int page) {
+      this.page = page;
+      return this;
+    }
+
+    public Builder setPageSize(int pageSize) {
+      this.pageSize = pageSize;
+      return this;
+    }
+
+    public Builder setBaseSnapshot(SnapshotDto baseSnapshot) {
+      this.baseSnapshot = baseSnapshot;
+      return this;
+    }
+
+    public Builder setSortFields(List<String> sorts) {
+      this.sortFields = requireNonNull(sorts);
+      return this;
+    }
+
+    public Builder setAsc(boolean asc) {
+      this.asc = asc;
+      return this;
+    }
+  }
+
+  private static class SortFieldToSqlSortFieldFunction implements Function<String, String> {
+    private static final String PATTERN = "LOWER(p.%1$s) %2$s, p.%1$s %2$s";
+
+    private final String direction;
+
+    private SortFieldToSqlSortFieldFunction(String direction) {
+      this.direction = direction;
+    }
+
+    @Nonnull
+    @Override
+    public String apply(@Nonnull String input) {
+      return String.format(PATTERN, input, direction);
+    }
+  }
+}
diff --git a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
index c9c9205ec8e..f88928f83ca 100644
--- a/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
+++ b/sonar-db/src/main/resources/org/sonar/db/component/ComponentMapper.xml
@@ -280,25 +280,84 @@
       AND p.enabled=${_true}
       AND p.copy_resource_id is null
       AND p.qualifier in
-      <foreach collection="qualifiers" item="qualifier" open="(" close=")" separator=",">
+      <foreach collection="query.qualifiers" item="qualifier" open="(" close=")" separator=",">
         #{qualifier}
       </foreach>
-      <if test="nameOrKeyQuery!=null">
+      <if test="query.nameOrKeyQuery!=null">
         AND (exists (
         select 1
         from resource_index ri
         where
         ri.resource_id=p.id
         AND ri.qualifier in
-        <foreach collection="qualifiers" item="qualifier" open="(" close=")" separator=",">
+        <foreach collection="query.qualifiers" item="qualifier" open="(" close=")" separator=",">
           #{qualifier}
         </foreach>
-        AND ri.kee like #{nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/')
-        OR p.kee like #{nameOrKeyQueryToSqlForProjectKey} ESCAPE '/')
+        AND ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/')
+        OR p.kee like #{query.nameOrKeyQueryToSqlForProjectKey} ESCAPE '/')
       </if>
     </where>
   </sql>
 
+  <select id="selectDirectChildren" resultType="Component">
+    select
+    <include refid="componentColumns"/>
+    <include refid="sqlSelectByTreeQuery"/>
+    and s.parent_snapshot_id = #{query.baseSnapshot.id}
+    order by ${query.sqlSort}
+  </select>
+
+  <select id="countDirectChildren" resultType="int">
+    select count(p.id)
+    <include refid="sqlSelectByTreeQuery"/>
+    and s.parent_snapshot_id = #{query.baseSnapshot.id}
+  </select>
+
+  <select id="selectAllChildren" resultType="Component">
+    select
+    <include refid="componentColumns"/>
+    <include refid="sqlSelectAllChildren" />
+    order by ${query.sqlSort}
+  </select>
+
+  <select id="countAllChildren" resultType="int">
+    select count(p.id)
+    <include refid="sqlSelectAllChildren"/>
+  </select>
+
+  <sql id="sqlSelectAllChildren">
+    <include refid="sqlSelectByTreeQuery"/>
+    <if test="query.baseSnapshot.rootId!=null">
+      and s.root_snapshot_id = #{query.baseSnapshot.rootId}
+    </if>
+    <if test="query.baseSnapshot.rootId==null">
+      and s.root_snapshot_id = #{query.baseSnapshot.id}
+    </if>
+    and s.path like #{query.baseSnapshotPath} ESCAPE '/'
+  </sql>
+
+  <sql id="sqlSelectByTreeQuery">
+    from projects p
+    inner join snapshots s on p.id = s.project_id
+    where
+    p.enabled=${_true}
+    <if test="query.qualifiers!=null">
+      AND p.qualifier in
+      <foreach collection="query.qualifiers" item="qualifier" open="(" close=")" separator=",">
+        #{qualifier}
+      </foreach>
+    </if>
+    <if test="query.nameOrKeyQuery!=null">
+      AND (exists (
+      select 1
+      from resource_index ri
+      where
+      ri.resource_id=p.id
+      AND ri.kee like #{query.nameOrKeyQueryToSqlForResourceIndex} ESCAPE '/')
+      OR p.kee like #{query.nameOrKeyQueryToSqlForProjectKey} ESCAPE '/')
+    </if>
+  </sql>
+
   <select id="countRootComponents" resultType="int">
     select count(p.id)
     from projects p
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
index 129a2c4f117..5cf3d42c5f7 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentDaoTest.java
@@ -42,6 +42,8 @@ import static java.util.Collections.singletonList;
 import static org.assertj.core.api.Assertions.assertThat;
 import static org.assertj.guava.api.Assertions.assertThat;
 import static org.sonar.db.component.ComponentTesting.newDeveloper;
+import static org.sonar.db.component.ComponentTesting.newFileDto;
+import static org.sonar.db.component.ComponentTesting.newModuleDto;
 import static org.sonar.db.component.ComponentTesting.newProjectDto;
 import static org.sonar.db.component.ComponentTesting.newView;
 
@@ -672,11 +674,11 @@ public class ComponentDaoTest {
 
   @Test
   public void select_by_query_with_paging_query_and_qualifiers() {
-    componentDb.insertProjectAndSnapshot(dbSession, newProjectDto().setName("aaaa-name"));
-    componentDb.insertProjectAndSnapshot(dbSession, newView());
-    componentDb.insertProjectAndSnapshot(dbSession, newDeveloper("project-name"));
+    componentDb.insertProjectAndSnapshot(newProjectDto().setName("aaaa-name"));
+    componentDb.insertProjectAndSnapshot(newView());
+    componentDb.insertProjectAndSnapshot(newDeveloper("project-name"));
     for (int i = 9; i >= 1; i--) {
-      componentDb.insertProjectAndSnapshot(dbSession, newProjectDto().setName("project-" + i));
+      componentDb.insertProjectAndSnapshot(newProjectDto().setName("project-" + i));
     }
     db.commit();
     componentDb.indexProjects();
@@ -691,7 +693,7 @@ public class ComponentDaoTest {
 
   @Test
   public void select_by_query_name_with_special_characters() {
-    componentDb.insertProjectAndSnapshot(dbSession, newProjectDto().setName("project-\\_%/-name"));
+    componentDb.insertProjectAndSnapshot(newProjectDto().setName("project-\\_%/-name"));
     db.commit();
     componentDb.indexProjects();
 
@@ -704,7 +706,7 @@ public class ComponentDaoTest {
 
   @Test
   public void select_by_query_key_with_special_characters() {
-    componentDb.insertProjectAndSnapshot(dbSession, newProjectDto()
+    componentDb.insertProjectAndSnapshot(newProjectDto()
       .setKey("project-_%-key"));
     db.commit();
     componentDb.indexProjects();
@@ -715,4 +717,181 @@ public class ComponentDaoTest {
     assertThat(result).hasSize(1);
     assertThat(result.get(0).key()).isEqualTo("project-_%-key");
   }
+
+  @Test
+  public void select_direct_children_of_a_project() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid"), moduleSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot).build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+    int count = underTest.countDirectChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(2);
+    assertThat(result).extracting("uuid").containsExactly("file-1-uuid", "module-1-uuid");
+  }
+
+  @Test
+  public void select_direct_children_with_name_query() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid").setName("file-name-1"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid").setName("file-name-2"), moduleSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot)
+      .setNameOrKeyQuery("file-name").build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+    int count = underTest.countDirectChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(1);
+    assertThat(result).extracting("uuid").containsExactly("file-1-uuid");
+  }
+
+  @Test
+  public void select_direct_children_with_key_query() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid").setKey("file-key-1"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid").setKey("file-key-2"), moduleSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot)
+      .setNameOrKeyQuery("file-key").build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+    int count = underTest.countDirectChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(1);
+    assertThat(result).extracting("uuid").containsExactly("file-1-uuid");
+  }
+
+  @Test
+  public void select_direct_children_with_pagination() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    for (int i = 1; i <= 9; i++) {
+      componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-" + i), projectSnapshot);
+    }
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot)
+      .setPage(2)
+      .setPageSize(3)
+      .setAsc(false)
+      .build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+    int count = underTest.countDirectChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(9);
+    assertThat(result).extracting("uuid").containsExactly("file-uuid-6", "file-uuid-5", "file-uuid-4");
+  }
+
+  @Test
+  public void select_direct_children_with_order_by_path() {
+    ComponentDto project = newProjectDto();
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-1").setName("file-name-1").setPath("3"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-2").setName("file-name-2").setPath("2"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-3").setName("file-name-3").setPath("1"), projectSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot)
+      .setSortFields(singletonList("path"))
+      .setAsc(true)
+      .build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+
+    assertThat(result).extracting("uuid").containsExactly("file-uuid-3", "file-uuid-2", "file-uuid-1");
+  }
+
+  @Test
+  public void select_direct_children_of_a_module() {
+    ComponentDto project = newProjectDto();
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid"), moduleSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(moduleSnapshot).build();
+
+    List<ComponentDto> result = underTest.selectDirectChildren(dbSession, query);
+
+    assertThat(result).extracting("uuid").containsOnly("file-2-uuid");
+  }
+
+  @Test
+  public void select_all_children_of_a_project() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-1-uuid"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-2-uuid"), moduleSnapshot);
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot).build();
+
+    List<ComponentDto> result = underTest.selectAllChildren(dbSession, query);
+    int count = underTest.countAllChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(3);
+    assertThat(result).extracting("uuid").containsExactly("file-1-uuid", "file-2-uuid", "module-1-uuid");
+  }
+
+  @Test
+  public void select_all_files_of_a_project_paginated_and_ordered() {
+    ComponentDto project = newProjectDto().setKey("project-key").setUuid("project-uuid");
+    SnapshotDto projectSnapshot = componentDb.insertProjectAndSnapshot(project);
+    SnapshotDto moduleSnapshot = componentDb.insertComponentAndSnapshot(newModuleDto("module-1-uuid", project), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-1").setName("file-name-1"), projectSnapshot);
+    componentDb.insertComponentAndSnapshot(newFileDto(project, "another-uuid"), projectSnapshot);
+    for (int i = 2; i <= 9; i++) {
+      componentDb.insertComponentAndSnapshot(newFileDto(project, "file-uuid-" + i).setName("file-name-" + i), moduleSnapshot);
+    }
+    db.commit();
+    componentDb.indexProjects();
+
+    ComponentTreeQuery query = newTreeQuery(projectSnapshot)
+      .setQualifiers(newArrayList(Qualifiers.FILE))
+      .setPage(2)
+      .setPageSize(3)
+      .setNameOrKeyQuery("file-name")
+      .setSortFields(singletonList("name"))
+      .setAsc(false)
+      .build();
+
+    List<ComponentDto> result = underTest.selectAllChildren(dbSession, query);
+    int count = underTest.countAllChildren(dbSession, query);
+
+    assertThat(count).isEqualTo(9);
+    assertThat(result).extracting("uuid").containsExactly("file-uuid-6", "file-uuid-5", "file-uuid-4");
+  }
+
+  private static ComponentTreeQuery.Builder newTreeQuery(SnapshotDto baseSnapshot) {
+    return ComponentTreeQuery.builder()
+      .setPage(1)
+      .setPageSize(500)
+      .setBaseSnapshot(baseSnapshot)
+      .setSortFields(singletonList("name"))
+      .setAsc(true)
+      .setQualifiers(newArrayList(Qualifiers.FILE, Qualifiers.MODULE, Qualifiers.DIRECTORY, Qualifiers.PROJECT));
+  }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentDbTester.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentDbTester.java
index 731a05a866a..74932cfe65e 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/ComponentDbTester.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentDbTester.java
@@ -24,7 +24,9 @@ import org.sonar.db.DbClient;
 import org.sonar.db.DbSession;
 import org.sonar.db.DbTester;
 
+import static org.sonar.db.component.SnapshotTesting.createForComponent;
 import static org.sonar.db.component.SnapshotTesting.newSnapshotForProject;
+import static org.sonar.db.component.SnapshotTesting.newSnapshotForView;
 
 public class ComponentDbTester {
   private final DbTester db;
@@ -37,9 +39,28 @@ public class ComponentDbTester {
     this.dbSession = db.getSession();
   }
 
-  public void insertProjectAndSnapshot(DbSession dbSession, ComponentDto component) {
+  public SnapshotDto insertProjectAndSnapshot(ComponentDto component) {
     dbClient.componentDao().insert(dbSession, component);
-    dbClient.snapshotDao().insert(dbSession, newSnapshotForProject(component));
+    SnapshotDto snapshot = dbClient.snapshotDao().insert(dbSession, newSnapshotForProject(component));
+    db.commit();
+
+    return snapshot;
+  }
+
+  public SnapshotDto insertViewAndSnapshot(ComponentDto component) {
+    dbClient.componentDao().insert(dbSession, component);
+    SnapshotDto snapshot = dbClient.snapshotDao().insert(dbSession, newSnapshotForView(component));
+    db.commit();
+
+    return snapshot;
+  }
+
+  public SnapshotDto insertComponentAndSnapshot(ComponentDto component, SnapshotDto parentSnapshot) {
+    dbClient.componentDao().insert(dbSession, component);
+    SnapshotDto snapshot = dbClient.snapshotDao().insert(dbSession, createForComponent(component, parentSnapshot));
+    db.commit();
+
+    return snapshot;
   }
 
   public ComponentDto insertComponent(ComponentDto component) {
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentTesting.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentTesting.java
index 11c3cebbc8c..8ac8cb816b2 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/ComponentTesting.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentTesting.java
@@ -20,6 +20,7 @@
 
 package org.sonar.db.component;
 
+import java.util.Date;
 import org.sonar.api.resources.Qualifiers;
 import org.sonar.api.resources.Scopes;
 import org.sonar.core.util.Uuids;
@@ -42,6 +43,7 @@ public class ComponentTesting {
       .setScope(Scopes.FILE)
       .setQualifier(Qualifiers.FILE)
       .setPath(path)
+      .setCreatedAt(new Date())
       .setLanguage("xoo");
   }
 
@@ -168,6 +170,7 @@ public class ComponentTesting {
       .setModuleUuid(module.uuid())
       .setModuleUuidPath(module.moduleUuidPath())
       .setParentProjectId(module.getId())
+      .setCreatedAt(new Date())
       .setEnabled(true);
   }
 }
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ComponentTreeQueryTest.java b/sonar-db/src/test/java/org/sonar/db/component/ComponentTreeQueryTest.java
new file mode 100644
index 00000000000..c3a1695dac4
--- /dev/null
+++ b/sonar-db/src/test/java/org/sonar/db/component/ComponentTreeQueryTest.java
@@ -0,0 +1,63 @@
+/*
+ * SonarQube, open source software quality management tool.
+ * Copyright (C) 2008-2014 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.db.component;
+
+import java.util.Collections;
+import org.junit.Rule;
+import org.junit.Test;
+import org.junit.rules.ExpectedException;
+
+import static com.google.common.collect.Lists.newArrayList;
+import static org.assertj.core.api.Assertions.assertThat;
+
+public class ComponentTreeQueryTest {
+
+  @Rule
+  public ExpectedException expectedException = ExpectedException.none();
+
+  @Test
+  public void convert_sorts_in_sql_representation() {
+    ComponentTreeQuery result = ComponentTreeQuery.builder()
+      .setBaseSnapshot(new SnapshotDto())
+      .setSortFields(newArrayList("name", "path", "qualifier"))
+      .build();
+
+    assertThat(result.getSqlSort()).isEqualTo("LOWER(p.name) ASC, p.name ASC, LOWER(p.path) ASC, p.path ASC, LOWER(p.qualifier) ASC, p.qualifier ASC");
+  }
+
+  @Test
+  public void fail_if_no_base_snapshot() {
+    expectedException.expect(NullPointerException.class);
+
+    ComponentTreeQuery.builder()
+      .setSortFields(Collections.<String>emptyList())
+      .build();
+  }
+
+  @Test
+  public void fail_if_no_sort() {
+    expectedException.expect(NullPointerException.class);
+
+    ComponentTreeQuery.builder()
+      .setBaseSnapshot(new SnapshotDto())
+      .build();
+  }
+}
diff --git a/sonar-db/src/test/java/org/sonar/db/component/ResourceTypesRule.java b/sonar-db/src/test/java/org/sonar/db/component/ResourceTypesRule.java
index e305ca60a63..23d00b9f211 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/ResourceTypesRule.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/ResourceTypesRule.java
@@ -20,6 +20,8 @@
 
 package org.sonar.db.component;
 
+import java.util.ArrayList;
+import java.util.Arrays;
 import java.util.Collection;
 import java.util.HashSet;
 import java.util.List;
@@ -31,6 +33,8 @@ import org.sonar.api.resources.ResourceTypes;
 public class ResourceTypesRule extends ResourceTypes {
   private final Set<ResourceType> allResourceTypes = new HashSet<>();
   private final Set<ResourceType> rootResourceTypes = new HashSet<>();
+  private final List<String> childrenQualifiers = new ArrayList<>();
+  private final List<String> leavesQualifiers = new ArrayList<>();
 
   @Override
   public Collection<ResourceType> getAll() {
@@ -51,6 +55,19 @@ public class ResourceTypesRule extends ResourceTypes {
     return this;
   }
 
+  public ResourceTypesRule setLeavesQualifiers(String... qualifiers) {
+    leavesQualifiers.clear();
+    leavesQualifiers.addAll(Arrays.asList(qualifiers));
+    return this;
+  }
+
+  public ResourceTypesRule setChildrenQualifiers(String... qualifiers) {
+    childrenQualifiers.clear();
+    childrenQualifiers.addAll(Arrays.asList(qualifiers));
+
+    return this;
+  }
+
   public ResourceTypesRule setAllQualifiers(String... qualifiers) {
     allResourceTypes.clear();
     for (String qualifier : qualifiers) {
@@ -82,7 +99,7 @@ public class ResourceTypesRule extends ResourceTypes {
 
   @Override
   public List<String> getChildrenQualifiers(String qualifier) {
-    throw new UnsupportedOperationException();
+    return this.childrenQualifiers;
   }
 
   @Override
@@ -92,7 +109,7 @@ public class ResourceTypesRule extends ResourceTypes {
 
   @Override
   public List<String> getLeavesQualifiers(String qualifier) {
-    throw new UnsupportedOperationException();
+    return this.leavesQualifiers;
   }
 
   @Override
diff --git a/sonar-db/src/test/java/org/sonar/db/component/SnapshotTesting.java b/sonar-db/src/test/java/org/sonar/db/component/SnapshotTesting.java
index 65d2aca5c72..727adc6f0f4 100644
--- a/sonar-db/src/test/java/org/sonar/db/component/SnapshotTesting.java
+++ b/sonar-db/src/test/java/org/sonar/db/component/SnapshotTesting.java
@@ -20,20 +20,22 @@
 
 package org.sonar.db.component;
 
-import com.google.common.base.Preconditions;
 import org.assertj.core.util.Strings;
 
+import static com.google.common.base.Preconditions.checkNotNull;
+
 public class SnapshotTesting {
 
   /**
    * Can be used for modules and files
    */
   public static SnapshotDto createForComponent(ComponentDto component, SnapshotDto parentSnapshot) {
-    Preconditions.checkNotNull(parentSnapshot.getId(), "The parent snapshot need to be persisted before creating this snapshot");
+    checkNotNull(parentSnapshot.getId(), "The parent snapshot need to be persisted before creating this snapshot");
     Long parentRootId = parentSnapshot.getRootId();
     return createBasicSnapshot(component, parentSnapshot.getRootProjectId())
       .setRootId(parentRootId != null ? parentRootId : parentSnapshot.getId())
       .setParentId(parentSnapshot.getId())
+      .setDepth(parentSnapshot.getDepth()+1)
       .setPath(
         Strings.isNullOrEmpty(parentSnapshot.getPath()) ? Long.toString(parentSnapshot.getId()) + "." : parentSnapshot.getPath() + Long.toString(parentSnapshot.getId()) + ".");
   }
@@ -57,8 +59,8 @@ public class SnapshotTesting {
   }
 
   private static SnapshotDto createBasicSnapshot(ComponentDto component, Long rootProjectId) {
-    Preconditions.checkNotNull(component.getId(), "The project need to be persisted before creating this snapshot");
-    Preconditions.checkNotNull(rootProjectId, "Root project id is null");
+    checkNotNull(component.getId(), "The project need to be persisted before creating this snapshot");
+    checkNotNull(rootProjectId, "Root project id is null");
     return new SnapshotDto()
       .setComponentId(component.getId())
       .setRootProjectId(rootProjectId)