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

Сервер: читать пользователей напрямую из PDA current

Browse Source
This commit is contained in:
AidarKC
2026-07-27 18:04:39 +04:00
parent 618a30c2ab
commit f74fecddd8
8 changed files with 82 additions and 34 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ConnectionsStateDAO.java
+17 -12
View File
@@ -1,6 +1,7 @@
package shine.db.dao;
import shine.db.DbController;
import shine.db.sql.SolanaUsersSql;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -14,10 +15,8 @@ import java.util.List;
*
* ВАЖНО:
* - login в запросах может быть в любом регистре;
* - в ответах возвращаем логины в каноническом регистре через JOIN на solana_users.
*
* ПРИМЕЧАНИЕ:
* Таблица пользователей тут названа "solana_users". Если у тебя иначе — поменяй в SQL.
* - в ответах возвращаем логины в каноническом регистре через прямой lookup
* по актуальным PDA-данным и manual fallback.
*/
public final class ConnectionsStateDAO {
@@ -42,14 +41,17 @@ public final class ConnectionsStateDAO {
String sql = """
SELECT COALESCE(u_login.login, u_bch.login, cs.to_login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_login
LEFT JOIN %s
ON LOWER(u_login.login) = LOWER(cs.to_login)
LEFT JOIN solana_users u_bch
LEFT JOIN %s
ON LOWER(u_bch.blockchain_name) = LOWER(cs.to_bch_name)
WHERE LOWER(cs.login) = LOWER(?)
AND cs.rel_type = ?
ORDER BY friend_login
""";
""".formatted(
SolanaUsersSql.usersSubquery("u_login"),
SolanaUsersSql.usersSubquery("u_bch")
);
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -72,9 +74,9 @@ public final class ConnectionsStateDAO {
String sql = """
SELECT COALESCE(u_actor.login, cs.login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_actor
LEFT JOIN %s
ON LOWER(u_actor.login) = LOWER(cs.login)
LEFT JOIN solana_users u_target
LEFT JOIN %s
ON LOWER(u_target.login) = LOWER(?)
WHERE (
LOWER(cs.to_login) = LOWER(?)
@@ -82,7 +84,10 @@ public final class ConnectionsStateDAO {
)
AND cs.rel_type = ?
ORDER BY friend_login
""";
""".formatted(
SolanaUsersSql.usersSubquery("u_actor"),
SolanaUsersSql.usersSubquery("u_target")
);
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -106,7 +111,7 @@ public final class ConnectionsStateDAO {
String sql = """
SELECT u.login AS friend_login
FROM connections_state a
JOIN solana_users u
JOIN %s
ON LOWER(u.login) = LOWER(a.to_login)
WHERE LOWER(a.login) = LOWER(?)
AND a.rel_type = ?
@@ -118,7 +123,7 @@ public final class ConnectionsStateDAO {
AND b.rel_type = a.rel_type
)
ORDER BY u.login
""";
""".formatted(SolanaUsersSql.usersSubquery("u"));
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SolanaUsersDAO.java
+11 -10
View File
@@ -3,6 +3,7 @@ package shine.db.dao;
import shine.db.DbController;
import shine.db.KeyEncodingUtil;
import shine.db.entities.SolanaUserEntry;
import shine.db.sql.SolanaUsersSql;
import java.sql.*;
import java.util.ArrayList;
@@ -80,10 +81,10 @@ public final class SolanaUsersDAO {
public boolean existsByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT 1
FROM solana_users
FROM %s
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
""".formatted(SolanaUsersSql.usersSubquery("su"));
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
@@ -104,10 +105,10 @@ public final class SolanaUsersDAO {
public boolean existsByBlockchainName(Connection c, String blockchainName) throws SQLException {
String sql = """
SELECT 1
FROM solana_users
FROM %s
WHERE blockchain_name = ?
LIMIT 1
""";
""".formatted(SolanaUsersSql.usersSubquery("su"));
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, blockchainName);
@@ -135,9 +136,9 @@ public final class SolanaUsersDAO {
solana_key,
blockchain_key,
client_key
FROM solana_users
FROM %s
WHERE LOWER(login) = LOWER(?)
""";
""".formatted(SolanaUsersSql.usersSubquery("su"));
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
@@ -164,9 +165,9 @@ public final class SolanaUsersDAO {
solana_key,
blockchain_key,
client_key
FROM solana_users
FROM %s
WHERE blockchain_name = ?
""";
""".formatted(SolanaUsersSql.usersSubquery("su"));
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, blockchainName);
@@ -193,11 +194,11 @@ public final class SolanaUsersDAO {
solana_key,
blockchain_key,
client_key
FROM solana_users
FROM %s
WHERE LOWER(login) LIKE ?
ORDER BY login
LIMIT 5
""";
""".formatted(SolanaUsersSql.usersSubquery("su"));
List<SolanaUserEntry> result = new ArrayList<>();
SHiNE-server/shine-server-db/src/main/java/shine/db/sql/SolanaUsersSql.java
+38
View File
@@ -0,0 +1,38 @@
package shine.db.sql;
public final class SolanaUsersSql {
private SolanaUsersSql() {}
public static String usersSubquery(String alias) {
if (alias == null || alias.isBlank()) {
throw new IllegalArgumentException("alias is blank");
}
return """
(
SELECT
current_users.login AS login,
current_users.blockchain_name AS blockchain_name,
current_users.client_key AS solana_key,
current_users.blockchain_key AS blockchain_key,
current_users.client_key AS client_key
FROM solana_user_pda_current current_users
UNION ALL
SELECT
manual.login AS login,
manual.blockchain_name AS blockchain_name,
manual.solana_key AS solana_key,
manual.blockchain_key AS blockchain_key,
manual.client_key AS client_key
FROM solana_users_manual manual
WHERE NOT EXISTS (
SELECT 1
FROM solana_user_pda_current current_users
WHERE LOWER(current_users.login) = LOWER(manual.login)
)
) %s
""".formatted(alias);
}
}