/* * Copyright (C) 2008-2021, 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.model.*; import com.juick.model.User; import com.juick.www.WebApp; import com.juick.util.MessageUtils; import org.apache.commons.collections4.CollectionUtils; import org.apache.commons.lang3.StringUtils; import org.apache.commons.lang3.tuple.ImmutablePair; import org.apache.commons.lang3.tuple.Pair; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import org.springframework.beans.factory.annotation.Value; import org.springframework.dao.DataIntegrityViolationException; import org.springframework.dao.IncorrectResultSizeDataAccessException; import org.springframework.jdbc.core.BatchPreparedStatementSetter; 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.annotation.Nonnull; import javax.inject.Inject; import java.net.URI; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Timestamp; import java.sql.Types; import java.time.Instant; import java.util.*; 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 SearchService searchService; @Inject private ImagesService imagesService; @Inject private WebApp webApp; @Value("${photos_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.setCreated(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 = StringUtils.defaultString(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))); } else { quoteUser.setAvatar(webApp.getAvatarUrl(quoteUser)); } 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)); msg.setUnread(rs.getBoolean(26)); 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 List<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) { if (CollectionUtils.isNotEmpty(tags)) { getJdbcTemplate().batchUpdate( "INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(@Nonnull PreparedStatement ps, int i) throws SQLException { ps.setInt(1, mid); ps.setInt(2, tags.get(i).TID); } @Override public int getBatchSize() { return tags.size(); } }); } getJdbcTemplate().update( "INSERT INTO messages_txt(message_id, txt, updated_at) VALUES (?, ?, ?)", new Object[]{mid, txt, Timestamp.from(now)}, new int[]{Types.INTEGER, 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, user.getUid()); if (ridnew > 0) { Timestamp ts = Timestamp.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()); 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; } public int getReplyIDIncrement(final int mid, final int uid) { return getJdbcTemplate().execute((ConnectionCallback<Integer>) conn -> { conn.setAutoCommit(false); int replyNo; final int readOnly; final int userId; try (PreparedStatement ps = conn.prepareStatement("SELECT maxreplyid+1, readonly, user_id FROM messages WHERE message_id=? FOR UPDATE")) { ps.setInt(1, mid); try (ResultSet resultSet = ps.executeQuery()) { if (resultSet.next()) { replyNo = resultSet.getInt(1); readOnly = resultSet.getInt(2); userId = resultSet.getInt(3); } else { throw new IncorrectResultSizeDataAccessException("while getting getReplyIDIncrement, mid=" + mid, 1, 0); } } } // author can reply to his readonly post if (readOnly == 0 || uid == userId) { 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); } } } else { replyNo = -1; } 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, Integer reaction) { return likeMessage(mid, vuid, reaction, StringUtils.EMPTY); } @Transactional @Override public RecommendStatus likeMessage(int mid, int vuid, Integer reaction, String userUri) throws IllegalArgumentException { if (reaction == null) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", vuid) .addValue("uri", userUri) .addValue("mid", mid); boolean wasDeleted = getNamedParameterJdbcTemplate() .update("DELETE FROM favorites WHERE user_id=:uid AND message_id=:mid AND user_uri=:uri", sqlParameterSource) > 0; if (wasDeleted) { return RecommendStatus.Deleted; } else { return RecommendStatus.Error; } } boolean wasAdded = getJdbcTemplate() .update("INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", vuid, mid, reaction, 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 = ?", Integer.class, mid); 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 = ?", Integer.class, uid, mid); return !list.isEmpty() && list.get(0) == 1; } @Transactional(readOnly = true) @Override public int getMessagePrivacy(final int mid) { List<Integer> list = getJdbcTemplate().queryForList( "SELECT privacy FROM messages WHERE message_id = ?", Integer.class, mid); return list.isEmpty() ? -4 : list.get(0); } @Transactional(readOnly = true) @Override public Optional<Message> getMessage(final int mid) { List<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," + "GROUP_CONCAT(tags.name SEPARATOR ' '), txt.repliesby, txt.txt, '' as q, messages.updated as updated, 0 as to_uid, " + "NULL as to_name, txt.updated_at, '' as reply_user_uri, '' as to_uri, '' as reply_uri, 0 as html, 0 as unread 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 " + "LEFT JOIN messages_tags ON messages_tags.message_id=txt.message_id " + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id " + "WHERE messages.message_id = ? AND users.banned = 0 " + "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, " + "privacy, replies, attach, repliesby, q, updated_at, reply_user_uri, to_uri, reply_uri, html, unread", 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())); message.setEntities(MessageUtils.getEntities(message)); return Optional.of(message); } return Optional.empty(); } @Transactional(readOnly = true) @Override public Message getReply(final int mid, final int rid) { List<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.setCreated(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); } } msg.setEntities(MessageUtils.getEntities(msg)); 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 = ?", (rs, num) -> { User res = new User(); res.setUid(rs.getInt(1)); res.setName(rs.getString(2)); return res; }, mid); return list.isEmpty() ? null : list.get(0); } @Transactional(readOnly = true) @Override public List<Pair<Integer, User>> getMessagesRecommendations(final Collection<Integer> mids) { return getNamedParameterJdbcTemplate().query( "SELECT DISTINCT favorites.message_id, users.id, users.nick, favorites.user_uri FROM favorites " + "INNER JOIN users ON (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)) " + "AND favorites.message_id IN (:mids)", new MapSqlParameterSource("mids", mids), (rs, rowNum) -> { User user = new User(); user.setUid(rs.getInt(2)); user.setName(rs.getString(3)); user.setUri(URI.create(rs.getString(4))); return new ImmutablePair<>(rs.getInt(1), user); }); } @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); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages WHERE " + "(user_id=:uid OR " + "(EXISTS (SELECT 1 FROM subscr_users WHERE subscr_users.suser_id = :uid " + "AND subscr_users.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) " + "AND (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)))) " + (recommended ? "OR (EXISTS (SELECT 1 FROM favorites WHERE favorites.message_id=messages.message_id " + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) " + "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) " + "AND (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_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" : StringUtils.EMPTY) + ") " + (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> 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); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy AND users.banned = 0 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" + " ON messages.message_id = messages_tags.message_id" + " INNER JOIN users" + " ON messages.user_id=users.id" + " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid" + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " AND messages.privacy >= :privacy AND users.banned = 0" + " 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" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid" + (daysback > 0 ? " AND ts >= date(NOW() - INTERVAL :daysback day)" + " AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) + " AND privacy >= :privacy AND users.banned = 0 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 " + " INNER JOIN users" + " ON favorites.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20) as r" + " UNION ALL " + "SELECT message_id FROM " + "(SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " + "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" + " INNER JOIN users" + " ON favorites.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0 " + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " ORDER BY message_id DESC LIMIT 20", 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" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid and users.banned = 0" + (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY) + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20", 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<Message> getMessages(final User visitor, final List<Integer> mids) { if (CollectionUtils.isNotEmpty(mids)) { List<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 replies.reply_id != replies.replyto AND banned.message_id=replies.message_id " + "WHERE replies.message_id IN (:ids)) " + "SELECT messages.message_id, 0 as rid, 0 as replyto, " + "messages.user_id,users.nick, 0 as usr_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," + "GROUP_CONCAT(tags.name SEPARATOR ' '), messages_txt.repliesby, messages_txt.txt, '' as q, " + "messages.updated, 0 as to_uid, NULL as to_name, messages_txt.updated_at, '' as m_user_uri, " + "'' as to_uri, '' as msg_reply_uri, 0 as html, cast(messages.replies as signed)-cast(subscr_messages.last_read_rid as signed) > 0 as unread " + "FROM (messages INNER JOIN messages_txt " + "ON messages.message_id=messages_txt.message_id) " + "INNER JOIN users ON messages.user_id=users.id " + "LEFT JOIN subscr_messages " + "ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid " + "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 " + "LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id " + "LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id " + "WHERE messages.message_id IN (:ids) GROUP BY " + "messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, " + "messages.readonly, messages.privacy, messages.attach, messages.hidden, " + "messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, " + "m_user_uri, msg_reply_uri, html", 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()))); msgs.forEach(i -> i.setEntities(MessageUtils.getEntities(i))); List<Pair<Integer, User>> allRecommendations = getMessagesRecommendations(mids); msgs.forEach(m -> { m.setRecommendations(new HashSet<>(allRecommendations.stream() .filter(r -> r.getLeft().equals(m.getMid())).map(Pair::getRight).collect(Collectors.toList()))); m.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r))); }); 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, 0 as unread " + "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 && !user.isAnonymous()) { setRead(user, mid); } replies.forEach(i -> i.setEntities(MessageUtils.getEntities(i))); 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 NOT EXISTS (SELECT 1 FROM bl_users WHERE " + "user_id = ? AND bl_user_id = messages.user_id) AND " + "messages.replies>subscr_messages.last_read_rid", Integer.class, user.getUid(), 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; } } @Transactional @Override public boolean updateReplyUri(Message reply, URI replyUri) { return jdbcTemplate.update("UPDATE replies SET reply_uri=?, html=0 WHERE message_id=? AND reply_id=?", replyUri.toASCIIString(), reply.getMid(), reply.getRid()) > 0; } @Transactional(readOnly = true) @Override public boolean replyExists(URI replyUri) { return jdbcTemplate.queryForList("SELECT reply_id FROM replies WHERE reply_uri=?", Integer.class, replyUri.toASCIIString()).size() > 0; } @Transactional @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; } @Transactional(readOnly = true) @Override public String getMessageProperty(Integer mid, Integer rid, String key) { List<String> results = jdbcTemplate.queryForList( "SELECT property_value FROM messages_properties WHERE message_id=? AND reply_id=? AND property_key=?", String.class, mid, rid, key); if (results.size() > 0) { return results.get(0); } return StringUtils.EMPTY; } @Transactional @Override public void setMessageProperty(Integer mid, Integer rid, String key, String value) { SqlParameterSource parameterSource = new MapSqlParameterSource() .addValue("mid", mid) .addValue("rid", rid) .addValue("key", key) .addValue("value", value); if (StringUtils.isNotEmpty(value)) { try { getNamedParameterJdbcTemplate().update("INSERT INTO messages_properties(message_id, reply_id, property_key, property_value) " + "VALUES(:mid, :rid, :key, :value)", parameterSource); } catch (DataIntegrityViolationException ex) { getNamedParameterJdbcTemplate().update("UPDATE messages_properties SET property_value=:value " + "WHERE message_id=:mid AND reply_id=:rid AND property_key=:key", parameterSource); } } else { getNamedParameterJdbcTemplate().update("DELETE FROM messages_properties " + "WHERE message_id=:mid AND reply_id=:rid AND property_key=:key", parameterSource); } } @Transactional(readOnly = true) @Override public Optional<Pair<Integer, Integer>> findMessageByProperty(String key, String value) { List<Pair<Integer, Integer>> results = jdbcTemplate.query("SELECT message_id, reply_id FROM messages_properties " + "WHERE property_key=? AND property_value=?", (rs, rowNum) -> Pair.of(rs.getInt(1), rs.getInt(2)), key, value); if (results.size() > 0) { return Optional.of(results.get(0)); } return Optional.empty(); } }