From 10e6ff1418366a9283d40ba8c703aacbe58218c0 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 5 Oct 2018 15:01:14 +0300 Subject: Add user uri to replies --- .../main/java/com/juick/service/MessagesServiceImpl.java | 16 +++++++++------- .../src/main/resources/db/migration/V1.6__user_uri.sql | 1 + 2 files changed, 10 insertions(+), 7 deletions(-) create mode 100644 juick-server/src/main/resources/db/migration/V1.6__user_uri.sql (limited to 'juick-server/src/main') diff --git a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java index e83e2705..8bf0cebc 100644 --- a/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -37,6 +37,7 @@ 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.*; @@ -71,6 +72,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ user.setUid(rs.getInt(4)); user.setName(rs.getString(5)); 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); @@ -331,7 +333,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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 FROM messages " + + "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 " @@ -339,7 +341,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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", + + "privacy, replies, attach, tags, repliesby, q, updated_at, uri", new MessageMapper(), mid); if (!list.isEmpty()) { @@ -359,7 +361,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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.updated_at, replies.user_uri as uri " + "FROM replies INNER 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 " @@ -821,7 +823,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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 " + + "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 " @@ -830,7 +832,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "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", + + "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()); @@ -898,8 +900,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ "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 " + - "FROM replies INNER JOIN users " + + "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 " + diff --git a/juick-server/src/main/resources/db/migration/V1.6__user_uri.sql b/juick-server/src/main/resources/db/migration/V1.6__user_uri.sql new file mode 100644 index 00000000..c302907c --- /dev/null +++ b/juick-server/src/main/resources/db/migration/V1.6__user_uri.sql @@ -0,0 +1 @@ +ALTER TABLE replies ADD COLUMN user_uri char(255) DEFAULT NULL; \ No newline at end of file -- cgit v1.2.3