Немного улучшен дизайн и обновлены каналы

This commit is contained in:
AidarKC
2026-11-08 15:25:20 +03:00
parent 571f4cbc7d
commit 9405f28525
13 changed files with 240 additions and 19 deletions
@@ -0,0 +1,8 @@
-- SHiNE DB v40: код валюты и актуализация стоимости namespace.
-- Исторический namespace 1.0 не содержит currency_code и означает SOL (код 1).
BEGIN;
ALTER TABLE shine_namespace_current
ADD COLUMN currency_code SMALLINT NOT NULL DEFAULT 1
CHECK (currency_code = 1);
UPDATE db_schema_version SET schema_version = 40 WHERE id = 1;
COMMIT;
@@ -101,7 +101,7 @@ final class ChannelsReadSupport {
String sql = """
SELECT block_bytes 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_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -156,7 +156,7 @@ final class ChannelsReadSupport {
String sql = """
SELECT COUNT(*) AS cnt FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type IN (?, ?, ?, ?, ?, ?) 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)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
String login=loginFromChainName(ownerBch); ps.setString(1,login); ps.setInt(2,MSG_TYPE_TEXT); ps.setInt(3,MsgSubType.TEXT_POST); ps.setInt(4,MsgSubType.TEXT_REPOST); ps.setInt(5,MsgSubType.TEXT_ENTRYPOINT); ps.setInt(6,MsgSubType.TEXT_EXERCISE); ps.setInt(7,MsgSubType.TEXT_SERVICE); ps.setInt(8,MsgSubType.TEXT_COURSE); ps.setInt(9,lineCode); ps.setString(10,login);
@@ -176,7 +176,7 @@ final class ChannelsReadSupport {
String sql="""
SELECT login,bch_name,block_number,block_hash,block_bytes,this_line_number FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type IN (?, ?, ?, ?, ?, ?) 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)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number DESC LIMIT 1
""";
try(PreparedStatement ps=c.prepareStatement(sql)){String login=loginFromChainName(ownerBch);ps.setString(1,login);ps.setInt(2,MSG_TYPE_TEXT);ps.setInt(3,MsgSubType.TEXT_POST);ps.setInt(4,MsgSubType.TEXT_REPOST);ps.setInt(5,MsgSubType.TEXT_ENTRYPOINT);ps.setInt(6,MsgSubType.TEXT_EXERCISE);ps.setInt(7,MsgSubType.TEXT_SERVICE);ps.setInt(8,MsgSubType.TEXT_COURSE);ps.setInt(9,lineCode);ps.setString(10,login);try(ResultSet rs=ps.executeQuery()){if(!rs.next())return null;PostBlock pb=new PostBlock();pb.login=rs.getString("login");pb.bchName=rs.getString("bch_name");pb.blockNumber=rs.getInt("block_number");pb.blockHash=rs.getBytes("block_hash");pb.blockBytes=rs.getBytes("block_bytes");return pb;}}
@@ -188,7 +188,7 @@ final class ChannelsReadSupport {
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? 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_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number DESC
LIMIT 1
""";
@@ -239,7 +239,7 @@ final class ChannelsReadSupport {
SELECT login,bch_name,block_number,block_hash,block_bytes,to_bch_name,to_block_number,to_block_hash,msg_sub_type,this_line_number
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type IN (?, ?, ?, ?, ?, ?) 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)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number
""" + order + (bounded ? " LIMIT ?" : "");
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -290,7 +290,7 @@ final class ChannelsReadSupport {
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? 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_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number ASC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -343,7 +343,7 @@ final class ChannelsReadSupport {
AND LOWER(to_login) = LOWER(?)
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(blocks.login) LIMIT 1),block_number)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(blocks.login) LIMIT 1),block_number)
""";
int ratingsCount = 0;
try (PreparedStatement ps = c.prepareStatement(ratingsSql)) {
@@ -390,7 +390,7 @@ final class ChannelsReadSupport {
String sql = """
SELECT block_bytes 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_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -467,7 +467,7 @@ final class ChannelsReadSupport {
SELECT login,bch_name,block_number,block_hash,block_bytes,this_line_number
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type=? 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_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
LIMIT 1
""";
try (PreparedStatement ps = c.prepareStatement(rootSql)) {
@@ -519,7 +519,7 @@ final class ChannelsReadSupport {
SELECT login,bch_name,block_number,block_hash,block_bytes,this_line_number
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type=? 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)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number ASC
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {
@@ -559,7 +559,7 @@ final class ChannelsReadSupport {
SELECT login,bch_name,block_number,block_hash,block_bytes,this_line_number
FROM blocks
WHERE LOWER(login)=LOWER(?) AND msg_type=? AND msg_sub_type=? 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)
AND fork_number=shine_canonical_fork(login,(SELECT substring(blockchain_name from '([0-9]{3})$')::INTEGER FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1),block_number)
ORDER BY block_number ASC
""";
try (PreparedStatement ps = c.prepareStatement(deleteSql)) {
@@ -595,7 +595,7 @@ final class ChannelsReadSupport {
String partnerBchSql = """
SELECT blockchain_name
FROM solana_user_pda_current
FROM user_identity_current
WHERE LOWER(login) = LOWER(?)
LIMIT 1
""";
@@ -121,10 +121,11 @@ public class Net_ListSubscriptionsFeed_Handler implements JsonMessageHandler {
return out;
}
// Общий реестр включает FREE_CERT и пользователей, зарегистрированных через Solana.
private List<ChannelKey> loadOwnChannels(Connection c, String canonicalLogin) throws Exception {
List<ChannelKey> out = new ArrayList<>();
String activeBch = null;
try (PreparedStatement ps=c.prepareStatement("SELECT blockchain_name FROM solana_user_pda_current WHERE LOWER(login)=LOWER(?) LIMIT 1")){
try (PreparedStatement ps=c.prepareStatement("SELECT blockchain_name FROM user_identity_current WHERE LOWER(login)=LOWER(?) LIMIT 1")){
ps.setString(1,canonicalLogin);try(ResultSet rs=ps.executeQuery()){if(rs.next())activeBch=rs.getString(1);}
}
if(activeBch==null||activeBch.isBlank())return out;
@@ -139,11 +140,11 @@ public class Net_ListSubscriptionsFeed_Handler implements JsonMessageHandler {
List<ChannelKey> out = new ArrayList<>();
String sql = """
SELECT cs.to_login,
COALESCE(cn.owner_bch_name,(SELECT su.blockchain_name FROM solana_user_pda_current su WHERE LOWER(su.login)=LOWER(cs.to_login) LIMIT 1)) AS to_bch_name,
COALESCE(cn.owner_bch_name,(SELECT su.blockchain_name FROM user_identity_current su WHERE LOWER(su.login)=LOWER(cs.to_login) LIMIT 1)) AS to_bch_name,
COALESCE(cs.to_block_number,0) AS root_number, cs.to_block_hash
FROM connections_state cs
LEFT JOIN channel_names_state cn ON LOWER(cn.owner_login)=LOWER(cs.to_login) AND cn.channel_root_block_number=cs.to_block_number AND cn.channel_root_block_hash=cs.to_block_hash
WHERE cs.login=? AND cs.rel_type=?
WHERE LOWER(cs.login)=LOWER(?) AND cs.rel_type=?
ORDER BY cs.to_login, root_number
""";
try (PreparedStatement ps = c.prepareStatement(sql)) {