From 89f20b0ff36c8413adc2aa9e3c5226ae2eed2101 Mon Sep 17 00:00:00 2001
From: Vitaly Takmazov
Date: Fri, 3 Feb 2023 06:39:25 +0300
Subject: messages_txt -> messages

---
 .../db/migration/V1.43__merge_messages_txt.sql     |  8 +++++
 src/main/resources/schema-h2.sql                   | 12 +++-----
 src/main/resources/schema-mysql.sql                | 35 ++--------------------
 src/main/resources/schema-sqlite.sql               | 10 ++-----
 src/main/resources/schema-sqlserver.sql            | 10 ++-----
 5 files changed, 21 insertions(+), 54 deletions(-)
 create mode 100644 src/main/resources/db/migration/V1.43__merge_messages_txt.sql

(limited to 'src/main/resources')

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`),
@@ -261,38 +264,6 @@ CREATE TABLE `messages_tags` (
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
 /*!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,
-- 
cgit v1.2.3