aboutsummaryrefslogtreecommitdiff
path: root/juick-server-jdbc/src
diff options
context:
space:
mode:
Diffstat (limited to 'juick-server-jdbc/src')
-rw-r--r--juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java40
-rw-r--r--juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java4
2 files changed, 28 insertions, 16 deletions
diff --git a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
index de8b256e..d7067901 100644
--- a/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/juick-server-jdbc/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -97,6 +97,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
msg.setRepliesBy(rs.getString(19));
msg.setText(rs.getString(20));
msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(21)));
+ msg.setUpdated(rs.getTimestamp(22).toInstant());
try {
imagesService.setAttachmentMetadata(imgDir, baseImagesUrl, msg);
} catch (Exception e) {
@@ -305,7 +306,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "messages.readonly, messages.privacy, messages.replies,"
+ "messages.attach, messages.place_id, messages.lat,"
+ "messages.lon, COUNT(favorites.user_id) as likes, messages.hidden,"
- + "txt.tags, txt.repliesby, txt.txt, '' as q FROM messages "
+ + "txt.tags, txt.repliesby, txt.txt, '' as q, messages.updated FROM messages "
+ "INNER JOIN users ON messages.user_id = users.id "
+ "INNER JOIN messages_txt AS txt "
+ "ON messages.message_id = txt.message_id "
@@ -511,18 +512,20 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
- public List<Integer> getDiscussions(final int uid, final int before) {
+ public List<Integer> getDiscussions(final int uid, final Long to) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
- .addValue("before", before);
+ .addValue("to", new Timestamp(to));
- return getNamedParameterJdbcTemplate().queryForList(
- "SELECT message_id FROM subscr_messages WHERE suser_id = :uid" +
- (before > 0 ?
- " AND message_id < :before " : " ") +
- "ORDER BY message_id DESC LIMIT 20",
+ 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) +
+ "ORDER BY updated DESC, message_id DESC LIMIT 20",
sqlParameterSource,
- Integer.class);
+ (rs, rowNum) -> rs.getInt(1));
}
@Transactional(readOnly = true)
@@ -722,9 +725,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
- public List<com.juick.Message> getMessages(final Collection<Integer> mids) {
+ public List<com.juick.Message> getMessages(final List<Integer> mids) {
if (CollectionUtils.isNotEmpty(mids)) {
- return getNamedParameterJdbcTemplate().query(
+ List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query(
"SELECT messages.message_id, 0 as rid, 0 as replyto, "
+ "messages.user_id,users.nick, 0 as banned, "
+ "TIMESTAMPDIFF(MINUTE,messages.ts,NOW()),"
@@ -732,15 +735,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "messages.readonly,messages.privacy,messages.replies,"
+ "messages.attach,messages.place_id,messages.lat,"
+ "messages.lon,COUNT(favorites.user_id) AS likes,messages.hidden,"
- + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q "
+ + "messages_txt.tags,messages_txt.repliesby, messages_txt.txt, '' as q, "
+ + "messages.updated "
+ "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 "
- + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id ORDER BY messages.message_id DESC",
+ + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id",
new MapSqlParameterSource("ids", mids),
new MessageMapper());
+ msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid())));
+ return msgs;
}
return Collections.emptyList();
}
@@ -756,7 +762,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
"replies.attach, 0 as place_id, 0 as lat, " +
"0 as lon, 0 as likes, 0 as hidden, " +
"NULL as tags, NULL as repliesby, replies.txt, " +
- "IFNULL(qw.txt, t.txt) as q " +
+ "IFNULL(qw.txt, t.txt) as q, " +
+ "NOW() " +
"FROM replies INNER 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 " +
@@ -880,10 +887,11 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
public List<com.juick.Message> getNotifications(User user, LocalDateTime before) {
- return getNamedParameterJdbcTemplate().query("SELECT n.message_id as mid, n.reply_id, n.replyto, " +
+ return getNamedParameterJdbcTemplate().query("SELECT n.message_id as mid, n.reply_id, " +
+ "n.replyto, " +
"n.user_id, users.nick, users.banned, 0 as ago, n.ts, 0 as readonly, 0 as privacy, " +
"0 as replies, n.attach, 0 as place_id, 0 as lat, 0 as lon, 0 as likes, 0 as hidden, " +
- "NULL as tags, NULL as repliesby, n.txt, IFNULL(qw.txt, t.txt) as q " +
+ "NULL as tags, NULL as repliesby, n.txt, IFNULL(qw.txt, t.txt) as q, NOW() " +
"FROM (SELECT * FROM replies WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " +
"AND replies.user_id!=:uid AND replies.message_id=message_id " +
(before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) +
diff --git a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
index bdb9cdcb..4ebfe056 100644
--- a/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
+++ b/juick-server-jdbc/src/test/java/com/juick/service/MessageServiceTest.java
@@ -35,6 +35,8 @@ import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import javax.inject.Inject;
import java.sql.Timestamp;
import java.time.Instant;
+import java.time.LocalDateTime;
+import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
@@ -164,6 +166,8 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests {
assertEquals(-1, userService.checkPassword(ugnich.getName(), "xy"));
subscriptionService.subscribeMessage(msg.getMid(), ugnich.getUid());
assertEquals(1, subscriptionService.getUsersSubscribedToComments(msg.getMid(), user.getUid()).size());
+ assertThat(messagesService.getDiscussions(ugnich.getUid(), Instant.now().toEpochMilli()).get(0),
+ equalTo(msg.getMid()));
messagesService.deleteMessage(user_id, mid);
messagesService.deleteMessage(user_id, mid2);
String htmlTagName = ">_<";