diff options
Diffstat (limited to 'juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java')
| -rw-r--r-- | juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java | 1006 |
1 files changed, 0 insertions, 1006 deletions
diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java deleted file mode 100644 index 35e48383..00000000 --- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java +++ /dev/null @@ -1,1006 +0,0 @@ -/* - * Copyright (C) 2008-2017, Juick - * - * This program is free software: you can redistribute it and/or modify - * it under the terms of the GNU Affero General Public License as - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see <http://www.gnu.org/licenses/>. - */ - -package com.juick.service; - -import com.juick.*; -import com.juick.server.helpers.PrivacyOpts; -import com.juick.server.helpers.ResponseReply; -import com.juick.util.MessageUtils; -import org.apache.commons.collections4.CollectionUtils; -import org.apache.commons.lang3.StringUtils; -import org.slf4j.Logger; -import org.slf4j.LoggerFactory; -import org.springframework.beans.factory.annotation.Value; -import org.springframework.dao.IncorrectResultSizeDataAccessException; -import org.springframework.jdbc.core.ConnectionCallback; -import org.springframework.jdbc.core.RowMapper; -import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; -import org.springframework.jdbc.core.namedparam.SqlParameterSource; -import org.springframework.jdbc.core.simple.SimpleJdbcInsert; -import org.springframework.stereotype.Repository; -import org.springframework.transaction.annotation.Transactional; - -import javax.inject.Inject; -import java.sql.*; -import java.time.Instant; -import java.util.*; -import java.util.Date; -import java.util.stream.Collectors; - -/** - * Created by aalexeev on 11/13/16. - */ -@Repository -public class MessagesServiceImpl extends BaseJdbcService implements MessagesService { - private static final Logger logger = LoggerFactory.getLogger(MessagesServiceImpl.class); - @Inject - private UserService userService; - @Inject - private TagService tagService; - @Inject - private SearchService searchService; - @Inject - private ImagesService imagesService; - @Value("${img_url:https://i.juick.com/}") - private String baseImagesUrl; - - private class MessageMapper implements RowMapper<Message> { - @Override - public Message mapRow(ResultSet rs, int rowNum) throws SQLException { - Message msg = new Message(); - msg.setMid(rs.getInt(1)); - msg.setRid(rs.getInt(2)); - msg.setReplyto(rs.getInt(3)); - User user = new User(); - user.setUid(rs.getInt(4)); - user.setName(rs.getString(5)); - user.setBanned(rs.getBoolean(6)); - msg.setUser(user); - msg.setTimestamp(rs.getTimestamp(7).toInstant()); - msg.ReadOnly = rs.getBoolean(8); - msg.setPrivacy(rs.getInt(9)); - msg.FriendsOnly = msg.getPrivacy() < 0; - msg.setReplies(rs.getInt(10)); - msg.setAttachmentType(rs.getString(11)); - msg.setLikes(rs.getInt(12)); - msg.Hidden = rs.getBoolean(13); - String tagsStr = rs.getString(14); - msg.setTags(MessageUtils.parseTags(tagsStr)); - msg.setRepliesBy(rs.getString(15)); - msg.setText(rs.getString(16)); - msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(17))); - msg.setUpdated(rs.getTimestamp(18).toInstant()); - int quoteUid = rs.getInt(19); - if (quoteUid > 0) { - User quoteUser = new User(); - quoteUser.setUid(quoteUid); - quoteUser.setName(rs.getString(20)); - msg.setTo(quoteUser); - } - if (StringUtils.isNotEmpty(msg.getAttachmentType())) { - try { - imagesService.setAttachmentMetadata(baseImagesUrl, msg); - } catch (Exception e) { - logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e); - } - } - return msg; - } - } - - - - /** - * @see <a href="https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java, JDBC and MySQL Types</a> - */ - @Transactional - @Override - public int createMessage(final int uid, final String txt, final String attachment, final Collection<com.juick.Tag> tags) { - SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages") - .usingColumns("user_id", "attach", "ts") - .usingGeneratedKeyColumns("message_id"); - Map<String, Object> insertMap = new HashMap<>(); - insertMap.put("user_id", uid); - Instant now = Instant.now(); - insertMap.put("ts", Timestamp.from(now)); - if (attachment != null) { - insertMap.put("attach", attachment); - } - int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue(); - if (mid > 0) { - String tagsNames = StringUtils.EMPTY; - - if (CollectionUtils.isNotEmpty(tags)) { - StringBuilder tasNamesBuilder = new StringBuilder(); - List<Object[]> params = new ArrayList<>(tags.size()); - - boolean next = false; - - for (Tag tag : tags) { - if (next) { - tasNamesBuilder.append(" "); - } else - next = true; - - tasNamesBuilder.append(tag.getName()); - params.add(new Object[]{mid, tag.TID}); - } - tagsNames = tasNamesBuilder.toString(); - - getJdbcTemplate().batchUpdate( - "INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", - params, new int[]{Types.INTEGER, Types.INTEGER}); - } - - getJdbcTemplate().update( - "INSERT INTO messages_txt(message_id, tags, txt) VALUES (?, ?, ?)", - new Object[]{mid, tagsNames, txt}, - new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR}); - getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", Timestamp.from(now), uid); - } - - return mid; - } - - /** - * @param mid - * @param rid - * @param user - * @param txt - * @param attachment - * @return - * @see <a href="https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java, JDBC and MySQL Types</a> - */ - @Transactional - @Override - public int createReply(final int mid, final int rid, final User user, final String txt, final String attachment) { - int ridnew = getReplyIDIncrement(mid); - Date ts = Date.from(Instant.now()); - getJdbcTemplate().update("INSERT INTO replies(message_id, reply_id, user_id, replyto, attach, txt, ts) " + - "VALUES (?, ?, ?, ?, ?, ?, ?)", - mid, ridnew, user.getUid(), rid, attachment, txt, ts); - - if (ridnew > 0) { - getJdbcTemplate().update( - "UPDATE messages SET replies = replies + 1, updated=? WHERE message_id = ?", - ts, mid); - setLastReadComment(user, mid, ridnew); - getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", ts, user.getUid()); - } - return ridnew; - } - - @Override - public int getReplyIDIncrement(final int mid) { - return getJdbcTemplate().execute((ConnectionCallback<Integer>) conn -> { - conn.setAutoCommit(false); - final int replyNo; - try (PreparedStatement ps = conn.prepareStatement("SELECT maxreplyid+1 FROM messages WHERE message_id=? FOR UPDATE")) { - ps.setInt(1, mid); - try (ResultSet resultSet = ps.executeQuery()) { - if (resultSet.next()) { - replyNo = resultSet.getInt(1); - } else { - throw new IncorrectResultSizeDataAccessException("while getting getReplyIDIncrement, mid=" + mid, 1, 0); - } - } - } - try (PreparedStatement ps = conn.prepareStatement("UPDATE messages SET maxreplyid=? WHERE message_id=?")) { - ps.setInt(1, replyNo); - ps.setInt(2, mid); - if (ps.executeUpdate() != 1) { - throw new IncorrectResultSizeDataAccessException("Cannot find a message to update: " + mid, 1, 0); - } - } - conn.commit(); - return replyNo; - }); - - } - - @Transactional - void updateRepliesBy(int mid) { - List<String> 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 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); - } - - @Transactional - @Override - public RecommendStatus recommendMessage(final int mid, final int vuid) { - int wasDeleted = getJdbcTemplate() - .update("DELETE FROM favorites WHERE user_id=? AND message_id=? and like_id=?", vuid, mid, Reaction.LIKE); - if (wasDeleted > 0) { - return RecommendStatus.Deleted; - } else { - boolean wasAdded = getJdbcTemplate() - .update("INSERT INTO favorites(user_id, message_id, ts, like_id ) VALUES (?, ?, NOW(), ?)", vuid, mid,Reaction.LIKE) == 1; - if (wasAdded) { - return RecommendStatus.Added; - } - } - return RecommendStatus.Error; - } - - @Override - public List<Reaction> listReactions() { - return jdbcTemplate.query("SELECT like_id, description FROM reactions", (rs, rowNum) -> { - Reaction reaction = new Reaction(rs.getInt("like_id")); - reaction.setDescription(rs.getString("description")); - return reaction; - }); - } - - @Transactional - @Override - public RecommendStatus likeMessage(int mid, int vuid, int reactionId) throws IllegalArgumentException { - boolean wasAdded = getJdbcTemplate() - .update("INSERT INTO favorites(user_id, message_id, ts, like_id ) VALUES (?, ?, NOW(), ?)", vuid, mid, reactionId) == 1; - if (wasAdded) { - return RecommendStatus.Added; - } - - return RecommendStatus.Error; - } - - @Transactional(readOnly = true) - @Override - public boolean canViewThread(final int mid, final int uid) { - List<PrivacyOpts> list = getJdbcTemplate().query( - "SELECT user_id, privacy FROM messages WHERE message_id = ?", - (rs, rowNum) -> { - PrivacyOpts res = new PrivacyOpts(); - - res.setUid(rs.getInt(1)); - res.setPrivacy(rs.getInt(2)); - - return res; - }, - mid); - - PrivacyOpts privacyOpts = list.isEmpty() ? null : list.get(0); - - return privacyOpts == null || - privacyOpts.getPrivacy() >= 0 || - uid == privacyOpts.getUid() || - ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && - uid > 0 && userService.isInWL(privacyOpts.getUid(), uid)); - } - - @Transactional(readOnly = true) - @Override - public boolean isReadOnly(final int mid) { - List<Integer> list = getJdbcTemplate().queryForList( - "SELECT readonly FROM messages WHERE message_id = ?", - new Object[]{mid}, - Integer.class); - - return !list.isEmpty() && list.get(0) == 1; - } - - @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 = ?", - new Object[]{uid, mid}, - Integer.class); - - 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 = ?", - new Object[]{mid}, - Integer.class); - - return list.isEmpty() ? -4 : list.get(0); - } - - @Transactional(readOnly = true) - @Override - public com.juick.Message getMessage(final int mid) { - - List<com.juick.Message> 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," - + "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated, 0 as to_uid, " - + "NULL as to_name 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 " - + "WHERE messages.message_id = ? " - + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, " - + "privacy, replies, attach, tags, repliesby, q", - new MessageMapper(), - mid); - if (!list.isEmpty()) { - final Message message = list.get(0); - Map<Integer, Set<Reaction>> reactionStats = updateReactionsFor(Collections.singletonList(mid)); - message.setReactions(reactionStats.get(message.getMid())); - return message; - } - return null; - } - - @Transactional(readOnly = true) - @Override - public com.juick.Message getReply(final int mid, final int rid) { - List<com.juick.Message> list = getJdbcTemplate().query( - "SELECT replies.user_id, users.nick," - + "replies.replyto, replies.ts," - + "replies.attach, replies.txt, IFNULL(q.txt,t.txt) as quote, " - + "COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name " - + "FROM replies INNER JOIN users ON replies.user_id = users.id " - + "LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.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 q.user_id=qu.id " - + "LEFT JOIN users mu ON m.user_id=mu.id " - + "WHERE replies.message_id = ? AND replies.reply_id = ?", - (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)); - msg.setReplyto(rs.getInt(3)); - msg.setTimestamp(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); - if (quoteUid > 0) { - User quoteUser = new User(); - quoteUser.setUid(quoteUid); - quoteUser.setName(rs.getString(9)); - msg.setTo(quoteUser); - } - - return msg; - }, - mid, rid); - - return list.isEmpty() ? null : list.get(0); - } - - @Transactional(readOnly = true) - @Override - public User getMessageAuthor(final int mid) { - List<User> list = getJdbcTemplate().query( - "SELECT messages.user_id, users.nick " - + "FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.message_id = ?", - new Object[]{mid}, - (rs, num) -> { - User res = new com.juick.User(); - res.setUid(rs.getInt(1)); - res.setName(rs.getString(2)); - return res; - }); - - return list.isEmpty() ? - null : list.get(0); - } - - @Transactional(readOnly = true) - @Override - public List<String> getMessageRecommendations(final int mid) { - return getJdbcTemplate().queryForList( - "SELECT DISTINCT users.nick FROM favorites INNER JOIN users " + - "ON (favorites.message_id = ? AND favorites.user_id = users.id) WHERE favorites.like_id=1", - new Object[]{mid}, - String.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getAll(final int visitorUid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("before", before) - .addValue("visitorUid", visitorUid); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT m.message_id FROM messages m WHERE " + - (before > 0 ? - " m.message_id < :before AND " : StringUtils.EMPTY) + - " m.hidden = 0 AND (m.privacy > 0" + - (visitorUid > 1 ? - " OR m.user_id = :visitorUid) AND NOT EXISTS (" + - " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : - ")") + - " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + - "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)" + - " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id) ORDER BY m.message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getTag(final int tid, final int visitorUid, final int before, final int cnt) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("tid", tid) - .addValue("cnt", cnt) - .addValue("before", before) - .addValue("visitorUid", visitorUid); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM (tags INNER JOIN messages_tags " + - "ON ((tags.synonym_id = :tid OR tags.tag_id = :tid) AND tags.tag_id = messages_tags.tag_id)) " + - "INNER JOIN messages USING(message_id) WHERE " + - (before > 0 ? - " messages.message_id < :before AND " : StringUtils.EMPTY) + - "(messages.privacy > 0 OR messages.user_id = :visitorUid) ORDER BY message_id DESC LIMIT :cnt", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getTags(final String tids, final int visitorUid, final int before, final int cnt) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("cnt", cnt) - .addValue("before", before) - .addValue("visitorUid", visitorUid); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT messages.message_id FROM messages_tags " + - "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") " + - (before > 0 ? - " AND messages.message_id < :before " : StringUtils.EMPTY) + - " AND (messages.privacy > 0 OR messages.user_id = :visitorUid) " + - "ORDER BY messages.message_id DESC LIMIT :cnt", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getPlace(final int placeId, final int visitorUid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("placeId", placeId) - .addValue("before", before) - .addValue("visitorUid", visitorUid); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE place_id = :placeId " + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + - " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getMyFeed(final int uid, final int before, boolean recommended) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("before", before); - - List<Integer> mids = getNamedParameterJdbcTemplate().queryForList( - "(SELECT message_id FROM messages " + - " INNER JOIN subscr_users ON (subscr_users.suser_id = :uid AND subscr_users.user_id = messages.user_id) " + - " WHERE " + - (before > 0 ? - " message_id < :before AND " : StringUtils.EMPTY) + - " (privacy >= 0 OR (privacy >= -2 AND privacy <= -1" + - " AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + - " UNION " + - " (SELECT message_id FROM messages WHERE user_id=:uid " + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + - (recommended ? - ") UNION " + - " (SELECT f.message_id as message_id FROM favorites f WHERE " + - "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" + - (before > 0 ? - " AND f.message_id < :before " : StringUtils.EMPTY) : StringUtils.EMPTY) + - ") ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - - return mids; - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getPrivate(final int uid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList - ("SELECT message_id FROM messages WHERE user_id = :uid AND privacy < 0" + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + - "ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getDiscussions(final int uid, final Long to) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("to", new Timestamp(to)); - - return getNamedParameterJdbcTemplate().query( - "SELECT messages.message_id, messages.updated FROM subscr_messages " + - "INNER JOIN messages ON messages.message_id=subscr_messages.message_id " + - "WHERE suser_id = :uid " + - (to != 0 ? - "AND updated < :to " : StringUtils.EMPTY) + - "ORDER BY updated DESC, message_id DESC LIMIT 20", - sqlParameterSource, - (rs, rowNum) -> rs.getInt(1)); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getRecommended(final int uid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT f.message_id FROM favorites f WHERE " + - "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" + - (before > 0 ? - " AND f.message_id < :before " : StringUtils.EMPTY) + - "ORDER BY f.message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getPopular(final int visitorUid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("vid", visitorUid) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT m.message_id FROM messages m WHERE m.privacy > 0 " + - (before > 0 ? - " AND m.message_id < :before " : StringUtils.EMPTY) + - " AND m.popular > 0 AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) " + - " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + - "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :vid = bt.user_id)" + - " ORDER BY m.message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getPhotos(final int visitorUid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("vid", visitorUid) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT m.message_id FROM messages m WHERE (m.privacy > 0 OR m.user_id = :vid) " + - (before > 0 ? - " AND m.message_id < :before " : StringUtils.EMPTY) + - " AND m.attach IS NOT NULL " + - " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + - "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :vid = bt.user_id)" + - " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id) " + - " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) " + - " ORDER BY m.message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getSearch(final String search, final int before) { - List<Integer> mids = searchService.searchInAllMessages(search, before); - - if (!mids.isEmpty()) - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy > 0 ORDER BY message_id DESC LIMIT 20", - new MapSqlParameterSource("ids", mids), - Integer.class); - return mids; - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserBlog(final int uid, final int privacy, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("privacy", privacy) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE user_id = :uid" + - (before > 0 ? - " AND message_id < :before" : StringUtils.EMPTY) + - " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserTag(final int uid, final int tid, final int privacy, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("tid", tid) - .addValue("privacy", privacy) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT messages.message_id FROM messages_tags INNER JOIN messages " + - " USING (message_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", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserBlogAtDay(final int uid, final int privacy, final int daysback) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("privacy", privacy) - .addValue("daysback", daysback); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages 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", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserBlogWithRecommendations(final int uid, final int privacy, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("privacy", privacy) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM " + - "(SELECT message_id FROM favorites " + - " WHERE user_id = :uid " + - (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" + - (before > 0 ? - " AND message_id < :before" : StringUtils.EMPTY) + - " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " + - "ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserRecommendations(final int uid, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM favorites " + - " WHERE user_id = :uid " + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + - " ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserPhotos(final int uid, final int privacy, final int before) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("uid", uid) - .addValue("privacy", privacy) - .addValue("before", before); - - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE user_id = :uid " + - (before > 0 ? - " AND message_id < :before " : StringUtils.EMPTY) + - " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", - sqlParameterSource, - Integer.class); - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getUserSearch(final int UID, final String search, final int privacy, final int before) { - List<Integer> mids = searchService.searchByStringAndUser(search, UID, before); - - if (!mids.isEmpty()) { - return getNamedParameterJdbcTemplate().queryForList( - "SELECT message_id FROM messages WHERE message_id IN (:ids) AND privacy >= :privacy ORDER BY message_id DESC", - new MapSqlParameterSource("ids", mids) - .addValue("privacy", privacy), - Integer.class); - } - return mids; - } - - @Transactional(readOnly = true) - @Override - public List<com.juick.Message> getMessages(final List<Integer> mids) { - if (CollectionUtils.isNotEmpty(mids)) { - - List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query( - "SELECT messages.message_id, 0 as rid, 0 as replyto, " - + "messages.user_id,users.nick, 0 as banned, " - + "messages.ts," - + "messages.readonly,messages.privacy,messages.replies," - + "messages.attach,COUNT(DISTINCT favorites.user_id) AS likes,messages.hidden," - + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q, " - + "messages.updated, 0 as to_uid, NULL as to_name " - + "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 favorites " - + "ON messages.message_id = favorites.message_id AND favorites.like_id=1 " - + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id", - new MapSqlParameterSource("ids", mids), - 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()))); - - return msgs; - } - return Collections.emptyList(); - } - - - @Transactional(readOnly = true) - @Override - public Map<Integer,Set<Reaction>> updateReactionsFor(final List<Integer> mids) { - - 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", new MapSqlParameterSource("mids", mids), (ResultSet rs) -> { - Map<Integer,Set<Reaction>> results = new HashMap<>(); - - - while (rs.next()) { - int messageId = rs.getInt("mid"); - int likeId = rs.getInt("lid"); - int count = rs.getInt("cnt"); - String description = rs.getString("descr"); - Reaction reaction = new Reaction(likeId); - reaction.setCount(count); - reaction.setDescription(description); - results.computeIfAbsent(messageId, HashSet::new); - results.get(messageId).add(reaction); - } - - return results; - }); - - } - - - @Transactional - @Override - public List<Message> getReplies(final User user, final int mid) { - List<Message> replies = getNamedParameterJdbcTemplate().query( - "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, " + - "IFNULL(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 " + - "FROM replies INNER 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 ORDER BY replies.reply_id ASC", - new MapSqlParameterSource("mid", mid), - new MessageMapper()); - if (replies.size() > 0) { - setLastReadComment(user, mid, replies.stream().map(Message::getRid).max(Comparator.naturalOrder()).get()); - } - return replies; - } - - @Transactional - @Override - public boolean setMessagePopular(final int mid, final int popular) { - int ret; - MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("mid", mid) - .addValue("popular", popular); - - switch (popular) { - case -2: - ret = getNamedParameterJdbcTemplate().update( - "UPDATE messages SET hidden = 1 WHERE message_id = :mid", - sqlParameterSource); - break; - case -1: - sqlParameterSource.addValue("popular", 0); - default: - ret = getNamedParameterJdbcTemplate().update( - "UPDATE messages SET popular = :popular WHERE message_id = :mid", - sqlParameterSource); - break; - } - - if (popular == -1) - ret = getNamedParameterJdbcTemplate().update( - "INSERT INTO top_ignore_messages VALUES (:mid)", - sqlParameterSource); - - return ret > 0; - } - - @Transactional - @Override - public boolean setMessagePrivacy(final int mid) { - return getJdbcTemplate().update("UPDATE messages SET privacy=1 WHERE message_id=?", mid) > 0; - } - - @Transactional - @Override - public boolean deleteMessage(final int uid, final int mid) { - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("mid", mid) - .addValue("uid", uid); - - if (getNamedParameterJdbcTemplate().update( - "DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", sqlParameterSource) > 0) { - - getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", sqlParameterSource); - getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", sqlParameterSource); - getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", sqlParameterSource); - getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", sqlParameterSource); - - return true; - } - return false; - } - @Transactional - @Override - public boolean deleteReply(final int uid, final int mid, final int rid) { - User author = getMessageAuthor(mid); - SqlParameterSource sqlParameterSource = new MapSqlParameterSource() - .addValue("mid", mid) - .addValue("uid", uid) - .addValue("rid", rid); - boolean result; - if (author.getUid() == uid) { - result = getNamedParameterJdbcTemplate() - .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", sqlParameterSource) > 0; - } else { - result = getNamedParameterJdbcTemplate() - .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid" - , sqlParameterSource) > 0; - } - if (result) { - getNamedParameterJdbcTemplate().update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", sqlParameterSource); - updateRepliesBy(mid); - return true; - } - return false; - } - - @Transactional(readOnly = true) - @Override - public List<Integer> getLastMessages(int hours) { - return getJdbcTemplate().queryForList("SELECT message_id FROM messages WHERE messages.ts>TIMESTAMPADD(HOUR,?,NOW())", - Integer.class, -hours); - - } - - @Transactional(readOnly = true) - @Override - public List<ResponseReply> getLastReplies(int hours) { - return getJdbcTemplate().query("SELECT users2.nick,replies.message_id,replies.reply_id," + - "users.nick,replies.txt," + - "replies.ts,replies.attach,replies.ts+0 " + - "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>TIMESTAMPADD(HOUR,?,NOW()) AND messages.privacy>0", (rs, rowNum) -> { - ResponseReply reply = new ResponseReply(); - reply.setMuname(rs.getString(1)); - reply.setMid(rs.getInt(2)); - reply.setRid(rs.getInt(3)); - reply.setUname(rs.getString(4)); - reply.setDescription(rs.getString(5)); - reply.setPubDate(rs.getTimestamp(6)); - reply.setAttachmentType(rs.getString(7)); - return reply; - }, -hours); - } - @Transactional(readOnly = true) - @Override - public List<Integer> getPopularCandidates() { - return getJdbcTemplate().queryForList("SELECT replies.message_id FROM replies " + - "INNER JOIN messages ON replies.message_id = messages.message_id " + - "LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id " + - "WHERE COALESCE(messages_tags.tag_id, 0) != 2 " + - "AND COALESCE(messages_tags.tag_id, 0) != 805 AND replies.ts > TIMESTAMPADD(HOUR, -2, CURRENT_TIMESTAMP) " + - "AND messages.popular=0 GROUP BY messages.message_id having COUNT(DISTINCT(replies.user_id)) > 5 " + - "UNION ALL SELECT favorites.message_id FROM favorites " + - "INNER JOIN messages ON messages.message_id = favorites.message_id " + - "LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id " + - "WHERE COALESCE(messages_tags.tag_id, 0) != 2 AND favorites.ts > TIMESTAMPADD(HOUR, -2, CURRENT_TIMESTAMP) " + - "AND messages.popular=0 GROUP BY messages.message_id HAVING COUNT(DISTINCT favorites.user_id) > 1;", Integer.class); - } - @Transactional - @Override - public void setLastReadComment(User user, Integer mid, Integer rid) { - jdbcTemplate.update("UPDATE subscr_messages SET last_read_rid=GREATEST(?, last_read_rid) WHERE message_id=? AND suser_id=?", - rid, mid, user.getUid()); - } - - @Override - public List<Integer> getUnread(User user) { - return jdbcTemplate.queryForList( - "select subscr_messages.message_id " + - "from subscr_messages inner join messages on subscr_messages.message_id=messages.message_id " + - "where subscr_messages.suser_id=? and " + - "messages.replies>subscr_messages.last_read_rid", - Integer.class, user.getUid()); - } -} |