database/emulator_schema.sql - develop - h0tnanny/IotPlatform

database/emulator_schema.sql
48 строк2 KB
h0tnanny
Эмулирование устройств
07 фев 2026, 00:02
07 фев 2026, 00:02074eca7
-- Emulator Schema Migration for IoT Platform
-- Эмулируемые устройства для тестирования платформы

-- Таблица эмулируемых устройств
CREATE TABLE IF NOT EXISTS emulated_devices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    mode VARCHAR(20) NOT NULL,              -- 'server' или 'client'
    is_running BOOLEAN NOT NULL DEFAULT FALSE,
    server_path VARCHAR(255),               -- Путь для server-режима (авто)
    udp_port INTEGER,                       -- UDP порт для server-режима
    target_equipment_id VARCHAR(255) REFERENCES equipment(id) ON DELETE SET NULL,
    push_interval INTEGER NOT NULL DEFAULT 5000,  -- Интервал отправки (мс) для client-режима
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица переменных эмулируемых устройств
CREATE TABLE IF NOT EXISTS emulated_device_variables (
    id SERIAL PRIMARY KEY,
    device_id UUID NOT NULL REFERENCES emulated_devices(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50) NOT NULL,              -- 'Number', 'Double', 'Boolean', 'String'
    gen_config JSONB NOT NULL DEFAULT '{}', -- Конфигурация генерации значений
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(device_id, name)
);

-- Индексы
CREATE INDEX IF NOT EXISTS idx_emulated_devices_mode ON emulated_devices(mode);
CREATE INDEX IF NOT EXISTS idx_emulated_devices_target ON emulated_devices(target_equipment_id);
CREATE INDEX IF NOT EXISTS idx_emulated_device_variables_device ON emulated_device_variables(device_id);

-- Триггеры для updated_at
DROP TRIGGER IF EXISTS update_emulated_devices_updated_at ON emulated_devices;
CREATE TRIGGER update_emulated_devices_updated_at
    BEFORE UPDATE ON emulated_devices
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

DROP TRIGGER IF EXISTS update_emulated_device_variables_updated_at ON emulated_device_variables;
CREATE TRIGGER update_emulated_device_variables_updated_at
    BEFORE UPDATE ON emulated_device_variables
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();