From f8a7d417cb916b81cfa685175f3e6afbe6063cee Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sun, 29 Jan 2023 05:44:21 +0300 Subject: SQLite support --- .../java/com/juick/service/BaseJdbcService.java | 80 ++++++- .../java/com/juick/service/ChatServiceImpl.java | 11 +- .../com/juick/service/MessagesServiceImpl.java | 119 ++++++----- .../java/com/juick/service/TagServiceImpl.java | 17 +- .../java/com/juick/service/UserServiceImpl.java | 19 +- .../java/com/juick/util/PrettyTimeFormatter.java | 3 +- src/main/java/com/juick/www/api/Messages.java | 3 + src/main/java/com/juick/www/controllers/Site.java | 3 + .../pebble/extension/FormatterExtension.java | 1 - .../pebble/extension/filters/PrettyTimeFilter.java | 2 +- .../pebble/extension/filters/TimestampFilter.java | 42 ---- src/main/resources/data-mysql.sql | 13 ++ src/main/resources/data-sqlite.sql | 22 +- src/main/resources/schema-sqlite.sql | 231 +++++++++++++-------- .../resources/templates/views/partial/message.html | 4 +- src/main/resources/templates/views/thread.html | 8 +- 16 files changed, 350 insertions(+), 228 deletions(-) delete mode 100644 src/main/java/com/mitchellbosecke/pebble/extension/filters/TimestampFilter.java create mode 100644 src/main/resources/data-mysql.sql (limited to 'src/main') 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 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 { @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 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 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 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 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 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 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 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 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 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 updateTags(final int mid, final Collection newTags) { Set 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 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 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 adminUsers; - @Value("#{new Boolean('${spring.sql.init.platform}' == 'sqlserver')}") - private boolean omitRecursiveKeyword; - - private static class UserMapper implements RowMapper { + private class UserMapper implements RowMapper { @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()); } diff --git a/src/main/java/com/juick/util/PrettyTimeFormatter.java b/src/main/java/com/juick/util/PrettyTimeFormatter.java index 34a230f5..79240eb2 100644 --- a/src/main/java/com/juick/util/PrettyTimeFormatter.java +++ b/src/main/java/com/juick/util/PrettyTimeFormatter.java @@ -19,6 +19,7 @@ package com.juick.util; import org.ocpsoft.prettytime.PrettyTime; +import java.time.Instant; import java.util.Date; import java.util.LinkedHashMap; import java.util.Locale; @@ -39,7 +40,7 @@ public class PrettyTimeFormatter { } }; - public String format(final Locale locale, final Date value) + public String format(final Locale locale, final Instant value) { PrettyTime prettyTime; diff --git a/src/main/java/com/juick/www/api/Messages.java b/src/main/java/com/juick/www/api/Messages.java index 06e3ac87..e3426454 100644 --- a/src/main/java/com/juick/www/api/Messages.java +++ b/src/main/java/com/juick/www/api/Messages.java @@ -174,6 +174,9 @@ public class Messages { // update replies counter to exclude banned replies msg.setReplies(replies.size()); if (!visitor.isAnonymous()) { + if (replies.size() > 0) { + messagesService.setRead(visitor, mid); + } userService.updateLastSeen(visitor); applicationEventPublisher .publishEvent(new SystemEvent(this, SystemActivity.read(visitor, msg))); diff --git a/src/main/java/com/juick/www/controllers/Site.java b/src/main/java/com/juick/www/controllers/Site.java index eb483ccc..49736a60 100644 --- a/src/main/java/com/juick/www/controllers/Site.java +++ b/src/main/java/com/juick/www/controllers/Site.java @@ -582,6 +582,9 @@ public class Site { && (isReplyAuthor || !userService.isInBL(visitor.getUid(), reply.getUser().getUid()))); } } + if (replies.size() > 0 && !visitor.isAnonymous()) { + messagesService.setRead(visitor, mid); + } model.addAttribute("replies", replies); return "views/thread"; } diff --git a/src/main/java/com/mitchellbosecke/pebble/extension/FormatterExtension.java b/src/main/java/com/mitchellbosecke/pebble/extension/FormatterExtension.java index bd8ba19d..4f6f3b53 100644 --- a/src/main/java/com/mitchellbosecke/pebble/extension/FormatterExtension.java +++ b/src/main/java/com/mitchellbosecke/pebble/extension/FormatterExtension.java @@ -34,7 +34,6 @@ public class FormatterExtension extends AbstractExtension { Map filters = new HashMap<>(); filters.put("formatMessage", new FormatMessageFilter()); filters.put("prettyTime", new PrettyTimeFilter()); - filters.put("timestamp", new TimestampFilter()); filters.put("tagsList", new TagsListFilter()); return filters; } diff --git a/src/main/java/com/mitchellbosecke/pebble/extension/filters/PrettyTimeFilter.java b/src/main/java/com/mitchellbosecke/pebble/extension/filters/PrettyTimeFilter.java index f5d82bfe..e3708e2a 100644 --- a/src/main/java/com/mitchellbosecke/pebble/extension/filters/PrettyTimeFilter.java +++ b/src/main/java/com/mitchellbosecke/pebble/extension/filters/PrettyTimeFilter.java @@ -39,7 +39,7 @@ public class PrettyTimeFilter implements Filter { public Object apply(Object input, Map args, PebbleTemplate self, EvaluationContext context, int lineNumber) { if (input instanceof Instant) { Locale locale = context.getLocale(); - return formatter.format(locale, Date.from((Instant)input)); + return formatter.format(locale, (Instant)input); } throw new IllegalArgumentException("invalid input"); } diff --git a/src/main/java/com/mitchellbosecke/pebble/extension/filters/TimestampFilter.java b/src/main/java/com/mitchellbosecke/pebble/extension/filters/TimestampFilter.java deleted file mode 100644 index dfe93673..00000000 --- a/src/main/java/com/mitchellbosecke/pebble/extension/filters/TimestampFilter.java +++ /dev/null @@ -1,42 +0,0 @@ -/* - * Copyright (C) 2008-2020, 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 - * published by the Free Software Foundation, either version 3 of the - * License, or (at your option) any later version. - * - * This program is distributed in the hope that it will be useful, - * but WITHOUT ANY WARRANTY; without even the implied warranty of - * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the - * GNU Affero General Public License for more details. - * - * You should have received a copy of the GNU Affero General Public License - * along with this program. If not, see . - */ - -package com.mitchellbosecke.pebble.extension.filters; - -import io.pebbletemplates.pebble.extension.Filter; -import io.pebbletemplates.pebble.template.EvaluationContext; -import io.pebbletemplates.pebble.template.PebbleTemplate; - -import java.time.Instant; -import java.util.Date; -import java.util.List; -import java.util.Map; - -public class TimestampFilter implements Filter { - @Override - public Object apply(Object input, Map args, PebbleTemplate self, EvaluationContext context, int lineNumber) { - if (input instanceof Instant) { - return Date.from((Instant)input); - } - throw new IllegalArgumentException("invalid input"); - } - - @Override - public List getArgumentNames() { - return null; - } -} diff --git a/src/main/resources/data-mysql.sql b/src/main/resources/data-mysql.sql new file mode 100644 index 00000000..a5db173a --- /dev/null +++ b/src/main/resources/data-mysql.sql @@ -0,0 +1,13 @@ +INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password'); +INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password'); +INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password'); +INSERT INTO tags(tag_id, name) VALUES(2, 'juick'); +ALTER TABLE tags AUTO_INCREMENT = 10; +ALTER TABLE users AUTO_INCREMENT = 10; +INSERT INTO reactions (like_id, description) VALUES (1, 'like'); +INSERT INTO reactions (like_id, description) VALUES (2, 'love'); +INSERT INTO reactions (like_id, description) VALUES (3, 'lol'); +INSERT INTO reactions (like_id, description) VALUES (4, 'hmm'); +INSERT INTO reactions (like_id, description) VALUES (5, 'angry'); +INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya'); +INSERT INTO reactions (like_id, description) VALUES (7, 'ugh'); diff --git a/src/main/resources/data-sqlite.sql b/src/main/resources/data-sqlite.sql index bb1203a4..d34cb832 100644 --- a/src/main/resources/data-sqlite.sql +++ b/src/main/resources/data-sqlite.sql @@ -1,11 +1,11 @@ -INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password'); -INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password'); -INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password'); -INSERT INTO tags(tag_id, name) VALUES(2, 'juick'); -INSERT INTO reactions (like_id, description) VALUES (1, 'like'); -INSERT INTO reactions (like_id, description) VALUES (2, 'love'); -INSERT INTO reactions (like_id, description) VALUES (3, 'lol'); -INSERT INTO reactions (like_id, description) VALUES (4, 'hmm'); -INSERT INTO reactions (like_id, description) VALUES (5, 'angry'); -INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya'); -INSERT INTO reactions (like_id, description) VALUES (7, 'ugh'); +INSERT INTO users(id, nick, passw) VALUES(0, 'Anonymous', 'password');; +INSERT INTO users(id, nick, passw) VALUES(2, 'juick', 'password');; +INSERT INTO users(id, nick, passw) VALUES(5, 'archive', 'password');; +INSERT INTO tags(tag_id, name) VALUES(2, 'juick');; +INSERT INTO reactions (like_id, description) VALUES (1, 'like');; +INSERT INTO reactions (like_id, description) VALUES (2, 'love');; +INSERT INTO reactions (like_id, description) VALUES (3, 'lol');; +INSERT INTO reactions (like_id, description) VALUES (4, 'hmm');; +INSERT INTO reactions (like_id, description) VALUES (5, 'angry');; +INSERT INTO reactions (like_id, description) VALUES (6, 'uhblya');; +INSERT INTO reactions (like_id, description) VALUES (7, 'ugh') \ No newline at end of file diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql index b624d67b..8e496069 100644 --- a/src/main/resources/schema-sqlite.sql +++ b/src/main/resources/schema-sqlite.sql @@ -1,64 +1,91 @@ -CREATE TABLE IF NOT EXISTS "user_services" ( +PRAGMA journal_mode=WAL;; +CREATE TABLE user_services ( user_id INTEGER NOT NULL, regid character varying(1024) NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, service_type varchar(255) not null default 'fcm', + ts DEFAULT CURRENT_TIMESTAMP 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + 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 bigint NOT NULL, + user_id INTEGER NOT NULL, bl_user_id bigint NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + 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 bigint, + user_id INTEGER, fb_id numeric, loginhash character varying(36), access_token character varying(255), - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + user_id INTEGER NOT NULL, message_id bigint NOT NULL, ts timestamp with time zone, 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 bigint, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + user_id INTEGER, + ts DEFAULT CURRENT_TIMESTAMP 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, @@ -68,33 +95,38 @@ CREATE TABLE images ( height bigint NOT NULL, width bigint NOT NULL, PRIMARY KEY (mid, rid) -); +);; CREATE TABLE jids ( - user_id bigint, + user_id INTEGER, jid character varying(64) NOT NULL, active smallint DEFAULT 0 NOT NULL, loginhash character varying(36), - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + 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 bigint NOT NULL, + user_id INTEGER NOT NULL, link character varying(255) NOT NULL, 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 bigint NOT NULL, + user_id INTEGER NOT NULL, lang TEXT DEFAULT '__' NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP NOT NULL, replies smallint DEFAULT (0) NOT NULL, maxreplyid smallint DEFAULT (0) NOT NULL, privacy smallint DEFAULT (1) NOT NULL, @@ -106,35 +138,35 @@ CREATE TABLE messages ( popular smallint DEFAULT (0) NOT NULL, hidden smallint DEFAULT (0) NOT NULL, likes smallint DEFAULT (0) NOT NULL, - updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + updated DEFAULT CURRENT_TIMESTAMP NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -); +);; CREATE TABLE messages_access ( message_id INTEGER NOT NULL, - user_id bigint 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 PRIMARY KEY NOT NULL, + message_id INTEGER NOT NULL, reply_id smallint NOT NULL, property_key character varying(255) NOT NULL, 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + updated_at DEFAULT CURRENT_TIMESTAMP 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, @@ -143,60 +175,70 @@ CREATE TABLE places ( descr character varying(255), url character varying(128), user_id bigint NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL -); + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, - user_id_to bigint NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + user_id INTEGER NOT NULL, + user_id_to INTEGER NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + user_id INTEGER NOT NULL, replyto smallint DEFAULT (0) NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP NOT NULL, attach TEXT CHECK (attach IN ('jpg', 'mp4', 'png')), txt text NOT NULL, - updated_at timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + updated_at DEFAULT CURRENT_TIMESTAMP NOT NULL, user_uri character varying(255) DEFAULT NULL, reply_uri character varying(255) DEFAULT NULL, 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 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 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 bigint NOT NULL, - suser_id bigint NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + user_id INTEGER NOT NULL, + suser_id INTEGER NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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, @@ -206,46 +248,56 @@ 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 bigint, + user_id INTEGER, tg_id numeric NOT NULL, tg_name character varying(64) NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + 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 timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + id INTEGER PRIMARY KEY, nick character varying(64) NOT NULL COLLATE NOCASE, passw character varying(32) NOT NULL, lang users_lang DEFAULT '__' NOT NULL, @@ -255,15 +307,15 @@ CREATE TABLE users ( lastphoto bigint DEFAULT (0) NOT NULL, karma smallint DEFAULT (0) NOT NULL, last_seen timestamp with time zone, - PRIMARY KEY (id) -); + UNIQUE(nick) +);; CREATE TABLE users_subscr ( - user_id bigint NOT NULL, + user_id INTEGER NOT NULL, cnt smallint DEFAULT (0) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) -); +);; CREATE TABLE usersinfo ( - user_id bigint NOT NULL, + user_id INTEGER NOT NULL, jid character varying(64), fullname character varying(64), country character varying(32), @@ -271,26 +323,32 @@ CREATE TABLE usersinfo ( gender character varying(32), bday character varying(32), descr text, + UNIQUE(user_id), FOREIGN KEY (user_id) REFERENCES users(id) -); +);; CREATE TABLE vk ( - user_id bigint, + user_id INTEGER, vk_id numeric NULL, loginhash character varying(36), access_token character varying(128) NOT NULL, - ts timestamp with time zone DEFAULT CURRENT_TIMESTAMP NOT NULL, + ts DEFAULT CURRENT_TIMESTAMP 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 bigint NOT NULL, + 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, @@ -305,4 +363,9 @@ 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/main/resources/templates/views/partial/message.html b/src/main/resources/templates/views/partial/message.html index 9a33c432..9d54d614 100644 --- a/src/main/resources/templates/views/partial/message.html +++ b/src/main/resources/templates/views/partial/message.html @@ -11,8 +11,8 @@ · {% endif %} - diff --git a/src/main/resources/templates/views/thread.html b/src/main/resources/templates/views/thread.html index 7b68df66..ca74a2ff 100644 --- a/src/main/resources/templates/views/thread.html +++ b/src/main/resources/templates/views/thread.html @@ -16,8 +16,8 @@ · {% endif %} - @@ -143,8 +143,8 @@ {% endif %}
- -- cgit v1.2.3