sql/create_pg_db.sql - master - Magister/Venera_20

sql/create_pg_db.sql
167 строк7 KB
Magister
02 июл 2026, 23:59
02 июл 2026, 23:59182c551
-- SQL скрипт для создания базы данных Venera в PostgreSQL
-- Версия: 1.0.0

-- ��оздание базы данных (выполнять от имени пользователя с правами CREATE DATABASE)
-- CREATE DATABASE Venera;

-- Подключение к базе данных
-- \c Venera

-- Создание расширения для работы с UUID
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Создание таблицы для хранения данных
CREATE TABLE IF NOT EXISTS data_records (
    id SERIAL PRIMARY KEY,
    source_name VARCHAR(255) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value TEXT NOT NULL,
    date_first TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    date_last TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для ускорения поиска
CREATE INDEX IF NOT EXISTS idx_data_records_source_name ON data_records(source_name);
CREATE INDEX IF NOT EXISTS idx_data_records_key ON data_records(key);
CREATE INDEX IF NOT EXISTS idx_data_records_value ON data_records(value);
CREATE INDEX IF NOT EXISTS idx_data_records_date_first ON data_records(date_first);
CREATE INDEX IF NOT EXISTS idx_data_records_date_last ON data_records(date_last);

-- Создание составного индекса для частых запросов
CREATE INDEX IF NOT EXISTS idx_data_records_source_key ON data_records(source_name, key);

-- Создание уникального индекса для предотвращения дубликатов
CREATE UNIQUE INDEX IF NOT EXISTS idx_data_records_unique ON data_records(source_name, key, value);

-- Создание таблицы для хранения статистики
CREATE TABLE IF NOT EXISTS statistics (
    id SERIAL PRIMARY KEY,
    process_id VARCHAR(255) NOT NULL,
    packets_processed BIGINT DEFAULT 0,
    keys_parsed BIGINT DEFAULT 0,
    entries_added_list BIGINT DEFAULT 0,
    entries_added_set BIGINT DEFAULT 0,
    entries_inserted_pg BIGINT DEFAULT 0,
    errors BIGINT DEFAULT 0,
    cpu_usage FLOAT DEFAULT 0,
    memory_usage BIGINT DEFAULT 0,
    throughput BIGINT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для таблицы статистики
CREATE INDEX IF NOT EXISTS idx_statistics_process_id ON statistics(process_id);
CREATE INDEX IF NOT EXISTS idx_statistics_created_at ON statistics(created_at);

-- Создание таблицы для хранения алертов
CREATE TABLE IF NOT EXISTS alerts (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50) NOT NULL,
    description TEXT,
    process_id VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для таблицы алертов
CREATE INDEX IF NOT EXISTS idx_alerts_code ON alerts(code);
CREATE INDEX IF NOT EXISTS idx_alerts_process_id ON alerts(process_id);
CREATE INDEX IF NOT EXISTS idx_alerts_created_at ON alerts(created_at);

-- Создание таблицы для хранения логов
CREATE TABLE IF NOT EXISTS logs (
    id SERIAL PRIMARY KEY,
    level VARCHAR(20) NOT NULL,
    message TEXT NOT NULL,
    source VARCHAR(255),
    process_id VARCHAR(255),
    line_number INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для таблицы логов
CREATE INDEX IF NOT EXISTS idx_logs_level ON logs(level);
CREATE INDEX IF NOT EXISTS idx_logs_source ON logs(source);
CREATE INDEX IF NOT EXISTS idx_logs_process_id ON logs(process_id);
CREATE INDEX IF NOT EXISTS idx_logs_created_at ON logs(created_at);

-- Создание таблицы для хранения конфигурации
CREATE TABLE IF NOT EXISTS config (
    id SERIAL PRIMARY KEY,
    section VARCHAR(100) NOT NULL,
    key VARCHAR(255) NOT NULL,
    value TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(section, key)
);

-- Создание индексов для таблицы конфигурации
CREATE INDEX IF NOT EXISTS idx_config_section ON config(section);
CREATE INDEX IF NOT EXISTS idx_config_key ON config(key);

-- Создание таблицы для хранения версий манифеста
CREATE TABLE IF NOT EXISTS manifest_versions (
    id SERIAL PRIMARY KEY,
    version VARCHAR(50) NOT NULL,
    build_date TIMESTAMP WITH TIME ZONE,
    git_commit VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Создание индексов для таблицы версий манифеста
CREATE INDEX IF NOT EXISTS idx_manifest_versions_version ON manifest_versions(version);
CREATE INDEX IF NOT EXISTS idx_manifest_versions_created_at ON manifest_versions(created_at);

-- Создание функции для обновления поля updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Создание триггеров для автоматического обновления updated_at
CREATE TRIGGER update_data_records_updated_at
    BEFORE UPDATE ON data_records
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_statistics_updated_at
    BEFORE UPDATE ON statistics
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_config_updated_at
    BEFORE UPDATE ON config
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Создание роли для приложения
CREATE ROLE venera_user WITH LOGIN PASSWORD 'venera_password';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO venera_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO venera_user;

-- Предоставление прав на использование схемы
GRANT USAGE, CREATE ON SCHEMA public TO venera_user;

-- Вставка начальных данных в таблицу конфигурации
INSERT INTO config (section, key, value) VALUES
    ('Generic', 'mode', 'tray'),
    ('Generic', 'auto_start_processes', 'false'),
    ('Generic', 'max_processes', '20'),
    ('Generic', 'web_server_port', '8080'),
    ('Generic', 'ram_critical_percent', '90'),
    ('Generic', 'disk_critical_percent', '95')
ON CONFLICT (section, key) DO NOTHING;

-- Вставка начальных данных в таблицу версий манифеста
INSERT INTO manifest_versions (version, build_date, git_commit) VALUES
    ('1.0.0', CURRENT_TIMESTAMP, 'initial')
ON CONFLICT DO NOTHING;

-- Завершение
-- \q