SHA256
Исправить каналы free-пользователей без PDA
This commit is contained in:
@@ -35,6 +35,7 @@ public final class DatabaseInitializer {
|
||||
public static final int SCHEMA_VERSION_42 = 42;
|
||||
public static final int SCHEMA_VERSION_43 = 43;
|
||||
public static final int SCHEMA_VERSION_44 = 44;
|
||||
public static final int SCHEMA_VERSION_45 = 45;
|
||||
public static final String POSTGRES_SCHEMA_RESOURCE = "postgres/schema_v1.sql";
|
||||
public static final String POSTGRES_MIGRATION_V31_RESOURCE = "postgres/migration_v31.sql";
|
||||
public static final String POSTGRES_MIGRATION_V32_RESOURCE = "postgres/migration_v32.sql";
|
||||
@@ -50,6 +51,7 @@ public final class DatabaseInitializer {
|
||||
public static final String POSTGRES_MIGRATION_V42_RESOURCE = "postgres/migration_v42.sql";
|
||||
public static final String POSTGRES_MIGRATION_V43_RESOURCE = "postgres/migration_v43.sql";
|
||||
public static final String POSTGRES_MIGRATION_V44_RESOURCE = "postgres/migration_v44.sql";
|
||||
public static final String POSTGRES_MIGRATION_V45_RESOURCE = "postgres/migration_v45.sql";
|
||||
|
||||
private DatabaseInitializer() {}
|
||||
|
||||
@@ -197,9 +199,13 @@ public final class DatabaseInitializer {
|
||||
runSqlScript(conn, POSTGRES_MIGRATION_V44_RESOURCE);
|
||||
currentVersion = readCurrentSchemaVersion(conn);
|
||||
}
|
||||
if (currentVersion != SCHEMA_VERSION_44) {
|
||||
if (currentVersion == SCHEMA_VERSION_44) {
|
||||
runSqlScript(conn, POSTGRES_MIGRATION_V45_RESOURCE);
|
||||
currentVersion = readCurrentSchemaVersion(conn);
|
||||
}
|
||||
if (currentVersion != SCHEMA_VERSION_45) {
|
||||
throw new SQLException(
|
||||
"Unsupported SHiNE DB v2 schema version: expected 44, found " + currentVersion);
|
||||
"Unsupported SHiNE DB v2 schema version: expected 45, found " + currentVersion);
|
||||
}
|
||||
normalizeBlockchainStateKeysToBase64(conn);
|
||||
}
|
||||
|
||||
@@ -0,0 +1,68 @@
|
||||
-- v45: FREE_CERT — полноценная identity без фиктивной Solana PDA.
|
||||
-- Обычные проекции и чтение blockchain выбирают ветку по user_identity_current;
|
||||
-- Solana PDA остаётся источником только Solana-специфичных данных.
|
||||
BEGIN;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_identity_active_fork(p_login TEXT)
|
||||
RETURNS INTEGER
|
||||
LANGUAGE plpgsql
|
||||
STABLE
|
||||
AS $$
|
||||
DECLARE
|
||||
v_fork INTEGER;
|
||||
BEGIN
|
||||
SELECT substring(blockchain_name FROM '([0-9]{3})$')::INTEGER
|
||||
INTO v_fork
|
||||
FROM user_identity_current
|
||||
WHERE LOWER(login) = LOWER(BTRIM(p_login))
|
||||
LIMIT 1;
|
||||
|
||||
IF v_fork BETWEEN 1 AND 999 THEN
|
||||
RETURN v_fork;
|
||||
END IF;
|
||||
|
||||
SELECT fork_number
|
||||
INTO v_fork
|
||||
FROM blockchain_state_store
|
||||
WHERE LOWER(login) = LOWER(BTRIM(p_login))
|
||||
ORDER BY updated_at_ms DESC, fork_number DESC
|
||||
LIMIT 1;
|
||||
|
||||
RETURN COALESCE(v_fork, 1);
|
||||
END $$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_active_fork(p_login TEXT, p_block_number INTEGER)
|
||||
RETURNS INTEGER
|
||||
LANGUAGE sql
|
||||
STABLE
|
||||
AS $$
|
||||
SELECT shine_canonical_fork(p_login, shine_identity_active_fork(p_login), p_block_number)
|
||||
$$;
|
||||
|
||||
CREATE OR REPLACE FUNCTION shine_channel_names_state_refresh_owner_stats()
|
||||
RETURNS TRIGGER
|
||||
LANGUAGE plpgsql
|
||||
AS $$
|
||||
BEGIN
|
||||
IF TG_OP = 'DELETE' THEN
|
||||
PERFORM shine_refresh_user_stats(OLD.owner_login);
|
||||
RETURN OLD;
|
||||
END IF;
|
||||
PERFORM shine_refresh_user_stats(NEW.owner_login);
|
||||
RETURN NEW;
|
||||
END $$;
|
||||
|
||||
DROP TRIGGER IF EXISTS trg_channel_names_state_refresh_owner_stats ON channel_names_state;
|
||||
CREATE TRIGGER trg_channel_names_state_refresh_owner_stats
|
||||
AFTER INSERT OR UPDATE OF owner_login, channel_type_code, is_deleted OR DELETE
|
||||
ON channel_names_state
|
||||
FOR EACH ROW EXECUTE FUNCTION shine_channel_names_state_refresh_owner_stats();
|
||||
|
||||
-- Исправить уже записанные FREE_CERT и paid identity одним пересчётом.
|
||||
SELECT shine_refresh_user_stats(login) FROM user_identity_current;
|
||||
|
||||
UPDATE db_schema_version
|
||||
SET schema_version = 45,
|
||||
updated_at_ms = CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
|
||||
WHERE id = 1;
|
||||
COMMIT;
|
||||
+4
-25
@@ -49,14 +49,7 @@ public final class ChannelNamesStateBootstrapper {
|
||||
SELECT login, bch_name, block_number, block_hash, block_bytes
|
||||
FROM blocks
|
||||
WHERE msg_type = ? AND msg_sub_type = ?
|
||||
AND fork_number=shine_canonical_fork(
|
||||
login,
|
||||
(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER
|
||||
FROM solana_user_pda_current
|
||||
WHERE LOWER(login)=LOWER(blocks.login)
|
||||
LIMIT 1),
|
||||
block_number
|
||||
)
|
||||
AND fork_number=shine_active_fork(login, block_number)
|
||||
ORDER BY LOWER(login), block_number
|
||||
""";
|
||||
|
||||
@@ -149,7 +142,7 @@ public final class ChannelNamesStateBootstrapper {
|
||||
applyMetaUpdates(c, dao, entries, skipped);
|
||||
applyDeletionStates(c, dao, skipped);
|
||||
// v18: counters are a projection, so rebuild them from current state after a full channel-name bootstrap.
|
||||
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_user_stats(login) FROM solana_user_pda_current")) {
|
||||
try (PreparedStatement ps = c.prepareStatement("SELECT shine_refresh_user_stats(login) FROM user_identity_current")) {
|
||||
ps.execute();
|
||||
}
|
||||
try (PreparedStatement ps = c.prepareStatement("""
|
||||
@@ -197,14 +190,7 @@ public final class ChannelNamesStateBootstrapper {
|
||||
SELECT bch_name, block_number, block_bytes, msg_sub_type
|
||||
FROM blocks
|
||||
WHERE msg_type = ? AND msg_sub_type IN (?, ?, ?, ?, ?, ?, ?)
|
||||
AND fork_number=shine_canonical_fork(
|
||||
login,
|
||||
(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER
|
||||
FROM solana_user_pda_current
|
||||
WHERE LOWER(login)=LOWER(blocks.login)
|
||||
LIMIT 1),
|
||||
block_number
|
||||
)
|
||||
AND fork_number=shine_active_fork(login, block_number)
|
||||
ORDER BY LOWER(login), block_number
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
@@ -246,14 +232,7 @@ public final class ChannelNamesStateBootstrapper {
|
||||
SELECT login, bch_name, block_number, block_bytes
|
||||
FROM blocks
|
||||
WHERE msg_type = ? AND msg_sub_type = ?
|
||||
AND fork_number=shine_canonical_fork(
|
||||
login,
|
||||
(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER
|
||||
FROM solana_user_pda_current
|
||||
WHERE LOWER(login)=LOWER(blocks.login)
|
||||
LIMIT 1),
|
||||
block_number
|
||||
)
|
||||
AND fork_number=shine_active_fork(login, block_number)
|
||||
ORDER BY LOWER(login), block_number
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
|
||||
+1
-2
@@ -158,14 +158,13 @@ public class Net_GetGroupDialog_Handler implements JsonMessageHandler {
|
||||
String sql = """
|
||||
SELECT login, bch_name, block_number, block_hash, block_bytes
|
||||
FROM blocks
|
||||
WHERE LOWER(login)=LOWER(?) AND msg_type=1 AND line_code=? AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
WHERE LOWER(login)=LOWER(?) AND msg_type=1 AND line_code=? AND fork_number=shine_active_fork(login,block_number)
|
||||
ORDER BY block_number ASC
|
||||
""";
|
||||
List<MsgRow> out = new ArrayList<>();
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setString(1, ChannelsReadSupport.loginFromChainName(ownerBch));
|
||||
ps.setInt(2, line);
|
||||
ps.setString(3, ChannelsReadSupport.loginFromChainName(ownerBch));
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
while (rs.next()) {
|
||||
MsgRow row = new MsgRow();
|
||||
|
||||
+5
-9
@@ -123,7 +123,7 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
(msg_sub_type=? AND (LOWER(to_login)=LOWER(?) OR to_bch_name=?))
|
||||
OR (msg_sub_type=? AND LOWER(to_login)=LOWER(?))
|
||||
)
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(blocks.login) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setInt(1, toBlockNumber);
|
||||
@@ -155,14 +155,13 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
SELECT login,bch_name,block_number,block_hash,block_bytes,data_item_id,arweave_publish_pending,arweave_published_at_ms,to_login,to_bch_name,to_block_number,to_block_hash,line_code,msg_sub_type,this_line_number
|
||||
FROM blocks
|
||||
WHERE LOWER(login)=LOWER(?) AND block_number=? AND block_hash=?
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
LIMIT 1
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
ps.setString(1, login);
|
||||
ps.setInt(2, blockNumber);
|
||||
ps.setBytes(3, blockHash);
|
||||
ps.setString(4, login);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
return rs.next() ? mapRow(rs) : null;
|
||||
}
|
||||
@@ -174,14 +173,13 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
SELECT login,bch_name,block_number,block_hash,block_bytes,data_item_id,arweave_publish_pending,arweave_published_at_ms,to_login,to_bch_name,to_block_number,to_block_hash,line_code,msg_sub_type,this_line_number
|
||||
FROM blocks
|
||||
WHERE LOWER(login)=LOWER(?) AND block_number=?
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
LIMIT 1
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
String login=ChannelsReadSupport.loginFromChainName(bchName);
|
||||
ps.setString(1, login);
|
||||
ps.setInt(2, blockNumber);
|
||||
ps.setString(3, login);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
return rs.next() ? mapRow(rs) : null;
|
||||
}
|
||||
@@ -193,7 +191,7 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
SELECT login,bch_name,block_number,block_hash,block_bytes,data_item_id,arweave_publish_pending,arweave_published_at_ms,to_login,to_bch_name,to_block_number,to_block_hash,line_code,msg_sub_type,this_line_number
|
||||
FROM blocks
|
||||
WHERE LOWER(login)=LOWER(?) AND block_number=? AND block_hash=?
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
LIMIT 1
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
@@ -201,7 +199,6 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
ps.setString(1, login);
|
||||
ps.setInt(2, blockNumber);
|
||||
ps.setBytes(3, blockHash);
|
||||
ps.setString(4, login);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
return rs.next() ? mapRow(rs) : null;
|
||||
}
|
||||
@@ -321,7 +318,7 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
FROM blocks
|
||||
WHERE LOWER(login)=LOWER(?) AND msg_type=1 AND msg_sub_type=?
|
||||
AND to_block_number=? AND to_block_hash=?
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
ORDER BY block_number ASC
|
||||
""";
|
||||
try (PreparedStatement ps = c.prepareStatement(sql)) {
|
||||
@@ -330,7 +327,6 @@ public class Net_GetMessageThread_Handler implements JsonMessageHandler {
|
||||
ps.setInt(2, subType);
|
||||
ps.setInt(3, targetBlock);
|
||||
ps.setBytes(4, targetHash);
|
||||
ps.setString(5, login);
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
List<PostRow> out = new ArrayList<>();
|
||||
while (rs.next()) out.add(mapRow(rs));
|
||||
|
||||
+2
-3
@@ -89,7 +89,7 @@ public class Net_GetPersonalDiary_Handler implements JsonMessageHandler {
|
||||
private String loadPrimaryBlockchainName(Connection c, String canonicalLogin) throws Exception {
|
||||
try (PreparedStatement ps = c.prepareStatement("""
|
||||
SELECT blockchain_name
|
||||
FROM solana_user_pda_current
|
||||
FROM user_identity_current
|
||||
WHERE LOWER(login)=LOWER(?)
|
||||
LIMIT 1
|
||||
""")) {
|
||||
@@ -181,7 +181,7 @@ public class Net_GetPersonalDiary_Handler implements JsonMessageHandler {
|
||||
AND msg_sub_type = ?
|
||||
AND to_block_number = ?
|
||||
AND to_block_hash = ?
|
||||
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
|
||||
AND fork_number=shine_active_fork(login,block_number)
|
||||
ORDER BY block_number ASC
|
||||
""")) {
|
||||
ps.setString(1, ChannelsReadSupport.loginFromChainName(ownerBch));
|
||||
@@ -189,7 +189,6 @@ public class Net_GetPersonalDiary_Handler implements JsonMessageHandler {
|
||||
ps.setInt(3, shine.db.MsgSubType.TEXT_EDIT_REPLY);
|
||||
ps.setInt(4, originalBlockNumber);
|
||||
ps.setBytes(5, originalBlockHash);
|
||||
ps.setString(6, ChannelsReadSupport.loginFromChainName(ownerBch));
|
||||
try (ResultSet rs = ps.executeQuery()) {
|
||||
while (rs.next()) {
|
||||
ChannelsReadSupport.TextInfo editText = ChannelsReadSupport.parseTextAndTime(rs.getBytes("block_bytes"));
|
||||
|
||||
+1
-1
@@ -1,2 +1,2 @@
|
||||
client.version=1.20.67
|
||||
server.version=1.16.33
|
||||
server.version=1.16.34
|
||||
|
||||
@@ -48,9 +48,15 @@
|
||||
- `SolanaUsersSyncService` + `PostgresStorageRepository` создают/перезаписывают snapshots; не допустить, чтобы sync очищал доверенные free-сертификаты или создавал дубль при Solana upgrade.
|
||||
- `ArweaveBlockSyncService` читает `solana_user_pda_current` и blockchain-данные: восстановление free должно оставаться независимым от факта существования реального Solana PDA.
|
||||
|
||||
## Решение для этапа 5 — пока НЕ принято
|
||||
## Принятое решение: единая identity без фиктивной PDA
|
||||
|
||||
Требование мастер-плана: одна логическая личность для `FREE_CERT` и `PAID_UPGRADED`; при free **нет on-chain PDA**, но сохраняются FK/индексы/функциональность сообщений, каналов, связей и сессий. Не вставлять фиктивный Solana snapshot со slot/signature только ради удовлетворения FK. Выбрать единый identity-index после проверки реальной схемы и протестировать:
|
||||
Одна логическая личность существует для `FREE_CERT` и `PAID_UPGRADED`; при free **нет on-chain PDA**, но сохраняются FK/индексы/функциональность сообщений, каналов, связей и сессий. Не вставлять фиктивный Solana snapshot со slot/signature только ради удовлетворения FK.
|
||||
|
||||
Обычные серверные операции обязаны использовать `user_identity_current`: он содержит подтверждённые сертификатом Arweave login, blockchain name и публичные ключи free-пользователя. `solana_user_pda_current` допустимо использовать только для функций, которым действительно нужны on-chain сведения: платежи, Solana-ротация ключей, PDA и маршруты из Solana.
|
||||
|
||||
Для чтения блокчейна SQL-функции `shine_identity_active_fork(login)` и `shine_active_fork(login, block_number)` выбирают ветку по общей identity; при отсутствии PDA используется актуальная запись `blockchain_state_store`, а последним fallback служит fork `1`. Это обеспечивает одинаковую работу каналов и обсуждений у free и paid пользователей.
|
||||
|
||||
После миграции статистика free-пользователей пересчитывается из `channel_names_state` и `connections_state`. Проверить:
|
||||
|
||||
1. создание free до появления в Solana, работа всех 15 зависимых таблиц;
|
||||
2. транзакционный upgrade без перегенерации root/blockchain/client и без переименования логина;
|
||||
|
||||
Reference in New Issue
Block a user