aidar/SHiNE-server
SHA256
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity

Доделать связи и профиль

Browse Source
This commit is contained in:
AidarKC
2026-09-01 14:59:42 +04:00
parent a66d7a64e6
commit 390a2b988e
16 changed files with 599 additions and 304 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+16
View File
@@ -33,6 +33,7 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_14 = 14;
public static final int SCHEMA_VERSION_15 = 15;
public static final int SCHEMA_VERSION_16 = 16;
public static final int SCHEMA_VERSION_17 = 17;
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";
@@ -49,6 +50,7 @@ public final class DatabaseInitializer {
public static final String POSTGRES_MIGRATION_V14_RESOURCE = "postgres/migration_v14.sql";
public static final String POSTGRES_MIGRATION_V15_RESOURCE = "postgres/migration_v15.sql";
public static final String POSTGRES_MIGRATION_V16_RESOURCE = "postgres/migration_v16.sql";
public static final String POSTGRES_MIGRATION_V17_RESOURCE = "postgres/migration_v17.sql";
private DatabaseInitializer() {}
@@ -70,12 +72,17 @@ public final class DatabaseInitializer {
public static final short CONNECTION_CLOSE_FRIEND = 10;
public static final short CONNECTION_UNCLOSE_FRIEND = 11;
public static final short CONNECTION_FRIEND = 14;
public static final short CONNECTION_UNFRIEND = 15;
public static final short CONNECTION_CONTACT = 20;
public static final short CONNECTION_UNCONTACT = 21;
// Пока не используется в UI. DO NOT REMOVE.
public static final short CONNECTION_FOLLOW = 30;
public static final short CONNECTION_UNFOLLOW = 31;
// Родственные связи пока не используются в UI. DO NOT REMOVE.
public static final short CONNECTION_SPOUSE = 40;
public static final short CONNECTION_UNSPOUSE = 41;
@@ -88,15 +95,20 @@ public final class DatabaseInitializer {
public static final short CONNECTION_SIBLING = 54;
public static final short CONNECTION_UNSIBLING = 55;
// Legacy 60/61: новый UI не использует. DO NOT REMOVE.
public static final short CONNECTION_KNOWN_PERSON = 60;
public static final short CONNECTION_UNKNOWN_PERSON = 61;
public static final short CONNECTION_SHINE_CONFIRMED = 70;
public static final short CONNECTION_SHINE_UNCONFIRMED = 71;
// Пока не используется в UI. DO NOT REMOVE.
public static final short CONNECTION_SHINE_SEEN = 74;
public static final short CONNECTION_SHINE_UNSEEN = 75;
public static final short CONNECTION_OFFICIAL_ACCOUNT_CONFIRMED = 80;
public static final short CONNECTION_OFFICIAL_ACCOUNT_UNCONFIRMED = 81;
public static void ensurePostgresSchemaInitialized(String jdbcUrl,
String user,
String password) throws SQLException {
@@ -172,6 +184,10 @@ public final class DatabaseInitializer {
runSqlScript(conn, POSTGRES_MIGRATION_V16_RESOURCE);
currentVersion = SCHEMA_VERSION_16;
}
if (currentVersion < SCHEMA_VERSION_17) {
runSqlScript(conn, POSTGRES_MIGRATION_V17_RESOURCE);
currentVersion = SCHEMA_VERSION_17;
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/MsgSubType.java
+20 -7
View File
@@ -58,10 +58,10 @@ public final class MsgSubType {
/* ===================== CONNECTION (msg_type=3) ===================== */
/**
* Совпадает с ConnectionBody:
* SET: CLOSE_FRIEND=10, CONTACT=20, FOLLOW=30, SPOUSE=40, PARENT=50, CHILD=52, SIBLING=54,
* KNOWN_PERSON=60, SHINE_CONFIRMED=70, SHINE_SEEN=74
* UNSET: UNCLOSE_FRIEND=11, UNCONTACT=21, UNFOLLOW=31, UNSPOUSE=41, UNPARENT=51, UNCHILD=53, UNSIBLING=55,
* UNKNOWN_PERSON=61, SHINE_UNCONFIRMED=71, SHINE_UNSEEN=75
* SET: CLOSE_FRIEND=10, FRIEND=14, CONTACT=20, FOLLOW=30, SPOUSE=40, PARENT=50, CHILD=52, SIBLING=54,
* KNOWN_PERSON(legacy)=60, SHINE_CONFIRMED=70, SHINE_SEEN=74, OFFICIAL_ACCOUNT_CONFIRMED=80
* UNSET: UNCLOSE_FRIEND=11, UNFRIEND=15, UNCONTACT=21, UNFOLLOW=31, UNSPOUSE=41, UNPARENT=51, UNCHILD=53, UNSIBLING=55,
* UNKNOWN_PERSON(legacy)=61, SHINE_UNCONFIRMED=71, SHINE_UNSEEN=75, OFFICIAL_ACCOUNT_UNCONFIRMED=81
*/
/** Добавить в близкие друзья (close friend). */
@@ -70,18 +70,26 @@ public final class MsgSubType {
/** Удалить из близких друзей (close friend). */
public static final short CONNECTION_UNCLOSE_FRIEND = 11;
/** Добавить в друзья. */
public static final short CONNECTION_FRIEND = 14;
/** Удалить из друзей. */
public static final short CONNECTION_UNFRIEND = 15;
/** Добавить в контакты. */
public static final short CONNECTION_CONTACT = 20;
/** Удалить из контактов. */
public static final short CONNECTION_UNCONTACT = 21;
// Пока не используется в UI. Сохраняем для совместимости. DO NOT REMOVE.
/** Подписаться (follow). */
public static final short CONNECTION_FOLLOW = 30;
/** Отписаться (unfollow). */
public static final short CONNECTION_UNFOLLOW = 31;
// Родственные связи пока не используются в UI. Сохраняем коды. DO NOT REMOVE.
/** Добавить связь "жена/муж". */
public static final short CONNECTION_SPOUSE = 40;
@@ -106,10 +114,8 @@ public final class MsgSubType {
/** Удалить связь "брат/сестра". */
public static final short CONNECTION_UNSIBLING = 55;
/** Просто знаю этого человека. */
// Legacy 60/61: принимаем старые данные, но новый UI их не использует. DO NOT REMOVE.
public static final short CONNECTION_KNOWN_PERSON = 60;
/** Не знаю этого человека. */
public static final short CONNECTION_UNKNOWN_PERSON = 61;
/** Точно уверен, что сияющий. */
@@ -118,12 +124,19 @@ public final class MsgSubType {
/** Не подтверждаю, что сияющий. */
public static final short CONNECTION_SHINE_UNCONFIRMED = 71;
// Пока не используется в UI. Сохраняем для совместимости. DO NOT REMOVE.
/** Мало знаком, но видел сияющим. */
public static final short CONNECTION_SHINE_SEEN = 74;
/** Не отмечаю, что видел сияющим. */
public static final short CONNECTION_SHINE_UNSEEN = 75;
/** Подтверждение официального аккаунта человека. */
public static final short CONNECTION_OFFICIAL_ACCOUNT_CONFIRMED = 80;
/** Снятие подтверждения официального аккаунта человека. */
public static final short CONNECTION_OFFICIAL_ACCOUNT_UNCONFIRMED = 81;
/* ===================== USER_PARAM (msg_type=4) ===================== */
/** Параметр профиля key/value (обе строки). */
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v17.sql
+381
View File
@@ -0,0 +1,381 @@
BEGIN;
-- Add active FRIEND 14/15 and OFFICIAL_ACCOUNT confirmation 80/81 to connection state.
-- Legacy 60/61 remain accepted at protocol level; this migration does not reinterpret existing rows.
CREATE OR REPLACE FUNCTION shine_blocks_connection_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
resolved_login TEXT;
positive_rel_type INTEGER;
existed_before BOOLEAN;
target_channel_type INTEGER;
BEGIN
IF NEW.msg_type <> 3 THEN
RETURN NEW;
END IF;
resolved_login := shine_resolve_login(NEW.to_login, NEW.to_bch_name);
IF NEW.msg_sub_type IN (10, 14, 20, 30, 40, 50, 52, 54, 60, 70, 74, 80) THEN
IF resolved_login IS NULL OR NEW.to_bch_name IS NULL THEN
RETURN NEW;
END IF;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
IF NEW.msg_sub_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = user_stats_state.close_friends_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.msg_sub_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
1,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = user_stats_state.following_users_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = channel_stats_state.subscribers_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
INSERT INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
) VALUES (
NEW.login,
NEW.msg_sub_type,
resolved_login,
NEW.to_bch_name,
COALESCE(NEW.to_block_number, 0),
COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
);
RETURN NEW;
END IF;
positive_rel_type := CASE NEW.msg_sub_type
WHEN 11 THEN 10
WHEN 15 THEN 14
WHEN 21 THEN 20
WHEN 31 THEN 30
WHEN 41 THEN 40
WHEN 51 THEN 50
WHEN 53 THEN 52
WHEN 55 THEN 54
WHEN 61 THEN 60
WHEN 71 THEN 70
WHEN 75 THEN 74
WHEN 81 THEN 80
ELSE NULL
END;
IF positive_rel_type IS NULL OR resolved_login IS NULL THEN
RETURN NEW;
END IF;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
RETURN NEW;
END IF;
IF positive_rel_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = GREATEST(0, user_stats_state.close_friends_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF positive_rel_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = GREATEST(0, user_stats_state.following_users_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = GREATEST(0, channel_stats_state.subscribers_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
RETURN NEW;
END;
$$;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 17, 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
+3 -1
View File
@@ -1113,7 +1113,7 @@ BEGIN
resolved_login := shine_resolve_login(NEW.to_login, NEW.to_bch_name);
IF NEW.msg_sub_type IN (10, 20, 30, 40, 50, 52, 54, 60, 70, 74) THEN
IF NEW.msg_sub_type IN (10, 14, 20, 30, 40, 50, 52, 54, 60, 70, 74, 80) THEN
IF resolved_login IS NULL OR NEW.to_bch_name IS NULL THEN
RETURN NEW;
END IF;
@@ -1288,6 +1288,7 @@ BEGIN
positive_rel_type := CASE NEW.msg_sub_type
WHEN 11 THEN 10
WHEN 15 THEN 14
WHEN 21 THEN 20
WHEN 31 THEN 30
WHEN 41 THEN 40
@@ -1297,6 +1298,7 @@ BEGIN
WHEN 61 THEN 60
WHEN 71 THEN 70
WHEN 75 THEN 74
WHEN 81 THEN 80
ELSE NULL
END;