SHA256
889 lines
30 KiB
PL/PgSQL
889 lines
30 KiB
PL/PgSQL
-- SHiNE PostgreSQL runtime schema v1
|
|
-- Дата: 2026-07-24
|
|
--
|
|
-- Назначение:
|
|
-- - поднять пустую PostgreSQL БД сервера SHiNE с нуля;
|
|
-- - включить таблицы модуля синхронизации Solana users;
|
|
-- - включить runtime-таблицы сервера без legacy-таблиц старого runtime:
|
|
-- * НЕ создаём solana_users
|
|
-- * НЕ создаём direct_messages
|
|
-- * основной runtime DM storage = signed_messages
|
|
--
|
|
-- Важно:
|
|
-- - источник истины по пользователям: solana_user_pda_current;
|
|
-- - runtime table blockchain_state остаётся как локальное серверное состояние chain,
|
|
-- но не мигрируется из старого локального runtime и не считается identity-слоем;
|
|
-- - триггеры переписаны под PostgreSQL и сохраняют текущую серверную логику.
|
|
|
|
BEGIN;
|
|
|
|
CREATE TABLE IF NOT EXISTS db_schema_version (
|
|
id INTEGER PRIMARY KEY,
|
|
schema_version INTEGER NOT NULL,
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
INSERT INTO db_schema_version (id, schema_version, updated_at_ms)
|
|
VALUES (1, 1, 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;
|
|
|
|
CREATE TABLE IF NOT EXISTS solana_sync_state (
|
|
id INTEGER PRIMARY KEY,
|
|
status TEXT NOT NULL,
|
|
ready BOOLEAN NOT NULL,
|
|
last_poll_at_ms BIGINT,
|
|
last_successful_poll_at_ms BIGINT,
|
|
last_seen_signature TEXT,
|
|
last_seen_slot BIGINT,
|
|
last_relevant_signature TEXT,
|
|
last_relevant_slot BIGINT,
|
|
last_error TEXT,
|
|
economy_config_version INTEGER,
|
|
registration_fee_lamports BIGINT,
|
|
lamports_per_limit_step BIGINT,
|
|
start_bonus_limit BIGINT,
|
|
updated_at_ms BIGINT
|
|
);
|
|
|
|
INSERT INTO solana_sync_state (id, status, ready)
|
|
VALUES (1, 'EMPTY', FALSE)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
|
|
CREATE TABLE IF NOT EXISTS solana_sync_tx_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
signature TEXT NOT NULL UNIQUE,
|
|
slot BIGINT NOT NULL,
|
|
block_time BIGINT,
|
|
tx_kind TEXT NOT NULL,
|
|
is_relevant BOOLEAN NOT NULL,
|
|
affected_pda_address TEXT,
|
|
affected_login TEXT,
|
|
raw_summary_json TEXT NOT NULL,
|
|
processed_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_slot
|
|
ON solana_sync_tx_history(slot);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_relevant
|
|
ON solana_sync_tx_history(is_relevant);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_tx_history_login
|
|
ON solana_sync_tx_history(affected_login);
|
|
|
|
CREATE TABLE IF NOT EXISTS solana_user_pda_current (
|
|
pda_address TEXT PRIMARY KEY,
|
|
login TEXT NOT NULL UNIQUE,
|
|
record_number INTEGER NOT NULL,
|
|
slot BIGINT NOT NULL,
|
|
last_tx_signature TEXT NOT NULL,
|
|
recovery_key TEXT NOT NULL,
|
|
root_key TEXT NOT NULL,
|
|
client_key TEXT NOT NULL,
|
|
blockchain_name TEXT NOT NULL,
|
|
blockchain_key TEXT NOT NULL,
|
|
paid_limit_bytes BIGINT NOT NULL,
|
|
used_bytes BIGINT NOT NULL,
|
|
last_block_number INTEGER NOT NULL,
|
|
last_block_hash TEXT NOT NULL,
|
|
last_block_signature TEXT NOT NULL,
|
|
arweave_tx_id TEXT NOT NULL,
|
|
is_server BOOLEAN NOT NULL,
|
|
address_format_type INTEGER NOT NULL,
|
|
address_format_version INTEGER NOT NULL,
|
|
server_address TEXT NOT NULL,
|
|
sync_servers_json TEXT NOT NULL,
|
|
access_servers_json TEXT NOT NULL,
|
|
sessions_mode INTEGER NOT NULL,
|
|
sessions_json TEXT NOT NULL,
|
|
trusted_count INTEGER NOT NULL,
|
|
created_at_ms BIGINT NOT NULL,
|
|
updated_at_ms BIGINT NOT NULL,
|
|
prev_record_hash TEXT NOT NULL,
|
|
record_signature TEXT NOT NULL,
|
|
raw_data_base64 TEXT NOT NULL,
|
|
first_seen_at_ms BIGINT NOT NULL,
|
|
last_synced_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_pda_current_slot
|
|
ON solana_user_pda_current(slot);
|
|
|
|
CREATE TABLE IF NOT EXISTS solana_user_pda_history (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
tx_signature TEXT NOT NULL,
|
|
slot BIGINT NOT NULL,
|
|
block_time BIGINT,
|
|
pda_address TEXT NOT NULL,
|
|
login TEXT NOT NULL,
|
|
record_number INTEGER NOT NULL,
|
|
recovery_key TEXT NOT NULL,
|
|
root_key TEXT NOT NULL,
|
|
client_key TEXT NOT NULL,
|
|
blockchain_name TEXT NOT NULL,
|
|
blockchain_key TEXT NOT NULL,
|
|
paid_limit_bytes BIGINT NOT NULL,
|
|
used_bytes BIGINT NOT NULL,
|
|
last_block_number INTEGER NOT NULL,
|
|
last_block_hash TEXT NOT NULL,
|
|
last_block_signature TEXT NOT NULL,
|
|
arweave_tx_id TEXT NOT NULL,
|
|
is_server BOOLEAN NOT NULL,
|
|
address_format_type INTEGER NOT NULL,
|
|
address_format_version INTEGER NOT NULL,
|
|
server_address TEXT NOT NULL,
|
|
sync_servers_json TEXT NOT NULL,
|
|
access_servers_json TEXT NOT NULL,
|
|
sessions_mode INTEGER NOT NULL,
|
|
sessions_json TEXT NOT NULL,
|
|
trusted_count INTEGER NOT NULL,
|
|
created_at_ms BIGINT NOT NULL,
|
|
updated_at_ms BIGINT NOT NULL,
|
|
prev_record_hash TEXT NOT NULL,
|
|
record_signature TEXT NOT NULL,
|
|
raw_data_base64 TEXT NOT NULL,
|
|
saved_at_ms BIGINT NOT NULL,
|
|
UNIQUE (pda_address, record_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_pda_history_login
|
|
ON solana_user_pda_history(login);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_pda_history_slot
|
|
ON solana_user_pda_history(slot);
|
|
|
|
CREATE TABLE IF NOT EXISTS active_sessions (
|
|
session_id TEXT PRIMARY KEY,
|
|
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
|
|
session_key TEXT NOT NULL,
|
|
storage_pwd TEXT NOT NULL,
|
|
session_created_at_ms BIGINT NOT NULL,
|
|
last_authirificated_at_ms BIGINT NOT NULL,
|
|
push_endpoint TEXT,
|
|
push_p256dh_key TEXT,
|
|
push_auth_key TEXT,
|
|
client_ip TEXT,
|
|
client_info_from_client TEXT,
|
|
client_info_from_request TEXT,
|
|
session_type INTEGER NOT NULL DEFAULT 1,
|
|
client_platform TEXT NOT NULL DEFAULT '',
|
|
user_language TEXT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_active_sessions_login
|
|
ON active_sessions(login);
|
|
|
|
CREATE TABLE IF NOT EXISTS esp_pairing_settings (
|
|
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login),
|
|
enabled INTEGER NOT NULL DEFAULT 0,
|
|
password_hash TEXT NOT NULL DEFAULT '',
|
|
ttl_seconds INTEGER NOT NULL DEFAULT 300,
|
|
failed_attempts INTEGER NOT NULL DEFAULT 0,
|
|
first_failed_at_ms BIGINT NOT NULL DEFAULT 0,
|
|
blocked_until_ms BIGINT NOT NULL DEFAULT 0,
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS esp_pairing_requests (
|
|
pairing_id TEXT PRIMARY KEY,
|
|
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
|
|
requester_session_key TEXT NOT NULL,
|
|
requester_session_type INTEGER NOT NULL DEFAULT 1,
|
|
requester_client_platform TEXT NOT NULL DEFAULT '',
|
|
payload_type INTEGER NOT NULL,
|
|
status TEXT NOT NULL,
|
|
short_code TEXT NOT NULL,
|
|
fingerprint_b58 TEXT NOT NULL,
|
|
encrypted_payload TEXT,
|
|
reject_reason TEXT,
|
|
approved_by_session_id TEXT,
|
|
created_at_ms BIGINT NOT NULL,
|
|
expires_at_ms BIGINT NOT NULL,
|
|
updated_at_ms BIGINT NOT NULL,
|
|
delivered_to_homeserver INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_esp_pairing_requests_login_status
|
|
ON esp_pairing_requests(login, status, expires_at_ms);
|
|
|
|
CREATE TABLE IF NOT EXISTS users_params (
|
|
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
|
|
param TEXT NOT NULL,
|
|
time_ms BIGINT NOT NULL,
|
|
value TEXT NOT NULL,
|
|
client_key TEXT,
|
|
signature TEXT,
|
|
UNIQUE (login, param)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_users_params_login
|
|
ON users_params(login);
|
|
|
|
CREATE TABLE IF NOT EXISTS ip_geo_cache (
|
|
ip TEXT PRIMARY KEY,
|
|
geo TEXT,
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_ip_geo_cache_updated_at
|
|
ON ip_geo_cache(updated_at_ms);
|
|
|
|
CREATE TABLE IF NOT EXISTS test_free_avatar_uploads (
|
|
login TEXT PRIMARY KEY REFERENCES solana_user_pda_current(login),
|
|
used_count INTEGER NOT NULL DEFAULT 0,
|
|
updated_at_ms BIGINT NOT NULL,
|
|
last_tx_id TEXT NOT NULL DEFAULT ''
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_test_free_avatar_uploads_updated
|
|
ON test_free_avatar_uploads(updated_at_ms);
|
|
|
|
CREATE TABLE IF NOT EXISTS sync_servers (
|
|
login TEXT PRIMARY KEY,
|
|
server_address TEXT NOT NULL DEFAULT '',
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_sync_servers_updated
|
|
ON sync_servers(updated_at_ms);
|
|
|
|
CREATE TABLE IF NOT EXISTS blockchain_state (
|
|
blockchain_name TEXT PRIMARY KEY,
|
|
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
|
|
blockchain_key TEXT NOT NULL,
|
|
size_limit BIGINT NOT NULL,
|
|
file_size_bytes BIGINT NOT NULL,
|
|
last_block_number INTEGER NOT NULL,
|
|
last_block_hash BYTEA,
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_blockchain_state_login
|
|
ON blockchain_state(login);
|
|
|
|
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),
|
|
bch_name TEXT NOT NULL REFERENCES blockchain_state(blockchain_name),
|
|
block_number INTEGER NOT NULL CHECK (block_number >= 0),
|
|
msg_type INTEGER NOT NULL,
|
|
msg_sub_type INTEGER NOT NULL,
|
|
block_bytes BYTEA NOT NULL,
|
|
to_login TEXT,
|
|
to_bch_name TEXT,
|
|
to_block_number INTEGER CHECK (to_block_number IS NULL OR to_block_number >= 0),
|
|
to_block_hash BYTEA,
|
|
block_hash BYTEA NOT NULL,
|
|
block_signature BYTEA NOT NULL,
|
|
edited_by_block_number INTEGER CHECK (edited_by_block_number IS NULL OR edited_by_block_number >= 0),
|
|
line_code INTEGER CHECK (line_code IS NULL OR line_code >= 0),
|
|
prev_line_number INTEGER CHECK (prev_line_number IS NULL OR prev_line_number >= 0),
|
|
prev_line_hash BYTEA,
|
|
this_line_number INTEGER CHECK (this_line_number IS NULL OR this_line_number >= 0),
|
|
UNIQUE (bch_name, block_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_by_chain_number
|
|
ON blocks (bch_name, block_number);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_blocks_to_target
|
|
ON blocks (to_login, to_bch_name, to_block_number);
|
|
|
|
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),
|
|
rel_type INTEGER NOT NULL,
|
|
to_login TEXT NOT NULL,
|
|
to_bch_name TEXT NOT NULL,
|
|
to_block_number INTEGER NOT NULL,
|
|
to_block_hash BYTEA NOT NULL,
|
|
UNIQUE (login, rel_type, to_login, to_bch_name, to_block_number, to_block_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_connections_state_login
|
|
ON connections_state(login);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_connections_state_to_login
|
|
ON connections_state(to_login);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_connections_state_pair
|
|
ON connections_state(login, to_login);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_connections_state_target
|
|
ON connections_state(login, rel_type, to_bch_name, to_block_number);
|
|
|
|
CREATE TABLE IF NOT EXISTS message_stats (
|
|
to_login TEXT NOT NULL,
|
|
to_bch_name TEXT NOT NULL,
|
|
to_block_number INTEGER NOT NULL,
|
|
to_block_hash BYTEA NOT NULL,
|
|
likes_count INTEGER NOT NULL DEFAULT 0,
|
|
replies_count INTEGER NOT NULL DEFAULT 0,
|
|
edits_count INTEGER NOT NULL DEFAULT 0,
|
|
UNIQUE (to_login, to_bch_name, to_block_number, to_block_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_stats_target
|
|
ON message_stats (to_bch_name, to_block_number, to_block_hash);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_stats_login
|
|
ON message_stats (to_login);
|
|
|
|
CREATE TABLE IF NOT EXISTS reactions_state (
|
|
from_login TEXT NOT NULL,
|
|
from_bch_name TEXT NOT NULL,
|
|
reaction_type INTEGER NOT NULL,
|
|
to_login TEXT NOT NULL,
|
|
to_bch_name TEXT NOT NULL,
|
|
to_block_number INTEGER NOT NULL,
|
|
to_block_hash BYTEA NOT NULL,
|
|
last_sub_type INTEGER NOT NULL,
|
|
UNIQUE (
|
|
from_login,
|
|
from_bch_name,
|
|
reaction_type,
|
|
to_login,
|
|
to_bch_name,
|
|
to_block_number,
|
|
to_block_hash
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reactions_state_target
|
|
ON reactions_state (to_bch_name, to_block_number, to_block_hash);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_reactions_state_actor
|
|
ON reactions_state (from_login, from_bch_name, reaction_type);
|
|
|
|
CREATE TABLE IF NOT EXISTS channel_names_state (
|
|
slug TEXT NOT NULL,
|
|
display_name TEXT NOT NULL,
|
|
channel_description TEXT NOT NULL DEFAULT '',
|
|
owner_login TEXT NOT NULL,
|
|
owner_bch_name TEXT NOT NULL,
|
|
channel_type_code INTEGER NOT NULL DEFAULT 1,
|
|
channel_type_version INTEGER NOT NULL DEFAULT 1,
|
|
channel_root_block_number INTEGER NOT NULL,
|
|
channel_root_block_hash BYTEA NOT NULL,
|
|
created_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_owner_type_slug
|
|
ON channel_names_state (owner_bch_name, channel_type_code, slug);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_channel_names_state_target
|
|
ON channel_names_state (owner_bch_name, channel_root_block_number, channel_root_block_hash);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_channel_names_state_owner
|
|
ON channel_names_state (owner_login, owner_bch_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat200_state (
|
|
owner_login TEXT NOT NULL,
|
|
owner_bch_name TEXT NOT NULL,
|
|
channel_root_block_number INTEGER NOT NULL,
|
|
channel_root_block_hash BYTEA NOT NULL,
|
|
channel_name TEXT NOT NULL,
|
|
channel_type_version INTEGER NOT NULL,
|
|
chat_title TEXT NOT NULL DEFAULT '',
|
|
updated_at_ms BIGINT NOT NULL,
|
|
PRIMARY KEY (owner_bch_name, channel_root_block_number)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat200_state_owner
|
|
ON chat200_state (owner_login, owner_bch_name);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat200_members_state (
|
|
owner_bch_name TEXT NOT NULL,
|
|
channel_root_block_number INTEGER NOT NULL,
|
|
member_login TEXT NOT NULL,
|
|
member_channel_name TEXT NOT NULL,
|
|
is_active INTEGER NOT NULL,
|
|
updated_at_ms BIGINT NOT NULL,
|
|
updated_by_block_number INTEGER NOT NULL,
|
|
PRIMARY KEY (
|
|
owner_bch_name,
|
|
channel_root_block_number,
|
|
member_login,
|
|
member_channel_name
|
|
)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_chat200_members_owner
|
|
ON chat200_members_state (owner_bch_name, channel_root_block_number, is_active);
|
|
|
|
CREATE TABLE IF NOT EXISTS user_push_tokens (
|
|
token_id TEXT PRIMARY KEY,
|
|
login TEXT NOT NULL REFERENCES solana_user_pda_current(login),
|
|
session_id TEXT NOT NULL,
|
|
provider TEXT NOT NULL,
|
|
token TEXT NOT NULL,
|
|
platform TEXT,
|
|
user_agent TEXT,
|
|
updated_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login
|
|
ON user_push_tokens(login);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_push_tokens_login_session
|
|
ON user_push_tokens(login, session_id);
|
|
|
|
CREATE TABLE IF NOT EXISTS signed_direct_message_replay (
|
|
from_login TEXT NOT NULL,
|
|
time_ms BIGINT NOT NULL,
|
|
nonce BIGINT NOT NULL,
|
|
created_at_ms BIGINT NOT NULL,
|
|
UNIQUE (from_login, time_ms, nonce)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signed_dm_replay_created
|
|
ON signed_direct_message_replay(created_at_ms);
|
|
|
|
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),
|
|
target_mode INTEGER NOT NULL,
|
|
target_session_id TEXT,
|
|
message_type INTEGER NOT NULL,
|
|
time_ms BIGINT NOT NULL,
|
|
nonce BIGINT NOT NULL,
|
|
raw_packet BYTEA NOT NULL,
|
|
created_at_ms BIGINT NOT NULL
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signed_dm_history_to
|
|
ON signed_direct_messages_history(to_login, created_at_ms);
|
|
|
|
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),
|
|
time_ms BIGINT NOT NULL,
|
|
nonce BIGINT NOT NULL,
|
|
message_type INTEGER NOT NULL,
|
|
revision_time_ms BIGINT NOT NULL DEFAULT 0,
|
|
reencrypted_at_ms BIGINT NOT NULL DEFAULT 0,
|
|
raw_block BYTEA NOT NULL,
|
|
created_at_ms BIGINT NOT NULL,
|
|
source_api TEXT NOT NULL,
|
|
origin_session_id TEXT,
|
|
receipt_ref_base_key TEXT,
|
|
receipt_ref_type INTEGER,
|
|
read_at_ms BIGINT
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signed_messages_target
|
|
ON signed_messages(target_login, time_ms, created_at_ms);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signed_messages_base
|
|
ON signed_messages(base_key, message_type);
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_receipt_incoming
|
|
ON signed_messages(target_login, receipt_ref_base_key)
|
|
WHERE message_type = 3 AND receipt_ref_base_key IS NOT NULL;
|
|
|
|
CREATE UNIQUE INDEX IF NOT EXISTS uq_signed_messages_receipt_outgoing
|
|
ON signed_messages(target_login, receipt_ref_base_key)
|
|
WHERE message_type = 4 AND receipt_ref_base_key IS NOT NULL;
|
|
|
|
CREATE TABLE IF NOT EXISTS signed_message_session_delivery (
|
|
message_key TEXT NOT NULL REFERENCES signed_messages(message_key),
|
|
session_id TEXT NOT NULL,
|
|
delivered INTEGER NOT NULL DEFAULT 0,
|
|
delivered_at_ms BIGINT,
|
|
created_at_ms BIGINT NOT NULL,
|
|
PRIMARY KEY (message_key, session_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_signed_message_delivery_session
|
|
ON signed_message_session_delivery(session_id, delivered);
|
|
|
|
CREATE TABLE IF NOT EXISTS message_views_state (
|
|
viewer_login TEXT NOT NULL,
|
|
to_bch_name TEXT NOT NULL,
|
|
to_block_number INTEGER NOT NULL,
|
|
to_block_hash BYTEA NOT NULL,
|
|
first_seen_at_ms BIGINT NOT NULL,
|
|
PRIMARY KEY (viewer_login, to_bch_name, to_block_number, to_block_hash)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_views_state_target
|
|
ON message_views_state(to_bch_name, to_block_number, to_block_hash);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_views_state_viewer_channel
|
|
ON message_views_state(viewer_login, to_bch_name);
|
|
|
|
CREATE OR REPLACE FUNCTION shine_login_from_blockchain_name(blockchain_name_in TEXT)
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
dash_pos INTEGER;
|
|
suffix TEXT;
|
|
BEGIN
|
|
IF blockchain_name_in IS NULL OR length(blockchain_name_in) <= 4 THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
dash_pos := length(blockchain_name_in) - 3;
|
|
IF substr(blockchain_name_in, dash_pos, 1) <> '-' THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
suffix := substr(blockchain_name_in, dash_pos + 1);
|
|
IF suffix !~ '^[0-9]{3}$' THEN
|
|
RETURN NULL;
|
|
END IF;
|
|
|
|
RETURN substr(blockchain_name_in, 1, dash_pos - 1);
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION shine_resolve_login(target_login_in TEXT, target_bch_name_in TEXT)
|
|
RETURNS TEXT
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
resolved_login TEXT;
|
|
BEGIN
|
|
IF target_login_in IS NOT NULL AND btrim(target_login_in) <> '' THEN
|
|
RETURN target_login_in;
|
|
END IF;
|
|
|
|
IF target_bch_name_in IS NOT NULL AND btrim(target_bch_name_in) <> '' THEN
|
|
SELECT login
|
|
INTO resolved_login
|
|
FROM solana_user_pda_current
|
|
WHERE blockchain_name = target_bch_name_in
|
|
LIMIT 1;
|
|
|
|
IF resolved_login IS NOT NULL AND btrim(resolved_login) <> '' THEN
|
|
RETURN resolved_login;
|
|
END IF;
|
|
END IF;
|
|
|
|
RETURN shine_login_from_blockchain_name(target_bch_name_in);
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION shine_blocks_line_integrity_bi()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
prev_block RECORD;
|
|
BEGIN
|
|
IF NEW.line_code IS NULL
|
|
AND NEW.prev_line_number IS NULL
|
|
AND NEW.prev_line_hash IS NULL
|
|
AND NEW.this_line_number IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF NEW.msg_type NOT IN (0, 1, 3, 4) THEN
|
|
RAISE EXCEPTION 'LINE_ERR_UNSUPPORTED_TYPE_WITH_LINE';
|
|
END IF;
|
|
|
|
IF NEW.line_code IS NULL
|
|
OR NEW.prev_line_number IS NULL
|
|
OR NEW.prev_line_hash IS NULL
|
|
OR NEW.this_line_number IS NULL THEN
|
|
RAISE EXCEPTION 'LINE_ERR_PARTIAL_FIELDS';
|
|
END IF;
|
|
|
|
SELECT *
|
|
INTO prev_block
|
|
FROM blocks
|
|
WHERE bch_name = NEW.bch_name
|
|
AND block_number = NEW.prev_line_number
|
|
LIMIT 1;
|
|
|
|
IF NOT FOUND THEN
|
|
RAISE EXCEPTION 'LINE_ERR_NO_PREV';
|
|
END IF;
|
|
|
|
IF prev_block.block_hash IS DISTINCT FROM NEW.prev_line_hash THEN
|
|
RAISE EXCEPTION 'LINE_ERR_PREV_HASH_MISMATCH';
|
|
END IF;
|
|
|
|
IF NEW.prev_line_number <> NEW.line_code
|
|
AND prev_block.line_code IS DISTINCT FROM NEW.line_code THEN
|
|
RAISE EXCEPTION 'LINE_ERR_LINE_CODE_MISMATCH';
|
|
END IF;
|
|
|
|
IF NEW.prev_line_number = NEW.line_code THEN
|
|
IF NEW.this_line_number <> (CASE WHEN NEW.msg_type = 1 THEN 0 ELSE 1 END) THEN
|
|
RAISE EXCEPTION 'LINE_ERR_FIRST_STEP_BAD_THIS';
|
|
END IF;
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF prev_block.this_line_number IS NULL THEN
|
|
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
|
|
END IF;
|
|
|
|
IF NEW.msg_type = 1 THEN
|
|
IF NEW.this_line_number <> prev_block.this_line_number
|
|
AND NEW.this_line_number <> prev_block.this_line_number + 1 THEN
|
|
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
|
|
END IF;
|
|
ELSE
|
|
IF NEW.this_line_number <> prev_block.this_line_number + 1 THEN
|
|
RAISE EXCEPTION 'LINE_ERR_THIS_LINE_BAD_STEP';
|
|
END IF;
|
|
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;
|
|
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;
|
|
|
|
DELETE FROM connections_state
|
|
WHERE login = NEW.login
|
|
AND rel_type = NEW.msg_sub_type
|
|
AND to_login = resolved_login;
|
|
|
|
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;
|
|
|
|
DELETE FROM connections_state
|
|
WHERE login = NEW.login
|
|
AND rel_type = positive_rel_type
|
|
AND to_login = resolved_login;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_like_ai()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
DECLARE
|
|
previous_sub_type INTEGER;
|
|
BEGIN
|
|
IF NEW.msg_type <> 2 OR NEW.msg_sub_type NOT IN (1, 2) THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
INSERT INTO message_stats (
|
|
to_login, to_bch_name, to_block_number, to_block_hash,
|
|
likes_count, replies_count, edits_count
|
|
) VALUES (
|
|
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
|
0, 0, 0
|
|
)
|
|
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
|
|
|
|
SELECT last_sub_type
|
|
INTO previous_sub_type
|
|
FROM reactions_state
|
|
WHERE from_login = NEW.login
|
|
AND from_bch_name = NEW.bch_name
|
|
AND reaction_type = 1
|
|
AND to_login = NEW.to_login
|
|
AND to_bch_name = NEW.to_bch_name
|
|
AND to_block_number = NEW.to_block_number
|
|
AND to_block_hash = NEW.to_block_hash
|
|
LIMIT 1;
|
|
|
|
IF NEW.msg_sub_type = 1 AND previous_sub_type IS DISTINCT FROM 1 THEN
|
|
UPDATE message_stats
|
|
SET likes_count = likes_count + 1
|
|
WHERE to_login = NEW.to_login
|
|
AND to_bch_name = NEW.to_bch_name
|
|
AND to_block_number = NEW.to_block_number
|
|
AND to_block_hash = NEW.to_block_hash;
|
|
ELSIF NEW.msg_sub_type = 2 AND previous_sub_type = 1 THEN
|
|
UPDATE message_stats
|
|
SET likes_count = GREATEST(0, likes_count - 1)
|
|
WHERE to_login = NEW.to_login
|
|
AND to_bch_name = NEW.to_bch_name
|
|
AND to_block_number = NEW.to_block_number
|
|
AND to_block_hash = NEW.to_block_hash;
|
|
END IF;
|
|
|
|
INSERT INTO reactions_state (
|
|
from_login, from_bch_name, reaction_type,
|
|
to_login, to_bch_name, to_block_number, to_block_hash,
|
|
last_sub_type
|
|
) VALUES (
|
|
NEW.login, NEW.bch_name, 1,
|
|
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
|
NEW.msg_sub_type
|
|
)
|
|
ON CONFLICT (
|
|
from_login, from_bch_name, reaction_type,
|
|
to_login, to_bch_name, to_block_number, to_block_hash
|
|
) DO UPDATE SET
|
|
last_sub_type = EXCLUDED.last_sub_type;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION shine_blocks_message_stats_reply_ai()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.msg_type <> 1 OR NEW.msg_sub_type <> 20 THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
INSERT INTO message_stats (
|
|
to_login, to_bch_name, to_block_number, to_block_hash,
|
|
likes_count, replies_count, edits_count
|
|
) VALUES (
|
|
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
|
0, 0, 0
|
|
)
|
|
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
|
|
|
|
UPDATE message_stats
|
|
SET replies_count = replies_count + 1
|
|
WHERE to_login = NEW.to_login
|
|
AND to_bch_name = NEW.to_bch_name
|
|
AND to_block_number = NEW.to_block_number
|
|
AND to_block_hash = NEW.to_block_hash;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION shine_blocks_edit_apply_ai()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
IF NEW.msg_type <> 1 OR NEW.msg_sub_type NOT IN (11, 21) THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
UPDATE blocks
|
|
SET edited_by_block_number = NEW.block_number
|
|
WHERE login = NEW.login
|
|
AND bch_name = NEW.bch_name
|
|
AND block_number = NEW.to_block_number
|
|
AND NEW.to_block_number IS NOT NULL;
|
|
|
|
IF NEW.to_login IS NULL OR NEW.to_bch_name IS NULL OR NEW.to_block_number IS NULL OR NEW.to_block_hash IS NULL THEN
|
|
RETURN NEW;
|
|
END IF;
|
|
|
|
INSERT INTO message_stats (
|
|
to_login, to_bch_name, to_block_number, to_block_hash,
|
|
likes_count, replies_count, edits_count
|
|
) VALUES (
|
|
NEW.to_login, NEW.to_bch_name, NEW.to_block_number, NEW.to_block_hash,
|
|
0, 0, 0
|
|
)
|
|
ON CONFLICT (to_login, to_bch_name, to_block_number, to_block_hash) DO NOTHING;
|
|
|
|
UPDATE message_stats
|
|
SET edits_count = edits_count + 1
|
|
WHERE to_login = NEW.to_login
|
|
AND to_bch_name = NEW.to_bch_name
|
|
AND to_block_number = NEW.to_block_number
|
|
AND to_block_hash = NEW.to_block_hash;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
DROP TRIGGER IF EXISTS trg_blocks_line_integrity_bi ON blocks;
|
|
CREATE TRIGGER trg_blocks_line_integrity_bi
|
|
BEFORE INSERT ON blocks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION shine_blocks_line_integrity_bi();
|
|
|
|
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();
|
|
|
|
DROP TRIGGER IF EXISTS trg_blocks_message_stats_like_ai ON blocks;
|
|
CREATE TRIGGER trg_blocks_message_stats_like_ai
|
|
AFTER INSERT ON blocks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION shine_blocks_message_stats_like_ai();
|
|
|
|
DROP TRIGGER IF EXISTS trg_blocks_message_stats_reply_ai ON blocks;
|
|
CREATE TRIGGER trg_blocks_message_stats_reply_ai
|
|
AFTER INSERT ON blocks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION shine_blocks_message_stats_reply_ai();
|
|
|
|
DROP TRIGGER IF EXISTS trg_blocks_edit_apply_ai ON blocks;
|
|
CREATE TRIGGER trg_blocks_edit_apply_ai
|
|
AFTER INSERT ON blocks
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION shine_blocks_edit_apply_ai();
|
|
|
|
COMMIT;
|