From 6cd35dbc723a64720c08a331ede9e605df11b8b4 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 19:58:01 +0300 Subject: CI: enable sqlserver job --- .../com/juick/service/MessagesServiceImpl.java | 33 +++++++++++----------- .../java/com/juick/service/TagServiceImpl.java | 2 +- 2 files changed, 18 insertions(+), 17 deletions(-) (limited to 'src/main/java') diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 74e5db77..8149f37f 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -780,7 +780,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getMessages(int uid, final List mids) { if (CollectionUtils.isNotEmpty(mids)) { - var query = omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE " + """ + var query = (omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + """ banned(message_id, reply_id) AS (SELECT message_id, reply_id FROM replies WHERE replies.message_id IN (:ids) AND (EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) @@ -822,10 +822,10 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id WHERE messages.message_id IN (:ids) GROUP BY - messages.message_id, rid, replyto, messages.user_id, users.nick, usr_banned, messages.ts, + messages.message_id, messages.user_id, users.nick, users.banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, - messages_txt.repliesby, messages_txt.txt, q, messages.updated, to_uid, to_name, updated_at, - m_user_uri, msg_reply_uri, html, subscr_messages.last_read_rid"""; + messages_txt.repliesby, messages_txt.txt, messages.updated, messages.replies, updated_at, + subscr_messages.last_read_rid"""; List msgs = getNamedParameterJdbcTemplate().query(query, new MapSqlParameterSource("ids", mids) .addValue("uid", uid), new MessageMapper()); @@ -923,22 +923,23 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ Instant messageDeletingWindow = now.minus(3, ChronoUnit.DAYS); Optional message = getMessage(uid, mid); if (message.isPresent()) { + if (message.get().getUser().getUid() != uid) return false; Instant ts = message.get().getUpdatedAt(); SqlParameterSource parameters = new MapSqlParameterSource().addValue("mid", mid).addValue("uid", uid) .addValue("archive_uid", archiveUser.getUid()); if (ts.compareTo(messageDeletingWindow) >= 0) { - if (getNamedParameterJdbcTemplate() - .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0) { - - getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", - parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", - parameters); - getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", - parameters); - - return true; + // TODO: cascade delete + getNamedParameterJdbcTemplate().update("DELETE FROM replies WHERE message_id = :mid", parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM messages_tags WHERE message_id = :mid", + parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM subscr_messages WHERE message_id = :mid", + parameters); + getNamedParameterJdbcTemplate().update("DELETE FROM messages_properties WHERE message_id = :mid", + parameters); + if (getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", + parameters) > 0) { + return getNamedParameterJdbcTemplate() + .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0; } } else { return getNamedParameterJdbcTemplate().update( diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index bbf21bb0..4657659b 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -166,7 +166,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { @Override public List getPopularTags() { return getJdbcTemplate().queryForList(""" - select name from tags where noindex=false + select name from tags where noindex=0 order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class); } -- cgit v1.2.3