-- MySQL dump 10.13 Distrib 8.0.34, for Linux (x86_64) -- -- Host: localhost Database: thetool_addressdb_live -- ------------------------------------------------------ -- Server version 8.0.34-0ubuntu0.22.04.1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table `Gemeinde` -- DROP TABLE IF EXISTS `Gemeinde`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Gemeinde` ( `id` int NOT NULL AUTO_INCREMENT, `kennziffer` int NOT NULL, `code` int NOT NULL, `name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`(4)), KEY `kennziffer` (`kennziffer`) ) ENGINE=InnoDB AUTO_INCREMENT=2099 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `GemeindeNetzgebiet` -- DROP TABLE IF EXISTS `GemeindeNetzgebiet`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `GemeindeNetzgebiet` ( `id` int NOT NULL AUTO_INCREMENT, `gemeinde_id` int NOT NULL, `netzgebiet_id` int NOT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `gemeind_id-netzgebiet_id` (`gemeinde_id`,`netzgebiet_id`) ) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Hausnummer` -- DROP TABLE IF EXISTS `Hausnummer`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Hausnummer` ( `id` int NOT NULL AUTO_INCREMENT, `oaid` varchar(64) DEFAULT NULL, `adrcd` varchar(64) DEFAULT NULL, `netzgebiet_id` int NOT NULL, `extref` varchar(1024) DEFAULT NULL, `ortschaft_id` int DEFAULT NULL, `plz_id` int NOT NULL, `strasse_id` int NOT NULL, `hausnummer` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `zusatz` varchar(1024) DEFAULT NULL, `grund_nr` varchar(64) DEFAULT NULL, `gdaeigenschaft` varchar(1024) DEFAULT NULL, `rw` decimal(10,2) DEFAULT NULL, `hw` decimal(10,2) DEFAULT NULL, `meridian` varchar(64) DEFAULT NULL COMMENT 'RML/liezen', `unit_count` int NOT NULL DEFAULT '0', `gps_lat` decimal(15,10) DEFAULT NULL, `gps_long` decimal(15,10) DEFAULT NULL, `rollout` int DEFAULT NULL, `rollout_info` text, `freigabe` json DEFAULT NULL, `manual_add` int DEFAULT NULL, `manual_add_by` varchar(1024) DEFAULT NULL, `manual_add_info` text, `manual_update` int DEFAULT NULL, `manual_update_by` int DEFAULT NULL, `manual_update_info` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `rimo_id` varchar(1024) DEFAULT NULL, `rimo_fcp_name` varchar(64) DEFAULT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `strasse_id-hausnummer` (`strasse_id`,`hausnummer`(1)), KEY `strasse_id` (`strasse_id`), KEY `plz_id` (`plz_id`), KEY `netzgebiet_id` (`netzgebiet_id`), KEY `hausnummer` (`hausnummer`(2)), KEY `adrcd` (`adrcd`(4)), KEY `extref` (`extref`(4)), KEY `oaid` (`oaid`(10)), KEY `netzgebiet_id-hausnummer` (`netzgebiet_id`,`hausnummer`(2)), FULLTEXT KEY `hausnummer-full` (`hausnummer`) ) ENGINE=InnoDB AUTO_INCREMENT=2452223 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Netzgebiet` -- DROP TABLE IF EXISTS `Netzgebiet`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Netzgebiet` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `extref` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `source` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `source_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `freigabe` json DEFAULT NULL, `unit_count` int NOT NULL DEFAULT '0', `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Ortschaft` -- DROP TABLE IF EXISTS `Ortschaft`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Ortschaft` ( `id` int NOT NULL AUTO_INCREMENT, `gemeinde_id` int NOT NULL, `kennziffer` int DEFAULT NULL, `name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `plz` int DEFAULT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`(4)), KEY `kennziffer` (`kennziffer`), FULLTEXT KEY `name-full` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=17083 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Plz` -- DROP TABLE IF EXISTS `Plz`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Plz` ( `id` int NOT NULL AUTO_INCREMENT, `gemeinde_id` int NOT NULL, `plz` varchar(12) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `plzstring` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `gemeinde_id-plz` (`gemeinde_id`,`plz`), KEY `plzstring` (`plzstring`(2)), KEY `plz` (`plz`(2)) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=8089 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Strasse` -- DROP TABLE IF EXISTS `Strasse`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Strasse` ( `id` int NOT NULL AUTO_INCREMENT, `ortschaft_id` int DEFAULT NULL, `gemeinde_id` int NOT NULL, `kennziffer` int DEFAULT NULL, `name` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `name` (`name`(4)) USING BTREE, KEY `gemeinde_id-name` (`gemeinde_id`,`name`(3)), KEY `kennziffer` (`kennziffer`), FULLTEXT KEY `name-full` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=137594 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `Wohneinheit` -- DROP TABLE IF EXISTS `Wohneinheit`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `Wohneinheit` ( `id` int NOT NULL AUTO_INCREMENT, `oaid` varchar(64) DEFAULT NULL, `extref` varchar(1024) DEFAULT NULL, `hausnummer_id` int NOT NULL, `num` int DEFAULT NULL, `block` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `stiege` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `stock` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `tuer` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `zusatz` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `bezeichner` varchar(255) DEFAULT NULL, `nutzung` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL, `external_data` json DEFAULT NULL, `note` text, `create` int NOT NULL, `edit` int NOT NULL, PRIMARY KEY (`id`), KEY `extref` (`extref`(16)), KEY `hausnummer_id` (`hausnummer_id`), KEY `hausnummer-block-stiege-stock-tuer` (`hausnummer_id`,`block`(2),`stiege`(2),`stock`(2),`tuer`(2)) USING BTREE, KEY `oaid` (`oaid`(10)) ) ENGINE=InnoDB AUTO_INCREMENT=72927 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table `phinxlog` -- DROP TABLE IF EXISTS `phinxlog`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!50503 SET character_set_client = utf8mb4 */; CREATE TABLE `phinxlog` ( `version` bigint NOT NULL, `migration_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `start_time` timestamp NULL DEFAULT NULL, `end_time` timestamp NULL DEFAULT NULL, `breakpoint` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`version`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Temporary view structure for view `view_hausnummer` -- DROP TABLE IF EXISTS `view_hausnummer`; /*!50001 DROP VIEW IF EXISTS `view_hausnummer`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `view_hausnummer` AS SELECT 1 AS `netzgebiet_id`, 1 AS `netzgebiet`, 1 AS `netzgebiet_extref`, 1 AS `gemeinde_id`, 1 AS `gemeinde_kennziffer`, 1 AS `gemeinde_code`, 1 AS `gemeinde`, 1 AS `ortschaft_id`, 1 AS `ortschaft_kennziffer`, 1 AS `ortschaft`, 1 AS `plz`, 1 AS `strasse_id`, 1 AS `strasse_kennziffer`, 1 AS `strasse`, 1 AS `hausnummer_id`, 1 AS `oaid`, 1 AS `adrcd`, 1 AS `hausnummer`, 1 AS `hausnummer_extref`, 1 AS `unit_count`, 1 AS `gps_lat`, 1 AS `gps_long`, 1 AS `grund_nr`, 1 AS `rollout`, 1 AS `rollout_info`, 1 AS `freigabe`*/; SET character_set_client = @saved_cs_client; -- -- Temporary view structure for view `view_wohneinheit` -- DROP TABLE IF EXISTS `view_wohneinheit`; /*!50001 DROP VIEW IF EXISTS `view_wohneinheit`*/; SET @saved_cs_client = @@character_set_client; /*!50503 SET character_set_client = utf8mb4 */; /*!50001 CREATE VIEW `view_wohneinheit` AS SELECT 1 AS `netzgebiet_id`, 1 AS `netzgebiet`, 1 AS `netzgebiet_extref`, 1 AS `gemeinde_id`, 1 AS `gemeinde_kennziffer`, 1 AS `gemeinde_code`, 1 AS `gemeinde`, 1 AS `ortschaft_id`, 1 AS `ortschaft_kennziffer`, 1 AS `ortschaft`, 1 AS `plz`, 1 AS `strasse_id`, 1 AS `strasse_kennziffer`, 1 AS `strasse`, 1 AS `hausnummer_id`, 1 AS `oaid`, 1 AS `adrcd`, 1 AS `hausnummer`, 1 AS `hausnummer_extref`, 1 AS `unit_count`, 1 AS `gps_lat`, 1 AS `gps_long`, 1 AS `grund_nr`, 1 AS `rollout`, 1 AS `rollout_info`, 1 AS `freigabe`, 1 AS `wohneinheit_id`, 1 AS `wohneinheit_oaid`, 1 AS `wohneinheit_extref`, 1 AS `num`, 1 AS `block`, 1 AS `stiege`, 1 AS `stock`, 1 AS `tuer`, 1 AS `bezeichner`, 1 AS `zusatz`, 1 AS `nutzung`*/; SET character_set_client = @saved_cs_client; -- -- Final view structure for view `view_hausnummer` -- /*!50001 DROP VIEW IF EXISTS `view_hausnummer`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`admin`@`localhost` SQL SECURITY INVOKER */ /*!50001 VIEW `view_hausnummer` AS select `Netzgebiet`.`id` AS `netzgebiet_id`,`Netzgebiet`.`name` AS `netzgebiet`,`Netzgebiet`.`extref` AS `netzgebiet_extref`,`Gemeinde`.`id` AS `gemeinde_id`,`Gemeinde`.`kennziffer` AS `gemeinde_kennziffer`,`Gemeinde`.`code` AS `gemeinde_code`,`Gemeinde`.`name` AS `gemeinde`,`Ortschaft`.`id` AS `ortschaft_id`,`Ortschaft`.`kennziffer` AS `ortschaft_kennziffer`,`Ortschaft`.`name` AS `ortschaft`,`Plz`.`plz` AS `plz`,`Strasse`.`id` AS `strasse_id`,`Strasse`.`kennziffer` AS `strasse_kennziffer`,`Strasse`.`name` AS `strasse`,`Hausnummer`.`id` AS `hausnummer_id`,`Hausnummer`.`oaid` AS `oaid`,`Hausnummer`.`adrcd` AS `adrcd`,`Hausnummer`.`hausnummer` AS `hausnummer`,`Hausnummer`.`extref` AS `hausnummer_extref`,`Hausnummer`.`unit_count` AS `unit_count`,`Hausnummer`.`gps_lat` AS `gps_lat`,`Hausnummer`.`gps_long` AS `gps_long`,`Hausnummer`.`grund_nr` AS `grund_nr`,`Hausnummer`.`rollout` AS `rollout`,`Hausnummer`.`rollout_info` AS `rollout_info`,`Hausnummer`.`freigabe` AS `freigabe` from (((((`Hausnummer` left join `Netzgebiet` on((`Hausnummer`.`netzgebiet_id` = `Netzgebiet`.`id`))) left join `Plz` on((`Plz`.`id` = `Hausnummer`.`plz_id`))) left join `Strasse` on((`Strasse`.`id` = `Hausnummer`.`strasse_id`))) left join `Gemeinde` on((`Gemeinde`.`id` = `Strasse`.`gemeinde_id`))) left join `Ortschaft` on((`Ortschaft`.`id` = `Hausnummer`.`ortschaft_id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; -- -- Final view structure for view `view_wohneinheit` -- /*!50001 DROP VIEW IF EXISTS `view_wohneinheit`*/; /*!50001 SET @saved_cs_client = @@character_set_client */; /*!50001 SET @saved_cs_results = @@character_set_results */; /*!50001 SET @saved_col_connection = @@collation_connection */; /*!50001 SET character_set_client = utf8mb4 */; /*!50001 SET character_set_results = utf8mb4 */; /*!50001 SET collation_connection = utf8mb4_0900_ai_ci */; /*!50001 CREATE ALGORITHM=UNDEFINED */ /*!50013 DEFINER=`admin`@`localhost` SQL SECURITY INVOKER */ /*!50001 VIEW `view_wohneinheit` AS select `Netzgebiet`.`id` AS `netzgebiet_id`,`Netzgebiet`.`name` AS `netzgebiet`,`Netzgebiet`.`extref` AS `netzgebiet_extref`,`Gemeinde`.`id` AS `gemeinde_id`,`Gemeinde`.`kennziffer` AS `gemeinde_kennziffer`,`Gemeinde`.`code` AS `gemeinde_code`,`Gemeinde`.`name` AS `gemeinde`,`Ortschaft`.`id` AS `ortschaft_id`,`Ortschaft`.`kennziffer` AS `ortschaft_kennziffer`,`Ortschaft`.`name` AS `ortschaft`,`Plz`.`plz` AS `plz`,`Strasse`.`id` AS `strasse_id`,`Strasse`.`kennziffer` AS `strasse_kennziffer`,`Strasse`.`name` AS `strasse`,`Hausnummer`.`id` AS `hausnummer_id`,`Hausnummer`.`oaid` AS `oaid`,`Hausnummer`.`adrcd` AS `adrcd`,`Hausnummer`.`hausnummer` AS `hausnummer`,`Hausnummer`.`extref` AS `hausnummer_extref`,`Hausnummer`.`unit_count` AS `unit_count`,`Hausnummer`.`gps_lat` AS `gps_lat`,`Hausnummer`.`gps_long` AS `gps_long`,`Hausnummer`.`grund_nr` AS `grund_nr`,`Hausnummer`.`rollout` AS `rollout`,`Hausnummer`.`rollout_info` AS `rollout_info`,`Hausnummer`.`freigabe` AS `freigabe`,`Wohneinheit`.`id` AS `wohneinheit_id`,`Wohneinheit`.`oaid` AS `wohneinheit_oaid`,`Wohneinheit`.`extref` AS `wohneinheit_extref`,`Wohneinheit`.`num` AS `num`,`Wohneinheit`.`block` AS `block`,`Wohneinheit`.`stiege` AS `stiege`,`Wohneinheit`.`stock` AS `stock`,`Wohneinheit`.`tuer` AS `tuer`,`Wohneinheit`.`bezeichner` AS `bezeichner`,`Wohneinheit`.`zusatz` AS `zusatz`,`Wohneinheit`.`nutzung` AS `nutzung` from ((((((`Hausnummer` left join `Netzgebiet` on((`Hausnummer`.`netzgebiet_id` = `Netzgebiet`.`id`))) left join `Plz` on((`Plz`.`id` = `Hausnummer`.`plz_id`))) left join `Strasse` on((`Strasse`.`id` = `Hausnummer`.`strasse_id`))) left join `Ortschaft` on((`Ortschaft`.`id` = `Hausnummer`.`ortschaft_id`))) left join `Gemeinde` on((`Gemeinde`.`id` = `Strasse`.`gemeinde_id`))) left join `Wohneinheit` on((`Wohneinheit`.`hausnummer_id` = `Hausnummer`.`id`))) */; /*!50001 SET character_set_client = @saved_cs_client */; /*!50001 SET character_set_results = @saved_cs_results */; /*!50001 SET collation_connection = @saved_col_connection */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2023-09-06 9:28:46