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

17 12 25

Browse Source 
Заработало
This commit is contained in:
AidarKC 2025-12-18 15:37:28 +03:00
parent 4fb6b10a97
commit 1b1da19d3d
5 changed files with 58 additions and 84 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

90
shine-server-db/src/main/java/shine/db/dao/ActiveSessionsDAO.java
View File

@ -9,50 +9,23 @@ import java.util.List;
/**
* DAO для таблицы active_sessions.
*
* Здесь мы храним данные об активных сессиях пользователя (для wss-соединений).
*
* Структура таблицы:
*
* CREATE TABLE active_sessions (
* sessionId TEXT NOT NULL PRIMARY KEY,
* loginId INTEGER NOT NULL,
* sessionPwd TEXT NOT NULL,
* storagePwd TEXT NOT NULL,
* sessionCreatedAtMs INTEGER NOT NULL,
* lastAuthirificatedAtMs INTEGER NOT NULL,
* pushEndpoint TEXT,
* pushP256dhKey TEXT,
* pushAuthKey TEXT,
* clientIp TEXT,
* clientInfoFromClient TEXT,
* clientInfoFromRequest TEXT,
* userLanguage TEXT,
* FOREIGN KEY (loginId) REFERENCES solana_users(loginId)
* );
*/
public final class ActiveSessionsDAO {
private static volatile ActiveSessionsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private ActiveSessionsDAO() {
}
private ActiveSessionsDAO() { }
public static ActiveSessionsDAO getInstance() {
if (instance == null) {
synchronized (ActiveSessionsDAO.class) {
if (instance == null) {
instance = new ActiveSessionsDAO();
}
if (instance == null) instance = new ActiveSessionsDAO();
}
}
return instance;
}
/**
* Вставка новой сессии.
*/
public void insert(ActiveSessionEntry session) throws SQLException {
String sql = """
INSERT INTO active_sessions (
@ -72,7 +45,9 @@ public final class ActiveSessionsDAO {
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, session.getSessionId());
ps.setLong(2, session.getLoginId());
ps.setString(3, session.getSessionPwd());
@ -91,9 +66,6 @@ public final class ActiveSessionsDAO {
}
}
/**
* Получить сессию по sessionId.
*/
public ActiveSessionEntry getBySessionId(String sessionId) throws SQLException {
String sql = """
SELECT
@ -114,20 +86,18 @@ public final class ActiveSessionsDAO {
WHERE sessionId = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
return null;
}
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/**
* Получить список всех активных сессий пользователя по loginId.
*/
public List<ActiveSessionEntry> getByLoginId(long loginId) throws SQLException {
String sql = """
SELECT
@ -150,22 +120,19 @@ public final class ActiveSessionsDAO {
List<ActiveSessionEntry> result = new ArrayList<>();
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
result.add(mapRow(rs));
}
while (rs.next()) result.add(mapRow(rs));
}
}
return result;
}
/**
* Обновить только lastAuthirificatedAtMs для конкретной сессии.
* (оставляю для совместимости, вдруг ещё где-то используется)
*/
public void updateLastAuthirificatedAtMs(String sessionId, long lastAuthMs) throws SQLException {
String sql = """
UPDATE active_sessions
@ -173,21 +140,15 @@ public final class ActiveSessionsDAO {
WHERE sessionId = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, lastAuthMs);
ps.setString(2, sessionId);
ps.executeUpdate();
}
}
/**
* Обновление метаданных при RefreshSession:
* - lastAuthirificatedAtMs
* - clientIp
* - clientInfoFromClient
* - clientInfoFromRequest
* - userLanguage
*/
public void updateOnRefresh(
String sessionId,
long lastAuthMs,
@ -208,7 +169,9 @@ public final class ActiveSessionsDAO {
WHERE sessionId = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, lastAuthMs);
ps.setString(2, clientIp);
ps.setString(3, clientInfoFromClient);
@ -219,22 +182,17 @@ public final class ActiveSessionsDAO {
}
}
/**
* Удаление записи по sessionId.
* Если записи нет — просто ничего не удалит (0 строк).
*/
public void deleteBySessionId(String sessionId) throws SQLException {
String sql = "DELETE FROM active_sessions WHERE sessionId = ?";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
ps.executeUpdate();
}
}
/**
* Маппинг ResultSet → ActiveSession (все 13 полей).
*/
private ActiveSessionEntry mapRow(ResultSet rs) throws SQLException {
String sessionId = rs.getString("sessionId");
long loginId = rs.getLong("loginId");

2
shine-server-db/src/main/java/shine/db/dao/BlockchainStateDAO.java
View File

@ -87,7 +87,7 @@ public final class BlockchainStateDAO {
line7_last_number, line7_last_hash,
updated_at_ms
) VALUES (
?,?,?,?,?,?,?,
?,?,?,?,?,?,?,?, -- ✅ было 7, стало 8
?,?,
?,?,
?,?,

12
shine-server-db/src/main/java/shine/db/dao/IpGeoCacheDAO.java
View File

@ -47,7 +47,9 @@ public final class IpGeoCacheDAO {
updated_at_ms = excluded.updated_at_ms
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getIp());
ps.setString(2, entry.getGeo());
ps.setLong(3, entry.getUpdatedAtMs());
@ -66,7 +68,9 @@ public final class IpGeoCacheDAO {
WHERE ip = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ip);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) {
@ -84,7 +88,9 @@ public final class IpGeoCacheDAO {
public int deleteOlderThan(long thresholdMs) throws SQLException {
String sql = "DELETE FROM ip_geo_cache WHERE updated_at_ms < ?";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, thresholdMs);
return ps.executeUpdate();
}

19
shine-server-db/src/main/java/shine/db/dao/SolanaUsersDAO.java
View File

@ -42,7 +42,9 @@ public final class SolanaUsersDAO {
VALUES (?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, user.getLogin());
ps.setLong(2, user.getLoginId());
ps.setLong(3, user.getBchId());
@ -66,9 +68,10 @@ public final class SolanaUsersDAO {
WHERE loginId = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
ps.setLong(1, loginId);
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
@ -100,9 +103,10 @@ public final class SolanaUsersDAO {
WHERE LOWER(login) = LOWER(?)
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
ps.setString(1, login);
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
@ -121,9 +125,10 @@ public final class SolanaUsersDAO {
List<SolanaUserEntry> result = new ArrayList<>();
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
ps.setString(1, prefix.toLowerCase() + "%");
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, prefix.toLowerCase() + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));
}

13
shine-server-db/src/main/java/shine/db/dao/UserParamsDAO.java
View File

@ -8,7 +8,6 @@ import java.util.ArrayList;
import java.util.List;
/** Здесь зраним сохранённые параметры пользователей (в основном до каково сообщения просмотрены ленты) */
public final class UserParamsDAO {
private static volatile UserParamsDAO instance;
@ -53,7 +52,9 @@ public final class UserParamsDAO {
signature = excluded.signature
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, param.getLoginId());
ps.setString(2, param.getParam());
ps.setLong(3, param.getBchChannelId());
@ -82,7 +83,9 @@ public final class UserParamsDAO {
WHERE loginId = ? AND param = ?
""";
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setString(2, paramName);
try (ResultSet rs = ps.executeQuery()) {
@ -112,7 +115,9 @@ public final class UserParamsDAO {
List<UserParamEntry> result = new ArrayList<>();
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
try (Connection c = db.getConnection();
PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));