From 17a83503dba25a0c10fcd0d9ddf8848933abc93d Mon Sep 17 00:00:00 2001 From: Pierre Date: Mon, 28 Aug 2023 17:18:13 +0200 Subject: [PATCH] SONAR-20164 replace in-memory pagination with sql pagination --- .../sonar/db/webhook/WebhookDeliveryDaoIT.java | 15 ++++++++------- .../org/sonar/db/webhook/WebhookDeliveryDao.java | 16 ++++++++-------- .../sonar/db/webhook/WebhookDeliveryMapper.java | 8 ++++---- .../sonar/db/webhook/WebhookDeliveryMapper.xml | 9 ++++++--- .../webhook/ws/WebhookDeliveriesAction.java | 8 ++++---- 5 files changed, 30 insertions(+), 26 deletions(-) diff --git a/server/sonar-db-dao/src/it/java/org/sonar/db/webhook/WebhookDeliveryDaoIT.java b/server/sonar-db-dao/src/it/java/org/sonar/db/webhook/WebhookDeliveryDaoIT.java index e57c45310f9..5f0d6214b62 100644 --- a/server/sonar-db-dao/src/it/java/org/sonar/db/webhook/WebhookDeliveryDaoIT.java +++ b/server/sonar-db-dao/src/it/java/org/sonar/db/webhook/WebhookDeliveryDaoIT.java @@ -28,6 +28,7 @@ import org.sonar.api.utils.System2; import org.sonar.db.DbClient; import org.sonar.db.DbSession; import org.sonar.db.DbTester; +import org.sonar.db.Pagination; import static com.google.common.collect.ImmutableList.of; import static org.assertj.core.api.Assertions.assertThat; @@ -56,7 +57,7 @@ public class WebhookDeliveryDaoIT { public void selectOrderedByComponentUuid_returns_empty_if_no_records() { underTest.insert(dbSession, WebhookDeliveryTesting.newDto("D1", "WEBHOOK_UUID_1", "PROJECT_1", "TASK_1")); - List deliveries = underTest.selectOrderedByProjectUuid(dbSession, "ANOTHER_PROJECT", 0, 10); + List deliveries = underTest.selectOrderedByProjectUuid(dbSession, "ANOTHER_PROJECT", Pagination.all()); assertThat(deliveries).isEmpty(); } @@ -70,7 +71,7 @@ public class WebhookDeliveryDaoIT { underTest.insert(dbSession, dto2); underTest.insert(dbSession, dto1); - List deliveries = underTest.selectOrderedByProjectUuid(dbSession, "PROJECT_1", 0, 10); + List deliveries = underTest.selectOrderedByProjectUuid(dbSession, "PROJECT_1", Pagination.all()); assertThat(deliveries).extracting(WebhookDeliveryLiteDto::getUuid).containsExactly("D2", "D1"); } @@ -79,7 +80,7 @@ public class WebhookDeliveryDaoIT { public void selectOrderedByCeTaskUuid_returns_empty_if_no_records() { underTest.insert(dbSession, WebhookDeliveryTesting.newDto("D1", "WEBHOOK_UUID_1", "PROJECT_1", "TASK_1")); - List deliveries = underTest.selectOrderedByCeTaskUuid(dbSession, "ANOTHER_TASK", 0, 10); + List deliveries = underTest.selectOrderedByCeTaskUuid(dbSession, "ANOTHER_TASK", Pagination.all()); assertThat(deliveries).isEmpty(); } @@ -93,7 +94,7 @@ public class WebhookDeliveryDaoIT { underTest.insert(dbSession, dto2); underTest.insert(dbSession, dto1); - List deliveries = underTest.selectOrderedByCeTaskUuid(dbSession, "TASK_1", 0, 10); + List deliveries = underTest.selectOrderedByCeTaskUuid(dbSession, "TASK_1", Pagination.all()); assertThat(deliveries).extracting(WebhookDeliveryLiteDto::getUuid).containsExactly("D2", "D1"); } @@ -103,7 +104,7 @@ public class WebhookDeliveryDaoIT { underTest.insert(dbSession, WebhookDeliveryTesting.newDto("D1", "WEBHOOK_UUID_1", "PROJECT_1", "TASK_1")); - List deliveries = underTest.selectByWebhookUuid(dbSession, "a-webhook-uuid", 0, 10); + List deliveries = underTest.selectByWebhookUuid(dbSession, "a-webhook-uuid", Pagination.all()); assertThat(deliveries).isEmpty(); } @@ -118,7 +119,7 @@ public class WebhookDeliveryDaoIT { underTest.insert(dbSession, dto2); underTest.insert(dbSession, dto1); - List deliveries = underTest.selectByWebhookUuid(dbSession, webhookDto.getUuid(), 0, 10); + List deliveries = underTest.selectByWebhookUuid(dbSession, webhookDto.getUuid(), Pagination.all()); assertThat(deliveries).extracting(WebhookDeliveryLiteDto::getUuid).containsExactly("D2", "D1"); } @@ -133,7 +134,7 @@ public class WebhookDeliveryDaoIT { underTest.insert(dbSession, WebhookDeliveryTesting.newDto("D5", webhookDto.getUuid(), "PROJECT_1", "TASK_2").setCreatedAt(NOW - 1_000L)); underTest.insert(dbSession, WebhookDeliveryTesting.newDto("D6", webhookDto.getUuid(), "PROJECT_1", "TASK_2").setCreatedAt(NOW)); - List deliveries = underTest.selectByWebhookUuid(dbSession, webhookDto.getUuid(), 2, 2); + List deliveries = underTest.selectByWebhookUuid(dbSession, webhookDto.getUuid(), Pagination.forPage(2).andSize(2)); assertThat(deliveries).extracting(WebhookDeliveryLiteDto::getUuid).containsExactly("D4", "D3"); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryDao.java b/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryDao.java index d71ab8370c8..565ad0deb7e 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryDao.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryDao.java @@ -22,9 +22,9 @@ package org.sonar.db.webhook; import java.util.List; import java.util.Map; import java.util.Optional; -import org.apache.ibatis.session.RowBounds; import org.sonar.db.Dao; import org.sonar.db.DbSession; +import org.sonar.db.Pagination; import static java.util.function.Function.identity; import static java.util.stream.Collectors.toMap; @@ -42,8 +42,8 @@ public class WebhookDeliveryDao implements Dao { /** * All the deliveries for the specified webhook. Results are ordered by descending date. */ - public List selectByWebhookUuid(DbSession dbSession, String webhookUuid, int offset, int limit) { - return mapper(dbSession).selectByWebhookUuid(webhookUuid, new RowBounds(offset, limit)); + public List selectByWebhookUuid(DbSession dbSession, String webhookUuid, Pagination pagination) { + return mapper(dbSession).selectByWebhookUuid(webhookUuid, pagination); } public int countDeliveriesByProjectUuid(DbSession dbSession, String projectUuid) { @@ -53,8 +53,8 @@ public class WebhookDeliveryDao implements Dao { /** * All the deliveries for the specified project. Results are ordered by descending date. */ - public List selectOrderedByProjectUuid(DbSession dbSession, String projectUuid, int offset, int limit) { - return mapper(dbSession).selectOrderedByProjectUuid(projectUuid, new RowBounds(offset, limit)); + public List selectOrderedByProjectUuid(DbSession dbSession, String projectUuid, Pagination pagination) { + return mapper(dbSession).selectOrderedByProjectUuid(projectUuid, pagination); } public int countDeliveriesByCeTaskUuid(DbSession dbSession, String ceTaskId) { @@ -64,8 +64,8 @@ public class WebhookDeliveryDao implements Dao { /** * All the deliveries for the specified CE task. Results are ordered by descending date. */ - public List selectOrderedByCeTaskUuid(DbSession dbSession, String ceTaskUuid, int offset, int limit) { - return mapper(dbSession).selectOrderedByCeTaskUuid(ceTaskUuid, new RowBounds(offset, limit)); + public List selectOrderedByCeTaskUuid(DbSession dbSession, String ceTaskUuid, Pagination pagination) { + return mapper(dbSession).selectOrderedByCeTaskUuid(ceTaskUuid, pagination); } public void insert(DbSession dbSession, WebhookDeliveryDto dto) { @@ -78,7 +78,7 @@ public class WebhookDeliveryDao implements Dao { public Map selectLatestDeliveries(DbSession dbSession, List webhooks) { return webhooks.stream() - .flatMap(webhook -> selectByWebhookUuid(dbSession, webhook.getUuid(),0,1).stream()) + .flatMap(webhook -> selectByWebhookUuid(dbSession, webhook.getUuid(), Pagination.forPage(1).andSize(1)).stream()) .collect(toMap(WebhookDeliveryLiteDto::getWebhookUuid, identity())); } diff --git a/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryMapper.java b/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryMapper.java index 66f4dead983..7a1764f78d1 100644 --- a/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryMapper.java +++ b/server/sonar-db-dao/src/main/java/org/sonar/db/webhook/WebhookDeliveryMapper.java @@ -22,7 +22,7 @@ package org.sonar.db.webhook; import java.util.List; import javax.annotation.CheckForNull; import org.apache.ibatis.annotations.Param; -import org.apache.ibatis.session.RowBounds; +import org.sonar.db.Pagination; public interface WebhookDeliveryMapper { @@ -31,15 +31,15 @@ public interface WebhookDeliveryMapper { int countByWebhookUuid(@Param("webhookUuid") String webhookUuid); - List selectByWebhookUuid(@Param("webhookUuid") String webhookUuid, RowBounds rowBounds); + List selectByWebhookUuid(@Param("webhookUuid") String webhookUuid, @Param("pagination") Pagination pagination); int countByProjectUuid(@Param("projectUuid") String projectUuid); - List selectOrderedByProjectUuid(@Param("projectUuid") String projectUuid, RowBounds rowBounds); + List selectOrderedByProjectUuid(@Param("projectUuid") String projectUuid, @Param("pagination") Pagination pagination); int countByCeTaskUuid(@Param("ceTaskUuid") String ceTaskId); - List selectOrderedByCeTaskUuid(@Param("ceTaskUuid") String ceTaskUuid, RowBounds rowBounds); + List selectOrderedByCeTaskUuid(@Param("ceTaskUuid") String ceTaskUuid, @Param("pagination") Pagination pagination); void insert(WebhookDeliveryDto dto); diff --git a/server/sonar-db-dao/src/main/resources/org/sonar/db/webhook/WebhookDeliveryMapper.xml b/server/sonar-db-dao/src/main/resources/org/sonar/db/webhook/WebhookDeliveryMapper.xml index cbb17b6a747..62796241a06 100644 --- a/server/sonar-db-dao/src/main/resources/org/sonar/db/webhook/WebhookDeliveryMapper.xml +++ b/server/sonar-db-dao/src/main/resources/org/sonar/db/webhook/WebhookDeliveryMapper.xml @@ -33,11 +33,12 @@ where webhook_uuid = #{webhookUuid,jdbcType=VARCHAR} - select from webhook_deliveries where webhook_uuid = #{webhookUuid,jdbcType=VARCHAR} order by created_at desc + offset (#{pagination.startRowNumber,jdbcType=INTEGER}-1) rows fetch next #{pagination.pageSize,jdbcType=INTEGER} rows only - select from webhook_deliveries where project_uuid = #{projectUuid,jdbcType=VARCHAR} order by created_at desc + offset (#{pagination.startRowNumber,jdbcType=INTEGER}-1) rows fetch next #{pagination.pageSize,jdbcType=INTEGER} rows only - select from webhook_deliveries where ce_task_uuid = #{ceTaskUuid,jdbcType=VARCHAR} order by created_at desc + offset (#{pagination.startRowNumber,jdbcType=INTEGER}-1) rows fetch next #{pagination.pageSize,jdbcType=INTEGER} rows only