aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-28 19:21:17 +0300
committerGravatar Vitaly Takmazov2023-01-28 19:54:12 +0300
commit4b1ff0806def1d7e632852f209bfda9c29093524 (patch)
tree67b1ca5b20cc1ac3b750d8d5fc9987281613a3a0 /src/main/java/com/juick/service
parent556d10c400add4b69d8832a40fb704fda4333080 (diff)
Update recursive CTEs
Diffstat (limited to 'src/main/java/com/juick/service')
-rw-r--r--src/main/java/com/juick/service/MessagesService.java2
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java19
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java4
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) " +