aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-22 20:27:45 +0300
committerGravatar Vitaly Takmazov2023-01-22 20:57:06 +0300
commit478a7bfba88401c1996d0be5a144459011b60e10 (patch)
tree2bdbf40a9910c799673874d64a87a4ef0e5dc04b /src/main/java/com/juick/service
parent9ad08dc78e0cdbe2cdebf10a96c800e72d48e48b (diff)
db refactoring: merge getMessages with isSubscribed query
Diffstat (limited to 'src/main/java/com/juick/service')
-rw-r--r--src/main/java/com/juick/service/MessagesService.java7
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java73
2 files changed, 42 insertions, 38 deletions
diff --git a/src/main/java/com/juick/service/MessagesService.java b/src/main/java/com/juick/service/MessagesService.java
index 88f79c14..6881c924 100644
--- a/src/main/java/com/juick/service/MessagesService.java
+++ b/src/main/java/com/juick/service/MessagesService.java
@@ -67,11 +67,10 @@ public interface MessagesService {
@CacheEvict(value = { "discover", "discussions", "messages", "replies" }, allEntries = true)
void setReadOnly(int mid, boolean readonly);
- boolean isSubscribed(int uid, int mid);
-
int getMessagePrivacy(int mid);
Optional<Message> getMessage(int mid);
+ Optional<Message> getMessage(int uid, int mid);
Message getReply(int mid, int rid);
@@ -121,8 +120,8 @@ public interface MessagesService {
List<Integer> getUserSearch(User visitor, int UID, String search, int privacy, int page);
- @Cacheable(value = "messages", key="{ #visitor.uid, #mids.hashCode() }")
- List<Message> getMessages(User visitor, List<Integer> mids);
+ @Cacheable(value = "messages", key="{ #uid, #mids.hashCode() }")
+ List<Message> getMessages(int uid, List<Integer> mids);
@Cacheable(value = "replies", key="{ #user.uid, #mid }")
List<Message> getReplies(User user, int mid);
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 9fe1873a..790e695e 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -114,6 +114,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY)));
msg.setHtml(rs.getBoolean(25));
msg.setUnread(rs.getBoolean(26));
+ msg.setSubscribed(rs.getBoolean("subscribed"));
if (StringUtils.isNotEmpty(msg.getAttachmentType())) {
try {
storageService.setAttachmentMetadata(baseImagesUrl, msg);
@@ -395,26 +396,19 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
- public boolean isSubscribed(final int uid, final int mid) {
- List<Integer> list = getJdbcTemplate().queryForList(
- "SELECT 1 FROM subscr_messages WHERE suser_id = ? AND message_id = ?", Integer.class, uid, mid);
-
- return !list.isEmpty() && list.get(0) == 1;
- }
-
- @Transactional(readOnly = true)
- @Override
public int getMessagePrivacy(final int mid) {
List<Integer> list = getJdbcTemplate().queryForList("SELECT privacy FROM messages WHERE message_id = ?",
Integer.class, mid);
return list.isEmpty() ? -4 : list.get(0);
}
-
+ public Optional<Message> getMessage(int mid) {
+ return getMessage(0, mid);
+ }
@Transactional(readOnly = true)
@Override
- public Optional<Message> getMessage(final int mid) {
- var messages = getMessages(AnonymousUser.INSTANCE, List.of(mid));
+ public Optional<Message> getMessage(int uid, final int mid) {
+ var messages = getMessages(uid, List.of(mid));
if (messages.size() == 1) {
var message = messages.get(0);
if (!message.getUser().isBanned()) {
@@ -781,9 +775,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
- public List<Message> getMessages(final User visitor, final List<Integer> mids) {
+ public List<Message> getMessages(int uid, final List<Integer> mids) {
if (CollectionUtils.isNotEmpty(mids)) {
-
var query = """
WITH RECURSIVE banned(message_id, reply_id)
AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids)
@@ -791,13 +784,33 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid))
UNION ALL SELECT replies.message_id, replies.reply_id FROM replies INNER JOIN banned
ON banned.reply_id = replies.replyto AND replies.reply_id != replies.replyto AND banned.message_id=replies.message_id
- WHERE replies.message_id IN (:ids)) SELECT messages.message_id, 0 as rid, 0 as replyto,
- messages.user_id,users.nick, users.banned as usr_banned, messages.ts,
- messages.readonly,messages.privacy, 1.*messages.replies-COUNT(DISTINCT banned.reply_id) as replies,
- messages.attach,COUNT(DISTINCT favorites.user_id) AS likes,messages.hidden,
- '' as tags, messages_txt.repliesby, messages_txt.txt, '' as q,
- messages.updated, 0 as to_uid, NULL as to_name, messages_txt.updated_at, '' as m_user_uri,
- '' as to_uri, '' as msg_reply_uri, 0 as html, (1.*messages.replies - subscr_messages.last_read_rid) > 0 as unread
+ WHERE replies.message_id IN (:ids))
+ SELECT messages.message_id,
+ 0 as rid,
+ 0 as replyto,
+ messages.user_id,
+ users.nick,
+ users.banned as usr_banned,
+ messages.ts,
+ messages.readonly,
+ messages.privacy,
+ 1.*messages.replies-COUNT(DISTINCT banned.reply_id) as replies,
+ messages.attach,
+ COUNT(DISTINCT favorites.user_id) AS likes,
+ messages.hidden,
+ '' as tags,
+ messages_txt.repliesby,
+ messages_txt.txt, '' as q,
+ messages.updated,
+ 0 as to_uid,
+ NULL as to_name,
+ messages_txt.updated_at,
+ '' as m_user_uri,
+ '' as to_uri,
+ '' as msg_reply_uri,
+ 0 as html,
+ (1.*messages.replies - subscr_messages.last_read_rid) > 0 as unread,
+ (SELECT EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid)) subscribed
FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id)
INNER JOIN users ON messages.user_id=users.id LEFT JOIN subscr_messages
ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid
@@ -810,31 +823,22 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
messages.readonly, messages.privacy, messages.attach, messages.hidden,
messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at,
m_user_uri, msg_reply_uri, html, subscr_messages.last_read_rid""";
-
List<Message> msgs = getNamedParameterJdbcTemplate().query(query,
- new MapSqlParameterSource("ids", mids).addValue("uid", visitor.getUid()), new MessageMapper());
-
+ new MapSqlParameterSource("ids", mids).addValue("uid", uid), new MessageMapper());
Map<Integer, Set<Reaction>> likes = updateReactionsFor(mids);
-
msgs.forEach(i -> i.setReactions(likes.get(i.getMid())));
-
msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
-
msgs.forEach(i -> i.setEntities(MessageUtils.getEntities(i)));
-
List<Pair<Integer, User>> allRecommendations = getMessagesRecommendations(mids);
-
msgs.forEach(m -> {
m.setRecommendations(new HashSet<>(allRecommendations.stream()
.filter(r -> r.getLeft().equals(m.getMid())).map(Pair::getRight).toList()));
m.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r)));
});
-
return msgs;
}
return Collections.emptyList();
}
-
private Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> mids) {
// This method always called from the transactional block, so it should not be
// marked as transactional itself
@@ -875,7 +879,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
+ "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
+ "replies.updated_at, replies.user_uri as uri, "
- + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread "
+ + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, "
+ + "0 as subscribed "
+ "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id "
+ "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id "
+ "LEFT JOIN messages_txt t on replies.message_id = t.message_id "
@@ -912,7 +917,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
public boolean deleteMessage(final int uid, final int mid) {
Instant now = Instant.now();
Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS);
- Optional<Message> message = getMessage(mid);
+ Optional<Message> message = getMessage(uid, mid);
if (message.isPresent()) {
Instant ts = message.get().getUpdatedAt();
SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid)
@@ -945,7 +950,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
public boolean deleteReply(final int uid, final int mid, final int rid) {
Instant now = Instant.now();
Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS);
- var message = getMessage(mid);
+ var message = getMessage(uid, mid);
if (message.isPresent()) {
Message reply = getReply(mid, rid);
if (reply != null) {