This is an automated email from the ASF dual-hosted git repository.
rcordier pushed a commit to branch postgresql
in repository https://gitbox.apache.org/repos/asf/james-project.git
The following commit(s) were added to refs/heads/postgresql by this push:
new 7239b59a79 JAMES-2586 Avoid Using COUNT() in SQL When You Could Use
EXISTS()
7239b59a79 is described below
commit 7239b59a7947c40897865cbb0d838fcbf77cf779
Author: Tung Tran <[email protected]>
AuthorDate: Sun Feb 18 11:41:02 2024 +0700
JAMES-2586 Avoid Using COUNT() in SQL When You Could Use EXISTS()
---
.../james/backends/postgres/utils/PostgresExecutor.java | 9 +++++++++
.../org/apache/james/events/PostgresEventDeadLetters.java | 6 ++----
.../apache/james/mailbox/postgres/DeleteMessageListener.java | 7 +++----
.../james/mailbox/postgres/mail/dao/PostgresMailboxDAO.java | 12 ++++--------
.../mailbox/postgres/mail/dao/PostgresMailboxMessageDAO.java | 9 ++++-----
.../pushsubscription/PostgresPushSubscriptionDAO.java | 10 ++++------
.../apache/james/sieve/postgres/PostgresSieveScriptDAO.java | 7 +++----
.../org/apache/james/user/postgres/PostgresUsersDAO.java | 7 ++++++-
.../vacation/postgres/PostgresNotificationRegistryDAO.java | 5 ++---
9 files changed, 37 insertions(+), 35 deletions(-)
diff --git
a/backends-common/postgres/src/main/java/org/apache/james/backends/postgres/utils/PostgresExecutor.java
b/backends-common/postgres/src/main/java/org/apache/james/backends/postgres/utils/PostgresExecutor.java
index 889c815115..37d3726e14 100644
---
a/backends-common/postgres/src/main/java/org/apache/james/backends/postgres/utils/PostgresExecutor.java
+++
b/backends-common/postgres/src/main/java/org/apache/james/backends/postgres/utils/PostgresExecutor.java
@@ -19,6 +19,9 @@
package org.apache.james.backends.postgres.utils;
+import static org.jooq.impl.DSL.exists;
+import static org.jooq.impl.DSL.field;
+
import java.time.Duration;
import java.util.Optional;
import java.util.function.Function;
@@ -32,6 +35,7 @@ import org.jooq.DeleteResultStep;
import org.jooq.Record;
import org.jooq.Record1;
import org.jooq.SQLDialect;
+import org.jooq.SelectConditionStep;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
@@ -129,6 +133,11 @@ public class PostgresExecutor {
.map(Record1::value1);
}
+ public Mono<Boolean> executeExists(Function<DSLContext,
SelectConditionStep<?>> queryFunction) {
+ return executeRow(dslContext ->
Mono.from(dslContext.select(field(exists(queryFunction.apply(dslContext))))))
+ .map(record -> record.get(0, Boolean.class));
+ }
+
public Mono<Long> executeReturnAffectedRowsCount(Function<DSLContext,
Mono<Integer>> queryFunction) {
return dslContext()
.flatMap(queryFunction)
diff --git
a/event-bus/postgres/src/main/java/org/apache/james/events/PostgresEventDeadLetters.java
b/event-bus/postgres/src/main/java/org/apache/james/events/PostgresEventDeadLetters.java
index be6db0c7be..540400266a 100644
---
a/event-bus/postgres/src/main/java/org/apache/james/events/PostgresEventDeadLetters.java
+++
b/event-bus/postgres/src/main/java/org/apache/james/events/PostgresEventDeadLetters.java
@@ -111,10 +111,8 @@ public class PostgresEventDeadLetters implements
EventDeadLetters {
@Override
public Mono<Boolean> containEvents() {
- return postgresExecutor.executeRow(dslContext -> Mono.from(dslContext
- .select(INSERTION_ID)
+ return postgresExecutor.executeExists(dslContext ->
dslContext.selectOne()
.from(TABLE_NAME)
- .limit(1)))
- .hasElement();
+ .where());
}
}
diff --git
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/DeleteMessageListener.java
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/DeleteMessageListener.java
index 79f739b0e0..22826c0cbf 100644
---
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/DeleteMessageListener.java
+++
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/DeleteMessageListener.java
@@ -38,6 +38,7 @@ import
org.apache.james.mailbox.postgres.mail.dao.PostgresAttachmentDAO;
import org.apache.james.mailbox.postgres.mail.dao.PostgresMailboxMessageDAO;
import org.apache.james.mailbox.postgres.mail.dao.PostgresMessageDAO;
import org.apache.james.mailbox.postgres.mail.dao.PostgresThreadDAO;
+import org.apache.james.util.FunctionalUtils;
import org.apache.james.util.ReactorUtils;
import org.reactivestreams.Publisher;
@@ -157,10 +158,8 @@ public class DeleteMessageListener implements
EventListener.ReactiveGroupEventLi
}
private Mono<Boolean> isUnreferenced(PostgresMessageId id,
PostgresMailboxMessageDAO postgresMailboxMessageDAO) {
- return postgresMailboxMessageDAO.countByMessageId(id)
- .filter(count -> count == 0)
- .map(count -> true)
- .defaultIfEmpty(false);
+ return postgresMailboxMessageDAO.existsByMessageId(id)
+ .map(FunctionalUtils.negate());
}
private Mono<Void> deleteAttachment(PostgresMessageId messageId,
PostgresAttachmentDAO attachmentDAO) {
diff --git
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxDAO.java
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxDAO.java
index cedcfb12af..e4f1a6f71d 100644
---
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxDAO.java
+++
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxDAO.java
@@ -30,7 +30,6 @@ import static
org.apache.james.mailbox.postgres.mail.PostgresMailboxModule.Postg
import static
org.apache.james.mailbox.postgres.mail.PostgresMailboxModule.PostgresMailboxTable.TABLE_NAME;
import static
org.apache.james.mailbox.postgres.mail.PostgresMailboxModule.PostgresMailboxTable.USER_NAME;
import static org.jooq.impl.DSL.coalesce;
-import static org.jooq.impl.DSL.count;
import java.util.LinkedHashMap;
import java.util.Map;
@@ -199,13 +198,10 @@ public class PostgresMailboxDAO {
public Mono<Boolean> hasChildren(Mailbox mailbox, char delimiter) {
String name = mailbox.getName() + delimiter + SQL_WILDCARD_CHAR;
- return postgresExecutor.executeRows(dsl ->
Flux.from(dsl.select(count()).from(TABLE_NAME)
- .where(MAILBOX_NAME.like(name)
- .and(USER_NAME.eq(mailbox.getUser().asString()))
- .and(MAILBOX_NAMESPACE.eq(mailbox.getNamespace())))))
- .map(record -> record.get(0, Integer.class))
- .filter(count -> count > 0)
- .hasElements();
+ return postgresExecutor.executeExists(dsl ->
dsl.selectOne().from(TABLE_NAME)
+ .where(MAILBOX_NAME.like(name)
+ .and(USER_NAME.eq(mailbox.getUser().asString()))
+ .and(MAILBOX_NAMESPACE.eq(mailbox.getNamespace()))));
}
public Flux<PostgresMailbox> getAll() {
diff --git
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxMessageDAO.java
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxMessageDAO.java
index fd51fcc2fb..e3018fc014 100644
---
a/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxMessageDAO.java
+++
b/mailbox/postgres/src/main/java/org/apache/james/mailbox/postgres/mail/dao/PostgresMailboxMessageDAO.java
@@ -385,11 +385,10 @@ public class PostgresMailboxMessageDAO {
.map(RECORD_TO_MESSAGE_UID_FUNCTION);
}
- public Mono<Long> countByMessageId(PostgresMessageId messageId) {
- return postgresExecutor.executeRow(dslContext ->
Mono.from(dslContext.selectCount()
- .from(TABLE_NAME)
- .where(MESSAGE_ID.eq(messageId.asUuid()))))
- .map(record -> record.get(0, Long.class));
+ public Mono<Boolean> existsByMessageId(PostgresMessageId messageId) {
+ return postgresExecutor.executeExists(dslContext ->
dslContext.selectOne()
+ .from(TABLE_NAME)
+ .where(MESSAGE_ID.eq(messageId.asUuid())));
}
public Flux<ComposedMessageIdWithMetaData>
findMessagesMetadata(PostgresMailboxId mailboxId, MessageRange range) {
diff --git
a/server/data/data-jmap-postgres/src/main/java/org/apache/james/jmap/postgres/pushsubscription/PostgresPushSubscriptionDAO.java
b/server/data/data-jmap-postgres/src/main/java/org/apache/james/jmap/postgres/pushsubscription/PostgresPushSubscriptionDAO.java
index 69f0abf41c..0c611859c2 100644
---
a/server/data/data-jmap-postgres/src/main/java/org/apache/james/jmap/postgres/pushsubscription/PostgresPushSubscriptionDAO.java
+++
b/server/data/data-jmap-postgres/src/main/java/org/apache/james/jmap/postgres/pushsubscription/PostgresPushSubscriptionDAO.java
@@ -136,12 +136,10 @@ public class PostgresPushSubscriptionDAO {
}
public Mono<Boolean> existDeviceClientId(Username username, String
deviceClientId) {
- return postgresExecutor.executeRow(dslContext ->
Mono.from(dslContext.select(PushSubscriptionTable.DEVICE_CLIENT_ID)
- .from(PushSubscriptionTable.TABLE_NAME)
- .where(PushSubscriptionTable.USER.eq(username.asString()))
- .and(PushSubscriptionTable.DEVICE_CLIENT_ID.eq(deviceClientId))
- .limit(1)))
- .hasElement();
+ return postgresExecutor.executeExists(dslContext ->
dslContext.selectOne()
+ .from(PushSubscriptionTable.TABLE_NAME)
+ .where(PushSubscriptionTable.USER.eq(username.asString()))
+ .and(PushSubscriptionTable.DEVICE_CLIENT_ID.eq(deviceClientId)));
}
private PushSubscription recordAsPushSubscription(Record record) {
diff --git
a/server/data/data-postgres/src/main/java/org/apache/james/sieve/postgres/PostgresSieveScriptDAO.java
b/server/data/data-postgres/src/main/java/org/apache/james/sieve/postgres/PostgresSieveScriptDAO.java
index a1d8b93b49..88ff9c4034 100644
---
a/server/data/data-postgres/src/main/java/org/apache/james/sieve/postgres/PostgresSieveScriptDAO.java
+++
b/server/data/data-postgres/src/main/java/org/apache/james/sieve/postgres/PostgresSieveScriptDAO.java
@@ -94,11 +94,10 @@ public class PostgresSieveScriptDAO {
}
public Mono<Boolean> scriptExists(Username username, ScriptName
scriptName) {
- return postgresExecutor.executeCount(dslContext ->
Mono.from(dslContext.selectCount()
+ return postgresExecutor.executeExists(dslContext ->
dslContext.selectOne()
.from(TABLE_NAME)
- .where(USERNAME.eq(username.asString()),
- SCRIPT_NAME.eq(scriptName.getValue()))))
- .map(count -> count > 0);
+ .where(USERNAME.eq(username.asString()),
+ SCRIPT_NAME.eq(scriptName.getValue())));
}
public Flux<PostgresSieveScript> getScripts(Username username) {
diff --git
a/server/data/data-postgres/src/main/java/org/apache/james/user/postgres/PostgresUsersDAO.java
b/server/data/data-postgres/src/main/java/org/apache/james/user/postgres/PostgresUsersDAO.java
index d0467bf847..0b58bf0b9b 100644
---
a/server/data/data-postgres/src/main/java/org/apache/james/user/postgres/PostgresUsersDAO.java
+++
b/server/data/data-postgres/src/main/java/org/apache/james/user/postgres/PostgresUsersDAO.java
@@ -116,7 +116,12 @@ public class PostgresUsersDAO implements UsersDAO {
@Override
public boolean contains(Username name) {
- return getUserByName(name).isPresent();
+ return containsReactive(name).block();
+ }
+
+ @Override
+ public Mono<Boolean> containsReactive(Username name) {
+ return postgresExecutor.executeExists(dsl ->
dsl.selectOne().from(TABLE_NAME).where(USERNAME.eq(name.asString())));
}
@Override
diff --git
a/server/data/data-postgres/src/main/java/org/apache/james/vacation/postgres/PostgresNotificationRegistryDAO.java
b/server/data/data-postgres/src/main/java/org/apache/james/vacation/postgres/PostgresNotificationRegistryDAO.java
index 4638ad9805..8ae01ce36f 100644
---
a/server/data/data-postgres/src/main/java/org/apache/james/vacation/postgres/PostgresNotificationRegistryDAO.java
+++
b/server/data/data-postgres/src/main/java/org/apache/james/vacation/postgres/PostgresNotificationRegistryDAO.java
@@ -57,12 +57,11 @@ public class PostgresNotificationRegistryDAO {
public Mono<Boolean> isRegistered(AccountId accountId, RecipientId
recipientId) {
LocalDateTime currentUTCTime =
zonedDateTimeProvider.get().withZoneSameInstant(ZoneOffset.UTC).toLocalDateTime();
- return postgresExecutor.executeRow(dsl ->
Mono.from(dsl.select(ACCOUNT_ID)
+ return postgresExecutor.executeExists(dsl -> dsl.selectOne()
.from(TABLE_NAME)
.where(ACCOUNT_ID.eq(accountId.getIdentifier()),
RECIPIENT_ID.eq(recipientId.getAsString()),
- EXPIRY_DATE.ge(currentUTCTime).or(EXPIRY_DATE.isNull()))))
- .hasElement();
+ EXPIRY_DATE.ge(currentUTCTime).or(EXPIRY_DATE.isNull())));
}
public Mono<Void> flush(AccountId accountId) {
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
