Files
SHiNE-server/SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v12.sql
T

140 lines
5.6 KiB
PL/PgSQL

BEGIN;
CREATE TABLE IF NOT EXISTS dm_delivery_state (
outgoing_message_key TEXT PRIMARY KEY,
event_id TEXT NOT NULL UNIQUE,
base_key TEXT NOT NULL,
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
incoming_message_key TEXT NOT NULL,
created_at_ms BIGINT NOT NULL,
delivery_expires_at_ms BIGINT NOT NULL,
delivery_state INTEGER NOT NULL DEFAULT 0 CHECK (delivery_state IN (0, 1, 2, 3)),
delivered_server_login TEXT,
recipient_routes_hash TEXT,
attempt_index INTEGER NOT NULL DEFAULT 0,
next_attempt_at_ms BIGINT,
last_attempt_at_ms BIGINT,
last_error TEXT,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_dm_delivery_state_due
ON dm_delivery_state(next_attempt_at_ms, delivery_state)
WHERE delivery_state = 0 AND next_attempt_at_ms IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_dm_delivery_state_base
ON dm_delivery_state(base_key, from_login);
CREATE TABLE IF NOT EXISTS dm_sync_outbox (
owner_login TEXT NOT NULL,
primary_message_key TEXT NOT NULL,
event_id TEXT NOT NULL,
secondary_message_key TEXT,
synced BOOLEAN NOT NULL DEFAULT FALSE,
created_at_ms BIGINT NOT NULL,
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_login, primary_message_key)
);
CREATE INDEX IF NOT EXISTS idx_dm_sync_outbox_unsynced
ON dm_sync_outbox(owner_login, created_at_ms, primary_message_key)
WHERE synced = FALSE;
-- До v12 факт межсерверной доставки не сохранялся. Старые исходящие пары
-- считаем уже доставленными, чтобы обновление не вызвало повторную рассылку.
INSERT INTO dm_delivery_state (
outgoing_message_key, event_id, base_key, from_login, to_login,
incoming_message_key, created_at_ms, delivery_expires_at_ms,
delivery_state, delivered_server_login, recipient_routes_hash,
attempt_index, next_attempt_at_ms, last_attempt_at_ms, last_error, updated_at_ms
)
SELECT
outgoing.message_key,
outgoing.message_key || ':' || outgoing.revision_time_ms || ':' || outgoing.reencrypted_at_ms,
outgoing.base_key,
outgoing.from_login,
outgoing.to_login,
incoming.message_key,
outgoing.created_at_ms,
outgoing.created_at_ms + 3600000,
2,
NULL,
NULL,
9,
NULL,
NULL,
'MIGRATED_AS_DELIVERED',
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM signed_messages outgoing
JOIN signed_messages incoming
ON incoming.base_key = outgoing.base_key
AND incoming.message_type = outgoing.message_type - 1
WHERE outgoing.message_type IN (2, 4)
ON CONFLICT (outgoing_message_key) DO NOTHING;
-- Историю до v12 считаем подтверждённой, иначе само обновление вызовет
-- массовую повторную передачу. При замене сервера общий reset вернёт FALSE.
INSERT INTO dm_sync_outbox (
owner_login, primary_message_key, event_id, secondary_message_key,
synced, created_at_ms, updated_at_ms
)
SELECT
LOWER(outgoing.from_login),
outgoing.message_key,
outgoing.message_key || ':' || outgoing.revision_time_ms || ':' || outgoing.reencrypted_at_ms,
incoming.message_key,
TRUE,
outgoing.created_at_ms,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM signed_messages outgoing
JOIN signed_messages incoming
ON incoming.base_key = outgoing.base_key
AND incoming.message_type = outgoing.message_type - 1
WHERE outgoing.message_type IN (2, 4)
ON CONFLICT (owner_login, primary_message_key) DO NOTHING;
-- Входящая копия синхронизируется отдельно между серверами получателя.
INSERT INTO dm_sync_outbox (
owner_login, primary_message_key, event_id, secondary_message_key,
synced, created_at_ms, updated_at_ms
)
SELECT
LOWER(incoming.to_login),
incoming.message_key,
incoming.message_key || ':' || incoming.revision_time_ms || ':' || incoming.reencrypted_at_ms,
NULL,
TRUE,
incoming.created_at_ms,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM signed_messages incoming
WHERE incoming.message_type IN (1, 3)
ON CONFLICT (owner_login, primary_message_key) DO NOTHING;
-- Tombstone принадлежит обоим участникам и должен попасть на второй сервер
-- каждого из них. Для self-DM конфликт безопасно схлопывается.
INSERT INTO dm_sync_outbox (
owner_login, primary_message_key, event_id, secondary_message_key,
synced, created_at_ms, updated_at_ms
)
SELECT
LOWER(owner_login),
tombstone.message_key,
tombstone.message_key || ':' || tombstone.revision_time_ms || ':' || tombstone.reencrypted_at_ms,
NULL,
TRUE,
tombstone.created_at_ms,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM signed_messages tombstone
CROSS JOIN LATERAL (VALUES (tombstone.from_login), (tombstone.to_login)) owners(owner_login)
WHERE tombstone.message_type IN (5, 6, 7, 8)
ON CONFLICT (owner_login, primary_message_key) DO NOTHING;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 12, 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;