182 lines
7.2 KiB
SQL
182 lines
7.2 KiB
SQL
-- ============================================================================
|
|
-- Somfy Tahoma Datenbank Schema
|
|
-- Normalisierte Struktur für Aktoren, Sensoren und ihre Parameter
|
|
-- ============================================================================
|
|
|
|
-- Datenbank erstellen (falls noch nicht vorhanden)
|
|
-- CREATE DATABASE IF NOT EXISTS EnergyFlow CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
|
|
-- USE EnergyFlow;
|
|
|
|
-- ============================================================================
|
|
-- HAUPTTABELLEN
|
|
-- ============================================================================
|
|
|
|
-- Tabelle: actors
|
|
-- Speichert alle Aktoren (Geräte mit Steuerungsfunktion)
|
|
CREATE TABLE IF NOT EXISTS `actors` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`type` varchar(50) NOT NULL COMMENT 'Gerätetyp z.B. RollerShutter',
|
|
`name` varchar(70) NOT NULL COMMENT 'Name des Geräts',
|
|
`parameters` text DEFAULT NULL COMMENT 'Zusätzliche Meta-Informationen als JSON',
|
|
`url` varchar(100) NOT NULL COMMENT 'Tahoma Device URL',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `url` (`url`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- Tabelle: sensors
|
|
-- Speichert alle Sensoren (Geräte die Werte melden)
|
|
CREATE TABLE IF NOT EXISTS `sensors` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`type` varchar(50) NOT NULL COMMENT 'Sensortyp z.B. TemperatureSensor',
|
|
`name` varchar(70) NOT NULL COMMENT 'Name des Sensors',
|
|
`parameters` text DEFAULT NULL COMMENT 'Zusätzliche Meta-Informationen als JSON',
|
|
`url` varchar(100) NOT NULL COMMENT 'Tahoma Device URL',
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `url` (`url`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- ============================================================================
|
|
-- AKTOR-BEZOGENE TABELLEN
|
|
-- ============================================================================
|
|
|
|
-- Tabelle: actor_commands
|
|
-- Speichert alle verfügbaren Commands für jeden Aktor
|
|
CREATE TABLE IF NOT EXISTS `actor_commands` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`actor_id` int(11) NOT NULL COMMENT 'Referenz zum Aktor',
|
|
`command_name` varchar(100) NOT NULL COMMENT 'Name des Commands z.B. setPosition, open, close',
|
|
PRIMARY KEY (`id`),
|
|
KEY `actor_id` (`actor_id`),
|
|
CONSTRAINT `fk_actor_commands_actor`
|
|
FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- Tabelle: command_parameters
|
|
-- Speichert die Parameter für jeden Command
|
|
CREATE TABLE IF NOT EXISTS `command_parameters` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`command_id` int(11) NOT NULL COMMENT 'Referenz zum Command',
|
|
`parameter_name` varchar(100) NOT NULL COMMENT 'Name des Parameters z.B. position',
|
|
`parameter_type` varchar(50) DEFAULT NULL COMMENT 'Datentyp z.B. integer, string',
|
|
`min_value` decimal(10,2) DEFAULT NULL COMMENT 'Minimaler Wert (falls numerisch)',
|
|
`max_value` decimal(10,2) DEFAULT NULL COMMENT 'Maximaler Wert (falls numerisch)',
|
|
`possible_values` text DEFAULT NULL COMMENT 'JSON Array mit möglichen Werten (für Enums)',
|
|
PRIMARY KEY (`id`),
|
|
KEY `command_id` (`command_id`),
|
|
CONSTRAINT `fk_command_parameters_command`
|
|
FOREIGN KEY (`command_id`) REFERENCES `actor_commands`(`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- ============================================================================
|
|
-- SENSOR-BEZOGENE TABELLEN
|
|
-- ============================================================================
|
|
|
|
-- Tabelle: sensor_states
|
|
-- Speichert alle verfügbaren States für jeden Sensor
|
|
CREATE TABLE IF NOT EXISTS `sensor_states` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`sensor_id` int(11) NOT NULL COMMENT 'Referenz zum Sensor',
|
|
`state_name` varchar(100) NOT NULL COMMENT 'Name des State z.B. core:TemperatureState',
|
|
`state_type` int(11) DEFAULT NULL COMMENT 'State-Typ Code aus Tahoma API',
|
|
`current_value` varchar(255) DEFAULT NULL COMMENT 'Aktueller Wert des State',
|
|
`unit` varchar(20) DEFAULT NULL COMMENT 'Einheit z.B. °C, %, lux',
|
|
`last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `sensor_id` (`sensor_id`),
|
|
CONSTRAINT `fk_sensor_states_sensor`
|
|
FOREIGN KEY (`sensor_id`) REFERENCES `sensors`(`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- ============================================================================
|
|
-- GEMEINSAME TABELLE FÜR ACTOR STATES (optional)
|
|
-- ============================================================================
|
|
|
|
-- Tabelle: actor_states
|
|
-- Speichert die aktuellen States von Aktoren (z.B. aktuelle Position)
|
|
CREATE TABLE IF NOT EXISTS `actor_states` (
|
|
`id` int(11) NOT NULL AUTO_INCREMENT,
|
|
`actor_id` int(11) NOT NULL COMMENT 'Referenz zum Aktor',
|
|
`state_name` varchar(100) NOT NULL COMMENT 'Name des State z.B. core:ClosureState',
|
|
`state_type` int(11) DEFAULT NULL COMMENT 'State-Typ Code aus Tahoma API',
|
|
`current_value` varchar(255) DEFAULT NULL COMMENT 'Aktueller Wert des State',
|
|
`unit` varchar(20) DEFAULT NULL COMMENT 'Einheit falls vorhanden',
|
|
`last_updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `actor_id` (`actor_id`),
|
|
CONSTRAINT `fk_actor_states_actor`
|
|
FOREIGN KEY (`actor_id`) REFERENCES `actors`(`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
|
|
|
|
-- ============================================================================
|
|
-- INDIZES FÜR PERFORMANCE
|
|
-- ============================================================================
|
|
|
|
-- Zusätzliche Indizes für häufige Queries
|
|
CREATE INDEX idx_actors_type ON actors(type);
|
|
CREATE INDEX idx_sensors_type ON sensors(type);
|
|
CREATE INDEX idx_actor_commands_name ON actor_commands(command_name);
|
|
CREATE INDEX idx_sensor_states_name ON sensor_states(state_name);
|
|
CREATE INDEX idx_actor_states_name ON actor_states(state_name);
|
|
|
|
-- ============================================================================
|
|
-- VIEWS (optional - für einfachere Queries)
|
|
-- ============================================================================
|
|
|
|
-- View: Alle Aktoren mit ihren Commands
|
|
CREATE OR REPLACE VIEW view_actors_with_commands AS
|
|
SELECT
|
|
a.id as actor_id,
|
|
a.name as actor_name,
|
|
a.type as actor_type,
|
|
a.url as actor_url,
|
|
ac.id as command_id,
|
|
ac.command_name,
|
|
cp.parameter_name,
|
|
cp.parameter_type,
|
|
cp.min_value,
|
|
cp.max_value,
|
|
cp.possible_values
|
|
FROM actors a
|
|
LEFT JOIN actor_commands ac ON a.id = ac.actor_id
|
|
LEFT JOIN command_parameters cp ON ac.id = cp.command_id
|
|
ORDER BY a.id, ac.id, cp.id;
|
|
|
|
-- View: Alle Sensoren mit ihren States
|
|
CREATE OR REPLACE VIEW view_sensors_with_states AS
|
|
SELECT
|
|
s.id as sensor_id,
|
|
s.name as sensor_name,
|
|
s.type as sensor_type,
|
|
s.url as sensor_url,
|
|
ss.state_name,
|
|
ss.state_type,
|
|
ss.current_value,
|
|
ss.unit,
|
|
ss.last_updated
|
|
FROM sensors s
|
|
LEFT JOIN sensor_states ss ON s.id = ss.sensor_id
|
|
ORDER BY s.id, ss.id;
|
|
|
|
-- View: Übersicht aller Geräte
|
|
CREATE OR REPLACE VIEW view_all_devices AS
|
|
SELECT
|
|
'actor' as device_category,
|
|
id,
|
|
type,
|
|
name,
|
|
url
|
|
FROM actors
|
|
UNION ALL
|
|
SELECT
|
|
'sensor' as device_category,
|
|
id,
|
|
type,
|
|
name,
|
|
url
|
|
FROM sensors
|
|
ORDER BY device_category, name;
|