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:
tram
2026-08-21 14:11:47 +03:00
parent 19362b950a
commit d3e6aa2be2
23 changed files with 744 additions and 116 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
+8 -4
View File
@@ -26,6 +26,7 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_7 = 7;
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 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";
@@ -35,6 +36,7 @@ public final class DatabaseInitializer {
public static final String POSTGRES_MIGRATION_V7_RESOURCE = "postgres/migration_v7.sql";
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";
private DatabaseInitializer() {}
@@ -53,10 +55,8 @@ public final class DatabaseInitializer {
public static final short REACTION_LIKE = 1;
public static final short REACTION_UNLIKE = 2;
public static final short CONNECTION_FRIEND = 10;
public static final short CONNECTION_UNFRIEND = 11;
public static final short CONNECTION_CLOSE_FRIEND = CONNECTION_FRIEND;
public static final short CONNECTION_UNCLOSE_FRIEND = CONNECTION_UNFRIEND;
public static final short CONNECTION_CLOSE_FRIEND = 10;
public static final short CONNECTION_UNCLOSE_FRIEND = 11;
public static final short CONNECTION_CONTACT = 20;
public static final short CONNECTION_UNCONTACT = 21;
@@ -130,6 +130,10 @@ public final class DatabaseInitializer {
}
if (currentVersion < SCHEMA_VERSION_9) {
runSqlScript(conn, POSTGRES_MIGRATION_V9_RESOURCE);
currentVersion = SCHEMA_VERSION_9;
}
if (currentVersion < SCHEMA_VERSION_10) {
runSqlScript(conn, POSTGRES_MIGRATION_V10_RESOURCE);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/MsgSubType.java
+4 -10
View File
@@ -58,23 +58,17 @@ public final class MsgSubType {
/* ===================== CONNECTION (msg_type=3) ===================== */
/**
* Совпадает с ConnectionBody:
* SET: CLOSE_FRIEND(=FRIEND)=10, CONTACT=20, FOLLOW=30, SPOUSE=40, PARENT=50, CHILD=52, SIBLING=54,
* 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(=UNFRIEND)=11, UNCONTACT=21, UNFOLLOW=31, UNSPOUSE=41, UNPARENT=51, UNCHILD=53, UNSIBLING=55,
* 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
*/
/** Добавить в близкие друзья (close friend). */
public static final short CONNECTION_FRIEND = 10;
public static final short CONNECTION_CLOSE_FRIEND = 10;
/** Удалить из близких друзей (close friend). */
public static final short CONNECTION_UNFRIEND = 11;
/** Alias: добавить в close friend (то же значение, что CONNECTION_FRIEND). */
public static final short CONNECTION_CLOSE_FRIEND = CONNECTION_FRIEND;
/** Alias: удалить из close friend (то же значение, что CONNECTION_UNFRIEND). */
public static final short CONNECTION_UNCLOSE_FRIEND = CONNECTION_UNFRIEND;
public static final short CONNECTION_UNCLOSE_FRIEND = 11;
/** Добавить в контакты. */
public static final short CONNECTION_CONTACT = 20;
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainResyncCleanupDAO.java
+6 -6
View File
@@ -185,10 +185,10 @@ public final class BlockchainResyncCleanupDAO {
private int decreaseForeignLikesCount(Connection c, String blockchainName) throws SQLException {
String sql = """
UPDATE message_stats
SET likes_count = MAX(
SET likes_count = GREATEST(
0,
likes_count - (
SELECT COUNT(*)
likes_count - COALESCE((
SELECT COUNT(*)::int
FROM reactions_state rs
WHERE rs.from_bch_name = ?
AND rs.reaction_type = ?
@@ -198,7 +198,7 @@ public final class BlockchainResyncCleanupDAO {
AND rs.to_block_number = message_stats.to_block_number
AND rs.to_block_hash = message_stats.to_block_hash
AND rs.to_bch_name <> ?
)
), 0)
)
WHERE EXISTS (
SELECT 1
@@ -237,10 +237,10 @@ public final class BlockchainResyncCleanupDAO {
private int decreaseForeignRepliesCount(Connection c, String blockchainName) throws SQLException {
String sql = """
UPDATE message_stats
SET replies_count = MAX(
SET replies_count = GREATEST(
0,
replies_count - COALESCE((
SELECT COUNT(*)
SELECT COUNT(*)::int
FROM blocks b
WHERE b.bch_name = ?
AND b.msg_type = 1
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserNotificationsStateDAO.java
+105
View File
@@ -0,0 +1,105 @@
package shine.db.dao;
import shine.db.DbController;
import shine.db.entities.UserNotificationEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;
public final class UserNotificationsStateDAO {
private static volatile UserNotificationsStateDAO instance;
private final DbController db = DbController.getInstance();
private UserNotificationsStateDAO() {}
public static UserNotificationsStateDAO getInstance() {
if (instance == null) {
synchronized (UserNotificationsStateDAO.class) {
if (instance == null) instance = new UserNotificationsStateDAO();
}
}
return instance;
}
public int upsert(Connection c, UserNotificationEntry e) throws SQLException {
String sql = """
INSERT INTO user_notifications_state (
owner_login, notification_kind, created_at_ms, source_login, source_bch_name,
source_block_number, source_block_hash, target_login, target_bch_name,
target_block_number, target_block_hash, source_msg_sub_type, source_text
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(owner_login, notification_kind, source_bch_name, source_block_number, source_block_hash) DO UPDATE SET
created_at_ms = EXCLUDED.created_at_ms,
source_login = EXCLUDED.source_login,
target_login = EXCLUDED.target_login,
target_bch_name = EXCLUDED.target_bch_name,
target_block_number = EXCLUDED.target_block_number,
target_block_hash = EXCLUDED.target_block_hash,
source_msg_sub_type = EXCLUDED.source_msg_sub_type,
source_text = EXCLUDED.source_text
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
ps.setString(i++, e.getOwnerLogin());
ps.setString(i++, e.getNotificationKind());
ps.setLong(i++, e.getCreatedAtMs());
ps.setString(i++, e.getSourceLogin());
ps.setString(i++, e.getSourceBchName());
ps.setInt(i++, e.getSourceBlockNumber());
ps.setBytes(i++, e.getSourceBlockHash());
if (e.getTargetLogin() != null) ps.setString(i++, e.getTargetLogin()); else ps.setNull(i++, Types.VARCHAR);
if (e.getTargetBchName() != null) ps.setString(i++, e.getTargetBchName()); else ps.setNull(i++, Types.VARCHAR);
if (e.getTargetBlockNumber() != null) ps.setInt(i++, e.getTargetBlockNumber()); else ps.setNull(i++, Types.INTEGER);
if (e.getTargetBlockHash() != null) ps.setBytes(i++, e.getTargetBlockHash()); else ps.setNull(i++, Types.BINARY);
ps.setInt(i++, e.getSourceMsgSubType());
ps.setString(i++, e.getSourceText() == null ? "" : e.getSourceText());
return ps.executeUpdate();
}
}
public List<UserNotificationEntry> listByOwnerAndKind(Connection c, String ownerLogin, String kind, int limit) throws SQLException {
String sql = """
SELECT owner_login, notification_kind, created_at_ms, source_login, source_bch_name,
source_block_number, source_block_hash, target_login, target_bch_name,
target_block_number, target_block_hash, source_msg_sub_type, source_text
FROM user_notifications_state
WHERE owner_login = ? AND notification_kind = ?
ORDER BY created_at_ms DESC, source_block_number DESC
LIMIT ?
""";
List<UserNotificationEntry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ownerLogin);
ps.setString(2, kind);
ps.setInt(3, Math.max(1, limit));
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(mapRow(rs));
}
}
return out;
}
private static UserNotificationEntry mapRow(ResultSet rs) throws SQLException {
UserNotificationEntry e = new UserNotificationEntry();
e.setOwnerLogin(rs.getString("owner_login"));
e.setNotificationKind(rs.getString("notification_kind"));
e.setCreatedAtMs(rs.getLong("created_at_ms"));
e.setSourceLogin(rs.getString("source_login"));
e.setSourceBchName(rs.getString("source_bch_name"));
e.setSourceBlockNumber(rs.getInt("source_block_number"));
e.setSourceBlockHash(rs.getBytes("source_block_hash"));
e.setTargetLogin(rs.getString("target_login"));
e.setTargetBchName(rs.getString("target_bch_name"));
Object targetBlockNumber = rs.getObject("target_block_number");
e.setTargetBlockNumber(targetBlockNumber == null ? null : ((Number) targetBlockNumber).intValue());
e.setTargetBlockHash(rs.getBytes("target_block_hash"));
e.setSourceMsgSubType(rs.getInt("source_msg_sub_type"));
e.setSourceText(rs.getString("source_text"));
return e;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/UserNotificationEntry.java
+44
View File
@@ -0,0 +1,44 @@
package shine.db.entities;
public class UserNotificationEntry {
private String ownerLogin;
private String notificationKind;
private long createdAtMs;
private String sourceLogin;
private String sourceBchName;
private int sourceBlockNumber;
private byte[] sourceBlockHash;
private String targetLogin;
private String targetBchName;
private Integer targetBlockNumber;
private byte[] targetBlockHash;
private int sourceMsgSubType;
private String sourceText;
public String getOwnerLogin() { return ownerLogin; }
public void setOwnerLogin(String ownerLogin) { this.ownerLogin = ownerLogin; }
public String getNotificationKind() { return notificationKind; }
public void setNotificationKind(String notificationKind) { this.notificationKind = notificationKind; }
public long getCreatedAtMs() { return createdAtMs; }
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
public String getSourceLogin() { return sourceLogin; }
public void setSourceLogin(String sourceLogin) { this.sourceLogin = sourceLogin; }
public String getSourceBchName() { return sourceBchName; }
public void setSourceBchName(String sourceBchName) { this.sourceBchName = sourceBchName; }
public int getSourceBlockNumber() { return sourceBlockNumber; }
public void setSourceBlockNumber(int sourceBlockNumber) { this.sourceBlockNumber = sourceBlockNumber; }
public byte[] getSourceBlockHash() { return sourceBlockHash; }
public void setSourceBlockHash(byte[] sourceBlockHash) { this.sourceBlockHash = sourceBlockHash; }
public String getTargetLogin() { return targetLogin; }
public void setTargetLogin(String targetLogin) { this.targetLogin = targetLogin; }
public String getTargetBchName() { return targetBchName; }
public void setTargetBchName(String targetBchName) { this.targetBchName = targetBchName; }
public Integer getTargetBlockNumber() { return targetBlockNumber; }
public void setTargetBlockNumber(Integer targetBlockNumber) { this.targetBlockNumber = targetBlockNumber; }
public byte[] getTargetBlockHash() { return targetBlockHash; }
public void setTargetBlockHash(byte[] targetBlockHash) { this.targetBlockHash = targetBlockHash; }
public int getSourceMsgSubType() { return sourceMsgSubType; }
public void setSourceMsgSubType(int sourceMsgSubType) { this.sourceMsgSubType = sourceMsgSubType; }
public String getSourceText() { return sourceText; }
public void setSourceText(String sourceText) { this.sourceText = sourceText; }
}
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v10.sql
+32
View File
@@ -0,0 +1,32 @@
BEGIN;
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);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 10, 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;