From 4b1ff0806def1d7e632852f209bfda9c29093524 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 28 Jan 2023 19:21:17 +0300 Subject: Update recursive CTEs --- src/main/java/com/juick/service/MessagesService.java | 2 +- .../java/com/juick/service/MessagesServiceImpl.java | 19 +++++++++++-------- src/main/java/com/juick/service/UserServiceImpl.java | 4 +++- 3 files changed, 15 insertions(+), 10 deletions(-) (limited to 'src/main') diff --git a/src/main/java/com/juick/service/MessagesService.java b/src/main/java/com/juick/service/MessagesService.java index 6881c924..a6b6e2e2 100644 --- a/src/main/java/com/juick/service/MessagesService.java +++ b/src/main/java/com/juick/service/MessagesService.java @@ -1,5 +1,5 @@ /* - * Copyright (C) 2008-2020, Juick + * Copyright (C) 2008-2023, Juick * * This program is free software: you can redistribute it and/or modify * it under the terms of the GNU Affero General Public License as diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index ef458979..74e5db77 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -70,6 +70,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ private String baseImagesUrl; @Inject private User archiveUser; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") + private boolean omitRecursiveKeyword; private class MessageMapper implements RowMapper { @Override @@ -113,8 +115,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ msg.setUpdatedAt(rs.getTimestamp(21).toInstant()); msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY))); msg.setHtml(rs.getBoolean(25)); - msg.setUnread(rs.getBoolean(26)); - msg.setSubscribed(rs.getBoolean("subscribed")); + msg.setUnread(rs.getInt(26) > 0); + msg.setSubscribed(rs.getInt("subscribed") > 0); if (StringUtils.isNotEmpty(msg.getAttachmentType())) { try { storageService.setAttachmentMetadata(baseImagesUrl, msg); @@ -778,8 +780,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getMessages(int uid, final List mids) { if (CollectionUtils.isNotEmpty(mids)) { - var query = """ - WITH RECURSIVE banned(message_id, reply_id) + 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) OR EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)) @@ -810,8 +812,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ '' as to_uri, '' as msg_reply_uri, 0 as html, - (1.*messages.replies - subscr_messages.last_read_rid) > 0 as unread, - (SELECT EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid)) subscribed + (1.*messages.replies - subscr_messages.last_read_rid) as unread, + (SELECT CASE WHEN EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid) THEN 1 ELSE 0 END) subscribed FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) INNER JOIN users ON messages.user_id=users.id LEFT JOIN subscr_messages ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid @@ -825,7 +827,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ 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"""; List msgs = getNamedParameterJdbcTemplate().query(query, - new MapSqlParameterSource("ids", mids).addValue("uid", uid), new MessageMapper()); + new MapSqlParameterSource("ids", mids) + .addValue("uid", uid), new MessageMapper()); Map> likes = updateReactionsFor(mids); msgs.forEach(i -> i.setReactions(likes.get(i.getMid()))); msgs.sort(Comparator.comparing(item -> mids.indexOf(item.getMid()))); @@ -869,7 +872,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional @Override public List getReplies(final User user, final int mid) { - List replies = getNamedParameterJdbcTemplate().query("WITH RECURSIVE banned(reply_id, user_id) AS (" + List replies = getNamedParameterJdbcTemplate().query((omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + "banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + "UNION ALL SELECT replies.reply_id, replies.user_id FROM replies " diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index ca63c322..77fb2e72 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -51,6 +51,8 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Value("${juick.admin_users:}") List adminUsers; + @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") + private boolean omitRecursiveKeyword; private static class UserMapper implements RowMapper { @Override @@ -392,7 +394,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService { @Transactional(readOnly = true) @Override public boolean isReplyToBL(final User user, final Message reply) { - var replies = getNamedParameterJdbcTemplate().queryForList("WITH RECURSIVE banned(reply_id, user_id) AS (" + + var replies = getNamedParameterJdbcTemplate().queryForList((omitRecursiveKeyword ? "WITH " : "WITH RECURSIVE ") + "banned(reply_id, user_id) AS (" + "SELECT reply_id, user_id FROM replies " + "WHERE replies.message_id = :mid " + "AND EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = replies.user_id) " + -- cgit v1.2.3