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

Синхронизировать регистр логинов и доработать UI каналов

Browse Source
This commit is contained in:
AidarKC
2026-08-19 14:30:29 +04:00
parent e8a4713f6e
commit ce30b77328
29 changed files with 6729 additions and 815 deletions
Download Patch File Download Diff File Expand all files Collapse all files
SHiNE-server/shine-server-db/src/main/java/shine/db/DatabaseInitializer.java
+24
View File
@@ -21,10 +21,18 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_2 = 2;
public static final int SCHEMA_VERSION_3 = 3;
public static final int SCHEMA_VERSION_4 = 4;
public static final int SCHEMA_VERSION_5 = 5;
public static final int SCHEMA_VERSION_6 = 6;
public static final int SCHEMA_VERSION_7 = 7;
public static final int SCHEMA_VERSION_8 = 8;
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
public static final String POSTGRES_MIGRATION_V2_RESOURCE = "postgres/migration_v2.sql";
public static final String POSTGRES_MIGRATION_V3_RESOURCE = "postgres/migration_v3.sql";
public static final String POSTGRES_MIGRATION_V4_RESOURCE = "postgres/migration_v4.sql";
public static final String POSTGRES_MIGRATION_V5_RESOURCE = "postgres/migration_v5.sql";
public static final String POSTGRES_MIGRATION_V6_RESOURCE = "postgres/migration_v6.sql";
public static final String POSTGRES_MIGRATION_V7_RESOURCE = "postgres/migration_v7.sql";
public static final String POSTGRES_MIGRATION_V8_RESOURCE = "postgres/migration_v8.sql";
private DatabaseInitializer() {}
@@ -100,6 +108,22 @@ public final class DatabaseInitializer {
}
if (currentVersion < SCHEMA_VERSION_4) {
runSqlScript(conn, POSTGRES_MIGRATION_V4_RESOURCE);
currentVersion = SCHEMA_VERSION_4;
}
if (currentVersion < SCHEMA_VERSION_5) {
runSqlScript(conn, POSTGRES_MIGRATION_V5_RESOURCE);
currentVersion = SCHEMA_VERSION_5;
}
if (currentVersion < SCHEMA_VERSION_6) {
runSqlScript(conn, POSTGRES_MIGRATION_V6_RESOURCE);
currentVersion = SCHEMA_VERSION_6;
}
if (currentVersion < SCHEMA_VERSION_7) {
runSqlScript(conn, POSTGRES_MIGRATION_V7_RESOURCE);
currentVersion = SCHEMA_VERSION_7;
}
if (currentVersion < SCHEMA_VERSION_8) {
runSqlScript(conn, POSTGRES_MIGRATION_V8_RESOURCE);
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/CurrentUsersDAO.java
+4 -4
View File
@@ -49,7 +49,7 @@ public final class CurrentUsersDAO {
String sql = """
SELECT 1
FROM %s
WHERE LOWER(login) = LOWER(?)
WHERE normalized_login = LOWER(BTRIM(?))
LIMIT 1
""".formatted(CurrentUsersSql.usersSubquery("su"));
@@ -104,7 +104,7 @@ public final class CurrentUsersDAO {
blockchain_key,
client_key
FROM %s
WHERE LOWER(login) = LOWER(?)
WHERE normalized_login = LOWER(BTRIM(?))
""".formatted(CurrentUsersSql.usersSubquery("su"));
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -167,7 +167,7 @@ public final class CurrentUsersDAO {
blockchain_key,
client_key
FROM %s
WHERE LOWER(login) LIKE ?
WHERE normalized_login LIKE LOWER(BTRIM(?))
AND (? IS NULL OR is_server = ?)
ORDER BY login
LIMIT 5
@@ -176,7 +176,7 @@ public final class CurrentUsersDAO {
List<CurrentUserEntry> result = new ArrayList<>();
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, prefix.toLowerCase() + "%");
ps.setString(1, prefix.trim() + "%");
if (isServer == null) {
ps.setNull(2, Types.BOOLEAN);
ps.setNull(3, Types.BOOLEAN);
SHiNE-server/shine-server-db/src/main/java/shine/db/sql/CurrentUsersSql.java
+1
View File
@@ -12,6 +12,7 @@ public final class CurrentUsersSql {
(
SELECT
current_users.login AS login,
current_users.normalized_login AS normalized_login,
current_users.blockchain_name AS blockchain_name,
current_users.client_key AS solana_key,
current_users.blockchain_key AS blockchain_key,
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v5.sql
+83
View File
@@ -0,0 +1,83 @@
BEGIN;
ALTER TABLE solana_user_pda_current
ADD COLUMN IF NOT EXISTS normalized_login TEXT;
UPDATE solana_user_pda_current
SET normalized_login = LOWER(BTRIM(login))
WHERE normalized_login IS NULL
OR normalized_login <> LOWER(BTRIM(login));
ALTER TABLE solana_user_pda_current
ALTER COLUMN normalized_login SET NOT NULL;
CREATE UNIQUE INDEX IF NOT EXISTS uq_solana_user_pda_current_normalized_login
ON solana_user_pda_current(normalized_login);
CREATE INDEX IF NOT EXISTS idx_user_pda_current_normalized_login
ON solana_user_pda_current(normalized_login);
WITH bad_signed_messages AS (
SELECT DISTINCT sm.message_key
FROM signed_messages sm
LEFT JOIN solana_user_pda_current u_from
ON u_from.normalized_login = LOWER(BTRIM(sm.from_login))
LEFT JOIN solana_user_pda_current u_to
ON u_to.normalized_login = LOWER(BTRIM(sm.to_login))
WHERE (u_from.login IS NOT NULL AND sm.from_login <> u_from.login)
OR (u_to.login IS NOT NULL AND sm.to_login <> u_to.login)
)
DELETE FROM signed_message_session_delivery d
USING bad_signed_messages bad
WHERE d.message_key = bad.message_key;
WITH bad_signed_messages AS (
SELECT DISTINCT sm.message_key
FROM signed_messages sm
LEFT JOIN solana_user_pda_current u_from
ON u_from.normalized_login = LOWER(BTRIM(sm.from_login))
LEFT JOIN solana_user_pda_current u_to
ON u_to.normalized_login = LOWER(BTRIM(sm.to_login))
WHERE (u_from.login IS NOT NULL AND sm.from_login <> u_from.login)
OR (u_to.login IS NOT NULL AND sm.to_login <> u_to.login)
)
DELETE FROM signed_messages sm
USING bad_signed_messages bad
WHERE sm.message_key = bad.message_key;
DELETE FROM signed_direct_messages_history h
USING solana_user_pda_current u_from,
solana_user_pda_current u_to
WHERE u_from.normalized_login = LOWER(BTRIM(h.from_login))
AND u_to.normalized_login = LOWER(BTRIM(h.to_login))
AND (h.from_login <> u_from.login OR h.to_login <> u_to.login);
DELETE FROM signed_direct_message_replay r
USING solana_user_pda_current u_from
WHERE u_from.normalized_login = LOWER(BTRIM(r.from_login))
AND r.from_login <> u_from.login;
DO $$
BEGIN
IF EXISTS (
SELECT 1
FROM information_schema.tables
WHERE table_schema = current_schema()
AND table_name = 'direct_messages'
) THEN
DELETE FROM direct_messages d
USING solana_user_pda_current u_from,
solana_user_pda_current u_to
WHERE u_from.normalized_login = LOWER(BTRIM(d.from_login))
AND u_to.normalized_login = LOWER(BTRIM(d.to_login))
AND (d.from_login <> u_from.login OR d.to_login <> u_to.login);
END IF;
END $$;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 5, 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;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v6.sql
+21
View File
@@ -0,0 +1,21 @@
BEGIN;
ALTER TABLE signed_messages
DROP CONSTRAINT IF EXISTS signed_messages_from_login_fkey;
ALTER TABLE signed_messages
DROP CONSTRAINT IF EXISTS signed_messages_to_login_fkey;
ALTER TABLE signed_direct_messages_history
DROP CONSTRAINT IF EXISTS signed_direct_messages_history_from_login_fkey;
ALTER TABLE signed_direct_messages_history
DROP CONSTRAINT IF EXISTS signed_direct_messages_history_to_login_fkey;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 6, 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;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v7.sql
+16
View File
@@ -0,0 +1,16 @@
BEGIN;
ALTER TABLE blocks
DROP CONSTRAINT IF EXISTS blocks_login_fkey;
ALTER TABLE blocks
ADD CONSTRAINT blocks_login_fkey
FOREIGN KEY (login) REFERENCES solana_user_pda_current(normalized_login);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 7, 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;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v8.sql
+16
View File
@@ -0,0 +1,16 @@
BEGIN;
ALTER TABLE connections_state
DROP CONSTRAINT IF EXISTS connections_state_login_fkey;
ALTER TABLE connections_state
ADD CONSTRAINT connections_state_login_fkey
FOREIGN KEY (login) REFERENCES solana_user_pda_current(normalized_login);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 8, 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;
COMMIT;
SHiNE-server/shine-server-db/src/main/resources/postgres/schema_v1.sql
+14 -7
View File
@@ -22,7 +22,7 @@ CREATE TABLE IF NOT EXISTS db_schema_version (
);
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 3, CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT))
VALUES (1, 8, 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;
@@ -74,6 +74,7 @@ CREATE INDEX IF NOT EXISTS idx_sync_tx_history_login
CREATE TABLE IF NOT EXISTS solana_user_pda_current (
pda_address TEXT PRIMARY KEY,
login TEXT NOT NULL UNIQUE,
normalized_login TEXT NOT NULL,
record_number INTEGER NOT NULL,
slot BIGINT NOT NULL,
last_tx_signature TEXT NOT NULL,
@@ -109,6 +110,12 @@ CREATE TABLE IF NOT EXISTS solana_user_pda_current (
CREATE INDEX IF NOT EXISTS idx_user_pda_current_slot
ON solana_user_pda_current(slot);
CREATE UNIQUE INDEX IF NOT EXISTS uq_solana_user_pda_current_normalized_login
ON solana_user_pda_current(normalized_login);
CREATE INDEX IF NOT EXISTS idx_user_pda_current_normalized_login
ON solana_user_pda_current(normalized_login);
CREATE TABLE IF NOT EXISTS user_access_servers_current (
user_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
server_login TEXT NOT NULL REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
@@ -440,7 +447,7 @@ 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),
login TEXT NOT NULL REFERENCES solana_user_pda_current(normalized_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,
@@ -470,7 +477,7 @@ 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),
login TEXT NOT NULL REFERENCES solana_user_pda_current(normalized_login),
rel_type INTEGER NOT NULL,
to_login TEXT NOT NULL,
to_bch_name TEXT NOT NULL,
@@ -624,8 +631,8 @@ CREATE INDEX IF NOT EXISTS idx_signed_dm_replay_created
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),
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
target_mode INTEGER NOT NULL,
target_session_id TEXT,
message_type INTEGER NOT NULL,
@@ -642,8 +649,8 @@ 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),
from_login TEXT NOT NULL,
to_login TEXT NOT NULL,
time_ms BIGINT NOT NULL,
nonce BIGINT NOT NULL,
message_type INTEGER NOT NULL,
SHiNE-server/shine-server-solana-users-sync/src/main/java/sync/storage/postgres/PostgresStorageRepository.java
+57 -31
View File
@@ -8,6 +8,7 @@ import sync.codec.ShineUsersCodec;
import java.sql.*;
import java.util.*;
import java.util.Locale;
public final class PostgresStorageRepository
implements AutoCloseable {
@@ -465,7 +466,7 @@ public final class PostgresStorageRepository
String sql =
"INSERT INTO solana_user_pda_current (" +
"pda_address, login, record_number, slot, last_tx_signature, " +
"pda_address, login, normalized_login, record_number, slot, last_tx_signature, " +
"recovery_key, root_key, client_key, blockchain_name, " +
"blockchain_key, paid_limit_bytes, used_bytes, " +
"last_block_number, last_block_hash, last_block_signature, " +
@@ -475,9 +476,10 @@ public final class PostgresStorageRepository
"trusted_count, created_at_ms, updated_at_ms, " +
"prev_record_hash, record_signature, raw_data_base64, " +
"first_seen_at_ms, last_synced_at_ms" +
") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) " +
"ON CONFLICT (pda_address) DO UPDATE SET " +
"login = EXCLUDED.login, " +
"normalized_login = EXCLUDED.normalized_login, " +
"record_number = EXCLUDED.record_number, " +
"slot = EXCLUDED.slot, " +
"last_tx_signature = EXCLUDED.last_tx_signature, " +
@@ -679,36 +681,41 @@ public final class PostgresStorageRepository
statement.setString(1, snapshot.pdaAddress());
statement.setString(2, snapshot.login());
statement.setInt(3, snapshot.recordNumber());
statement.setLong(4, snapshot.slot());
statement.setString(5, snapshot.lastTxSignature());
statement.setString(6, snapshot.recoveryKey());
statement.setString(7, snapshot.rootKey());
statement.setString(8, snapshot.clientKey());
statement.setString(9, snapshot.blockchainName());
statement.setString(10, snapshot.blockchainKey());
statement.setLong(11, snapshot.paidLimitBytes());
statement.setLong(12, snapshot.usedBytes());
statement.setInt(13, snapshot.lastBlockNumber());
statement.setString(14, snapshot.lastBlockHash());
statement.setString(15, snapshot.lastBlockSignature());
statement.setString(16, snapshot.arweaveTxId());
statement.setBoolean(17, snapshot.isServer());
statement.setInt(18, snapshot.addressFormatType());
statement.setInt(19, snapshot.addressFormatVersion());
statement.setString(20, snapshot.serverAddress());
statement.setString(21, writeJson(snapshot.syncServers()));
statement.setString(22, writeJson(snapshot.accessServers()));
statement.setInt(23, snapshot.sessionsMode());
statement.setString(24, writeJson(snapshot.sessions()));
statement.setInt(25, snapshot.trustedCount());
statement.setLong(26, snapshot.createdAtMs());
statement.setLong(27, snapshot.updatedAtMs());
statement.setString(28, snapshot.prevRecordHash());
statement.setString(29, snapshot.recordSignature());
statement.setString(30, snapshot.rawDataBase64());
statement.setLong(31, nowMs);
statement.setString(3, normalizeLogin(snapshot.login()));
statement.setInt(4, snapshot.recordNumber());
statement.setLong(5, snapshot.slot());
statement.setString(6, snapshot.lastTxSignature());
statement.setString(7, snapshot.recoveryKey());
statement.setString(8, snapshot.rootKey());
statement.setString(9, snapshot.clientKey());
statement.setString(10, snapshot.blockchainName());
statement.setString(11, snapshot.blockchainKey());
statement.setLong(12, snapshot.paidLimitBytes());
statement.setLong(13, snapshot.usedBytes());
statement.setInt(14, snapshot.lastBlockNumber());
statement.setString(15, snapshot.lastBlockHash());
statement.setString(16, snapshot.lastBlockSignature());
statement.setString(17, snapshot.arweaveTxId());
statement.setBoolean(18, snapshot.isServer());
statement.setInt(19, snapshot.addressFormatType());
statement.setInt(20, snapshot.addressFormatVersion());
statement.setString(21, snapshot.serverAddress());
statement.setString(22, writeJson(snapshot.syncServers()));
statement.setString(23, writeJson(snapshot.accessServers()));
statement.setInt(24, snapshot.sessionsMode());
statement.setString(25, writeJson(snapshot.sessions()));
statement.setInt(26, snapshot.trustedCount());
statement.setLong(27, snapshot.createdAtMs());
statement.setLong(28, snapshot.updatedAtMs());
statement.setString(29, snapshot.prevRecordHash());
statement.setString(30, snapshot.recordSignature());
statement.setString(31, snapshot.rawDataBase64());
statement.setLong(32, nowMs);
statement.setLong(33, nowMs);
}
private String normalizeLogin(String login) {
return login == null ? "" : login.trim().toLowerCase(Locale.ROOT);
}
private ShineUsersCodec.UserPdaSnapshot mapSnapshot(
@@ -911,6 +918,7 @@ public final class PostgresStorageRepository
"CREATE TABLE IF NOT EXISTS solana_user_pda_current (" +
"pda_address TEXT PRIMARY KEY, " +
"login TEXT NOT NULL UNIQUE, " +
"normalized_login TEXT NOT NULL, " +
"record_number INTEGER NOT NULL, " +
"slot BIGINT NOT NULL, " +
"last_tx_signature TEXT NOT NULL, " +
@@ -943,11 +951,29 @@ public final class PostgresStorageRepository
"last_synced_at_ms BIGINT NOT NULL" +
")"
);
statement.executeUpdate(
"ALTER TABLE solana_user_pda_current " +
"ADD COLUMN IF NOT EXISTS normalized_login TEXT"
);
statement.executeUpdate(
"UPDATE solana_user_pda_current " +
"SET normalized_login = LOWER(BTRIM(login)) " +
"WHERE normalized_login IS NULL " +
" OR normalized_login <> LOWER(BTRIM(login))"
);
statement.executeUpdate(
"CREATE INDEX IF NOT EXISTS idx_user_pda_current_slot " +
"ON solana_user_pda_current(slot)"
);
statement.executeUpdate(
"CREATE UNIQUE INDEX IF NOT EXISTS uq_solana_user_pda_current_normalized_login " +
"ON solana_user_pda_current(normalized_login)"
);
statement.executeUpdate(
"CREATE INDEX IF NOT EXISTS idx_user_pda_current_normalized_login " +
"ON solana_user_pda_current(normalized_login)"
);
statement.executeUpdate(
"CREATE TABLE IF NOT EXISTS solana_user_pda_history (" +