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 18:14:55 +04:00
parent 632d56737b
commit c03d94105c
42 changed files with 1555 additions and 414 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
+13 -1
View File
@@ -34,6 +34,8 @@ public final class DatabaseInitializer {
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 int SCHEMA_VERSION_18 = 18;
public static final int SCHEMA_VERSION_19 = 19;
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";
@@ -51,6 +53,8 @@ public final class DatabaseInitializer {
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";
public static final String POSTGRES_MIGRATION_V18_RESOURCE = "postgres/migration_v18.sql";
public static final String POSTGRES_MIGRATION_V19_RESOURCE = "postgres/migration_v19.sql";
private DatabaseInitializer() {}
@@ -78,7 +82,7 @@ public final class DatabaseInitializer {
public static final short CONNECTION_CONTACT = 20;
public static final short CONNECTION_UNCONTACT = 21;
// Пока не используется в UI. DO NOT REMOVE.
// FOLLOW на публичный канал используется; user-to-user FOLLOW legacy. DO NOT REMOVE.
public static final short CONNECTION_FOLLOW = 30;
public static final short CONNECTION_UNFOLLOW = 31;
@@ -188,6 +192,14 @@ public final class DatabaseInitializer {
runSqlScript(conn, POSTGRES_MIGRATION_V17_RESOURCE);
currentVersion = SCHEMA_VERSION_17;
}
if (currentVersion < SCHEMA_VERSION_18) {
runSqlScript(conn, POSTGRES_MIGRATION_V18_RESOURCE);
currentVersion = SCHEMA_VERSION_18;
}
if (currentVersion < SCHEMA_VERSION_19) {
runSqlScript(conn, POSTGRES_MIGRATION_V19_RESOURCE);
currentVersion = SCHEMA_VERSION_19;
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/MsgSubType.java
+1 -1
View File
@@ -82,7 +82,7 @@ public final class MsgSubType {
/** Удалить из контактов. */
public static final short CONNECTION_UNCONTACT = 21;
// Пока не используется в UI. Сохраняем для совместимости. DO NOT REMOVE.
// FOLLOW на публичный канал используется. Старый FOLLOW пользователя считается legacy. DO NOT REMOVE.
/** Подписаться (follow). */
public static final short CONNECTION_FOLLOW = 30;
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainResyncCleanupDAO.java
+19
View File
@@ -426,6 +426,25 @@ public final class BlockchainResyncCleanupDAO {
ps.executeUpdate();
}
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_user_profile(login) FROM solana_user_pda_current")) {
ps.execute();
}
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_user_stats(login) FROM solana_user_pda_current")) {
ps.execute();
}
try (PreparedStatement ps = c.prepareStatement("""
UPDATE message_stats ms SET
primary_likes_count=(SELECT COUNT(*)::INTEGER FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login)),
shining_likes_count=(SELECT COUNT(*)::INTEGER FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login) AND shine_is_shining(rs.from_login))
""")) {
ps.executeUpdate();
}
try (PreparedStatement ps = c.prepareStatement("""
INSERT INTO channel_stats_state (
owner_bch_name,
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/DmDialogStateDAO.java
+7
View File
@@ -64,6 +64,7 @@ public final class DmDialogStateDAO {
Map<String, DialogSummary> byPeer = new LinkedHashMap<>();
List<String> contacts = ConnectionsStateDAO.getInstance().listOutgoingByRelTypeCanonical(c, cleanOwner, MsgSubType.CONNECTION_CONTACT);
List<String> friends = ConnectionsStateDAO.getInstance().listOutgoingByRelTypeCanonical(c, cleanOwner, MsgSubType.CONNECTION_FRIEND);
List<String> closeFriends = ConnectionsStateDAO.getInstance().listOutgoingByRelTypeCanonical(c, cleanOwner, MsgSubType.CONNECTION_CLOSE_FRIEND);
try (PreparedStatement ps = c.prepareStatement("""
@@ -95,6 +96,9 @@ public final class DmDialogStateDAO {
for (String peer : contacts) {
addOrUpdateSummary(byPeer, cleanOwner, peer, "contact", false);
}
for (String peer : friends) {
addOrUpdateSummary(byPeer, cleanOwner, peer, "friend", false);
}
for (String peer : closeFriends) {
addOrUpdateSummary(byPeer, cleanOwner, peer, "close_friend", false);
}
@@ -286,6 +290,9 @@ public final class DmDialogStateDAO {
if (ConnectionsStateDAO.getInstance().hasOutgoingByRelTypeCanonical(c, ownerLogin, peerLogin, MsgSubType.CONNECTION_CLOSE_FRIEND)) {
return "close_friend";
}
if (ConnectionsStateDAO.getInstance().hasOutgoingByRelTypeCanonical(c, ownerLogin, peerLogin, MsgSubType.CONNECTION_FRIEND)) {
return "friend";
}
if (ConnectionsStateDAO.getInstance().hasOutgoingByRelTypeCanonical(c, ownerLogin, peerLogin, MsgSubType.CONNECTION_CONTACT)) {
return "contact";
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserParamsDAO.java
+32 -1
View File
@@ -55,6 +55,7 @@ public final class UserParamsDAO {
WHERE users_params.time_ms < excluded.time_ms
""";
int changed;
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, e.getLogin());
ps.setString(2, e.getParam());
@@ -67,8 +68,12 @@ public final class UserParamsDAO {
if (e.getSignature() != null) ps.setString(6, e.getSignature());
else ps.setNull(6, Types.VARCHAR);
return ps.executeUpdate();
changed = ps.executeUpdate();
}
if (changed > 0 && affectsFastProfile(e.getParam())) {
refreshFastProfileAndDerivedState(c, e.getLogin(), e.getParam());
}
return changed;
}
public int upsertIfNewer(UserParamEntry e) throws SQLException {
@@ -77,6 +82,32 @@ public final class UserParamsDAO {
}
}
private static boolean affectsFastProfile(String param) {
if (param == null) return false;
return switch (param) {
case "first_name", "last_name", "ava", "account_role", "shine" -> true;
default -> false;
};
}
private static void refreshFastProfileAndDerivedState(Connection c, String login, String param) throws SQLException {
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_user_profile(?)")) {
ps.setString(1, login);
ps.execute();
}
if ("account_role".equals(param) || "shine".equals(param)) {
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_related_user_stats(?)")) {
ps.setString(1, login);
ps.execute();
}
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_message_qualified_likes_for_actor(?)")) {
ps.setString(1, login);
ps.execute();
}
}
}
// -------------------- SELECT --------------------
public UserParamEntry getByLoginAndParam(Connection c, String login, String param) throws SQLException {
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserProfileStateDAO.java
+117
View File
@@ -0,0 +1,117 @@
package shine.db.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/** Fast denormalized projection for user cards shown in dialogs/relations/profile lists. */
public final class UserProfileStateDAO {
private static volatile UserProfileStateDAO instance;
private UserProfileStateDAO() {}
public static UserProfileStateDAO getInstance() {
if (instance == null) synchronized (UserProfileStateDAO.class) {
if (instance == null) instance = new UserProfileStateDAO();
}
return instance;
}
public ProfileCard get(Connection c, String login) throws SQLException {
try (PreparedStatement ps = c.prepareStatement("""
SELECT login, first_name, last_name, ava_ar, account_role, shine_status
FROM user_profile_state WHERE LOWER(login)=LOWER(?) LIMIT 1
""")) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return new ProfileCard(login, "", "", "", null, null);
return map(rs);
}
}
}
public List<RelationCard> listRelations(Connection c, String ownerLogin, String listType, int limit, int offset) throws SQLException {
int safeLimit = Math.max(1, Math.min(limit <= 0 ? 100 : limit, 500));
int safeOffset = Math.max(0, offset);
String where = switch (String.valueOf(listType)) {
case "friends" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=14 AND NOT EXISTS (SELECT 1 FROM connections_state cf WHERE LOWER(cf.login)=LOWER(cs.login) AND LOWER(cf.to_login)=LOWER(cs.to_login) AND cf.rel_type=10)";
case "close_friends" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=10";
case "primary_received" -> "LOWER(cs.to_login)=LOWER(?) AND cs.rel_type=80 AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(cs.to_login)";
case "primary_given" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=80 AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(cs.to_login)";
case "shine_received" -> "LOWER(cs.to_login)=LOWER(?) AND cs.rel_type=70 AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(cs.to_login)";
case "shine_given" -> "LOWER(cs.login)=LOWER(?) AND cs.rel_type=70 AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(cs.to_login)";
default -> throw new IllegalArgumentException("Unsupported listType: " + listType);
};
boolean received = "primary_received".equals(listType) || "shine_received".equals(listType);
String personExpr = received ? "cs.login" : "cs.to_login";
String sql = """
SELECT DISTINCT ON (LOWER(%s))
%s AS person_login,
COALESCE(up.first_name,'') AS first_name,
COALESCE(up.last_name,'') AS last_name,
COALESCE(up.ava_ar,'') AS ava_ar,
up.account_role, up.shine_status,
EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=80
AND shine_is_primary(x.login) AND shine_target_accepts_primary_confirmation(x.to_login)) AS primary_confirmed,
EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=70
AND shine_is_primary(x.login) AND shine_is_shining(x.login) AND shine_target_accepts_shine_confirmation(x.to_login)) AS shine_confirmed,
CASE
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=10) THEN 'close_friend'
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=14) THEN 'friend'
WHEN EXISTS(SELECT 1 FROM connections_state x WHERE LOWER(x.login)=LOWER(?) AND LOWER(x.to_login)=LOWER(%s) AND x.rel_type=20) THEN 'contact'
ELSE 'none' END AS relation_type,
COALESCE(us.primary_confirmations_received_count,0) AS primary_confirmations_count,
COALESCE(us.shine_confirmations_received_count,0) AS shine_confirmations_count
FROM connections_state cs
LEFT JOIN user_profile_state up ON LOWER(up.login)=LOWER(%s)
LEFT JOIN user_stats_state us ON LOWER(us.login)=LOWER(%s)
WHERE %s
ORDER BY LOWER(%s), %s
LIMIT ? OFFSET ?
""".formatted(personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, personExpr, where, personExpr, personExpr);
List<RelationCard> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i=1;
ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin); ps.setString(i++, ownerLogin);
ps.setString(i++, ownerLogin);
ps.setInt(i++, safeLimit); ps.setInt(i, safeOffset);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(new RelationCard(
rs.getString("person_login"), rs.getString("first_name"), rs.getString("last_name"), rs.getString("ava_ar"),
rs.getString("account_role"), rs.getString("shine_status"), rs.getBoolean("primary_confirmed"), rs.getBoolean("shine_confirmed"),
rs.getString("relation_type"), rs.getInt("primary_confirmations_count"), rs.getInt("shine_confirmations_count")
));
}
}
return out;
}
public List<ChannelCard> listPublicChannels(Connection c, String login, String mode, int limit, int offset) throws SQLException {
int safeLimit=Math.max(1,Math.min(limit<=0?100:limit,500)), safeOffset=Math.max(0,offset);
String sql;
if ("owned".equals(mode)) {
sql="""
SELECT cn.owner_login, cn.slug, cn.display_name, cn.ava_ar, cn.owner_bch_name, cn.channel_root_block_number, encode(cn.channel_root_block_hash,'hex') root_hash
FROM channel_names_state cn WHERE LOWER(cn.owner_login)=LOWER(?) AND cn.channel_type_code=1
ORDER BY LOWER(cn.display_name), cn.slug LIMIT ? OFFSET ?
""";
} else if ("following".equals(mode)) {
sql="""
SELECT DISTINCT cn.owner_login, cn.slug, cn.display_name, cn.ava_ar, cn.owner_bch_name, cn.channel_root_block_number, encode(cn.channel_root_block_hash,'hex') root_hash
FROM connections_state cs JOIN channel_names_state cn ON cn.owner_bch_name=cs.to_bch_name AND cn.channel_root_block_number=cs.to_block_number AND cn.channel_root_block_hash=cs.to_block_hash
WHERE LOWER(cs.login)=LOWER(?) AND cs.rel_type=30 AND cn.channel_type_code=1
ORDER BY LOWER(cn.display_name), cn.slug LIMIT ? OFFSET ?
""";
} else throw new IllegalArgumentException("Unsupported channel mode: "+mode);
List<ChannelCard> out=new ArrayList<>();
try(PreparedStatement ps=c.prepareStatement(sql)){ ps.setString(1,login);ps.setInt(2,safeLimit);ps.setInt(3,safeOffset);
try(ResultSet rs=ps.executeQuery()){while(rs.next())out.add(new ChannelCard(rs.getString("owner_login"),rs.getString("slug"),rs.getString("display_name"),rs.getString("ava_ar"),rs.getString("owner_bch_name"),rs.getInt("channel_root_block_number"),rs.getString("root_hash")));}}
return out;
}
private static ProfileCard map(ResultSet rs)throws SQLException{return new ProfileCard(rs.getString("login"),rs.getString("first_name"),rs.getString("last_name"),rs.getString("ava_ar"),rs.getString("account_role"),rs.getString("shine_status"));}
public record ProfileCard(String login,String firstName,String lastName,String avatarAr,String accountRole,String shineStatus){}
public record RelationCard(String login,String firstName,String lastName,String avatarAr,String accountRole,String shineStatus,boolean primaryConfirmed,boolean shineConfirmed,String relationType,int primaryConfirmationsCount,int shineConfirmationsCount){}
public record ChannelCard(String ownerLogin,String slug,String displayName,String avatarAr,String ownerBlockchainName,int rootBlockNumber,String rootBlockHashHex){}
}
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v18.sql
+348
View File
@@ -0,0 +1,348 @@
BEGIN;
-- v18: fast profile projection, effective social/voting statistics and qualified likes.
-- Source of truth remains users_params / connections_state / reactions_state / channel_names_state.
CREATE TABLE IF NOT EXISTS user_profile_state (
login TEXT PRIMARY KEY,
first_name TEXT NOT NULL DEFAULT '',
last_name TEXT NOT NULL DEFAULT '',
ava_ar TEXT NOT NULL DEFAULT '',
account_role TEXT,
shine_status TEXT,
updated_at_ms BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_user_profile_state_name
ON user_profile_state (LOWER(last_name), LOWER(first_name), LOWER(login));
ALTER TABLE user_stats_state
ADD COLUMN IF NOT EXISTS friends_count INTEGER NOT NULL DEFAULT 0 CHECK (friends_count >= 0),
ADD COLUMN IF NOT EXISTS primary_confirmations_received_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_confirmations_received_count >= 0),
ADD COLUMN IF NOT EXISTS primary_confirmations_given_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_confirmations_given_count >= 0),
ADD COLUMN IF NOT EXISTS shine_confirmations_received_count INTEGER NOT NULL DEFAULT 0 CHECK (shine_confirmations_received_count >= 0),
ADD COLUMN IF NOT EXISTS shine_confirmations_given_count INTEGER NOT NULL DEFAULT 0 CHECK (shine_confirmations_given_count >= 0);
ALTER TABLE message_stats
ADD COLUMN IF NOT EXISTS primary_likes_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_likes_count >= 0),
ADD COLUMN IF NOT EXISTS shining_likes_count INTEGER NOT NULL DEFAULT 0 CHECK (shining_likes_count >= 0);
CREATE OR REPLACE FUNCTION shine_refresh_user_profile(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_first_name TEXT := '';
v_last_name TEXT := '';
v_ava_ar TEXT := '';
v_account_role TEXT := NULL;
v_shine TEXT := NULL;
v_updated BIGINT := 0;
BEGIN
IF p_login IS NULL OR btrim(p_login) = '' THEN RETURN; END IF;
SELECT COALESCE(MAX(value) FILTER (WHERE param = 'first_name'), ''),
COALESCE(MAX(value) FILTER (WHERE param = 'last_name'), ''),
COALESCE(MAX(value) FILTER (WHERE param = 'ava'), ''),
MAX(value) FILTER (WHERE param = 'account_role'),
MAX(value) FILTER (WHERE param = 'shine'),
COALESCE(MAX(time_ms), 0)
INTO v_first_name, v_last_name, v_ava_ar, v_account_role, v_shine, v_updated
FROM users_params
WHERE LOWER(login) = LOWER(p_login);
v_account_role := LOWER(BTRIM(COALESCE(v_account_role, '')));
IF v_account_role NOT IN ('primary', 'non_voting') THEN v_account_role := NULL; END IF;
v_shine := LOWER(BTRIM(COALESCE(v_shine, '')));
IF v_shine = 'yes' THEN v_shine := 'shining'; END IF; -- legacy compatibility
IF v_shine NOT IN ('shining', 'unknown', 'not_interested') THEN v_shine := NULL; END IF;
INSERT INTO user_profile_state(login, first_name, last_name, ava_ar, account_role, shine_status, updated_at_ms)
VALUES (p_login, v_first_name, v_last_name, v_ava_ar, v_account_role, v_shine, v_updated)
ON CONFLICT (login) DO UPDATE SET
first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
ava_ar = EXCLUDED.ava_ar,
account_role = EXCLUDED.account_role,
shine_status = EXCLUDED.shine_status,
updated_at_ms = EXCLUDED.updated_at_ms;
END;
$$;
CREATE OR REPLACE FUNCTION shine_is_primary(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT account_role = 'primary' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), FALSE)
$$;
CREATE OR REPLACE FUNCTION shine_is_shining(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT shine_status = 'shining' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), FALSE)
$$;
CREATE OR REPLACE FUNCTION shine_target_accepts_primary_confirmation(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT account_role IS DISTINCT FROM 'non_voting' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), TRUE)
$$;
CREATE OR REPLACE FUNCTION shine_target_accepts_shine_confirmation(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT shine_status IS DISTINCT FROM 'not_interested' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), TRUE)
$$;
CREATE OR REPLACE FUNCTION shine_refresh_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_now BIGINT := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
BEGIN
IF p_login IS NULL OR btrim(p_login) = '' THEN RETURN; END IF;
INSERT INTO user_stats_state(login, owned_public_channels_count, following_users_count,
following_channels_count, close_friends_count, friends_count,
primary_confirmations_received_count, primary_confirmations_given_count,
shine_confirmations_received_count, shine_confirmations_given_count, updated_at_ms)
SELECT p_login,
(SELECT COUNT(*)::INT FROM channel_names_state cn WHERE LOWER(cn.owner_login)=LOWER(p_login) AND cn.channel_type_code=1),
0,
(SELECT COUNT(*)::INT FROM connections_state cs JOIN channel_names_state cn
ON cn.owner_bch_name=cs.to_bch_name AND cn.channel_root_block_number=cs.to_block_number AND cn.channel_root_block_hash=cs.to_block_hash
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=30 AND cn.channel_type_code=1),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=10),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=14
AND NOT EXISTS (SELECT 1 FROM connections_state cf WHERE LOWER(cf.login)=LOWER(p_login) AND LOWER(cf.to_login)=LOWER(cs.to_login) AND cf.rel_type=10)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(p_login) AND shine_target_accepts_primary_confirmation(cs.to_login)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(p_login) AND shine_is_shining(p_login) AND shine_target_accepts_shine_confirmation(cs.to_login)),
v_now
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count=EXCLUDED.owned_public_channels_count,
following_users_count=0,
following_channels_count=EXCLUDED.following_channels_count,
close_friends_count=EXCLUDED.close_friends_count,
friends_count=EXCLUDED.friends_count,
primary_confirmations_received_count=EXCLUDED.primary_confirmations_received_count,
primary_confirmations_given_count=EXCLUDED.primary_confirmations_given_count,
shine_confirmations_received_count=EXCLUDED.shine_confirmations_received_count,
shine_confirmations_given_count=EXCLUDED.shine_confirmations_given_count,
updated_at_ms=EXCLUDED.updated_at_ms;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_related_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
BEGIN
PERFORM shine_refresh_user_stats(p_login);
FOR r IN
SELECT DISTINCT x.login FROM (
SELECT cs.login FROM connections_state cs WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type IN (70,80)
UNION
SELECT cs.to_login AS login FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type IN (70,80)
) x
LOOP
PERFORM shine_refresh_user_stats(r.login);
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_message_qualified_likes_for_actor(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
BEGIN
FOR r IN
SELECT DISTINCT rs.to_login, rs.to_bch_name, rs.to_block_number, rs.to_block_hash
FROM reactions_state rs
WHERE LOWER(rs.from_login)=LOWER(p_login) AND rs.reaction_type=1 AND rs.last_sub_type=1
LOOP
UPDATE message_stats ms SET
primary_likes_count=(SELECT COUNT(*)::INT FROM reactions_state x WHERE x.reaction_type=1 AND x.last_sub_type=1
AND x.to_login=r.to_login AND x.to_bch_name=r.to_bch_name AND x.to_block_number=r.to_block_number AND x.to_block_hash=r.to_block_hash
AND shine_is_primary(x.from_login)),
shining_likes_count=(SELECT COUNT(*)::INT FROM reactions_state x WHERE x.reaction_type=1 AND x.last_sub_type=1
AND x.to_login=r.to_login AND x.to_bch_name=r.to_bch_name AND x.to_block_number=r.to_block_number AND x.to_block_hash=r.to_block_hash
AND shine_is_primary(x.from_login) AND shine_is_shining(x.from_login))
WHERE ms.to_login=r.to_login AND ms.to_bch_name=r.to_bch_name AND ms.to_block_number=r.to_block_number AND ms.to_block_hash=r.to_block_hash;
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_channel_follow_stats(p_bch_name TEXT, p_block_number INTEGER, p_block_hash BYTEA)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_owner_login TEXT;
v_channel_type INTEGER;
v_count INTEGER := 0;
v_now BIGINT := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
BEGIN
IF p_bch_name IS NULL OR p_block_number IS NULL OR p_block_hash IS NULL OR p_block_number <= 0 THEN RETURN; END IF;
SELECT cn.owner_login, cn.channel_type_code
INTO v_owner_login, v_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name=p_bch_name
AND cn.channel_root_block_number=p_block_number
AND cn.channel_root_block_hash=p_block_hash
LIMIT 1;
IF v_channel_type IS NULL THEN RETURN; END IF;
SELECT COUNT(DISTINCT LOWER(cs.login))::INT
INTO v_count
FROM connections_state cs
WHERE cs.rel_type=30
AND cs.to_bch_name=p_bch_name
AND cs.to_block_number=p_block_number
AND cs.to_block_hash=p_block_hash;
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(p_bch_name,p_block_number,p_block_hash,COALESCE(v_owner_login,''),v_channel_type,v_count,v_now)
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=EXCLUDED.subscribers_count,
updated_at_ms=EXCLUDED.updated_at_ms;
END;
$$;
-- Replace incremental channel-name counter with idempotent recomputation.
CREATE OR REPLACE FUNCTION shine_channel_names_state_stats_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.channel_type_code = 1 THEN
PERFORM shine_refresh_user_stats(NEW.owner_login);
END IF;
RETURN NEW;
END;
$$;
-- Rebuild connection state without incremental user counters; then recompute affected users.
CREATE OR REPLACE FUNCTION shine_blocks_connection_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
resolved_login TEXT;
positive_rel_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 resolved_login IS NULL OR NEW.to_bch_name IS NULL THEN RETURN NEW; END IF;
IF NEW.msg_sub_type IN (10,14,20,30,40,50,52,54,60,70,74,80) THEN
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')));
ELSE
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 THEN RETURN NEW; 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'));
END IF;
PERFORM shine_refresh_user_stats(NEW.login);
PERFORM shine_refresh_user_stats(resolved_login);
IF NEW.msg_sub_type IN (30,31) AND NEW.to_block_number IS NOT NULL AND NEW.to_block_number > 0 AND NEW.to_block_hash IS NOT NULL THEN
PERFORM shine_refresh_channel_follow_stats(NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash);
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_like_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE previous_sub_type INTEGER;
BEGIN
IF NEW.msg_type<>2 OR NEW.msg_sub_type NOT IN (1,2) THEN RETURN NEW; END IF;
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN RETURN NEW; END IF;
INSERT INTO message_stats(to_login,to_bch_name,to_block_number,to_block_hash,likes_count,primary_likes_count,shining_likes_count,replies_count,edits_count)
VALUES(NEW.to_login,NEW.to_bch_name,NEW.to_block_number,NEW.to_block_hash,0,0,0,0,0)
ON CONFLICT(to_login,to_bch_name,to_block_number,to_block_hash) DO NOTHING;
SELECT last_sub_type INTO previous_sub_type FROM reactions_state WHERE from_login=NEW.login AND from_bch_name=NEW.bch_name AND reaction_type=1
AND to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash LIMIT 1;
IF NEW.msg_sub_type=1 AND previous_sub_type IS DISTINCT FROM 1 THEN
UPDATE message_stats SET likes_count=likes_count+1,
primary_likes_count=primary_likes_count + CASE WHEN shine_is_primary(NEW.login) THEN 1 ELSE 0 END,
shining_likes_count=shining_likes_count + CASE WHEN shine_is_primary(NEW.login) AND shine_is_shining(NEW.login) THEN 1 ELSE 0 END
WHERE to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash;
ELSIF NEW.msg_sub_type=2 AND previous_sub_type=1 THEN
UPDATE message_stats SET likes_count=GREATEST(0,likes_count-1),
primary_likes_count=GREATEST(0,primary_likes_count - CASE WHEN shine_is_primary(NEW.login) THEN 1 ELSE 0 END),
shining_likes_count=GREATEST(0,shining_likes_count - CASE WHEN shine_is_primary(NEW.login) AND shine_is_shining(NEW.login) THEN 1 ELSE 0 END)
WHERE to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash;
END IF;
INSERT INTO reactions_state(from_login,from_bch_name,reaction_type,to_login,to_bch_name,to_block_number,to_block_hash,last_sub_type)
VALUES(NEW.login,NEW.bch_name,1,NEW.to_login,NEW.to_bch_name,NEW.to_block_number,NEW.to_block_hash,NEW.msg_sub_type)
ON CONFLICT(from_login,from_bch_name,reaction_type,to_login,to_bch_name,to_block_number,to_block_hash) DO UPDATE SET last_sub_type=EXCLUDED.last_sub_type;
RETURN NEW;
END;
$$;
-- Initial projection/rebuild.
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT DISTINCT login FROM users_params LOOP PERFORM shine_refresh_user_profile(r.login); END LOOP;
END $$;
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT login FROM solana_user_pda_current LOOP PERFORM shine_refresh_user_stats(r.login); END LOOP;
END $$;
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT owner_bch_name, channel_root_block_number, channel_root_block_hash FROM channel_names_state WHERE channel_type_code=1 LOOP
PERFORM shine_refresh_channel_follow_stats(r.owner_bch_name, r.channel_root_block_number, r.channel_root_block_hash);
END LOOP;
END $$;
UPDATE message_stats ms SET
primary_likes_count=(SELECT COUNT(*)::INT FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login)),
shining_likes_count=(SELECT COUNT(*)::INT FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login) AND shine_is_shining(rs.from_login));
INSERT INTO db_schema_version(id,schema_version,updated_at_ms)
VALUES(1,18,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/migration_v19.sql
+83
View File
@@ -0,0 +1,83 @@
BEGIN;
-- v19: skip user_stats refresh for logins that are absent from solana_user_pda_current.
-- This keeps bootstrap resilient to legacy rows that are still present in blocks.
CREATE OR REPLACE FUNCTION shine_refresh_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_now BIGINT := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
BEGIN
IF p_login IS NULL OR btrim(p_login) = '' THEN RETURN; END IF;
IF NOT EXISTS (
SELECT 1
FROM solana_user_pda_current
WHERE login = p_login
LIMIT 1
) THEN
RETURN;
END IF;
INSERT INTO user_stats_state(login, owned_public_channels_count, following_users_count,
following_channels_count, close_friends_count, friends_count,
primary_confirmations_received_count, primary_confirmations_given_count,
shine_confirmations_received_count, shine_confirmations_given_count, updated_at_ms)
SELECT p_login,
(SELECT COUNT(*)::INT FROM channel_names_state cn WHERE LOWER(cn.owner_login)=LOWER(p_login) AND cn.channel_type_code=1),
0,
(SELECT COUNT(*)::INT FROM connections_state cs JOIN channel_names_state cn
ON cn.owner_bch_name=cs.to_bch_name AND cn.channel_root_block_number=cs.to_block_number AND cn.channel_root_block_hash=cs.to_block_hash
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=30 AND cn.channel_type_code=1),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=10),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=14
AND NOT EXISTS (SELECT 1 FROM connections_state cf WHERE LOWER(cf.login)=LOWER(p_login) AND LOWER(cf.to_login)=LOWER(cs.to_login) AND cf.rel_type=10)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(p_login) AND shine_target_accepts_primary_confirmation(cs.to_login)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(p_login) AND shine_is_shining(p_login) AND shine_target_accepts_shine_confirmation(cs.to_login)),
v_now
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count=EXCLUDED.owned_public_channels_count,
following_users_count=0,
following_channels_count=EXCLUDED.following_channels_count,
close_friends_count=EXCLUDED.close_friends_count,
friends_count=EXCLUDED.friends_count,
primary_confirmations_received_count=EXCLUDED.primary_confirmations_received_count,
primary_confirmations_given_count=EXCLUDED.primary_confirmations_given_count,
shine_confirmations_received_count=EXCLUDED.shine_confirmations_received_count,
shine_confirmations_given_count=EXCLUDED.shine_confirmations_given_count,
updated_at_ms=EXCLUDED.updated_at_ms;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_related_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
BEGIN
PERFORM shine_refresh_user_stats(p_login);
FOR r IN
SELECT DISTINCT x.login FROM (
SELECT cs.login FROM connections_state cs WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type IN (70,80)
UNION
SELECT cs.to_login AS login FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type IN (70,80)
) x
LOOP
PERFORM shine_refresh_user_stats(r.login);
END LOOP;
END;
$$;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
+356 -1
View File
@@ -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, 16, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
VALUES (1, 18, 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;
@@ -1646,4 +1646,359 @@ AFTER INSERT ON channel_names_state
FOR EACH ROW
EXECUTE FUNCTION shine_channel_names_state_stats_ai();
-- v18 integrated fresh-schema definitions
-- v18: fast profile projection, effective social/voting statistics and qualified likes.
-- Source of truth remains users_params / connections_state / reactions_state / channel_names_state.
CREATE TABLE IF NOT EXISTS user_profile_state (
login TEXT PRIMARY KEY,
first_name TEXT NOT NULL DEFAULT '',
last_name TEXT NOT NULL DEFAULT '',
ava_ar TEXT NOT NULL DEFAULT '',
account_role TEXT,
shine_status TEXT,
updated_at_ms BIGINT NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_user_profile_state_name
ON user_profile_state (LOWER(last_name), LOWER(first_name), LOWER(login));
ALTER TABLE user_stats_state
ADD COLUMN IF NOT EXISTS friends_count INTEGER NOT NULL DEFAULT 0 CHECK (friends_count >= 0),
ADD COLUMN IF NOT EXISTS primary_confirmations_received_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_confirmations_received_count >= 0),
ADD COLUMN IF NOT EXISTS primary_confirmations_given_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_confirmations_given_count >= 0),
ADD COLUMN IF NOT EXISTS shine_confirmations_received_count INTEGER NOT NULL DEFAULT 0 CHECK (shine_confirmations_received_count >= 0),
ADD COLUMN IF NOT EXISTS shine_confirmations_given_count INTEGER NOT NULL DEFAULT 0 CHECK (shine_confirmations_given_count >= 0);
ALTER TABLE message_stats
ADD COLUMN IF NOT EXISTS primary_likes_count INTEGER NOT NULL DEFAULT 0 CHECK (primary_likes_count >= 0),
ADD COLUMN IF NOT EXISTS shining_likes_count INTEGER NOT NULL DEFAULT 0 CHECK (shining_likes_count >= 0);
CREATE OR REPLACE FUNCTION shine_refresh_user_profile(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_first_name TEXT := '';
v_last_name TEXT := '';
v_ava_ar TEXT := '';
v_account_role TEXT := NULL;
v_shine TEXT := NULL;
v_updated BIGINT := 0;
BEGIN
IF p_login IS NULL OR btrim(p_login) = '' THEN RETURN; END IF;
SELECT COALESCE(MAX(value) FILTER (WHERE param = 'first_name'), ''),
COALESCE(MAX(value) FILTER (WHERE param = 'last_name'), ''),
COALESCE(MAX(value) FILTER (WHERE param = 'ava'), ''),
MAX(value) FILTER (WHERE param = 'account_role'),
MAX(value) FILTER (WHERE param = 'shine'),
COALESCE(MAX(time_ms), 0)
INTO v_first_name, v_last_name, v_ava_ar, v_account_role, v_shine, v_updated
FROM users_params
WHERE LOWER(login) = LOWER(p_login);
v_account_role := LOWER(BTRIM(COALESCE(v_account_role, '')));
IF v_account_role NOT IN ('primary', 'non_voting') THEN v_account_role := NULL; END IF;
v_shine := LOWER(BTRIM(COALESCE(v_shine, '')));
IF v_shine = 'yes' THEN v_shine := 'shining'; END IF; -- legacy compatibility
IF v_shine NOT IN ('shining', 'unknown', 'not_interested') THEN v_shine := NULL; END IF;
INSERT INTO user_profile_state(login, first_name, last_name, ava_ar, account_role, shine_status, updated_at_ms)
VALUES (p_login, v_first_name, v_last_name, v_ava_ar, v_account_role, v_shine, v_updated)
ON CONFLICT (login) DO UPDATE SET
first_name = EXCLUDED.first_name,
last_name = EXCLUDED.last_name,
ava_ar = EXCLUDED.ava_ar,
account_role = EXCLUDED.account_role,
shine_status = EXCLUDED.shine_status,
updated_at_ms = EXCLUDED.updated_at_ms;
END;
$$;
CREATE OR REPLACE FUNCTION shine_is_primary(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT account_role = 'primary' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), FALSE)
$$;
CREATE OR REPLACE FUNCTION shine_is_shining(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT shine_status = 'shining' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), FALSE)
$$;
CREATE OR REPLACE FUNCTION shine_target_accepts_primary_confirmation(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT account_role IS DISTINCT FROM 'non_voting' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), TRUE)
$$;
CREATE OR REPLACE FUNCTION shine_target_accepts_shine_confirmation(p_login TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
AS $$
SELECT COALESCE((SELECT shine_status IS DISTINCT FROM 'not_interested' FROM user_profile_state WHERE LOWER(login)=LOWER(p_login) LIMIT 1), TRUE)
$$;
CREATE OR REPLACE FUNCTION shine_refresh_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_now BIGINT := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
BEGIN
IF p_login IS NULL OR btrim(p_login) = '' THEN RETURN; END IF;
IF NOT EXISTS (
SELECT 1
FROM solana_user_pda_current
WHERE login = p_login
LIMIT 1
) THEN
RETURN;
END IF;
INSERT INTO user_stats_state(login, owned_public_channels_count, following_users_count,
following_channels_count, close_friends_count, friends_count,
primary_confirmations_received_count, primary_confirmations_given_count,
shine_confirmations_received_count, shine_confirmations_given_count, updated_at_ms)
SELECT p_login,
(SELECT COUNT(*)::INT FROM channel_names_state cn WHERE LOWER(cn.owner_login)=LOWER(p_login) AND cn.channel_type_code=1),
0,
(SELECT COUNT(*)::INT FROM connections_state cs JOIN channel_names_state cn
ON cn.owner_bch_name=cs.to_bch_name AND cn.channel_root_block_number=cs.to_block_number AND cn.channel_root_block_hash=cs.to_block_hash
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=30 AND cn.channel_type_code=1),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=10),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=14
AND NOT EXISTS (SELECT 1 FROM connections_state cf WHERE LOWER(cf.login)=LOWER(p_login) AND LOWER(cf.to_login)=LOWER(cs.to_login) AND cf.rel_type=10)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(cs.login) AND shine_target_accepts_primary_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=80
AND shine_is_primary(p_login) AND shine_target_accepts_primary_confirmation(cs.to_login)),
(SELECT COUNT(DISTINCT LOWER(cs.login))::INT FROM connections_state cs
WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(cs.login) AND shine_is_shining(cs.login) AND shine_target_accepts_shine_confirmation(p_login)),
(SELECT COUNT(DISTINCT LOWER(cs.to_login))::INT FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type=70
AND shine_is_primary(p_login) AND shine_is_shining(p_login) AND shine_target_accepts_shine_confirmation(cs.to_login)),
v_now
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count=EXCLUDED.owned_public_channels_count,
following_users_count=0,
following_channels_count=EXCLUDED.following_channels_count,
close_friends_count=EXCLUDED.close_friends_count,
friends_count=EXCLUDED.friends_count,
primary_confirmations_received_count=EXCLUDED.primary_confirmations_received_count,
primary_confirmations_given_count=EXCLUDED.primary_confirmations_given_count,
shine_confirmations_received_count=EXCLUDED.shine_confirmations_received_count,
shine_confirmations_given_count=EXCLUDED.shine_confirmations_given_count,
updated_at_ms=EXCLUDED.updated_at_ms;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_related_user_stats(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
BEGIN
PERFORM shine_refresh_user_stats(p_login);
FOR r IN
SELECT DISTINCT x.login FROM (
SELECT cs.login FROM connections_state cs WHERE LOWER(cs.to_login)=LOWER(p_login) AND cs.rel_type IN (70,80)
UNION
SELECT cs.to_login AS login FROM connections_state cs WHERE LOWER(cs.login)=LOWER(p_login) AND cs.rel_type IN (70,80)
) x
LOOP
PERFORM shine_refresh_user_stats(r.login);
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_message_qualified_likes_for_actor(p_login TEXT)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE r RECORD;
BEGIN
FOR r IN
SELECT DISTINCT rs.to_login, rs.to_bch_name, rs.to_block_number, rs.to_block_hash
FROM reactions_state rs
WHERE LOWER(rs.from_login)=LOWER(p_login) AND rs.reaction_type=1 AND rs.last_sub_type=1
LOOP
UPDATE message_stats ms SET
primary_likes_count=(SELECT COUNT(*)::INT FROM reactions_state x WHERE x.reaction_type=1 AND x.last_sub_type=1
AND x.to_login=r.to_login AND x.to_bch_name=r.to_bch_name AND x.to_block_number=r.to_block_number AND x.to_block_hash=r.to_block_hash
AND shine_is_primary(x.from_login)),
shining_likes_count=(SELECT COUNT(*)::INT FROM reactions_state x WHERE x.reaction_type=1 AND x.last_sub_type=1
AND x.to_login=r.to_login AND x.to_bch_name=r.to_bch_name AND x.to_block_number=r.to_block_number AND x.to_block_hash=r.to_block_hash
AND shine_is_primary(x.from_login) AND shine_is_shining(x.from_login))
WHERE ms.to_login=r.to_login AND ms.to_bch_name=r.to_bch_name AND ms.to_block_number=r.to_block_number AND ms.to_block_hash=r.to_block_hash;
END LOOP;
END;
$$;
CREATE OR REPLACE FUNCTION shine_refresh_channel_follow_stats(p_bch_name TEXT, p_block_number INTEGER, p_block_hash BYTEA)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
v_owner_login TEXT;
v_channel_type INTEGER;
v_count INTEGER := 0;
v_now BIGINT := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
BEGIN
IF p_bch_name IS NULL OR p_block_number IS NULL OR p_block_hash IS NULL OR p_block_number <= 0 THEN RETURN; END IF;
SELECT cn.owner_login, cn.channel_type_code
INTO v_owner_login, v_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name=p_bch_name
AND cn.channel_root_block_number=p_block_number
AND cn.channel_root_block_hash=p_block_hash
LIMIT 1;
IF v_channel_type IS NULL THEN RETURN; END IF;
SELECT COUNT(DISTINCT LOWER(cs.login))::INT
INTO v_count
FROM connections_state cs
WHERE cs.rel_type=30
AND cs.to_bch_name=p_bch_name
AND cs.to_block_number=p_block_number
AND cs.to_block_hash=p_block_hash;
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(p_bch_name,p_block_number,p_block_hash,COALESCE(v_owner_login,''),v_channel_type,v_count,v_now)
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=EXCLUDED.subscribers_count,
updated_at_ms=EXCLUDED.updated_at_ms;
END;
$$;
-- Replace incremental channel-name counter with idempotent recomputation.
CREATE OR REPLACE FUNCTION shine_channel_names_state_stats_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.channel_type_code = 1 THEN
PERFORM shine_refresh_user_stats(NEW.owner_login);
END IF;
RETURN NEW;
END;
$$;
-- Rebuild connection state without incremental user counters; then recompute affected users.
CREATE OR REPLACE FUNCTION shine_blocks_connection_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
resolved_login TEXT;
positive_rel_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 resolved_login IS NULL OR NEW.to_bch_name IS NULL THEN RETURN NEW; END IF;
IF NEW.msg_sub_type IN (10,14,20,30,40,50,52,54,60,70,74,80) THEN
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')));
ELSE
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 THEN RETURN NEW; 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'));
END IF;
PERFORM shine_refresh_user_stats(NEW.login);
PERFORM shine_refresh_user_stats(resolved_login);
IF NEW.msg_sub_type IN (30,31) AND NEW.to_block_number IS NOT NULL AND NEW.to_block_number > 0 AND NEW.to_block_hash IS NOT NULL THEN
PERFORM shine_refresh_channel_follow_stats(NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash);
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_like_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE previous_sub_type INTEGER;
BEGIN
IF NEW.msg_type<>2 OR NEW.msg_sub_type NOT IN (1,2) THEN RETURN NEW; END IF;
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN RETURN NEW; END IF;
INSERT INTO message_stats(to_login,to_bch_name,to_block_number,to_block_hash,likes_count,primary_likes_count,shining_likes_count,replies_count,edits_count)
VALUES(NEW.to_login,NEW.to_bch_name,NEW.to_block_number,NEW.to_block_hash,0,0,0,0,0)
ON CONFLICT(to_login,to_bch_name,to_block_number,to_block_hash) DO NOTHING;
SELECT last_sub_type INTO previous_sub_type FROM reactions_state WHERE from_login=NEW.login AND from_bch_name=NEW.bch_name AND reaction_type=1
AND to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash LIMIT 1;
IF NEW.msg_sub_type=1 AND previous_sub_type IS DISTINCT FROM 1 THEN
UPDATE message_stats SET likes_count=likes_count+1,
primary_likes_count=primary_likes_count + CASE WHEN shine_is_primary(NEW.login) THEN 1 ELSE 0 END,
shining_likes_count=shining_likes_count + CASE WHEN shine_is_primary(NEW.login) AND shine_is_shining(NEW.login) THEN 1 ELSE 0 END
WHERE to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash;
ELSIF NEW.msg_sub_type=2 AND previous_sub_type=1 THEN
UPDATE message_stats SET likes_count=GREATEST(0,likes_count-1),
primary_likes_count=GREATEST(0,primary_likes_count - CASE WHEN shine_is_primary(NEW.login) THEN 1 ELSE 0 END),
shining_likes_count=GREATEST(0,shining_likes_count - CASE WHEN shine_is_primary(NEW.login) AND shine_is_shining(NEW.login) THEN 1 ELSE 0 END)
WHERE to_login=NEW.to_login AND to_bch_name=NEW.to_bch_name AND to_block_number=NEW.to_block_number AND to_block_hash=NEW.to_block_hash;
END IF;
INSERT INTO reactions_state(from_login,from_bch_name,reaction_type,to_login,to_bch_name,to_block_number,to_block_hash,last_sub_type)
VALUES(NEW.login,NEW.bch_name,1,NEW.to_login,NEW.to_bch_name,NEW.to_block_number,NEW.to_block_hash,NEW.msg_sub_type)
ON CONFLICT(from_login,from_bch_name,reaction_type,to_login,to_bch_name,to_block_number,to_block_hash) DO UPDATE SET last_sub_type=EXCLUDED.last_sub_type;
RETURN NEW;
END;
$$;
-- Initial projection/rebuild.
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT DISTINCT login FROM users_params LOOP PERFORM shine_refresh_user_profile(r.login); END LOOP;
END $$;
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT login FROM solana_user_pda_current LOOP PERFORM shine_refresh_user_stats(r.login); END LOOP;
END $$;
DO $$ DECLARE r RECORD; BEGIN
FOR r IN SELECT owner_bch_name, channel_root_block_number, channel_root_block_hash FROM channel_names_state WHERE channel_type_code=1 LOOP
PERFORM shine_refresh_channel_follow_stats(r.owner_bch_name, r.channel_root_block_number, r.channel_root_block_hash);
END LOOP;
END $$;
UPDATE message_stats ms SET
primary_likes_count=(SELECT COUNT(*)::INT FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login)),
shining_likes_count=(SELECT COUNT(*)::INT FROM reactions_state rs WHERE rs.reaction_type=1 AND rs.last_sub_type=1
AND rs.to_login=ms.to_login AND rs.to_bch_name=ms.to_bch_name AND rs.to_block_number=ms.to_block_number AND rs.to_block_hash=ms.to_block_hash
AND shine_is_primary(rs.from_login) AND shine_is_shining(rs.from_login));
INSERT INTO db_schema_version(id,schema_version,updated_at_ms)
VALUES(1,18,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;