Унифицировать платный статус регистрации

This commit is contained in:
AidarKC
2026-10-28 11:28:59 +03:00
parent a22f590395
commit ce97171d5a
12 changed files with 183 additions and 35 deletions
@@ -25,11 +25,13 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_32 = 32;
public static final int SCHEMA_VERSION_33 = 33;
public static final int SCHEMA_VERSION_34 = 34;
public static final int SCHEMA_VERSION_35 = 35;
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
public static final String POSTGRES_MIGRATION_V31_RESOURCE = "postgres/migration_v31.sql";
public static final String POSTGRES_MIGRATION_V32_RESOURCE = "postgres/migration_v32.sql";
public static final String POSTGRES_MIGRATION_V33_RESOURCE = "postgres/migration_v33.sql";
public static final String POSTGRES_MIGRATION_V34_RESOURCE = "postgres/migration_v34.sql";
public static final String POSTGRES_MIGRATION_V35_RESOURCE = "postgres/migration_v35.sql";
private DatabaseInitializer() {}
@@ -137,9 +139,13 @@ public final class DatabaseInitializer {
runSqlScript(conn, POSTGRES_MIGRATION_V34_RESOURCE);
currentVersion = readCurrentSchemaVersion(conn);
}
if (currentVersion != SCHEMA_VERSION_34) {
if (currentVersion == SCHEMA_VERSION_34) {
runSqlScript(conn, POSTGRES_MIGRATION_V35_RESOURCE);
currentVersion = readCurrentSchemaVersion(conn);
}
if (currentVersion != SCHEMA_VERSION_35) {
throw new SQLException(
"Unsupported SHiNE DB v2 schema version: expected 34, found " + currentVersion);
"Unsupported SHiNE DB v2 schema version: expected 35, found " + currentVersion);
}
}
}
@@ -0,0 +1,62 @@
-- v35: один полноценный статус для всех зарегистрированных в Solana.
-- Старые регистрационные сертификаты Arweave и дата исходной регистрации сохраняются.
BEGIN;
-- Сначала снимаем старое ограничение, затем переводим существующие строки.
ALTER TABLE user_identity_current
DROP CONSTRAINT user_identity_current_registration_status_check;
UPDATE user_identity_current
SET registration_status = 'PAID'
WHERE registration_status IN ('PAID_DIRECT', 'PAID_UPGRADED');
ALTER TABLE user_identity_current
ADD CONSTRAINT user_identity_current_registration_status_check
CHECK (registration_status IN ('FREE_CERT', 'PAID'));
-- Важнее миграции существующих строк: при следующих Solana sync/upsert
-- и обычный платный, и ранее бесплатный пользователь получают тот же PAID.
CREATE OR REPLACE FUNCTION shine_sync_identity_from_solana()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE old_user user_identity_current%ROWTYPE;
BEGIN
-- Если PostgreSQL получил старую запись из replay, она не должна затереть
-- уже принятую более новую логическую идентичность в BEFORE INSERT.
IF TG_OP = 'INSERT' AND EXISTS (
SELECT 1 FROM solana_user_pda_current prior
WHERE prior.pda_address = NEW.pda_address AND prior.record_number > NEW.record_number
) THEN
RETURN NEW;
END IF;
SELECT * INTO old_user FROM user_identity_current
WHERE normalized_login = NEW.normalized_login FOR UPDATE;
IF FOUND AND old_user.registration_status = 'FREE_CERT' AND
(old_user.root_key <> NEW.root_key OR old_user.client_key <> NEW.client_key OR old_user.blockchain_key <> NEW.blockchain_key) THEN
RAISE EXCEPTION 'Free certificate keys differ from Solana upgrade for %', NEW.login;
END IF;
IF FOUND AND old_user.solana_pda_address IS NOT NULL AND old_user.solana_pda_address <> NEW.pda_address THEN
RAISE EXCEPTION 'Duplicate on-chain identity for %', NEW.login;
END IF;
INSERT INTO user_identity_current (
login, normalized_login, namespace, registration_source, registration_status,
solana_pda_address, free_data_item_id, root_key, client_key, blockchain_key,
blockchain_name, is_server, registered_at_ms, updated_at_ms)
VALUES (NEW.login, NEW.normalized_login,
CASE WHEN position('@' in NEW.login) > 0 THEN split_part(NEW.login,'@',2) ELSE NULL END,
'SOLANA', 'PAID',
NEW.pda_address, old_user.free_data_item_id, NEW.root_key, NEW.client_key,
NEW.blockchain_key, NEW.blockchain_name, NEW.is_server,
COALESCE(old_user.registered_at_ms, NEW.created_at_ms), NEW.updated_at_ms)
ON CONFLICT (login) DO UPDATE SET
registration_source='SOLANA',
registration_status=EXCLUDED.registration_status,
solana_pda_address=EXCLUDED.solana_pda_address,
root_key=EXCLUDED.root_key, client_key=EXCLUDED.client_key,
blockchain_key=EXCLUDED.blockchain_key, blockchain_name=EXCLUDED.blockchain_name,
is_server=EXCLUDED.is_server, updated_at_ms=EXCLUDED.updated_at_ms;
RETURN NEW;
END $$;
UPDATE db_schema_version
SET schema_version=35,
updated_at_ms=CAST(EXTRACT(EPOCH FROM clock_timestamp())*1000 AS BIGINT)
WHERE id=1;
COMMIT;