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

Improve direct message history and SQLite resilience

Browse Source
This commit is contained in:
AidarKC
2026-07-22 13:09:42 +04:00
parent 2f3b1571e5
commit 93673e5786
18 changed files with 908 additions and 233 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
+1
View File
@@ -641,6 +641,7 @@ public final class DatabaseInitializer {
origin_session_id TEXT,
receipt_ref_base_key TEXT,
receipt_ref_type INTEGER,
read_at_ms INTEGER,
FOREIGN KEY (from_login) REFERENCES solana_users(login),
FOREIGN KEY (to_login) REFERENCES solana_users(login)
);
SHiNE-server/shine-server-db/src/main/java/shine/db/SqliteDbController.java
+49 -1
View File
@@ -14,7 +14,7 @@ import java.sql.Statement;
public final class SqliteDbController {
private static volatile SqliteDbController instance;
private static final int LATEST_SCHEMA_VERSION = 11;
private static final int LATEST_SCHEMA_VERSION = 12;
private final String jdbcUrl;
@@ -94,6 +94,7 @@ public final class SqliteDbController {
case 9 -> migrateToV9();
case 10 -> migrateToV10();
case 11 -> migrateToV11();
case 12 -> migrateToV12();
default -> throw new RuntimeException("Unknown DB migration target version: " + targetVersion);
}
}
@@ -329,6 +330,26 @@ public final class SqliteDbController {
}
}
private void migrateToV12() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureSignedMessagesReadAtColumn(c, st);
backfillSignedMessagesReadAt(st);
setSchemaVersion(c, 12);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v12 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v12 failed", e);
}
}
private static void ensureChat200StateTables(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS chat200_state (
@@ -463,6 +484,33 @@ public final class SqliteDbController {
}
}
private static void ensureSignedMessagesReadAtColumn(Connection c, Statement st) throws SQLException {
if (!tableExists(c, "signed_messages_v2")) return;
if (!columnExists(c, "signed_messages_v2", "read_at_ms")) {
st.executeUpdate("ALTER TABLE signed_messages_v2 ADD COLUMN read_at_ms INTEGER");
}
}
private static void backfillSignedMessagesReadAt(Statement st) throws SQLException {
st.executeUpdate("""
UPDATE signed_messages_v2 AS content
SET read_at_ms = (
SELECT MIN(receipt.time_ms)
FROM signed_messages_v2 AS receipt
WHERE receipt.message_type IN (3, 4)
AND receipt.receipt_ref_base_key = content.base_key
)
WHERE content.message_type IN (1, 2)
AND (content.read_at_ms IS NULL OR content.read_at_ms <= 0)
AND EXISTS (
SELECT 1
FROM signed_messages_v2 AS receipt
WHERE receipt.message_type IN (3, 4)
AND receipt.receipt_ref_base_key = content.base_key
);
""");
}
/**
* Временная одноразовая миграция на переходе к SHiNE_DM v1:
* старые строки signed_messages_v2 больше не гарантированно совместимы
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedMessagesV2DAO.java
+377 -214
View File
@@ -7,10 +7,14 @@ import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public final class SignedMessagesV2DAO {
private static final int SQLITE_BUSY_MAX_RETRIES = 6;
private static final long SQLITE_BUSY_RETRY_BASE_DELAY_MS = 40L;
public enum ApplyStatus {
APPLIED,
DUPLICATE_OR_OLDER,
@@ -37,174 +41,195 @@ public final class SignedMessagesV2DAO {
}
public ApplyStatus insertIfAbsent(SignedMessageV2Entry e) throws Exception {
try (Connection c = db.getConnection()) {
if (isBlockedByConversationDelete(c, e.getFromLogin(), e.getToLogin(), e.getTimeMs())) {
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
if (isBlockedByConversationDelete(c, e.getFromLogin(), e.getToLogin(), e.getTimeMs())) {
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
String sql = """
INSERT OR IGNORE INTO signed_messages_v2 (
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
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
ApplyStatus status = ps.executeUpdate() > 0 ? ApplyStatus.APPLIED : ApplyStatus.DUPLICATE_OR_OLDER;
if (status.applied()) {
markMessageReadByReceipt(c, e);
}
return status;
}
}
String sql = """
INSERT OR IGNORE INTO signed_messages_v2 (
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
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
return ps.executeUpdate() > 0 ? ApplyStatus.APPLIED : ApplyStatus.DUPLICATE_OR_OLDER;
}
}
});
}
public boolean insertPairBothOrNothing(SignedMessageV2Entry first, SignedMessageV2Entry second) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
int insertedFirst = insertStrict(c, first);
int insertedSecond = insertStrict(c, second);
if (insertedFirst == 1 && insertedSecond == 1) {
c.commit();
return true;
}
c.rollback();
return false;
} catch (SQLException sqlEx) {
try { c.rollback(); } catch (Exception ignored) {}
if (isConstraintViolation(sqlEx)) {
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
int insertedFirst = insertStrict(c, first);
int insertedSecond = insertStrict(c, second);
if (insertedFirst == 1 && insertedSecond == 1) {
markMessageReadByReceipt(c, first);
markMessageReadByReceipt(c, second);
c.commit();
return true;
}
c.rollback();
return false;
} catch (SQLException sqlEx) {
try { c.rollback(); } catch (Exception ignored) {}
if (isConstraintViolation(sqlEx)) {
return false;
}
throw sqlEx;
} finally {
c.setAutoCommit(prevAutoCommit);
}
throw sqlEx;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
});
}
public ApplyStatus upsertContentPair(SignedMessageV2Entry incoming, SignedMessageV2Entry outgoing) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, incoming.getFromLogin(), incoming.getToLogin(), incoming.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, incoming.getBaseKey())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_MESSAGE_TOMBSTONE;
}
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, incoming.getFromLogin(), incoming.getToLogin(), incoming.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, incoming.getBaseKey())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_MESSAGE_TOMBSTONE;
}
RevisionMarker currentMarker = getCurrentContentMarker(c, incoming.getBaseKey());
RevisionMarker nextMarker = RevisionMarker.of(incoming);
if (currentMarker != null && compareMarkers(nextMarker, currentMarker) <= 0) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
RevisionMarker currentMarker = getCurrentContentMarker(c, incoming.getBaseKey());
RevisionMarker nextMarker = RevisionMarker.of(incoming);
if (currentMarker != null && compareMarkers(nextMarker, currentMarker) <= 0) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
upsertMessage(c, incoming);
upsertMessage(c, outgoing);
markMessageReadByReceipt(c, incoming);
markMessageReadByReceipt(c, outgoing);
resetDeliveryRows(c, incoming.getMessageKey());
resetDeliveryRows(c, outgoing.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
upsertMessage(c, incoming);
upsertMessage(c, outgoing);
resetDeliveryRows(c, incoming.getMessageKey());
resetDeliveryRows(c, outgoing.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
});
}
public ApplyStatus upsertIncomingCopy(SignedMessageV2Entry incoming) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, incoming.getFromLogin(), incoming.getToLogin(), incoming.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, incoming.getBaseKey())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_MESSAGE_TOMBSTONE;
}
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, incoming.getFromLogin(), incoming.getToLogin(), incoming.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, incoming.getBaseKey())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_MESSAGE_TOMBSTONE;
}
RevisionMarker currentMarker = getRevisionMarkerByMessageKey(c, incoming.getMessageKey());
RevisionMarker nextMarker = RevisionMarker.of(incoming);
if (currentMarker != null && compareMarkers(nextMarker, currentMarker) <= 0) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
RevisionMarker currentMarker = getRevisionMarkerByMessageKey(c, incoming.getMessageKey());
RevisionMarker nextMarker = RevisionMarker.of(incoming);
if (currentMarker != null && compareMarkers(nextMarker, currentMarker) <= 0) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
upsertMessage(c, incoming);
resetDeliveryRows(c, incoming.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
upsertMessage(c, incoming);
markMessageReadByReceipt(c, incoming);
resetDeliveryRows(c, incoming.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
}
});
}
public ApplyStatus applyDeleteMessage(SignedMessageV2Entry tombstone) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, tombstone.getFromLogin(), tombstone.getToLogin(), tombstone.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, tombstone.getBaseKey())) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
if (isBlockedByConversationDelete(c, tombstone.getFromLogin(), tombstone.getToLogin(), tombstone.getTimeMs())) {
c.rollback();
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
if (hasMessageDeleteTombstone(c, tombstone.getBaseKey())) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
deleteMessageContentAndReceipts(c, tombstone.getBaseKey());
upsertMessage(c, tombstone);
resetDeliveryRows(c, tombstone.getMessageKey());
deleteMessageContentAndReceipts(c, tombstone.getBaseKey());
upsertMessage(c, tombstone);
resetDeliveryRows(c, tombstone.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
}
});
}
public ApplyStatus applyDeleteConversation(SignedMessageV2Entry tombstone) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
Long currentBoundary = getLatestConversationDeleteBoundary(c, tombstone.getFromLogin(), tombstone.getToLogin());
if (currentBoundary != null && tombstone.getTimeMs() <= currentBoundary) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
Long currentBoundary = getLatestConversationDeleteBoundary(c, tombstone.getFromLogin(), tombstone.getToLogin());
if (currentBoundary != null && tombstone.getTimeMs() <= currentBoundary) {
c.rollback();
return ApplyStatus.DUPLICATE_OR_OLDER;
}
deleteConversationHistoryBefore(c, tombstone.getFromLogin(), tombstone.getToLogin(), tombstone.getTimeMs());
upsertMessage(c, tombstone);
resetDeliveryRows(c, tombstone.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
deleteConversationHistoryBefore(c, tombstone.getFromLogin(), tombstone.getToLogin(), tombstone.getTimeMs());
upsertMessage(c, tombstone);
resetDeliveryRows(c, tombstone.getMessageKey());
c.commit();
return ApplyStatus.APPLIED;
} catch (Exception ex) {
try { c.rollback(); } catch (Exception ignored) {}
throw ex;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
});
}
public SignedMessageV2Entry getByMessageKey(String messageKey) throws Exception {
@@ -214,7 +239,7 @@ public final class SignedMessagesV2DAO {
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
receipt_ref_base_key, receipt_ref_type, read_at_ms
FROM signed_messages_v2
WHERE message_key = ?
""";
@@ -235,90 +260,153 @@ public final class SignedMessagesV2DAO {
}
public void ensureDeliveryRow(String messageKey, String sessionId, long nowMs) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, nowMs);
ps.executeUpdate();
ensureDeliveryRows(messageKey, List.of(sessionId), nowMs);
}
public void ensureDeliveryRows(String messageKey, List<String> sessionIds, long nowMs) throws Exception {
if (sessionIds == null || sessionIds.isEmpty()) return;
withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
for (String sessionId : sessionIds) {
if (sessionId == null || sessionId.isBlank()) continue;
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, nowMs);
ps.addBatch();
}
ps.executeBatch();
}
return null;
}
}
});
}
public void markDelivered(String messageKey, String sessionId, long deliveredAtMs) throws Exception {
try (Connection c = db.getConnection()) {
String insertSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""";
try (PreparedStatement ps = c.prepareStatement(insertSql)) {
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, deliveredAtMs);
ps.executeUpdate();
withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 1, ?, ?)
ON CONFLICT(message_key, session_id) DO UPDATE SET
delivered = 1,
delivered_at_ms = CASE
WHEN signed_message_session_delivery.delivered_at_ms IS NULL THEN excluded.delivered_at_ms
WHEN signed_message_session_delivery.delivered_at_ms > excluded.delivered_at_ms THEN excluded.delivered_at_ms
ELSE signed_message_session_delivery.delivered_at_ms
END
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, deliveredAtMs);
ps.setLong(4, deliveredAtMs);
ps.executeUpdate();
}
return null;
}
String updateSql = """
UPDATE signed_message_session_delivery
SET delivered = 1, delivered_at_ms = ?
WHERE message_key = ? AND session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(updateSql)) {
ps.setLong(1, deliveredAtMs);
ps.setString(2, messageKey);
ps.setString(3, sessionId);
ps.executeUpdate();
}
}
});
}
public List<SignedMessageV2Entry> listPendingForSession(String login, String sessionId) throws Exception {
try (Connection c = db.getConnection()) {
String fillSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
)
SELECT m.message_key, ?, 0, NULL, ?
FROM signed_messages_v2 m
WHERE (
(m.message_type IN (1, 3) AND m.to_login = ? COLLATE NOCASE)
OR (m.message_type IN (2, 4) AND m.from_login = ? COLLATE NOCASE)
OR (m.message_type IN (5, 6, 7, 8)
AND (m.from_login = ? COLLATE NOCASE OR m.to_login = ? COLLATE NOCASE))
)
""";
long now = System.currentTimeMillis();
try (PreparedStatement ps = c.prepareStatement(fillSql)) {
ps.setString(1, sessionId);
ps.setLong(2, now);
ps.setString(3, login);
ps.setString(4, login);
ps.setString(5, login);
ps.setString(6, login);
ps.executeUpdate();
}
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String fillSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
)
SELECT m.message_key, ?, 0, NULL, ?
FROM signed_messages_v2 m
WHERE (
(m.message_type IN (1, 3) AND m.to_login = ? COLLATE NOCASE)
OR (m.message_type IN (2, 4) AND m.from_login = ? COLLATE NOCASE)
OR (m.message_type IN (5, 6, 7, 8)
AND (m.from_login = ? COLLATE NOCASE OR m.to_login = ? COLLATE NOCASE))
)
""";
long now = System.currentTimeMillis();
try (PreparedStatement ps = c.prepareStatement(fillSql)) {
ps.setString(1, sessionId);
ps.setLong(2, now);
ps.setString(3, login);
ps.setString(4, login);
ps.setString(5, login);
ps.setString(6, login);
ps.executeUpdate();
}
String sql = """
SELECT
m.message_key, m.base_key, m.target_login, m.from_login, m.to_login,
m.time_ms, m.nonce, m.message_type, m.revision_time_ms, m.reencrypted_at_ms,
m.raw_block, m.created_at_ms, m.source_api, m.origin_session_id,
m.receipt_ref_base_key, m.receipt_ref_type, m.read_at_ms
FROM signed_messages_v2 m
JOIN signed_message_session_delivery d
ON d.message_key = m.message_key
WHERE d.session_id = ? AND d.delivered = 0
ORDER BY m.time_ms ASC, m.revision_time_ms ASC, m.reencrypted_at_ms ASC, m.created_at_ms ASC
""";
List<SignedMessageV2Entry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(mapRow(rs));
}
}
return out;
}
});
}
public List<SignedMessageV2Entry> listConversationPage(
String login,
String peerLogin,
long beforeTimeMs,
String beforeMessageKey,
int limit
) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT
m.message_key, m.base_key, m.target_login, m.from_login, m.to_login,
m.time_ms, m.nonce, m.message_type, m.revision_time_ms, m.reencrypted_at_ms,
m.raw_block, m.created_at_ms, m.source_api, m.origin_session_id,
m.receipt_ref_base_key, m.receipt_ref_type
FROM signed_messages_v2 m
JOIN signed_message_session_delivery d
ON d.message_key = m.message_key
WHERE d.session_id = ? AND d.delivered = 0
ORDER BY m.time_ms ASC, m.revision_time_ms ASC, m.reencrypted_at_ms ASC, m.created_at_ms ASC
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 signed_messages_v2
WHERE target_login = ? COLLATE NOCASE
AND message_type IN (1, 2)
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
)
AND (
? <= 0
OR time_ms < ?
OR (time_ms = ? AND (? = '' OR message_key < ?))
)
ORDER BY time_ms DESC, message_key DESC
LIMIT ?
""";
List<SignedMessageV2Entry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
ps.setString(1, login);
ps.setString(2, login);
ps.setString(3, peerLogin);
ps.setString(4, peerLogin);
ps.setString(5, login);
ps.setLong(6, beforeTimeMs);
ps.setLong(7, beforeTimeMs);
ps.setLong(8, beforeTimeMs);
ps.setString(9, beforeMessageKey == null ? "" : beforeMessageKey);
ps.setString(10, beforeMessageKey == null ? "" : beforeMessageKey);
ps.setInt(11, limit);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(mapRow(rs));
}
@@ -333,8 +421,8 @@ public final class SignedMessagesV2DAO {
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
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
receipt_ref_base_key, receipt_ref_type, read_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(message_key) DO UPDATE SET
base_key = excluded.base_key,
target_login = excluded.target_login,
@@ -350,7 +438,8 @@ public final class SignedMessagesV2DAO {
source_api = excluded.source_api,
origin_session_id = excluded.origin_session_id,
receipt_ref_base_key = excluded.receipt_ref_base_key,
receipt_ref_type = excluded.receipt_ref_type
receipt_ref_type = excluded.receipt_ref_type,
read_at_ms = COALESCE(signed_messages_v2.read_at_ms, excluded.read_at_ms)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
@@ -358,6 +447,32 @@ public final class SignedMessagesV2DAO {
}
}
private void markMessageReadByReceipt(Connection c, SignedMessageV2Entry entry) throws SQLException {
if (entry == null) return;
int messageType = entry.getMessageType();
if (messageType != 3 && messageType != 4) return;
String receiptRefBaseKey = String.valueOf(entry.getReceiptRefBaseKey() == null ? "" : entry.getReceiptRefBaseKey()).trim();
if (receiptRefBaseKey.isEmpty()) return;
long readAtMs = entry.getTimeMs();
if (readAtMs <= 0) return;
try (PreparedStatement ps = c.prepareStatement("""
UPDATE signed_messages_v2
SET read_at_ms = CASE
WHEN read_at_ms IS NULL OR read_at_ms <= 0 THEN ?
WHEN read_at_ms > ? THEN ?
ELSE read_at_ms
END
WHERE base_key = ?
AND message_type IN (1, 2)
""")) {
ps.setLong(1, readAtMs);
ps.setLong(2, readAtMs);
ps.setLong(3, readAtMs);
ps.setString(4, receiptRefBaseKey);
ps.executeUpdate();
}
}
private RevisionMarker getRevisionMarkerByMessageKey(Connection c, String messageKey) throws SQLException {
String sql = """
SELECT revision_time_ms, reencrypted_at_ms
@@ -536,8 +651,8 @@ public final class SignedMessagesV2DAO {
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
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
receipt_ref_base_key, receipt_ref_type, read_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
@@ -563,6 +678,8 @@ public final class SignedMessagesV2DAO {
ps.setString(15, e.getReceiptRefBaseKey());
if (e.getReceiptRefType() == null) ps.setObject(16, null);
else ps.setInt(16, e.getReceiptRefType());
if (e.getReadAtMs() == null) ps.setObject(17, null);
else ps.setLong(17, e.getReadAtMs());
}
private void bindObjects(PreparedStatement ps, Object... bindValues) throws SQLException {
@@ -586,6 +703,45 @@ public final class SignedMessagesV2DAO {
return msg.contains("constraint") || msg.contains("unique") || msg.contains("primary key");
}
private boolean isBusyLock(SQLException ex) {
Throwable current = ex;
while (current != null) {
String msg = String.valueOf(current.getMessage()).toLowerCase();
if (msg.contains("sqlite_busy") || msg.contains("database is locked") || msg.contains("database table is locked")) {
return true;
}
current = current.getCause();
}
return false;
}
private void sleepBeforeBusyRetry(int attempt) throws SQLException {
long delayMs = SQLITE_BUSY_RETRY_BASE_DELAY_MS * (1L << Math.min(attempt, 4));
try {
Thread.sleep(delayMs);
} catch (InterruptedException ie) {
Thread.currentThread().interrupt();
SQLException sqlEx = new SQLException("Interrupted while retrying SQLite busy lock", ie);
throw sqlEx;
}
}
private <T> T withBusyRetry(SqlWork<T> work) throws Exception {
SQLException lastBusy = null;
for (int attempt = 0; attempt < SQLITE_BUSY_MAX_RETRIES; attempt++) {
try {
return work.run();
} catch (SQLException ex) {
if (!isBusyLock(ex) || attempt >= SQLITE_BUSY_MAX_RETRIES - 1) {
throw ex;
}
lastBusy = ex;
sleepBeforeBusyRetry(attempt);
}
}
throw lastBusy == null ? new SQLException("SQLite busy retry failed") : lastBusy;
}
private int compareMarkers(RevisionMarker left, RevisionMarker right) {
int revisionCompare = Long.compare(left.revisionTimeMs, right.revisionTimeMs);
if (revisionCompare != 0) return revisionCompare;
@@ -611,6 +767,8 @@ public final class SignedMessagesV2DAO {
e.setReceiptRefBaseKey(rs.getString("receipt_ref_base_key"));
int maybeRefType = rs.getInt("receipt_ref_type");
e.setReceiptRefType(rs.wasNull() ? null : maybeRefType);
long maybeReadAt = rs.getLong("read_at_ms");
e.setReadAtMs(rs.wasNull() ? null : maybeReadAt);
return e;
}
@@ -619,4 +777,9 @@ public final class SignedMessagesV2DAO {
return new RevisionMarker(entry.getRevisionTimeMs(), entry.getReencryptedAtMs());
}
}
@FunctionalInterface
private interface SqlWork<T> {
T run() throws Exception;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/SignedMessageV2Entry.java
+3
View File
@@ -17,6 +17,7 @@ public class SignedMessageV2Entry {
private String originSessionId;
private String receiptRefBaseKey;
private Integer receiptRefType;
private Long readAtMs;
public String getMessageKey() { return messageKey; }
public void setMessageKey(String messageKey) { this.messageKey = messageKey; }
@@ -50,4 +51,6 @@ public class SignedMessageV2Entry {
public void setReceiptRefBaseKey(String receiptRefBaseKey) { this.receiptRefBaseKey = receiptRefBaseKey; }
public Integer getReceiptRefType() { return receiptRefType; }
public void setReceiptRefType(Integer receiptRefType) { this.receiptRefType = receiptRefType; }
public Long getReadAtMs() { return readAtMs; }
public void setReadAtMs(Long readAtMs) { this.readAtMs = readAtMs; }
}