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

19 12 25

Browse Source 
Ещё поправил мелкие детали в библиотеке работы с БД
This commit is contained in:
AidarKC 2025-12-22 12:47:17 +03:00
parent 627321d4ae
commit 7f92dc5f51
5 changed files with 105 additions and 85 deletions
Show Stats Download Patch File Download Diff File Expand all files Collapse all files

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

@ -152,45 +152,45 @@ public class DatabaseInitializer {
ON ip_geo_cache (updated_at_ms); ON ip_geo_cache (updated_at_ms);
"""); """);
// 5. blockchain_state (MVP) — оставляю как было (там уже user_login TEXT) // 5. blockchain_state (MVP) — теперь PK blockchainName + поле login
st.executeUpdate(""" st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blockchain_state ( CREATE TABLE IF NOT EXISTS blockchain_state (
blockchain_id INTEGER NOT NULL PRIMARY KEY, blockchainName TEXT NOT NULL PRIMARY KEY,
user_login TEXT NOT NULL, login TEXT NOT NULL,
public_key_base64 TEXT NOT NULL, public_key_base64 TEXT NOT NULL,
size_limit INTEGER NOT NULL, size_limit INTEGER NOT NULL,
size_bytes INTEGER NOT NULL, size_bytes INTEGER NOT NULL,
file_size_bytes INTEGER NOT NULL, file_size_bytes INTEGER NOT NULL,
last_global_number INTEGER NOT NULL, last_global_number INTEGER NOT NULL,
last_global_hash TEXT NOT NULL, last_global_hash TEXT NOT NULL,
updated_at_ms INTEGER NOT NULL, updated_at_ms INTEGER NOT NULL,
-- Линии 0..7 (MVP: максимум 8 линий) -- Линии 0..7 (MVP: максимум 8 линий)
line0_last_number INTEGER NOT NULL, line0_last_number INTEGER NOT NULL,
line0_last_hash TEXT NOT NULL, line0_last_hash TEXT NOT NULL,
line1_last_number INTEGER NOT NULL, line1_last_number INTEGER NOT NULL,
line1_last_hash TEXT NOT NULL, line1_last_hash TEXT NOT NULL,
line2_last_number INTEGER NOT NULL, line2_last_number INTEGER NOT NULL,
line2_last_hash TEXT NOT NULL, line2_last_hash TEXT NOT NULL,
line3_last_number INTEGER NOT NULL, line3_last_number INTEGER NOT NULL,
line3_last_hash TEXT NOT NULL, line3_last_hash TEXT NOT NULL,
line4_last_number INTEGER NOT NULL, line4_last_number INTEGER NOT NULL,
line4_last_hash TEXT NOT NULL, line4_last_hash TEXT NOT NULL,
line5_last_number INTEGER NOT NULL, line5_last_number INTEGER NOT NULL,
line5_last_hash TEXT NOT NULL, line5_last_hash TEXT NOT NULL,
line6_last_number INTEGER NOT NULL, line6_last_number INTEGER NOT NULL,
line6_last_hash TEXT NOT NULL, line6_last_hash TEXT NOT NULL,
line7_last_number INTEGER NOT NULL, line7_last_number INTEGER NOT NULL,
line7_last_hash TEXT NOT NULL line7_last_hash TEXT NOT NULL
); );
"""); """);
st.executeUpdate(""" st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blockchain_state_user_login CREATE INDEX IF NOT EXISTS idx_blockchain_state_login
ON blockchain_state (user_login); ON blockchain_state (login);
"""); """);
st.executeUpdate(""" st.executeUpdate("""
@ -198,26 +198,26 @@ public class DatabaseInitializer {
ON blockchain_state (updated_at_ms); ON blockchain_state (updated_at_ms);
"""); """);
// 6. blocks — PK удалён полностью // 6. blocks — PK удалён полностью, to* теперь nullable
st.executeUpdate(""" st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blocks ( CREATE TABLE IF NOT EXISTS blocks (
login TEXT NOT NULL, login TEXT NOT NULL,
bchName TEXT NOT NULL, bchName TEXT NOT NULL,
blockGlobalNumber INTEGER NOT NULL, blockGlobalNumber INTEGER NOT NULL,
blockGlobalPreHashe TEXT NOT NULL, blockGlobalPreHashe TEXT NOT NULL,
blockLineIndex INTEGER NOT NULL, blockLineIndex INTEGER NOT NULL,
blockLineNumber INTEGER NOT NULL, blockLineNumber INTEGER NOT NULL,
blockLinePreHashe TEXT NOT NULL, blockLinePreHashe TEXT NOT NULL,
msgType INTEGER NOT NULL, msgType INTEGER NOT NULL,
blockByte BLOB, blockByte BLOB,
to_login TEXT, to_login TEXT,
toBchName TEXT NOT NULL, toBchName TEXT,
toBlockGlobalNumber INTEGER NOT NULL, toBlockGlobalNumber INTEGER,
toBlockHashe TEXT NOT NULL, toBlockHashe TEXT,
FOREIGN KEY (login) REFERENCES solana_users(login) FOREIGN KEY (login) REFERENCES solana_users(login)
); );

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

@ -21,19 +21,19 @@ public final class BlockchainStateDAO {
return instance; return instance;
} }
/** Получить по blockchainId без внешнего соединения. Сам открывает/закрывает. */ /** Получить по blockchainName без внешнего соединения. Сам открывает/закрывает. */
public BlockchainStateEntry getByBlockchainId(long blockchainId) throws SQLException { public BlockchainStateEntry getByBlockchainName(String blockchainName) throws SQLException {
try (Connection c = db.getConnection()) { try (Connection c = db.getConnection()) {
return getByBlockchainId(c, blockchainId); return getByBlockchainName(c, blockchainName);
} }
} }
/** Получить по blockchainId с внешним соединением. Соединение НЕ закрывает. */ /** Получить по blockchainName с внешним соединением. Соединение НЕ закрывает. */
public BlockchainStateEntry getByBlockchainId(Connection c, long blockchainId) throws SQLException { public BlockchainStateEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
String sql = """ String sql = """
SELECT SELECT
blockchain_id, blockchainName,
user_login, login,
public_key_base64, public_key_base64,
size_limit, size_limit,
size_bytes, size_bytes,
@ -50,11 +50,11 @@ public final class BlockchainStateDAO {
line7_last_number, line7_last_hash, line7_last_number, line7_last_hash,
updated_at_ms updated_at_ms
FROM blockchain_state FROM blockchain_state
WHERE blockchain_id = ? WHERE blockchainName = ?
"""; """;
try (PreparedStatement ps = c.prepareStatement(sql)) { try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, blockchainId); ps.setString(1, blockchainName);
try (ResultSet rs = ps.executeQuery()) { try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null; if (!rs.next()) return null;
return mapRow(rs); return mapRow(rs);
@ -73,8 +73,8 @@ public final class BlockchainStateDAO {
public void upsert(Connection c, BlockchainStateEntry e) throws SQLException { public void upsert(Connection c, BlockchainStateEntry e) throws SQLException {
String sql = """ String sql = """
INSERT INTO blockchain_state ( INSERT INTO blockchain_state (
blockchain_id, blockchainName,
user_login, login,
public_key_base64, public_key_base64,
size_limit, size_limit,
size_bytes, size_bytes,
@ -102,9 +102,9 @@ public final class BlockchainStateDAO {
?,?, ?,?,
? ?
) )
ON CONFLICT(blockchain_id) ON CONFLICT(blockchainName)
DO UPDATE SET DO UPDATE SET
user_login = excluded.user_login, login = excluded.login,
public_key_base64 = excluded.public_key_base64, public_key_base64 = excluded.public_key_base64,
size_limit = excluded.size_limit, size_limit = excluded.size_limit,
size_bytes = excluded.size_bytes, size_bytes = excluded.size_bytes,
@ -132,8 +132,8 @@ public final class BlockchainStateDAO {
try (PreparedStatement ps = c.prepareStatement(sql)) { try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1; int i = 1;
ps.setLong(i++, e.getBlockchainId()); ps.setString(i++, e.getBlockchainName());
ps.setString(i++, nn(e.getUserLogin())); ps.setString(i++, nn(e.getLogin()));
ps.setString(i++, nn(e.getPublicKeyBase64())); ps.setString(i++, nn(e.getPublicKeyBase64()));
ps.setInt(i++, e.getSizeLimit()); ps.setInt(i++, e.getSizeLimit());
ps.setInt(i++, e.getSizeBytes()); ps.setInt(i++, e.getSizeBytes());
@ -153,8 +153,8 @@ public final class BlockchainStateDAO {
private BlockchainStateEntry mapRow(ResultSet rs) throws SQLException { private BlockchainStateEntry mapRow(ResultSet rs) throws SQLException {
BlockchainStateEntry e = new BlockchainStateEntry(); BlockchainStateEntry e = new BlockchainStateEntry();
e.setBlockchainId(rs.getLong("blockchain_id")); e.setBlockchainName(rs.getString("blockchainName"));
e.setUserLogin(rs.getString("user_login")); e.setLogin(rs.getString("login"));
e.setPublicKeyBase64(rs.getString("public_key_base64")); e.setPublicKeyBase64(rs.getString("public_key_base64"));
e.setSizeLimit(rs.getInt("size_limit")); e.setSizeLimit(rs.getInt("size_limit"));

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

@ -184,9 +184,14 @@ public final class BlocksDAO {
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin()); if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
else ps.setNull(i++, Types.VARCHAR); else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBchName())); if (e.getToBchName() != null) ps.setString(i++, e.getToBchName());
ps.setInt(i++, e.getToBlockGlobalNumber()); else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBlockHashe()));
if (e.getToBlockGlobalNumber() != null) ps.setInt(i++, e.getToBlockGlobalNumber());
else ps.setNull(i++, Types.INTEGER);
if (e.getToBlockHashe() != null) ps.setString(i++, e.getToBlockHashe());
else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, e.getLogin()); ps.setString(i++, e.getLogin());
ps.setString(i++, e.getBchName()); ps.setString(i++, e.getBchName());
@ -273,9 +278,14 @@ public final class BlocksDAO {
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin()); if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
else ps.setNull(i++, Types.VARCHAR); else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBchName())); if (e.getToBchName() != null) ps.setString(i++, e.getToBchName());
ps.setInt(i++, e.getToBlockGlobalNumber()); else ps.setNull(i++, Types.VARCHAR);
ps.setString(i++, nn(e.getToBlockHashe()));
if (e.getToBlockGlobalNumber() != null) ps.setInt(i++, e.getToBlockGlobalNumber());
else ps.setNull(i++, Types.INTEGER);
if (e.getToBlockHashe() != null) ps.setString(i++, e.getToBlockHashe());
else ps.setNull(i++, Types.VARCHAR);
} }
private BlockEntry mapRow(ResultSet rs) throws SQLException { private BlockEntry mapRow(ResultSet rs) throws SQLException {
@ -295,9 +305,17 @@ public final class BlocksDAO {
e.setBlockByte(rs.getBytes("blockByte")); e.setBlockByte(rs.getBytes("blockByte"));
e.setToLogin(rs.getString("to_login")); e.setToLogin(rs.getString("to_login"));
e.setToBchName(rs.getString("toBchName"));
e.setToBlockGlobalNumber(rs.getInt("toBlockGlobalNumber")); String toBchName = rs.getString("toBchName");
e.setToBlockHashe(rs.getString("toBlockHashe")); if (rs.wasNull()) toBchName = null;
e.setToBchName(toBchName);
Integer toBlockGlobalNumber = (Integer) rs.getObject("toBlockGlobalNumber");
e.setToBlockGlobalNumber(toBlockGlobalNumber);
String toBlockHashe = rs.getString("toBlockHashe");
if (rs.wasNull()) toBlockHashe = null;
e.setToBlockHashe(toBlockHashe);
return e; return e;
} }

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

@ -7,7 +7,9 @@ package shine.db.entities;
* - login TEXT NOT NULL * - login TEXT NOT NULL
* - bchName TEXT NOT NULL (идёт сразу после login) * - bchName TEXT NOT NULL (идёт сразу после login)
* - to_login TEXT nullable * - to_login TEXT nullable
* - toBchName TEXT NOT NULL (идёт сразу после to_login) * - toBchName TEXT nullable
* - toBlockGlobalNumber INTEGER nullable
* - toBlockHashe TEXT nullable
* *
* PRIMARY KEY пока убран вообще. * PRIMARY KEY пока убран вообще.
*/ */
@ -28,9 +30,9 @@ public class BlockEntry {
private byte[] blockByte; // BLOB private byte[] blockByte; // BLOB
private String toLogin; // TEXT nullable private String toLogin; // TEXT nullable
private String toBchName; // TEXT private String toBchName; // TEXT nullable
private int toBlockGlobalNumber; // int32 private Integer toBlockGlobalNumber; // INTEGER nullable
private String toBlockHashe; // TEXT private String toBlockHashe; // TEXT nullable
public BlockEntry() {} public BlockEntry() {}
@ -45,7 +47,7 @@ public class BlockEntry {
byte[] blockByte, byte[] blockByte,
String toLogin, String toLogin,
String toBchName, String toBchName,
int toBlockGlobalNumber, Integer toBlockGlobalNumber,
String toBlockHashe) { String toBlockHashe) {
this.login = login; this.login = login;
this.bchName = bchName; this.bchName = bchName;
@ -95,8 +97,8 @@ public class BlockEntry {
public String getToBchName() { return toBchName; } public String getToBchName() { return toBchName; }
public void setToBchName(String toBchName) { this.toBchName = toBchName; } public void setToBchName(String toBchName) { this.toBchName = toBchName; }
public int getToBlockGlobalNumber() { return toBlockGlobalNumber; } public Integer getToBlockGlobalNumber() { return toBlockGlobalNumber; }
public void setToBlockGlobalNumber(int toBlockGlobalNumber) { this.toBlockGlobalNumber = toBlockGlobalNumber; } public void setToBlockGlobalNumber(Integer toBlockGlobalNumber) { this.toBlockGlobalNumber = toBlockGlobalNumber; }
public String getToBlockHashe() { return toBlockHashe; } public String getToBlockHashe() { return toBlockHashe; }
public void setToBlockHashe(String toBlockHashe) { this.toBlockHashe = toBlockHashe; } public void setToBlockHashe(String toBlockHashe) { this.toBlockHashe = toBlockHashe; }

22
shine-server-db/src/main/java/shine/db/entities/BlockchainStateEntry.java
View File

@ -4,13 +4,13 @@ import java.util.Arrays;
/** /**
* Агрегатная сущность текущего состояния блокчейна. * Агрегатная сущность текущего состояния блокчейна.
* 1 строка = 1 blockchainId, плюс состояние линий 0..7. * 1 строка = 1 blockchainName, плюс состояние линий 0..7.
*/ */
public final class BlockchainStateEntry { public final class BlockchainStateEntry {
private long blockchainId; private String blockchainName;
private String userLogin; private String login;
private String publicKeyBase64; private String publicKeyBase64;
private int sizeLimit; private int sizeLimit;
@ -35,8 +35,8 @@ public final class BlockchainStateEntry {
this.lastGlobalHash = ""; this.lastGlobalHash = "";
} }
public BlockchainStateEntry(long blockchainId, public BlockchainStateEntry(String blockchainName,
String userLogin, String login,
String publicKeyBase64, String publicKeyBase64,
int sizeLimit, int sizeLimit,
int sizeBytes, int sizeBytes,
@ -46,8 +46,8 @@ public final class BlockchainStateEntry {
int[] lastLineNumbers, int[] lastLineNumbers,
String[] lastLineHashes, String[] lastLineHashes,
long updatedAtMs) { long updatedAtMs) {
this.blockchainId = blockchainId; this.blockchainName = blockchainName;
this.userLogin = userLogin; this.login = login;
this.publicKeyBase64 = publicKeyBase64; this.publicKeyBase64 = publicKeyBase64;
this.sizeLimit = sizeLimit; this.sizeLimit = sizeLimit;
this.sizeBytes = sizeBytes; this.sizeBytes = sizeBytes;
@ -69,11 +69,11 @@ public final class BlockchainStateEntry {
this.updatedAtMs = updatedAtMs; this.updatedAtMs = updatedAtMs;
} }
public long getBlockchainId() { return blockchainId; } public String getBlockchainName() { return blockchainName; }
public void setBlockchainId(long blockchainId) { this.blockchainId = blockchainId; } public void setBlockchainName(String blockchainName) { this.blockchainName = blockchainName; }
public String getUserLogin() { return userLogin; } public String getLogin() { return login; }
public void setUserLogin(String userLogin) { this.userLogin = userLogin; } public void setLogin(String login) { this.login = login; }
public String getPublicKeyBase64() { return publicKeyBase64; } public String getPublicKeyBase64() { return publicKeyBase64; }
public void setPublicKeyBase64(String publicKeyBase64) { this.publicKeyBase64 = publicKeyBase64; } public void setPublicKeyBase64(String publicKeyBase64) { this.publicKeyBase64 = publicKeyBase64; }