aboutsummaryrefslogtreecommitdiff
path: root/src/main/java
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-01-06 17:06:43 +0300
committerGravatar Vitaly Takmazov2023-01-07 04:04:26 +0300
commitc5e6a65323a7558ca0322ec810a9dff847968730 (patch)
tree037ff0ac5a2eb8224c3c5bc440484ffcef1cd0e9 /src/main/java
parent02e31496ee0ff099dd2a0fe0b1754acee6f2263a (diff)
Initial SQLite schema (work in progress)
Diffstat (limited to 'src/main/java')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java87
-rw-r--r--src/main/java/com/juick/service/NullSearchService.java3
-rw-r--r--src/main/java/com/juick/service/UserServiceImpl.java25
3 files changed, 59 insertions, 56 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 68d47429..bb50d953 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -296,9 +296,16 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (wasDeleted > 0) {
return RecommendStatus.Deleted;
} else {
- boolean wasAdded = getJdbcTemplate().update(
- "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)",
- vuid, mid, Reaction.LIKE, userUri) == 1;
+ var now = Instant.now().atOffset(ZoneOffset.UTC);
+ boolean wasAdded = getNamedParameterJdbcTemplate().update("""
+ INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri)
+ VALUES (:user_id, :message_id, :ts, :like_id, :user_uri)
+ """, new MapSqlParameterSource()
+ .addValue("user_id", vuid)
+ .addValue("message_id", mid)
+ .addValue("ts", now, Types.TIMESTAMP_WITH_TIMEZONE)
+ .addValue("like_id", Reaction.LIKE)
+ .addValue("user_uri", userUri)) == 1;
if (wasAdded) {
return RecommendStatus.Added;
}
@@ -342,9 +349,9 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
}
}
boolean wasAdded = getJdbcTemplate().update(
- "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, NOW(), ?, ?)",
+ "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)",
vuid,
- mid, reaction, userUri) == 1;
+ mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1;
if (wasAdded) {
return RecommendStatus.Added;
}
@@ -659,7 +666,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, message_id DESC LIMIT 20",
+ + "ORDER BY updated DESC, messages.message_id DESC LIMIT 20",
sqlParameterSource, (rs, rowNum) -> rs.getInt(1));
}
@@ -896,27 +903,30 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Override
public List<Message> getReplies(final User user, final int mid) {
List<Message> replies = getNamedParameterJdbcTemplate().query("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 "
- + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
- + "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(), "
- + "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 "
- + "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 "
- + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
- + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
- + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
- + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource("mid", mid).addValue("uid", user.getUid()),
+ + "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 "
+ + "INNER JOIN banned ON banned.reply_id = replies.replyto " + "WHERE replies.message_id = :mid) "
+ + "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, "
+ + "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 "
+ + "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 "
+ + "AND NOT EXISTS (SELECT 1 from users u WHERE u.banned = 1 and u.id = replies.user_id and u.id <> :uid)"
+ + "AND NOT EXISTS (SELECT 1 FROM banned WHERE banned.reply_id = replies.reply_id) "
+ + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid AND b.bl_user_id = m.user_id) "
+ + "ORDER BY replies.reply_id ASC", new MapSqlParameterSource()
+ .addValue("mid", mid)
+ .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);
@@ -1022,12 +1032,17 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
@Transactional(readOnly = true)
@Override
public List<ResponseReply> getLastReplies(int hours) {
- return getJdbcTemplate().query("SELECT users2.nick,replies.message_id,replies.reply_id,"
- + "users.nick,replies.txt," + "replies.ts,replies.attach,replies.ts+0, replies.html "
- + "FROM ((replies INNER JOIN users ON replies.user_id=users.id) "
- + "INNER JOIN messages ON replies.message_id=messages.message_id) "
- + "INNER JOIN users AS users2 ON messages.user_id=users2.id "
- + "WHERE replies.ts>TIMESTAMPADD(HOUR,?,NOW()) AND messages.privacy>0", (rs, rowNum) -> {
+ var datetime = Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC);
+ return getNamedParameterJdbcTemplate().query("""
+ SELECT users2.nick,replies.message_id,replies.reply_id,
+ users.nick,replies.txt,replies.ts,replies.attach,replies.ts, replies.html
+ FROM ((replies INNER JOIN users ON replies.user_id=users.id)
+ INNER JOIN messages ON replies.message_id=messages.message_id)
+ INNER JOIN users AS users2 ON messages.user_id=users2.id
+ WHERE replies.ts>:datetime AND messages.privacy>0
+ """,
+ new MapSqlParameterSource()
+ .addValue("datetime", datetime, Types.TIMESTAMP_WITH_TIMEZONE), (rs, rowNum) -> {
ResponseReply reply = new ResponseReply();
reply.setMuname(rs.getString(1));
reply.setMid(rs.getInt(2));
@@ -1038,7 +1053,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
reply.setAttachmentType(rs.getString(7));
reply.setHtml(rs.getBoolean(8));
return reply;
- }, -hours);
+ });
}
@Transactional(readOnly = true)
@@ -1109,7 +1124,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (ts.compareTo(messageEditingWindow) >= 0 || foreign) {
return jdbcTemplate.update(
"UPDATE messages_txt SET txt=?, updated_at=? WHERE messages_txt.message_id=?", body,
- Timestamp.from(now), mid) > 0;
+ now.atOffset(ZoneOffset.UTC), mid) > 0;
}
}
return false;
@@ -1120,7 +1135,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
if (ts.compareTo(messageEditingWindow) >= 0 || foreign) {
return jdbcTemplate.update(
"UPDATE replies SET txt=?, updated_at=? WHERE message_id=? AND reply_id=?", body,
- Timestamp.from(now), mid, rid) > 0;
+ now.atOffset(ZoneOffset.UTC), mid, rid) > 0;
}
}
return false;
diff --git a/src/main/java/com/juick/service/NullSearchService.java b/src/main/java/com/juick/service/NullSearchService.java
index efaccd53..2fb51b07 100644
--- a/src/main/java/com/juick/service/NullSearchService.java
+++ b/src/main/java/com/juick/service/NullSearchService.java
@@ -18,6 +18,7 @@
package com.juick.service;
import com.juick.model.User;
+import org.springframework.boot.autoconfigure.condition.ConditionalOnExpression;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.stereotype.Repository;
@@ -25,7 +26,7 @@ import java.util.Collections;
import java.util.List;
@Repository
-@ConditionalOnProperty(name = "spring.sql.init.platform", havingValue = "h2")
+@ConditionalOnExpression("('${spring.sql.init.platform}' == 'h2') or ('${spring.sql.init.platform}' == 'sqlite')")
public class NullSearchService implements SearchService {
@Override
public void setMaxResult(int maxResult) {
diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java
index b15a18e5..ab947bac 100644
--- a/src/main/java/com/juick/service/UserServiceImpl.java
+++ b/src/main/java/com/juick/service/UserServiceImpl.java
@@ -17,12 +17,7 @@
package com.juick.service;
-import com.juick.model.AnonymousUser;
-import com.juick.model.ApplicationStatus;
-import com.juick.model.AuthResponse;
-import com.juick.model.ExternalToken;
-import com.juick.model.Message;
-import com.juick.model.User;
+import com.juick.model.*;
import com.juick.util.UsernameTakenException;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.RandomStringUtils;
@@ -32,7 +27,6 @@ import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataIntegrityViolationException;
-import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
@@ -43,17 +37,10 @@ import org.springframework.transaction.annotation.Transactional;
import javax.annotation.Nonnull;
import java.net.URI;
-import java.sql.PreparedStatement;
-import java.sql.ResultSet;
-import java.sql.SQLException;
-import java.sql.Statement;
-import java.sql.Timestamp;
-import java.util.Collection;
-import java.util.Collections;
-import java.util.List;
-import java.util.Objects;
-import java.util.Optional;
-import java.util.UUID;
+import java.sql.*;
+import java.time.Instant;
+import java.time.ZoneOffset;
+import java.util.*;
/**
* Created by aalexeev on 11/13/16.
@@ -674,7 +661,7 @@ public class UserServiceImpl extends BaseJdbcService implements UserService {
@Override
public void updateLastSeen(User user) {
- getJdbcTemplate().update("UPDATE users SET last_seen=now() WHERE id=?", user.getUid());
+ getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", Instant.now().atOffset(ZoneOffset.UTC), user.getUid());
}
@Override