diff options
| -rw-r--r-- | src/main/java/com/juick/service/MessagesServiceImpl.java | 41 | ||||
| -rw-r--r-- | src/main/java/com/juick/service/PostgresSearchService.java | 8 | ||||
| -rw-r--r-- | src/main/resources/db/migration/V1.43__merge_messages_txt.sql | 8 | ||||
| -rw-r--r-- | src/main/resources/schema-h2.sql | 12 | ||||
| -rw-r--r-- | src/main/resources/schema-mysql.sql | 35 | ||||
| -rw-r--r-- | src/main/resources/schema-sqlite.sql | 10 | ||||
| -rw-r--r-- | src/main/resources/schema-sqlserver.sql | 10 | ||||
| -rw-r--r-- | src/test/java/com/juick/server/tests/ServerTests.java | 4 |
8 files changed, 41 insertions, 87 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java index c5b29fd5..ccace3a8 100644 --- a/src/main/java/com/juick/service/MessagesServiceImpl.java +++ b/src/main/java/com/juick/service/MessagesServiceImpl.java @@ -136,12 +136,15 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ @Override public int createMessage(final int uid, final String txt, final String attachment, @NonNull final Set<Tag> tags) { SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(getJdbcTemplate()).withTableName("messages") - .usingColumns("user_id", "attach", "ts", "readonly", "updated").usingGeneratedKeyColumns("message_id"); + .usingColumns("user_id", "attach", "ts", "readonly", "updated", "updated_at", "txt") + .usingGeneratedKeyColumns("message_id"); var insertMap = new MapSqlParameterSource(); insertMap.addValue("user_id", uid); var now = Instant.now(); insertMap.addValue("ts", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); insertMap.addValue("updated", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); + insertMap.addValue("updated_at", toDateTime(now.atOffset(ZoneOffset.UTC)), dateTimeType()); + insertMap.addValue("txt", StringUtils.defaultString(txt)); if (StringUtils.isNotEmpty(attachment)) { insertMap.addValue("attach", attachment); } @@ -165,13 +168,6 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ }); } getNamedParameterJdbcTemplate() - .update("INSERT INTO messages_txt(message_id, txt, updated_at) VALUES (:mid, :txt, :now)", - new MapSqlParameterSource() - .addValue("mid", mid) - .addValue("txt", txt) - .addValue("now", toDateTime(now.atOffset(ZoneOffset.UTC)), - dateTimeType())); - getNamedParameterJdbcTemplate() .update("UPDATE users SET lastmessage=:lastmessage, last_seen=:last_seen where id=:uid", new MapSqlParameterSource() .addValue("lastmessage", toDateTime(now.atOffset(ZoneOffset.UTC)), @@ -284,7 +280,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "GROUP BY replies.user_id, users.nick ORDER BY COUNT(replies.reply_id) DESC " + limit(5), String.class, mid); String result = users.stream().map(u -> "@" + u).collect(Collectors.joining(",")); - getJdbcTemplate().update("UPDATE messages_txt SET repliesby=? WHERE message_id=?", result, mid); + getJdbcTemplate().update("UPDATE messages SET repliesby=? WHERE message_id=?", result, mid); } @Transactional @@ -427,13 +423,12 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ public Message getReply(final int mid, final int rid) { var sql = """ SELECT replies.user_id, users.nick, - replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(q.txt,t.txt) as quote, + replies.replyto, replies.ts, replies.attach, replies.txt, COALESCE(q.txt,m.txt) as quote, COALESCE(q.user_id, m.user_id) AS to_uid, COALESCE(qu.nick, mu.nick) AS to_name, replies.updated_at, replies.user_uri as uri, q.user_uri AS to_uri, replies.reply_uri AS reply_uri, replies.html, q.reply_uri FROM replies LEFT JOIN users ON replies.user_id = users.id LEFT JOIN replies q ON replies.message_id = q.message_id and replies.replyto = q.reply_id - LEFT JOIN messages_txt t ON replies.message_id = t.message_id LEFT JOIN messages m ON replies.message_id = m.message_id LEFT JOIN users qu ON q.user_id=qu.id LEFT JOIN users mu ON m.user_id=mu.id WHERE replies.message_id = ? AND replies.reply_id = ? @@ -811,29 +806,29 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ COUNT(DISTINCT favorites.user_id) AS likes, messages.hidden, '' as tags, - messages_txt.repliesby, - messages_txt.txt, '' as q, + messages.repliesby, + messages.txt, '' as q, messages.updated, 0 as to_uid, NULL as to_name, - messages_txt.updated_at, + messages.updated_at, '' as m_user_uri, '' as to_uri, '' as msg_reply_uri, 0 as html, (1.*messages.replies - subscr_messages.last_read_rid) as unread, (SELECT CASE WHEN EXISTS(SELECT * from subscr_messages where message_id=messages.message_id and suser_id=:uid) THEN 1 ELSE 0 END) subscribed - FROM (messages INNER JOIN messages_txt ON messages.message_id=messages_txt.message_id) + FROM messages INNER JOIN users ON messages.user_id=users.id LEFT JOIN subscr_messages ON messages.message_id=subscr_messages.message_id AND subscr_messages.suser_id=:uid LEFT JOIN favorites ON messages.message_id = favorites.message_id AND favorites.like_id=1 LEFT JOIN banned ON messages.message_id = banned.message_id - LEFT JOIN messages_tags ON messages_tags.message_id=messages_txt.message_id + LEFT JOIN messages_tags ON messages_tags.message_id=messages.message_id LEFT JOIN tags ON tags.tag_id=messages_tags.tag_id WHERE messages.message_id IN (:ids) GROUP BY messages.message_id, messages.user_id, users.nick, users.banned, messages.ts, messages.readonly, messages.privacy, messages.attach, messages.hidden, - messages_txt.repliesby, messages_txt.txt, messages.updated, messages.replies, updated_at, + messages.repliesby, messages.txt, messages.updated, messages.replies, updated_at, subscr_messages.last_read_rid"""; List<Message> msgs = getNamedParameterJdbcTemplate().query(query, new MapSqlParameterSource("ids", mids) @@ -890,14 +885,13 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ + "SELECT replies.message_id as mid, replies.reply_id, replies.replyto, " + "replies.user_id, users.nick, users.banned, " + "replies.ts, " + "0 as readonly, 0 as privacy, 0 as replies, " + "replies.attach, 0 as likes, 0 as hidden, " - + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, t.txt) as q, " + ":now, " + + "NULL as tags, NULL as repliesby, replies.txt, " + "COALESCE(qw.txt, m.txt) as q, " + ":now, " + "COALESCE(qw.user_id, m.user_id) as to_uid, COALESCE(qu.nick, mu.nick) as to_name, " + "replies.updated_at, replies.user_uri as uri, " + "qw.user_uri as to_uri, replies.reply_uri, replies.html, 0 as unread, " + "0 as subscribed " + "FROM replies LEFT JOIN users " + "ON replies.user_id = users.id " + "LEFT JOIN replies qw ON replies.message_id = qw.message_id and replies.replyto = qw.reply_id " - + "LEFT JOIN messages_txt t on replies.message_id = t.message_id " + "LEFT JOIN messages m on replies.message_id = m.message_id " + "LEFT JOIN users qu ON qw.user_id=qu.id " + "LEFT JOIN users mu ON m.user_id=mu.id " + "WHERE replies.message_id = :mid " @@ -943,11 +937,8 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ parameters); getNamedParameterJdbcTemplate().update("DELETE FROM messages_properties WHERE message_id = :mid", parameters); - if (getNamedParameterJdbcTemplate().update("DELETE FROM messages_txt WHERE message_id = :mid", - parameters) > 0) { - return getNamedParameterJdbcTemplate() - .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0; - } + return getNamedParameterJdbcTemplate() + .update("DELETE FROM messages WHERE message_id = :mid AND user_id = :uid", parameters) > 0; } else { return getNamedParameterJdbcTemplate().update( "UPDATE messages SET user_id=:archive_uid WHERE message_id = :mid AND user_id = :uid", @@ -1109,7 +1100,7 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ Instant ts = message.get().getUpdatedAt(); if (ts.compareTo(messageEditingWindow) >= 0 || foreign) { return namedParameterJdbcTemplate.update( - "UPDATE messages_txt SET txt=:txt, updated_at=:now WHERE messages_txt.message_id=:mid", + "UPDATE messages SET txt=:txt, updated_at=:now WHERE message_id=:mid", new MapSqlParameterSource() .addValue("txt", body) .addValue("mid", mid) diff --git a/src/main/java/com/juick/service/PostgresSearchService.java b/src/main/java/com/juick/service/PostgresSearchService.java index 500f3d20..2493a45e 100644 --- a/src/main/java/com/juick/service/PostgresSearchService.java +++ b/src/main/java/com/juick/service/PostgresSearchService.java @@ -70,14 +70,10 @@ public class PostgresSearchService extends BaseJdbcService implements SearchServ FROM ( select - messages.message_id message_id, - messages.user_id user_id, - txt, + message_id, user_id, txt, to_tsvector('russian', txt) v, websearch_to_tsquery('russian', :query) q - from - messages_txt - inner join messages on messages.message_id = messages_txt.message_id + from messages ) t WHERE v @@ q diff --git a/src/main/resources/db/migration/V1.43__merge_messages_txt.sql b/src/main/resources/db/migration/V1.43__merge_messages_txt.sql new file mode 100644 index 00000000..fb43420d --- /dev/null +++ b/src/main/resources/db/migration/V1.43__merge_messages_txt.sql @@ -0,0 +1,8 @@ +ALTER TABLE messages ADD COLUMN txt TEXT; +ALTER TABLE messages ADD COLUMN repliesby TEXT; +ALTER TABLE messages ADD COLUMN updated_at timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP; +ALTER TABLE replies ALTER updated_at TYPE timestamptz USING updated_at AT TIME ZONE 'UTC'; +UPDATE messages +SET txt=m.txt, repliesby=m.repliesby, updated_at=m.updated_at +FROM (SELECT message_id id, txt, repliesby, updated_at AT TIME ZONE 'UTC' updated_at FROM messages_txt) m +WHERE message_id=m.id
\ No newline at end of file diff --git a/src/main/resources/schema-h2.sql b/src/main/resources/schema-h2.sql index 58ce9219..08a63839 100644 --- a/src/main/resources/schema-h2.sql +++ b/src/main/resources/schema-h2.sql @@ -121,13 +121,6 @@ CREATE MEMORY TABLE "PUBLIC"."REPLIES"( "HTML" TINYINT DEFAULT '0' NOT NULL ); CREATE INDEX "PUBLIC"."REPLY_URI_INDEX" ON "PUBLIC"."REPLIES"("REPLY_URI" NULLS FIRST); -CREATE MEMORY TABLE "PUBLIC"."MESSAGES_TXT"( - "MESSAGE_ID" INTEGER NOT NULL, - "REPLIESBY" CHARACTER VARYING(96) DEFAULT NULL, - "TXT" CHARACTER VARYING NOT NULL, - "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL -); -ALTER TABLE "PUBLIC"."MESSAGES_TXT" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_3E" PRIMARY KEY("MESSAGE_ID"); CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "MESSAGE_ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 1) DEFAULT ON NULL NOT NULL, "USER_ID" INTEGER NOT NULL, @@ -144,7 +137,10 @@ CREATE MEMORY TABLE "PUBLIC"."MESSAGES"( "POPULAR" TINYINT DEFAULT '0' NOT NULL, "HIDDEN" TINYINT DEFAULT '0' NOT NULL, "LIKES" SMALLINT DEFAULT '0' NOT NULL, - "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL + "UPDATED" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL, + "REPLIESBY" CHARACTER VARYING(96) DEFAULT NULL, + "TXT" CHARACTER VARYING NOT NULL, + "UPDATED_AT" TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL ); ALTER TABLE "PUBLIC"."MESSAGES" ADD CONSTRAINT "PUBLIC"."CONSTRAINT_13" PRIMARY KEY("MESSAGE_ID"); CREATE MEMORY TABLE "PUBLIC"."VK"( diff --git a/src/main/resources/schema-mysql.sql b/src/main/resources/schema-mysql.sql index 00261574..581b1c1c 100644 --- a/src/main/resources/schema-mysql.sql +++ b/src/main/resources/schema-mysql.sql @@ -204,6 +204,9 @@ CREATE TABLE `messages` ( `hidden` tinyint(3) unsigned NOT NULL DEFAULT 0, `likes` smallint(6) NOT NULL DEFAULT 0, `updated` timestamp(6) NOT NULL DEFAULT current_timestamp(6), + `repliesby` varchar(96) DEFAULT NULL, + `txt` mediumtext NOT NULL, + `updated_at` timestamp(6) NOT NULL DEFAULT current_timestamp(6), PRIMARY KEY (`message_id`), KEY `user_id` (`user_id`), KEY `ts` (`ts`), @@ -262,38 +265,6 @@ CREATE TABLE `messages_tags` ( /*!40101 SET character_set_client = @saved_cs_client */; -- --- Table structure for table `messages_txt` --- - -DROP TABLE IF EXISTS `messages_txt`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `messages_txt` ( - `message_id` int(10) unsigned NOT NULL, - `repliesby` varchar(96) DEFAULT NULL, - `txt` mediumtext NOT NULL, - `updated_at` timestamp(6) NOT NULL DEFAULT current_timestamp(6), - PRIMARY KEY (`message_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- --- Table structure for table `messenger` --- - -DROP TABLE IF EXISTS `messenger`; -/*!40101 SET @saved_cs_client = @@character_set_client */; -/*!40101 SET character_set_client = utf8 */; -CREATE TABLE `messenger` ( - `user_id` int(10) unsigned DEFAULT NULL, - `sender_id` bigint(20) NOT NULL, - `display_name` char(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, - `ts` timestamp NOT NULL DEFAULT current_timestamp(), - `loginhash` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; -/*!40101 SET character_set_client = @saved_cs_client */; - --- -- Table structure for table `oauth2_registered_client` -- diff --git a/src/main/resources/schema-sqlite.sql b/src/main/resources/schema-sqlite.sql index bffd38d2..ea9ffe36 100644 --- a/src/main/resources/schema-sqlite.sql +++ b/src/main/resources/schema-sqlite.sql @@ -99,6 +99,9 @@ CREATE TABLE messages ( hidden smallint DEFAULT (0) NOT NULL, likes smallint DEFAULT (0) NOT NULL, updated DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, + repliesby text, + txt text NOT NULL, + updated_at DEFAULT (strftime('%s','now') || substr(strftime('%f','now'),4)) NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE messages_access ( @@ -120,13 +123,6 @@ CREATE TABLE messages_tags ( 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 (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, diff --git a/src/main/resources/schema-sqlserver.sql b/src/main/resources/schema-sqlserver.sql index c28c318e..a8d04cdf 100644 --- a/src/main/resources/schema-sqlserver.sql +++ b/src/main/resources/schema-sqlserver.sql @@ -125,6 +125,9 @@ CREATE TABLE messages ( hidden smallint DEFAULT (0) NOT NULL, likes smallint DEFAULT (0) NOT NULL, updated datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, + repliesby varchar(max), + txt varchar(max) NOT NULL, + updated_at datetimeoffset DEFAULT CURRENT_TIMESTAMP NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ); CREATE TABLE messages_access ( @@ -146,13 +149,6 @@ CREATE TABLE messages_tags ( tag_id bigint NOT NULL, FOREIGN KEY (message_id) REFERENCES messages(message_id) ); -CREATE TABLE messages_txt ( - message_id INTEGER NOT NULL, - repliesby varchar(max), - txt varchar(max) NOT NULL, - updated_at datetimeoffset 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, diff --git a/src/test/java/com/juick/server/tests/ServerTests.java b/src/test/java/com/juick/server/tests/ServerTests.java index 414ad2ab..f297ee2e 100644 --- a/src/test/java/com/juick/server/tests/ServerTests.java +++ b/src/test/java/com/juick/server/tests/ServerTests.java @@ -1421,13 +1421,13 @@ public class ServerTests { is(true)); assertThat(messagesService.getReply(updatedComment.getMid(), updatedComment.getRid()).getUser(), is(archiveUser)); - jdbcTemplate.update("UPDATE messages_txt SET updated_at=? WHERE message_id=?", + jdbcTemplate.update("UPDATE messages SET updated_at=? WHERE message_id=?", Instant.now().minus(100, ChronoUnit.HOURS).atOffset(ZoneOffset.UTC), original.getMid()); assertThat(messagesService.deleteMessage(ugnich.getUid(), original.getMid()), is(true)); assertThat(messagesService.getMessage(original.getMid()).orElseThrow().getUser(), is(archiveUser)); - jdbcTemplate.update("UPDATE messages_txt SET updated_at=? WHERE message_id=?", + jdbcTemplate.update("UPDATE messages SET updated_at=? WHERE message_id=?", Instant.now().atOffset(ZoneOffset.UTC), original.getMid()); assertThat(messagesService.deleteMessage(ugnich.getUid(), original.getMid()), is(false)); |