SHA256
160 lines
6.5 KiB
Java
160 lines
6.5 KiB
Java
package shine.db.dao;
|
||
|
||
import shine.db.DbController;
|
||
import shine.db.KeyEncodingUtil;
|
||
import shine.db.entities.UserAccessServerRouteEntry;
|
||
|
||
import java.sql.Connection;
|
||
import java.sql.PreparedStatement;
|
||
import java.sql.ResultSet;
|
||
import java.sql.SQLException;
|
||
import java.util.ArrayList;
|
||
import java.util.List;
|
||
|
||
/**
|
||
* DAO локальной routing-проекции access servers пользователей.
|
||
*/
|
||
public final class UserAccessServersCurrentDAO {
|
||
|
||
private static volatile UserAccessServersCurrentDAO instance;
|
||
private final DbController db = DbController.getInstance();
|
||
|
||
private UserAccessServersCurrentDAO() {}
|
||
|
||
public static UserAccessServersCurrentDAO getInstance() {
|
||
if (instance == null) {
|
||
synchronized (UserAccessServersCurrentDAO.class) {
|
||
if (instance == null) instance = new UserAccessServersCurrentDAO();
|
||
}
|
||
}
|
||
return instance;
|
||
}
|
||
|
||
public List<UserAccessServerRouteEntry> listByUserLogin(String userLogin) throws SQLException {
|
||
try (Connection c = db.getConnection()) {
|
||
return listByUserLogin(c, userLogin);
|
||
}
|
||
}
|
||
|
||
/**
|
||
* Возвращает первый access server пользователя ровно в порядке
|
||
* solana_user_pda_current.access_servers_json (access_servers[0]).
|
||
*
|
||
* В user_access_servers_current порядок массива намеренно не хранится,
|
||
* поэтому для auth-routing первичный сервер читается напрямую из PDA snapshot.
|
||
*/
|
||
public UserAccessServerRouteEntry findPrimaryByUserLogin(String userLogin) throws SQLException {
|
||
String sql = """
|
||
SELECT
|
||
i.login AS user_login,
|
||
s.login AS server_login,
|
||
s.server_address AS server_url,
|
||
s.client_key AS server_client_key
|
||
FROM user_identity_current i
|
||
LEFT JOIN solana_user_pda_current u ON u.login = i.login
|
||
LEFT JOIN shine_namespace_current ns ON ns.namespace = i.namespace
|
||
CROSS JOIN LATERAL (
|
||
SELECT CASE WHEN i.registration_status = 'FREE_CERT'
|
||
THEN ns.default_server_login
|
||
ELSE (SELECT BTRIM(a.login_value)
|
||
FROM jsonb_array_elements_text(
|
||
COALESCE(NULLIF(u.access_servers_json, ''), '[]')::jsonb)
|
||
WITH ORDINALITY AS a(login_value, ord)
|
||
WHERE a.ord = 1)
|
||
END AS server_login
|
||
) primary_access
|
||
JOIN solana_user_pda_current s
|
||
ON s.normalized_login = LOWER(primary_access.server_login)
|
||
AND s.is_server = TRUE
|
||
AND BTRIM(COALESCE(s.server_address, '')) <> ''
|
||
WHERE i.normalized_login = LOWER(BTRIM(?))
|
||
LIMIT 1
|
||
""";
|
||
try (Connection c = db.getConnection();
|
||
PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, userLogin);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
if (!rs.next()) return null;
|
||
return mapRow(rs);
|
||
}
|
||
}
|
||
}
|
||
|
||
public List<UserAccessServerRouteEntry> listByUserLogin(Connection c, String userLogin) throws SQLException {
|
||
String sql = """
|
||
SELECT user_login, server_login, server_url, server_client_key
|
||
FROM user_access_servers_current
|
||
WHERE LOWER(user_login) = LOWER(?)
|
||
ORDER BY server_login
|
||
""";
|
||
List<UserAccessServerRouteEntry> result = new ArrayList<>();
|
||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, userLogin);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) {
|
||
result.add(mapRow(rs));
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
public List<String> listUserLoginsByServerLogin(String serverLogin) throws SQLException {
|
||
String sql = """
|
||
SELECT user_login
|
||
FROM user_access_servers_current
|
||
WHERE LOWER(server_login) = LOWER(?)
|
||
ORDER BY user_login
|
||
""";
|
||
List<String> result = new ArrayList<>();
|
||
try (Connection c = db.getConnection();
|
||
PreparedStatement ps = c.prepareStatement(sql)) {
|
||
ps.setString(1, serverLogin);
|
||
try (ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) {
|
||
result.add(rs.getString("user_login"));
|
||
}
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
/**
|
||
* Возвращает уникальные физические серверы из текущей routing-проекции.
|
||
* Используется транспортным пулом: одно WSS-соединение держится на сервер,
|
||
* а не на каждого пользователя этого сервера.
|
||
*/
|
||
public List<UserAccessServerRouteEntry> listDistinctServers() throws SQLException {
|
||
String sql = """
|
||
SELECT DISTINCT ON (LOWER(server_login))
|
||
server_login, server_url
|
||
FROM user_access_servers_current
|
||
WHERE server_login IS NOT NULL AND BTRIM(server_login) <> ''
|
||
AND server_url IS NOT NULL AND BTRIM(server_url) <> ''
|
||
ORDER BY LOWER(server_login), server_login, server_url
|
||
""";
|
||
List<UserAccessServerRouteEntry> result = new ArrayList<>();
|
||
try (Connection c = db.getConnection();
|
||
PreparedStatement ps = c.prepareStatement(sql);
|
||
ResultSet rs = ps.executeQuery()) {
|
||
while (rs.next()) {
|
||
UserAccessServerRouteEntry entry = new UserAccessServerRouteEntry();
|
||
entry.setUserLogin("");
|
||
entry.setServerLogin(rs.getString("server_login"));
|
||
entry.setServerUrl(rs.getString("server_url"));
|
||
result.add(entry);
|
||
}
|
||
}
|
||
return result;
|
||
}
|
||
|
||
private UserAccessServerRouteEntry mapRow(ResultSet rs) throws SQLException {
|
||
UserAccessServerRouteEntry entry = new UserAccessServerRouteEntry();
|
||
entry.setUserLogin(rs.getString("user_login"));
|
||
entry.setServerLogin(rs.getString("server_login"));
|
||
entry.setServerUrl(rs.getString("server_url"));
|
||
entry.setServerClientKey(KeyEncodingUtil.normalizeKeyToBase64_32(rs.getString("server_client_key")));
|
||
return entry;
|
||
}
|
||
}
|