diff options
Diffstat (limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java')
| -rw-r--r-- | src/main/java/com/juick/service/MessagesServiceImpl.java | 87 |
1 files changed, 51 insertions, 36 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 68d47429..bb50d953 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -296,9 +296,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ 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; + var now = 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, Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("like_id", Reaction.LIKE) + .addValue("user_uri", userUri)) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -342,9 +349,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ } } boolean wasAdded = getJdbcTemplate().update( - "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)", + "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)", vuid, - mid, reaction, userUri) == 1; + mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -659,7 +666,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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", + + "ORDER BY updated DESC, messages.message_id DESC LIMIT 20", sqlParameterSource, (rs, rowNum) -> rs.getInt(1)); } @@ -896,27 +903,30 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @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, " + "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 " - + "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()), + + "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 " + + "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", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE), new MessageMapper()); if (replies.size() > 0 && !user.isAnonymous()) { setRead(user, mid); @@ -1022,12 +1032,17 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @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) -> { + 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", datetime, Types.TIMESTAMP_WITH_TIMEZONE), (rs, rowNum) -> { ResponseReply reply = new ResponseReply(); reply.setMuname(rs.getString(1)); reply.setMid(rs.getInt(2)); @@ -1038,7 +1053,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ reply.setAttachmentType(rs.getString(7)); reply.setHtml(rs.getBoolean(8)); return reply; - }, -hours); + }); } @Transactional(readOnly = true) @@ -1109,7 +1124,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return jdbcTemplate.update( "UPDATE messages_txt SET txt=?, updated_at=? WHERE messages_txt.message_id=?", body, - Timestamp.from(now), mid) > 0; + now.atOffset(ZoneOffset.UTC), mid) > 0; } } return false; @@ -1120,7 +1135,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return jdbcTemplate.update( "UPDATE replies SET txt=?, updated_at=? WHERE message_id=? AND reply_id=?", body, - Timestamp.from(now), mid, rid) > 0; + now.atOffset(ZoneOffset.UTC), mid, rid) > 0; } } return false; |