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

Добавили счётчик подписчиков в каналах и подписки пользователя

Browse Source
This commit is contained in:
AidarKC
2026-08-26 11:11:46 +04:00
parent 3a5851939e
commit 01321f5200
28 changed files with 1966 additions and 44 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
+12
View File
@@ -30,6 +30,8 @@ public final class DatabaseInitializer {
public static final int SCHEMA_VERSION_11 = 11;
public static final int SCHEMA_VERSION_12 = 12;
public static final int SCHEMA_VERSION_13 = 13;
public static final int SCHEMA_VERSION_14 = 14;
public static final int SCHEMA_VERSION_15 = 15;
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";
@@ -43,6 +45,8 @@ public final class DatabaseInitializer {
public static final String POSTGRES_MIGRATION_V11_RESOURCE = "postgres/migration_v11.sql";
public static final String POSTGRES_MIGRATION_V12_RESOURCE = "postgres/migration_v12.sql";
public static final String POSTGRES_MIGRATION_V13_RESOURCE = "postgres/migration_v13.sql";
public static final String POSTGRES_MIGRATION_V14_RESOURCE = "postgres/migration_v14.sql";
public static final String POSTGRES_MIGRATION_V15_RESOURCE = "postgres/migration_v15.sql";
private DatabaseInitializer() {}
@@ -154,6 +158,14 @@ public final class DatabaseInitializer {
runSqlScript(conn, POSTGRES_MIGRATION_V13_RESOURCE);
currentVersion = SCHEMA_VERSION_13;
}
if (currentVersion < SCHEMA_VERSION_14) {
runSqlScript(conn, POSTGRES_MIGRATION_V14_RESOURCE);
currentVersion = SCHEMA_VERSION_14;
}
if (currentVersion < SCHEMA_VERSION_15) {
runSqlScript(conn, POSTGRES_MIGRATION_V15_RESOURCE);
currentVersion = SCHEMA_VERSION_15;
}
}
}
SHiNE-server/shine-server-db/src/main/java/shine/db/dao/BlockchainResyncCleanupDAO.java
+96
View File
@@ -99,6 +99,8 @@ public final class BlockchainResyncCleanupDAO {
int deletedBlocks = deleteBlocksForChain(c, blockchainName);
int deletedBlockchainState = deleteBlockchainStateForChain(c, blockchainName);
rebuildStatsState(c);
c.commit();
return new CleanupResult(
@@ -366,6 +368,100 @@ public final class BlockchainResyncCleanupDAO {
""", blockchainName);
}
private void rebuildStatsState(Connection c) throws SQLException {
try (PreparedStatement truncate = c.prepareStatement("""
TRUNCATE TABLE user_stats_state, channel_stats_state
""")) {
truncate.executeUpdate();
}
try (PreparedStatement ps = c.prepareStatement("""
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
)
SELECT
u.login,
COALESCE(own.owned_public_channels_count, 0),
COALESCE(fu.following_users_count, 0),
COALESCE(fc.following_channels_count, 0),
COALESCE(cf.close_friends_count, 0),
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM solana_user_pda_current u
LEFT JOIN (
SELECT owner_login, COUNT(*)::INTEGER AS owned_public_channels_count
FROM channel_names_state
WHERE channel_type_code = 1
GROUP BY owner_login
) own ON LOWER(own.owner_login) = LOWER(u.login)
LEFT JOIN (
SELECT login, COUNT(*)::INTEGER AS following_users_count
FROM connections_state
WHERE rel_type = 30
AND to_block_number = 0
GROUP BY login
) fu ON LOWER(fu.login) = LOWER(u.login)
LEFT JOIN (
SELECT cs.login, COUNT(*)::INTEGER AS following_channels_count
FROM connections_state cs
JOIN channel_names_state cn
ON cn.owner_bch_name = cs.to_bch_name
AND cn.channel_root_block_number = cs.to_block_number
AND cn.channel_root_block_hash = cs.to_block_hash
WHERE cs.rel_type = 30
AND cn.channel_type_code = 1
GROUP BY cs.login
) fc ON LOWER(fc.login) = LOWER(u.login)
LEFT JOIN (
SELECT login, COUNT(*)::INTEGER AS close_friends_count
FROM connections_state
WHERE rel_type = 10
GROUP BY login
) cf ON LOWER(cf.login) = LOWER(u.login)
""")) {
ps.executeUpdate();
}
try (PreparedStatement ps = c.prepareStatement("""
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
)
SELECT
cn.owner_bch_name,
cn.channel_root_block_number,
cn.channel_root_block_hash,
cn.owner_login,
cn.channel_type_code,
COUNT(DISTINCT cs.login)::INTEGER AS subscribers_count,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
FROM channel_names_state cn
LEFT JOIN connections_state cs
ON cs.rel_type = 30
AND cs.to_bch_name = cn.owner_bch_name
AND cs.to_block_number = cn.channel_root_block_number
AND cs.to_block_hash = cn.channel_root_block_hash
WHERE cn.channel_type_code = 1
GROUP BY
cn.owner_bch_name,
cn.channel_root_block_number,
cn.channel_root_block_hash,
cn.owner_login,
cn.channel_type_code
""")) {
ps.executeUpdate();
}
}
private int executeDelete(Connection c, String sql, String value) throws SQLException {
try (PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, value);
SHiNE-server/shine-server-db/src/main/resources/postgres/migration_v14.sql
+620
View File
@@ -0,0 +1,620 @@
BEGIN;
CREATE TABLE IF NOT EXISTS user_stats_state (
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
owned_public_channels_count INTEGER NOT NULL DEFAULT 0 CHECK (owned_public_channels_count >= 0),
following_users_count INTEGER NOT NULL DEFAULT 0 CHECK (following_users_count >= 0),
following_channels_count INTEGER NOT NULL DEFAULT 0 CHECK (following_channels_count >= 0),
close_friends_count INTEGER NOT NULL DEFAULT 0 CHECK (close_friends_count >= 0),
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_user_stats_state_following_users_count
ON user_stats_state (following_users_count);
CREATE TABLE IF NOT EXISTS channel_stats_state (
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL CHECK (channel_root_block_number >= 0),
channel_root_block_hash BYTEA NOT NULL,
owner_login TEXT NOT NULL,
channel_type_code INTEGER NOT NULL DEFAULT 1,
subscribers_count INTEGER NOT NULL DEFAULT 0 CHECK (subscribers_count >= 0),
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_bch_name, channel_root_block_number, channel_root_block_hash)
);
CREATE INDEX IF NOT EXISTS idx_channel_stats_state_owner_login
ON channel_stats_state (owner_login);
CREATE OR REPLACE FUNCTION shine_user_stats_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
now_ms BIGINT;
BEGIN
now_ms := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
now_ms
)
ON CONFLICT (login) DO NOTHING;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_channel_names_state_stats_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
now_ms BIGINT;
public_subscribers_count INTEGER;
BEGIN
now_ms := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
IF NEW.owner_login IS NULL OR btrim(NEW.owner_login) = '' THEN
RETURN NEW;
END IF;
IF NEW.channel_type_code = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.owner_login,
1,
0,
0,
0,
now_ms
)
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count = user_stats_state.owned_public_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
SELECT COUNT(*)::INTEGER
INTO public_subscribers_count
FROM connections_state cs
JOIN channel_names_state cn
ON cn.owner_bch_name = cs.to_bch_name
AND cn.channel_root_block_number = cs.to_block_number
AND cn.channel_root_block_hash = cs.to_block_hash
WHERE cs.rel_type = 30
AND cn.owner_bch_name = NEW.owner_bch_name
AND cn.channel_root_block_number = NEW.channel_root_block_number
AND cn.channel_root_block_hash = NEW.channel_root_block_hash
AND cn.channel_type_code = 1;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.owner_bch_name,
NEW.channel_root_block_number,
NEW.channel_root_block_hash,
NEW.owner_login,
NEW.channel_type_code,
COALESCE(public_subscribers_count, 0),
now_ms
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = EXCLUDED.subscribers_count,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
WITH pending AS (
SELECT cs.login, COUNT(*)::INTEGER AS cnt
FROM connections_state cs
WHERE cs.rel_type = 30
AND cs.to_bch_name = NEW.owner_bch_name
AND cs.to_block_number = NEW.channel_root_block_number
AND cs.to_block_hash = NEW.channel_root_block_hash
GROUP BY cs.login
)
UPDATE user_stats_state us
SET following_channels_count = GREATEST(0, us.following_channels_count - pending.cnt),
updated_at_ms = now_ms
FROM pending
WHERE us.login = pending.login;
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;
existed_before BOOLEAN;
target_channel_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;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
IF NEW.msg_sub_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = user_stats_state.close_friends_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.msg_sub_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
1,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = user_stats_state.following_users_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = channel_stats_state.subscribers_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
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;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
RETURN NEW;
END IF;
IF positive_rel_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = GREATEST(0, user_stats_state.close_friends_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF positive_rel_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = GREATEST(0, user_stats_state.following_users_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = GREATEST(0, channel_stats_state.subscribers_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
RETURN NEW;
END;
$$;
DROP TRIGGER IF EXISTS trg_user_stats_state_ai ON solana_user_pda_current;
CREATE TRIGGER trg_user_stats_state_ai
AFTER INSERT ON solana_user_pda_current
FOR EACH ROW
EXECUTE FUNCTION shine_user_stats_state_ai();
DROP TRIGGER IF EXISTS trg_channel_names_state_stats_ai ON channel_names_state;
CREATE TRIGGER trg_channel_names_state_stats_ai
AFTER INSERT ON channel_names_state
FOR EACH ROW
EXECUTE FUNCTION shine_channel_names_state_stats_ai();
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();
TRUNCATE TABLE user_stats_state, channel_stats_state;
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
)
SELECT
u.login,
COALESCE(own.owned_public_channels_count, 0) AS owned_public_channels_count,
COALESCE(fu.following_users_count, 0) AS following_users_count,
COALESCE(fc.following_channels_count, 0) AS following_channels_count,
COALESCE(cf.close_friends_count, 0) AS close_friends_count,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT) AS updated_at_ms
FROM solana_user_pda_current u
LEFT JOIN (
SELECT owner_login, COUNT(*)::INTEGER AS owned_public_channels_count
FROM channel_names_state
WHERE channel_type_code = 1
GROUP BY owner_login
) own ON LOWER(own.owner_login) = LOWER(u.login)
LEFT JOIN (
SELECT login, COUNT(*)::INTEGER AS following_users_count
FROM connections_state
WHERE rel_type = 30
AND to_block_number = 0
GROUP BY login
) fu ON LOWER(fu.login) = LOWER(u.login)
LEFT JOIN (
SELECT cs.login, COUNT(*)::INTEGER AS following_channels_count
FROM connections_state cs
JOIN channel_names_state cn
ON cn.owner_bch_name = cs.to_bch_name
AND cn.channel_root_block_number = cs.to_block_number
AND cn.channel_root_block_hash = cs.to_block_hash
WHERE cs.rel_type = 30
AND cn.channel_type_code = 1
GROUP BY cs.login
) fc ON LOWER(fc.login) = LOWER(u.login)
LEFT JOIN (
SELECT login, COUNT(*)::INTEGER AS close_friends_count
FROM connections_state
WHERE rel_type = 10
GROUP BY login
) cf ON LOWER(cf.login) = LOWER(u.login);
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
)
SELECT
cn.owner_bch_name,
cn.channel_root_block_number,
cn.channel_root_block_hash,
cn.owner_login,
cn.channel_type_code,
COUNT(DISTINCT cs.login)::INTEGER AS subscribers_count,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT) AS updated_at_ms
FROM channel_names_state cn
LEFT JOIN connections_state cs
ON cs.rel_type = 30
AND cs.to_bch_name = cn.owner_bch_name
AND cs.to_block_number = cn.channel_root_block_number
AND cs.to_block_hash = cn.channel_root_block_hash
WHERE cn.channel_type_code = 1
GROUP BY
cn.owner_bch_name,
cn.channel_root_block_number,
cn.channel_root_block_hash,
cn.owner_login,
cn.channel_type_code;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 14, 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_v15.sql
+105
View File
@@ -0,0 +1,105 @@
BEGIN;
CREATE OR REPLACE FUNCTION shine_channel_names_state_stats_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
now_ms BIGINT;
public_subscribers_count INTEGER;
BEGIN
now_ms := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
IF NEW.owner_login IS NULL OR btrim(NEW.owner_login) = '' THEN
RETURN NEW;
END IF;
IF NEW.channel_type_code = 1 THEN
IF EXISTS (
SELECT 1
FROM solana_user_pda_current su
WHERE su.login = NEW.owner_login
) THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.owner_login,
1,
0,
0,
0,
now_ms
)
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count = user_stats_state.owned_public_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
SELECT COUNT(*)::INTEGER
INTO public_subscribers_count
FROM connections_state cs
JOIN channel_names_state cn
ON cn.owner_bch_name = cs.to_bch_name
AND cn.channel_root_block_number = cs.to_block_number
AND cn.channel_root_block_hash = cs.to_block_hash
WHERE cs.rel_type = 30
AND cn.owner_bch_name = NEW.owner_bch_name
AND cn.channel_root_block_number = NEW.channel_root_block_number
AND cn.channel_root_block_hash = NEW.channel_root_block_hash
AND cn.channel_type_code = 1;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.owner_bch_name,
NEW.channel_root_block_number,
NEW.channel_root_block_hash,
NEW.owner_login,
NEW.channel_type_code,
COALESCE(public_subscribers_count, 0),
now_ms
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = EXCLUDED.subscribers_count,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
WITH pending AS (
SELECT cs.login, COUNT(*)::INTEGER AS cnt
FROM connections_state cs
WHERE cs.rel_type = 30
AND cs.to_bch_name = NEW.owner_bch_name
AND cs.to_block_number = NEW.channel_root_block_number
AND cs.to_block_hash = NEW.channel_root_block_hash
GROUP BY cs.login
)
UPDATE user_stats_state us
SET following_channels_count = GREATEST(0, us.following_channels_count - pending.cnt),
updated_at_ms = now_ms
FROM pending
WHERE us.login = pending.login;
END IF;
RETURN NEW;
END;
$$;
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
VALUES (1, 15, 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
+468 -2
View File
@@ -291,6 +291,12 @@ AFTER TRUNCATE ON solana_user_pda_current
FOR EACH STATEMENT
EXECUTE FUNCTION trg_refresh_user_access_servers_from_user_pda_truncate();
DROP TRIGGER IF EXISTS trg_user_stats_state_ai ON solana_user_pda_current;
CREATE TRIGGER trg_user_stats_state_ai
AFTER INSERT ON solana_user_pda_current
FOR EACH ROW
EXECUTE FUNCTION shine_user_stats_state_ai();
CREATE TABLE IF NOT EXISTS solana_user_pda_history (
id BIGSERIAL PRIMARY KEY,
tx_signature TEXT NOT NULL,
@@ -605,6 +611,32 @@ CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_target
CREATE INDEX IF NOT EXISTS idx_channel_names_state_owner
ON channel_names_state (owner_login, owner_bch_name);
CREATE TABLE IF NOT EXISTS user_stats_state (
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login) ON DELETE CASCADE,
owned_public_channels_count INTEGER NOT NULL DEFAULT 0 CHECK (owned_public_channels_count >= 0),
following_users_count INTEGER NOT NULL DEFAULT 0 CHECK (following_users_count >= 0),
following_channels_count INTEGER NOT NULL DEFAULT 0 CHECK (following_channels_count >= 0),
close_friends_count INTEGER NOT NULL DEFAULT 0 CHECK (close_friends_count >= 0),
updated_at_ms BIGINT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_user_stats_state_following_users_count
ON user_stats_state (following_users_count);
CREATE TABLE IF NOT EXISTS channel_stats_state (
owner_bch_name TEXT NOT NULL,
channel_root_block_number INTEGER NOT NULL CHECK (channel_root_block_number >= 0),
channel_root_block_hash BYTEA NOT NULL,
owner_login TEXT NOT NULL,
channel_type_code INTEGER NOT NULL DEFAULT 1,
subscribers_count INTEGER NOT NULL DEFAULT 0 CHECK (subscribers_count >= 0),
updated_at_ms BIGINT NOT NULL,
PRIMARY KEY (owner_bch_name, channel_root_block_number, channel_root_block_hash)
);
CREATE INDEX IF NOT EXISTS idx_channel_stats_state_owner_login
ON channel_stats_state (owner_login);
CREATE TABLE IF NOT EXISTS chat200_state (
owner_login TEXT NOT NULL,
owner_bch_name TEXT NOT NULL,
@@ -921,6 +953,132 @@ BEGIN
END;
$$;
CREATE OR REPLACE FUNCTION shine_user_stats_state_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
now_ms BIGINT;
BEGIN
now_ms := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
now_ms
)
ON CONFLICT (login) DO NOTHING;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_channel_names_state_stats_ai()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
now_ms BIGINT;
public_subscribers_count INTEGER;
BEGIN
now_ms := CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT);
IF NEW.owner_login IS NULL OR btrim(NEW.owner_login) = '' THEN
RETURN NEW;
END IF;
IF NEW.channel_type_code = 1 THEN
IF EXISTS (
SELECT 1
FROM solana_user_pda_current su
WHERE su.login = NEW.owner_login
) THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.owner_login,
1,
0,
0,
0,
now_ms
)
ON CONFLICT (login) DO UPDATE SET
owned_public_channels_count = user_stats_state.owned_public_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
SELECT COUNT(*)::INTEGER
INTO public_subscribers_count
FROM connections_state cs
JOIN channel_names_state cn
ON cn.owner_bch_name = cs.to_bch_name
AND cn.channel_root_block_number = cs.to_block_number
AND cn.channel_root_block_hash = cs.to_block_hash
WHERE cs.rel_type = 30
AND cn.owner_bch_name = NEW.owner_bch_name
AND cn.channel_root_block_number = NEW.channel_root_block_number
AND cn.channel_root_block_hash = NEW.channel_root_block_hash
AND cn.channel_type_code = 1;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.owner_bch_name,
NEW.channel_root_block_number,
NEW.channel_root_block_hash,
NEW.owner_login,
NEW.channel_type_code,
COALESCE(public_subscribers_count, 0),
now_ms
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = EXCLUDED.subscribers_count,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
WITH pending AS (
SELECT cs.login, COUNT(*)::INTEGER AS cnt
FROM connections_state cs
WHERE cs.rel_type = 30
AND cs.to_bch_name = NEW.owner_bch_name
AND cs.to_block_number = NEW.channel_root_block_number
AND cs.to_block_hash = NEW.channel_root_block_hash
GROUP BY cs.login
)
UPDATE user_stats_state us
SET following_channels_count = GREATEST(0, us.following_channels_count - pending.cnt),
updated_at_ms = now_ms
FROM pending
WHERE us.login = pending.login;
END IF;
RETURN NEW;
END;
$$;
CREATE OR REPLACE FUNCTION shine_blocks_line_integrity_bi()
RETURNS TRIGGER
LANGUAGE plpgsql
@@ -999,6 +1157,8 @@ AS $$
DECLARE
resolved_login TEXT;
positive_rel_type INTEGER;
existed_before BOOLEAN;
target_channel_type INTEGER;
BEGIN
IF NEW.msg_type <> 3 THEN
RETURN NEW;
@@ -1011,10 +1171,159 @@ BEGIN
RETURN NEW;
END IF;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
IF NEW.msg_sub_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = user_stats_state.close_friends_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.msg_sub_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
1,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = user_stats_state.following_users_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
1,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = channel_stats_state.subscribers_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
1,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = user_stats_state.following_channels_count + 1,
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = NEW.msg_sub_type
AND to_login = resolved_login;
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
INSERT INTO connections_state (
login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash
@@ -1048,10 +1357,161 @@ BEGIN
RETURN NEW;
END IF;
SELECT EXISTS (
SELECT 1
FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'))
)
INTO existed_before;
IF NOT existed_before THEN
RETURN NEW;
END IF;
IF positive_rel_type = 10 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
close_friends_count = GREATEST(0, user_stats_state.close_friends_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF positive_rel_type = 30 THEN
IF NEW.to_block_number IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF NEW.to_block_number = 0 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_users_count = GREATEST(0, user_stats_state.following_users_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSE
SELECT cn.channel_type_code
INTO target_channel_type
FROM channel_names_state cn
WHERE cn.owner_bch_name = NEW.to_bch_name
AND cn.channel_root_block_number = NEW.to_block_number
AND cn.channel_root_block_hash = NEW.to_block_hash
LIMIT 1;
IF target_channel_type = 1 THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
INSERT INTO channel_stats_state (
owner_bch_name,
channel_root_block_number,
channel_root_block_hash,
owner_login,
channel_type_code,
subscribers_count,
updated_at_ms
) VALUES (
NEW.to_bch_name,
NEW.to_block_number,
NEW.to_block_hash,
resolved_login,
target_channel_type,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (owner_bch_name, channel_root_block_number, channel_root_block_hash) DO UPDATE SET
owner_login = EXCLUDED.owner_login,
channel_type_code = EXCLUDED.channel_type_code,
subscribers_count = GREATEST(0, channel_stats_state.subscribers_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
ELSIF target_channel_type IS NULL THEN
INSERT INTO user_stats_state (
login,
owned_public_channels_count,
following_users_count,
following_channels_count,
close_friends_count,
updated_at_ms
) VALUES (
NEW.login,
0,
0,
0,
0,
CAST(EXTRACT(EPOCH FROM clock_timestamp()) * 1000 AS BIGINT)
)
ON CONFLICT (login) DO UPDATE SET
following_channels_count = GREATEST(0, user_stats_state.following_channels_count - 1),
updated_at_ms = EXCLUDED.updated_at_ms;
END IF;
END IF;
END IF;
DELETE FROM connections_state
WHERE login = NEW.login
AND rel_type = positive_rel_type
AND to_login = resolved_login;
AND to_login = resolved_login
AND to_bch_name = NEW.to_bch_name
AND to_block_number = COALESCE(NEW.to_block_number, 0)
AND to_block_hash = COALESCE(NEW.to_block_hash, decode(repeat('00', 32), 'hex'));
RETURN NEW;
END;
@@ -1231,4 +1691,10 @@ AFTER INSERT ON blocks
FOR EACH ROW
EXECUTE FUNCTION shine_blocks_edit_apply_ai();
DROP TRIGGER IF EXISTS trg_channel_names_state_stats_ai ON channel_names_state;
CREATE TRIGGER trg_channel_names_state_stats_ai
AFTER INSERT ON channel_names_state
FOR EACH ROW
EXECUTE FUNCTION shine_channel_names_state_stats_ai();
COMMIT;