SHA256
WIP: новая схема сообщений и push (не проверено)
This commit is contained in:
AidarKC
@@ -496,6 +496,56 @@ public final class DatabaseInitializer {
|
||||
ON signed_direct_messages_history (to_login, created_at_ms);
|
||||
""");
|
||||
|
||||
// 13) signed_messages_v2 (универсальное хранилище блоков типов 1/2/3/4)
|
||||
st.executeUpdate("""
|
||||
CREATE TABLE IF NOT EXISTS signed_messages_v2 (
|
||||
message_key TEXT NOT NULL PRIMARY KEY,
|
||||
base_key TEXT NOT NULL,
|
||||
target_login TEXT NOT NULL,
|
||||
from_login TEXT NOT NULL,
|
||||
to_login TEXT NOT NULL,
|
||||
time_ms INTEGER NOT NULL,
|
||||
nonce INTEGER NOT NULL,
|
||||
message_type INTEGER NOT NULL,
|
||||
raw_block BLOB NOT NULL,
|
||||
created_at_ms INTEGER NOT NULL,
|
||||
source_api TEXT NOT NULL,
|
||||
origin_session_id TEXT,
|
||||
receipt_ref_base_key TEXT,
|
||||
receipt_ref_type INTEGER,
|
||||
FOREIGN KEY (from_login) REFERENCES solana_users(login),
|
||||
FOREIGN KEY (to_login) REFERENCES solana_users(login)
|
||||
);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_signed_messages_v2_target
|
||||
ON signed_messages_v2 (target_login, time_ms, created_at_ms);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_signed_messages_v2_base
|
||||
ON signed_messages_v2 (base_key, message_type);
|
||||
""");
|
||||
|
||||
// 14) signed_message_session_delivery (доставка по сессиям)
|
||||
st.executeUpdate("""
|
||||
CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
|
||||
message_key TEXT NOT NULL,
|
||||
session_id TEXT NOT NULL,
|
||||
delivered INTEGER NOT NULL DEFAULT 0,
|
||||
delivered_at_ms INTEGER,
|
||||
created_at_ms INTEGER NOT NULL,
|
||||
PRIMARY KEY (message_key, session_id),
|
||||
FOREIGN KEY (message_key) REFERENCES signed_messages_v2(message_key)
|
||||
);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
|
||||
ON signed_message_session_delivery (session_id, delivered);
|
||||
""");
|
||||
|
||||
DatabaseTriggersInstaller.createAllTriggers(st);
|
||||
}
|
||||
}
|
||||
|
||||
@@ -0,0 +1,182 @@
|
||||
package shine.db.dao;
|
||||
|
||||
import shine.db.SqliteDbController;
|
||||
import shine.db.entities.SignedMessageV2Entry;
|
||||
|
||||
import java.sql.Connection;
|
||||
import java.sql.PreparedStatement;
|
||||
import java.sql.ResultSet;
|
||||
import java.util.ArrayList;
|
||||
import java.util.List;
|
||||
|
||||
public final class SignedMessagesV2DAO {
|
||||
private static volatile SignedMessagesV2DAO instance;
|
||||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||||
|
||||
private SignedMessagesV2DAO() {}
|
||||
|
||||
public static SignedMessagesV2DAO getInstance() {
|
||||
if (instance == null) {
|
||||
synchronized (SignedMessagesV2DAO.class) {
|
||||
if (instance == null) instance = new SignedMessagesV2DAO();
|
||||
}
|
||||
}
|
||||
return instance;
|
||||
}
|
||||
|
||||
public boolean insertIfAbsent(SignedMessageV2Entry e) throws Exception {
|
||||
try (Connection c = db.getConnection()) {
|
||||
String sql = """
|
||||
INSERT OR IGNORE INTO signed_messages_v2 (
|
||||
message_key, base_key, target_login, from_login, to_login,
|
||||
time_ms, nonce, message_type, raw_block, created_at_ms,
|
||||
source_api, origin_session_id, receipt_ref_base_key, receipt_ref_type
|
||||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setString(1, e.getMessageKey());
|
||||
ps.setString(2, e.getBaseKey());
|
||||
ps.setString(3, e.getTargetLogin());
|
||||
ps.setString(4, e.getFromLogin());
|
||||
ps.setString(5, e.getToLogin());
|
||||
ps.setLong(6, e.getTimeMs());
|
||||
ps.setLong(7, e.getNonce());
|
||||
ps.setInt(8, e.getMessageType());
|
||||
ps.setBytes(9, e.getRawBlock());
|
||||
ps.setLong(10, e.getCreatedAtMs());
|
||||
ps.setString(11, e.getSourceApi());
|
||||
ps.setString(12, e.getOriginSessionId());
|
||||
ps.setString(13, e.getReceiptRefBaseKey());
|
||||
if (e.getReceiptRefType() == null) ps.setObject(14, null);
|
||||
else ps.setInt(14, e.getReceiptRefType());
|
||||
return ps.executeUpdate() > 0;
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
public SignedMessageV2Entry getByMessageKey(String messageKey) throws Exception {
|
||||
try (Connection c = db.getConnection()) {
|
||||
String sql = """
|
||||
SELECT
|
||||
message_key, base_key, target_login, from_login, to_login,
|
||||
time_ms, nonce, message_type, raw_block, created_at_ms,
|
||||
source_api, origin_session_id, receipt_ref_base_key, receipt_ref_type
|
||||
FROM signed_messages_v2
|
||||
WHERE message_key = ?
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setString(1, messageKey);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
if (!rs.next()) return null;
|
||||
return mapRow(rs);
|
||||
}
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
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();
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
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();
|
||||
}
|
||||
|
||||
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, int limit) 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.target_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.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.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.created_at_ms ASC
|
||||
LIMIT ?
|
||||
""";
|
||||
List<SignedMessageV2Entry> out = new ArrayList<>();
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setString(1, sessionId);
|
||||
ps.setInt(2, Math.max(1, limit));
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
while (rs.next()) out.add(mapRow(rs));
|
||||
}
|
||||
}
|
||||
return out;
|
||||
}
|
||||
}
|
||||
|
||||
private SignedMessageV2Entry mapRow(ResultSet rs) throws Exception {
|
||||
SignedMessageV2Entry e = new SignedMessageV2Entry();
|
||||
e.setMessageKey(rs.getString("message_key"));
|
||||
e.setBaseKey(rs.getString("base_key"));
|
||||
e.setTargetLogin(rs.getString("target_login"));
|
||||
e.setFromLogin(rs.getString("from_login"));
|
||||
e.setToLogin(rs.getString("to_login"));
|
||||
e.setTimeMs(rs.getLong("time_ms"));
|
||||
e.setNonce(rs.getLong("nonce"));
|
||||
e.setMessageType(rs.getInt("message_type"));
|
||||
e.setRawBlock(rs.getBytes("raw_block"));
|
||||
e.setCreatedAtMs(rs.getLong("created_at_ms"));
|
||||
e.setSourceApi(rs.getString("source_api"));
|
||||
e.setOriginSessionId(rs.getString("origin_session_id"));
|
||||
e.setReceiptRefBaseKey(rs.getString("receipt_ref_base_key"));
|
||||
int maybeRefType = rs.getInt("receipt_ref_type");
|
||||
e.setReceiptRefType(rs.wasNull() ? null : maybeRefType);
|
||||
return e;
|
||||
}
|
||||
}
|
||||
@@ -0,0 +1,47 @@
|
||||
package shine.db.entities;
|
||||
|
||||
public class SignedMessageV2Entry {
|
||||
private String messageKey;
|
||||
private String baseKey;
|
||||
private String targetLogin;
|
||||
private String fromLogin;
|
||||
private String toLogin;
|
||||
private long timeMs;
|
||||
private long nonce;
|
||||
private int messageType;
|
||||
private byte[] rawBlock;
|
||||
private long createdAtMs;
|
||||
private String sourceApi;
|
||||
private String originSessionId;
|
||||
private String receiptRefBaseKey;
|
||||
private Integer receiptRefType;
|
||||
|
||||
public String getMessageKey() { return messageKey; }
|
||||
public void setMessageKey(String messageKey) { this.messageKey = messageKey; }
|
||||
public String getBaseKey() { return baseKey; }
|
||||
public void setBaseKey(String baseKey) { this.baseKey = baseKey; }
|
||||
public String getTargetLogin() { return targetLogin; }
|
||||
public void setTargetLogin(String targetLogin) { this.targetLogin = targetLogin; }
|
||||
public String getFromLogin() { return fromLogin; }
|
||||
public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
|
||||
public String getToLogin() { return toLogin; }
|
||||
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
|
||||
public long getTimeMs() { return timeMs; }
|
||||
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
|
||||
public long getNonce() { return nonce; }
|
||||
public void setNonce(long nonce) { this.nonce = nonce; }
|
||||
public int getMessageType() { return messageType; }
|
||||
public void setMessageType(int messageType) { this.messageType = messageType; }
|
||||
public byte[] getRawBlock() { return rawBlock; }
|
||||
public void setRawBlock(byte[] rawBlock) { this.rawBlock = rawBlock; }
|
||||
public long getCreatedAtMs() { return createdAtMs; }
|
||||
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
|
||||
public String getSourceApi() { return sourceApi; }
|
||||
public void setSourceApi(String sourceApi) { this.sourceApi = sourceApi; }
|
||||
public String getOriginSessionId() { return originSessionId; }
|
||||
public void setOriginSessionId(String originSessionId) { this.originSessionId = originSessionId; }
|
||||
public String getReceiptRefBaseKey() { return receiptRefBaseKey; }
|
||||
public void setReceiptRefBaseKey(String receiptRefBaseKey) { this.receiptRefBaseKey = receiptRefBaseKey; }
|
||||
public Integer getReceiptRefType() { return receiptRefType; }
|
||||
public void setReceiptRefType(Integer receiptRefType) { this.receiptRefType = receiptRefType; }
|
||||
}
|
||||
Reference in New Issue
Block a user