From 0c0ea8897e64461b1cfa9cd86a939b48f0bdd640 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Sat, 3 Dec 2022 15:28:06 +0300 Subject: Initial PostgreSQL schema and profile --- .../db/specific/postgresql/V1.10__favorites_user_uri.sql | 3 +++ .../postgresql/V1.11__increase pm timestamp precision.sql | 1 + src/main/resources/db/specific/postgresql/V1.16__last seen.sql | 2 ++ ...18__increase messages and replies timestamp precision copy.sql | 3 +++ .../db/specific/postgresql/V1.19__messages_properties.sql | 6 ++++++ .../postgresql/V1.20__reply id in messages_properties.sql | 1 + .../V1.21__recreate messages_properties with correct index.sql | 8 ++++++++ .../specific/postgresql/V1.22__increase updated_at precision.sql | 1 + .../db/specific/postgresql/V1.2__Drop telegram_chats.sql | 3 +++ .../postgresql/V1.3__Nullable user_id column in auth table.sql | 1 + .../db/specific/postgresql/V1.4__ActivityPub followers.sql | 5 +++++ .../resources/db/specific/postgresql/V1.5__Drop acct index.sql | 6 ++++++ src/main/resources/db/specific/postgresql/V1.6__user_uri.sql | 1 + src/main/resources/db/specific/postgresql/V1.7__reply_uri.sql | 1 + src/main/resources/db/specific/postgresql/V1.8__html reply.sql | 1 + 15 files changed, 43 insertions(+) create mode 100644 src/main/resources/db/specific/postgresql/V1.10__favorites_user_uri.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.11__increase pm timestamp precision.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.16__last seen.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.18__increase messages and replies timestamp precision copy.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.19__messages_properties.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.20__reply id in messages_properties.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.21__recreate messages_properties with correct index.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.22__increase updated_at precision.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.2__Drop telegram_chats.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.3__Nullable user_id column in auth table.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.4__ActivityPub followers.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.5__Drop acct index.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.6__user_uri.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.7__reply_uri.sql create mode 100644 src/main/resources/db/specific/postgresql/V1.8__html reply.sql (limited to 'src/main/resources/db/specific/postgresql') diff --git a/src/main/resources/db/specific/postgresql/V1.10__favorites_user_uri.sql b/src/main/resources/db/specific/postgresql/V1.10__favorites_user_uri.sql new file mode 100644 index 00000000..90e512bc --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.10__favorites_user_uri.sql @@ -0,0 +1,3 @@ +ALTER TABLE favorites ADD COLUMN user_uri character varying (255) DEFAULT NULL; +UPDATE favorites SET user_uri='' WHERE user_uri IS NULL; +ALTER TABLE favorites ALTER COLUMN user_uri SET DEFAULT ''; \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.11__increase pm timestamp precision.sql b/src/main/resources/db/specific/postgresql/V1.11__increase pm timestamp precision.sql new file mode 100644 index 00000000..c5a8e528 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.11__increase pm timestamp precision.sql @@ -0,0 +1 @@ +-- changes are not needed \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.16__last seen.sql b/src/main/resources/db/specific/postgresql/V1.16__last seen.sql new file mode 100644 index 00000000..9543ca14 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.16__last seen.sql @@ -0,0 +1,2 @@ +ALTER TABLE users ADD COLUMN last_seen timestamp with time zone NULL; +UPDATE users SET last_seen=TO_TIMESTAMP(lastmessage::double precision / 1000); diff --git a/src/main/resources/db/specific/postgresql/V1.18__increase messages and replies timestamp precision copy.sql b/src/main/resources/db/specific/postgresql/V1.18__increase messages and replies timestamp precision copy.sql new file mode 100644 index 00000000..99b7f5d5 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.18__increase messages and replies timestamp precision copy.sql @@ -0,0 +1,3 @@ +ALTER TABLE users DROP COLUMN lastmessage; +ALTER TABLE users ADD COLUMN lastmessage timestamp with time zone; +UPDATE users SET lastmessage=last_seen; diff --git a/src/main/resources/db/specific/postgresql/V1.19__messages_properties.sql b/src/main/resources/db/specific/postgresql/V1.19__messages_properties.sql new file mode 100644 index 00000000..f3b72f66 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.19__messages_properties.sql @@ -0,0 +1,6 @@ +CREATE TABLE messages_properties ( + message_id bigint NOT NULL, + property_key character varying(255) NOT NULL, + property_value text NOT NULL, + CONSTRAINT message_key UNIQUE(message_id, property_key) +) diff --git a/src/main/resources/db/specific/postgresql/V1.20__reply id in messages_properties.sql b/src/main/resources/db/specific/postgresql/V1.20__reply id in messages_properties.sql new file mode 100644 index 00000000..53d4f169 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.20__reply id in messages_properties.sql @@ -0,0 +1 @@ +ALTER TABLE messages_properties ADD COLUMN reply_id smallint NOT NULL diff --git a/src/main/resources/db/specific/postgresql/V1.21__recreate messages_properties with correct index.sql b/src/main/resources/db/specific/postgresql/V1.21__recreate messages_properties with correct index.sql new file mode 100644 index 00000000..eadc6f56 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.21__recreate messages_properties with correct index.sql @@ -0,0 +1,8 @@ +DROP TABLE messages_properties; +CREATE TABLE messages_properties ( + message_id bigint NOT NULL, + reply_id smallint NOT NULL, + property_key character varying(255) NOT NULL, + property_value text NOT NULL, + CONSTRAINT message_properties_key UNIQUE(message_id, reply_id, property_key) +) diff --git a/src/main/resources/db/specific/postgresql/V1.22__increase updated_at precision.sql b/src/main/resources/db/specific/postgresql/V1.22__increase updated_at precision.sql new file mode 100644 index 00000000..dfb8628c --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.22__increase updated_at precision.sql @@ -0,0 +1 @@ +-- changes are not needed diff --git a/src/main/resources/db/specific/postgresql/V1.2__Drop telegram_chats.sql b/src/main/resources/db/specific/postgresql/V1.2__Drop telegram_chats.sql new file mode 100644 index 00000000..00e7fdbe --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.2__Drop telegram_chats.sql @@ -0,0 +1,3 @@ +CREATE EXTENSION IF NOT EXISTS "pgcrypto"; +INSERT INTO telegram(tg_id, tg_name, loginhash) SELECT chat_id AS tg_id, 'Anonymous', gen_random_uuid() FROM telegram_chats; +DROP TABLE telegram_chats; \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.3__Nullable user_id column in auth table.sql b/src/main/resources/db/specific/postgresql/V1.3__Nullable user_id column in auth table.sql new file mode 100644 index 00000000..eea8c2a6 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.3__Nullable user_id column in auth table.sql @@ -0,0 +1 @@ +ALTER TABLE auth ALTER COLUMN user_id DROP NOT NULL; \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.4__ActivityPub followers.sql b/src/main/resources/db/specific/postgresql/V1.4__ActivityPub followers.sql new file mode 100644 index 00000000..fd75b87c --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.4__ActivityPub followers.sql @@ -0,0 +1,5 @@ +CREATE TABLE IF NOT EXISTS followers ( + user_id bigint REFERENCES users(id), + acct character varying(64) NOT NULL UNIQUE, + ts timestamp with time zone DEFAULT now() NOT NULL +); \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.5__Drop acct index.sql b/src/main/resources/db/specific/postgresql/V1.5__Drop acct index.sql new file mode 100644 index 00000000..d4f1c6ef --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.5__Drop acct index.sql @@ -0,0 +1,6 @@ +ALTER TABLE followers ADD COLUMN acct_migr character varying(64) NOT NULL; +UPDATE followers SET acct_migr = acct; +ALTER TABLE followers DROP COLUMN acct; +ALTER TABLE followers ADD COLUMN acct character varying(64) NOT NULL; +UPDATE followers SET acct = acct_migr; +ALTER TABLE followers DROP COLUMN acct_migr; \ No newline at end of file diff --git a/src/main/resources/db/specific/postgresql/V1.6__user_uri.sql b/src/main/resources/db/specific/postgresql/V1.6__user_uri.sql new file mode 100644 index 00000000..c067810a --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.6__user_uri.sql @@ -0,0 +1 @@ +ALTER TABLE replies ADD COLUMN user_uri character varying(255) DEFAULT NULL; diff --git a/src/main/resources/db/specific/postgresql/V1.7__reply_uri.sql b/src/main/resources/db/specific/postgresql/V1.7__reply_uri.sql new file mode 100644 index 00000000..09dcfb56 --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.7__reply_uri.sql @@ -0,0 +1 @@ +ALTER TABLE replies ADD COLUMN reply_uri character varying(255) DEFAULT NULL; diff --git a/src/main/resources/db/specific/postgresql/V1.8__html reply.sql b/src/main/resources/db/specific/postgresql/V1.8__html reply.sql new file mode 100644 index 00000000..88bcc04c --- /dev/null +++ b/src/main/resources/db/specific/postgresql/V1.8__html reply.sql @@ -0,0 +1 @@ +ALTER TABLE replies ADD COLUMN html smallint NOT NULL DEFAULT '0'; \ No newline at end of file -- cgit v1.2.3