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

Сервер: удалить SQLite runtime и оставить только PostgreSQL

Browse Source
This commit is contained in:
AidarKC
2026-07-25 22:09:22 +04:00
parent 1dddb5fb3c
commit 91e7239866
13 changed files with 99 additions and 2246 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/build.gradle
-1
View File
@@ -16,7 +16,6 @@ repositories {
}
dependencies {
implementation 'org.xerial:sqlite-jdbc:3.47.0.0' // sqlite
implementation 'org.postgresql:postgresql:42.7.7'
implementation "org.slf4j:slf4j-api:2.0.16" // вызов логгера
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+1 -651
View File
@@ -1,13 +1,8 @@
package shine.db;
import utils.config.AppConfig;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.nio.file.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
@@ -17,12 +12,7 @@ import java.util.ArrayList;
import java.util.List;
/**
* DatabaseInitializer — инициализация серверной БД SHiNE.
*
* Сейчас класс умеет:
* - создавать legacy SQLite-схему;
* - автоматически поднимать PostgreSQL runtime schema v1 из ресурса
* `postgres/schema_v1.sql`, если БД пустая.
* PostgreSQL runtime schema bootstrapper for SHiNE server.
*/
public final class DatabaseInitializer {
@@ -32,21 +22,15 @@ public final class DatabaseInitializer {
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;
@@ -79,51 +63,6 @@ public final class DatabaseInitializer {
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);
}
public static void ensurePostgresSchemaInitialized(String jdbcUrl,
String user,
String password) throws SQLException {
@@ -137,599 +76,10 @@ public final class DatabaseInitializer {
if (postgresSchemaVersionTableExists(conn)) {
return;
}
runPostgresSchemaScript(conn);
}
}
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, client_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,
client_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,
session_type INTEGER NOT NULL DEFAULT 1,
client_platform TEXT NOT NULL DEFAULT '',
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);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS esp_pairing_settings (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
enabled INTEGER NOT NULL DEFAULT 0,
password_hash TEXT NOT NULL DEFAULT '',
ttl_seconds INTEGER NOT NULL DEFAULT 300,
failed_attempts INTEGER NOT NULL DEFAULT 0,
first_failed_at_ms INTEGER NOT NULL DEFAULT 0,
blocked_until_ms INTEGER NOT NULL DEFAULT 0,
updated_at_ms INTEGER NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS esp_pairing_requests (
pairing_id TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
requester_session_key TEXT NOT NULL,
requester_session_type INTEGER NOT NULL DEFAULT 1,
requester_client_platform TEXT NOT NULL DEFAULT '',
payload_type INTEGER NOT NULL,
status TEXT NOT NULL,
short_code TEXT NOT NULL,
fingerprint_b58 TEXT NOT NULL,
encrypted_payload TEXT,
reject_reason TEXT,
approved_by_session_id TEXT,
created_at_ms INTEGER NOT NULL,
expires_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
delivered_to_homeserver INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_esp_pairing_requests_login_status
ON esp_pairing_requests (login, status, expires_at_ms);
""");
// 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,
client_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);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS test_free_avatar_uploads (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
used_count INTEGER NOT NULL DEFAULT 0,
updated_at_ms INTEGER NOT NULL,
last_tx_id TEXT NOT NULL DEFAULT '',
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_test_free_avatar_uploads_updated
ON test_free_avatar_uploads (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..8)
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,
revision_time_ms INTEGER NOT NULL DEFAULT 0,
reencrypted_at_ms INTEGER NOT NULL DEFAULT 0,
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,
read_at_ms 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);
}
}
private static Connection openConnection(String jdbcUrl, String user, String password) throws SQLException {
if (user == null || user.isBlank()) {
return DriverManager.getConnection(jdbcUrl);
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseTriggersInstaller.java
-569
View File
@@ -1,569 +0,0 @@
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/DbController.java
+4 -9
View File
@@ -8,17 +8,16 @@ import java.sql.SQLException;
/**
* Нейтральная точка входа в runtime БД сервера.
*
* Сейчас это адаптер над legacy singleton `SqliteDbController`,
* но остальной код больше не должен зависеть от SQLite по имени класса.
* Runtime-сервер теперь поддерживает только PostgreSQL.
*/
public final class DbController implements DbProvider {
private static volatile DbController instance;
private final SqliteDbController delegate;
private final PostgresDbController delegate;
private DbController() {
this.delegate = SqliteDbController.getInstance();
this.delegate = PostgresDbController.getInstance();
}
public static DbController getInstance() {
@@ -37,12 +36,8 @@ public final class DbController implements DbProvider {
return delegate.getConnection();
}
public boolean isSqlite() {
return delegate.isSqlite();
}
public boolean isPostgres() {
return delegate.isPostgres();
return true;
}
@Override
SHiNE-server/shine-server-db/src/main/java/shine/db/PostgresDbController.java
+63
View File
@@ -0,0 +1,63 @@
package shine.db;
import shine.db.connection.DriverManagerDbProvider;
import utils.config.AppConfig;
import java.sql.Connection;
import java.sql.SQLException;
public final class PostgresDbController {
private static volatile PostgresDbController instance;
private final DriverManagerDbProvider delegate;
private PostgresDbController() {
AppConfig config = AppConfig.getInstance();
String jdbcUrl = trimToNull(config.getParam("db.url"));
String dbUser = trimToNull(config.getParam("db.user"));
String dbPassword = trimToNull(config.getParam("db.password"));
if (jdbcUrl == null) {
throw new IllegalStateException("Config param 'db.url' is required and must point to PostgreSQL");
}
if (!jdbcUrl.startsWith("jdbc:postgresql:")) {
throw new IllegalStateException("Only PostgreSQL runtime is supported. Unsupported db.url=" + jdbcUrl);
}
try {
DatabaseInitializer.ensurePostgresSchemaInitialized(jdbcUrl, dbUser, dbPassword);
} catch (SQLException e) {
throw new RuntimeException("PostgreSQL schema auto-init failed", e);
}
this.delegate = new DriverManagerDbProvider(jdbcUrl, dbUser, dbPassword, connection -> {
connection.setAutoCommit(true);
});
}
public static PostgresDbController getInstance() {
if (instance == null) {
synchronized (PostgresDbController.class) {
if (instance == null) {
instance = new PostgresDbController();
}
}
}
return instance;
}
public Connection getConnection() throws SQLException {
return delegate.getConnection();
}
public void close() {
// no-op
}
private static String trimToNull(String value) {
if (value == null) return null;
String trimmed = value.trim();
return trimmed.isEmpty() ? null : trimmed;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/SqliteDbController.java
-900
View File
@@ -1,900 +0,0 @@
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 = 12;
private final String jdbcUrl;
private final String dbUser;
private final String dbPassword;
private final boolean sqliteMode;
private SqliteDbController() {
AppConfig config = AppConfig.getInstance();
String configuredJdbcUrl = trimToNull(config.getParam("db.url"));
this.dbUser = trimToNull(config.getParam("db.user"));
this.dbPassword = trimToNull(config.getParam("db.password"));
if (configuredJdbcUrl != null) {
this.jdbcUrl = configuredJdbcUrl;
this.sqliteMode = configuredJdbcUrl.startsWith("jdbc:sqlite:");
} else {
String dbPath = config.getParam("db.path");
if (dbPath == null || dbPath.isBlank()) {
throw new RuntimeException("Config param 'db.path' or 'db.url' 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;
this.sqliteMode = true;
}
initializeDatabase();
}
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 = openConnection();
conn.setAutoCommit(true);
if (sqliteMode) {
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
}
public boolean isSqlite() {
return sqliteMode;
}
public boolean isPostgres() {
return !sqliteMode && jdbcUrl.startsWith("jdbc:postgresql:");
}
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();
case 4 -> migrateToV4();
case 5 -> migrateToV5();
case 6 -> migrateToV6();
case 7 -> migrateToV7();
case 8 -> migrateToV8();
case 9 -> migrateToV9();
case 10 -> migrateToV10();
case 11 -> migrateToV11();
case 12 -> migrateToV12();
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 void migrateToV4() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureActiveSessionsSessionTypeColumn(c, st);
ensureActiveSessionsClientPlatformColumn(c, st);
setSchemaVersion(c, 4);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v4 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v4 failed", e);
}
}
private void migrateToV5() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureEspPairingTables(st);
setSchemaVersion(c, 5);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v5 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v5 failed", e);
}
}
private void migrateToV6() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureSignedMessagesRevisionColumn(c, st);
setSchemaVersion(c, 6);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v6 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v6 failed", e);
}
}
private void migrateToV7() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
dropDmFileTables(st);
setSchemaVersion(c, 7);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v7 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v7 failed", e);
}
}
private void migrateToV8() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureTestFreeAvatarUploadsTable(st);
setSchemaVersion(c, 8);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v8 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v8 failed", e);
}
}
private void migrateToV9() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureSyncServersTable(st);
setSchemaVersion(c, 9);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v9 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v9 failed", e);
}
}
private void migrateToV10() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureSignedMessagesReencryptedColumn(c, st);
setSchemaVersion(c, 10);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v10 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v10 failed", e);
}
}
private void migrateToV11() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
clearLegacySignedMessagesForDmV11(c, st);
setSchemaVersion(c, 11);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v11 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v11 failed", e);
}
}
private void migrateToV12() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
ensureSignedMessagesReadAtColumn(c, st);
backfillSignedMessagesReadAt(st);
setSchemaVersion(c, 12);
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB migration to v12 failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB migration to v12 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 = openConnection()) {
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 ensureActiveSessionsSessionTypeColumn(Connection c, Statement st) throws SQLException {
if (columnExists(c, "active_sessions", "session_type")) return;
st.executeUpdate("ALTER TABLE active_sessions ADD COLUMN session_type INTEGER NOT NULL DEFAULT 1");
}
private static void ensureActiveSessionsClientPlatformColumn(Connection c, Statement st) throws SQLException {
if (columnExists(c, "active_sessions", "client_platform")) return;
st.executeUpdate("ALTER TABLE active_sessions ADD COLUMN client_platform TEXT NOT NULL DEFAULT ''");
}
private static void ensureEspPairingTables(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS esp_pairing_settings (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
enabled INTEGER NOT NULL DEFAULT 0,
password_hash TEXT NOT NULL DEFAULT '',
ttl_seconds INTEGER NOT NULL DEFAULT 300,
failed_attempts INTEGER NOT NULL DEFAULT 0,
first_failed_at_ms INTEGER NOT NULL DEFAULT 0,
blocked_until_ms INTEGER NOT NULL DEFAULT 0,
updated_at_ms INTEGER NOT NULL,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS esp_pairing_requests (
pairing_id TEXT NOT NULL PRIMARY KEY,
login TEXT NOT NULL,
requester_session_key TEXT NOT NULL,
requester_session_type INTEGER NOT NULL DEFAULT 1,
requester_client_platform TEXT NOT NULL DEFAULT '',
payload_type INTEGER NOT NULL,
status TEXT NOT NULL,
short_code TEXT NOT NULL,
fingerprint_b58 TEXT NOT NULL,
encrypted_payload TEXT,
reject_reason TEXT,
approved_by_session_id TEXT,
created_at_ms INTEGER NOT NULL,
expires_at_ms INTEGER NOT NULL,
updated_at_ms INTEGER NOT NULL,
delivered_to_homeserver INTEGER NOT NULL DEFAULT 0,
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_esp_pairing_requests_login_status
ON esp_pairing_requests (login, status, expires_at_ms);
""");
}
private static void ensureSignedMessagesRevisionColumn(Connection c, Statement st) throws SQLException {
if (!tableExists(c, "signed_messages_v2")) return;
if (!columnExists(c, "signed_messages_v2", "revision_time_ms")) {
st.executeUpdate("ALTER TABLE signed_messages_v2 ADD COLUMN revision_time_ms INTEGER NOT NULL DEFAULT 0");
}
}
private static void ensureSignedMessagesReencryptedColumn(Connection c, Statement st) throws SQLException {
if (!tableExists(c, "signed_messages_v2")) return;
if (!columnExists(c, "signed_messages_v2", "reencrypted_at_ms")) {
st.executeUpdate("ALTER TABLE signed_messages_v2 ADD COLUMN reencrypted_at_ms INTEGER NOT NULL DEFAULT 0");
}
}
private static void ensureSignedMessagesReadAtColumn(Connection c, Statement st) throws SQLException {
if (!tableExists(c, "signed_messages_v2")) return;
if (!columnExists(c, "signed_messages_v2", "read_at_ms")) {
st.executeUpdate("ALTER TABLE signed_messages_v2 ADD COLUMN read_at_ms INTEGER");
}
}
private static void backfillSignedMessagesReadAt(Statement st) throws SQLException {
st.executeUpdate("""
UPDATE signed_messages_v2 AS content
SET read_at_ms = (
SELECT MIN(receipt.time_ms)
FROM signed_messages_v2 AS receipt
WHERE receipt.message_type IN (3, 4)
AND receipt.receipt_ref_base_key = content.base_key
)
WHERE content.message_type IN (1, 2)
AND (content.read_at_ms IS NULL OR content.read_at_ms <= 0)
AND EXISTS (
SELECT 1
FROM signed_messages_v2 AS receipt
WHERE receipt.message_type IN (3, 4)
AND receipt.receipt_ref_base_key = content.base_key
);
""");
}
/**
* Временная одноразовая миграция на переходе к SHiNE_DM v1:
* старые строки signed_messages_v2 больше не гарантированно совместимы
* с новым бинарным форматом и правилами tombstone/reencrypt.
* Позже эту миграцию нужно убрать, чтобы случайно не очищать рабочие DM.
*/
private static void clearLegacySignedMessagesForDmV11(Connection c, Statement st) throws SQLException {
if (tableExists(c, "signed_message_session_delivery")) {
st.executeUpdate("DELETE FROM signed_message_session_delivery");
}
if (tableExists(c, "signed_messages_v2")) {
st.executeUpdate("DELETE FROM signed_messages_v2");
}
}
private static void dropDmFileTables(Statement st) throws SQLException {
st.executeUpdate("DROP INDEX IF EXISTS idx_dm_message_file_links_login");
st.executeUpdate("DROP INDEX IF EXISTS idx_dm_message_file_links_message");
st.executeUpdate("DROP TABLE IF EXISTS dm_message_file_links");
st.executeUpdate("DROP TABLE IF EXISTS dm_files");
}
private static boolean columnExists(Connection c, String tableName, String columnName) throws SQLException {
try (Statement probe = c.createStatement();
ResultSet rs = probe.executeQuery("PRAGMA table_info(" + tableName + ")")) {
while (rs.next()) {
if (columnName.equalsIgnoreCase(rs.getString("name"))) {
return true;
}
}
return false;
}
}
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 ensureTestFreeAvatarUploadsTable(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS test_free_avatar_uploads (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
used_count INTEGER NOT NULL DEFAULT 0,
updated_at_ms INTEGER NOT NULL,
last_tx_id TEXT NOT NULL DEFAULT '',
FOREIGN KEY (login) REFERENCES solana_users(login)
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_test_free_avatar_uploads_updated
ON test_free_avatar_uploads (updated_at_ms);
""");
}
private static void ensureSyncServersTable(Statement st) throws SQLException {
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS sync_servers (
login TEXT NOT NULL PRIMARY KEY COLLATE NOCASE,
server_address TEXT NOT NULL DEFAULT '',
updated_at_ms INTEGER NOT NULL
);
""");
st.executeUpdate("""
CREATE INDEX IF NOT EXISTS idx_sync_servers_updated
ON sync_servers (updated_at_ms);
""");
}
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;
}
private void initializeDatabase() {
if (sqliteMode) {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
throw new RuntimeException("SQLite JDBC driver not found", e);
}
ensureSchemaMigrations();
return;
}
if (jdbcUrl.startsWith("jdbc:postgresql:")) {
try {
DatabaseInitializer.ensurePostgresSchemaInitialized(jdbcUrl, dbUser, dbPassword);
} catch (SQLException e) {
throw new RuntimeException("PostgreSQL schema auto-init failed", e);
}
return;
}
throw new RuntimeException("Unsupported JDBC URL: " + jdbcUrl);
}
private Connection openConnection() throws SQLException {
if (dbUser == null) {
return DriverManager.getConnection(jdbcUrl);
}
return DriverManager.getConnection(jdbcUrl, dbUser, dbPassword == null ? "" : dbPassword);
}
private static String trimToNull(String value) {
if (value == null) return null;
String trimmed = value.trim();
return trimmed.isEmpty() ? null : trimmed;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ConnectionsStateDAO.java
+10 -12
View File
@@ -141,18 +141,16 @@ public final class ConnectionsStateDAO {
String toBchName,
Integer toBlockNumber,
byte[] toBlockHash) throws SQLException {
if (db.isPostgres()) {
try (PreparedStatement deletePs = c.prepareStatement("""
DELETE FROM connections_state
WHERE LOWER(login) = LOWER(?)
AND rel_type = ?
AND LOWER(to_login) = LOWER(?)
""")) {
deletePs.setString(1, login);
deletePs.setInt(2, relType);
deletePs.setString(3, toLogin);
deletePs.executeUpdate();
}
try (PreparedStatement deletePs = c.prepareStatement("""
DELETE FROM connections_state
WHERE LOWER(login) = LOWER(?)
AND rel_type = ?
AND LOWER(to_login) = LOWER(?)
""")) {
deletePs.setString(1, login);
deletePs.setInt(2, relType);
deletePs.setString(3, toLogin);
deletePs.executeUpdate();
}
String sql = """
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedDmReplayDAO.java
+1 -7
View File
@@ -23,17 +23,11 @@ public final class SignedDmReplayDAO {
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 = db.isPostgres()
? """
String sql = """
INSERT INTO signed_direct_message_replay (
from_login, time_ms, nonce, created_at_ms
) VALUES (?, ?, ?, ?)
ON CONFLICT DO NOTHING
"""
: """
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);
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedMessagesV2DAO.java
+9 -60
View File
@@ -12,8 +12,6 @@ import java.util.ArrayList;
import java.util.List;
public final class SignedMessagesV2DAO {
private static final int SQLITE_BUSY_MAX_RETRIES = 6;
private static final long SQLITE_BUSY_RETRY_BASE_DELAY_MS = 40L;
public enum ApplyStatus {
APPLIED,
@@ -46,7 +44,7 @@ public final class SignedMessagesV2DAO {
if (isBlockedByConversationDelete(c, e.getFromLogin(), e.getToLogin(), e.getTimeMs())) {
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
String sql = db.isPostgres() ? """
String sql = """
INSERT INTO signed_messages (
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, revision_time_ms, reencrypted_at_ms,
@@ -54,13 +52,6 @@ public final class SignedMessagesV2DAO {
receipt_ref_base_key, receipt_ref_type, read_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT DO NOTHING
""" : """
INSERT OR IGNORE INTO signed_messages_v2 (
message_key, base_key, target_login, from_login, to_login,
time_ms, nonce, message_type, revision_time_ms, reencrypted_at_ms,
raw_block, created_at_ms, source_api, origin_session_id,
receipt_ref_base_key, receipt_ref_type, read_at_ms
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
@@ -276,13 +267,11 @@ public final class SignedMessagesV2DAO {
withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String sql = """
INSERT %s INTO signed_message_session_delivery (
INSERT INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
) VALUES (?, ?, 0, NULL, ?)
""".formatted(db.isPostgres() ? "INTO" : "OR IGNORE INTO");
if (db.isPostgres()) {
sql += "\nON CONFLICT DO NOTHING";
}
ON CONFLICT DO NOTHING
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
for (String sessionId : sessionIds) {
if (sessionId == null || sessionId.isBlank()) continue;
@@ -329,7 +318,7 @@ public final class SignedMessagesV2DAO {
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String fillSql = """
INSERT %s INTO signed_message_session_delivery (
INSERT INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
)
SELECT m.message_key, ?, 0, NULL, ?
@@ -340,10 +329,8 @@ public final class SignedMessagesV2DAO {
OR (m.message_type IN (5, 6, 7, 8)
AND (LOWER(m.from_login) = LOWER(?) OR LOWER(m.to_login) = LOWER(?)))
)
""".formatted(db.isPostgres() ? "INTO" : "OR IGNORE INTO", messagesTable());
if (db.isPostgres()) {
fillSql += "\nON CONFLICT DO NOTHING";
}
ON CONFLICT DO NOTHING
""".formatted(messagesTable());
long now = System.currentTimeMillis();
try (PreparedStatement ps = c.prepareStatement(fillSql)) {
ps.setString(1, sessionId);
@@ -717,46 +704,8 @@ public final class SignedMessagesV2DAO {
return msg.contains("constraint") || msg.contains("unique") || msg.contains("primary key");
}
private boolean isBusyLock(SQLException ex) {
if (db.isPostgres()) {
return false;
}
Throwable current = ex;
while (current != null) {
String msg = String.valueOf(current.getMessage()).toLowerCase();
if (msg.contains("sqlite_busy") || msg.contains("database is locked") || msg.contains("database table is locked")) {
return true;
}
current = current.getCause();
}
return false;
}
private void sleepBeforeBusyRetry(int attempt) throws SQLException {
long delayMs = SQLITE_BUSY_RETRY_BASE_DELAY_MS * (1L << Math.min(attempt, 4));
try {
Thread.sleep(delayMs);
} catch (InterruptedException ie) {
Thread.currentThread().interrupt();
SQLException sqlEx = new SQLException("Interrupted while retrying SQLite busy lock", ie);
throw sqlEx;
}
}
private <T> T withBusyRetry(SqlWork<T> work) throws Exception {
SQLException lastBusy = null;
for (int attempt = 0; attempt < SQLITE_BUSY_MAX_RETRIES; attempt++) {
try {
return work.run();
} catch (SQLException ex) {
if (!isBusyLock(ex) || attempt >= SQLITE_BUSY_MAX_RETRIES - 1) {
throw ex;
}
lastBusy = ex;
sleepBeforeBusyRetry(attempt);
}
}
throw lastBusy == null ? new SQLException("SQLite busy retry failed") : lastBusy;
return work.run();
}
private int compareMarkers(RevisionMarker left, RevisionMarker right) {
@@ -766,7 +715,7 @@ public final class SignedMessagesV2DAO {
}
private String messagesTable() {
return db.isPostgres() ? "signed_messages" : "signed_messages_v2";
return "signed_messages";
}
private SignedMessageV2Entry mapRow(ResultSet rs) throws Exception {
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SolanaUserPdaCurrentDAO.java
-7
View File
@@ -32,19 +32,12 @@ public final class SolanaUserPdaCurrentDAO {
}
public SolanaUserPdaCurrentEntry getByBlockchainName(String blockchainName) throws SQLException {
if (!db.isPostgres()) {
return null;
}
try (Connection c = db.getConnection()) {
return getByBlockchainName(c, blockchainName);
}
}
public SolanaUserPdaCurrentEntry getByBlockchainName(Connection c, String blockchainName) throws SQLException {
if (!db.isPostgres()) {
return null;
}
String sql = """
SELECT
login,
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SolanaUsersDAO.java
+10 -28
View File
@@ -44,35 +44,16 @@ public final class SolanaUsersDAO {
/** Вставка с внешним соединением. Соединение НЕ закрывает. */
public void insert(Connection c, SolanaUserEntry user) throws SQLException {
if (db.isPostgres()) {
String sql = """
INSERT INTO solana_users_manual (
login, blockchain_name, solana_key, blockchain_key, client_key, updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (login) DO UPDATE SET
blockchain_name = EXCLUDED.blockchain_name,
solana_key = EXCLUDED.solana_key,
blockchain_key = EXCLUDED.blockchain_key,
client_key = EXCLUDED.client_key,
updated_at_ms = EXCLUDED.updated_at_ms
""";
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.getClientKey());
ps.setLong(6, System.currentTimeMillis());
ps.executeUpdate();
}
return;
}
String sql = """
INSERT INTO solana_users (
login, blockchain_name, solana_key, blockchain_key, client_key
) VALUES (?, ?, ?, ?, ?)
INSERT INTO solana_users_manual (
login, blockchain_name, solana_key, blockchain_key, client_key, updated_at_ms
) VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (login) DO UPDATE SET
blockchain_name = EXCLUDED.blockchain_name,
solana_key = EXCLUDED.solana_key,
blockchain_key = EXCLUDED.blockchain_key,
client_key = EXCLUDED.client_key,
updated_at_ms = EXCLUDED.updated_at_ms
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -81,6 +62,7 @@ public final class SolanaUsersDAO {
ps.setString(3, user.getSolanaKey());
ps.setString(4, user.getBlockchainKey());
ps.setString(5, user.getClientKey());
ps.setLong(6, System.currentTimeMillis());
ps.executeUpdate();
}
}
SHiNE-server/src/main/resources/application.properties
-1
View File
@@ -1,5 +1,4 @@
server.1port=7070
db.path=data/shine.sqlite
db.url=
db.user=
db.password=
VERSION.properties
+1 -1
View File
@@ -1,2 +1,2 @@
client.version=1.2.354
server.version=1.2.329
server.version=1.2.330