383 lines
15 KiB
PL/PgSQL
383 lines
15 KiB
PL/PgSQL
-- =============================================================================
|
||
-- MY UPLOADER BOT - PRODUCTION DATABASE INITIALIZATION
|
||
-- =============================================================================
|
||
|
||
-- Создание расширений
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
|
||
CREATE EXTENSION IF NOT EXISTS "btree_gin";
|
||
|
||
-- Настройка SSL (если требуется)
|
||
-- ALTER SYSTEM SET ssl = on;
|
||
-- ALTER SYSTEM SET ssl_cert_file = '/var/lib/postgresql/server.crt';
|
||
-- ALTER SYSTEM SET ssl_key_file = '/var/lib/postgresql/server.key';
|
||
|
||
-- =============================================================================
|
||
-- СОЗДАНИЕ СХЕМ
|
||
-- =============================================================================
|
||
|
||
-- Основная схема приложения
|
||
CREATE SCHEMA IF NOT EXISTS app;
|
||
|
||
-- Схема для MY Network
|
||
CREATE SCHEMA IF NOT EXISTS my_network;
|
||
|
||
-- Схема для мониторинга
|
||
CREATE SCHEMA IF NOT EXISTS monitoring;
|
||
|
||
-- =============================================================================
|
||
-- ТАБЛИЦЫ ПОЛЬЗОВАТЕЛЕЙ
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS app.users (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
username VARCHAR(50) UNIQUE NOT NULL,
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password_hash VARCHAR(255) NOT NULL,
|
||
salt VARCHAR(255) NOT NULL,
|
||
is_active BOOLEAN DEFAULT true,
|
||
is_verified BOOLEAN DEFAULT false,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
last_login TIMESTAMP WITH TIME ZONE,
|
||
|
||
-- Индексы
|
||
CONSTRAINT users_username_check CHECK (length(username) >= 3),
|
||
CONSTRAINT users_email_check CHECK (email ~* '^[A-Za-z0-9._%-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$')
|
||
);
|
||
|
||
-- Индексы для пользователей
|
||
CREATE INDEX IF NOT EXISTS idx_users_username ON app.users(username);
|
||
CREATE INDEX IF NOT EXISTS idx_users_email ON app.users(email);
|
||
CREATE INDEX IF NOT EXISTS idx_users_active ON app.users(is_active);
|
||
CREATE INDEX IF NOT EXISTS idx_users_created_at ON app.users(created_at);
|
||
|
||
-- =============================================================================
|
||
-- ТАБЛИЦЫ КОНТЕНТА
|
||
-- =============================================================================
|
||
|
||
CREATE TABLE IF NOT EXISTS app.content (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
user_id UUID NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
|
||
filename VARCHAR(255) NOT NULL,
|
||
original_filename VARCHAR(255) NOT NULL,
|
||
content_type VARCHAR(100) NOT NULL,
|
||
file_size BIGINT NOT NULL,
|
||
file_hash VARCHAR(64) NOT NULL,
|
||
upload_path TEXT NOT NULL,
|
||
|
||
-- Метаданные
|
||
metadata JSONB DEFAULT '{}',
|
||
tags TEXT[] DEFAULT '{}',
|
||
|
||
-- MY Network данные
|
||
my_network_id VARCHAR(64) UNIQUE,
|
||
replication_status VARCHAR(20) DEFAULT 'pending',
|
||
replicated_nodes TEXT[] DEFAULT '{}',
|
||
|
||
-- Статус
|
||
status VARCHAR(20) DEFAULT 'uploaded',
|
||
is_public BOOLEAN DEFAULT false,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
-- Ограничения
|
||
CONSTRAINT content_file_size_check CHECK (file_size > 0),
|
||
CONSTRAINT content_status_check CHECK (status IN ('uploaded', 'processing', 'ready', 'error', 'deleted')),
|
||
CONSTRAINT content_replication_status_check CHECK (replication_status IN ('pending', 'replicating', 'replicated', 'failed'))
|
||
);
|
||
|
||
-- Индексы для контента
|
||
CREATE INDEX IF NOT EXISTS idx_content_user_id ON app.content(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_content_file_hash ON app.content(file_hash);
|
||
CREATE INDEX IF NOT EXISTS idx_content_my_network_id ON app.content(my_network_id);
|
||
CREATE INDEX IF NOT EXISTS idx_content_status ON app.content(status);
|
||
CREATE INDEX IF NOT EXISTS idx_content_replication_status ON app.content(replication_status);
|
||
CREATE INDEX IF NOT EXISTS idx_content_created_at ON app.content(created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_content_metadata ON app.content USING GIN(metadata);
|
||
CREATE INDEX IF NOT EXISTS idx_content_tags ON app.content USING GIN(tags);
|
||
|
||
-- =============================================================================
|
||
-- MY NETWORK ТАБЛИЦЫ
|
||
-- =============================================================================
|
||
|
||
-- Узлы MY Network
|
||
CREATE TABLE IF NOT EXISTS my_network.nodes (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
node_id VARCHAR(64) UNIQUE NOT NULL,
|
||
hostname VARCHAR(255) NOT NULL,
|
||
port INTEGER NOT NULL DEFAULT 15100,
|
||
public_key TEXT,
|
||
|
||
-- Статус узла
|
||
status VARCHAR(20) DEFAULT 'active',
|
||
last_seen TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
version VARCHAR(20),
|
||
|
||
-- Статистика
|
||
total_content BIGINT DEFAULT 0,
|
||
total_storage BIGINT DEFAULT 0,
|
||
|
||
-- Метаданные
|
||
metadata JSONB DEFAULT '{}',
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT nodes_status_check CHECK (status IN ('active', 'inactive', 'banned', 'maintenance'))
|
||
);
|
||
|
||
-- Индексы для узлов
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_node_id ON my_network.nodes(node_id);
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_status ON my_network.nodes(status);
|
||
CREATE INDEX IF NOT EXISTS idx_nodes_last_seen ON my_network.nodes(last_seen);
|
||
|
||
-- Репликация контента между узлами
|
||
CREATE TABLE IF NOT EXISTS my_network.content_replications (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
content_id UUID NOT NULL REFERENCES app.content(id) ON DELETE CASCADE,
|
||
source_node_id VARCHAR(64) NOT NULL,
|
||
target_node_id VARCHAR(64) NOT NULL,
|
||
|
||
-- Статус репликации
|
||
status VARCHAR(20) DEFAULT 'pending',
|
||
started_at TIMESTAMP WITH TIME ZONE,
|
||
completed_at TIMESTAMP WITH TIME ZONE,
|
||
error_message TEXT,
|
||
|
||
-- Метаданные
|
||
file_size BIGINT,
|
||
transfer_speed BIGINT, -- bytes per second
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT replications_status_check CHECK (status IN ('pending', 'in_progress', 'completed', 'failed', 'cancelled')),
|
||
CONSTRAINT replications_unique_transfer UNIQUE (content_id, target_node_id)
|
||
);
|
||
|
||
-- Индексы для репликации
|
||
CREATE INDEX IF NOT EXISTS idx_replications_content_id ON my_network.content_replications(content_id);
|
||
CREATE INDEX IF NOT EXISTS idx_replications_status ON my_network.content_replications(status);
|
||
CREATE INDEX IF NOT EXISTS idx_replications_source_node ON my_network.content_replications(source_node_id);
|
||
CREATE INDEX IF NOT EXISTS idx_replications_target_node ON my_network.content_replications(target_node_id);
|
||
|
||
-- =============================================================================
|
||
-- МОНИТОРИНГ И ЛОГИ
|
||
-- =============================================================================
|
||
|
||
-- Системные события
|
||
CREATE TABLE IF NOT EXISTS monitoring.system_events (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
event_type VARCHAR(50) NOT NULL,
|
||
severity VARCHAR(20) DEFAULT 'info',
|
||
source VARCHAR(100) NOT NULL,
|
||
message TEXT NOT NULL,
|
||
|
||
-- Дополнительные данные
|
||
details JSONB DEFAULT '{}',
|
||
user_id UUID REFERENCES app.users(id),
|
||
node_id VARCHAR(64),
|
||
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
CONSTRAINT events_severity_check CHECK (severity IN ('debug', 'info', 'warning', 'error', 'critical'))
|
||
);
|
||
|
||
-- Индексы для событий
|
||
CREATE INDEX IF NOT EXISTS idx_events_type ON monitoring.system_events(event_type);
|
||
CREATE INDEX IF NOT EXISTS idx_events_severity ON monitoring.system_events(severity);
|
||
CREATE INDEX IF NOT EXISTS idx_events_source ON monitoring.system_events(source);
|
||
CREATE INDEX IF NOT EXISTS idx_events_created_at ON monitoring.system_events(created_at);
|
||
CREATE INDEX IF NOT EXISTS idx_events_user_id ON monitoring.system_events(user_id);
|
||
|
||
-- Метрики производительности
|
||
CREATE TABLE IF NOT EXISTS monitoring.performance_metrics (
|
||
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
|
||
metric_name VARCHAR(100) NOT NULL,
|
||
metric_value NUMERIC NOT NULL,
|
||
metric_unit VARCHAR(20),
|
||
|
||
-- Контекст
|
||
source VARCHAR(100) NOT NULL,
|
||
tags JSONB DEFAULT '{}',
|
||
|
||
measured_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
||
-- Партиционирование по времени
|
||
PARTITION BY RANGE (measured_at)
|
||
);
|
||
|
||
-- Создание партиций для метрик (по месяцам)
|
||
CREATE TABLE IF NOT EXISTS monitoring.performance_metrics_current
|
||
PARTITION OF monitoring.performance_metrics
|
||
FOR VALUES FROM (date_trunc('month', NOW())) TO (date_trunc('month', NOW() + interval '1 month'));
|
||
|
||
-- Индексы для метрик
|
||
CREATE INDEX IF NOT EXISTS idx_metrics_name_time ON monitoring.performance_metrics(metric_name, measured_at);
|
||
CREATE INDEX IF NOT EXISTS idx_metrics_source ON monitoring.performance_metrics(source);
|
||
|
||
-- =============================================================================
|
||
-- ФУНКЦИИ И ТРИГГЕРЫ
|
||
-- =============================================================================
|
||
|
||
-- Функция обновления updated_at
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- Триггеры для обновления updated_at
|
||
CREATE TRIGGER trigger_users_updated_at
|
||
BEFORE UPDATE ON app.users
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_content_updated_at
|
||
BEFORE UPDATE ON app.content
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER trigger_nodes_updated_at
|
||
BEFORE UPDATE ON my_network.nodes
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- Функция логирования событий
|
||
CREATE OR REPLACE FUNCTION log_system_event(
|
||
p_event_type VARCHAR(50),
|
||
p_message TEXT,
|
||
p_severity VARCHAR(20) DEFAULT 'info',
|
||
p_source VARCHAR(100) DEFAULT 'system',
|
||
p_details JSONB DEFAULT '{}',
|
||
p_user_id UUID DEFAULT NULL,
|
||
p_node_id VARCHAR(64) DEFAULT NULL
|
||
)
|
||
RETURNS UUID AS $$
|
||
DECLARE
|
||
event_id UUID;
|
||
BEGIN
|
||
INSERT INTO monitoring.system_events (
|
||
event_type, message, severity, source, details, user_id, node_id
|
||
) VALUES (
|
||
p_event_type, p_message, p_severity, p_source, p_details, p_user_id, p_node_id
|
||
) RETURNING id INTO event_id;
|
||
|
||
RETURN event_id;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- =============================================================================
|
||
-- ПРЕДСТАВЛЕНИЯ (VIEWS)
|
||
-- =============================================================================
|
||
|
||
-- Активные узлы
|
||
CREATE OR REPLACE VIEW my_network.active_nodes AS
|
||
SELECT
|
||
node_id,
|
||
hostname,
|
||
port,
|
||
status,
|
||
last_seen,
|
||
total_content,
|
||
total_storage,
|
||
version
|
||
FROM my_network.nodes
|
||
WHERE status = 'active'
|
||
AND last_seen > NOW() - INTERVAL '1 hour';
|
||
|
||
-- Статистика контента
|
||
CREATE OR REPLACE VIEW app.content_stats AS
|
||
SELECT
|
||
COUNT(*) as total_files,
|
||
SUM(file_size) as total_size,
|
||
COUNT(DISTINCT user_id) as unique_users,
|
||
COUNT(*) FILTER (WHERE status = 'ready') as ready_files,
|
||
COUNT(*) FILTER (WHERE replication_status = 'replicated') as replicated_files,
|
||
AVG(file_size) as avg_file_size
|
||
FROM app.content
|
||
WHERE status != 'deleted';
|
||
|
||
-- =============================================================================
|
||
-- НАЧАЛЬНЫЕ ДАННЫЕ
|
||
-- =============================================================================
|
||
|
||
-- Создание администратора (пароль: admin123)
|
||
-- Хеш создан с использованием bcrypt
|
||
INSERT INTO app.users (username, email, password_hash, salt, is_active, is_verified)
|
||
VALUES (
|
||
'admin',
|
||
'admin@mynetwork.local',
|
||
'$2b$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LowN5wVrtJr.pjdXW',
|
||
'production_salt_2024',
|
||
true,
|
||
true
|
||
) ON CONFLICT (username) DO NOTHING;
|
||
|
||
-- Регистрация локального узла
|
||
INSERT INTO my_network.nodes (node_id, hostname, port, status, version, metadata)
|
||
VALUES (
|
||
'local-production-node',
|
||
'localhost',
|
||
15100,
|
||
'active',
|
||
'1.0.0',
|
||
'{"type": "production", "bootstrap": true}'
|
||
) ON CONFLICT (node_id) DO NOTHING;
|
||
|
||
-- Начальное событие
|
||
SELECT log_system_event(
|
||
'system_init',
|
||
'Production database initialized successfully',
|
||
'info',
|
||
'database',
|
||
'{"version": "1.0.0", "environment": "production"}'
|
||
);
|
||
|
||
-- =============================================================================
|
||
-- НАСТРОЙКИ ПРОИЗВОДИТЕЛЬНОСТИ
|
||
-- =============================================================================
|
||
|
||
-- Оптимизация для production
|
||
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
|
||
ALTER SYSTEM SET track_activities = on;
|
||
ALTER SYSTEM SET track_counts = on;
|
||
ALTER SYSTEM SET track_io_timing = on;
|
||
ALTER SYSTEM SET log_statement = 'mod';
|
||
ALTER SYSTEM SET log_min_duration_statement = 1000;
|
||
ALTER SYSTEM SET max_connections = 200;
|
||
ALTER SYSTEM SET shared_buffers = '256MB';
|
||
ALTER SYSTEM SET effective_cache_size = '1GB';
|
||
ALTER SYSTEM SET maintenance_work_mem = '64MB';
|
||
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
|
||
ALTER SYSTEM SET wal_buffers = '16MB';
|
||
|
||
-- Применение настроек
|
||
SELECT pg_reload_conf();
|
||
|
||
-- =============================================================================
|
||
-- ПРАВА ДОСТУПА
|
||
-- =============================================================================
|
||
|
||
-- Права для пользователя приложения
|
||
GRANT USAGE ON SCHEMA app TO my_user;
|
||
GRANT USAGE ON SCHEMA my_network TO my_user;
|
||
GRANT USAGE ON SCHEMA monitoring TO my_user;
|
||
|
||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA app TO my_user;
|
||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA my_network TO my_user;
|
||
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA monitoring TO my_user;
|
||
|
||
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA app TO my_user;
|
||
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA my_network TO my_user;
|
||
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA monitoring TO my_user;
|
||
|
||
-- Права на функции
|
||
GRANT EXECUTE ON FUNCTION update_updated_at_column() TO my_user;
|
||
GRANT EXECUTE ON FUNCTION log_system_event(VARCHAR, TEXT, VARCHAR, VARCHAR, JSONB, UUID, VARCHAR) TO my_user;
|
||
|
||
COMMIT;
|
||
|
||
-- Финальное сообщение
|
||
\echo 'MY Uploader Bot production database initialized successfully!'
|
||
\echo 'Default admin user: admin / admin123'
|
||
\echo 'Please change the default password immediately!' |