/*
* Copyright (C) 2008-2017, Juick
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU Affero General Public License as
* published by the Free Software Foundation, either version 3 of the
* License, or (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* You should have received a copy of the GNU Affero General Public License
* along with this program. If not, see .
*/
package com.juick.service;
import com.juick.*;
import com.juick.model.AnonymousUser;
import com.juick.model.PrivacyOpts;
import com.juick.model.ResponseReply;
import com.juick.server.util.HttpNotFoundException;
import com.juick.util.MessageUtils;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.IncorrectResultSizeDataAccessException;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.inject.Inject;
import java.net.URI;
import java.sql.*;
import java.time.Instant;
import java.util.*;
import java.util.Date;
import java.util.stream.Collectors;
/**
* Created by aalexeev on 11/13/16.
*/
@Repository
public class MessagesServiceImpl extends BaseJdbcService implements MessagesService {
private static final Logger logger = LoggerFactory.getLogger(MessagesServiceImpl.class);
@Inject
private UserService userService;
@Inject
private TagService tagService;
@Inject
private SearchService searchService;
@Inject
private ImagesService imagesService;
@Value("${img_url:https://i.juick.com/}")
private String baseImagesUrl;
private class MessageMapper implements RowMapper {
@Override
public Message mapRow(ResultSet rs, int rowNum) throws SQLException {
Message msg = new Message();
msg.setMid(rs.getInt(1));
msg.setRid(rs.getInt(2));
msg.setReplyto(rs.getInt(3));
User user = new User();
user.setUid(rs.getInt(4));
user.setName(Optional.ofNullable(rs.getString(5)).orElse(AnonymousUser.INSTANCE.getName()));
user.setBanned(rs.getBoolean(6));
user.setUri(URI.create(Optional.ofNullable(rs.getString(22)).orElse(StringUtils.EMPTY)));
msg.setUser(user);
msg.setTimestamp(rs.getTimestamp(7).toInstant());
msg.ReadOnly = rs.getBoolean(8);
msg.setPrivacy(rs.getInt(9));
msg.FriendsOnly = msg.getPrivacy() < 0;
msg.setReplies(rs.getInt(10));
msg.setAttachmentType(rs.getString(11));
msg.setLikes(rs.getInt(12));
msg.Hidden = rs.getBoolean(13);
String tagsStr = rs.getString(14);
msg.setTags(MessageUtils.parseTags(tagsStr));
msg.setRepliesBy(rs.getString(15));
msg.setText(rs.getString(16));
msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(17)));
msg.setUpdated(rs.getTimestamp(18).toInstant());
int quoteUid = rs.getInt(19);
if (quoteUid > 0) {
User quoteUser = new User();
quoteUser.setUid(quoteUid);
quoteUser.setName(rs.getString(20));
msg.setTo(quoteUser);
}
msg.setUpdatedAt(rs.getTimestamp(21).toInstant());
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 Java, JDBC and MySQL Types
*/
@Transactional
@Override
public int createMessage(final int uid, final String txt, final String attachment, final Collection tags) {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages")
.usingColumns("user_id", "attach", "ts")
.usingGeneratedKeyColumns("message_id");
Map insertMap = new HashMap<>();
insertMap.put("user_id", uid);
Instant now = Instant.now();
insertMap.put("ts", Timestamp.from(now));
if (StringUtils.isNotEmpty(attachment)) {
insertMap.put("attach", attachment);
}
int mid = simpleJdbcInsert.executeAndReturnKey(insertMap).intValue();
if (mid > 0) {
String tagsNames = StringUtils.EMPTY;
if (CollectionUtils.isNotEmpty(tags)) {
StringBuilder tasNamesBuilder = new StringBuilder();
List params = new ArrayList<>(tags.size());
boolean next = false;
for (Tag tag : tags) {
if (next) {
tasNamesBuilder.append(" ");
} else
next = true;
tasNamesBuilder.append(tag.getName());
params.add(new Object[]{mid, tag.TID});
}
tagsNames = tasNamesBuilder.toString();
getJdbcTemplate().batchUpdate(
"INSERT INTO messages_tags(message_id, tag_id) VALUES (?, ?)",
params, new int[]{Types.INTEGER, Types.INTEGER});
}
getJdbcTemplate().update(
"INSERT INTO messages_txt(message_id, tags, txt, updated_at) VALUES (?, ?, ?, ?)",
new Object[]{mid, tagsNames, txt, Timestamp.from(now)},
new int[]{Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP});
getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", Timestamp.from(now), 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);
Date ts = Date.from(Instant.now());
getJdbcTemplate().update("INSERT INTO replies(message_id, reply_id, user_id, replyto, attach, txt, ts, updated_at, user_uri) " +
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
mid, ridnew, user.getUid(), rid, attachment, txt, ts, ts, user.getUri().toASCIIString());
if (ridnew > 0) {
getJdbcTemplate().update(
"UPDATE messages SET replies = replies + 1, updated=? WHERE message_id = ?",
ts, mid);
setLastReadComment(user, mid, ridnew);
getJdbcTemplate().update("UPDATE users SET lastmessage=? where id=?", ts, user.getUid());
}
return ridnew;
}
@Override
public int getReplyIDIncrement(final int mid) {
return getJdbcTemplate().execute((ConnectionCallback) conn -> {
conn.setAutoCommit(false);
final int replyNo;
try (PreparedStatement ps = conn.prepareStatement("SELECT maxreplyid+1 FROM messages WHERE message_id=? FOR UPDATE")) {
ps.setInt(1, mid);
try (ResultSet resultSet = ps.executeQuery()) {
if (resultSet.next()) {
replyNo = resultSet.getInt(1);
} else {
throw new IncorrectResultSizeDataAccessException("while getting getReplyIDIncrement, mid=" + mid, 1, 0);
}
}
}
try (PreparedStatement ps = conn.prepareStatement("UPDATE messages SET maxreplyid=? WHERE message_id=?")) {
ps.setInt(1, replyNo);
ps.setInt(2, mid);
if (ps.executeUpdate() != 1) {
throw new IncorrectResultSizeDataAccessException("Cannot find a message to update: " + mid, 1, 0);
}
}
conn.commit();
return replyNo;
});
}
@Transactional
void updateRepliesBy(int mid) {
List users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies " +
"INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? " +
"GROUP BY replies.user_id ORDER BY COUNT(replies.reply_id) DESC LIMIT 5", String.class, mid);
String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(","));
getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid);
}
@Transactional
@Override
public RecommendStatus recommendMessage(final int mid, final int vuid) {
int wasDeleted = getJdbcTemplate()
.update("DELETE FROM favorites WHERE user_id=? AND message_id=? and like_id=?", vuid, mid, Reaction.LIKE);
if (wasDeleted > 0) {
return RecommendStatus.Deleted;
} else {
boolean wasAdded = getJdbcTemplate()
.update("INSERT INTO favorites(user_id, message_id, ts, like_id ) VALUES (?, ?, NOW(), ?)", vuid, mid,Reaction.LIKE) == 1;
if (wasAdded) {
return RecommendStatus.Added;
}
}
return RecommendStatus.Error;
}
@Override
public List listReactions() {
return jdbcTemplate.query("SELECT like_id, description FROM reactions", (rs, rowNum) -> {
Reaction reaction = new Reaction(rs.getInt("like_id"));
reaction.setDescription(rs.getString("description"));
return reaction;
});
}
@Transactional
@Override
public RecommendStatus likeMessage(int mid, int vuid, int reactionId) throws IllegalArgumentException {
boolean wasAdded = getJdbcTemplate()
.update("INSERT INTO favorites(user_id, message_id, ts, like_id ) VALUES (?, ?, NOW(), ?)", vuid, mid, reactionId) == 1;
if (wasAdded) {
return RecommendStatus.Added;
}
return RecommendStatus.Error;
}
@Transactional(readOnly = true)
@Override
public boolean canViewThread(final int mid, final int uid) {
List 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 = ?",
new Object[]{mid},
Integer.class);
return !list.isEmpty() && list.get(0) == 1;
}
@Transactional(readOnly = true)
@Override
public boolean isSubscribed(final int uid, final int mid) {
List list = getJdbcTemplate().queryForList(
"SELECT 1 FROM subscr_messages WHERE suser_id = ? AND message_id = ?",
new Object[]{uid, mid},
Integer.class);
return !list.isEmpty() && list.get(0) == 1;
}
@Transactional(readOnly = true)
@Override
public int getMessagePrivacy(final int mid) {
List list = getJdbcTemplate().queryForList(
"SELECT privacy FROM messages WHERE message_id = ?",
new Object[]{mid},
Integer.class);
return list.isEmpty() ? -4 : list.get(0);
}
@Transactional(readOnly = true)
@Override
public com.juick.Message getMessage(final int mid) {
List list = getJdbcTemplate().query(
"SELECT messages.message_id as mid, 0 as rid, 0 as replyto, "
+ "messages.user_id as uid, users.nick, users.banned as banned, "
+ ""
+ "messages.ts,"
+ "messages.readonly, messages.privacy, messages.replies,"
+ "messages.attach, COUNT(DISTINCT favorites.user_id) as likes, messages.hidden,"
+ "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated as updated, 0 as to_uid, "
+ "NULL as to_name, txt.updated_at, '' as uri FROM messages "
+ "INNER JOIN users ON messages.user_id = users.id "
+ "INNER JOIN messages_txt AS txt "
+ "ON messages.message_id = txt.message_id "
+ "LEFT JOIN favorites "
+ "ON messages.message_id = favorites.message_id AND favorites.like_id=1 "
+ "WHERE messages.message_id = ? "
+ "GROUP BY mid, rid, replyto, uid, nick, banned, messages.ts, readonly, "
+ "privacy, replies, attach, tags, repliesby, q, updated_at, uri",
new MessageMapper(),
mid);
if (!list.isEmpty()) {
final Message message = list.get(0);
Map> reactionStats = updateReactionsFor(Collections.singletonList(mid));
message.setReactions(reactionStats.get(message.getMid()));
return message;
}
return null;
}
@Transactional(readOnly = true)
@Override
public com.juick.Message getReply(final int mid, final int rid) {
List 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 "
+ "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));
msg.setReplyto(rs.getInt(3));
msg.setTimestamp(rs.getTimestamp(4).toInstant());
msg.setAttachmentType(rs.getString(5));
msg.setText(rs.getString(6));
String quote = rs.getString(7);
if (!StringUtils.isEmpty(quote)) {
msg.setReplyQuote(MessageUtils.formatQuote(quote));
}
int quoteUid = rs.getInt(8);
if (quoteUid > 0) {
User quoteUser = new User();
quoteUser.setUid(quoteUid);
quoteUser.setName(rs.getString(9));
msg.setTo(quoteUser);
}
msg.setUpdatedAt(rs.getTimestamp(10).toInstant());
if (StringUtils.isNotEmpty(msg.getAttachmentType())) {
try {
imagesService.setAttachmentMetadata(baseImagesUrl, msg);
} catch (Exception e) {
logger.warn("exception reading images for mid {} rid {}", msg.getMid(), msg.getRid(), e);
}
}
return msg;
},
mid, rid);
return list.isEmpty() ? null : list.get(0);
}
@Transactional(readOnly = true)
@Override
public User getMessageAuthor(final int mid) {
List list = getJdbcTemplate().query(
"SELECT messages.user_id, users.nick "
+ "FROM messages INNER JOIN users ON messages.user_id = users.id WHERE messages.message_id = ?",
new Object[]{mid},
(rs, num) -> {
User res = new com.juick.User();
res.setUid(rs.getInt(1));
res.setName(rs.getString(2));
return res;
});
return list.isEmpty() ?
null : list.get(0);
}
@Transactional(readOnly = true)
@Override
public List getMessageRecommendations(final int mid) {
return getJdbcTemplate().queryForList(
"SELECT DISTINCT users.nick FROM favorites " +
"INNER JOIN users ON (favorites.message_id = ? AND favorites.user_id = users.id) " +
"INNER JOIN messages m ON favorites.message_id=m.message_id WHERE favorites.like_id=1 " +
"AND NOT EXISTS (SELECT 1 FROM bl_users WHERE " +
"(user_id = favorites.user_id AND bl_user_id = m.user_id) " +
"OR (user_id = m.user_id AND bl_user_id = favorites.user_id))",
String.class, mid);
}
@Transactional(readOnly = true)
@Override
public List 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("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 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 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);
List mids = getNamedParameterJdbcTemplate().queryForList(
"(SELECT message_id FROM messages " +
" INNER JOIN subscr_users ON (subscr_users.suser_id = :uid AND subscr_users.user_id = messages.user_id) " +
" WHERE " +
(before > 0 ?
" message_id < :before AND " : StringUtils.EMPTY) +
" (privacy >= 0 OR (privacy >= -2 AND privacy <= -1" +
" AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id))) " +
" AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " +
"(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :uid = bt.user_id))" +
" UNION " +
" (SELECT message_id FROM messages WHERE user_id=:uid " +
(before > 0 ?
" AND message_id < :before " : StringUtils.EMPTY) +
(recommended ?
") UNION " +
" (SELECT f.message_id as message_id FROM favorites f INNER JOIN messages ON " +
"f.message_id=messages.message_id WHERE " +
"EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)" +
(before > 0 ?
" AND f.message_id < :before " : StringUtils.EMPTY) : StringUtils.EMPTY) +
" AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" +
" AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN " +
"(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :uid = bt.user_id)) " +
"ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
return mids;
}
@Transactional(readOnly = true)
@Override
public List 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", 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 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 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 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);
;
if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
throw new HttpNotFoundException();
}
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages WHERE user_id = :uid" +
(before > 0 ?
" AND message_id < :before" : StringUtils.EMPTY) +
" AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List getUserTag(final int uid, final int tid, final int privacy, final int before) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("tid", tid)
.addValue("privacy", privacy)
.addValue("before", before);
if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
throw new HttpNotFoundException();
}
return getNamedParameterJdbcTemplate().queryForList(
"SELECT messages.message_id FROM messages_tags INNER JOIN messages " +
" USING (message_id) WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid " +
(before > 0 ?
" AND messages.message_id < :before " : StringUtils.EMPTY) +
" AND messages.privacy >= :privacy ORDER BY messages.message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List getUserBlogAtDay(final int uid, final int privacy, final int daysback) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("privacy", privacy)
.addValue("daysback", daysback);
if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
throw new HttpNotFoundException();
}
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages WHERE user_id = :uid" +
(daysback > 0 ?
" AND ts >= date(NOW() - INTERVAL :daysback day)" +
" AND ts < date(NOW() - INTERVAL :daysback day + INTERVAL 1 day)" : StringUtils.EMPTY) +
" AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List getUserBlogWithRecommendations(final int uid, final int privacy, final int before) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("privacy", privacy)
.addValue("before", before);
if (userService.getUserByUID(uid).orElseThrow(IllegalStateException::new).isBanned()) {
throw new HttpNotFoundException();
}
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM " +
"(SELECT message_id FROM favorites " +
" WHERE user_id = :uid " +
(before > 0 ?
" AND message_id < :before " : StringUtils.EMPTY) +
" ORDER BY message_id DESC LIMIT 20) as r" +
" UNION ALL " +
"SELECT message_id FROM " +
"(SELECT message_id FROM messages WHERE user_id = :uid" +
(before > 0 ?
" AND message_id < :before" : StringUtils.EMPTY) +
" AND privacy >= :privacy ORDER BY message_id DESC LIMIT 20) as m " +
"ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List getUserRecommendations(final int uid, final int before) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("before", before);
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM favorites " +
" WHERE user_id = :uid " +
(before > 0 ?
" AND message_id < :before " : StringUtils.EMPTY) +
" ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List getUserPhotos(final int uid, final int privacy, final int before) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("privacy", privacy)
.addValue("before", before);
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages WHERE user_id = :uid " +
(before > 0 ?
" AND message_id < :before " : StringUtils.EMPTY) +
" AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC LIMIT 20",
sqlParameterSource,
Integer.class);
}
@Transactional(readOnly = true)
@Override
public List 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(final User visitor, final List mids) {
if (CollectionUtils.isNotEmpty(mids)) {
List msgs = getNamedParameterJdbcTemplate().query(
"WITH RECURSIVE banned(message_id, reply_id) "
+ "AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) "
+ "AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) "
+ "OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)) "
+ "UNION ALL SELECT replies.message_id, replies.reply_id FROM replies INNER JOIN banned "
+ "ON banned.reply_id = replies.replyto AND banned.message_id=replies.message_id "
+ "WHERE replies.message_id IN (:ids)) "
+ "SELECT messages.message_id, 0 as rid, 0 as replyto, "
+ "messages.user_id,users.nick, 0 as banned, "
+ "messages.ts,"
+ "messages.readonly,messages.privacy, messages.replies-COUNT(DISTINCT banned.reply_id) as replies,"
+ "messages.attach,COUNT(DISTINCT favorites.user_id) AS likes,messages.hidden,"
+ "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q, "
+ "messages.updated, 0 as to_uid, NULL as to_name, messages_txt.updated_at, '' as uri "
+ "FROM (messages INNER JOIN messages_txt "
+ "ON messages.message_id=messages_txt.message_id) "
+ "INNER JOIN users ON messages.user_id=users.id "
+ "LEFT JOIN favorites "
+ "ON messages.message_id = favorites.message_id AND favorites.like_id=1 "
+ "LEFT JOIN banned "
+ "ON messages.message_id = banned.message_id "
+ "WHERE messages.message_id IN (:ids) GROUP BY "
+ "messages.message_id, rid, replyto, messages.user_id, users.nick, banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, messages_txt.tags, messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, uri",
new MapSqlParameterSource("ids", mids)
.addValue("uid", visitor.getUid()),
new MessageMapper());
Map> likes = updateReactionsFor(mids);
msgs.forEach(i -> i.setReactions(likes.get(i.getMid())));
msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
return msgs;
}
return Collections.emptyList();
}
@Transactional(readOnly = true)
@Override
public Map> updateReactionsFor(final List 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> 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(
"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 " +
"FROM replies LEFT JOIN users " +
"ON replies.user_id = users.id " +
"LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " +
"LEFT JOIN messages_txt t on replies.message_id = t.message_id " +
"LEFT JOIN messages m on replies.message_id = m.message_id " +
"LEFT JOIN users qu ON qw.user_id=qu.id " +
"LEFT JOIN users mu ON m.user_id=mu.id " +
"WHERE replies.message_id = :mid " +
"AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)" +
"AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) " +
"AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) " +
"ORDER BY replies.reply_id ASC",
new MapSqlParameterSource("mid", mid).addValue("uid", user.getUid()),
new MessageMapper());
if (replies.size() > 0) {
setRead(user, mid);
}
return replies;
}
@Transactional
@Override
public boolean setMessagePopular(final int mid, final int popular) {
int ret;
MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("mid", mid)
.addValue("popular", popular);
switch (popular) {
case -2:
ret = getNamedParameterJdbcTemplate().update(
"UPDATE messages SET hidden = 1 WHERE message_id = :mid",
sqlParameterSource);
break;
case -1:
sqlParameterSource.addValue("popular", 0);
default:
ret = getNamedParameterJdbcTemplate().update(
"UPDATE messages SET popular = :popular WHERE message_id = :mid",
sqlParameterSource);
break;
}
if (popular == -1)
ret = getNamedParameterJdbcTemplate().update(
"INSERT INTO top_ignore_messages VALUES (:mid)",
sqlParameterSource);
return ret > 0;
}
@Transactional
@Override
public boolean setMessagePrivacy(final int mid) {
return getJdbcTemplate().update("UPDATE messages SET privacy=1 WHERE message_id=?", mid) > 0;
}
@Transactional
@Override
public boolean deleteMessage(final int uid, final int mid) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("mid", mid)
.addValue("uid", uid);
if (getNamedParameterJdbcTemplate().update(
"DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", sqlParameterSource) > 0) {
getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", sqlParameterSource);
getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", sqlParameterSource);
getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", sqlParameterSource);
getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", sqlParameterSource);
return true;
}
return false;
}
@Transactional
@Override
public boolean deleteReply(final int uid, final int mid, final int rid) {
User author = getMessageAuthor(mid);
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("mid", mid)
.addValue("uid", uid)
.addValue("rid", rid);
boolean result;
if (author.getUid() == uid) {
result = getNamedParameterJdbcTemplate()
.update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid", sqlParameterSource) > 0;
} else {
result = getNamedParameterJdbcTemplate()
.update("DELETE FROM replies WHERE message_id=:mid AND reply_id=:rid AND user_id=:uid"
, sqlParameterSource) > 0;
}
if (result) {
getNamedParameterJdbcTemplate().update("UPDATE messages SET replies=replies-1 WHERE message_id=:mid", sqlParameterSource);
updateRepliesBy(mid);
return true;
}
return false;
}
@Transactional(readOnly = true)
@Override
public List 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 getLastReplies(int hours) {
return getJdbcTemplate().query("SELECT users2.nick,replies.message_id,replies.reply_id," +
"users.nick,replies.txt," +
"replies.ts,replies.attach,replies.ts+0 " +
"FROM ((replies INNER JOIN users ON replies.user_id=users.id) " +
"INNER JOIN messages ON replies.message_id=messages.message_id) " +
"INNER JOIN users AS users2 ON messages.user_id=users2.id " +
"WHERE replies.ts>TIMESTAMPADD(HOUR,?,NOW()) AND messages.privacy>0", (rs, rowNum) -> {
ResponseReply reply = new ResponseReply();
reply.setMuname(rs.getString(1));
reply.setMid(rs.getInt(2));
reply.setRid(rs.getInt(3));
reply.setUname(rs.getString(4));
reply.setDescription(rs.getString(5));
reply.setPubDate(rs.getTimestamp(6));
reply.setAttachmentType(rs.getString(7));
return reply;
}, -hours);
}
@Transactional(readOnly = true)
@Override
public List 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 getUnread(User user) {
return jdbcTemplate.queryForList(
"select subscr_messages.message_id " +
"from subscr_messages inner join messages on subscr_messages.message_id=messages.message_id " +
"where subscr_messages.suser_id=? and " +
"messages.replies>subscr_messages.last_read_rid",
Integer.class, user.getUid());
}
@Transactional
@Override
public boolean updateMessage(Integer mid, Integer rid, String body) {
Instant now = Instant.now();
if (rid == 0) {
return jdbcTemplate.update("UPDATE messages_txt SET txt=?, updated_at=? WHERE message_id=?", body, Timestamp.from(now), mid) > 0;
} else {
return jdbcTemplate.update("UPDATE replies SET txt=?, updated_at=? WHERE message_id=? and reply_id=?",
body, Timestamp.from(now), mid, rid) > 0;
}
}
}