aboutsummaryrefslogtreecommitdiff
path: root/src/main/java/com/juick/service
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-29 05:44:21 +0300
committerGravatar Vitaly Takmazov2023-01-30 23:49:25 +0300
commitf8a7d417cb916b81cfa685175f3e6afbe6063cee (patch)
tree3f6923f3f32540e8506ce5b43b610460b4c67559 /src/main/java/com/juick/service
parentdd23559a978da8980675ad4089948ade9bbc323d (diff)
SQLite support
Diffstat (limited to 'src/main/java/com/juick/service')
-rw-r--r--src/main/java/com/juick/service/BaseJdbcService.java80
-rw-r--r--src/main/java/com/juick/service/ChatServiceImpl.java11
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java119
-rw-r--r--src/main/java/com/juick/service/TagServiceImpl.java17
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java19
5 files changed, 164 insertions, 82 deletions
diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java
index 51c41251..d6f29283 100644
--- a/src/main/java/com/juick/service/BaseJdbcService.java
+++ b/src/main/java/com/juick/service/BaseJdbcService.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
@@ -17,10 +17,20 @@
package com.juick.service;
+import org.apache.commons.lang3.StringUtils;
+import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import javax.inject.Inject;
+import java.sql.ResultSet;
+import java.sql.SQLException;
+import java.sql.Timestamp;
+import java.sql.Types;
+import java.time.Instant;
+import java.time.OffsetDateTime;
+import java.time.ZoneOffset;
+import java.time.format.DateTimeFormatter;
/**
* Created by aalexeev on 11/13/16.
@@ -30,6 +40,19 @@ public class BaseJdbcService {
JdbcTemplate jdbcTemplate;
@Inject
NamedParameterJdbcTemplate namedParameterJdbcTemplate;
+ @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}")
+ protected boolean omitRecursiveKeyword;
+ @Value("#{('${spring.sql.init.platform}' == 'sqlite') or ('${spring.sql.init.platform}' == 'mysql')}")
+ // Added in MariaDB 10.6
+ protected boolean haveNoANSIFetch;
+ @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}")
+ protected boolean haveNoDates;
+ @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}")
+ protected boolean haveNoGreatest;
+ @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlite')}")
+ protected boolean haveNoForUpdate;
+ @Value("#{new Boolean('${spring.sql.init.platform}' == 'mysql')}")
+ protected boolean haveNoOffsetDateTime;
public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return namedParameterJdbcTemplate;
@@ -38,4 +61,59 @@ public class BaseJdbcService {
public JdbcTemplate getJdbcTemplate() {
return jdbcTemplate;
}
+ protected String limit(int rows) {
+ if (haveNoANSIFetch) {
+ return "LIMIT " + rows;
+ } else {
+ return "OFFSET 0 ROWS FETCH NEXT " + rows + " ROWS ONLY";
+ }
+ }
+ protected String greatest() {
+ if (haveNoGreatest) {
+ return "MAX";
+ }
+ return "GREATEST";
+ }
+ protected String forUpdate() {
+ if (haveNoForUpdate) {
+ return "";
+ }
+ return "FOR UPDATE";
+ }
+ public OffsetDateTime getOffsetDateTime(ResultSet rs, int columnIndex) throws SQLException {
+ if (haveNoDates) {
+ var date = rs.getString(columnIndex);
+ if (StringUtils.isNotEmpty(date)) {
+ return OffsetDateTime.parse(date);
+ }
+ return null;
+ }
+ if (haveNoOffsetDateTime) {
+ var date = rs.getTimestamp(columnIndex);
+ if (date != null) {
+ return date.toInstant().atOffset(ZoneOffset.UTC);
+ }
+ return null;
+ }
+ return rs.getObject(columnIndex, OffsetDateTime.class);
+ }
+ public Object fromEpochMilli(Long milliseconds) {
+ if (haveNoDates) {
+ return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC)
+ .format(DateTimeFormatter.ISO_OFFSET_DATE_TIME);
+ }
+ if (haveNoOffsetDateTime) {
+ return new Timestamp(milliseconds);
+ }
+ return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC);
+ }
+ public int dateTimeType() {
+ if (haveNoDates) {
+ return Types.VARCHAR;
+ }
+ if (haveNoOffsetDateTime) {
+ return Types.TIMESTAMP;
+ }
+ return Types.TIMESTAMP_WITH_TIMEZONE;
+ }
}
diff --git a/src/main/java/com/juick/service/ChatServiceImpl.java b/src/main/java/com/juick/service/ChatServiceImpl.java
index d1c4ce96..c0d2f17b 100644
--- a/src/main/java/com/juick/service/ChatServiceImpl.java
+++ b/src/main/java/com/juick/service/ChatServiceImpl.java
@@ -56,7 +56,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService {
Chat u = new Chat();
u.setUid(rs.getInt(1));
u.setName(rs.getString(2));
- u.setLastMessageTimestamp(rs.getTimestamp(3).toInstant());
+ u.setLastMessageTimestamp(getOffsetDateTime(rs,3).toInstant());
u.setLastMessageText(rs.getString(4).trim());
return u;
},
@@ -72,7 +72,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService {
return getNamedParameterJdbcTemplate().query(
"SELECT pm.user_id, pm.txt, pm.ts, users.nick FROM pm INNER JOIN users ON users.id=pm.user_id WHERE (user_id = :uid AND user_id_to = :uidTo) "
- + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC " + limit(20),
sqlParameterSource,
(rs, rowNum) -> {
Message msg = new Message();
@@ -82,7 +82,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService {
user.setName(rs.getString(4));
msg.setUser(user);
msg.setText(rs.getString(2).trim());
- msg.setCreated(rs.getTimestamp(3).toInstant());
+ msg.setCreated(getOffsetDateTime(rs,3).toInstant());
return msg;
});
}
@@ -92,7 +92,8 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService {
public List<Message> getInbox(final int uid) {
return getJdbcTemplate().query(
"SELECT pm.user_id, users.nick, pm.txt, pm.ts " +
- "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ "FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC " +
+ limit(20),
(rs, num) -> {
Message msg = new Message();
msg.setUser(new User());
@@ -111,7 +112,7 @@ public class ChatServiceImpl extends BaseJdbcService implements ChatService {
return getJdbcTemplate().query(
"SELECT pm.user_id_to, users.nick, pm.txt, " +
"pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " +
- "WHERE pm.user_id=? ORDER BY pm.ts DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ "WHERE pm.user_id=? ORDER BY pm.ts DESC " + limit(20),
(rs, num) -> {
Message msg = new Message();
msg.setUser(new User());
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 8149f37f..112fc5bf 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -45,6 +45,7 @@ import javax.inject.Inject;
import java.net.URI;
import java.sql.*;
import java.time.Instant;
+import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.time.temporal.ChronoUnit;
import java.util.*;
@@ -70,8 +71,6 @@ 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
@@ -86,7 +85,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
user.setBanned(rs.getBoolean(6));
user.setUri(URI.create(Optional.ofNullable(rs.getString(22)).orElse(StringUtils.EMPTY)));
msg.setUser(user);
- msg.setCreated(rs.getTimestamp(7).toInstant());
+ msg.setCreated(MessagesServiceImpl.this.getOffsetDateTime(rs,7).toInstant());
msg.ReadOnly = rs.getBoolean(8);
msg.setPrivacy(rs.getInt(9));
msg.FriendsOnly = msg.getPrivacy() < 0;
@@ -100,7 +99,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
msg.setRepliesBy(rs.getString(15));
msg.setText(rs.getString(16));
msg.setReplyQuote(MessageUtils.formatQuote(rs.getString(17)));
- msg.setUpdated(rs.getTimestamp(18).toInstant());
+ msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs,18).toInstant());
int quoteUid = rs.getInt(19);
User quoteUser = new User();
quoteUser.setUid(quoteUid);
@@ -112,7 +111,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
quoteUser.setAvatar(webApp.getAvatarUrl(quoteUser));
}
msg.setTo(quoteUser);
- msg.setUpdatedAt(rs.getTimestamp(21).toInstant());
+ msg.setUpdatedAt(MessagesServiceImpl.this.getOffsetDateTime(rs,21).toInstant());
msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(24)).orElse(StringUtils.EMPTY)));
msg.setHtml(rs.getBoolean(25));
msg.setUnread(rs.getInt(26) > 0);
@@ -137,11 +136,12 @@ 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").usingGeneratedKeyColumns("message_id");
+ .usingColumns("user_id", "attach", "ts", "readonly", "updated").usingGeneratedKeyColumns("message_id");
var insertMap = new MapSqlParameterSource();
insertMap.addValue("user_id", uid);
var now = Instant.now();
insertMap.addValue("ts", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
+ insertMap.addValue("updated", now.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE);
if (StringUtils.isNotEmpty(attachment)) {
insertMap.addValue("attach", attachment);
}
@@ -244,7 +244,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
final boolean readOnly;
final int userId;
try (PreparedStatement ps = conn.prepareStatement(
- "SELECT maxreplyid+1, readonly, user_id FROM messages WHERE message_id=? FOR UPDATE")) {
+ "SELECT maxreplyid+1, readonly, user_id FROM messages WHERE message_id=? " + forUpdate())) {
ps.setInt(1, mid);
try (ResultSet resultSet = ps.executeQuery()) {
if (resultSet.next()) {
@@ -281,7 +281,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
void updateRepliesBy(int mid) {
List<String> users = getJdbcTemplate().queryForList("SELECT users.nick FROM replies "
+ "INNER JOIN users ON replies.user_id=users.id WHERE replies.message_id=? "
- + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY", String.class,
+ + "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);
@@ -450,7 +450,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
.setUri(URI.create(Optional.ofNullable(rs.getString(11)).orElse(StringUtils.EMPTY)));
}
msg.setReplyto(rs.getInt(3));
- msg.setCreated(rs.getTimestamp(4).toInstant());
+ msg.setCreated(getOffsetDateTime(rs, 4).toInstant());
msg.setAttachmentType(rs.getString(5));
msg.setText(rs.getString(6));
String quote = rs.getString(7);
@@ -464,7 +464,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
quoteUser.setName(Optional.ofNullable(rs.getString(9)).orElse(AnonymousUser.INSTANCE.getName()));
quoteUser.setUri(URI.create(Optional.ofNullable(rs.getString(12)).orElse(StringUtils.EMPTY)));
msg.setTo(quoteUser);
- msg.setUpdatedAt(rs.getTimestamp(10).toInstant());
+ msg.setUpdatedAt(getOffsetDateTime(rs, 10).toInstant());
msg.setReplyUri(URI.create(Optional.ofNullable(rs.getString(13)).orElse(StringUtils.EMPTY)));
msg.setHtml(rs.getBoolean(14));
msg.setReplyToUri(URI.create(Optional.ofNullable(rs.getString(15)).orElse(StringUtils.EMPTY)));
@@ -522,15 +522,17 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
+ "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :visitorUid = bt.user_id)"
+ " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :visitorUid)" +
- " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ " ORDER BY m.message_id DESC " + limit(20),
sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getTag(final int tid, final int visitorUid, final int before, final int cnt) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("tid", tid).addValue("cnt", cnt)
- .addValue("before", before).addValue("visitorUid", visitorUid);
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("tid", tid)
+ .addValue("before", before)
+ .addValue("visitorUid", visitorUid);
return getNamedParameterJdbcTemplate()
.queryForList("SELECT messages.message_id FROM (tags INNER JOIN messages_tags "
@@ -539,20 +541,21 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ (before > 0 ? " messages.message_id < :before AND " : StringUtils.EMPTY)
+ "(messages.privacy > 0 OR messages.user_id = :visitorUid) "
+ "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid and b.bl_user_id = messages.user_id) "
- + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class);
+ + "ORDER BY messages.message_id DESC " + limit(cnt), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getTags(final String tids, final int visitorUid, final int before, final int cnt) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("cnt", cnt)
- .addValue("before", before).addValue("visitorUid", visitorUid);
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("before", before)
+ .addValue("visitorUid", visitorUid);
return getNamedParameterJdbcTemplate().queryForList("SELECT messages.message_id FROM messages_tags "
+ "INNER JOIN messages USING(message_id) WHERE messages_tags.tag_id IN (" + tids + ") "
+ (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
+ " AND (messages.privacy > 0 OR messages.user_id = :visitorUid) "
- + "ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT :cnt ROWS ONLY", sqlParameterSource, Integer.class);
+ + "ORDER BY messages.message_id DESC " + limit(cnt), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@@ -564,7 +567,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages WHERE place_id = :placeId "
+ (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
- + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + " AND (privacy > 0 OR user_id = :visitorUid) ORDER BY message_id DESC " + limit(20),
sqlParameterSource, Integer.class);
}
@@ -591,7 +594,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)"
: StringUtils.EMPTY)
+ ") " + (before > 0 ? "AND message_id < :before " : StringUtils.EMPTY)
- + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + "ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@@ -603,21 +606,21 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return getNamedParameterJdbcTemplate()
.queryForList("SELECT message_id FROM messages WHERE user_id = :uid AND privacy < 0"
+ (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
- + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + "ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getDiscussions(final int uid, final Long to) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid).addValue("to",
- new Timestamp(to));
-
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("uid", uid)
+ .addValue("to", fromEpochMilli(to), dateTimeType());
if (uid == 0) {
return getNamedParameterJdbcTemplate().query(
"SELECT message_id FROM messages WHERE " + (to != 0 ? " updated < :to AND" : StringUtils.EMPTY)
+ " NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1"
+ " AND u.id = messages.user_id and u.id <> :uid) "
- + " ORDER BY updated DESC, message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + " ORDER BY updated DESC, message_id DESC " + limit(20),
sqlParameterSource, (rs, rowNum) -> rs.getInt(1));
}
return getNamedParameterJdbcTemplate().query(
@@ -626,7 +629,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "WHERE suser_id = :uid " + (to != 0 ? "AND updated < :to " : StringUtils.EMPTY)
+ " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1"
+ " AND u.id = messages.user_id and u.id <> :uid) "
- + "ORDER BY updated DESC, messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + "ORDER BY updated DESC, messages.message_id DESC " + limit(20),
sqlParameterSource, (rs, rowNum) -> rs.getInt(1));
}
@@ -639,7 +642,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return getNamedParameterJdbcTemplate().queryForList("SELECT f.message_id FROM favorites f WHERE "
+ "EXISTS (SELECT 1 FROM subscr_users s WHERE s.suser_id = :uid and f.user_id = s.user_id)"
+ (before > 0 ? " AND f.message_id < :before " : StringUtils.EMPTY)
- + "ORDER BY f.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + "ORDER BY f.message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@@ -655,7 +658,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ "(SELECT tag_id FROM messages_tags WHERE message_id = m.message_id) and :vid = bt.user_id)"
+ " AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = m.user_id and u.id <> :vid) "
+ " AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :vid and b.bl_user_id = m.user_id) "
- + " ORDER BY m.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + " ORDER BY m.message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@@ -673,16 +676,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages INNER JOIN users" + " ON messages.user_id = users.id"
+ " WHERE user_id = :uid" + (before > 0 ? " AND message_id < :before" : StringUtils.EMPTY)
- + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC " + limit(20),
sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getUserTag(final int uid, final int tid, final int privacy, final int before) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid).addValue("tid", tid)
- .addValue("privacy", privacy).addValue("before", before);
-
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("uid", uid)
+ .addValue("tid", tid)
+ .addValue("privacy", privacy)
+ .addValue("before", before);
return getNamedParameterJdbcTemplate()
.queryForList("SELECT messages.message_id FROM messages_tags INNER JOIN messages"
+ " ON messages.message_id = messages_tags.message_id" + " INNER JOIN users"
@@ -690,7 +695,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ " WHERE messages.user_id = :uid AND messages_tags.tag_id = :tid"
+ (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
+ " AND messages.privacy >= :privacy AND users.banned = 0"
- + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + " ORDER BY messages.message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@@ -709,7 +714,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
? " AND ts >= :date"
+ " AND ts < :date"
: StringUtils.EMPTY)
- + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC " + limit(20),
sqlParameterSource, Integer.class);
}
@@ -731,7 +736,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
+ "(SELECT tag_id FROM messages_tags WHERE message_id = favorites.message_id) and :vid = bt.user_id)"
+ (before > 0 ? " AND messages.message_id < :before " : StringUtils.EMPTY)
- + " ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as r" + " UNION ALL "
+ + " ORDER BY messages.message_id DESC " + limit(20) + ") as r" + " UNION ALL "
+ "SELECT message_id FROM "
+ "(SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id"
+ " WHERE user_id = :uid AND users.banned = 0"
@@ -739,33 +744,35 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
+ " AND NOT EXISTS (SELECT 1 FROM bl_tags bt WHERE bt.tag_id IN "
+ "(SELECT tag_id FROM messages_tags WHERE message_id = messages.message_id) and :vid = bt.user_id)"
+ (before > 0 ? " AND messages.message_id < :before" : StringUtils.EMPTY)
- + " AND privacy >= :privacy ORDER BY messages.message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY) as m "
- + "ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + " AND privacy >= :privacy ORDER BY messages.message_id DESC " + limit(20) + ") as m "
+ + "ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getUserRecommendations(final int uid, final int before) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid).addValue("before",
- before);
-
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("uid", uid)
+ .addValue("before", before);
return getNamedParameterJdbcTemplate().queryForList("SELECT message_id FROM favorites" + " INNER JOIN users"
+ " ON favorites.user_id = users.id" + " WHERE user_id = :uid AND users.banned = 0 "
+ (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
- + " ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY", sqlParameterSource, Integer.class);
+ + " ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class);
}
@Transactional(readOnly = true)
@Override
public List<Integer> getUserPhotos(final int uid, final int privacy, final int before) {
- SqlParameterSource sqlParameterSource = new MapSqlParameterSource().addValue("uid", uid)
- .addValue("privacy", privacy).addValue("before", before);
-
+ SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
+ .addValue("uid", uid)
+ .addValue("privacy", privacy)
+ .addValue("before", before);
return getNamedParameterJdbcTemplate().queryForList(
"SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id"
+ " WHERE user_id = :uid and users.banned = 0"
+ (before > 0 ? " AND message_id < :before " : StringUtils.EMPTY)
- + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY",
+ + " AND privacy >= :privacy AND attach IS NOT NULL ORDER BY message_id DESC "
+ + limit(20),
sqlParameterSource, Integer.class);
}
@@ -900,9 +907,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
.addValue("uid", user.getUid())
.addValue("now", Instant.now().atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE),
new MessageMapper());
- if (replies.size() > 0 && !user.isAnonymous()) {
- setRead(user, mid);
- }
replies.forEach(i -> {
i.setEntities(MessageUtils.getEntities(i));
i.getUser().setAvatar(webApp.getAvatarUrl(i.getUser()));
@@ -1067,7 +1071,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Override
public void setLastReadComment(User user, Integer mid, Integer rid) {
jdbcTemplate.update(
- "UPDATE subscr_messages SET last_read_rid=GREATEST(?, last_read_rid) WHERE message_id=? AND suser_id=?",
+ "UPDATE subscr_messages SET last_read_rid=" + greatest() + "(?, last_read_rid) WHERE message_id=? AND suser_id=?",
rid, mid, user.getUid());
}
@@ -1102,9 +1106,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (message.isPresent()) {
Instant ts = message.get().getUpdatedAt();
if (ts.compareTo(messageEditingWindow) >= 0 || foreign) {
- return jdbcTemplate.update(
- "UPDATE messages_txt SET txt=?, updated_at=? WHERE messages_txt.message_id=?", body,
- now.atOffset(ZoneOffset.UTC), mid) > 0;
+ return namedParameterJdbcTemplate.update(
+ "UPDATE messages_txt SET txt=:txt, updated_at=:now WHERE messages_txt.message_id=:mid",
+ new MapSqlParameterSource()
+ .addValue("txt", body)
+ .addValue("mid", mid)
+ .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
}
}
return false;
@@ -1113,9 +1120,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (reply != null) {
Instant ts = reply.getUpdatedAt();
if (ts.compareTo(messageEditingWindow) >= 0 || foreign) {
- return jdbcTemplate.update(
- "UPDATE replies SET txt=?, updated_at=? WHERE message_id=? AND reply_id=?", body,
- now.atOffset(ZoneOffset.UTC), mid, rid) > 0;
+ return namedParameterJdbcTemplate.update(
+ "UPDATE replies SET txt=:txt, updated_at=:now WHERE message_id=:mid AND reply_id=:rid",
+ new MapSqlParameterSource()
+ .addValue("txt", body)
+ .addValue("mid", mid)
+ .addValue("rid", rid)
+ .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0;
}
}
return false;
diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java
index 4657659b..c6f5bb89 100644
--- a/src/main/java/com/juick/service/TagServiceImpl.java
+++ b/src/main/java/com/juick/service/TagServiceImpl.java
@@ -18,8 +18,8 @@
package com.juick.service;
import com.juick.model.Tag;
-import com.juick.model.User;
import com.juick.model.TagStats;
+import com.juick.model.User;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.tuple.Pair;
@@ -38,11 +38,7 @@ import java.sql.Statement;
import java.time.Instant;
import java.time.ZoneOffset;
import java.time.temporal.ChronoUnit;
-import java.util.Arrays;
-import java.util.Collection;
-import java.util.List;
-import java.util.Objects;
-import java.util.Set;
+import java.util.*;
import java.util.function.Supplier;
import java.util.stream.Collectors;
import java.util.stream.Stream;
@@ -167,7 +163,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
public List<String> getPopularTags() {
return getJdbcTemplate().queryForList("""
select name from tags where noindex=0
- order by stat_messages desc OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""", String.class);
+ order by stat_messages desc
+ """ + limit(20), String.class);
}
@Transactional(readOnly = true)
@@ -181,7 +178,8 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
INNER JOIN tags ON messages_tags.tag_id=tags.tag_id
WHERE tags.tag_id NOT IN (SELECT tag_id FROM tags_ignore)
GROUP BY tags.tag_id, tags.name HAVING COUNT(DISTINCT messages.user_id) > 1
- ORDER BY cnt DESC OFFSET 0 ROWS FETCH NEXT 20 ROWS ONLY""";
+ ORDER BY cnt DESC
+ """ + limit(20);
return getNamedParameterJdbcTemplate()
.query(sql, new MapSqlParameterSource()
.addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE),
@@ -193,7 +191,6 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
public Set<Tag> updateTags(final int mid, final Collection<Tag> newTags) {
Set<Tag> currentTags = getMessageTags(mid).stream()
.map(TagStats::getTag).collect(Collectors.toSet());
-
if (CollectionUtils.isEmpty(newTags))
return currentTags;
@@ -240,7 +237,6 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
return Pair.of(body, tags);
}
- @Transactional(readOnly = true)
@Override
public List<TagStats> getMessageTags(final int mid) {
return getJdbcTemplate().query(
@@ -257,7 +253,6 @@ public class TagServiceImpl extends BaseJdbcService implements TagService {
}, mid);
}
- @Transactional(readOnly = true)
@Override
public List<Integer> getMessageTagsIDs(final int mid) {
return getJdbcTemplate().queryForList(
diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index 77fb2e72..e30be17b 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -29,6 +29,7 @@ import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.EmptyResultDataAccessException;
+import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
@@ -38,6 +39,7 @@ import org.springframework.transaction.annotation.Transactional;
import java.net.URI;
import java.sql.*;
import java.time.Instant;
+import java.time.OffsetDateTime;
import java.time.ZoneOffset;
import java.util.*;
@@ -46,15 +48,10 @@ import java.util.*;
*/
@Repository
public class UserServiceImpl extends BaseJdbcService implements UserService {
-
private static final Logger logger = LoggerFactory.getLogger("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> {
+ private class UserMapper implements RowMapper<User> {
@Override
public User mapRow(@Nonnull ResultSet rs, int rowNum) throws SQLException {
User user = new User();
@@ -63,7 +60,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
user.setName(rs.getString(2));
user.setCredentials(rs.getString(3));
user.setBanned(rs.getInt(4) > 0);
- Timestamp seen = rs.getTimestamp(5);
+ OffsetDateTime seen = UserServiceImpl.this.getOffsetDateTime(rs, 5);
if (seen != null) {
user.setSeen(seen.toInstant());
}
@@ -105,7 +102,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
getJdbcTemplate().update("INSERT INTO subscr_users(user_id, suser_id) VALUES (2, ?)", uid);
return getUserByUID(uid);
}
- } catch (DataIntegrityViolationException e) {
+ } catch (DataIntegrityViolationException | UncategorizedSQLException e) {
throw new UsernameTakenException();
}
return Optional.empty();
@@ -446,15 +443,14 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
"SELECT users.id,users.nick FROM (subscr_users " +
"INNER JOIN users_subscr ON (subscr_users.suser_id=? " +
"AND subscr_users.user_id=users_subscr.user_id)) INNER JOIN users " +
- "ON subscr_users.user_id=users.id ORDER BY cnt OFFSET 0 ROWS FETCH NEXT ? ROWS ONLY",
+ "ON subscr_users.user_id=users.id ORDER BY cnt " + limit(cnt),
(rs, num) -> {
User u = new User();
u.setUid(rs.getInt(1));
u.setName(rs.getString(2));
return u;
},
- uid,
- cnt);
+ uid);
}
@Transactional(readOnly = true)
@@ -593,6 +589,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
}
@Override
+ @Transactional
public void updateLastSeen(User user) {
getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid());
}