SHA256
16 12 25
Промежуточная версия и ТУДУ на чём остановился
This commit is contained in:
AidarKC
@@ -20,6 +20,7 @@ import java.sql.Statement;
|
||||
* - active_sessions
|
||||
* - users_params
|
||||
* - ip_geo_cache
|
||||
* - blockchain_state (MVP: одна таблица, линии 0..7 внутри строки)
|
||||
*/
|
||||
public class DatabaseInitializer {
|
||||
|
||||
@@ -151,6 +152,50 @@ public class DatabaseInitializer {
|
||||
CREATE INDEX IF NOT EXISTS idx_ip_geo_cache_updated_at
|
||||
ON ip_geo_cache (updated_at_ms);
|
||||
""");
|
||||
|
||||
// 5. blockchain_state (MVP)
|
||||
// TODO: позже можно вынести линии в отдельную таблицу blockchain_line_state и убрать "широкую" схему.
|
||||
st.executeUpdate("""
|
||||
CREATE TABLE IF NOT EXISTS blockchain_state (
|
||||
blockchain_id INTEGER NOT NULL PRIMARY KEY,
|
||||
user_login TEXT NOT NULL,
|
||||
public_key_base64 TEXT NOT NULL,
|
||||
size_limit INTEGER NOT NULL,
|
||||
size_bytes INTEGER NOT NULL,
|
||||
last_global_number INTEGER NOT NULL,
|
||||
last_global_hash TEXT NOT NULL,
|
||||
updated_at_ms INTEGER NOT NULL,
|
||||
|
||||
-- Линии 0..7 (MVP: максимум 8 линий)
|
||||
line0_last_number INTEGER NOT NULL,
|
||||
line0_last_hash TEXT NOT NULL,
|
||||
line1_last_number INTEGER NOT NULL,
|
||||
line1_last_hash TEXT NOT NULL,
|
||||
line2_last_number INTEGER NOT NULL,
|
||||
line2_last_hash TEXT NOT NULL,
|
||||
line3_last_number INTEGER NOT NULL,
|
||||
line3_last_hash TEXT NOT NULL,
|
||||
line4_last_number INTEGER NOT NULL,
|
||||
line4_last_hash TEXT NOT NULL,
|
||||
line5_last_number INTEGER NOT NULL,
|
||||
line5_last_hash TEXT NOT NULL,
|
||||
line6_last_number INTEGER NOT NULL,
|
||||
line6_last_hash TEXT NOT NULL,
|
||||
line7_last_number INTEGER NOT NULL,
|
||||
line7_last_hash TEXT NOT NULL
|
||||
);
|
||||
""");
|
||||
|
||||
// Индексы под быстрые проверки/поиск
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_user_login
|
||||
ON blockchain_state (user_login);
|
||||
""");
|
||||
|
||||
st.executeUpdate("""
|
||||
CREATE INDEX IF NOT EXISTS idx_blockchain_state_updated_at
|
||||
ON blockchain_state (updated_at_ms);
|
||||
""");
|
||||
}
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
@@ -0,0 +1,173 @@
|
||||
package shine.db.dao;
|
||||
|
||||
import shine.db.SqliteDbController;
|
||||
import shine.db.entities.BlockchainStateEntry;
|
||||
|
||||
import java.sql.*;
|
||||
|
||||
/**
|
||||
* DAO для таблицы blockchain_state.
|
||||
* 1 строка = 1 blockchainId, линии 0..7 в колонках.
|
||||
*/
|
||||
public final class BlockchainStateDAO {
|
||||
|
||||
private static volatile BlockchainStateDAO instance;
|
||||
private final SqliteDbController db = SqliteDbController.getInstance();
|
||||
|
||||
private BlockchainStateDAO() {}
|
||||
|
||||
public static BlockchainStateDAO getInstance() {
|
||||
if (instance == null) {
|
||||
synchronized (BlockchainStateDAO.class) {
|
||||
if (instance == null) instance = new BlockchainStateDAO();
|
||||
}
|
||||
}
|
||||
return instance;
|
||||
}
|
||||
|
||||
public BlockchainStateEntry getByBlockchainId(long blockchainId) throws SQLException {
|
||||
String sql = """
|
||||
SELECT
|
||||
blockchain_id,
|
||||
user_login,
|
||||
public_key_base64,
|
||||
size_limit,
|
||||
size_bytes,
|
||||
last_global_number,
|
||||
last_global_hash,
|
||||
line0_last_number, line0_last_hash,
|
||||
line1_last_number, line1_last_hash,
|
||||
line2_last_number, line2_last_hash,
|
||||
line3_last_number, line3_last_hash,
|
||||
line4_last_number, line4_last_hash,
|
||||
line5_last_number, line5_last_hash,
|
||||
line6_last_number, line6_last_hash,
|
||||
line7_last_number, line7_last_hash,
|
||||
updated_at_ms
|
||||
FROM blockchain_state
|
||||
WHERE blockchain_id = ?
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
|
||||
ps.setLong(1, blockchainId);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
if (!rs.next()) return null;
|
||||
return mapRow(rs);
|
||||
}
|
||||
}
|
||||
}
|
||||
|
||||
/**
|
||||
* UPSERT: если строки нет — вставка, если есть — обновление.
|
||||
* Это один вызов из кода, и один SQL.
|
||||
*/
|
||||
public void upsert(BlockchainStateEntry e) throws SQLException {
|
||||
long now = System.currentTimeMillis();
|
||||
if (e.getUpdatedAtMs() <= 0) e.setUpdatedAtMs(now);
|
||||
|
||||
String sql = """
|
||||
INSERT INTO blockchain_state (
|
||||
blockchain_id,
|
||||
user_login,
|
||||
public_key_base64,
|
||||
size_limit,
|
||||
size_bytes,
|
||||
last_global_number,
|
||||
last_global_hash,
|
||||
line0_last_number, line0_last_hash,
|
||||
line1_last_number, line1_last_hash,
|
||||
line2_last_number, line2_last_hash,
|
||||
line3_last_number, line3_last_hash,
|
||||
line4_last_number, line4_last_hash,
|
||||
line5_last_number, line5_last_hash,
|
||||
line6_last_number, line6_last_hash,
|
||||
line7_last_number, line7_last_hash,
|
||||
updated_at_ms
|
||||
) VALUES (
|
||||
?,?,?,?,?,?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?,?,
|
||||
?
|
||||
)
|
||||
ON CONFLICT(blockchain_id)
|
||||
DO UPDATE SET
|
||||
user_login = excluded.user_login,
|
||||
public_key_base64 = excluded.public_key_base64,
|
||||
size_limit = excluded.size_limit,
|
||||
size_bytes = excluded.size_bytes,
|
||||
last_global_number = excluded.last_global_number,
|
||||
last_global_hash = excluded.last_global_hash,
|
||||
|
||||
line0_last_number = excluded.line0_last_number,
|
||||
line0_last_hash = excluded.line0_last_hash,
|
||||
line1_last_number = excluded.line1_last_number,
|
||||
line1_last_hash = excluded.line1_last_hash,
|
||||
line2_last_number = excluded.line2_last_number,
|
||||
line2_last_hash = excluded.line2_last_hash,
|
||||
line3_last_number = excluded.line3_last_number,
|
||||
line3_last_hash = excluded.line3_last_hash,
|
||||
line4_last_number = excluded.line4_last_number,
|
||||
line4_last_hash = excluded.line4_last_hash,
|
||||
line5_last_number = excluded.line5_last_number,
|
||||
line5_last_hash = excluded.line5_last_hash,
|
||||
line6_last_number = excluded.line6_last_number,
|
||||
line6_last_hash = excluded.line6_last_hash,
|
||||
line7_last_number = excluded.line7_last_number,
|
||||
line7_last_hash = excluded.line7_last_hash,
|
||||
|
||||
updated_at_ms = excluded.updated_at_ms
|
||||
""";
|
||||
|
||||
try (PreparedStatement ps = db.getConnection().prepareStatement(sql)) {
|
||||
|
||||
int i = 1;
|
||||
ps.setLong(i++, e.getBlockchainId());
|
||||
ps.setString(i++, nn(e.getUserLogin()));
|
||||
ps.setString(i++, nn(e.getPublicKeyBase64()));
|
||||
ps.setInt(i++, e.getSizeLimit());
|
||||
ps.setInt(i++, e.getSizeBytes());
|
||||
ps.setInt(i++, e.getLastGlobalNumber());
|
||||
ps.setString(i++, nn(e.getLastGlobalHash()));
|
||||
|
||||
for (int line = 0; line < 8; line++) {
|
||||
ps.setInt(i++, e.getLastLineNumber(line));
|
||||
ps.setString(i++, nn(e.getLastLineHash(line)));
|
||||
}
|
||||
|
||||
ps.setLong(i++, e.getUpdatedAtMs());
|
||||
|
||||
ps.executeUpdate();
|
||||
}
|
||||
}
|
||||
|
||||
private BlockchainStateEntry mapRow(ResultSet rs) throws SQLException {
|
||||
BlockchainStateEntry e = new BlockchainStateEntry();
|
||||
e.setBlockchainId(rs.getLong("blockchain_id"));
|
||||
e.setUserLogin(rs.getString("user_login"));
|
||||
e.setPublicKeyBase64(rs.getString("public_key_base64"));
|
||||
|
||||
e.setSizeLimit(rs.getInt("size_limit"));
|
||||
e.setSizeBytes(rs.getInt("size_bytes"));
|
||||
|
||||
e.setLastGlobalNumber(rs.getInt("last_global_number"));
|
||||
e.setLastGlobalHash(rs.getString("last_global_hash"));
|
||||
|
||||
for (int line = 0; line < 8; line++) {
|
||||
e.setLastLineNumber(line, rs.getInt("line" + line + "_last_number"));
|
||||
e.setLastLineHash(line, rs.getString("line" + line + "_last_hash"));
|
||||
}
|
||||
|
||||
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
|
||||
return e;
|
||||
}
|
||||
|
||||
private static String nn(String s) {
|
||||
return s == null ? "" : s;
|
||||
}
|
||||
}
|
||||
@@ -0,0 +1,124 @@
|
||||
package shine.db.entities;
|
||||
|
||||
import java.util.Arrays;
|
||||
|
||||
/**
|
||||
* Агрегатная сущность текущего состояния блокчейна.
|
||||
* 1 строка = 1 blockchainId, плюс состояние линий 0..7.
|
||||
*/
|
||||
public final class BlockchainStateEntry {
|
||||
|
||||
private long blockchainId;
|
||||
|
||||
private String userLogin;
|
||||
private String publicKeyBase64;
|
||||
|
||||
private int sizeLimit;
|
||||
private int sizeBytes;
|
||||
|
||||
private int lastGlobalNumber;
|
||||
private String lastGlobalHash; // HEX(64) либо пустая строка для "нулевого"
|
||||
|
||||
/** line 0..7 */
|
||||
private final int[] lastLineNumbers = new int[8];
|
||||
/** line 0..7 */
|
||||
private final String[] lastLineHashes = new String[8];
|
||||
|
||||
private long updatedAtMs;
|
||||
|
||||
public BlockchainStateEntry() {
|
||||
// по умолчанию хэши пустые (как "0")
|
||||
for (int i = 0; i < 8; i++) lastLineHashes[i] = "";
|
||||
this.lastGlobalHash = "";
|
||||
}
|
||||
|
||||
// --- удобный конструктор (если хочешь) ---
|
||||
public BlockchainStateEntry(long blockchainId,
|
||||
String userLogin,
|
||||
String publicKeyBase64,
|
||||
int sizeLimit,
|
||||
int sizeBytes,
|
||||
int lastGlobalNumber,
|
||||
String lastGlobalHash,
|
||||
int[] lastLineNumbers,
|
||||
String[] lastLineHashes,
|
||||
long updatedAtMs) {
|
||||
this.blockchainId = blockchainId;
|
||||
this.userLogin = userLogin;
|
||||
this.publicKeyBase64 = publicKeyBase64;
|
||||
this.sizeLimit = sizeLimit;
|
||||
this.sizeBytes = sizeBytes;
|
||||
this.lastGlobalNumber = lastGlobalNumber;
|
||||
this.lastGlobalHash = lastGlobalHash == null ? "" : lastGlobalHash;
|
||||
|
||||
if (lastLineNumbers != null) {
|
||||
if (lastLineNumbers.length != 8) throw new IllegalArgumentException("lastLineNumbers must be len=8");
|
||||
System.arraycopy(lastLineNumbers, 0, this.lastLineNumbers, 0, 8);
|
||||
}
|
||||
if (lastLineHashes != null) {
|
||||
if (lastLineHashes.length != 8) throw new IllegalArgumentException("lastLineHashes must be len=8");
|
||||
for (int i = 0; i < 8; i++) this.lastLineHashes[i] = lastLineHashes[i] == null ? "" : lastLineHashes[i];
|
||||
} else {
|
||||
for (int i = 0; i < 8; i++) this.lastLineHashes[i] = "";
|
||||
}
|
||||
|
||||
this.updatedAtMs = updatedAtMs;
|
||||
}
|
||||
|
||||
// --- getters / setters ---
|
||||
|
||||
public long getBlockchainId() { return blockchainId; }
|
||||
public void setBlockchainId(long blockchainId) { this.blockchainId = blockchainId; }
|
||||
|
||||
public String getUserLogin() { return userLogin; }
|
||||
public void setUserLogin(String userLogin) { this.userLogin = userLogin; }
|
||||
|
||||
public String getPublicKeyBase64() { return publicKeyBase64; }
|
||||
public void setPublicKeyBase64(String publicKeyBase64) { this.publicKeyBase64 = publicKeyBase64; }
|
||||
|
||||
public int getSizeLimit() { return sizeLimit; }
|
||||
public void setSizeLimit(int sizeLimit) { this.sizeLimit = sizeLimit; }
|
||||
|
||||
public int getSizeBytes() { return sizeBytes; }
|
||||
public void setSizeBytes(int sizeBytes) { this.sizeBytes = sizeBytes; }
|
||||
|
||||
public int getLastGlobalNumber() { return lastGlobalNumber; }
|
||||
public void setLastGlobalNumber(int lastGlobalNumber) { this.lastGlobalNumber = lastGlobalNumber; }
|
||||
|
||||
public String getLastGlobalHash() { return lastGlobalHash; }
|
||||
public void setLastGlobalHash(String lastGlobalHash) { this.lastGlobalHash = lastGlobalHash == null ? "" : lastGlobalHash; }
|
||||
|
||||
/** line in [0..7] */
|
||||
public int getLastLineNumber(int line) {
|
||||
checkLine(line);
|
||||
return lastLineNumbers[line];
|
||||
}
|
||||
public void setLastLineNumber(int line, int value) {
|
||||
checkLine(line);
|
||||
lastLineNumbers[line] = value;
|
||||
}
|
||||
|
||||
/** line in [0..7] */
|
||||
public String getLastLineHash(int line) {
|
||||
checkLine(line);
|
||||
return lastLineHashes[line];
|
||||
}
|
||||
public void setLastLineHash(int line, String value) {
|
||||
checkLine(line);
|
||||
lastLineHashes[line] = value == null ? "" : value;
|
||||
}
|
||||
|
||||
public int[] getLastLineNumbersCopy() {
|
||||
return Arrays.copyOf(lastLineNumbers, 8);
|
||||
}
|
||||
public String[] getLastLineHashesCopy() {
|
||||
return Arrays.copyOf(lastLineHashes, 8);
|
||||
}
|
||||
|
||||
public long getUpdatedAtMs() { return updatedAtMs; }
|
||||
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
|
||||
|
||||
private static void checkLine(int line) {
|
||||
if (line < 0 || line > 7) throw new IllegalArgumentException("line must be 0..7");
|
||||
}
|
||||
}
|
||||
Reference in New Issue
Block a user