From 57222853c90b0ffc4b800adeeddc289d58ee0a25 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Wed, 1 Feb 2023 20:22:05 +0300 Subject: Store timestamp in milliseconds when using SQLite --- .../java/com/juick/service/BaseJdbcService.java | 19 ++- .../java/com/juick/service/EmailServiceImpl.java | 2 +- .../com/juick/service/MessagesServiceImpl.java | 185 +++++++++++---------- .../java/com/juick/service/TagServiceImpl.java | 2 +- .../java/com/juick/service/UserServiceImpl.java | 2 +- src/main/resources/schema-sqlite.sql | 174 +++++++------------ .../java/com/juick/server/tests/ServerTests.java | 3 +- src/test/resources/application-sqlite.yml | 1 - 8 files changed, 166 insertions(+), 222 deletions(-) diff --git a/src/main/java/com/juick/service/BaseJdbcService.java b/src/main/java/com/juick/service/BaseJdbcService.java index d6f29283..ab2e3fc8 100644 --- a/src/main/java/com/juick/service/BaseJdbcService.java +++ b/src/main/java/com/juick/service/BaseJdbcService.java @@ -17,7 +17,6 @@ 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; @@ -30,7 +29,6 @@ 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. @@ -82,9 +80,9 @@ public class BaseJdbcService { } public OffsetDateTime getOffsetDateTime(ResultSet rs, int columnIndex) throws SQLException { if (haveNoDates) { - var date = rs.getString(columnIndex); - if (StringUtils.isNotEmpty(date)) { - return OffsetDateTime.parse(date); + var date = rs.getLong(columnIndex); + if (date != 0) { + return Instant.ofEpochMilli(date).atOffset(ZoneOffset.UTC); } return null; } @@ -99,17 +97,22 @@ public class BaseJdbcService { } public Object fromEpochMilli(Long milliseconds) { if (haveNoDates) { - return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC) - .format(DateTimeFormatter.ISO_OFFSET_DATE_TIME); + return milliseconds; } if (haveNoOffsetDateTime) { return new Timestamp(milliseconds); } return Instant.ofEpochMilli(milliseconds).atOffset(ZoneOffset.UTC); } + public Object toDateTime(OffsetDateTime now) { + if (haveNoDates) { + return now.toInstant().toEpochMilli(); + } + return now; + } public int dateTimeType() { if (haveNoDates) { - return Types.VARCHAR; + return Types.INTEGER; } if (haveNoOffsetDateTime) { return Types.TIMESTAMP; diff --git a/src/main/java/com/juick/service/EmailServiceImpl.java b/src/main/java/com/juick/service/EmailServiceImpl.java index 7f358533..85e56a05 100644 --- a/src/main/java/com/juick/service/EmailServiceImpl.java +++ b/src/main/java/com/juick/service/EmailServiceImpl.java @@ -119,6 +119,6 @@ public class EmailServiceImpl extends BaseJdbcService implements EmailService { Instant day = Instant.now().minus(1, ChronoUnit.DAYS); return getNamedParameterJdbcTemplate().update("DELETE FROM auth WHERE ts < :day", new MapSqlParameterSource() - .addValue("day", day.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)); + .addValue("day", toDateTime(day.atOffset(ZoneOffset.UTC)), dateTimeType())); } } diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index 112fc5bf..3ddfcd00 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -85,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(MessagesServiceImpl.this.getOffsetDateTime(rs,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; @@ -99,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(MessagesServiceImpl.this.getOffsetDateTime(rs,18).toInstant()); + msg.setUpdated(MessagesServiceImpl.this.getOffsetDateTime(rs, 18).toInstant()); int quoteUid = rs.getInt(19); User quoteUser = new User(); quoteUser.setUid(quoteUid); @@ -111,7 +111,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ quoteUser.setAvatar(webApp.getAvatarUrl(quoteUser)); } msg.setTo(quoteUser); - msg.setUpdatedAt(MessagesServiceImpl.this.getOffsetDateTime(rs,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); @@ -129,8 +129,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ /** * @see Java, - * JDBC and MySQL Types + * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java, + * JDBC and MySQL Types */ @Transactional @Override @@ -140,8 +140,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ 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); + insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); + insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); if (StringUtils.isNotEmpty(attachment)) { insertMap.addValue("attach", attachment); } @@ -169,15 +169,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ new MapSqlParameterSource() .addValue("mid", mid) .addValue("txt", txt) - .addValue("now", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE)); + .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", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) - .addValue("last_seen", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) + .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) .addValue("uid", uid)); } return mid; @@ -191,8 +191,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ * @param attachment * @return * @see Java, - * JDBC and MySQL Types + * "https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html">Java, + * JDBC and MySQL Types */ @Transactional @Override @@ -211,27 +211,27 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("replyto", rid) .addValue("attach", attachment) .addValue("txt", txt) - .addValue("ts", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) - .addValue("updated_at", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) + .addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) .addValue("user_uri", user.getUri().toASCIIString())); getNamedParameterJdbcTemplate() .update( "UPDATE messages SET replies = replies + 1, updated=:updated WHERE message_id = :message_id", new MapSqlParameterSource() - .addValue("updated", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) .addValue("message_id", mid)); setLastReadComment(user, mid, ridnew); getNamedParameterJdbcTemplate() .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid", new MapSqlParameterSource() - .addValue("lastmessage", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) - .addValue("last_seen", now.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE) + .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) + .addValue("last_seen", toDateTime(now.atOffset(ZoneOffset.UTC)), + dateTimeType()) .addValue("uid", user.getUid())); } return ridnew; @@ -280,8 +280,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional void updateRepliesBy(int mid) { List 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 " + limit(5), String.class, + + "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 " + 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); @@ -299,14 +299,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ if (wasDeleted > 0) { return RecommendStatus.Deleted; } else { - var now = Instant.now().atOffset(ZoneOffset.UTC); + var now = toDateTime(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("ts", now, dateTimeType()) .addValue("like_id", Reaction.LIKE) .addValue("user_uri", userUri)) == 1; if (wasAdded) { @@ -354,7 +354,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ boolean wasAdded = getJdbcTemplate().update( "INSERT INTO favorites(user_id, message_id, ts, like_id, user_uri) VALUES (?, ?, ?, ?, ?)", vuid, - mid, Instant.now().atOffset(ZoneOffset.UTC), reaction, userUri) == 1; + mid, toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), reaction, userUri) == 1; if (wasAdded) { return RecommendStatus.Added; } @@ -379,7 +379,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return privacyOpts == null || privacyOpts.getPrivacy() >= 0 || uid == privacyOpts.getUid() || ((privacyOpts.getPrivacy() == -1 || privacyOpts.getPrivacy() == -2) && uid > 0 - && userService.isInWL(privacyOpts.getUid(), uid)); + && userService.isInWL(privacyOpts.getUid(), uid)); } @Transactional(readOnly = true) @@ -404,9 +404,11 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ return list.isEmpty() ? -4 : list.get(0); } + public Optional getMessage(int mid) { return getMessage(0, mid); } + @Transactional(readOnly = true) @Override public Optional getMessage(int uid, final int mid) { @@ -516,12 +518,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("visitorUid", visitorUid); return getNamedParameterJdbcTemplate().queryForList("SELECT m.message_id FROM messages m WHERE " - + (before > 0 ? " m.message_id < :before AND " : StringUtils.EMPTY) + " m.hidden = 0 AND (m.privacy > 0" - + (visitorUid > 1 ? " OR m.user_id = :visitorUid) AND NOT EXISTS (" + + (before > 0 ? " m.message_id < :before AND " : StringUtils.EMPTY) + " m.hidden = 0 AND (m.privacy > 0" + + (visitorUid > 1 ? " OR m.user_id = :visitorUid) AND NOT EXISTS (" + " SELECT 1 FROM bl_users b WHERE b.user_id = :visitorUid AND b.bl_user_id = m.user_id)" : ")") - + " 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)" + + + " 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 " + limit(20), sqlParameterSource, Integer.class); } @@ -585,14 +587,14 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 " + "AND EXISTS (SELECT 1 FROM wl_users w WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + (recommended ? "OR (EXISTS (SELECT 1 FROM favorites WHERE favorites.message_id=messages.message_id " - + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) " - + "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 :uid = bt.user_id) " - + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 " - + "AND EXISTS (SELECT 1 FROM wl_users w " - + "WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " - + "AND NOT EXISTS (SELECT 1 FROM bl_users b WHERE b.user_id = :uid and b.bl_user_id = messages.user_id)" - : StringUtils.EMPTY) + + "AND favorites.user_id IN (SELECT user_id FROM subscr_users WHERE suser_id=:uid)) " + + "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 :uid = bt.user_id) " + + "AND (privacy >= 0 OR (privacy >= -2 AND privacy <= -1 " + + "AND EXISTS (SELECT 1 FROM wl_users w " + + "WHERE w.wl_user_id = :uid and w.user_id = messages.user_id)))) " + + "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 " + limit(20), sqlParameterSource, Integer.class); } @@ -705,15 +707,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", uid) .addValue("privacy", privacy) - .addValue("date", backDate, Types.TIMESTAMP_WITH_TIMEZONE); + .addValue("date", toDateTime(backDate), dateTimeType()); return getNamedParameterJdbcTemplate().queryForList( "SELECT message_id FROM messages" + " INNER JOIN users" + " ON messages.user_id = users.id" + " WHERE user_id = :uid" + (daysback > 0 - ? " AND ts >= :date" - + " AND ts < :date" - : StringUtils.EMPTY) + ? " AND ts >= :date" + + " AND ts < :date" + : StringUtils.EMPTY) + " AND privacy >= :privacy AND users.banned = 0 ORDER BY message_id DESC " + limit(20), sqlParameterSource, Integer.class); } @@ -721,7 +723,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public List getUserBlogWithRecommendations(final User user, final User visitor, final int privacy, - final int before) { + final int before) { SqlParameterSource sqlParameterSource = new MapSqlParameterSource() .addValue("uid", user.getUid()) .addValue("vid", visitor.getUid()) @@ -779,7 +781,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Transactional(readOnly = true) @Override public List getUserSearch(final User visitor, final int UID, final String search, final int privacy, - final int page) { + final int page) { return searchService.searchByStringAndUser(visitor, search, UID, page); } @@ -850,14 +852,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ } return Collections.emptyList(); } + private Map> updateReactionsFor(final List mids) { // This method always called from the transactional block, so it should not be // marked as transactional itself return getNamedParameterJdbcTemplate().query(""" - select f.message_id as mid, f.like_id as lid, - r.description as descr, count(f.like_id) as cnt - from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id - where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""", + select f.message_id as mid, f.like_id as lid, + r.description as descr, count(f.like_id) as cnt + from favorites f LEFT JOIN reactions r ON f.like_id = r.like_id + where f.message_id IN (:mids) group by f.message_id, f.like_id, r.description""", new MapSqlParameterSource("mids", mids), (ResultSet rs) -> { Map> results = new HashMap<>(); while (rs.next()) { @@ -880,32 +883,32 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public List getReplies(final User user, final int mid) { List 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 " - + "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, " - + "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 " - + "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", + + "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, " + + "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 " + + "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), + .addValue("now", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), dateTimeType()), new MessageMapper()); replies.forEach(i -> { i.setEntities(MessageUtils.getEntities(i)); @@ -1003,8 +1006,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ "SELECT message_id FROM messages WHERE messages.ts > :hours", new MapSqlParameterSource() .addValue("hours", - Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE), + toDateTime(Instant.now().minus(hours, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC)), + dateTimeType()), Integer.class); } @@ -1014,15 +1017,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ public List getLastReplies(int hours) { 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 - """, + 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), + .addValue("datetime", toDateTime(datetime), dateTimeType()), (rs, rowNum) -> { ResponseReply reply = new ResponseReply(); reply.setMuname(rs.getString(1)); @@ -1062,8 +1065,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ """; return getNamedParameterJdbcTemplate().queryForList(sql, new MapSqlParameterSource() .addValue("nsfw_id", nsfw.getId()) - .addValue("before", beforeTime.atOffset(ZoneOffset.UTC), - java.sql.Types.TIMESTAMP_WITH_TIMEZONE), + .addValue("before", toDateTime(beforeTime.atOffset(ZoneOffset.UTC)), + dateTimeType()), Integer.class); } @@ -1111,7 +1114,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ new MapSqlParameterSource() .addValue("txt", body) .addValue("mid", mid) - .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0; + .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0; } } return false; @@ -1126,7 +1129,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ .addValue("txt", body) .addValue("mid", mid) .addValue("rid", rid) - .addValue("now", now.atOffset(ZoneOffset.UTC), Types.TIMESTAMP_WITH_TIMEZONE)) > 0; + .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType())) > 0; } } return false; diff --git a/src/main/java/com/juick/service/TagServiceImpl.java b/src/main/java/com/juick/service/TagServiceImpl.java index c6f5bb89..ef5f6e84 100644 --- a/src/main/java/com/juick/service/TagServiceImpl.java +++ b/src/main/java/com/juick/service/TagServiceImpl.java @@ -182,7 +182,7 @@ public class TagServiceImpl extends BaseJdbcService implements TagService { """ + limit(20); return getNamedParameterJdbcTemplate() .query(sql, new MapSqlParameterSource() - .addValue("ts", ts.atOffset(ZoneOffset.UTC), java.sql.Types.TIMESTAMP_WITH_TIMEZONE), + .addValue("ts", toDateTime(ts.atOffset(ZoneOffset.UTC)), dateTimeType()), new TagStatsMapper()); } diff --git a/src/main/java/com/juick/service/UserServiceImpl.java b/src/main/java/com/juick/service/UserServiceImpl.java index e30be17b..8ed8fd96 100644 --- a/src/main/java/com/juick/service/UserServiceImpl.java +++ b/src/main/java/com/juick/service/UserServiceImpl.java @@ -591,7 +591,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()); + getJdbcTemplate().update("UPDATE users SET last_seen=? WHERE id=?", toDateTime(Instant.now().atOffset(ZoneOffset.UTC)), user.getUid()); } @Override diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql index 8e496069..39895b2e 100644 --- a/src/main/resources/schema-sqlite.sql +++ b/src/main/resources/schema-sqlite.sql @@ -1,71 +1,51 @@ -PRAGMA journal_mode=WAL;; +PRAGMA journal_mode=WAL; CREATE TABLE user_services ( user_id INTEGER NOT NULL, regid character varying(1024) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, service_type varchar(255) not null default 'fcm', FOREIGN KEY (user_id) REFERENCES users(id), UNIQUE (regid) -);; -CREATE TRIGGER INSERT_user_services AFTER INSERT ON user_services - BEGIN - UPDATE user_services SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE auth ( user_id INTEGER, protocol TEXT CHECK (protocol IN ('xmpp', 'email', 'sms')) NOT NULL, account character varying(128) NOT NULL, authcode character varying(8) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_auth AFTER INSERT ON auth - BEGIN - UPDATE auth SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE bl_tags ( user_id INTEGER NOT NULL, tag_id bigint NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (tag_id) REFERENCES tags(tag_id) -);; +); CREATE TABLE bl_users ( user_id INTEGER NOT NULL, bl_user_id bigint NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, PRIMARY KEY (user_id, bl_user_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (bl_user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_bl_users AFTER INSERT ON bl_users - BEGIN - UPDATE bl_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE emails ( user_id INTEGER NOT NULL, email character varying(128) NOT NULL, subscr_hour smallint, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE facebook ( user_id INTEGER, fb_id numeric, loginhash character varying(36), access_token character varying(255), - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, fb_name character varying(64), fb_link character varying(255) NOT NULL, crosspost boolean DEFAULT true NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_facebook AFTER INSERT ON facebook - BEGIN - UPDATE facebook SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE favorites ( user_id INTEGER NOT NULL, message_id bigint NOT NULL, @@ -73,19 +53,14 @@ CREATE TABLE favorites ( like_id smallint DEFAULT 1 NOT NULL, user_uri character varying(255) NOT NULL DEFAULT '', FOREIGN KEY (like_id) REFERENCES reactions(like_id) -);; +); CREATE TABLE followers ( user_id INTEGER, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, acct character varying(64) NOT NULL, PRIMARY KEY (user_id) FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_followers AFTER INSERT ON followers - BEGIN - UPDATE followers SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE images ( mid bigint NOT NULL, rid bigint NOT NULL, @@ -95,25 +70,20 @@ CREATE TABLE images ( height bigint NOT NULL, width bigint NOT NULL, PRIMARY KEY (mid, rid) -);; +); CREATE TABLE jids ( user_id INTEGER, jid character varying(64) NOT NULL, active smallint DEFAULT 0 NOT NULL, loginhash character varying(36), - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_jids AFTER INSERT ON jids - BEGIN - UPDATE jids SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE logins ( user_id INTEGER NOT NULL, hash character varying(16) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE meon ( id INTEGER NOT NULL, user_id INTEGER NOT NULL, @@ -121,12 +91,12 @@ CREATE TABLE meon ( name character varying(32) NOT NULL, ico smallint, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE messages ( message_id INTEGER PRIMARY KEY NOT NULL, user_id INTEGER NOT NULL, lang TEXT DEFAULT '__' NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, replies smallint DEFAULT (0) NOT NULL, maxreplyid smallint DEFAULT (0) NOT NULL, privacy smallint DEFAULT (1) NOT NULL, @@ -138,15 +108,15 @@ CREATE TABLE messages ( popular smallint DEFAULT (0) NOT NULL, hidden smallint DEFAULT (0) NOT NULL, likes smallint DEFAULT (0) NOT NULL, - updated DEFAULT CURRENT_TIMESTAMP NOT NULL, + updated DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE messages_access ( message_id INTEGER NOT NULL, user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (message_id) REFERENCES messages(message_id) -);; +); CREATE TABLE messages_properties ( message_id INTEGER NOT NULL, reply_id smallint NOT NULL, @@ -154,19 +124,19 @@ CREATE TABLE messages_properties ( property_value text NOT NULL, UNIQUE (message_id, reply_id, property_key), FOREIGN KEY (message_id) REFERENCES messages(message_id) -);; +); CREATE TABLE messages_tags ( message_id INTEGER NOT NULL, tag_id bigint NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id) -);; +); CREATE TABLE messages_txt ( message_id INTEGER NOT NULL, repliesby text, txt text NOT NULL, - updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL, + updated_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id) -);; +); CREATE TABLE places ( place_id INTEGER PRIMARY KEY NOT NULL, lat numeric(10,7) NOT NULL, @@ -175,36 +145,31 @@ CREATE TABLE places ( descr character varying(255), url character varying(128), user_id bigint NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL -);; + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL +); CREATE TABLE places_tags ( place_id INTEGER NOT NULL, tag_id bigint NOT NULL, FOREIGN KEY (place_id) REFERENCES places(place_id) -);; +); CREATE TABLE pm ( user_id INTEGER NOT NULL, user_id_to INTEGER NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, txt text NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (user_id_to) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_pm AFTER INSERT ON pm - BEGIN - UPDATE pm SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE reactions ( like_id INTEGER PRIMARY KEY NOT NULL, description character varying(100) NOT NULL -);; +); CREATE TABLE replies ( message_id bigint NOT NULL, reply_id smallint NOT NULL, user_id INTEGER NOT NULL, replyto smallint DEFAULT (0) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')), txt text NOT NULL, updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL, @@ -213,32 +178,27 @@ CREATE TABLE replies ( html smallint DEFAULT '0' NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id), FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE subscr_messages ( message_id bigint NOT NULL, suser_id INTEGER NOT NULL, last_read_rid smallint DEFAULT 0 NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id), FOREIGN KEY (suser_id) REFERENCES users(id) -);; +); CREATE TABLE subscr_tags ( tag_id bigint NOT NULL, suser_id INTEGER NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(tag_id), FOREIGN KEY (suser_id) REFERENCES users(id) -);; +); CREATE TABLE subscr_users ( user_id INTEGER NOT NULL, suser_id INTEGER NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (suser_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_subscr_users AFTER INSERT ON subscr_users - BEGIN - UPDATE subscr_users SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE tags ( tag_id INTEGER PRIMARY KEY NOT NULL, synonym_id bigint, @@ -248,54 +208,44 @@ CREATE TABLE tags ( stat_messages bigint DEFAULT (0) NOT NULL, stat_users smallint DEFAULT (0) NOT NULL, FOREIGN KEY (synonym_id) REFERENCES tags(tag_id) -);; +); CREATE TABLE tags_ignore ( tag_id bigint NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(tag_id) -);; +); CREATE TABLE tags_synonyms ( name character varying(64) NOT NULL, changeto character varying(64) NOT NULL -);; +); CREATE TABLE telegram ( user_id INTEGER, tg_id numeric NOT NULL, tg_name character varying(64) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, loginhash character varying(36), FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_telegram AFTER INSERT ON telegram - BEGIN - UPDATE telegram SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE top_ignore_messages ( message_id bigint NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id) -);; +); CREATE TABLE top_ignore_tags ( tag_id NOT NULL, FOREIGN KEY (tag_id) REFERENCES tag(tag_id) -);; +); CREATE TABLE top_ignore_users ( user_id INTEGER NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE twitter ( user_id INTEGER NOT NULL, access_token character varying(64) NOT NULL, access_token_secret character varying(64) NOT NULL, uname character varying(64) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, crosspost boolean DEFAULT true NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_twitter AFTER INSERT ON twitter - BEGIN - UPDATE twitter SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE users ( id INTEGER PRIMARY KEY, nick character varying(64) NOT NULL COLLATE NOCASE, @@ -308,12 +258,12 @@ CREATE TABLE users ( karma smallint DEFAULT (0) NOT NULL, last_seen timestamp with time zone, UNIQUE(nick) -);; +); CREATE TABLE users_subscr ( user_id INTEGER NOT NULL, cnt smallint DEFAULT (0) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE usersinfo ( user_id INTEGER NOT NULL, jid character varying(64), @@ -325,34 +275,29 @@ CREATE TABLE usersinfo ( descr text, UNIQUE(user_id), FOREIGN KEY (user_id) REFERENCES users(id) -);; +); CREATE TABLE vk ( user_id INTEGER, vk_id numeric NULL, loginhash character varying(36), access_token character varying(128) NOT NULL, - ts DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, vk_name character varying(64) NOT NULL, vk_link character varying(64) NOT NULL, crosspost smallint DEFAULT (1) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -);; -CREATE TRIGGER INSERT_vk AFTER INSERT ON vk - BEGIN - UPDATE vk SET ts = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END;; +); CREATE TABLE wl_users ( user_id INTEGER NOT NULL, wl_user_id bigint NOT NULL, PRIMARY KEY (user_id, wl_user_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (wl_user_id) REFERENCES users(id) -);; +); CREATE TABLE oauth2_registered_client ( id varchar(100) NOT NULL, client_id varchar(100) NOT NULL, - client_id_issued_at timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, + client_id_issued_at timestamp DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, client_secret varchar(200) DEFAULT NULL, client_secret_expires_at timestamp DEFAULT NULL, client_name varchar(200) NOT NULL, @@ -363,9 +308,4 @@ CREATE TABLE oauth2_registered_client ( client_settings varchar(2000) NOT NULL, token_settings varchar(2000) NOT NULL, PRIMARY KEY (id) -);; -CREATE TRIGGER INSERT_oauth2_registered_client AFTER INSERT ON oauth2_registered_client - BEGIN - UPDATE oauth2_registered_client SET client_id_issued_at = strftime("%Y-%m-%dT%H:%M:%fZ", CURRENT_TIMESTAMP) - WHERE rowid = new.rowid; - END +); diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java index 62f50b12..4cbc30b5 100644 --- a/src/test/java/com/juick/server/tests/ServerTests.java +++ b/src/test/java/com/juick/server/tests/ServerTests.java @@ -1779,7 +1779,6 @@ public class ServerTests { @Test @Order(20) - @Disabled("Failed on SQLite") public void discussionsShouldBePageableByTimestamp() throws Exception { String msgText = "Привет, я снова Угнич"; int mid = messagesService.createMessage(ugnich.getUid(), msgText, null, Set.of()); @@ -2622,7 +2621,7 @@ public class ServerTests { "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid()); assertThat(count, is(1)); var timestamp = Instant.now().minus(3, ChronoUnit.DAYS).atOffset(ZoneOffset.UTC); - jdbcTemplate.update("UPDATE auth SET ts=? WHERE user_id=?", timestamp, ugnich.getUid()); + jdbcTemplate.update("UPDATE auth SET ts=? WHERE user_id=?", ((EmailServiceImpl)emailService).toDateTime(timestamp), ugnich.getUid()); emailService.cleanupAuthCodes(); count = jdbcTemplate.queryForObject( "SELECT COUNT(*) FROM auth WHERE user_id=?", Integer.class, ugnich.getUid()); diff --git a/src/test/resources/application-sqlite.yml b/src/test/resources/application-sqlite.yml index 6a838f13..6a9c0516 100644 --- a/src/test/resources/application-sqlite.yml +++ b/src/test/resources/application-sqlite.yml @@ -5,7 +5,6 @@ spring: url: jdbc:sqlite:data.db sql: init: - separator: ;; platform: sqlite mode: always -- cgit v1.2.3