Split DM dialog migration to v11

SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseInitializer.java

@@ -27,6 +27,7 @@ public final class DatabaseInitializer {
     public static final int SCHEMA_VERSION_8 = 8;
     public static final int SCHEMA_VERSION_9 = 9;
     public static final int SCHEMA_VERSION_10 = 10;
+    public static final int SCHEMA_VERSION_11 = 11;
     public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
     public static final String POSTGRES_MIGRATION_V2_RESOURCE = "postgres/migration_v2.sql";
     public static final String POSTGRES_MIGRATION_V3_RESOURCE = "postgres/migration_v3.sql";
@@ -37,6 +38,7 @@ public final class DatabaseInitializer {
     public static final String POSTGRES_MIGRATION_V8_RESOURCE = "postgres/migration_v8.sql";
     public static final String POSTGRES_MIGRATION_V9_RESOURCE = "postgres/migration_v9.sql";
     public static final String POSTGRES_MIGRATION_V10_RESOURCE = "postgres/migration_v10.sql";
+    public static final String POSTGRES_MIGRATION_V11_RESOURCE = "postgres/migration_v11.sql";
 
     private DatabaseInitializer() {}
 
@@ -136,6 +138,10 @@ public final class DatabaseInitializer {
             }
             if (currentVersion < SCHEMA_VERSION_10) {
                 runSqlScript(conn, POSTGRES_MIGRATION_V10_RESOURCE);
+                currentVersion = SCHEMA_VERSION_10;
+            }
+            if (currentVersion < SCHEMA_VERSION_11) {
+                runSqlScript(conn, POSTGRES_MIGRATION_V11_RESOURCE);
             }
         }
     }

SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v10.sql

@@ -1,25 +1,27 @@
 BEGIN;
 
-CREATE TABLE IF NOT EXISTS dm_dialog_state (
+CREATE TABLE IF NOT EXISTS user_notifications_state (
-    owner_login                   TEXT NOT NULL,
+    owner_login           TEXT NOT NULL REFERENCES solana_user_pda_current(normalized_login),
-    peer_login                    TEXT NOT NULL,
+    notification_kind     TEXT NOT NULL CHECK (notification_kind IN ('reply', 'connection')),
-    relation_flag                 TEXT NOT NULL DEFAULT 'none',
+    created_at_ms         BIGINT NOT NULL,
-    last_message_blob_b64         TEXT NOT NULL DEFAULT '',
+    source_login          TEXT NOT NULL,
-    last_message_key              TEXT NOT NULL DEFAULT '',
+    source_bch_name       TEXT NOT NULL,
-    last_message_type             INTEGER NOT NULL DEFAULT 0,
+    source_block_number   INTEGER NOT NULL CHECK (source_block_number >= 0),
-    last_message_time_ms          BIGINT NOT NULL DEFAULT 0,
+    source_block_hash     BYTEA NOT NULL,
-    last_message_revision_ms      BIGINT NOT NULL DEFAULT 0,
+    target_login          TEXT,
-    unread_count                  INTEGER NOT NULL DEFAULT 0,
+    target_bch_name       TEXT,
-    last_read_receipt_time_ms     BIGINT NOT NULL DEFAULT 0,
+    target_block_number   INTEGER,
-    updated_at_ms                 BIGINT NOT NULL,
+    target_block_hash     BYTEA,
-    PRIMARY KEY (owner_login, peer_login)
+    source_msg_sub_type   INTEGER NOT NULL,
+    source_text           TEXT NOT NULL DEFAULT '',
+    UNIQUE (owner_login, notification_kind, source_bch_name, source_block_number, source_block_hash)
 );
 
-ALTER TABLE IF EXISTS dm_dialog_state
+CREATE INDEX IF NOT EXISTS idx_user_notifications_state_owner_kind_time
-    ADD COLUMN IF NOT EXISTS last_message_blob_b64 TEXT NOT NULL DEFAULT '';
+    ON user_notifications_state(owner_login, notification_kind, created_at_ms DESC, source_block_number DESC);
 
-CREATE INDEX IF NOT EXISTS idx_dm_dialog_state_owner_last_time
+CREATE INDEX IF NOT EXISTS idx_user_notifications_state_owner_time
-    ON dm_dialog_state(owner_login, last_message_time_ms DESC, peer_login);
+    ON user_notifications_state(owner_login, created_at_ms DESC);
 
 INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
 VALUES (1, 10, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))

SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v11.sql

@@ -0,0 +1,30 @@
+BEGIN;
+
+CREATE TABLE IF NOT EXISTS dm_dialog_state (
+    owner_login                   TEXT NOT NULL,
+    peer_login                    TEXT NOT NULL,
+    relation_flag                 TEXT NOT NULL DEFAULT 'none',
+    last_message_blob_b64         TEXT NOT NULL DEFAULT '',
+    last_message_key              TEXT NOT NULL DEFAULT '',
+    last_message_type             INTEGER NOT NULL DEFAULT 0,
+    last_message_time_ms          BIGINT NOT NULL DEFAULT 0,
+    last_message_revision_ms      BIGINT NOT NULL DEFAULT 0,
+    unread_count                  INTEGER NOT NULL DEFAULT 0,
+    last_read_receipt_time_ms     BIGINT NOT NULL DEFAULT 0,
+    updated_at_ms                 BIGINT NOT NULL,
+    PRIMARY KEY (owner_login, peer_login)
+);
+
+ALTER TABLE IF EXISTS dm_dialog_state
+    ADD COLUMN IF NOT EXISTS last_message_blob_b64 TEXT NOT NULL DEFAULT '';
+
+CREATE INDEX IF NOT EXISTS idx_dm_dialog_state_owner_last_time
+    ON dm_dialog_state(owner_login, last_message_time_ms DESC, peer_login);
+
+INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
+VALUES (1, 11, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
+ON CONFLICT (id) DO UPDATE SET
+    schema_version = EXCLUDED.schema_version,
+    updated_at_ms = EXCLUDED.updated_at_ms;
+
+COMMIT;

SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql

@@ -22,7 +22,7 @@ CREATE TABLE IF NOT EXISTS db_schema_version (
 );
 
 INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
-    VALUES (1, 9, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
+    VALUES (1, 11, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
 ON CONFLICT (id) DO UPDATE SET
     schema_version = EXCLUDED.schema_version,
     updated_at_ms = EXCLUDED.updated_at_ms;
@@ -751,6 +751,29 @@ CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
 CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
     ON signed_message_session_delivery(session_id, delivered);
 
+CREATE TABLE IF NOT EXISTS user_notifications_state (
+    owner_login           TEXT NOT NULL REFERENCES solana_user_pda_current(normalized_login),
+    notification_kind     TEXT NOT NULL CHECK (notification_kind IN ('reply', 'connection')),
+    created_at_ms         BIGINT NOT NULL,
+    source_login          TEXT NOT NULL,
+    source_bch_name       TEXT NOT NULL,
+    source_block_number   INTEGER NOT NULL CHECK (source_block_number >= 0),
+    source_block_hash     BYTEA NOT NULL,
+    target_login          TEXT,
+    target_bch_name       TEXT,
+    target_block_number   INTEGER,
+    target_block_hash     BYTEA,
+    source_msg_sub_type   INTEGER NOT NULL,
+    source_text           TEXT NOT NULL DEFAULT '',
+    UNIQUE (owner_login, notification_kind, source_bch_name, source_block_number, source_block_hash)
+);
+
+CREATE INDEX IF NOT EXISTS idx_user_notifications_state_owner_kind_time
+    ON user_notifications_state(owner_login, notification_kind, created_at_ms DESC, source_block_number DESC);
+
+CREATE INDEX IF NOT EXISTS idx_user_notifications_state_owner_time
+    ON user_notifications_state(owner_login, created_at_ms DESC);
+
 CREATE TABLE IF NOT EXISTS dm_dialog_state (
     owner_login                   TEXT NOT NULL,
     peer_login                    TEXT NOT NULL,