From 9ad08dc78e0cdbe2cdebf10a96c800e72d48e48b Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 22 Jan 2023 14:14:58 +0300 Subject: Refactor messages service, remove 2 unneeded SQL queries --- .../com/juick/service/MessagesServiceImpl.java | 309 ++++++++++----------- 1 file changed, 140 insertions(+), 169 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 add8db22..9fe1873a 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -278,7 +278,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ void updateRepliesBy(int mid) { List users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies " + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? " - + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, mid); + + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, + mid); String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(",")); getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid); } @@ -413,33 +414,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public Optional getMessage(final int mid) { - - List list = getJdbcTemplate().query("SELECT messages.message_id as mid, 0 as rid, 0 as replyto, " - + "messages.user_id as uid, users.nick, users.banned as banned, " + "" + "messages.ts," - + "messages.readonly, messages.privacy, messages.replies," - + "messages.attach, COUNT(DISTINCT favorites.user_id) as likes, messages.hidden," - + "'' as tags, txt.repliesby, txt.txt, '' as q, messages.updated as updated, 0 as to_uid, " - + "NULL as to_name, txt.updated_at, '' as reply_user_uri, '' as to_uri, '' as reply_uri, 0 as html, 0 as unread FROM messages " - + "INNER JOIN users ON messages.user_id = users.id " + "INNER JOIN messages_txt AS txt " - + "ON messages.message_id = txt.message_id " + "LEFT JOIN favorites " - + "ON messages.message_id = favorites.message_id AND favorites.like_id=1 " - + "LEFT JOIN messages_tags ON messages_tags.message_id=txt.message_id " - + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id " - + "WHERE messages.message_id = ? AND users.banned = 0 " - + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, " - + "privacy, replies, attach, repliesby, q, txt.txt, updated_at, reply_user_uri, to_uri, reply_uri, html, unread", - new MessageMapper(), mid); - if (!list.isEmpty()) { - final Message message = list.get(0); - Map> reactionStats = updateReactionsFor(Collections.singletonList(mid)); - message.setReactions(reactionStats.get(message.getMid())); - message.setEntities(MessageUtils.getEntities(message)); - List> allRecommendations = getMessagesRecommendations(List.of(mid)); - - message.setRecommendations(new HashSet<>(allRecommendations.stream() - .filter(r -> r.getLeft().equals(mid)).map(Pair::getRight).toList())); - message.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r))); - return Optional.of(message); + var messages = getMessages(AnonymousUser.INSTANCE, List.of(mid)); + if (messages.size() == 1) { + var message = messages.get(0); + if (!message.getUser().isBanned()) { + return Optional.of(messages.get(0)); + } } return Optional.empty(); } @@ -461,47 +441,47 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ WHERE replies.message_id = ? AND replies.reply_id = ? """; List list = getJdbcTemplate().query(sql, (rs, num) -> { - Message msg = new Message(); - - msg.setMid(mid); - msg.setRid(rid); - msg.setUser(new User()); - msg.getUser().setUid(rs.getInt(1)); - msg.getUser().setName(rs.getString(2)); - if (msg.getUser().getUid() == 0) { - msg.getUser().setName(AnonymousUser.INSTANCE.getName()); - msg.getUser() - .setUri(URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY))); - } - msg.setReplyto(rs.getInt(3)); - msg.setCreated(rs.getTimestamp(4).toInstant()); - msg.setAttachmentType(rs.getString(5)); - msg.setText(rs.getString(6)); - String quote = rs.getString(7); - - if (!StringUtils.isEmpty(quote)) { - msg.setReplyQuote(MessageUtils.formatQuote(quote)); - } - int quoteUid = rs.getInt(8); - User quoteUser = new User(); - quoteUser.setUid(quoteUid); - quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName())); - quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY))); - msg.setTo(quoteUser); - msg.setUpdatedAt(rs.getTimestamp(10).toInstant()); - msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY))); - msg.setHtml(rs.getBoolean(14)); - msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY))); - if (StringUtils.isNotEmpty(msg.getAttachmentType())) { - try { - storageService.setAttachmentMetadata(baseImagesUrl, msg); - } catch (Exception e) { - logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e); - } - } - msg.setEntities(MessageUtils.getEntities(msg)); - return msg; - }, mid, rid); + Message msg = new Message(); + + msg.setMid(mid); + msg.setRid(rid); + msg.setUser(new User()); + msg.getUser().setUid(rs.getInt(1)); + msg.getUser().setName(rs.getString(2)); + if (msg.getUser().getUid() == 0) { + msg.getUser().setName(AnonymousUser.INSTANCE.getName()); + msg.getUser() + .setUri(URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY))); + } + msg.setReplyto(rs.getInt(3)); + msg.setCreated(rs.getTimestamp(4).toInstant()); + msg.setAttachmentType(rs.getString(5)); + msg.setText(rs.getString(6)); + String quote = rs.getString(7); + + if (!StringUtils.isEmpty(quote)) { + msg.setReplyQuote(MessageUtils.formatQuote(quote)); + } + int quoteUid = rs.getInt(8); + User quoteUser = new User(); + quoteUser.setUid(quoteUid); + quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName())); + quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY))); + msg.setTo(quoteUser); + msg.setUpdatedAt(rs.getTimestamp(10).toInstant()); + msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY))); + msg.setHtml(rs.getBoolean(14)); + msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY))); + if (StringUtils.isNotEmpty(msg.getAttachmentType())) { + try { + storageService.setAttachmentMetadata(baseImagesUrl, msg); + } catch (Exception e) { + logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e); + } + } + msg.setEntities(MessageUtils.getEntities(msg)); + return msg; + }, mid, rid); return list.isEmpty() ? null : list.get(0); } @@ -513,22 +493,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return replies.isEmpty() ? null : replies.get(0); } - @Transactional(readOnly = true) - @Override - public User getMessageAuthor(final int mid) { - List list = getJdbcTemplate().query( - "SELECT messages.user_id, users.nick " - + "FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.message_id = ?", - (rs, num) -> { - User res = new User(); - res.setUid(rs.getInt(1)); - res.setName(rs.getString(2)); - return res; - }, mid); - - return list.isEmpty() ? null : list.get(0); - } - @Transactional(readOnly = true) @Override public List> getMessagesRecommendations(final Collection mids) { @@ -820,31 +784,32 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ public List getMessages(final User visitor, 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) " - + "AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " - + "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, 0 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 " - + "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 " - + "LEFT JOIN favorites " + "ON messages.message_id = favorites.message_id AND favorites.like_id=1 " - + "LEFT JOIN banned " + "ON messages.message_id = banned.message_id " - + "LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id " - + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id " - + "WHERE messages.message_id IN (:ids) GROUP BY " - + "messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, " - + "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"; + var query = """ + WITH RECURSIVE banned(message_id, reply_id) + AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) + AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) + 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 + 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 + LEFT JOIN favorites ON messages.message_id = favorites.message_id AND favorites.like_id=1 + LEFT JOIN banned ON messages.message_id = banned.message_id + LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id + LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id + WHERE messages.message_id IN (:ids) GROUP BY + messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, + 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()); @@ -871,12 +836,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ } private Map> updateReactionsFor(final List mids) { - // This method always called from the transactional block, so it should not be marked as transactional itself + // This method always called from the transactional block, so it should not be + // marked as transactional itself return getNamedParameterJdbcTemplate().query(""" - select f.message_id as mid, f.like_id as lid, - r.description as descr, count(f.like_id) as cnt - from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id - where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""", + select f.message_id as mid, f.like_id as lid, + r.description as descr, count(f.like_id) as cnt + from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id + where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""", new MapSqlParameterSource("mids", mids), (ResultSet rs) -> { Map> results = new HashMap<>(); while (rs.next()) { @@ -899,27 +865,28 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getReplies(final User user, final int mid) { List replies = getNamedParameterJdbcTemplate().query("WITH RECURSIVE banned(reply_id, user_id) AS (" - + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " - + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " - + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " - + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " - + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " - + "replies.user_id, users.nick, users.banned, " + "replies.ts, " - + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, " - + "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 " - + "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 " - + "LEFT JOIN messages m on replies.message_id = m.message_id " - + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " - + "WHERE replies.message_id = :mid " - + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" - + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " - + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " - + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource() + + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " + + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " + + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " + + "replies.user_id, users.nick, users.banned, " + "replies.ts, " + + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, " + + "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 " + + "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 " + + "LEFT JOIN messages m on replies.message_id = m.message_id " + + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " + + "WHERE replies.message_id = :mid " + + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" + + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " + + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " + + "ORDER BY replies.reply_id ASC", + new MapSqlParameterSource() .addValue("mid", mid) .addValue("uid", user.getUid()) .addValue("now", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE), @@ -978,34 +945,37 @@ 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); - Message reply = getReply(mid, rid); - if (reply != null) { - Instant ts = reply.getUpdatedAt(); - User author = getMessageAuthor(mid); - SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) - .addValue("rid", rid).addValue("archive_uid", archiveUser.getUid()); - if (ts.compareTo(messageDeletingWindow) >= 0) { - boolean result; - // allow to delete author replies and replies to author - if (author.getUid() == uid) { - result = getNamedParameterJdbcTemplate() - .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", parameters) > 0; + var message = getMessage(mid); + if (message.isPresent()) { + Message reply = getReply(mid, rid); + if (reply != null) { + Instant ts = reply.getUpdatedAt(); + User author = message.get().getUser(); + SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) + .addValue("rid", rid).addValue("archive_uid", archiveUser.getUid()); + if (ts.compareTo(messageDeletingWindow) >= 0) { + boolean result; + // allow to delete author replies and replies to author + if (author.getUid() == uid) { + result = getNamedParameterJdbcTemplate() + .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", parameters) > 0; + } else { + result = getNamedParameterJdbcTemplate().update( + "DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", + parameters) > 0; + } + if (result) { + getNamedParameterJdbcTemplate() + .update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", parameters); + updateRepliesBy(mid); + return true; + } } else { - result = getNamedParameterJdbcTemplate().update( - "DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", + // only archive author replies + return getNamedParameterJdbcTemplate().update( + "UPDATE replies SET user_id=:archive_uid WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", parameters) > 0; } - if (result) { - getNamedParameterJdbcTemplate() - .update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", parameters); - updateRepliesBy(mid); - return true; - } - } else { - // only archive author replies - return getNamedParameterJdbcTemplate().update( - "UPDATE replies SET user_id=:archive_uid WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", - parameters) > 0; } } return false; @@ -1030,15 +1000,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ public List getLastReplies(int hours) { var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC); return getNamedParameterJdbcTemplate().query(""" - SELECT users2.nick,replies.message_id,replies.reply_id, - users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html - FROM ((replies INNER JOIN users ON replies.user_id=users.id) - INNER JOIN messages ON replies.message_id=messages.message_id) - INNER JOIN users AS users2 ON messages.user_id=users2.id - WHERE replies.ts>:datetime AND messages.privacy>0 - """, + SELECT users2.nick,replies.message_id,replies.reply_id, + users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html + FROM ((replies INNER JOIN users ON replies.user_id=users.id) + INNER JOIN messages ON replies.message_id=messages.message_id) + INNER JOIN users AS users2 ON messages.user_id=users2.id + WHERE replies.ts>:datetime AND messages.privacy>0 + """, new MapSqlParameterSource() - .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE), (rs, rowNum) -> { + .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE), + (rs, rowNum) -> { ResponseReply reply = new ResponseReply(); reply.setMuname(rs.getString(1)); reply.setMid(rs.getInt(2)); -- cgit v1.2.3