From 1b5d8ba596869e7321dd7a0f4757d08306a4d9fd Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 26 Apr 2019 17:29:25 +0300 Subject: Minimize SQL queries for banned users --- .../java/com/juick/server/CommandsManager.java | 2 +- src/main/java/com/juick/server/api/Messages.java | 5 +- .../com/juick/server/api/activity/Profile.java | 2 +- src/main/java/com/juick/server/api/rss/Feeds.java | 5 +- .../juick/server/www/controllers/MessagesWWW.java | 4 +- .../com/juick/service/MessagesServiceImpl.java | 68 +++++++++++----------- 6 files changed, 43 insertions(+), 43 deletions(-) diff --git a/src/main/java/com/juick/server/CommandsManager.java b/src/main/java/com/juick/server/CommandsManager.java index fdea0d83..634deb35 100644 --- a/src/main/java/com/juick/server/CommandsManager.java +++ b/src/main/java/com/juick/server/CommandsManager.java @@ -410,7 +410,7 @@ public class CommandsManager { public CommandResult commandUser(User user, URI attachment, String... arguments) { User blogUser = userService.getUserByName(arguments[0]); int page = arguments[1].length(); - if (!blogUser.isAnonymous()) { + if (!blogUser.isAnonymous() && !blogUser.isBanned()) { List mids = messagesService.getUserBlog(blogUser.getUid(), 0, 0); return CommandResult.fromString(String.format("Last messages from @%s:\n%s", arguments[0], printMessages(user, mids, false))); diff --git a/src/main/java/com/juick/server/api/Messages.java b/src/main/java/com/juick/server/api/Messages.java index 60e05d6d..402d2162 100644 --- a/src/main/java/com/juick/server/api/Messages.java +++ b/src/main/java/com/juick/server/api/Messages.java @@ -106,7 +106,7 @@ public class Messages { List mids; if (!StringUtils.isEmpty(uname)) { User user = userService.getUserByName(uname); - if (!user.isAnonymous()) { + if (!user.isAnonymous() && !user.isBanned()) { if (!StringUtils.isEmpty(media)) { mids = messagesService.getUserPhotos(user.getUid(), 0, before); } else if (!StringUtils.isEmpty(tag)) { @@ -180,9 +180,6 @@ public class Messages { if (!messagesService.canViewThread(mid, visitor.getUid())) { return FORBIDDEN; } else { - if (userService.getUserByName(msg.getUser().getName()).isBanned()) { - throw new HttpNotFoundException(); - } msg.getUser().setAvatar(webApp.getAvatarUrl(msg.getUser())); msg.setRecommendations(new HashSet<>(messagesService.getMessagesRecommendations( Collections.singletonList(msg.getMid())) diff --git a/src/main/java/com/juick/server/api/activity/Profile.java b/src/main/java/com/juick/server/api/activity/Profile.java index 4e375a54..701b1949 100644 --- a/src/main/java/com/juick/server/api/activity/Profile.java +++ b/src/main/java/com/juick/server/api/activity/Profile.java @@ -136,7 +136,7 @@ public class Profile { @RequestParam(required = false, defaultValue = "0") int before) { User visitor = UserUtils.getCurrentUser(); User user = userService.getUserByName(userName); - if (!user.isAnonymous()) { + if (!user.isAnonymous() && !user.isBanned()) { UriComponentsBuilder uri = UriComponentsBuilder.fromUriString(baseUri); String personUri = uri.path(String.format("/u/%s", userName)).toUriString(); List mids = messagesService.getUserBlog(user.getUid(), 0, before); diff --git a/src/main/java/com/juick/server/api/rss/Feeds.java b/src/main/java/com/juick/server/api/rss/Feeds.java index c72f3a5e..47d9fd5e 100644 --- a/src/main/java/com/juick/server/api/rss/Feeds.java +++ b/src/main/java/com/juick/server/api/rss/Feeds.java @@ -19,6 +19,7 @@ package com.juick.server.api.rss; import com.juick.User; import com.juick.server.util.HttpBadRequestException; +import com.juick.server.util.HttpNotFoundException; import com.juick.server.util.UserUtils; import com.juick.service.MessagesService; import com.juick.service.UserService; @@ -46,7 +47,7 @@ public class Feeds { @RequestMapping(value = "/rss/{userName}/blog", method = RequestMethod.GET, produces = "text/xml; charset=utf-8") public ModelAndView getBlog(@PathVariable String userName) { User user = userService.getUserByName(userName); - if (!user.isAnonymous()) { + if (!user.isAnonymous() && !user.isBanned()) { List mids = messagesService.getUserBlog(user.getUid(), 0, 0); ModelAndView modelAndView = new ModelAndView(); modelAndView.setViewName("messagesView"); @@ -54,7 +55,7 @@ public class Feeds { modelAndView.addObject("messages", messagesService.getMessages(UserUtils.getCurrentUser(), mids)); return modelAndView; } - throw new HttpBadRequestException(); + throw new HttpNotFoundException(); } @RequestMapping(value = "/rss/", method = RequestMethod.GET, produces = "text/xml; charset=utf-8") diff --git a/src/main/java/com/juick/server/www/controllers/MessagesWWW.java b/src/main/java/com/juick/server/www/controllers/MessagesWWW.java index 41c95dcb..e1c1bed8 100644 --- a/src/main/java/com/juick/server/www/controllers/MessagesWWW.java +++ b/src/main/java/com/juick/server/www/controllers/MessagesWWW.java @@ -224,7 +224,7 @@ public class MessagesWWW { if (paramTag == null) { throw new HttpNotFoundException(); } else if (!paramTag.getName().equals(paramTagStr)) { - String url = user.getName() + "/?tag=" + URLEncoder.encode(paramTag.getName(), CharEncoding.UTF_8); + String url = user.getName() + "/?tag=" + URLEncoder.encode(paramTag.getName(), StandardCharsets.UTF_8); return "redirect:/" + url; } } @@ -510,7 +510,7 @@ public class MessagesWWW { visitor.setAvatar(webApp.getAvatarWebPath(visitor)); Optional message = messagesService.getMessage(mid); - if (!message.isPresent() || message.get().getUser().isBanned()) { + if (message.isEmpty()) { throw new HttpNotFoundException(); } diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 5e36093e..b1cb03d9 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -380,7 +380,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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 = ? " + + "WHERE messages.message_id = ? AND users.banned = 0 " + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, " + "privacy, replies, attach, repliesby, q, updated_at, reply_user_uri, to_uri, reply_uri, html", new MessageMapper(), @@ -736,16 +736,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("privacy", privacy) .addValue("before", before); - ; - if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { - throw new HttpNotFoundException(); - } - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE user_id = :uid" + + "SELECT message_id FROM messages INNER JOIN users" + + " ON messages.user_id = users.id" + + " WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + - " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20", + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @@ -759,16 +756,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("privacy", privacy) .addValue("before", before); - if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { - throw new HttpNotFoundException(); - } - return getNamedParameterJdbcTemplate().queryForList( - "SELECT messages.message_id FROM messages_tags INNER JOIN messages " + - " ON messages.message_id = messages_tags.message_id WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid " + + "SELECT messages.message_id FROM messages_tags INNER JOIN messages" + + " ON messages.message_id = messages_tags.message_id" + + " INNER JOIN users" + + " ON messages.user_id=users.id" + + " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid" + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + - " AND messages.privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20", + " AND messages.privacy >= :privacy AND users.banned = 0" + + " ORDER BY messages.message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @@ -781,16 +778,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("privacy", privacy) .addValue("daysback", daysback); - if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { - throw new HttpNotFoundException(); - } - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE user_id = :uid" + + "SELECT message_id FROM messages" + + " INNER JOIN users" + + " ON messages.user_id = users.id" + + " WHERE user_id = :uid" + (daysback > 0 ? " AND ts >= date(NOW() - INTERVAL :daysback day)" + - " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) + - " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20", + " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) + + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC LIMIT 20", sqlParameterSource, Integer.class); } @@ -803,20 +799,21 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("privacy", privacy) .addValue("before", before); - if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { - throw new HttpNotFoundException(); - } - return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM " + - "(SELECT message_id FROM favorites " + - " WHERE user_id = :uid " + - (before > 0 ? + "(SELECT message_id FROM favorites " + + " INNER JOIN users" + + " ON messages.user_id = users.id" + + " WHERE user_id = :uid AND users.banned = 0" + + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20) as r" + " UNION ALL " + "SELECT message_id FROM " + - "(SELECT message_id FROM messages WHERE user_id = :uid" + + "(SELECT message_id FROM messages" + + " INNER JOIN users" + + " ON messages.user_id = users.id" + + " WHERE user_id = :uid AND users.banned = 0" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " + @@ -833,8 +830,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM favorites " + - " WHERE user_id = :uid " + + "SELECT message_id FROM favorites" + + " INNER JOIN users" + + " ON favorites.user_id = users.id" + + " WHERE user_id = :uid AND users.banned = 0 " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20", @@ -851,7 +850,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("before", before); return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE user_id = :uid " + + "SELECT message_id FROM messages" + + " INNER JOIN users" + + " ON messages.user_id = users.id" + + " WHERE user_id = :uid and users.banned = 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", -- cgit v1.2.3