aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/java/com/juick/service')
-rw-r--r--src/main/java/com/juick/service/BaseJdbcService.java19
-rw-r--r--src/main/java/com/juick/service/EmailServiceImpl.java2
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java185
-rw-r--r--src/main/java/com/juick/service/TagServiceImpl.java2
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java2
5 files changed, 108 insertions, 102 deletions
diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java
index d6f29283..ab2e3fc8 100644
--- a/src/main/java/com/juick/service/BaseJdbcService.java
+++ b/src/main/java/com/juick/service/BaseJdbcService.java
@@ -17,7 +17,6 @@
package com.juick.service;
-import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
@@ -30,7 +29,6 @@ import java.sql.Types;
import java.time.Instant;
import java.time.OffsetDateTime;
import java.time.ZoneOffset;
-import java.time.format.DateTimeFormatter;
/**
* Created by aalexeev on 11/13/16.
@@ -82,9 +80,9 @@ public class BaseJdbcService {
}
public OffsetDateTime getOffsetDateTime(ResultSet rs, int columnIndex) throws SQLException {
if (haveNoDates) {
- var date = rs.getString(columnIndex);
- if (StringUtils.isNotEmpty(date)) {
- return OffsetDateTime.parse(date);
+ var date = rs.getLong(columnIndex);
+ if (date != 0) {
+ return Instant.ofEpochMilli(date).atOffset(ZoneOffset.UTC);
}
return null;
}
@@ -99,17 +97,22 @@ public class BaseJdbcService {
}
public Object fromEpochMilli(Long milliseconds) {
if (haveNoDates) {
- return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC)
- .format(DateTimeFormatter.ISO_OFFSET_DATE_TIME);
+ return milliseconds;
}
if (haveNoOffsetDateTime) {
return new Timestamp(milliseconds);
}
return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC);
}
+ public Object toDateTime(OffsetDateTime now) {
+ if (haveNoDates) {
+ return now.toInstant().toEpochMilli();
+ }
+ return now;
+ }
public int dateTimeType() {
if (haveNoDates) {
- return Types.VARCHAR;
+ return Types.INTEGER;
}
if (haveNoOffsetDateTime) {
return Types.TIMESTAMP;
diff --git a/src/main/java/com/juick/service/EmailServiceImpl.java b/src/main/java/com/juick/service/EmailServiceImpl.java
index 7f358533..85e56a05 100644
--- a/src/main/java/com/juick/service/EmailServiceImpl.java
+++ b/src/main/java/com/juick/service/EmailServiceImpl.java
@@ -119,6 +119,6 @@ public class EmailServiceImpl extends BaseJdbcService implements EmailService {
Instant day = Instant.now().minus(1, ChronoUnit.DAYS);
return getNamedParameterJdbcTemplate().update("DELETE FROM auth WHERE ts < :day",
new MapSqlParameterSource()
- .addValue("day", day.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE));
+ .addValue("day", toDateTime(day.atOffset(ZoneOffset.UTC)), dateTimeType()));
}
}
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 112fc5bf..3ddfcd00 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -85,7 +85,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
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.setCreated(MessagesServiceImpl.this.getOffsetDateTime(rs, 7).toInstant());
msg.ReadOnly = rs.getBoolean(8);
msg.setPrivacy(rs.getInt(9));
msg.FriendsOnly = msg.getPrivacy() < 0;
@@ -99,7 +99,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
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());
+ msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs, 18).toInstant());
int quoteUid = rs.getInt(19);
User quoteUser = new User();
quoteUser.setUid(quoteUid);
@@ -111,7 +111,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
quoteUser.setAvatar(webApp.getAvatarUrl(quoteUser));
}
msg.setTo(quoteUser);
- msg.setUpdatedAt(MessagesServiceImpl.this.getOffsetDateTime(rs,21).toInstant());
+ 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);
@@ -129,8 +129,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
/**
* @see <a href=
- * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
- * JDBC and MySQL Types</a>
+ * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
+ * JDBC and MySQL Types</a>
*/
@Transactional
@Override
@@ -140,8 +140,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
var insertMap = new MapSqlParameterSource();
insertMap.addValue("user_id", uid);
var now = Instant.now();
- insertMap.addValue("ts", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
- insertMap.addValue("updated", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
+ 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);
}
@@ -169,15 +169,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
new MapSqlParameterSource()
.addValue("mid", mid)
.addValue("txt", txt)
- .addValue("now", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE));
+ .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", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
- .addValue("last_seen", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+ .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)),
+ dateTimeType())
+ .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)),
+ dateTimeType())
.addValue("uid", uid));
}
return mid;
@@ -191,8 +191,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
* @param attachment
* @return
* @see <a href=
- * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
- * JDBC and MySQL Types</a>
+ * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java,
+ * JDBC and MySQL Types</a>
*/
@Transactional
@Override
@@ -211,27 +211,27 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
.addValue("replyto", rid)
.addValue("attach", attachment)
.addValue("txt", txt)
- .addValue("ts", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
- .addValue("updated_at", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+ .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", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+ .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", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
- .addValue("last_seen", now.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE)
+ .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)),
+ dateTimeType())
+ .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)),
+ dateTimeType())
.addValue("uid", user.getUid()));
}
return ridnew;
@@ -280,8 +280,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional
void updateRepliesBy(int mid) {
List<String> users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies "
- + "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? "
- + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC " + limit(5), String.class,
+ + "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);
@@ -299,14 +299,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (wasDeleted > 0) {
return RecommendStatus.Deleted;
} else {
- var now = Instant.now().atOffset(ZoneOffset.UTC);
+ 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, Types.TIMESTAMP_WITH_TIMEZONE)
+ .addValue("ts", now, dateTimeType())
.addValue("like_id", Reaction.LIKE)
.addValue("user_uri", userUri)) == 1;
if (wasAdded) {
@@ -354,7 +354,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
boolean wasAdded = getJdbcTemplate().update(
"INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)",
vuid,
- mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1;
+ mid, toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), reaction, userUri) == 1;
if (wasAdded) {
return RecommendStatus.Added;
}
@@ -379,7 +379,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return privacyOpts == null || privacyOpts.getPrivacy() >= 0 || uid == privacyOpts.getUid()
|| ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0
- && userService.isInWL(privacyOpts.getUid(), uid));
+ && userService.isInWL(privacyOpts.getUid(), uid));
}
@Transactional(readOnly = true)
@@ -404,9 +404,11 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return list.isEmpty() ? -4 : list.get(0);
}
+
public Optional<Message> getMessage(int mid) {
return getMessage(0, mid);
}
+
@Transactional(readOnly = true)
@Override
public Optional<Message> getMessage(int uid, final int mid) {
@@ -516,12 +518,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
.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 ("
+ + (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)" +
+ + " 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);
}
@@ -585,14 +587,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "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)
+ + "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);
}
@@ -705,15 +707,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("privacy", privacy)
- .addValue("date", backDate, Types.TIMESTAMP_WITH_TIMEZONE);
+ .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 ts >= :date"
+ + " AND ts < :date"
+ : StringUtils.EMPTY)
+ " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC " + limit(20),
sqlParameterSource, Integer.class);
}
@@ -721,7 +723,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
public List<Integer> getUserBlogWithRecommendations(final User user, final User visitor, final int privacy,
- final int before) {
+ final int before) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", user.getUid())
.addValue("vid", visitor.getUid())
@@ -779,7 +781,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
public List<Integer> getUserSearch(final User visitor, final int UID, final String search, final int privacy,
- final int page) {
+ final int page) {
return searchService.searchByStringAndUser(visitor, search, UID, page);
}
@@ -850,14 +852,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
}
return Collections.emptyList();
}
+
private Map<Integer, Set<Reaction>> updateReactionsFor(final List<Integer> 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""",
+ 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<Integer, Set<Reaction>> results = new HashMap<>();
while (rs.next()) {
@@ -880,32 +883,32 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Override
public List<Message> getReplies(final User user, final int mid) {
List<Message> 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",
+ + "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", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE),
+ .addValue("now", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), dateTimeType()),
new MessageMapper());
replies.forEach(i -> {
i.setEntities(MessageUtils.getEntities(i));
@@ -1003,8 +1006,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
"SELECT message_id FROM messages WHERE messages.ts > :hours",
new MapSqlParameterSource()
.addValue("hours",
- Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+ toDateTime(Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC)),
+ dateTimeType()),
Integer.class);
}
@@ -1014,15 +1017,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
public List<ResponseReply> 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
- """,
+ 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),
+ .addValue("datetime", toDateTime(datetime), dateTimeType()),
(rs, rowNum) -> {
ResponseReply reply = new ResponseReply();
reply.setMuname(rs.getString(1));
@@ -1062,8 +1065,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
""";
return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource()
.addValue("nsfw_id", nsfw.getId())
- .addValue("before", beforeTime.atOffset(ZoneOffset.UTC),
- java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+ .addValue("before", toDateTime(beforeTime.atOffset(ZoneOffset.UTC)),
+ dateTimeType()),
Integer.class);
}
@@ -1111,7 +1114,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
new MapSqlParameterSource()
.addValue("txt", body)
.addValue("mid", mid)
- .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
+ .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0;
}
}
return false;
@@ -1126,7 +1129,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
.addValue("txt", body)
.addValue("mid", mid)
.addValue("rid", rid)
- .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
+ .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0;
}
}
return false;
diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java
index c6f5bb89..ef5f6e84 100644
--- a/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/src/main/java/com/juick/service/TagServiceImpl.java
@@ -182,7 +182,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
""" + limit(20);
return getNamedParameterJdbcTemplate()
.query(sql, new MapSqlParameterSource()
- .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
+ .addValue("ts", toDateTime(ts.atOffset(ZoneOffset.UTC)), dateTimeType()),
new TagStatsMapper());
}
diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index e30be17b..8ed8fd96 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -591,7 +591,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
@Override
@Transactional
public void updateLastSeen(User user) {
- getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid());
+ getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), user.getUid());
}
@Override