157 lines
8.6 KiB
PHP
157 lines
8.6 KiB
PHP
<?php
|
|
declare(strict_types=1);
|
|
|
|
use Phinx\Migration\AbstractMigration;
|
|
|
|
final class UpdateAdbViews extends AbstractMigration
|
|
{
|
|
public function up(): void
|
|
{
|
|
if($this->getEnvironment() == "thetool") {
|
|
|
|
}
|
|
|
|
if($this->getEnvironment() == "addressdb") {
|
|
$this->query("CREATE SQL SECURITY INVOKER
|
|
VIEW `view_hausnummer_new` 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`.`status_id` AS `status_id`,
|
|
`Hausnummer`.`oaid` AS `oaid`,
|
|
`Hausnummer`.`adrcd` AS `adrcd`,
|
|
`Hausnummer`.`subcd` AS `subcd`,
|
|
`Hausnummer`.`hausnummer` AS `hausnummer`,
|
|
`Hausnummer`.`stiege` AS `hausnummer_stiege`,
|
|
`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`.`gdaeigenschaft` AS `gdaeigenschaft`,
|
|
`Hausnummer`.`rw` AS `rw`,
|
|
`Hausnummer`.`hw` AS `hw`,
|
|
`Hausnummer`.`meridian` AS `meridian`,
|
|
`Hausnummer`.`rollout` AS `rollout`,
|
|
`Hausnummer`.`rollout_info` AS `rollout_info`,
|
|
`Hausnummer`.`freigabe` AS `freigabe`,
|
|
`Hausnummer`.`visibility` AS `visibility`,
|
|
`Hausnummer`.`rimo_id` AS `rimo_id`,
|
|
`Hausnummer`.`rimo_type` AS `rimo_type`,
|
|
`Hausnummer`.`rimo_ex_state` AS `rimo_ex_state`,
|
|
`Hausnummer`.`rimo_op_state` AS `rimo_op_state`,
|
|
`Hausnummer`.`rimo_fcp_name` AS `rimo_fcp_name`,
|
|
`Hausnummer`.`borderpoint_lat` AS `borderpoint_lat`,
|
|
`Hausnummer`.`borderpoint_long` AS `borderpoint_long`
|
|
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`)"
|
|
);
|
|
|
|
$this->query("CREATE SQL SECURITY INVOKER
|
|
VIEW `view_wohneinheit_new` 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`.`status_id` AS `status_id`,
|
|
`Hausnummer`.`oaid` AS `oaid`,
|
|
`Hausnummer`.`adrcd` AS `adrcd`,
|
|
`Hausnummer`.`subcd` AS `subcd`,
|
|
`Hausnummer`.`hausnummer` AS `hausnummer`,
|
|
`Hausnummer`.`stiege` AS `hausnummer_stiege`,
|
|
`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`.`gdaeigenschaft` AS `gdaeigenschaft`,
|
|
`Hausnummer`.`rw` AS `rw`,
|
|
`Hausnummer`.`hw` AS `hw`,
|
|
`Hausnummer`.`meridian` AS `meridian`,
|
|
`Hausnummer`.`rollout` AS `rollout`,
|
|
`Hausnummer`.`rollout_info` AS `rollout_info`,
|
|
`Hausnummer`.`freigabe` AS `freigabe`,
|
|
`Hausnummer`.`visibility` AS `visibility`,
|
|
`Hausnummer`.`rimo_id` AS `hausnummer_rimo_id`,
|
|
`Hausnummer`.`rimo_type` AS `hausnummer_rimo_type`,
|
|
`Hausnummer`.`rimo_ex_state` AS `hausnummer_rimo_ex_state`,
|
|
`Hausnummer`.`rimo_op_state` AS `hausnummer_rimo_op_state`,
|
|
`Hausnummer`.`rimo_fcp_name` AS `rimo_fcp_name`,
|
|
`Hausnummer`.`borderpoint_lat` AS `borderpoint_lat`,
|
|
`Hausnummer`.`borderpoint_long` AS `borderpoint_long`,
|
|
`Wohneinheit`.`id` AS `wohneinheit_id`,
|
|
`Wohneinheit`.`status_id` AS `wohneinheit_status_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`,
|
|
`Wohneinheit`.`rimo_ex_state` AS `wohneinheit_rimo_ex_state`,
|
|
`Wohneinheit`.`rimo_op_state` AS `wohneinheit_rimo_op_state`,
|
|
`Wohneinheit`.`patch_cluster` AS `patch_cluster`,
|
|
`Wohneinheit`.`patch_shelf` AS `patch_shelf`,
|
|
`Wohneinheit`.`patch_module` AS `patch_module`,
|
|
`Wohneinheit`.`patch_port` AS `patch_port`
|
|
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`)"
|
|
);
|
|
|
|
$this->query("RENAME TABLE `view_hausnummer` TO `view_hausnummer_old`");
|
|
$this->query("RENAME TABLE `view_hausnummer_new` TO `view_hausnummer`");
|
|
|
|
$this->query("RENAME TABLE `view_wohneinheit` TO `view_wohneinheit_old`");
|
|
$this->query("RENAME TABLE `view_wohneinheit_new` TO `view_wohneinheit`");
|
|
}
|
|
}
|
|
|
|
public function down(): void
|
|
{
|
|
if($this->getEnvironment() == "thetool") {
|
|
|
|
}
|
|
|
|
if($this->getEnvironment() == "addressdb") {
|
|
|
|
}
|
|
}
|
|
}
|