From afdba016ef43de75ee9aef715c9a460ab08e90d8 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 19 Oct 2018 09:58:07 +0300 Subject: drop pm_streams --- .../com/juick/service/PMQueriesServiceImpl.java | 45 ++++------------------ .../db/migration/V1.14__drop broken pm_streams.sql | 1 + .../java/com/juick/server/tests/ServerTests.java | 2 - 3 files changed, 8 insertions(+), 40 deletions(-) create mode 100644 juick-server/src/main/resources/db/migration/V1.14__drop broken pm_streams.sql (limited to 'juick-server') diff --git a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java index ee49755d..ab153006 100644 --- a/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java +++ b/juick-server/src/main/java/com/juick/service/PMQueriesServiceImpl.java @@ -19,7 +19,6 @@ package com.juick.service; import com.juick.Chat; import com.juick.User; -import org.springframework.dao.DuplicateKeyException; import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; import org.springframework.jdbc.core.namedparam.SqlParameterSource; import org.springframework.stereotype.Repository; @@ -36,20 +35,9 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe @Transactional @Override public boolean createPM(final int uidFrom, final int uid_to, final String body) { - boolean ret = getJdbcTemplate().update( + return getJdbcTemplate().update( "INSERT INTO pm(user_id, user_id_to, txt) VALUES (?, ?, ?)", uidFrom, uid_to, body) > 0; - - if (ret) { - try { - getJdbcTemplate().update( - "INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1)", - uidFrom, uid_to); - } catch (DuplicateKeyException e) { - // ignore - } - } - return ret; } @Transactional @@ -76,36 +64,23 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe return res.size() > 0; } - @Transactional(readOnly = true) - @Override - public String getLastView(final int uidFrom, final int uidTo) { - List list = getJdbcTemplate().queryForList( - "SELECT lastview FROM pm_streams WHERE user_id = ? AND user_id_to = ?", - String.class, - uidFrom, uidTo); - - return list.isEmpty() ? - null : list.get(0); - } - @Transactional(readOnly = true) @Override public List getLastChats(final User user) { return getJdbcTemplate().query( - "SELECT pm_streams.user_id, users.nick, l.last, pm.txt, pm_streams.unread FROM pm_streams " - + "INNER JOIN users ON users.id = pm_streams.user_id " - + "INNER JOIN pm ON pm.user_id = pm_streams.user_id " + "SELECT l.user_id, users.nick, l.last, pm.txt FROM pm " + + "INNER JOIN users ON users.id = pm.user_id " + + "" + "INNER JOIN (SELECT user_id, MAX(ts) AS last FROM pm " + "WHERE user_id_to=? GROUP BY user_id) l ON l.last = pm.ts " - + "WHERE pm_streams.user_id_to=? " - + "ORDER BY pm_streams.unread DESC, l.last DESC", + + "WHERE pm.user_id_to=? " + + "ORDER BY l.last DESC", (rs, rowNum) -> { com.juick.Chat u = new com.juick.Chat(); u.setUid(rs.getInt(1)); u.setName(rs.getString(2)); u.setLastMessageTimestamp(rs.getTimestamp(3).toInstant()); u.setLastMessageText(rs.getString(4)); - u.setUnreadCount(rs.getInt(5)); return u; }, user.getUid(), user.getUid()); @@ -118,7 +93,7 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe .addValue("uid", uid) .addValue("uidTo", uidTo); - List msgs = getNamedParameterJdbcTemplate().query( + return getNamedParameterJdbcTemplate().query( "SELECT pm.user_id, pm.txt, pm.ts, users.nick FROM pm INNER JOIN users ON users.id=pm.user_id WHERE (user_id = :uid AND user_id_to = :uidTo) " + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC LIMIT 20", sqlParameterSource, @@ -133,12 +108,6 @@ public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesSe msg.setTimestamp(rs.getTimestamp(3).toInstant()); return msg; }); - - getNamedParameterJdbcTemplate().update( - "UPDATE pm_streams SET lastview = NOW(), unread = 0 WHERE user_id_to = :uid AND user_id = :uidTo", - sqlParameterSource); - - return msgs; } @Transactional(readOnly = true) diff --git a/juick-server/src/main/resources/db/migration/V1.14__drop broken pm_streams.sql b/juick-server/src/main/resources/db/migration/V1.14__drop broken pm_streams.sql new file mode 100644 index 00000000..448c5ce2 --- /dev/null +++ b/juick-server/src/main/resources/db/migration/V1.14__drop broken pm_streams.sql @@ -0,0 +1 @@ +DROP TABLE IF EXISTS pm_streams; \ No newline at end of file diff --git a/juick-server/src/test/java/com/juick/server/tests/ServerTests.java b/juick-server/src/test/java/com/juick/server/tests/ServerTests.java index 9057aced..a6750644 100644 --- a/juick-server/src/test/java/com/juick/server/tests/ServerTests.java +++ b/juick-server/src/test/java/com/juick/server/tests/ServerTests.java @@ -1734,7 +1734,6 @@ public class ServerTests { @Test public void pms() throws Exception { jdbcTemplate.execute("DELETE FROM pm"); - jdbcTemplate.execute("DELETE FROM pm_streams"); jdbcTemplate.execute("DELETE FROM bl_users"); CommandResult res = commandsManager.processCommand(ugnich, "@freefd DICK", emptyUri); assertThat(res.getNewMessage(), is(Optional.empty())); @@ -1745,6 +1744,5 @@ public class ServerTests { .andReturn(); PrivateChats chats = jsonMapper.readValue(result.getResponse().getContentAsString(), PrivateChats.class); assertThat(chats.getUsers().size(), is(1)); - assertThat(chats.getUsers().get(0).getUnreadCount(), is(1)); } } -- cgit v1.2.3