SHA256
Добавлена доработка сценариев ротации ключей, закрытия сессий и повторного шифрования DM. Версия пока не проверена полностью, но ожидается рабочей.
335 lines
15 KiB
Java
335 lines
15 KiB
Java
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 LOWER(owner_login)=LOWER(?) AND channel_name=?
|
|
LIMIT 1
|
|
""";
|
|
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
|
ps.setString(1, viewerLogin);
|
|
ps.setString(2, loginFromChainName(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 LOWER(cs.to_login)=LOWER(cns.owner_login)
|
|
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
|
|
AND cs.to_block_hash=cns.channel_root_block_hash
|
|
WHERE LOWER(cns.owner_login)=LOWER(crs.owner_login)
|
|
AND cns.slug=crs.channel_name
|
|
AND cns.channel_type_code=1
|
|
AND NOT cns.is_deleted
|
|
)
|
|
""";
|
|
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 LOWER(cs.to_login)=LOWER(cns.owner_login)
|
|
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
|
|
AND cs.to_block_hash=cns.channel_root_block_hash
|
|
WHERE LOWER(cns.owner_login)=LOWER(?)
|
|
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, loginFromChainName(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 LOWER(owner_login)=LOWER(?) AND channel_root_block_number=? AND channel_type_code=1
|
|
LIMIT 1
|
|
""";
|
|
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
|
ps.setString(1, loginFromChainName(ownerBchName));
|
|
ps.setInt(2, rootBlockNumber);
|
|
try (ResultSet rs = ps.executeQuery()) {
|
|
return rs.next() ? rs.getString("slug") : null;
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
public Integer resolvePublicChannelRootByName(Connection c, String ownerBchName, String channelName) throws SQLException {
|
|
String sql = """
|
|
SELECT channel_root_block_number
|
|
FROM channel_names_state
|
|
WHERE LOWER(owner_login)=LOWER(?) AND slug=? AND channel_type_code=1
|
|
LIMIT 1
|
|
""";
|
|
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
|
ps.setString(1, loginFromChainName(ownerBchName));
|
|
ps.setString(2, channelName);
|
|
try (ResultSet rs = ps.executeQuery()) {
|
|
return rs.next() ? rs.getInt("channel_root_block_number") : null;
|
|
}
|
|
}
|
|
}
|
|
|
|
public String resolveOwnerLogin(Connection c, String ownerBchName, String channelName) throws SQLException {
|
|
String sql = """
|
|
SELECT owner_login
|
|
FROM channel_names_state
|
|
WHERE LOWER(owner_login)=LOWER(?) AND slug=? AND channel_type_code=1
|
|
LIMIT 1
|
|
""";
|
|
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
|
ps.setString(1, loginFromChainName(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_login, channel_name)
|
|
DO UPDATE SET
|
|
owner_login = EXCLUDED.owner_login,
|
|
owner_bch_name = EXCLUDED.owner_bch_name,
|
|
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, loginFromChainName(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 LOWER(crs.owner_login)=LOWER(?)
|
|
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 LOWER(cs.to_login)=LOWER(cns.owner_login)
|
|
AND COALESCE(cs.to_block_number,0)=cns.channel_root_block_number
|
|
AND cs.to_block_hash=cns.channel_root_block_hash
|
|
WHERE LOWER(cns.owner_login)=LOWER(crs.owner_login)
|
|
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, loginFromChainName(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 LOWER(crs.owner_login)=LOWER(?)
|
|
AND LOWER(cns.owner_login)=LOWER(crs.owner_login)
|
|
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, loginFromChainName(ownerBchName));
|
|
ps.setInt(3, rootBlockNumber);
|
|
return ps.executeUpdate() > 0;
|
|
}
|
|
}
|
|
/**
|
|
* Recomputes server-owned unread counters for all public channels owned by one logical login.
|
|
* Signed read_count/read signature fields are preserved exactly. Safe to call repeatedly after replay.
|
|
*/
|
|
public int recalculateUnreadForOwnedChannels(Connection c, String ownerLogin, long nowMs) throws SQLException {
|
|
String sql = """
|
|
WITH counts AS (
|
|
SELECT cns.owner_login, cns.slug, COUNT(b.block_number)::BIGINT AS message_count
|
|
FROM channel_names_state cns
|
|
LEFT JOIN blocks_store b
|
|
ON LOWER(b.login)=LOWER(cns.owner_login)
|
|
AND b.line_code=cns.channel_root_block_number
|
|
AND b.msg_type=1
|
|
AND b.msg_sub_type IN (10,50,100,110,120,130)
|
|
WHERE LOWER(cns.owner_login)=LOWER(?)
|
|
AND cns.channel_type_code=1
|
|
GROUP BY cns.owner_login,cns.slug
|
|
)
|
|
UPDATE channel_read_state crs
|
|
SET unread_count=GREATEST(0, counts.message_count-crs.read_count),
|
|
updated_at_ms=?
|
|
FROM counts
|
|
WHERE LOWER(crs.owner_login)=LOWER(counts.owner_login)
|
|
AND crs.channel_name=counts.slug
|
|
""";
|
|
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
|
ps.setString(1, ownerLogin);
|
|
ps.setLong(2, nowMs);
|
|
return ps.executeUpdate();
|
|
}
|
|
}
|
|
|
|
private static String loginFromChainName(String bch){ if(bch==null)return null; int d=bch.lastIndexOf('-'); return d>0?bch.substring(0,d):bch; }
|
|
|
|
}
|