Старт

2025-12-04 12:20:47 +03:00
commit ff9301eddb
79 changed files with 4655 additions and 0 deletions
@@ -0,0 +1,116 @@
+package shine.db.dao;
+
+import shine.db.SqliteDbController;
+import shine.db.entities.ActiveSession;
+
+import java.sql.*;
+
+/** Здесь мы хрним данные об активных сессиях пользователя (для wss соединений) */
+
+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;
+    }
+
+    public void insert(ActiveSession session) throws SQLException {
+        String sql = """
+            INSERT INTO active_sessions (
+                sessionId,
+                session_pwd,
+                loginId,
+                time_ms,
+                pubkey_num,
+                push_endpoint,
+                push_p256dh_key,
+                push_auth_key
+            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, session.getSessionId());
+            ps.setString(2, session.getSessionPwd());
+            ps.setLong(3, session.getLoginId());
+            ps.setLong(4, session.getTimeMs());
+            ps.setInt(5, session.getPubkeyNum());
+            ps.setString(6, session.getPushEndpoint());
+            ps.setString(7, session.getPushP256dhKey());
+            ps.setString(8, session.getPushAuthKey());
+            ps.executeUpdate();
+        }
+    }
+
+    public ActiveSession getBySessionId(long sessionId) throws SQLException {
+        String sql = """
+            SELECT
+                sessionId,
+                session_pwd,
+                loginId,
+                time_ms,
+                pubkey_num,
+                push_endpoint,
+                push_p256dh_key,
+                push_auth_key
+            FROM active_sessions
+            WHERE sessionId = ?
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, sessionId);
+            try (ResultSet rs = ps.executeQuery()) {
+                if (!rs.next()) {
+                    return null;
+                }
+                return mapRow(rs);
+            }
+        }
+    }
+
+    /**
+     * Удаление записи по sessionId.
+     * Если записи нет — просто ничего не удалит (0 строк).
+     */
+    public void deleteBySessionId(long sessionId) throws SQLException {
+        String sql = "DELETE FROM active_sessions WHERE sessionId = ?";
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, sessionId);
+            ps.executeUpdate();
+        }
+    }
+
+    private ActiveSession mapRow(ResultSet rs) throws SQLException {
+        long sessionId = rs.getLong("sessionId");
+        String sessionPwd = rs.getString("session_pwd");
+        long loginId = rs.getLong("loginId");
+        long timeMs = rs.getLong("time_ms");
+        short pubkeyNum = (short) rs.getInt("pubkey_num");
+        String pushEndpoint = rs.getString("push_endpoint");
+        String pushP256dhKey = rs.getString("push_p256dh_key");
+        String pushAuthKey = rs.getString("push_auth_key");
+
+        return new ActiveSession(
+                sessionId,
+                sessionPwd,
+                loginId,
+                timeMs,
+                pubkeyNum,
+                pushEndpoint,
+                pushP256dhKey,
+                pushAuthKey
+        );
+    }
+}
@@ -0,0 +1,119 @@
+package shine.db.dao;
+
+import shine.db.SqliteDbController;
+import shine.db.entities.SolanaUser;
+
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
+
+/** Здесь храним данные об пользователях - локальная копия того что есть в солане */
+
+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;
+    }
+
+    public void insert(SolanaUser user) throws SQLException {
+        String sql = """
+            INSERT INTO solana_users (login, loginId, bchId, pubkey0, pubkey1, bchLimit)
+            VALUES (?, ?, ?, ?, ?, ?)
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setString(1, user.getLogin());
+            ps.setLong(2, user.getLoginId());
+            ps.setLong(3, user.getBchId());
+            ps.setString(4, user.getPubkey0());
+            ps.setString(5, user.getPubkey1());
+
+            if (user.getBchLimit() != null) {
+                ps.setInt(6, user.getBchLimit());
+            } else {
+                ps.setNull(6, Types.INTEGER);
+            }
+
+            ps.executeUpdate();
+        }
+    }
+
+    public SolanaUser getByLoginId(long loginId) throws SQLException {
+        String sql = """
+            SELECT login, loginId, bchId, pubkey0, pubkey1, bchLimit
+            FROM solana_users
+            WHERE loginId = ?
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, loginId);
+
+            try (ResultSet rs = ps.executeQuery()) {
+                if (!rs.next()) return null;
+                return mapRow(rs);
+            }
+        }
+    }
+
+    public SolanaUser getByLogin(String login) throws SQLException {
+        String sql = """
+            SELECT login, loginId, bchId, pubkey0, pubkey1, bchLimit
+            FROM solana_users
+            WHERE LOWER(login) = LOWER(?)
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setString(1, login);
+
+            try (ResultSet rs = ps.executeQuery()) {
+                if (!rs.next()) return null;
+                return mapRow(rs);
+            }
+        }
+    }
+
+    public List<SolanaUser> searchByLoginPrefix(String prefix) throws SQLException {
+        String sql = """
+            SELECT login, loginId, bchId, pubkey0, pubkey1, bchLimit
+            FROM solana_users
+            WHERE LOWER(login) LIKE ?
+            ORDER BY login
+            LIMIT 5
+            """;
+
+        List<SolanaUser> result = new ArrayList<>();
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setString(1, prefix.toLowerCase() + "%");
+
+            try (ResultSet rs = ps.executeQuery()) {
+                while (rs.next()) result.add(mapRow(rs));
+            }
+        }
+
+        return result;
+    }
+
+    private SolanaUser mapRow(ResultSet rs) throws SQLException {
+        return new SolanaUser(
+                rs.getLong("loginId"),
+                rs.getString("login"),
+                rs.getLong("bchId"),
+                rs.getString("pubkey0"),
+                rs.getString("pubkey1"),
+                rs.getObject("bchLimit") != null ? rs.getInt("bchLimit") : null
+        );
+    }
+}
@@ -0,0 +1,136 @@
+package shine.db.dao;
+
+import shine.db.SqliteDbController;
+import shine.db.entities.UserParam;
+
+import java.sql.*;
+import java.util.ArrayList;
+import java.util.List;
+
+/** Здесь зраним сохранённые параметры пользователей (в основном до каково сообщения просмотрены ленты) */
+
+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 методом ON CONFLICT — одним SQL-запросом.
+     * Если запись существует -> обновляем поля.
+     * Если нет -> вставляем новую запись.
+     */
+    public void upsert(UserParam param) throws SQLException {
+        String sql = """
+            INSERT INTO users_params (
+                loginId,
+                param,
+                bch_channel_id,
+                value,
+                time_ms,
+                pubkey_num,
+                signature
+            ) VALUES (?, ?, ?, ?, ?, ?, ?)
+            ON CONFLICT(loginId, param)
+            DO UPDATE SET
+                bch_channel_id = excluded.bch_channel_id,
+                value          = excluded.value,
+                time_ms        = excluded.time_ms,
+                pubkey_num     = excluded.pubkey_num,
+                signature      = excluded.signature
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, param.getLoginId());
+            ps.setString(2, param.getParam());
+            ps.setLong(3, param.getBchChannelId());
+            ps.setString(4, param.getValue());
+            ps.setLong(5, param.getTimeMs());
+            ps.setInt(6, param.getPubkeyNum());
+            ps.setString(7, param.getSignature());
+            ps.executeUpdate();
+        }
+    }
+
+    /**
+     * Получить параметр по loginId + param.
+     */
+    public UserParam getByUserIdAndParam(long loginId, String paramName) throws SQLException {
+        String sql = """
+            SELECT
+                loginId,
+                param,
+                bch_channel_id,
+                value,
+                time_ms,
+                pubkey_num,
+                signature
+            FROM users_params
+            WHERE loginId = ? AND param = ?
+            """;
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, loginId);
+            ps.setString(2, paramName);
+            try (ResultSet rs = ps.executeQuery()) {
+                if (!rs.next()) return null;
+                return mapRow(rs);
+            }
+        }
+    }
+
+    /**
+     * Получить все параметры пользователя.
+     */
+    public List<UserParam> getByUserId(long loginId) throws SQLException {
+        String sql = """
+            SELECT
+                loginId,
+                param,
+                bch_channel_id,
+                value,
+                time_ms,
+                pubkey_num,
+                signature
+            FROM users_params
+            WHERE loginId = ?
+            ORDER BY time_ms DESC
+            """;
+
+        List<UserParam> result = new ArrayList<>();
+
+        try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
+            ps.setLong(1, loginId);
+            try (ResultSet rs = ps.executeQuery()) {
+                while (rs.next()) result.add(mapRow(rs));
+            }
+        }
+
+        return result;
+    }
+
+    private UserParam mapRow(ResultSet rs) throws SQLException {
+        return new UserParam(
+                rs.getLong("loginId"),
+                rs.getString("param"),
+                rs.getLong("bch_channel_id"),
+                rs.getString("value"),
+                rs.getLong("time_ms"),
+                (short) rs.getInt("pubkey_num"),
+                rs.getString("signature")
+        );
+    }
+}