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 --- src/main/java/com/juick/CommandsManager.java | 2 +- src/main/java/com/juick/config/SecurityConfig.java | 1 - src/main/java/com/juick/model/Message.java | 10 +++ .../java/com/juick/service/MessagesService.java | 7 +-- .../com/juick/service/MessagesServiceImpl.java | 73 ++++++++++++---------- src/main/java/com/juick/www/api/Mastodon.java | 2 +- src/main/java/com/juick/www/api/Messages.java | 6 +- .../java/com/juick/www/api/activity/Profile.java | 2 +- src/main/java/com/juick/www/controllers/Site.java | 9 ++- src/main/java/com/juick/www/rss/Feeds.java | 4 +- src/main/resources/templates/views/thread.html | 2 +- 11 files changed, 65 insertions(+), 53 deletions(-) (limited to 'src/main') diff --git a/src/main/java/com/juick/CommandsManager.java b/src/main/java/com/juick/CommandsManager.java index e01e5020..ec89975c 100644 --- a/src/main/java/com/juick/CommandsManager.java +++ b/src/main/java/com/juick/CommandsManager.java @@ -607,7 +607,7 @@ public class CommandsManager { } String printMessages(User visitor, List mids, boolean crop) { - return messagesService.getMessages(visitor, mids).stream() + return messagesService.getMessages(visitor.getUid(), mids).stream() .sorted(Collections.reverseOrder()) .map(PlainTextFormatter::formatPostSummary).collect(Collectors.joining("\n\n")); } diff --git a/src/main/java/com/juick/config/SecurityConfig.java b/src/main/java/com/juick/config/SecurityConfig.java index e73aa65d..501a1e79 100644 --- a/src/main/java/com/juick/config/SecurityConfig.java +++ b/src/main/java/com/juick/config/SecurityConfig.java @@ -240,7 +240,6 @@ public class SecurityConfig { http.addFilterBefore(wwwAuthenticationFilter(), BasicAuthenticationFilter.class) .authorizeHttpRequests(authorize -> authorize .requestMatchers("/settings", "/pm/**", "/**/bl", "/_twitter", "/post", - "/post2", "/comment") .authenticated() .requestMatchers("/actuator/**").hasRole("ADMIN") diff --git a/src/main/java/com/juick/model/Message.java b/src/main/java/com/juick/model/Message.java index cfd2582b..d5066af6 100644 --- a/src/main/java/com/juick/model/Message.java +++ b/src/main/java/com/juick/model/Message.java @@ -90,6 +90,8 @@ public class Message implements Comparable, Serializable { private List entities; + private boolean subscribed; + public Message() { tags = new LinkedHashSet<>(); reactions = new HashSet<>(); @@ -384,4 +386,12 @@ public class Message implements Comparable, Serializable { public void setEntities(List entities) { this.entities = entities; } + + public boolean isSubscribed() { + return subscribed; + } + + public void setSubscribed(boolean subscribed) { + this.subscribed = subscribed; + } } 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 getMessage(int mid); + Optional getMessage(int uid, int mid); Message getReply(int mid, int rid); @@ -121,8 +120,8 @@ public interface MessagesService { List getUserSearch(User visitor, int UID, String search, int privacy, int page); - @Cacheable(value = "messages", key="{ #visitor.uid, #mids.hashCode() }") - List getMessages(User visitor, List mids); + @Cacheable(value = "messages", key="{ #uid, #mids.hashCode() }") + List getMessages(int uid, List mids); @Cacheable(value = "replies", key="{ #user.uid, #mid }") List 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); @@ -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) { diff --git a/src/main/java/com/juick/www/api/Mastodon.java b/src/main/java/com/juick/www/api/Mastodon.java index a190defe..91de2162 100644 --- a/src/main/java/com/juick/www/api/Mastodon.java +++ b/src/main/java/com/juick/www/api/Mastodon.java @@ -353,7 +353,7 @@ public class Mastodon { } else if (timeline.equals("home")) { mids = messagesService.getMyFeed(visitor.getUid(), before, true); } - return messagesService.getMessages(visitor, mids).stream() + return messagesService.getMessages(visitor.getUid(), mids).stream() .map(m -> { m.getUser().setAvatar(webApp.getAvatarUrl(m.getUser())); return toStatus(m); diff --git a/src/main/java/com/juick/www/api/Messages.java b/src/main/java/com/juick/www/api/Messages.java index fb10d7a7..ecdd304f 100644 --- a/src/main/java/com/juick/www/api/Messages.java +++ b/src/main/java/com/juick/www/api/Messages.java @@ -73,7 +73,7 @@ public class Messages { @RequestParam(defaultValue = "0") int before_mid) { int vuid = visitor.getUid(); List mids = messagesService.getMyFeed(vuid, before_mid, true); - List msgs = messagesService.getMessages(visitor, mids); + List msgs = messagesService.getMessages(vuid, mids); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarUrl(m.getUser()))); return msgs; } @@ -131,7 +131,7 @@ public class Messages { mids = messagesService.getAll(visitor.getUid(), before); } } - List msgs = messagesService.getMessages(visitor, mids); + List msgs = messagesService.getMessages(visitor.getUid(), mids); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarUrl(m.getUser()))); return msgs; } @@ -153,7 +153,7 @@ public class Messages { @GetMapping("/api/messages/discussions") public List getDiscussions(@Parameter(hidden = true) User visitor, @RequestParam(required = false, defaultValue = "0") Long to) { - List msgs = messagesService.getMessages(visitor, messagesService.getDiscussions(visitor.getUid(), to)); + List msgs = messagesService.getMessages(visitor.getUid(), messagesService.getDiscussions(visitor.getUid(), to)); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarUrl(m.getUser()))); return msgs; } diff --git a/src/main/java/com/juick/www/api/activity/Profile.java b/src/main/java/com/juick/www/api/activity/Profile.java index 7b440a6a..580ed340 100644 --- a/src/main/java/com/juick/www/api/activity/Profile.java +++ b/src/main/java/com/juick/www/api/activity/Profile.java @@ -141,7 +141,7 @@ public class Profile { UriComponentsBuilder uri = UriComponentsBuilder.fromUriString(baseUri); String personUri = uri.path(String.format("/u/%s", userName)).toUriString(); List mids = messagesService.getUserBlog(user.getUid(), 0, before); - List notes = messagesService.getMessages(visitor, mids).stream().map(activityPubManager::makeNote) + List notes = messagesService.getMessages(visitor.getUid(), mids).stream().map(activityPubManager::makeNote) .toList(); OrderedCollectionPage page = new OrderedCollectionPage(); page.setPartOf(uri.replacePath(String.format("/u/%s/blog/toc", userName)).toUriString()); diff --git a/src/main/java/com/juick/www/controllers/Site.java b/src/main/java/com/juick/www/controllers/Site.java index bfe7d654..d0ea3dee 100644 --- a/src/main/java/com/juick/www/controllers/Site.java +++ b/src/main/java/com/juick/www/controllers/Site.java @@ -190,7 +190,7 @@ public class Site { model.addAttribute("headers", head); model.addAttribute("visitor", visitor); model.addAttribute("noindex", !(paramShow == null && paramBefore == 0)); - List msgs = messagesService.getMessages(visitor, mids); + List msgs = messagesService.getMessages(visitor.getUid(), mids); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarWebPath(m.getUser()))); if (!visitor.isAnonymous()) { fillUserModel(model, visitor, visitor); @@ -301,7 +301,7 @@ public class Site { model.addAttribute("noindex", paramShow == null && before == 0); fillUserModel(model, user, visitor); model.addAttribute("paramTag", paramTag); - List msgs = messagesService.getMessages(visitor, mids); + List msgs = messagesService.getMessages(visitor.getUid(), mids); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarWebPath(m.getUser()))); if (!visitor.isAnonymous()) { List unread = messagesService.getUnread(visitor); @@ -423,7 +423,7 @@ public class Site { model.addAttribute("title", title); List mids = messagesService.getTag(paramTag.TID, visitor.getUid(), before, (visitor.isAnonymous()) ? 40 : 20); - List msgs = messagesService.getMessages(visitor, mids); + List msgs = messagesService.getMessages(visitor.getUid(), mids); msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarWebPath(m.getUser()))); if (!visitor.isAnonymous()) { List unread = messagesService.getUnread(visitor); @@ -504,7 +504,7 @@ public class Site { throw new HttpForbiddenException(); } visitor.setAvatar(webApp.getAvatarWebPath(visitor)); - Optional message = messagesService.getMessage(mid); + Optional message = messagesService.getMessage(visitor.getUid(), mid); if (message.isEmpty()) { throw new HttpNotFoundException(); @@ -567,7 +567,6 @@ public class Site { + msg.getTags().stream().map(Tag::getName).collect(Collectors.joining(", ")) + "\" />\n"; } model.addAttribute("headers", headers); - model.addAttribute("visitorSubscribed", messagesService.isSubscribed(visitor.getUid(), msg.getMid())); model.addAttribute("visitorInBL", userService.isInBL(msg.getUser().getUid(), visitor.getUid())); List blUIDs = new ArrayList<>(); for (Message reply : replies) { diff --git a/src/main/java/com/juick/www/rss/Feeds.java b/src/main/java/com/juick/www/rss/Feeds.java index 4e64bc5a..f19f1505 100644 --- a/src/main/java/com/juick/www/rss/Feeds.java +++ b/src/main/java/com/juick/www/rss/Feeds.java @@ -58,7 +58,7 @@ public class Feeds { modelAndView.setViewName("messagesView"); modelAndView.addObject("user", user); modelAndView.addObject("feedType", feedType.name()); - modelAndView.addObject("messages", messagesService.getMessages(visitor, mids)); + modelAndView.addObject("messages", messagesService.getMessages(visitor.getUid(), mids)); return modelAndView; } throw new HttpNotFoundException(); @@ -72,7 +72,7 @@ public class Feeds { ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("messagesView"); modelAndView.addObject("user", visitor); - modelAndView.addObject("messages", messagesService.getMessages(visitor, mids)); + modelAndView.addObject("messages", messagesService.getMessages(visitor.getUid(), mids)); return modelAndView; } @GetMapping("/rss/comments") diff --git a/src/main/resources/templates/views/thread.html b/src/main/resources/templates/views/thread.html index 759c47e1..7b68df66 100644 --- a/src/main/resources/templates/views/thread.html +++ b/src/main/resources/templates/views/thread.html @@ -73,7 +73,7 @@ {% endif %} {% if visitor.uid > 0 %} {% if visitor.uid != msg.user.uid %} - {% if visitorSubscribed %} + {% if msg.subscribed %}  {{ i18n("messages","message.subscribed") }} -- cgit v1.2.3