/* * 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 . */ package com.juick.service; import com.juick.model.*; import com.juick.model.User; import com.juick.www.WebApp; import com.juick.util.MessageUtils; import com.juick.util.TagUtils; 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.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.lang.NonNull; 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.time.OffsetDateTime; import java.time.ZoneOffset; import java.time.temporal.ChronoUnit; 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 TagService tagService; @Inject private SearchService searchService; @Inject private StorageService storageService; @Inject private WebApp webApp; @Value("${photos_url:https://i.juick.com/}") private String baseImagesUrl; @Inject private User archiveUser; private class MessageMapper implements RowMapper { @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(MessagesServiceImpl.this.getOffsetDateTime(rs, 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.Hidden = rs.getBoolean(13); if (msg.getRid() == 0) { msg.setTags(tagService.getMessageTags(msg.getMid()).stream() .map(TagStats::getTag).collect(Collectors.toSet())); } msg.setRepliesBy(rs.getString(15)); msg.setText(rs.getString(16)); msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(17))); msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs, 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(MessagesServiceImpl.this.getOffsetDateTime(rs, 21).toInstant()); msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY))); msg.setHtml(rs.getBoolean(25)); msg.setUnread(rs.getInt(26) > 0); msg.setSubscribed(rs.getInt("subscribed") > 0); if (StringUtils.isNotEmpty(msg.getAttachmentType())) { try { storageService.setAttachmentMetadata(baseImagesUrl, msg); } catch (Exception e) { logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e); } } return msg; } } /** * @see Java, * JDBC and MySQL Types */ @Transactional @Override public int createMessage(final int uid, final String txt, final String attachment, @NonNull final Set tags) { SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages") .usingColumns("user_id", "attach", "ts", "readonly", "updated").usingGeneratedKeyColumns("message_id"); var insertMap = new MapSqlParameterSource(); insertMap.addValue("user_id", uid); var now = Instant.now(); insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); if (StringUtils.isNotEmpty(attachment)) { insertMap.addValue("attach", attachment); } insertMap.addValue("readonly", TagUtils.hasTag(tags, "readonly")); int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue(); if (mid > 0) { if (CollectionUtils.isNotEmpty(tags)) { var newTags = new ArrayList<>(tags); getJdbcTemplate().batchUpdate("INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)", new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { ps.setInt(1, mid); ps.setInt(2, newTags.get(i).getId()); } @Override public int getBatchSize() { return tags.size(); } }); } getNamedParameterJdbcTemplate() .update("INSERT INTO messages_txt(message_id, txt, updated_at) VALUES (:mid, :txt, :now)", new MapSqlParameterSource() .addValue("mid", mid) .addValue("txt", txt) .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())); getNamedParameterJdbcTemplate() .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid", new MapSqlParameterSource() .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("uid", uid)); } return mid; } /** * @param mid * @param rid * @param user * @param txt * @param attachment * @return * @see Java, * JDBC and MySQL Types */ @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) { var now = Instant.now(); getNamedParameterJdbcTemplate() .update( "INSERT INTO replies(message_id, reply_id, user_id, replyto, attach, txt, ts, updated_at, user_uri) " + "VALUES (:mid, :rid, :uid, :replyto, :attach, :txt, :ts, :updated_at, :user_uri)", new MapSqlParameterSource() .addValue("mid", mid) .addValue("rid", ridnew) .addValue("uid", user.getUid()) .addValue("replyto", rid) .addValue("attach", attachment) .addValue("txt", txt) .addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("user_uri", user.getUri().toASCIIString())); getNamedParameterJdbcTemplate() .update( "UPDATE messages SET replies = replies + 1, updated=:updated WHERE message_id = :message_id", new MapSqlParameterSource() .addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("message_id", mid)); setLastReadComment(user, mid, ridnew); getNamedParameterJdbcTemplate() .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid", new MapSqlParameterSource() .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()) .addValue("uid", user.getUid())); } return ridnew; } public int getReplyIDIncrement(final int mid, final int uid) { return getJdbcTemplate().execute((ConnectionCallback) conn -> { conn.setAutoCommit(false); int replyNo; final boolean readOnly; final int userId; try (PreparedStatement ps = conn.prepareStatement( "SELECT maxreplyid+1, readonly, user_id FROM messages WHERE message_id=? " + forUpdate())) { ps.setInt(1, mid); try (ResultSet resultSet = ps.executeQuery()) { if (resultSet.next()) { replyNo = resultSet.getInt(1); readOnly = resultSet.getBoolean(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 || 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 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, users.nick 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) { logger.info("Message {} recommended by {} ({})", mid, vuid, 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 { var now = toDateTime(Instant.now().atOffset(ZoneOffset.UTC)); boolean wasAdded = getNamedParameterJdbcTemplate().update(""" INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (:user_id, :message_id, :ts, :like_id, :user_uri) """, new MapSqlParameterSource() .addValue("user_id", vuid) .addValue("message_id", mid) .addValue("ts", now, dateTimeType()) .addValue("like_id", Reaction.LIKE) .addValue("user_uri", 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 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 (?, ?, ?, ?, ?)", vuid, mid, toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), 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 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 list = getJdbcTemplate().queryForList("SELECT readonly FROM messages WHERE message_id = ?", Integer.class, mid); return !list.isEmpty() && list.get(0) == 1; } @Override public void setReadOnly(final int mid, final boolean readonly) { getJdbcTemplate().update("UPDATE messages SET readonly=? WHERE message_id=?", readonly, mid); } @Transactional(readOnly = true) @Override public int getMessagePrivacy(final int mid) { List list = getJdbcTemplate().queryForList("SELECT privacy FROM messages WHERE message_id = ?", Integer.class, mid); return list.isEmpty() ? -4 : list.get(0); } public Optional getMessage(int mid) { return getMessage(0, mid); } @Transactional(readOnly = true) @Override public Optional getMessage(int uid, final int mid) { var messages = getMessages(uid, List.of(mid)); if (messages.size() == 1) { var message = messages.get(0); if (!message.getUser().isBanned()) { return Optional.of(messages.get(0)); } } return Optional.empty(); } @Transactional(readOnly = true) @Override public Message getReply(final int mid, final int rid) { var sql = """ SELECT replies.user_id, users.nick, replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(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 = ? """; List list = getJdbcTemplate().query(sql, (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(getOffsetDateTime(rs, 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(getOffsetDateTime(rs, 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 { storageService.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 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 List> getMessagesRecommendations(final Collection 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 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 getTag(final int tid, final int visitorUid, final int before, final int cnt) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("tid", tid) .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 getTags(final String tids, final int visitorUid, final int before, final int cnt) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .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 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 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 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 getDiscussions(final int uid, final Long to) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("to", fromEpochMilli(to), dateTimeType()); 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, messages.message_id DESC " + limit(20), sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } @Transactional(readOnly = true) @Override public List 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 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 getSearch(final User visitor, final String search, final int page) { return searchService.searchInAllMessages(visitor, search, page); } @Transactional(readOnly = true) @Override public List 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 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 getUserBlogAtDay(final int uid, final int privacy, final int daysback) { var backDate = Instant.now().minus(daysback, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC); SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) .addValue("date", toDateTime(backDate), dateTimeType()); 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" + " AND ts < :date" : 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 getUserBlogWithRecommendations(final User user, final User visitor, final int privacy, final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", user.getUid()) .addValue("vid", visitor.getUid()) .addValue("privacy", privacy).addValue("before", before); return getNamedParameterJdbcTemplate() .queryForList("SELECT message_id FROM " + "(SELECT favorites.message_id FROM favorites " + " INNER JOIN messages ON messages.message_id = favorites.message_id" + " INNER JOIN users ON messages.user_id = users.id" + " WHERE favorites.user_id = :uid AND users.banned = 0" + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid 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 = favorites.message_id) and :vid = bt.user_id)" + (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY) + " ORDER BY messages.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" + " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid 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 :vid = bt.user_id)" + (before > 0 ? " AND messages.message_id < :before" : StringUtils.EMPTY) + " AND privacy >= :privacy ORDER BY messages.message_id DESC " + limit(20) + ") as m " + "ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class); } @Transactional(readOnly = true) @Override public List 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 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 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 getMessages(int uid, final List mids) { if (CollectionUtils.isNotEmpty(mids)) { var query = (omitRecursiveKeyword ? "WITH " : "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, users.banned as usr_banned, messages.ts, messages.readonly, messages.privacy, 1.*messages.replies-COUNT(DISTINCT banned.reply_id) as replies, messages.attach, COUNT(DISTINCT favorites.user_id) AS likes, messages.hidden, '' as tags, 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, (1.*messages.replies - subscr_messages.last_read_rid) as unread, (SELECT CASE WHEN EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid) THEN 1 ELSE 0 END) subscribed 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, messages.user_id, users.nick, users.banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, messages_txt.repliesby, messages_txt.txt, messages.updated, messages.replies, updated_at, subscr_messages.last_read_rid"""; List msgs = getNamedParameterJdbcTemplate().query(query, new MapSqlParameterSource("ids", mids) .addValue("uid", uid), new MessageMapper()); Map> 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> allRecommendations = getMessagesRecommendations(mids); msgs.forEach(m -> { m.setRecommendations(new HashSet<>(allRecommendations.stream() .filter(r -> r.getLeft().equals(m.getMid())).map(Pair::getRight).toList())); m.getRecommendations().forEach(r -> r.setAvatar(webApp.getAvatarUrl(r))); }); return msgs; } return Collections.emptyList(); } private Map> updateReactionsFor(final List mids) { // This method always called from the transactional block, so it should not be // marked as transactional itself 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, r.description""", new MapSqlParameterSource("mids", mids), (ResultSet rs) -> { Map> 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 getReplies(final User user, final int mid) { List replies = getNamedParameterJdbcTemplate().query((omitRecursiveKeyword ? "WITH " : "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, " + "COALESCE(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, " + "0 as subscribed " + "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() .addValue("mid", mid) .addValue("uid", user.getUid()) .addValue("now", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), dateTimeType()), new MessageMapper()); replies.forEach(i -> { i.setEntities(MessageUtils.getEntities(i)); i.getUser().setAvatar(webApp.getAvatarUrl(i.getUser())); }); return replies; } @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) { Instant now = Instant.now(); Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS); Optional message = getMessage(uid, mid); if (message.isPresent()) { if (message.get().getUser().getUid() != uid) return false; Instant ts = message.get().getUpdatedAt(); SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) .addValue("archive_uid", archiveUser.getUid()); if (ts.compareTo(messageDeletingWindow) >= 0) { // TODO: cascade delete getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", parameters); getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", parameters); getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", parameters); getNamedParameterJdbcTemplate().update("DELETE FROM messages_properties WHERE message_id = :mid", parameters); if (getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", parameters) > 0) { return getNamedParameterJdbcTemplate() .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0; } } else { return getNamedParameterJdbcTemplate().update( "UPDATE messages SET user_id=:archive_uid WHERE message_id = :mid AND user_id = :uid", parameters) > 0; } } return false; } @Transactional @Override public boolean deleteReply(final int uid, final int mid, final int rid) { Instant now = Instant.now(); Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS); var message = getMessage(uid, mid); if (message.isPresent()) { Message reply = getReply(mid, rid); if (reply != null) { Instant ts = reply.getUpdatedAt(); User author = message.get().getUser(); SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) .addValue("rid", rid).addValue("archive_uid", archiveUser.getUid()); if (ts.compareTo(messageDeletingWindow) >= 0) { boolean result; // allow to delete author replies and replies to author if (author.getUid() == uid) { result = getNamedParameterJdbcTemplate() .update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", parameters) > 0; } else { result = getNamedParameterJdbcTemplate().update( "DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", parameters) > 0; } if (result) { getNamedParameterJdbcTemplate() .update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", parameters); updateRepliesBy(mid); return true; } } else { // only archive author replies return getNamedParameterJdbcTemplate().update( "UPDATE replies SET user_id=:archive_uid WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid", parameters) > 0; } } } return false; } @Transactional(readOnly = true) @Override public List getLastMessages(int hours) { return getNamedParameterJdbcTemplate() .queryForList( "SELECT message_id FROM messages WHERE messages.ts > :hours", new MapSqlParameterSource() .addValue("hours", toDateTime(Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC)), dateTimeType()), Integer.class); } @Transactional(readOnly = true) @Override public List getLastReplies(int hours) { var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC); return getNamedParameterJdbcTemplate().query(""" SELECT users2.nick,replies.message_id,replies.reply_id, users.nick,replies.txt,replies.ts,replies.attach,replies.ts, 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>:datetime AND messages.privacy>0 """, new MapSqlParameterSource() .addValue("datetime", toDateTime(datetime), dateTimeType()), (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; }); } @Transactional(readOnly = true) @Override public List getPopularCandidates() { var beforeTime = Instant.now().minus(2, ChronoUnit.HOURS); var nsfw = tagService.getTag("NSFW", true); var sql = """ SELECT replies.message_id FROM replies INNER JOIN messages ON replies.message_id = messages.message_id LEFT JOIN favorites ON favorites.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) != :nsfw_id AND replies.ts > :before AND NOT EXISTS (SELECT 1 FROM favorites WHERE message_id = messages.message_id AND user_id = 2) GROUP BY replies.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 > :before AND NOT EXISTS (SELECT 1 FROM favorites WHERE message_id = messages.message_id AND user_id = 2) GROUP BY favorites.message_id HAVING COUNT(DISTINCT favorites.user_id) > 2 """; return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource() .addValue("nsfw_id", nsfw.getId()) .addValue("before", toDateTime(beforeTime.atOffset(ZoneOffset.UTC)), dateTimeType()), 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 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, boolean foreign) { Instant now = Instant.now(); Instant messageEditingWindow = now.minus(15, ChronoUnit.MINUTES); if (rid == 0) { Optional message = getMessage(mid); if (message.isPresent()) { Instant ts = message.get().getUpdatedAt(); if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return namedParameterJdbcTemplate.update( "UPDATE messages_txt SET txt=:txt, updated_at=:now WHERE messages_txt.message_id=:mid", new MapSqlParameterSource() .addValue("txt", body) .addValue("mid", mid) .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0; } } return false; } else { Message reply = getReply(mid, rid); if (reply != null) { Instant ts = reply.getUpdatedAt(); if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return namedParameterJdbcTemplate.update( "UPDATE replies SET txt=:txt, updated_at=:now WHERE message_id=:mid AND reply_id=:rid", new MapSqlParameterSource() .addValue("txt", body) .addValue("mid", mid) .addValue("rid", rid) .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0; } } return false; } } @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 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)) { var exists = getMessageProperty(mid, rid, key); if (StringUtils.isEmpty(exists)) { getNamedParameterJdbcTemplate() .update("INSERT INTO messages_properties(message_id, reply_id, property_key, property_value) " + "VALUES(:mid, :rid, :key, :value)", parameterSource); } else { 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> findMessageByProperty(String key, String value) { List> 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(); } }