aboutsummaryrefslogtreecommitdiff
path: root/src/main/resources
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-02-03 06:39:25 +0300
committerGravatar Vitaly Takmazov2023-02-03 06:39:25 +0300
commit89f20b0ff36c8413adc2aa9e3c5226ae2eed2101 (patch)
treefe56fa9f8de597ff313c334b69ffafe5adf0b95d /src/main/resources
parent73b8c2dc544df207d8c7b9e36d20955ff1d88577 (diff)
messages_txt -> messages
Diffstat (limited to 'src/main/resources')
-rw-r--r--src/main/resources/db/migration/V1.43__merge_messages_txt.sql8
-rw-r--r--src/main/resources/schema-h2.sql12
-rw-r--r--src/main/resources/schema-mysql.sql35
-rw-r--r--src/main/resources/schema-sqlite.sql10
-rw-r--r--src/main/resources/schema-sqlserver.sql10
5 files changed, 21 insertions, 54 deletions
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,