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

Сервер: начать перенос runtime БД на PostgreSQL

Browse Source
This commit is contained in:
AidarKC
2026-07-24 21:20:36 +04:00
parent f9dd245481
commit 5326ad85d8
53 changed files with 1741 additions and 297 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/build.gradle
+1
View File
@@ -17,6 +17,7 @@ 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
+149 -8
View File
@@ -4,28 +4,31 @@ 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;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
/**
* DatabaseInitializer — создание новой SQLite-БД по схеме SHiNE.
* DatabaseInitializer — инициализация серверной БД SHiNE.
*
* В этой версии:
* - создаём ТОЛЬКО таблицы/индексы
* - в конце вызываем DatabaseTriggersInstaller.createAllTriggers(st)
*
* v2 (sessions):
* - active_sessions.session_pwd удалён
* - active_sessions.session_key хранит публичный ключ сессии целиком одной строкой
* Сейчас класс умеет:
* - создавать legacy SQLite-схему;
* - автоматически поднимать PostgreSQL runtime schema v1 из ресурса
* `postgres/schema_v1.sql`, если БД пустая.
*/
public final class DatabaseInitializer {
public static final String DB_SCHEMA_VERSION_TABLE = "db_schema_version";
public static final int SCHEMA_VERSION_1 = 1;
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
private DatabaseInitializer() {}
@@ -121,6 +124,24 @@ public final class DatabaseInitializer {
createSchema(jdbcUrl, false);
}
public static void ensurePostgresSchemaInitialized(String jdbcUrl,
String user,
String password) throws SQLException {
try {
Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException("PostgreSQL JDBC driver not found", e);
}
try (Connection conn = openConnection(jdbcUrl, user, password)) {
if (postgresSchemaVersionTableExists(conn)) {
return;
}
runPostgresSchemaScript(conn);
}
}
private static void createSchema(String jdbcUrl) throws SQLException {
createSchema(jdbcUrl, true);
}
@@ -708,4 +729,124 @@ public final class DatabaseInitializer {
DatabaseTriggersInstaller.createAllTriggers(st);
}
}
private static Connection openConnection(String jdbcUrl, String user, String password) throws SQLException {
if (user == null || user.isBlank()) {
return DriverManager.getConnection(jdbcUrl);
}
return DriverManager.getConnection(jdbcUrl, user, password == null ? "" : password);
}
private static boolean postgresSchemaVersionTableExists(Connection conn) throws SQLException {
String sql = """
SELECT EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = ?
)
""";
try (var ps = conn.prepareStatement(sql)) {
ps.setString(1, DB_SCHEMA_VERSION_TABLE);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() && rs.getBoolean(1);
}
}
}
private static void runPostgresSchemaScript(Connection conn) throws SQLException {
String sqlScript = loadClasspathResource(POSTGRES_SCHEMA_RESOURCE);
List<String> statements = splitSqlStatements(sqlScript);
boolean previousAutoCommit = conn.getAutoCommit();
conn.setAutoCommit(true);
try (Statement st = conn.createStatement()) {
for (String statement : statements) {
String trimmed = statement.trim();
if (!trimmed.isEmpty()) {
st.execute(trimmed);
}
}
} finally {
conn.setAutoCommit(previousAutoCommit);
}
}
private static String loadClasspathResource(String resourcePath) {
try (InputStream in = DatabaseInitializer.class.getClassLoader().getResourceAsStream(resourcePath)) {
if (in == null) {
throw new RuntimeException("Resource not found: " + resourcePath);
}
return new String(in.readAllBytes(), StandardCharsets.UTF_8);
} catch (IOException e) {
throw new RuntimeException("Failed to read resource: " + resourcePath, e);
}
}
static List<String> splitSqlStatements(String sqlScript) {
List<String> statements = new ArrayList<>();
StringBuilder current = new StringBuilder();
boolean inSingleQuote = false;
String dollarQuoteTag = null;
for (int i = 0; i < sqlScript.length(); i++) {
char ch = sqlScript.charAt(i);
if (dollarQuoteTag == null && ch == '\'' && !isEscapedSingleQuote(sqlScript, i)) {
inSingleQuote = !inSingleQuote;
current.append(ch);
continue;
}
if (!inSingleQuote) {
String tag = readDollarQuoteTag(sqlScript, i);
if (tag != null) {
current.append(tag);
i += tag.length() - 1;
if (dollarQuoteTag == null) {
dollarQuoteTag = tag;
} else if (dollarQuoteTag.equals(tag)) {
dollarQuoteTag = null;
}
continue;
}
}
if (ch == ';' && !inSingleQuote && dollarQuoteTag == null) {
statements.add(current.toString());
current.setLength(0);
continue;
}
current.append(ch);
}
if (!current.isEmpty()) {
statements.add(current.toString());
}
return statements;
}
private static boolean isEscapedSingleQuote(String sqlScript, int index) {
return index + 1 < sqlScript.length() && sqlScript.charAt(index + 1) == '\'';
}
private static String readDollarQuoteTag(String sqlScript, int index) {
if (sqlScript.charAt(index) != '$') {
return null;
}
int end = index + 1;
while (end < sqlScript.length()) {
char current = sqlScript.charAt(end);
if (current == '$') {
return sqlScript.substring(index, end + 1);
}
if (!(Character.isLetterOrDigit(current) || current == '_')) {
return null;
}
end++;
}
return null;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/DbController.java
+52
View File
@@ -0,0 +1,52 @@
package shine.db;
import shine.db.connection.DbProvider;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Нейтральная точка входа в runtime БД сервера.
*
* Сейчас это адаптер над legacy singleton `SqliteDbController`,
* но остальной код больше не должен зависеть от SQLite по имени класса.
*/
public final class DbController implements DbProvider {
private static volatile DbController instance;
private final SqliteDbController delegate;
private DbController() {
this.delegate = SqliteDbController.getInstance();
}
public static DbController getInstance() {
if (instance == null) {
synchronized (DbController.class) {
if (instance == null) {
instance = new DbController();
}
}
}
return instance;
}
@Override
public Connection getConnection() throws SQLException {
return delegate.getConnection();
}
public boolean isSqlite() {
return delegate.isSqlite();
}
public boolean isPostgres() {
return delegate.isPostgres();
}
@Override
public void close() {
delegate.close();
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/SqliteDbController.java
+80 -26
View File
@@ -17,29 +17,37 @@ public final class SqliteDbController {
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() {
try {
Class.forName("org.sqlite.JDBC");
} catch (ClassNotFoundException e) {
throw new RuntimeException("SQLite JDBC driver not found", e);
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;
}
String dbPath = AppConfig.getInstance().getParam("db.path");
if (dbPath == null || dbPath.isBlank()) {
throw new RuntimeException("Config param 'db.path' is not set in application.properties");
}
Path dbFile = Paths.get(dbPath);
if (!Files.exists(dbFile)) {
System.out.println("[DB] Файл БД не найден: " + dbFile.toAbsolutePath());
System.out.println("[DB] Создаём новую БД с помощью DatabaseInitializer...");
DatabaseInitializer.createNewDB(new String[0]);
}
this.jdbcUrl = "jdbc:sqlite:" + dbPath;
ensureSchemaMigrations();
initializeDatabase();
}
public static SqliteDbController getInstance() {
@@ -54,14 +62,16 @@ public final class SqliteDbController {
}
public Connection getConnection() throws SQLException {
Connection conn = DriverManager.getConnection(jdbcUrl);
Connection conn = openConnection();
conn.setAutoCommit(true);
try (Statement st = conn.createStatement()) {
st.execute("PRAGMA foreign_keys = ON");
st.execute("PRAGMA journal_mode = WAL");
st.execute("PRAGMA synchronous = NORMAL");
st.execute("PRAGMA busy_timeout = 5000");
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;
@@ -71,6 +81,14 @@ public final class SqliteDbController {
// no-op
}
public boolean isSqlite() {
return sqliteMode;
}
public boolean isPostgres() {
return !sqliteMode && jdbcUrl.startsWith("jdbc:postgresql:");
}
private void ensureSchemaMigrations() {
int currentVersion = getCurrentSchemaVersion();
@@ -395,7 +413,7 @@ public final class SqliteDbController {
}
private int getCurrentSchemaVersion() {
try (Connection c = DriverManager.getConnection(jdbcUrl)) {
try (Connection c = openConnection()) {
if (!tableExists(c, DatabaseInitializer.DB_SCHEMA_VERSION_TABLE)) {
return 0;
}
@@ -843,4 +861,40 @@ public final class SqliteDbController {
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/connection/ConnectionFactory.java
+9
View File
@@ -0,0 +1,9 @@
package shine.db.connection;
import java.sql.Connection;
import java.sql.SQLException;
@FunctionalInterface
public interface ConnectionFactory {
Connection getConnection() throws SQLException;
}
SHiNE-server/shine-server-db/src/main/java/shine/db/connection/DbProvider.java
+21
View File
@@ -0,0 +1,21 @@
package shine.db.connection;
import java.sql.Connection;
import java.sql.SQLException;
/**
* Нейтральная точка доступа к JDBC-соединениям без привязки к конкретной БД.
*
* На первом этапе переносов этот интерфейс нужен как новая опора для DAO,
* чтобы постепенно убрать прямую зависимость от DbController.
*/
public interface DbProvider extends ConnectionFactory, AutoCloseable {
@Override
Connection getConnection() throws SQLException;
@Override
default void close() throws Exception {
// По умолчанию ресурсов на уровне provider нет.
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/connection/DriverManagerDbProvider.java
+58
View File
@@ -0,0 +1,58 @@
package shine.db.connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
* Базовый JDBC provider поверх DriverManager.
*
* Не знает ничего про SQLite/PostgreSQL как про доменные режимы:
* конкретные параметры и post-connect инициализация задаются снаружи.
*/
public final class DriverManagerDbProvider implements DbProvider {
private final String jdbcUrl;
private final String user;
private final String password;
private final ConnectionInitializer initializer;
public DriverManagerDbProvider(String jdbcUrl,
String user,
String password,
ConnectionInitializer initializer) {
if (jdbcUrl == null || jdbcUrl.isBlank()) {
throw new IllegalArgumentException("jdbcUrl is blank");
}
this.jdbcUrl = jdbcUrl;
this.user = blankToNull(user);
this.password = blankToNull(password);
this.initializer = initializer;
}
@Override
public Connection getConnection() throws SQLException {
Connection connection;
if (user == null) {
connection = DriverManager.getConnection(jdbcUrl);
} else {
connection = DriverManager.getConnection(jdbcUrl, user, password == null ? "" : password);
}
if (initializer != null) {
initializer.initialize(connection);
}
return connection;
}
private static String blankToNull(String value) {
if (value == null) return null;
String trimmed = value.trim();
return trimmed.isEmpty() ? null : trimmed;
}
@FunctionalInterface
public interface ConnectionInitializer {
void initialize(Connection connection) throws SQLException;
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ActiveSessionsDAO.java
+3 -3
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.ActiveSessionEntry;
import java.sql.*;
@@ -17,7 +17,7 @@ import java.util.List;
public final class ActiveSessionsDAO {
private static volatile ActiveSessionsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private ActiveSessionsDAO() { }
@@ -137,7 +137,7 @@ public final class ActiveSessionsDAO {
client_platform,
user_language
FROM active_sessions
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
""";
List<ActiveSessionEntry> result = new ArrayList<>();
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainResyncCleanupDAO.java
+4 -4
View File
@@ -1,7 +1,7 @@
package shine.db.dao;
import shine.db.DatabaseInitializer;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -39,7 +39,7 @@ public final class BlockchainResyncCleanupDAO {
private static volatile BlockchainResyncCleanupDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private BlockchainResyncCleanupDAO() {}
@@ -307,7 +307,7 @@ public final class BlockchainResyncCleanupDAO {
private int deleteConnectionsStateForLogin(Connection c, String login) throws SQLException {
return executeDelete(c, """
DELETE FROM connections_state
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
""", login);
}
@@ -317,7 +317,7 @@ public final class BlockchainResyncCleanupDAO {
private int deleteUsersParamsForLogin(Connection c, String login) throws SQLException {
return executeDelete(c, """
DELETE FROM users_params
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
""", login);
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainStateDAO.java
+3 -3
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.BlockchainStateEntry;
import java.sql.*;
@@ -10,7 +10,7 @@ import java.util.List;
public final class BlockchainStateDAO {
private static volatile BlockchainStateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private BlockchainStateDAO() {}
@@ -75,7 +75,7 @@ public final class BlockchainStateDAO {
last_block_hash,
updated_at_ms
FROM blockchain_state
ORDER BY blockchain_name COLLATE NOCASE
ORDER BY LOWER(blockchain_name)
""";
List<BlockchainStateEntry> result = new ArrayList<>();
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlocksDAO.java
+2 -2
View File
@@ -2,7 +2,7 @@ package shine.db.dao;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.BlockEntry;
import java.sql.*;
@@ -22,7 +22,7 @@ import java.util.List;
public final class BlocksDAO {
private static volatile BlocksDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private static final Logger log = LoggerFactory.getLogger(BlocksDAO.class);
private BlocksDAO() { }
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ChannelNameStateDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.ChannelNameStateEntry;
import java.sql.Connection;
@@ -11,7 +11,7 @@ import java.util.List;
public final class ChannelNameStateDAO {
private static volatile ChannelNameStateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private ChannelNameStateDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/ConnectionsStateDAO.java
+30 -16
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -13,8 +13,8 @@ import java.util.List;
* ConnectionsStateDAO — чтение текущего состояния связей из connections_state.
*
* ВАЖНО:
* - login в запросах может быть в любом регистре, поэтому в WHERE используем COLLATE NOCASE
* - в ответах возвращаем логины в каноническом регистре через JOIN на solana_users
* - login в запросах может быть в любом регистре;
* - в ответах возвращаем логины в каноническом регистре через JOIN на solana_users.
*
* ПРИМЕЧАНИЕ:
* Таблица пользователей тут названа "solana_users". Если у тебя иначе — поменяй в SQL.
@@ -22,7 +22,7 @@ import java.util.List;
public final class ConnectionsStateDAO {
private static volatile ConnectionsStateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private ConnectionsStateDAO() {}
@@ -43,10 +43,10 @@ public final class ConnectionsStateDAO {
SELECT COALESCE(u_login.login, u_bch.login, cs.to_login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_login
ON u_login.login = cs.to_login COLLATE NOCASE
ON LOWER(u_login.login) = LOWER(cs.to_login)
LEFT JOIN solana_users u_bch
ON u_bch.blockchain_name = cs.to_bch_name COLLATE NOCASE
WHERE cs.login = ? COLLATE NOCASE
ON LOWER(u_bch.blockchain_name) = LOWER(cs.to_bch_name)
WHERE LOWER(cs.login) = LOWER(?)
AND cs.rel_type = ?
ORDER BY friend_login
""";
@@ -73,12 +73,12 @@ public final class ConnectionsStateDAO {
SELECT COALESCE(u_actor.login, cs.login) AS friend_login
FROM connections_state cs
LEFT JOIN solana_users u_actor
ON u_actor.login = cs.login COLLATE NOCASE
ON LOWER(u_actor.login) = LOWER(cs.login)
LEFT JOIN solana_users u_target
ON u_target.login = ? COLLATE NOCASE
ON LOWER(u_target.login) = LOWER(?)
WHERE (
cs.to_login = ? COLLATE NOCASE
OR (u_target.blockchain_name IS NOT NULL AND cs.to_bch_name = u_target.blockchain_name COLLATE NOCASE)
LOWER(cs.to_login) = LOWER(?)
OR (u_target.blockchain_name IS NOT NULL AND LOWER(cs.to_bch_name) = LOWER(u_target.blockchain_name))
)
AND cs.rel_type = ?
ORDER BY friend_login
@@ -107,14 +107,14 @@ public final class ConnectionsStateDAO {
SELECT u.login AS friend_login
FROM connections_state a
JOIN solana_users u
ON u.login = a.to_login COLLATE NOCASE
WHERE a.login = ? COLLATE NOCASE
ON LOWER(u.login) = LOWER(a.to_login)
WHERE LOWER(a.login) = LOWER(?)
AND a.rel_type = ?
AND EXISTS (
SELECT 1
FROM connections_state b
WHERE b.login = a.to_login COLLATE NOCASE
AND b.to_login = a.login COLLATE NOCASE
WHERE LOWER(b.login) = LOWER(a.to_login)
AND LOWER(b.to_login) = LOWER(a.login)
AND b.rel_type = a.rel_type
)
ORDER BY u.login
@@ -141,10 +141,24 @@ 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();
}
}
String sql = """
INSERT INTO connections_state (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT(login, rel_type, to_login) DO UPDATE SET
ON CONFLICT(login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash) DO UPDATE SET
to_bch_name=excluded.to_bch_name,
to_block_number=excluded.to_block_number,
to_block_hash=excluded.to_block_hash
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/DirectMessagesDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.DirectMessageEntry;
import java.sql.Connection;
@@ -8,7 +8,7 @@ import java.sql.PreparedStatement;
public final class DirectMessagesDAO {
private static volatile DirectMessagesDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private DirectMessagesDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/EspPairingRequestsDAO.java
+4 -4
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.EspPairingRequestEntry;
import java.sql.Connection;
@@ -13,7 +13,7 @@ import java.util.List;
public final class EspPairingRequestsDAO {
private static volatile EspPairingRequestsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private EspPairingRequestsDAO() { }
@@ -110,7 +110,7 @@ public final class EspPairingRequestsDAO {
payload_type, status, short_code, fingerprint_b58, encrypted_payload, reject_reason,
approved_by_session_id, created_at_ms, expires_at_ms, updated_at_ms, delivered_to_homeserver
FROM esp_pairing_requests
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
AND expires_at_ms > ?
AND status = 'created'
ORDER BY created_at_ms DESC
@@ -131,7 +131,7 @@ public final class EspPairingRequestsDAO {
StringBuilder sql = new StringBuilder("""
SELECT COUNT(*)
FROM esp_pairing_requests
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
AND created_at_ms >= ?
AND status IN (
""");
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/EspPairingSettingsDAO.java
+3 -3
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.EspPairingSettingsEntry;
import java.sql.Connection;
@@ -11,7 +11,7 @@ import java.sql.SQLException;
public final class EspPairingSettingsDAO {
private static volatile EspPairingSettingsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private EspPairingSettingsDAO() { }
@@ -74,7 +74,7 @@ public final class EspPairingSettingsDAO {
String sql = """
SELECT login, enabled, password_hash, ttl_seconds, failed_attempts, first_failed_at_ms, blocked_until_ms, updated_at_ms
FROM esp_pairing_settings
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/IpGeoCacheDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.IpGeoCacheEntry;
import java.sql.*;
@@ -20,7 +20,7 @@ import java.sql.*;
public final class IpGeoCacheDAO {
private static volatile IpGeoCacheDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private IpGeoCacheDAO() { }
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/PushTokensDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.PushTokenEntry;
import java.sql.Connection;
@@ -11,7 +11,7 @@ import java.util.List;
public final class PushTokensDAO {
private static volatile PushTokensDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private PushTokensDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedDirectMessagesHistoryDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.SignedDirectMessageHistoryEntry;
import java.sql.Connection;
@@ -8,7 +8,7 @@ import java.sql.PreparedStatement;
public final class SignedDirectMessagesHistoryDAO {
private static volatile SignedDirectMessagesHistoryDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SignedDirectMessagesHistoryDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedDmReplayDAO.java
+14 -7
View File
@@ -1,13 +1,13 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
public final class SignedDmReplayDAO {
private static volatile SignedDmReplayDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SignedDmReplayDAO() {}
@@ -23,11 +23,18 @@ 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 = """
INSERT OR IGNORE INTO signed_direct_message_replay (
from_login, time_ms, nonce, created_at_ms
) VALUES (?, ?, ?, ?)
""";
String sql = db.isPostgres()
? """
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);
ps.setLong(2, timeMs);
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SignedMessagesV2DAO.java
+72 -51
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.SignedMessageV2Entry;
import java.sql.Connection;
@@ -27,7 +27,7 @@ public final class SignedMessagesV2DAO {
}
private static volatile SignedMessagesV2DAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SignedMessagesV2DAO() {}
@@ -46,7 +46,15 @@ public final class SignedMessagesV2DAO {
if (isBlockedByConversationDelete(c, e.getFromLogin(), e.getToLogin(), e.getTimeMs())) {
return ApplyStatus.BLOCKED_BY_CONVERSATION_TOMBSTONE;
}
String sql = """
String sql = db.isPostgres() ? """
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,
raw_block, created_at_ms, source_api, origin_session_id,
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,
@@ -268,10 +276,13 @@ public final class SignedMessagesV2DAO {
withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String sql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
INSERT %s 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";
}
try (PreparedStatement ps = c.prepareStatement(sql)) {
for (String sessionId : sessionIds) {
if (sessionId == null || sessionId.isBlank()) continue;
@@ -318,18 +329,21 @@ public final class SignedMessagesV2DAO {
return withBusyRetry(() -> {
try (Connection c = db.getConnection()) {
String fillSql = """
INSERT OR IGNORE INTO signed_message_session_delivery (
INSERT %s INTO signed_message_session_delivery (
message_key, session_id, delivered, delivered_at_ms, created_at_ms
)
SELECT m.message_key, ?, 0, NULL, ?
FROM signed_messages_v2 m
FROM %s m
WHERE (
(m.message_type IN (1, 3) AND m.to_login = ? COLLATE NOCASE)
OR (m.message_type IN (2, 4) AND m.from_login = ? COLLATE NOCASE)
(m.message_type IN (1, 3) AND LOWER(m.to_login) = LOWER(?))
OR (m.message_type IN (2, 4) AND LOWER(m.from_login) = LOWER(?))
OR (m.message_type IN (5, 6, 7, 8)
AND (m.from_login = ? COLLATE NOCASE OR m.to_login = ? COLLATE NOCASE))
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";
}
long now = System.currentTimeMillis();
try (PreparedStatement ps = c.prepareStatement(fillSql)) {
ps.setString(1, sessionId);
@@ -347,12 +361,12 @@ public final class SignedMessagesV2DAO {
m.time_ms, m.nonce, m.message_type, m.revision_time_ms, m.reencrypted_at_ms,
m.raw_block, m.created_at_ms, m.source_api, m.origin_session_id,
m.receipt_ref_base_key, m.receipt_ref_type, m.read_at_ms
FROM signed_messages_v2 m
FROM %s m
JOIN signed_message_session_delivery d
ON d.message_key = m.message_key
WHERE d.session_id = ? AND d.delivered = 0
ORDER BY m.time_ms ASC, m.revision_time_ms ASC, m.reencrypted_at_ms ASC, m.created_at_ms ASC
""";
""".formatted(messagesTable());
List<SignedMessageV2Entry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, sessionId);
@@ -379,12 +393,12 @@ public final class SignedMessagesV2DAO {
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
FROM signed_messages_v2
WHERE target_login = ? COLLATE NOCASE
FROM %s
WHERE LOWER(target_login) = LOWER(?)
AND message_type IN (1, 2)
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
(LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
OR (LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
)
AND (
? <= 0
@@ -393,7 +407,7 @@ public final class SignedMessagesV2DAO {
)
ORDER BY time_ms DESC, message_key DESC
LIMIT ?
""";
""".formatted(messagesTable());
List<SignedMessageV2Entry> out = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
@@ -417,7 +431,7 @@ public final class SignedMessagesV2DAO {
private void upsertMessage(Connection c, SignedMessageV2Entry e) throws SQLException {
String sql = """
INSERT INTO signed_messages_v2 (
INSERT INTO %s (
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,
@@ -439,8 +453,8 @@ public final class SignedMessagesV2DAO {
origin_session_id = excluded.origin_session_id,
receipt_ref_base_key = excluded.receipt_ref_base_key,
receipt_ref_type = excluded.receipt_ref_type,
read_at_ms = COALESCE(signed_messages_v2.read_at_ms, excluded.read_at_ms)
""";
read_at_ms = COALESCE(%s.read_at_ms, excluded.read_at_ms)
""".formatted(messagesTable(), messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
ps.executeUpdate();
@@ -456,7 +470,7 @@ public final class SignedMessagesV2DAO {
long readAtMs = entry.getTimeMs();
if (readAtMs <= 0) return;
try (PreparedStatement ps = c.prepareStatement("""
UPDATE signed_messages_v2
UPDATE %s
SET read_at_ms = CASE
WHEN read_at_ms IS NULL OR read_at_ms <= 0 THEN ?
WHEN read_at_ms > ? THEN ?
@@ -464,7 +478,7 @@ public final class SignedMessagesV2DAO {
END
WHERE base_key = ?
AND message_type IN (1, 2)
""")) {
""".formatted(messagesTable()))) {
ps.setLong(1, readAtMs);
ps.setLong(2, readAtMs);
ps.setLong(3, readAtMs);
@@ -476,10 +490,10 @@ public final class SignedMessagesV2DAO {
private RevisionMarker getRevisionMarkerByMessageKey(Connection c, String messageKey) throws SQLException {
String sql = """
SELECT revision_time_ms, reencrypted_at_ms
FROM signed_messages_v2
FROM %s
WHERE message_key = ?
LIMIT 1
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, messageKey);
try (ResultSet rs = ps.executeQuery()) {
@@ -492,12 +506,12 @@ public final class SignedMessagesV2DAO {
private RevisionMarker getCurrentContentMarker(Connection c, String baseKey) throws SQLException {
String sql = """
SELECT revision_time_ms, reencrypted_at_ms
FROM signed_messages_v2
FROM %s
WHERE base_key = ?
AND message_type IN (1, 2)
ORDER BY revision_time_ms DESC, reencrypted_at_ms DESC
LIMIT 1
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, baseKey);
try (ResultSet rs = ps.executeQuery()) {
@@ -510,11 +524,11 @@ public final class SignedMessagesV2DAO {
private boolean hasMessageDeleteTombstone(Connection c, String baseKey) throws SQLException {
String sql = """
SELECT 1
FROM signed_messages_v2
FROM %s
WHERE base_key = ?
AND message_type IN (5, 6)
LIMIT 1
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, baseKey);
try (ResultSet rs = ps.executeQuery()) {
@@ -531,13 +545,13 @@ public final class SignedMessagesV2DAO {
private Long getLatestConversationDeleteBoundary(Connection c, String fromLogin, String toLogin) throws SQLException {
String sql = """
SELECT MAX(time_ms)
FROM signed_messages_v2
FROM %s
WHERE message_type IN (7, 8)
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
(LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
OR (LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
)
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, fromLogin);
ps.setString(2, toLogin);
@@ -558,15 +572,15 @@ public final class SignedMessagesV2DAO {
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
FROM signed_messages_v2
FROM %s
WHERE message_type IN (7, 8)
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
(LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
OR (LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
)
ORDER BY time_ms DESC
LIMIT 1
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, fromLogin);
ps.setString(2, toLogin);
@@ -582,16 +596,16 @@ public final class SignedMessagesV2DAO {
private void deleteMessageContentAndReceipts(Connection c, String baseKey) throws SQLException {
deleteDeliveryRowsByMessageSelection(c, """
SELECT message_key
FROM signed_messages_v2
FROM %s
WHERE (base_key = ? AND message_type IN (1, 2))
OR (receipt_ref_base_key = ? AND message_type IN (3, 4))
""", baseKey, baseKey);
""".formatted(messagesTable()), baseKey, baseKey);
try (PreparedStatement ps = c.prepareStatement("""
DELETE FROM signed_messages_v2
DELETE FROM %s
WHERE (base_key = ? AND message_type IN (1, 2))
OR (receipt_ref_base_key = ? AND message_type IN (3, 4))
""")) {
""".formatted(messagesTable()))) {
ps.setString(1, baseKey);
ps.setString(2, baseKey);
ps.executeUpdate();
@@ -601,22 +615,22 @@ public final class SignedMessagesV2DAO {
private void deleteConversationHistoryBefore(Connection c, String fromLogin, String toLogin, long boundaryTimeMs) throws SQLException {
deleteDeliveryRowsByMessageSelection(c, """
SELECT message_key
FROM signed_messages_v2
FROM %s
WHERE time_ms < ?
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
(LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
OR (LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
)
""", boundaryTimeMs, fromLogin, toLogin, toLogin, fromLogin);
""".formatted(messagesTable()), boundaryTimeMs, fromLogin, toLogin, toLogin, fromLogin);
try (PreparedStatement ps = c.prepareStatement("""
DELETE FROM signed_messages_v2
DELETE FROM %s
WHERE time_ms < ?
AND (
(from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
OR (from_login = ? COLLATE NOCASE AND to_login = ? COLLATE NOCASE)
(LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
OR (LOWER(from_login) = LOWER(?) AND LOWER(to_login) = LOWER(?))
)
""")) {
""".formatted(messagesTable()))) {
ps.setLong(1, boundaryTimeMs);
ps.setString(2, fromLogin);
ps.setString(3, toLogin);
@@ -647,13 +661,13 @@ public final class SignedMessagesV2DAO {
private int insertStrict(Connection c, SignedMessageV2Entry e) throws SQLException {
String sql = """
INSERT INTO signed_messages_v2 (
INSERT INTO %s (
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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
""".formatted(messagesTable());
try (PreparedStatement ps = c.prepareStatement(sql)) {
bindSignedMessage(ps, e);
return ps.executeUpdate();
@@ -704,6 +718,9 @@ public final class SignedMessagesV2DAO {
}
private boolean isBusyLock(SQLException ex) {
if (db.isPostgres()) {
return false;
}
Throwable current = ex;
while (current != null) {
String msg = String.valueOf(current.getMessage()).toLowerCase();
@@ -748,6 +765,10 @@ public final class SignedMessagesV2DAO {
return Long.compare(left.reencryptedAtMs, right.reencryptedAtMs);
}
private String messagesTable() {
return db.isPostgres() ? "signed_messages" : "signed_messages_v2";
}
private SignedMessageV2Entry mapRow(ResultSet rs) throws Exception {
SignedMessageV2Entry e = new SignedMessageV2Entry();
e.setMessageKey(rs.getString("message_key"));
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SolanaUsersDAO.java
+28 -3
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.SolanaUserEntry;
import java.sql.*;
@@ -26,7 +26,7 @@ import java.util.List;
public final class SolanaUsersDAO {
private static volatile SolanaUsersDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SolanaUsersDAO() {}
@@ -43,6 +43,31 @@ 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
@@ -223,4 +248,4 @@ public final class SolanaUsersDAO {
return e;
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SubscriptionsDAO.java
+2 -2
View File
@@ -1,7 +1,7 @@
package shine.db.dao;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.*;
import java.util.ArrayList;
@@ -25,7 +25,7 @@ import java.util.List;
public final class SubscriptionsDAO {
private static volatile SubscriptionsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SubscriptionsDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/SyncServersDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.SyncServerEntry;
import java.sql.Connection;
@@ -17,7 +17,7 @@ import java.util.List;
public final class SyncServersDAO {
private static volatile SyncServersDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private SyncServersDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/TestFreeAvatarUploadsDAO.java
+3 -3
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.TestFreeAvatarUploadEntry;
import java.sql.Connection;
@@ -11,7 +11,7 @@ import java.sql.SQLException;
public final class TestFreeAvatarUploadsDAO {
private static volatile TestFreeAvatarUploadsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private TestFreeAvatarUploadsDAO() {
}
@@ -29,7 +29,7 @@ public final class TestFreeAvatarUploadsDAO {
String sql = """
SELECT login, used_count, updated_at_ms, last_tx_id
FROM test_free_avatar_uploads
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserCreateDAO.java
+2 -2
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.SolanaUserEntry;
import java.sql.*;
@@ -17,7 +17,7 @@ import java.sql.*;
public final class UserCreateDAO {
private static volatile UserCreateDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private final SolanaUsersDAO usersDao = SolanaUsersDAO.getInstance();
private UserCreateDAO() {}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/UserParamsDAO.java
+4 -4
View File
@@ -1,6 +1,6 @@
package shine.db.dao;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.entities.UserParamEntry;
import java.sql.*;
@@ -21,7 +21,7 @@ import java.util.List;
public final class UserParamsDAO {
private static volatile UserParamsDAO instance;
private final SqliteDbController db = SqliteDbController.getInstance();
private final DbController db = DbController.getInstance();
private UserParamsDAO() { }
@@ -89,7 +89,7 @@ public final class UserParamsDAO {
client_key,
signature
FROM users_params
WHERE login = ? COLLATE NOCASE AND param = ?
WHERE LOWER(login) = LOWER(?) AND param = ?
LIMIT 1
""";
@@ -120,7 +120,7 @@ public final class UserParamsDAO {
client_key,
signature
FROM users_params
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
ORDER BY time_ms DESC
""";
SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
+943
View File
@@ -0,0 +1,943 @@
-- SHiNE PostgreSQL runtime schema v1
-- Дата: 2026-07-24
--
-- Назначение:
-- - поднять пустую PostgreSQL БД сервера SHiNE с нуля;
-- - включить таблицы модуля синхронизации Solana users;
-- - включить runtime-таблицы сервера без legacy SQLite таблиц:
-- * НЕ создаём solana_users
-- * НЕ создаём direct_messages
-- * основной runtime DM storage = signed_messages
--
-- Важно:
-- - источник истины по пользователям: solana_user_pda_current;
-- - runtime table blockchain_state остаётся как локальное серверное состояние chain,
-- но не мигрируется из старой SQLite и не считается identity-слоем;
-- - триггеры переписаны под PostgreSQL и сохраняют текущую серверную логику.
BEGIN;
CREATE TABLE IF NOT EXISTS db_schema_version (
id INTEGER PRIMARY KEY,
schema_version INTEGER NOT NULL,
updated_at_ms BIGINT NOT NULL
);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 1, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
ON CONFLICT (id) DO UPDATE SET
schema_version = EXCLUDED.schema_version,
updated_at_ms = EXCLUDED.updated_at_ms;
CREATE TABLE IF NOT EXISTS solana_sync_state (
id INTEGER PRIMARY KEY,
status TEXT NOT NULL,
ready BOOLEAN NOT NULL,
last_poll_at_ms BIGINT,
last_successful_poll_at_ms BIGINT,
last_seen_signature TEXT,
last_seen_slot BIGINT,
last_relevant_signature TEXT,
last_relevant_slot BIGINT,
last_error TEXT,
economy_config_version INTEGER,
registration_fee_lamports BIGINT,
lamports_per_limit_step BIGINT,
start_bonus_limit BIGINT,
updated_at_ms BIGINT
);
INSERT INTO solana_sync_state (id, status, ready)
VALUES (1, 'EMPTY', FALSE)
ON CONFLICT (id) DO NOTHING;
CREATE TABLE IF NOT EXISTS solana_sync_tx_history (
id BIGSERIAL PRIMARY KEY,
signature TEXT NOT NULL UNIQUE,
slot BIGINT NOT NULL,
block_time BIGINT,
tx_kind TEXT NOT NULL,
is_relevant BOOLEAN NOT NULL,
affected_pda_address TEXT,
affected_login TEXT,
raw_tx_json TEXT NOT NULL,
saved_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_slot
ON solana_sync_tx_history(slot);
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_relevant
ON solana_sync_tx_history(is_relevant);
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_login
ON solana_sync_tx_history(affected_login);
CREATE TABLE IF NOT EXISTS solana_user_pda_current (
pda_address TEXT PRIMARY KEY,
login TEXT NOT NULL UNIQUE,
record_number INTEGER NOT NULL,
slot BIGINT NOT NULL,
last_tx_signature TEXT NOT NULL,
recovery_key TEXT NOT NULL,
root_key TEXT NOT NULL,
client_key TEXT NOT NULL,
blockchain_name TEXT NOT NULL,
blockchain_key TEXT NOT NULL,
paid_limit_bytes BIGINT NOT NULL,
used_bytes BIGINT NOT NULL,
last_block_number INTEGER NOT NULL,
last_block_hash TEXT NOT NULL,
last_block_signature TEXT NOT NULL,
arweave_tx_id TEXT NOT NULL,
is_server BOOLEAN NOT NULL,
address_format_type INTEGER NOT NULL,
address_format_version INTEGER NOT NULL,
server_address TEXT NOT NULL,
sync_servers_json TEXT NOT NULL,
access_servers_json TEXT NOT NULL,
sessions_mode INTEGER NOT NULL,
sessions_json TEXT NOT NULL,
trusted_count INTEGER NOT NULL,
created_at_ms BIGINT NOT NULL,
updated_at_ms BIGINT NOT NULL,
prev_record_hash TEXT NOT NULL,
record_signature TEXT NOT NULL,
raw_data_base64 TEXT NOT NULL,
first_seen_at_ms BIGINT NOT NULL,
last_synced_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_user_pda_current_slot
ON solana_user_pda_current(slot);
CREATE TABLE IF NOT EXISTS solana_users_manual (
login TEXT PRIMARY KEY,
blockchain_name TEXT NOT NULL UNIQUE,
solana_key TEXT NOT NULL,
blockchain_key TEXT NOT NULL,
client_key TEXT NOT NULL,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_solana_users_manual_login
ON solana_users_manual(login);
CREATE OR REPLACE VIEW solana_users AS
SELECT
current_users.login AS login,
current_users.blockchain_name AS blockchain_name,
current_users.client_key AS solana_key,
current_users.blockchain_key AS blockchain_key,
current_users.client_key AS client_key
FROM solana_user_pda_current current_users
UNION ALL
SELECT
manual.login,
manual.blockchain_name,
manual.solana_key,
manual.blockchain_key,
manual.client_key
FROM solana_users_manual manual
WHERE NOT EXISTS (
SELECT 1
FROM solana_user_pda_current current_users
WHERE LOWER(current_users.login) = LOWER(manual.login)
);
CREATE TABLE IF NOT EXISTS solana_user_pda_history (
id BIGSERIAL PRIMARY KEY,
tx_signature TEXT NOT NULL,
slot BIGINT NOT NULL,
block_time BIGINT,
pda_address TEXT NOT NULL,
login TEXT NOT NULL,
record_number INTEGER NOT NULL,
recovery_key TEXT NOT NULL,
root_key TEXT NOT NULL,
client_key TEXT NOT NULL,
blockchain_name TEXT NOT NULL,
blockchain_key TEXT NOT NULL,
paid_limit_bytes BIGINT NOT NULL,
used_bytes BIGINT NOT NULL,
last_block_number INTEGER NOT NULL,
last_block_hash TEXT NOT NULL,
last_block_signature TEXT NOT NULL,
arweave_tx_id TEXT NOT NULL,
is_server BOOLEAN NOT NULL,
address_format_type INTEGER NOT NULL,
address_format_version INTEGER NOT NULL,
server_address TEXT NOT NULL,
sync_servers_json TEXT NOT NULL,
access_servers_json TEXT NOT NULL,
sessions_mode INTEGER NOT NULL,
sessions_json TEXT NOT NULL,
trusted_count INTEGER NOT NULL,
created_at_ms BIGINT NOT NULL,
updated_at_ms BIGINT NOT NULL,
prev_record_hash TEXT NOT NULL,
record_signature TEXT NOT NULL,
raw_data_base64 TEXT NOT NULL,
saved_at_ms BIGINT NOT NULL,
UNIQUE (pda_address, record_number)
);
CREATE INDEX IF NOT EXISTS idx_user_pda_history_login
ON solana_user_pda_history(login);
CREATE INDEX IF NOT EXISTS idx_user_pda_history_slot
ON solana_user_pda_history(slot);
CREATE TABLE IF NOT EXISTS active_sessions (
session_id TEXT PRIMARY KEY,
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
session_key TEXT NOT NULL,
storage_pwd TEXT NOT NULL,
session_created_at_ms BIGINT NOT NULL,
last_authirificated_at_ms BIGINT 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
);
CREATE INDEX IF NOT EXISTS idx_active_sessions_login
ON active_sessions(login);
CREATE TABLE IF NOT EXISTS esp_pairing_settings (
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login),
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 BIGINT NOT NULL DEFAULT 0,
blocked_until_ms BIGINT NOT NULL DEFAULT 0,
updated_at_ms BIGINT NOT NULL
);
CREATE TABLE IF NOT EXISTS esp_pairing_requests (
pairing_id TEXT PRIMARY KEY,
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
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 BIGINT NOT NULL,
expires_at_ms BIGINT NOT NULL,
updated_at_ms BIGINT NOT NULL,
delivered_to_homeserver INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX IF NOT EXISTS idx_esp_pairing_requests_login_status
ON esp_pairing_requests(login, status, expires_at_ms);
CREATE TABLE IF NOT EXISTS users_params (
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
param TEXT NOT NULL,
time_ms BIGINT NOT NULL,
value TEXT NOT NULL,
client_key TEXT,
signature TEXT,
UNIQUE (login, param)
);
CREATE INDEX IF NOT EXISTS idx_users_params_login
ON users_params(login);
CREATE TABLE IF NOT EXISTS ip_geo_cache (
ip TEXT PRIMARY KEY,
geo TEXT,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_ip_geo_cache_updated_at
ON ip_geo_cache(updated_at_ms);
CREATE TABLE IF NOT EXISTS test_free_avatar_uploads (
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login),
used_count INTEGER NOT NULL DEFAULT 0,
updated_at_ms BIGINT NOT NULL,
last_tx_id TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_test_free_avatar_uploads_updated
ON test_free_avatar_uploads(updated_at_ms);
CREATE TABLE IF NOT EXISTS sync_servers (
login TEXT PRIMARY KEY,
server_address TEXT NOT NULL DEFAULT '',
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_sync_servers_updated
ON sync_servers(updated_at_ms);
CREATE TABLE IF NOT EXISTS blockchain_state (
blockchain_name TEXT PRIMARY KEY,
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
blockchain_key TEXT NOT NULL,
size_limit BIGINT NOT NULL,
file_size_bytes BIGINT NOT NULL,
last_block_number INTEGER NOT NULL,
last_block_hash BYTEA,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_blockchain_state_login
ON blockchain_state(login);
CREATE INDEX IF NOT EXISTS idx_blockchain_state_updated_at
ON blockchain_state(updated_at_ms);
CREATE TABLE IF NOT EXISTS blocks (
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
bch_name TEXT NOT NULL REFERENCES blockchain_state(blockchain_name),
block_number INTEGER NOT NULL CHECK (block_number >= 0),
msg_type INTEGER NOT NULL,
msg_sub_type INTEGER NOT NULL,
block_bytes BYTEA NOT NULL,
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 BYTEA,
block_hash BYTEA NOT NULL,
block_signature BYTEA NOT NULL,
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 BYTEA,
this_line_number INTEGER CHECK (this_line_number IS NULL OR this_line_number >= 0),
UNIQUE (bch_name, block_number)
);
CREATE INDEX IF NOT EXISTS idx_blocks_by_chain_number
ON blocks (bch_name, block_number);
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
ON blocks (to_login, to_bch_name, to_block_number);
CREATE INDEX IF NOT EXISTS idx_blocks_by_line
ON blocks (bch_name, line_code, this_line_number);
CREATE TABLE IF NOT EXISTS connections_state (
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
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 BYTEA NOT NULL,
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
);
CREATE INDEX IF NOT EXISTS idx_connections_state_login
ON connections_state(login);
CREATE INDEX IF NOT EXISTS idx_connections_state_to_login
ON connections_state(to_login);
CREATE INDEX IF NOT EXISTS idx_connections_state_pair
ON connections_state(login, to_login);
CREATE INDEX IF NOT EXISTS idx_connections_state_target
ON connections_state(login, rel_type, to_bch_name, to_block_number);
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 BYTEA 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)
);
CREATE INDEX IF NOT EXISTS idx_message_stats_target
ON message_stats (to_bch_name, to_block_number, to_block_hash);
CREATE INDEX IF NOT EXISTS idx_message_stats_login
ON message_stats (to_login);
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 BYTEA 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
)
);
CREATE INDEX IF NOT EXISTS idx_reactions_state_target
ON reactions_state (to_bch_name, to_block_number, to_block_hash);
CREATE INDEX IF NOT EXISTS idx_reactions_state_actor
ON reactions_state (from_login, from_bch_name, reaction_type);
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 BYTEA NOT NULL,
created_at_ms BIGINT NOT NULL
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_owner_type_slug
ON channel_names_state (owner_bch_name, channel_type_code, slug);
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);
CREATE INDEX IF NOT EXISTS idx_channel_names_state_owner
ON channel_names_state (owner_login, owner_bch_name);
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 BYTEA NOT NULL,
channel_name TEXT NOT NULL,
channel_type_version INTEGER NOT NULL,
chat_title TEXT NOT NULL DEFAULT '',
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_bch_name, channel_root_block_number)
);
CREATE INDEX IF NOT EXISTS idx_chat200_state_owner
ON chat200_state (owner_login, owner_bch_name);
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 BIGINT NOT NULL,
updated_by_block_number INTEGER NOT NULL,
PRIMARY KEY (
owner_bch_name,
channel_root_block_number,
member_login,
member_channel_name
)
);
CREATE INDEX IF NOT EXISTS idx_chat200_members_owner
ON chat200_members_state (owner_bch_name, channel_root_block_number, is_active);
CREATE TABLE IF NOT EXISTS user_push_tokens (
token_id TEXT PRIMARY KEY,
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
session_id TEXT NOT NULL,
provider TEXT NOT NULL,
token TEXT NOT NULL,
platform TEXT,
user_agent TEXT,
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login
ON user_push_tokens(login);
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login_session
ON user_push_tokens(login, session_id);
CREATE TABLE IF NOT EXISTS signed_direct_message_replay (
from_login TEXT NOT NULL,
time_ms BIGINT NOT NULL,
nonce BIGINT NOT NULL,
created_at_ms BIGINT NOT NULL,
UNIQUE (from_login, time_ms, nonce)
);
CREATE INDEX IF NOT EXISTS idx_signed_dm_replay_created
ON signed_direct_message_replay(created_at_ms);
CREATE TABLE IF NOT EXISTS signed_direct_messages_history (
message_id TEXT PRIMARY KEY,
from_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
to_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
target_mode INTEGER NOT NULL,
target_session_id TEXT,
message_type INTEGER NOT NULL,
time_ms BIGINT NOT NULL,
nonce BIGINT NOT NULL,
raw_packet BYTEA NOT NULL,
created_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_signed_dm_history_to
ON signed_direct_messages_history(to_login, created_at_ms);
CREATE TABLE IF NOT EXISTS signed_messages (
message_key TEXT PRIMARY KEY,
base_key TEXT NOT NULL,
target_login TEXT NOT NULL,
from_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
to_login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
time_ms BIGINT NOT NULL,
nonce BIGINT NOT NULL,
message_type INTEGER NOT NULL,
revision_time_ms BIGINT NOT NULL DEFAULT 0,
reencrypted_at_ms BIGINT NOT NULL DEFAULT 0,
raw_block BYTEA NOT NULL,
created_at_ms BIGINT NOT NULL,
source_api TEXT NOT NULL,
origin_session_id TEXT,
receipt_ref_base_key TEXT,
receipt_ref_type INTEGER,
read_at_ms BIGINT
);
CREATE INDEX IF NOT EXISTS idx_signed_messages_target
ON signed_messages(target_login, time_ms, created_at_ms);
CREATE INDEX IF NOT EXISTS idx_signed_messages_base
ON signed_messages(base_key, message_type);
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_receipt_incoming
ON signed_messages(target_login, receipt_ref_base_key)
WHERE message_type = 3 AND receipt_ref_base_key IS NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_receipt_outgoing
ON signed_messages(target_login, receipt_ref_base_key)
WHERE message_type = 4 AND receipt_ref_base_key IS NOT NULL;
CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
message_key TEXT NOT NULL REFERENCES signed_messages(message_key),
session_id TEXT NOT NULL,
delivered INTEGER NOT NULL DEFAULT 0,
delivered_at_ms BIGINT,
created_at_ms BIGINT NOT NULL,
PRIMARY KEY (message_key, session_id)
);
CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
ON signed_message_session_delivery(session_id, delivered);
CREATE OR REPLACE VIEW signed_messages_v2 AS
SELECT
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
FROM signed_messages;
CREATE TABLE IF NOT EXISTS message_views_state (
viewer_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
to_block_number INTEGER NOT NULL,
to_block_hash BYTEA NOT NULL,
first_seen_at_ms BIGINT NOT NULL,
PRIMARY KEY (viewer_login, to_bch_name, to_block_number, to_block_hash)
);
CREATE INDEX IF NOT EXISTS idx_message_views_state_target
ON message_views_state(to_bch_name, to_block_number, to_block_hash);
CREATE INDEX IF NOT EXISTS idx_message_views_state_viewer_channel
ON message_views_state(viewer_login, to_bch_name);
CREATE OR REPLACE FUNCTION shine_login_from_blockchain_name(blockchain_name_in TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
dash_pos INTEGER;
suffix TEXT;
BEGIN
IF blockchain_name_in IS NULL OR length(blockchain_name_in) <= 4 THEN
RETURN NULL;
END IF;
dash_pos := length(blockchain_name_in) - 3;
IF substr(blockchain_name_in, dash_pos, 1) <> '-' THEN
RETURN NULL;
END IF;
suffix := substr(blockchain_name_in, dash_pos + 1);
IF suffix !~ '^[0-9]{3}$' THEN
RETURN NULL;
END IF;
RETURN substr(blockchain_name_in, 1, dash_pos - 1);
END;
$$;
CREATE OR REPLACE FUNCTION shine_resolve_login(target_login_in TEXT, target_bch_name_in TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
resolved_login TEXT;
BEGIN
IF target_login_in IS NOT NULL AND btrim(target_login_in) <> '' THEN
RETURN target_login_in;
END IF;
IF target_bch_name_in IS NOT NULL AND btrim(target_bch_name_in) <> '' THEN
SELECT login
INTO resolved_login
FROM solana_user_pda_current
WHERE blockchain_name = target_bch_name_in
LIMIT 1;
IF resolved_login IS NOT NULL AND btrim(resolved_login) <> '' THEN
RETURN resolved_login;
END IF;
END IF;
RETURN shine_login_from_blockchain_name(target_bch_name_in);
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_line_integrity_bi()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
prev_block RECORD;
BEGIN
IF NEW.line_code IS NULL
AND NEW.prev_line_number IS NULL
AND NEW.prev_line_hash IS NULL
AND NEW.this_line_number IS NULL THEN
RETURN NEW;
END IF;
IF NEW.msg_type NOT IN (0, 1, 3, 4) THEN
RAISE EXCEPTION 'LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE';
END IF;
IF 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 THEN
RAISE EXCEPTION 'LINE_ERR_PARTIAL_FIELDS';
END IF;
SELECT *
INTO prev_block
FROM blocks
WHERE bch_name = NEW.bch_name
AND block_number = NEW.prev_line_number
LIMIT 1;
IF NOT FOUND THEN
RAISE EXCEPTION 'LINE_ERR_NO_PREV';
END IF;
IF prev_block.block_hash IS DISTINCT FROM NEW.prev_line_hash THEN
RAISE EXCEPTION 'LINE_ERR_PREV_HASH_MISMATCH';
END IF;
IF NEW.prev_line_number <> NEW.line_code
AND prev_block.line_code IS DISTINCT FROM NEW.line_code THEN
RAISE EXCEPTION 'LINE_ERR_LINE_CODE_MISMATCH';
END IF;
IF NEW.prev_line_number = NEW.line_code THEN
IF NEW.this_line_number <> CASE WHEN NEW.msg_type = 1 THEN 0 ELSE 1 END THEN
RAISE EXCEPTION 'LINE_ERR_FIRST_STEP_BAD_THIS';
END IF;
RETURN NEW;
END IF;
IF prev_block.this_line_number IS NULL THEN
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
END IF;
IF NEW.msg_type = 1 THEN
IF NEW.this_line_number <> prev_block.this_line_number
AND NEW.this_line_number <> prev_block.this_line_number + 1 THEN
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
END IF;
ELSE
IF NEW.this_line_number <> prev_block.this_line_number + 1 THEN
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
END IF;
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_connection_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
resolved_login TEXT;
positive_rel_type INTEGER;
BEGIN
IF NEW.msg_type <> 3 THEN
RETURN NEW;
END IF;
resolved_login := shine_resolve_login(NEW.to_login, NEW.to_bch_name);
IF NEW.msg_sub_type IN (10, 20, 30, 40, 50, 52, 54, 60, 70, 74) THEN
IF resolved_login IS NULL OR NEW.to_bch_name IS NULL THEN
RETURN NEW;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login;
INSERT INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
) VALUES (
NEW.login,
NEW.msg_sub_type,
resolved_login,
NEW.to_bch_name,
COALESCE(NEW.to_block_number, 0),
COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
);
RETURN NEW;
END IF;
positive_rel_type := CASE NEW.msg_sub_type
WHEN 11 THEN 10
WHEN 21 THEN 20
WHEN 31 THEN 30
WHEN 41 THEN 40
WHEN 51 THEN 50
WHEN 53 THEN 52
WHEN 55 THEN 54
WHEN 61 THEN 60
WHEN 71 THEN 70
WHEN 75 THEN 74
ELSE NULL
END;
IF positive_rel_type IS NULL OR resolved_login IS NULL THEN
RETURN NEW;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_like_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
previous_sub_type INTEGER;
BEGIN
IF NEW.msg_type <> 2 OR NEW.msg_sub_type NOT IN (1, 2) THEN
RETURN NEW;
END IF;
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
RETURN NEW;
END IF;
INSERT INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
) VALUES (
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
)
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
SELECT last_sub_type
INTO previous_sub_type
FROM reactions_state
WHERE from_login = NEW.login
AND from_bch_name = NEW.bch_name
AND reaction_type = 1
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
LIMIT 1;
IF NEW.msg_sub_type = 1 AND previous_sub_type IS DISTINCT FROM 1 THEN
UPDATE message_stats
SET likes_count = likes_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;
ELSIF NEW.msg_sub_type = 2 AND previous_sub_type = 1 THEN
UPDATE message_stats
SET likes_count = GREATEST(0, likes_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;
END IF;
INSERT INTO reactions_state (
from_login, from_bch_name, reaction_type,
to_login, to_bch_name, to_block_number, to_block_hash,
last_sub_type
) VALUES (
NEW.login, NEW.bch_name, 1,
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
NEW.msg_sub_type
)
ON CONFLICT (
from_login, from_bch_name, reaction_type,
to_login, to_bch_name, to_block_number, to_block_hash
) DO UPDATE SET
last_sub_type = EXCLUDED.last_sub_type;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_reply_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.msg_type <> 1 OR NEW.msg_sub_type <> 20 THEN
RETURN NEW;
END IF;
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
RETURN NEW;
END IF;
INSERT INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
) VALUES (
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
)
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
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;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_edit_apply_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
IF NEW.msg_type <> 1 OR NEW.msg_sub_type NOT IN (11, 21) THEN
RETURN NEW;
END IF;
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;
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
RETURN NEW;
END IF;
INSERT INTO message_stats (
to_login, to_bch_name, to_block_number, to_block_hash,
likes_count, replies_count, edits_count
) VALUES (
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
0, 0, 0
)
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
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;
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_blocks_line_integrity_bi ON blocks;
CREATE TRIGGER trg_blocks_line_integrity_bi
BEFORE INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_line_integrity_bi();
DROP TRIGGER IF EXISTS trg_blocks_connection_state_ai ON blocks;
CREATE TRIGGER trg_blocks_connection_state_ai
AFTER INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_connection_state_ai();
DROP TRIGGER IF EXISTS trg_blocks_message_stats_like_ai ON blocks;
CREATE TRIGGER trg_blocks_message_stats_like_ai
AFTER INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_message_stats_like_ai();
DROP TRIGGER IF EXISTS trg_blocks_message_stats_reply_ai ON blocks;
CREATE TRIGGER trg_blocks_message_stats_reply_ai
AFTER INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_message_stats_reply_ai();
DROP TRIGGER IF EXISTS trg_blocks_edit_apply_ai ON blocks;
CREATE TRIGGER trg_blocks_edit_apply_ai
AFTER INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_edit_apply_ai();
COMMIT;
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/blockchain/Net_AddBlock_Handler.java
+4 -4
View File
@@ -544,7 +544,7 @@ public final class Net_AddBlock_Handler implements JsonMessageHandler {
}
private void upsertChat200StateFromCreate(Chat200CreateSeed seed) throws Exception {
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection();
try (Connection c = shine.db.DbController.getInstance().getConnection();
PreparedStatement ps = c.prepareStatement("""
INSERT INTO chat200_state (
owner_login, owner_bch_name, channel_root_block_number, channel_root_block_hash,
@@ -586,7 +586,7 @@ public final class Net_AddBlock_Handler implements JsonMessageHandler {
long updatedAtMs = block.timestamp * 1000L;
if ("desc".equals(cmd.command)) {
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection();
try (Connection c = shine.db.DbController.getInstance().getConnection();
PreparedStatement ps = c.prepareStatement("""
UPDATE chat200_state
SET chat_title = ?, updated_at_ms = ?
@@ -606,7 +606,7 @@ public final class Net_AddBlock_Handler implements JsonMessageHandler {
String memberChannel = cmd.arg2 == null ? "" : cmd.arg2.trim();
if (memberLogin.isBlank() || memberChannel.isBlank()) return;
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection();
try (Connection c = shine.db.DbController.getInstance().getConnection();
PreparedStatement ps = c.prepareStatement("""
INSERT INTO chat200_members_state (
owner_bch_name, channel_root_block_number, member_login, member_channel_name,
@@ -630,7 +630,7 @@ public final class Net_AddBlock_Handler implements JsonMessageHandler {
}
private boolean isChat200Channel(String ownerBch, int rootBlockNumber) throws Exception {
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection();
try (Connection c = shine.db.DbController.getInstance().getConnection();
PreparedStatement ps = c.prepareStatement("""
SELECT channel_type_code
FROM channel_names_state
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/blockchain/Net_AddBlock_Handler_utils/BlockchainWriter.java
+1 -1
View File
@@ -68,7 +68,7 @@ public final class BlockchainWriter {
prepareWriteArtifacts(blockchainName, block.blockNumber, blockHashHex, candidateBytes);
boolean committed = false;
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection()) {
try (Connection c = shine.db.DbController.getInstance().getConnection()) {
c.setAutoCommit(false);
try {
// 1) insert block
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/ChannelNamesStateBootstrapper.java
+2 -2
View File
@@ -4,7 +4,7 @@ import blockchain.BchBlockEntry;
import blockchain.body.CreateChannelBody;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.channels.ChannelNameRules;
import shine.db.dao.ChannelNameStateDAO;
import shine.db.entities.ChannelNameStateEntry;
@@ -48,7 +48,7 @@ public final class ChannelNamesStateBootstrapper {
ORDER BY bch_name, block_number
""";
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
c.setAutoCommit(false);
try {
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/ChannelsReadSupport.java
+3 -3
View File
@@ -30,7 +30,7 @@ final class ChannelsReadSupport {
private ChannelsReadSupport() {}
static String canonicalLogin(Connection c, String anyCaseLogin) throws SQLException {
String sql = "SELECT login FROM solana_users WHERE login = ? COLLATE NOCASE LIMIT 1";
String sql = "SELECT login FROM solana_users WHERE LOWER(login) = LOWER(?) LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, anyCaseLogin);
try (ResultSet rs = ps.executeQuery()) {
@@ -411,7 +411,7 @@ final class ChannelsReadSupport {
String partnerBchSql = """
SELECT blockchain_name
FROM blockchain_state
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
ORDER BY blockchain_name
LIMIT 1
""";
@@ -462,7 +462,7 @@ final class ChannelsReadSupport {
String sql = """
SELECT msg_sub_type
FROM blocks
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
AND msg_type = ?
AND to_bch_name = ?
AND to_block_number = ?
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_GetChannelMessages_Handler.java
+2 -2
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelMe
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelMessages_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.MsgSubType;
import utils.blockchain.BlockchainNameUtil;
import blockchain.body.CreateChannelBody;
@@ -39,7 +39,7 @@ public class Net_GetChannelMessages_Handler implements JsonMessageHandler {
boolean asc = req.getSort() == null || !"desc".equalsIgnoreCase(req.getSort());
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String viewerLogin = ctx != null ? ctx.getLogin() : null;
if (viewerLogin == null || viewerLogin.isBlank()) {
viewerLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin());
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_GetChannelsCounters_Handler.java
+6 -6
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelsC
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetChannelsCounters_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.MsgSubType;
import java.sql.Connection;
@@ -26,7 +26,7 @@ public class Net_GetChannelsCounters_Handler implements JsonMessageHandler {
if (req.getLogin() == null || req.getLogin().isBlank()) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "bad_fields", "Некорректные поля: login");
}
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String canonicalLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin().trim());
if (canonicalLogin == null) {
return NetExceptionResponseFactory.error(req, 404, "user_not_found", "Пользователь не найден");
@@ -52,7 +52,7 @@ public class Net_GetChannelsCounters_Handler implements JsonMessageHandler {
String sql = """
SELECT COUNT(*)
FROM connections_state
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
AND rel_type = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -68,7 +68,7 @@ public class Net_GetChannelsCounters_Handler implements JsonMessageHandler {
String sql = """
SELECT COUNT(*)
FROM channel_names_state
WHERE owner_login = ? COLLATE NOCASE
WHERE LOWER(owner_login) = LOWER(?)
AND channel_type_code = ?
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -81,7 +81,7 @@ public class Net_GetChannelsCounters_Handler implements JsonMessageHandler {
}
private int countMyChannels(Connection c, String login) throws Exception {
String bchCountSql = "SELECT COUNT(*) FROM blockchain_state WHERE login = ? COLLATE NOCASE";
String bchCountSql = "SELECT COUNT(*) FROM blockchain_state WHERE LOWER(login) = LOWER(?)";
int stories = 0;
try (PreparedStatement ps = c.prepareStatement(bchCountSql)) {
ps.setString(1, login);
@@ -92,7 +92,7 @@ public class Net_GetChannelsCounters_Handler implements JsonMessageHandler {
String namedSql = """
SELECT COUNT(*)
FROM channel_names_state
WHERE owner_login = ? COLLATE NOCASE
WHERE LOWER(owner_login) = LOWER(?)
AND channel_type_code IN (1,100,200)
""";
int named = 0;
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_GetGroupDialog_Handler.java
+3 -4
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetGroupDial
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetGroupDialog_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.channels.ChannelNameRules;
import java.sql.Connection;
@@ -33,7 +33,7 @@ public class Net_GetGroupDialog_Handler implements JsonMessageHandler {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "bad_fields", "Некорректные поля group");
}
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
Net_GetGroupDialog_Response resp = new Net_GetGroupDialog_Response();
resp.setOp(req.getOp());
resp.setRequestId(req.getRequestId());
@@ -115,7 +115,7 @@ public class Net_GetGroupDialog_Handler implements JsonMessageHandler {
String canonicalLogin = ChannelsReadSupport.canonicalLogin(c, ref.memberLogin);
if (canonicalLogin == null || canonicalLogin.isBlank()) return null;
String bchSql = "SELECT blockchain_name FROM blockchain_state WHERE login = ? COLLATE NOCASE ORDER BY blockchain_name LIMIT 1";
String bchSql = "SELECT blockchain_name FROM blockchain_state WHERE LOWER(login) = LOWER(?) ORDER BY blockchain_name LIMIT 1";
String memberBch = null;
try (PreparedStatement ps = c.prepareStatement(bchSql)) {
ps.setString(1, canonicalLogin);
@@ -214,4 +214,3 @@ public class Net_GetGroupDialog_Handler implements JsonMessageHandler {
String text;
}
}
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_GetMessageThread_Handler.java
+2 -2
View File
@@ -12,7 +12,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_GetMessageTh
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -35,7 +35,7 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
int depthDown = req.getDepthDown() == null ? 2 : Math.max(0, req.getDepthDown());
int childLimit = req.getLimitChildrenPerNode() == null ? 50 : Math.max(1, req.getLimitChildrenPerNode());
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String viewerLogin = ctx != null ? ctx.getLogin() : null;
if (viewerLogin == null || viewerLogin.isBlank()) {
viewerLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin());
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_ListGroupChats200_Handler.java
+3 -4
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_ListGroupCha
import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_ListGroupChats200_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -27,7 +27,7 @@ public class Net_ListGroupChats200_Handler implements JsonMessageHandler {
if (req.getLogin() == null || req.getLogin().isBlank()) {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "bad_fields", "Некорректные поля: login");
}
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String canonicalLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin().trim());
if (canonicalLogin == null) {
return NetExceptionResponseFactory.error(req, 404, "user_not_found", "Пользователь не найден");
@@ -59,7 +59,7 @@ public class Net_ListGroupChats200_Handler implements JsonMessageHandler {
) m
ON m.owner_bch_name = s.owner_bch_name
AND m.channel_root_block_number = s.channel_root_block_number
WHERE s.owner_login = ? COLLATE NOCASE
WHERE LOWER(s.owner_login) = LOWER(?)
ORDER BY s.updated_at_ms DESC, s.channel_root_block_number DESC
""";
List<Net_ListGroupChats200_Response.Row> out = new ArrayList<>();
@@ -83,4 +83,3 @@ public class Net_ListGroupChats200_Handler implements JsonMessageHandler {
return out;
}
}
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_ListSubscriptionsFeed_Handler.java
+2 -2
View File
@@ -11,7 +11,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_ListSubscrip
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -29,7 +29,7 @@ public class Net_ListSubscriptionsFeed_Handler implements JsonMessageHandler {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "bad_fields", "Некорректные поля: login");
}
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String canonicalLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin().trim());
if (canonicalLogin == null) {
return NetExceptionResponseFactory.error(req, 404, "user_not_found", "Пользователь не найден");
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/channels/Net_MarkChannelMessagesSeen_Handler.java
+4 -4
View File
@@ -11,7 +11,7 @@ import server.logic.ws_protocol.JSON.handlers.channels.entyties.Net_MarkChannelM
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import shine.db.DbController;
import java.sql.Connection;
import java.sql.PreparedStatement;
@@ -37,7 +37,7 @@ public class Net_MarkChannelMessagesSeen_Handler implements JsonMessageHandler {
return ok;
}
try (Connection c = SqliteDbController.getInstance().getConnection()) {
try (Connection c = DbController.getInstance().getConnection()) {
String viewerLogin = ctx != null ? ctx.getLogin() : null;
if (viewerLogin == null || viewerLogin.isBlank()) {
viewerLogin = ChannelsReadSupport.canonicalLogin(c, req.getLogin());
@@ -64,9 +64,10 @@ public class Net_MarkChannelMessagesSeen_Handler implements JsonMessageHandler {
""".formatted(strictChannelMatch ? "AND line_code = ?" : "");
String insertSql = """
INSERT OR IGNORE INTO message_views_state (
INSERT INTO message_views_state (
viewer_login, to_bch_name, to_block_number, to_block_hash, first_seen_at_ms
) VALUES (?, ?, ?, ?, ?)
ON CONFLICT DO NOTHING
""";
int seenAccepted = 0;
@@ -132,4 +133,3 @@ public class Net_MarkChannelMessagesSeen_Handler implements JsonMessageHandler {
}
}
}
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/connections/Net_AddCloseFriend_Handler.java
+5 -4
View File
@@ -31,7 +31,7 @@ public class Net_AddCloseFriend_Handler implements JsonMessageHandler {
return NetExceptionResponseFactory.error(req, WireCodes.Status.BAD_REQUEST, "BAD_FIELDS", "Нельзя добавить себя");
}
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection()) {
try (Connection c = shine.db.DbController.getInstance().getConnection()) {
String canonicalTo = findCanonicalLogin(c, toLogin);
if (canonicalTo == null) {
return NetExceptionResponseFactory.error(req, 404, "USER_NOT_FOUND", "Пользователь не найден");
@@ -58,7 +58,7 @@ public class Net_AddCloseFriend_Handler implements JsonMessageHandler {
}
private String findCanonicalLogin(Connection c, String login) throws Exception {
String sql = "SELECT login FROM solana_users WHERE login = ? COLLATE NOCASE LIMIT 1";
String sql = "SELECT login FROM solana_users WHERE LOWER(login) = LOWER(?) LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
@@ -68,7 +68,7 @@ public class Net_AddCloseFriend_Handler implements JsonMessageHandler {
}
private String findPrimaryBlockchain(Connection c, String login) throws Exception {
String sql = "SELECT blockchain_name FROM blockchain_state WHERE login = ? COLLATE NOCASE ORDER BY blockchain_name LIMIT 1";
String sql = "SELECT blockchain_name FROM blockchain_state WHERE LOWER(login) = LOWER(?) ORDER BY blockchain_name LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, login);
try (ResultSet rs = ps.executeQuery()) {
@@ -82,10 +82,11 @@ public class Net_AddCloseFriend_Handler implements JsonMessageHandler {
String toLogin,
String toBchName) throws Exception {
String sql = """
INSERT OR IGNORE INTO connections_state (
INSERT INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
)
VALUES (?, ?, ?, ?, ?, ?)
ON CONFLICT (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/connections/Net_GetFriendsLists_Handler.java
+3 -3
View File
@@ -11,7 +11,7 @@ import server.logic.ws_protocol.JSON.handlers.connections.entyties.Net_GetFriend
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.MsgSubType;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.dao.ConnectionsStateDAO;
import java.sql.Connection;
@@ -51,7 +51,7 @@ public class Net_GetFriendsLists_Handler implements JsonMessageHandler {
final String loginAnyCase = req.getLogin().trim();
try {
SqliteDbController db = SqliteDbController.getInstance();
DbController db = DbController.getInstance();
ConnectionsStateDAO dao = ConnectionsStateDAO.getInstance();
try (Connection c = db.getConnection()) {
@@ -100,7 +100,7 @@ public class Net_GetFriendsLists_Handler implements JsonMessageHandler {
String sql = """
SELECT login
FROM solana_users
WHERE login = ? COLLATE NOCASE
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/connections/Net_GetUserConnectionsGraph_Handler.java
+5 -5
View File
@@ -39,7 +39,7 @@ public class Net_GetUserConnectionsGraph_Handler implements JsonMessageHandler {
}
}
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection()) {
try (Connection c = shine.db.DbController.getInstance().getConnection()) {
String canonicalLogin = findCanonicalLogin(c, requestedLogin);
if (canonicalLogin == null) {
return NetExceptionResponseFactory.error(req, 404, "USER_NOT_FOUND", "Пользователь не найден");
@@ -113,7 +113,7 @@ public class Net_GetUserConnectionsGraph_Handler implements JsonMessageHandler {
}
private String findCanonicalLogin(Connection c, String loginAnyCase) throws Exception {
String sql = "SELECT login FROM solana_users WHERE login = ? COLLATE NOCASE LIMIT 1";
String sql = "SELECT login FROM solana_users WHERE LOWER(login) = LOWER(?) LIMIT 1";
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, loginAnyCase);
try (ResultSet rs = ps.executeQuery()) {
@@ -170,9 +170,9 @@ public class Net_GetUserConnectionsGraph_Handler implements JsonMessageHandler {
MAX(CASE WHEN up.param = 'ava' THEN up.value END) AS avatar_value
FROM solana_users su
LEFT JOIN users_params up
ON up.login = su.login COLLATE NOCASE
ON LOWER(up.login) = LOWER(su.login)
AND up.param IN ('gender', 'official', 'shine', 'ava')
WHERE su.login COLLATE NOCASE IN (%s)
WHERE LOWER(su.login) IN (%s)
GROUP BY su.login
ORDER BY su.login
""".formatted(String.join(", ", placeholders));
@@ -180,7 +180,7 @@ public class Net_GetUserConnectionsGraph_Handler implements JsonMessageHandler {
try (PreparedStatement ps = c.prepareStatement(sql)) {
int i = 1;
for (String login : logins) {
ps.setString(i, login);
ps.setString(i, normKey(login));
i += 1;
}
try (ResultSet rs = ps.executeQuery()) {
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/connections/Net_ListContacts_Handler.java
+1 -1
View File
@@ -22,7 +22,7 @@ public class Net_ListContacts_Handler implements JsonMessageHandler {
return NetExceptionResponseFactory.error(req, WireCodes.Status.UNVERIFIED, "NOT_AUTHENTICATED", "Требуется авторизация");
}
try (Connection c = shine.db.SqliteDbController.getInstance().getConnection()) {
try (Connection c = shine.db.DbController.getInstance().getConnection()) {
List<String> contacts = ConnectionsStateDAO.getInstance().listOutgoingByRelTypeCanonical(c, ctx.getLogin(), MsgSubType.CONNECTION_CONTACT);
Net_ListContacts_Response resp = new Net_ListContacts_Response();
resp.setOp(req.getOp());
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/userParams/Net_GetUserParam_Handler.java
+2 -2
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_GetUserPar
import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_GetUserParam_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.dao.UserParamsDAO;
import shine.db.entities.UserParamEntry;
@@ -48,7 +48,7 @@ public class Net_GetUserParam_Handler implements JsonMessageHandler {
String param = req.getParam().trim();
try {
SqliteDbController db = SqliteDbController.getInstance();
DbController db = DbController.getInstance();
UserParamsDAO dao = UserParamsDAO.getInstance();
try (Connection c = db.getConnection()) {
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/userParams/Net_ListUserParams_Handler.java
+2 -2
View File
@@ -10,7 +10,7 @@ import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_ListUserPa
import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_ListUserParams_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.dao.SolanaUsersDAO;
import shine.db.dao.UserParamsDAO;
import shine.db.entities.SolanaUserEntry;
@@ -50,7 +50,7 @@ public class Net_ListUserParams_Handler implements JsonMessageHandler {
String login = req.getLogin().trim();
try {
SqliteDbController db = SqliteDbController.getInstance();
DbController db = DbController.getInstance();
UserParamsDAO dao = UserParamsDAO.getInstance();
List<UserParamEntry> entries;
SHiNE-server/shine-server-net-protocol/src/main/java/server/logic/ws_protocol/JSON/handlers/userParams/Net_UpsertUserParam_Handler.java
+2 -2
View File
@@ -11,7 +11,7 @@ import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_UpsertUser
import server.logic.ws_protocol.JSON.handlers.userParams.entyties.Net_UpsertUserParam_Response;
import server.logic.ws_protocol.JSON.utils.NetExceptionResponseFactory;
import server.logic.ws_protocol.WireCodes;
import shine.db.SqliteDbController;
import shine.db.DbController;
import shine.db.dao.SolanaUsersDAO;
import shine.db.dao.UserParamsDAO;
import shine.db.entities.SolanaUserEntry;
@@ -104,7 +104,7 @@ public class Net_UpsertUserParam_Handler implements JsonMessageHandler {
}
// ---------------- DB checks + upsert ----------------
SqliteDbController db = SqliteDbController.getInstance();
DbController db = DbController.getInstance();
SolanaUsersDAO usersDAO = SolanaUsersDAO.getInstance();
UserParamsDAO paramsDAO = UserParamsDAO.getInstance();
SHiNE-server/src/main/resources/application.properties
+3
View File
@@ -1,5 +1,8 @@
server.1port=7070
db.path=data/shine.sqlite
db.url=
db.user=
db.password=
server.SHiNE.login=shineupme
solana.cluster=mainnet-beta
solana.rpcUrl=https://api.mainnet-beta.solana.com
VERSION.properties
+1 -1
View File
@@ -1,2 +1,2 @@
client.version=1.2.354
server.version=1.2.319
server.version=1.2.320
deploy/SOLANA_USERS_SYNC_SERVER_SETUP.md
+70 -90
View File
@@ -1,116 +1,96 @@
# Интеграция синхронизации `shine_users` в основной сервер
# Интеграция Solana users sync в сервер SHiNE
Этот документ описывает, что нужно для встраивания Solana sync-модуля пользовательских PDA в основной SHiNE-server.
Этот документ фиксирует серверную конфигурацию модуля синхронизации `shine_users`
и базовую инициализацию новой PostgreSQL runtime-схемы сервера.
Основной архитектурный документ:
## Что уже есть
- [docs/Solana/SOLANA_USERS_SYNC_MODULE_DESIGN.md](/home/ai/work/SHiNE/SHiNE-server-sha256/SHiNE-product/docs/Solana/SOLANA_USERS_SYNC_MODULE_DESIGN.md)
## Что уже готово
Отдельный модуль `sync-solana` уже умеет:
- подключаться к Solana RPC и WebSocket;
- вычислять `users_economy_config_pda`;
- хранить checkpoint синхронизации в PostgreSQL;
- читать историю через `getSignaturesForAddress(users_economy_config_pda)`;
- поддерживать realtime через websocket;
- выполнять страховочный periodic poll раз в 5 минут;
- хранить:
- основной сервер запускает `SolanaUsersSyncStartupService` до продолжения startup;
- модуль синхронизации держит актуальными таблицы:
- `solana_sync_state`
- `solana_sync_tx_history`
- `solana_user_pda_current`
- `solana_user_pda_history`
- блокировать дальнейший startup до входа в `READY`.
- источник истины по пользовательским PDA: `solana_user_pda_current`.
## Что нужно перенести в основной сервер
## Что должно быть настроено в `application.properties`
Из `sync-solana` в сервер нужно перенести рабочие классы:
- `sync-solana/src/main/java/sync-solana/config/`
- `sync-solana/src/main/java/sync-solana/service/`
- `sync-solana/src/main/java/sync-solana/source/`
- `sync-solana/src/main/java/sync-solana/source/rpc/`
- `sync-solana/src/main/java/sync-solana/storage/postgres/`
- `sync-solana/src/main/java/sync-solana/codec/`
- `sync-solana/src/main/java/sync-solana/model/`
- `sync-solana/src/main/java/sync-solana/util/`
`Main.java` нужен только как reference для bootstrap и как отдельный `main` в сервере уже не понадобится.
Рекомендуемый вариант:
- оформить это как отдельный Gradle submodule внутри `SHiNE-server`;
- запускать его из server startup как lifecycle-сервис.
## Порядок запуска в сервере
При старте основного сервера последовательность должна быть такой:
1. прочитать общий server config;
2. создать Solana users sync service;
3. вызвать `start()`;
4. вызвать `awaitReady()`;
5. только после этого продолжать остальной startup сервера:
- синхронизацию с другими нодами;
- запуск WS/HTTP;
- остальную серверную инициализацию.
Если Solana initial sync не дошёл до `READY`, startup сервера должен считаться неуспешным.
## Переменные окружения сервера
На сервере должны быть доступны:
```text
SOLANA_RPC_URL=
SOLANA_WS_URL=
SOLANA_PROGRAM_ID=SHiNEPr1APdAgNBteUyBXcNovaHctpSjUu8oH2ZJdN6
SYNC_POLL_INTERVAL_SECONDS=300
```properties
solana.users.sync.enabled=true
solana.users.sync.rpcUrl=https://api.devnet.solana.com
solana.users.sync.wsUrl=wss://api.devnet.solana.com/
solana.users.sync.databaseUrl=jdbc:postgresql://127.0.0.1:5432/shine_server_db
solana.users.sync.dbUser=shine_server
solana.users.sync.dbPassword=CHANGE_ME
solana.users.sync.pollIntervalSeconds=300
```
Для PostgreSQL sync-модуль может использовать уже существующую server PostgreSQL-конфигурацию, если сервер уже предоставляет:
Замечания:
- `solana.users.sync.enabled=true` обязателен, иначе сервер пропустит startup sync.
- `solana.users.sync.databaseUrl` должен указывать на ту же PostgreSQL БД, где создана серверная runtime-схема.
- `solana.users.sync.wsUrl` задаётся явно, автоматически из `rpcUrl` не строится.
## Как создать пустую PostgreSQL runtime БД
SQL-скрипт инициализации лежит в:
```text
DATABASE_URL=
PGUSER=
PGPASSWORD=
SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
```
Если в сервере используется другая схема конфигов, нужно сделать адаптер на уровне server config, а не менять саму логику sync.
Пример запуска:
## Логи
```bash
psql \
"postgresql://shine_server:CHANGE_ME@127.0.0.1:5432/shine_server_db" \
-f SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
```
Sync-модуль должен писать в общие server logs через тот же `slf4j/logback`, что и основной сервер.
Скрипт:
Минимум, который должен быть виден в логах:
- создаёт таблицу версии схемы `db_schema_version`;
- ставит `schema_version = 1`;
- создаёт таблицы sync-модуля Solana users;
- создаёт server runtime tables;
- не создаёт legacy SQLite-таблицы `solana_users` и `direct_messages`;
- использует `signed_messages` как единственную таблицу серверных DM.
- старт sync-модуля;
- вход в `READY`;
- realtime sync;
- periodic poll;
- reconnect websocket;
- fallback на full snapshot;
- ошибки RPC/WS/DB.
## Как поднять PostgreSQL в Docker
## Что потребуется по deploy
Шаблоны лежат в:
Отдельных deploy-скриптов для sync-модуля не требуется, если он встроен в основной server jar.
```text
deploy/postgres/docker-compose.yml.example
deploy/postgres/.env.example
```
По deploy нужно:
Минимальная последовательность:
- обновить server env/override-конфиг новыми переменными `SOLANA_*` и `SYNC_POLL_INTERVAL_SECONDS`;
- убедиться, что на сервере доступен PostgreSQL, в который модуль будет писать свои таблицы;
- при необходимости описать новые env в документации конкретного server-контура.
```bash
mkdir -p /home/player/SHiNE/postgres
cp deploy/postgres/.env.example /home/player/SHiNE/postgres/.env
cp deploy/postgres/docker-compose.yml.example /home/player/SHiNE/postgres/docker-compose.yml
cd /home/player/SHiNE/postgres
docker compose up -d
```
## Что ещё проверить после интеграции
После старта контейнера:
После встраивания в основной сервер нужно отдельно проверить:
```bash
cp /path/to/SHiNE-product/application.properties ./application.properties
# задать db.url/db.user/db.password и запустить сервер
```
- startup сервера с ожиданием `awaitReady()`;
- создание таблиц в server PostgreSQL;
- initial sync после пустой БД;
- restart recovery после уже существующего checkpoint;
- realtime update через websocket;
- periodic poll без новых транзакций;
- fallback на full snapshot при потере history anchor.
Сложность тут низкая:
- сам Docker Postgres поднимается просто;
- сервер сам создаёт runtime schema v1, если БД пустая и в ней нет `db_schema_version`;
- основная аккуратность нужна в паролях, bind-mount каталоге и backup;
- для SHiNE важно не открывать `5432` наружу, только `127.0.0.1:5432`.
## Что пока остаётся как есть
- `sync_servers` сервер по-прежнему загружает из server PDA в Solana;
- старый SQLite runtime код ещё может лежать в репозитории, но новая runtime-схема на него не должна опираться;
- механический перенос DAO и runtime SQL на PostgreSQL делается отдельным шагом после утверждения схемы `v1`.
deploy/postgres/.env.example
+7
View File
@@ -0,0 +1,7 @@
POSTGRES_SUPERUSER_DB=postgres
POSTGRES_SUPERUSER=postgres
POSTGRES_SUPERUSER_PASSWORD=CHANGE_ME_SUPERUSER
SHINE_APP_DB=shine_server_db
SHINE_APP_USER=shine_server
SHINE_APP_PASSWORD=CHANGE_ME_APP
SHINE_POSTGRES_DATA_DIR=/home/player/SHiNE/postgres/shine_server_db
deploy/postgres/docker-compose.yml.example
+24
View File
@@ -0,0 +1,24 @@
services:
shine-postgres:
image: postgres:18
container_name: shine-postgres
restart: unless-stopped
env_file:
- .env
environment:
POSTGRES_DB: ${POSTGRES_SUPERUSER_DB}
POSTGRES_USER: ${POSTGRES_SUPERUSER}
POSTGRES_PASSWORD: ${POSTGRES_SUPERUSER_PASSWORD}
PGDATA: /var/lib/postgresql/data/pgdata
ports:
- "127.0.0.1:5432:5432"
volumes:
- ${SHINE_POSTGRES_DATA_DIR}:/var/lib/postgresql/data
healthcheck:
test:
- CMD-SHELL
- pg_isready -U "$${POSTGRES_SUPERUSER}" -d "$${POSTGRES_SUPERUSER_DB}"
interval: 10s
timeout: 5s
retries: 10
start_period: 20s
docs/Solana/SOLANA_USERS_SYNC_MODULE_DESIGN.md
+6
View File
@@ -15,6 +15,12 @@
На текущем этапе модуль должен запускаться как отдельный Java-процесс со своим `main`, но внутренняя структура должна быть такой, чтобы потом его можно было перенести в сервер как обычный lifecycle-сервис.
На дату `2026-07-24` в основном сервере SHiNE уже есть серверная интеграция startup-уровня:
- сервер запускает sync-модуль до продолжения собственного startup;
- server startup ждёт входа модуля в состояние `READY`;
- актуальный источник истины по пользователям для нового PostgreSQL runtime-слоя: `solana_user_pda_current`.
---
## Базовая идея
docs/libs/shine-server-bd/POSTGRES_RUNTIME_SCHEMA_V1.md
+79
View File
@@ -0,0 +1,79 @@
# PostgreSQL runtime schema v1
Дата фиксации: `2026-07-24`
## Назначение
Это целевая серверная runtime-схема PostgreSQL для SHiNE без опоры на SQLite.
Схема `v1` нужна как стартовая точка большого механического переноса DAO и runtime-запросов
с существующей SQLite-логики на PostgreSQL.
## Ключевые решения
- Источник истины по пользователям: `solana_user_pda_current`.
- Legacy-таблица `solana_users` в новой схеме не создаётся.
- Legacy-таблица `direct_messages` в новой схеме не создаётся.
- Основная таблица серверных личных сообщений: `signed_messages`.
- Таблица `blockchain_state` сохраняется как runtime-state таблица сервера:
она не является identity-слоем и не мигрируется как legacy SQLite data.
- Триггеры по `blocks` сохраняются и переписываются под PostgreSQL.
## Таблицы sync-модуля Solana users
- `solana_sync_state`
- `solana_sync_tx_history`
- `solana_user_pda_current`
- `solana_user_pda_history`
## Таблицы server runtime
- `db_schema_version`
- `active_sessions`
- `esp_pairing_settings`
- `esp_pairing_requests`
- `users_params`
- `ip_geo_cache`
- `test_free_avatar_uploads`
- `sync_servers`
- `blockchain_state`
- `blocks`
- `connections_state`
- `message_stats`
- `reactions_state`
- `channel_names_state`
- `chat200_state`
- `chat200_members_state`
- `user_push_tokens`
- `signed_direct_message_replay`
- `signed_direct_messages_history`
- `signed_messages`
- `signed_message_session_delivery`
## Триггеры
Схема `v1` уже включает PostgreSQL-версии триггеров:
- `trg_blocks_line_integrity_bi`
- `trg_blocks_connection_state_ai`
- `trg_blocks_message_stats_like_ai`
- `trg_blocks_message_stats_reply_ai`
- `trg_blocks_edit_apply_ai`
## Что не входит в v1
- удаление legacy SQLite-классов;
- переименование Java DAO/классов `*V2` в runtime-коде;
- перенос прямых SQL-запросов из хэндлеров в DAO/service;
- переключение всего runtime-кода на новый `DbProvider`.
Это отдельные механические шаги поверх уже утверждённой схемы.
## Инициализация пустой БД
Если сервер подключается к PostgreSQL через `db.url=jdbc:postgresql:...` и в выбранной БД ещё нет таблицы `db_schema_version`,
он сам автоматически накатывает `schema_v1.sql` из classpath-ресурса:
- ресурс: `shine-server-db/src/main/resources/postgres/schema_v1.sql`
- признак пустой схемы: отсутствует `db_schema_version`
- стартовая версия схемы: `1`