From 3d7f60c1c0b48dad2f58bffe3352d1c5c1b93f00 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Thu, 1 Dec 2016 13:56:53 +0300 Subject: juick-api: Tags controller from perl, refactoring --- .../java/com/juick/server/MessagesQueries.java | 13 +++-- .../src/main/java/com/juick/server/TagQueries.java | 16 +++-- .../java/com/juick/server/helpers/TagStats.java | 29 +++++++++ .../com/juick/server/protocol/JuickProtocol.java | 5 +- .../java/com/juick/service/MessagesService.java | 4 -- .../com/juick/service/MessagesServiceImpl.java | 26 --------- .../com/juick/service/SubscriptionServiceImpl.java | 11 +++- .../main/java/com/juick/service/TagService.java | 8 ++- .../java/com/juick/service/TagServiceImpl.java | 68 ++++++++++++++++++---- 9 files changed, 121 insertions(+), 59 deletions(-) create mode 100644 juick-server/src/main/java/com/juick/server/helpers/TagStats.java (limited to 'juick-server/src/main') diff --git a/juick-server/src/main/java/com/juick/server/MessagesQueries.java b/juick-server/src/main/java/com/juick/server/MessagesQueries.java index fd1e5b51..36d311df 100644 --- a/juick-server/src/main/java/com/juick/server/MessagesQueries.java +++ b/juick-server/src/main/java/com/juick/server/MessagesQueries.java @@ -21,6 +21,7 @@ import com.juick.Message; import com.juick.Tag; import com.juick.User; import com.juick.server.helpers.PrivacyOpts; +import com.juick.server.helpers.TagStats; import com.juick.util.MessageUtils; import org.apache.commons.lang3.StringEscapeUtils; import org.springframework.dao.EmptyResultDataAccessException; @@ -308,16 +309,18 @@ public class MessagesQueries { } } - public static List getMessageTags(JdbcTemplate sql, int mid) { + public static List getMessageTags(JdbcTemplate sql, int mid) { return sql.query("SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + "INNER JOIN messages_tags ON (messages_tags.message_id=? AND messages_tags.tag_id=tags.tag_id)", - new Object[]{mid}, (rs, num) -> { + (rs, num) -> { com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3))); t.TID = rs.getInt(1); t.SynonymID = rs.getInt(2); - t.UsageCnt = rs.getInt(4); - return t; - }); + TagStats s = new TagStats(); + s.setTag(t); + s.setUsageCount(rs.getInt(4)); + return s; + }, mid); } public static List getMessageTagsIDs(JdbcTemplate sql, int mid) { diff --git a/juick-server/src/main/java/com/juick/server/TagQueries.java b/juick-server/src/main/java/com/juick/server/TagQueries.java index ee9a1e21..6d416a37 100644 --- a/juick-server/src/main/java/com/juick/server/TagQueries.java +++ b/juick-server/src/main/java/com/juick/server/TagQueries.java @@ -18,6 +18,7 @@ package com.juick.server; import com.juick.Tag; +import com.juick.server.helpers.TagStats; import org.apache.commons.lang3.StringEscapeUtils; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.JdbcTemplate; @@ -109,15 +110,16 @@ public class TagQueries { return holder.getKey().intValue(); } - public static List getUserTagsAll(JdbcTemplate sql, int uid) { + public static List getTagsStats(JdbcTemplate sql, int uid) { return sql.query("SELECT tags.name,COUNT(messages.message_id) " + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + "AND messages.message_id=messages_tags.message_id)) " + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", (rs, rowNum) -> { - Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1))); - t.UsageCnt = rs.getInt(2); - return t; + TagStats s = new TagStats(); + s.setTag(new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1)))); + s.setUsageCount(rs.getInt(2)); + return s; }, uid); } @@ -132,12 +134,14 @@ public class TagQueries { .map(StringEscapeUtils::unescapeHtml4).collect(Collectors.toList()); } public static List updateTags(JdbcTemplate sql, int mid, List newTags) { - List currentTags = MessagesQueries.getMessageTags(sql, mid); + List currentTags = MessagesQueries.getMessageTags(sql, mid).stream() + .map(TagStats::getTag).collect(Collectors.toList()); newTags.stream().filter(currentTags::contains) .forEach(t -> sql.update("DELETE FROM messages_tags WHERE message_id=? AND tag_id=?", mid, t.TID)); newTags.stream().filter(t -> !currentTags.contains(t)) .forEach(t -> sql.update("INSERT INTO messages_tags(message_id,tag_id) VALUES (?,?)", mid, t.TID)); - return MessagesQueries.getMessageTags(sql, mid); + return MessagesQueries.getMessageTags(sql, mid).stream() + .map(TagStats::getTag).collect(Collectors.toList()); } public static List fromString(JdbcTemplate sql, String txt, boolean tagsOnly) { diff --git a/juick-server/src/main/java/com/juick/server/helpers/TagStats.java b/juick-server/src/main/java/com/juick/server/helpers/TagStats.java new file mode 100644 index 00000000..e8720991 --- /dev/null +++ b/juick-server/src/main/java/com/juick/server/helpers/TagStats.java @@ -0,0 +1,29 @@ +package com.juick.server.helpers; + +import com.fasterxml.jackson.annotation.JsonProperty; +import com.juick.Tag; + +/** + * Created by vitalyster on 01.12.2016. + */ +public class TagStats { + private Tag tag; + private int usageCount; + + public Tag getTag() { + return tag; + } + + public void setTag(Tag tag) { + this.tag = tag; + } + + @JsonProperty("messages") + public int getUsageCount() { + return usageCount; + } + + public void setUsageCount(int usageCount) { + this.usageCount = usageCount; + } +} diff --git a/juick-server/src/main/java/com/juick/server/protocol/JuickProtocol.java b/juick-server/src/main/java/com/juick/server/protocol/JuickProtocol.java index 1bac9fce..68c7c37c 100644 --- a/juick-server/src/main/java/com/juick/server/protocol/JuickProtocol.java +++ b/juick-server/src/main/java/com/juick/server/protocol/JuickProtocol.java @@ -8,6 +8,7 @@ import com.juick.Tag; import com.juick.User; import com.juick.formatters.PlainTextFormatter; import com.juick.server.*; +import com.juick.server.helpers.TagStats; import com.juick.server.protocol.annotation.UserCommand; import com.juick.util.TagUtils; import org.springframework.jdbc.core.JdbcTemplate; @@ -179,10 +180,10 @@ public class JuickProtocol { @UserCommand(pattern = "\\*", help = "* - Show your tags") public ProtocolReply commandTags(User currentUser, String... args) { - List tags = TagQueries.getUserTagsAll(sql, currentUser.getUid()); + List tags = TagQueries.getTagsStats(sql, currentUser.getUid()); String msg = "Your tags: (tag - messages)\n" + tags.stream() - .map(t -> String.format("\n*%s - %d", t.getName(), t.UsageCnt)).collect(Collectors.joining()); + .map(t -> String.format("\n*%s - %d", t.getTag().getName(), t.getUsageCount())).collect(Collectors.joining()); return new ProtocolReply(msg, Optional.empty()); } diff --git a/juick-server/src/main/java/com/juick/service/MessagesService.java b/juick-server/src/main/java/com/juick/service/MessagesService.java index 5990f408..f7175967 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesService.java +++ b/juick-server/src/main/java/com/juick/service/MessagesService.java @@ -32,10 +32,6 @@ public interface MessagesService { User getMessageAuthor(int mid); - List getMessageTags(int mid); - - List getMessageTagsIDs(int mid); - List getMessageRecommendations(int mid); List getAll(int visitorUid, int before); diff --git a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java index 948e9137..9cf103a1 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -7,7 +7,6 @@ import com.juick.server.helpers.PrivacyOpts; import com.juick.service.search.SearchService; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; -import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.ConnectionCallback; @@ -344,31 +343,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ null : list.get(0); } - @Transactional(readOnly = true) - @Override - public List getMessageTags(final int mid) { - return getJdbcTemplate().query( - "SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + - "INNER JOIN messages_tags ON (messages_tags.message_id = ? AND messages_tags.tag_id = tags.tag_id)", - new Object[]{mid}, - (rs, num) -> { - com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3))); - t.TID = rs.getInt(1); - t.SynonymID = rs.getInt(2); - t.UsageCnt = rs.getInt(4); - return t; - }); - } - - @Transactional(readOnly = true) - @Override - public List getMessageTagsIDs(final int mid) { - return getJdbcTemplate().queryForList( - "SELECT tag_id FROM messages_tags WHERE message_id = ?", - new Object[]{mid}, - Integer.class); - } - @Transactional(readOnly = true) @Override public List getMessageRecommendations(final int mid) { diff --git a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java index bd915961..767c62fc 100644 --- a/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/SubscriptionServiceImpl.java @@ -23,9 +23,11 @@ import java.util.stream.Collectors; public class SubscriptionServiceImpl extends BaseJdbcService implements SubscriptionService { private final UserService userService; private final MessagesService messagesService; + private final TagService tagService; @Inject - public SubscriptionServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, MessagesService messagesService) { + public SubscriptionServiceImpl(JdbcTemplate jdbcTemplate, UserService userService, + MessagesService messagesService, TagService tagService) { super(jdbcTemplate, null); Assert.notNull(userService); @@ -33,6 +35,9 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip Assert.notNull(messagesService); this.messagesService = messagesService; + + Assert.notNull(tagService); + this.tagService = tagService; } @Transactional(readOnly = true) @@ -60,7 +65,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip .map(User::getUid) .collect(Collectors.toList())); - List tags = messagesService.getMessageTagsIDs(mid); + List tags = tagService.getMessageTagsIDs(mid); if (!tags.isEmpty()) { List tagUsers = getNamedParameterJdbcTemplate().queryForList( "SELECT st.suser_id FROM subscr_tags st " + @@ -94,7 +99,7 @@ public class SubscriptionServiceImpl extends BaseJdbcService implements Subscrip @Transactional(readOnly = true) @Override public List getUsersSubscribedToUserRecommendations(final int uid, final int mid, final int muid) { - List tags = messagesService.getMessageTagsIDs(mid); + List tags = tagService.getMessageTagsIDs(mid); String query = "SELECT s.suser_id FROM subscr_users s WHERE s.user_id = :uid " + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.bl_user_id = :muid and b.user_id = s.user_id) " + diff --git a/juick-server/src/main/java/com/juick/service/TagService.java b/juick-server/src/main/java/com/juick/service/TagService.java index 03dcd842..2fcc7097 100644 --- a/juick-server/src/main/java/com/juick/service/TagService.java +++ b/juick-server/src/main/java/com/juick/service/TagService.java @@ -1,6 +1,7 @@ package com.juick.service; import com.juick.Tag; +import com.juick.server.helpers.TagStats; import java.util.Collection; import java.util.List; @@ -19,13 +20,18 @@ public interface TagService { int createTag(String name); - List getUserTagsAll(int uid); + List getUserTagStats(int uid); List getUserBLTags(int uid); List getPopularTags(); + List getTagStats(); List updateTags(int mid, Collection newTags); List fromString(String txt, boolean tagsOnly); + + List getMessageTags(int mid); + + List getMessageTagsIDs(int mid); } diff --git a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java index 8f420ba9..23b8d2a9 100644 --- a/juick-server/src/main/java/com/juick/service/TagServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/TagServiceImpl.java @@ -1,11 +1,13 @@ package com.juick.service; import com.juick.Tag; +import com.juick.server.helpers.TagStats; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.ArrayUtils; import org.apache.commons.lang3.StringEscapeUtils; import org.apache.commons.lang3.StringUtils; import org.springframework.jdbc.core.JdbcTemplate; +import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.support.GeneratedKeyHolder; import org.springframework.jdbc.support.KeyHolder; @@ -14,9 +16,7 @@ import org.springframework.transaction.annotation.Transactional; import org.springframework.util.Assert; import javax.inject.Inject; -import java.sql.PreparedStatement; -import java.sql.Statement; -import java.sql.Types; +import java.sql.*; import java.util.ArrayList; import java.util.Collection; import java.util.Collections; @@ -132,19 +132,27 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { return holder.getKey().intValue(); } + private class TagStatsMapper implements RowMapper { + + @Override + public TagStats mapRow(ResultSet rs, int rowNum) throws SQLException { + Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1))); + TagStats s = new TagStats(); + s.setTag(t); + s.setUsageCount(rs.getInt(2)); + return s; + } + } + @Transactional(readOnly = true) @Override - public List getUserTagsAll(final int uid) { + public List getUserTagStats(final int uid) { return getJdbcTemplate().query( "SELECT tags.name,COUNT(messages.message_id) " + "FROM (messages INNER JOIN messages_tags ON (messages.user_id=? " + "AND messages.message_id=messages_tags.message_id)) " + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id GROUP BY tags.tag_id ORDER BY tags.name ASC", - (rs, rowNum) -> { - Tag t = new Tag(StringEscapeUtils.unescapeHtml4(rs.getString(1))); - t.UsageCnt = rs.getInt(2); - return t; - }, + new TagStatsMapper(), uid); } @@ -167,13 +175,24 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { .collect(Collectors.toList()); } + @Override + public List getTagStats() { + return getJdbcTemplate().query( + "SELECT tags.name,COUNT(DISTINCT messages.user_id) AS cnt " + + "FROM (messages INNER JOIN messages_tags ON (messages.ts>TIMESTAMPADD(DAY,-3,NOW()) " + + "AND messages.message_id=messages_tags.message_id)) " + + "INNER JOIN tags ON messages_tags.tag_id=tags.tag_id " + + "WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore) " + + "GROUP BY tags.tag_id ORDER BY cnt DESC LIMIT 20", new TagStatsMapper()); + } + @Transactional @Override public List updateTags(final int mid, final Collection newTags) { - List currentTags = messagesService.getMessageTags(mid); + List currentTags = getMessageTags(mid); if (CollectionUtils.isEmpty(newTags)) - return currentTags; + return currentTags.stream().map(TagStats::getTag).collect(Collectors.toList()); List ids = new ArrayList<>(newTags.size()); List params = new ArrayList<>(newTags.size()); @@ -198,7 +217,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { .addValue("mid", mid) .addValue("ids", ids)); - return messagesService.getMessageTags(mid); + return getMessageTags(mid).stream().map(TagStats::getTag).collect(Collectors.toList()); } @Override @@ -219,4 +238,29 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { } return Collections.emptyList(); } + + @Transactional(readOnly = true) + @Override + public List getMessageTags(final int mid) { + return getJdbcTemplate().query( + "SELECT tags.tag_id,synonym_id,name,stat_messages FROM tags " + + "INNER JOIN messages_tags ON (messages_tags.message_id = ? AND messages_tags.tag_id = tags.tag_id)", + (rs, num) -> { + com.juick.Tag t = new com.juick.Tag(StringEscapeUtils.unescapeHtml4(rs.getString(3))); + t.TID = rs.getInt(1); + t.SynonymID = rs.getInt(2); + TagStats s = new TagStats(); + s.setTag(t); + s.setUsageCount(rs.getInt(4)); + return s; + }, mid); + } + + @Transactional(readOnly = true) + @Override + public List getMessageTagsIDs(final int mid) { + return getJdbcTemplate().queryForList( + "SELECT tag_id FROM messages_tags WHERE message_id = ?", + Integer.class, mid); + } } -- cgit v1.2.3