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

feat: finalize channels fixes and runtime stability

Browse Source
This commit is contained in:
DrygMira
2026-04-13 23:00:36 +03:00
parent 0c7d8fac02
commit a9c69e5947
46 changed files with 4654 additions and 356 deletions
Download Patch File Download Diff File Expand all files Collapse all files
shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+73 -5
View File
@@ -36,6 +36,7 @@ public final class DatabaseInitializer {
/* ===================== REACTION (msg_type=2) ===================== */
public static final short REACTION_LIKE = 1;
public static final short REACTION_UNLIKE = 2;
/* ===================== CONNECTION (msg_type=3) ===================== */
public static final short CONNECTION_FRIEND = 10;
@@ -273,12 +274,12 @@ public final class DatabaseInitializer {
rel_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER,
to_block_hash BLOB,
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)
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
);
""");
@@ -297,6 +298,11 @@ public final class DatabaseInitializer {
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 (
@@ -328,7 +334,69 @@ public final class DatabaseInitializer {
ON message_stats (to_login);
""");
// 9) direct_messages
// 8.1) 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 PRIMARY KEY,
display_name TEXT NOT NULL,
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,
created_at_ms INTEGER NOT NULL
);
""");
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_slug
ON channel_names_state (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);
""");
// 10) direct_messages
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS direct_messages (
message_id TEXT NOT NULL PRIMARY KEY,
@@ -351,7 +419,7 @@ public final class DatabaseInitializer {
ON direct_messages (from_login, created_at_ms);
""");
// 10) user_push_tokens
// 11) user_push_tokens
st.executeUpdate("""
CREATE TABLE IF NOT EXISTS user_push_tokens (
token_id TEXT NOT NULL PRIMARY KEY,
shine-server-db/src/main/java/shine/db/DatabaseTriggersInstaller.java
+147 -48
View File
@@ -1,52 +1,55 @@
package shine.db;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* DatabaseTriggersInstaller — устанавливает триггеры, которые поддерживают бизнес-логику БД.
* DatabaseTriggersInstaller — устанавливает триггеры, которые поддерживают бизнес-логику БД.
*
* Мы специально сделали триггеры максимально "совместимыми":
* - НЕТ динамических сообщений в RAISE(...): только фиксированные строки.
* (Некоторые SQLite-сборки / просмотрщики падают на "||" внутри RAISE.)
* - НЕТ UPSERT "ON CONFLICT DO UPDATE" — вместо него:
* Мы специально сделали триггеры максимально "совместимыми":
* - НЕТ динамических сообщений в RAISE(...): только фиксированные строки.
* (Некоторые SQLite-сборки / просмотрщики падают на "||" внутри RAISE.)
* - НЕТ UPSERT "ON CONFLICT DO UPDATE" — вместо него:
* INSERT OR IGNORE + UPDATE
* (Старые SQLite не знают UPSERT.)
* (Старые SQLite не знают UPSERT.)
*
* =============================================================================
* ОПИСАНИЕ ТРИГГЕРОВ
* ОПИСАНИЕ ТРИГГЕРОВ
* =============================================================================
*
* [1] trg_blocks_line_integrity_bi (BEFORE INSERT ON blocks)
* Контроль целостности "линий" (line_code / prev_line_number / prev_line_hash / this_line_number).
* Контроль целостности "линий" (line_code / prev_line_number / prev_line_hash / this_line_number).
*
* Зачем это нужно:
* - В каналах/ветках/действиях ты хочешь иметь "линейную" последовательность,
* где каждый следующий блок явно ссылается на предыдущий блок линии
* и подтверждает, что ссылка не подменена.
* Зачем это нужно:
* - В каналах/ветках/действиях ты хочешь иметь "линейную" последовательность,
* где каждый следующий блок явно ссылается на предыдущий блок линии
* и подтверждает, что ссылка не подменена.
*
* Когда срабатывает:
* - ТОЛЬКО если при вставке передано ХОТЯ БЫ ОДНО из line-полей.
* - Если line-поля не переданы — триггер вообще не работает (это важно).
* Когда срабатывает:
* - ТОЛЬКО если при вставке передано ХОТЯ БЫ ОДНО из line-полей.
* - Если line-поля не переданы — триггер вообще не работает (это важно).
*
* Что проверяет:
* A) line-поля допускаются только для msg_type:
* Что проверяет:
* 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
* 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:
* - первый шаг после 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
* - обычный шаг:
* 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
@@ -56,28 +59,30 @@ import java.sql.Statement;
* - 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 -> удалить соответствующее "позитивное" состояние
* Поддерживает таблицу 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_*).
* Поддерживает 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.
* Поддерживает 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
* Логика 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;");
@@ -93,6 +98,24 @@ public final class DatabaseTriggersInstaller {
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
@@ -182,7 +205,7 @@ public final class DatabaseTriggersInstaller {
WHEN NEW.msg_type = 3
BEGIN
-- FRIEND/CONTACT/FOLLOW:
-- 1) если записи нет — создаём
-- 1) если записи нет — создаём
INSERT OR IGNORE INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
)
@@ -197,7 +220,7 @@ public final class DatabaseTriggersInstaller {
AND NEW.to_login IS NOT NULL
AND NEW.to_bch_name IS NOT NULL;
-- 2) если запись есть — обновляем актуальные to_*
-- 2) если запись есть — обновляем актуальные to_*
UPDATE connections_state
SET
to_bch_name = NEW.to_bch_name,
@@ -211,7 +234,7 @@ public final class DatabaseTriggersInstaller {
AND NEW.to_bch_name IS NOT NULL;
-- UNFRIEND/UNCONTACT/UNFOLLOW:
-- удаляем соответствующее "позитивное" состояние
-- удаляем соответствующее "позитивное" состояние
DELETE FROM connections_state
WHERE login = NEW.login
AND to_login = NEW.to_login
@@ -237,13 +260,14 @@ public final class DatabaseTriggersInstaller {
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 = %d
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
@@ -256,9 +280,48 @@ public final class DatabaseTriggersInstaller {
AND NEW.to_block_number IS NOT NULL
AND NEW.to_block_hash IS NOT NULL;
-- +1 like
-- apply delta by state transition (none/unlike->like = +1, like->unlike = -1)
UPDATE message_stats
SET likes_count = likes_count + 1
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
@@ -267,8 +330,43 @@ public final class DatabaseTriggersInstaller {
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));
""".formatted(
LIKE, UNLIKE,
LIKE, LIKE,
UNLIKE, LIKE,
LIKE,
LIKE
));
}
private static void createMessageStatsReplyTrigger(Statement st) throws SQLException {
@@ -314,7 +412,7 @@ public final class DatabaseTriggersInstaller {
AFTER INSERT ON blocks
WHEN NEW.msg_type = 1 AND NEW.msg_sub_type IN (%d, %d)
BEGIN
-- 1) помечаем исходный блок, что его "перекрыл" этот edit
-- 1) помечаем исходный блок, что его "перекрыл" этот edit
UPDATE blocks
SET edited_by_block_number = NEW.block_number
WHERE login = NEW.login
@@ -322,7 +420,7 @@ public final class DatabaseTriggersInstaller {
AND block_number = NEW.to_block_number
AND NEW.to_block_number IS NOT NULL;
-- 2) создаём stats-строку если её не было
-- 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
@@ -350,3 +448,4 @@ public final class DatabaseTriggersInstaller {
""".formatted(EDIT_POST, EDIT_REPLY));
}
}
shine-server-db/src/main/java/shine/db/MsgSubType.java
+2
View File
@@ -34,6 +34,8 @@ public final class MsgSubType {
/** Лайк (LIKE). */
public static final short REACTION_LIKE = 1;
/** Снятие лайка (UNLIKE). */
public static final short REACTION_UNLIKE = 2;
/* ===================== CONNECTION (msg_type=3) ===================== */
/**
shine-server-db/src/main/java/shine/db/SqliteDbController.java
+202 -1
View File
@@ -7,6 +7,7 @@ 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;
@@ -37,6 +38,7 @@ public final class SqliteDbController {
}
this.jdbcUrl = "jdbc:sqlite:" + dbPath;
ensureSchemaUpgrades();
}
public static SqliteDbController getInstance() {
@@ -67,4 +69,203 @@ public final class SqliteDbController {
public void close() {
// no-op
}
}
private void ensureSchemaUpgrades() {
try (Connection c = DriverManager.getConnection(jdbcUrl);
Statement st = c.createStatement()) {
c.setAutoCommit(false);
try {
st.execute("PRAGMA foreign_keys = OFF");
ensureReactionsStateTable(st);
if (!tableExists(c, "connections_state")) {
createConnectionsStateTable(st);
} else if (needsConnectionsStateUpgrade(c)) {
rebuildConnectionsStateTable(st);
}
ensureChannelNamesStateTable(st);
ensureConnectionsIndexes(st);
ensureReactionsIndexes(st);
ensureChannelNamesIndexes(st);
DatabaseTriggersInstaller.createAllTriggers(st);
st.execute("PRAGMA foreign_keys = ON");
c.commit();
} catch (Exception e) {
try { c.rollback(); } catch (Exception ignored) {}
throw new RuntimeException("DB schema upgrade failed", e);
} finally {
try { c.setAutoCommit(true); } catch (Exception ignored) {}
}
} catch (SQLException e) {
throw new RuntimeException("DB schema upgrade failed", e);
}
}
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 PRIMARY KEY,
display_name TEXT NOT NULL,
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,
created_at_ms INTEGER NOT NULL
);
""");
}
private static void ensureChannelNamesIndexes(Statement st) throws SQLException {
st.executeUpdate("""
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_slug
ON channel_names_state (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 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-db/src/main/java/shine/db/channels/ChannelNameRules.java
+84
View File
@@ -0,0 +1,84 @@
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("^[\\p{IsLatin}\\p{IsCyrillic}0-9 _-]+$");
private ChannelNameRules() {}
public static String normalizeDisplayName(String value) {
if (value == null) return "";
return value.trim().replaceAll("\\s+", " ");
}
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).replace('\u0451', '\u0435');
StringBuilder slug = new StringBuilder(lowered.length());
boolean pendingSeparator = false;
for (int i = 0; i < lowered.length(); ) {
int cp = lowered.codePointAt(i);
i += Character.charCount(cp);
if (cp == ' ' || cp == '_' || cp == '-') {
pendingSeparator = slug.length() > 0;
continue;
}
if (!isLatinOrCyrillicOrDigit(cp)) {
throw new IllegalArgumentException("channelName contains unsupported characters");
}
if (pendingSeparator && slug.length() > 0) {
slug.append('-');
}
pendingSeparator = false;
slug.appendCodePoint(cp);
}
int len = slug.length();
if (len > 0 && slug.charAt(len - 1) == '-') {
slug.deleteCharAt(len - 1);
}
if (slug.length() == 0) {
throw new IllegalArgumentException("channelName canonical slug is empty");
}
return slug.toString();
}
private static boolean isLatinOrCyrillicOrDigit(int cp) {
if (Character.isDigit(cp)) return true;
Character.UnicodeScript script = Character.UnicodeScript.of(cp);
return script == Character.UnicodeScript.LATIN || script == Character.UnicodeScript.CYRILLIC;
}
}
shine-server-db/src/main/java/shine/db/dao/ChannelNameStateDAO.java
+78
View File
@@ -0,0 +1,78 @@
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 existsBySlug(Connection c, String slug) throws SQLException {
String sql = "SELECT 1 FROM channel_names_state WHERE slug = ? LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, slug);
try (ResultSet rs = ps.executeQuery()) {
return rs.next();
}
}
}
public boolean existsBySlug(String slug) throws SQLException {
try (Connection c = db.getConnection()) {
return existsBySlug(c, 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,
owner_login,
owner_bch_name,
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.getOwnerLogin());
ps.setString(4, entry.getOwnerBlockchainName());
ps.setInt(5, entry.getChannelRootBlockNumber());
ps.setBytes(6, entry.getChannelRootBlockHash());
ps.setLong(7, entry.getCreatedAtMs());
ps.executeUpdate();
}
}
public void insertAll(Connection c, List<ChannelNameStateEntry> entries) throws SQLException {
for (ChannelNameStateEntry entry : entries) {
insert(c, entry);
}
}
}
shine-server-db/src/main/java/shine/db/entities/ChannelNameStateEntry.java
+69
View File
@@ -0,0 +1,69 @@
package shine.db.entities;
import java.util.Arrays;
public class ChannelNameStateEntry {
private String slug;
private String displayName;
private String ownerLogin;
private String ownerBlockchainName;
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 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 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;
}
}