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

19 12 25

Browse Source 
Передел библиотеку работы с БД под только login и bchName
This commit is contained in:
AidarKC 2025-12-19 14:08:05 +03:00
parent 0c49cae055
commit 627321d4ae
9 changed files with 233 additions and 448 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

58
shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
View File

@ -14,14 +14,13 @@ import java.sql.Statement;
/**
* DatabaseInitializer — создание новой SQLite-БД по схеме SHiNE.
*
* Читает путь к файлу БД из application.properties (db.path),
* при необходимости удаляет старый файл и создаёт таблицы:
* - solana_users
* - active_sessions
* - users_params
* Таблицы:
* - solana_users (login TEXT PK, bchName TEXT)
* - active_sessions (login TEXT FK)
* - users_params (login TEXT FK, UNIQUE(login,param))
* - ip_geo_cache
* - blockchain_state (MVP)
* - blocks
* - blocks (login TEXT, bchName TEXT, PK убран)
*/
public class DatabaseInitializer {
@ -81,9 +80,8 @@ public class DatabaseInitializer {
// 1. solana_users
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS solana_users (
login TEXT NOT NULL,
loginId INTEGER NOT NULL PRIMARY KEY,
bchId INTEGER NOT NULL,
login TEXT NOT NULL PRIMARY KEY,
bchName TEXT NOT NULL,
loginKey TEXT,
deviceKey TEXT,
bchLimit INTEGER
@ -99,7 +97,7 @@ public class DatabaseInitializer {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS active_sessions (
sessionId TEXT NOT NULL PRIMARY KEY,
loginId INTEGER NOT NULL,
login TEXT NOT NULL,
sessionPwd TEXT NOT NULL,
storagePwd TEXT NOT NULL,
sessionCreatedAtMs INTEGER NOT NULL,
@ -111,33 +109,33 @@ public class DatabaseInitializer {
clientInfoFromClient TEXT,
clientInfoFromRequest TEXT,
userLanguage TEXT,
FOREIGN KEY (loginId) REFERENCES solana_users(loginId)
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_active_sessions_loginId
ON active_sessions (loginId);
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 (
loginId INTEGER NOT NULL,
login TEXT NOT NULL,
param TEXT NOT NULL,
bch_channel_id INTEGER NOT NULL DEFAULT 0,
value TEXT,
time_ms INTEGER NOT NULL,
pubkey_num INTEGER NOT NULL,
signature TEXT,
FOREIGN KEY (loginId) REFERENCES solana_users(loginId),
UNIQUE (loginId, param)
FOREIGN KEY (login) REFERENCES solana_users(login),
UNIQUE (login, param)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_users_params_loginId
ON users_params (loginId);
CREATE INDEX IF NOT EXISTS idx_users_params_login
ON users_params (login);
""");
// 4. ip_geo_cache
@ -154,8 +152,7 @@ public class DatabaseInitializer {
ON ip_geo_cache (updated_at_ms);
""");
// 5. blockchain_state (MVP)
// TODO: позже можно вынести линии в отдельную таблицу blockchain_line_state и убрать "широкую" схему.
// 5. blockchain_state (MVP) — оставляю как было (там уже user_login TEXT)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blockchain_state (
blockchain_id INTEGER NOT NULL PRIMARY KEY,
@ -201,11 +198,11 @@ public class DatabaseInitializer {
ON blockchain_state (updated_at_ms);
""");
// 6. blocks
// 6. blocks — PK удалён полностью
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blocks (
loginId INTEGER NOT NULL,
blockchainId INTEGER NOT NULL,
login TEXT NOT NULL,
bchName TEXT NOT NULL,
blockGlobalNumber INTEGER NOT NULL,
blockGlobalPreHashe TEXT NOT NULL,
@ -217,28 +214,23 @@ public class DatabaseInitializer {
blockByte BLOB,
toLoginId INTEGER NOT NULL,
toBlockchainId INTEGER NOT NULL,
to_login TEXT,
toBchName TEXT NOT NULL,
toBlockGlobalNumber INTEGER NOT NULL,
toBlockHashe TEXT NOT NULL,
-- Выбранный PK (см. BlocksDAO)
PRIMARY KEY (loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber),
-- Связи (по желанию можно ослабить/убрать, если будут "частичные" данные)
FOREIGN KEY (loginId) REFERENCES solana_users(loginId)
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
// Индексы под типовые запросы: по цепочке/глобальному номеру и по "to*"
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blocks_chain_global
ON blocks (loginId, blockchainId, blockGlobalNumber);
ON blocks (login, bchName, blockGlobalNumber);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
ON blocks (toLoginId, toBlockchainId, toBlockGlobalNumber);
ON blocks (to_login, toBchName, toBlockGlobalNumber);
""");
}
}

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

@ -37,7 +37,7 @@ public final class ActiveSessionsDAO {
String sql = """
INSERT INTO active_sessions (
sessionId,
loginId,
login,
sessionPwd,
storagePwd,
sessionCreatedAtMs,
@ -54,7 +54,7 @@ public final class ActiveSessionsDAO {
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, session.getSessionId());
ps.setLong(2, session.getLoginId());
ps.setString(2, session.getLogin());
ps.setString(3, session.getSessionPwd());
ps.setString(4, session.getStoragePwd());
ps.setLong(5, session.getSessionCreatedAtMs());
@ -84,7 +84,7 @@ public final class ActiveSessionsDAO {
String sql = """
SELECT
sessionId,
loginId,
login,
sessionPwd,
storagePwd,
sessionCreatedAtMs,
@ -116,12 +116,12 @@ public final class ActiveSessionsDAO {
}
}
/** Получить список по loginId с внешним соединением. Соединение НЕ закрывает. */
public List<ActiveSessionEntry> getByLoginId(Connection c, long loginId) throws SQLException {
/** Получить список по login с внешним соединением. Соединение НЕ закрывает. */
public List<ActiveSessionEntry> getByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT
sessionId,
loginId,
login,
sessionPwd,
storagePwd,
sessionCreatedAtMs,
@ -134,13 +134,13 @@ public final class ActiveSessionsDAO {
clientInfoFromRequest,
userLanguage
FROM active_sessions
WHERE loginId = ?
WHERE login = ?
""";
List<ActiveSessionEntry> result = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));
}
@ -149,10 +149,10 @@ public final class ActiveSessionsDAO {
return result;
}
/** Получить список по loginId без внешнего соединения. Сам открывает/закрывает. */
public List<ActiveSessionEntry> getByLoginId(long loginId) throws SQLException {
/** Получить список по login без внешнего соединения. Сам открывает/закрывает. */
public List<ActiveSessionEntry> getByLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLoginId(c, loginId);
return getByLogin(c, login);
}
}
@ -250,7 +250,7 @@ public final class ActiveSessionsDAO {
private ActiveSessionEntry mapRow(ResultSet rs) throws SQLException {
String sessionId = rs.getString("sessionId");
long loginId = rs.getLong("loginId");
String login = rs.getString("login");
String sessionPwd = rs.getString("sessionPwd");
String storagePwd = rs.getString("storagePwd");
long sessionCreatedAtMs = rs.getLong("sessionCreatedAtMs");
@ -265,7 +265,7 @@ public final class ActiveSessionsDAO {
return new ActiveSessionEntry(
sessionId,
loginId,
login,
sessionPwd,
storagePwd,
sessionCreatedAtMs,

148
shine-server-db/src/main/java/shine/db/dao/BlocksDAO.java
View File

@ -13,7 +13,7 @@ import java.sql.*;
* - методы без Connection сами открывают и закрывают соединение
*
* Важно:
* - PRIMARY KEY: (loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber)
* - PRIMARY KEY удалён (временно), поэтому "upsert" сделан через UPDATE->INSERT.
*/
public final class BlocksDAO {
@ -37,8 +37,8 @@ public final class BlocksDAO {
public void insert(Connection c, BlockEntry e) throws SQLException {
String sql = """
INSERT INTO blocks (
loginId,
blockchainId,
login,
bchName,
blockGlobalNumber,
blockGlobalPreHashe,
blockLineIndex,
@ -46,8 +46,8 @@ public final class BlocksDAO {
blockLinePreHashe,
msgType,
blockByte,
toLoginId,
toBlockchainId,
to_login,
toBchName,
toBlockGlobalNumber,
toBlockHashe
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
@ -66,45 +66,15 @@ public final class BlocksDAO {
}
}
// -------------------- UPSERT --------------------
// -------------------- UPSERT (UPDATE -> INSERT) --------------------
/**
* Сохранить (upsert) с внешним соединением. Соединение НЕ закрывает.
* Если запись с таким PK уже есть — обновляем поля.
* Сохранить (условный upsert) с внешним соединением. Соединение НЕ закрывает.
* Без PK/UNIQUE делаем: UPDATE по "ключевым" полям -> если 0 строк, то INSERT.
*/
public void upsert(Connection c, BlockEntry e) throws SQLException {
String sql = """
INSERT INTO blocks (
loginId,
blockchainId,
blockGlobalNumber,
blockGlobalPreHashe,
blockLineIndex,
blockLineNumber,
blockLinePreHashe,
msgType,
blockByte,
toLoginId,
toBlockchainId,
toBlockGlobalNumber,
toBlockHashe
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber)
DO UPDATE SET
blockGlobalPreHashe = excluded.blockGlobalPreHashe,
blockLinePreHashe = excluded.blockLinePreHashe,
msgType = excluded.msgType,
blockByte = excluded.blockByte,
toLoginId = excluded.toLoginId,
toBlockchainId = excluded.toBlockchainId,
toBlockGlobalNumber = excluded.toBlockGlobalNumber,
toBlockHashe = excluded.toBlockHashe
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindAll(ps, e);
ps.executeUpdate();
}
int updated = update(c, e);
if (updated == 0) insert(c, e);
}
/** Сохранить (upsert) без внешнего соединения. Сам открывает/закрывает. */
@ -116,18 +86,18 @@ public final class BlocksDAO {
// -------------------- SELECT --------------------
/** Получить блок по PK с внешним соединением. Соединение НЕ закрывает. */
/** Получить блок по "PK-подобному" набору полей с внешним соединением. Соединение НЕ закрывает. */
public BlockEntry getByPk(Connection c,
long loginId,
long blockchainId,
String login,
String bchName,
int blockGlobalNumber,
int blockLineIndex,
int blockLineNumber) throws SQLException {
String sql = """
SELECT
loginId,
blockchainId,
login,
bchName,
blockGlobalNumber,
blockGlobalPreHashe,
blockLineIndex,
@ -135,22 +105,23 @@ public final class BlocksDAO {
blockLinePreHashe,
msgType,
blockByte,
toLoginId,
toBlockchainId,
to_login,
toBchName,
toBlockGlobalNumber,
toBlockHashe
FROM blocks
WHERE
loginId = ?
AND blockchainId = ?
login = ?
AND bchName = ?
AND blockGlobalNumber = ?
AND blockLineIndex = ?
AND blockLineNumber = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setLong(2, blockchainId);
ps.setString(1, login);
ps.setString(2, bchName);
ps.setInt(3, blockGlobalNumber);
ps.setInt(4, blockLineIndex);
ps.setInt(5, blockLineNumber);
@ -162,22 +133,22 @@ public final class BlocksDAO {
}
}
/** Получить блок по PK без внешнего соединения. Сам открывает/закрывает. */
public BlockEntry getByPk(long loginId,
long blockchainId,
/** Получить блок по "PK-подобному" набору полей без внешнего соединения. Сам открывает/закрывает. */
public BlockEntry getByPk(String login,
String bchName,
int blockGlobalNumber,
int blockLineIndex,
int blockLineNumber) throws SQLException {
try (Connection c = db.getConnection()) {
return getByPk(c, loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber);
return getByPk(c, login, bchName, blockGlobalNumber, blockLineIndex, blockLineNumber);
}
}
// -------------------- UPDATE --------------------
/**
* Обновить (строго UPDATE) по PK с внешним соединением. Соединение НЕ закрывает.
* Если строки нет — updateCount будет 0.
* Обновить (строго UPDATE) по "PK-подобному" набору полей с внешним соединением. Соединение НЕ закрывает.
* Может обновить >1 строк, если в таблице появились дубликаты.
*/
public int update(Connection c, BlockEntry e) throws SQLException {
String sql = """
@ -187,13 +158,13 @@ public final class BlocksDAO {
blockLinePreHashe = ?,
msgType = ?,
blockByte = ?,
toLoginId = ?,
toBlockchainId = ?,
to_login = ?,
toBchName = ?,
toBlockGlobalNumber = ?,
toBlockHashe = ?
WHERE
loginId = ?
AND blockchainId = ?
login = ?
AND bchName = ?
AND blockGlobalNumber = ?
AND blockLineIndex = ?
AND blockLineNumber = ?
@ -210,13 +181,15 @@ public final class BlocksDAO {
if (bytes != null) ps.setBytes(i++, bytes);
else ps.setNull(i++, Types.BLOB);
ps.setLong(i++, e.getToLoginId());
ps.setInt(i++, e.getToBlockchainId());
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBchName()));
ps.setInt(i++, e.getToBlockGlobalNumber());
ps.setString(i++, nn(e.getToBlockHashe()));
ps.setLong(i++, e.getLoginId());
ps.setLong(i++, e.getBlockchainId());
ps.setString(i++, e.getLogin());
ps.setString(i++, e.getBchName());
ps.setInt(i++, e.getBlockGlobalNumber());
ps.setInt(i++, e.getBlockLineIndex());
ps.setInt(i++, e.getBlockLineNumber());
@ -234,10 +207,13 @@ public final class BlocksDAO {
// -------------------- DELETE --------------------
/** Удалить по PK с внешним соединением. Соединение НЕ закрывает. */
/**
* Удалить по "PK-подобному" набору полей с внешним соединением. Соединение НЕ закрывает.
* Может удалить >1 строк, если есть дубликаты.
*/
public int deleteByPk(Connection c,
long loginId,
long blockchainId,
String login,
String bchName,
int blockGlobalNumber,
int blockLineIndex,
int blockLineNumber) throws SQLException {
@ -245,16 +221,16 @@ public final class BlocksDAO {
String sql = """
DELETE FROM blocks
WHERE
loginId = ?
AND blockchainId = ?
login = ?
AND bchName = ?
AND blockGlobalNumber = ?
AND blockLineIndex = ?
AND blockLineNumber = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setLong(2, blockchainId);
ps.setString(1, login);
ps.setString(2, bchName);
ps.setInt(3, blockGlobalNumber);
ps.setInt(4, blockLineIndex);
ps.setInt(5, blockLineNumber);
@ -262,25 +238,25 @@ public final class BlocksDAO {
}
}
/** Удалить по PK без внешнего соединения. Сам открывает/закрывает. */
public int deleteByPk(long loginId,
long blockchainId,
/** Удалить по "PK-подобному" набору полей без внешнего соединения. Сам открывает/закрывает. */
public int deleteByPk(String login,
String bchName,
int blockGlobalNumber,
int blockLineIndex,
int blockLineNumber) throws SQLException {
try (Connection c = db.getConnection()) {
return deleteByPk(c, loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber);
return deleteByPk(c, login, bchName, blockGlobalNumber, blockLineIndex, blockLineNumber);
}
}
// -------------------- INTERNAL --------------------
/** Единая привязка параметров под INSERT/UPSERT — чтобы не разъезжалось. */
/** Единая привязка параметров под INSERT — чтобы не разъезжалось. */
private static void bindAll(PreparedStatement ps, BlockEntry e) throws SQLException {
int i = 1;
ps.setLong(i++, e.getLoginId());
ps.setLong(i++, e.getBlockchainId());
ps.setString(i++, e.getLogin());
ps.setString(i++, e.getBchName());
ps.setInt(i++, e.getBlockGlobalNumber());
ps.setString(i++, nn(e.getBlockGlobalPreHashe()));
@ -294,8 +270,10 @@ public final class BlocksDAO {
if (bytes != null) ps.setBytes(i++, bytes);
else ps.setNull(i++, Types.BLOB);
ps.setLong(i++, e.getToLoginId());
ps.setInt(i++, e.getToBlockchainId());
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBchName()));
ps.setInt(i++, e.getToBlockGlobalNumber());
ps.setString(i++, nn(e.getToBlockHashe()));
}
@ -303,8 +281,8 @@ public final class BlocksDAO {
private BlockEntry mapRow(ResultSet rs) throws SQLException {
BlockEntry e = new BlockEntry();
e.setLoginId(rs.getLong("loginId"));
e.setBlockchainId(rs.getLong("blockchainId"));
e.setLogin(rs.getString("login"));
e.setBchName(rs.getString("bchName"));
e.setBlockGlobalNumber(rs.getInt("blockGlobalNumber"));
e.setBlockGlobalPreHashe(rs.getString("blockGlobalPreHashe"));
@ -316,8 +294,8 @@ public final class BlocksDAO {
e.setBlockByte(rs.getBytes("blockByte"));
e.setToLoginId(rs.getLong("toLoginId"));
e.setToBlockchainId(rs.getInt("toBlockchainId"));
e.setToLogin(rs.getString("to_login"));
e.setToBchName(rs.getString("toBchName"));
e.setToBlockGlobalNumber(rs.getInt("toBlockGlobalNumber"));
e.setToBlockHashe(rs.getString("toBlockHashe"));

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

@ -11,16 +11,15 @@ import java.util.List;
* SolanaUsersDAO — локальная таблица пользователей из Solana.
*
* Колонки:
* - login TEXT
* - loginId INTEGER (PK)
* - bchId INTEGER
* - login TEXT (PK)
* - bchName TEXT
* - loginKey TEXT
* - deviceKey TEXT
* - bchLimit INTEGER (может быть NULL)
*
* * Правило:
* * - методы с Connection НЕ закрывают соединение
* * - методы без Connection сами открывают и закрывают соединение
* Правило:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*/
public final class SolanaUsersDAO {
@ -43,22 +42,18 @@ public final class SolanaUsersDAO {
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
public void insert(Connection c, SolanaUserEntry user) throws SQLException {
String sql = """
INSERT INTO solana_users (login, loginId, bchId, loginKey, deviceKey, bchLimit)
VALUES (?, ?, ?, ?, ?, ?)
INSERT INTO solana_users (login, bchName, loginKey, deviceKey, bchLimit)
VALUES (?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, user.getLogin());
ps.setLong(2, user.getLoginId());
ps.setLong(3, user.getBchId());
ps.setString(4, user.getLoginKey());
ps.setString(5, user.getDeviceKey());
ps.setString(2, user.getBchName());
ps.setString(3, user.getLoginKey());
ps.setString(4, user.getDeviceKey());
if (user.getBchLimit() != null) {
ps.setInt(6, user.getBchLimit());
} else {
ps.setNull(6, Types.INTEGER);
}
if (user.getBchLimit() != null) ps.setInt(5, user.getBchLimit());
else ps.setNull(5, Types.INTEGER);
ps.executeUpdate();
}
@ -73,34 +68,10 @@ public final class SolanaUsersDAO {
// -------------------- SELECT --------------------
/** Получить по loginId с внешним соединением. Соединение НЕ закрывает. */
public SolanaUserEntry getByLoginId(Connection c, long loginId) throws SQLException {
String sql = """
SELECT login, loginId, bchId, loginKey, deviceKey, bchLimit
FROM solana_users
WHERE loginId = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/** Получить по loginId без внешнего соединения. Сам открывает/закрывает. */
public SolanaUserEntry getByLoginId(long loginId) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLoginId(c, loginId);
}
}
/** Получить по login (case-insensitive) с внешним соединением. Соединение НЕ закрывает. */
public SolanaUserEntry getByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT login, loginId, bchId, loginKey, deviceKey, bchLimit
SELECT login, bchName, loginKey, deviceKey, bchLimit
FROM solana_users
WHERE LOWER(login) = LOWER(?)
""";
@ -124,7 +95,7 @@ public final class SolanaUsersDAO {
/** Поиск по префиксу с внешним соединением. Соединение НЕ закрывает. */
public List<SolanaUserEntry> searchByLoginPrefix(Connection c, String prefix) throws SQLException {
String sql = """
SELECT login, loginId, bchId, loginKey, deviceKey, bchLimit
SELECT login, bchName, loginKey, deviceKey, bchLimit
FROM solana_users
WHERE LOWER(login) LIKE ?
ORDER BY login
@ -154,9 +125,8 @@ public final class SolanaUsersDAO {
private SolanaUserEntry mapRow(ResultSet rs) throws SQLException {
return new SolanaUserEntry(
rs.getLong("loginId"),
rs.getString("login"),
rs.getLong("bchId"),
rs.getString("bchName"),
rs.getString("loginKey"),
rs.getString("deviceKey"),
rs.getObject("bchLimit") != null ? rs.getInt("bchLimit") : null

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

@ -30,7 +30,7 @@ public final class UserParamsDAO {
public void upsert(Connection c, UserParamEntry param) throws SQLException {
String sql = """
INSERT INTO users_params (
loginId,
login,
param,
bch_channel_id,
value,
@ -38,7 +38,7 @@ public final class UserParamsDAO {
pubkey_num,
signature
) VALUES (?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(loginId, param)
ON CONFLICT(login, param)
DO UPDATE SET
bch_channel_id = excluded.bch_channel_id,
value = excluded.value,
@ -48,7 +48,7 @@ public final class UserParamsDAO {
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, param.getLoginId());
ps.setString(1, param.getLogin());
ps.setString(2, param.getParam());
ps.setLong(3, param.getBchChannelId());
ps.setString(4, param.getValue());
@ -69,10 +69,10 @@ public final class UserParamsDAO {
// -------------------- SELECT --------------------
/** Получить параметр с внешним соединением. Соединение НЕ закрывает. */
public UserParamEntry getByUserIdAndParam(Connection c, long loginId, String paramName) throws SQLException {
public UserParamEntry getByUserLoginAndParam(Connection c, String login, String paramName) throws SQLException {
String sql = """
SELECT
loginId,
login,
param,
bch_channel_id,
value,
@ -80,11 +80,11 @@ public final class UserParamsDAO {
pubkey_num,
signature
FROM users_params
WHERE loginId = ? AND param = ?
WHERE login = ? AND param = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setString(1, login);
ps.setString(2, paramName);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
@ -94,17 +94,17 @@ public final class UserParamsDAO {
}
/** Получить параметр без внешнего соединения. Сам открывает/закрывает. */
public UserParamEntry getByUserIdAndParam(long loginId, String paramName) throws SQLException {
public UserParamEntry getByUserLoginAndParam(String login, String paramName) throws SQLException {
try (Connection c = db.getConnection()) {
return getByUserIdAndParam(c, loginId, paramName);
return getByUserLoginAndParam(c, login, paramName);
}
}
/** Получить все параметры пользователя с внешним соединением. Соединение НЕ закрывает. */
public List<UserParamEntry> getByUserId(Connection c, long loginId) throws SQLException {
public List<UserParamEntry> getByUserLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT
loginId,
login,
param,
bch_channel_id,
value,
@ -112,14 +112,14 @@ public final class UserParamsDAO {
pubkey_num,
signature
FROM users_params
WHERE loginId = ?
WHERE login = ?
ORDER BY time_ms DESC
""";
List<UserParamEntry> result = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, loginId);
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));
}
@ -129,9 +129,9 @@ public final class UserParamsDAO {
}
/** Получить все параметры пользователя без внешнего соединения. Сам открывает/закрывает. */
public List<UserParamEntry> getByUserId(long loginId) throws SQLException {
public List<UserParamEntry> getByUserLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return getByUserId(c, loginId);
return getByUserLogin(c, login);
}
}
@ -139,7 +139,7 @@ public final class UserParamsDAO {
private UserParamEntry mapRow(ResultSet rs) throws SQLException {
return new UserParamEntry(
rs.getLong("loginId"),
rs.getString("login"),
rs.getString("param"),
rs.getLong("bch_channel_id"),
rs.getString("value"),

143
shine-server-db/src/main/java/shine/db/entities/ActiveSessionEntry.java
View File

@ -3,27 +3,13 @@ package shine.db.entities;
/**
* Модель активной сессии (таблица active_sessions).
*
* 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)
* );
* Теперь вместо loginId:
* - login TEXT NOT NULL (FK -> solana_users(login))
*/
public class ActiveSessionEntry {
private String sessionId; // TEXT base64(32 bytes)
private long loginId; // INTEGER
private String login; // TEXT NOT NULL
private String sessionPwd; // TEXT
private String storagePwd; // TEXT
private long sessionCreatedAtMs; // INTEGER
@ -42,7 +28,7 @@ public class ActiveSessionEntry {
}
public ActiveSessionEntry(String sessionId,
long loginId,
String login,
String sessionPwd,
String storagePwd,
long sessionCreatedAtMs,
@ -55,7 +41,7 @@ public class ActiveSessionEntry {
String clientInfoFromRequest,
String userLanguage) {
this.sessionId = sessionId;
this.loginId = loginId;
this.login = login;
this.sessionPwd = sessionPwd;
this.storagePwd = storagePwd;
this.sessionCreatedAtMs = sessionCreatedAtMs;
@ -69,109 +55,42 @@ public class ActiveSessionEntry {
this.userLanguage = userLanguage;
}
// --- getters / setters ---
public String getSessionId() { return sessionId; }
public void setSessionId(String sessionId) { this.sessionId = sessionId; }
public String getSessionId() {
return sessionId;
}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public void setSessionId(String sessionId) {
this.sessionId = sessionId;
}
public String getSessionPwd() { return sessionPwd; }
public void setSessionPwd(String sessionPwd) { this.sessionPwd = sessionPwd; }
public long getLoginId() {
return loginId;
}
public String getStoragePwd() { return storagePwd; }
public void setStoragePwd(String storagePwd) { this.storagePwd = storagePwd; }
public void setLoginId(long loginId) {
this.loginId = loginId;
}
public long getSessionCreatedAtMs() { return sessionCreatedAtMs; }
public void setSessionCreatedAtMs(long sessionCreatedAtMs) { this.sessionCreatedAtMs = sessionCreatedAtMs; }
public String getSessionPwd() {
return sessionPwd;
}
public long getLastAuthirificatedAtMs() { return lastAuthirificatedAtMs; }
public void setLastAuthirificatedAtMs(long lastAuthirificatedAtMs) { this.lastAuthirificatedAtMs = lastAuthirificatedAtMs; }
public void setSessionPwd(String sessionPwd) {
this.sessionPwd = sessionPwd;
}
public String getPushEndpoint() { return pushEndpoint; }
public void setPushEndpoint(String pushEndpoint) { this.pushEndpoint = pushEndpoint; }
public String getStoragePwd() {
return storagePwd;
}
public String getPushP256dhKey() { return pushP256dhKey; }
public void setPushP256dhKey(String pushP256dhKey) { this.pushP256dhKey = pushP256dhKey; }
public void setStoragePwd(String storagePwd) {
this.storagePwd = storagePwd;
}
public String getPushAuthKey() { return pushAuthKey; }
public void setPushAuthKey(String pushAuthKey) { this.pushAuthKey = pushAuthKey; }
public long getSessionCreatedAtMs() {
return sessionCreatedAtMs;
}
public String getClientIp() { return clientIp; }
public void setClientIp(String clientIp) { this.clientIp = clientIp; }
public void setSessionCreatedAtMs(long sessionCreatedAtMs) {
this.sessionCreatedAtMs = sessionCreatedAtMs;
}
public String getClientInfoFromClient() { return clientInfoFromClient; }
public void setClientInfoFromClient(String clientInfoFromClient) { this.clientInfoFromClient = clientInfoFromClient; }
public long getLastAuthirificatedAtMs() {
return lastAuthirificatedAtMs;
}
public String getClientInfoFromRequest() { return clientInfoFromRequest; }
public void setClientInfoFromRequest(String clientInfoFromRequest) { this.clientInfoFromRequest = clientInfoFromRequest; }
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;
}
public String getUserLanguage() { return userLanguage; }
public void setUserLanguage(String userLanguage) { this.userLanguage = userLanguage; }
}

57
shine-server-db/src/main/java/shine/db/entities/BlockEntry.java
View File

@ -3,22 +3,19 @@ package shine.db.entities;
/**
* Запись блока (таблица blocks).
*
* Идея:
* - Храним и "глобальную" позицию (blockGlobalNumber + blockGlobalPreHashe),
* и "линейную" позицию (blockLineIndex + blockLineNumber + blockLinePreHashe),
* плюс полезные поля маршрутизации (to*).
* Теперь:
* - login TEXT NOT NULL
* - bchName TEXT NOT NULL (идёт сразу после login)
* - to_login TEXT nullable
* - toBchName TEXT NOT NULL (идёт сразу после to_login)
*
* В БД:
* - int64 -> INTEGER (Java long)
* - int32 -> INTEGER (Java int)
* - int16 -> INTEGER (Java int/short)
* - bytes -> BLOB (Java byte[])
* - hashes -> TEXT
* PRIMARY KEY пока убран вообще.
*/
public class BlockEntry {
private long loginId; // int64
private long blockchainId; // int64
private String login; // TEXT
private String bchName; // TEXT
private int blockGlobalNumber; // int32
private String blockGlobalPreHashe; // TEXT
@ -30,15 +27,15 @@ public class BlockEntry {
private byte[] blockByte; // BLOB
private long toLoginId; // int64
private int toBlockchainId; // int32
private String toLogin; // TEXT nullable
private String toBchName; // TEXT
private int toBlockGlobalNumber; // int32
private String toBlockHashe; // TEXT
public BlockEntry() {}
public BlockEntry(long loginId,
long blockchainId,
public BlockEntry(String login,
String bchName,
int blockGlobalNumber,
String blockGlobalPreHashe,
int blockLineIndex,
@ -46,12 +43,12 @@ public class BlockEntry {
String blockLinePreHashe,
int msgType,
byte[] blockByte,
long toLoginId,
int toBlockchainId,
String toLogin,
String toBchName,
int toBlockGlobalNumber,
String toBlockHashe) {
this.loginId = loginId;
this.blockchainId = blockchainId;
this.login = login;
this.bchName = bchName;
this.blockGlobalNumber = blockGlobalNumber;
this.blockGlobalPreHashe = blockGlobalPreHashe;
this.blockLineIndex = blockLineIndex;
@ -59,17 +56,17 @@ public class BlockEntry {
this.blockLinePreHashe = blockLinePreHashe;
this.msgType = msgType;
this.blockByte = blockByte;
this.toLoginId = toLoginId;
this.toBlockchainId = toBlockchainId;
this.toLogin = toLogin;
this.toBchName = toBchName;
this.toBlockGlobalNumber = toBlockGlobalNumber;
this.toBlockHashe = toBlockHashe;
}
public long getLoginId() { return loginId; }
public void setLoginId(long loginId) { this.loginId = loginId; }
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public long getBlockchainId() { return blockchainId; }
public void setBlockchainId(long blockchainId) { this.blockchainId = blockchainId; }
public String getBchName() { return bchName; }
public void setBchName(String bchName) { this.bchName = bchName; }
public int getBlockGlobalNumber() { return blockGlobalNumber; }
public void setBlockGlobalNumber(int blockGlobalNumber) { this.blockGlobalNumber = blockGlobalNumber; }
@ -92,11 +89,11 @@ public class BlockEntry {
public byte[] getBlockByte() { return blockByte; }
public void setBlockByte(byte[] blockByte) { this.blockByte = blockByte; }
public long getToLoginId() { return toLoginId; }
public void setToLoginId(long toLoginId) { this.toLoginId = toLoginId; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public int getToBlockchainId() { return toBlockchainId; }
public void setToBlockchainId(int toBlockchainId) { this.toBlockchainId = toBlockchainId; }
public String getToBchName() { return toBchName; }
public void setToBchName(String toBchName) { this.toBchName = toBchName; }
public int getToBlockGlobalNumber() { return toBlockGlobalNumber; }
public void setToBlockGlobalNumber(int toBlockGlobalNumber) { this.toBlockGlobalNumber = toBlockGlobalNumber; }

84
shine-server-db/src/main/java/shine/db/entities/SolanaUserEntry.java
View File

@ -3,86 +3,50 @@ package shine.db.entities;
/**
* Локальная копия пользователя из Solana.
*
* Храним:
* - login / loginId;
* - bchId — id персонального блокчейна;
* - loginKey — публичный ключ для логина / авторизации;
* - deviceKey — публичный ключ устройства (второй ключ);
* - bchLimit — лимит по количеству блоков / размеру цепочки (может быть null).
* Теперь:
* - login — PRIMARY KEY (TEXT)
* - bchName — имя/идентификатор персонального блокчейна (TEXT)
* - loginKey — публичный ключ логина
* - deviceKey — публичный ключ устройства
* - bchLimit — лимит (может быть null)
*/
public class SolanaUserEntry {
private long loginId;
private String login;
private long bchId;
private String loginKey; // раньше pubkey0
private String deviceKey; // раньше pubkey1
private Integer bchLimit; // может быть null
private String login; // TEXT PK
private String bchName; // TEXT NOT NULL
private String loginKey; // TEXT
private String deviceKey; // TEXT
private Integer bchLimit; // INTEGER nullable
public SolanaUserEntry() {
}
public SolanaUserEntry(long loginId,
String login,
long bchId,
public SolanaUserEntry(String login,
String bchName,
String loginKey,
String deviceKey,
Integer bchLimit) {
this.loginId = loginId;
this.login = login;
this.bchId = bchId;
this.bchName = bchName;
this.loginKey = loginKey;
this.deviceKey = deviceKey;
this.bchLimit = bchLimit;
}
public long getLoginId() {
return loginId;
}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public void setLoginId(long loginId) {
this.loginId = loginId;
}
public String getLogin() {
return login;
}
public void setLogin(String login) {
this.login = login;
}
public long getBchId() {
return bchId;
}
public void setBchId(long bchId) {
this.bchId = bchId;
}
public String getBchName() { return bchName; }
public void setBchName(String bchName) { this.bchName = bchName; }
/** Публичный ключ логина (основной ключ пользователя). */
public String getLoginKey() {
return loginKey;
}
public void setLoginKey(String loginKey) {
this.loginKey = loginKey;
}
public String getLoginKey() { return loginKey; }
public void setLoginKey(String loginKey) { this.loginKey = loginKey; }
/** Публичный ключ устройства (device key). */
public String getDeviceKey() {
return deviceKey;
}
public String getDeviceKey() { return deviceKey; }
public void setDeviceKey(String deviceKey) { this.deviceKey = deviceKey; }
public void setDeviceKey(String deviceKey) {
this.deviceKey = deviceKey;
}
public Integer getBchLimit() {
return bchLimit;
}
public void setBchLimit(Integer bchLimit) {
this.bchLimit = bchLimit;
}
public Integer getBchLimit() { return bchLimit; }
public void setBchLimit(Integer bchLimit) { this.bchLimit = bchLimit; }
}

69
shine-server-db/src/main/java/shine/db/entities/UserParamEntry.java
View File

@ -2,7 +2,7 @@ package shine.db.entities;
public class UserParamEntry {
private long loginId;
private String login; // TEXT NOT NULL
private String param;
private long bchChannelId; // новый канал, 8 байт, может быть 0
private String value;
@ -13,14 +13,14 @@ public class UserParamEntry {
public UserParamEntry() {
}
public UserParamEntry(long loginId,
public UserParamEntry(String login,
String param,
long bchChannelId,
String value,
long timeMs,
short pubkeyNum,
String signature) {
this.loginId = loginId;
this.login = login;
this.param = param;
this.bchChannelId = bchChannelId;
this.value = value;
@ -29,59 +29,24 @@ public class UserParamEntry {
this.signature = signature;
}
public long getLoginId() {
return loginId;
}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public void setLoginId(long loginId) {
this.loginId = loginId;
}
public String getParam() { return param; }
public void setParam(String param) { this.param = param; }
public String getParam() {
return param;
}
public long getBchChannelId() { return bchChannelId; }
public void setBchChannelId(long bchChannelId) { this.bchChannelId = bchChannelId; }
public void setParam(String param) {
this.param = param;
}
public String getValue() { return value; }
public void setValue(String value) { this.value = value; }
public long getBchChannelId() {
return bchChannelId;
}
public long getTimeMs() { return timeMs; }
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
public void setBchChannelId(long bchChannelId) {
this.bchChannelId = bchChannelId;
}
public short getPubkeyNum() { return pubkeyNum; }
public void setPubkeyNum(short pubkeyNum) { this.pubkeyNum = pubkeyNum; }
public String getValue() {
return value;
}
public void setValue(String value) {
this.value = value;
}
public long getTimeMs() {
return timeMs;
}
public void setTimeMs(long timeMs) {
this.timeMs = timeMs;
}
public short getPubkeyNum() {
return pubkeyNum;
}
public void setPubkeyNum(short pubkeyNum) {
this.pubkeyNum = pubkeyNum;
}
public String getSignature() {
return signature;
}
public void setSignature(String signature) {
this.signature = signature;
}
public String getSignature() { return signature; }
public void setSignature(String signature) { this.signature = signature; }
}