From 478a7bfba88401c1996d0be5a144459011b60e10 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 22 Jan 2023 20:27:45 +0300 Subject: db refactoring: merge getMessages with isSubscribed query --- .../com/juick/service/MessagesServiceImpl.java | 73 ++++++++++++---------- 1 file changed, 39 insertions(+), 34 deletions(-) (limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java') 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); @@ -393,15 +394,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ getJdbcTemplate().update("UPDATE messages SET readonly=? WHERE message_id=?", readonly, mid); } - @Transactional(readOnly = true) - @Override - public boolean isSubscribed(final int uid, final int mid) { - List 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) { @@ -410,11 +402,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return list.isEmpty() ? -4 : list.get(0); } - + public Optional getMessage(int mid) { + return getMessage(0, mid); + } @Transactional(readOnly = true) @Override - public Optional getMessage(final int mid) { - var messages = getMessages(AnonymousUser.INSTANCE, List.of(mid)); + public Optional 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 getMessages(final User visitor, final List mids) { + public List getMessages(int uid, final List 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 msgs = getNamedParameterJdbcTemplate().query(query, - new MapSqlParameterSource("ids", mids).addValue("uid", visitor.getUid()), new MessageMapper()); - + new MapSqlParameterSource("ids", mids).addValue("uid", uid), new MessageMapper()); Map> 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> 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> updateReactionsFor(final List 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 = getMessage(mid); + Optional 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) { -- cgit v1.2.3