Update recursive CTEs

3 files changed, 15 insertions, 10 deletions

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<Message> {
         @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<Message> getMessages(int uid, final List<Integer> 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<Message> msgs = getNamedParameterJdbcTemplate().query(query,
-                    new MapSqlParameterSource("ids", mids).addValue("uid", uid), new MessageMapper());
+                    new MapSqlParameterSource("ids", mids)
+                            .addValue("uid", uid), 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())));
@@ -869,7 +872,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
     @Transactional
     @Override
     public List<Message> getReplies(final User user, final int mid) {
-        List<Message> replies = getNamedParameterJdbcTemplate().query("WITH RECURSIVE banned(reply_id, user_id) AS ("
+        List<Message> 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<String> adminUsers;
+    @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}")
+    private boolean omitRecursiveKeyword;
 
     private static class UserMapper implements RowMapper<User> {
         @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) " +