/* * 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.model.AnonymousUser; import com.juick.model.PrivacyOpts; import com.juick.model.ResponseReply; import com.juick.server.util.HttpNotFoundException; 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.net.URI; 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(Optional.ofNullable(rs.getString(5)).orElse(AnonymousUser.INSTANCE.getName())); user.setBanned(rs.getBoolean(6)); user.setUri(URI.create(Optional.ofNullable(rs.getString(22)).orElse(StringUtils.EMPTY))); 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); User quoteUser = new User(); quoteUser.setUid(quoteUid); quoteUser.setName(rs.getString(20)); if (quoteUid == 0) { quoteUser.setName(AnonymousUser.INSTANCE.getName()); quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(23)).orElse(StringUtils.EMPTY))); } msg.setTo(quoteUser); msg.setUpdatedAt(rs.getTimestamp(21).toInstant()); msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY))); msg.setHtml(rs.getBoolean(25)); 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 (StringUtils.isNotEmpty(attachment)) { 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, updated_at) VALUES (?, ?, ?, ?)", new Object[]{mid, tagsNames, txt, Timestamp.from(now)}, new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP}); getJdbcTemplate().update("UPDATE users SET lastmessage=?, last_seen=? where id=?", Timestamp.from(now), 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, updated_at, user_uri) " + "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", mid, ridnew, user.getUid(), rid, attachment, txt, ts, ts, user.getUri().toASCIIString()); 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=?, last_seen=? where id=?", ts, 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, final String userUri) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", vuid) .addValue("uri", userUri) .addValue("like_id", Reaction.LIKE) .addValue("mid", mid); int wasDeleted = getNamedParameterJdbcTemplate() .update("DELETE FROM favorites WHERE user_id=:uid AND message_id=:mid AND like_id=:like_id AND user_uri=:uri", sqlParameterSource); if (wasDeleted > 0) { return RecommendStatus.Deleted; } else { boolean wasAdded = getJdbcTemplate() .update("INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", vuid, mid,Reaction.LIKE, userUri) == 1; if (wasAdded) { return RecommendStatus.Added; } } return RecommendStatus.Error; } @Override public RecommendStatus recommendMessage(int mid, int vuid) { return recommendMessage(mid, vuid, StringUtils.EMPTY); } @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; }); } @Override public RecommendStatus likeMessage(int mid, int vuid, int reactionId) { return likeMessage(mid, vuid, reactionId, StringUtils.EMPTY); } @Transactional @Override public RecommendStatus likeMessage(int mid, int vuid, int reactionId, String userUri) throws IllegalArgumentException { boolean wasAdded = getJdbcTemplate() .update("INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", vuid, mid, reactionId, userUri) == 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 as updated, 0 as to_uid, " + "NULL as to_name, txt.updated_at, '' as user_uri, '' as to_uri, '' as reply_uri, 0 as html 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, updated_at, user_uri, to_uri, reply_uri, html", 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, " + "replies.updated_at, replies.user_uri as uri, " + "q.user_uri AS to_uri, replies.reply_uri AS reply_uri, replies.html, q.reply_uri " + "FROM replies LEFT 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)); if (msg.getUser().getUid() == 0) { msg.getUser().setName(AnonymousUser.INSTANCE.getName()); msg.getUser().setUri( URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY))); } 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); User quoteUser = new User(); quoteUser.setUid(quoteUid); quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName())); quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY))); msg.setTo(quoteUser); msg.setUpdatedAt(rs.getTimestamp(10).toInstant()); msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY))); msg.setHtml(rs.getBoolean(14)); msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY))); 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; }, mid, rid); return list.isEmpty() ? null : list.get(0); } @Override public Message getReplyByUri(String replyUri) { List<Message> replies = getJdbcTemplate().query("SELECT message_id, reply_id from replies WHERE reply_uri=?", (rs, rowNum) -> getReply(rs.getInt(1), rs.getInt(2)), replyUri); return replies.isEmpty() ? null : replies.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) " + "INNER JOIN messages m ON favorites.message_id=m.message_id WHERE favorites.like_id=1 " + "AND NOT EXISTS (SELECT 1 FROM bl_users WHERE " + "(user_id = favorites.user_id AND bl_user_id = m.user_id) " + "OR (user_id = m.user_id AND bl_user_id = favorites.user_id))", String.class, mid); } @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 and u.id <> :visitorUid) 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 messages.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 ON messages.message_id = messages_tags.message_id WHERE " + (before > 0 ? " messages.message_id < :before AND " : StringUtils.EMPTY) + "(messages.privacy > 0 OR messages.user_id = :visitorUid) " + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid and b.bl_user_id = messages.user_id) " + "ORDER BY messages.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))) " + " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " + "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :uid = bt.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 INNER JOIN messages ON " + "f.message_id=messages.message_id 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) + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.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 = messages.message_id) and :uid = bt.user_id)) " + "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)); if (uid == 0) { return getNamedParameterJdbcTemplate().query("SELECT message_id FROM messages WHERE " + (to != 0 ? " updated < :to AND" : StringUtils.EMPTY) + " NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1" + " AND u.id = messages.user_id and u.id <> :uid) " + " ORDER BY updated DESC, message_id DESC LIMIT 20", sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } 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) + " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1" + " AND u.id = messages.user_id and u.id <> :uid) " + "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 u.id <> :vid) " + " 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 User visitor, final String search, final int page) { return searchService.searchInAllMessages(visitor, search, page); } @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); ; if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { throw new HttpNotFoundException(); } 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); if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { throw new HttpNotFoundException(); } 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); if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) { throw new HttpNotFoundException(); } 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); 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 ? " 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 User visitor, final int UID, final String search, final int privacy, final int page) { return searchService.searchByStringAndUser(visitor, search, UID, page); } @Transactional(readOnly = true) @Override public List<com.juick.Message> getMessages(final User visitor, final List<Integer> mids) { if (CollectionUtils.isNotEmpty(mids)) { List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query( "WITH RECURSIVE banned(message_id, reply_id) " + "AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) " + "AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + "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 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, 0 as banned, " + "messages.ts," + "messages.readonly,messages.privacy, messages.replies-COUNT(DISTINCT banned.reply_id) as 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, messages_txt.updated_at, '' as user_uri, " + "'' as to_uri, '' as reply_uri, 0 as html " + "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 " + "LEFT JOIN banned " + "ON messages.message_id = banned.message_id " + "WHERE messages.message_id IN (:ids) GROUP BY " + "messages.message_id, rid, replyto, messages.user_id, users.nick, banned, messages.ts, " + "messages.readonly, messages.privacy, messages.attach, messages.hidden, messages_txt.tags, " + "messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, " + "user_uri, reply_uri, html", new MapSqlParameterSource("ids", mids) .addValue("uid", visitor.getUid()), 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( "WITH RECURSIVE banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) " + "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, " + "replies.updated_at, replies.user_uri as uri, " + "qw.user_uri as to_uri, replies.reply_uri, replies.html " + "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 " + "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 " + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource("mid", mid).addValue("uid", user.getUid()), new MessageMapper()); if (replies.size() > 0) { setRead(user, mid); } 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, replies.html " + "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)); reply.setHtml(rs.getBoolean(8)); 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()); } @Transactional @Override public void setRead(User user, Integer mid) { jdbcTemplate.update("UPDATE subscr_messages SET last_read_rid=(select replies from messages " + "where messages.message_id=subscr_messages.message_id) WHERE message_id=? AND suser_id=?", mid, user.getUid()); } @Transactional(readOnly = true) @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()); } @Transactional @Override public boolean updateMessage(Integer mid, Integer rid, String body) { Instant now = Instant.now(); if (rid == 0) { return jdbcTemplate.update("UPDATE messages_txt SET txt=?, updated_at=? WHERE message_id=?", body, Timestamp.from(now), mid) > 0; } else { return jdbcTemplate.update("UPDATE replies SET txt=?, updated_at=? WHERE message_id=? and reply_id=?", body, Timestamp.from(now), mid, rid) > 0; } } @Override public boolean updateReplyUri(Message reply, URI replyUri) { return jdbcTemplate.update("UPDATE replies SET reply_uri=?, html=1 WHERE message_id=? AND reply_id=?", replyUri.toASCIIString(), reply.getMid(), reply.getRid()) > 0; } @Override public boolean replyExists(URI replyUri) { return jdbcTemplate.queryForList("SELECT reply_id FROM replies WHERE reply_uri=?", Integer.class, replyUri.toASCIIString()).size() > 0; } @Override public boolean deleteReply(URI userUri, URI replyUri) { return jdbcTemplate.update("DELETE FROM replies WHERE user_uri=? AND reply_uri=?", userUri.toASCIIString(), replyUri.toASCIIString()) > 0; } }