From 6d59c892844b0e9f69c740b9829267a4b7ee3213 Mon Sep 17 00:00:00 2001 From: Pierre Date: Thu, 28 Sep 2023 16:16:58 +0200 Subject: [PATCH] SONAR-20315 update scim queries, introduce OffsetBasedPagination --- .../java/org/sonar/db/scim/ScimUserDaoIT.java | 17 ++- .../org/sonar/db/OffsetBasedPagination.java | 89 +++++++++++ .../main/java/org/sonar/db/Pagination.java | 5 +- .../main/java/org/sonar/db/Pagineable.java | 30 ++++ .../java/org/sonar/db/scim/ScimGroupDao.java | 6 +- .../org/sonar/db/scim/ScimGroupMapper.java | 4 +- .../java/org/sonar/db/scim/ScimUserDao.java | 15 +- .../org/sonar/db/scim/ScimUserMapper.java | 4 +- .../org/sonar/db/scim/ScimGroupMapper.xml | 5 + .../org/sonar/db/scim/ScimUserMapper.xml | 7 +- .../sonar/db/OffsetBasedPaginationTest.java | 141 ++++++++++++++++++ .../org/sonar/db/scim/ScimGroupDaoTest.java | 9 +- 12 files changed, 304 insertions(+), 28 deletions(-) create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/OffsetBasedPagination.java create mode 100644 server/sonar-db-dao/src/main/java/org/sonar/db/Pagineable.java create mode 100644 server/sonar-db-dao/src/test/java/org/sonar/db/OffsetBasedPaginationTest.java diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/scim/ScimUserDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/scim/ScimUserDaoIT.java index 91390b3b9b2..0530023890b 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/scim/ScimUserDaoIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/scim/ScimUserDaoIT.java @@ -36,6 +36,8 @@ import org.junit.Test; import org.junit.runner.RunWith; import org.sonar.db.DbSession; import org.sonar.db.DbTester; +import org.sonar.db.OffsetBasedPagination; +import org.sonar.db.Pagination; import org.sonar.db.user.GroupDto; import org.sonar.db.user.UserDto; @@ -111,7 +113,6 @@ public class ScimUserDaoIT { {9, 3, 3, List.of("4", "5", "6")}, {9, 7, 3, List.of("8", "9")}, {5, 5, 20, List.of()}, - {5, 0, 0, List.of()}, }; } @@ -120,7 +121,7 @@ public class ScimUserDaoIT { public void findScimUsers_whenPaginationAndStartIndex_shouldReturnTheCorrectNumberOfScimUsers(int totalScimUsers, int offset, int pageSize, List expectedScimUserUuids) { generateScimUsers(totalScimUsers); - List scimUserDtos = scimUserDao.findScimUsers(dbSession, ScimUserQuery.empty(), offset, pageSize); + List scimUserDtos = scimUserDao.findScimUsers(dbSession, ScimUserQuery.empty(), OffsetBasedPagination.forOffset(offset, pageSize)); List scimUsersUuids = toScimUsersUuids(scimUserDtos); assertThat(scimUsersUuids).containsExactlyElementsOf(expectedScimUserUuids); @@ -195,7 +196,7 @@ public class ScimUserDaoIT { insertScimUsersWithUsers(userLogins); ScimUserQuery query = ScimUserQuery.builder().userName(search).build(); - List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, 0, 100); + List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); List scimUsersUuids = toScimUsersUuids(scimUsersByQuery); assertThat(scimUsersUuids).containsExactlyElementsOf(expectedScimUserUuids); @@ -212,7 +213,7 @@ public class ScimUserDaoIT { ScimUserQuery query = ScimUserQuery.builder().groupUuid(group1dto.getUuid()).build(); - List scimUsers = scimUserDao.findScimUsers(dbSession, query, 0, 100); + List scimUsers = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); List scimUsersUuids = toScimUsersUuids(scimUsers); assertThat(scimUsersUuids).containsExactlyInAnyOrder( @@ -238,7 +239,7 @@ public class ScimUserDaoIT { ScimUserQuery query = ScimUserQuery.builder().scimUserUuids(expectedScimUserUuids).build(); - List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, 0, Integer.MAX_VALUE); + List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); List scimUsersUuids = toScimUsersUuids(scimUsersByQuery); assertThat(scimUsersByQuery) @@ -255,7 +256,7 @@ public class ScimUserDaoIT { ScimUserQuery query = ScimUserQuery.builder().scimUserUuids(scimUserUuids).userName("username_5").build(); - List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, 0, Integer.MAX_VALUE); + List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); assertThat(scimUsersByQuery).hasSize(1) .extracting(ScimUserDto::getScimUserUuid) @@ -271,7 +272,7 @@ public class ScimUserDaoIT { ScimUserQuery query = ScimUserQuery.builder().userUuids(allUsersUuid).build(); - List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, 0, Integer.MAX_VALUE); + List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); assertThat(scimUsersByQuery) .hasSameSizeAs(allUsersUuid) @@ -288,7 +289,7 @@ public class ScimUserDaoIT { ScimUserQuery query = ScimUserQuery.builder().userUuids(allUsersUuid).userName("username_5").build(); - List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, 0, Integer.MAX_VALUE); + List scimUsersByQuery = scimUserDao.findScimUsers(dbSession, query, Pagination.all()); assertThat(scimUsersByQuery).hasSize(1) .extracting(ScimUserDto::getScimUserUuid) diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/OffsetBasedPagination.java b/server/sonar-db-dao/src/main/java/org/sonar/db/OffsetBasedPagination.java new file mode 100644 index 00000000000..3ba9042b683 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/OffsetBasedPagination.java @@ -0,0 +1,89 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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; + +import java.util.Objects; + +import static com.google.common.base.Preconditions.checkArgument; + +public class OffsetBasedPagination implements Pagineable { + + private final int offset; + private final int pageSize; + + private OffsetBasedPagination(int offset, int pageSize) { + this.offset = offset; + this.pageSize = pageSize; + } + + /** + * @param offset as meant by database sql offset: how many rows will be skipped before selecting the first result. offset=0 means no element would be skipped + * @param pageSize how many rows should be returned + * @return + */ + public static OffsetBasedPagination forOffset(int offset, int pageSize) { + checkArgument(offset >= 0, "offset must be >= 0"); + checkArgument(pageSize >= 1, "page size must be >= 1"); + return new OffsetBasedPagination(offset, pageSize); + } + + /** + * @param startRowNumber index of the first element to be returned. startRowNumber = 1 means no element would be skipped + * @param pageSize how many rows should be returned + * @return + */ + public static OffsetBasedPagination forStartRowNumber(int startRowNumber, int pageSize) { + checkArgument(startRowNumber >= 1, "startRowNumber must be >= 1"); + checkArgument(pageSize >= 1, "page size must be >= 1"); + return new OffsetBasedPagination(startRowNumber - 1, pageSize); + } + + @Override + public int getStartRowNumber() { + return offset + 1; + } + + @Override + public int getOffset() { + return offset; + } + + @Override + public int getPageSize() { + return pageSize; + } + + @Override + public boolean equals(Object o) { + if (this == o) { + return true; + } + if (o == null || getClass() != o.getClass()) { + return false; + } + OffsetBasedPagination that = (OffsetBasedPagination) o; + return offset == that.offset && pageSize == that.pageSize; + } + + @Override + public int hashCode() { + return Objects.hash(offset, pageSize); + } +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/Pagination.java b/server/sonar-db-dao/src/main/java/org/sonar/db/Pagination.java index bf1f260add7..5ee079241a2 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/Pagination.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/Pagination.java @@ -24,7 +24,7 @@ import javax.annotation.concurrent.Immutable; import static com.google.common.base.Preconditions.checkArgument; @Immutable -public final class Pagination { +public final class Pagination implements Pagineable { private static final Pagination ALL = new Builder(1).andSize(Integer.MAX_VALUE); private static final Pagination FIRST = new Builder(1).andSize(1); @@ -53,14 +53,17 @@ public final class Pagination { return page; } + @Override public int getPageSize() { return pageSize; } + @Override public int getOffset() { return (page - 1) * pageSize; } + @Override public int getStartRowNumber() { return getOffset() + 1; } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/Pagineable.java b/server/sonar-db-dao/src/main/java/org/sonar/db/Pagineable.java new file mode 100644 index 00000000000..5bba0a4d0a3 --- /dev/null +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/Pagineable.java @@ -0,0 +1,30 @@ +/* + * SonarQube + * Copyright (C) 2009-2023 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; + +public interface Pagineable { + + int getStartRowNumber(); + + int getOffset(); + + int getPageSize(); + +} diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupDao.java index ae1a8be730e..e3000bef838 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupDao.java @@ -21,10 +21,10 @@ package org.sonar.db.scim; import java.util.List; import java.util.Optional; -import org.apache.ibatis.session.RowBounds; import org.sonar.core.util.UuidFactory; import org.sonar.db.Dao; import org.sonar.db.DbSession; +import org.sonar.db.Pagineable; public class ScimGroupDao implements Dao { private final UuidFactory uuidFactory; @@ -37,8 +37,8 @@ public class ScimGroupDao implements Dao { return mapper(dbSession).findAll(); } - public List findScimGroups(DbSession dbSession, ScimGroupQuery query, int offset, int limit) { - return mapper(dbSession).findScimGroups(query, new RowBounds(offset, limit)); + public List findScimGroups(DbSession dbSession, ScimGroupQuery query, Pagineable pagination) { + return mapper(dbSession).findScimGroups(query, pagination); } public Optional findByScimUuid(DbSession dbSession, String scimGroupUuid) { diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupMapper.java index ec52527f856..8d87f514e5f 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimGroupMapper.java @@ -22,7 +22,7 @@ package org.sonar.db.scim; import java.util.List; import javax.annotation.CheckForNull; import org.apache.ibatis.annotations.Param; -import org.apache.ibatis.session.RowBounds; +import org.sonar.db.Pagineable; public interface ScimGroupMapper { @@ -34,7 +34,7 @@ public interface ScimGroupMapper { @CheckForNull ScimGroupDto findByGroupUuid(@Param("groupUuid") String groupUuid); - List findScimGroups(@Param("query") ScimGroupQuery query, RowBounds rowBounds); + List findScimGroups(@Param("query") ScimGroupQuery query, @Param("pagination") Pagineable pagination); int countScimGroups(@Param("query") ScimGroupQuery query); diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserDao.java index c68481ae812..1aaa9d66473 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserDao.java @@ -23,10 +23,10 @@ import java.util.HashSet; import java.util.List; import java.util.Optional; import java.util.function.BiFunction; -import org.apache.ibatis.session.RowBounds; import org.sonar.core.util.UuidFactory; import org.sonar.db.Dao; import org.sonar.db.DbSession; +import org.sonar.db.Pagineable; import static org.sonar.api.utils.Preconditions.checkState; import static org.sonar.db.DatabaseUtils.executeLargeInputs; @@ -56,7 +56,7 @@ public class ScimUserDao implements Dao { return scimUserDto; } - public List findScimUsers(DbSession dbSession, ScimUserQuery scimUserQuery, int offset, int limit) { + public List findScimUsers(DbSession dbSession, ScimUserQuery scimUserQuery, Pagineable pagination) { checkState(scimUserQuery.getUserUuids() == null || scimUserQuery.getScimUserUuids() == null, "Only one of userUuids & scimUserUuids request parameter is supported."); if (scimUserQuery.getScimUserUuids() != null) { @@ -64,7 +64,7 @@ public class ScimUserDao implements Dao { scimUserQuery.getScimUserUuids(), partialSetOfUsers -> createPartialQuery(scimUserQuery, partialSetOfUsers, (builder, scimUserUuids) -> builder.scimUserUuids(new HashSet<>(scimUserUuids)), - dbSession, offset, limit) + dbSession, pagination) ); } if (scimUserQuery.getUserUuids() != null) { @@ -72,20 +72,21 @@ public class ScimUserDao implements Dao { scimUserQuery.getUserUuids(), partialSetOfUsers -> createPartialQuery(scimUserQuery, partialSetOfUsers, (builder, userUuids) -> builder.userUuids(new HashSet<>(userUuids)), - dbSession, offset, limit) + dbSession, pagination) ); } - return mapper(dbSession).findScimUsers(scimUserQuery, new RowBounds(offset, limit)); + + return mapper(dbSession).findScimUsers(scimUserQuery, pagination); } private static List createPartialQuery(ScimUserQuery completeQuery, List strings, BiFunction, ScimUserQuery.ScimUserQueryBuilder> queryModifier, - DbSession dbSession, int offset, int limit) { + DbSession dbSession, Pagineable pagination) { ScimUserQuery.ScimUserQueryBuilder partialScimUserQuery = ScimUserQuery.builder() .userName(completeQuery.getUserName()); partialScimUserQuery = queryModifier.apply(partialScimUserQuery, strings); - return mapper(dbSession).findScimUsers(partialScimUserQuery.build(), new RowBounds(offset, limit)); + return mapper(dbSession).findScimUsers(partialScimUserQuery.build(), pagination); } public int countScimUsers(DbSession dbSession, ScimUserQuery scimUserQuery) { diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserMapper.java index 8fe42ca6f90..87ef3285dd1 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/scim/ScimUserMapper.java @@ -22,7 +22,7 @@ package org.sonar.db.scim; import java.util.List; import javax.annotation.CheckForNull; import org.apache.ibatis.annotations.Param; -import org.apache.ibatis.session.RowBounds; +import org.sonar.db.Pagineable; public interface ScimUserMapper { @@ -36,7 +36,7 @@ public interface ScimUserMapper { void insert(@Param("scimUserDto") ScimUserDto scimUserDto); - List findScimUsers(@Param("query") ScimUserQuery scimUserQuery, RowBounds rowBounds); + List findScimUsers(@Param("query") ScimUserQuery scimUserQuery, @Param("pagination") Pagineable pagination); int countScimUsers(@Param("query") ScimUserQuery scimUserQuery); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/scim/ScimGroupMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/scim/ScimGroupMapper.xml index 779940e91f1..44fd376acaf 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/scim/ScimGroupMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/scim/ScimGroupMapper.xml @@ -39,8 +39,13 @@ where g.name = #{query.displayName,jdbcType=VARCHAR} order by s.scim_uuid asc + + + offset #{pagination.offset,jdbcType=INTEGER} rows fetch next #{pagination.pageSize,jdbcType=INTEGER} rows only + + + + + offset #{pagination.offset,jdbcType=INTEGER} rows fetch next #{pagination.pageSize,jdbcType=INTEGER} rows only + +