aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick
diff options
context:
space:
mode:
Diffstat (limited to 'src/main/java/com/juick')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java41
-rw-r--r--src/main/java/com/juick/service/PostgresSearchService.java8
2 files changed, 18 insertions, 31 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index c5b29fd5..ccace3a8 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -136,12 +136,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Override
public int createMessage(final int uid, final String txt, final String attachment, @NonNull final Set<Tag> tags) {
SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages")
- .usingColumns("user_id", "attach", "ts", "readonly", "updated").usingGeneratedKeyColumns("message_id");
+ .usingColumns("user_id", "attach", "ts", "readonly", "updated", "updated_at", "txt")
+ .usingGeneratedKeyColumns("message_id");
var insertMap = new MapSqlParameterSource();
insertMap.addValue("user_id", uid);
var now = Instant.now();
insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType());
insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType());
+ insertMap.addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType());
+ insertMap.addValue("txt", StringUtils.defaultString(txt));
if (StringUtils.isNotEmpty(attachment)) {
insertMap.addValue("attach", attachment);
}
@@ -165,13 +168,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
});
}
getNamedParameterJdbcTemplate()
- .update("INSERT INTO messages_txt(message_id, txt, updated_at) VALUES (:mid, :txt, :now)",
- new MapSqlParameterSource()
- .addValue("mid", mid)
- .addValue("txt", txt)
- .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)),
- dateTimeType()));
- getNamedParameterJdbcTemplate()
.update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid",
new MapSqlParameterSource()
.addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)),
@@ -284,7 +280,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC " + limit(5), String.class,
mid);
String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(","));
- getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid);
+ getJdbcTemplate().update("UPDATE messages SET repliesby=? WHERE message_id=?", result, mid);
}
@Transactional
@@ -427,13 +423,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
public Message getReply(final int mid, final int rid) {
var sql = """
SELECT replies.user_id, users.nick,
- replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(q.txt,t.txt) as quote,
+ replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(q.txt,m.txt) as quote,
COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name,
replies.updated_at, replies.user_uri as uri,
q.user_uri AS to_uri, replies.reply_uri AS reply_uri, replies.html, q.reply_uri
FROM replies LEFT JOIN users ON replies.user_id = users.id
LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.reply_id
- LEFT JOIN messages_txt t ON replies.message_id = t.message_id
LEFT JOIN messages m ON replies.message_id = m.message_id
LEFT JOIN users qu ON q.user_id=qu.id LEFT JOIN users mu ON m.user_id=mu.id
WHERE replies.message_id = ? AND replies.reply_id = ?
@@ -811,29 +806,29 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
COUNT(DISTINCT favorites.user_id) AS likes,
messages.hidden,
'' as tags,
- messages_txt.repliesby,
- messages_txt.txt, '' as q,
+ messages.repliesby,
+ messages.txt, '' as q,
messages.updated,
0 as to_uid,
NULL as to_name,
- messages_txt.updated_at,
+ messages.updated_at,
'' as m_user_uri,
'' as to_uri,
'' as msg_reply_uri,
0 as html,
(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)
+ FROM messages
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
LEFT JOIN favorites ON messages.message_id = favorites.message_id AND favorites.like_id=1
LEFT JOIN banned ON messages.message_id = banned.message_id
- LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id
+ LEFT JOIN messages_tags ON messages_tags.message_id=messages.message_id
LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id
WHERE messages.message_id IN (:ids) GROUP BY
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, messages.updated, messages.replies, updated_at,
+ messages.repliesby, messages.txt, messages.updated, messages.replies, updated_at,
subscr_messages.last_read_rid""";
List<Message> msgs = getNamedParameterJdbcTemplate().query(query,
new MapSqlParameterSource("ids", mids)
@@ -890,14 +885,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, "
+ "replies.user_id, users.nick, users.banned, " + "replies.ts, "
+ "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, "
- + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, "
+ + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, m.txt) as q, " + ":now, "
+ "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, "
+ "replies.updated_at, replies.user_uri as uri, "
+ "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, "
+ "0 as subscribed "
+ "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id "
+ "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id "
- + "LEFT JOIN messages_txt t on replies.message_id = t.message_id "
+ "LEFT JOIN messages m on replies.message_id = m.message_id "
+ "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id "
+ "WHERE replies.message_id = :mid "
@@ -943,11 +937,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
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;
- }
+ return getNamedParameterJdbcTemplate()
+ .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0;
} else {
return getNamedParameterJdbcTemplate().update(
"UPDATE messages SET user_id=:archive_uid WHERE message_id = :mid AND user_id = :uid",
@@ -1109,7 +1100,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
Instant ts = message.get().getUpdatedAt();
if (ts.compareTo(messageEditingWindow) >= 0 || foreign) {
return namedParameterJdbcTemplate.update(
- "UPDATE messages_txt SET txt=:txt, updated_at=:now WHERE messages_txt.message_id=:mid",
+ "UPDATE messages SET txt=:txt, updated_at=:now WHERE message_id=:mid",
new MapSqlParameterSource()
.addValue("txt", body)
.addValue("mid", mid)
diff --git a/src/main/java/com/juick/service/PostgresSearchService.java b/src/main/java/com/juick/service/PostgresSearchService.java
index 500f3d20..2493a45e 100644
--- a/src/main/java/com/juick/service/PostgresSearchService.java
+++ b/src/main/java/com/juick/service/PostgresSearchService.java
@@ -70,14 +70,10 @@ public class PostgresSearchService extends BaseJdbcService implements SearchServ
FROM
(
select
- messages.message_id message_id,
- messages.user_id user_id,
- txt,
+ message_id, user_id, txt,
to_tsvector('russian', txt) v,
websearch_to_tsquery('russian', :query) q
- from
- messages_txt
- inner join messages on messages.message_id = messages_txt.message_id
+ from messages
) t
WHERE
v @@ q