messages_txt -> messages

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));