Initial getNotifications query

* using Java 8 DateTime API where possible

4 files changed, 54 insertions, 8 deletions

diff --git a/juick-server-jdbc/build.gradle b/juick-server-jdbc/build.gradle
index 9b19bcb4..a9da3dff 100644
--- a/juick-server-jdbc/build.gradle
+++ b/juick-server-jdbc/build.gradle
@@ -10,6 +10,7 @@ dependencies {
     compile "com.fasterxml.jackson.core:jackson-databind:${rootProject.jacksonVersion}"
     compile "com.fasterxml.jackson.core:jackson-annotations:${rootProject.jacksonVersion}"
     compile "com.fasterxml.jackson.datatype:jackson-datatype-jdk8:${rootProject.jacksonVersion}"
+    compile "com.fasterxml.jackson.datatype:jackson-datatype-jsr310:${rootProject.jacksonVersion}"
 
     providedCompile "ch.qos.logback:logback-classic:${rootProject.logbackVersion}"
     providedCompile "ch.qos.logback:logback-core:${rootProject.logbackVersion}"
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 3533d701..c23b04e4 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
@@ -40,7 +40,11 @@ import org.springframework.util.Assert;
 
 import javax.inject.Inject;
 import java.sql.*;
-import java.util.*;
+import java.time.LocalDateTime;
+import java.util.ArrayList;
+import java.util.Collection;
+import java.util.Collections;
+import java.util.List;
 
 /**
  * Created by aalexeev on 11/13/16.
@@ -74,7 +78,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             user.setBanned(rs.getBoolean(6));
             msg.setUser(user);
             msg.TimeAgo = rs.getInt(7);
-            msg.setDate(rs.getTimestamp(8));
+            msg.setTimestamp(rs.getTimestamp(8).toInstant());
             msg.ReadOnly = rs.getBoolean(9);
             msg.setPrivacy(rs.getInt(10));
             msg.FriendsOnly = msg.getPrivacy() < 0;
@@ -322,7 +326,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
                     msg.getUser().setUid(rs.getInt(1));
                     msg.getUser().setName(rs.getString(2));
                     msg.setReplyto(rs.getInt(3));
-                    msg.setDate(rs.getTimestamp(4));
+                    msg.setTimestamp(rs.getTimestamp(4).toInstant());
                     msg.setAttachmentType(rs.getString(5));
                     msg.setText(rs.getString(6));
                     String quote = rs.getString(7);
@@ -834,4 +838,27 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
             return reply;
         }, -hours);
     }
+
+    @Transactional(readOnly = true)
+    @Override
+    public List<com.juick.Message> getNotifications(User user, LocalDateTime before) {
+        return getNamedParameterJdbcTemplate().query("SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " +
+                        "replies.user_id, users.nick, users.banned, " +
+                        "TIMESTAMPDIFF(MINUTE, replies.ts, NOW()), replies.ts, " +
+                        "0 as readonly, 0 as privacy, 0 as replies, " +
+                        "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 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 " +
+                        "LEFT JOIN messages_txt t on replies.message_id = t.message_id " +
+                        "WHERE EXISTS (SELECT 1 FROM subscr_messages WHERE suser_id=:uid " +
+                        "AND replies.message_id=message_id) " +
+                        (before != null ? "AND replies.ts < :before " : StringUtils.EMPTY) +
+                        "ORDER BY ts DESC LIMIT 20", new MapSqlParameterSource()
+                .addValue("uid", user.getUid())
+                .addValue("before", before),
+                new MessageMapper());
+    }
 }
diff --git a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
index 6b13f7bc..80be200b 100644
--- a/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
+++ b/juick-server-jdbc/src/main/java/com/juick/service/PMQueriesServiceImpl.java
@@ -125,7 +125,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                     msg.setUser(new User());
                     msg.getUser().setUid(uuid);
                     msg.setText(rs.getString(2));
-                    msg.setDate(rs.getTimestamp(3));
+                    msg.setTimestamp(rs.getTimestamp(3).toInstant());
                     return msg;
                 });
 
@@ -149,7 +149,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                     msg.getUser().setName(rs.getString(2));
                     msg.setText(rs.getString(3));
                     msg.TimeAgo = rs.getInt(4);
-                    msg.setDate(rs.getTimestamp(5));
+                    msg.setTimestamp(rs.getTimestamp(5).toInstant());
                     return msg;
                 },
                 uid);
@@ -169,7 +169,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe
                     msg.getUser().setName(rs.getString(2));
                     msg.setText(rs.getString(3));
                     msg.TimeAgo = rs.getInt(4);
-                    msg.setDate(rs.getTimestamp(5));
+                    msg.setTimestamp(rs.getTimestamp(5).toInstant());
                     return msg;
                 },
                 uid);
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 64de9424..738a1250 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
@@ -17,17 +17,22 @@
 
 package com.juick.service;
 
+import com.juick.Message;
+import com.juick.User;
 import com.juick.configuration.RepositoryConfiguration;
 import com.juick.server.helpers.AnonymousUser;
+import org.junit.Before;
 import org.junit.Test;
 import org.springframework.test.context.ContextConfiguration;
 import org.springframework.test.context.junit4.AbstractJUnit4SpringContextTests;
 
 import javax.inject.Inject;
+import java.time.ZoneId;
 import java.util.List;
 
 import static org.hamcrest.MatcherAssert.assertThat;
 import static org.hamcrest.Matchers.equalTo;
+import static org.hamcrest.Matchers.is;
 
 /**
  * Created by aalexeev on 11/25/16.
@@ -43,10 +48,14 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests {
 
     int ugnichId, freefdId, mid;
 
-    @Test
-    public void getMyFeed() {
+    @Before
+    public void createUgniches() {
         ugnichId = userService.createUser("ugnich", "secret");
         freefdId = userService.createUser("freefd", "secret");
+    }
+
+    @Test
+    public void getMyFeed() {
         mid = messagesService.createMessage(ugnichId, "test", null, null);
         int mid2 = messagesService.createMessage(ugnichId, "test2", null, null);
         subscriptionService.subscribeUser(userService.getUserByUID(freefdId).orElse(AnonymousUser.INSTANCE),
@@ -65,5 +74,14 @@ public class MessageServiceTest extends AbstractJUnit4SpringContextTests {
         assertThat(messagesService.recommendMessage(mid, ugnichId), equalTo(false));
         assertThat(messagesService.getMessage(mid).getLikes(), equalTo(0));
         assertThat(messagesService.getAll(ugnichId, 0).size(), equalTo(3));
+
+        User ugnich = userService.getUserByUID(ugnichId).orElse(AnonymousUser.INSTANCE);
+        User freefd = userService.getUserByUID(freefdId).orElse(AnonymousUser.INSTANCE);
+        int mid = messagesService.createMessage(ugnichId, "yo", null, null);
+        subscriptionService.subscribeMessage(mid, freefdId);
+        int rid = messagesService.createReply(mid, 0, ugnichId, "yo", null);
+        Message reply = messagesService.getReply(mid, rid);
+        assertThat(messagesService.getNotifications(freefd, null).size(), is(1));
+        assertThat(messagesService.getNotifications(freefd, reply.getTimestamp().atZone(ZoneId.systemDefault()).toLocalDateTime()).size(), is(0));
     }
 }