troy-grunt/netwatch
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
netwatch/init.sql
fixclean ec20fa2f96 docs: update open TODO status and patchpanel documentation 
- refresh TODO.md section for bugs/docs/status files

- align implementation status for delete/auth progress

- mark patchpanel infrastructure and floor SVG editor status in README

- finalize database patchpanel status notes and init.sql connection rules
2026-02-18 09:29:57 +01:00

814 lines
32 KiB
SQL
Raw Permalink Blame History

-- phpMyAdmin SQL Dump
-- version 5.2.3
-- https://www.phpmyadmin.net/
--
-- Host: db
-- Erstellungszeit: 12. Feb 2026 um 13:38
-- Server-Version: 11.8.6-MariaDB-ubu2404
-- PHP-Version: 8.3.26
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Datenbank: `netdoc`
--
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `buildings`
--
CREATE TABLE `buildings` (
`id` int(11) NOT NULL,
`location_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `buildings`
--
INSERT INTO `buildings` (`id`, `location_id`, `name`, `comment`) VALUES
(1, 1, 'Gebäude', '');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `connections`
--
CREATE TABLE `connections` (
`id` int(11) NOT NULL,
`connection_type_id` int(11) NOT NULL,
`port_a_type` enum('device','module','outlet','patchpanel') NOT NULL,
`port_a_id` int(11) NOT NULL,
`port_b_type` enum('device','module','outlet','patchpanel') NOT NULL,
`port_b_id` int(11) NOT NULL,
`vlan_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`vlan_config`)),
`mode` varchar(50) DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `connection_types`
--
CREATE TABLE `connection_types` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`medium` enum('copper','fiber','coax','other') NOT NULL,
`duplex` enum('half','full','custom') DEFAULT 'custom',
`max_speed` varchar(50) DEFAULT NULL,
`color` varchar(20) DEFAULT NULL,
`line_style` enum('solid','dashed','dotted') DEFAULT 'solid',
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten fuer Tabelle `connection_types`
--
INSERT INTO `connection_types` (`id`, `name`, `medium`, `duplex`, `max_speed`, `color`, `line_style`, `comment`) VALUES
(1, 'Default', 'copper', 'custom', NULL, NULL, 'solid', 'Auto-created default type');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `devices`
--
CREATE TABLE `devices` (
`id` int(11) NOT NULL,
`device_type_id` int(11) NOT NULL,
`rack_id` int(11) DEFAULT NULL,
`name` varchar(255) NOT NULL,
`rack_position_he` int(11) DEFAULT NULL,
`rack_height_he` int(11) DEFAULT NULL,
`serial_number` varchar(255) DEFAULT NULL,
`comment` text DEFAULT NULL,
`web_config_url` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `devices`
--
INSERT INTO `devices` (`id`, `device_type_id`, `rack_id`, `name`, `rack_position_he`, `rack_height_he`, `serial_number`, `comment`, `web_config_url`) VALUES
(1, 2, 1, 'Aruba 24p PoE EG', 3, 1, '', '', 'http:192.168.2.5'),
(3, 2, 1, 'Aruba 24p PoE EG', 3, 1, '', '', 'http://192.168.2.5');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `device_ports`
--
CREATE TABLE `device_ports` (
`id` int(11) NOT NULL,
`device_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`port_type_id` int(11) DEFAULT NULL,
`status` enum('active','disabled') DEFAULT 'active',
`mode` varchar(50) DEFAULT NULL,
`vlan_config` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`vlan_config`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `device_ports`
--
INSERT INTO `device_ports` (`id`, `device_id`, `name`, `port_type_id`, `status`, `mode`, `vlan_config`) VALUES
(1, 3, 'Port 1', NULL, 'active', NULL, NULL),
(2, 3, 'Port 2', NULL, 'active', NULL, NULL),
(3, 3, 'Port 3', NULL, 'active', NULL, NULL),
(4, 3, 'Port 4', NULL, 'active', NULL, NULL),
(5, 3, 'Port 5', NULL, 'active', NULL, NULL),
(6, 3, 'Port 6', NULL, 'active', NULL, NULL),
(7, 3, 'Port 7', NULL, 'active', NULL, NULL),
(8, 3, 'Port 8', NULL, 'active', NULL, NULL),
(9, 3, 'Port 9', NULL, 'active', NULL, NULL),
(10, 3, 'Port 10', NULL, 'active', NULL, NULL),
(11, 3, 'Port 11', NULL, 'active', NULL, NULL),
(12, 3, 'Port 12', NULL, 'active', NULL, NULL),
(13, 3, 'Port 13', NULL, 'active', NULL, NULL),
(14, 3, 'Port 14', NULL, 'active', NULL, NULL),
(15, 3, 'Port 15', NULL, 'active', NULL, NULL),
(16, 3, 'Port 16', NULL, 'active', NULL, NULL),
(17, 3, 'Port 17', NULL, 'active', NULL, NULL),
(18, 3, 'Port 18', NULL, 'active', NULL, NULL),
(19, 3, 'Port 19', NULL, 'active', NULL, NULL),
(20, 3, 'Port 20', NULL, 'active', NULL, NULL),
(21, 3, 'Port 21', NULL, 'active', NULL, NULL),
(22, 3, 'Port 22', NULL, 'active', NULL, NULL),
(23, 3, 'Port 23', NULL, 'active', NULL, NULL),
(24, 3, 'Port 24', NULL, 'active', NULL, NULL),
(25, 3, 'Port 25', NULL, 'active', NULL, NULL),
(26, 3, 'Port 26', NULL, 'active', NULL, NULL),
(27, 3, 'Port 27', NULL, 'active', NULL, NULL),
(28, 3, 'Port 28', NULL, 'active', NULL, NULL);
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `device_port_modules`
--
CREATE TABLE `device_port_modules` (
`id` int(11) NOT NULL,
`device_port_id` int(11) NOT NULL,
`module_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `device_types`
--
CREATE TABLE `device_types` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`category` enum('switch','server','patchpanel','other') NOT NULL,
`image_path` varchar(255) DEFAULT NULL,
`image_type` enum('svg','bitmap') NOT NULL,
`comment` text DEFAULT NULL,
`shape_definition` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`shape_definition`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `device_types`
--
INSERT INTO `device_types` (`id`, `name`, `category`, `image_path`, `image_type`, `comment`, `shape_definition`) VALUES
(2, 'Aruba 24p PoE', 'switch', NULL, 'bitmap', 'HPE Networking Instant On 1930 24p Gigabit CL4 PoE 4p SFP+ 370W Switch JL684B', '{\"shapes\":[{\"id\":\"shape_1770878292291_976b1d\",\"type\":\"text\",\"x\":15,\"y\":15,\"width\":0,\"height\":0,\"radius\":0,\"text\":\"Aruba\",\"fontSize\":16,\"fill\":\"#2a2a2a\",\"stroke\":\"#2a2a2a\",\"strokeWidth\":0,\"isPort\":false,\"portName\":\"\"},{\"id\":\"shape_1770878328120_e52a87\",\"type\":\"rect\",\"x\":70,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 1\"},{\"id\":\"shape_1770878332271_922cc2\",\"type\":\"rect\",\"x\":70,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 2\"},{\"id\":\"shape_1770878455031_158061\",\"type\":\"rect\",\"x\":100,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 3\"},{\"id\":\"shape_1770878458151_0ce0b7\",\"type\":\"rect\",\"x\":100,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 4\"},{\"id\":\"shape_1770878459943_803dc4\",\"type\":\"rect\",\"x\":130,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 5\"},{\"id\":\"shape_1770878461112_7ef223\",\"type\":\"rect\",\"x\":130,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 6\"},{\"id\":\"shape_1770878462402_25e355\",\"type\":\"rect\",\"x\":160,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 7\"},{\"id\":\"shape_1770878463812_41c329\",\"type\":\"rect\",\"x\":160,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 8\"},{\"id\":\"shape_1770878465576_1d8fd0\",\"type\":\"rect\",\"x\":190,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 9\"},{\"id\":\"shape_1770878466939_4d3386\",\"type\":\"rect\",\"x\":190,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 10\"},{\"id\":\"shape_1770878468874_0bfa1f\",\"type\":\"rect\",\"x\":220,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 11\"},{\"id\":\"shape_1770878470105_9bff02\",\"type\":\"rect\",\"x\":220,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 12\"},{\"id\":\"shape_1770882348087_fa27ec\",\"type\":\"rect\",\"x\":270,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 13\"},{\"id\":\"shape_1770882349343_6ea6e3\",\"type\":\"rect\",\"x\":270,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 14\"},{\"id\":\"shape_1770882350720_535b82\",\"type\":\"rect\",\"x\":300,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 15\"},{\"id\":\"shape_1770882352077_6a2b8e\",\"type\":\"rect\",\"x\":300,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 16\"},{\"id\":\"shape_1770882355266_1b9f88\",\"type\":\"rect\",\"x\":330,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 17\"},{\"id\":\"shape_1770882356586_6023fb\",\"type\":\"rect\",\"x\":330,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 18\"},{\"id\":\"shape_1770882357860_0c6b2f\",\"type\":\"rect\",\"x\":360,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 19\"},{\"id\":\"shape_1770882358933_d90959\",\"type\":\"rect\",\"x\":360,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 20\"},{\"id\":\"shape_1770882360208_e20f6c\",\"type\":\"rect\",\"x\":390,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 21\"},{\"id\":\"shape_1770882361363_54363b\",\"type\":\"rect\",\"x\":390,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 22\"},{\"id\":\"shape_1770882363727_02ff1b\",\"type\":\"rect\",\"x\":420,\"y\":20,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 23\"},{\"id\":\"shape_1770882365872_1140df\",\"type\":\"rect\",\"x\":420,\"y\":50,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 24\"},{\"id\":\"shape_1770882830474_1a4ad4\",\"type\":\"rect\",\"x\":600,\"y\":20,\"width\":40,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 25\"},{\"id\":\"shape_1770882885151_f681dd\",\"type\":\"rect\",\"x\":660,\"y\":20,\"width\":40,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 27\"},{\"id\":\"shape_1770882886569_1edc3d\",\"type\":\"rect\",\"x\":600,\"y\":70,\"width\":40,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 26\"},{\"id\":\"shape_1770882887934_b8b7b2\",\"type\":\"rect\",\"x\":660,\"y\":70,\"width\":40,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 28\"}],\"meta\":{\"formFactor\":\"19\",\"heightHe\":1,\"canvasWidth\":760,\"canvasHeight\":120}}'),
(3, 'Sophos', 'server', NULL, 'bitmap', 'Firewall', '{\"shapes\":[{\"id\":\"shape_1770888157882_f93f40\",\"type\":\"rect\",\"x\":285,\"y\":30,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 1\\/LAN\"},{\"id\":\"shape_1770888166333_0ef5c8\",\"type\":\"rect\",\"x\":206,\"y\":53,\"width\":40,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":false,\"portName\":\"\"},{\"id\":\"shape_1770888169550_d61d77\",\"type\":\"rect\",\"x\":142,\"y\":60,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":false,\"portName\":\"\"},{\"id\":\"shape_1770888173647_29f4c0\",\"type\":\"rect\",\"x\":285,\"y\":60,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 2\\/WAN\"},{\"id\":\"shape_1770888176195_a28894\",\"type\":\"rect\",\"x\":315,\"y\":30,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 3\\/DMZ\"},{\"id\":\"shape_1770888177599_fe66be\",\"type\":\"rect\",\"x\":315,\"y\":60,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 4\"},{\"id\":\"shape_1770888180323_59f711\",\"type\":\"rect\",\"x\":345,\"y\":30,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 5\"},{\"id\":\"shape_1770888192869_9ab1c4\",\"type\":\"rect\",\"x\":345,\"y\":60,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 6\"},{\"id\":\"shape_1770888194788_6a0f6e\",\"type\":\"rect\",\"x\":375,\"y\":30,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 7\"},{\"id\":\"shape_1770888196544_0ae796\",\"type\":\"rect\",\"x\":375,\"y\":60,\"width\":30,\"height\":30,\"radius\":0,\"text\":\"Text\",\"fontSize\":16,\"fill\":\"#d9e8b3\",\"stroke\":\"#4d5f27\",\"strokeWidth\":1,\"isPort\":true,\"portName\":\"Port 8\"}],\"meta\":{\"formFactor\":\"10\",\"heightHe\":1,\"canvasWidth\":420,\"canvasHeight\":120}}');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `device_type_ports`
--
CREATE TABLE `device_type_ports` (
`id` int(11) NOT NULL,
`device_type_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`port_type_id` int(11) DEFAULT NULL,
`x` int(11) NOT NULL,
`y` int(11) NOT NULL,
`metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `device_type_ports`
--
INSERT INTO `device_type_ports` (`id`, `device_type_id`, `name`, `port_type_id`, `x`, `y`, `metadata`) VALUES
(1, 2, 'Port 1', 1, 0, 0, NULL),
(2, 2, 'Port 2', 1, 0, 0, NULL),
(3, 2, 'Port 3', 1, 0, 0, NULL),
(4, 2, 'Port 4', 1, 0, 0, NULL),
(5, 2, 'Port 5', 1, 0, 0, NULL),
(6, 2, 'Port 6', 1, 0, 0, NULL),
(7, 2, 'Port 7', 1, 0, 0, NULL),
(8, 2, 'Port 8', 1, 0, 0, NULL),
(9, 2, 'Port 9', 1, 0, 0, NULL),
(10, 2, 'Port 10', 1, 0, 0, NULL),
(11, 2, 'Port 11', 1, 0, 0, NULL),
(12, 2, 'Port 12', 1, 0, 0, NULL),
(13, 2, 'Port 13', 1, 0, 0, NULL),
(14, 2, 'Port 14', 1, 0, 0, NULL),
(15, 2, 'Port 15', 1, 0, 0, NULL),
(16, 2, 'Port 16', 1, 0, 0, NULL),
(17, 2, 'Port 17', 1, 0, 0, NULL),
(18, 2, 'Port 18', 1, 0, 0, NULL),
(19, 2, 'Port 19', 1, 0, 0, NULL),
(20, 2, 'Port 20', 1, 0, 0, NULL),
(21, 2, 'Port 21', 1, 0, 0, NULL),
(22, 2, 'Port 22', 1, 0, 0, NULL),
(23, 2, 'Port 23', 1, 0, 0, NULL),
(24, 2, 'Port 24', 1, 0, 0, NULL),
(25, 2, 'Port 25', 2, 0, 0, NULL),
(26, 2, 'Port 26', 2, 0, 0, NULL),
(27, 2, 'Port 27', 2, 0, 0, NULL),
(28, 2, 'Port 28', 2, 0, 0, NULL),
(29, 3, 'Port 1/LAN', 1, 0, 0, NULL),
(30, 3, 'Port 2/WAN', 1, 0, 0, NULL),
(31, 3, 'Port 3/DMZ', 1, 0, 0, NULL),
(32, 3, 'Port 4', 1, 0, 0, NULL),
(33, 3, 'Port 5', 1, 0, 0, NULL),
(34, 3, 'Port 6', 1, 0, 0, NULL),
(35, 3, 'Port 7', 1, 0, 0, NULL),
(36, 3, 'Port 8', 1, 0, 0, NULL);
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `floors`
--
CREATE TABLE `floors` (
`id` int(11) NOT NULL,
`building_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`level` int(11) DEFAULT NULL,
`svg_path` varchar(255) DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `floors`
--
INSERT INTO `floors` (`id`, `building_id`, `name`, `level`, `svg_path`, `comment`) VALUES
(1, 1, 'Erdgeschoss', 0, 'uploads/floorplans/floor_698d9e1faaebd.svg', ''),
(2, 1, 'Erster Stock', 1, 'uploads/floorplans/floor_698d9d9ecc434.svg', '');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `locations`
--
CREATE TABLE `locations` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `locations`
--
INSERT INTO `locations` (`id`, `name`, `comment`) VALUES
(1, 'Fixclean HQ', 'Wolbecker Windmühle 32\r\n48167 Wolbeck');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `modules`
--
CREATE TABLE `modules` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`module_type` varchar(100) DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `module_ports`
--
CREATE TABLE `module_ports` (
`id` int(11) NOT NULL,
`module_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`port_type_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `network_outlets`
--
CREATE TABLE `network_outlets` (
`id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`x` int(11) DEFAULT NULL,
`y` int(11) DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `network_outlet_ports`
--
CREATE TABLE `network_outlet_ports` (
`id` int(11) NOT NULL,
`outlet_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`port_type_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `floor_patchpanels`
--
CREATE TABLE `floor_patchpanels` (
`id` int(11) NOT NULL,
`floor_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`pos_x` int(11) NOT NULL DEFAULT 0,
`pos_y` int(11) NOT NULL DEFAULT 0,
`width` int(11) NOT NULL DEFAULT 0,
`height` int(11) NOT NULL DEFAULT 0,
`port_count` int(11) NOT NULL DEFAULT 0,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `floor_patchpanel_ports`
--
CREATE TABLE `floor_patchpanel_ports` (
`id` int(11) NOT NULL,
`patchpanel_id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`port_type_id` int(11) DEFAULT NULL,
`status` enum('active','inactive','pending') NOT NULL DEFAULT 'active',
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- Port-Regeln fuer Patchpanel-Verbindungen:
-- 1) `connections.port_a_type` / `connections.port_b_type` nutzen den Wert `patchpanel` fuer IDs aus `floor_patchpanel_ports`.
-- 2) Patchpanel-Ports duerfen mit Patchpanel-Ports, Netzwerkbuchsen-Ports (`outlet`) oder Geraete-/Modulports verbunden werden.
-- 3) Die fachliche Validierung erfolgt in den Save-Handlern der Verbindungs-Module; das Schema bleibt polymorph.
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `port_types`
--
CREATE TABLE `port_types` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`medium` enum('copper','fiber','coax','other') NOT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `port_types`
--
INSERT INTO `port_types` (`id`, `name`, `medium`, `comment`) VALUES
(1, 'RJ45', 'copper', ''),
(2, 'SFP', 'fiber', '');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `racks`
--
CREATE TABLE `racks` (
`id` int(11) NOT NULL,
`floor_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`height_he` int(11) NOT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Daten für Tabelle `racks`
--
INSERT INTO `racks` (`id`, `floor_id`, `name`, `height_he`, `comment`) VALUES
(1, 1, 'Verteilerschrank Lager', 12, '');
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `rooms`
--
CREATE TABLE `rooms` (
`id` int(11) NOT NULL,
`floor_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`number` varchar(50) DEFAULT NULL,
`x` int(11) DEFAULT NULL,
`y` int(11) DEFAULT NULL,
`width` int(11) DEFAULT NULL,
`height` int(11) DEFAULT NULL,
`polygon_points` longtext DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
-- --------------------------------------------------------
--
-- Tabellenstruktur für Tabelle `vlans`
--
CREATE TABLE `vlans` (
`id` int(11) NOT NULL,
`vlan_id` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`comment` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;
--
-- Indizes der exportierten Tabellen
--
--
-- Indizes für die Tabelle `buildings`
--
ALTER TABLE `buildings`
ADD PRIMARY KEY (`id`),
ADD KEY `location_id` (`location_id`);
--
-- Indizes für die Tabelle `connections`
--
ALTER TABLE `connections`
ADD PRIMARY KEY (`id`),
ADD KEY `connection_type_id` (`connection_type_id`);
--
-- Indizes für die Tabelle `connection_types`
--
ALTER TABLE `connection_types`
ADD PRIMARY KEY (`id`);
--
-- Indizes für die Tabelle `devices`
--
ALTER TABLE `devices`
ADD PRIMARY KEY (`id`),
ADD KEY `device_type_id` (`device_type_id`),
ADD KEY `rack_id` (`rack_id`);
--
-- Indizes für die Tabelle `device_ports`
--
ALTER TABLE `device_ports`
ADD PRIMARY KEY (`id`),
ADD KEY `device_id` (`device_id`);
--
-- Indizes für die Tabelle `device_port_modules`
--
ALTER TABLE `device_port_modules`
ADD PRIMARY KEY (`id`),
ADD KEY `device_port_id` (`device_port_id`),
ADD KEY `module_id` (`module_id`);
--
-- Indizes für die Tabelle `device_types`
--
ALTER TABLE `device_types`
ADD PRIMARY KEY (`id`);
--
-- Indizes für die Tabelle `device_type_ports`
--
ALTER TABLE `device_type_ports`
ADD PRIMARY KEY (`id`),
ADD KEY `device_type_id` (`device_type_id`);
--
-- Indizes für die Tabelle `floors`
--
ALTER TABLE `floors`
ADD PRIMARY KEY (`id`),
ADD KEY `building_id` (`building_id`);
--
-- Indizes für die Tabelle `locations`
--
ALTER TABLE `locations`
ADD PRIMARY KEY (`id`);
--
-- Indizes für die Tabelle `modules`
--
ALTER TABLE `modules`
ADD PRIMARY KEY (`id`);
--
-- Indizes für die Tabelle `module_ports`
--
ALTER TABLE `module_ports`
ADD PRIMARY KEY (`id`),
ADD KEY `module_id` (`module_id`);
--
-- Indizes für die Tabelle `network_outlets`
--
ALTER TABLE `network_outlets`
ADD PRIMARY KEY (`id`),
ADD KEY `room_id` (`room_id`);
--
-- Indizes für die Tabelle `network_outlet_ports`
--
ALTER TABLE `network_outlet_ports`
ADD PRIMARY KEY (`id`),
ADD KEY `outlet_id` (`outlet_id`);
--
-- Indizes für die Tabelle `floor_patchpanels`
--
ALTER TABLE `floor_patchpanels`
ADD PRIMARY KEY (`id`),
ADD KEY `floor_id` (`floor_id`);
--
-- Indizes für die Tabelle `floor_patchpanel_ports`
--
ALTER TABLE `floor_patchpanel_ports`
ADD PRIMARY KEY (`id`),
ADD KEY `patchpanel_id` (`patchpanel_id`),
ADD KEY `port_type_id` (`port_type_id`);
--
-- Indizes für die Tabelle `port_types`
--
ALTER TABLE `port_types`
ADD PRIMARY KEY (`id`);
--
-- Indizes für die Tabelle `racks`
--
ALTER TABLE `racks`
ADD PRIMARY KEY (`id`),
ADD KEY `floor_id` (`floor_id`);
--
-- Indizes für die Tabelle `rooms`
--
ALTER TABLE `rooms`
ADD PRIMARY KEY (`id`),
ADD KEY `floor_id` (`floor_id`);
--
-- Indizes für die Tabelle `vlans`
--
ALTER TABLE `vlans`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT für exportierte Tabellen
--
--
-- AUTO_INCREMENT für Tabelle `buildings`
--
ALTER TABLE `buildings`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT für Tabelle `connections`
--
ALTER TABLE `connections`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `connection_types`
--
ALTER TABLE `connection_types`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `devices`
--
ALTER TABLE `devices`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT für Tabelle `device_ports`
--
ALTER TABLE `device_ports`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=29;
--
-- AUTO_INCREMENT für Tabelle `device_port_modules`
--
ALTER TABLE `device_port_modules`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `device_types`
--
ALTER TABLE `device_types`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
--
-- AUTO_INCREMENT für Tabelle `device_type_ports`
--
ALTER TABLE `device_type_ports`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=37;
--
-- AUTO_INCREMENT für Tabelle `floors`
--
ALTER TABLE `floors`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT für Tabelle `locations`
--
ALTER TABLE `locations`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT für Tabelle `modules`
--
ALTER TABLE `modules`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `module_ports`
--
ALTER TABLE `module_ports`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `network_outlets`
--
ALTER TABLE `network_outlets`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `network_outlet_ports`
--
ALTER TABLE `network_outlet_ports`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `floor_patchpanels`
--
ALTER TABLE `floor_patchpanels`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `floor_patchpanel_ports`
--
ALTER TABLE `floor_patchpanel_ports`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `port_types`
--
ALTER TABLE `port_types`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT für Tabelle `racks`
--
ALTER TABLE `racks`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT für Tabelle `rooms`
--
ALTER TABLE `rooms`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT für Tabelle `vlans`
--
ALTER TABLE `vlans`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- Constraints der exportierten Tabellen
--
--
-- Constraints der Tabelle `buildings`
--
ALTER TABLE `buildings`
ADD CONSTRAINT `buildings_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `connections`
--
ALTER TABLE `connections`
ADD CONSTRAINT `connections_ibfk_1` FOREIGN KEY (`connection_type_id`) REFERENCES `connection_types` (`id`);
--
-- Constraints der Tabelle `devices`
--
ALTER TABLE `devices`
ADD CONSTRAINT `devices_ibfk_1` FOREIGN KEY (`device_type_id`) REFERENCES `device_types` (`id`),
ADD CONSTRAINT `devices_ibfk_2` FOREIGN KEY (`rack_id`) REFERENCES `racks` (`id`) ON DELETE SET NULL;
--
-- Constraints der Tabelle `device_ports`
--
ALTER TABLE `device_ports`
ADD CONSTRAINT `device_ports_ibfk_1` FOREIGN KEY (`device_id`) REFERENCES `devices` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `device_port_modules`
--
ALTER TABLE `device_port_modules`
ADD CONSTRAINT `device_port_modules_ibfk_1` FOREIGN KEY (`device_port_id`) REFERENCES `device_ports` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `device_port_modules_ibfk_2` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `device_type_ports`
--
ALTER TABLE `device_type_ports`
ADD CONSTRAINT `device_type_ports_ibfk_1` FOREIGN KEY (`device_type_id`) REFERENCES `device_types` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `floors`
--
ALTER TABLE `floors`
ADD CONSTRAINT `floors_ibfk_1` FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `module_ports`
--
ALTER TABLE `module_ports`
ADD CONSTRAINT `module_ports_ibfk_1` FOREIGN KEY (`module_id`) REFERENCES `modules` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `network_outlets`
--
ALTER TABLE `network_outlets`
ADD CONSTRAINT `network_outlets_ibfk_1` FOREIGN KEY (`room_id`) REFERENCES `rooms` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `network_outlet_ports`
--
ALTER TABLE `network_outlet_ports`
ADD CONSTRAINT `network_outlet_ports_ibfk_1` FOREIGN KEY (`outlet_id`) REFERENCES `network_outlets` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `floor_patchpanels`
--
ALTER TABLE `floor_patchpanels`
ADD CONSTRAINT `floor_patchpanels_ibfk_1` FOREIGN KEY (`floor_id`) REFERENCES `floors` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `floor_patchpanel_ports`
--
ALTER TABLE `floor_patchpanel_ports`
ADD CONSTRAINT `floor_patchpanel_ports_ibfk_1` FOREIGN KEY (`patchpanel_id`) REFERENCES `floor_patchpanels` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `floor_patchpanel_ports_ibfk_2` FOREIGN KEY (`port_type_id`) REFERENCES `port_types` (`id`);
--
-- Constraints der Tabelle `racks`
--
ALTER TABLE `racks`
ADD CONSTRAINT `racks_ibfk_1` FOREIGN KEY (`floor_id`) REFERENCES `floors` (`id`) ON DELETE CASCADE;
--
-- Constraints der Tabelle `rooms`
--
ALTER TABLE `rooms`
ADD CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`floor_id`) REFERENCES `floors` (`id`) ON DELETE CASCADE;
COMMIT;
Reference in New Issue View Git Blame Copy Permalink