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

refactor: перенос серверных модулей в папку SHiNE-server

Browse Source
This commit is contained in:
AidarKC
2026-05-30 17:12:15 +04:00
parent 134e877b7c
commit 1b0e1cf1d4
312 changed files with 34 additions and 8 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/all_files.txt
+2832
View File
File diff suppressed because it is too large Load Diff
SHiNE-server/shine-server-db/build.gradle
+29
View File
@@ -0,0 +1,29 @@
plugins {
id 'java'
}
group = 'shine'
version = '1.0.0'
java {
toolchain {
languageVersion = JavaLanguageVersion.of(17)
}
}
repositories {
mavenCentral()
}
dependencies {
implementation 'org.xerial:sqlite-jdbc:3.47.0.0' // sqlite
implementation "org.slf4j:slf4j-api:2.0.16" // вызов логгера
implementation project(':shine-server-config') // модуль с настройками
}
java {
sourceCompatibility = JavaVersion.VERSION_17
targetCompatibility = JavaVersion.VERSION_17
}
SHiNE-server/shine-server-db/concat_to_file.sh
Executable
+20
View File
@@ -0,0 +1,20 @@
#!/usr/bin/env bash
set -euo pipefail
OUTFILE="all_files.txt"
# очищаем или создаём файл
: > "$OUTFILE"
# собрать только *.java файлы и вывести их содержимое в файл
find . -type f -name "*.java" | sort | while read -r f; do
cat "$f" >> "$OUTFILE"
echo >> "$OUTFILE" # пустая строка-разделитель
done
# скопировать весь файл в буфер обмена (Wayland)
wl-copy < "$OUTFILE"
echo "Готово!"
echo "Все .java файлы собраны в $OUTFILE"
echo "Содержимое скопировано в буфер обмена (Wayland)"
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+650
View File
@@ -0,0 +1,650 @@
package shine.db;
import utils.config.AppConfig;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.file.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
/**
* DatabaseInitializer — создание новой SQLite-БД по схеме SHiNE.
*
* В этой версии:
* - создаём ТОЛЬКО таблицы/индексы
* - в конце вызываем DatabaseTriggersInstaller.createAllTriggers(st)
*
* v2 (sessions):
* - active_sessions.session_pwd удалён
* - active_sessions.session_key хранит публичный ключ сессии целиком одной строкой
*/
public final class DatabaseInitializer {
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
public static final int SCHEMA_VERSION_1 = 1;
private DatabaseInitializer() {}
/* ===================== TEXT (msg_type=1) ===================== */
public static final short TEXT_POST = 10;
public static final short TEXT_EDIT_POST = 11;
public static final short TEXT_REPLY = 20;
public static final short TEXT_EDIT_REPLY = 21;
public static final short TEXT_REPOST = 30;
/* ===================== REACTION (msg_type=2) ===================== */
public static final short REACTION_LIKE = 1;
public static final short REACTION_UNLIKE = 2;
/* ===================== CONNECTION (msg_type=3) ===================== */
// Близкий друг (close friend). Исторически в коде использовалось имя FRIEND.
public static final short CONNECTION_FRIEND = 10;
public static final short CONNECTION_UNFRIEND = 11;
public static final short CONNECTION_CLOSE_FRIEND = CONNECTION_FRIEND;
public static final short CONNECTION_UNCLOSE_FRIEND = CONNECTION_UNFRIEND;
public static final short CONNECTION_CONTACT = 20;
public static final short CONNECTION_UNCONTACT = 21;
public static final short CONNECTION_FOLLOW = 30;
public static final short CONNECTION_UNFOLLOW = 31;
public static final short CONNECTION_SPOUSE = 40;
public static final short CONNECTION_UNSPOUSE = 41;
public static final short CONNECTION_PARENT = 50;
public static final short CONNECTION_UNPARENT = 51;
public static final short CONNECTION_CHILD = 52;
public static final short CONNECTION_UNCHILD = 53;
public static final short CONNECTION_SIBLING = 54;
public static final short CONNECTION_UNSIBLING = 55;
public static final short CONNECTION_KNOWN_PERSON = 60;
public static final short CONNECTION_UNKNOWN_PERSON = 61;
public static final short CONNECTION_SHINE_CONFIRMED = 70;
public static final short CONNECTION_SHINE_UNCONFIRMED = 71;
public static final short CONNECTION_SHINE_SEEN = 74;
public static final short CONNECTION_SHINE_UNSEEN = 75;
public static void createNewDB(String[] args) {
AppConfig config = AppConfig.getInstance();
String dbPath = config.getParam("db.path");
if (dbPath == null || dbPath.isBlank()) {
System.err.println("Параметр db.path не задан в application.properties");
return;
}
Path dbFile = Paths.get(dbPath);
try {
Path parent = dbFile.getParent();
if (parent != null && !Files.exists(parent)) {
Files.createDirectories(parent);
}
if (Files.exists(dbFile)) {
System.out.println("Файл базы данных уже существует: " + dbFile.toAbsolutePath());
System.out.print("Пересоздать БД (СТАРАЯ БУДЕТ УДАЛЕНА)? [y/N]: ");
BufferedReader reader = new BufferedReader(new InputStreamReader(System.in));
String answer = reader.readLine();
if (!"y".equalsIgnoreCase(answer) && !"yes".equalsIgnoreCase(answer)) {
System.out.println("Операция отменена. БД не изменена.");
return;
}
Files.delete(dbFile);
System.out.println("Старый файл БД удалён.");
}
createSchema("jdbc:sqlite:" + dbPath);
System.out.println("Новая БД успешно создана по пути: " + dbFile.toAbsolutePath());
} catch (IOException e) {
System.err.println("Ошибка работы с файлом БД: " + e.getMessage());
} catch (SQLException e) {
System.err.println("Ошибка создания схемы БД: " + e.getMessage());
}
}
public static void ensureSchemaV1Structure(String jdbcUrl) throws SQLException {
createSchema(jdbcUrl, false);
}
private static void createSchema(String jdbcUrl) throws SQLException {
createSchema(jdbcUrl, true);
}
private static void createSchema(String jdbcUrl, boolean initializeVersionRow) throws SQLException {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
throw new RuntimeException("SQLite JDBC driver not found", e);
}
try (Connection conn = DriverManager.getConnection(jdbcUrl);
Statement st = conn.createStatement()) {
st.execute("PRAGMA foreign_keys = ON");
// 1. solana_users
// ВАЖНО:
// - Все требуемые поля теперь лежат в solana_users:
// login, blockchain_name, solana_key, blockchain_key, device_key
// - Поиск по login в DAO сделан case-insensitive.
// - Для защиты от дублей "Anya" и "anya" добавляем COLLATE NOCASE на PRIMARY KEY.
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS solana_users (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
blockchain_name TEXT NOT NULL,
solana_key TEXT NOT NULL,
blockchain_key TEXT NOT NULL,
device_key TEXT NOT NULL
);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_solana_users_blockchain_name
ON solana_users (blockchain_name);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_solana_users_login
ON solana_users (login);
""");
// 2. active_sessions (v2)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS active_sessions (
session_id TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
session_key TEXT NOT NULL,
storage_pwd TEXT NOT NULL,
session_created_at_ms INTEGER NOT NULL,
last_authirificated_at_ms INTEGER NOT NULL,
push_endpoint TEXT,
push_p256dh_key TEXT,
push_auth_key TEXT,
client_ip TEXT,
client_info_from_client TEXT,
client_info_from_request TEXT,
user_language TEXT,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
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 (
login TEXT NOT NULL,
param TEXT NOT NULL,
time_ms INTEGER NOT NULL,
value TEXT NOT NULL,
device_key TEXT,
signature TEXT,
FOREIGN KEY (login) REFERENCES solana_users(login),
UNIQUE (login, param)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_users_params_login
ON users_params (login);
""");
// 4. ip_geo_cache
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS ip_geo_cache (
ip TEXT NOT NULL PRIMARY KEY,
geo TEXT,
updated_at_ms INTEGER NOT NULL
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_ip_geo_cache_updated_at
ON ip_geo_cache (updated_at_ms);
""");
// 5. blockchain_state
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blockchain_state (
blockchain_name TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
blockchain_key TEXT NOT NULL,
size_limit INTEGER NOT NULL,
file_size_bytes INTEGER NOT NULL,
last_block_number INTEGER NOT NULL,
last_block_hash BLOB,
updated_at_ms INTEGER NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blockchain_state_login
ON blockchain_state (login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blockchain_state_updated_at
ON blockchain_state (updated_at_ms);
""");
// 6. blocks (+ line_code)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS blocks (
login TEXT NOT NULL,
bch_name TEXT NOT NULL,
block_number INTEGER NOT NULL CHECK(block_number >= 0),
msg_type INTEGER NOT NULL,
msg_sub_type INTEGER NOT NULL,
block_bytes BLOB NOT NULL,
-- target (reply/like/edit и т.д.)
to_login TEXT,
to_bch_name TEXT,
to_block_number INTEGER CHECK(to_block_number IS NULL OR to_block_number >= 0),
to_block_hash BLOB,
-- собственные данные
block_hash BLOB NOT NULL,
block_signature BLOB NOT NULL,
-- если этот блок был изменён последним edit'ом
edited_by_block_number INTEGER CHECK(edited_by_block_number IS NULL OR edited_by_block_number >= 0),
-- линейность (опционально)
line_code INTEGER CHECK(line_code IS NULL OR line_code >= 0),
prev_line_number INTEGER CHECK(prev_line_number IS NULL OR prev_line_number >= 0),
prev_line_hash BLOB,
this_line_number INTEGER CHECK(this_line_number IS NULL OR this_line_number >= 0),
FOREIGN KEY (login) REFERENCES solana_users(login),
FOREIGN KEY (bch_name) REFERENCES blockchain_state(blockchain_name),
UNIQUE (bch_name, block_number)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blocks_by_chain_number
ON blocks (bch_name, block_number);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
ON blocks (to_login, to_bch_name, to_block_number);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_blocks_by_line
ON blocks (bch_name, line_code, this_line_number);
""");
// 7) connections_state
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS connections_state (
login TEXT NOT NULL,
rel_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login),
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_login
ON connections_state (login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_to_login
ON connections_state (to_login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_pair
ON connections_state (login, to_login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_target
ON connections_state (login, rel_type, to_bch_name, to_block_number);
""");
// 8) message_stats
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS message_stats (
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
likes_count INTEGER NOT NULL DEFAULT 0,
replies_count INTEGER NOT NULL DEFAULT 0,
edits_count INTEGER NOT NULL DEFAULT 0,
UNIQUE (
to_login,
to_bch_name,
to_block_number,
to_block_hash
)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_message_stats_target
ON message_stats (to_bch_name, to_block_number, to_block_hash);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_message_stats_login
ON message_stats (to_login);
""");
// 8.0) reactions_state (идемпотентный LIKE/UNLIKE per actor/target)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS reactions_state (
from_login TEXT NOT NULL,
from_bch_name TEXT NOT NULL,
reaction_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
last_sub_type INTEGER NOT NULL,
UNIQUE (
from_login,
from_bch_name,
reaction_type,
to_login,
to_bch_name,
to_block_number,
to_block_hash
)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_reactions_state_target
ON reactions_state (to_bch_name, to_block_number, to_block_hash);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_reactions_state_actor
ON reactions_state (from_login, from_bch_name, reaction_type);
""");
// 9) channel_names_state (global normalized channel names)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS channel_names_state (
slug TEXT NOT NULL,
display_name TEXT NOT NULL,
channel_description TEXT NOT NULL DEFAULT '',
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_type_code INTEGER NOT NULL DEFAULT 1,
channel_type_version INTEGER NOT NULL DEFAULT 1,
channel_root_block_number INTEGER NOT NULL,
channel_root_block_hash BLOB NOT NULL,
created_at_ms INTEGER NOT NULL
);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_owner_type_slug
ON channel_names_state (owner_bch_name, channel_type_code, slug);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_target
ON channel_names_state (owner_bch_name, channel_root_block_number, channel_root_block_hash);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_channel_names_state_owner
ON channel_names_state (owner_login, owner_bch_name);
""");
// 9.1) chat200_state
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS chat200_state (
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL,
channel_root_block_hash BLOB NOT NULL,
channel_name TEXT NOT NULL,
channel_type_version INTEGER NOT NULL,
chat_title TEXT NOT NULL DEFAULT '',
updated_at_ms INTEGER NOT NULL,
PRIMARY KEY (owner_bch_name, channel_root_block_number)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_chat200_state_owner
ON chat200_state (owner_login, owner_bch_name);
""");
// 9.2) chat200_members_state
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS chat200_members_state (
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL,
member_login TEXT NOT NULL,
member_channel_name TEXT NOT NULL,
is_active INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
updated_by_block_number INTEGER NOT NULL,
PRIMARY KEY (
owner_bch_name,
channel_root_block_number,
member_login,
member_channel_name
)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_chat200_members_owner
ON chat200_members_state (owner_bch_name, channel_root_block_number, is_active);
""");
// 10) direct_messages
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS direct_messages (
message_id TEXT NOT NULL PRIMARY KEY,
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
text TEXT NOT NULL,
created_at_ms INTEGER NOT NULL,
FOREIGN KEY (from_login) REFERENCES solana_users(login),
FOREIGN KEY (to_login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_direct_messages_to_login
ON direct_messages (to_login, created_at_ms);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_direct_messages_from_login
ON direct_messages (from_login, created_at_ms);
""");
// 11) user_push_tokens
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS user_push_tokens (
token_id TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
session_id TEXT NOT NULL,
provider TEXT NOT NULL,
token TEXT NOT NULL,
platform TEXT,
user_agent TEXT,
updated_at_ms INTEGER NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login
ON user_push_tokens (login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login_session
ON user_push_tokens (login, session_id);
""");
// 11) signed_direct_message_replay (anti-replay window)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS signed_direct_message_replay (
from_login TEXT NOT NULL,
time_ms INTEGER NOT NULL,
nonce INTEGER NOT NULL,
created_at_ms INTEGER NOT NULL,
UNIQUE (from_login, time_ms, nonce)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_signed_dm_replay_created
ON signed_direct_message_replay (created_at_ms);
""");
// 12) signed_direct_messages_history (сырой бинарный пакет + мета)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS signed_direct_messages_history (
message_id TEXT NOT NULL PRIMARY KEY,
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
target_mode INTEGER NOT NULL,
target_session_id TEXT,
message_type INTEGER NOT NULL,
time_ms INTEGER NOT NULL,
nonce INTEGER NOT NULL,
raw_packet BLOB NOT NULL,
created_at_ms INTEGER NOT NULL,
FOREIGN KEY (from_login) REFERENCES solana_users(login),
FOREIGN KEY (to_login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_signed_dm_history_to
ON signed_direct_messages_history (to_login, created_at_ms);
""");
// 13) signed_messages_v2 (универсальное хранилище блоков типов 1/2/3/4)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS signed_messages_v2 (
message_key TEXT NOT NULL PRIMARY KEY,
base_key TEXT NOT NULL,
target_login TEXT NOT NULL,
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
time_ms INTEGER NOT NULL,
nonce INTEGER NOT NULL,
message_type INTEGER NOT NULL,
raw_block BLOB NOT NULL,
created_at_ms INTEGER NOT NULL,
source_api TEXT NOT NULL,
origin_session_id TEXT,
receipt_ref_base_key TEXT,
receipt_ref_type INTEGER,
FOREIGN KEY (from_login) REFERENCES solana_users(login),
FOREIGN KEY (to_login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_signed_messages_v2_target
ON signed_messages_v2 (target_login, time_ms, created_at_ms);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_signed_messages_v2_base
ON signed_messages_v2 (base_key, message_type);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_v2_receipt_incoming
ON signed_messages_v2 (target_login, receipt_ref_base_key)
WHERE message_type = 3 AND receipt_ref_base_key IS NOT NULL;
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_v2_receipt_outgoing
ON signed_messages_v2 (target_login, receipt_ref_base_key)
WHERE message_type = 4 AND receipt_ref_base_key IS NOT NULL;
""");
// 14) signed_message_session_delivery (доставка по сессиям)
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
message_key TEXT NOT NULL,
session_id TEXT NOT NULL,
delivered INTEGER NOT NULL DEFAULT 0,
delivered_at_ms INTEGER,
created_at_ms INTEGER NOT NULL,
PRIMARY KEY (message_key, session_id),
FOREIGN KEY (message_key) REFERENCES signed_messages_v2(message_key)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
ON signed_message_session_delivery (session_id, delivered);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS db_schema_version (
id INTEGER NOT NULL PRIMARY KEY CHECK (id = 1),
schema_version INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL
);
""");
if (initializeVersionRow) {
st.executeUpdate("""
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 1, CAST(strftime('%s','now') AS INTEGER) * 1000)
ON CONFLICT(id) DO UPDATE SET
schema_version = excluded.schema_version,
updated_at_ms = excluded.updated_at_ms;
""");
}
DatabaseTriggersInstaller.createAllTriggers(st);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseTriggersInstaller.java
+569
View File
@@ -0,0 +1,569 @@
package shine.db;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* DatabaseTriggersInstaller — устанавливает триггеры, которые поддерживают бизнес-логику БД.
*
* Мы специально сделали триггеры максимально "совместимыми":
* - НЕТ динамических сообщений в RAISE(...): только фиксированные строки.
* (Некоторые SQLite-сборки / просмотрщики падают на "||" внутри RAISE.)
* - НЕТ UPSERT "ON CONFLICT DO UPDATE" — вместо него:
* INSERT OR IGNORE + UPDATE
* (Старые SQLite не знают UPSERT.)
*
* =============================================================================
* ОПИСАНИЕ ТРИГГЕРОВ
* =============================================================================
*
* [1] trg_blocks_line_integrity_bi (BEFORE INSERT ON blocks)
* Контроль целостности "линий" (line_code / prev_line_number / prev_line_hash / this_line_number).
*
* Зачем это нужно:
* - В каналах/ветках/действиях ты хочешь иметь "линейную" последовательность,
* где каждый следующий блок явно ссылается на предыдущий блок линии
* и подтверждает, что ссылка не подменена.
*
* Когда срабатывает:
* - ТОЛЬКО если при вставке передано ХОТЯ БЫ ОДНО из line-полей.
* - Если line-поля не переданы — триггер вообще не работает (это важно).
*
* Что проверяет:
* A) line-поля допускаются только для msg_type:
* 0 (TECH), 1 (TEXT), 3 (CONNECTION), 4 (USER_PARAM)
* B) Если пришло хоть одно line-поле — обязаны прийти ВСЕ 4 (никаких "частичных")
* C) prev-блок линии существует в той же цепочке bch_name
* D) prev_hash совпадает с block_hash найденного prev-блока
* E) line_code корректный:
* - либо первый шаг после root: prev_line_number == line_code
* - либо prev уже принадлежит этой линии: p.line_code == NEW.line_code
* F) this_line_number:
* - первый шаг после root:
* TEXT: this_line_number = 0
* TECH/CONNECTION/USER_PARAM: this_line_number = 1
* - обычный шаг:
* TEXT: допускаем same или +1 (чтобы "edit" мог не двигать шаг)
* TECH/CONNECTION/USER_PARAM: строго prev.this + 1
*
* Какие ошибки кидает:
* - LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE
* - LINE_ERR_PARTIAL_FIELDS
* - LINE_ERR_NO_PREV
* - LINE_ERR_PREV_HASH_MISMATCH
* - LINE_ERR_LINE_CODE_MISMATCH
* - LINE_ERR_FIRST_STEP_BAD_THIS
* - LINE_ERR_THIS_LINE_BAD_STEP
*
* [2] trg_blocks_connection_state_ai (AFTER INSERT ON blocks WHEN msg_type=3)
* Поддерживает таблицу connections_state как "текущее состояние" отношений:
* - FRIEND/CONTACT/FOLLOW -> добавить/обновить состояние
* - UNFRIEND/UNCONTACT/UNFOLLOW -> удалить соответствующее "позитивное" состояние
*
* [3] trg_blocks_message_stats_like_ai (AFTER INSERT ON blocks WHEN msg_type=2 AND sub_type=LIKE)
* Поддерживает likes_count в message_stats для цели (to_*).
*
* [4] trg_blocks_message_stats_reply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=REPLY)
* Поддерживает replies_count в message_stats.
*
* [5] trg_blocks_edit_apply_ai (AFTER INSERT ON blocks WHEN msg_type=1 AND sub_type=EDIT)
* Логика edit:
* - помечает исходный блок edited_by_block_number = NEW.block_number
* - увеличивает edits_count в message_stats
*/
public final class DatabaseTriggersInstaller {
private DatabaseTriggersInstaller() {}
public static void createAllTriggers(Statement st) throws SQLException {
dropTriggersByPrefix(st, "trg_blocks_");
// На всякий случай убираем старые "криво названные" триггеры,
// если они когда-то попадали в БД.
st.executeUpdate("DROP TRIGGER IF EXISTS trg_block_lini_integriti_by;");
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_line_integrity_bi;");
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_connection_state_ai;");
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_like_ai;");
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_message_stats_reply_ai;");
st.executeUpdate("DROP TRIGGER IF EXISTS trg_blocks_edit_apply_ai;");
createLineIntegrityTrigger(st);
createConnectionStateTrigger(st);
createMessageStatsLikeTrigger(st);
createMessageStatsReplyTrigger(st);
createEditApplyTrigger(st);
}
private static void dropTriggersByPrefix(Statement st, String prefix) throws SQLException {
List<String> triggerNames = new ArrayList<>();
String sql = "SELECT name FROM sqlite_master WHERE type='trigger' AND name LIKE '" + prefix + "%'";
try (ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
String name = rs.getString("name");
if (name != null && !name.isBlank()) {
triggerNames.add(name);
}
}
}
for (String name : triggerNames) {
String safeName = name.replace("\"", "\"\"");
st.executeUpdate("DROP TRIGGER IF EXISTS \"" + safeName + "\"");
}
}
private static void createLineIntegrityTrigger(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TRIGGER IF NOT EXISTS trg_blocks_line_integrity_bi
BEFORE INSERT ON blocks
WHEN
NEW.line_code IS NOT NULL
OR NEW.prev_line_number IS NOT NULL
OR NEW.prev_line_hash IS NOT NULL
OR NEW.this_line_number IS NOT NULL
BEGIN
SELECT RAISE(ABORT, 'LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE')
WHERE NOT (NEW.msg_type IN (0, 1, 3, 4));
SELECT RAISE(ABORT, 'LINE_ERR_PARTIAL_FIELDS')
WHERE NEW.line_code IS NULL
OR NEW.prev_line_number IS NULL
OR NEW.prev_line_hash IS NULL
OR NEW.this_line_number IS NULL;
SELECT RAISE(ABORT, 'LINE_ERR_NO_PREV')
WHERE NOT EXISTS(
SELECT 1
FROM blocks p
WHERE p.bch_name = NEW.bch_name
AND p.block_number = NEW.prev_line_number
LIMIT 1
);
SELECT RAISE(ABORT, 'LINE_ERR_PREV_HASH_MISMATCH')
WHERE NOT EXISTS(
SELECT 1
FROM blocks p
WHERE p.bch_name = NEW.bch_name
AND p.block_number = NEW.prev_line_number
AND p.block_hash = NEW.prev_line_hash
LIMIT 1
);
SELECT RAISE(ABORT, 'LINE_ERR_LINE_CODE_MISMATCH')
WHERE NEW.prev_line_number <> NEW.line_code
AND NOT EXISTS(
SELECT 1
FROM blocks p
WHERE p.bch_name = NEW.bch_name
AND p.block_number = NEW.prev_line_number
AND p.line_code = NEW.line_code
LIMIT 1
);
SELECT RAISE(ABORT, 'LINE_ERR_FIRST_STEP_BAD_THIS')
WHERE NEW.prev_line_number = NEW.line_code
AND NEW.this_line_number <> (CASE WHEN NEW.msg_type = 1 THEN 0 ELSE 1 END);
SELECT RAISE(ABORT, 'LINE_ERR_THIS_LINE_BAD_STEP')
WHERE NEW.prev_line_number <> NEW.line_code
AND NOT EXISTS(
SELECT 1
FROM blocks p
WHERE p.bch_name = NEW.bch_name
AND p.block_number = NEW.prev_line_number
AND p.this_line_number IS NOT NULL
AND (
(NEW.msg_type = 1 AND
(NEW.this_line_number = p.this_line_number OR NEW.this_line_number = p.this_line_number + 1)
)
OR
(NEW.msg_type IN (0,3,4) AND NEW.this_line_number = p.this_line_number + 1)
)
LIMIT 1
);
END;
""");
}
private static void createConnectionStateTrigger(Statement st) throws SQLException {
int FRIEND = (int) DatabaseInitializer.CONNECTION_FRIEND;
int CONTACT = (int) DatabaseInitializer.CONNECTION_CONTACT;
int FOLLOW = (int) DatabaseInitializer.CONNECTION_FOLLOW;
int SPOUSE = (int) DatabaseInitializer.CONNECTION_SPOUSE;
int PARENT = (int) DatabaseInitializer.CONNECTION_PARENT;
int CHILD = (int) DatabaseInitializer.CONNECTION_CHILD;
int SIBLING = (int) DatabaseInitializer.CONNECTION_SIBLING;
int KNOWN = (int) DatabaseInitializer.CONNECTION_KNOWN_PERSON;
int SHINE_CONF = (int) DatabaseInitializer.CONNECTION_SHINE_CONFIRMED;
int SHINE_SEEN = (int) DatabaseInitializer.CONNECTION_SHINE_SEEN;
int UNFRIEND = (int) DatabaseInitializer.CONNECTION_UNFRIEND;
int UNCONTACT = (int) DatabaseInitializer.CONNECTION_UNCONTACT;
int UNFOLLOW = (int) DatabaseInitializer.CONNECTION_UNFOLLOW;
int UNSPOUSE = (int) DatabaseInitializer.CONNECTION_UNSPOUSE;
int UNPARENT = (int) DatabaseInitializer.CONNECTION_UNPARENT;
int UNCHILD = (int) DatabaseInitializer.CONNECTION_UNCHILD;
int UNSIBLING = (int) DatabaseInitializer.CONNECTION_UNSIBLING;
int UNKNOWN = (int) DatabaseInitializer.CONNECTION_UNKNOWN_PERSON;
int SHINE_UNCONF = (int) DatabaseInitializer.CONNECTION_SHINE_UNCONFIRMED;
int SHINE_UNSEEN = (int) DatabaseInitializer.CONNECTION_SHINE_UNSEEN;
st.executeUpdate("""
CREATE TRIGGER IF NOT EXISTS trg_blocks_connection_state_ai
AFTER INSERT ON blocks
WHEN NEW.msg_type = 3
BEGIN
-- FRIEND/CONTACT/FOLLOW/SPOUSE/PARENT/CHILD/SIBLING/KNOWN_PERSON/SHINE_*:
-- 1) если записи нет — создаём
INSERT OR IGNORE INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
)
SELECT
NEW.login,
NEW.msg_sub_type,
COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
),
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash
WHERE NEW.msg_sub_type IN (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d)
AND COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
) IS NOT NULL
AND NEW.to_bch_name IS NOT NULL;
-- 2) если запись есть — обновляем актуальные to_*
UPDATE connections_state
SET
to_bch_name = NEW.to_bch_name,
to_block_number = NEW.to_block_number,
to_block_hash = NEW.to_block_hash
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
)
AND NEW.msg_sub_type IN (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d)
AND COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
) IS NOT NULL
AND NEW.to_bch_name IS NOT NULL;
-- UNFRIEND/UNCONTACT/UNFOLLOW/UNSPOUSE/UNPARENT/UNCHILD/UNSIBLING/UNKNOWN_PERSON/SHINE_UN*:
-- удаляем соответствующее "позитивное" состояние
DELETE FROM connections_state
WHERE login = NEW.login
AND to_login = COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
)
AND rel_type = CASE NEW.msg_sub_type
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
WHEN %d THEN %d
ELSE rel_type
END
AND COALESCE(
NEW.to_login,
(
SELECT su.login
FROM solana_users su
WHERE su.blockchain_name = NEW.to_bch_name COLLATE NOCASE
LIMIT 1
),
CASE
WHEN NEW.to_bch_name IS NOT NULL
AND length(NEW.to_bch_name) > 4
AND substr(NEW.to_bch_name, length(NEW.to_bch_name) - 3, 1) = '-'
THEN substr(NEW.to_bch_name, 1, length(NEW.to_bch_name) - 4)
ELSE NULL
END
) IS NOT NULL
AND NEW.msg_sub_type IN (%d, %d, %d, %d, %d, %d, %d, %d, %d, %d);
END;
""".formatted(
FRIEND, CONTACT, FOLLOW, SPOUSE, PARENT, CHILD, SIBLING, KNOWN, SHINE_CONF, SHINE_SEEN,
FRIEND, CONTACT, FOLLOW, SPOUSE, PARENT, CHILD, SIBLING, KNOWN, SHINE_CONF, SHINE_SEEN,
UNFRIEND, FRIEND,
UNCONTACT, CONTACT,
UNFOLLOW, FOLLOW,
UNSPOUSE, SPOUSE,
UNPARENT, PARENT,
UNCHILD, CHILD,
UNSIBLING, SIBLING,
UNKNOWN, KNOWN,
SHINE_UNCONF, SHINE_CONF,
SHINE_UNSEEN, SHINE_SEEN,
UNFRIEND, UNCONTACT, UNFOLLOW, UNSPOUSE, UNPARENT, UNCHILD, UNSIBLING, UNKNOWN, SHINE_UNCONF, SHINE_UNSEEN
));
}
private static void createMessageStatsLikeTrigger(Statement st) throws SQLException {
int LIKE = (int) DatabaseInitializer.REACTION_LIKE;
int UNLIKE = (int) DatabaseInitializer.REACTION_UNLIKE;
st.executeUpdate("""
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_like_ai
AFTER INSERT ON blocks
WHEN NEW.msg_type = 2 AND NEW.msg_sub_type IN (%d, %d)
BEGIN
-- ensure target stats row exists
INSERT OR IGNORE INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
)
SELECT
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
WHERE NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
-- apply delta by state transition (none/unlike->like = +1, like->unlike = -1)
UPDATE message_stats
SET likes_count = MAX(
0,
likes_count + (
CASE
WHEN NEW.msg_sub_type = %d
AND COALESCE((
SELECT b.msg_sub_type
FROM blocks b
WHERE b.login = NEW.login
AND b.bch_name = NEW.bch_name
AND b.msg_type = 2
AND b.to_login = NEW.to_login
AND b.to_bch_name = NEW.to_bch_name
AND b.to_block_number = NEW.to_block_number
AND b.to_block_hash = NEW.to_block_hash
AND b.block_number < NEW.block_number
ORDER BY b.block_number DESC
LIMIT 1
), -1) <> %d
THEN 1
WHEN NEW.msg_sub_type = %d
AND COALESCE((
SELECT b.msg_sub_type
FROM blocks b
WHERE b.login = NEW.login
AND b.bch_name = NEW.bch_name
AND b.msg_type = 2
AND b.to_login = NEW.to_login
AND b.to_bch_name = NEW.to_bch_name
AND b.to_block_number = NEW.to_block_number
AND b.to_block_hash = NEW.to_block_hash
AND b.block_number < NEW.block_number
ORDER BY b.block_number DESC
LIMIT 1
), -1) = %d
THEN -1
ELSE 0
END
)
)
WHERE to_login = NEW.to_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = NEW.to_block_number
AND to_block_hash = NEW.to_block_hash
AND NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
-- persist latest actor->target reaction state
INSERT OR IGNORE INTO reactions_state (
from_login, from_bch_name, reaction_type,
to_login, to_bch_name, to_block_number, to_block_hash,
last_sub_type
)
SELECT
NEW.login, NEW.bch_name, %d,
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
NEW.msg_sub_type
WHERE NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
UPDATE reactions_state
SET last_sub_type = NEW.msg_sub_type
WHERE from_login = NEW.login
AND from_bch_name = NEW.bch_name
AND reaction_type = %d
AND to_login = NEW.to_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = NEW.to_block_number
AND to_block_hash = NEW.to_block_hash
AND NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
END;
""".formatted(
LIKE, UNLIKE,
LIKE, LIKE,
UNLIKE, LIKE,
LIKE,
LIKE
));
}
private static void createMessageStatsReplyTrigger(Statement st) throws SQLException {
int REPLY = (int) DatabaseInitializer.TEXT_REPLY;
st.executeUpdate("""
CREATE TRIGGER IF NOT EXISTS trg_blocks_message_stats_reply_ai
AFTER INSERT ON blocks
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type = %d
BEGIN
INSERT OR IGNORE INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
)
SELECT
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
WHERE NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
UPDATE message_stats
SET replies_count = replies_count + 1
WHERE to_login = NEW.to_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = NEW.to_block_number
AND to_block_hash = NEW.to_block_hash
AND NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
END;
""".formatted(REPLY));
}
private static void createEditApplyTrigger(Statement st) throws SQLException {
int EDIT_POST = (int) DatabaseInitializer.TEXT_EDIT_POST;
int EDIT_REPLY = (int) DatabaseInitializer.TEXT_EDIT_REPLY;
st.executeUpdate("""
CREATE TRIGGER IF NOT EXISTS trg_blocks_edit_apply_ai
AFTER INSERT ON blocks
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type IN (%d, %d)
BEGIN
-- 1) помечаем исходный блок, что его "перекрыл" этот edit
UPDATE blocks
SET edited_by_block_number = NEW.block_number
WHERE login = NEW.login
AND bch_name = NEW.bch_name
AND block_number = NEW.to_block_number
AND NEW.to_block_number IS NOT NULL;
-- 2) создаём stats-строку если её не было
INSERT OR IGNORE INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
)
SELECT
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
WHERE NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
-- 3) +1 edit
UPDATE message_stats
SET edits_count = edits_count + 1
WHERE to_login = NEW.to_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = NEW.to_block_number
AND to_block_hash = NEW.to_block_hash
AND NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
END;
""".formatted(EDIT_POST, EDIT_REPLY));
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/MsgSubType.java
+126
View File
@@ -0,0 +1,126 @@
package shine.db;
/**
* MsgSubType — единое место для ВСЕХ subType сообщений (msg_sub_type).
*
* ВАЖНО:
* - Значения должны совпадать с body-классами (TextBody/ReactionBody/ConnectionBody/UserParamBody/HeaderBody).
* - После релиза менять числа нельзя (иначе ломается совместимость данных).
*/
public final class MsgSubType {
private MsgSubType() {}
/* ===================== HEADER (msg_type=0) ===================== */
/** HeaderBody: subType всегда 0 (compat). */
public static final short HEADER_COMPAT = 0;
/* ===================== TEXT (msg_type=1) ===================== */
/** POST — обычный пост в канале (в линии канала). */
public static final short TEXT_POST = 10;
/** EDIT_POST — редактирование исходного поста. */
public static final short TEXT_EDIT_POST = 11;
/** REPLY — ответ на сообщение. */
public static final short TEXT_REPLY = 20;
/** EDIT_REPLY — редактирование исходного ответа. */
public static final short TEXT_EDIT_REPLY = 21;
/** REPOST — репост сообщения в линии канала (с комментарием и target на оригинал). */
public static final short TEXT_REPOST = 30;
/* ===================== REACTION (msg_type=2) ===================== */
/** Лайк (LIKE). */
public static final short REACTION_LIKE = 1;
/** Снятие лайка (UNLIKE). */
public static final short REACTION_UNLIKE = 2;
/* ===================== CONNECTION (msg_type=3) ===================== */
/**
* Совпадает с ConnectionBody:
* SET: CLOSE_FRIEND(=FRIEND)=10, CONTACT=20, FOLLOW=30, SPOUSE=40, PARENT=50, CHILD=52, SIBLING=54,
* KNOWN_PERSON=60, SHINE_CONFIRMED=70, SHINE_SEEN=74
* UNSET: UNCLOSE_FRIEND(=UNFRIEND)=11, UNCONTACT=21, UNFOLLOW=31, UNSPOUSE=41, UNPARENT=51, UNCHILD=53, UNSIBLING=55,
* UNKNOWN_PERSON=61, SHINE_UNCONFIRMED=71, SHINE_UNSEEN=75
*/
/** Добавить в близкие друзья (close friend). */
public static final short CONNECTION_FRIEND = 10;
/** Удалить из близких друзей (close friend). */
public static final short CONNECTION_UNFRIEND = 11;
/** Alias: добавить в close friend (то же значение, что CONNECTION_FRIEND). */
public static final short CONNECTION_CLOSE_FRIEND = CONNECTION_FRIEND;
/** Alias: удалить из close friend (то же значение, что CONNECTION_UNFRIEND). */
public static final short CONNECTION_UNCLOSE_FRIEND = CONNECTION_UNFRIEND;
/** Добавить в контакты. */
public static final short CONNECTION_CONTACT = 20;
/** Удалить из контактов. */
public static final short CONNECTION_UNCONTACT = 21;
/** Подписаться (follow). */
public static final short CONNECTION_FOLLOW = 30;
/** Отписаться (unfollow). */
public static final short CONNECTION_UNFOLLOW = 31;
/** Добавить связь "жена/муж". */
public static final short CONNECTION_SPOUSE = 40;
/** Удалить связь "жена/муж". */
public static final short CONNECTION_UNSPOUSE = 41;
/** Добавить связь "родитель". */
public static final short CONNECTION_PARENT = 50;
/** Удалить связь "родитель". */
public static final short CONNECTION_UNPARENT = 51;
/** Добавить связь "ребёнок". */
public static final short CONNECTION_CHILD = 52;
/** Удалить связь "ребёнок". */
public static final short CONNECTION_UNCHILD = 53;
/** Добавить связь "брат/сестра". */
public static final short CONNECTION_SIBLING = 54;
/** Удалить связь "брат/сестра". */
public static final short CONNECTION_UNSIBLING = 55;
/** Просто знаю этого человека. */
public static final short CONNECTION_KNOWN_PERSON = 60;
/** Не знаю этого человека. */
public static final short CONNECTION_UNKNOWN_PERSON = 61;
/** Точно уверен, что сияющий. */
public static final short CONNECTION_SHINE_CONFIRMED = 70;
/** Не подтверждаю, что сияющий. */
public static final short CONNECTION_SHINE_UNCONFIRMED = 71;
/** Мало знаком, но видел сияющим. */
public static final short CONNECTION_SHINE_SEEN = 74;
/** Не отмечаю, что видел сияющим. */
public static final short CONNECTION_SHINE_UNSEEN = 75;
/* ===================== USER_PARAM (msg_type=4) ===================== */
/** Параметр профиля key/value (обе строки). */
public static final short USER_PARAM_TEXT_TEXT = 1;
/* ===================== РЕЗЕРВ НА БУДУЩЕЕ ===================== */
// Если позже захочешь BLOCK/UNBLOCK — лучше добавить новые значения,
// не трогая уже занятые коды.
}
SHiNE-server/shine-server-db/src/main/java/shine/db/SqliteDbController.java
+506
View File
@@ -0,0 +1,506 @@
package shine.db;
import utils.config.AppConfig;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public final class SqliteDbController {
private static volatile SqliteDbController instance;
private static final int LATEST_SCHEMA_VERSION = 3;
private final String jdbcUrl;
private SqliteDbController() {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
throw new RuntimeException("SQLite JDBC driver not found", e);
}
String dbPath = AppConfig.getInstance().getParam("db.path");
if (dbPath == null || dbPath.isBlank()) {
throw new RuntimeException("Config param 'db.path' is not set in application.properties");
}
Path dbFile = Paths.get(dbPath);
if (!Files.exists(dbFile)) {
System.out.println("[DB] Файл БД не найден: " + dbFile.toAbsolutePath());
System.out.println("[DB] Создаём новую БД с помощью DatabaseInitializer...");
DatabaseInitializer.createNewDB(new String[0]);
}
this.jdbcUrl = "jdbc:sqlite:" + dbPath;
ensureSchemaMigrations();
}
public static SqliteDbController getInstance() {
if (instance == null) {
synchronized (SqliteDbController.class) {
if (instance == null) {
instance = new SqliteDbController();
}
}
}
return instance;
}
public Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(jdbcUrl);
conn.setAutoCommit(true);
try (Statement st = conn.createStatement()) {
st.execute("PRAGMA foreign_keys = ON");
st.execute("PRAGMA journal_mode = WAL");
st.execute("PRAGMA synchronous = NORMAL");
st.execute("PRAGMA busy_timeout = 5000");
}
return conn;
}
public void close() {
// no-op
}
private void ensureSchemaMigrations() {
int currentVersion = getCurrentSchemaVersion();
while (currentVersion < LATEST_SCHEMA_VERSION) {
int nextVersion = currentVersion + 1;
applyMigration(nextVersion);
currentVersion = nextVersion;
}
}
private void applyMigration(int targetVersion) {
switch (targetVersion) {
case 1 -> migrateToV1();
case 2 -> migrateToV2();
case 3 -> migrateToV3();
default -> throw new RuntimeException("Unknown DB migration target version: " + targetVersion);
}
}
private void migrateToV1() {
try {
DatabaseInitializer.ensureSchemaV1Structure(jdbcUrl);
} catch (SQLException e) {
throw new RuntimeException("DB migration to v1 failed (base schema)", e);
}
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
st.execute("PRAGMA foreign_keys = OFF");
if (tableExists(c, "connections_state") && needsConnectionsStateUpgrade(c)) {
rebuildConnectionsStateTable(st);
}
ensureChannelNamesDescriptionColumn(c, st);
ensureChannelNamesTypeColumns(c, st);
ensureSignedMessageReceiptUniq(c, st);
DatabaseTriggersInstaller.createAllTriggers(st);
setSchemaVersion(c, 1);
st.execute("PRAGMA foreign_keys = ON");
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v1 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v1 failed", e);
}
}
private void migrateToV2() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
st.execute("PRAGMA foreign_keys = OFF");
st.executeUpdate("DROP TABLE IF EXISTS message_views_state");
st.executeUpdate("DROP INDEX IF EXISTS idx_message_views_state_target");
st.executeUpdate("DROP INDEX IF EXISTS idx_message_views_state_viewer_channel");
setSchemaVersion(c, 2);
st.execute("PRAGMA foreign_keys = ON");
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v2 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v2 failed", e);
}
}
private void migrateToV3() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureChat200StateTables(st);
setSchemaVersion(c, 3);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v3 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v3 failed", e);
}
}
private static void ensureChat200StateTables(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS chat200_state (
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL,
channel_root_block_hash BLOB NOT NULL,
channel_name TEXT NOT NULL,
channel_type_version INTEGER NOT NULL,
chat_title TEXT NOT NULL DEFAULT '',
updated_at_ms INTEGER NOT NULL,
PRIMARY KEY (owner_bch_name, channel_root_block_number)
);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS chat200_members_state (
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL,
member_login TEXT NOT NULL,
member_channel_name TEXT NOT NULL,
is_active INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
updated_by_block_number INTEGER NOT NULL,
PRIMARY KEY (
owner_bch_name,
channel_root_block_number,
member_login,
member_channel_name
)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_chat200_state_owner
ON chat200_state (owner_login, owner_bch_name);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_chat200_members_owner
ON chat200_members_state (owner_bch_name, channel_root_block_number, is_active);
""");
}
private int getCurrentSchemaVersion() {
try (Connection c = DriverManager.getConnection(jdbcUrl)) {
if (!tableExists(c, DatabaseInitializer.DB_SCHEMA_VERSION_TABLE)) {
return 0;
}
try (var ps = c.prepareStatement("""
SELECT schema_version
FROM db_schema_version
WHERE id = 1
LIMIT 1
""");
ResultSet rs = ps.executeQuery()) {
if (rs.next()) {
return rs.getInt(1);
}
}
return 0;
} catch (SQLException e) {
throw new RuntimeException("Cannot read DB schema version", e);
}
}
private static void setSchemaVersion(Connection c, int version) throws SQLException {
try (var ps = c.prepareStatement("""
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, ?, CAST(strftime('%s','now') AS INTEGER) * 1000)
ON CONFLICT(id) DO UPDATE SET
schema_version = excluded.schema_version,
updated_at_ms = excluded.updated_at_ms
""")) {
ps.setInt(1, version);
ps.executeUpdate();
}
}
private static void ensureReactionsStateTable(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS reactions_state (
from_login TEXT NOT NULL,
from_bch_name TEXT NOT NULL,
reaction_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
last_sub_type INTEGER NOT NULL,
UNIQUE (
from_login,
from_bch_name,
reaction_type,
to_login,
to_bch_name,
to_block_number,
to_block_hash
)
);
""");
}
private static void createConnectionsStateTable(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS connections_state (
login TEXT NOT NULL,
rel_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login),
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
);
""");
}
private static void ensureConnectionsIndexes(Statement st) throws SQLException {
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_login
ON connections_state (login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_to_login
ON connections_state (to_login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_pair
ON connections_state (login, to_login);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_connections_state_target
ON connections_state (login, rel_type, to_bch_name, to_block_number);
""");
}
private static void ensureReactionsIndexes(Statement st) throws SQLException {
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_reactions_state_target
ON reactions_state (to_bch_name, to_block_number, to_block_hash);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_reactions_state_actor
ON reactions_state (from_login, from_bch_name, reaction_type);
""");
}
private static void ensureChannelNamesStateTable(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS channel_names_state (
slug TEXT NOT NULL,
display_name TEXT NOT NULL,
channel_description TEXT NOT NULL DEFAULT '',
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
channel_type_code INTEGER NOT NULL DEFAULT 1,
channel_type_version INTEGER NOT NULL DEFAULT 1,
channel_root_block_number INTEGER NOT NULL,
channel_root_block_hash BLOB NOT NULL,
created_at_ms INTEGER NOT NULL
);
""");
}
private static void ensureChannelNamesDescriptionColumn(Connection c, Statement st) throws SQLException {
boolean hasDescription = false;
try (Statement probe = c.createStatement();
ResultSet rs = probe.executeQuery("PRAGMA table_info(channel_names_state)")) {
while (rs.next()) {
String name = rs.getString("name");
if ("channel_description".equalsIgnoreCase(name)) {
hasDescription = true;
break;
}
}
}
if (!hasDescription) {
st.executeUpdate("ALTER TABLE channel_names_state ADD COLUMN channel_description TEXT NOT NULL DEFAULT ''");
}
}
private static void ensureChannelNamesTypeColumns(Connection c, Statement st) throws SQLException {
boolean hasTypeCode = false;
boolean hasTypeVersion = false;
try (Statement probe = c.createStatement();
ResultSet rs = probe.executeQuery("PRAGMA table_info(channel_names_state)")) {
while (rs.next()) {
String name = rs.getString("name");
if ("channel_type_code".equalsIgnoreCase(name)) hasTypeCode = true;
if ("channel_type_version".equalsIgnoreCase(name)) hasTypeVersion = true;
}
}
if (!hasTypeCode) {
st.executeUpdate("ALTER TABLE channel_names_state ADD COLUMN channel_type_code INTEGER NOT NULL DEFAULT 1");
}
if (!hasTypeVersion) {
st.executeUpdate("ALTER TABLE channel_names_state ADD COLUMN channel_type_version INTEGER NOT NULL DEFAULT 1");
}
}
private static void ensureChannelNamesIndexes(Statement st) throws SQLException {
st.executeUpdate("DROP INDEX IF EXISTS uq_channel_names_state_slug");
st.executeUpdate("DROP INDEX IF EXISTS uq_channel_names_state_owner_slug");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_owner_type_slug
ON channel_names_state (owner_bch_name, channel_type_code, slug);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_target
ON channel_names_state (owner_bch_name, channel_root_block_number, channel_root_block_hash);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_channel_names_state_owner
ON channel_names_state (owner_login, owner_bch_name);
""");
}
private static void ensureSignedMessageReceiptUniq(Connection c, Statement st) throws SQLException {
if (!tableExists(c, "signed_messages_v2")) return;
if (tableExists(c, "signed_message_session_delivery")) {
st.executeUpdate("""
DELETE FROM signed_message_session_delivery
WHERE message_key IN (
SELECT message_key
FROM signed_messages_v2
WHERE message_type IN (3, 4)
AND receipt_ref_base_key IS NOT NULL
AND rowid NOT IN (
SELECT MIN(rowid)
FROM signed_messages_v2
WHERE message_type IN (3, 4)
AND receipt_ref_base_key IS NOT NULL
GROUP BY target_login, message_type, receipt_ref_base_key
)
);
""");
}
st.executeUpdate("""
DELETE FROM signed_messages_v2
WHERE message_type IN (3, 4)
AND receipt_ref_base_key IS NOT NULL
AND rowid NOT IN (
SELECT MIN(rowid)
FROM signed_messages_v2
WHERE message_type IN (3, 4)
AND receipt_ref_base_key IS NOT NULL
GROUP BY target_login, message_type, receipt_ref_base_key
);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_v2_receipt_incoming
ON signed_messages_v2 (target_login, receipt_ref_base_key)
WHERE message_type = 3 AND receipt_ref_base_key IS NOT NULL;
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_v2_receipt_outgoing
ON signed_messages_v2 (target_login, receipt_ref_base_key)
WHERE message_type = 4 AND receipt_ref_base_key IS NOT NULL;
""");
}
private static void rebuildConnectionsStateTable(Statement st) throws SQLException {
st.executeUpdate("DROP TABLE IF EXISTS connections_state_v2");
st.executeUpdate("""
CREATE TABLE connections_state_v2 (
login TEXT NOT NULL,
rel_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BLOB NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login),
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
);
""");
st.executeUpdate("""
INSERT OR IGNORE INTO connections_state_v2
(login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
SELECT
login,
rel_type,
to_login,
to_bch_name,
COALESCE(to_block_number, 0),
COALESCE(to_block_hash, zeroblob(32))
FROM connections_state
WHERE login IS NOT NULL
AND to_login IS NOT NULL
AND to_bch_name IS NOT NULL;
""");
st.executeUpdate("DROP TABLE connections_state");
st.executeUpdate("ALTER TABLE connections_state_v2 RENAME TO connections_state");
}
private static boolean tableExists(Connection c, String tableName) throws SQLException {
String sql = "SELECT 1 FROM sqlite_master WHERE type='table' AND name=? LIMIT 1";
try (var ps = c.prepareStatement(sql)) {
ps.setString(1, tableName);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
private static boolean needsConnectionsStateUpgrade(Connection c) throws SQLException {
boolean toBlockNumberNotNull = false;
boolean toBlockHashNotNull = false;
try (Statement st = c.createStatement();
ResultSet rs = st.executeQuery("PRAGMA table_info(connections_state)")) {
while (rs.next()) {
String name = rs.getString("name");
int notNull = rs.getInt("notnull");
if ("to_block_number".equalsIgnoreCase(name)) {
toBlockNumberNotNull = notNull == 1;
}
if ("to_block_hash".equalsIgnoreCase(name)) {
toBlockHashNotNull = notNull == 1;
}
}
}
return !toBlockNumberNotNull || !toBlockHashNotNull;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/channels/ChannelNameRules.java
+49
View File
@@ -0,0 +1,49 @@
package shine.db.channels;
import java.util.Locale;
import java.util.regex.Pattern;
public final class ChannelNameRules {
private static final int MIN_DISPLAY_NAME_LENGTH = 3;
private static final int MAX_DISPLAY_NAME_LENGTH = 32;
private static final Pattern DISPLAY_ALLOWED_PATTERN =
Pattern.compile("^[A-Za-z0-9_-]+$");
private ChannelNameRules() {}
public static String normalizeDisplayName(String value) {
if (value == null) return "";
return value.trim();
}
public static String requireValidDisplayNameForCreate(String rawName) {
String normalized = normalizeDisplayName(rawName);
if (normalized.isEmpty()) {
throw new IllegalArgumentException("channelName is blank");
}
int length = normalized.codePointCount(0, normalized.length());
if (length < MIN_DISPLAY_NAME_LENGTH || length > MAX_DISPLAY_NAME_LENGTH) {
throw new IllegalArgumentException("channelName length must be 3..32");
}
if (!DISPLAY_ALLOWED_PATTERN.matcher(normalized).matches()) {
throw new IllegalArgumentException("channelName contains unsupported characters");
}
return normalized;
}
public static String toCanonicalSlug(String rawName) {
String normalized = normalizeDisplayName(rawName);
if (normalized.isEmpty()) {
throw new IllegalArgumentException("channelName is blank");
}
String lowered = normalized.toLowerCase(Locale.ROOT);
if (!DISPLAY_ALLOWED_PATTERN.matcher(lowered).matches()) {
throw new IllegalArgumentException("channelName contains unsupported characters");
}
return lowered;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ActiveSessionsDAO.java
+289
View File
@@ -0,0 +1,289 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.ActiveSessionEntry;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* DAO для таблицы active_sessions.
*
* Правило:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*/
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;
}
// -------------------- INSERT --------------------
public void insert(Connection c, ActiveSessionEntry session) throws SQLException {
String sql = """
INSERT INTO active_sessions (
session_id,
login,
session_key,
storage_pwd,
session_created_at_ms,
last_authirificated_at_ms,
push_endpoint,
push_p256dh_key,
push_auth_key,
client_ip,
client_info_from_client,
client_info_from_request,
user_language
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, session.getSessionId());
ps.setString(2, session.getLogin());
ps.setString(3, session.getSessionKey());
ps.setString(4, session.getStoragePwd());
ps.setLong(5, session.getSessionCreatedAtMs());
ps.setLong(6, session.getLastAuthirificatedAtMs());
ps.setString(7, session.getPushEndpoint());
ps.setString(8, session.getPushP256dhKey());
ps.setString(9, session.getPushAuthKey());
ps.setString(10, session.getClientIp());
ps.setString(11, session.getClientInfoFromClient());
ps.setString(12, session.getClientInfoFromRequest());
ps.setString(13, session.getUserLanguage());
ps.executeUpdate();
}
}
public void insert(ActiveSessionEntry session) throws SQLException {
try (Connection c = db.getConnection()) {
insert(c, session);
}
}
// -------------------- SELECT --------------------
public ActiveSessionEntry getBySessionId(Connection c, String sessionId) throws SQLException {
String sql = """
SELECT
session_id,
login,
session_key,
storage_pwd,
session_created_at_ms,
last_authirificated_at_ms,
push_endpoint,
push_p256dh_key,
push_auth_key,
client_ip,
client_info_from_client,
client_info_from_request,
user_language
FROM active_sessions
WHERE session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
public ActiveSessionEntry getBySessionId(String sessionId) throws SQLException {
try (Connection c = db.getConnection()) {
return getBySessionId(c, sessionId);
}
}
public List<ActiveSessionEntry> getByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT
session_id,
login,
session_key,
storage_pwd,
session_created_at_ms,
last_authirificated_at_ms,
push_endpoint,
push_p256dh_key,
push_auth_key,
client_ip,
client_info_from_client,
client_info_from_request,
user_language
FROM active_sessions
WHERE login = ? COLLATE NOCASE
""";
List<ActiveSessionEntry> result = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));
}
}
return result;
}
public List<ActiveSessionEntry> getByLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLogin(c, login);
}
}
// -------------------- UPDATE --------------------
public void updateLastAuthirificatedAtMs(Connection c, String sessionId, long lastAuthMs) throws SQLException {
String sql = """
UPDATE active_sessions
SET last_authirificated_at_ms = ?
WHERE session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, lastAuthMs);
ps.setString(2, sessionId);
ps.executeUpdate();
}
}
public void updateLastAuthirificatedAtMs(String sessionId, long lastAuthMs) throws SQLException {
try (Connection c = db.getConnection()) {
updateLastAuthirificatedAtMs(c, sessionId, lastAuthMs);
}
}
public void updateOnRefresh(
Connection c,
String sessionId,
long lastAuthMs,
String clientIp,
String clientInfoFromClient,
String clientInfoFromRequest,
String userLanguage
) throws SQLException {
String sql = """
UPDATE active_sessions
SET
last_authirificated_at_ms = ?,
client_ip = ?,
client_info_from_client = ?,
client_info_from_request = ?,
user_language = ?
WHERE session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, lastAuthMs);
ps.setString(2, clientIp);
ps.setString(3, clientInfoFromClient);
ps.setString(4, clientInfoFromRequest);
ps.setString(5, userLanguage);
ps.setString(6, sessionId);
ps.executeUpdate();
}
}
public void updateOnRefresh(
String sessionId,
long lastAuthMs,
String clientIp,
String clientInfoFromClient,
String clientInfoFromRequest,
String userLanguage
) throws SQLException {
try (Connection c = db.getConnection()) {
updateOnRefresh(c, sessionId, lastAuthMs, clientIp, clientInfoFromClient, clientInfoFromRequest, userLanguage);
}
}
public void updatePushSubscription(String sessionId, String endpoint, String p256dhKey, String authKey) throws SQLException {
try (Connection c = db.getConnection()) {
String sql = """
UPDATE active_sessions
SET push_endpoint = ?,
push_p256dh_key = ?,
push_auth_key = ?
WHERE session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, endpoint);
ps.setString(2, p256dhKey);
ps.setString(3, authKey);
ps.setString(4, sessionId);
ps.executeUpdate();
}
}
}
// -------------------- DELETE --------------------
public void deleteBySessionId(Connection c, String sessionId) throws SQLException {
String sql = "DELETE FROM active_sessions WHERE session_id = ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
ps.executeUpdate();
}
}
public void deleteBySessionId(String sessionId) throws SQLException {
try (Connection c = db.getConnection()) {
deleteBySessionId(c, sessionId);
}
}
// -------------------- MAPPER --------------------
private ActiveSessionEntry mapRow(ResultSet rs) throws SQLException {
String sessionId = rs.getString("session_id");
String login = rs.getString("login");
String sessionKey = rs.getString("session_key");
String storagePwd = rs.getString("storage_pwd");
long sessionCreatedAtMs = rs.getLong("session_created_at_ms");
long lastAuthirificatedAtMs = rs.getLong("last_authirificated_at_ms");
String pushEndpoint = rs.getString("push_endpoint");
String pushP256dhKey = rs.getString("push_p256dh_key");
String pushAuthKey = rs.getString("push_auth_key");
String clientIp = rs.getString("client_ip");
String clientInfoFromClient = rs.getString("client_info_from_client");
String clientInfoFromRequest = rs.getString("client_info_from_request");
String userLanguage = rs.getString("user_language");
return new ActiveSessionEntry(
sessionId,
login,
sessionKey,
storagePwd,
sessionCreatedAtMs,
lastAuthirificatedAtMs,
pushEndpoint,
pushP256dhKey,
pushAuthKey,
clientIp,
clientInfoFromClient,
clientInfoFromRequest,
userLanguage
);
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainStateDAO.java
+153
View File
@@ -0,0 +1,153 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.BlockchainStateEntry;
import java.sql.*;
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;
}
/** Получить по blockchainName без внешнего соединения. Сам открывает/закрывает. */
public BlockchainStateEntry getByBlockchainName(String blockchainName) throws SQLException {
try (Connection c = db.getConnection()) {
return getByBlockchainName(c, blockchainName);
}
}
/** Получить по blockchainName с внешним соединением. Соединение НЕ закрывает. */
public BlockchainStateEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
String sql = """
SELECT
blockchain_name,
login,
blockchain_key,
size_limit,
file_size_bytes,
last_block_number,
last_block_hash,
updated_at_ms
FROM blockchain_state
WHERE blockchain_name = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, blockchainName);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/** UPSERT без внешнего соединения. Сам открывает/закрывает. */
public void upsert(BlockchainStateEntry e) throws SQLException {
try (Connection c = db.getConnection()) {
upsert(c, e);
}
}
/** UPSERT с внешним соединением. Соединение НЕ закрывает. */
public void upsert(Connection c, BlockchainStateEntry e) throws SQLException {
String sql = """
INSERT INTO blockchain_state (
blockchain_name,
login,
blockchain_key,
size_limit,
file_size_bytes,
last_block_number,
last_block_hash,
updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(blockchain_name)
DO UPDATE SET
login = excluded.login,
blockchain_key = excluded.blockchain_key,
size_limit = excluded.size_limit,
file_size_bytes = excluded.file_size_bytes,
last_block_number= excluded.last_block_number,
last_block_hash = excluded.last_block_hash,
updated_at_ms = excluded.updated_at_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
ps.setString(i++, e.getBlockchainName());
ps.setString(i++, nn(e.getLogin()));
ps.setString(i++, nn(e.getBlockchainKey()));
ps.setLong(i++, e.getSizeLimit());
ps.setLong(i++, e.getFileSizeBytes());
ps.setInt(i++, e.getLastBlockNumber());
setBytesNullable(ps, i++, e.getLastBlockHash());
ps.setLong(i++, e.getUpdatedAtMs());
ps.executeUpdate();
}
}
/**
* Атомарно увеличить file_size_bytes на deltaBytes, но только если НЕ превысим size_limit.
*/
public boolean tryIncreaseFileSizeWithinLimit(Connection c, String blockchainName, long deltaBytes, long nowMs) throws SQLException {
String sql = """
UPDATE blockchain_state
SET
file_size_bytes = file_size_bytes + ?,
updated_at_ms = ?
WHERE
blockchain_name = ?
AND (file_size_bytes + ?) <= size_limit
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, deltaBytes);
ps.setLong(2, nowMs);
ps.setString(3, blockchainName);
ps.setLong(4, deltaBytes);
return ps.executeUpdate() > 0;
}
}
private BlockchainStateEntry mapRow(ResultSet rs) throws SQLException {
BlockchainStateEntry e = new BlockchainStateEntry();
e.setBlockchainName(rs.getString("blockchain_name"));
e.setLogin(rs.getString("login"));
e.setBlockchainKey(rs.getString("blockchain_key"));
e.setSizeLimit(rs.getLong("size_limit"));
e.setFileSizeBytes(rs.getLong("file_size_bytes"));
e.setLastBlockNumber(rs.getInt("last_block_number"));
e.setLastBlockHash(rs.getBytes("last_block_hash")); // nullable
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
return e;
}
private static void setBytesNullable(PreparedStatement ps, int index, byte[] b) throws SQLException {
if (b != null) ps.setBytes(index, b);
else ps.setNull(index, Types.BLOB);
}
private static String nn(String s) { return s == null ? "" : s; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlocksDAO.java
+245
View File
@@ -0,0 +1,245 @@
package shine.db.dao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import shine.db.SqliteDbController;
import shine.db.entities.BlockEntry;
import java.sql.*;
/**
* DAO для таблицы blocks (новый формат).
*
* Правило:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*
* Ключ:
* - (bch_name, block_number) — уникальная пара в рамках общей БД сервера.
*/
public final class BlocksDAO {
private static volatile BlocksDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private static final Logger log = LoggerFactory.getLogger(BlocksDAO.class);
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 {
log.info("DBG BlockEntry: type={} sub={} lineCode={} prevLineNumber={} thisLineNumber={} prevLineHashLen={}",
e.getMsgType(), e.getMsgSubType(),
e.getLineCode(), e.getPrevLineNumber(), e.getThisLineNumber(),
e.getPrevLineHash() == null ? null : e.getPrevLineHash().length
);
String sql = """
INSERT INTO blocks (
login,
bch_name,
block_number,
msg_type,
msg_sub_type,
block_bytes,
to_login,
to_bch_name,
to_block_number,
to_block_hash,
block_hash,
block_signature,
edited_by_block_number,
line_code,
prev_line_number,
prev_line_hash,
this_line_number
) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
ps.setString(i++, e.getLogin());
ps.setString(i++, e.getBchName());
ps.setInt(i++, e.getBlockNumber());
ps.setInt(i++, e.getMsgType());
ps.setInt(i++, e.getMsgSubType());
ps.setBytes(i++, e.getBlockBytes());
if (e.getToLogin() != null) ps.setString(i++, e.getToLogin());
else ps.setNull(i++, Types.VARCHAR);
if (e.getToBchName() != null) ps.setString(i++, e.getToBchName());
else ps.setNull(i++, Types.VARCHAR);
if (e.getToBlockNumber() != null) ps.setInt(i++, e.getToBlockNumber());
else ps.setNull(i++, Types.INTEGER);
if (e.getToBlockHash() != null) ps.setBytes(i++, e.getToBlockHash());
else ps.setNull(i++, Types.BLOB);
ps.setBytes(i++, e.getBlockHash());
ps.setBytes(i++, e.getBlockSignature());
if (e.getEditedByBlockNumber() != null) ps.setInt(i++, e.getEditedByBlockNumber());
else ps.setNull(i++, Types.INTEGER);
// NEW: line_code
if (e.getLineCode() != null) ps.setInt(i++, e.getLineCode());
else ps.setNull(i++, Types.INTEGER);
if (e.getPrevLineNumber() != null) ps.setInt(i++, e.getPrevLineNumber());
else ps.setNull(i++, Types.INTEGER);
if (e.getPrevLineHash() != null) ps.setBytes(i++, e.getPrevLineHash());
else ps.setNull(i++, Types.BLOB);
if (e.getThisLineNumber() != null) ps.setInt(i++, e.getThisLineNumber());
else ps.setNull(i++, Types.INTEGER);
ps.executeUpdate();
}
}
/** Вставка без внешнего соединения. Сам открывает/закрывает. */
public void insert(BlockEntry e) throws SQLException {
try (Connection c = db.getConnection()) {
insert(c, e);
}
}
// -------------------- SELECT: HASH BY NUMBER --------------------
/** Получить block_hash по (bch_name, block_number). Нужен для линейной проверки. */
public byte[] getHashByNumber(Connection c, String bchName, int blockNumber) throws SQLException {
String sql = """
SELECT block_hash
FROM blocks
WHERE bch_name = ? AND block_number = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, bchName);
ps.setInt(2, blockNumber);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return rs.getBytes("block_hash");
}
}
}
public byte[] getHashByNumber(String bchName, int blockNumber) throws SQLException {
try (Connection c = db.getConnection()) {
return getHashByNumber(c, bchName, blockNumber);
}
}
// -------------------- SELECT: FULL ENTRY --------------------
public BlockEntry getByNumber(Connection c, String bchName, int blockNumber) throws SQLException {
String sql = """
SELECT
login,
bch_name,
block_number,
msg_type,
msg_sub_type,
block_bytes,
to_login,
to_bch_name,
to_block_number,
to_block_hash,
block_hash,
block_signature,
edited_by_block_number,
line_code,
prev_line_number,
prev_line_hash,
this_line_number
FROM blocks
WHERE bch_name = ? AND block_number = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, bchName);
ps.setInt(2, blockNumber);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
public BlockEntry getByNumber(String bchName, int blockNumber) throws SQLException {
try (Connection c = db.getConnection()) {
return getByNumber(c, bchName, blockNumber);
}
}
// -------------------- INTERNAL --------------------
private BlockEntry mapRow(ResultSet rs) throws SQLException {
BlockEntry e = new BlockEntry();
e.setLogin(rs.getString("login"));
e.setBchName(rs.getString("bch_name"));
e.setBlockNumber(rs.getInt("block_number"));
e.setMsgType(rs.getInt("msg_type"));
e.setMsgSubType(rs.getInt("msg_sub_type"));
e.setBlockBytes(rs.getBytes("block_bytes"));
String toLogin = rs.getString("to_login");
if (rs.wasNull()) toLogin = null;
e.setToLogin(toLogin);
String toBchName = rs.getString("to_bch_name");
if (rs.wasNull()) toBchName = null;
e.setToBchName(toBchName);
Integer toBlockNumber = (Integer) rs.getObject("to_block_number");
e.setToBlockNumber(toBlockNumber);
byte[] toHash = rs.getBytes("to_block_hash");
if (rs.wasNull()) toHash = null;
e.setToBlockHash(toHash);
e.setBlockHash(rs.getBytes("block_hash"));
e.setBlockSignature(rs.getBytes("block_signature"));
Integer editedBy = (Integer) rs.getObject("edited_by_block_number");
e.setEditedByBlockNumber(editedBy);
// NEW: line_code
Integer lineCode = (Integer) rs.getObject("line_code");
e.setLineCode(lineCode);
Integer prevLn = (Integer) rs.getObject("prev_line_number");
e.setPrevLineNumber(prevLn);
byte[] prevLh = rs.getBytes("prev_line_hash");
if (rs.wasNull()) prevLh = null;
e.setPrevLineHash(prevLh);
Integer thisLn = (Integer) rs.getObject("this_line_number");
e.setThisLineNumber(thisLn);
return e;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ChannelNameStateDAO.java
+91
View File
@@ -0,0 +1,91 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.ChannelNameStateEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
public final class ChannelNameStateDAO {
private static volatile ChannelNameStateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private ChannelNameStateDAO() {}
public static ChannelNameStateDAO getInstance() {
if (instance == null) {
synchronized (ChannelNameStateDAO.class) {
if (instance == null) instance = new ChannelNameStateDAO();
}
}
return instance;
}
public boolean existsByOwnerTypeAndSlug(Connection c, String ownerBchName, int channelTypeCode, String slug) throws SQLException {
String sql = """
SELECT 1
FROM channel_names_state
WHERE owner_bch_name = ? AND channel_type_code = ? AND slug = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ownerBchName);
ps.setInt(2, channelTypeCode);
ps.setString(3, slug);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
public boolean existsByOwnerTypeAndSlug(String ownerBchName, int channelTypeCode, String slug) throws SQLException {
try (Connection c = db.getConnection()) {
return existsByOwnerTypeAndSlug(c, ownerBchName, channelTypeCode, slug);
}
}
public void clearAll(Connection c) throws SQLException {
try (PreparedStatement ps = c.prepareStatement("DELETE FROM channel_names_state")) {
ps.executeUpdate();
}
}
public void insert(Connection c, ChannelNameStateEntry entry) throws SQLException {
String sql = """
INSERT INTO channel_names_state (
slug,
display_name,
channel_description,
owner_login,
owner_bch_name,
channel_type_code,
channel_type_version,
channel_root_block_number,
channel_root_block_hash,
created_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getSlug());
ps.setString(2, entry.getDisplayName());
ps.setString(3, entry.getChannelDescription() == null ? "" : entry.getChannelDescription());
ps.setString(4, entry.getOwnerLogin());
ps.setString(5, entry.getOwnerBlockchainName());
ps.setInt(6, entry.getChannelTypeCode());
ps.setInt(7, entry.getChannelTypeVersion());
ps.setInt(8, entry.getChannelRootBlockNumber());
ps.setBytes(9, entry.getChannelRootBlockHash());
ps.setLong(10, entry.getCreatedAtMs());
ps.executeUpdate();
}
}
public void insertAll(Connection c, List<ChannelNameStateEntry> entries) throws SQLException {
for (ChannelNameStateEntry entry : entries) {
insert(c, entry);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ConnectionsStateDAO.java
+164
View File
@@ -0,0 +1,164 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
/**
* ConnectionsStateDAO — чтение текущего состояния связей из connections_state.
*
* ВАЖНО:
* - login в запросах может быть в любом регистре, поэтому в WHERE используем COLLATE NOCASE
* - в ответах возвращаем логины в каноническом регистре через JOIN на solana_users
*
* ПРИМЕЧАНИЕ:
* Таблица пользователей тут названа "solana_users". Если у тебя иначе — поменяй в SQL.
*/
public final class ConnectionsStateDAO {
private static volatile ConnectionsStateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private ConnectionsStateDAO() {}
public static ConnectionsStateDAO getInstance() {
if (instance == null) {
synchronized (ConnectionsStateDAO.class) {
if (instance == null) instance = new ConnectionsStateDAO();
}
}
return instance;
}
/**
* Outgoing: список логинов (канонических), кому login поставил relType.
*/
public List<String> listOutgoingByRelTypeCanonical(Connection c, String loginAnyCase, int relType) throws SQLException {
String sql = """
SELECT COALESCE(u_login.login, u_bch.login, cs.to_login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_login
ON u_login.login = cs.to_login COLLATE NOCASE
LEFT JOIN solana_users u_bch
ON u_bch.blockchain_name = cs.to_bch_name COLLATE NOCASE
WHERE cs.login = ? COLLATE NOCASE
AND cs.rel_type = ?
ORDER BY friend_login
""";
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, loginAnyCase);
ps.setInt(2, relType);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String v = rs.getString("friend_login");
if (v != null) out.add(v);
}
}
}
return out;
}
/**
* Incoming: список логинов (канонических), кто поставил relType пользователю login.
*/
public List<String> listIncomingByRelTypeCanonical(Connection c, String loginAnyCase, int relType) throws SQLException {
String sql = """
SELECT COALESCE(u_actor.login, cs.login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_actor
ON u_actor.login = cs.login COLLATE NOCASE
LEFT JOIN solana_users u_target
ON u_target.login = ? COLLATE NOCASE
WHERE (
cs.to_login = ? COLLATE NOCASE
OR (u_target.blockchain_name IS NOT NULL AND cs.to_bch_name = u_target.blockchain_name COLLATE NOCASE)
)
AND cs.rel_type = ?
ORDER BY friend_login
""";
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, loginAnyCase);
ps.setString(2, loginAnyCase);
ps.setInt(3, relType);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String v = rs.getString("friend_login");
if (v != null) out.add(v);
}
}
}
return out;
}
/**
* Mutual: список логинов (канонических), у кого дружба в обе стороны.
*/
public List<String> listMutualByRelTypeCanonical(Connection c, String loginAnyCase, int relType) throws SQLException {
String sql = """
SELECT u.login AS friend_login
FROM connections_state a
JOIN solana_users u
ON u.login = a.to_login COLLATE NOCASE
WHERE a.login = ? COLLATE NOCASE
AND a.rel_type = ?
AND EXISTS (
SELECT 1
FROM connections_state b
WHERE b.login = a.to_login COLLATE NOCASE
AND b.to_login = a.login COLLATE NOCASE
AND b.rel_type = a.rel_type
)
ORDER BY u.login
""";
List<String> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, loginAnyCase);
ps.setInt(2, relType);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String v = rs.getString("friend_login");
if (v != null) out.add(v);
}
}
}
return out;
}
public void upsertRelation(Connection c,
String login,
int relType,
String toLogin,
String toBchName,
Integer toBlockNumber,
byte[] toBlockHash) throws SQLException {
String sql = """
INSERT INTO connections_state (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(login, rel_type, to_login) DO UPDATE SET
to_bch_name=excluded.to_bch_name,
to_block_number=excluded.to_block_number,
to_block_hash=excluded.to_block_hash
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setInt(2, relType);
ps.setString(3, toLogin);
ps.setString(4, toBchName);
if (toBlockNumber == null) ps.setNull(5, java.sql.Types.INTEGER); else ps.setInt(5, toBlockNumber);
ps.setBytes(6, toBlockHash);
ps.executeUpdate();
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/DirectMessagesDAO.java
+53
View File
@@ -0,0 +1,53 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.DirectMessageEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
public final class DirectMessagesDAO {
private static volatile DirectMessagesDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private DirectMessagesDAO() {}
public static DirectMessagesDAO getInstance() {
if (instance == null) {
synchronized (DirectMessagesDAO.class) {
if (instance == null) instance = new DirectMessagesDAO();
}
}
return instance;
}
public void insert(DirectMessageEntry entry) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO direct_messages (
message_id, from_login, to_login, text, created_at_ms
) VALUES (?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getMessageId());
ps.setString(2, entry.getFromLogin());
ps.setString(3, entry.getToLogin());
ps.setString(4, entry.getText());
ps.setLong(5, entry.getCreatedAtMs());
ps.executeUpdate();
}
}
}
public boolean existsFromTo(String fromLogin, String toLogin) throws Exception {
try (Connection c = db.getConnection()) {
String sql = "SELECT 1 FROM direct_messages WHERE from_login = ? AND to_login = ? LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, fromLogin);
ps.setString(2, toLogin);
return ps.executeQuery().next();
}
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/IpGeoCacheDAO.java
+117
View File
@@ -0,0 +1,117 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.IpGeoCacheEntry;
import java.sql.*;
/**
* DAO для таблицы ip_geo_cache.
*
* Таблица:
* - ip TEXT PRIMARY KEY
* - geo TEXT
* - updated_at_ms INTEGER NOT NULL
*
* Правило:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*/
public final class IpGeoCacheDAO {
private static volatile IpGeoCacheDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private IpGeoCacheDAO() { }
public static IpGeoCacheDAO getInstance() {
if (instance == null) {
synchronized (IpGeoCacheDAO.class) {
if (instance == null) instance = new IpGeoCacheDAO();
}
}
return instance;
}
// -------------------- UPSERT --------------------
/** UPSERT с внешним соединением. Соединение НЕ закрывает. */
public void upsert(Connection c, IpGeoCacheEntry entry) throws SQLException {
String sql = """
INSERT INTO ip_geo_cache (ip, geo, updated_at_ms)
VALUES (?, ?, ?)
ON CONFLICT(ip)
DO UPDATE SET
geo = excluded.geo,
updated_at_ms = excluded.updated_at_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getIp());
ps.setString(2, entry.getGeo());
ps.setLong(3, entry.getUpdatedAtMs());
ps.executeUpdate();
}
}
/** UPSERT без внешнего соединения. Сам открывает/закрывает. */
public void upsert(IpGeoCacheEntry entry) throws SQLException {
try (Connection c = db.getConnection()) {
upsert(c, entry);
}
}
// -------------------- SELECT --------------------
/** Получить по IP с внешним соединением. Соединение НЕ закрывает. */
public IpGeoCacheEntry getByIp(Connection c, String ip) throws SQLException {
String sql = """
SELECT ip, geo, updated_at_ms
FROM ip_geo_cache
WHERE ip = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, ip);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/** Получить по IP без внешнего соединения. Сам открывает/закрывает. */
public IpGeoCacheEntry getByIp(String ip) throws SQLException {
try (Connection c = db.getConnection()) {
return getByIp(c, ip);
}
}
// -------------------- DELETE --------------------
/** Удалить старые записи с внешним соединением. Соединение НЕ закрывает. */
public int deleteOlderThan(Connection c, long thresholdMs) throws SQLException {
String sql = "DELETE FROM ip_geo_cache WHERE updated_at_ms < ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, thresholdMs);
return ps.executeUpdate();
}
}
/** Удалить старые записи без внешнего соединения. Сам открывает/закрывает. */
public int deleteOlderThan(long thresholdMs) throws SQLException {
try (Connection c = db.getConnection()) {
return deleteOlderThan(c, thresholdMs);
}
}
// -------------------- MAPPER --------------------
private IpGeoCacheEntry mapRow(ResultSet rs) throws SQLException {
String ip = rs.getString("ip");
String geo = rs.getString("geo");
long updatedAtMs = rs.getLong("updated_at_ms");
return new IpGeoCacheEntry(ip, geo, updatedAtMs);
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/PushTokensDAO.java
+116
View File
@@ -0,0 +1,116 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.PushTokenEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public final class PushTokensDAO {
private static volatile PushTokensDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private PushTokensDAO() {}
public static PushTokensDAO getInstance() {
if (instance == null) {
synchronized (PushTokensDAO.class) {
if (instance == null) instance = new PushTokensDAO();
}
}
return instance;
}
public void upsert(PushTokenEntry entry) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO user_push_tokens (
token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(token_id) DO UPDATE SET
login=excluded.login,
session_id=excluded.session_id,
provider=excluded.provider,
token=excluded.token,
platform=excluded.platform,
user_agent=excluded.user_agent,
updated_at_ms=excluded.updated_at_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, entry.getTokenId());
ps.setString(2, entry.getLogin());
ps.setString(3, entry.getSessionId());
ps.setString(4, entry.getProvider());
ps.setString(5, entry.getToken());
ps.setString(6, entry.getPlatform());
ps.setString(7, entry.getUserAgent());
ps.setLong(8, entry.getUpdatedAtMs());
ps.executeUpdate();
}
}
}
public List<PushTokenEntry> listByLogin(String login) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
FROM user_push_tokens
WHERE login = ?
ORDER BY updated_at_ms DESC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
List<PushTokenEntry> out = new ArrayList<>();
while (rs.next()) {
PushTokenEntry e = new PushTokenEntry();
e.setTokenId(rs.getString("token_id"));
e.setLogin(rs.getString("login"));
e.setSessionId(rs.getString("session_id"));
e.setProvider(rs.getString("provider"));
e.setToken(rs.getString("token"));
e.setPlatform(rs.getString("platform"));
e.setUserAgent(rs.getString("user_agent"));
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
out.add(e);
}
return out;
}
}
}
}
public List<PushTokenEntry> listByLoginAndSession(String login, String sessionId) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT token_id, login, session_id, provider, token, platform, user_agent, updated_at_ms
FROM user_push_tokens
WHERE login = ? AND session_id = ?
ORDER BY updated_at_ms DESC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setString(2, sessionId);
try (ResultSet rs = ps.executeQuery()) {
List<PushTokenEntry> out = new ArrayList<>();
while (rs.next()) {
PushTokenEntry e = new PushTokenEntry();
e.setTokenId(rs.getString("token_id"));
e.setLogin(rs.getString("login"));
e.setSessionId(rs.getString("session_id"));
e.setProvider(rs.getString("provider"));
e.setToken(rs.getString("token"));
e.setPlatform(rs.getString("platform"));
e.setUserAgent(rs.getString("user_agent"));
e.setUpdatedAtMs(rs.getLong("updated_at_ms"));
out.add(e);
}
return out;
}
}
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedDirectMessagesHistoryDAO.java
+47
View File
@@ -0,0 +1,47 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.SignedDirectMessageHistoryEntry;
import java.sql.Connection;
import java.sql.PreparedStatement;
public final class SignedDirectMessagesHistoryDAO {
private static volatile SignedDirectMessagesHistoryDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private SignedDirectMessagesHistoryDAO() {}
public static SignedDirectMessagesHistoryDAO getInstance() {
if (instance == null) {
synchronized (SignedDirectMessagesHistoryDAO.class) {
if (instance == null) instance = new SignedDirectMessagesHistoryDAO();
}
}
return instance;
}
public void insert(SignedDirectMessageHistoryEntry e) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT INTO signed_direct_messages_history (
message_id, from_login, to_login, target_mode, target_session_id,
message_type, time_ms, nonce, raw_packet, created_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, e.getMessageId());
ps.setString(2, e.getFromLogin());
ps.setString(3, e.getToLogin());
ps.setInt(4, e.getTargetMode());
ps.setString(5, e.getTargetSessionId());
ps.setInt(6, e.getMessageType());
ps.setLong(7, e.getTimeMs());
ps.setLong(8, e.getNonce());
ps.setBytes(9, e.getRawPacket());
ps.setLong(10, e.getCreatedAtMs());
ps.executeUpdate();
}
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedDmReplayDAO.java
+50
View File
@@ -0,0 +1,50 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
public final class SignedDmReplayDAO {
private static volatile SignedDmReplayDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private SignedDmReplayDAO() {}
public static SignedDmReplayDAO getInstance() {
if (instance == null) {
synchronized (SignedDmReplayDAO.class) {
if (instance == null) instance = new SignedDmReplayDAO();
}
}
return instance;
}
public boolean registerUnique(String fromLogin, long timeMs, long nonce, long nowMs) throws Exception {
cleanupExpired(nowMs - 15L * 60L * 1000L);
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_direct_message_replay (
from_login, time_ms, nonce, created_at_ms
) VALUES (?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, fromLogin);
ps.setLong(2, timeMs);
ps.setLong(3, nonce);
ps.setLong(4, nowMs);
return ps.executeUpdate() > 0;
}
}
}
public void cleanupExpired(long minCreatedAtMs) throws Exception {
try (Connection c = db.getConnection()) {
String sql = "DELETE FROM signed_direct_message_replay WHERE created_at_ms < ?";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setLong(1, minCreatedAtMs);
ps.executeUpdate();
}
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedMessagesV2DAO.java
+246
View File
@@ -0,0 +1,246 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.SignedMessageV2Entry;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public final class SignedMessagesV2DAO {
private static volatile SignedMessagesV2DAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private SignedMessagesV2DAO() {}
public static SignedMessagesV2DAO getInstance() {
if (instance == null) {
synchronized (SignedMessagesV2DAO.class) {
if (instance == null) instance = new SignedMessagesV2DAO();
}
}
return instance;
}
public boolean insertIfAbsent(SignedMessageV2Entry e) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_messages_v2 (
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, raw_block, created_at_ms,
source_api, origin_session_id, receipt_ref_base_key, receipt_ref_type
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, e.getMessageKey());
ps.setString(2, e.getBaseKey());
ps.setString(3, e.getTargetLogin());
ps.setString(4, e.getFromLogin());
ps.setString(5, e.getToLogin());
ps.setLong(6, e.getTimeMs());
ps.setLong(7, e.getNonce());
ps.setInt(8, e.getMessageType());
ps.setBytes(9, e.getRawBlock());
ps.setLong(10, e.getCreatedAtMs());
ps.setString(11, e.getSourceApi());
ps.setString(12, e.getOriginSessionId());
ps.setString(13, e.getReceiptRefBaseKey());
if (e.getReceiptRefType() == null) ps.setObject(14, null);
else ps.setInt(14, e.getReceiptRefType());
return ps.executeUpdate() > 0;
}
}
}
/**
* Атомарная вставка пары блоков: либо вставляются оба, либо не вставляется ни один.
* Возвращает true только если обе записи добавлены в БД.
* Если хотя бы одна запись уже существует (или конфликтует по уникальности), возвращает false.
*/
public boolean insertPairBothOrNothing(SignedMessageV2Entry first, SignedMessageV2Entry second) throws Exception {
try (Connection c = db.getConnection()) {
boolean prevAutoCommit = c.getAutoCommit();
c.setAutoCommit(false);
try {
int insertedFirst = insertStrict(c, first);
int insertedSecond = insertStrict(c, second);
if (insertedFirst == 1 && insertedSecond == 1) {
c.commit();
return true;
}
c.rollback();
return false;
} catch (SQLException sqlEx) {
try { c.rollback(); } catch (Exception ignored) {}
if (isConstraintViolation(sqlEx)) {
return false;
}
throw sqlEx;
} finally {
c.setAutoCommit(prevAutoCommit);
}
}
}
private int insertStrict(Connection c, SignedMessageV2Entry e) throws SQLException {
String sql = """
INSERT INTO signed_messages_v2 (
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, raw_block, created_at_ms,
source_api, origin_session_id, receipt_ref_base_key, receipt_ref_type
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, e.getMessageKey());
ps.setString(2, e.getBaseKey());
ps.setString(3, e.getTargetLogin());
ps.setString(4, e.getFromLogin());
ps.setString(5, e.getToLogin());
ps.setLong(6, e.getTimeMs());
ps.setLong(7, e.getNonce());
ps.setInt(8, e.getMessageType());
ps.setBytes(9, e.getRawBlock());
ps.setLong(10, e.getCreatedAtMs());
ps.setString(11, e.getSourceApi());
ps.setString(12, e.getOriginSessionId());
ps.setString(13, e.getReceiptRefBaseKey());
if (e.getReceiptRefType() == null) ps.setObject(14, null);
else ps.setInt(14, e.getReceiptRefType());
return ps.executeUpdate();
}
}
private boolean isConstraintViolation(SQLException ex) {
String msg = String.valueOf(ex.getMessage()).toLowerCase();
return msg.contains("constraint") || msg.contains("unique") || msg.contains("primary key");
}
public SignedMessageV2Entry getByMessageKey(String messageKey) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
SELECT
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, raw_block, created_at_ms,
source_api, origin_session_id, receipt_ref_base_key, receipt_ref_type
FROM signed_messages_v2
WHERE message_key = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, messageKey);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
}
public void ensureDeliveryRow(String messageKey, String sessionId, long nowMs) throws Exception {
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, nowMs);
ps.executeUpdate();
}
}
}
public void markDelivered(String messageKey, String sessionId, long deliveredAtMs) throws Exception {
try (Connection c = db.getConnection()) {
String insertSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""";
try (PreparedStatement ps = c.prepareStatement(insertSql)) {
ps.setString(1, messageKey);
ps.setString(2, sessionId);
ps.setLong(3, deliveredAtMs);
ps.executeUpdate();
}
String updateSql = """
UPDATE signed_message_session_delivery
SET delivered = 1, delivered_at_ms = ?
WHERE message_key = ? AND session_id = ?
""";
try (PreparedStatement ps = c.prepareStatement(updateSql)) {
ps.setLong(1, deliveredAtMs);
ps.setString(2, messageKey);
ps.setString(3, sessionId);
ps.executeUpdate();
}
}
}
public List<SignedMessageV2Entry> listPendingForSession(String login, String sessionId, int limit) throws Exception {
try (Connection c = db.getConnection()) {
String fillSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
)
SELECT m.message_key, ?, 0, NULL, ?
FROM signed_messages_v2 m
WHERE m.target_login = ? COLLATE NOCASE
""";
long now = System.currentTimeMillis();
try (PreparedStatement ps = c.prepareStatement(fillSql)) {
ps.setString(1, sessionId);
ps.setLong(2, now);
ps.setString(3, login);
ps.executeUpdate();
}
String sql = """
SELECT
m.message_key, m.base_key, m.target_login, m.from_login, m.to_login,
m.time_ms, m.nonce, m.message_type, m.raw_block, m.created_at_ms,
m.source_api, m.origin_session_id, m.receipt_ref_base_key, m.receipt_ref_type
FROM signed_messages_v2 m
JOIN signed_message_session_delivery d
ON d.message_key = m.message_key
WHERE d.session_id = ? AND d.delivered = 0
ORDER BY m.time_ms ASC, m.created_at_ms ASC
LIMIT ?
""";
List<SignedMessageV2Entry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
ps.setInt(2, Math.max(1, limit));
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(mapRow(rs));
}
}
return out;
}
}
private SignedMessageV2Entry mapRow(ResultSet rs) throws Exception {
SignedMessageV2Entry e = new SignedMessageV2Entry();
e.setMessageKey(rs.getString("message_key"));
e.setBaseKey(rs.getString("base_key"));
e.setTargetLogin(rs.getString("target_login"));
e.setFromLogin(rs.getString("from_login"));
e.setToLogin(rs.getString("to_login"));
e.setTimeMs(rs.getLong("time_ms"));
e.setNonce(rs.getLong("nonce"));
e.setMessageType(rs.getInt("message_type"));
e.setRawBlock(rs.getBytes("raw_block"));
e.setCreatedAtMs(rs.getLong("created_at_ms"));
e.setSourceApi(rs.getString("source_api"));
e.setOriginSessionId(rs.getString("origin_session_id"));
e.setReceiptRefBaseKey(rs.getString("receipt_ref_base_key"));
int maybeRefType = rs.getInt("receipt_ref_type");
e.setReceiptRefType(rs.wasNull() ? null : maybeRefType);
return e;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SolanaUsersDAO.java
+226
View File
@@ -0,0 +1,226 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.SolanaUserEntry;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* SolanaUsersDAO — локальная таблица пользователей из Solana.
*
* Таблица: solana_users
*
* Колонки:
* - login TEXT PRIMARY KEY (COLLATE NOCASE)
* - blockchain_name TEXT NOT NULL
* - solana_key TEXT NOT NULL
* - blockchain_key TEXT NOT NULL
* - device_key TEXT NOT NULL
*
* Правило работы с соединениями:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*/
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;
}
// -------------------- INSERT --------------------
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
public void insert(Connection c, SolanaUserEntry user) throws SQLException {
String sql = """
INSERT INTO solana_users (
login, blockchain_name, solana_key, blockchain_key, device_key
) VALUES (?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, user.getLogin());
ps.setString(2, user.getBlockchainName());
ps.setString(3, user.getSolanaKey());
ps.setString(4, user.getBlockchainKey());
ps.setString(5, user.getDeviceKey());
ps.executeUpdate();
}
}
/** Вставка без внешнего соединения. Сам открывает/закрывает. */
public void insert(SolanaUserEntry user) throws SQLException {
try (Connection c = db.getConnection()) {
insert(c, user);
}
}
// -------------------- EXISTS --------------------
/** Проверка существования по login (case-insensitive) с внешним соединением. Соединение НЕ закрывает. */
public boolean existsByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT 1
FROM solana_users
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
/** Проверка существования по login (case-insensitive) без внешнего соединения. Сам открывает/закрывает. */
public boolean existsByLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return existsByLogin(c, login);
}
}
/** Проверка существования по blockchain_name (case-sensitive, как в БД) с внешним соединением. */
public boolean existsByBlockchainName(Connection c, String blockchainName) throws SQLException {
String sql = """
SELECT 1
FROM solana_users
WHERE blockchain_name = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, blockchainName);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
/** Проверка существования по blockchain_name без внешнего соединения. */
public boolean existsByBlockchainName(String blockchainName) throws SQLException {
try (Connection c = db.getConnection()) {
return existsByBlockchainName(c, blockchainName);
}
}
// -------------------- SELECT --------------------
/** Получить по login (case-insensitive) с внешним соединением. Соединение НЕ закрывает. */
public SolanaUserEntry getByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT
login,
blockchain_name,
solana_key,
blockchain_key,
device_key
FROM solana_users
WHERE LOWER(login) = LOWER(?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/** Получить по login (case-insensitive) без внешнего соединения. Сам открывает/закрывает. */
public SolanaUserEntry getByLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLogin(c, login);
}
}
/** Получить по blockchain_name (case-sensitive) с внешним соединением. Соединение НЕ закрывает. */
public SolanaUserEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
String sql = """
SELECT
login,
blockchain_name,
solana_key,
blockchain_key,
device_key
FROM solana_users
WHERE blockchain_name = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, blockchainName);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
/** Получить по blockchain_name без внешнего соединения. */
public SolanaUserEntry getByBlockchainName(String blockchainName) throws SQLException {
try (Connection c = db.getConnection()) {
return getByBlockchainName(c, blockchainName);
}
}
/** Поиск по префиксу с внешним соединением. Соединение НЕ закрывает. */
public List<SolanaUserEntry> searchByLoginPrefix(Connection c, String prefix) throws SQLException {
String sql = """
SELECT
login,
blockchain_name,
solana_key,
blockchain_key,
device_key
FROM solana_users
WHERE LOWER(login) LIKE ?
ORDER BY login
LIMIT 5
""";
List<SolanaUserEntry> result = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, prefix.toLowerCase() + "%");
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) result.add(mapRow(rs));
}
}
return result;
}
/** Поиск по префиксу без внешнего соединения. Сам открывает/закрывает. */
public List<SolanaUserEntry> searchByLoginPrefix(String prefix) throws SQLException {
try (Connection c = db.getConnection()) {
return searchByLoginPrefix(c, prefix);
}
}
// -------------------- MAPPER --------------------
private SolanaUserEntry mapRow(ResultSet rs) throws SQLException {
SolanaUserEntry e = new SolanaUserEntry();
e.setLogin(rs.getString("login"));
e.setBlockchainName(rs.getString("blockchain_name"));
e.setSolanaKey(rs.getString("solana_key"));
e.setBlockchainKey(rs.getString("blockchain_key"));
e.setDeviceKey(rs.getString("device_key"));
return e;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SubscriptionsDAO.java
+253
View File
@@ -0,0 +1,253 @@
package shine.db.dao;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* SubscriptionsDAO — агрегатный DAO для "каналов" (подписок).
*
* Возвращает по каждой активной подписке (FOLLOW) + "сам на себя":
* - login цели (channelLogin)
* - blockchainName цели (channelBchName)
* - count публикаций (TEXT_POST)
* - last publication: bytes оригинального блока (для timestamp)
* - last publication: bytes актуального блока (edit или orig) — для текста превью
*
* Важно:
* - это НЕ таблица => сущность результата хранится вложенным классом.
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*/
public final class SubscriptionsDAO {
private static volatile SubscriptionsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private SubscriptionsDAO() {}
public static SubscriptionsDAO getInstance() {
if (instance == null) {
synchronized (SubscriptionsDAO.class) {
if (instance == null) instance = new SubscriptionsDAO();
}
}
return instance;
}
/** Результат одной строки ("канал") для подписок. */
public static final class ChannelRow {
private final String channelLogin;
private final String channelBchName;
private final int publicationsCount;
/** Последняя публикация: global number (nullable если публикаций нет). */
private final Integer lastPublicationGlobalNumber;
/** Байты оригинальной публикации (FULL bytes блока) — для timestamp (nullable). */
private final byte[] lastPublicationBlockBytes;
/** Если публикация редактировалась: global number edit-блока (nullable). */
private final Integer lastEditGlobalNumber;
/** Байты edit-блока (FULL bytes блока) (nullable). */
private final byte[] lastEditBlockBytes;
public ChannelRow(String channelLogin,
String channelBchName,
int publicationsCount,
Integer lastPublicationGlobalNumber,
byte[] lastPublicationBlockBytes,
Integer lastEditGlobalNumber,
byte[] lastEditBlockBytes) {
this.channelLogin = channelLogin;
this.channelBchName = channelBchName;
this.publicationsCount = publicationsCount;
this.lastPublicationGlobalNumber = lastPublicationGlobalNumber;
this.lastPublicationBlockBytes = lastPublicationBlockBytes;
this.lastEditGlobalNumber = lastEditGlobalNumber;
this.lastEditBlockBytes = lastEditBlockBytes;
}
public String getChannelLogin() { return channelLogin; }
public String getChannelBchName() { return channelBchName; }
public int getPublicationsCount() { return publicationsCount; }
public Integer getLastPublicationGlobalNumber() { return lastPublicationGlobalNumber; }
public byte[] getLastPublicationBlockBytes() { return lastPublicationBlockBytes; }
public Integer getLastEditGlobalNumber() { return lastEditGlobalNumber; }
public byte[] getLastEditBlockBytes() { return lastEditBlockBytes; }
}
// В проекте msg_type=1 означает TEXT (у тебя это уже зафиксировано).
private static final int MSG_TYPE_TEXT = 1;
/**
* Получить список подписок (активные FOLLOW) + "сам на себя" и по каждой:
* - count публикаций (TEXT_POST)
* - последнюю публикацию (orig bytes) + её edit (если есть)
*
* Поведение при 0 публикаций:
* - publications_count = 0
* - last_pub_* = NULL
* - last_edit_* = NULL
*/
public List<ChannelRow> getSubscribedChannels(Connection c, String requesterLogin) throws SQLException {
String sql = """
WITH subs AS (
-- 1) FOLLOW-каналы
SELECT
cs.to_login AS channel_login,
cs.to_bch_name AS channel_bch_name
FROM connections_state cs
WHERE cs.login = ?
AND cs.rel_type = ?
UNION
-- 2) self: все блокчейны пользователя (если их несколько)
SELECT
bs.login AS channel_login,
bs.blockchain_name AS channel_bch_name
FROM blockchain_state bs
WHERE bs.login = ?
),
pub_counts AS (
SELECT
b.login AS channel_login,
b.bch_name AS channel_bch_name,
COUNT(*) AS publications_count
FROM blocks b
JOIN subs s
ON s.channel_login = b.login
AND s.channel_bch_name = b.bch_name
WHERE b.msg_type = ?
AND b.msg_sub_type IN (?, ?)
GROUP BY b.login, b.bch_name
),
last_pub AS (
SELECT
b.login AS channel_login,
b.bch_name AS channel_bch_name,
MAX(b.block_global_number) AS last_pub_global_number
FROM blocks b
JOIN subs s
ON s.channel_login = b.login
AND s.channel_bch_name = b.bch_name
WHERE b.msg_type = ?
AND b.msg_sub_type IN (?, ?)
GROUP BY b.login, b.bch_name
),
last_pub_block AS (
SELECT
b.login AS channel_login,
b.bch_name AS channel_bch_name,
b.block_global_number AS last_pub_global_number,
b.block_byte AS last_pub_block_bytes,
b.edited_by_block_global_number AS last_edit_global_number
FROM blocks b
JOIN last_pub lp
ON lp.channel_login = b.login
AND lp.channel_bch_name = b.bch_name
AND lp.last_pub_global_number = b.block_global_number
),
last_edit_block AS (
SELECT
e.login AS channel_login,
e.bch_name AS channel_bch_name,
e.block_global_number AS last_edit_global_number,
e.block_byte AS last_edit_block_bytes
FROM blocks e
JOIN last_pub_block p
ON p.channel_login = e.login
AND p.channel_bch_name = e.bch_name
AND p.last_edit_global_number = e.block_global_number
)
SELECT
s.channel_login,
s.channel_bch_name,
COALESCE(pc.publications_count, 0) AS publications_count,
p.last_pub_global_number,
p.last_pub_block_bytes,
p.last_edit_global_number,
e.last_edit_block_bytes
FROM subs s
LEFT JOIN pub_counts pc
ON pc.channel_login = s.channel_login
AND pc.channel_bch_name = s.channel_bch_name
LEFT JOIN last_pub_block p
ON p.channel_login = s.channel_login
AND p.channel_bch_name = s.channel_bch_name
LEFT JOIN last_edit_block e
ON e.channel_login = s.channel_login
AND e.channel_bch_name = s.channel_bch_name
ORDER BY s.channel_login, s.channel_bch_name
""";
List<ChannelRow> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
// FOLLOW
ps.setString(i++, requesterLogin);
ps.setInt(i++, (int) MsgSubType.CONNECTION_FOLLOW);
// self
ps.setString(i++, requesterLogin);
// pub_counts
ps.setInt(i++, MSG_TYPE_TEXT);
ps.setInt(i++, (int) MsgSubType.TEXT_POST);
ps.setInt(i++, (int) MsgSubType.TEXT_REPOST);
// last_pub
ps.setInt(i++, MSG_TYPE_TEXT);
ps.setInt(i++, (int) MsgSubType.TEXT_POST);
ps.setInt(i++, (int) MsgSubType.TEXT_REPOST);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String channelLogin = rs.getString("channel_login");
String channelBchName = rs.getString("channel_bch_name");
int publicationsCount = rs.getInt("publications_count");
Integer lastPubGn = (Integer) rs.getObject("last_pub_global_number");
byte[] lastPubBytes = rs.getBytes("last_pub_block_bytes");
Integer lastEditGn = (Integer) rs.getObject("last_edit_global_number");
byte[] lastEditBytes = rs.getBytes("last_edit_block_bytes");
out.add(new ChannelRow(
channelLogin,
channelBchName,
publicationsCount,
lastPubGn,
lastPubBytes,
lastEditGn,
lastEditBytes
));
}
}
}
return out;
}
/** Вариант без внешнего соединения. Сам открывает/закрывает. */
public List<ChannelRow> getSubscribedChannels(String requesterLogin) throws SQLException {
try (Connection c = db.getConnection()) {
return getSubscribedChannels(c, requesterLogin);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserCreateDAO.java
+128
View File
@@ -0,0 +1,128 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.SolanaUserEntry;
import java.sql.*;
/**
* UserCreateDAO — атомарное добавление пользователя:
* - solana_users (login, blockchain_name, solana_key, blockchain_key, device_key)
* - blockchain_state (blockchain_name, login, blockchain_key, size_limit, ... last_block_number=-1 ...)
*
* ВАЖНО:
* - только INSERT (без перезаписи существующих записей)
* - если login или blockchainName заняты — возвращаем false (пользователь уже есть/занято)
*/
public final class UserCreateDAO {
private static volatile UserCreateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final SolanaUsersDAO usersDao = SolanaUsersDAO.getInstance();
private UserCreateDAO() {}
public static UserCreateDAO getInstance() {
if (instance == null) {
synchronized (UserCreateDAO.class) {
if (instance == null) instance = new UserCreateDAO();
}
}
return instance;
}
/**
* @return true если добавили; false если занято (login уже есть или blockchainName уже существует).
*/
public boolean insertUserWithBlockchain(
String login,
String blockchainName,
String solanaKey,
String blockchainKey,
String deviceKey,
long sizeLimit,
long nowMs
) throws SQLException {
try (Connection c = db.getConnection()) {
boolean oldAuto = c.getAutoCommit();
c.setAutoCommit(false);
try {
// 1) solana_users
SolanaUserEntry u = new SolanaUserEntry();
u.setLogin(login);
u.setBlockchainName(blockchainName);
u.setSolanaKey(solanaKey);
u.setBlockchainKey(blockchainKey);
u.setDeviceKey(deviceKey);
usersDao.insert(c, u); // если login занят (NOCASE) или blockchainName (unique) -> constraint
// 2) blockchain_state — строго INSERT, без UPSERT (иначе можно перезаписать существующую цепочку)
insertBlockchainStateStrict(
c,
blockchainName,
login,
blockchainKey,
sizeLimit,
nowMs
);
c.commit();
return true;
} catch (SQLException e) {
c.rollback();
String msg = e.getMessage() == null ? "" : e.getMessage().toLowerCase();
if (msg.contains("constraint")) {
return false;
}
throw e;
} finally {
c.setAutoCommit(oldAuto);
}
}
}
private static void insertBlockchainStateStrict(
Connection c,
String blockchainName,
String login,
String blockchainKey,
long sizeLimit,
long nowMs
) throws SQLException {
String sql = """
INSERT INTO blockchain_state (
blockchain_name,
login,
blockchain_key,
size_limit,
file_size_bytes,
last_block_number,
last_block_hash,
updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
ps.setString(i++, blockchainName);
ps.setString(i++, login);
ps.setString(i++, blockchainKey);
ps.setLong(i++, sizeLimit);
ps.setLong(i++, 0L);
ps.setInt(i++, -1);
ps.setNull(i++, Types.BLOB); // старт: блоков ещё нет
ps.setLong(i++, nowMs);
ps.executeUpdate(); // если blockchainName занят -> constraint (PK)
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserParamsDAO.java
+162
View File
@@ -0,0 +1,162 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.entities.UserParamEntry;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* UserParamsDAO — хранение сохранённых параметров пользователя.
*
* Правило:
* - методы с Connection НЕ закрывают соединение
* - методы без Connection сами открывают и закрывают соединение
*
* ЛОГИКА time_ms:
* - БД принимает запись только если она "новее" (time_ms строго больше текущего).
* - Реализовано атомарно одним SQL: UPSERT + WHERE users_params.time_ms < excluded.time_ms
*/
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 (IF NEWER) --------------------
public int upsertIfNewer(Connection c, UserParamEntry e) throws SQLException {
String sql = """
INSERT INTO users_params (
login,
param,
time_ms,
value,
device_key,
signature
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(login, param)
DO UPDATE SET
time_ms = excluded.time_ms,
value = excluded.value,
device_key = excluded.device_key,
signature = excluded.signature
WHERE users_params.time_ms < excluded.time_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, e.getLogin());
ps.setString(2, e.getParam());
ps.setLong(3, e.getTimeMs());
ps.setString(4, e.getValue());
if (e.getDeviceKey() != null) ps.setString(5, e.getDeviceKey());
else ps.setNull(5, Types.VARCHAR);
if (e.getSignature() != null) ps.setString(6, e.getSignature());
else ps.setNull(6, Types.VARCHAR);
return ps.executeUpdate();
}
}
public int upsertIfNewer(UserParamEntry e) throws SQLException {
try (Connection c = db.getConnection()) {
return upsertIfNewer(c, e);
}
}
// -------------------- SELECT --------------------
public UserParamEntry getByLoginAndParam(Connection c, String login, String param) throws SQLException {
String sql = """
SELECT
login,
param,
time_ms,
value,
device_key,
signature
FROM users_params
WHERE login = ? COLLATE NOCASE AND param = ?
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
ps.setString(2, param);
try (ResultSet rs = ps.executeQuery()) {
if (!rs.next()) return null;
return mapRow(rs);
}
}
}
public UserParamEntry getByLoginAndParam(String login, String param) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLoginAndParam(c, login, param);
}
}
public List<UserParamEntry> getByLogin(Connection c, String login) throws SQLException {
String sql = """
SELECT
login,
param,
time_ms,
value,
device_key,
signature
FROM users_params
WHERE login = ? COLLATE NOCASE
ORDER BY time_ms DESC
""";
List<UserParamEntry> list = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) list.add(mapRow(rs));
}
}
return list;
}
public List<UserParamEntry> getByLogin(String login) throws SQLException {
try (Connection c = db.getConnection()) {
return getByLogin(c, login);
}
}
// -------------------- MAPPER --------------------
private static UserParamEntry mapRow(ResultSet rs) throws SQLException {
UserParamEntry e = new UserParamEntry();
e.setLogin(rs.getString("login"));
e.setParam(rs.getString("param"));
e.setTimeMs(rs.getLong("time_ms"));
e.setValue(rs.getString("value"));
String dk = rs.getString("device_key");
if (rs.wasNull()) dk = null;
e.setDeviceKey(dk);
String sig = rs.getString("signature");
if (rs.wasNull()) sig = null;
e.setSignature(sig);
return e;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/Справка по DAO. Правило перегруженных методов.txt
+27
View File
@@ -0,0 +1,27 @@
DAO: правило перегруженных методов (короткая справка)
1) Всегда два метода:
insert(Connection c, …) // не закрывает соединение, не коммитит
insert(…) // открывает своё соединение и сам закрывает
2) Внутри одного бизнес-метода:
открываем одно соединение conn = db.getConnection();
делаем несколько DAO-вызовов через версии с Connection
в конце вручную conn.commit();
conn закрываем только один раз в finally
3) DAO-методы с Connection:
не создают соединение
не закрывают соединение
не делают commit/rollback
4) DAO-методы без Connection:
маленькие удобные обёртки
открывают соединение в try-with-resources
внутри вызывают версию с Connection
5) Итог:
одиночные операции → вызываем короткий метод без Connection
пакетные/атомарные операции → берём одно соединение и используем только методы с Connection
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/ActiveSessionEntry.java
+95
View File
@@ -0,0 +1,95 @@
package shine.db.entities;
/**
* Модель активной сессии (таблица active_sessions).
*/
public class ActiveSessionEntry {
private String sessionId;
private String login;
/** session_key: публичный ключ сессии целиком одной строкой, например ed25519/BASE64_PUBLIC_KEY. */
private String sessionKey;
private String storagePwd;
private long sessionCreatedAtMs;
private long lastAuthirificatedAtMs;
private String pushEndpoint;
private String pushP256dhKey;
private String pushAuthKey;
private String clientIp;
private String clientInfoFromClient;
private String clientInfoFromRequest;
private String userLanguage;
public ActiveSessionEntry() { }
public ActiveSessionEntry(String sessionId,
String login,
String sessionKey,
String storagePwd,
long sessionCreatedAtMs,
long lastAuthirificatedAtMs,
String pushEndpoint,
String pushP256dhKey,
String pushAuthKey,
String clientIp,
String clientInfoFromClient,
String clientInfoFromRequest,
String userLanguage) {
this.sessionId = sessionId;
this.login = login;
this.sessionKey = sessionKey;
this.storagePwd = storagePwd;
this.sessionCreatedAtMs = sessionCreatedAtMs;
this.lastAuthirificatedAtMs = lastAuthirificatedAtMs;
this.pushEndpoint = pushEndpoint;
this.pushP256dhKey = pushP256dhKey;
this.pushAuthKey = pushAuthKey;
this.clientIp = clientIp;
this.clientInfoFromClient = clientInfoFromClient;
this.clientInfoFromRequest = clientInfoFromRequest;
this.userLanguage = userLanguage;
}
public String getSessionId() { return sessionId; }
public void setSessionId(String sessionId) { this.sessionId = sessionId; }
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getSessionKey() { return sessionKey; }
public void setSessionKey(String sessionKey) { this.sessionKey = sessionKey; }
public String getStoragePwd() { return storagePwd; }
public void setStoragePwd(String storagePwd) { this.storagePwd = storagePwd; }
public long getSessionCreatedAtMs() { return sessionCreatedAtMs; }
public void setSessionCreatedAtMs(long sessionCreatedAtMs) { this.sessionCreatedAtMs = sessionCreatedAtMs; }
public long getLastAuthirificatedAtMs() { return lastAuthirificatedAtMs; }
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; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/BlockEntry.java
+103
View File
@@ -0,0 +1,103 @@
package shine.db.entities;
/**
* Запись блока (таблица blocks) — обновлённая модель под новый формат.
*
* Храним:
* - login, bch_name (как было в проекте, чтобы не ломать общую БД)
* - block_number (глобальный номер в этой цепочке)
* - block_bytes (полный блок: preimage + signature)
* - block_hash (32 байта вычисленный SHA-256(preimage))
* - block_signature (64 байта)
*
* Опционально:
* - line_code / prev_line_number / prev_line_hash / this_line_number
*
* Плюс поля индексации:
* - msg_type / msg_sub_type
* - to_* (если есть target)
* - edited_by_block_number (для TEXT_EDIT_POST / TEXT_EDIT_REPLY)
*/
public class BlockEntry {
private String login;
private String bchName;
private int blockNumber;
private int msgType;
private int msgSubType;
private byte[] blockBytes;
private String toLogin;
private String toBchName;
private Integer toBlockNumber;
private byte[] toBlockHash;
private byte[] blockHash;
private byte[] blockSignature;
private Integer editedByBlockNumber;
// NEW:
private Integer lineCode;
private Integer prevLineNumber;
private byte[] prevLineHash;
private Integer thisLineNumber;
public BlockEntry() {}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getBchName() { return bchName; }
public void setBchName(String bchName) { this.bchName = bchName; }
public int getBlockNumber() { return blockNumber; }
public void setBlockNumber(int blockNumber) { this.blockNumber = blockNumber; }
public int getMsgType() { return msgType; }
public void setMsgType(int msgType) { this.msgType = msgType; }
public int getMsgSubType() { return msgSubType; }
public void setMsgSubType(int msgSubType) { this.msgSubType = msgSubType; }
public byte[] getBlockBytes() { return blockBytes; }
public void setBlockBytes(byte[] blockBytes) { this.blockBytes = blockBytes; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public String getToBchName() { return toBchName; }
public void setToBchName(String toBchName) { this.toBchName = toBchName; }
public Integer getToBlockNumber() { return toBlockNumber; }
public void setToBlockNumber(Integer toBlockNumber) { this.toBlockNumber = toBlockNumber; }
public byte[] getToBlockHash() { return toBlockHash; }
public void setToBlockHash(byte[] toBlockHash) { this.toBlockHash = toBlockHash; }
public byte[] getBlockHash() { return blockHash; }
public void setBlockHash(byte[] blockHash) { this.blockHash = blockHash; }
public byte[] getBlockSignature() { return blockSignature; }
public void setBlockSignature(byte[] blockSignature) { this.blockSignature = blockSignature; }
public Integer getEditedByBlockNumber() { return editedByBlockNumber; }
public void setEditedByBlockNumber(Integer editedByBlockNumber) { this.editedByBlockNumber = editedByBlockNumber; }
// NEW:
public Integer getLineCode() { return lineCode; }
public void setLineCode(Integer lineCode) { this.lineCode = lineCode; }
public Integer getPrevLineNumber() { return prevLineNumber; }
public void setPrevLineNumber(Integer prevLineNumber) { this.prevLineNumber = prevLineNumber; }
public byte[] getPrevLineHash() { return prevLineHash; }
public void setPrevLineHash(byte[] prevLineHash) { this.prevLineHash = prevLineHash; }
public Integer getThisLineNumber() { return thisLineNumber; }
public void setThisLineNumber(Integer thisLineNumber) { this.thisLineNumber = thisLineNumber; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/BlockchainStateEntry.java
+69
View File
@@ -0,0 +1,69 @@
package shine.db.entities;
import java.util.Base64;
/**
* Агрегатная сущность текущего состояния блокчейна.
*
* ВАЖНО:
* - Убраны все поля линий line0..7 (они больше не нужны).
* - Оставляем:
* last_block_number
* last_block_hash
*
* Остальные поля (login, blockchain_key, лимиты) оставлены как в проекте,
* потому что серверу они реально нужны (ключ подписи/лимит файла).
*/
public final class BlockchainStateEntry {
private String blockchainName;
private String login;
private String blockchainKey; // Base64(32)
private long sizeLimit;
private long fileSizeBytes;
private int lastBlockNumber; // было last_global_number
private byte[] lastBlockHash; // было last_global_hash (nullable)
private long updatedAtMs;
public BlockchainStateEntry() {}
public String getBlockchainName() { return blockchainName; }
public void setBlockchainName(String blockchainName) { this.blockchainName = blockchainName; }
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getBlockchainKey() { return blockchainKey; }
public void setBlockchainKey(String blockchainKey) { this.blockchainKey = blockchainKey; }
public byte[] getBlockchainKeyBytes() {
if (blockchainKey == null) return null;
String s = blockchainKey.trim();
if (s.isEmpty()) return null;
try {
byte[] b = Base64.getDecoder().decode(s);
return (b != null && b.length == 32) ? b : null;
} catch (IllegalArgumentException e) {
return null;
}
}
public long getSizeLimit() { return sizeLimit; }
public void setSizeLimit(long sizeLimit) { this.sizeLimit = sizeLimit; }
public long getFileSizeBytes() { return fileSizeBytes; }
public void setFileSizeBytes(long fileSizeBytes) { this.fileSizeBytes = fileSizeBytes; }
public int getLastBlockNumber() { return lastBlockNumber; }
public void setLastBlockNumber(int lastBlockNumber) { this.lastBlockNumber = lastBlockNumber; }
public byte[] getLastBlockHash() { return lastBlockHash; }
public void setLastBlockHash(byte[] lastBlockHash) { this.lastBlockHash = lastBlockHash; }
public long getUpdatedAtMs() { return updatedAtMs; }
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/ChannelNameStateEntry.java
+96
View File
@@ -0,0 +1,96 @@
package shine.db.entities;
import java.util.Arrays;
public class ChannelNameStateEntry {
private String slug;
private String displayName;
private String channelDescription;
private String ownerLogin;
private String ownerBlockchainName;
private int channelTypeCode;
private int channelTypeVersion;
private int channelRootBlockNumber;
private byte[] channelRootBlockHash;
private long createdAtMs;
public String getSlug() {
return slug;
}
public void setSlug(String slug) {
this.slug = slug;
}
public String getDisplayName() {
return displayName;
}
public void setDisplayName(String displayName) {
this.displayName = displayName;
}
public String getChannelDescription() {
return channelDescription;
}
public void setChannelDescription(String channelDescription) {
this.channelDescription = channelDescription;
}
public String getOwnerLogin() {
return ownerLogin;
}
public void setOwnerLogin(String ownerLogin) {
this.ownerLogin = ownerLogin;
}
public String getOwnerBlockchainName() {
return ownerBlockchainName;
}
public void setOwnerBlockchainName(String ownerBlockchainName) {
this.ownerBlockchainName = ownerBlockchainName;
}
public int getChannelTypeCode() {
return channelTypeCode;
}
public void setChannelTypeCode(int channelTypeCode) {
this.channelTypeCode = channelTypeCode;
}
public int getChannelTypeVersion() {
return channelTypeVersion;
}
public void setChannelTypeVersion(int channelTypeVersion) {
this.channelTypeVersion = channelTypeVersion;
}
public int getChannelRootBlockNumber() {
return channelRootBlockNumber;
}
public void setChannelRootBlockNumber(int channelRootBlockNumber) {
this.channelRootBlockNumber = channelRootBlockNumber;
}
public byte[] getChannelRootBlockHash() {
return channelRootBlockHash == null ? null : Arrays.copyOf(channelRootBlockHash, channelRootBlockHash.length);
}
public void setChannelRootBlockHash(byte[] channelRootBlockHash) {
this.channelRootBlockHash = channelRootBlockHash == null ? null : Arrays.copyOf(channelRootBlockHash, channelRootBlockHash.length);
}
public long getCreatedAtMs() {
return createdAtMs;
}
public void setCreatedAtMs(long createdAtMs) {
this.createdAtMs = createdAtMs;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/DirectMessageEntry.java
+24
View File
@@ -0,0 +1,24 @@
package shine.db.entities;
public class DirectMessageEntry {
private String messageId;
private String fromLogin;
private String toLogin;
private String text;
private long createdAtMs;
public String getMessageId() { return messageId; }
public void setMessageId(String messageId) { this.messageId = messageId; }
public String getFromLogin() { return fromLogin; }
public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public String getText() { return text; }
public void setText(String text) { this.text = text; }
public long getCreatedAtMs() { return createdAtMs; }
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/IpGeoCacheEntry.java
+44
View File
@@ -0,0 +1,44 @@
package shine.db.entities;
/**
* Запись в таблице ip_geo_cache.
*/
public class IpGeoCacheEntry {
private String ip;
private String geo;
private long updatedAtMs;
public IpGeoCacheEntry() {
}
public IpGeoCacheEntry(String ip, String geo, long updatedAtMs) {
this.ip = ip;
this.geo = geo;
this.updatedAtMs = updatedAtMs;
}
public String getIp() {
return ip;
}
public void setIp(String ip) {
this.ip = ip;
}
public String getGeo() {
return geo;
}
public void setGeo(String geo) {
this.geo = geo;
}
public long getUpdatedAtMs() {
return updatedAtMs;
}
public void setUpdatedAtMs(long updatedAtMs) {
this.updatedAtMs = updatedAtMs;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/PushTokenEntry.java
+36
View File
@@ -0,0 +1,36 @@
package shine.db.entities;
public class PushTokenEntry {
private String tokenId;
private String login;
private String sessionId;
private String provider;
private String token;
private String platform;
private String userAgent;
private long updatedAtMs;
public String getTokenId() { return tokenId; }
public void setTokenId(String tokenId) { this.tokenId = tokenId; }
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getSessionId() { return sessionId; }
public void setSessionId(String sessionId) { this.sessionId = sessionId; }
public String getProvider() { return provider; }
public void setProvider(String provider) { this.provider = provider; }
public String getToken() { return token; }
public void setToken(String token) { this.token = token; }
public String getPlatform() { return platform; }
public void setPlatform(String platform) { this.platform = platform; }
public String getUserAgent() { return userAgent; }
public void setUserAgent(String userAgent) { this.userAgent = userAgent; }
public long getUpdatedAtMs() { return updatedAtMs; }
public void setUpdatedAtMs(long updatedAtMs) { this.updatedAtMs = updatedAtMs; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/SignedDirectMessageHistoryEntry.java
+35
View File
@@ -0,0 +1,35 @@
package shine.db.entities;
public class SignedDirectMessageHistoryEntry {
private String messageId;
private String fromLogin;
private String toLogin;
private int targetMode;
private String targetSessionId;
private int messageType;
private long timeMs;
private long nonce;
private byte[] rawPacket;
private long createdAtMs;
public String getMessageId() { return messageId; }
public void setMessageId(String messageId) { this.messageId = messageId; }
public String getFromLogin() { return fromLogin; }
public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public int getTargetMode() { return targetMode; }
public void setTargetMode(int targetMode) { this.targetMode = targetMode; }
public String getTargetSessionId() { return targetSessionId; }
public void setTargetSessionId(String targetSessionId) { this.targetSessionId = targetSessionId; }
public int getMessageType() { return messageType; }
public void setMessageType(int messageType) { this.messageType = messageType; }
public long getTimeMs() { return timeMs; }
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
public long getNonce() { return nonce; }
public void setNonce(long nonce) { this.nonce = nonce; }
public byte[] getRawPacket() { return rawPacket; }
public void setRawPacket(byte[] rawPacket) { this.rawPacket = rawPacket; }
public long getCreatedAtMs() { return createdAtMs; }
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/SignedMessageV2Entry.java
+47
View File
@@ -0,0 +1,47 @@
package shine.db.entities;
public class SignedMessageV2Entry {
private String messageKey;
private String baseKey;
private String targetLogin;
private String fromLogin;
private String toLogin;
private long timeMs;
private long nonce;
private int messageType;
private byte[] rawBlock;
private long createdAtMs;
private String sourceApi;
private String originSessionId;
private String receiptRefBaseKey;
private Integer receiptRefType;
public String getMessageKey() { return messageKey; }
public void setMessageKey(String messageKey) { this.messageKey = messageKey; }
public String getBaseKey() { return baseKey; }
public void setBaseKey(String baseKey) { this.baseKey = baseKey; }
public String getTargetLogin() { return targetLogin; }
public void setTargetLogin(String targetLogin) { this.targetLogin = targetLogin; }
public String getFromLogin() { return fromLogin; }
public void setFromLogin(String fromLogin) { this.fromLogin = fromLogin; }
public String getToLogin() { return toLogin; }
public void setToLogin(String toLogin) { this.toLogin = toLogin; }
public long getTimeMs() { return timeMs; }
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
public long getNonce() { return nonce; }
public void setNonce(long nonce) { this.nonce = nonce; }
public int getMessageType() { return messageType; }
public void setMessageType(int messageType) { this.messageType = messageType; }
public byte[] getRawBlock() { return rawBlock; }
public void setRawBlock(byte[] rawBlock) { this.rawBlock = rawBlock; }
public long getCreatedAtMs() { return createdAtMs; }
public void setCreatedAtMs(long createdAtMs) { this.createdAtMs = createdAtMs; }
public String getSourceApi() { return sourceApi; }
public void setSourceApi(String sourceApi) { this.sourceApi = sourceApi; }
public String getOriginSessionId() { return originSessionId; }
public void setOriginSessionId(String originSessionId) { this.originSessionId = originSessionId; }
public String getReceiptRefBaseKey() { return receiptRefBaseKey; }
public void setReceiptRefBaseKey(String receiptRefBaseKey) { this.receiptRefBaseKey = receiptRefBaseKey; }
public Integer getReceiptRefType() { return receiptRefType; }
public void setReceiptRefType(Integer receiptRefType) { this.receiptRefType = receiptRefType; }
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/SolanaUserEntry.java
+84
View File
@@ -0,0 +1,84 @@
package shine.db.entities;
import java.util.Base64;
/**
* SolanaUserEntry — локальная запись пользователя из Solana.
*
* Таблица: solana_users
*
* Поля:
* - login — PRIMARY KEY (TEXT) (case-insensitive на уровне COLLATE NOCASE)
* - blockchain_name — TEXT NOT NULL
* - solana_key — TEXT NOT NULL
* - blockchain_key — TEXT NOT NULL
* - device_key — TEXT NOT NULL
*/
public class SolanaUserEntry {
private String login;
private String blockchainName;
/** Ключ пользователя Solana (публичный ключ логина) */
private String solanaKey;
/** Ключ блокчейна (публичный ключ блокчейна) */
private String blockchainKey;
/** Ключ устройства (публичный ключ устройства) */
private String deviceKey;
public SolanaUserEntry() {}
public SolanaUserEntry(String login,
String blockchainName,
String solanaKey,
String blockchainKey,
String deviceKey) {
this.login = login;
this.blockchainName = blockchainName;
this.solanaKey = solanaKey;
this.blockchainKey = blockchainKey;
this.deviceKey = deviceKey;
}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getBlockchainName() { return blockchainName; }
public void setBlockchainName(String blockchainName) { this.blockchainName = blockchainName; }
public String getSolanaKey() { return solanaKey; }
public void setSolanaKey(String solanaKey) { this.solanaKey = solanaKey; }
public String getBlockchainKey() { return blockchainKey; }
public void setBlockchainKey(String blockchainKey) { this.blockchainKey = blockchainKey; }
public String getDeviceKey() { return deviceKey; }
public void setDeviceKey(String deviceKey) { this.deviceKey = deviceKey; }
// оставляю этот метод как утилиту (иногда удобно), но он работает только для deviceKey:
public byte[] getDeviceKeyByte() {
if (deviceKey == null) return null;
String s = deviceKey.trim();
if (s.isEmpty()) return null;
try {
byte[] b = Base64.getDecoder().decode(s);
if (b != null && b.length == 32) return b;
} catch (IllegalArgumentException ignore) {}
if (s.length() == 64 && s.matches("^[0-9a-fA-F]+$")) {
byte[] out = new byte[32];
for (int i = 0; i < 32; i++) {
int hi = Character.digit(s.charAt(i * 2), 16);
int lo = Character.digit(s.charAt(i * 2 + 1), 16);
out[i] = (byte) ((hi << 4) | lo);
}
return out;
}
return null;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/entities/UserParamEntry.java
+52
View File
@@ -0,0 +1,52 @@
package shine.db.entities;
/**
* UserParamEntry — сохранённый параметр пользователя.
*
* Таблица: users_params
* - login TEXT NOT NULL
* - param TEXT NOT NULL
* - time_ms INTEGER NOT NULL
* - value TEXT NOT NULL
* - device_key TEXT NULL
* - signature TEXT NULL
*/
public class UserParamEntry {
private String login;
private String param;
private long timeMs;
private String value;
private String deviceKey;
private String signature;
public UserParamEntry() {}
public UserParamEntry(String login, String param, long timeMs, String value, String deviceKey, String signature) {
this.login = login;
this.param = param;
this.timeMs = timeMs;
this.value = value;
this.deviceKey = deviceKey;
this.signature = signature;
}
public String getLogin() { return login; }
public void setLogin(String login) { this.login = login; }
public String getParam() { return param; }
public void setParam(String param) { this.param = param; }
public long getTimeMs() { return timeMs; }
public void setTimeMs(long timeMs) { this.timeMs = timeMs; }
public String getValue() { return value; }
public void setValue(String value) { this.value = value; }
public String getDeviceKey() { return deviceKey; }
public void setDeviceKey(String deviceKey) { this.deviceKey = deviceKey; }
public String getSignature() { return signature; }
public void setSignature(String signature) { this.signature = signature; }
}