Добавить состояние чтения каналов

This commit is contained in:
AidarKC
2026-09-09 18:59:54 +03:00
parent 2d059e9ff5
commit 8e86872aa7
29 changed files with 2538 additions and 231 deletions
@@ -17,6 +17,9 @@ public final class ShineSignatureConstants {
/** Подписываемые данные пользовательских настроек: prefix + login + type + key + time_ms + value_text + value_num */
public static final String USER_SETTINGS_PREFIX = "SHiNe/UserSettings:";
/** Подписанный watermark чтения канала: prefix + login + owner_bch_name + channel_name + time_ms + read_count */
public static final String CHANNEL_READ_STATE_PREFIX = "SHiNe/ChannelReadState:";
/** TAG в HeaderBody (genesis). ASCII "SHiNe". */
public static final String BLOCKCHAIN_HEADER_TAG = "SHiNe";
@@ -37,6 +37,7 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_18 = 18;
public static final int SCHEMA_VERSION_19 = 19;
public static final int SCHEMA_VERSION_20 = 20;
public static final int SCHEMA_VERSION_21 = 21;
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";
@@ -57,6 +58,7 @@ public final class DatabaseInitializer {
public static final String POSTGRES_MIGRATION_V18_RESOURCE = "postgres/migration_v18.sql";
public static final String POSTGRES_MIGRATION_V19_RESOURCE = "postgres/migration_v19.sql";
public static final String POSTGRES_MIGRATION_V20_RESOURCE = "postgres/migration_v20.sql";
public static final String POSTGRES_MIGRATION_V21_RESOURCE = "postgres/migration_v21.sql";
private DatabaseInitializer() {}
@@ -206,6 +208,10 @@ public final class DatabaseInitializer {
runSqlScript(conn, POSTGRES_MIGRATION_V20_RESOURCE);
currentVersion = SCHEMA_VERSION_20;
}
if (currentVersion < SCHEMA_VERSION_21) {
runSqlScript(conn, POSTGRES_MIGRATION_V21_RESOURCE);
currentVersion = SCHEMA_VERSION_21;
}
}
}
@@ -0,0 +1,281 @@
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;
/**
* Server projection for per-user channel read/unread state.
*
* Important rules:
* - rows are created only by a signed client SetChannelReadState;
* - channel publications increment unread_count only for rows that already exist;
* - read_count is monotonic and unread_count is reduced by the accepted read delta;
* - unread_count is never part of the client signature and is server-owned.
*/
public final class ChannelReadStateDAO {
private static volatile ChannelReadStateDAO instance;
private ChannelReadStateDAO() {}
public static ChannelReadStateDAO getInstance() {
if (instance == null) {
synchronized (ChannelReadStateDAO.class) {
if (instance == null) instance = new ChannelReadStateDAO();
}
}
return instance;
}
public record ReadState(long readCount, long unreadCount, long readUpdatedAtMs,
String clientKey, String readSignature) {}
public record UpsertResult(boolean applied, long readCount, long unreadCount) {}
public ReadState get(Connection c, String viewerLogin, String ownerBchName, String channelName) throws SQLException {
String sql = """
SELECT read_count, unread_count, read_updated_at_ms, client_key, read_signature
FROM channel_read_state
WHERE LOWER(viewer_login)=LOWER(?) AND owner_bch_name=? AND channel_name=?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
ps.setString(2, ownerBchName);
ps.setString(3, channelName);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return new ReadState(
Math.max(0L, rs.getLong("read_count")),
Math.max(0L, rs.getLong("unread_count")),
rs.getLong("read_updated_at_ms"),
rs.getString("client_key"),
rs.getString("read_signature"));
}
}
}
public long getUnreadCount(Connection c, String viewerLogin, String ownerBchName, String channelName) throws SQLException {
ReadState state = get(c, viewerLogin, ownerBchName, channelName);
return state == null ? 0L : state.unreadCount();
}
public long sumUnreadCount(Connection c, String viewerLogin) throws SQLException {
String sql = """
SELECT COALESCE(SUM(crs.unread_count),0)
FROM channel_read_state crs
WHERE LOWER(crs.viewer_login)=LOWER(?)
AND EXISTS (
SELECT 1
FROM channel_names_state cns
JOIN connections_state cs
ON LOWER(cs.login)=LOWER(crs.viewer_login)
AND cs.rel_type=30
AND cs.to_bch_name=cns.owner_bch_name
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
AND cs.to_block_hash=cns.channel_root_block_hash
WHERE cns.owner_bch_name=crs.owner_bch_name
AND cns.slug=crs.channel_name
AND cns.channel_type_code=1
)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Math.max(0L, rs.getLong(1)) : 0L;
}
}
}
/**
* Validates that the requested public channel exists and that viewer currently follows it.
*/
public boolean hasActiveSubscription(Connection c, String viewerLogin, String ownerBchName, String channelName,
int followRelType) throws SQLException {
String sql = """
SELECT 1
FROM channel_names_state cns
JOIN connections_state cs
ON LOWER(cs.login)=LOWER(?)
AND cs.rel_type=?
AND cs.to_bch_name=cns.owner_bch_name
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
AND cs.to_block_hash=cns.channel_root_block_hash
WHERE cns.owner_bch_name=?
AND cns.slug=?
AND cns.channel_type_code=1
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
ps.setInt(2, followRelType);
ps.setString(3, ownerBchName);
ps.setString(4, channelName);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
public String resolvePublicChannelNameByRoot(Connection c, String ownerBchName, int rootBlockNumber) throws SQLException {
String sql = """
SELECT slug
FROM channel_names_state
WHERE owner_bch_name=? AND channel_root_block_number=? AND channel_type_code=1
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ownerBchName);
ps.setInt(2, rootBlockNumber);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getString("slug") : null;
}
}
}
public String resolveOwnerLogin(Connection c, String ownerBchName, String channelName) throws SQLException {
String sql = """
SELECT owner_login
FROM channel_names_state
WHERE owner_bch_name=? AND slug=? AND channel_type_code=1
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ownerBchName);
ps.setString(2, channelName);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? rs.getString("owner_login") : null;
}
}
}
/**
* Inserts the first client baseline with unread=0, or advances an existing read watermark.
* Older timestamps and backwards read_count values are ignored.
*/
public UpsertResult upsertSignedReadIfNewer(Connection c,
String viewerLogin,
String ownerLogin,
String ownerBchName,
String channelName,
long readCount,
long readUpdatedAtMs,
String clientKey,
String readSignature,
long nowMs) throws SQLException {
String sql = """
INSERT INTO channel_read_state (
viewer_login, owner_login, owner_bch_name, channel_name,
read_count, unread_count,
read_updated_at_ms, client_key, read_signature, updated_at_ms
) VALUES (?, ?, ?, ?, ?, 0, ?, ?, ?, ?)
ON CONFLICT (viewer_login, owner_bch_name, channel_name)
DO UPDATE SET
owner_login = EXCLUDED.owner_login,
unread_count = GREATEST(
0,
channel_read_state.unread_count -
GREATEST(0, EXCLUDED.read_count - channel_read_state.read_count)
),
read_count = GREATEST(channel_read_state.read_count, EXCLUDED.read_count),
read_updated_at_ms = EXCLUDED.read_updated_at_ms,
client_key = EXCLUDED.client_key,
read_signature = EXCLUDED.read_signature,
updated_at_ms = EXCLUDED.updated_at_ms
WHERE channel_read_state.read_updated_at_ms < EXCLUDED.read_updated_at_ms
AND EXCLUDED.read_count >= channel_read_state.read_count
RETURNING read_count, unread_count
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
ps.setString(2, ownerLogin);
ps.setString(3, ownerBchName);
ps.setString(4, channelName);
ps.setLong(5, Math.max(0L, readCount));
ps.setLong(6, readUpdatedAtMs);
ps.setString(7, clientKey);
ps.setString(8, readSignature);
ps.setLong(9, nowMs);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return new UpsertResult(true,
Math.max(0L, rs.getLong("read_count")),
Math.max(0L, rs.getLong("unread_count")));
}
}
}
ReadState current = get(c, viewerLogin, ownerBchName, channelName);
return new UpsertResult(false,
current == null ? 0L : current.readCount(),
current == null ? 0L : current.unreadCount());
}
/**
* Increments existing rows for current subscribers of one public channel.
* Missing channel_read_state rows are deliberately NOT created.
* Returns affected viewer logins for post-commit counter pushes.
*/
public List<String> incrementUnreadForSubscribers(Connection c,
String ownerBchName,
String channelName,
int followRelType,
long nowMs) throws SQLException {
String sql = """
UPDATE channel_read_state crs
SET unread_count = crs.unread_count + 1,
updated_at_ms = ?
WHERE crs.owner_bch_name = ?
AND crs.channel_name = ?
AND LOWER(crs.viewer_login) <> LOWER(crs.owner_login)
AND EXISTS (
SELECT 1
FROM channel_names_state cns
JOIN connections_state cs
ON LOWER(cs.login)=LOWER(crs.viewer_login)
AND cs.rel_type=?
AND cs.to_bch_name=cns.owner_bch_name
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
AND cs.to_block_hash=cns.channel_root_block_hash
WHERE cns.owner_bch_name=crs.owner_bch_name
AND cns.slug=crs.channel_name
AND cns.channel_type_code=1
)
RETURNING viewer_login
""";
List<String> affected = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, nowMs);
ps.setString(2, ownerBchName);
ps.setString(3, channelName);
ps.setInt(4, followRelType);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) affected.add(rs.getString("viewer_login"));
}
}
return affected;
}
/** Delete the projection row when a channel FOLLOW is explicitly removed. */
public boolean deleteForUnfollowTarget(Connection c, String viewerLogin, String ownerBchName,
int rootBlockNumber) throws SQLException {
String sql = """
DELETE FROM channel_read_state crs
USING channel_names_state cns
WHERE LOWER(crs.viewer_login)=LOWER(?)
AND crs.owner_bch_name=?
AND cns.owner_bch_name=crs.owner_bch_name
AND cns.slug=crs.channel_name
AND cns.channel_root_block_number=?
AND cns.channel_type_code=1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
ps.setString(2, ownerBchName);
ps.setInt(3, rootBlockNumber);
return ps.executeUpdate() > 0;
}
}
}
@@ -0,0 +1,25 @@
-- Channel unread/read projection.
-- The row is created only by a signed SetChannelReadState from the client.
-- New channel publications only increment existing rows.
CREATE TABLE IF NOT EXISTS channel_read_state (
viewer_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_name TEXT NOT NULL,
read_count BIGINT NOT NULL DEFAULT 0 CHECK (read_count >= 0),
unread_count BIGINT NOT NULL DEFAULT 0 CHECK (unread_count >= 0),
read_updated_at_ms BIGINT NOT NULL,
client_key TEXT NOT NULL,
read_signature TEXT NOT NULL,
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (viewer_login, owner_bch_name, channel_name)
);
CREATE INDEX IF NOT EXISTS idx_channel_read_state_viewer
ON channel_read_state(viewer_login);
CREATE INDEX IF NOT EXISTS idx_channel_read_state_channel
ON channel_read_state(owner_bch_name, channel_name);
UPDATE db_schema_version SET schema_version = 21 WHERE id = 1;
@@ -425,6 +425,26 @@ CREATE INDEX IF NOT EXISTS idx_user_settings_login
CREATE INDEX IF NOT EXISTS idx_user_settings_type_key
ON user_settings(setting_type, setting_key);
CREATE TABLE IF NOT EXISTS channel_read_state (
viewer_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_name TEXT NOT NULL,
read_count BIGINT NOT NULL DEFAULT 0 CHECK (read_count >= 0),
unread_count BIGINT NOT NULL DEFAULT 0 CHECK (unread_count >= 0),
read_updated_at_ms BIGINT NOT NULL,
client_key TEXT NOT NULL,
read_signature TEXT NOT NULL,
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (viewer_login, owner_bch_name, channel_name)
);
CREATE INDEX IF NOT EXISTS idx_channel_read_state_viewer
ON channel_read_state(viewer_login);
CREATE INDEX IF NOT EXISTS idx_channel_read_state_channel
ON channel_read_state(owner_bch_name, channel_name);
CREATE TABLE IF NOT EXISTS ip_geo_cache (
ip TEXT PRIMARY KEY,
geo TEXT,
@@ -2009,7 +2029,7 @@ CREATE TABLE IF NOT EXISTS user_notification_seen_state (
);
INSERT INTO db_schema_version(id,schema_version,updated_at_ms)
VALUES(1,20,CAST(EXTRACT(EPOCH FROM clock_timestamp())*1000 AS BIGINT))
VALUES(1,21,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;
@@ -82,6 +82,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.Net_GetChannelsCounters_H
import server.logic.ws_protocol.JSON.handlers.channels.Net_ListGroupChats200_Handler;
import server.logic.ws_protocol.JSON.handlers.channels.Net_ListSubscriptionsFeed_Handler;
import server.logic.ws_protocol.JSON.handlers.channels.Net_GetUserCounters_Handler;
import server.logic.ws_protocol.JSON.handlers.channels.Net_SetChannelReadState_Handler;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelsCounters_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelMessages_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetGroupDialog_Request;
@@ -91,6 +92,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetPersonalD
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_ListGroupChats200_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_ListSubscriptionsFeed_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetUserCounters_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_SetChannelReadState_Request;
import server.logic.ws_protocol.JSON.handlers.connections.Net_GetUserConnectionsGraph_Handler;
import server.logic.ws_protocol.JSON.handlers.profile.Net_ListUserProfileRelations_Handler;
import server.logic.ws_protocol.JSON.handlers.profile.Net_ListUserProfileChannels_Handler;
@@ -217,6 +219,7 @@ public final class JsonHandlerRegistry {
Map.entry("ListGroupChats200", new Net_ListGroupChats200_Handler()),
Map.entry("GetChannelsCounters", new Net_GetChannelsCounters_Handler()),
Map.entry("GetUserCounters", new Net_GetUserCounters_Handler()),
Map.entry("SetChannelReadState", new Net_SetChannelReadState_Handler()),
Map.entry("ListContacts", new Net_ListContacts_Handler()),
Map.entry("GetUserConnectionsGraph", new Net_GetUserConnectionsGraph_Handler()),
Map.entry("ListUserProfileRelations", new Net_ListUserProfileRelations_Handler()),
@@ -310,6 +313,7 @@ public final class JsonHandlerRegistry {
Map.entry("ListGroupChats200", Net_ListGroupChats200_Request.class),
Map.entry("GetChannelsCounters", Net_GetChannelsCounters_Request.class),
Map.entry("GetUserCounters", Net_GetUserCounters_Request.class),
Map.entry("SetChannelReadState", Net_SetChannelReadState_Request.class),
Map.entry("ListContacts", Net_ListContacts_Request.class),
Map.entry("GetUserConnectionsGraph", Net_GetUserConnectionsGraph_Request.class),
Map.entry("ListUserProfileRelations", Net_ListUserProfileRelations_Request.class),
@@ -26,6 +26,7 @@ import server.logic.ws_protocol.JSON.handlers.blockchain.entyties.Net_AddBlock_R
import server.logic.ws_protocol.JSON.handlers.blockchain.entyties.Net_AddBlock_Response;
import server.logic.ws_protocol.JSON.handlers.channels.ChannelMetaTextParser;
import server.logic.ws_protocol.JSON.handlers.channels.ChannelNamesStateBootstrapper;
import server.logic.ws_protocol.JSON.handlers.channels.UserCountersSupport;
import server.logic.ws_protocol.WireCodes;
import server.sync.AddBlockSyncService;
import shine.db.channels.ChannelNameRules;
@@ -42,6 +43,7 @@ import shine.db.entities.UserParamEntry;
import utils.blockchain.BlockchainNameUtil;
import java.util.Arrays;
import java.util.List;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
@@ -586,9 +588,13 @@ public final class Net_AddBlock_Handler implements JsonMessageHandler {
}
UserNotificationEntry notificationEntry = buildNotificationEntry(block, be);
dbWriter.appendBlockAndState(
List<String> counterChangedLogins = dbWriter.appendBlockAndState(
blockchainName, block, st, be, upsertedParam, channelNameStateEntry, channelMetaUpdateEntry, notificationEntry);
for (String changedLogin : counterChangedLogins) {
UserCountersSupport.pushChanged(changedLogin);
}
if (chat200CreateSeed != null) {
upsertChat200StateFromCreate(chat200CreateSeed);
}
@@ -4,6 +4,8 @@ import blockchain.BchBlockEntry;
import shine.db.dao.BlockchainStateDAO;
import shine.db.dao.BlocksDAO;
import shine.db.dao.ChannelNameStateDAO;
import shine.db.dao.ChannelReadStateDAO;
import shine.db.MsgSubType;
import shine.db.dao.UserParamsDAO;
import shine.db.dao.UserNotificationsStateDAO;
import shine.db.entities.BlockchainStateEntry;
@@ -19,6 +21,8 @@ import java.nio.file.Path;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.HexFormat;
import java.util.ArrayList;
import java.util.List;
/**
* BlockchainWriter — запись блока в БД и формирование файловой версии цепочки.
@@ -44,6 +48,7 @@ public final class BlockchainWriter {
private final ChannelNameStateDAO channelNameStateDAO;
private final UserParamsDAO userParamsDAO;
private final UserNotificationsStateDAO userNotificationsStateDAO;
private final ChannelReadStateDAO channelReadStateDAO = ChannelReadStateDAO.getInstance();
private final FileStoreUtil fs = FileStoreUtil.getInstance();
public BlockchainWriter(BlocksDAO blocksDAO,
@@ -58,7 +63,7 @@ public final class BlockchainWriter {
this.userNotificationsStateDAO = userNotificationsStateDAO;
}
public void appendBlockAndState(String blockchainName,
public List<String> appendBlockAndState(String blockchainName,
BchBlockEntry block,
BlockchainStateEntry st,
BlockEntry be,
@@ -75,6 +80,7 @@ public final class BlockchainWriter {
prepareWriteArtifacts(blockchainName, block.blockNumber, blockHashHex, candidateBytes);
boolean committed = false;
List<String> counterChangedLogins = new ArrayList<>();
try (Connection c = shine.db.DbController.getInstance().getConnection()) {
c.setAutoCommit(false);
try {
@@ -108,6 +114,29 @@ public final class BlockchainWriter {
userNotificationsStateDAO.upsert(c, notificationEntry);
}
// Channel read/unread projection is updated in the same SQL transaction as the block.
int msgType = block.type & 0xFFFF;
int msgSubType = block.subType & 0xFFFF;
Integer lineCode = be.getLineCode();
if (isPublicChannelPublication(msgType, msgSubType) && lineCode != null && lineCode > 0) {
String channelName = channelReadStateDAO.resolvePublicChannelNameByRoot(c, blockchainName, lineCode);
if (channelName != null && !channelName.isBlank()) {
counterChangedLogins.addAll(channelReadStateDAO.incrementUnreadForSubscribers(
c, blockchainName, channelName, MsgSubType.CONNECTION_FOLLOW, nowMs));
}
}
// Explicit UNFOLLOW removes the projection row. FOLLOW never creates it.
if (msgType == 3
&& msgSubType == (MsgSubType.CONNECTION_UNFOLLOW & 0xFFFF)
&& be.getToBchName() != null && !be.getToBchName().isBlank()
&& be.getToBlockNumber() != null && be.getToBlockNumber() > 0) {
if (channelReadStateDAO.deleteForUnfollowTarget(
c, be.getLogin(), be.getToBchName(), be.getToBlockNumber())) {
counterChangedLogins.add(be.getLogin());
}
}
c.commit();
committed = true;
} catch (Exception e) {
@@ -142,6 +171,17 @@ public final class BlockchainWriter {
// 4) После успешной подмены — чистим временные артефакты.
cleanupWriteArtifactsBestEffort(blockchainName);
return counterChangedLogins;
}
private static boolean isPublicChannelPublication(int msgType, int msgSubType) {
if (msgType != 1) return false;
return msgSubType == (MsgSubType.TEXT_POST & 0xFFFF)
|| msgSubType == (MsgSubType.TEXT_REPOST & 0xFFFF)
|| msgSubType == (MsgSubType.TEXT_ENTRYPOINT & 0xFFFF)
|| msgSubType == (MsgSubType.TEXT_EXERCISE & 0xFFFF)
|| msgSubType == (MsgSubType.TEXT_SERVICE & 0xFFFF)
|| msgSubType == (MsgSubType.TEXT_COURSE & 0xFFFF);
}
private byte[] buildCandidateBlockchainBytes(String blockchainName, byte[] blockBytes) {
@@ -144,38 +144,12 @@ final class ChannelsReadSupport {
}
}
static String userSettingsChannelKey(String ownerBch, String channelName) {
String bch = ownerBch == null ? "" : ownerBch.trim();
String name = channelName == null ? "" : channelName.trim();
return bch + "/" + name;
}
static int countUnreadMessages(Connection c, String viewerLogin, String ownerBch, String channelName, int messagesCount) throws SQLException {
if (viewerLogin == null || viewerLogin.isBlank()) return 0;
String key = userSettingsChannelKey(ownerBch, channelName);
String sql = """
SELECT value_num
FROM user_settings
WHERE LOWER(login) = LOWER(?)
AND setting_type = ?
AND setting_key = ?
LIMIT 1
""";
long lastSeen = messagesCount;
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, viewerLogin);
ps.setInt(2, 1);
ps.setString(3, key);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
long value = rs.getLong("value_num");
if (!rs.wasNull()) lastSeen = value;
}
}
}
if (lastSeen < 0) lastSeen = 0;
if (lastSeen > messagesCount) return 0;
return Math.max(0, messagesCount - (int) lastSeen);
if (viewerLogin == null || viewerLogin.isBlank() || ownerBch == null || ownerBch.isBlank()
|| channelName == null || channelName.isBlank()) return 0;
long unread = shine.db.dao.ChannelReadStateDAO.getInstance()
.getUnreadCount(c, viewerLogin, ownerBch, channelName);
return unread > Integer.MAX_VALUE ? Integer.MAX_VALUE : (int) Math.max(0L, unread);
}
static PostBlock loadLastPost(Connection c, String ownerBch, int lineCode) throws SQLException {
@@ -20,7 +20,6 @@ import java.util.List;
public class Net_GetMessageLikes_Handler implements JsonMessageHandler {
private static final Logger log = LoggerFactory.getLogger(Net_GetMessageLikes_Handler.class);
private static final int HARD_LIMIT = 1000;
@Override
public Net_Response handle(Net_Request baseRequest, ConnectionContext ctx) {
@@ -42,7 +41,6 @@ public class Net_GetMessageLikes_Handler implements JsonMessageHandler {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "bad_hash", "Некорректный blockHash");
}
int requested = req.getLimit() == null ? HARD_LIMIT : Math.max(1, Math.min(HARD_LIMIT, req.getLimit()));
try (Connection c = DbController.getInstance().getConnection()) {
String sql = """
SELECT rs.from_login,
@@ -59,7 +57,6 @@ public class Net_GetMessageLikes_Handler implements JsonMessageHandler {
AND rs.to_block_number = ?
AND rs.to_block_hash = ?
ORDER BY LOWER(rs.from_login)
LIMIT ?
""";
List<Net_GetMessageLikes_Response.UserItem> shining = new ArrayList<>();
@@ -69,16 +66,8 @@ public class Net_GetMessageLikes_Handler implements JsonMessageHandler {
ps.setString(1, message.getBlockchainName().trim());
ps.setInt(2, message.getBlockNumber());
ps.setBytes(3, blockHash);
ps.setInt(4, requested + 1);
try (ResultSet rs = ps.executeQuery()) {
int accepted = 0;
boolean truncated = false;
while (rs.next()) {
if (accepted >= requested) {
truncated = true;
break;
}
accepted++;
Net_GetMessageLikes_Response.UserItem user = new Net_GetMessageLikes_Response.UserItem();
user.setLogin(rs.getString("from_login"));
user.setFirstName(rs.getString("first_name"));
@@ -100,7 +89,7 @@ public class Net_GetMessageLikes_Handler implements JsonMessageHandler {
resp.setOfficial(official);
resp.setOthers(others);
resp.setTotal(shining.size() + official.size() + others.size());
resp.setTruncated(truncated);
resp.setTruncated(false);
return resp;
}
}
@@ -12,6 +12,7 @@ import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.MsgSubType;
import shine.db.DbController;
import shine.db.dao.ChannelReadStateDAO;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -83,9 +84,11 @@ public class Net_ListSubscriptionsFeed_Handler implements JsonMessageHandler {
row.setChannel(channelRef);
row.setMessagesCount(ChannelsReadSupport.countPosts(c, key.ownerBch, key.rootNumber));
boolean ownChannel = key.ownerLogin != null && key.ownerLogin.equalsIgnoreCase(viewerLogin);
row.setUnreadCount(ownChannel
? 0
: ChannelsReadSupport.countUnreadMessages(c, viewerLogin, key.ownerBch, meta.channelName, row.getMessagesCount()));
ChannelReadStateDAO.ReadState readState = ownChannel || meta.channelName == null
? null
: ChannelReadStateDAO.getInstance().get(c, viewerLogin, key.ownerBch, meta.channelName);
row.setReadStateInitialized(readState != null);
row.setUnreadCount(readState == null ? 0 : (int) Math.min(Integer.MAX_VALUE, readState.unreadCount()));
ChannelsReadSupport.PostBlock lastPost = ChannelsReadSupport.loadLastPost(c, key.ownerBch, key.rootNumber);
if (lastPost != null) {
@@ -0,0 +1,137 @@
package server.logic.ws_protocol.JSON.handlers.channels;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import server.logic.ws_protocol.Base64Ws;
import server.logic.ws_protocol.JSON.ConnectionContext;
import server.logic.ws_protocol.JSON.entyties.Net_Request;
import server.logic.ws_protocol.JSON.entyties.Net_Response;
import server.logic.ws_protocol.JSON.handlers.JsonMessageHandler;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_SetChannelReadState_Request;
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_SetChannelReadState_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.DbController;
import shine.db.MsgSubType;
import shine.db.dao.ChannelReadStateDAO;
import utils.config.ShineSignatureConstants;
import utils.crypto.Ed25519Util;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
/** Accepts the signed client read watermark for one followed public channel. */
public final class Net_SetChannelReadState_Handler implements JsonMessageHandler {
private static final Logger log = LoggerFactory.getLogger(Net_SetChannelReadState_Handler.class);
private static final long MAX_FUTURE_SKEW_MS = 5 * 60_000L;
@Override
public Net_Response handle(Net_Request baseRequest, ConnectionContext ctx) {
Net_SetChannelReadState_Request req = (Net_SetChannelReadState_Request) baseRequest;
if (ctx == null || !ctx.isAuthenticatedUser() || ctx.getCurrentUser() == null) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.UNVERIFIED,
"NOT_AUTHENTICATED", "Требуется авторизация");
}
if (req.getLogin() == null || req.getLogin().isBlank()
|| req.getOwner_bch_name() == null || req.getOwner_bch_name().isBlank()
|| req.getChannel_name() == null || req.getChannel_name().isBlank()
|| req.getRead_count() == null || req.getRead_count() < 0
|| req.getTime_ms() == null || req.getTime_ms() <= 0
|| req.getClient_key() == null || req.getClient_key().isBlank()
|| req.getSignature() == null || req.getSignature().isBlank()) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST,
"BAD_FIELDS", "Некорректные поля ChannelReadState");
}
String authenticatedLogin = String.valueOf(ctx.getCurrentUser().getLogin()).trim();
String login = req.getLogin().trim();
String ownerBch = req.getOwner_bch_name().trim();
String channelName = req.getChannel_name().trim();
long readCount = req.getRead_count();
long timeMs = req.getTime_ms();
String clientKeyB64 = req.getClient_key().trim();
String signatureB64 = req.getSignature().trim();
if (!authenticatedLogin.equalsIgnoreCase(login)) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.UNVERIFIED,
"LOGIN_MISMATCH", "Состояние чтения принадлежит другому пользователю");
}
long nowMs = System.currentTimeMillis();
if (timeMs > nowMs + MAX_FUTURE_SKEW_MS) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.UNVERIFIED,
"BAD_TIME", "Некорректное время подписи");
}
try {
byte[] pubKey32;
byte[] sig64;
try {
pubKey32 = Base64Ws.decodeLen(clientKeyB64, 32, "client_key");
sig64 = Base64Ws.decodeLen(signatureB64, 64, "signature");
} catch (IllegalArgumentException e) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST,
"BAD_BASE64", "client_key/signature должны быть Base64");
}
String userClientKey = String.valueOf(ctx.getCurrentUser().getClientKey() == null
? "" : ctx.getCurrentUser().getClientKey()).trim();
if (userClientKey.isBlank() || !userClientKey.equals(clientKeyB64)) {
return NetExceptionResponseFactory.error(req, 403,
"DEVICE_KEY_MISMATCH", "client_key не соответствует пользователю");
}
String signText = ShineSignatureConstants.CHANNEL_READ_STATE_PREFIX
+ escapePart(login) + '|'
+ escapePart(ownerBch) + '|'
+ escapePart(channelName) + '|'
+ timeMs + '|'
+ readCount;
if (!Ed25519Util.verify(signText.getBytes(StandardCharsets.UTF_8), sig64, pubKey32)) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.UNVERIFIED,
"INVALID_SIGNATURE", "Подпись состояния чтения канала не прошла проверку");
}
ChannelReadStateDAO dao = ChannelReadStateDAO.getInstance();
ChannelReadStateDAO.UpsertResult result;
try (Connection c = DbController.getInstance().getConnection()) {
if (!dao.hasActiveSubscription(c, authenticatedLogin, ownerBch, channelName,
MsgSubType.CONNECTION_FOLLOW)) {
return NetExceptionResponseFactory.error(req, 409,
"CHANNEL_NOT_FOLLOWED", "Пользователь не подписан на этот канал");
}
String ownerLogin = dao.resolveOwnerLogin(c, ownerBch, channelName);
if (ownerLogin == null || ownerLogin.isBlank()) {
return NetExceptionResponseFactory.error(req, 404,
"CHANNEL_NOT_FOUND", "Канал не найден");
}
result = dao.upsertSignedReadIfNewer(c,
authenticatedLogin, ownerLogin, ownerBch, channelName,
readCount, timeMs, clientKeyB64, signatureB64, nowMs);
}
Net_SetChannelReadState_Response resp = new Net_SetChannelReadState_Response();
resp.setOp(req.getOp());
resp.setRequestId(req.getRequestId());
resp.setStatus(WireCodes.Status.OK);
resp.setLogin(authenticatedLogin);
resp.setOwner_bch_name(ownerBch);
resp.setChannel_name(channelName);
resp.setRead_count(result.readCount());
resp.setUnread_count(result.unreadCount());
resp.setTime_ms(timeMs);
resp.setApplied(result.applied());
if (result.applied()) UserCountersSupport.pushChanged(authenticatedLogin);
return resp;
} catch (Exception e) {
log.error("SetChannelReadState failed", e);
return NetExceptionResponseFactory.error(req, WireCodes.Status.INTERNAL_ERROR,
"INTERNAL_ERROR", NetExceptionResponseFactory.detailedMessage(
"Внутренняя ошибка SetChannelReadState", e));
}
}
private static String escapePart(String value) {
String s = String.valueOf(value == null ? "" : value);
return s.replace("\\", "\\\\").replace("|", "\\|");
}
}
@@ -6,8 +6,8 @@ import server.logic.ws_protocol.JSON.ActiveConnectionsRegistry;
import server.logic.ws_protocol.JSON.ConnectionContext;
import server.logic.ws_protocol.JSON.push.WsEventSender;
import shine.db.DbController;
import shine.db.MsgSubType;
import shine.db.dao.UserNotificationSeenStateDAO;
import shine.db.dao.ChannelReadStateDAO;
import shine.db.dao.UserNotificationsStateDAO;
import java.sql.Connection;
@@ -89,28 +89,6 @@ public final class UserCountersSupport {
}
private static long countChannelsUnread(Connection c, String login) throws Exception {
String sql = """
SELECT cs.to_bch_name, COALESCE(cs.to_block_number,0) AS root_number
FROM connections_state cs
WHERE LOWER(cs.login)=LOWER(?) AND cs.rel_type=?
""";
long total = 0L;
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setInt(2, MsgSubType.CONNECTION_FOLLOW);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String ownerBch = rs.getString("to_bch_name");
int rootNumber = rs.getInt("root_number");
if (ownerBch == null || ownerBch.isBlank()) continue;
ChannelsReadSupport.ChannelMeta meta = ChannelsReadSupport.detectChannelMeta(c, ownerBch, rootNumber);
if (meta == null || meta.channelName == null || meta.channelName.isBlank()) continue;
if (meta.channelTypeCode == 0 || "stories".equalsIgnoreCase(meta.channelName)) continue;
int messages = ChannelsReadSupport.countPosts(c, ownerBch, rootNumber);
total += Math.max(0, ChannelsReadSupport.countUnreadMessages(c, login, ownerBch, meta.channelName, messages));
}
}
}
return total;
return ChannelReadStateDAO.getInstance().sumUnreadCount(c, login);
}
}
@@ -27,6 +27,7 @@ public class Net_ListSubscriptionsFeed_Response extends Net_Response {
private ChannelRef channel;
private int messagesCount;
private int unreadCount;
private boolean readStateInitialized;
private LastMessage lastMessage;
public ChannelRef getChannel() { return channel; }
@@ -38,6 +39,9 @@ public class Net_ListSubscriptionsFeed_Response extends Net_Response {
public int getUnreadCount() { return unreadCount; }
public void setUnreadCount(int unreadCount) { this.unreadCount = unreadCount; }
public boolean isReadStateInitialized() { return readStateInitialized; }
public void setReadStateInitialized(boolean readStateInitialized) { this.readStateInitialized = readStateInitialized; }
public LastMessage getLastMessage() { return lastMessage; }
public void setLastMessage(LastMessage lastMessage) { this.lastMessage = lastMessage; }
}
@@ -0,0 +1,34 @@
package server.logic.ws_protocol.JSON.handlers.channels.entyties;
import server.logic.ws_protocol.JSON.entyties.Net_Request;
public class Net_SetChannelReadState_Request extends Net_Request {
private String login;
private String owner_bch_name;
private String channel_name;
private Long read_count;
private Long time_ms;
private String client_key;
private String signature;
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getOwner_bch_name() { return owner_bch_name; }
public void setOwner_bch_name(String owner_bch_name) { this.owner_bch_name = owner_bch_name; }
public String getChannel_name() { return channel_name; }
public void setChannel_name(String channel_name) { this.channel_name = channel_name; }
public Long getRead_count() { return read_count; }
public void setRead_count(Long read_count) { this.read_count = read_count; }
public Long getTime_ms() { return time_ms; }
public void setTime_ms(Long time_ms) { this.time_ms = time_ms; }
public String getClient_key() { return client_key; }
public void setClient_key(String client_key) { this.client_key = client_key; }
public String getSignature() { return signature; }
public void setSignature(String signature) { this.signature = signature; }
}
@@ -0,0 +1,34 @@
package server.logic.ws_protocol.JSON.handlers.channels.entyties;
import server.logic.ws_protocol.JSON.entyties.Net_Response;
public class Net_SetChannelReadState_Response extends Net_Response {
private String login;
private String owner_bch_name;
private String channel_name;
private Long read_count;
private Long unread_count;
private Long time_ms;
private Boolean applied;
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getOwner_bch_name() { return owner_bch_name; }
public void setOwner_bch_name(String owner_bch_name) { this.owner_bch_name = owner_bch_name; }
public String getChannel_name() { return channel_name; }
public void setChannel_name(String channel_name) { this.channel_name = channel_name; }
public Long getRead_count() { return read_count; }
public void setRead_count(Long read_count) { this.read_count = read_count; }
public Long getUnread_count() { return unread_count; }
public void setUnread_count(Long unread_count) { this.unread_count = unread_count; }
public Long getTime_ms() { return time_ms; }
public void setTime_ms(Long time_ms) { this.time_ms = time_ms; }
public Boolean getApplied() { return applied; }
public void setApplied(Boolean applied) { this.applied = applied; }
}
@@ -9,7 +9,6 @@ import server.logic.ws_protocol.JSON.entyties.Net_Response;
import server.logic.ws_protocol.JSON.handlers.JsonMessageHandler;
import server.logic.ws_protocol.JSON.handlers.userSettings.entyties.Net_UpsertUserSetting_Request;
import server.logic.ws_protocol.JSON.handlers.userSettings.entyties.Net_UpsertUserSetting_Response;
import server.logic.ws_protocol.JSON.handlers.channels.UserCountersSupport;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.DbController;
@@ -101,9 +100,6 @@ public class Net_UpsertUserSetting_Handler implements JsonMessageHandler {
resp.setSetting_type(settingType);
resp.setSetting_key(settingKey);
resp.setTime_ms(timeMs);
if (settingType == 1) {
UserCountersSupport.pushChanged(login);
}
return resp;
}
} catch (SQLException e) {