--
-- Host: localhost    Database: silemap
-- ------------------------------------------------------


--
-- Table structure for table `affected`
--

CREATE TABLE `affected` (
  `commit` int(11) NOT NULL COMMENT 'Identyfikator transakcji, której dotyczy wpis',
  `table` char(1) COLLATE utf8_polish_ci NOT NULL COMMENT 'Skrót tabeli, która została zmieniona',
  `record` bigint(20) NOT NULL COMMENT 'Rekord, który został zmieniony'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `comments`
--

CREATE TABLE `comments` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator komentarza',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `element` bigint(20) NOT NULL COMMENT 'Identyfikator elementu',
  `place` bigint(20) NOT NULL COMMENT 'Identyfikator miejsca',
  `weld` bigint(20) NOT NULL COMMENT 'Identyfikator spawu',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `commits`
--

CREATE TABLE `commits` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Unikalny identyfikator transakcji',
  `ip` varchar(64) COLLATE utf8_polish_ci NOT NULL COMMENT 'Adres IPv4/IPv6, z którego wykonano operację',
  `project` bigint(20) NOT NULL COMMENT 'Projekt, w którym nastąpiła transakcja',
  `source` bigint(20) NOT NULL COMMENT 'Projekt, który wygenerował transakcję',
  `log` text COLLATE utf8_polish_ci COMMENT 'Wykonane zmiany',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  `reverts` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Operacja odwracana przez tę operację',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=283 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `edges`
--

CREATE TABLE `edges` (
  `type` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Rodzaj krawędzi: miejsce (0) lub połączenie (1)',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `id` bigint(20) NOT NULL COMMENT 'Identyfikator krawędzi lub miejsca',
  `a` bigint(20) NOT NULL COMMENT 'Pierwszy z łączonych punktów',
  `b` bigint(20) NOT NULL COMMENT 'Drugi z łączonych punktów'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `elements`
--

CREATE TABLE `elements` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator elementu sieci',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `type` bigint(20) NOT NULL COMMENT 'Rodzaj kabla/urządzenia',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `from` bigint(20) NOT NULL COMMENT 'Początek kabla (places) lub miejsce pobytu urządzenia',
  `to` bigint(20) NOT NULL COMMENT 'Koniec kabla (places) lub miejsce pobytu urządzenia',
  `via` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Punkty pośrednie (places), przez które prowadzić kabel',
  `len` decimal(10,2) NOT NULL COMMENT 'Długość optyczna kabla [m]',
  `connections` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Połączenia pomiędzy portami w tym elemencie',
  `surplus` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Zapasy',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  `capacity` int(11) NOT NULL DEFAULT '0',
  `remaining` int(11) NOT NULL DEFAULT '0',
  `unrouted` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'Problem z routingiem',
  `parent` bigint(20) NOT NULL DEFAULT '0',
  `slot` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `comment` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `elements_view`
--

/*!50001 DROP VIEW IF EXISTS `elements_view`*/;
/*!50001 CREATE TABLE `elements_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `from` tinyint NOT NULL,
  `to` tinyint NOT NULL,
  `via` tinyint NOT NULL,
  `len` tinyint NOT NULL,
  `connections` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `surplus` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `capacity` tinyint NOT NULL,
  `remaining` tinyint NOT NULL,
  `unrouted` tinyint NOT NULL,
  `parent` tinyint NOT NULL,
  `slot` tinyint NOT NULL,
  `cable` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `endpoints`
--

CREATE TABLE `endpoints` (
  `id` bigint(20) NOT NULL COMMENT 'Identyfikator urządzenia',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `port` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Nazwa portu',
  `weld` bigint(20) NOT NULL COMMENT 'Dołączony spaw',
  `peer` bigint(20) NOT NULL COMMENT 'Dołączony element',
  `peer_port` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Dołączony port',
  `side` char(1) COLLATE utf8_polish_ci NOT NULL COMMENT 'Strona spawu',
  PRIMARY KEY (`project`,`weld`,`side`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `endpoints_view`
--

/*!50001 DROP VIEW IF EXISTS `endpoints_view`*/;
/*!50001 CREATE TABLE `endpoints_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `port` tinyint NOT NULL,
  `weld` tinyint NOT NULL,
  `peer` tinyint NOT NULL,
  `peer_port` tinyint NOT NULL,
  `side` tinyint NOT NULL,
  `place` tinyint NOT NULL,
  `route` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `flags`
--

CREATE TABLE `flags` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator flagi',
  `flag` char(1) COLLATE utf8_polish_ci NOT NULL COMMENT 'Znak flagi',
  `caption` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwa flagi',
  `ord` int(11) NOT NULL DEFAULT '0' COMMENT 'Kolejność na liście',
  `tables` varchar(64) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Stosowalność w tabelach',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `lengths_view`
--

/*!50001 DROP VIEW IF EXISTS `lengths_view`*/;
/*!50001 CREATE TABLE `lengths_view` (
  `project` tinyint NOT NULL,
  `route` tinyint NOT NULL,
  `len` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `lines`
--

CREATE TABLE `lines` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator połączenia',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `a` bigint(20) NOT NULL COMMENT 'Pierwszy z łączonych punktów',
  `b` bigint(20) NOT NULL COMMENT 'Drugi z łączonych punktów',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwa łącznika',
  `flags` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '0' COMMENT 'Ciąg jednoliterowych flag',
  `comment` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Komentarz',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `lines_view`
--

/*!50001 DROP VIEW IF EXISTS `lines_view`*/;
/*!50001 CREATE TABLE `lines_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `a` tinyint NOT NULL,
  `b` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `flags` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `places`
--

CREATE TABLE `places` (
  `id` bigint(20) NOT NULL COMMENT 'Identyfikator punktu',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `lat` decimal(9,6) DEFAULT NULL COMMENT 'Szerokość geograficzna WGS84',
  `lon` decimal(9,6) DEFAULT NULL COMMENT 'Długość geograficzna WGS84',
  `parent` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Punkt dołączenia',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwa miejsca',
  `flags` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  `type` tinyint(4) NOT NULL,
  `city` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `street` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `num` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `comment` int(11) DEFAULT NULL,
  UNIQUE KEY `id` (`id`,`project`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `places_view`
--

/*!50001 DROP VIEW IF EXISTS `places_view`*/;
/*!50001 CREATE TABLE `places_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `lat` tinyint NOT NULL,
  `lon` tinyint NOT NULL,
  `parent` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `flags` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `city` tinyint NOT NULL,
  `street` tinyint NOT NULL,
  `num` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `ports`
--

CREATE TABLE `ports` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `port` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Nazwa portu',
  `source` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'Źródło światła',
  `conncount` int(11) NOT NULL DEFAULT '0' COMMENT 'Ilość portów połączonych z tym, wewnątrz urządzenia',
  `ord` int(11) NOT NULL DEFAULT '0' COMMENT 'Port ordering within a device',
  `group` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Port group name',
  `sink` tinyint(3) NOT NULL DEFAULT '0' COMMENT 'Port odbiorczy?',
  `conns` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Połączenia z innymi portami',
  `basename` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Bazowa nazwa portu',
  PRIMARY KEY (`id`,`project`,`port`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `projects`
--

CREATE TABLE `projects` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator projektu',
  `designation` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Numer projektu',
  `callsign` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Słowo kluczowe',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwa projektu',
  `parent` bigint(20) NOT NULL DEFAULT '0' COMMENT 'Projekt nadrzędny',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `projects_view`
--

/*!50001 DROP VIEW IF EXISTS `projects_view`*/;
/*!50001 CREATE TABLE `projects_view` (
  `id` tinyint NOT NULL,
  `designation` tinyint NOT NULL,
  `callsign` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `parent` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `route_elements_view`
--

/*!50001 DROP VIEW IF EXISTS `route_elements_view`*/;
/*!50001 CREATE TABLE `route_elements_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `cable` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `from` tinyint NOT NULL,
  `to` tinyint NOT NULL,
  `via` tinyint NOT NULL,
  `len` tinyint NOT NULL,
  `connections` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `surplus` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `capacity` tinyint NOT NULL,
  `remaining` tinyint NOT NULL,
  `ref` tinyint NOT NULL,
  `route` tinyint NOT NULL,
  `weld` tinyint NOT NULL,
  `port` tinyint NOT NULL,
  `peer` tinyint NOT NULL,
  `peer_port` tinyint NOT NULL,
  `conncount` tinyint NOT NULL,
  `source` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Temporary table structure for view `routes`
--

/*!50001 DROP VIEW IF EXISTS `routes`*/;
/*!50001 CREATE TABLE `routes` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `route` tinyint NOT NULL,
  `a` tinyint NOT NULL,
  `a_port` tinyint NOT NULL,
  `b` tinyint NOT NULL,
  `b_port` tinyint NOT NULL,
  `by` tinyint NOT NULL,
  `flags` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `seq` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `len` tinyint NOT NULL,
  `places` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `routing`
--

CREATE TABLE `routing` (
  `id` bigint(20) NOT NULL COMMENT 'Identyfikator elementu sieci',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `order` int(11) NOT NULL COMMENT 'Numer kolejny punktu na trasie',
  `edge_id` bigint(20) NOT NULL,
  `edge_type` tinyint(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator typu',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `cable` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Kabel (1) lub urządzenie (0)',
  `vendor` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Dostawca',
  `model` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Model',
  `ports` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Specyfikacja portów',
  `connections` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Domyślne połączenia',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  `comment` text COLLATE utf8_polish_ci NOT NULL COMMENT 'Komentarz',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `types_view`
--

/*!50001 DROP VIEW IF EXISTS `types_view`*/;
/*!50001 CREATE TABLE `types_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `cable` tinyint NOT NULL,
  `vendor` tinyint NOT NULL,
  `model` tinyint NOT NULL,
  `ports` tinyint NOT NULL,
  `connections` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `login` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Login',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwisko i imię',
  `password` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Hash MD5(hasło || login)',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `users_view`
--

/*!50001 DROP VIEW IF EXISTS `users_view`*/;
/*!50001 CREATE TABLE `users_view` (
  `id` tinyint NOT NULL,
  `login` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `password` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Table structure for table `welds`
--

CREATE TABLE `welds` (
  `id` bigint(20) NOT NULL COMMENT 'Unikalny identyfikator spawu (połączenia)',
  `project` bigint(20) NOT NULL COMMENT 'Identyfikator świata',
  `route` bigint(20) NOT NULL COMMENT 'Identyfikator włókna',
  `a` bigint(20) NOT NULL COMMENT 'Element nr 1',
  `a_port` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Port dołączenia w urządzeniu 1',
  `b` bigint(20) NOT NULL COMMENT 'Element nr 2',
  `b_port` varchar(255) COLLATE utf8_polish_ci NOT NULL COMMENT 'Port dołączenia w urządzeniu 2',
  `by` bigint(20) NOT NULL COMMENT 'Urządzenie, w którym ma miejsce połączenie',
  `flags` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Flagi połączenia',
  `creat` datetime NOT NULL COMMENT 'Data utworzenia rekordu',
  `mod` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data ostatniej zmiany rekordu lub 0, jeśli nie modyfikowano',
  `deleted` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Data usunięcia rekordu lub 0, jeśli nie usunieto',
  `creat_by` int(11) NOT NULL COMMENT 'Identyfikator użytkownika tworzącego rekord',
  `mod_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika modyfikującego ostatnio rekord, 0 jeśli nie usunięto',
  `deleted_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Identyfikator użytkownika który usunął rekord, 0 jeśli nie usunięto',
  `name` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '' COMMENT 'Nazwa spawu',
  `type` tinyint(3) NOT NULL DEFAULT '0',
  `seq` varchar(255) COLLATE utf8_polish_ci NOT NULL DEFAULT '',
  `comment` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

--
-- Temporary table structure for view `welds_view`
--

/*!50001 DROP VIEW IF EXISTS `welds_view`*/;
/*!50001 CREATE TABLE `welds_view` (
  `id` tinyint NOT NULL,
  `project` tinyint NOT NULL,
  `route` tinyint NOT NULL,
  `a` tinyint NOT NULL,
  `a_port` tinyint NOT NULL,
  `b` tinyint NOT NULL,
  `b_port` tinyint NOT NULL,
  `by` tinyint NOT NULL,
  `flags` tinyint NOT NULL,
  `comment` tinyint NOT NULL,
  `creat` tinyint NOT NULL,
  `mod` tinyint NOT NULL,
  `deleted` tinyint NOT NULL,
  `creat_by` tinyint NOT NULL,
  `mod_by` tinyint NOT NULL,
  `deleted_by` tinyint NOT NULL,
  `name` tinyint NOT NULL,
  `place` tinyint NOT NULL,
  `type` tinyint NOT NULL,
  `seq` tinyint NOT NULL,
  `ref` tinyint NOT NULL
) ENGINE=MyISAM */;

--
-- Final view structure for view `elements_view`
--

/*!50001 DROP TABLE IF EXISTS `elements_view`*/;
/*!50001 DROP VIEW IF EXISTS `elements_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `elements_view` AS select `e`.`id` AS `id`,`e`.`project` AS `project`,`e`.`type` AS `type`,`e`.`name` AS `name`,`e`.`from` AS `from`,`e`.`to` AS `to`,`e`.`via` AS `via`,`e`.`len` AS `len`,`e`.`connections` AS `connections`,`e`.`comment` AS `comment`,`e`.`surplus` AS `surplus`,`e`.`creat` AS `creat`,`e`.`mod` AS `mod`,`e`.`deleted` AS `deleted`,`e`.`creat_by` AS `creat_by`,`e`.`mod_by` AS `mod_by`,`e`.`deleted_by` AS `deleted_by`,`e`.`capacity` AS `capacity`,`e`.`remaining` AS `remaining`,`e`.`unrouted` AS `unrouted`,`e`.`parent` AS `parent`,`e`.`slot` AS `slot`,`t`.`cable` AS `cable`,concat(`t`.`ref`,if((length(`e`.`name`) > 0),': ',''),if((length(`e`.`name`) > 20),concat(substr(`e`.`name`,1,20),'...'),`e`.`name`)) AS `ref` from (`elements` `e` join `types_view` `t` on(((`t`.`project` = `e`.`project`) and (`t`.`id` = `e`.`type`)))) */;

--
-- Final view structure for view `endpoints_view`
--

/*!50001 DROP TABLE IF EXISTS `endpoints_view`*/;
/*!50001 DROP VIEW IF EXISTS `endpoints_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `endpoints_view` AS select `e`.`id` AS `id`,`e`.`project` AS `project`,`e`.`port` AS `port`,`e`.`weld` AS `weld`,`e`.`peer` AS `peer`,`e`.`peer_port` AS `peer_port`,`e`.`side` AS `side`,`w`.`place` AS `place`,`w`.`route` AS `route` from (`endpoints` `e` join `welds_view` `w` on(((`w`.`id` = `e`.`weld`) and (`w`.`project` = `e`.`project`)))) */;

--
-- Final view structure for view `lengths_view`
--

/*!50001 DROP TABLE IF EXISTS `lengths_view`*/;
/*!50001 DROP VIEW IF EXISTS `lengths_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `lengths_view` AS select `fel`.`project` AS `project`,`fel`.`route` AS `route`,sum(if(isnull(`fer`.`ref`),`fel`.`len`,(`fel`.`len` / 2))) AS `len` from (`route_elements_view` `fel` left join `route_elements_view` `fer` on(((`fer`.`route` = `fel`.`route`) and (`fel`.`id` = `fer`.`id`) and (`fel`.`port` = `fer`.`port`) and (`fel`.`weld` <> `fer`.`weld`) and (`fel`.`project` = `fer`.`project`)))) where (`fel`.`cable` = 1) group by `fel`.`project`,`fel`.`route` */;

--
-- Final view structure for view `lines_view`
--

/*!50001 DROP TABLE IF EXISTS `lines_view`*/;
/*!50001 DROP VIEW IF EXISTS `lines_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `lines_view` AS select `lines`.`id` AS `id`,`lines`.`project` AS `project`,`lines`.`a` AS `a`,`lines`.`b` AS `b`,`lines`.`name` AS `name`,`lines`.`flags` AS `flags`,`lines`.`comment` AS `comment`,`lines`.`creat` AS `creat`,`lines`.`mod` AS `mod`,`lines`.`deleted` AS `deleted`,`lines`.`creat_by` AS `creat_by`,`lines`.`mod_by` AS `mod_by`,`lines`.`deleted_by` AS `deleted_by`,`lines`.`id` AS `ref` from `lines` */;

--
-- Final view structure for view `places_view`
--

/*!50001 DROP TABLE IF EXISTS `places_view`*/;
/*!50001 DROP VIEW IF EXISTS `places_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `places_view` AS select `p`.`id` AS `id`,`p`.`project` AS `project`,`p`.`lat` AS `lat`,`p`.`lon` AS `lon`,`p`.`parent` AS `parent`,`p`.`name` AS `name`,`p`.`flags` AS `flags`,`p`.`comment` AS `comment`,`p`.`creat` AS `creat`,`p`.`mod` AS `mod`,`p`.`deleted` AS `deleted`,`p`.`creat_by` AS `creat_by`,`p`.`mod_by` AS `mod_by`,`p`.`deleted_by` AS `deleted_by`,`p`.`type` AS `type`,`p`.`city` AS `city`,`p`.`street` AS `street`,`p`.`num` AS `num`,(if((length(`p`.`name`) > 0),`p`.`name`,if((length(`p`.`street`) > 0),concat(`p`.`city`,if((length(`p`.`city`) > 0),', ',''),`p`.`street`,if((length(`p`.`num`) > 0),' ',''),`p`.`num`),convert(`p`.`id` using utf8))) collate utf8_general_ci) AS `ref` from `places` `p` */;

--
-- Final view structure for view `projects_view`
--

/*!50001 DROP TABLE IF EXISTS `projects_view`*/;
/*!50001 DROP VIEW IF EXISTS `projects_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `projects_view` AS select `projects`.`id` AS `id`,`projects`.`designation` AS `designation`,`projects`.`callsign` AS `callsign`,`projects`.`name` AS `name`,`projects`.`parent` AS `parent`,`projects`.`creat` AS `creat`,`projects`.`mod` AS `mod`,`projects`.`deleted` AS `deleted`,`projects`.`creat_by` AS `creat_by`,`projects`.`mod_by` AS `mod_by`,`projects`.`deleted_by` AS `deleted_by`,concat(`projects`.`designation`,if((length(`projects`.`callsign`) > 0),' - ',''),`projects`.`callsign`,if((length(`projects`.`name`) > 0),' - ',''),`projects`.`name`) AS `ref` from `projects` */;

--
-- Final view structure for view `route_elements_view`
--

/*!50001 DROP TABLE IF EXISTS `route_elements_view`*/;
/*!50001 DROP VIEW IF EXISTS `route_elements_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `route_elements_view` AS select `e`.`id` AS `id`,`e`.`project` AS `project`,`e`.`cable` AS `cable`,`e`.`type` AS `type`,`e`.`name` AS `name`,`e`.`from` AS `from`,`e`.`to` AS `to`,`e`.`via` AS `via`,`e`.`len` AS `len`,`e`.`connections` AS `connections`,`e`.`comment` AS `comment`,`e`.`surplus` AS `surplus`,`e`.`creat` AS `creat`,`e`.`mod` AS `mod`,`e`.`deleted` AS `deleted`,`e`.`creat_by` AS `creat_by`,`e`.`mod_by` AS `mod_by`,`e`.`deleted_by` AS `deleted_by`,`e`.`capacity` AS `capacity`,`e`.`remaining` AS `remaining`,`e`.`ref` AS `ref`,`f`.`route` AS `route`,`ep`.`weld` AS `weld`,`ep`.`port` AS `port`,`ep`.`peer` AS `peer`,`ep`.`peer_port` AS `peer_port`,`p`.`conncount` AS `conncount`,`p`.`source` AS `source` from ((((`welds` `f` join `welds` `fw` on(((`fw`.`route` = `f`.`route`) and (`f`.`project` = `fw`.`project`) and (`fw`.`deleted` = 0)))) join `endpoints` `ep` on(((`ep`.`weld` = `fw`.`id`) and (`ep`.`project` = `fw`.`project`)))) join `elements_view` `e` on(((`e`.`project` = `ep`.`project`) and (`e`.`id` = `ep`.`id`) and (`e`.`deleted` = 0)))) join `ports` `p` on(((`p`.`port` = `ep`.`port`) and (`p`.`project` = `ep`.`project`) and (`p`.`id` = `e`.`type`)))) where ((`f`.`id` = `f`.`route`) and (`f`.`deleted` = 0)) */;

--
-- Final view structure for view `routes`
--

/*!50001 DROP TABLE IF EXISTS `routes`*/;
/*!50001 DROP VIEW IF EXISTS `routes`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `routes` AS select `t`.`id` AS `id`,`t`.`project` AS `project`,`t`.`route` AS `route`,`t`.`a` AS `a`,`t`.`a_port` AS `a_port`,`t`.`b` AS `b`,`t`.`b_port` AS `b_port`,`t`.`by` AS `by`,`t`.`flags` AS `flags`,`t`.`creat` AS `creat`,`t`.`mod` AS `mod`,`t`.`deleted` AS `deleted`,`t`.`creat_by` AS `creat_by`,`t`.`mod_by` AS `mod_by`,`t`.`deleted_by` AS `deleted_by`,`t`.`name` AS `name`,`t`.`type` AS `type`,`t`.`seq` AS `seq`,`t`.`comment` AS `comment`,ifnull(`lens`.`len`,0) AS `len`,group_concat(distinct `p`.`ref` order by `ep`.`source` DESC,`ep`.`id` ASC separator ' - ') AS `places`,concat(`t`.`id`,' ',ifnull(group_concat(distinct `p`.`ref` order by `ep`.`source` DESC,`ep`.`id` ASC separator ' - '),'')) AS `ref` from (((`welds` `t` left join `lengths_view` `lens` on(((`lens`.`route` = `t`.`route`) and (`lens`.`project` = `t`.`project`)))) left join `route_elements_view` `ep` on(((`ep`.`route` = `t`.`route`) and (`ep`.`project` = `t`.`project`) and (`ep`.`cable` = 0)))) left join `places_view` `p` on(((`ep`.`from` = `p`.`id`) and (`p`.`project` = `ep`.`project`) and (`p`.`deleted` = 0)))) where ((`t`.`route` = `t`.`id`) and (`t`.`deleted` = 0)) group by `t`.`id` */;

--
-- Final view structure for view `types_view`
--

/*!50001 DROP TABLE IF EXISTS `types_view`*/;
/*!50001 DROP VIEW IF EXISTS `types_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `types_view` AS select `types`.`id` AS `id`,`types`.`project` AS `project`,`types`.`cable` AS `cable`,`types`.`vendor` AS `vendor`,`types`.`model` AS `model`,`types`.`ports` AS `ports`,`types`.`connections` AS `connections`,`types`.`creat` AS `creat`,`types`.`mod` AS `mod`,`types`.`deleted` AS `deleted`,`types`.`creat_by` AS `creat_by`,`types`.`mod_by` AS `mod_by`,`types`.`deleted_by` AS `deleted_by`,concat(`types`.`model`,if((`types`.`vendor` = ''),'',concat(' (',`types`.`vendor`,')'))) AS `ref` from `types` */;

--
-- Final view structure for view `users_view`
--

/*!50001 DROP TABLE IF EXISTS `users_view`*/;
/*!50001 DROP VIEW IF EXISTS `users_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `users_view` AS select `users`.`id` AS `id`,`users`.`login` AS `login`,`users`.`name` AS `name`,`users`.`password` AS `password`,`users`.`creat` AS `creat`,`users`.`mod` AS `mod`,`users`.`deleted` AS `deleted`,`users`.`creat_by` AS `creat_by`,`users`.`mod_by` AS `mod_by`,`users`.`deleted_by` AS `deleted_by`,if((length(`users`.`name`) > 0),concat(`users`.`login`,' (',`users`.`name`,')'),`users`.`login`) AS `ref` from `users` */;

--
-- Final view structure for view `welds_view`
--

/*!50001 DROP TABLE IF EXISTS `welds_view`*/;
/*!50001 DROP VIEW IF EXISTS `welds_view`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `welds_view` AS select `w`.`id` AS `id`,`w`.`project` AS `project`,`w`.`route` AS `route`,`w`.`a` AS `a`,`w`.`a_port` AS `a_port`,`w`.`b` AS `b`,`w`.`b_port` AS `b_port`,`w`.`by` AS `by`,`w`.`flags` AS `flags`,`w`.`comment` AS `comment`,`w`.`creat` AS `creat`,`w`.`mod` AS `mod`,`w`.`deleted` AS `deleted`,`w`.`creat_by` AS `creat_by`,`w`.`mod_by` AS `mod_by`,`w`.`deleted_by` AS `deleted_by`,`w`.`name` AS `name`,if((`w`.`a_port` like '==: %'),`a`.`to`,`a`.`from`) AS `place`,`w`.`type` AS `type`,`w`.`seq` AS `seq`,concat(`a`.`ref`,' [',`w`.`a_port`,']---[',`w`.`b_port`,'] ',`b`.`ref`) AS `ref` from ((`welds` `w` join `elements_view` `a` on(((`a`.`id` = `w`.`a`) and (`a`.`project` = `w`.`project`)))) join `elements_view` `b` on(((`b`.`id` = `w`.`b`) and (`b`.`project` = `w`.`project`)))) group by `w`.`id` */;


