2833 lines
104 KiB
Plaintext
2833 lines
104 KiB
Plaintext
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.ActiveSessionEntry;
|
||
|
||
import java.sql.*;
|
||
import java.util.ArrayList;
|
||
import java.util.List;
|
||
|
||
/**
|
||
* DAO для таблицы active_sessions.
|
||
*
|
||
* Правило:
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*/
|
||
public final class ActiveSessionsDAO {
|
||
|
||
private static volatile ActiveSessionsDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private ActiveSessionsDAO() { }
|
||
|
||
public static ActiveSessionsDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (ActiveSessionsDAO.class) {
|
||
if (instance == null) instance = new ActiveSessionsDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
// -------------------- INSERT --------------------
|
||
|
||
public void insert(Connection c, ActiveSessionEntry session) throws SQLException {
|
||
String sql = """
|
||
INSERT INTO active_sessions (
|
||
session_id,
|
||
login,
|
||
session_key,
|
||
storage_pwd,
|
||
session_created_at_ms,
|
||
last_authirificated_at_ms,
|
||
push_endpoint,
|
||
push_p256dh_key,
|
||
push_auth_key,
|
||
client_ip,
|
||
client_info_from_client,
|
||
client_info_from_request,
|
||
user_language
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, session.getSessionId());
|
||
ps.setString(2, session.getLogin());
|
||
ps.setString(3, session.getSessionKey());
|
||
ps.setString(4, session.getStoragePwd());
|
||
ps.setLong(5, session.getSessionCreatedAtMs());
|
||
ps.setLong(6, session.getLastAuthirificatedAtMs());
|
||
ps.setString(7, session.getPushEndpoint());
|
||
ps.setString(8, session.getPushP256dhKey());
|
||
ps.setString(9, session.getPushAuthKey());
|
||
ps.setString(10, session.getClientIp());
|
||
ps.setString(11, session.getClientInfoFromClient());
|
||
ps.setString(12, session.getClientInfoFromRequest());
|
||
ps.setString(13, session.getUserLanguage());
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
public void insert(ActiveSessionEntry session) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
insert(c, session);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT --------------------
|
||
|
||
public ActiveSessionEntry getBySessionId(Connection c, String sessionId) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
session_id,
|
||
login,
|
||
session_key,
|
||
storage_pwd,
|
||
session_created_at_ms,
|
||
last_authirificated_at_ms,
|
||
push_endpoint,
|
||
push_p256dh_key,
|
||
push_auth_key,
|
||
client_ip,
|
||
client_info_from_client,
|
||
client_info_from_request,
|
||
user_language
|
||
FROM active_sessions
|
||
WHERE session_id = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, sessionId);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
public ActiveSessionEntry getBySessionId(String sessionId) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getBySessionId(c, sessionId);
|
||
}
|
||
}
|
||
|
||
public List<ActiveSessionEntry> getByLogin(Connection c, String login) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
session_id,
|
||
login,
|
||
session_key,
|
||
storage_pwd,
|
||
session_created_at_ms,
|
||
last_authirificated_at_ms,
|
||
push_endpoint,
|
||
push_p256dh_key,
|
||
push_auth_key,
|
||
client_ip,
|
||
client_info_from_client,
|
||
client_info_from_request,
|
||
user_language
|
||
FROM active_sessions
|
||
WHERE login = ?
|
||
""";
|
||
|
||
List<ActiveSessionEntry> result = new ArrayList<>();
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, login);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) result.add(mapRow(rs));
|
||
}
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
public List<ActiveSessionEntry> getByLogin(String login) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByLogin(c, login);
|
||
}
|
||
}
|
||
|
||
// -------------------- UPDATE --------------------
|
||
|
||
public void updateLastAuthirificatedAtMs(Connection c, String sessionId, long lastAuthMs) throws SQLException {
|
||
String sql = """
|
||
UPDATE active_sessions
|
||
SET last_authirificated_at_ms = ?
|
||
WHERE session_id = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setLong(1, lastAuthMs);
|
||
ps.setString(2, sessionId);
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
public void updateLastAuthirificatedAtMs(String sessionId, long lastAuthMs) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
updateLastAuthirificatedAtMs(c, sessionId, lastAuthMs);
|
||
}
|
||
}
|
||
|
||
public void updateOnRefresh(
|
||
Connection c,
|
||
String sessionId,
|
||
long lastAuthMs,
|
||
String clientIp,
|
||
String clientInfoFromClient,
|
||
String clientInfoFromRequest,
|
||
String userLanguage
|
||
) throws SQLException {
|
||
|
||
String sql = """
|
||
UPDATE active_sessions
|
||
SET
|
||
last_authirificated_at_ms = ?,
|
||
client_ip = ?,
|
||
client_info_from_client = ?,
|
||
client_info_from_request = ?,
|
||
user_language = ?
|
||
WHERE session_id = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setLong(1, lastAuthMs);
|
||
ps.setString(2, clientIp);
|
||
ps.setString(3, clientInfoFromClient);
|
||
ps.setString(4, clientInfoFromRequest);
|
||
ps.setString(5, userLanguage);
|
||
ps.setString(6, sessionId);
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
public void updateOnRefresh(
|
||
String sessionId,
|
||
long lastAuthMs,
|
||
String clientIp,
|
||
String clientInfoFromClient,
|
||
String clientInfoFromRequest,
|
||
String userLanguage
|
||
) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
updateOnRefresh(c, sessionId, lastAuthMs, clientIp, clientInfoFromClient, clientInfoFromRequest, userLanguage);
|
||
}
|
||
}
|
||
|
||
// -------------------- DELETE --------------------
|
||
|
||
public void deleteBySessionId(Connection c, String sessionId) throws SQLException {
|
||
String sql = "DELETE FROM active_sessions WHERE session_id = ?";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, sessionId);
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
public void deleteBySessionId(String sessionId) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
deleteBySessionId(c, sessionId);
|
||
}
|
||
}
|
||
|
||
// -------------------- MAPPER --------------------
|
||
|
||
private ActiveSessionEntry mapRow(ResultSet rs) throws SQLException {
|
||
String sessionId = rs.getString("session_id");
|
||
String login = rs.getString("login");
|
||
String sessionKey = rs.getString("session_key");
|
||
String storagePwd = rs.getString("storage_pwd");
|
||
long sessionCreatedAtMs = rs.getLong("session_created_at_ms");
|
||
long lastAuthirificatedAtMs = rs.getLong("last_authirificated_at_ms");
|
||
String pushEndpoint = rs.getString("push_endpoint");
|
||
String pushP256dhKey = rs.getString("push_p256dh_key");
|
||
String pushAuthKey = rs.getString("push_auth_key");
|
||
String clientIp = rs.getString("client_ip");
|
||
String clientInfoFromClient = rs.getString("client_info_from_client");
|
||
String clientInfoFromRequest = rs.getString("client_info_from_request");
|
||
String userLanguage = rs.getString("user_language");
|
||
|
||
return new ActiveSessionEntry(
|
||
sessionId,
|
||
login,
|
||
sessionKey,
|
||
storagePwd,
|
||
sessionCreatedAtMs,
|
||
lastAuthirificatedAtMs,
|
||
pushEndpoint,
|
||
pushP256dhKey,
|
||
pushAuthKey,
|
||
clientIp,
|
||
clientInfoFromClient,
|
||
clientInfoFromRequest,
|
||
userLanguage
|
||
);
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.BlockchainStateEntry;
|
||
|
||
import java.sql.*;
|
||
|
||
public final class BlockchainStateDAO {
|
||
|
||
private static volatile BlockchainStateDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private BlockchainStateDAO() {}
|
||
|
||
public static BlockchainStateDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (BlockchainStateDAO.class) {
|
||
if (instance == null) instance = new BlockchainStateDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
/** Получить по blockchainName без внешнего соединения. Сам открывает/закрывает. */
|
||
public BlockchainStateEntry getByBlockchainName(String blockchainName) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByBlockchainName(c, blockchainName);
|
||
}
|
||
}
|
||
|
||
/** Получить по blockchainName с внешним соединением. Соединение НЕ закрывает. */
|
||
public BlockchainStateEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
blockchain_name,
|
||
login,
|
||
blockchain_key,
|
||
size_limit,
|
||
file_size_bytes,
|
||
last_block_number,
|
||
last_block_hash,
|
||
updated_at_ms
|
||
FROM blockchain_state
|
||
WHERE blockchain_name = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, blockchainName);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
/** UPSERT без внешнего соединения. Сам открывает/закрывает. */
|
||
public void upsert(BlockchainStateEntry e) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
upsert(c, e);
|
||
}
|
||
}
|
||
|
||
/** UPSERT с внешним соединением. Соединение НЕ закрывает. */
|
||
public void upsert(Connection c, BlockchainStateEntry e) throws SQLException {
|
||
String sql = """
|
||
INSERT INTO blockchain_state (
|
||
blockchain_name,
|
||
login,
|
||
blockchain_key,
|
||
size_limit,
|
||
file_size_bytes,
|
||
last_block_number,
|
||
last_block_hash,
|
||
updated_at_ms
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(blockchain_name)
|
||
DO UPDATE SET
|
||
login = excluded.login,
|
||
blockchain_key = excluded.blockchain_key,
|
||
size_limit = excluded.size_limit,
|
||
file_size_bytes = excluded.file_size_bytes,
|
||
last_block_number= excluded.last_block_number,
|
||
last_block_hash = excluded.last_block_hash,
|
||
updated_at_ms = excluded.updated_at_ms
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
int i = 1;
|
||
|
||
ps.setString(i++, e.getBlockchainName());
|
||
ps.setString(i++, nn(e.getLogin()));
|
||
ps.setString(i++, nn(e.getBlockchainKey()));
|
||
|
||
ps.setLong(i++, e.getSizeLimit());
|
||
ps.setLong(i++, e.getFileSizeBytes());
|
||
|
||
ps.setInt(i++, e.getLastBlockNumber());
|
||
setBytesNullable(ps, i++, e.getLastBlockHash());
|
||
|
||
ps.setLong(i++, e.getUpdatedAtMs());
|
||
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Атомарно увеличить file_size_bytes на deltaBytes, но только если НЕ превысим size_limit.
|
||
*/
|
||
public boolean tryIncreaseFileSizeWithinLimit(Connection c, String blockchainName, long deltaBytes, long nowMs) throws SQLException {
|
||
String sql = """
|
||
UPDATE blockchain_state
|
||
SET
|
||
file_size_bytes = file_size_bytes + ?,
|
||
updated_at_ms = ?
|
||
WHERE
|
||
blockchain_name = ?
|
||
AND (file_size_bytes + ?) <= size_limit
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setLong(1, deltaBytes);
|
||
ps.setLong(2, nowMs);
|
||
ps.setString(3, blockchainName);
|
||
ps.setLong(4, deltaBytes);
|
||
return ps.executeUpdate() > 0;
|
||
}
|
||
}
|
||
|
||
private BlockchainStateEntry mapRow(ResultSet rs) throws SQLException {
|
||
BlockchainStateEntry e = new BlockchainStateEntry();
|
||
|
||
e.setBlockchainName(rs.getString("blockchain_name"));
|
||
e.setLogin(rs.getString("login"));
|
||
e.setBlockchainKey(rs.getString("blockchain_key"));
|
||
|
||
e.setSizeLimit(rs.getLong("size_limit"));
|
||
e.setFileSizeBytes(rs.getLong("file_size_bytes"));
|
||
|
||
e.setLastBlockNumber(rs.getInt("last_block_number"));
|
||
e.setLastBlockHash(rs.getBytes("last_block_hash")); // nullable
|
||
|
||
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
|
||
|
||
return e;
|
||
}
|
||
|
||
private static void setBytesNullable(PreparedStatement ps, int index, byte[] b) throws SQLException {
|
||
if (b != null) ps.setBytes(index, b);
|
||
else ps.setNull(index, Types.BLOB);
|
||
}
|
||
|
||
private static String nn(String s) { return s == null ? "" : s; }
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import org.slf4j.Logger;
|
||
import org.slf4j.LoggerFactory;
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.BlockEntry;
|
||
|
||
import java.sql.*;
|
||
|
||
/**
|
||
* DAO для таблицы blocks (новый формат).
|
||
*
|
||
* Правило:
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*
|
||
* Ключ:
|
||
* - (bch_name, block_number) — уникальная пара в рамках общей БД сервера.
|
||
*/
|
||
public final class BlocksDAO {
|
||
|
||
private static volatile BlocksDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
private static final Logger log = LoggerFactory.getLogger(BlocksDAO.class);
|
||
|
||
private BlocksDAO() { }
|
||
|
||
public static BlocksDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (BlocksDAO.class) {
|
||
if (instance == null) instance = new BlocksDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
// -------------------- INSERT --------------------
|
||
|
||
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
|
||
public void insert(Connection c, BlockEntry e) throws SQLException {
|
||
log.info("DBG BlockEntry: type={} sub={} lineCode={} prevLineNumber={} thisLineNumber={} prevLineHashLen={}",
|
||
e.getMsgType(), e.getMsgSubType(),
|
||
e.getLineCode(), e.getPrevLineNumber(), e.getThisLineNumber(),
|
||
e.getPrevLineHash() == null ? null : e.getPrevLineHash().length
|
||
);
|
||
|
||
String sql = """
|
||
INSERT INTO blocks (
|
||
login,
|
||
bch_name,
|
||
block_number,
|
||
msg_type,
|
||
msg_sub_type,
|
||
block_bytes,
|
||
to_login,
|
||
to_bch_name,
|
||
to_block_number,
|
||
to_block_hash,
|
||
block_hash,
|
||
block_signature,
|
||
edited_by_block_number,
|
||
line_code,
|
||
prev_line_number,
|
||
prev_line_hash,
|
||
this_line_number
|
||
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
int i = 1;
|
||
|
||
ps.setString(i++, e.getLogin());
|
||
ps.setString(i++, e.getBchName());
|
||
ps.setInt(i++, e.getBlockNumber());
|
||
|
||
ps.setInt(i++, e.getMsgType());
|
||
ps.setInt(i++, e.getMsgSubType());
|
||
|
||
ps.setBytes(i++, e.getBlockBytes());
|
||
|
||
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
|
||
else ps.setNull(i++, Types.VARCHAR);
|
||
|
||
if (e.getToBchName() != null) ps.setString(i++, e.getToBchName());
|
||
else ps.setNull(i++, Types.VARCHAR);
|
||
|
||
if (e.getToBlockNumber() != null) ps.setInt(i++, e.getToBlockNumber());
|
||
else ps.setNull(i++, Types.INTEGER);
|
||
|
||
if (e.getToBlockHash() != null) ps.setBytes(i++, e.getToBlockHash());
|
||
else ps.setNull(i++, Types.BLOB);
|
||
|
||
ps.setBytes(i++, e.getBlockHash());
|
||
ps.setBytes(i++, e.getBlockSignature());
|
||
|
||
if (e.getEditedByBlockNumber() != null) ps.setInt(i++, e.getEditedByBlockNumber());
|
||
else ps.setNull(i++, Types.INTEGER);
|
||
|
||
// NEW: line_code
|
||
if (e.getLineCode() != null) ps.setInt(i++, e.getLineCode());
|
||
else ps.setNull(i++, Types.INTEGER);
|
||
|
||
if (e.getPrevLineNumber() != null) ps.setInt(i++, e.getPrevLineNumber());
|
||
else ps.setNull(i++, Types.INTEGER);
|
||
|
||
if (e.getPrevLineHash() != null) ps.setBytes(i++, e.getPrevLineHash());
|
||
else ps.setNull(i++, Types.BLOB);
|
||
|
||
if (e.getThisLineNumber() != null) ps.setInt(i++, e.getThisLineNumber());
|
||
else ps.setNull(i++, Types.INTEGER);
|
||
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
/** Вставка без внешнего соединения. Сам открывает/закрывает. */
|
||
public void insert(BlockEntry e) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
insert(c, e);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT: HASH BY NUMBER --------------------
|
||
|
||
/** Получить block_hash по (bch_name, block_number). Нужен для линейной проверки. */
|
||
public byte[] getHashByNumber(Connection c, String bchName, int blockNumber) throws SQLException {
|
||
String sql = """
|
||
SELECT block_hash
|
||
FROM blocks
|
||
WHERE bch_name = ? AND block_number = ?
|
||
LIMIT 1
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, bchName);
|
||
ps.setInt(2, blockNumber);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return rs.getBytes("block_hash");
|
||
}
|
||
}
|
||
}
|
||
|
||
public byte[] getHashByNumber(String bchName, int blockNumber) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getHashByNumber(c, bchName, blockNumber);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT: FULL ENTRY --------------------
|
||
|
||
public BlockEntry getByNumber(Connection c, String bchName, int blockNumber) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
bch_name,
|
||
block_number,
|
||
msg_type,
|
||
msg_sub_type,
|
||
block_bytes,
|
||
to_login,
|
||
to_bch_name,
|
||
to_block_number,
|
||
to_block_hash,
|
||
block_hash,
|
||
block_signature,
|
||
edited_by_block_number,
|
||
line_code,
|
||
prev_line_number,
|
||
prev_line_hash,
|
||
this_line_number
|
||
FROM blocks
|
||
WHERE bch_name = ? AND block_number = ?
|
||
LIMIT 1
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, bchName);
|
||
ps.setInt(2, blockNumber);
|
||
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
public BlockEntry getByNumber(String bchName, int blockNumber) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByNumber(c, bchName, blockNumber);
|
||
}
|
||
}
|
||
|
||
// -------------------- INTERNAL --------------------
|
||
|
||
private BlockEntry mapRow(ResultSet rs) throws SQLException {
|
||
BlockEntry e = new BlockEntry();
|
||
|
||
e.setLogin(rs.getString("login"));
|
||
e.setBchName(rs.getString("bch_name"));
|
||
e.setBlockNumber(rs.getInt("block_number"));
|
||
|
||
e.setMsgType(rs.getInt("msg_type"));
|
||
e.setMsgSubType(rs.getInt("msg_sub_type"));
|
||
|
||
e.setBlockBytes(rs.getBytes("block_bytes"));
|
||
|
||
String toLogin = rs.getString("to_login");
|
||
if (rs.wasNull()) toLogin = null;
|
||
e.setToLogin(toLogin);
|
||
|
||
String toBchName = rs.getString("to_bch_name");
|
||
if (rs.wasNull()) toBchName = null;
|
||
e.setToBchName(toBchName);
|
||
|
||
Integer toBlockNumber = (Integer) rs.getObject("to_block_number");
|
||
e.setToBlockNumber(toBlockNumber);
|
||
|
||
byte[] toHash = rs.getBytes("to_block_hash");
|
||
if (rs.wasNull()) toHash = null;
|
||
e.setToBlockHash(toHash);
|
||
|
||
e.setBlockHash(rs.getBytes("block_hash"));
|
||
e.setBlockSignature(rs.getBytes("block_signature"));
|
||
|
||
Integer editedBy = (Integer) rs.getObject("edited_by_block_number");
|
||
e.setEditedByBlockNumber(editedBy);
|
||
|
||
// NEW: line_code
|
||
Integer lineCode = (Integer) rs.getObject("line_code");
|
||
e.setLineCode(lineCode);
|
||
|
||
Integer prevLn = (Integer) rs.getObject("prev_line_number");
|
||
e.setPrevLineNumber(prevLn);
|
||
|
||
byte[] prevLh = rs.getBytes("prev_line_hash");
|
||
if (rs.wasNull()) prevLh = null;
|
||
e.setPrevLineHash(prevLh);
|
||
|
||
Integer thisLn = (Integer) rs.getObject("this_line_number");
|
||
e.setThisLineNumber(thisLn);
|
||
|
||
return e;
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.IpGeoCacheEntry;
|
||
|
||
import java.sql.*;
|
||
|
||
/**
|
||
* DAO для таблицы ip_geo_cache.
|
||
*
|
||
* Таблица:
|
||
* - ip TEXT PRIMARY KEY
|
||
* - geo TEXT
|
||
* - updated_at_ms INTEGER NOT NULL
|
||
*
|
||
* Правило:
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*/
|
||
public final class IpGeoCacheDAO {
|
||
|
||
private static volatile IpGeoCacheDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private IpGeoCacheDAO() { }
|
||
|
||
public static IpGeoCacheDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (IpGeoCacheDAO.class) {
|
||
if (instance == null) instance = new IpGeoCacheDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
// -------------------- UPSERT --------------------
|
||
|
||
/** UPSERT с внешним соединением. Соединение НЕ закрывает. */
|
||
public void upsert(Connection c, IpGeoCacheEntry entry) throws SQLException {
|
||
String sql = """
|
||
INSERT INTO ip_geo_cache (ip, geo, updated_at_ms)
|
||
VALUES (?, ?, ?)
|
||
ON CONFLICT(ip)
|
||
DO UPDATE SET
|
||
geo = excluded.geo,
|
||
updated_at_ms = excluded.updated_at_ms
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, entry.getIp());
|
||
ps.setString(2, entry.getGeo());
|
||
ps.setLong(3, entry.getUpdatedAtMs());
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
/** UPSERT без внешнего соединения. Сам открывает/закрывает. */
|
||
public void upsert(IpGeoCacheEntry entry) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
upsert(c, entry);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT --------------------
|
||
|
||
/** Получить по IP с внешним соединением. Соединение НЕ закрывает. */
|
||
public IpGeoCacheEntry getByIp(Connection c, String ip) throws SQLException {
|
||
String sql = """
|
||
SELECT ip, geo, updated_at_ms
|
||
FROM ip_geo_cache
|
||
WHERE ip = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, ip);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Получить по IP без внешнего соединения. Сам открывает/закрывает. */
|
||
public IpGeoCacheEntry getByIp(String ip) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByIp(c, ip);
|
||
}
|
||
}
|
||
|
||
// -------------------- DELETE --------------------
|
||
|
||
/** Удалить старые записи с внешним соединением. Соединение НЕ закрывает. */
|
||
public int deleteOlderThan(Connection c, long thresholdMs) throws SQLException {
|
||
String sql = "DELETE FROM ip_geo_cache WHERE updated_at_ms < ?";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setLong(1, thresholdMs);
|
||
return ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
/** Удалить старые записи без внешнего соединения. Сам открывает/закрывает. */
|
||
public int deleteOlderThan(long thresholdMs) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return deleteOlderThan(c, thresholdMs);
|
||
}
|
||
}
|
||
|
||
// -------------------- MAPPER --------------------
|
||
|
||
private IpGeoCacheEntry mapRow(ResultSet rs) throws SQLException {
|
||
String ip = rs.getString("ip");
|
||
String geo = rs.getString("geo");
|
||
long updatedAtMs = rs.getLong("updated_at_ms");
|
||
return new IpGeoCacheEntry(ip, geo, updatedAtMs);
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.SolanaUserEntry;
|
||
|
||
import java.sql.*;
|
||
import java.util.ArrayList;
|
||
import java.util.List;
|
||
|
||
/**
|
||
* SolanaUsersDAO — локальная таблица пользователей из Solana.
|
||
*
|
||
* Таблица: solana_users
|
||
*
|
||
* Колонки:
|
||
* - login TEXT PRIMARY KEY (COLLATE NOCASE)
|
||
* - blockchain_name TEXT NOT NULL
|
||
* - solana_key TEXT NOT NULL
|
||
* - blockchain_key TEXT NOT NULL
|
||
* - device_key TEXT NOT NULL
|
||
*
|
||
* Правило работы с соединениями:
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*/
|
||
public final class SolanaUsersDAO {
|
||
|
||
private static volatile SolanaUsersDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private SolanaUsersDAO() {}
|
||
|
||
public static SolanaUsersDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (SolanaUsersDAO.class) {
|
||
if (instance == null) instance = new SolanaUsersDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
// -------------------- INSERT --------------------
|
||
|
||
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
|
||
public void insert(Connection c, SolanaUserEntry user) throws SQLException {
|
||
String sql = """
|
||
INSERT INTO solana_users (
|
||
login, blockchain_name, solana_key, blockchain_key, device_key
|
||
) VALUES (?, ?, ?, ?, ?)
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, user.getLogin());
|
||
ps.setString(2, user.getBlockchainName());
|
||
ps.setString(3, user.getSolanaKey());
|
||
ps.setString(4, user.getBlockchainKey());
|
||
ps.setString(5, user.getDeviceKey());
|
||
ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
/** Вставка без внешнего соединения. Сам открывает/закрывает. */
|
||
public void insert(SolanaUserEntry user) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
insert(c, user);
|
||
}
|
||
}
|
||
|
||
// -------------------- EXISTS --------------------
|
||
|
||
/** Проверка существования по login (case-insensitive) с внешним соединением. Соединение НЕ закрывает. */
|
||
public boolean existsByLogin(Connection c, String login) throws SQLException {
|
||
String sql = """
|
||
SELECT 1
|
||
FROM solana_users
|
||
WHERE LOWER(login) = LOWER(?)
|
||
LIMIT 1
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, login);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
return rs.next();
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Проверка существования по login (case-insensitive) без внешнего соединения. Сам открывает/закрывает. */
|
||
public boolean existsByLogin(String login) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return existsByLogin(c, login);
|
||
}
|
||
}
|
||
|
||
/** Проверка существования по blockchain_name (case-sensitive, как в БД) с внешним соединением. */
|
||
public boolean existsByBlockchainName(Connection c, String blockchainName) throws SQLException {
|
||
String sql = """
|
||
SELECT 1
|
||
FROM solana_users
|
||
WHERE blockchain_name = ?
|
||
LIMIT 1
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, blockchainName);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
return rs.next();
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Проверка существования по blockchain_name без внешнего соединения. */
|
||
public boolean existsByBlockchainName(String blockchainName) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return existsByBlockchainName(c, blockchainName);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT --------------------
|
||
|
||
/** Получить по login (case-insensitive) с внешним соединением. Соединение НЕ закрывает. */
|
||
public SolanaUserEntry getByLogin(Connection c, String login) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
blockchain_name,
|
||
solana_key,
|
||
blockchain_key,
|
||
device_key
|
||
FROM solana_users
|
||
WHERE LOWER(login) = LOWER(?)
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, login);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Получить по login (case-insensitive) без внешнего соединения. Сам открывает/закрывает. */
|
||
public SolanaUserEntry getByLogin(String login) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByLogin(c, login);
|
||
}
|
||
}
|
||
|
||
/** Получить по blockchain_name (case-sensitive) с внешним соединением. Соединение НЕ закрывает. */
|
||
public SolanaUserEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
blockchain_name,
|
||
solana_key,
|
||
blockchain_key,
|
||
device_key
|
||
FROM solana_users
|
||
WHERE blockchain_name = ?
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, blockchainName);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
/** Получить по blockchain_name без внешнего соединения. */
|
||
public SolanaUserEntry getByBlockchainName(String blockchainName) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByBlockchainName(c, blockchainName);
|
||
}
|
||
}
|
||
|
||
/** Поиск по префиксу с внешним соединением. Соединение НЕ закрывает. */
|
||
public List<SolanaUserEntry> searchByLoginPrefix(Connection c, String prefix) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
blockchain_name,
|
||
solana_key,
|
||
blockchain_key,
|
||
device_key
|
||
FROM solana_users
|
||
WHERE LOWER(login) LIKE ?
|
||
ORDER BY login
|
||
LIMIT 5
|
||
""";
|
||
|
||
List<SolanaUserEntry> result = new ArrayList<>();
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, prefix.toLowerCase() + "%");
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) result.add(mapRow(rs));
|
||
}
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
/** Поиск по префиксу без внешнего соединения. Сам открывает/закрывает. */
|
||
public List<SolanaUserEntry> searchByLoginPrefix(String prefix) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return searchByLoginPrefix(c, prefix);
|
||
}
|
||
}
|
||
|
||
// -------------------- MAPPER --------------------
|
||
|
||
private SolanaUserEntry mapRow(ResultSet rs) throws SQLException {
|
||
SolanaUserEntry e = new SolanaUserEntry();
|
||
|
||
e.setLogin(rs.getString("login"));
|
||
e.setBlockchainName(rs.getString("blockchain_name"));
|
||
e.setSolanaKey(rs.getString("solana_key"));
|
||
e.setBlockchainKey(rs.getString("blockchain_key"));
|
||
e.setDeviceKey(rs.getString("device_key"));
|
||
|
||
return e;
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.MsgSubType;
|
||
import shine.db.SqliteDbController;
|
||
|
||
import java.sql.*;
|
||
import java.util.ArrayList;
|
||
import java.util.List;
|
||
|
||
/**
|
||
* SubscriptionsDAO — агрегатный DAO для "каналов" (подписок).
|
||
*
|
||
* Возвращает по каждой активной подписке (FOLLOW) + "сам на себя":
|
||
* - login цели (channelLogin)
|
||
* - blockchainName цели (channelBchName)
|
||
* - count публикаций (TEXT_NEW)
|
||
* - last publication: bytes оригинального блока (для timestamp)
|
||
* - last publication: bytes актуального блока (edit или orig) — для текста превью
|
||
*
|
||
* Важно:
|
||
* - это НЕ таблица => сущность результата хранится вложенным классом.
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*/
|
||
public final class SubscriptionsDAO {
|
||
|
||
private static volatile SubscriptionsDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private SubscriptionsDAO() {}
|
||
|
||
public static SubscriptionsDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (SubscriptionsDAO.class) {
|
||
if (instance == null) instance = new SubscriptionsDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
/** Результат одной строки ("канал") для подписок. */
|
||
public static final class ChannelRow {
|
||
|
||
private final String channelLogin;
|
||
private final String channelBchName;
|
||
|
||
private final int publicationsCount;
|
||
|
||
/** Последняя публикация: global number (nullable если публикаций нет). */
|
||
private final Integer lastPublicationGlobalNumber;
|
||
|
||
/** Байты оригинальной публикации (FULL bytes блока) — для timestamp (nullable). */
|
||
private final byte[] lastPublicationBlockBytes;
|
||
|
||
/** Если публикация редактировалась: global number edit-блока (nullable). */
|
||
private final Integer lastEditGlobalNumber;
|
||
|
||
/** Байты edit-блока (FULL bytes блока) (nullable). */
|
||
private final byte[] lastEditBlockBytes;
|
||
|
||
public ChannelRow(String channelLogin,
|
||
String channelBchName,
|
||
int publicationsCount,
|
||
Integer lastPublicationGlobalNumber,
|
||
byte[] lastPublicationBlockBytes,
|
||
Integer lastEditGlobalNumber,
|
||
byte[] lastEditBlockBytes) {
|
||
|
||
this.channelLogin = channelLogin;
|
||
this.channelBchName = channelBchName;
|
||
this.publicationsCount = publicationsCount;
|
||
this.lastPublicationGlobalNumber = lastPublicationGlobalNumber;
|
||
this.lastPublicationBlockBytes = lastPublicationBlockBytes;
|
||
this.lastEditGlobalNumber = lastEditGlobalNumber;
|
||
this.lastEditBlockBytes = lastEditBlockBytes;
|
||
}
|
||
|
||
public String getChannelLogin() { return channelLogin; }
|
||
public String getChannelBchName() { return channelBchName; }
|
||
|
||
public int getPublicationsCount() { return publicationsCount; }
|
||
|
||
public Integer getLastPublicationGlobalNumber() { return lastPublicationGlobalNumber; }
|
||
public byte[] getLastPublicationBlockBytes() { return lastPublicationBlockBytes; }
|
||
|
||
public Integer getLastEditGlobalNumber() { return lastEditGlobalNumber; }
|
||
public byte[] getLastEditBlockBytes() { return lastEditBlockBytes; }
|
||
}
|
||
|
||
// В проекте msg_type=1 означает TEXT (у тебя это уже зафиксировано).
|
||
private static final int MSG_TYPE_TEXT = 1;
|
||
|
||
/**
|
||
* Получить список подписок (активные FOLLOW) + "сам на себя" и по каждой:
|
||
* - count публикаций (TEXT_NEW)
|
||
* - последнюю публикацию (orig bytes) + её edit (если есть)
|
||
*
|
||
* Поведение при 0 публикаций:
|
||
* - publications_count = 0
|
||
* - last_pub_* = NULL
|
||
* - last_edit_* = NULL
|
||
*/
|
||
public List<ChannelRow> getSubscribedChannels(Connection c, String requesterLogin) throws SQLException {
|
||
|
||
String sql = """
|
||
WITH subs AS (
|
||
-- 1) FOLLOW-каналы
|
||
SELECT
|
||
cs.to_login AS channel_login,
|
||
cs.to_bch_name AS channel_bch_name
|
||
FROM connections_state cs
|
||
WHERE cs.login = ?
|
||
AND cs.rel_type = ?
|
||
|
||
UNION
|
||
|
||
-- 2) self: все блокчейны пользователя (если их несколько)
|
||
SELECT
|
||
bs.login AS channel_login,
|
||
bs.blockchain_name AS channel_bch_name
|
||
FROM blockchain_state bs
|
||
WHERE bs.login = ?
|
||
),
|
||
pub_counts AS (
|
||
SELECT
|
||
b.login AS channel_login,
|
||
b.bch_name AS channel_bch_name,
|
||
COUNT(*) AS publications_count
|
||
FROM blocks b
|
||
JOIN subs s
|
||
ON s.channel_login = b.login
|
||
AND s.channel_bch_name = b.bch_name
|
||
WHERE b.msg_type = ?
|
||
AND b.msg_sub_type = ?
|
||
GROUP BY b.login, b.bch_name
|
||
),
|
||
last_pub AS (
|
||
SELECT
|
||
b.login AS channel_login,
|
||
b.bch_name AS channel_bch_name,
|
||
MAX(b.block_global_number) AS last_pub_global_number
|
||
FROM blocks b
|
||
JOIN subs s
|
||
ON s.channel_login = b.login
|
||
AND s.channel_bch_name = b.bch_name
|
||
WHERE b.msg_type = ?
|
||
AND b.msg_sub_type = ?
|
||
GROUP BY b.login, b.bch_name
|
||
),
|
||
last_pub_block AS (
|
||
SELECT
|
||
b.login AS channel_login,
|
||
b.bch_name AS channel_bch_name,
|
||
b.block_global_number AS last_pub_global_number,
|
||
b.block_byte AS last_pub_block_bytes,
|
||
b.edited_by_block_global_number AS last_edit_global_number
|
||
FROM blocks b
|
||
JOIN last_pub lp
|
||
ON lp.channel_login = b.login
|
||
AND lp.channel_bch_name = b.bch_name
|
||
AND lp.last_pub_global_number = b.block_global_number
|
||
),
|
||
last_edit_block AS (
|
||
SELECT
|
||
e.login AS channel_login,
|
||
e.bch_name AS channel_bch_name,
|
||
e.block_global_number AS last_edit_global_number,
|
||
e.block_byte AS last_edit_block_bytes
|
||
FROM blocks e
|
||
JOIN last_pub_block p
|
||
ON p.channel_login = e.login
|
||
AND p.channel_bch_name = e.bch_name
|
||
AND p.last_edit_global_number = e.block_global_number
|
||
)
|
||
SELECT
|
||
s.channel_login,
|
||
s.channel_bch_name,
|
||
COALESCE(pc.publications_count, 0) AS publications_count,
|
||
p.last_pub_global_number,
|
||
p.last_pub_block_bytes,
|
||
p.last_edit_global_number,
|
||
e.last_edit_block_bytes
|
||
FROM subs s
|
||
LEFT JOIN pub_counts pc
|
||
ON pc.channel_login = s.channel_login
|
||
AND pc.channel_bch_name = s.channel_bch_name
|
||
LEFT JOIN last_pub_block p
|
||
ON p.channel_login = s.channel_login
|
||
AND p.channel_bch_name = s.channel_bch_name
|
||
LEFT JOIN last_edit_block e
|
||
ON e.channel_login = s.channel_login
|
||
AND e.channel_bch_name = s.channel_bch_name
|
||
ORDER BY s.channel_login, s.channel_bch_name
|
||
""";
|
||
|
||
List<ChannelRow> out = new ArrayList<>();
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
int i = 1;
|
||
|
||
// FOLLOW
|
||
ps.setString(i++, requesterLogin);
|
||
ps.setInt(i++, (int) MsgSubType.CONNECTION_FOLLOW);
|
||
|
||
// self
|
||
ps.setString(i++, requesterLogin);
|
||
|
||
// pub_counts
|
||
ps.setInt(i++, MSG_TYPE_TEXT);
|
||
ps.setInt(i++, (int) MsgSubType.TEXT_NEW);
|
||
|
||
// last_pub
|
||
ps.setInt(i++, MSG_TYPE_TEXT);
|
||
ps.setInt(i++, (int) MsgSubType.TEXT_NEW);
|
||
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) {
|
||
String channelLogin = rs.getString("channel_login");
|
||
String channelBchName = rs.getString("channel_bch_name");
|
||
|
||
int publicationsCount = rs.getInt("publications_count");
|
||
|
||
Integer lastPubGn = (Integer) rs.getObject("last_pub_global_number");
|
||
byte[] lastPubBytes = rs.getBytes("last_pub_block_bytes");
|
||
|
||
Integer lastEditGn = (Integer) rs.getObject("last_edit_global_number");
|
||
byte[] lastEditBytes = rs.getBytes("last_edit_block_bytes");
|
||
|
||
out.add(new ChannelRow(
|
||
channelLogin,
|
||
channelBchName,
|
||
publicationsCount,
|
||
lastPubGn,
|
||
lastPubBytes,
|
||
lastEditGn,
|
||
lastEditBytes
|
||
));
|
||
}
|
||
}
|
||
}
|
||
|
||
return out;
|
||
}
|
||
|
||
/** Вариант без внешнего соединения. Сам открывает/закрывает. */
|
||
public List<ChannelRow> getSubscribedChannels(String requesterLogin) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getSubscribedChannels(c, requesterLogin);
|
||
}
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.SolanaUserEntry;
|
||
|
||
import java.sql.*;
|
||
|
||
/**
|
||
* UserCreateDAO — атомарное добавление пользователя:
|
||
* - solana_users (login, blockchain_name, solana_key, blockchain_key, device_key)
|
||
* - blockchain_state (blockchain_name, login, blockchain_key, size_limit, ... last_block_number=-1 ...)
|
||
*
|
||
* ВАЖНО:
|
||
* - только INSERT (без перезаписи существующих записей)
|
||
* - если login или blockchainName заняты — возвращаем false (пользователь уже есть/занято)
|
||
*/
|
||
public final class UserCreateDAO {
|
||
|
||
private static volatile UserCreateDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
private final SolanaUsersDAO usersDao = SolanaUsersDAO.getInstance();
|
||
|
||
private UserCreateDAO() {}
|
||
|
||
public static UserCreateDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (UserCreateDAO.class) {
|
||
if (instance == null) instance = new UserCreateDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
/**
|
||
* @return true если добавили; false если занято (login уже есть или blockchainName уже существует).
|
||
*/
|
||
public boolean insertUserWithBlockchain(
|
||
String login,
|
||
String blockchainName,
|
||
String solanaKey,
|
||
String blockchainKey,
|
||
String deviceKey,
|
||
long sizeLimit,
|
||
long nowMs
|
||
) throws SQLException {
|
||
|
||
try (Connection c = db.getConnection()) {
|
||
boolean oldAuto = c.getAutoCommit();
|
||
c.setAutoCommit(false);
|
||
|
||
// BEGIN IMMEDIATE — чтобы сразу взять write-lock и не ловить гонки
|
||
try (Statement st = c.createStatement()) {
|
||
st.execute("BEGIN IMMEDIATE");
|
||
}
|
||
|
||
try {
|
||
// 1) solana_users
|
||
SolanaUserEntry u = new SolanaUserEntry();
|
||
u.setLogin(login);
|
||
u.setBlockchainName(blockchainName);
|
||
u.setSolanaKey(solanaKey);
|
||
u.setBlockchainKey(blockchainKey);
|
||
u.setDeviceKey(deviceKey);
|
||
|
||
usersDao.insert(c, u); // если login занят (NOCASE) или blockchainName (unique) -> constraint
|
||
|
||
// 2) blockchain_state — строго INSERT, без UPSERT (иначе можно перезаписать существующую цепочку)
|
||
insertBlockchainStateStrict(
|
||
c,
|
||
blockchainName,
|
||
login,
|
||
blockchainKey,
|
||
sizeLimit,
|
||
nowMs
|
||
);
|
||
|
||
c.commit();
|
||
return true;
|
||
|
||
} catch (SQLException e) {
|
||
c.rollback();
|
||
|
||
String msg = e.getMessage() == null ? "" : e.getMessage().toLowerCase();
|
||
if (msg.contains("constraint")) {
|
||
return false;
|
||
}
|
||
throw e;
|
||
|
||
} finally {
|
||
c.setAutoCommit(oldAuto);
|
||
}
|
||
}
|
||
}
|
||
|
||
private static void insertBlockchainStateStrict(
|
||
Connection c,
|
||
String blockchainName,
|
||
String login,
|
||
String blockchainKey,
|
||
long sizeLimit,
|
||
long nowMs
|
||
) throws SQLException {
|
||
|
||
String sql = """
|
||
INSERT INTO blockchain_state (
|
||
blockchain_name,
|
||
login,
|
||
blockchain_key,
|
||
size_limit,
|
||
file_size_bytes,
|
||
last_block_number,
|
||
last_block_hash,
|
||
updated_at_ms
|
||
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
int i = 1;
|
||
ps.setString(i++, blockchainName);
|
||
ps.setString(i++, login);
|
||
ps.setString(i++, blockchainKey);
|
||
|
||
ps.setLong(i++, sizeLimit);
|
||
ps.setLong(i++, 0L);
|
||
|
||
ps.setInt(i++, -1);
|
||
ps.setNull(i++, Types.BLOB); // старт: блоков ещё нет
|
||
ps.setLong(i++, nowMs);
|
||
|
||
ps.executeUpdate(); // если blockchainName занят -> constraint (PK)
|
||
}
|
||
}
|
||
}
|
||
package shine.db.dao;
|
||
|
||
import shine.db.SqliteDbController;
|
||
import shine.db.entities.UserParamEntry;
|
||
|
||
import java.sql.*;
|
||
import java.util.ArrayList;
|
||
import java.util.List;
|
||
|
||
/**
|
||
* UserParamsDAO — хранение сохранённых параметров пользователя.
|
||
*
|
||
* Правило:
|
||
* - методы с Connection НЕ закрывают соединение
|
||
* - методы без Connection сами открывают и закрывают соединение
|
||
*
|
||
* ЛОГИКА time_ms:
|
||
* - БД принимает запись только если она "новее" (time_ms строго больше текущего).
|
||
* - Реализовано атомарно одним SQL: UPSERT + WHERE users_params.time_ms < excluded.time_ms
|
||
*/
|
||
public final class UserParamsDAO {
|
||
|
||
private static volatile UserParamsDAO instance;
|
||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||
|
||
private UserParamsDAO() { }
|
||
|
||
public static UserParamsDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (UserParamsDAO.class) {
|
||
if (instance == null) instance = new UserParamsDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
// -------------------- UPSERT (IF NEWER) --------------------
|
||
|
||
public int upsertIfNewer(Connection c, UserParamEntry e) throws SQLException {
|
||
String sql = """
|
||
INSERT INTO users_params (
|
||
login,
|
||
param,
|
||
time_ms,
|
||
value,
|
||
device_key,
|
||
signature
|
||
) VALUES (?, ?, ?, ?, ?, ?)
|
||
ON CONFLICT(login, param)
|
||
DO UPDATE SET
|
||
time_ms = excluded.time_ms,
|
||
value = excluded.value,
|
||
device_key = excluded.device_key,
|
||
signature = excluded.signature
|
||
WHERE users_params.time_ms < excluded.time_ms
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, e.getLogin());
|
||
ps.setString(2, e.getParam());
|
||
ps.setLong(3, e.getTimeMs());
|
||
ps.setString(4, e.getValue());
|
||
|
||
if (e.getDeviceKey() != null) ps.setString(5, e.getDeviceKey());
|
||
else ps.setNull(5, Types.VARCHAR);
|
||
|
||
if (e.getSignature() != null) ps.setString(6, e.getSignature());
|
||
else ps.setNull(6, Types.VARCHAR);
|
||
|
||
return ps.executeUpdate();
|
||
}
|
||
}
|
||
|
||
public int upsertIfNewer(UserParamEntry e) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return upsertIfNewer(c, e);
|
||
}
|
||
}
|
||
|
||
// -------------------- SELECT --------------------
|
||
|
||
public UserParamEntry getByLoginAndParam(Connection c, String login, String param) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
param,
|
||
time_ms,
|
||
value,
|
||
device_key,
|
||
signature
|
||
FROM users_params
|
||
WHERE login = ? AND param = ?
|
||
LIMIT 1
|
||
""";
|
||
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, login);
|
||
ps.setString(2, param);
|
||
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
public UserParamEntry getByLoginAndParam(String login, String param) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByLoginAndParam(c, login, param);
|
||
}
|
||
}
|
||
|
||
public List<UserParamEntry> getByLogin(Connection c, String login) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
login,
|
||
param,
|
||
time_ms,
|
||
value,
|
||
device_key,
|
||
signature
|
||
FROM users_params
|
||
WHERE login = ?
|
||
ORDER BY time_ms DESC
|
||
""";
|
||
|
||
List<UserParamEntry> list = new ArrayList<>();
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, login);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) list.add(mapRow(rs));
|
||
}
|
||
}
|
||
return list;
|
||
}
|
||
|
||
public List<UserParamEntry> getByLogin(String login) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return getByLogin(c, login);
|
||
}
|
||
}
|
||
|
||
// -------------------- MAPPER --------------------
|
||
|
||
private static UserParamEntry mapRow(ResultSet rs) throws SQLException {
|
||
UserParamEntry e = new UserParamEntry();
|
||
e.setLogin(rs.getString("login"));
|
||
e.setParam(rs.getString("param"));
|
||
e.setTimeMs(rs.getLong("time_ms"));
|
||
e.setValue(rs.getString("value"));
|
||
|
||
String dk = rs.getString("device_key");
|
||
if (rs.wasNull()) dk = null;
|
||
e.setDeviceKey(dk);
|
||
|
||
String sig = rs.getString("signature");
|
||
if (rs.wasNull()) sig = null;
|
||
e.setSignature(sig);
|
||
|
||
return e;
|
||
}
|
||
}
|
||
package shine.db;
|
||
|
||
import utils.config.AppConfig;
|
||
|
||
import java.io.BufferedReader;
|
||
import java.io.IOException;
|
||
import java.io.InputStreamReader;
|
||
import java.nio.file.*;
|
||
import java.sql.Connection;
|
||
import java.sql.DriverManager;
|
||
import java.sql.SQLException;
|
||
import java.sql.Statement;
|
||
|
||
/**
|
||
* DatabaseInitializer — создание новой SQLite-БД по схеме SHiNE.
|
||
*
|
||
* В этой версии:
|
||
* - создаём ТОЛЬКО таблицы/индексы
|
||
* - в конце вызываем DatabaseTriggersInstaller.createAllTriggers(st)
|
||
*
|
||
* v2 (sessions):
|
||
* - active_sessions.session_pwd удалён
|
||
* - active_sessions.session_key хранит публичный ключ сессии (sessionPubKeyB64)
|
||
*/
|
||
public final class DatabaseInitializer {
|
||
|
||
private DatabaseInitializer() {}
|
||
|
||
/* ===================== TEXT (msg_type=1) ===================== */
|
||
|
||
public static final short TEXT_NEW = 1;
|
||
public static final short TEXT_REPLY = 2;
|
||
public static final short TEXT_REPOST = 3;
|
||
public static final short TEXT_EDIT = 10;
|
||
|
||
/* ===================== REACTION (msg_type=2) ===================== */
|
||
|
||
public static final short REACTION_LIKE = 1;
|
||
|
||
/* ===================== CONNECTION (msg_type=3) ===================== */
|
||
public static final short CONNECTION_FRIEND = 10;
|
||
public static final short CONNECTION_UNFRIEND = 11;
|
||
|
||
public static final short CONNECTION_CONTACT = 20;
|
||
public static final short CONNECTION_UNCONTACT = 21;
|
||
|
||
public static final short CONNECTION_FOLLOW = 30;
|
||
public static final short CONNECTION_UNFOLLOW = 31;
|
||
|
||
public static void createNewDB(String[] args) {
|
||
AppConfig config = AppConfig.getInstance();
|
||
String dbPath = config.getParam("db.path");
|
||
|
||
if (dbPath == null || dbPath.isBlank()) {
|
||
System.err.println("Параметр db.path не задан в application.properties");
|
||
return;
|
||
}
|
||
|
||
Path dbFile = Paths.get(dbPath);
|
||
try {
|
||
Path parent = dbFile.getParent();
|
||
if (parent != null && !Files.exists(parent)) {
|
||
Files.createDirectories(parent);
|
||
}
|
||
|
||
if (Files.exists(dbFile)) {
|
||
System.out.println("Файл базы данных уже существует: " + dbFile.toAbsolutePath());
|
||
System.out.print("Пересоздать БД (СТАРАЯ БУДЕТ УДАЛЕНА)? [y/N]: ");
|
||
|
||
BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
|
||
String answer = reader.readLine();
|
||
if (!"y".equalsIgnoreCase(answer) && !"yes".equalsIgnoreCase(answer)) {
|
||
System.out.println("Операция отменена. БД не изменена.");
|
||
return;
|
||
}
|
||
|
||
Files.delete(dbFile);
|
||
System.out.println("Старый файл БД удалён.");
|
||
}
|
||
|
||
createSchema("jdbc:sqlite:" + dbPath);
|
||
System.out.println("Новая БД успешно создана по пути: " + dbFile.toAbsolutePath());
|
||
|
||
} catch (IOException e) {
|
||
System.err.println("Ошибка работы с файлом БД: " + e.getMessage());
|
||
} catch (SQLException e) {
|
||
System.err.println("Ошибка создания схемы БД: " + e.getMessage());
|
||
}
|
||
}
|
||
|
||
private static void createSchema(String jdbcUrl) throws SQLException {
|
||
try {
|
||
Class.forName("org.sqlite.JDBC");
|
||
} catch (ClassNotFoundException e) {
|
||
throw new RuntimeException("SQLite JDBC driver not found", e);
|
||
}
|
||
|
||
try (Connection conn = DriverManager.getConnection(jdbcUrl);
|
||
Statement st = conn.createStatement()) {
|
||
|
||
st.execute("PRAGMA foreign_keys = ON");
|
||
|
||
// 1. solana_users
|
||
// ВАЖНО:
|
||
// - Все требуемые поля теперь лежат в solana_users:
|
||
// login, blockchain_name, solana_key, blockchain_key, device_key
|
||
// - Поиск по login в DAO сделан case-insensitive.
|
||
// - Для защиты от дублей "Anya" и "anya" добавляем COLLATE NOCASE на PRIMARY KEY.
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS solana_users (
|
||
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
|
||
blockchain_name TEXT NOT NULL,
|
||
solana_key TEXT NOT NULL,
|
||
blockchain_key TEXT NOT NULL,
|
||
device_key TEXT NOT NULL
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE UNIQUE INDEX IF NOT EXISTS uq_solana_users_blockchain_name
|
||
ON solana_users (blockchain_name);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_solana_users_login
|
||
ON solana_users (login);
|
||
""");
|
||
|
||
// 2. active_sessions (v2)
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS active_sessions (
|
||
session_id TEXT NOT NULL PRIMARY KEY,
|
||
login TEXT NOT NULL,
|
||
session_key TEXT NOT NULL,
|
||
storage_pwd TEXT NOT NULL,
|
||
session_created_at_ms INTEGER NOT NULL,
|
||
last_authirificated_at_ms INTEGER NOT NULL,
|
||
push_endpoint TEXT,
|
||
push_p256dh_key TEXT,
|
||
push_auth_key TEXT,
|
||
client_ip TEXT,
|
||
client_info_from_client TEXT,
|
||
client_info_from_request TEXT,
|
||
user_language TEXT,
|
||
FOREIGN KEY (login) REFERENCES solana_users(login)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_active_sessions_login
|
||
ON active_sessions (login);
|
||
""");
|
||
|
||
// 3. users_params
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS users_params (
|
||
login TEXT NOT NULL,
|
||
param TEXT NOT NULL,
|
||
time_ms INTEGER NOT NULL,
|
||
value TEXT NOT NULL,
|
||
device_key TEXT,
|
||
signature TEXT,
|
||
FOREIGN KEY (login) REFERENCES solana_users(login),
|
||
UNIQUE (login, param)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_users_params_login
|
||
ON users_params (login);
|
||
""");
|
||
|
||
// 4. ip_geo_cache
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS ip_geo_cache (
|
||
ip TEXT NOT NULL PRIMARY KEY,
|
||
geo TEXT,
|
||
updated_at_ms INTEGER NOT NULL
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_ip_geo_cache_updated_at
|
||
ON ip_geo_cache (updated_at_ms);
|
||
""");
|
||
|
||
// 5. blockchain_state
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS blockchain_state (
|
||
blockchain_name TEXT NOT NULL PRIMARY KEY,
|
||
login TEXT NOT NULL,
|
||
blockchain_key TEXT NOT NULL,
|
||
|
||
size_limit INTEGER NOT NULL,
|
||
file_size_bytes INTEGER NOT NULL,
|
||
|
||
last_block_number INTEGER NOT NULL,
|
||
last_block_hash BLOB,
|
||
|
||
updated_at_ms INTEGER NOT NULL,
|
||
|
||
FOREIGN KEY (login) REFERENCES solana_users(login)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_login
|
||
ON blockchain_state (login);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_updated_at
|
||
ON blockchain_state (updated_at_ms);
|
||
""");
|
||
|
||
// 6. blocks (+ line_code)
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS blocks (
|
||
login TEXT NOT NULL,
|
||
bch_name TEXT NOT NULL,
|
||
block_number INTEGER NOT NULL CHECK(block_number >= 0),
|
||
|
||
msg_type INTEGER NOT NULL,
|
||
msg_sub_type INTEGER NOT NULL,
|
||
|
||
block_bytes BLOB NOT NULL,
|
||
|
||
-- target (reply/like/edit и т.д.)
|
||
to_login TEXT,
|
||
to_bch_name TEXT,
|
||
to_block_number INTEGER CHECK(to_block_number IS NULL OR to_block_number >= 0),
|
||
to_block_hash BLOB,
|
||
|
||
-- собственные данные
|
||
block_hash BLOB NOT NULL,
|
||
block_signature BLOB NOT NULL,
|
||
|
||
-- если этот блок был изменён последним edit'ом
|
||
edited_by_block_number INTEGER CHECK(edited_by_block_number IS NULL OR edited_by_block_number >= 0),
|
||
|
||
-- линейность (опционально)
|
||
line_code INTEGER CHECK(line_code IS NULL OR line_code >= 0),
|
||
prev_line_number INTEGER CHECK(prev_line_number IS NULL OR prev_line_number >= 0),
|
||
prev_line_hash BLOB,
|
||
this_line_number INTEGER CHECK(this_line_number IS NULL OR this_line_number >= 0),
|
||
|
||
FOREIGN KEY (login) REFERENCES solana_users(login),
|
||
FOREIGN KEY (bch_name) REFERENCES blockchain_state(blockchain_name),
|
||
|
||
UNIQUE (bch_name, block_number)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_blocks_by_chain_number
|
||
ON blocks (bch_name, block_number);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
|
||
ON blocks (to_login, to_bch_name, to_block_number);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_blocks_by_line
|
||
ON blocks (bch_name, line_code, this_line_number);
|
||
""");
|
||
|
||
// 7) connections_state
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS connections_state (
|
||
login TEXT NOT NULL,
|
||
rel_type INTEGER NOT NULL,
|
||
to_login TEXT NOT NULL,
|
||
to_bch_name TEXT NOT NULL,
|
||
to_block_number INTEGER,
|
||
to_block_hash BLOB,
|
||
|
||
FOREIGN KEY (login) REFERENCES solana_users(login),
|
||
|
||
UNIQUE (login, rel_type, to_login)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_connections_state_login
|
||
ON connections_state (login);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_connections_state_to_login
|
||
ON connections_state (to_login);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_connections_state_pair
|
||
ON connections_state (login, to_login);
|
||
""");
|
||
|
||
// 8) message_stats
|
||
st.executeUpdate("""
|
||
CREATE TABLE IF NOT EXISTS message_stats (
|
||
to_login TEXT NOT NULL,
|
||
to_bch_name TEXT NOT NULL,
|
||
to_block_number INTEGER NOT NULL,
|
||
to_block_hash BLOB NOT NULL,
|
||
|
||
likes_count INTEGER NOT NULL DEFAULT 0,
|
||
replies_count INTEGER NOT NULL DEFAULT 0,
|
||
edits_count INTEGER NOT NULL DEFAULT 0,
|
||
|
||
UNIQUE (
|
||
to_login,
|
||
to_bch_name,
|
||
to_block_number,
|
||
to_block_hash
|
||
)
|
||
);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_message_stats_target
|
||
ON message_stats (to_bch_name, to_block_number, to_block_hash);
|
||
""");
|
||
|
||
st.executeUpdate("""
|
||
CREATE INDEX IF NOT EXISTS idx_message_stats_login
|
||
ON message_stats (to_login);
|
||
""");
|
||
|
||
DatabaseTriggersInstaller.createAllTriggers(st);
|
||
}
|
||
}
|
||
}
|
||
package shine.db;
|
||
|
||
import java.sql.SQLException;
|
||
import java.sql.Statement;
|
||
|
||
/**
|
||
* DatabaseTriggersInstaller — устанавливает триггеры, которые поддерживают бизнес-логику БД.
|
||
*
|
||
* Мы специально сделали триггеры максимально "совместимыми":
|
||
* - НЕТ динамических сообщений в RAISE(...): только фиксированные строки.
|
||
* (Некоторые SQLite-сборки / просмотрщики падают на "||" внутри RAISE.)
|
||
* - НЕТ UPSERT "ON CONFLICT DO UPDATE" — вместо него:
|
||
* INSERT OR IGNORE + UPDATE
|
||
* (Старые SQLite не знают UPSERT.)
|
||
*
|
||
* =============================================================================
|
||
* ОПИСАНИЕ ТРИГГЕРОВ
|
||
* =============================================================================
|
||
*
|
||
* [1] trg_blocks_line_integrity_bi (BEFORE INSERT ON blocks)
|
||
* Контроль целостности "линий" (line_code / prev_line_number / prev_line_hash / this_line_number).
|
||
*
|
||
* Зачем это нужно:
|
||
* - В каналах/ветках/действиях ты хочешь иметь "линейную" последовательность,
|
||
* где каждый следующий блок явно ссылается на предыдущий блок линии
|
||
* и подтверждает, что ссылка не подменена.
|
||
*
|
||
* Когда срабатывает:
|
||
* - ТОЛЬКО если при вставке передано ХОТЯ БЫ ОДНО из line-полей.
|
||
* - Если line-поля не переданы — триггер вообще не работает (это важно).
|
||
*
|
||
* Что проверяет:
|
||
* A) line-поля допускаются только для msg_type:
|
||
* 0 (TECH), 1 (TEXT), 3 (CONNECTION), 4 (USER_PARAM)
|
||
* B) Если пришло хоть одно line-поле — обязаны прийти ВСЕ 4 (никаких "частичных")
|
||
* C) prev-блок линии существует в той же цепочке bch_name
|
||
* D) prev_hash совпадает с block_hash найденного prev-блока
|
||
* E) line_code корректный:
|
||
* - либо первый шаг после root: prev_line_number == line_code
|
||
* - либо prev уже принадлежит этой линии: p.line_code == NEW.line_code
|
||
* F) this_line_number:
|
||
* - первый шаг после root:
|
||
* TEXT: this_line_number = 0
|
||
* TECH/CONNECTION/USER_PARAM: this_line_number = 1
|
||
* - обычный шаг:
|
||
* TEXT: допускаем same или +1 (чтобы "edit" мог не двигать шаг)
|
||
* TECH/CONNECTION/USER_PARAM: строго prev.this + 1
|
||
*
|
||
* Какие ошибки кидает:
|
||
* - LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE
|
||
* - LINE_ERR_PARTIAL_FIELDS
|
||
* - LINE_ERR_NO_PREV
|
||
* - LINE_ERR_PREV_HASH_MISMATCH
|
||
* - LINE_ERR_LINE_CODE_MISMATCH
|
||
* - LINE_ERR_FIRST_STEP_BAD_THIS
|
||
* - LINE_ERR_THIS_LINE_BAD_STEP
|
||
*
|
||
* [2] trg_blocks_connection_state_ai (AFTER INSERT ON blocks WHEN msg_type=3)
|
||
* Поддерживает таблицу connections_state как "текущее состояние" отношений:
|
||
* - FRIEND/CONTACT/FOLLOW -> добавить/обновить состояние
|
||
* - UNFRIEND/UNCONTACT/UNFOLLOW -> удалить соответствующее "позитивное" состояние
|
||
*
|
||
* [3] trg_blocks_message_stats_like_ai (AFTER INSERT ON blocks WHEN msg_type=2 AND sub_type=LIKE)
|
||
* Поддерживает likes_count в message_stats для цели (to_*).
|
||
*
|
||
* [4] trg_blocks_message_stats_reply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=REPLY)
|
||
* Поддерживает replies_count в message_stats.
|
||
*
|
||
* [5] trg_blocks_edit_apply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=EDIT)
|
||
* Логика edit:
|
||
* - помечает исходный блок edited_by_block_number = NEW.block_number
|
||
* - увеличивает edits_count в message_stats
|
||
*/
|
||
public final class DatabaseTriggersInstaller {
|
||
|
||
private DatabaseTriggersInstaller() {}
|
||
|
||
public static void createAllTriggers(Statement st) throws SQLException {
|
||
// На всякий случай убираем старые "криво названные" триггеры,
|
||
// если они когда-то попадали в БД.
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_block_lini_integriti_by;");
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_line_integrity_bi;");
|
||
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_connection_state_ai;");
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_like_ai;");
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_reply_ai;");
|
||
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_edit_apply_ai;");
|
||
|
||
createLineIntegrityTrigger(st);
|
||
createConnectionStateTrigger(st);
|
||
createMessageStatsLikeTrigger(st);
|
||
createMessageStatsReplyTrigger(st);
|
||
createEditApplyTrigger(st);
|
||
}
|
||
|
||
private static void createLineIntegrityTrigger(Statement st) throws SQLException {
|
||
st.executeUpdate("""
|
||
CREATE TRIGGER IF NOT EXISTS trg_blocks_line_integrity_bi
|
||
BEFORE INSERT ON blocks
|
||
WHEN
|
||
NEW.line_code IS NOT NULL
|
||
OR NEW.prev_line_number IS NOT NULL
|
||
OR NEW.prev_line_hash IS NOT NULL
|
||
OR NEW.this_line_number IS NOT NULL
|
||
BEGIN
|
||
SELECT RAISE(ABORT, 'LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE')
|
||
WHERE NOT (NEW.msg_type IN (0, 1, 3, 4));
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_PARTIAL_FIELDS')
|
||
WHERE NEW.line_code IS NULL
|
||
OR NEW.prev_line_number IS NULL
|
||
OR NEW.prev_line_hash IS NULL
|
||
OR NEW.this_line_number IS NULL;
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_NO_PREV')
|
||
WHERE NOT EXISTS(
|
||
SELECT 1
|
||
FROM blocks p
|
||
WHERE p.bch_name = NEW.bch_name
|
||
AND p.block_number = NEW.prev_line_number
|
||
LIMIT 1
|
||
);
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_PREV_HASH_MISMATCH')
|
||
WHERE NOT EXISTS(
|
||
SELECT 1
|
||
FROM blocks p
|
||
WHERE p.bch_name = NEW.bch_name
|
||
AND p.block_number = NEW.prev_line_number
|
||
AND p.block_hash = NEW.prev_line_hash
|
||
LIMIT 1
|
||
);
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_LINE_CODE_MISMATCH')
|
||
WHERE NEW.prev_line_number <> NEW.line_code
|
||
AND NOT EXISTS(
|
||
SELECT 1
|
||
FROM blocks p
|
||
WHERE p.bch_name = NEW.bch_name
|
||
AND p.block_number = NEW.prev_line_number
|
||
AND p.line_code = NEW.line_code
|
||
LIMIT 1
|
||
);
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_FIRST_STEP_BAD_THIS')
|
||
WHERE NEW.prev_line_number = NEW.line_code
|
||
AND NEW.this_line_number <> (CASE WHEN NEW.msg_type = 1 THEN 0 ELSE 1 END);
|
||
|
||
SELECT RAISE(ABORT, 'LINE_ERR_THIS_LINE_BAD_STEP')
|
||
WHERE NEW.prev_line_number <> NEW.line_code
|
||
AND NOT EXISTS(
|
||
SELECT 1
|
||
FROM blocks p
|
||
WHERE p.bch_name = NEW.bch_name
|
||
AND p.block_number = NEW.prev_line_number
|
||
AND p.this_line_number IS NOT NULL
|
||
AND (
|
||
(NEW.msg_type = 1 AND
|
||
(NEW.this_line_number = p.this_line_number OR NEW.this_line_number = p.this_line_number + 1)
|
||
)
|
||
OR
|
||
(NEW.msg_type IN (0,3,4) AND NEW.this_line_number = p.this_line_number + 1)
|
||
)
|
||
LIMIT 1
|
||
);
|
||
END;
|
||
""");
|
||
}
|
||
|
||
private static void createConnectionStateTrigger(Statement st) throws SQLException {
|
||
int FRIEND = (int) DatabaseInitializer.CONNECTION_FRIEND;
|
||
int CONTACT = (int) DatabaseInitializer.CONNECTION_CONTACT;
|
||
int FOLLOW = (int) DatabaseInitializer.CONNECTION_FOLLOW;
|
||
|
||
int UNFRIEND = (int) DatabaseInitializer.CONNECTION_UNFRIEND;
|
||
int UNCONTACT = (int) DatabaseInitializer.CONNECTION_UNCONTACT;
|
||
int UNFOLLOW = (int) DatabaseInitializer.CONNECTION_UNFOLLOW;
|
||
|
||
st.executeUpdate("""
|
||
CREATE TRIGGER IF NOT EXISTS trg_blocks_connection_state_ai
|
||
AFTER INSERT ON blocks
|
||
WHEN NEW.msg_type = 3
|
||
BEGIN
|
||
-- FRIEND/CONTACT/FOLLOW:
|
||
-- 1) если записи нет — создаём
|
||
INSERT OR IGNORE INTO connections_state (
|
||
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
|
||
)
|
||
SELECT
|
||
NEW.login,
|
||
NEW.msg_sub_type,
|
||
NEW.to_login,
|
||
NEW.to_bch_name,
|
||
NEW.to_block_number,
|
||
NEW.to_block_hash
|
||
WHERE NEW.msg_sub_type IN (%d, %d, %d)
|
||
AND NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL;
|
||
|
||
-- 2) если запись есть — обновляем актуальные to_*
|
||
UPDATE connections_state
|
||
SET
|
||
to_bch_name = NEW.to_bch_name,
|
||
to_block_number = NEW.to_block_number,
|
||
to_block_hash = NEW.to_block_hash
|
||
WHERE login = NEW.login
|
||
AND rel_type = NEW.msg_sub_type
|
||
AND to_login = NEW.to_login
|
||
AND NEW.msg_sub_type IN (%d, %d, %d)
|
||
AND NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL;
|
||
|
||
-- UNFRIEND/UNCONTACT/UNFOLLOW:
|
||
-- удаляем соответствующее "позитивное" состояние
|
||
DELETE FROM connections_state
|
||
WHERE login = NEW.login
|
||
AND to_login = NEW.to_login
|
||
AND rel_type = CASE NEW.msg_sub_type
|
||
WHEN %d THEN %d
|
||
WHEN %d THEN %d
|
||
WHEN %d THEN %d
|
||
ELSE rel_type
|
||
END
|
||
AND NEW.msg_sub_type IN (%d, %d, %d);
|
||
END;
|
||
""".formatted(
|
||
FRIEND, CONTACT, FOLLOW,
|
||
FRIEND, CONTACT, FOLLOW,
|
||
|
||
UNFRIEND, FRIEND,
|
||
UNCONTACT, CONTACT,
|
||
UNFOLLOW, FOLLOW,
|
||
|
||
UNFRIEND, UNCONTACT, UNFOLLOW
|
||
));
|
||
}
|
||
|
||
private static void createMessageStatsLikeTrigger(Statement st) throws SQLException {
|
||
int LIKE = (int) DatabaseInitializer.REACTION_LIKE;
|
||
|
||
st.executeUpdate("""
|
||
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_like_ai
|
||
AFTER INSERT ON blocks
|
||
WHEN NEW.msg_type = 2 AND NEW.msg_sub_type = %d
|
||
BEGIN
|
||
-- создаём строку, если её не было
|
||
INSERT OR IGNORE INTO message_stats (
|
||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||
likes_count, replies_count, edits_count
|
||
)
|
||
SELECT
|
||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||
0, 0, 0
|
||
WHERE NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
|
||
-- +1 like
|
||
UPDATE message_stats
|
||
SET likes_count = likes_count + 1
|
||
WHERE to_login = NEW.to_login
|
||
AND to_bch_name = NEW.to_bch_name
|
||
AND to_block_number = NEW.to_block_number
|
||
AND to_block_hash = NEW.to_block_hash
|
||
AND NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
END;
|
||
""".formatted(LIKE));
|
||
}
|
||
|
||
private static void createMessageStatsReplyTrigger(Statement st) throws SQLException {
|
||
int REPLY = (int) DatabaseInitializer.TEXT_REPLY;
|
||
|
||
st.executeUpdate("""
|
||
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_reply_ai
|
||
AFTER INSERT ON blocks
|
||
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type = %d
|
||
BEGIN
|
||
INSERT OR IGNORE INTO message_stats (
|
||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||
likes_count, replies_count, edits_count
|
||
)
|
||
SELECT
|
||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||
0, 0, 0
|
||
WHERE NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
|
||
UPDATE message_stats
|
||
SET replies_count = replies_count + 1
|
||
WHERE to_login = NEW.to_login
|
||
AND to_bch_name = NEW.to_bch_name
|
||
AND to_block_number = NEW.to_block_number
|
||
AND to_block_hash = NEW.to_block_hash
|
||
AND NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
END;
|
||
""".formatted(REPLY));
|
||
}
|
||
|
||
private static void createEditApplyTrigger(Statement st) throws SQLException {
|
||
int EDIT = (int) DatabaseInitializer.TEXT_EDIT;
|
||
|
||
st.executeUpdate("""
|
||
CREATE TRIGGER IF NOT EXISTS trg_blocks_edit_apply_ai
|
||
AFTER INSERT ON blocks
|
||
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type = %d
|
||
BEGIN
|
||
-- 1) помечаем исходный блок, что его "перекрыл" этот edit
|
||
UPDATE blocks
|
||
SET edited_by_block_number = NEW.block_number
|
||
WHERE login = NEW.login
|
||
AND bch_name = NEW.bch_name
|
||
AND block_number = NEW.to_block_number
|
||
AND NEW.to_block_number IS NOT NULL;
|
||
|
||
-- 2) создаём stats-строку если её не было
|
||
INSERT OR IGNORE INTO message_stats (
|
||
to_login, to_bch_name, to_block_number, to_block_hash,
|
||
likes_count, replies_count, edits_count
|
||
)
|
||
SELECT
|
||
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
||
0, 0, 0
|
||
WHERE NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
|
||
-- 3) +1 edit
|
||
UPDATE message_stats
|
||
SET edits_count = edits_count + 1
|
||
WHERE to_login = NEW.to_login
|
||
AND to_bch_name = NEW.to_bch_name
|
||
AND to_block_number = NEW.to_block_number
|
||
AND to_block_hash = NEW.to_block_hash
|
||
AND NEW.to_login IS NOT NULL
|
||
AND NEW.to_bch_name IS NOT NULL
|
||
AND NEW.to_block_number IS NOT NULL
|
||
AND NEW.to_block_hash IS NOT NULL;
|
||
END;
|
||
""".formatted(EDIT));
|
||
}
|
||
}
|
||
package shine.db.entities;
|
||
|
||
/**
|
||
* Модель активной сессии (таблица active_sessions).
|
||
*/
|
||
public class ActiveSessionEntry {
|
||
|
||
private String sessionId;
|
||
private String login;
|
||
|
||
/** session_key: публичный ключ сессии (base64 от 32 байт). */
|
||
private String sessionKey;
|
||
|
||
private String storagePwd;
|
||
private long sessionCreatedAtMs;
|
||
private long lastAuthirificatedAtMs;
|
||
|
||
private String pushEndpoint;
|
||
private String pushP256dhKey;
|
||
private String pushAuthKey;
|
||
|
||
private String clientIp;
|
||
private String clientInfoFromClient;
|
||
private String clientInfoFromRequest;
|
||
private String userLanguage;
|
||
|
||
public ActiveSessionEntry() { }
|
||
|
||
public ActiveSessionEntry(String sessionId,
|
||
String login,
|
||
String sessionKey,
|
||
String storagePwd,
|
||
long sessionCreatedAtMs,
|
||
long lastAuthirificatedAtMs,
|
||
String pushEndpoint,
|
||
String pushP256dhKey,
|
||
String pushAuthKey,
|
||
String clientIp,
|
||
String clientInfoFromClient,
|
||
String clientInfoFromRequest,
|
||
String userLanguage) {
|
||
this.sessionId = sessionId;
|
||
this.login = login;
|
||
this.sessionKey = sessionKey;
|
||
this.storagePwd = storagePwd;
|
||
this.sessionCreatedAtMs = sessionCreatedAtMs;
|
||
this.lastAuthirificatedAtMs = lastAuthirificatedAtMs;
|
||
this.pushEndpoint = pushEndpoint;
|
||
this.pushP256dhKey = pushP256dhKey;
|
||
this.pushAuthKey = pushAuthKey;
|
||
this.clientIp = clientIp;
|
||
this.clientInfoFromClient = clientInfoFromClient;
|
||
this.clientInfoFromRequest = clientInfoFromRequest;
|
||
this.userLanguage = userLanguage;
|
||
}
|
||
|
||
public String getSessionId() { return sessionId; }
|
||
public void setSessionId(String sessionId) { this.sessionId = sessionId; }
|
||
|
||
public String getLogin() { return login; }
|
||
public void setLogin(String login) { this.login = login; }
|
||
|
||
public String getSessionKey() { return sessionKey; }
|
||
public void setSessionKey(String sessionKey) { this.sessionKey = sessionKey; }
|
||
|
||
public String getStoragePwd() { return storagePwd; }
|
||
public void setStoragePwd(String storagePwd) { this.storagePwd = storagePwd; }
|
||
|
||
public long getSessionCreatedAtMs() { return sessionCreatedAtMs; }
|
||
public void setSessionCreatedAtMs(long sessionCreatedAtMs) { this.sessionCreatedAtMs = sessionCreatedAtMs; }
|
||
|
||
public long getLastAuthirificatedAtMs() { return lastAuthirificatedAtMs; }
|
||
public void setLastAuthirificatedAtMs(long lastAuthirificatedAtMs) { this.lastAuthirificatedAtMs = lastAuthirificatedAtMs; }
|
||
|
||
public String getPushEndpoint() { return pushEndpoint; }
|
||
public void setPushEndpoint(String pushEndpoint) { this.pushEndpoint = pushEndpoint; }
|
||
|
||
public String getPushP256dhKey() { return pushP256dhKey; }
|
||
public void setPushP256dhKey(String pushP256dhKey) { this.pushP256dhKey = pushP256dhKey; }
|
||
|
||
public String getPushAuthKey() { return pushAuthKey; }
|
||
public void setPushAuthKey(String pushAuthKey) { this.pushAuthKey = pushAuthKey; }
|
||
|
||
public String getClientIp() { return clientIp; }
|
||
public void setClientIp(String clientIp) { this.clientIp = clientIp; }
|
||
|
||
public String getClientInfoFromClient() { return clientInfoFromClient; }
|
||
public void setClientInfoFromClient(String clientInfoFromClient) { this.clientInfoFromClient = clientInfoFromClient; }
|
||
|
||
public String getClientInfoFromRequest() { return clientInfoFromRequest; }
|
||
public void setClientInfoFromRequest(String clientInfoFromRequest) { this.clientInfoFromRequest = clientInfoFromRequest; }
|
||
|
||
public String getUserLanguage() { return userLanguage; }
|
||
public void setUserLanguage(String userLanguage) { this.userLanguage = userLanguage; }
|
||
}
|
||
package shine.db.entities;
|
||
|
||
import java.util.Base64;
|
||
|
||
/**
|
||
* Агрегатная сущность текущего состояния блокчейна.
|
||
*
|
||
* ВАЖНО:
|
||
* - Убраны все поля линий line0..7 (они больше не нужны).
|
||
* - Оставляем:
|
||
* last_block_number
|
||
* last_block_hash
|
||
*
|
||
* Остальные поля (login, blockchain_key, лимиты) оставлены как в проекте,
|
||
* потому что серверу они реально нужны (ключ подписи/лимит файла).
|
||
*/
|
||
public final class BlockchainStateEntry {
|
||
|
||
private String blockchainName;
|
||
private String login;
|
||
|
||
private String blockchainKey; // Base64(32)
|
||
|
||
private long sizeLimit;
|
||
private long fileSizeBytes;
|
||
|
||
private int lastBlockNumber; // было last_global_number
|
||
private byte[] lastBlockHash; // было last_global_hash (nullable)
|
||
|
||
private long updatedAtMs;
|
||
|
||
public BlockchainStateEntry() {}
|
||
|
||
public String getBlockchainName() { return blockchainName; }
|
||
public void setBlockchainName(String blockchainName) { this.blockchainName = blockchainName; }
|
||
|
||
public String getLogin() { return login; }
|
||
public void setLogin(String login) { this.login = login; }
|
||
|
||
public String getBlockchainKey() { return blockchainKey; }
|
||
public void setBlockchainKey(String blockchainKey) { this.blockchainKey = blockchainKey; }
|
||
|
||
public byte[] getBlockchainKeyBytes() {
|
||
if (blockchainKey == null) return null;
|
||
String s = blockchainKey.trim();
|
||
if (s.isEmpty()) return null;
|
||
try {
|
||
byte[] b = Base64.getDecoder().decode(s);
|
||
return (b != null && b.length == 32) ? b : null;
|
||
} catch (IllegalArgumentException e) {
|
||
return null;
|
||
}
|
||
}
|
||
|
||
public long getSizeLimit() { return sizeLimit; }
|
||
public void setSizeLimit(long sizeLimit) { this.sizeLimit = sizeLimit; }
|
||
|
||
public long getFileSizeBytes() { return fileSizeBytes; }
|
||
public void setFileSizeBytes(long fileSizeBytes) { this.fileSizeBytes = fileSizeBytes; }
|
||
|
||
public int getLastBlockNumber() { return lastBlockNumber; }
|
||
public void setLastBlockNumber(int lastBlockNumber) { this.lastBlockNumber = lastBlockNumber; }
|
||
|
||
public byte[] getLastBlockHash() { return lastBlockHash; }
|
||
public void setLastBlockHash(byte[] lastBlockHash) { this.lastBlockHash = lastBlockHash; }
|
||
|
||
public long getUpdatedAtMs() { return updatedAtMs; }
|
||
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
|
||
}
|
||
package shine.db.entities;
|
||
|
||
/**
|
||
* Запись блока (таблица blocks) — обновлённая модель под новый формат.
|
||
*
|
||
* Храним:
|
||
* - login, bch_name (как было в проекте, чтобы не ломать общую БД)
|
||
* - block_number (глобальный номер в этой цепочке)
|
||
* - block_bytes (полный блок: preimage + signature)
|
||
* - block_hash (32 байта вычисленный SHA-256(preimage))
|
||
* - block_signature (64 байта)
|
||
*
|
||
* Опционально:
|
||
* - line_code / prev_line_number / prev_line_hash / this_line_number
|
||
*
|
||
* Плюс поля индексации:
|
||
* - msg_type / msg_sub_type
|
||
* - to_* (если есть target)
|
||
* - edited_by_block_number (для TEXT_EDIT)
|
||
*/
|
||
public class BlockEntry {
|
||
|
||
private String login;
|
||
private String bchName;
|
||
|
||
private int blockNumber;
|
||
|
||
private int msgType;
|
||
private int msgSubType;
|
||
|
||
private byte[] blockBytes;
|
||
|
||
private String toLogin;
|
||
private String toBchName;
|
||
private Integer toBlockNumber;
|
||
private byte[] toBlockHash;
|
||
|
||
private byte[] blockHash;
|
||
private byte[] blockSignature;
|
||
|
||
private Integer editedByBlockNumber;
|
||
|
||
// NEW:
|
||
private Integer lineCode;
|
||
|
||
private Integer prevLineNumber;
|
||
private byte[] prevLineHash;
|
||
private Integer thisLineNumber;
|
||
|
||
public BlockEntry() {}
|
||
|
||
public String getLogin() { return login; }
|
||
public void setLogin(String login) { this.login = login; }
|
||
|
||
public String getBchName() { return bchName; }
|
||
public void setBchName(String bchName) { this.bchName = bchName; }
|
||
|
||
public int getBlockNumber() { return blockNumber; }
|
||
public void setBlockNumber(int blockNumber) { this.blockNumber = blockNumber; }
|
||
|
||
public int getMsgType() { return msgType; }
|
||
public void setMsgType(int msgType) { this.msgType = msgType; }
|
||
|
||
public int getMsgSubType() { return msgSubType; }
|
||
public void setMsgSubType(int msgSubType) { this.msgSubType = msgSubType; }
|
||
|
||
public byte[] getBlockBytes() { return blockBytes; }
|
||
public void setBlockBytes(byte[] blockBytes) { this.blockBytes = blockBytes; }
|
||
|
||
public String getToLogin() { return toLogin; }
|
||
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
|
||
|
||
public String getToBchName() { return toBchName; }
|
||
public void setToBchName(String toBchName) { this.toBchName = toBchName; }
|
||
|
||
public Integer getToBlockNumber() { return toBlockNumber; }
|
||
public void setToBlockNumber(Integer toBlockNumber) { this.toBlockNumber = toBlockNumber; }
|
||
|
||
public byte[] getToBlockHash() { return toBlockHash; }
|
||
public void setToBlockHash(byte[] toBlockHash) { this.toBlockHash = toBlockHash; }
|
||
|
||
public byte[] getBlockHash() { return blockHash; }
|
||
public void setBlockHash(byte[] blockHash) { this.blockHash = blockHash; }
|
||
|
||
public byte[] getBlockSignature() { return blockSignature; }
|
||
public void setBlockSignature(byte[] blockSignature) { this.blockSignature = blockSignature; }
|
||
|
||
public Integer getEditedByBlockNumber() { return editedByBlockNumber; }
|
||
public void setEditedByBlockNumber(Integer editedByBlockNumber) { this.editedByBlockNumber = editedByBlockNumber; }
|
||
|
||
// NEW:
|
||
public Integer getLineCode() { return lineCode; }
|
||
public void setLineCode(Integer lineCode) { this.lineCode = lineCode; }
|
||
|
||
public Integer getPrevLineNumber() { return prevLineNumber; }
|
||
public void setPrevLineNumber(Integer prevLineNumber) { this.prevLineNumber = prevLineNumber; }
|
||
|
||
public byte[] getPrevLineHash() { return prevLineHash; }
|
||
public void setPrevLineHash(byte[] prevLineHash) { this.prevLineHash = prevLineHash; }
|
||
|
||
public Integer getThisLineNumber() { return thisLineNumber; }
|
||
public void setThisLineNumber(Integer thisLineNumber) { this.thisLineNumber = thisLineNumber; }
|
||
}
|
||
package shine.db.entities;
|
||
|
||
/**
|
||
* Запись в таблице ip_geo_cache.
|
||
*/
|
||
public class IpGeoCacheEntry {
|
||
|
||
private String ip;
|
||
private String geo;
|
||
private long updatedAtMs;
|
||
|
||
public IpGeoCacheEntry() {
|
||
}
|
||
|
||
public IpGeoCacheEntry(String ip, String geo, long updatedAtMs) {
|
||
this.ip = ip;
|
||
this.geo = geo;
|
||
this.updatedAtMs = updatedAtMs;
|
||
}
|
||
|
||
public String getIp() {
|
||
return ip;
|
||
}
|
||
|
||
public void setIp(String ip) {
|
||
this.ip = ip;
|
||
}
|
||
|
||
public String getGeo() {
|
||
return geo;
|
||
}
|
||
|
||
public void setGeo(String geo) {
|
||
this.geo = geo;
|
||
}
|
||
|
||
public long getUpdatedAtMs() {
|
||
return updatedAtMs;
|
||
}
|
||
|
||
public void setUpdatedAtMs(long updatedAtMs) {
|
||
this.updatedAtMs = updatedAtMs;
|
||
}
|
||
}
|
||
package shine.db.entities;
|
||
|
||
import java.util.Base64;
|
||
|
||
/**
|
||
* SolanaUserEntry — локальная запись пользователя из Solana.
|
||
*
|
||
* Таблица: solana_users
|
||
*
|
||
* Поля:
|
||
* - login — PRIMARY KEY (TEXT) (case-insensitive на уровне COLLATE NOCASE)
|
||
* - blockchain_name — TEXT NOT NULL
|
||
* - solana_key — TEXT NOT NULL
|
||
* - blockchain_key — TEXT NOT NULL
|
||
* - device_key — TEXT NOT NULL
|
||
*/
|
||
public class SolanaUserEntry {
|
||
|
||
private String login;
|
||
|
||
private String blockchainName;
|
||
|
||
/** Ключ пользователя Solana (публичный ключ логина) */
|
||
private String solanaKey;
|
||
|
||
/** Ключ блокчейна (публичный ключ блокчейна) */
|
||
private String blockchainKey;
|
||
|
||
/** Ключ устройства (публичный ключ устройства) */
|
||
private String deviceKey;
|
||
|
||
public SolanaUserEntry() {}
|
||
|
||
public SolanaUserEntry(String login,
|
||
String blockchainName,
|
||
String solanaKey,
|
||
String blockchainKey,
|
||
String deviceKey) {
|
||
this.login = login;
|
||
this.blockchainName = blockchainName;
|
||
this.solanaKey = solanaKey;
|
||
this.blockchainKey = blockchainKey;
|
||
this.deviceKey = deviceKey;
|
||
}
|
||
|
||
public String getLogin() { return login; }
|
||
public void setLogin(String login) { this.login = login; }
|
||
|
||
public String getBlockchainName() { return blockchainName; }
|
||
public void setBlockchainName(String blockchainName) { this.blockchainName = blockchainName; }
|
||
|
||
public String getSolanaKey() { return solanaKey; }
|
||
public void setSolanaKey(String solanaKey) { this.solanaKey = solanaKey; }
|
||
|
||
public String getBlockchainKey() { return blockchainKey; }
|
||
public void setBlockchainKey(String blockchainKey) { this.blockchainKey = blockchainKey; }
|
||
|
||
public String getDeviceKey() { return deviceKey; }
|
||
public void setDeviceKey(String deviceKey) { this.deviceKey = deviceKey; }
|
||
|
||
// оставляю этот метод как утилиту (иногда удобно), но он работает только для deviceKey:
|
||
public byte[] getDeviceKeyByte() {
|
||
if (deviceKey == null) return null;
|
||
String s = deviceKey.trim();
|
||
if (s.isEmpty()) return null;
|
||
|
||
try {
|
||
byte[] b = Base64.getDecoder().decode(s);
|
||
if (b != null && b.length == 32) return b;
|
||
} catch (IllegalArgumentException ignore) {}
|
||
|
||
if (s.length() == 64 && s.matches("^[0-9a-fA-F]+$")) {
|
||
byte[] out = new byte[32];
|
||
for (int i = 0; i < 32; i++) {
|
||
int hi = Character.digit(s.charAt(i * 2), 16);
|
||
int lo = Character.digit(s.charAt(i * 2 + 1), 16);
|
||
out[i] = (byte) ((hi << 4) | lo);
|
||
}
|
||
return out;
|
||
}
|
||
|
||
return null;
|
||
}
|
||
}
|
||
package shine.db.entities;
|
||
|
||
/**
|
||
* UserParamEntry — сохранённый параметр пользователя.
|
||
*
|
||
* Таблица: users_params
|
||
* - login TEXT NOT NULL
|
||
* - param TEXT NOT NULL
|
||
* - time_ms INTEGER NOT NULL
|
||
* - value TEXT NOT NULL
|
||
* - device_key TEXT NULL
|
||
* - signature TEXT NULL
|
||
*/
|
||
public class UserParamEntry {
|
||
|
||
private String login;
|
||
private String param;
|
||
private long timeMs;
|
||
private String value;
|
||
|
||
private String deviceKey;
|
||
private String signature;
|
||
|
||
public UserParamEntry() {}
|
||
|
||
public UserParamEntry(String login, String param, long timeMs, String value, String deviceKey, String signature) {
|
||
this.login = login;
|
||
this.param = param;
|
||
this.timeMs = timeMs;
|
||
this.value = value;
|
||
this.deviceKey = deviceKey;
|
||
this.signature = signature;
|
||
}
|
||
|
||
public String getLogin() { return login; }
|
||
public void setLogin(String login) { this.login = login; }
|
||
|
||
public String getParam() { return param; }
|
||
public void setParam(String param) { this.param = param; }
|
||
|
||
public long getTimeMs() { return timeMs; }
|
||
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
|
||
|
||
public String getValue() { return value; }
|
||
public void setValue(String value) { this.value = value; }
|
||
|
||
public String getDeviceKey() { return deviceKey; }
|
||
public void setDeviceKey(String deviceKey) { this.deviceKey = deviceKey; }
|
||
|
||
public String getSignature() { return signature; }
|
||
public void setSignature(String signature) { this.signature = signature; }
|
||
}
|
||
package shine.db;
|
||
|
||
/**
|
||
* MsgSubType — единое место для ВСЕХ subType сообщений (msg_sub_type).
|
||
*
|
||
* ВАЖНО:
|
||
* - Значения должны совпадать с body-классами (TextBody/ReactionBody/ConnectionBody/UserParamBody/HeaderBody).
|
||
* - После релиза менять числа нельзя (иначе ломается совместимость данных).
|
||
*/
|
||
public final class MsgSubType {
|
||
|
||
private MsgSubType() {}
|
||
|
||
/* ===================== HEADER (msg_type=0) ===================== */
|
||
|
||
/** HeaderBody: subType всегда 0 (compat). */
|
||
public static final short HEADER_COMPAT = 0;
|
||
|
||
/* ===================== TEXT (msg_type=1) ===================== */
|
||
|
||
/** Новая публикация. */
|
||
public static final short TEXT_NEW = 1;
|
||
|
||
/** Ответ (reply). */
|
||
public static final short TEXT_REPLY = 2;
|
||
|
||
/** Репост (repost). */
|
||
public static final short TEXT_REPOST = 3;
|
||
|
||
/** Редактирование (edit). */
|
||
public static final short TEXT_EDIT = 10;
|
||
|
||
/* ===================== REACTION (msg_type=2) ===================== */
|
||
|
||
/** Лайк (LIKE). */
|
||
public static final short REACTION_LIKE = 1;
|
||
|
||
/* ===================== CONNECTION (msg_type=3) ===================== */
|
||
/**
|
||
* Совпадает с ConnectionBody:
|
||
* SET: FRIEND=10, CONTACT=20, FOLLOW=30
|
||
* UNSET: UNFRIEND=11, UNCONTACT=21, UNFOLLOW=31
|
||
*/
|
||
|
||
/** Добавить в друзья. */
|
||
public static final short CONNECTION_FRIEND = 10;
|
||
|
||
/** Удалить из друзей. */
|
||
public static final short CONNECTION_UNFRIEND = 11;
|
||
|
||
/** Добавить в контакты. */
|
||
public static final short CONNECTION_CONTACT = 20;
|
||
|
||
/** Удалить из контактов. */
|
||
public static final short CONNECTION_UNCONTACT = 21;
|
||
|
||
/** Подписаться (follow). */
|
||
public static final short CONNECTION_FOLLOW = 30;
|
||
|
||
/** Отписаться (unfollow). */
|
||
public static final short CONNECTION_UNFOLLOW = 31;
|
||
|
||
/* ===================== USER_PARAM (msg_type=4) ===================== */
|
||
|
||
/** Параметр профиля key/value (обе строки). */
|
||
public static final short USER_PARAM_TEXT_TEXT = 1;
|
||
|
||
/* ===================== РЕЗЕРВ НА БУДУЩЕЕ ===================== */
|
||
// Если позже захочешь BLOCK/UNBLOCK — лучше добавить НОВЫЕ значения,
|
||
// не трогая 10/20/30 и 11/21/31 (например, 40/41).
|
||
// public static final short CONNECTION_BLOCK = 40;
|
||
// public static final short CONNECTION_UNBLOCK = 41;
|
||
}
|
||
package shine.db;
|
||
|
||
import utils.config.AppConfig;
|
||
|
||
import java.nio.file.Files;
|
||
import java.nio.file.Path;
|
||
import java.nio.file.Paths;
|
||
import java.sql.Connection;
|
||
import java.sql.DriverManager;
|
||
import java.sql.SQLException;
|
||
import java.sql.Statement;
|
||
|
||
public final class SqliteDbController {
|
||
|
||
private static volatile SqliteDbController instance;
|
||
|
||
private final String jdbcUrl;
|
||
|
||
private SqliteDbController() {
|
||
try {
|
||
Class.forName("org.sqlite.JDBC");
|
||
} catch (ClassNotFoundException e) {
|
||
throw new RuntimeException("SQLite JDBC driver not found", e);
|
||
}
|
||
|
||
String dbPath = AppConfig.getInstance().getParam("db.path");
|
||
if (dbPath == null || dbPath.isBlank()) {
|
||
throw new RuntimeException("Config param 'db.path' is not set in application.properties");
|
||
}
|
||
|
||
Path dbFile = Paths.get(dbPath);
|
||
|
||
if (!Files.exists(dbFile)) {
|
||
System.out.println("[DB] Файл БД не найден: " + dbFile.toAbsolutePath());
|
||
System.out.println("[DB] Создаём новую БД с помощью DatabaseInitializer...");
|
||
DatabaseInitializer.createNewDB(new String[0]);
|
||
}
|
||
|
||
this.jdbcUrl = "jdbc:sqlite:" + dbPath;
|
||
}
|
||
|
||
public static SqliteDbController getInstance() {
|
||
if (instance == null) {
|
||
synchronized (SqliteDbController.class) {
|
||
if (instance == null) {
|
||
instance = new SqliteDbController();
|
||
}
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
public Connection getConnection() throws SQLException {
|
||
Connection conn = DriverManager.getConnection(jdbcUrl);
|
||
conn.setAutoCommit(true);
|
||
|
||
try (Statement st = conn.createStatement()) {
|
||
st.execute("PRAGMA foreign_keys = ON");
|
||
st.execute("PRAGMA journal_mode = WAL");
|
||
st.execute("PRAGMA synchronous = NORMAL");
|
||
st.execute("PRAGMA busy_timeout = 5000");
|
||
}
|
||
|
||
return conn;
|
||
}
|
||
|
||
public void close() {
|
||
// no-op
|
||
}
|
||
}
|