diff options
| author |  Vitaly Takmazov | 2022-12-03 15:28:06 +0300 |
|---|---|---|
| committer | Vitaly Takmazov | 2022-12-14 12:57:52 +0300 |
| commit | 0c0ea8897e64461b1cfa9cd86a939b48f0bdd640 (patch) | |
| tree | f11c73b8246b0724b26ca08529c3c702e1722d79 /src/main/resources/db/specific/postgresql | |
| parent | 8344ea3e3e652f5307ffb6115d6fdebf638ba098 (diff) | |
Initial PostgreSQL schema and profile
Diffstat (limited to 'src/main/resources/db/specific/postgresql')
15 files changed, 43 insertions, 0 deletions
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 |