database/schema.sql - develop - h0tnanny/IotPlatform

database/schema.sql
113 строк5 KB
h0tnanny
Добавление функциональных требований
08 фев 2026, 23:02
08 фев 2026, 23:02a8a23c5
-- IoT LowCode Platform Database Schema
-- PostgreSQL 12+

-- Таблица для Workflows
CREATE TABLE IF NOT EXISTS workflows (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255),
    description TEXT,
    update_interval INTEGER,
    is_activated BOOLEAN NOT NULL DEFAULT FALSE,
    start_node_id VARCHAR(255) NOT NULL,
    trigger_type VARCHAR(50) NOT NULL DEFAULT 'manual',  -- 'manual', 'interval', 'cron', 'condition'
    trigger_config JSONB,                                 -- Конфигурация триггера (JSON)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для узлов Workflow
CREATE TABLE IF NOT EXISTS workflow_nodes (
    id VARCHAR(255) PRIMARY KEY,
    workflow_id VARCHAR(255) NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50) NOT NULL, -- 'ActionNode', 'ConditionNode', 'LoopNode'
    config JSONB NOT NULL, -- JSON конфигурация узла (code, nextNodeId, условия и т.д.)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для переменных Workflow
CREATE TABLE IF NOT EXISTS workflow_variables (
    id SERIAL PRIMARY KEY,
    workflow_id VARCHAR(255) NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50) NOT NULL, -- 'String', 'Number', 'Double', 'Boolean'
    value JSONB, -- Значение переменной
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(workflow_id, name)
);

-- Таблица для Equipment (источников данных)
CREATE TABLE IF NOT EXISTS equipment (
    id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    protocol VARCHAR(50) NOT NULL, -- 'http', 'udp', 'mqtt', 'coap'
    data_mode VARCHAR(50) NOT NULL, -- 'active', 'passive'
    endpoint TEXT NOT NULL,
    poll_interval INTEGER NOT NULL DEFAULT 0,
    mqtt_broker_url TEXT,            -- MQTT Broker URL (для протокола mqtt)
    mqtt_topic VARCHAR(500),         -- MQTT Topic (для протокола mqtt)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Таблица для переменных Equipment
CREATE TABLE IF NOT EXISTS equipment_variables (
    id SERIAL PRIMARY KEY,
    equipment_id VARCHAR(255) NOT NULL REFERENCES equipment(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(50) NOT NULL, -- 'String', 'Number', 'Double', 'Boolean'
    value JSONB, -- Значение переменной
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(equipment_id, name)
);

-- Индексы для производительности
CREATE INDEX IF NOT EXISTS idx_workflow_nodes_workflow_id ON workflow_nodes(workflow_id);
CREATE INDEX IF NOT EXISTS idx_workflow_variables_workflow_id ON workflow_variables(workflow_id);
CREATE INDEX IF NOT EXISTS idx_equipment_variables_equipment_id ON equipment_variables(equipment_id);
CREATE INDEX IF NOT EXISTS idx_workflows_is_activated ON workflows(is_activated);

-- Функция для автоматического обновления 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
DROP TRIGGER IF EXISTS update_workflows_updated_at ON workflows;
CREATE TRIGGER update_workflows_updated_at
    BEFORE UPDATE ON workflows
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

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

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

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

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