SHA256
621 lines
22 KiB
PL/PgSQL
621 lines
22 KiB
PL/PgSQL
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;
|