diff options
Diffstat (limited to 'juick-server-jdbc')
3 files changed, 92 insertions, 11 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 c0dccc6a..362a918c 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 @@ -17,9 +17,7 @@ package com.juick.service; -import com.juick.Message; -import com.juick.Tag; -import com.juick.User; +import com.juick.*; import com.juick.server.helpers.PrivacyOpts; import com.juick.server.helpers.ResponseReply; import com.juick.util.MessageUtils; @@ -105,6 +103,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> */ @@ -226,12 +226,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public RecommendStatus recommendMessage(final int mid, final int vuid) { int wasDeleted = getJdbcTemplate() - .update("DELETE FROM favorites WHERE user_id=? AND message_id=?", vuid, mid); + .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) VALUES (?, ?, NOW())", vuid, mid) == 1; + .update("INSERT INTO favorites(user_id, message_id, ts, like_id ) VALUES (?, ?, NOW(), ?)", vuid, mid,Reaction.LIKE) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -239,6 +239,19 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return RecommendStatus.Error; } + @Override + public RecommendStatus likeMessage(int mid, int vuid, int reactionId) throws IllegalArgumentException { + if(reactionId == Reaction.LIKE) throw new IllegalArgumentException("this reaction type does not support here"); + + 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) { @@ -299,6 +312,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public com.juick.Message getMessage(final int mid) { + List<com.juick.Message> 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, " @@ -319,8 +333,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "privacy, replies, attach, place_id, lat, lon, tags, repliesby, q", new MessageMapper(), mid); - - return list.isEmpty() ? null : list.get(0); + if (!list.isEmpty()) { + final Message message = list.get(0); + Map<Integer, Set<Reaction>> reactionStats = updateReactionsFor(Collections.singletonList(mid)); + message.setReactions(reactionStats.get(message.getMid())); + return message; + } + return null; } @Transactional(readOnly = true) @@ -740,6 +759,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List<com.juick.Message> getMessages(final List<Integer> mids) { if (CollectionUtils.isNotEmpty(mids)) { + 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, " @@ -758,12 +778,50 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "WHERE messages.message_id IN (:ids) GROUP BY messages.message_id", new MapSqlParameterSource("ids", mids), new MessageMapper()); + + + Map<Integer,Set<Reaction>> 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<Integer,Set<Reaction>> updateReactionsFor(final List<Integer> mids) { + + return getNamedParameterJdbcTemplate().query("select f.message_id as mid, f.like_id as lid, " + + "r.description as desc, count(f.like_id) as count" + + " 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<Integer,Set<Reaction>> results = new HashMap<>(); + + + while (rs.next()) { + int messageId = rs.getInt("message_id"); + int likeId = rs.getInt("like_id"); + int count = rs.getInt("count"); + String description = rs.getString("description"); + 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<Message> getReplies(final User user, final int mid) { diff --git a/juick-server-jdbc/src/main/resources/juick.sql b/juick-server-jdbc/src/main/resources/juick.sql index d13a06a2..4f15ba70 100644 --- a/juick-server-jdbc/src/main/resources/juick.sql +++ b/juick-server-jdbc/src/main/resources/juick.sql @@ -177,9 +177,11 @@ CREATE TABLE `favorites` ( `user_id` int(10) unsigned NOT NULL, `message_id` int(10) unsigned NOT NULL, `ts` datetime NOT NULL, + `like_id` int(10) unsigned NOT NULL DEFAULT '1', UNIQUE KEY `user_id_2` (`user_id`,`message_id`), KEY `user_id` (`user_id`), - KEY `message_id` (`message_id`) + KEY `message_id` (`message_id`), + KEY `like_id` (`like_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; @@ -504,6 +506,19 @@ CREATE TABLE `presence` ( /*!40101 SET character_set_client = @saved_cs_client */; -- +-- Table structure for table `reactions` +-- + +DROP TABLE IF EXISTS `reactions`; +/*!40101 SET @saved_cs_client = @@character_set_client */; +/*!40101 SET character_set_client = utf8 */; +CREATE TABLE `reactions` ( + `like_id` int(10) unsigned NOT NULL, + `description` varchar(100) NOT NULL +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +/*!40101 SET character_set_client = @saved_cs_client */; + +-- -- Table structure for table `reader_links` -- @@ -914,4 +929,4 @@ CREATE TABLE `wl_users` ( /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2018-04-15 19:47:16 +-- Dump completed on 2018-04-20 7:47:13 diff --git a/juick-server-jdbc/src/main/resources/schema.sql b/juick-server-jdbc/src/main/resources/schema.sql index ec28a456..59c979d5 100644 --- a/juick-server-jdbc/src/main/resources/schema.sql +++ b/juick-server-jdbc/src/main/resources/schema.sql @@ -39,13 +39,21 @@ CREATE TABLE IF NOT EXISTS `facebook` ( `crosspost` tinyint(1) unsigned NOT NULL DEFAULT '1', KEY `user_id` (`user_id`) ); + +CREATE TABLE IF NOT EXISTS `reactions` ( + `like_id` int(10) unsigned NOT NULL, + `description` varchar (100) NOT NULL +); CREATE TABLE IF NOT EXISTS `favorites` ( `user_id` int(10) unsigned NOT NULL, `message_id` int(10) unsigned NOT NULL, `ts` datetime NOT NULL, - UNIQUE KEY `user_id_2` (`user_id`,`message_id`), - KEY `message_id` (`message_id`) + `like_id` int(10), + foreign key (like_id) references reactions(like_id) ); + + + CREATE TABLE IF NOT EXISTS `friends_facebook` ( `user_id` int(10) unsigned NOT NULL, `friend_id` bigint(20) unsigned NOT NULL, |