19 12 25
Добавил таблицу для хранения блоков
This commit is contained in:
AidarKC
parent
d6d2bfeb73
commit
6c4d8cd51b
@ -21,6 +21,7 @@ import java.sql.Statement;
|
||||
* - users_params
|
||||
* - ip_geo_cache
|
||||
* - blockchain_state (MVP)
|
||||
* - blocks
|
||||
*/
|
||||
public class DatabaseInitializer {
|
||||
|
||||
@ -190,7 +191,6 @@ public class DatabaseInitializer {
|
||||
);
|
||||
""");
|
||||
|
||||
// Индексы под быстрые проверки/поиск
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_user_login
|
||||
ON blockchain_state (user_login);
|
||||
@ -200,6 +200,46 @@ public class DatabaseInitializer {
|
||||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_updated_at
|
||||
ON blockchain_state (updated_at_ms);
|
||||
""");
|
||||
|
||||
// 6. blocks
|
||||
st.executeUpdate("""
|
||||
CREATE TABLE IF NOT EXISTS blocks (
|
||||
loginId INTEGER NOT NULL,
|
||||
blockchainId INTEGER NOT NULL,
|
||||
blockGlobalNumber INTEGER NOT NULL,
|
||||
blockGlobalPreHashe TEXT NOT NULL,
|
||||
|
||||
blockLineIndex INTEGER NOT NULL,
|
||||
blockLineNumber INTEGER NOT NULL,
|
||||
blockLinePreHashe TEXT NOT NULL,
|
||||
|
||||
msgType INTEGER NOT NULL,
|
||||
|
||||
blockByte BLOB,
|
||||
|
||||
toLoginId INTEGER NOT NULL,
|
||||
toBlockchainId INTEGER 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)
|
||||
);
|
||||
""");
|
||||
|
||||
// Индексы под типовые запросы: по цепочке/глобальному номеру и по "to*"
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_blocks_chain_global
|
||||
ON blocks (loginId, blockchainId, blockGlobalNumber);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
|
||||
ON blocks (toLoginId, toBlockchainId, toBlockGlobalNumber);
|
||||
""");
|
||||
}
|
||||
}
|
||||
}
|
||||
346
shine-server-db/src/main/java/shine/db/dao/BlocksDAO.java
Normal file
346
shine-server-db/src/main/java/shine/db/dao/BlocksDAO.java
Normal file
@ -0,0 +1,346 @@
|
||||
package shine.db.dao;
|
||||
|
||||
import shine.db.SqliteDbController;
|
||||
import shine.db.entities.BlockEntry;
|
||||
|
||||
import java.sql.*;
|
||||
|
||||
/**
|
||||
* DAO для таблицы blocks.
|
||||
*
|
||||
* Правило:
|
||||
* - методы с Connection НЕ закрывают соединение
|
||||
* - методы без Connection сами открывают и закрывают соединение
|
||||
*
|
||||
* Важно:
|
||||
* - PRIMARY KEY выбран: (loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber)
|
||||
* Это означает: в рамках одной цепочки и одного globalNumber, каждая линия/номер уникальны.
|
||||
*/
|
||||
public final class BlocksDAO {
|
||||
|
||||
private static volatile BlocksDAO instance;
|
||||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||||
|
||||
private BlocksDAO() { }
|
||||
|
||||
public static BlocksDAO getInstance() {
|
||||
if (instance == null) {
|
||||
synchronized (BlocksDAO.class) {
|
||||
if (instance == null) instance = new BlocksDAO();
|
||||
}
|
||||
}
|
||||
return instance;
|
||||
}
|
||||
|
||||
// -------------------- INSERT --------------------
|
||||
|
||||
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
|
||||
public void insert(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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
int i = 1;
|
||||
ps.setLong(i++, e.getLoginId());
|
||||
ps.setLong(i++, e.getBlockchainId());
|
||||
ps.setInt(i++, e.getBlockGlobalNumber());
|
||||
ps.setString(i++, nn(e.getBlockGlobalPreHashe()));
|
||||
|
||||
ps.setInt(i++, e.getBlockLineIndex());
|
||||
ps.setInt(i++, e.getBlockLineNumber());
|
||||
ps.setString(i++, nn(e.getBlockLinePreHashe()));
|
||||
|
||||
ps.setInt(i++, e.getMsgType());
|
||||
|
||||
byte[] bytes = e.getBlockByte();
|
||||
if (bytes != null) ps.setBytes(i++, bytes);
|
||||
else ps.setNull(i++, Types.BLOB);
|
||||
|
||||
ps.setLong(i++, e.getToLoginId());
|
||||
ps.setInt(i++, e.getToBlockchainId());
|
||||
ps.setInt(i++, e.getToBlockGlobalNumber());
|
||||
ps.setString(i++, nn(e.getToBlockHashe()));
|
||||
|
||||
ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
/** Вставка без внешнего соединения. Сам открывает/закрывает. */
|
||||
public void insert(BlockEntry e) throws SQLException {
|
||||
try (Connection c = db.getConnection()) {
|
||||
insert(c, e);
|
||||
}
|
||||
}
|
||||
|
||||
// -------------------- UPSERT (SAVE) --------------------
|
||||
|
||||
/**
|
||||
* Сохранить (upsert) с внешним соединением. Соединение НЕ закрывает.
|
||||
* Если запись с таким PK уже есть — обновляем поля.
|
||||
*/
|
||||
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)) {
|
||||
int i = 1;
|
||||
ps.setLong(i++, e.getLoginId());
|
||||
ps.setLong(i++, e.getBlockchainId());
|
||||
ps.setInt(i++, e.getBlockGlobalNumber());
|
||||
ps.setString(i++, nn(e.getBlockGlobalPreHashe()));
|
||||
|
||||
ps.setInt(i++, e.getBlockLineIndex());
|
||||
ps.setInt(i++, e.getBlockLineNumber());
|
||||
ps.setString(i++, nn(e.getBlockLinePreHashe()));
|
||||
|
||||
ps.setInt(i++, e.getMsgType());
|
||||
|
||||
byte[] bytes = e.getBlockByte();
|
||||
if (bytes != null) ps.setBytes(i++, bytes);
|
||||
else ps.setNull(i++, Types.BLOB);
|
||||
|
||||
ps.setLong(i++, e.getToLoginId());
|
||||
ps.setInt(i++, e.getToBlockchainId());
|
||||
ps.setInt(i++, e.getToBlockGlobalNumber());
|
||||
ps.setString(i++, nn(e.getToBlockHashe()));
|
||||
|
||||
ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
/** Сохранить (upsert) без внешнего соединения. Сам открывает/закрывает. */
|
||||
public void upsert(BlockEntry e) throws SQLException {
|
||||
try (Connection c = db.getConnection()) {
|
||||
upsert(c, e);
|
||||
}
|
||||
}
|
||||
|
||||
// -------------------- SELECT --------------------
|
||||
|
||||
/**
|
||||
* Получить блок по PK с внешним соединением. Соединение НЕ закрывает.
|
||||
*/
|
||||
public BlockEntry getByPk(Connection c,
|
||||
long loginId,
|
||||
long blockchainId,
|
||||
int blockGlobalNumber,
|
||||
int blockLineIndex,
|
||||
int blockLineNumber) throws SQLException {
|
||||
|
||||
String sql = """
|
||||
SELECT
|
||||
loginId,
|
||||
blockchainId,
|
||||
blockGlobalNumber,
|
||||
blockGlobalPreHashe,
|
||||
blockLineIndex,
|
||||
blockLineNumber,
|
||||
blockLinePreHashe,
|
||||
msgType,
|
||||
blockByte,
|
||||
toLoginId,
|
||||
toBlockchainId,
|
||||
toBlockGlobalNumber,
|
||||
toBlockHashe
|
||||
FROM blocks
|
||||
WHERE
|
||||
loginId = ?
|
||||
AND blockchainId = ?
|
||||
AND blockGlobalNumber = ?
|
||||
AND blockLineIndex = ?
|
||||
AND blockLineNumber = ?
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setLong(1, loginId);
|
||||
ps.setLong(2, blockchainId);
|
||||
ps.setInt(3, blockGlobalNumber);
|
||||
ps.setInt(4, blockLineIndex);
|
||||
ps.setInt(5, blockLineNumber);
|
||||
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
if (!rs.next()) return null;
|
||||
return mapRow(rs);
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
/** Получить блок по PK без внешнего соединения. Сам открывает/закрывает. */
|
||||
public BlockEntry getByPk(long loginId,
|
||||
long blockchainId,
|
||||
int blockGlobalNumber,
|
||||
int blockLineIndex,
|
||||
int blockLineNumber) throws SQLException {
|
||||
try (Connection c = db.getConnection()) {
|
||||
return getByPk(c, loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber);
|
||||
}
|
||||
}
|
||||
|
||||
// -------------------- UPDATE --------------------
|
||||
|
||||
/**
|
||||
* Обновить (строго UPDATE) по PK с внешним соединением. Соединение НЕ закрывает.
|
||||
* Если строки нет — updateCount будет 0.
|
||||
*/
|
||||
public int update(Connection c, BlockEntry e) throws SQLException {
|
||||
String sql = """
|
||||
UPDATE blocks
|
||||
SET
|
||||
blockGlobalPreHashe = ?,
|
||||
blockLinePreHashe = ?,
|
||||
msgType = ?,
|
||||
blockByte = ?,
|
||||
toLoginId = ?,
|
||||
toBlockchainId = ?,
|
||||
toBlockGlobalNumber = ?,
|
||||
toBlockHashe = ?
|
||||
WHERE
|
||||
loginId = ?
|
||||
AND blockchainId = ?
|
||||
AND blockGlobalNumber = ?
|
||||
AND blockLineIndex = ?
|
||||
AND blockLineNumber = ?
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
int i = 1;
|
||||
|
||||
ps.setString(i++, nn(e.getBlockGlobalPreHashe()));
|
||||
ps.setString(i++, nn(e.getBlockLinePreHashe()));
|
||||
ps.setInt(i++, e.getMsgType());
|
||||
|
||||
byte[] bytes = e.getBlockByte();
|
||||
if (bytes != null) ps.setBytes(i++, bytes);
|
||||
else ps.setNull(i++, Types.BLOB);
|
||||
|
||||
ps.setLong(i++, e.getToLoginId());
|
||||
ps.setInt(i++, e.getToBlockchainId());
|
||||
ps.setInt(i++, e.getToBlockGlobalNumber());
|
||||
ps.setString(i++, nn(e.getToBlockHashe()));
|
||||
|
||||
ps.setLong(i++, e.getLoginId());
|
||||
ps.setLong(i++, e.getBlockchainId());
|
||||
ps.setInt(i++, e.getBlockGlobalNumber());
|
||||
ps.setInt(i++, e.getBlockLineIndex());
|
||||
ps.setInt(i++, e.getBlockLineNumber());
|
||||
|
||||
return ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
/** Обновить без внешнего соединения. Сам открывает/закрывает. */
|
||||
public int update(BlockEntry e) throws SQLException {
|
||||
try (Connection c = db.getConnection()) {
|
||||
return update(c, e);
|
||||
}
|
||||
}
|
||||
|
||||
// -------------------- DELETE (на всякий) --------------------
|
||||
|
||||
/** Удалить по PK с внешним соединением. Соединение НЕ закрывает. */
|
||||
public int deleteByPk(Connection c,
|
||||
long loginId,
|
||||
long blockchainId,
|
||||
int blockGlobalNumber,
|
||||
int blockLineIndex,
|
||||
int blockLineNumber) throws SQLException {
|
||||
|
||||
String sql = """
|
||||
DELETE FROM blocks
|
||||
WHERE
|
||||
loginId = ?
|
||||
AND blockchainId = ?
|
||||
AND blockGlobalNumber = ?
|
||||
AND blockLineIndex = ?
|
||||
AND blockLineNumber = ?
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setLong(1, loginId);
|
||||
ps.setLong(2, blockchainId);
|
||||
ps.setInt(3, blockGlobalNumber);
|
||||
ps.setInt(4, blockLineIndex);
|
||||
ps.setInt(5, blockLineNumber);
|
||||
return ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
/** Удалить по PK без внешнего соединения. Сам открывает/закрывает. */
|
||||
public int deleteByPk(long loginId,
|
||||
long blockchainId,
|
||||
int blockGlobalNumber,
|
||||
int blockLineIndex,
|
||||
int blockLineNumber) throws SQLException {
|
||||
try (Connection c = db.getConnection()) {
|
||||
return deleteByPk(c, loginId, blockchainId, blockGlobalNumber, blockLineIndex, blockLineNumber);
|
||||
}
|
||||
}
|
||||
|
||||
// -------------------- MAPPER --------------------
|
||||
|
||||
private BlockEntry mapRow(ResultSet rs) throws SQLException {
|
||||
BlockEntry e = new BlockEntry();
|
||||
|
||||
e.setLoginId(rs.getLong("loginId"));
|
||||
e.setBlockchainId(rs.getLong("blockchainId"));
|
||||
e.setBlockGlobalNumber(rs.getInt("blockGlobalNumber"));
|
||||
e.setBlockGlobalPreHashe(rs.getString("blockGlobalPreHashe"));
|
||||
|
||||
e.setBlockLineIndex(rs.getInt("blockLineIndex"));
|
||||
e.setBlockLineNumber(rs.getInt("blockLineNumber"));
|
||||
e.setBlockLinePreHashe(rs.getString("blockLinePreHashe"));
|
||||
|
||||
e.setMsgType(rs.getInt("msgType"));
|
||||
|
||||
e.setBlockByte(rs.getBytes("blockByte"));
|
||||
|
||||
e.setToLoginId(rs.getLong("toLoginId"));
|
||||
e.setToBlockchainId(rs.getInt("toBlockchainId"));
|
||||
e.setToBlockGlobalNumber(rs.getInt("toBlockGlobalNumber"));
|
||||
e.setToBlockHashe(rs.getString("toBlockHashe"));
|
||||
|
||||
return e;
|
||||
}
|
||||
|
||||
private static String nn(String s) { return s == null ? "" : s; }
|
||||
}
|
||||
106
shine-server-db/src/main/java/shine/db/entities/BlockEntry.java
Normal file
106
shine-server-db/src/main/java/shine/db/entities/BlockEntry.java
Normal file
@ -0,0 +1,106 @@
|
||||
package shine.db.entities;
|
||||
|
||||
/**
|
||||
* Запись блока (таблица blocks).
|
||||
*
|
||||
* Идея:
|
||||
* - Храним и "глобальную" позицию (blockGlobalNumber + blockGlobalPreHashe),
|
||||
* и "линейную" позицию (blockLineIndex + blockLineNumber + blockLinePreHashe),
|
||||
* плюс полезные поля маршрутизации (to*).
|
||||
*
|
||||
* В БД:
|
||||
* - int64 -> INTEGER (Java long)
|
||||
* - int32 -> INTEGER (Java int)
|
||||
* - int16 -> INTEGER (Java int/short)
|
||||
* - bytes -> BLOB (Java byte[])
|
||||
* - hashes -> TEXT
|
||||
*/
|
||||
public class BlockEntry {
|
||||
|
||||
private long loginId; // int64
|
||||
private long blockchainId; // int64
|
||||
private int blockGlobalNumber; // int32
|
||||
private String blockGlobalPreHashe; // TEXT
|
||||
|
||||
private int blockLineIndex; // int16 (храним как int)
|
||||
private int blockLineNumber; // int32
|
||||
private String blockLinePreHashe; // TEXT
|
||||
|
||||
private int msgType; // int16 (храним как int)
|
||||
|
||||
private byte[] blockByte; // BLOB
|
||||
|
||||
private long toLoginId; // int64
|
||||
private int toBlockchainId; // int32
|
||||
private int toBlockGlobalNumber; // int32
|
||||
private String toBlockHashe; // TEXT
|
||||
|
||||
public BlockEntry() {}
|
||||
|
||||
public BlockEntry(long loginId,
|
||||
long blockchainId,
|
||||
int blockGlobalNumber,
|
||||
String blockGlobalPreHashe,
|
||||
int blockLineIndex,
|
||||
int blockLineNumber,
|
||||
String blockLinePreHashe,
|
||||
int msgType,
|
||||
byte[] blockByte,
|
||||
long toLoginId,
|
||||
int toBlockchainId,
|
||||
int toBlockGlobalNumber,
|
||||
String toBlockHashe) {
|
||||
this.loginId = loginId;
|
||||
this.blockchainId = blockchainId;
|
||||
this.blockGlobalNumber = blockGlobalNumber;
|
||||
this.blockGlobalPreHashe = blockGlobalPreHashe;
|
||||
this.blockLineIndex = blockLineIndex;
|
||||
this.blockLineNumber = blockLineNumber;
|
||||
this.blockLinePreHashe = blockLinePreHashe;
|
||||
this.msgType = msgType;
|
||||
this.blockByte = blockByte;
|
||||
this.toLoginId = toLoginId;
|
||||
this.toBlockchainId = toBlockchainId;
|
||||
this.toBlockGlobalNumber = toBlockGlobalNumber;
|
||||
this.toBlockHashe = toBlockHashe;
|
||||
}
|
||||
|
||||
public long getLoginId() { return loginId; }
|
||||
public void setLoginId(long loginId) { this.loginId = loginId; }
|
||||
|
||||
public long getBlockchainId() { return blockchainId; }
|
||||
public void setBlockchainId(long blockchainId) { this.blockchainId = blockchainId; }
|
||||
|
||||
public int getBlockGlobalNumber() { return blockGlobalNumber; }
|
||||
public void setBlockGlobalNumber(int blockGlobalNumber) { this.blockGlobalNumber = blockGlobalNumber; }
|
||||
|
||||
public String getBlockGlobalPreHashe() { return blockGlobalPreHashe; }
|
||||
public void setBlockGlobalPreHashe(String blockGlobalPreHashe) { this.blockGlobalPreHashe = blockGlobalPreHashe; }
|
||||
|
||||
public int getBlockLineIndex() { return blockLineIndex; }
|
||||
public void setBlockLineIndex(int blockLineIndex) { this.blockLineIndex = blockLineIndex; }
|
||||
|
||||
public int getBlockLineNumber() { return blockLineNumber; }
|
||||
public void setBlockLineNumber(int blockLineNumber) { this.blockLineNumber = blockLineNumber; }
|
||||
|
||||
public String getBlockLinePreHashe() { return blockLinePreHashe; }
|
||||
public void setBlockLinePreHashe(String blockLinePreHashe) { this.blockLinePreHashe = blockLinePreHashe; }
|
||||
|
||||
public int getMsgType() { return msgType; }
|
||||
public void setMsgType(int msgType) { this.msgType = msgType; }
|
||||
|
||||
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 int getToBlockchainId() { return toBlockchainId; }
|
||||
public void setToBlockchainId(int toBlockchainId) { this.toBlockchainId = toBlockchainId; }
|
||||
|
||||
public int getToBlockGlobalNumber() { return toBlockGlobalNumber; }
|
||||
public void setToBlockGlobalNumber(int toBlockGlobalNumber) { this.toBlockGlobalNumber = toBlockGlobalNumber; }
|
||||
|
||||
public String getToBlockHashe() { return toBlockHashe; }
|
||||
public void setToBlockHashe(String toBlockHashe) { this.toBlockHashe = toBlockHashe; }
|
||||
}
|
||||
Loading…
Reference in New Issue
Block a user