db refactoring: merge getMessages with isSubscribed query

11 files changed, 65 insertions, 53 deletions

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<Integer> 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<Message>, Serializable {
 
     private List<Entity> entities;
 
+    private boolean subscribed;
+
     public Message() {
         tags = new LinkedHashSet<>();
         reactions = new HashSet<>();
@@ -384,4 +386,12 @@ public class Message implements Comparable<Message>, Serializable {
     public void setEntities(List<Entity> 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<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) {
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<Integer> mids = messagesService.getMyFeed(vuid, before_mid, true);
-        List<Message> msgs = messagesService.getMessages(visitor, mids);
+        List<Message> 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<Message> msgs = messagesService.getMessages(visitor, mids);
+        List<Message> 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<Message> getDiscussions(@Parameter(hidden = true) User visitor,
             @RequestParam(required = false, defaultValue = "0") Long to) {
-        List<Message> msgs = messagesService.getMessages(visitor, messagesService.getDiscussions(visitor.getUid(), to));
+        List<Message> 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<Integer> mids = messagesService.getUserBlog(user.getUid(), 0, before);
-            List<Note> notes = messagesService.getMessages(visitor, mids).stream().map(activityPubManager::makeNote)
+            List<Note> 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<Message> msgs = messagesService.getMessages(visitor, mids);
+        List<Message> 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<Message> msgs = messagesService.getMessages(visitor, mids);
+        List<Message> msgs = messagesService.getMessages(visitor.getUid(), mids);
         msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarWebPath(m.getUser())));
         if (!visitor.isAnonymous()) {
             List<Integer> unread = messagesService.getUnread(visitor);
@@ -423,7 +423,7 @@ public class Site {
         model.addAttribute("title", title);
         List<Integer> mids = messagesService.getTag(paramTag.TID, visitor.getUid(), before,
                 (visitor.isAnonymous()) ? 40 : 20);
-        List<Message> msgs = messagesService.getMessages(visitor, mids);
+        List<Message> msgs = messagesService.getMessages(visitor.getUid(), mids);
         msgs.forEach(m -> m.getUser().setAvatar(webApp.getAvatarWebPath(m.getUser())));
         if (!visitor.isAnonymous()) {
             List<Integer> unread = messagesService.getUnread(visitor);
@@ -504,7 +504,7 @@ public class Site {
             throw new HttpForbiddenException();
         }
         visitor.setAvatar(webApp.getAvatarWebPath(visitor));
-        Optional<Message> message = messagesService.getMessage(mid);
+        Optional<Message> 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<Integer> 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 %}
                 <a href="/post?body=U+%23{{ msg.mid }}" class="msg-button">
                     <i data-icon="ei-check" data-size="s"></i>
                     <span>&nbsp;{{ i18n("messages","message.subscribed") }}</span>