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

Добавить догоняющую синхронизацию DM между access-серверами

Browse Source
This commit is contained in:
AidarKC
2026-07-29 13:59:40 +04:00
parent ca8b6a33ba
commit 143adcbbe4
21 changed files with 1025 additions and 13 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
+6
View File
@@ -19,8 +19,10 @@ public final class DatabaseInitializer {
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
public static final int SCHEMA_VERSION_1 = 1;
public static final int SCHEMA_VERSION_2 = 2;
public static final int SCHEMA_VERSION_3 = 3;
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";
private DatabaseInitializer() {}
@@ -82,6 +84,10 @@ public final class DatabaseInitializer {
int currentVersion = readCurrentSchemaVersion(conn);
if (currentVersion < SCHEMA_VERSION_2) {
runSqlScript(conn, POSTGRES_MIGRATION_V2_RESOURCE);
currentVersion = SCHEMA_VERSION_2;
}
if (currentVersion < SCHEMA_VERSION_3) {
runSqlScript(conn, POSTGRES_MIGRATION_V3_RESOURCE);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/DmSyncPeerStateDAO.java
+173
View File
@@ -0,0 +1,173 @@
package shine.db.dao;
import shine.db.DbController;
import shine.db.entities.DmSyncPeerStateEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public final class DmSyncPeerStateDAO {
private static volatile DmSyncPeerStateDAO instance;
private final DbController db = DbController.getInstance();
private DmSyncPeerStateDAO() {}
public static DmSyncPeerStateDAO getInstance() {
if (instance == null) {
synchronized (DmSyncPeerStateDAO.class) {
if (instance == null) instance = new DmSyncPeerStateDAO();
}
}
return instance;
}
public DmSyncPeerStateEntry getOrCreate(String ownerLogin, String remoteServerLogin, String remoteServerUrl) throws SQLException {
DmSyncPeerStateEntry existing = get(ownerLogin, remoteServerLogin);
if (existing != null) {
if (remoteServerUrl != null && !remoteServerUrl.isBlank()
&& !remoteServerUrl.equals(existing.getRemoteServerUrl())) {
touchRemoteUrl(ownerLogin, remoteServerLogin, remoteServerUrl);
existing.setRemoteServerUrl(remoteServerUrl);
}
return existing;
}
long nowMs = System.currentTimeMillis();
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement("""
INSERT INTO dm_sync_peer_state (
owner_login, remote_server_login, remote_server_url,
cursor_stored_at_ms, cursor_message_key, bootstrap_completed,
last_sync_at_ms, last_error, updated_at_ms
) VALUES (?, ?, ?, 0, '', FALSE, NULL, NULL, ?)
ON CONFLICT DO NOTHING
""")) {
ps.setString(1, ownerLogin);
ps.setString(2, remoteServerLogin);
ps.setString(3, remoteServerUrl == null ? "" : remoteServerUrl);
ps.setLong(4, nowMs);
ps.executeUpdate();
}
existing = get(ownerLogin, remoteServerLogin);
if (existing == null) {
throw new SQLException("Failed to create dm_sync_peer_state row");
}
return existing;
}
public void updateSuccess(
String ownerLogin,
String remoteServerLogin,
String remoteServerUrl,
long cursorStoredAtMs,
String cursorMessageKey,
boolean bootstrapCompleted
) throws SQLException {
long nowMs = System.currentTimeMillis();
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement("""
INSERT INTO dm_sync_peer_state (
owner_login, remote_server_login, remote_server_url,
cursor_stored_at_ms, cursor_message_key, bootstrap_completed,
last_sync_at_ms, last_error, updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, NULL, ?)
ON CONFLICT (owner_login, remote_server_login) DO UPDATE SET
remote_server_url = EXCLUDED.remote_server_url,
cursor_stored_at_ms = EXCLUDED.cursor_stored_at_ms,
cursor_message_key = EXCLUDED.cursor_message_key,
bootstrap_completed = EXCLUDED.bootstrap_completed,
last_sync_at_ms = EXCLUDED.last_sync_at_ms,
last_error = NULL,
updated_at_ms = EXCLUDED.updated_at_ms
""")) {
ps.setString(1, ownerLogin);
ps.setString(2, remoteServerLogin);
ps.setString(3, remoteServerUrl == null ? "" : remoteServerUrl);
ps.setLong(4, Math.max(0L, cursorStoredAtMs));
ps.setString(5, cursorMessageKey == null ? "" : cursorMessageKey);
ps.setBoolean(6, bootstrapCompleted);
ps.setLong(7, nowMs);
ps.setLong(8, nowMs);
ps.executeUpdate();
}
}
public void updateError(String ownerLogin, String remoteServerLogin, String remoteServerUrl, String error) throws SQLException {
long nowMs = System.currentTimeMillis();
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement("""
INSERT INTO dm_sync_peer_state (
owner_login, remote_server_login, remote_server_url,
cursor_stored_at_ms, cursor_message_key, bootstrap_completed,
last_sync_at_ms, last_error, updated_at_ms
) VALUES (?, ?, ?, 0, '', FALSE, NULL, ?, ?)
ON CONFLICT (owner_login, remote_server_login) DO UPDATE SET
remote_server_url = EXCLUDED.remote_server_url,
last_error = EXCLUDED.last_error,
updated_at_ms = EXCLUDED.updated_at_ms
""")) {
ps.setString(1, ownerLogin);
ps.setString(2, remoteServerLogin);
ps.setString(3, remoteServerUrl == null ? "" : remoteServerUrl);
ps.setString(4, truncate(error));
ps.setLong(5, nowMs);
ps.executeUpdate();
}
}
private DmSyncPeerStateEntry get(String ownerLogin, String remoteServerLogin) throws SQLException {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement("""
SELECT owner_login, remote_server_login, remote_server_url,
cursor_stored_at_ms, cursor_message_key, bootstrap_completed,
last_sync_at_ms, last_error, updated_at_ms
FROM dm_sync_peer_state
WHERE LOWER(owner_login) = LOWER(?)
AND LOWER(remote_server_login) = LOWER(?)
LIMIT 1
""")) {
ps.setString(1, ownerLogin);
ps.setString(2, remoteServerLogin);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
DmSyncPeerStateEntry e = new DmSyncPeerStateEntry();
e.setOwnerLogin(rs.getString("owner_login"));
e.setRemoteServerLogin(rs.getString("remote_server_login"));
e.setRemoteServerUrl(rs.getString("remote_server_url"));
e.setCursorStoredAtMs(rs.getLong("cursor_stored_at_ms"));
e.setCursorMessageKey(rs.getString("cursor_message_key"));
e.setBootstrapCompleted(rs.getBoolean("bootstrap_completed"));
long lastSyncAt = rs.getLong("last_sync_at_ms");
e.setLastSyncAtMs(rs.wasNull() ? null : lastSyncAt);
e.setLastError(rs.getString("last_error"));
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
return e;
}
}
}
private void touchRemoteUrl(String ownerLogin, String remoteServerLogin, String remoteServerUrl) throws SQLException {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement("""
UPDATE dm_sync_peer_state
SET remote_server_url = ?, updated_at_ms = ?
WHERE LOWER(owner_login) = LOWER(?)
AND LOWER(remote_server_login) = LOWER(?)
""")) {
ps.setString(1, remoteServerUrl);
ps.setLong(2, System.currentTimeMillis());
ps.setString(3, ownerLogin);
ps.setString(4, remoteServerLogin);
ps.executeUpdate();
}
}
private static String truncate(String value) {
if (value == null) return null;
String clean = value.trim();
if (clean.length() <= 500) return clean;
return clean.substring(0, 500);
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedMessagesDAO.java
+68
View File
@@ -416,6 +416,72 @@ public final class SignedMessagesDAO {
}
}
public SyncBatch listSyncBatch(
String ownerLogin,
long afterStoredAtMs,
String afterMessageKey,
int limit,
int maxBytes
) throws Exception {
int safeLimit = Math.max(1, Math.min(limit, 500));
int sqlLimit = safeLimit + 1;
String safeAfterMessageKey = afterMessageKey == null ? "" : afterMessageKey;
try (Connection c = db.getConnection()) {
String sql = """
SELECT
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, revision_time_ms, reencrypted_at_ms,
raw_block, created_at_ms, source_api, origin_session_id,
receipt_ref_base_key, receipt_ref_type, read_at_ms
FROM %s
WHERE (
LOWER(target_login) = LOWER(?)
OR (
message_type IN (5, 6, 7, 8)
AND (LOWER(from_login) = LOWER(?) OR LOWER(to_login) = LOWER(?))
)
)
AND (
created_at_ms > ?
OR (created_at_ms = ? AND (? = '' OR message_key > ?))
)
ORDER BY created_at_ms ASC, message_key ASC
LIMIT ?
""".formatted(messagesTable());
List<SignedMessageEntry> out = new ArrayList<>();
boolean hasMore = false;
int usedBytes = 0;
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ownerLogin);
ps.setString(2, ownerLogin);
ps.setString(3, ownerLogin);
ps.setLong(4, Math.max(0L, afterStoredAtMs));
ps.setLong(5, Math.max(0L, afterStoredAtMs));
ps.setString(6, safeAfterMessageKey);
ps.setString(7, safeAfterMessageKey);
ps.setInt(8, sqlLimit);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
SignedMessageEntry row = mapRow(rs);
byte[] raw = row.getRawBlock();
int rowBytes = raw == null ? 0 : raw.length;
if (out.size() >= safeLimit) {
hasMore = true;
break;
}
if (!out.isEmpty() && maxBytes > 0 && usedBytes + rowBytes > maxBytes) {
hasMore = true;
break;
}
out.add(row);
usedBytes += rowBytes;
}
}
}
return new SyncBatch(out, hasMore, usedBytes);
}
}
private void upsertMessage(Connection c, SignedMessageEntry e) throws SQLException {
String sql = """
INSERT INTO %s (
@@ -748,6 +814,8 @@ public final class SignedMessagesDAO {
}
}
public record SyncBatch(List<SignedMessageEntry> items, boolean hasMore, int rawBytes) {}
@FunctionalInterface
private interface SqlWork<T> {
T run() throws Exception;
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserAccessServersCurrentDAO.java
+20
View File
@@ -55,6 +55,26 @@ public final class UserAccessServersCurrentDAO {
return result;
}
public List<String> listUserLoginsByServerLogin(String serverLogin) throws SQLException {
String sql = """
SELECT user_login
FROM user_access_servers_current
WHERE LOWER(server_login) = LOWER(?)
ORDER BY user_login
""";
List<String> result = new ArrayList<>();
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, serverLogin);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
result.add(rs.getString("user_login"));
}
}
}
return result;
}
private UserAccessServerRouteEntry mapRow(ResultSet rs) throws SQLException {
UserAccessServerRouteEntry entry = new UserAccessServerRouteEntry();
entry.setUserLogin(rs.getString("user_login"));
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/DmSyncPeerStateEntry.java
+32
View File
@@ -0,0 +1,32 @@
package shine.db.entities;
public class DmSyncPeerStateEntry {
private String ownerLogin;
private String remoteServerLogin;
private String remoteServerUrl;
private long cursorStoredAtMs;
private String cursorMessageKey;
private boolean bootstrapCompleted;
private Long lastSyncAtMs;
private String lastError;
private long updatedAtMs;
public String getOwnerLogin() { return ownerLogin; }
public void setOwnerLogin(String ownerLogin) { this.ownerLogin = ownerLogin; }
public String getRemoteServerLogin() { return remoteServerLogin; }
public void setRemoteServerLogin(String remoteServerLogin) { this.remoteServerLogin = remoteServerLogin; }
public String getRemoteServerUrl() { return remoteServerUrl; }
public void setRemoteServerUrl(String remoteServerUrl) { this.remoteServerUrl = remoteServerUrl; }
public long getCursorStoredAtMs() { return cursorStoredAtMs; }
public void setCursorStoredAtMs(long cursorStoredAtMs) { this.cursorStoredAtMs = cursorStoredAtMs; }
public String getCursorMessageKey() { return cursorMessageKey; }
public void setCursorMessageKey(String cursorMessageKey) { this.cursorMessageKey = cursorMessageKey; }
public boolean isBootstrapCompleted() { return bootstrapCompleted; }
public void setBootstrapCompleted(boolean bootstrapCompleted) { this.bootstrapCompleted = bootstrapCompleted; }
public Long getLastSyncAtMs() { return lastSyncAtMs; }
public void setLastSyncAtMs(Long lastSyncAtMs) { this.lastSyncAtMs = lastSyncAtMs; }
public String getLastError() { return lastError; }
public void setLastError(String lastError) { this.lastError = lastError; }
public long getUpdatedAtMs() { return updatedAtMs; }
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
}
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v3.sql
+47
View File
@@ -0,0 +1,47 @@
BEGIN;
CREATE INDEX IF NOT EXISTS idx_signed_messages_sync_cursor
ON signed_messages(target_login, created_at_ms, message_key);
CREATE INDEX IF NOT EXISTS idx_signed_messages_sync_cursor_lower
ON signed_messages(LOWER(target_login), created_at_ms, message_key);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_from_sync_cursor
ON signed_messages(from_login, created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_to_sync_cursor
ON signed_messages(to_login, created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_from_sync_cursor_lower
ON signed_messages(LOWER(from_login), created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_to_sync_cursor_lower
ON signed_messages(LOWER(to_login), created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE TABLE IF NOT EXISTS dm_sync_peer_state (
owner_login TEXT NOT NULL,
remote_server_login TEXT NOT NULL,
remote_server_url TEXT NOT NULL,
cursor_stored_at_ms BIGINT NOT NULL DEFAULT 0,
cursor_message_key TEXT NOT NULL DEFAULT '',
bootstrap_completed BOOLEAN NOT NULL DEFAULT FALSE,
last_sync_at_ms BIGINT,
last_error TEXT,
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_login, remote_server_login)
);
CREATE INDEX IF NOT EXISTS idx_dm_sync_peer_state_owner
ON dm_sync_peer_state(owner_login);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 3, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
ON CONFLICT (id) DO UPDATE SET
schema_version = EXCLUDED.schema_version,
updated_at_ms = EXCLUDED.updated_at_ms;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
+39 -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, 2, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
VALUES (1, 3, 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;
@@ -652,6 +652,28 @@ CREATE INDEX IF NOT EXISTS idx_signed_messages_target
CREATE INDEX IF NOT EXISTS idx_signed_messages_base
ON signed_messages(base_key, message_type);
CREATE INDEX IF NOT EXISTS idx_signed_messages_sync_cursor
ON signed_messages(target_login, created_at_ms, message_key);
CREATE INDEX IF NOT EXISTS idx_signed_messages_sync_cursor_lower
ON signed_messages(LOWER(target_login), created_at_ms, message_key);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_from_sync_cursor
ON signed_messages(from_login, created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_to_sync_cursor
ON signed_messages(to_login, created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_from_sync_cursor_lower
ON signed_messages(LOWER(from_login), created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE INDEX IF NOT EXISTS idx_signed_messages_delete_to_sync_cursor_lower
ON signed_messages(LOWER(to_login), created_at_ms, message_key)
WHERE message_type IN (5, 6, 7, 8);
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_receipt_incoming
ON signed_messages(target_login, receipt_ref_base_key)
WHERE message_type = 3 AND receipt_ref_base_key IS NOT NULL;
@@ -672,6 +694,22 @@ CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
ON signed_message_session_delivery(session_id, delivered);
CREATE TABLE IF NOT EXISTS dm_sync_peer_state (
owner_login TEXT NOT NULL,
remote_server_login TEXT NOT NULL,
remote_server_url TEXT NOT NULL,
cursor_stored_at_ms BIGINT NOT NULL DEFAULT 0,
cursor_message_key TEXT NOT NULL DEFAULT '',
bootstrap_completed BOOLEAN NOT NULL DEFAULT FALSE,
last_sync_at_ms BIGINT,
last_error TEXT,
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_login, remote_server_login)
);
CREATE INDEX IF NOT EXISTS idx_dm_sync_peer_state_owner
ON dm_sync_peer_state(owner_login);
CREATE TABLE IF NOT EXISTS message_views_state (
viewer_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,